In this post I would like to share how I have been able to troubleshoot and solve a catalog import issue using RMAN debug function.
As we can see, the error message provided by RMAN is not very helpful.
oracle@vmtestoradg1:/home/oracle/ [RCAT12C] rman catalog rcat/manager Recovery Manager: Release 126.96.36.199.0 connected to recovery catalog database RMAN> import catalog rcat/manager@RCAT11G; Starting import catalog at 05-JAN-2018 14:11:45 connected to source recovery catalog database RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of import catalog command at 01/05/2018 14:11:46 RMAN-06004: ORACLE error from recovery catalog database: ORA-00933: SQL command not properly ended RMAN>
Instead of looking for possible more information in appropriate logs, we can easily use the RMAN debug function.
RMAN> debug on;
By running the import command again we will be able to extract below useful information on the command RMAN is complaining about.
DBGSQL: CREATE DATABASE LINK DBGSQL: RCAT11G.IT.DBI-SERVICES.COM DBGSQL: CONNECT DBGSQL: TO DBGSQL: "RCAT" DBGSQL: IDENTIFIED BY DBGSQL: "manager" DBGSQL: USING DBGSQL: 'RCAT11G' DBGSQL: DBGSQL: sqlcode = 933 DBGSQL: error: ORA-00933: SQL command not properly ended (krmkosqlerr)
Let’s run the command into a sqlplus session to understand the failure.
SQL> conn rcat/manager Connected. SQL> CREATE DATABASE LINK RCAT11G.IT.DBI-SERVICES.COM CONNECT TO "RCAT" IDENTIFIED BY "manager" USING 'RCAT11G'; 2 3 CREATE DATABASE LINK RCAT11G.IT.DBI-SERVICES.COM * ERROR at line 1: ORA-00933: SQL command not properly ended
We can quickly understand that the ‘-‘ character is not appropriately used for domain name.
Let’s temporarily update needed parameters. An instance restart will be needed.
SQL> alter system set db_domain='dbiservices' scope=spfile; System altered. SQL> alter system set service_names='dbiservices' scope=both; System altered. SQL> alter database rename GLOBAL_NAME to "RCAT11G.dbiservices"; Database altered.
And our next import will be successful.
RMAN> import catalog rcat/manager@RCAT11G; Starting import catalog at 05-JAN-2018 15:21:48 connected to source recovery catalog database import validation complete database unregistered from the source recovery catalog Finished import catalog at 05-JAN-2018 15:21:52 RMAN>
We will be able to restore DB_DOMAIN, SERVICE_NAMES and GLOBAL_NAME to previous values, followed by a catalog instance restart.