Summary of DBMS_CLOUD_ADMIN Subprograms

This section covers the DBMS_CLOUD_ADMIN subprograms provided with Autonomous Database.

Subprogram	Description
ATTACH_FILE_SYSTEM Procedure	This procedure attaches a file system in a directory on your database.
CANCEL_WORKLOAD_CAPTURE Procedure	This procedure cancels the current workload capture.
CREATE_DATABASE_LINK Procedure	This procedure creates a database link to a target database in the schema calling the API. You first need to upload the wallet (cwallet.sso) containing the certificates for the target database using DBMS_CLOUD.GET_OBJECT and then create the database link using the wallet.
DETACH_FILE_SYSTEM Procedure	This procedure detaches a file system from a directory on your database.
DISABLE_APP_CONT Procedure	This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database.
DISABLE_EXTERNAL_AUTHENTICATION Procedure	This procedure disables external authentication for the Autonomous Database instance.
DISABLE_FEATURE Procedure	This procedure disables the specified feature on the Autonomous Database instance.
DISABLE_PRINCIPAL_AUTH Procedure	This procedure revokes principal based authentication for the specified provider and applies to the ADMIN user or to the specified user.
DISABLE_RESOURCE_PRINCIPAL Procedure	This procedure disables resource principal credential and creates the credential OCI$RESOURCE_PRINCIPAL. With a user name specified, other than ADMIN, the procedure grants the specified schema access to the resource principal credential.
DROP_DATABASE_LINK Procedure	This procedure drops a database link.
ENABLE_APP_CONT Procedure	This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database.
ENABLE_AWS_ARN Procedure	This procedure enables a user to create AWS ARN credentials in Autonomous Database.
ENABLE_EXTERNAL_AUTHENTICATION Procedure	This procedure enables a user to logon to Autonomous Database using the specified external authentication scheme.
ENABLE_FEATURE Procedure	This procedure enables the specified feature on the Autonomous Database instance.
ENABLE_PRINCIPAL_AUTH Procedure	This procedure enables principal authentication for the specified provider and applies to the ADMIN user or the specified user.
ENABLE_RESOURCE_PRINCIPAL Procedure	This procedure enables resource principal credential and creates the credential OCI$RESOURCE_PRINCIPAL. With a user name specified, other than ADMIN, the procedure grants the specified schema access to the resource principal credential.
FINISH_WORKLOAD_CAPTURE Procedure	This procedure stops the workload capture and uploads capture files to object storage.
PREPARE_REPLAY Procedure	This procedure prepares replay for the refreshable clone.
PURGE_FLASHBACK_ARCHIVE Procedure	This procedure purges historical data from the Flashback Data Archive.
REPLAY_WORKLOAD Procedure	This procedure is overloaded. It initiates the workload replay.
SET_FLASHBACK_ARCHIVE_RETENTION Procedure	This procedure enables ADMIN users to modify the retention period for Flashback Time Travel flashback_archive.
START_WORKLOAD_CAPTURE Procedure	This procedure initiates a workload capture.

• ATTACH_FILE_SYSTEM Procedure
  This procedure attaches a file system in the database.
• CANCEL_WORKLOAD_CAPTURE Procedure
  This procedure cancels any ongoing workload capture on the database.
• CREATE_DATABASE_LINK Procedure
  This procedure creates a database link to a target database in the schema calling the API. The overloaded form enables you to create a database link with Oracle-managed heterogeneous connectivity to a supported non-Oracle database.
• DETACH_FILE_SYSTEM Procedure
  This procedure detaches a file system from the database.
• DISABLE_APP_CONT Procedure
  This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database.
• DISABLE_EXTERNAL_AUTHENTICATION Procedure
  Disables user authentication with external authentication schemes for the database.
• DISABLE_FEATURE Procedure
  This procedure disables the specified feature on the Autonomous Database instance.
• DISABLE_PRINCIPAL_AUTH Procedure
  This procedure revokes principal based authentication for a specified provider on Autonomous Database and applies to the ADMIN user or to the specified user.
• DISABLE_RESOURCE_PRINCIPAL Procedure
  Disable resource principal credential for the database or for the specified schema.
• DROP_DATABASE_LINK Procedure
  This procedure drops a database link.
• ENABLE_APP_CONT Procedure
  This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database.
• ENABLE_AWS_ARN Procedure
  This procedure enables an Autonomous Database instance to use Amazon Resource Names (ARNs) to access AWS resources.
• ENABLE_EXTERNAL_AUTHENTICATION Procedure
  Enable users to login to the database with external authentication schemes.
• ENABLE_FEATURE Procedure
  This procedure enables the specified feature on the Autonomous Database instance.
• ENABLE_PRINCIPAL_AUTH Procedure
  This procedure enables principal authentication on Autonomous Database for the specified provider and applies to the ADMIN user or the specified user.
• ENABLE_RESOURCE_PRINCIPAL Procedure
  Enable resource principal credential for the database or for the specified schema. This procedure creates the credential OCI$RESOURCE_PRINCIPAL.
• FINISH_WORKLOAD_CAPTURE Procedure
  This procedure finishes the current workload capture, stops any subsequent workload capture requests to the database, and uploads the capture files to Object Storage.
• PREPARE_REPLAY Procedure
  The PREPARE_REPLAY procedure prepares the refreshable clone for a replay.
• PURGE_FLASHBACK_ARCHIVE Procedure
  This procedure enables ADMIN users to purge historical data from Flashback Data Archive. You can either purge all historical data from Flashback Data Archive flashback_archive or selective data based on timestamps or System Change Number.
• REPLAY_WORKLOAD Procedure
  This procedure initiates a workload replay on your Autonomous Database instance. The overloaded form enables you to replay the capture files from an Autonomous Database instance, on-premises database, or other cloud service databases.
• SET_FLASHBACK_ARCHIVE_RETENTION Procedure
  This procedure allows ADMIN users to modify the retention period for Flashback Data Archive flashback_archive.
• START_WORKLOAD_CAPTURE Procedure
  This procedure initiates a workload capture on your Autonomous Database instance.

ATTACH_FILE_SYSTEM Procedure

This procedure attaches a file system in the database.

The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure attaches a file system in your database and stores information about the file system in the DBA_CLOUD_FILE_SYSTEMS view.

Syntax

DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name         IN VARCHAR2,
    file_system_location     IN VARCHAR2,
    directory_name           IN VARCHAR2,
    description              IN VARCHAR2
);

Parameters

Parameter	Description
file_system_name	Specifies the name of the file system. This parameter is mandatory.
file_system_location	Specifies the location of the file system. The value you supply with file_system_location consists of a Fully Qualified Domain Name (FQDN) and a file path in the form: FQDN:file_path. For example: FQDN: myhost.sub000445.myvcn.oraclevcn.com For Oracle Cloud Infrastructure File Storage set the FQDN in Show Advanced Options when you create a file system. See Creating File Systems for more information. File Path: /results This parameter is mandatory.
directory_name	Specifies the directory name for the attached file system. The directory must exist. This parameter is mandatory.
description	Provides a description of the task. This parameter is mandatory.

Example:

BEGIN
   DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM (
    file_system_name      => 'FSS',
    file_system_location  => 'myhost.sub000445.myvcn.oraclevcn.com:/results',
    directory_name        => 'FSS_DIR',
    description           => 'Source NFS for sales data'
  );
END;
/     

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• You must have WRITE privilege on the directory object in the database to attach a file system using DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM.

• The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure can only attach a private File Storage Service in databases with Private Endpoints enabled.

  See OCI File Storage Service and Configuring Network Access with Private Endpoints for more information.

• The DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM procedure looks up the Network File System hostname on the customer's virtual cloud network (VCN). The error "ORA-20000: Mounting NFS fails" is returned if the hostname specified in the location cannot be located.

• Oracle Cloud Infrastructure File Storage uses NFS Version 3 to share

• If you attach to non-Oracle Cloud Infrastructure File Storage systems, the procedure supports NFS Version 2 and NFS Version 3

• DBMS_CLOUD_ADMIN.ATTACH_FILE_SYSTEM does not support NFS Version 4

CANCEL_WORKLOAD_CAPTURE Procedure

This procedure cancels any ongoing workload capture on the database.

Syntax

This procedure cancels the current workload capture and enables refresh on the refreshable clone.

DBMS_CLOUD_ADMIN.CANCEL_WORKLOAD_CAPTURE;

Example

BEGIN
   DBMS_CLOUD_ADMIN.CANCEL_WORKLOAD_CAPTURE;
END;
/

Usage Note

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

CREATE_DATABASE_LINK Procedure

This procedure creates a database link to a target database in the schema calling the API. The overloaded form enables you to create a database link with Oracle-managed heterogeneous connectivity to a supported non-Oracle database.

Syntax

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
       db_link_name         IN VARCHAR2,
       hostname             IN VARCHAR2,
       port                 IN NUMBER,
       service_name         IN VARCHAR2,
       ssl_server_cert_dn   IN VARCHAR2 DEFAULT,
       credential_name      IN VARCHAR2 DEFAULT,
       directory_name       IN VARCHAR2 DEFAULT,
       gateway_link         IN BOOLEAN DEFAULT,
       public_link          IN BOOLEAN DEFAULT,
       private_target       IN BOOLEAN DEFAULT
       gateway_params       IN CLOB DEFAULT);

Parameters

Parameter	Description
db_link_name	The name of the database link to create.
hostname	The hostname for the target database. When you specify a connection with Oracle-managed heterogeneous connectivity by supplying the gateway_params parameter, note the following: When the db_type value is GOOGLE_BIGQUERY the hostname is not used and you can provide value such as example.com. When the db_type value is SNOWFLAKE the hostname is the Snowflake account identifier. To find your Snowflake account identifier, see Account Identifier Formats by Cloud Platform and Region.
port	Specifies the port for the connections to the target database. When you specify a connection with Oracle-managed heterogeneous connectivity using the gateway_params parameter, set the port based on the db_type value: AWSREDSHIFT: use port 5439 AZURE: use port 1433 DB2: use port 2500 for Db2 versions >= 11.5.6 DB2: use port 5000 for Db2 versions <= 11.5.5 GOOGLE_ANALYTICS: use port 433 GOOGLE_BIGQUERY: use port 433 HIVE: use port 433 MONGODB: use port 27017 MYSQL: use port 3306 POSTGRES: use port 5432 SALESFORCE: use port 19937 SNOWFLAKE: use port 443 See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for more information.
service_name	The service_name for the database to link to. For a target Autonomous Database, find the service name by one of the following methods: Look in the tnsnames.ora file in the wallet.zip that you download from an Autonomous Database for your connection. Click Database connection on the Oracle Cloud Infrastructure Console. In the Connection Strings area, each connection string includes a service_name entry with the connection string for the corresponding service. When both Mutual TLS (mTLS) and TLS connections are allowed, under TLS authentication select TLS to view the TNS names and connection strings for connections with TLS authentication. See View TNS Names and Connection Strings for an Autonomous Database Instance for more information. Query V$SERVICES view. For example: SELECT name FROM V$SERVICES; When you specify a connection with Oracle-managed heterogeneous connectivity using the gateway_params parameter, the service_name is the database name of the non-Oracle database.
ssl_server_cert_dn	The DN value found in the server certificate. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. The ssl_server_cert_dn must be NULL when you supply the gateway_params parameter. T o connect to an Oracle Database on a private endpoint without a wallet, specify a NULL value for the ssl_server_cert_dn parameter. In addition, to connect to an Oracle Database with TCP, the following must be true: The target database must be on a private endpoint. The directory_name parameter must be NULL. The private_target parameter must be TRUE.
credential_name	The name of a stored credential created with DBMS_CLOUD.CREATE_CREDENTIAL. This is the credentials to access the target database.
directory_name	The directory for the stored cwallet.sso file. The default value for this parameter is 'data_pump_dir'. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. The directory_name is not required when you supply the gateway_params parameter. T o connect to an Oracle Database with TCP, specify a NULL value for the directory_name parameter. In addition, to connect to an Oracle Database with TCP, the following must be true: The target database must be on a private endpoint. The directory_name parameter must be NULL. The private_target parameter must be TRUE.
gateway_link	Indicates if the database link is created to another Oracle Database or to an Oracle Database Gateway. If gateway_link is set to FALSE, this specifies a database link to another Autonomous Database or to another Oracle Database. If gateway_link is set to TRUE, this specifies a database link to a non-Oracle system. This creates a connect descriptor in the database link definition that specifies (HS=OK). When gateway_link is set to TRUE and gateway_params is NULL, this specifies a database link to a customer-managed Oracle gateway. The default value for this parameter is FALSE.
public_link	Indicates if the database link is created as a public database link. To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with this parameter set to TRUE, the user invoking the procedure must have EXECUTE privilege on the credential associated with the public database link and must have the CREATE PUBLIC DATABASE LINK system privilege. The EXECUTE privilege on the credential can be granted either by the ADMIN user or by the credential owner. The default value for this parameter is FALSE.
private_target	When a database link accesses a hostname that needs to be resolved in a VCN DNS server, specify the private_target parameter with value TRUE. When private_target is TRUE, the hostname parameter must be a single hostname. Using an IP address or SCAN IP for the hostname is not supported. The default value for this parameter is FALSE.
gateway_params	Specify the target database type for Oracle-managed heterogeneous connectivity to connect to non-Oracle databases. The db_type value is one of: AWSREDSHIFT AZURE DB2 GOOGLE_BIGQUERY * See Usage Notes for additional supported gateway_params when db_type is GOOGLE_BIGQUERY. HIVE * See Usage Notes for additional supported gateway_params when db_type is HIVE. MONGODB MYSQL POSTGRES SALESFORCE * See Usage Notes for additional supported gateway_params when db_type is SALESFORCE. SNOWFLAKE * See Usage Notes for additional supported gateway_params when db_type is SNOWFLAKE. NULL When gateway_params is NULL and gateway_link is set to TRUE, this specifies a database link to a customer-managed Oracle gateway. Specify the parameter with the json_object form. For example: gateway_params => json_object('db_type' value 'AWSREDSHIFT') See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for required port values for each database type. When gateway_params is NULL and private_target is TRUE, if directory_name is NULL, a TCP-based database link is created. When gateway_params is NULL and private_target is TRUE, if directory_name is NULL, a TCPS-based database link is created.

Usage Notes

• When you specify the gateway_params parameter, for some db_type values additional gateway_params parameters are supported:

  • GOOGLE_BIGQUERY

    When db_type is GOOGLE_BIGQUERY, the parameter project is valid. This parameter specifies the project name on GOOGLE_BIGQUERY and is required.

    The table name you specify when you use SELECT with Google BigQuery must be in quotes. For example:

    SELECT * FROM "sales"@GOOGLE_BIGQUERY_LINK

  • HIVE

    When db_type is HIVE, the parameter http_path is valid. This parameter specifies the HttpPath value, if required, to connect to the Hive instance.

  • SALESFORCE

    When the db_type is SALESFORCE, the parameter: security_token is valid. A security token is a case-sensitive alphanumeric code. Supplying a security_token value is required to access Salesforce. For example:

    gateway_params => JSON_OBJECT(
         'db_type' value 'salesforce',
         'security_token' value 'security_token_value' )

    See Reset Your Security Token for more information.

  • SNOWFLAKE

    When the db_type is SNOWFLAKE, the parameters: role, schema, and warehouse are valid. These values specify a different schema, role, or warehouse value, other than the default. For example:

    gateway_params => JSON_OBJECT(
         'db_type' value 'snowflake',
         'role' value 'ADMIN',
         'schema' value 'PUBLIC',
         'warehouse' value 'TEST' )

• When you use the private_target parameter, note that database links from an Autonomous Database to a database service that is on a private endpoint are only supported in commercial regions and US Government regions.

  This feature is enabled by default in all commercial regions.

  This feature is enabled by default in US Government regions for newly provisioned databases.

  For existing US Government databases on a private endpoint, if you want to create database links from an Autonomous Database to a target in a US Government region, please file a Service Request at Oracle Cloud Support and request to enable the private endpoint in government regions database linking feature.

  US Government regions include the following:

  • Oracle Cloud Infrastructure US Government Cloud with FedRAMP Authorization
  • Oracle Cloud Infrastructure US Federal Cloud with DISA Impact Level 5 Authorization
• When connecting to a non-Oracle database, database linking is only supported if the target database is accessible through a public IP or a public hostname. See Create Database Links to Non-Oracle Databases for more information.

• To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with a user other than ADMIN you need to grant EXECUTE and CREATE DATABASE LINK privileges to that user. For example, run the following command as ADMIN to grant privileges to adb_user:

  GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO adb_user;
  GRANT CREATE DATABASE LINK TO adb_user;

• Only one wallet file is valid per directory specified with the directory_name parameter. You can only upload one cwallet.sso at a time to the directory you choose for wallet files. This means with a cwallet.sso in a directory, you can only create database links to the databases for which the wallet in that directory is valid. To use multiple cwallet.sso files with database links you need to create additional directories and put each cwallet.sso in a different directory.

  See Create Directory in Autonomous Database for information on creating directories.

• To create a database link to an Autonomous Database, set GLOBAL_NAMES to FALSE on the source database (non-Autonomous Database).

  SQL> ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
   
  System altered.
  
  SQL> SHOW PARAMETER GLOBAL_NAMES
  NAME                     TYPE        VALUE
  ----------------------   ----------- -----------
  global_names             boolean     FALSE
  

• When the private_target parameter is TRUE, the hostname parameter specifies a private host inside the VCN.

Examples

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'DB_LINK_CRED',
     username => 'adb_user',
     password => 'password');
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
     db_link_name => 'SALESLINK', 
     hostname => 'adb.eu-frankfurt-1.oraclecloud.com', 
     port => '1522',
     service_name => 'example_medium.adb.example.oraclecloud.com',
     ssl_server_cert_dn => 'CN=adb.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
     credential_name => 'DB_LINK_CRED');
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'AWS_REDSHIFT_LINK_CRED',
    username => 'NICK',
    password => 'password'
  );
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'AWSREDSHIFT_LINK', 
          hostname => 'example.com', 
          port => '5439',
          service_name => 'example_service_name',
          credential_name => 'AWS_REDSHIFT_LINK_CRED',
          gateway_params => JSON_OBJECT('db_type'  value 'AWSREDSHIFT'),
          ssl_server_cert_dn => NULL);
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'PRIVATE_ENDPOINT_CRED',
    username => 'db_user',
    password => 'password'
  );
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'PRIVATE_ENDPOINT_DB_LINK', 
          hostname => 'exampleHostname', 
          port => '1521',
          service_name => 'exampleServiceName',
          credential_name => 'PRIVATE_ENDPOINT_CRED',
          ssl_server_cert_dn => NULL,
          directory_name => NULL,
          private_target => TRUE);
END;
/

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'GOOGLE_BIGQUERY_CRED',
    params => JSON_OBJECT( 'gcp_oauth2' value JSON_OBJECT(
          'client_id' value 'client_id',
          'client_secret' value 'client_secret', 
          'refresh_token' value 'refresh_token' )));

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'GOOGLE_BIGQUERY_LINK', 
          hostname => 'example.com', 
          port => '443',
          service_name => 'example_service_name',
          credential_name => 'GOOGLE_BIGQUERY_CRED',
          gateway_params => JSON_OBJECT(
                     'db_type' value 'GOOGLE_BIGQUERY',
                     'project' value 'project_name1' ));
END;
/

The table name you specify when you use SELECT with Google BigQuery must be in quotes. For example:

SELECT * FROM "sales"@GOOGLE_BIGQUERY_LINK

DETACH_FILE_SYSTEM Procedure

This procedure detaches a file system from the database.

The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure detaches a file system from your database. In addition to that, the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure also removes the information about the file system from the DBA_CLOUD_FILE_SYSTEMS view.

Syntax

DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM(
    file_system_name         IN VARCHAR2
);

Parameters

Parameter	Description
file_system_name	Specifies the name of the file system. This parameter is mandatory.

Example:

BEGIN
   DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM (
    file_system_name      => 'FSS'                                       
  );                                                                          
END;                                                                          
/     

Usage Notes

• To run this procedure, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• You must have the WRITE privilege on the directory object in the database, to detach a file system from a directory using the DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure.

• The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure can only detach a private File Storage Service in databases with Private Endpoints enabled.

  See OCI File Storage Service and Configuring Network Access with Private Endpoints for more information.

• The DBMS_CLOUD_ADMIN.DETACH_FILE_SYSTEM procedure looks up the Network File System hostname on the customer's virtual cloud network (VCN). The error "ORA-20000: Mounting NFS fails" is returned if the hostname specified in the location cannot be located.

DISABLE_APP_CONT Procedure

This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_APP_CONT(
    service_name      IN VARCHAR2);

Parameters

Parameter

Description

service_name

The service_name for the Autonomous Database service. To find service names: Look in the tnsnames.ora file in the wallet.zip that you download from an Autonomous Database for your connection. Click Database connection on the Oracle Cloud Infrastructure Console. In the Connection strings area, each connection string includes a service_name entry that contains the connection string for the corresponding service. When both Mutual TLS (mTLS) and TLS connections are allowed, under TLS authentication select TLS to view the TNS names and connection strings for connections with TLS authentication. See View TNS Names and Connection Strings for an Autonomous Database Instance for more information. Query V$SERVICES view. For example: SELECT name FROM V$SERVICES;

Usage Notes

See Overview of Application Continuity for more information on Application Continuity.

Example

BEGIN
    DBMS_CLOUD_ADMIN.DISABLE_APP_CONT(
        service_name => 'nv123abc1_adb1_high.adb.oraclecloud.com' );
END;
/

Verify the value as follows:

SELECT name, failover_type FROM DBA_SERVICES;

NAME                                                    FAILOVER_TYPE
------------------------------------------------------- --------------
nv123abc1_adb1_high.adb.oraclecloud.com      

DISABLE_EXTERNAL_AUTHENTICATION Procedure

Disables user authentication with external authentication schemes for the database.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;

Exceptions

Exception	Error	Description
invalid_ext_auth	ORA-20004	See the accompanying message for a detailed explanation.

Example

BEGIN 
   DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;
END;
/

PL/SQL procedure successfully completed.

DISABLE_FEATURE Procedure

This procedure disables the specified feature on the Autonomous Database instance.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name    IN  VARCHAR2);

Parameters

Parameter	Description
feature_name	Specifies the feature type to be disabled. Supported values are: 'ORAMTS': Disable OraMTS feature. 'AUTO_DST_UPGRADE': Disable AUTO DST feature. 'OWM': Disable Oracle Workspace Manager. This parameter is mandatory.

Example

BEGIN 
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'ORAMTS');   
END;
/

Example

BEGIN 
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'AUTO_DST_UPGRADE');   
END;
/

Example

BEGIN 
DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
    feature_name => 'OWM');   
END;
/

Usage Notes

• To disable the OraMTS, AUTO_DST_UPGRADE, or OWM features for your Autonomous Database instance, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• When the AUTO_DST_UPGRADE feature is disabled, if new timezone versions are available the Autonomous Database instance does not upgrade to use the latest available version.

• Query dba_cloud_config to verify that AUTO_DST_UPGRADE is disabled.

  SELECT param_name, param_value FROM dba_cloud_config WHERE
         LOWER(param_name) = 'auto_dst_upgrade';
  
  0 rows selected.

DISABLE_PRINCIPAL_AUTH Procedure

This procedure revokes principal based authentication for a specified provider on Autonomous Database and applies to the ADMIN user or to the specified user.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
       provider    IN VARCHAR2,
       username    IN VARCHAR2 DEFAULT 'ADMIN' );

Parameters

Parameter	Description
provider	Specifies the type of provider. Valid values: AWS AZURE GCP OCI
username	Specifier the user to disable principal based authentication for. A null value is valid for the username. If username is not specified, the procedure applies for the "ADMIN" user.

Usage Notes

• When the provider value is AZURE and the username is ADMIN, the procedure disables Azure service principal based authentication on Autonomous Database and deletes the Azure application on the Autonomous Database instance.

• When the provider value is AZURE and the username is a user other than the ADMIN user, the procedure revokes the privileges from the specified user. The ADMIN user and other users that are enabled to use the Azure service principal can continue to use ADMIN.AZURE$PA and the application that is created for the Autonomous Database instance remains on the instance.

Examples

BEGIN 
     DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
       provider => 'AZURE',
       username => 'SCOTT');
END;
/

BEGIN 
     DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
       provider => 'GCP');
END;
/

DISABLE_RESOURCE_PRINCIPAL Procedure

Disable resource principal credential for the database or for the specified schema.

Syntax

DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL(
   username         IN VARCHAR2);

Parameter

Parameter	Description
username	Specifies an optional user name. The name of the database schema to remove resource principal access. If you do not supply a username, the username is set to ADMIN and the command removes the OCI$RESOURCE_PRINCIPAL credential.

Exceptions

Exception	Error	Description
resource principal is already disabled	ORA-20031	If you attempt to disable the resource principal when it is already disabled.

Usage Notes

• Resource principal is not available with refreshable clones.

• You must set up a dynamic group and policies for the dynamic group before you call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

  See the following for more information on creating policies, creating a dynamic group, and creating rules:

  • Use Resource Principal to Access Oracle Cloud Infrastructure Resources

  • Managing Dynamic Groups

  • Managing Policies

• Verify that a resource principal credential is enabled by querying one of the views: DBA_CREDENTIALS or ALL_TAB_PRIVS.

  For example, as the ADMIN user query the view DBA_CREDENTIALS:

  SELECT owner, credential_name FROM dba_credentials 
          WHERE credential_name = 'OCI$RESOURCE_PRINCIPAL' AND owner = 'ADMIN'; 
  
  OWNER  CREDENTIAL_NAME
  -----  ----------------------
  ADMIN  OCI$RESOURCE_PRINCIPAL 
  

  For example, as a non-ADMIN user query the view ALL_TAB_PRIVS:

  SELECT grantee, table_name, grantor, FROM ALL_TAB_PRIVS 
            WHERE  grantee = 'ADB_USER';
  
  GRANTEE   TABLE_NAME GRANTOR
  --------- -------------------------------------
  ADB_USER  OCI$RESOURCE_PRINCIPAL ADMIN

Example

EXEC DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL();

PL/SQL procedure successfully completed. 

SQL> select owner, credential_name from dba_credentials where credential_name = 'OCI$RESOURCE_PRINCIPAL'; 

No rows selected.

DROP_DATABASE_LINK Procedure

This procedure drops a database link.

Syntax

DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
        db_link_name      IN VARCHAR2,
        public_link       IN BOOLEAN DEFAULT);

Parameters

Parameter	Description
db_link_name	The name of the database link to drop.
public_link	To run DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK with public_link set to TRUE, you must have the DROP PUBLIC DATABASE LINK system privilege. The default value for this parameter is FALSE.

Example

BEGIN
    DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
        db_link_name => 'SALESLINK' );
END;
/

Usage Notes

After you are done using a database link and you run DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK, to ensure security of your Oracle database remove any stored wallet files. For example:

• Remove the wallet file in Object Store.

• Use DBMS_CLOUD.DELETE_FILE to remove the wallet file from the data_pump_dir directory or from the user defined directory where the wallet file was uploaded.

ENABLE_APP_CONT Procedure

This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_APP_CONT(
    service_name      IN VARCHAR2);

Parameters

Parameter

Description

service_name

The service_name for the Autonomous Database service. To find service names: Look in the tnsnames.ora file in the wallet.zip that you download from an Autonomous Database for your connection. Click Database connection on the Oracle Cloud Infrastructure Console. In the Connection strings area, each connection string includes a service_name entry that contains the connection string for the corresponding service. When both Mutual TLS (mTLS) and TLS connections are allowed, under TLS authentication select TLS to view the TNS names and connection strings for connections with TLS authentication. See View TNS Names and Connection Strings for an Autonomous Database Instance for more information. Query V$SERVICES view. For example: SELECT name FROM V$SERVICES;

Usage Notes

See Overview of Application Continuity for more information on Application Continuity.

Example

BEGIN
    DBMS_CLOUD_ADMIN.ENABLE_APP_CONT(
        service_name => 'nvthp2ht_adb1_high.adb.oraclecloud.com'
    );
END;
/

Verify the value as follows:

SELECT name, failover_type FROM DBA_SERVICES;

NAME                                                    FAILOVER_TYPE
------------------------------------------------------- -------------
nvthp2ht_adb1_high.adb.oraclecloud.com                 TRANSACTION

ENABLE_AWS_ARN Procedure

This procedure enables an Autonomous Database instance to use Amazon Resource Names (ARNs) to access AWS resources.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN(
       username     IN VARCHAR2 DEFAULT NULL,
       grant_option IN BOOLEAN DEFAULT FALSE);

Parameters

Parameter	Description
username	Name of the user to enable to use Amazon Resource Names (ARNs). A null value is valid for the username. If username is not specified, the procedure applies for the "ADMIN" user.
grant_option	When username is supplied, if grant_option is TRUE the specified username can enable Amazon Resource Names (ARNs) usage for other users.

Example

BEGIN
    DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN(
        username => 'adb_user'); 
END;
/

Usage Note

• You must be the ADMIN user to run the procedure DBMS_CLOUD_ADMIN.ENABLE_AWS_ARN.

See Use Amazon Resource Names (ARNs) to Access AWS Resources for more information.

ENABLE_EXTERNAL_AUTHENTICATION Procedure

Enable users to login to the database with external authentication schemes.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
   type         IN VARCHAR2,
   force        IN BOOLEAN DEFAULT FALSE,
   params       IN CLOB DEFAULT NULL
);

Parameter

Parameter	Description
type	Specifies the external authentication type. Valid values: or . 'OCI_IAM' 'AZURE_AD' 'CMU' 'KERBEROS'
force	(Optional) Override a currently enabled external authentication scheme. Valid values are TRUE or FALSE. The default value is FALSE.
params	A JSON string that provides additional parameters for the external authentication. CMU parameters: location_uri: specifies the cloud storage URI for the bucket where files required for CMU are stored. If you specify location_uri there is a fixed name directory object CMU_WALLET_DIR created in the database at the path 'cmu_wallet' to save the CMU configuration files. In this case, you do not need to supply the directory_name parameter. credential_name: specifies the credentials that are used to download the CMU configuration files from the Object Store to the directory object. Default value is NULL which allows you to provide a Public, Preauthenticated, or pre-signed URL for Object Store bucket or subfolder. directory_name: specifies the directory name where configuration files required for CMU are stored. If directory_name is supplied, you are expected to copy the CMU configuration files dsi.ora and cwallet.sso to this directory object. KERBEROS parameters: location_uri: specifies the cloud storage URI for the the bucket where files required for Kerberos are stored. If you specify location_uri there is a fixed name directory object KERBEROS_DIR created in the database to save the Kerberos configuration files. In this case, you do not need to supply the directory_name parameter. credential_name: specifies the credential that are used to download Kerberos configuration files from the Object Store location to the directory object. Default value is NULL which allows you to provide a Public, Preauthenticated, or pre-signed URL for Object Store bucket or subfolder. directory_name: specifies the directory name where files required for Kerberos are stored. If directory_name is supplied, you are expected to copy the Kerberos configuration files to this directory object. AZURE_AD parameters: tenant_id: Tenant ID of the Azure Account. Tenant Id specifies the Autonomous Database instance's Azure AD application registration. application_id: Azure Application ID created in Azure AD to assign roles/schema mappings for external authentication in the Autonomous Database instance. application_id_uri: Unique URI assigned to the Azure Application. This it the identifier for the Autonomous Database instance. The name must be domain qualified (this supports cross tenancy resource access). The maximum length for this parameter is 256 characters.

Exceptions

Exception	Error	Description
invalid_ext_auth	ORA-20004	See the accompanying message for a detailed explanation.

Usage Notes

• With type OCI_IAM, if the resource principal is not enabled on the Autonomous Database instance, this routine enables resource principal with DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

• This procedure sets the system parameters IDENTITY_PROVIDER_TYPE and IDENTITY_PROVIDER_CONFIG to required users to access the instance with Oracle Cloud Infrastructure Identity and Access Management authentication and authorization.

Examples

Enable OCI_IAM Authentication

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'OCI_IAM',
     force=> TRUE );
END;
/

PL/SQL procedure successfully completed.

Enable CMU Authentication for Microsoft Active Directory

You pass in a directory name that contains the CMU configuration files through params JSON argument.

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'CMU',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'CMU_DIR'); // CMU_DIR directory object already exists
END;
/

PL/SQL procedure successfully completed.

You pass in a location URI pointing to an Object Storage location that contains CMU configuration files through params JSON argument.

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
       type     => 'CMU',
       params   => JSON_OBJECT('location_uri' value 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
                               'credential_name' value 'my_credential_name')
   );
END;
/

PL/SQL procedure successfully completed.

Enable Azure AD Authentication

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'AZURE_AD',
     force => TRUE,
     params   => JSON_OBJECT( 'tenant_id' VALUE '....',
                              'application_id' VALUE '...',
                              'application_id_uri' VALUE '.....' ));
END;
/

PL/SQL procedure successfully completed.

Enable Kerberos Authentication

You pass in a directory name that contains Kerberos configuration files through params JSON argument.

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('directory_name' value 'KERBEROS_DIR'); // KERBEROS_DIR directory object already exists
END;
/

PL/SQL procedure successfully completed.

You pass in a location URI pointing to an Object Storage location that contains Kerberos configuration files through params JSON argument.

BEGIN DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
     type => 'KERBEROS',
     force => TRUE,
     params => JSON_OBJECT('location_uri' value 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
                           'credential_name' value 'my_credential_name');
END;
/

PL/SQL procedure successfully completed.

ENABLE_FEATURE Procedure

This procedure enables the specified feature on the Autonomous Database instance.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
    feature_name     IN VARCHAR2,
    params           IN  CLOB   DEFAULT NULL);

Parameters

Parameter	Description
feature_name	Name of the feature to enable. The supported values are: 'JAVAVM': Enable JAVAVM feature. 'ORAMTS': Enable OraMTS feature. 'AUTO_DST_UPGRAD': Enable AUTO DST feature. 'OWM': Enable Oracle Workspace Manager. This parameter is mandatory.
params	A JSON string that provides additional parameters for some features. The params parameter is: location_uri: the location_uri accepts a string value. The value specifies the HTTPS URL for the OraMTS server in a customer network.

Example to Enable JAVAVM Feature:

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE (
        feature_name => 'JAVAVM' );
END;
/

Example to Enable Auto DST Feature:

BEGIN
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE (
        feature_name => 'AUTO_DST_UPGRADE' );
END;
/

Example to Enable OraMTS Feature:

BEGIN 
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'ORAMTS',   
        params       => JSON_OBJECT('location_uri' VALUE 'https://mymtsserver.mycorp.com')
   );
END;
/

Example to Enable OWM Feature:

BEGIN 
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'OWM' );
END;
/

Usage Notes

• After you run DBMS_CLOUD_ADMIN.ENABLE_FEATURE with feature_name value 'JAVAVM', you must restart the Autonomous Database instance to install Oracle Java.

• To enable AUTO_DST_UPGRADE, ORAMTS, JAVAVM, or OWM features for your database, you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• After you enable AUTO_DST_UPGRADE, the next time you restart, or stop and then start the Autonomous Database instance, the instance upgrades to use the latest available timezone version. When is AUTO_DST_UPGRADE enabled, when new timezone versions are available, the instance continues to upgrade to the latest available version on every subsequent restart or stop and start, until the feature is disabled.

• Query dba_cloud_config to verify that AUTO_DST_UPGRADE is enabled.

  SELECT param_name, param_value FROM dba_cloud_config WHERE
         LOWER(param_name) = 'auto_dst_upgrade';
  
  PARAM_NAME              PARAM_VALUE                              
  ----------------------- ---------------------------------------- 
  auto_dst_upgrade        enable                                   

ENABLE_PRINCIPAL_AUTH Procedure

This procedure enables principal authentication on Autonomous Database for the specified provider and applies to the ADMIN user or the specified user.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
       provider    IN VARCHAR2,
       username    IN VARCHAR2 DEFAULT 'ADMIN',
       params      IN CLOB DEFAULT NULL);

Parameters

Parameter	Description
provider	Specifies the type of provider. Valid values: AWS AZURE GCP OCI
username	Name of the user who has principal authentication usage enabled. A null value is valid for the username. If username is not specified, the procedure applies for the "ADMIN" user.
params	Specifies the configuration parameters. When the provider parameter is AWS, GCP, or OCI, params is not required. The default value is NULL. When the provider parameter is AZURE, the following options are valid: azure_tenantid: with the value of the Azure tenant ID. grant_option: with the value specified as a boolean value: TRUE or FALSE. The default is FALSE. When TRUE and a username is specified, the specified user can enable Azure principal authentication for other users. When the provider parameter is GCP, the following option is valid: grant_option: with the value specified as a boolean value: TRUE or FALSE. The default is FALSE. When TRUE and a username is specified, the specified user can enable Google service account authentication for other users.

Usage Note

When the provider parameter is AZURE, the params parameter must include the azure_tenantid in the following cases:

• When DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH is called for the first time.

• When DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH is called for the first time after DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH is called with the provider parameter AZURE and the username ADMIN.

Examples

BEGIN 
     DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
       provider => 'AZURE',
       username => 'SCOTT',
       params   => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/

BEGIN
     DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH( 
     provider => 'GCP');
END;
/

ENABLE_RESOURCE_PRINCIPAL Procedure

Enable resource principal credential for the database or for the specified schema. This procedure creates the credential OCI$RESOURCE_PRINCIPAL.

Syntax

DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(
   username         IN VARCHAR2,
   grant_option     IN BOOLEAN DEFAULT FALSE);

Parameter

Parameter	Description
username	Specifies an optional user name. The name of the database schema to be granted resource principal access. If you do not supply a username, the username is set to ADMIN.
grant_option	When username is supplied, if grant_option is TRUE the specified username can enable resource principal usage for other users.

Exceptions

Exception	Error	Description
resource principal is already enabled	ORA-20031	If you attempt to enable the resource principal when it is already enabled.

Usage Notes

• You must call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL with the ADMIN username or with no arguments before you call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL with a username for a database user schema.

• Resource principal is not available with refreshable clones.

• You must set up a dynamic group and policies for the dynamic group before you call DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL.

  See the following for more information on policies, creating a dynamic group, and creating rules:

  • Use Resource Principal to Access Oracle Cloud Infrastructure Resources

  • Managing Dynamic Groups

  • Managing Policies

Example

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

PL/SQL procedure successfully completed. 

SQL> select owner, credential_name from dba_credentials where credential_name = 'OCI$RESOURCE_PRINCIPAL'; 

OWNER    CREDENTIAL_NAME
-------  ---------------
ADMIN    OCI$RESOURCE_PRINCIPAL

FINISH_WORKLOAD_CAPTURE Procedure

This procedure finishes the current workload capture, stops any subsequent workload capture requests to the database, and uploads the capture files to Object Storage.

Example

BEGIN
    DBMS_CLOUD_ADMIN.FINISH_WORKLOAD_CAPTURE
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• When you pass the duration parameter to START_WORKLOAD_CAPTURE, the capture finishes when it reaches the specified time. However, if you call FINISH_WORKLOAD_CAPTURE, this stops the workload capture (possibly before the time specified with the duration parameter).

  You can query the DBA_CAPTURE_REPLAY_STATUS view to check the finish workload status. See DBA_CAPTURE_REPLAY_STATUS View for more information.

Note:

You must subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about the completion of FINISH_WORKLOAD_CAPTURE as well as the Object Storage link to download the capture file. This PAR URL is contained in the captureDownloadURL field of the event and is valid for 7 days from the date of generation. See Information Events on Autonomous Database for more information.

PREPARE_REPLAY Procedure

The PREPARE_REPLAY procedure prepares the refreshable clone for a replay.

Parameters

Parameter	Description
capture_name	Specifies the name of the workload capture. This parameter is mandatory.

Syntax

DBMS_CLOUD_ADMIN.PREPARE_REPLAY(
        capture_name IN VARCHAR2);

Example

BEGIN
    DBMS_CLOUD_ADMIN.PREPARE_REPLAY
      capture_name => 'cap_test1');
END;
/

This example prepares the refreshable clone to replay the workload indicated by the capture_name parameter, which involves bringing it up to the capture start time and then disconnecting it.

Usage Note

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

PURGE_FLASHBACK_ARCHIVE Procedure

This procedure enables ADMIN users to purge historical data from Flashback Data Archive. You can either purge all historical data from Flashback Data Archive flashback_archive or selective data based on timestamps or System Change Number.

Syntax

DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE( 
   scope      IN VARCHAR2, 
   before_scn IN INTEGER DEFAULT NULL, 
   before_ts  IN TIMESTAMP DEFAULT NULL); 

Parameter	Description
scope	This specifies the scope to remove data from the flashback data archive. all implies PURGE ALL;before_scn and before_timestamp must both be NULL. scn implies PURGE BEFORE SCN;before_scn must be non-NULL and before_timestamp must be NULL. TIMESTAMP implies PURGE BEFORE timestamp;before_scn must be NULL and before_timestamp must be non-NULL.
before_scn	This specifies the system change number before which all the data is removed from the flashback archive.
before_timestamp	This specifies the timestamp before which all the data is removed from the flashback archive.

Example

BEGIN
	DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE(
           scope => 'ALL'); // Purge all historical data from Flashback Data Archive flashback_archive
END;
/

REPLAY_WORKLOAD Procedure

This procedure initiates a workload replay on your Autonomous Database instance. The overloaded form enables you to replay the capture files from an Autonomous Database instance, on-premises database, or other cloud service databases.

Syntax

DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        capture_name                  IN VARCHAR2,        
        replay_name                   IN VARCHAR2 DEFAULT NULL,
        capture_source_tenancy_ocid   IN VARCHAR2 DEFAULT NULL,
        capture_source_db_name        IN VARCHAR2 DEFAULT NULL);

DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        location_uri      IN VARCHAR2,               
        credential_name   IN VARCHAR2 DEFAULT NULL,
        synchronization   IN BOOLEAN  DEFAULT TRUE,
        process_capture   IN BOOLEAN  DEFAULT TRUE);

Parameters

Parameter	Description
CAPTURE_NAME	Specifies the name of the workload capture. This parameter is mandatory.
REPLAY_NAME	Specifies the replay name. If you do not supply a REPLAY_NAME value, the REPLAY_NAME is auto-generated with the format REPLAY_RANDOMNUMBER, for example, REPLAY_1678329506.
CAPTURE_SOURCE_TENANCY_OCID	Specifies the source tenancy OCID of the workload capture. If you do not supply a CAPTURE_SOURCE_TENANCY_OCID value, the CAPTURE_SOURCE_TENANCY_OCID is set to NULL. This parameter is only mandatory when running the workload capture in a full clone.
CAPTURE_SOURCE_DB_NAME	Specifies the source database name of the workload capture If you do not supply a CAPTURE_SOURCE_DB_NAME value, the CAPTURE_SOURCE_DB_NAME is set to NULL. This parameter is only mandatory when running the workload capture in a full clone.
LOCATION_URI	Specifies URI that points to an Object Storage location that contains the captured files. This parameter is mandatory.
CREDENTIAL_NAME	Specifies the credential to access the object storage bucket. If you do not supply a credential_name value, the database's default credentials are used.
SYNCHRONIZATION	Specifies the synchronization method used during workload replay. TRUE specifies that the synchronization is based on SCN. FALSE specifies that the synchronization is based on TIME. If you do not supply a synchronization value, the synchronization is set to TRUE.
PROCESS_CAPTURE	Specifies whether or not you need to specify process_capture value. It can be set to FALSE only when you replay the same workload on the target database repeatedly. If you do not supply a process_capture value, the process_capture is set to TRUE.

Example to replay the workload from an on-premises database on an Autonomous Database instance:

BEGIN 
  DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
        credential_name => 'CRED_TEST',
        synchronization => TRUE,
        process_capture => TRUE);    
END;
/

When you run this example, it:

• Downloads the capture files from the Object Storage location specified in location_uri and processes the capture files based on the process_capture parameter value.

• Replays the captured workload based on the synchronization parameter value.

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

See Navigate to Oracle Cloud Infrastructure Object Storage and Create Bucket for more information on Object Storage.

See Upload Files to Your Oracle Cloud Infrastructure Object Store Bucket for more information on uploading files to Object Storage.

The credential_name you use in this step is the credentials for the Object Store.

You don't need to create a credential to access Oracle Cloud Infrastructure Object Store if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

Example to replay the workload from an Autonomous Database instance on another Autonomous Database:

BEGIN 
  DBMS_CLOUD_ADMIN.REPLAY_WORKLOAD(
        capture_name => 'CAP_TEST1');
END;
/

When you run this example, it:

• Disconnects the current Autonomous Database instance.

• Downloads the capture files from the Object Storage.

• Replays the captured workload.

• Uploads replay report after a replay.

Usage Notes for Replaying the Workload from an On-Premises or Other Cloud Service Database on another Autonomous Database

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• Before you start replay, you should upload the cap and capfiles subdirectories, which contain the workload capture files, to the object storage location.

Usage Notes for Replaying the Workload from an Autonomous Database instance on another Autonomous Database

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• The replay files are automatically uploaded to the Object Store as a zip file.

• You can query the DBA_CAPTURE_REPLAY_STATUS view to check the workload replay status.

  See DBA_CAPTURE_REPLAY_STATUS View for more information.

Note:

You must subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified about the start and completion of the REPLAY_WORKLOAD as well as the Object Storage link to download the replay reports. This PAR URL is contained in the replayDownloadURL field of the event and is valid for 7 days from the date of generation. See Information Events on Autonomous Database for more information.

SET_FLASHBACK_ARCHIVE_RETENTION Procedure

This procedure allows ADMIN users to modify the retention period for Flashback Data Archive flashback_archive.

Syntax

DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION (
    retention_days INTEGER);

Parameter	Description
retention_days	This specifies the length of time in days that the archived data should be retained for. The value of retention_days must be greater than 0.

Example

BEGIN
     DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION(
           retention_days => 90); // sets the retention time to 90 days
END;
/

START_WORKLOAD_CAPTURE Procedure

This procedure initiates a workload capture on your Autonomous Database instance.

Syntax

DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE(
        capture_name  IN VARCHAR2,
        duration      IN NUMBER   DEFAULT NULL);

Parameters

Parameter	Description
capture_name	Specifies the name of the workload capture. This parameter is mandatory.
duration	Specifies the duration in minutes for which you want to run the workload capture. If you do not supply a duration value, the duration is set to NULL. If set to NULL, the workload will continue until you run the FINISH_WORKLOAD_CAPTURE procedure.

Example

BEGIN 
  DBMS_CLOUD_ADMIN.START_WORKLOAD_CAPTURE(
        capture_name => 'test');
END;
/

Usage Notes

• To run this procedure you must be logged in as the ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.

• To measure the impacts of a system change on a workload, you must ensure that the capture and replay systems are in the same logical state.

• Before initiating a workload capture, you should consider provisioning a refreshable clone to ensure the same start point for the replay.

Note:

You must subscribe to the Information event com.oraclecloud.databaseservice.autonomous.database.information to be notified at the start of START_WORKLOAD_CAPTURE. See Information Events on Autonomous Database for more information.