12C_PDB的不完整恢復
官方文件
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2718111/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 如何恢復SSD NVME固態硬碟的資料恢復硬碟資料恢復
- bitlocker如何恢復金鑰 bitlocker恢復金鑰的方法
- 照片恢復軟體是如何恢復數位相機照片的？
- Prometheus 告警恢復時，怎麼獲取恢復時的值？Prometheus
- 怎樣用恢復驅動器來恢復win10 使用恢復驅動器恢復win10系統的步驟Win10
- 資料恢復：AMDU資料抽取恢復資料恢復
- postgreSQL 恢復至故障點 精準恢復SQL
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 請恢復我的帖子
- 教你一招，告警恢復時如何拿到恢復時的值？
- Vsan資料恢復—Vsan資料恢復案例資料恢復
- MySQL 非常規恢復與物理備份恢復MySql
- 【Vsan資料恢復】Vsan資料恢復案例資料恢復
- PostgreSQL啟動恢復期間，恢復到的時間線的確定SQL
- 資料底層損壞的恢復方法—拼碎片恢復資料
- 教你一招，告警恢復時如何拿到恢復時的值？ 合
- 【硬碟資料恢復】加電有異響的硬碟資料恢復硬碟資料恢復
- word怎麼恢復儲存前的檔案，word檔案恢復
- 檔案替換後怎麼恢復，恢復被覆蓋的檔案
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- SeSparse資料恢復方案研究及恢復方法演示資料恢復
- RAC備份恢復之Voting備份與恢復
- DB的備份與恢復
- MySQL資料庫的恢復MySql資料庫
- GitLab的備份與恢復Gitlab
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- NBU恢復oracleOracle
- RMAN增量恢復
- PRemiere介面怎麼恢復？PR介面功能恢復預設狀態的技巧REM
- 【轉載】TortoiseSVN怎麼恢復到以前版本-恢復到以前版本的方法
- 【NetApp資料恢復案例】針對NetApp誤刪除資料的恢復APP資料恢復
- 剪下後的檔案可以恢復嗎？恢復剪下檔案怎麼辦？
- word自動儲存的檔案怎麼恢復，word檔案恢復
- 讀資料保護：工作負載的可恢復性10恢復方式負載
- 【VSAN資料恢復】VSAN儲存資料恢復案例資料恢復
- NAS陣列恢復資料資料恢復開盤陣列資料恢復
- RMAN備份恢復典型案例——異機恢復未知DBID
- 【伺服器資料恢復】Vsan資料恢復案例伺服器資料恢復