Recovery Catalog

Recovery catalog allows to store database metadata in a dedicated database schema. It can have longer renteion for backup records than storing them in control file. If data guard is used a recovery catalog is needed to easily interchange backups between standby and primary. This post gives few highlights of using recovery catalog.

    Creating Recovery Catalog
    Registering Database in Recovery Catalog
    Sync Changes to Catalog
    Unregistering a Database From Recovery Catalog
    Dropping the Recovery Catalog

Creating Recovery Catalog
To be useful recovery catalog must reside external to the database registered within. Usually on a dedicated DB server and Oracle provides restricted special usage license to be used with databases used purely for recovery catalog.
It's adivsed to use a dedicated tablespace for recovery catalog schema user. In this post following tablespace is used.

SQL> create tablespace recocattbs datafile size 50m autoextend on next 50m maxsize unlimited;

Tablespace created.

The recovery catalog schema user could be created in a pdb or non-cdb.

create user recocat identified by CAT_#123reco default tablespace recocattbs temporary tablespace temp quota unlimited on recocattbs;

The recovery catalog user need following role to create the catalog.

GRANT RECOVERY_CATALOG_OWNER TO recocat;

However, this role alone isn't enough to prevent the following error.

RMAN> create catalog;

error creating dbms_rcvcat package body
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog

To resolve this add the following (refer 1086561.1)

grant SELECT ON USER_DB_LINKS to recocat;

If as part of DB security hardening (CIS standard) if certain execution rights were revoked from public then grant those privileges to recovery catalog user. Below is the required list for recovery catalog.

grant execute on utl_http to recocat;
grant execute on dbms_lob to recocat;
grant execute on dbms_xmlgen to recocat;
grant execute on dbms_sql to recocat;

With the role and privileges granted connect to the recovery catalog using rman and create the catalog.

rman catalog recocat/CAT_#123reco

RMAN> create catalog;

recovery catalog created

Registering Database in Recovery Catalog
To register a database in recovery catalog connect to the target database and the recovery catalog at the same time.

rman target / catalog recocat/CAT_#123reco@devpdb

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Aug 24 17:30:37 2020
Version 19.8.0.0.0

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

connected to target database: DEVCDB (DBID=2653754887)
connected to recovery catalog database

. Once connected run register database command. First time this is run an implicit resync happens. Depending on amount of records kept in controlfile, netwrok bandwidth between target DB and recovery catalog DB server this could take some time.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Once registered with recovery catalog all the database registered could be viewed with following.

RMAN> list db_unique_name all;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1       DEVCDB   2653754887       PRIMARY          DEVCDB
608     XGENLT   4291278017       PRIMARY          XGENLT

Sync Changes to Catalog
Whenever changes are made to DB while disconnected from recovery catalog, those changes are sync to recovery catalog with an implicit sync job. This happpens when next time target DB is connected to the recovery catalog and a command is run (not necessarily a sync comamnd). For exmaple the retention is changed while disconnected from recovery catalog.

rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Aug 24 17:32:49 2020
Version 19.8.0.0.0

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

connected to target database: DEVCDB (DBID=2653754887)


RMAN>  CONFIGURE RETENTION POLICY TO recovery window of  1 days;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
new RMAN configuration parameters are successfully stored

When next time connected to recvoery catalog an implicit resync happens when a command (this case show command) is run.

rman target / catalog recocat/CAT_#123reco@devpdb

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Aug 24 17:35:43 2020
Version 19.8.0.0.0

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

connected to target database: DEVCDB (DBID=2653754887)
connected to recovery catalog database

RMAN>  show all for db_unique_name devcdb;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name DEVCDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

If an explicity resync is needed then run the following.

RMAN> resync catalog;
starting resync of recovery catalog
resync complete

Unregistering a Database From Recovery Catalog
To unregister a database from recovery catalog invoke unregister command with the db_name or db_unique_name (in the case of standby instances)

RMAN> unregister database xgenlt;

database name is "XGENLT" and DBID is 4291278017

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN> list db_unique_name all;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1       DEVCDB   2653754887       PRIMARY          DEVCDB

Oracle documentation mentions an option to unregister with backups. But invoking this results in an error.

RMAN> unregister db_unique_name PPCBXDB5  including backups;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of unregister db & bck command at 09/03/2020 19:15:34
RMAN-06014: command not implemented yet: unregister db & bck

This is mentioned as bug 5308590.

Dropping the Recovery Catalog
To drop the catalog connect to target and recovery catalog as recovery catalog owner and run the drop catalog command twice.

 rman target / catalog recocat/CAT_#123reco@devpdb

RMAN> drop catalog;

recovery catalog owner is RECOCAT
enter DROP CATALOG command again to confirm catalog removal

RMAN>  drop catalog ;

recovery catalog dropped

UCP Error Even When Not Using Multi-Tenant Shared Pool Feature

Following errors were seen on CDB alert log where JDBC connections were made to a service associated with a PDB.

2020-10-15T12:18:50.823318+00:00
TESTPDB(3):WARNING: too many parse errors, count=100 SQL hash=0x6870fc2d
TESTPDB(3):PARSE ERROR: ospid=12456, error=904 for statement:
2020-10-15T12:18:50.823502+00:00
TESTPDB(3):select dbms_service_prvt.get_topology(:"SYS_B_0") from dual
TESTPDB(3):Additional information: hd=0x6fc6cb80 phd=0x7025df10 flg=0x100476 cisid=80 sid=80 ciuid=80 uid=80 sqlid=bwkv6d1n71z1d
TESTPDB(3):...Current username=ASANGA
TESTPDB(3):...Application: JDBC Thin Client Action:

As per 2489973.1 the reason for this issue is UCP making calls that are associated with Multi-Tenant Shared Pool feature even when the feature is not used.



One way to remedy is to grant the parsing user the execute privilege on dbms_service_prvt. But this means the un-necessary calls would still happen but error won't be raised.
However, there's patch to fix this on 12.2 28643583.
If patch 28643583 is already applied on UCP then use the merge patch 32004255.

JDBC Thin Connections to Autonomous Transaction Processing DB

There are two methods for connecting to ATP using a JDBC thin client. One is using Oracle wallet and the other is using java keystore. This post gives a summary of setting up JDBC thin connections using these methods. It is assume that the client credentials zip file is dowloaded from the ATP DB console.

Using Java KeyStore for JDBC Thin Connections

Using java keystore (JKS) is the simplest method for connecting a JDBC thin client to ATP. It requires no new libraries to be added to the classpath and if existing class conforms to using TNS entries then require no code changes either.
1. To use this method to connect to ATP DB following files are needed which are included in the client credentials zip file downloaded from the ATP DB console. 

•  truststore.jks
• keystore.jks
• ojdbc.properties
• tnsnames.ora
  

2. Modify the ojdbc.properties file to include JKS related entries (The original file in the client credentail zip will have additonal entries). This include specifying the locations of the two JKS files and their passwords. Password is the same password given when client credentials zip was downloaded from ATP DB console. Below is an example of ojdbc.properties file used in this method.

javax.net.ssl.trustStore=C:\\Asanga\\java\\atpjdbc\\Wallet_ATPFree\\truststore.jks
javax.net.ssl.trustStorePassword=<wallet password here>
javax.net.ssl.keyStore=C:\\Asanga\\java\\atpjdbc\\Wallet_ATPFree\\keystore.jks
javax.net.ssl.keyStorePassword=<wallet password here>

3. Next the location of the tnsnames.ora and ojdbc.properties files must be specified to the java app. This could be done by specifying the oracle.net.tns_admin system property, which could be passed on through the JVM options.

-Doracle.net.tns_admin=./Wallet_ATPFree

This would require no code changes. Other methods of specifying this location includes using the TNS_ADMIN in the JDBC URL (requies driver 18.3 or above) or using connection property OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN. In this example location "./Wallet_ATPFree" containes tnsnames.ora and ojdbc.properties files.

4. With these files and enties in place create a JDBC thin connection using UCP as below. In this case the tpurgent service is used in the URL.

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setConnectionPoolName("ATP_Pool");
        ds.setURL("jdbc:oracle:thin:@atpfree_tpurgent");
        ds.setUser("asanga");
        ds.setPassword("pwd_here");

Using Oracle Wallet for JDBC Thin Connections

1. Using this method requires following files and library jars. 

•  ewallet.p12 and cwallet.sso
• ojdbc.properties
• tnsnames.ora
• oraclepki.jar, osdt_cert.jar and osdt_core.jar (not included in client credential zip file)

2. The wallet file is doesn't contain any passwords.

mkstore -wrl . -listCredential
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)

However, it is possible to add a password to the wallet and use it for passwordless login. What is contains is the certificate which allows to make TCPS connections. These certificates could be listed with following (redacted output shown).

orapki wallet display -wallet . -complete
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=............................,DNQ=V1
Issuer:         C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Serial Number:  00
Key Length      2048
MD5 digest:     ....
SHA digest:     ....

Trusted Certificates:
Subject:        CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Issuer:         CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Serial Number:  ....
Key Length      2048
MD5 digest:     ...
SHA digest:     ...

Subject:        C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Issuer:         C=US,ST=California,L=Redwood Shores,O=Oracle Corporation Autonomous Data Warehouse Cloud Self-signed CA,CN=Autonomous Data Warehouse Cloud CA
Serial Number:  ...
Key Length      2048
MD5 digest:     ...
SHA digest:     ...

Subject:        CN=DigiCert SHA2 Secure Server CA,O=DigiCert Inc,C=US
Issuer:         CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Serial Number:  ...
Key Length      2048
MD5 digest:     ...
SHA digest:     ...

As by default no password contains in the wallet, it must be specified in the JDBC connection. Secondly the auto login wallte included in the downloaded wallet zip file is not a auto login local wallet. For added security a new auto login local wallet could be created. Password of the wallet is the same password given when client credentials zip was downloaded from ATP DB console

3. Modify the ojdbc.properties file to contain the following entry which specify the wallet file location.

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\\Asanga\\java\\atpjdbc\\Wallet_ATPFree))))

4. Similar to JKS method, specify the location of the tnsnames.ora and ojdbc.properties files. This could be done by specifying the oracle.net.tns_admin system property, which could be passed on through the JVM options.

-Doracle.net.tns_admin=./Wallet_ATPFree

This would require no code changes. Other methods of specifying this location includes using the TNS_ADMIN in the JDBC URL (requies driver 18.3 or above) or using connection property OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN. In this example location "./Wallet_ATPFree" containes tnsnames.ora and ojdbc.properties files.

5. Include the oraclepki.jar, osdt_cert.jar and osdt_core.jar files in the classpath of the java application.

6. With these files and entries in place same JDBC Connection code as shown above in JKS method could be used to make JDBC thin connections to the ATP DB. Example below shows password explicity being specified in the java code rathe than rely on password stoed in wallet.

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setConnectionPoolName("ATP_Pool");
        ds.setURL("jdbc:oracle:thin:@atpfree_tpurgent");
        ds.setUser("asanga");
        ds.setPassword("pwd_here");