Summary of DBMS_CLOUD_ADMIN Subprograms
This section covers the DBMS_CLOUD_ADMIN
subprograms provided with Autonomous Database.
Subprogram | Description |
---|---|
This procedure creates a database link to a target database in the schema calling the API. You first need to upload the wallet ( |
|
This procedure disables database application continuity for the session associated with the specified service name in Autonomous Database. |
|
This procedure disables external authentication for the Autonomous Database instance. |
|
This procedure revokes principal based authentication for the specified provider and applies to the ADMIN user or to the specified user. |
|
This procedure disables resource principal credential and creates the credential |
|
This procedure drops a database link. |
|
This procedure enables database application continuity for the session associated with the specified service name in Autonomous Database. |
|
This procedure enables a user to create AWS ARN credentials in Autonomous Database. |
|
This procedure enables a user to logon to Autonomous Database using the specified external authentication scheme. |
|
This procedure enables the specified feature on the Autonomous Database instance. |
|
This procedure enables principal authentication for the specified provider and applies to the ADMIN user or the specified user. |
|
This procedure enables resource principal credential and creates the credential |
|
REPLAY_WORKLOAD Procedure | This procedure is overloaded. It initiates the workload replay. |
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,
credential_name IN VARCHAR2,
directory_name IN VARCHAR2 DEFAULT,
gateway_link IN BOOLEAN DEFAULT,
public_link IN BOOLEAN DEFAULT,
private_target IN BOOLEAN DEFAULT);
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,
credential_name IN VARCHAR2,
directory_name IN VARCHAR2 DEFAULT,
gateway_params IN CLOB DEFAULT);
Parameters
Parameter | Description |
---|---|
|
The name of the database link to create. |
|
The hostname for the target database. When you specify a connection with Oracle-managed heterogeneous
connectivity by supplying
the
|
port |
The port for the connections to the target database. To ensure security, when the connection is to an Oracle Database, ports are
restricted to: When you specify a connection with Oracle-managed heterogeneous
connectivity using the
See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for more information. |
|
The
When you specify a connection with Oracle-managed heterogeneous
connectivity using the |
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 T o connect to an Oracle Database on a private endpoint without a wallet,
specify a
|
credential_name |
The name of a stored credential created with |
directory_name |
The directory for the stored Oracle-managed heterogeneous
connectivity is preconfigured with a wallet that
contains most of the common trusted root and intermediate SSL
certificates. The T o connect to an Oracle Database with TCP, specify a
|
gateway_link |
Indicates if the database link is created to another Oracle Database or to an Oracle Database Gateway. If If The default value for this parameter is
|
public_link |
Indicates if the database link is created as a public database link. To run The default value for this parameter is
|
private_target |
When a database link accesses a hostname that needs to be resolved in a VCN
DNS server, specify the When The default value for this parameter is
|
|
Specify the target database type for Oracle-managed heterogeneous
connectivity to connect to non-Oracle databases. The
db_type value must be one of:
Specify the parameter with the
gateway_params => json_object('db_type'
value 'AWSREDSHIFT') See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for required |
Usage Notes
-
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:
- 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 grantEXECUTE
andCREATE DATABASE LINK
privileges to that user. For example, run the following command as ADMIN to grant privileges toadb_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 onecwallet.sso
at a time to the directory you choose for wallet files. This means with acwallet.sso
in a directory, you can only create database links to the databases for which the wallet in that directory is valid. To use multiplecwallet.sso
files with database links you need to create additional directories and put eachcwallet.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
toFALSE
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 isTRUE
, thehostname
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;
/
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 |
---|---|
|
The To find service names:
|
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_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 |
---|---|
|
Specifies the type of provider. Valid values are:
|
|
Specifier the user to disable principal based authentication for. A null value is valid for the
|
Usage Notes
-
When the provider value is
AZURE
and theusername
isADMIN
, 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 theusername
is a user other than theADMIN
user, the procedure revokes the privileges from the specified user. TheADMIN
user and other users that are enabled to use the Azure service principal can continue to useADMIN.AZURE$PA
and the application that is created for the Autonomous Database instance remains on the instance.
Example
BEGIN
DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH
(
provider => 'AZURE',
username => 'SCOTT');
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 |
---|---|
|
Specifies an optional user name. The name of the database schema to remove resource principal access. If you do not supply a |
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:
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 |
---|---|
|
The name of the database link to drop. |
|
To run The default value for this parameter is
|
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 thedata_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 |
---|---|
|
The To find service names:
|
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 |
---|---|
|
Name of the user to enable to use Amazon Resource Names (ARNs). A null value is valid for the |
|
When |
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 |
---|---|
|
Specifies the external authentication type. Valid values: or .
|
|
(Optional) Override a currently enabled external
authentication scheme. Valid values are The default value is |
params |
A JSON string that provides additional parameters for the external authentication.
|
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 withDBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL
. -
This procedure sets the system parameters
IDENTITY_PROVIDER_TYPE
andIDENTITY_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);
Parameters
Parameter | Description |
---|---|
|
Name of the feature to enable. The
only supported value is:
|
Example
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_FEATURE
(
feature_name => 'JAVAVM' );
END;
/
Usage Note
-
After you run
DBMS_CLOUD_ADMIN.ENABLE_FEATURE
withfeature_name
value'JAVAVM'
, you must restart the Autonomous Database instance to install Oracle Java.
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 |
---|---|
|
Specifies the type of provider. Valid values: |
|
Name of the user who has principal authentication usage enabled. A null value is valid for the
|
|
Specifies the configuration parameters. When the When the
|
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 afterDBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH
is called with theprovider
parameterAZURE
and theusername
ADMIN
.
Example
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH
(
provider => 'AZURE',
username => 'SCOTT',
params => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
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 |
---|---|
|
Specifies an optional user name. The name of the database schema to be granted resource principal access. If you do not supply a |
|
When |
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 ADMINusername
or with no arguments before you callDBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL
with ausername
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: for more information on creating a dynamic group and creating rules.
See for more information on 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
REPLAY_WORKLOAD Procedure
This procedure enables the ADMIN user to initiate a workload replay. This procedure allows you to replay the capture files from an on-premises database or other cloud service databases.
Syntax
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 |
---|---|
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 |
synchronization |
Specifies the synchronization method used during workload replay.
If you do not supply a |
process_capture |
Specifies whether you need to specify process_capture value or not. It can be set to FALSE only when you replay the same workload on the target database repeatedly.
If you do not supply a |
Example to replay the workload from an on-premises or other cloud service database into an Autonomous Database:
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 procedure, it:
-
Downloads the capture files from the Object Storage location specified in
location_uri
, processes the capture files based on theprocess_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.
Usage Notes
-
Run this procedure as the ADMIN user.
-
Before you start replay, you should upload the
cap
andcapfiles
subdirectories containing capture files that are created during capture to the Object Storage location.