Looking for something? Try here..

Wednesday, February 19, 2020

RMAN Duplicate errors with RMAN-11003, ORA-32001

Here is a short error and trouble shoot performed while performing the RMAN duplicate.

Database version: 12.2.0.1

Error encountered:
..
..
..

executing command: SET NEWNAME

Starting Duplicate Db at 18-FEB-20
current log archived at primary database

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area  178241142784 bytes

Fixed Size                    19249424 bytes
Variable Size             124017182448 bytes
Database Buffers           53687091200 bytes
Redo Buffers                 517619712 bytes
allocated channel: a1
channel a1: SID=1940 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''BRP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''BRT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+DATA/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+RECO/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''BRP'' comment= ''Modified by RMAN duplicate'' scope=spfile
released channel: t1
released channel: t2
released channel: t3
released channel: t4
released channel: t5
released channel: t6
released channel: t7
released channel: t8
released channel: t9
released channel: t10
released channel: t11
released channel: t12
released channel: t13
released channel: t14
released channel: t15
released channel: t16
released channel: a1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/18/2020 09:17:41
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 02/18/2020 09:17:41
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_name =  'BRP' comment= 'Modified by RMAN duplicate' scope=spfile
ORA-32001: write to SPFILE requested but no SPFILE is in use

RMAN> **end-of-file**

RMAN> exit

You can see that even though RMAN internal script created spfile using memory as in the highlighted line above, it failed with ORA-32001.

Cause:

The environment uses ASM as storage and the previous database was dropped prior to the RMAN duplicate attempt. So when the create spfile is executed, the spfile created by default inside the ASM storage whereas RMAN expects the spfile to be in the default directory i.e $ORACLE_HOME/dbs

Solution:

As the database is dropped, we can now remove the service from the CRS using below command as shown
-sh-4.2$ srvctl stop database -d BRT
-sh-4.2$ srvctl remove database -d BRT
Remove the database BRT? (y/[n]) y


This would also remove the entry from /etc/oratab if you are using and has to be added manually again if you tend to use . oraenv to set up your oracle environment before starting sqlplus.

Once the service is removed and the same duplicate command is executed, RMAN continues without issues and succeeds.

..
..
..
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area  178241142784 bytes

Fixed Size                    19249424 bytes
Variable Size             124017182448 bytes
Database Buffers           53687091200 bytes
Redo Buffers                 517619712 bytes
allocated channel: a1
channel a1: SID=1826 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''BRP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''BRT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+DATA/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   restore clone primary controlfile to  '+RECO/BRT/cntrlbrt.dbf' from
 '+ARCH/BRT/cntrlbrt.dbf';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area  178241142784 bytes

Fixed Size                    19249424 bytes
Variable Size             124017182448 bytes
Database Buffers           53687091200 bytes
Redo Buffers                 517619712 bytes
allocated channel: a1
channel a1: SID=1826 device type=DISK

Starting backup at 18-FEB-20
channel t1: starting datafile copy
copying current control file
output file name=/oracle/BRP/122/dbs/snapcf_BRP.f tag=TAG20200218T093756
channel t1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-FEB-20

Starting restore at 18-FEB-20

channel a1: copied control file copy
..
..
..
Once the duplicate is completed, you can add the service back to the CRS.

Happy Troubleshooting!!!