Thursday, August 18, 2022

Database Links using Autonomous Database with private endpoints across regions

I am using Autonomous Databases 19c shared infrastructure(ADB-S) with the source in Phoenix and the target in London.

Prereqs include:

  • Two ADB-S with private endpoints , one in each region
  • Region peering configured
  • DNS configured -- I like to create a compute VM in each region and verify region peering and DNS is working
Once the above is completed follow the next steps to create the db links.

  • Upload the target database wallet to object storage
  • Create the directory in the database to hold the wallet in the source database
  • Create the credential to access object storage in the source database
  • Copy the wallet from object storage to the source database
  • Create another credential to access target database in the source database
  • Create the database link
References:

Example:
Run the following commands  on the source database:

create directory wallet_dir_london as 'WALLETDIR_LONDON';


begin

    DBMS_CLOUD.create_credential (

    credential_name => 'OBJ_STORE_CREDA',

    username => 'YOUROCIUSERNAME',

    password => 'YOURTOKEN

  ) ;

end;

/


begin DBMS_CLOUD.GET_OBJECT(

      credential_name => 'OBJ_STORE_CREDA',

      object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/YOURTENANCY/b/gfswinglondon/o/cwallet.sso',

      directory_name => 'WALLET_DIR_LONDON'); 

      END;

 /  


SELECT * FROM DBMS_CLOUD.LIST_FILES('WALLET_DIR_LONDON');


BEGIN

   DBMS_CLOUD.CREATE_CREDENTIAL(

   credential_name => 'DB_LINK_CRED',

   username => 'ADMIN',

   password => 'YOURADBPASSWORD');

END;

/


SELECT owner, credential_name FROM dba_credentials


BEGIN

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(

    db_link_name => 'LONDONDBLINK', 

    hostname => '*********.adb.uk-london-1.oraclecloud.com',    (your hostname here)

    port => '1522',

   service_name => '**************_gfswinglon_high.adb.oraclecloud.com',   (your service name here)

   ssl_server_cert_dn => 'CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US',

   credential_name => 'DB_LINK_CRED',

   directory_name => 'WALLET_DIR_LONDON',

   private_target => TRUE);

END;

/


select ora_database_name@londondblink from dual;

Thursday, August 11, 2022

Using ADB wallet to hide your sqlplus and sqlldr password

 Using the following:

  • Autonomous Database (ADB) 19c
  • Oracle database client 21c on an OCI VM running OL 8
Here are the steps:

  • Download the ADB wallet from the OCI console and unzip to a empty directory
  • Modify the sqlnet.ora file
    • Add the following line “SQLNET.WALLET_OVERRIDE=TRUE”
    • Modify the WALLET_LOCATION line, change DIRECTORY="?/network/admin” to point to your new unzip directory. ,  i.e.   DIRECTORY="/home/oracle/Wallet_gfships”
  • create a credential to the Wallet for your db user.    
    • mkstore -wrl /home/oracle/Wallet_gfships -createCredential gfships_high admin  youradminpassword          (Or use your db user account)    this command will ask for the wallet password which is the same password you used to download the wallet from the OCI console.
  • set TNS_ADMIN to this wallet directory
  • verify connectivity with tnsping
Below are examples using SQLPLUS and SQLLDR with and without the password

SQLPLUS Example:

[oracle@linux-jump-server ~]$ sqlplus admin/yourpassword@gfships_high

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 1 02:23:36 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 01 2022 02:21:59 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
[oracle@linux-jump-server ~]$ sqlplus /@gfships_high

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 1 02:23:51 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 01 2022 02:23:37 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0

SQL> show user
USER is "ADMIN"
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
[oracle@linux-jump-server ~]$


SQLLDR Example:

[oracle@linux-jump-server ~]$ sqlldr admin/yourpassword@gfships_high control=emp.ctl

SQL*Loader: Release 21.0.0.0.0 - Production on Mon Aug 1 02:29:02 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 6

Table EMPLOYEE:
  6 Rows successfully loaded.

Check the log file:
  emp.log
for more information about the load.
[oracle@linux-jump-server ~]$ sqlldr /@gfships_high control=emp.ctl

SQL*Loader: Release 21.0.0.0.0 - Production on Mon Aug 1 02:29:24 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 6

Table EMPLOYEE:
  6 Rows successfully loaded.

Check the log file:
  emp.log
for more information about the load.
[oracle@linux-jump-server ~]$ cat emp.ctl
load data
 infile '/home/oracle/emp.txt'
 append into table employee
 fields terminated by ","
 ( id, name, dept, salary )
[oracle@linux-jump-server ~]$