Infrastructure at your Service

David Hueber

Oracle 12c duplicate in cluster fails with ORA-65500

I had to perform a duplicate database between 2 ODAs and was always facing an ORA-65500 at the begin of the RMAN operation. Looking a bit deeper in the log outputs pointed me out that it was due to some changes in the way RMAN makes the duplicate in 12c and cluster resources management.

Basically up to 12c when RMAN was doing a duplicate, it was changing the auxiliary spfile to set the DB_NAME at the value of the target database. This is required as after restoring a copy of the control file, RMAN needs to MOUNT the database. If the DB_NAME does not match between the spfile and the control files it won’t be possible to MOUNT the database. At the end of the duplicate RMAN changes the DB_NAME back in the spfile and uses NID principle to change it in the control files.

In version 12c now RMAN doesn’t change only the DB_NAME, but also the DB_UNIQUE_NAME and here is the trick!

While working in a cluster environment (Oracle Restart, RAC One Node, RAC, ODA…) the database is defined as a cluster resource based on its DB_UNIQUE_NAME

[root@dbioda1 bin]# ./crsctl stat res ora.dbtest_site1.db -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.dbtest_site1.db
      1        ONLINE  ONLINE       dbioda1              Open,STABLE
--------------------------------------------------------------------------------

 

At the beginning of the duplicate process RMAN runs following SQL:

sql statement: alter system set  db_name =  ''DBPROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DBTEST_SITE1'' comment= ''Modified by RMAN duplicate'' scope=spfile

 

Unfortunately changing the DB_UNIQUE_NAME is NOT allowed on a database defined as a cluster resource and that’s why we get the following error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/03/2015 10:11:00
RMAN-04014: startup failed: ORA-00304: requested INSTANCE_NUMBER is busy
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 09/03/2015 10:10:36
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'DBTEST_SITE1' comment= 'Modified by RMAN duplicate' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

 

The most frustrating is that after this failure, you won’t be able to start anymore your auxiliary database using your spfile. The only solution is to generate it again from a pfile

Connected to an idle instance.

SQL> startup nomount
ORA-00304: requested INSTANCE_NUMBER is busy

SQL> create spfile='/u02/app/oracle/oradata/datastore/.ACFS/snaps/DBTEST/DBTEST_SITE1/spfileDBTEST.ora' from pfile='/home/oracle/initDBTEST.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
Fixed Size		    3731384 bytes
Variable Size		 2550136904 bytes
Database Buffers	 1.4563E+10 bytes
Redo Buffers		   63377408 bytes

 

Indeed you can even validate this behaviour after setting your database in NOMOUNT and trying manually to change the DB_UNIQUE_NAME.

Therefore I tried some workarounds:

  1. Stopping the database using srvctl before setting it in NOMOUNT and changing the DB_UNIQUE_NAME
  2. Disabling the database in the cluster before setting it in NOMOUNT and changing the DB_UNIQUE_NAME

Of course the solution 1 doesn’t work as after starting the database in NOMOUNT, cluster resource is up again

oracle@dbioda1:/home/oracle/ [DBTEST] srvctl status database -db DBTEST_SITE1
Instance DBTEST is running on node dbioda1
oracle@dbioda1:/home/oracle/ [DBTEST] srvctl stop database -db DBTEST_SITE1
oracle@dbioda1:/home/oracle/ [DBTEST] sqh
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 3 10:25:46 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
...

SQL> alter system set db_unique_name='TOTO' scope=spfile;
alter system set db_unique_name='TOTO' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

SQL> exit

oracle@dbioda1:/home/oracle/ [DBTEST] srvctl status database -db DBTEST_SITE1
Instance DBTEST is running on node dbioda1

 

I was a bit more confident in the workaround 2 by disabling the resource in the cluster before doing the changes. Unfortunately we still face the same issue:

oracle@dbioda1:/home/oracle/ [DBTEST] srvctl status database -db DBTEST_SITE1
Instance DBTEST is running on node dbioda1
oracle@dbioda1:/home/oracle/ [DBTEST] srvctl stop database -db DBTEST_SITE1
oracle@dbioda1:/home/oracle/ [DBTEST] srvctl disable database -db DBTEST_SITE1
oracle@dbioda1:/home/oracle/ [DBTEST] sqh
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 3 10:32:50 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
...
SQL> alter system set db_unique_name='TOTO' scope=spfile;
alter system set db_unique_name='TOTO' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

SQL> exit

oracle@dbioda1:/home/oracle/ [DBTEST] srvctl status database -db DBTEST_SITE1
Instance DBTEST is not running on node dbioda1

As we can see  after setting the database in NOMOUNT, the cluster resource remains stopped as it is disabled. But even so we are not allowed to change the DB_UNIQUE_NAME

Thefore the only solution I could found so far is first to remove the resource from the cluster, run the duplicate and then add the resource back in the cluster with a command like:

srvctl add database -db DBTEST_SITE1 -oraclehome $ORACLE_HOME -dbtype SINGLE -role PRIMARY -spfile /u02/app/oracle/oradata/datastore/.ACFS/snaps/DBTEST/DBTEST_SITE1/spfileDBTEST.ora -pwfile /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwDBTEST -dbname DBTEST -startoption mount -stopoption immediate -instance DBTEST -node dbioda1 -acfspath "/u01/app/oracle/oradata/datastore,/u02/app/oracle/oradata/datastore,/u01/app/oracle/fast_recovery_area/datastore"

 

As a conclusion, we can say the following:

  •  Oracle 11g database duplicate in cluster or ODA environments works straight forward
  • Oracle 12c database duplicate in cluster or ODA environmentsrequries first to remove the database resource from the cluster and to add it back afterwards.

 

Hope it helps! :-D

 

 

 

2 Comments

Leave a Reply


− 7 = one

David Hueber
David Hueber

Chief Executive Officer (CEO), Principal Consultant