Forum Stats

  • 3,825,745 Users
  • 2,260,557 Discussions
  • 7,896,658 Comments

Discussions

RMAN backup and cloning database noarchivelog

Alex2068
Alex2068 Member Posts: 14 Blue Ribbon
edited Jul 23, 2020 4:16AM in Recovery Manager (RMAN)

I have this 19c database, SOUR1CDB. In mounted state I did take a backup and left it as is (mounted).

I then created new instance, called CLONE, then created pfile, then created spfile. All in all I started this new instance in nomount state.

Then connected to target (SOUR1CDB) and auxiliary (CLONE):

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 22 15:53:13 2020Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.connected to target database: SOUR1CDB (DBID=3153670755, not open)connected to auxiliary database: CLONE (not mounted)RMAN> run {2>        set newname for datafile 1 TO3> 'D:\ORADATA\CLONE\SYSTEM01.DBF';4>        set newname for datafile 2 TO5> 'D:\ORADATA\CLONE\PDBSEED\SYSTEM01.DBF';6>        set newname for datafile 3 TO7> 'D:\ORADATA\CLONE\SYSAUX01.DBF';8>        set newname for datafile 4 TO9> 'D:\ORADATA\CLONE\PDBSEED\SYSAUX01.DBF';10>        set newname for datafile 5 TO11> 'D:\ORADATA\CLONE\UNDOTBS01.DBF';12>        set newname for datafile 6 TO13> 'D:\ORADATA\CLONE\PDBSEED\UNDOTBS01.DBF';14>        set newname for datafile 7 TO15> 'D:\ORADATA\CLONE\USERS01.DBF';16>        set newname for datafile 8 TO17> 'D:\ORADATA\CLONE\PDB1\SYSTEM01.DBF';18>        set newname for datafile 9 TO19> 'D:\ORADATA\CLONE\PDB1\SYSAUX01.DBF';20>        set newname for datafile 11 TO21> 'D:\ORADATA\CLONE\PDB1\USERS01.DBF';22>        set newname for datafile 12 TO23> 'D:\ORADATA\CLONE\PDB1\REPORT_INDEX01.DBF';24>        set newname for datafile 13 TO25> 'D:\ORADATA\CLONE\PDB1\REPORT_DATA01.DBF';26>        set newname for datafile 14 TO27> 'D:\ORADATA\CLONE\PDB1\LOB01.DBF';28>        set newname for datafile 15 TO29> 'D:\ORADATA\CLONE\PDB1\INDEX01.DBF';30>        set newname for datafile 16 TO31> 'D:\ORADATA\CLONE\PDB1\DATA01.DBF';32>        set newname for datafile 17 TO33> 'D:\ORADATA\CLONE\PDB1\ARCHIVE_INDEX01.DBF';34>        set newname for datafile 18 TO35> 'D:\ORADATA\CLONE\PDB1\ARCHIVE_DATA01.DBF';36>        set newname for datafile 20 TO37> 'D:\ORADATA\CLONE\PDB1\UNDOTBS01.DBF';38>        set newname for tempfile 1 TO 'D:\ORADATA\CLONE\TEMP01.DBF';39>        set newname for tempfile 2 TO 'D:\ORADATA\CLONE\PDBSEED\TEMP01.DBF';40>        set newname for tempfile 3 TO 'D:\ORADATA\CLONE\PDB1\TEMP01.DBF';41> duplicate target database to CLONE42>     logfile43> 'D:\oradata\CLONE\redo010.log' SIZE 100M,44> 'D:\oradata\CLONE\redo020.log' SIZE 100M,45> 'D:\oradata\CLONE\redo030.log' SIZE 100M,46> 'D:\oradata\CLONE\redo040.log' SIZE 100M,47> 'D:\oradata\CLONE\redo050.log' SIZE 100M,48> 'D:\oradata\CLONE\redo060.log' SIZE 100M;49> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting Duplicate Db at 22-JUL-20using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=311 device type=DISKcontents of Memory Script:{   sql clone "alter system set  db_name = ''SOUR1CDB'' comment= ''Modified by RMAN duplicate'' scope=spfile";   sql clone "alter system set  db_unique_name = ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile";   shutdown clone immediate;   startup clone force nomount   restore clone primary controlfile;   alter clone database mount;}executing Memory Scriptsql statement: alter system set  db_name =  ''SOUR1CDB'' comment= ''Modified by RMAN duplicate'' scope=spfilesql statement: alter system set  db_unique_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfileOracle instance shut downOracle instance startedTotal System Global Area    3238002128 bytesFixed Size                     9272784 bytesVariable Size               2147483648 bytesDatabase Buffers            1073741824 bytesRedo Buffers                   7503872 bytesStarting restore at 22-JUL-20allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=465 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece E:\TEMP\BACKUP04V59U2N_1_1channel ORA_AUX_DISK_1: piece handle=E:\TEMP\BACKUP04V59U2N_1_1 tag=FOR DUPLICATIONchannel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02output file name=D:\ORADATA\CLONE\CONTROL01.CTLFinished restore at 22-JUL-20database mountedcontents of Memory Script:{   set newname for datafile  1 to "D:\ORADATA\CLONE\SYSTEM01.DBF";   set newname for datafile  2 to "D:\ORADATA\CLONE\PDBSEED\SYSTEM01.DBF";   set newname for datafile  3 to "D:\ORADATA\CLONE\SYSAUX01.DBF";   set newname for datafile  4 to "D:\ORADATA\CLONE\PDBSEED\SYSAUX01.DBF";   set newname for datafile  5 to "D:\ORADATA\CLONE\UNDOTBS01.DBF";   set newname for datafile  6 to "D:\ORADATA\CLONE\PDBSEED\UNDOTBS01.DBF";   set newname for datafile  7 to "D:\ORADATA\CLONE\USERS01.DBF";   set newname for datafile  8 to "D:\ORADATA\CLONE\PDB1\SYSTEM01.DBF";   set newname for datafile  9 to "D:\ORADATA\CLONE\PDB1\SYSAUX01.DBF";   set newname for datafile  11 to "D:\ORADATA\CLONE\PDB1\USERS01.DBF";   set newname for datafile  12 to "D:\ORADATA\CLONE\PDB1\REPORT_INDEX01.DBF";   set newname for datafile  13 to "D:\ORADATA\CLONE\PDB1\REPORT_DATA01.DBF";   set newname for datafile  14 to "D:\ORADATA\CLONE\PDB1\LOB01.DBF";   set newname for datafile  15 to "D:\ORADATA\CLONE\PDB1\INDEX01.DBF";   set newname for datafile  16 to "D:\ORADATA\CLONE\PDB1\DATA01.DBF";   set newname for datafile  17 to "D:\ORADATA\CLONE\PDB1\ARCHIVE_INDEX01.DBF";   set newname for datafile  18 to "D:\ORADATA\CLONE\PDB1\ARCHIVE_DATA01.DBF";   set newname for datafile  20 to "D:\ORADATA\CLONE\PDB1\UNDOTBS01.DBF";   restore   clone database   ;}executing Memory Scriptexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 22-JUL-20using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00008 to D:\ORADATA\CLONE\PDB1\SYSTEM01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00009 to D:\ORADATA\CLONE\PDB1\SYSAUX01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00011 to D:\ORADATA\CLONE\PDB1\USERS01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00012 to D:\ORADATA\CLONE\PDB1\REPORT_INDEX01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00013 to D:\ORADATA\CLONE\PDB1\REPORT_DATA01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00014 to D:\ORADATA\CLONE\PDB1\LOB01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00015 to D:\ORADATA\CLONE\PDB1\INDEX01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00016 to D:\ORADATA\CLONE\PDB1\DATA01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00017 to D:\ORADATA\CLONE\PDB1\ARCHIVE_INDEX01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00018 to D:\ORADATA\CLONE\PDB1\ARCHIVE_DATA01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00020 to D:\ORADATA\CLONE\PDB1\UNDOTBS01.DBFchannel ORA_AUX_DISK_1: reading from backup piece E:\TEMP\BACKUP01V59TTN_1_1channel ORA_AUX_DISK_1: piece handle=E:\TEMP\BACKUP01V59TTN_1_1 tag=FOR DUPLICATIONchannel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:35channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to D:\ORADATA\CLONE\SYSTEM01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00003 to D:\ORADATA\CLONE\SYSAUX01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00005 to D:\ORADATA\CLONE\UNDOTBS01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00007 to D:\ORADATA\CLONE\USERS01.DBFchannel ORA_AUX_DISK_1: reading from backup piece E:\TEMP\BACKUP02V59U28_1_1channel ORA_AUX_DISK_1: piece handle=E:\TEMP\BACKUP02V59U28_1_1 tag=FOR DUPLICATIONchannel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00002 to D:\ORADATA\CLONE\PDBSEED\SYSTEM01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00004 to D:\ORADATA\CLONE\PDBSEED\SYSAUX01.DBFchannel ORA_AUX_DISK_1: restoring datafile 00006 to D:\ORADATA\CLONE\PDBSEED\UNDOTBS01.DBFchannel ORA_AUX_DISK_1: reading from backup piece E:\TEMP\BACKUP03V59U2F_1_1channel ORA_AUX_DISK_1: piece handle=E:\TEMP\BACKUP03V59U2F_1_1 tag=FOR DUPLICATIONchannel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15Finished restore at 22-JUL-20contents of Memory Script:{   switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy RECID=19 STAMP=1046447889 file name=D:\ORADATA\CLONE\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafile copy RECID=20 STAMP=1046447889 file name=D:\ORADATA\CLONE\PDBSEED\SYSTEM01.DBFdatafile 3 switched to datafile copyinput datafile copy RECID=21 STAMP=1046447890 file name=D:\ORADATA\CLONE\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafile copy RECID=22 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDBSEED\SYSAUX01.DBFdatafile 5 switched to datafile copyinput datafile copy RECID=23 STAMP=1046447890 file name=D:\ORADATA\CLONE\UNDOTBS01.DBFdatafile 6 switched to datafile copyinput datafile copy RECID=24 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDBSEED\UNDOTBS01.DBFdatafile 7 switched to datafile copyinput datafile copy RECID=25 STAMP=1046447890 file name=D:\ORADATA\CLONE\USERS01.DBFdatafile 8 switched to datafile copyinput datafile copy RECID=26 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\SYSTEM01.DBFdatafile 9 switched to datafile copyinput datafile copy RECID=27 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\SYSAUX01.DBFdatafile 11 switched to datafile copyinput datafile copy RECID=28 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\USERS01.DBFdatafile 12 switched to datafile copyinput datafile copy RECID=29 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\REPORT_INDEX01.DBFdatafile 13 switched to datafile copyinput datafile copy RECID=30 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\REPORT_DATA01.DBFdatafile 14 switched to datafile copyinput datafile copy RECID=31 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\LOB01.DBFdatafile 15 switched to datafile copyinput datafile copy RECID=32 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\INDEX01.DBFdatafile 16 switched to datafile copyinput datafile copy RECID=33 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\DATA01.DBFdatafile 17 switched to datafile copyinput datafile copy RECID=34 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\ARCHIVE_INDEX01.DBFdatafile 18 switched to datafile copyinput datafile copy RECID=35 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\ARCHIVE_DATA01.DBFdatafile 20 switched to datafile copyinput datafile copy RECID=36 STAMP=1046447890 file name=D:\ORADATA\CLONE\PDB1\UNDOTBS01.DBFcontents of Memory Script:{   recover   clone database   noredo    delete archivelog   ;}executing Memory ScriptStarting recover at 22-JUL-20using channel ORA_AUX_DISK_1Oracle instance startedTotal System Global Area    3238002128 bytesFixed Size                     9272784 bytesVariable Size               2147483648 bytesDatabase Buffers            1073741824 bytesRedo Buffers                   7503872 bytescontents of Memory Script:{   sql clone "alter system set  db_name = ''CLONE'' comment= ''Reset to original value by RMAN'' scope=spfile";   sql clone "alter system reset  db_unique_name scope=spfile";   shutdown clone immediate;}executing Memory Scriptsql statement: alter system set  db_name =  ''CLONE'' comment= ''Reset to original value by RMAN'' scope=spfilesql statement: alter system reset  db_unique_name scope=spfileOracle instance shut downRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 07/22/2020 15:59:30RMAN-05501: aborting duplication of target databaseRMAN-03015: error occurred in stored script Memory ScriptORA-19649: offline-range record RECID 180 STAMP 1045848118 not found in file current controlfileRMAN>

Now, if I replace

duplicate target database to CLONE with

duplicate target database to CLONE FROM ACTIVE DATABASE

cloning is successful.

Now, after trying this and that, I decided to restore my target database (SOUR1CDB) from the taken backup (used for cloning), to verify that backup is ok.

When doing so, I was surprised I needed to recover the database.

Finished restore at 22-JUL-20RMAN> recover database;Starting recover at 22-JUL-20using channel ORA_DISK_1applied offline range to datafile 00008offline range RECID=180 STAMP=1045848118applied offline range to datafile 00009offline range RECID=179 STAMP=1045848118applied offline range to datafile 00011offline range RECID=178 STAMP=1045848118applied offline range to datafile 00012offline range RECID=177 STAMP=1045848118applied offline range to datafile 00013offline range RECID=176 STAMP=1045848118applied offline range to datafile 00014offline range RECID=175 STAMP=1045848118applied offline range to datafile 00015offline range RECID=174 STAMP=1045848118applied offline range to datafile 00016offline range RECID=173 STAMP=1045848118applied offline range to datafile 00017offline range RECID=172 STAMP=1045848118applied offline range to datafile 00018offline range RECID=171 STAMP=1045848118applied offline range to datafile 00020offline range RECID=170 STAMP=1045848118starting media recoveryarchived log for thread 1 with sequence 461 is already on disk as file D:\ORADATA\SOUR1CDB\REDO050.LOGarchived log for thread 1 with sequence 462 is already on disk as file D:\ORADATA\SOUR1CDB\REDO060.LOGarchived log for thread 1 with sequence 463 is already on disk as file D:\ORADATA\SOUR1CDB\REDO010.LOGarchived log for thread 1 with sequence 464 is already on disk as file D:\ORADATA\SOUR1CDB\REDO020.LOGarchived log for thread 1 with sequence 465 is already on disk as file D:\ORADATA\SOUR1CDB\REDO030.LOGarchived log file name=D:\ORADATA\SOUR1CDB\REDO050.LOG thread=1 sequence=461archived log file name=D:\ORADATA\SOUR1CDB\REDO060.LOG thread=1 sequence=462archived log file name=D:\ORADATA\SOUR1CDB\REDO010.LOG thread=1 sequence=463archived log file name=D:\ORADATA\SOUR1CDB\REDO020.LOG thread=1 sequence=464archived log file name=D:\ORADATA\SOUR1CDB\REDO030.LOG thread=1 sequence=465media recovery complete, elapsed time: 00:00:10Finished recover at 22-JUL-20

Notice, again RECID=180 STAMP=1045848118.

So I did again duplicated the database getting that error

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/22/2020 15:59:30

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

ORA-19649: offline-range record RECID 180 STAMP 1045848118 not found in file current controlfile

RMAN>

but this time, I straight away issued recover database command (seems like nothing done):

RMAN> recover database;Starting recover at 22-JUL-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=768 device type=DISKstarting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 22-JUL-20

and managed to open CLONE database with resetlogs.

Can someone tell me please what this RMAN error is telling me and comment on the whole situation?

Addendum1 -an hour later.

My joy was premature. I thought I managed to open CLONE database with resetlogs (as it seemed)

RMAN> alter database open resetlogs;

Statement processed

when I did quit RMAN (without issues) the instance was in idle. I started it in nomount, then

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01103: database name 'SOUR1CDB' in control file is not 'CLONE'

Addendum2 - 12 hours later

I realized, that opening database with resetlogs worked as it was applied to target database (SOUR1CDB), not auxiliary. I guess for many RMANers this was obvious. So my basic puzzle is, why this process is failing when duplicate target database to CLONE and not when duplicate target database to CLONE FROM ACTIVE DATABASE.

Message was edited by: Alex2068