DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from
an Autonomous Database to an Oracle Database Gateway
to access Non-Oracle databases.
An Oracle Database Gateway is a gateway that is designed for accessing a specific non-Oracle system. Using an Oracle Database Gateway, you can access data anywhere in a distributed database system without knowing either the location of the data or how it is stored. Using database links on Autonomous Database with Oracle Database Gateway supports heterogeneous environments and eliminates the need to customize your applications to access data from non-Oracle systems.
Autonomous Database supports creating database links only if the target gateway is accessible through a public IP or public hostname.
To use database links with Autonomous Database, the target gateway must be configured to use TCP/IP with SSL (TCPS) authentication. See Configuring Secure Sockets Layer Authentication for more information.
To ensure security when using database links with an Oracle Database Gateway,
the database link port is restricted to the range
1521-1525. You specify
the target gateway port when you create a database link with
Note:Before you create database links to a target gateway, do the following:
Configure the Oracle Database Gateway to access a non-Oracle database.
Configure Oracle Net Listener to handle incoming requests on the Oracle Database Gateway.
Create a self signed wallet on the Oracle Database Gateway.
For more information on Oracle Database Gateway, see Oracle Database Gateways in Oracle Database Heterogeneous Connectivity User's Guide. Also see the Installation and Configuration Guide and the Gateway User's Guide for the database you want to connect to. For example, for Oracle Database Gateway for SQL Server see:
Introduction to the Oracle Database Gateway for SQL Server in Oracle Database Gateway for SQL Server User's Guide.
To create database links to a target gateway, do the following:
- Copy the target gateway self signed wallet, for example,
cwallet.sso, containing the certificates for the Oracle Database Gateway to Object Store.
Note:The wallet file, along with the Database user ID and password provide access to data available through the target gateway. Store wallet files in a secure location. Share wallet files only with authorized users.
- Create credentials to access the Object Store where you store the
cwallet.sso. See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.
- Create a directory on Autonomous Database for
the wallet file
CREATE DIRECTORY dblink_wallet_dir AS 'directory_path_of_your_choice';
See Create Directory in Autonomous Database for information on creating directories.
DBMS_CLOUD.GET_OBJECTto upload the target gateway self signed wallet to the directory you created in the previous step, DBLINK_WALLET_DIR.
DBMS_CLOUD.GET_OBJECT( credential_name => 'DEF_CRED_NAME', object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso', directory_name => 'DBLINK_WALLET_DIR'); END; /
In this example,
namespace-stringis the Oracle Cloud Infrastructure object storage namespace and
bucketnameis the bucket name. See Understanding Object Storage Namespaces for more information.
credential_nameyou use in this step is the credentials for the Object Store. In the next step you create the credentials to access the target gateway.
- On Autonomous Database create credentials to
access the target database. The
passwordyou specify with
DBMS_CLOUD.CREATE_CREDENTIALare the credentials for the target database used within the database link, (where the target database is accessed through the Oracle Database Gateway).
credential_nameparameter is required.
DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'DB_LINK_CRED', username => 'NICK', password => 'password' ); END; /
The characters in the
usernameparameter must be all uppercase letters.
This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name.
- Create the database link to the target gateway using
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK( db_link_name => 'SALESLINK', hostname => 'example.com', port => '1522', service_name => 'example_service_name', ssl_server_cert_dn => 'ssl_server_cert_dn', credential_name => 'DB_LINK_CRED', directory_name => 'DBLINK_WALLET_DIR', gateway_link => TRUE); END; /
Users other than ADMIN require privileges to run
- Use the database link you created to access data on the target gateway.
SELECT * FROM employees@SALESLINK;
Note:For the credentials you create in Step 5, the Oracle Database Gateway credentials, if the password of the target user changes you can update the credential that contains the target user's credentials as follows:
DBMS_CLOUD.UPDATE_CREDENTIAL( credential_name => 'DB_LINK_CRED', attribute => 'PASSWORD', value => 'password'); END; /
Where password is the new password.
After this operation, the existing database links that use this credential continue to work without having to drop and recreate the database links.
See the following for an example that shows you how to create a database link to an Oracle Database Gateway to access a Microsoft SQL Server database:
For additional information, see: