12C-PDB基于时间点恢复

RMAN>alter  pluggable database zaki1 close immediate;

RMAN> run{

2> set until time "TO_DATE('2016-10-31 12:03:08','yyyy-mm-dd hh24:mi:ss')";
3> restore pluggable database zaki1;
4> recover pluggable database zaki1 ;
5> }
executing command: SET until clause
Starting restore at 31-OCT-16
using channel ORA_DISK_1

skipping datafile 9; already restored to file /oracle/db/oradata/orcl/zaki1/zaki1_users01.dbf
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 00007 to /oracle/db/oradata/orcl/zaki1/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oracle/db/oradata/orcl/zaki1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00018 to /oracle/db/oradata/orcl/zaki1/gdb_data02.dbf
channel ORA_DISK_1: reading from backup piece /app/backup/zaki1_02rjglc9_1_120161028
channel ORA_DISK_1: piece handle=/app/backup/zaki1_02rjglc9_1_120161028 tag=TAG20161028T160437
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 31-OCT-16

Starting recover at 31-OCT-16
using channel ORA_DISK_1
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='vsfu'

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=vsfu_pitr_zaki1_ORCL
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle/db
_system_trig_enabled=FALSE
sga_target=2352M
processes=200
#No auxiliary destination in use
enable_pluggable_database=true
_clone_one_pdb_recovery=true
control_files=/oracle/db/fast_recovery_area/ORCL/controlfile/o1_mf_d1fmv34p_.ctl
#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area    2466250752 bytes

Fixed Size                     2927384 bytes
Variable Size                553649384 bytes
Database Buffers            1895825408 bytes
Redo Buffers                  13848576 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2016-10-31 12:03:08','yyyy-mm-dd hh24:mi:ss')";
# 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 31-OCT-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 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 /oracle/db/fast_recovery_area/ORCL/autobackup/2016_10_28/o1_mf_s_926440264_d16369h7_.bkp
channel ORA_AUX_DISK_1: piece handle=/oracle/db/fast_recovery_area/ORCL/autobackup/2016_10_28/o1_mf_s_926440264_d16369h7_.bkp tag=TAG20161028T163104
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/db/fast_recovery_area/ORCL/controlfile/o1_mf_d1fmv34p_.ctl
Finished restore at 31-OCT-16

sql statement: alter database mount clone database

Removing automatic instance
shutting down automatic instance 
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/31/2016 12:58:59
ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 10737418240 bytes disk space from 5048893440 limit 

--进行recover操作时会启动辅助实例,辅助实例则会使用到fast recovery area,如果fast recovery area的空间不够了,recover时会报错。后来手动使用AUXILIARY DESTINATION子句来为辅助实例指定一个临时的位置,就没有问题了。

RMAN> 

RMAN> run{
2>  set until time "TO_DATE('2016-10-31 12:03:08','yyyy-mm-dd hh24:mi:ss')";
3>  restore pluggable database zaki1;
4>  recover pluggable database zaki1 auxiliary destination '/app/backup/tmp';
5>  }

executing command: SET until clause

Starting restore at 31-OCT-16
using channel ORA_DISK_1

skipping datafile 7; already restored to file /oracle/db/oradata/orcl/zaki1/system01.dbf
skipping datafile 8; already restored to file /oracle/db/oradata/orcl/zaki1/sysaux01.dbf
skipping datafile 9; already restored to file /oracle/db/oradata/orcl/zaki1/zaki1_users01.dbf
skipping datafile 18; already restored to file /oracle/db/oradata/orcl/zaki1/gdb_data02.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 31-OCT-16

Starting recover at 31-OCT-16
using channel ORA_DISK_1
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='Bckv'

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=Bckv_pitr_zaki1_ORCL
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle/db
_system_trig_enabled=FALSE
sga_target=2352M
processes=200
db_create_file_dest=/app/backup/tmp
log_archive_dest_1='location=/app/backup/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area    2466250752 bytes

Fixed Size                     2927384 bytes
Variable Size                553649384 bytes
Database Buffers            1895825408 bytes
Redo Buffers                  13848576 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2016-10-31 12:03:08','yyyy-mm-dd hh24:mi:ss')";
# 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 31-OCT-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=173 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 /oracle/db/fast_recovery_area/ORCL/autobackup/2016_10_28/o1_mf_s_926440264_d16369h7_.bkp
channel ORA_AUX_DISK_1: piece handle=/oracle/db/fast_recovery_area/ORCL/autobackup/2016_10_28/o1_mf_s_926440264_d16369h7_.bkp tag=TAG20161028T163104
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/app/backup/tmp/ORCL/controlfile/o1_mf_d1fpyqvq_.ctl
Finished restore at 31-OCT-16

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2016-10-31 12:03:08','yyyy-mm-dd hh24:mi:ss')";
# switch to valid datafilecopies
switch clone datafile  7 to datafilecopy 
 "/oracle/db/oradata/orcl/zaki1/system01.dbf";
switch clone datafile  8 to datafilecopy 
 "/oracle/db/oradata/orcl/zaki1/sysaux01.dbf";
switch clone datafile  9 to datafilecopy 
 "/oracle/db/oradata/orcl/zaki1/zaki1_users01.dbf";
switch clone datafile  18 to datafilecopy 
 "/oracle/db/oradata/orcl/zaki1/gdb_data02.dbf";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
set newname for clone datafile  17 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 3, 6, 17;
 
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 7 switched to datafile copy
input datafile copy RECID=1 STAMP=926689888 file name=/oracle/db/oradata/orcl/zaki1/system01.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=2 STAMP=926689888 file name=/oracle/db/oradata/orcl/zaki1/sysaux01.dbf

datafile 9 switched to datafile copy
input datafile copy RECID=3 STAMP=926689888 file name=/oracle/db/oradata/orcl/zaki1/zaki1_users01.dbf

datafile 18 switched to datafile copy
input datafile copy RECID=4 STAMP=926689888 file name=/oracle/db/oradata/orcl/zaki1/gdb_data02.dbf

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 31-OCT-16
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 /app/backup/tmp/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /app/backup/tmp/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /app/backup/tmp/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /app/backup/tmp/ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00017 to /app/backup/tmp/ORCL/datafile/o1_mf_gdb_data_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /app/backup/zaki1_01rjgl8l_1_120161028
channel ORA_AUX_DISK_1: piece handle=/app/backup/zaki1_01rjgl8l_1_120161028 tag=TAG20161028T160437
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:12:45
Finished restore at 31-OCT-16

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=926690655 file name=/app/backup/tmp/ORCL/datafile/o1_mf_system_d1fpz1nr_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=926690655 file name=/app/backup/tmp/ORCL/datafile/o1_mf_undotbs1_d1fpz1mz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=926690655 file name=/app/backup/tmp/ORCL/datafile/o1_mf_sysaux_d1fpz1m9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=926690655 file name=/app/backup/tmp/ORCL/datafile/o1_mf_users_d1fpz1om_.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=14 STAMP=926690655 file name=/app/backup/tmp/ORCL/datafile/o1_mf_gdb_data_d1fpz1lg_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2016-10-31 12:03:08','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone 'ZAKI1' "alter database datafile 
 7 online";
sql clone 'ZAKI1' "alter database datafile 
 8 online";
sql clone 'ZAKI1' "alter database datafile 
 9 online";
sql clone 'ZAKI1' "alter database datafile 
 18 online";
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  17 online";
# recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "GDB_DATA" pluggable database 
 'ZAKI1'   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 =>  'ZAKI1');
end; >>>;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  7 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  18 online

sql statement: alter database datafile  6 online

sql statement: alter database datafile  17 online

Starting recover at 31-OCT-16
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 814 is already on disk as file /app/archivelog/1_814_918752760.dbf
archived log for thread 1 with sequence 815 is already on disk as file /app/archivelog/1_815_918752760.dbf
archived log for thread 1 with sequence 816 is already on disk as file /app/archivelog/1_816_918752760.dbf
archived log for thread 1 with sequence 817 is already on disk as file /app/archivelog/1_817_918752760.dbf
archived log for thread 1 with sequence 818 is already on disk as file /app/archivelog/1_818_918752760.dbf
archived log for thread 1 with sequence 819 is already on disk as file /app/archivelog/1_819_918752760.dbf
archived log for thread 1 with sequence 820 is already on disk as file /app/archivelog/1_820_918752760.dbf
archived log for thread 1 with sequence 821 is already on disk as file /app/archivelog/1_821_918752760.dbf
archived log for thread 1 with sequence 822 is already on disk as file /app/archivelog/1_822_918752760.dbf
archived log for thread 1 with sequence 823 is already on disk as file /app/archivelog/1_823_918752760.dbf
archived log for thread 1 with sequence 824 is already on disk as file /app/archivelog/1_824_918752760.dbf
archived log for thread 1 with sequence 825 is already on disk as file /app/archivelog/1_825_918752760.dbf
archived log for thread 1 with sequence 826 is already on disk as file /app/archivelog/1_826_918752760.dbf
archived log for thread 1 with sequence 827 is already on disk as file /app/archivelog/1_827_918752760.dbf
archived log for thread 1 with sequence 828 is already on disk as file /app/archivelog/1_828_918752760.dbf
archived log for thread 1 with sequence 829 is already on disk as file /app/archivelog/1_829_918752760.dbf
archived log for thread 1 with sequence 830 is already on disk as file /app/archivelog/1_830_918752760.dbf
archived log for thread 1 with sequence 831 is already on disk as file /app/archivelog/1_831_918752760.dbf
archived log for thread 1 with sequence 832 is already on disk as file /app/archivelog/1_832_918752760.dbf
archived log for thread 1 with sequence 833 is already on disk as file /app/archivelog/1_833_918752760.dbf
archived log for thread 1 with sequence 834 is already on disk as file /app/archivelog/1_834_918752760.dbf
archived log for thread 1 with sequence 835 is already on disk as file /app/archivelog/1_835_918752760.dbf
archived log for thread 1 with sequence 836 is already on disk as file /app/archivelog/1_836_918752760.dbf
archived log for thread 1 with sequence 837 is already on disk as file /app/archivelog/1_837_918752760.dbf
archived log for thread 1 with sequence 838 is already on disk as file /app/archivelog/1_838_918752760.dbf
archived log for thread 1 with sequence 839 is already on disk as file /app/archivelog/1_839_918752760.dbf
archived log for thread 1 with sequence 840 is already on disk as file /app/archivelog/1_840_918752760.dbf
archived log file name=/app/archivelog/1_814_918752760.dbf thread=1 sequence=814
archived log file name=/app/archivelog/1_815_918752760.dbf thread=1 sequence=815
archived log file name=/app/archivelog/1_816_918752760.dbf thread=1 sequence=816
archived log file name=/app/archivelog/1_817_918752760.dbf thread=1 sequence=817
archived log file name=/app/archivelog/1_818_918752760.dbf thread=1 sequence=818
archived log file name=/app/archivelog/1_819_918752760.dbf thread=1 sequence=819
archived log file name=/app/archivelog/1_820_918752760.dbf thread=1 sequence=820
archived log file name=/app/archivelog/1_821_918752760.dbf thread=1 sequence=821
archived log file name=/app/archivelog/1_822_918752760.dbf thread=1 sequence=822
archived log file name=/app/archivelog/1_823_918752760.dbf thread=1 sequence=823
archived log file name=/app/archivelog/1_824_918752760.dbf thread=1 sequence=824
archived log file name=/app/archivelog/1_825_918752760.dbf thread=1 sequence=825
archived log file name=/app/archivelog/1_826_918752760.dbf thread=1 sequence=826
archived log file name=/app/archivelog/1_827_918752760.dbf thread=1 sequence=827
archived log file name=/app/archivelog/1_828_918752760.dbf thread=1 sequence=828
archived log file name=/app/archivelog/1_829_918752760.dbf thread=1 sequence=829
archived log file name=/app/archivelog/1_830_918752760.dbf thread=1 sequence=830
archived log file name=/app/archivelog/1_831_918752760.dbf thread=1 sequence=831
archived log file name=/app/archivelog/1_832_918752760.dbf thread=1 sequence=832
archived log file name=/app/archivelog/1_833_918752760.dbf thread=1 sequence=833
archived log file name=/app/archivelog/1_834_918752760.dbf thread=1 sequence=834
archived log file name=/app/archivelog/1_835_918752760.dbf thread=1 sequence=835
archived log file name=/app/archivelog/1_836_918752760.dbf thread=1 sequence=836
archived log file name=/app/archivelog/1_837_918752760.dbf thread=1 sequence=837
archived log file name=/app/archivelog/1_838_918752760.dbf thread=1 sequence=838
archived log file name=/app/archivelog/1_839_918752760.dbf thread=1 sequence=839
archived log file name=/app/archivelog/1_840_918752760.dbf thread=1 sequence=840
media recovery complete, elapsed time: 00:06:19
Finished recover at 31-OCT-16
sql statement: alter database open read only
Oracle instance shut down

Removing automatic instance
Automatic instance removed
auxiliary instance file /app/backup/tmp/ORCL/datafile/o1_mf_sysaux_d1fpz1m9_.dbf deleted
auxiliary instance file /app/backup/tmp/ORCL/controlfile/o1_mf_d1fpyqvq_.ctl deleted
Finished recover at 31-OCT-16

RMAN> alter pluggable database zaki1 open resetlogs;

Statement processed