Create Database Links to
an Oracle Database that is not an Autonomous Database
You
can create database links from an Autonomous Database to an Oracle database that is on a private endpoint or on a
public endpoint (publicly accessible).
Create Database Links from Autonomous Database to a Publicly Accessible Oracle
Database with a Wallet (mTLS)
🔗
You can
create database links from an Autonomous Database to a
target Oracle database that is on a public endpoint.
To use database links with Autonomous Database the target database must be configured to use TCP/IP with SSL (TCPS)
authentication. Autonomous Databases use TCP/IP
with SSL (TCPS) authentication by default, so you do not need to do any additional
configuration in your target database to link to another Autonomous Database. Other Oracle databases must be
configured to use TCP/IP with SSL (TCPS) authentication. See Configuring Secure Sockets Layer Authentication for
more information.
To create database links to a public target, the target Oracle Database must
be accessible. Some databases may limit access (for example, using Access Control Lists).
Make sure you enable your target database to allow access from your source database for the
database link to work. If you limit access with Access Control Lists (ACLs), you can find
the outbound IP address of your source Autonomous Database and allow that IP address to connect to your target database.
To create database links to a target Oracle database with a wallet (mTLS):
Copy your target database wallet, cwallet.sso, containing the
certificates for the target database to Object Store.
Note the following for the wallet file:
The wallet file, along with the Database user ID and password provide access to
data in the target Oracle Database. Store wallet files in a secure location. Share
wallet files only with authorized users.
Do not rename the wallet file. The wallet file in Object Storage must be named
cwallet.sso.
Create credentials to access your Object Store where you store the wallet file
cwallet.sso. See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different
object storage services.
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.
Note
The credential_name
you use in this step is the credentials for the Object Store. In the next step you
create the credentials to access the target database.
On the Autonomous Database instance, create
credentials to access the target database. The username and
password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target
database that you use to create the database link.
Note
Supplying the
credential_name parameter is required.
Users other than ADMIN require
privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
If the wallet file in the directory specified with directory_name is
not cwallet.sso, the procedure reports an error such as:
ORA-28759: failure to open file.
Use the database link you created to access data on the target database.
For example:
SELECT * FROM employees@SALESLINK;
For the credentials you create in Step 5, the target database credentials, if the password
of the target user changes you can update the credential that contains the target user's
credentials as follows:
BEGIN
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.
Oracle Database, such as on-premises or a Database Cloud Service
database, on a private endpoint that is configured for TCP: In this case you
create the database link without a wallet and the database link communicates with TCP.
See Create Database Links to Oracle Databases on a Private Endpoint without a Wallet for details
Prerequisites for Database Links
from Autonomous Database to Oracle Databases on a
Private Endpoint
🔗
Lists the
prerequisites to create database links from an Autonomous Database to a target Oracle database that is on a private endpoint.
To create a database link to a target Oracle database on a private
endpoint:
The target database must be accessible from the source database's Oracle Cloud
Infrastructure VCN. For example, you can connect to the target database when:
The target database is on a private endpoint.
Both the source database and the target database are in the same Oracle Cloud
Infrastructure VCN.
The source database and the target database are in different Oracle Cloud
Infrastructure VCNs that are paired.
The target database is an on-premises database that is connected to the
source database's Oracle Cloud
Infrastructure VCN using FastConnect or VPN.
There are two options to specify the target database, use the
hostname parameter or the rac_hostnames
parameter:
For a target on a private endpoint, DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
supports specifying a single hostname with the
hostname parameter. On a private endpoint,
using an IP address, SCAN IP, or a SCAN hostname is not supported
(when the target is on a public endpoint,
CREATE_DATABASE_LINK supports using an IP
address, a SCAN IP, or a SCAN hostname).
When the target is an Oracle RAC
database, use the rac_hostnames parameter to
specify one or more hostnames with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
This allows you to take advantage of the high availability
capabilities of Oracle RAC. Using an IP address, a SCAN IP, or a
SCAN hostname in the rac_hostnames value is not
supported.
When you specify a list of host
names in the rac_hostnames parameter,
CREATE_DATABASE_LINK uses all of the
specified host names as addresses in the connect string. If one of
the specified hosts is not available on the target Oracle RAC
database, Autonomous Database automatically attempts to connect
using another host name from the list.
The following ingress and egress rules must be defined for the private endpoint:
Define an egress rule in the source database's subnet security list or
network security group such that the traffic over TCP is allowed to the target
database's IP address and port number.
Define an ingress rule in the target database's subnet security list or
network security group such that the traffic over TCP is allowed from the source
database IP address to the destination port.
When your Autonomous Database instance is configured with a
private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to
'PRIVATE_ENDPOINT' to specify that all outgoing database links are
subject to the Autonomous Database instance
private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.
Create Database Links to Oracle
Databases on a Private Endpoint without a Wallet 🔗
Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to
create database links from an Autonomous Database to a
target Oracle database that is on a private endpoint and connect without a wallet
(TCP).
Note
This option is for target Oracle
databases that are on a private endpoint and do not have SSL/TCPS configured.
To create a database link to a target database on a private endpoint using a secure
TCP connection without a wallet:
On Autonomous Database create credentials to
access the target database. The username and password
you specify with DBMS_CLOUD.CREATE_CREDENTIAL
are the credentials for the target database used within the database link, (where the
target database is accessed through the VCN).
For a target on a private endpoint, DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
supports specifying a single hostname with the
hostname parameter. On a private endpoint,
using an IP address, SCAN IP, or a SCAN hostname is not supported
(when the target is on a public endpoint,
CREATE_DATABASE_LINK supports using an IP
address, a SCAN IP, or a SCAN hostname).
When the target is an Oracle RAC
database, use the rac_hostnames parameter to
specify one or more hostnames with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
This allows you to take advantage of the high availability
capabilities of Oracle RAC. Using an IP address, a SCAN IP, or a
SCAN hostname in the rac_hostnames value is not
supported.
For example, with a target Oracle RAC database use the
rac_hostnames parameter:
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK does not support a
value of localhost for the hostname or in the
rac_hostnames parameter.
Users other than ADMIN require
privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
As shown in the example, to create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
to a target database on a private endpoint using a secure TCP connection without a
wallet, all of the following are required:
Use the database link you created to access data in the target database.
For example:
SELECT * FROM employees@PRIVATE_ENDPOINT_LINK;
Note
For the credentials you create in
Step 1, the Oracle Database credentials, if the password of the target user changes you can
update the credential that contains the target user's credentials as
follows:
BEGIN
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.
To create a database link to a target Oracle database on a private endpoint using
TCP/IP with SSL (TCPS) authentication:
Copy your target database wallet, cwallet.sso, containing the
certificates for the target database to Object Store.
Note
The wallet file, along with the Database user ID and password provide access to data
in the target Oracle database. Store wallet files in a secure location. Share wallet
files only with authorized users.
Create credentials to access your 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 cwallet.sso.
For example:
CREATE DIRECTORY wallet_dir AS 'directory_path_of_your_choice';
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.
Note
The credential_name
you use in this step is the credentials for the Object Store. In the next step you
create the credentials to access the target database.
On Autonomous Database create credentials to
access the target database. The username and password
you specify with DBMS_CLOUD.CREATE_CREDENTIAL
are the credentials for the target database used within the database link, (where the
target database is accessed through the VCN).
Note
Supplying the
credential_name parameter is required.
For a target on a private endpoint, DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
supports specifying a single hostname with the
hostname parameter. On a private endpoint,
using an IP address, SCAN IP, or a SCAN hostname is not supported
(when the target is on a public endpoint,
CREATE_DATABASE_LINK supports using an IP
address, a SCAN IP, or a SCAN hostname).
When the target is an Oracle RAC
database, use the rac_hostnames parameter to
specify one or more hostnames with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
This allows you to take advantage of the high availability
capabilities of Oracle RAC. Using an IP address, a SCAN IP, or a
SCAN hostname in the rac_hostnames value is not
supported.
For example, with a target Oracle RAC database use the
rac_hostnames parameter:
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK does not support a
value of localhost for the hostname or in the
rac_hostnames parameter.
Users other than ADMIN require
privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
Use the database link you created to access data in the target database.
For example:
SELECT * FROM employees@PEDBLINK1;
Note
For the credentials you create in
Step 5, the Oracle Database credentials, if the password of the target user changes you can
update the credential that contains the target user's credentials as
follows:
BEGIN
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.
Database Link Notes with a Target
Oracle Database 🔗
Provides notes for creating database links to a target Oracle database
(when the target is not an Autonomous Database)
Notes for database links to other Oracle databases:
If you are using database links between Autonomous Database and other Oracle Databases, you might need to
apply Patch 33843368 on the Oracle Database that is not an Autonomous Database. This applies to
cases where the Autonomous Database
instance is either the source or the target of the database link.
Only one wallet file is valid per directory for use with database
links. You can only upload one cwallet.sso at a time to the
directory you choose for wallet files (for example DBLINK_WALLET_DIR).
This means with a cwallet.sso in DBLINK_WALLET_DIR
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. When you create
database links with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK, specify
the directory that contains the wallet with the directory_name
parameter.
Autonomous Database sets the
SEC_CASE_SENSITIVE_LOGON parameter to true
and this value cannot be changed. If your target database is not an Autonomous Database, then you must set
SEC_CASE_SENSITIVE_LOGON parameter to
true on the target database. If
SEC_CASE_SENSITIVE_LOGON is set to false
on the target database, then error ORA-28040: No matching
authentication protocol is raised.
To list the database links, use the ALL_DB_LINKS
view. See ALL_DB_LINKS for more
information.
The wallet file, along with the Database user ID and password provide access to
data in the target Oracle database. Store wallet files in a secure location.
Share wallet files only with authorized users.
When the Autonomous Database instance is on a private endpoint, there are two options to
specify the target database: use either the hostname parameter
or the rac_hostnames parameter:
For a target on a private endpoint, DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
supports specifying a single hostname with the
hostname parameter. On a private endpoint,
using an IP address, SCAN IP, or a SCAN hostname is not supported
(when the target is on a public endpoint,
CREATE_DATABASE_LINK supports using an IP
address, a SCAN IP, or a SCAN hostname).
When the target is an Oracle RAC
database, use the rac_hostnames parameter to
specify one or more hostnames with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
This allows you to take advantage of the high availability
capabilities of Oracle RAC. Using an IP address, a SCAN IP, or a
SCAN hostname in the rac_hostnames value is not
supported.
When you specify a list of host
names in the rac_hostnames parameter,
CREATE_DATABASE_LINK uses all of the
specified host names as addresses in the connect string. If one of
the specified hosts is not available on the target Oracle RAC
database, Autonomous Database automatically attempts to connect
using another host name from the list.
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK
does not support a value of localhost for the
hostname or in the rac_hostnames
parameter.