I am a big fan of Oracle Recovery Manager (RMAN) since the very beginning. It evolved over time and makes the life of a DBA much easier, as long as you know the concepts. Todays blog post is about duplicating a database. Initially I wanted to perform a schema point in time restore which is not natively supported by RMAN. So let’s outline the system I have.
We are talking about an Oracle Database 19c, 19.18 to be precise. It is running the multitenant architecture and has 3 pluggable databases. I am using Oracle Managed Files (OMF) because this makes file handling easier for me. In one of these PDBs are several schemas spread over a couple of tablespaces. This is the tablespace layout:
SYS @ ORCL19:CDB$ROOT:>select con_id, tablespace_name, status from cdb_tablespaces;
CON_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ---------
3 SYSTEM ONLINE
3 SYSAUX ONLINE
3 UNDOTBS1 ONLINE
3 TEMP ONLINE
3 USERS READ ONLY <<<<<<<<
3 TBS_DATA READ ONLY
3 TBS_LOB READ ONLY
3 TBS_ORG_UTPLSQL ONLINE
1 SYSTEM ONLINE
1 SYSAUX ONLINE
1 UNDOTBS1 ONLINE
1 TEMP ONLINE
1 USERS ONLINE
4 SYSTEM ONLINE
4 SYSAUX ONLINE
4 UNDOTBS1 ONLINE
4 TEMP ONLINE
4 USERS ONLINE
4 DATA ONLINE
4 TBS_DATA READ ONLY
4 TBS_LOB READ ONLY
5 SYSTEM ONLINE
5 SYSAUX ONLINE
5 UNDOTBS1 ONLINE
5 TEMP ONLINE
5 USERS ONLINE
I wanted to restore a specific tablespace, USERS in CON_ID 3, to some point in the past. Since I cannot use an automatic auxiliary instance for this, I created my own auxiliary instance. Not that complex, just create a parameterfile.
[oracle@vm160 ~]$ cat $ORACLE_HOME/dbs/initaux.ora
db_name=aux
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/fra'
db_recovery_file_dest_size=10g
sga_target=1500M
enable_pluggable_database=true
Having that, I started RMAN, connected to my target and my auxiliary database and started the duplicate. The DUPLICATE command allows us to include or exclude PDBs and/or skip specific tablespaces. Since I wanted to only restore one tablespace of one PDB, I combined these features. Furthermore I used the “NOOPEN” option to prevent the auxiliary instance from opening automatically after the duplication.
[oracle@vm160 ~]$ rman
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 21 08:21:41 2024
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/********@vm160/orcl19.support.robotron.de
connected to target database: ORCL19 (DBID=422142402)
RMAN> connect auxiliary /
connected to auxiliary database (not started)
RMAN> startup clone nomount
Oracle instance started
Total System Global Area 1577055136 bytes
Fixed Size 9163680 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7610368 bytes
RMAN> sql clone 'create spfile from pfile';
using target database control file instead of recovery catalog
sql statement: create spfile from pfile
RMAN> startup clone nomount force
Oracle instance started
Total System Global Area 1577055136 bytes
Fixed Size 9163680 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7610368 bytes
RMAN> duplicate database to aux pluggable database pdb1 skip tablespace pdb1:TBS_DATA,pdb1:TBS_LOB,pdb1:TBS_ORG_UTPLSQL until time 'sysdate-1' noopen;
Starting Duplicate Db at 2024-03-21 08:22:30
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=179 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 42779928;
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/AUX/controlfile/o1_mf_lzqr1q5x_.ctl'', ''/u01/app/oracle/fra/AUX/controlfile/o1_mf_lzqr1q60_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL19'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''aux'' 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 = ''/u01/app/oracle/oradata/AUX/controlfile/o1_mf_lzqr1q5x_.ctl'', ''/u01/app/oracle/fra/AUX/controlfile/o1_mf_lzqr1q60_.ctl'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL19'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''aux'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1577055136 bytes
Fixed Size 9163680 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7610368 bytes
Starting restore at 2024-03-21 08:22:53
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4 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 /u01/app/oracle/fra/ORCL19/autobackup/2024_03_20/o1_mf_s_1164074408_lznf8rgr_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fra/ORCL19/autobackup/2024_03_20/o1_mf_s_1164074408_lznf8rgr_.bkp tag=TAG20240320T020008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/AUX/controlfile/o1_mf_lzqr1q5x_.ctl
output file name=/u01/app/oracle/fra/AUX/controlfile/o1_mf_lzqr1q60_.ctl
Finished restore at 2024-03-21 08:22:55
database mounted
Skipping pluggable database ORCL19PDB2
Skipping pluggable database PDB2
Checking that duplicated tablespaces are self-contained
Automatically adding tablespace SYSTEM
Automatically adding tablespace SYSAUX
Automatically adding tablespace PDB$SEED:SYSTEM
Automatically adding tablespace PDB$SEED:SYSAUX
Automatically adding tablespace UNDOTBS1
Skipping tablespace USERS
Skipping tablespace PDB1:TBS_ORG_UTPLSQL
Skipping tablespace PDB1:TBS_LOB
Skipping tablespace PDB1:TBS_DATA
contents of Memory Script:
{
set until scn 42779928;
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 6 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 19 to new;
set newname for clone datafile 20 to new;
set newname for clone datafile 21 to new;
set newname for clone datafile 22 to new;
restore
clone database
skip forever tablespace "USERS",
"ORCL19PDB2":"USERS",
"ORCL19PDB2":"UNDOTBS1",
"ORCL19PDB2":"SYSTEM",
"ORCL19PDB2":"SYSAUX",
"PDB2":"USERS",
"PDB2":"UNDOTBS1",
"PDB2":"TBS_LOB",
"PDB2":"TBS_DATA",
"PDB2":"SYSTEM",
"PDB2":"SYSAUX",
"PDB2":"DATA",
"PDB1":"TBS_ORG_UTPLSQL",
"PDB1":"TBS_LOB",
"PDB1":"TBS_DATA" ;
}
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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2024-03-21 08:23:13
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 00019 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gh2lsmoo_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gh2lsmoo_1_1 tag=TAG20240317T010008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gj2lsmq0_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gj2lsmq0_1_1 tag=TAG20240317T010008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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 00005 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gl2lsmqn_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_data_gl2lsmqn_1_1 tag=TAG20240317T010008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2024-03-21 08:23:51
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=20 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=21 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=22 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=23 STAMP=1164183831 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf
contents of Memory Script:
{
set until time "to_date('2024/03/20 08:22:30', 'YYYY/MM/DD HH24:MI:SS')";
recover
clone database
skip forever tablespace "USERS",
"ORCL19PDB2":"USERS",
"ORCL19PDB2":"UNDOTBS1",
"ORCL19PDB2":"SYSTEM",
"ORCL19PDB2":"SYSAUX",
"PDB2":"USERS",
"PDB2":"UNDOTBS1",
"PDB2":"TBS_LOB",
"PDB2":"TBS_DATA",
"PDB2":"SYSTEM",
"PDB2":"SYSAUX",
"PDB2":"DATA",
"PDB1":"TBS_ORG_UTPLSQL",
"PDB1":"TBS_LOB",
"PDB1":"TBS_DATA" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2024-03-21 08:23:51
using channel ORA_AUX_DISK_1
datafile 22 not processed because file is read-only
Executing: alter database datafile 7 offline drop
Executing: alter database datafile 29, 30, 33 offline drop
Executing: alter database datafile 24, 25, 26, 27, 28, 31, 32 offline drop
Executing: alter database datafile 15, 16, 17, 23 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=234
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_gm2lsmqq_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_gm2lsmqq_1_1 tag=TAG20240317T010114
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=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_234_lzqr4b89_.arc thread=1 sequence=234
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_234_lzqr4b89_.arc RECID=726 STAMP=1164183834
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=235
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_go2lsq94_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240317_arch_go2lsq94_1_1 tag=TAG20240317T020004
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=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_235_lzqr4ck8_.arc thread=1 sequence=235
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_235_lzqr4ck8_.arc RECID=727 STAMP=1164183835
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=236
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=237
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=238
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240318_arch_gq2lvel5_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240318_arch_gq2lvel5_1_1 tag=TAG20240318T020005
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_236_lzqr4gz1_.arc thread=1 sequence=236
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_236_lzqr4gz1_.arc RECID=729 STAMP=1164183840
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_237_lzqr4gys_.arc thread=1 sequence=237
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_237_lzqr4gys_.arc RECID=730 STAMP=1164183840
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_238_lzqr4h00_.arc thread=1 sequence=238
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_238_lzqr4h00_.arc RECID=728 STAMP=1164183840
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=239
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=240
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=241
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=242
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240319_arch_gs2m2315_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240319_arch_gs2m2315_1_1 tag=TAG20240319T020005
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_239_lzqr55dj_.arc thread=1 sequence=239
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_239_lzqr55dj_.arc RECID=733 STAMP=1164183862
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_240_lzqr55f6_.arc thread=1 sequence=240
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_240_lzqr55f6_.arc RECID=731 STAMP=1164183861
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_241_lzqr55d4_.arc thread=1 sequence=241
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_241_lzqr55d4_.arc RECID=734 STAMP=1164183862
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_242_lzqr55ds_.arc thread=1 sequence=242
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_242_lzqr55ds_.arc RECID=732 STAMP=1164183861
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=243
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=244
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240320_arch_gu2m4nd7_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240320_arch_gu2m4nd7_1_1 tag=TAG20240320T020007
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_243_lzqr5nvb_.arc thread=1 sequence=243
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_243_lzqr5nvb_.arc RECID=736 STAMP=1164183878
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_244_lzqr5nvn_.arc thread=1 sequence=244
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_244_lzqr5nvn_.arc RECID=735 STAMP=1164183877
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=245
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl19/ORCL19_20240321_arch_h02m7bp5_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orcl19/ORCL19_20240321_arch_h02m7bp5_1_1 tag=TAG20240321T020005
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=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_245_lzqr6354_.arc thread=1 sequence=245
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/AUX/archivelog/2024_03_21/o1_mf_1_245_lzqr6354_.arc RECID=737 STAMP=1164183891
media recovery complete, elapsed time: 00:00:02
Finished recover at 2024-03-21 08:24:55
Oracle instance started
Total System Global Area 1577055136 bytes
Fixed Size 9163680 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7610368 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''AUX'' 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 = ''AUX'' 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 1577055136 bytes
Fixed Size 9163680 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7610368 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 200 M ,
GROUP 2 SIZE 200 M ,
GROUP 3 SIZE 200 M
DATAFILE
'/u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf',
'/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf',
'/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
set newname for clone tempfile 3 to new;
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf",
"/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf",
"/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf",
"/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf",
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf",
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/AUX/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_temp_%u_.tmp in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf RECID=1 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf RECID=2 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf RECID=3 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf RECID=4 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf RECID=5 STAMP=1164183915
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf RECID=6 STAMP=1164183915
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=4 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=5 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=6 STAMP=1164183915 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
Leaving database unopened, as requested
Finished Duplicate Db at 2024-03-21 08:25:15
So far, so good. See line 180, my datafile got restored. It was also switched to after restoration, see line 229. But then, in line 260, it was skipped for the recovery part. That sounds not like a big deal since the file / the tablespace is read only. But at the end you see, that neither the CREATE CONTROLFILE nor the CATALOG and SWITCH part contains my datafile 22. Strange. Let’s see, what we have at this point.
SYS @ AUX:CDB$ROOT:>select file#, name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf
3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf
6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf
20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
9 rows selected.
SYS @ AUX:CDB$ROOT:>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 _###_UNKNOWN_PDB_#_3 MOUNTED
SYS @ AUX:CDB$ROOT:>select status from v$instance;
STATUS
------------
MOUNTED
As you can see, no file 22 is there. So let’s go ahead and open the database read only just to check things.
SYS @ AUX:CDB$ROOT:>alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16008: indeterminate control file checkpoint
SYS @ AUX:CDB$ROOT:>!oerr ora 16008
16008, 00000, "indeterminate control file checkpoint"
// *Cause: The control file for the database being opened for read-only access
// was created using the CREATE CONTROLFILE statement. Therefore, a
// control file checkpoint could not be calculated and the database
// could not be opened for read-only access.
// *Action: First, open the database for read/write access. This creates
// a valid control file checkpoint. Then, reopen the database for
// read-only access.
Ok, that makes sense, RMAN did create a new controlfile. I’ll open the database read/write then and check again.
SYS @ AUX:CDB$ROOT:>alter database open resetlogs;
Database altered.
SYS @ AUX:CDB$ROOT:>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 ORCL19PDB2 MOUNTED
SYS @ AUX:CDB$ROOT:>select file#, name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqr3jxq_.dbf
3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqr3jwy_.dbf
4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqr3jy4_.dbf
5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqr400z_.dbf
6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqr4014_.dbf
7 /u01/app/oracle/product/db/19/dbs/MISSING00007
8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqr401g_.dbf
19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqr31r4_.dbf
20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqr31rf_.dbf
21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqr31rq_.dbf
22 /u01/app/oracle/product/db/19/dbs/MISSING00022
29 /u01/app/oracle/product/db/19/dbs/MISSING00029
30 /u01/app/oracle/product/db/19/dbs/MISSING00030
33 /u01/app/oracle/product/db/19/dbs/MISSING00033
14 rows selected.
As you can see, we have much more information after opening the database. This is because we now have access to the data dictionary too, not only to the controlfile. So we can see, that the database knows about file 22, but not where to find it. But I do know, it was written in the RMAN output and can also be derived from the paths of the other datafiles.
SYS @ AUX:CDB$ROOT:>!ls -l /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile
total 3490876
-rw-r-----. 1 oracle dba 555753472 Mar 21 08:29 o1_mf_sysaux_lzqr31rf_.dbf
-rw-r-----. 1 oracle dba 713039872 Mar 21 08:29 o1_mf_system_lzqr31r4_.dbf
-rw-r-----. 1 oracle dba 104865792 Mar 21 08:29 o1_mf_temp_lzqrh54t_.tmp
-rw-r-----. 1 oracle dba 157294592 Mar 21 08:29 o1_mf_undotbs1_lzqr31rq_.dbf
-rw-r-----. 1 oracle dba 1073750016 Mar 21 08:23 o1_mf_users_lzqr31r2_.dbf
The file is there, so let’s just use it. The database allows us to rename files, that should do the trick. The file itself is consistent since it was read/only in the source database.
SYS @ AUX:CDB$ROOT:>alter database rename file '/u01/app/oracle/product/db/19/dbs/MISSING00022' to '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf';
Database altered.
SYS @ AUX:CDB$ROOT:>alter pluggable database pdb1 open ;
Pluggable database altered.
SYS @ AUX:CDB$ROOT:>alter session set container=pdb1;
Session altered.
SYS @ AUX:CDB$ROOT:>select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00376: file 22 cannot be read at this time
ORA-01110: data file 22: '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf'
SYS @ AUX:CDB$ROOT:>alter database datafile 22 online;
alter database datafile 22 online
*
ERROR at line 1:
ORA-01190: control file or data file 22 is from before the last RESETLOGS
ORA-01110: data file 22: '/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqr31r2_.dbf'
Obviously I cannot use the restored file at all. But why is that? To figure this out, I dropped the auxiliary database and did another DUPLICATE, this time ommiting the NOOPEN option, just to see, what RMAN will do. I’ll only paste the relevant parts of this operation for the sake of clarity.
RMAN> duplicate database to aux pluggable database pdb1 skip tablespace pdb1:TBS_ORG_UTPLSQL until time 'sysdate-1' ;
Starting Duplicate Db at 2024-03-21 08:37:59
[...]
channel ORA_AUX_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_%u_.dbf
[...]
Starting recover at 2024-03-21 08:39:22
using channel ORA_AUX_DISK_1
datafile 22 not processed because file is read-only
[...]
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 200 M ,
GROUP 2 SIZE 200 M ,
GROUP 3 SIZE 200 M
DATAFILE
'/u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqs0qjg_.dbf',
'/u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqs16m6_.dbf',
'/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqrzyg9_.dbf'
CHARACTER SET AL32UTF8
[...]
contents of Memory Script:
{
sql clone 'alter system set "_system_trig_enabled"=FALSE';
Alter clone database open resetlogs;
sql clone 'alter system reset "_system_trig_enabled"';
}
executing Memory Script
sql statement: alter system set "_system_trig_enabled"=FALSE
database opened
sql statement: alter system reset "_system_trig_enabled"
Executing: drop pluggable database "ORCL19PDB2" including datafiles
Executing: drop pluggable database "PDB2" including datafiles
contents of Memory Script:
{
sql clone "alter pluggable database all open";
}
executing Memory Script
sql statement: alter pluggable database all open
contents of Memory Script:
{
catalog clone datafilecopy "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf",
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf",
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf";
switch clone datafile 22 to datafilecopy
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf";
switch clone datafile 29 to datafilecopy
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf";
switch clone datafile 30 to datafilecopy
"/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf";
#online the readonly tablespace
sql clone 'PDB1' "alter tablespace
USERS online";
#online the readonly tablespace
sql clone 'PDB1' "alter tablespace
TBS_LOB online";
#online the readonly tablespace
sql clone 'PDB1' "alter tablespace
TBS_DATA online";
}
executing Memory Script
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf RECID=7 STAMP=1164184865
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf RECID=8 STAMP=1164184865
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf RECID=9 STAMP=1164184865
datafile 22 switched to datafile copy
input datafile copy RECID=7 STAMP=1164184865 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf
datafile 29 switched to datafile copy
input datafile copy RECID=8 STAMP=1164184865 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=9 STAMP=1164184865 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf
sql statement: alter tablespace USERS online
sql statement: alter tablespace TBS_LOB online
sql statement: alter tablespace TBS_DATA online
Dropping offline and skipped tablespaces
Executing: alter database default tablespace system
Executing: drop tablespace "USERS" including contents cascade constraints
Executing: drop tablespace "TBS_ORG_UTPLSQL" including contents cascade constraints
Finished Duplicate Db at 2024-03-21 08:41:25
As you see, the restore and recover work the same way as with the NOOPEN option, no surprise here, I highlighted those lines. But then RMAN opens the database and after opening the database, it catalogs all the remaining files and switches the location of these files, see lines 61-64 and 82-83. The SWITCH command does something else than just renaming the file. And just to proof it works, here we go.
SYS @ AUX:CDB$ROOT:>select status from v$instance;
STATUS
------------
OPEN
SYS @ AUX:CDB$ROOT:>col name format a120
SYS @ AUX:CDB$ROOT:>set lines 200
SYS @ AUX:CDB$ROOT:>set pages 200
SYS @ AUX:CDB$ROOT:>select file#, name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqs0qjg_.dbf
3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqs0qjb_.dbf
4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqs0qjk_.dbf
5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqs16m6_.dbf
6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqs16mb_.dbf
8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqs16mf_.dbf
19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqrzyg9_.dbf
20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqrzygc_.dbf
21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqrzygf_.dbf
22 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqrzyg7_.dbf
29 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_data_lzqrzygh_.dbf
30 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_tbs_lob_lzqrzyg4_.dbf
12 rows selected.
SYS @ AUX:CDB$ROOT:>alter session set container=pdb1;
Session altered.
SYS @ AUX:CDB$ROOT:>select count(*) from hr.employees;
COUNT(*)
----------
107
But can we use the SWITCH trick also for the NOOPEN case? I dropped the auxiliary database again and did another duplicate with the NOOPEN option as in the first case. After this duplication, the situation is again like this.
YS @ AUX:CDB$ROOT:>select file#, name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/AUX/datafile/o1_mf_system_lzqskm2l_.dbf
3 /u01/app/oracle/oradata/AUX/datafile/o1_mf_sysaux_lzqskm2f_.dbf
4 /u01/app/oracle/oradata/AUX/datafile/o1_mf_undotbs1_lzqskm2p_.dbf
5 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_system_lzqsl251_.dbf
6 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_sysaux_lzqsl256_.dbf
8 /u01/app/oracle/oradata/AUX/ED7F22D941443E9AE053A024100AD0FB/datafile/o1_mf_undotbs1_lzqsl259_.dbf
19 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_lzqsk40g_.dbf
20 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_lzqsk40j_.dbf
21 /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_lzqsk40k_.dbf
9 rows selected.
SYS @ AUX:CDB$ROOT:>!ls -l /u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/
total 2441260
-rw-r-----. 1 oracle dba 555753472 Mar 21 08:49 o1_mf_sysaux_lzqsk40j_.dbf
-rw-r-----. 1 oracle dba 713039872 Mar 21 08:49 o1_mf_system_lzqsk40g_.dbf
-rw-r-----. 1 oracle dba 157294592 Mar 21 08:49 o1_mf_undotbs1_lzqsk40k_.dbf
-rw-r-----. 1 oracle dba 1073750016 Mar 21 08:47 o1_mf_users_lzqsk40b_.dbf
No file 22 in the views, but at least it got restored again. So let’s try to open the auxiliary database again and use the SWITCH command this time. Note, that the command needs to put into a RUN-block.
[oracle@vm160 ~]$ export ORACLE_SID=aux
[oracle@vm160 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 21 09:59:32 2024
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AUX (DBID=2017441707, not open)
RMAN> alter database open resetlogs;
Statement processed
RMAN> run {
2> catalog datafilecopy "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf";
3> switch datafile 22 to datafilecopy "/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf";
4> }
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf RECID=7 STAMP=1164189777
datafile 22 switched to datafile copy
input datafile copy RECID=7 STAMP=1164189777 file name=/u01/app/oracle/oradata/AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_lzqsk40b_.dbf
RMAN> sql "alter pluggable database pdb1 open";
sql statement: alter pluggable database pdb1 open
RMAN> sql 'PDB1' "alter tablespace users online";
sql statement: alter tablespace users online
Looks promising so far. But am I able to query some data from this file now?
[oracle@vm160 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 21 10:04:55 2024
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SYS @ AUX:CDB$ROOT:>alter session set container=pdb1;
Session altered.
SYS @ AUX:CDB$ROOT:>select count(*) from hr.employees;
COUNT(*)
----------
107
Yes, I can! Finally! So what did I learn? RMAN duplication skips read only files during the recover operation which is totally fine. It adds those files *after* opening the database. If I open the database myself, I have to find the restored files myself and use the RMAN SWITCH command to make them part of the restored database, An “alter database rename file” is not sufficient. Good to know.
Marco's DBA Blog 