18.5 执行数据库的 DBPITR恢复

RMAN DBPITR使用备份文件，恢复数据库到一个制定的时间段，可以通过recover 命令指定SCN, time, log sequence number, or restore point。oracle建议优先使用restore points方法。

环境要求：

1，数据库必须运行在归档模式。

2，必须有数据库所有文件的备份在执行SCN时间点之前的备份，归档日志必须存在。

执行恢复操作：

1，确定相关SCN.

2，数据库启动到mount

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

3,恢复数据库到制定SCN

RUN
{ 
  SET UNTIL SCN 1000;    
  RESTORE DATABASE;
  RECOVER DATABASE;
}

在set until可以指定时间，日志或者还原点名称:

SET UNTIL TIME 'Nov 15 2013 09:00:00';
SET UNTIL SEQUENCE 9923;  
SET TO RESTORE POINT before_update;

启动数据库：

ALTER DATABASE OPEN RESETLOGS;

To perform point-in-time recovery of a whole CDB:、

恢复整个CDB:

1,确定SCN

2,cdb启动到mount状态

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

3，执行不完全恢复

RUN
{ 
  SET UNTIL SCN 1000;    
  RESTORE DATABASE;
  RECOVER DATABASE;
}

4，启动数据库cdb,pdb.

ALTER DATABASE OPEN RESETLOGS;

ALTER PLUGGABLE DATABASE ALL OPEN;

PDBs时间点恢复：

1，确定SCN

2，关闭pdb

ALTER PLUGGABLE DATABASE pdb1 CLOSE;

3,pdb执行恢复操作

RUN
{ 
  SET UNTIL SCN 1000;    
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
}

4,启动pdb

ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

  举例：pdb5不完全恢复。

ALTER PLUGGABLE DATABASE pdb5 CLOSE;
run 
{
   SET UNTIL SCN 1066;
   RESTORE PLUGGABLE DATABASE pdb5;
   RECOVER PLUGGABLE DATABASE pdb5;
}
ALTER PLUGGABLE DATABASE pdb5 OPEN RESETLOGS;

恢复一个被删除的PDB：

需要有控制文件，root,PDB的备份。

//You need a backup of the control file, root, and the PDB that was dropped.

1，确定SCN

2,执行恢复，没有配置闪回区，需要指定AUXILIARY DESTINATION参数。

run{
SET UNTIL SCN 2883453;  
	recover database pdb01 AUXILIARY DESTINATION '/u01/tmp';
}

3，启动pdb(19c自动open)

ALTER PLUGGABLE DATABASE pdb01 OPEN RESETLOGS;

相关日志记录：

RMAN> run{
SET UNTIL SCN 2883453;
        recover database pdb01 AUXILIARY DESTINATION '/u01/tmp';
}2> 3> 4>

executing command: SET until clause

Starting recover at 23-JUL-21
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='yagv'

initialization parameters used for automatic instance:
db_name=CDB
db_unique_name=yagv_pitr_pdb01_CDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=4768M
processes=200
db_create_file_dest=/u01/tmp
log_archive_dest_1='location=/u01/tmp'
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    4999608840 bytes

Fixed Size                     9144840 bytes
Variable Size                922746880 bytes
Database Buffers            4060086272 bytes
Redo Buffers                   7630848 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 2883453;
# 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 23-JUL-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=169 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/19.3/dbs/c-2195211038-20210723-0c
channel ORA_AUX_DISK_1: piece handle=/u01/app/19.3/dbs/c-2195211038-20210723-0c tag=TAG20210723T173030
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/tmp/CDB/controlfile/o1_mf_jho35jdf_.ctl
Finished restore at 23-JUL-21

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 2883453;
# 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  7 to new;
set newname for datafile  13 to
 "/u01/app/oracle/oradata/CDB/pdb01/system01.dbf";
set newname for datafile  14 to
 "/u01/app/oracle/oradata/CDB/pdb01/sysaux01.dbf";
set newname for datafile  15 to
 "/u01/app/oracle/oradata/CDB/pdb01/undotbs01.dbf";
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 7, 13, 14, 15;

switch clone datafile all;
}
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

Starting restore at 23-JUL-21
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 /u01/tmp/CDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/tmp/CDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/tmp/CDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/tmp/CDB/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/19.3/dbs/1b04mn0a_43_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/19.3/dbs/1b04mn0a_43_1_1 tag=TAG20210723T172946
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 00013 to /u01/app/oracle/oradata/CDB/pdb01/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/CDB/pdb01/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/CDB/pdb01/undotbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/bak/pb011d04mn1f_45_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/bak/pb011d04mn1f_45_1_1 tag=TAG20210723T173023
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 23-JUL-21

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=1078680780 file name=/u01/tmp/CDB/datafile/o1_mf_system_jho35p89_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1078680780 file name=/u01/tmp/CDB/datafile/o1_mf_undotbs1_jho35p90_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1078680780 file name=/u01/tmp/CDB/datafile/o1_mf_sysaux_jho35p8f_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=11 STAMP=1078680780 file name=/u01/tmp/CDB/datafile/o1_mf_users_jho35p9f_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 2883453;
# 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 "alter database datafile  7 online";
sql clone 'PDB01' "alter database datafile
 13 online";
sql clone 'PDB01' "alter database datafile
 14 online";
sql clone 'PDB01' "alter database datafile
 15 online";
#recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
 'PDB01'  delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB01 unplug into ''
/u01/app/19.3/dbs/_rm_pdb_pitr_2_yagv.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB01 using ''
/u01/app/19.3/dbs/_rm_pdb_pitr_2_yagv.xml'' nocopy tempfile reuse";
alter pluggable database PDB01 open;
}
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  7 online

sql statement: alter database datafile  13 online

sql statement: alter database datafile  14 online

sql statement: alter database datafile  15 online

Starting recover at 23-JUL-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/arc/1_3_1078619944.dbf
archived log for thread 1 with sequence 4 is already on disk as file /u01/arc/1_4_1078619944.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/arc/1_5_1078619944.dbf
archived log file name=/u01/arc/1_3_1078619944.dbf thread=1 sequence=3
archived log file name=/u01/arc/1_4_1078619944.dbf thread=1 sequence=4
archived log file name=/u01/arc/1_5_1078619944.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUL-21

sql statement: alter database open resetlogs

sql statement: alter pluggable database PDB01 unplug into ''/u01/app/19.3/dbs/_rm_pdb_pitr_2_yagv.xml''

sql statement: create pluggable database PDB01 using ''/u01/app/19.3/dbs/_rm_pdb_pitr_2_yagv.xml'' nocopy tempfile reuse

Statement processed

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/app/19.3/dbs/_rm_pdb_pitr_2_yagv.xml deleted
auxiliary instance file /u01/tmp/CDB/datafile/o1_mf_sysaux_jho35p8f_.dbf deleted
auxiliary instance file /u01/tmp/CDB/controlfile/o1_mf_jho35jdf_.ctl deleted
Finished recover at 23-JUL-21