官方文档https://docs.oracle.com/database/121/BRADV/rcmflash.htm#BRADV640
point-in-time recovery (PITR)
When you perform DBPITR of a PDB, all the data files for this PDB are recovered in-place. However, to recover the PDB to the specified target time, RMAN also needs the UNDO tablespace as it existed at the target time. Because the UNDO tablespace is shared by all PDBs, it cannot be recovered in-place. RMAN restores the UNDO, SYSTEM, and SYSAUX tablespaces in the root to an auxiliary database and then uses the undo information to recover the PDB to the target time.If a fast recovery is configured, Oracle Database uses it as the auxiliary destination. If the fast recovery area is not configured, then you must use the AUXILIARY DESTINATION clause to specify the location used for auxiliary database files. Ensure that there is sufficient space in the fast recovery area to restore the root tablespaces and the undo tablespace. If the fast recovery area does not have the required space, use an alternate location by specifying the AUXILIARY DESTINATION clause.
当您对一个PDB执行DBPITR时,该PDB的所有数据文件将被就地恢复。但是,为了将PDB恢复到指定的目标时间,RMAN还需要在目标时间内存在的UNDO表空间。因为UNDO表空间是由所有pdb共享的,所以不能就地恢复。RMAN将根表中的UNDO、SYSTEM和SYSAUX表空间恢复到一个辅助数据库,然后使用UNDO信息将PDB恢复到目标时间。如果配置了快速恢复,Oracle数据库将其作为辅助目的地。如果没有配置快速恢复区域,则必须使用AUXILIARY DESTINATION子句指定用于辅助数据库文件的位置。确保快速恢复区域中有足够的空间来恢复根表空间和undo表空间。如果快速恢复区域没有所需的空间,则通过指定辅助目的地子句使用替代位置。
1、只有单个PDB的备份,没有整个CDB的备份,则无法执行对单个PDB的不完全恢复
2、某个PDB进行restore和recover后,这个PDB数据文件的SCN都不会变,这个PDB执行open resetlogs后,这个PDB数据文件的SCN和其他容器的数据文件的SCN不一样
3、单个PDB执行了不完全恢复后,其他容器的数据不受任何影响
4、CDB里面每个PDB的SCN可以各不相同,比如PDB1和PDB2的SCN不一样,PDB1和CDB$ROOT的SCN不一样
5、PDB不完全恢复时如果没有fast recovery area,则必须指定AUXILIARY DESTINATION
实验使用的OS和DB版本
OS版本Red Hat Enterprise Linux Server release 6.8 (Santiago)
DB版本Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
1、只有PDB的备份,没有CDB的备份,则无法执行对PDB的不完全恢复
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> col file_name format a60
SQL> set linesize 200
SQL> select file_id,con_id,file_name from cdb_data_files order by 1;
FILE_ID CON_ID FILE_NAME
1 1 /u01/app/oracle/oradata/CDB/system01.dbf
3 1 /u01/app/oracle/oradata/CDB/sysaux01.dbf
4 1 /u01/app/oracle/oradata/CDB/undotbs01.dbf
6 1 /u01/app/oracle/oradata/CDB/users01.dbf
8 3 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
9 3 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
10 3 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
11 3 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
RMAN> backup pluggable database PDB format ‘/home/oracle/rman/full%U’ plus archivelog format ‘/home/oracle/rman/arch%U’ delete input;
SQL> create table cdb1 as select * from cdb_users;
SQL> alter session set container=PDB;
SQL> create table pdb1 as select * from dba_users;
SQL> alter session set container=CDB$ROOT;
SQL> alter system archive log current;–29号日志
SQL> create table cdb2 as select * from cdb_users;
SQL> alter session set container=PDB;
SQL> create table pdb2 as select * from dba_users;
SQL> alter session set container=CDB$ROOT;
SQL> alter system archive log current;–30号日志
SQL> create table cdb3 as select * from cdb_users;
SQL> alter session set container=PDB;
SQL> create table pdb3 as select * from dba_users;
SQL> alter session set container=CDB$ROOT;
SQL> alter system archive log current;–31号日志
SQL> shutdown immediate;
[oracle@12C CDB]$ cd /u01/app/oracle/oradata/CDB/PDB
[oracle@12C PDB]$ ll
total 2211424
-rw-r----- 1 oracle oinstall 1304174592 Sep 3 17:38 example01.dbf
-rw-r----- 1 oracle oinstall 206577664 Jun 7 12:00 PDB_temp012020-06-07_11-58-40-AM.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 3 17:38 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle oinstall 618668032 Sep 3 17:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 272637952 Sep 3 17:38 system01.dbf
[oracle@12C PDB]$ rm -rf *
[oracle@12C PDB]$ ll
total 0
SQL>startup
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> alter pluggable database PDB open;
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: ‘/u01/app/oracle/oradata/CDB/PDB/example01.dbf’
rman target /
RMAN> restore pluggable database PDB;
RMAN> recover pluggable database PDB until sequence 30;
RMAN-05107: AUXILIARY DESTINATION option is not specified
RMAN> recover pluggable database PDB until sequence 30 AUXILIARY DESTINATION=’/home/oracle/recover’;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/07/2020 13:58:26
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
2、有整个CDB的备份,可以执行对单个PDB的不完全恢复
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> col file_name format a60
SQL> set linesize 200
SQL> select file_id,con_id,file_name from cdb_data_files order by 1;
FILE_ID CON_ID FILE_NAME
1 1 /u01/app/oracle/oradata/CDB/system01.dbf
3 1 /u01/app/oracle/oradata/CDB/sysaux01.dbf
4 1 /u01/app/oracle/oradata/CDB/undotbs01.dbf
6 1 /u01/app/oracle/oradata/CDB/users01.dbf
8 3 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
9 3 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
10 3 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
11 3 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
RMAN> backup database format ‘/home/oracle/rman/full%U’ plus archivelog format ‘/home/oracle/rman/arch%U’ delete input;
Starting backup at 07-SEP-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=1 STAMP=1050502803
channel ORA_DISK_1: starting piece 1 at 07-SEP-20
channel ORA_DISK_1: finished piece 1 at 07-SEP-20
piece handle=/home/oracle/rman/arch01v9qpkj_1_1 tag=TAG20200907T142003 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/arch/1_27_1042457743.dbf RECID=1 STAMP=1050502803
Finished backup at 07-SEP-20
Starting backup at 07-SEP-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/CDB/PDB/example01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/CDB/PDB/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-20
channel ORA_DISK_1: finished piece 1 at 07-SEP-20
piece handle=/home/oracle/rman/full02v9qpkl_1_1 tag=TAG20200907T142004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/CDB/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/CDB/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/CDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-20
channel ORA_DISK_1: finished piece 1 at 07-SEP-20
piece handle=/home/oracle/rman/full03v9qplp_1_1 tag=TAG20200907T142004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-20
channel ORA_DISK_1: finished piece 1 at 07-SEP-20
piece handle=/home/oracle/rman/full04v9qpm8_1_1 tag=TAG20200907T142004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 07-SEP-20
Starting backup at 07-SEP-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=2 STAMP=1050502871
channel ORA_DISK_1: starting piece 1 at 07-SEP-20
channel ORA_DISK_1: finished piece 1 at 07-SEP-20
piece handle=/home/oracle/rman/arch05v9qpmn_1_1 tag=TAG20200907T142111 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/arch/1_28_1042457743.dbf RECID=2 STAMP=1050502871
Finished backup at 07-SEP-20
Starting Control File and SPFILE Autobackup at 07-SEP-20
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-2159061262-20200907-00 comment=NONE
Finished Control File and SPFILE Autobackup at 07-SEP-20
SQL> create table cdb1 as select * from cdb_users;
SQL> alter session set container=PDB;
SQL> create table pdb1 as select * from dba_users;
SQL> alter session set container=CDB$ROOT;
SQL> alter system archive log current;–29号日志
SQL> create table cdb2 as select * from cdb_users;
SQL> alter session set container=PDB;
SQL> create table pdb2 as select * from dba_users;
SQL> alter session set container=CDB$ROOT;
SQL> alter system archive log current;–30号日志
SQL> create table cdb3 as select * from cdb_users;
SQL> alter session set container=PDB;
SQL> create table pdb3 as select * from dba_users;
SQL> alter session set container=CDB$ROOT;
SQL> alter system archive log current;–31号日志
SQL> col CHECKPOINT_CHANGE# format 99999999999
SQL> col name format a60
SQL> set linesize 150
SQL> select CHECKPOINT_CHANGE#,CON_ID,FILE#,NAME from V$DATAFILE where con_id<>2 order by 3;
CHECKPOINT_CHANGE# CON_ID FILE# NAME
2237821 1 1 /u01/app/oracle/oradata/CDB/system01.dbf
2237821 1 3 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2237821 1 4 /u01/app/oracle/oradata/CDB/undotbs01.dbf
2237821 1 6 /u01/app/oracle/oradata/CDB/users01.dbf
2237821 3 8 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
2237821 3 9 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
2237821 3 10 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
2237821 3 11 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
SQL> shutdown immediate;
[oracle@12C CDB]$ cd /u01/app/oracle/oradata/CDB/PDB
[oracle@12C PDB]$ ll
total 2211424
-rw-r----- 1 oracle oinstall 1304174592 Sep 3 17:38 example01.dbf
-rw-r----- 1 oracle oinstall 206577664 Jun 7 12:00 PDB_temp012020-06-07_11-58-40-AM.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 3 17:38 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle oinstall 618668032 Sep 3 17:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 272637952 Sep 3 17:38 system01.dbf
[oracle@12C PDB]$ rm -rf *
[oracle@12C PDB]$ ll
total 0
SQL>startup
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
SQL> alter pluggable database PDB open;
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: ‘/u01/app/oracle/oradata/CDB/PDB/example01.dbf’
SQL> col CHECKPOINT_CHANGE# format 99999999999
SQL> col name format a60
SQL> set linesize 150
SQL> select CHECKPOINT_CHANGE#,CON_ID,FILE#,NAME from V$DATAFILE where con_id<>2 order by 3;
CHECKPOINT_CHANGE# CON_ID FILE# NAME
2238760 1 1 /u01/app/oracle/oradata/CDB/system01.dbf
2238760 1 3 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2238760 1 4 /u01/app/oracle/oradata/CDB/undotbs01.dbf
2238760 1 6 /u01/app/oracle/oradata/CDB/users01.dbf
2238743 3 8 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
2238743 3 9 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
2238743 3 10 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
2238743 3 11 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
rman target /
RMAN> restore pluggable database PDB;
Starting restore at 07-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
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 00008 to /u01/app/oracle/oradata/CDB/PDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/CDB/PDB/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/full02v9qpkl_1_1
channel ORA_DISK_1: piece handle=/home/oracle/rman/full02v9qpkl_1_1 tag=TAG20200907T142004
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 07-SEP-20
SQL> col CHECKPOINT_CHANGE# format 99999999999
SQL> col name format a60
SQL> set linesize 150
SQL> select CHECKPOINT_CHANGE#,CON_ID,FILE#,NAME from V$DATAFILE where con_id<>2 order by 3;
CHECKPOINT_CHANGE# CON_ID FILE# NAME
2238760 1 1 /u01/app/oracle/oradata/CDB/system01.dbf
2238760 1 3 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2238760 1 4 /u01/app/oracle/oradata/CDB/undotbs01.dbf
2238760 1 6 /u01/app/oracle/oradata/CDB/users01.dbf
2238743 3 8 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
2238743 3 9 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
2238743 3 10 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
2238743 3 11 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
RMAN> recover pluggable database PDB until sequence 30;
RMAN-05107: AUXILIARY DESTINATION option is not specified
[oracle@12C ~]$ ll /home/oracle/recover
total 0
RMAN> recover pluggable database PDB until sequence 30 AUXILIARY DESTINATION=’/home/oracle/recover’;
Starting recover at 07-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID=‘vblB’
initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=vblB_pitr_PDB_CDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1248M
processes=200
db_create_file_dest=/home/oracle/recover
log_archive_dest_1=‘location=/home/oracle/recover’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDB
Oracle instance started
Total System Global Area 1308622848 bytes
Fixed Size 2924064 bytes
Variable Size 352322016 bytes
Database Buffers 939524096 bytes
Redo Buffers 13852672 bytes
Automatic instance created
contents of Memory Script:
{
set requested point in time
set until logseq 30 thread 1;
restore the controlfile
restore clone controlfile;
mount the controlfile
sql clone ‘alter database mount clone database’;
}
executing Memory Script
executing command: SET until clause
Starting restore at 07-SEP-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 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/product/12.1.0/db_1/dbs/c-2159061262-20200907-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-2159061262-20200907-00 tag=TAG20200907T142112
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/recover/CDB/controlfile/o1_mf_hocnyxfn_.ctl
Finished restore at 07-SEP-20
sql statement: alter database mount clone database
contents of Memory Script:
{
set requested point in time
set until logseq 30 thread 1;
switch to valid datafilecopies
switch clone datafile 8 to datafilecopy
“/u01/app/oracle/oradata/CDB/PDB/system01.dbf”;
switch clone datafile 9 to datafilecopy
“/u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf”;
switch clone datafile 10 to datafilecopy
“/u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf”;
switch clone datafile 11 to datafilecopy
“/u01/app/oracle/oradata/CDB/PDB/example01.dbf”;
set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 6 to new;
restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=1050503523 file name=/u01/app/oracle/oradata/CDB/PDB/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1050503523 file name=/u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1050503523 file name=/u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=1050503523 file name=/u01/app/oracle/oradata/CDB/PDB/example01.dbf
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-SEP-20
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 /home/oracle/recover/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/recover/CDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/recover/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/recover/CDB/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/rman/full03v9qplp_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/rman/full03v9qplp_1_1 tag=TAG20200907T142004
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-SEP-20
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=1050503548 file name=/home/oracle/recover/CDB/datafile/o1_mf_system_hocnz339_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1050503548 file name=/home/oracle/recover/CDB/datafile/o1_mf_undotbs1_hocnz33n_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=1050503548 file name=/home/oracle/recover/CDB/datafile/o1_mf_sysaux_hocnz33l_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=1050503548 file name=/home/oracle/recover/CDB/datafile/o1_mf_users_hocnz344_.dbf
contents of Memory Script:
{
set requested point in time
set until logseq 30 thread 1;
online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 4 online”;
sql clone “alter database datafile 3 online”;
sql clone ‘PDB’ "alter database datafile
8 online";
sql clone ‘PDB’ "alter database datafile
9 online";
sql clone ‘PDB’ "alter database datafile
10 online";
sql clone ‘PDB’ "alter database datafile
11 online";
sql clone “alter database datafile 6 online”;
recover pdb
recover clone database tablespace “SYSTEM”, “UNDOTBS1”, “SYSAUX”, “USERS” pluggable database
‘PDB’ delete archivelog;
sql clone ‘alter database open read only’;
plsql <<<begin
add_dropped_ts;
end; >>>;
plsql <<<begin
save_pdb_clean_scn;
end; >>>;
shutdown clone before import
shutdown clone abort
plsql <<<begin
pdbpitr_inspect(pdbname => ‘PDB’);
end; >>>;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 10 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 6 online
Starting recover at 07-SEP-20
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 29 is already on disk as file /home/oracle/arch/1_29_1042457743.dbf
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=28
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/rman/arch05v9qpmn_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/rman/arch05v9qpmn_1_1 tag=TAG20200907T142111
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=/home/oracle/recover/1_28_1042457743.dbf thread=1 sequence=28
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/recover/1_28_1042457743.dbf RECID=3 STAMP=1050503548
archived log file name=/home/oracle/arch/1_29_1042457743.dbf thread=1 sequence=29
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-SEP-20
sql statement: alter database open read only
Oracle instance shut down
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/recover/CDB/datafile/o1_mf_sysaux_hocnz33l_.dbf deleted
auxiliary instance file /home/oracle/recover/CDB/controlfile/o1_mf_hocnyxfn_.ctl deleted
Finished recover at 07-SEP-20
SQL> col CHECKPOINT_CHANGE# format 99999999999
SQL> col name format a60
SQL> set linesize 150
SQL> select CHECKPOINT_CHANGE#,CON_ID,FILE#,NAME from V$DATAFILE where con_id<>2 order by 3;
CHECKPOINT_CHANGE# CON_ID FILE# NAME
2238760 1 1 /u01/app/oracle/oradata/CDB/system01.dbf
2238760 1 3 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2238760 1 4 /u01/app/oracle/oradata/CDB/undotbs01.dbf
2238760 1 6 /u01/app/oracle/oradata/CDB/users01.dbf
2238743 3 8 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
2238743 3 9 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
2238743 3 10 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
2238743 3 11 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
[oracle@12C ~]$ ll /home/oracle/recover
total 4
drwxr-x— 4 oracle oinstall 4096 Sep 3 17:43 CDB
[oracle@12C ~]$ ll /home/oracle/recover/CDB
total 8
drwxr-x— 2 oracle oinstall 4096 Sep 3 17:44 controlfile
drwxr-x— 2 oracle oinstall 4096 Sep 3 17:44 datafile
[oracle@12C ~]$ ll /home/oracle/recover/CDB/datafile/
total 1085472
-rw-r----- 1 oracle oinstall 828383232 Sep 7 14:32 o1_mf_system_hocnz339_.dbf
-rw-r----- 1 oracle oinstall 277880832 Sep 7 14:32 o1_mf_undotbs1_hocnz33n_.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 7 14:32 o1_mf_users_hocnz344_.dbf
[oracle@12C ~]$ ll /home/oracle/recover/CDB/controlfile/
total 0
SQL> alter pluggable database PDB open;
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: ‘/u01/app/oracle/oradata/CDB/PDB/example01.dbf’
SQL> alter pluggable database PDB open resetlogs;
Pluggable database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL> col CHECKPOINT_CHANGE# format 99999999999
SQL> col name format a60
SQL> set linesize 150
SQL> select CHECKPOINT_CHANGE#,CON_ID,FILE#,NAME from V$DATAFILE where con_id<>2 order by 3;
CHECKPOINT_CHANGE# CON_ID FILE# NAME
2238760 1 1 /u01/app/oracle/oradata/CDB/system01.dbf
2238760 1 3 /u01/app/oracle/oradata/CDB/sysaux01.dbf
2238760 1 4 /u01/app/oracle/oradata/CDB/undotbs01.dbf
2238760 1 6 /u01/app/oracle/oradata/CDB/users01.dbf
2240791 3 8 /u01/app/oracle/oradata/CDB/PDB/system01.dbf
2240791 3 9 /u01/app/oracle/oradata/CDB/PDB/sysaux01.dbf
2240791 3 10 /u01/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
2240791 3 11 /u01/app/oracle/oradata/CDB/PDB/example01.dbf
SQL> select count(*) from CDB3;
COUNT(*)
78
SQL> alter session set container=PDB;
SQL> select count(*) from PDB1;
COUNT(*)
43
SQL> select count(*) from PDB2;
ORA-00942: table or view does not exist