This post was originally published on this site
In this blog post I want to write about a typical restore scenario. You need to restore a database to a historical point in time to investigate some former data or maybe even rescue some data, but need to leave the source database intact. So you create a clone db, if you are unlucky you need to do it on same cluster or server. And maybe you also want to skip certain tablespaces, as the data of interest might be just some small percentage of all data or you simply do not have time or space to restore the entire database.
Overview of a scenario on Oracle 19.17 which I use for demonstration:
Source db:
Single instance database called nestor using ASM disk groups DATA and FRA.
It has a tablespace called “dat” with a demo table, the only index for the table is on tablespace “idx”.
The structure is as follows, it is a non-cdb, just to make the scenario a little simpler
and outputs shorter, as they are already lengthy enough. This example is – of course – also extendable to CDB architecture:
RMAN> REPORT SCHEMA;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name NESTOR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1030 SYSTEM YES +DATA/NESTOR/DATAFILE/system.279.1121600765
2 10 IDX NO +DATA/NESTOR/DATAFILE/idx.290.1121605775
3 800 SYSAUX NO +DATA/NESTOR/DATAFILE/sysaux.280.1121600799
4 705 UNDOTBS1 YES +DATA/NESTOR/DATAFILE/undotbs1.281.1121600825
5 10 DAT NO +DATA/NESTOR/DATAFILE/dat.289.1121605769
7 5 USERS NO +DATA/NESTOR/DATAFILE/users.282.1121600827
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 118 TEMP 32767 +DATA/NESTOR/TEMPFILE/temp.287.1121600889
SQL> SELECT group#, member FROM v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------
1 +DATA/NESTOR/ONLINELOG/group_1.286.1121613259
1 +FRA/NESTOR/ONLINELOG/group_1.274.1121613259
2 +DATA/NESTOR/ONLINELOG/group_2.285.1121613263
2 +FRA/NESTOR/ONLINELOG/group_2.275.1121613263
3 +DATA/NESTOR/ONLINELOG/group_3.284.1121613267
3 +FRA/NESTOR/ONLINELOG/group_3.276.1121613267
6 rows selected.
Some key init.ora’s are:
*.control_files='+DATA/NESTOR/CONTROLFILE/current.293.1121613869','+FRA/NESTOR/CONTROLFILE/current.265.1121613869'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+FRA'
*.db_name='nestor'
Cloned db:
Single instance database called clone using ASM diskgroups DATA and FRA.
Some key init.ora’s:
*.control_files='+DATA','+FRA'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+FRA'
*.db_name='clone'
For demonstration I will use a “time to recover” of 25.11.2022 15:30:00 and as tablespace to be skipped: “idx”.
Of course, we should be able to read table contents, even without access to the index.
duplicate from backup with until time:
The regular clone without the skip looks as follows:
RMAN> CONNECT TARGET sys/[email protected]
connected to target database: NESTOR (DBID=2138231914)
RMAN> CONNECT AUXILIARY SYS/[email protected]
connected to auxiliary database: CLONE (not mounted)
RMAN> DUPLICATE TARGET DATABASE TO 'clone'
UNTIL TIME "to_date('25.11.2022 15:30:00','DD.MM.YYYY HH24:MI:SS')";
Starting Duplicate Db at 25.11.2022 15:44:10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=263 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
contents of Memory Script:
{
set until scn 2502156;
sql clone "alter system set control_files =
''+DATA/CLONE/CONTROLFILE/current.295.1121701453'', ''+FRA/CLONE/CONTROLFILE/current.298.1121701453'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''NESTOR'' 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 Script
executing command: SET until clause
sql statement: alter system set control_files = ''+DATA/CLONE/CONTROLFILE/current.295.1121701453'', ''+FRA/CLONE/CONTROLFILE/current.298.1121701453'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''NESTOR'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7876608 bytes
Starting restore at 25.11.2022 15:44:47
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=259 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/NESTOR/BACKUPSET/2022_11_25/ncnnf0_tag20221125t152525_0.306.1121700327
channel ORA_AUX_DISK_1: piece handle=+FRA/NESTOR/BACKUPSET/2022_11_25/ncnnf0_tag20221125t152525_0.306.1121700327 tag=TAG20221125T152525
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/CLONE/CONTROLFILE/current.295.1121701453
output file name=+FRA/CLONE/CONTROLFILE/current.298.1121701453
Finished restore at 25.11.2022 15:44:51
database mounted
contents of Memory Script:
{
set until scn 2502156;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25.11.2022 15:44:56
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/NESTOR/BACKUPSET/2022_11_25/nnndf0_tag20221125t152319_0.299.1121700201
channel ORA_AUX_DISK_1: piece handle=+FRA/NESTOR/BACKUPSET/2022_11_25/nnndf0_tag20221125t152319_0.299.1121700201 tag=TAG20221125T152319
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 25.11.2022 15:46:12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1121701572 file name=+DATA/CLONE/DATAFILE/system.283.1121701497
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1121701572 file name=+DATA/CLONE/DATAFILE/idx.297.1121701497
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1121701572 file name=+DATA/CLONE/DATAFILE/sysaux.291.1121701497
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1121701572 file name=+DATA/CLONE/DATAFILE/undotbs1.296.1121701497
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1121701572 file name=+DATA/CLONE/DATAFILE/dat.298.1121701497
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=1121701572 file name=+DATA/CLONE/DATAFILE/users.299.1121701497
contents of Memory Script:
{
set until time "to_date('NOV 25 2022 15:30:00', 'MON DD YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25.11.2022 15:46:12
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file +FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_9.308.1121700303
archived log for thread 1 with sequence 10 is already on disk as file +FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_10.305.1121700683
archived log file name=+FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_9.308.1121700303 thread=1 sequence=9
archived log file name=+FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_10.305.1121700683 thread=1 sequence=10
media recovery complete, elapsed time: 00:00:01
Finished recover at 25.11.2022 15:46:15
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7876608 bytes
contents 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";
}
executing Memory Script
sql statement: alter system set db_name = ''CLONE'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7876608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 10 M ,
GROUP 2 SIZE 10 M ,
GROUP 3 SIZE 10 M
DATAFILE
'+DATA/CLONE/DATAFILE/system.283.1121701497'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/CLONE/DATAFILE/idx.297.1121701497",
"+DATA/CLONE/DATAFILE/sysaux.291.1121701497",
"+DATA/CLONE/DATAFILE/undotbs1.296.1121701497",
"+DATA/CLONE/DATAFILE/dat.298.1121701497",
"+DATA/CLONE/DATAFILE/users.299.1121701497";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/idx.297.1121701497 RECID=1 STAMP=1121701602
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/sysaux.291.1121701497 RECID=2 STAMP=1121701602
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/undotbs1.296.1121701497 RECID=3 STAMP=1121701602
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/dat.298.1121701497 RECID=4 STAMP=1121701602
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/users.299.1121701497 RECID=5 STAMP=1121701602
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1121701602 file name=+DATA/CLONE/DATAFILE/idx.297.1121701497
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1121701602 file name=+DATA/CLONE/DATAFILE/sysaux.291.1121701497
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1121701602 file name=+DATA/CLONE/DATAFILE/undotbs1.296.1121701497
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1121701602 file name=+DATA/CLONE/DATAFILE/dat.298.1121701497
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1121701602 file name=+DATA/CLONE/DATAFILE/users.299.1121701497
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25.11.2022 15:46:49
You can see in the lines 189 ff., that recursively a new control file was created. The redo log member names were not specified, so needed to be created during a clearing.
Outcome db structure is:
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1030 SYSTEM YES +DATA/CLONE/DATAFILE/system.283.1121701497
2 10 IDX NO +DATA/CLONE/DATAFILE/idx.297.1121701497
3 810 SYSAUX NO +DATA/CLONE/DATAFILE/sysaux.291.1121701497
4 705 UNDOTBS1 YES +DATA/CLONE/DATAFILE/undotbs1.296.1121701497
5 10 DAT NO +DATA/CLONE/DATAFILE/dat.298.1121701497
7 5 USERS NO +DATA/CLONE/DATAFILE/users.299.1121701497
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 118 TEMP 32767 +DATA/CLONE/TEMPFILE/temp.294.1121701605
SQL> SELECT group#, member FROM v$logfile;
GROUP# MEMBER
---------- -------------------------------------------------
3 +DATA/CLONE/ONLINELOG/group_3.302.1121701603
3 +FRA/CLONE/ONLINELOG/group_3.297.1121701603
2 +DATA/CLONE/ONLINELOG/group_2.301.1121701603
2 +FRA/CLONE/ONLINELOG/group_2.296.1121701603
1 +DATA/CLONE/ONLINELOG/group_1.292.1121701603
1 +FRA/CLONE/ONLINELOG/group_1.300.1121701603
6 rows selected.
init.ora parameter adapted automatically by RMAN:
*.control_files='+DATA/CLONE/CONTROLFILE/current.295.1121701453','+FRA/CLONE/CONTROLFILE/current.298.1121701453'#Restore Controlfile
If we try to skip a tablespace the output is nearly the same, until and error is raised:
DUPLICATE TARGET DATABASE TO 'clone'
UNTIL TIME "to_date('25.11.2022 15:30:00','DD.MM.YYYY HH24:MI:SS')"
SKIP TABLESPACE idx;
...
Checking that duplicated tablespaces are self-contained
The following errors need to be fixed before peforming this command
Violation: ORA-39908: Index SCOTT.PK_DEPT in tablespace IDX enforces primary constraints of table SCOTT.DEPT in tablespace DAT.
Violation: ORA-39908: Index SCOTT.PK_EMP in tablespace IDX enforces primary constraints of table SCOTT.EMP in tablespace DAT.
Oracle instance started
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/25/2022 15:54:36
RMAN-05501: aborting duplication of target database
RMAN-05548: The set of duplicated tablespaces is not self-contained
Well, it does not allow the skip, even if we are interested in the tables only, it looks like we must restore tablespaces with the indexes pointing to it as well. Why is that? RMAN checks for self-containedness, which makes typically sense, if you want to use this database later on, e.g. for testing.
But if we only want to rescue some data from tables on particular tablespaces, why should we restore data from other tablespaces?
Offline status of these data files would be completely fine, and should save time and space. But it looks like we cannot do it with RMAN duplicate.
Well, one trick is using a target-less clone, in that case a self-containedness check cannot be performed as we simply do not have a connection to target db to do that, so it is omitted. If you want to use such a duplication, you either need a catalog or disk backups available to reference them via BACKUP LOCATION clause. In the Oracle documentation you find it as “Backup-based duplication without a target connection”,
see here and here.
RMAN> CONNECT AUXILIARY sys/[email protected]
connected to auxiliary database: CLONE (not mounted)
RMAN> DUPLICATE DATABASE TO 'clone'
BACKUP LOCATION '+FRA/NESTOR/BACKUPSET/'
UNTIL TIME "to_date('25.11.2022 15:30:00','DD.MM.YYYY HH24:MI:SS')"
SKIP TABLESPACE idx;
Starting Duplicate Db at 25.11.2022 16:01:12
searching for database ID
found backup of database ID 2138231914
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/CLONE/CONTROLFILE/current.295.1121702473'', ''+FRA/CLONE/CONTROLFILE/current.296.1121702473'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''NESTOR'' 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 from '+FRA/NESTOR/BACKUPSET/2022_11_25/ncnnf0_TAG20221125T152525_0.306.1121700327';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/CLONE/CONTROLFILE/current.295.1121702473'', ''+FRA/CLONE/CONTROLFILE/current.296.1121702473'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''NESTOR'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7876608 bytes
Starting restore at 25.11.2022 16:01:48
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=259 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/CLONE/CONTROLFILE/current.295.1121702473
output file name=+FRA/CLONE/CONTROLFILE/current.296.1121702473
Finished restore at 25.11.2022 16:01:54
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=259 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 2 skipped by request
contents of Memory Script:
{
set until scn 2502156;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 7 to new;
restore
clone database
skip forever tablespace "IDX" ;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25.11.2022 16:02:00
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/NESTOR/BACKUPSET/2022_11_25/nnndf0_tag20221125t152319_0.299.1121700201
channel ORA_AUX_DISK_1: piece handle=+FRA/NESTOR/BACKUPSET/2022_11_25/nnndf0_tag20221125t152319_0.299.1121700201 tag=TAG20221125T152319
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 25.11.2022 16:03:05
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1121702585 file name=+DATA/CLONE/DATAFILE/system.299.1121702521
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1121702585 file name=+DATA/CLONE/DATAFILE/sysaux.298.1121702521
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1121702585 file name=+DATA/CLONE/DATAFILE/undotbs1.297.1121702521
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1121702585 file name=+DATA/CLONE/DATAFILE/dat.296.1121702521
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=1121702585 file name=+DATA/CLONE/DATAFILE/users.291.1121702521
contents of Memory Script:
{
set until time "to_date('NOV 25 2022 15:30:00', 'MON DD YYYY HH24:MI:SS')";
recover
clone database
skip forever tablespace "IDX" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25.11.2022 16:03:06
using channel ORA_AUX_DISK_1
Executing: alter database datafile 2 offline drop
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: reading from backup piece +FRA/NESTOR/BACKUPSET/2022_11_25/annnf0_tag20221125t153126_0.303.1121700687
channel ORA_AUX_DISK_1: piece handle=+FRA/NESTOR/BACKUPSET/2022_11_25/annnf0_tag20221125t153126_0.303.1121700687 tag=TAG20221125T153126
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/CLONE/ARCHIVELOG/2022_11_25/thread_1_seq_9.297.1121702587 thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=+FRA/CLONE/ARCHIVELOG/2022_11_25/thread_1_seq_9.297.1121702587 RECID=2 STAMP=1121702587
archived log file name=+FRA/CLONE/ARCHIVELOG/2022_11_25/thread_1_seq_10.300.1121702587 thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=+FRA/CLONE/ARCHIVELOG/2022_11_25/thread_1_seq_10.300.1121702587 RECID=1 STAMP=1121702587
media recovery complete, elapsed time: 00:00:01
Finished recover at 25.11.2022 16:03:09
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7876608 bytes
contents 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";
}
executing Memory Script
sql statement: alter system set db_name = ''CLONE'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7876608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 10 M ,
GROUP 2 SIZE 10 M ,
GROUP 3 SIZE 10 M
DATAFILE
'+DATA/CLONE/DATAFILE/system.299.1121702521'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/CLONE/DATAFILE/sysaux.298.1121702521",
"+DATA/CLONE/DATAFILE/undotbs1.297.1121702521",
"+DATA/CLONE/DATAFILE/dat.296.1121702521",
"+DATA/CLONE/DATAFILE/users.291.1121702521";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/sysaux.298.1121702521 RECID=1 STAMP=1121702614
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/undotbs1.297.1121702521 RECID=2 STAMP=1121702614
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/dat.296.1121702521 RECID=3 STAMP=1121702614
cataloged datafile copy
datafile copy file name=+DATA/CLONE/DATAFILE/users.291.1121702521 RECID=4 STAMP=1121702614
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1121702614 file name=+DATA/CLONE/DATAFILE/sysaux.298.1121702521
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1121702614 file name=+DATA/CLONE/DATAFILE/undotbs1.297.1121702521
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1121702614 file name=+DATA/CLONE/DATAFILE/dat.296.1121702521
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1121702614 file name=+DATA/CLONE/DATAFILE/users.291.1121702521
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Dropping offline and skipped tablespaces
Executing: drop tablespace "IDX" including contents cascade constraints
Oracle error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace IDX due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "IDX" including contents cascade constraints
Oracle error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace IDX due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "IDX" including contents cascade constraints
Oracle error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace IDX due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "IDX" including contents cascade constraints
Oracle error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace IDX due to constraints, will reattempt removal after other tablespaces are removed
Executing: drop tablespace "IDX" including contents cascade constraints
Oracle error from auxiliary database: ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
Could not drop tablespace IDX due to constraints
Finished Duplicate Db at 25.11.2022 16:04:06
Well, RMAN tried to drop the tablespace for us at the end, which did not work due to an enabled PK constraint, but that is not a problem. The clone worked and we can read the table data. We cannot read it via the index, but with full table scans it is not a problem:
SQL> SELECT * FROM scott.emp WHERE empno=7521;
select * from scott.emp where empno=7521
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01111: name for data file 2 is unknown - rename to correct file
ORA-01110: data file 2: '/u00/app/oracle/product/19/dbs/MISSING00002'
SQL> SELECT * FROM scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
...
7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
14 rows selected.
But are there maybe other solutions, if target-less is not suitable or even possible?
One obvious solution would be a “RESTORE DATABASE” command in RMAN, but it has some nasty traps, which will be illustrated now. As we are on the same cluster/server we need to make sure, that nothing is overwritten which belongs to our source, neither control nor data files nor redo log members.
Now we use the instance “clone” and restore the database nestor, so we need to set db_name to nestor first. This is what the duplicate statement also does internally to accomplish the clone.
SQL> ALTER SYSTEM SET db_name='NESTOR' SCOPE=SPFILE;
And it is also needed to change the other parameter, which was also noticeable during the duplicate:
SQL> ALTER SYSTEM SET db_unique_name='CLONE' SCOPE=SPFILE;
You will get problems if you try to skip that parameter changes. Starting the instance with colliding db_unique_name can lead to errors like:
SQL> STARTUP NOMOUNT
ORA-39511: Start of CRS resource for instance '223' failed with error:[CRS-5702: Resource 'ora.nestor.db' is already running on 'vm201'
CRS-0223: Resource 'ora.nestor.db' has placement error.
clsr_start_resource:260 status:223
clsrapi_start_db:start_asmdbs status:223
]
or
SQL> STARTUP NOMOUNT
ORA-00304: requested INSTANCE_NUMBER is busy
Now we can start to restore, but need to be careful with the destinations for all restored files. Our control files are handled correctly as we set the control_files parameter again to disk group only.
SQL> ALTER SYSTEM SET control_files='+DATA','+FRA' SCOPE=SPFILE;
SQL> STARTUP NOMOUNT FORCE
RMAN> CONNECT TARGET /
connected to target database: NESTOR (not mounted)
RMAN> RESTORE CONTROLFILE FROM '+FRA/NESTOR/BACKUPSET/2022_11_25/ncnnf0_tag20221125t153131_0.302.1121700693';
Starting restore at 25.11.2022 16:26:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=263 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/CLONE/CONTROLFILE/current.295.1121704001
output file name=+FRA/CLONE/CONTROLFILE/current.296.1121704001
Finished restore at 25.11.2022 16:26:41
RMAN> ALTER DATABASE MOUNT;
released channel: ORA_DISK_1
Statement processed
Now it is important to map the db files to somewhere else, maybe using db_file_name_convert, or using a “SET NEWNAME” approach, like I use here. I also include the skip of unwanted tablespaces at that time:
RMAN> RUN {
2> SET UNTIL TIME "to_date('25.11.2022 15:30:00','DD.MM.YYYY HH24:MI:SS')";
3> SET NEWNAME FOR DATABASE TO NEW;
4> RESTORE DATABASE SKIP FOREVER TABLESPACE idx;
5> }
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 25.11.2022 16:27:29
Starting implicit crosscheck backup at 25.11.2022 16:27:29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 25.11.2022 16:27:30
Starting implicit crosscheck copy at 25.11.2022 16:27:30
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25.11.2022 16:27:31
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: restoring datafile 00007 to +DATA
channel ORA_DISK_1: reading from backup piece +FRA/NESTOR/BACKUPSET/2022_11_25/nnndf0_tag20221125t152319_0.299.1121700201
channel ORA_DISK_1: piece handle=+FRA/NESTOR/BACKUPSET/2022_11_25/nnndf0_tag20221125t152319_0.299.1121700201 tag=TAG20221125T152319
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 25.11.2022 16:28:37
Now let us switch all files that were restored.
RMAN> SWITCH DATAFILE 1,3,4,5,7 TO COPY;
datafile 1 switched to datafile copy "+DATA/CLONE/DATAFILE/system.299.1121704053"
datafile 3 switched to datafile copy "+DATA/CLONE/DATAFILE/sysaux.298.1121704053"
datafile 4 switched to datafile copy "+DATA/CLONE/DATAFILE/undotbs1.297.1121704053"
datafile 5 switched to datafile copy "+DATA/CLONE/DATAFILE/dat.296.1121704053"
datafile 7 switched to datafile copy "+DATA/CLONE/DATAFILE/users.291.1121704053"
and recover them, again with the same skip clause as above:
RMAN> RUN {
2> SET UNTIL TIME "to_date('25.11.2022 15:30:00','DD.MM.YYYY HH24:MI:SS')";
3> RECOVER DATABASE SKIP FOREVER TABLESPACE idx;
4> }
executing command: SET until clause
Starting recover at 25.11.2022 16:33:57
using channel ORA_DISK_1
Executing: alter database datafile 2 offline drop
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file +FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_9.308.1121700303
archived log for thread 1 with sequence 10 is already on disk as file +FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_10.305.1121700683
archived log file name=+FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_9.308.1121700303 thread=1 sequence=9
archived log file name=+FRA/NESTOR/ARCHIVELOG/2022_11_25/thread_1_seq_10.305.1121700683 thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 25.11.2022 16:33:59
And now your source db is still in danger as this cloned control file still has the old redo members! See this:
SQL> SELECT member FROM v$logfile;
MEMBER
------------------------------------------------
+DATA/NESTOR/ONLINELOG/group_1.286.1121680021
+FRA/NESTOR/ONLINELOG/group_1.274.1121680021
+DATA/NESTOR/ONLINELOG/group_2.285.1121680021
+FRA/NESTOR/ONLINELOG/group_2.275.1121680023
+DATA/NESTOR/ONLINELOG/group_3.284.1121680023
+FRA/NESTOR/ONLINELOG/group_3.276.1121680023
6 rows selected.
!!Next step is the resetlogs, which would work but would destroy our source, if we do not change redo log members!!
How to deal with that:
1. we could also create a new control file and set different names for the members
2. we could rename the members in control file
3. we could rename the redo log members in source
I show you option 2 here, which is probably most suitable in my case:
SQL> ALTER DATABASE RENAME FILE '+DATA/NESTOR/ONLINELOG/group_1.286.1121680021' TO '+DATA';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+FRA/NESTOR/ONLINELOG/group_1.274.1121680021' TO '+FRA';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DATA/NESTOR/ONLINELOG/group_2.285.1121680021' TO '+DATA';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+FRA/NESTOR/ONLINELOG/group_2.275.1121680023' TO '+FRA';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+DATA/NESTOR/ONLINELOG/group_3.284.1121680023' TO '+DATA';
Database altered.
SQL> ALTER DATABASE RENAME FILE '+FRA/NESTOR/ONLINELOG/group_3.276.1121680023' TO '+FRA';
Database altered.
SQL> SELECT group#, member FROM v$logfile ORDER BY 1, 2;
GROUP# MEMBER
---------- -------------------------------------------------
1 +DATA
1 +FRA
2 +DATA
2 +FRA
3 +DATA
3 +FRA
6 rows selected.
also temp files are still the old:
SQL> SELECT name FROM v$tempfile;
NAME
----------------------------------------------
+DATA/NESTOR/TEMPFILE/temp.287.1121600889
SQL> ALTER DATABASE RENAME FILE '+DATA/NESTOR/TEMPFILE/temp.287.1121600889' TO '+DATA';
Database altered.
Now we are fit for the resetlogs, you can do it in rman or sqlplus.
Another warning: if you are connected with rman catalog and you run the resetlogs in rman in that session, you register a new incarnation for your source db, as you are connected to a clone with the same dbid. If you want to avoid that, what you probably should, better do the resetlogs without catalog connection or even in sqlplus.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Our data file number 2 was not restored as we can see here:
SQL> SELECT file#, name, status FROM v$datafile;
FILE# NAME STATUS
---------- --------------------------------------------- -------
1 +DATA/CLONE/DATAFILE/system.299.1121704053 SYSTEM
2 +DATA/NESTOR/DATAFILE/idx.290.1121605775 OFFLINE
3 +DATA/CLONE/DATAFILE/sysaux.298.1121704053 ONLINE
4 +DATA/CLONE/DATAFILE/undotbs1.297.1121704053 ONLINE
5 +DATA/CLONE/DATAFILE/dat.296.1121704053 ONLINE
7 +DATA/CLONE/DATAFILE/users.291.1121704053 ONLINE
6 rows selected.
If you want to change the dbid of that clone now, because you want to keep it longer,
see “How to Change the DBID, DBNAME Using NID Utility (Doc ID 863800.1)”
or here.
Hope that helps.
Mathias
