RMAN的表级和表分区级恢复应用场景
如果在某些故障或误操作场景中，需要将单个表恢复到特定的时间点，在这种情况下，TSPITR不是最有效的解决方案，因为它将表空间中的所有对象都移动到指定的时间点。在12c或以上版本的数据库中，可以只通过需要恢复已被逻辑损坏或已被删除和清除的表来完成数据的恢复。
如果在DDL之后导致的表数据丢失，则无法使用Flashback技术来进行表数据的恢复，Flashback表不能通过诸如截断表操作之类的结构更改来恢复表数据。
RMAN的表级和表分区级恢复的限制
- SYS用户表或分区无法恢复
- 存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复
- 在备库上的表和分区表不能恢复
- 当REMAP选项用来恢复的表包含NOT NULL约束时，恢复此表是不可行的
RMAN的表级和表分区级恢复前提
- 数据库必须处于读写模式
- 数据库必须处于归档模式
- 如果要恢复表或者分区，你必须拥有这些表或者分区存在后的时间的备份
- 想要恢复单个表分区，COMPATIBLE初始化参数所在的目标库必须设置为11.1.0或以上
表恢复语法：
RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
AUXILIARY DESTINATION '/u01/tablerecovery'
DATAPUMP DESTINATION '/u01/dumpfile'
DUMP FILE 'tablename.dmp'
NOTABLEIMPORT -- t此选项避免自动导入表)
REMAP TABLE 'username.tablename': 'username.new_table_name'; -- 此选项可以对表重命名
注意：until time 以后，还支持以下3种方式：
SCN号
Sequence number（日志序列号）
Time：根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间，也可以用SYSDATE，比如"SYSDATE-30"、"to_date('2018-04-09:13:51:48','yyyy-mm-dd hh24:mi:ss')"
恢复多张表到多个用户多个表空间
RECOVER TABLE owner1.table1,owner1.table2 OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups/'
REMAP TABLE owner1.table1:owner2.table1_1, owner1.table2:owner2.table2_1; ---重命名多张表，使用','分开
REMAP TABLESPACE 'tablespace1':'tablespace2'; ---更改表空间
在pdb中进行表的恢复示例：
数据库版本：Oracle Database 19c Production Version 19.3.0.0.0
操作系统: Red Hat Enterprise Linux release 8.0 (Ootpa)
- 普通表的恢复：
查看当前PDB；
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDKF01 READ WRITE NO
4 PDBAPEX READ WRITE NO
在PDB中创建测试用的表空间和数据：
SQL> alter session set container=PDKF01;
Session altered.
SQL> create tablespace dkftest;
Tablespace created.
SQL> create user dkf identified by dkf default tablespace dkftest;
User created.
SQL> grant dba to dkf;
Grant succeeded.
SQL> create table dkf.t1(id int,name varchar2(10));
Table created.
SQL> insert into dkf.t1 values(1,'a');
insert into dkf.t1 values(2,'b');
insert into dkf.t1 values(3,'c');
insert into dkf.t1 values(4,'d');
insert into dkf.t1 values(5,'e');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dkf.t1;
ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 d
5 e
SQL>
切换当前的日志，并记录当前的时间；
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL>
RMAN> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'
-------------------
2023-03-18 12:47:43
进行数据库全备;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/awrdump/c-%F';
RMAN>
RMAN> backup database plus archivelog;
Starting backup at 2023:03:1812:48:44
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=101 RECID=162 STAMP=1122903529
input archived log thread=1 sequence=102 RECID=163 STAMP=1122957286
input archived log thread=1 sequence=103 RECID=164 STAMP=1123038340
input archived log thread=1 sequence=104 RECID=165 STAMP=1123088442
input archived log thread=1 sequence=105 RECID=166 STAMP=1123135302
input archived log thread=1 sequence=106 RECID=167 STAMP=1123188954
input archived log thread=1 sequence=107 RECID=168 STAMP=1123273236
input archived log thread=1 sequence=108 RECID=169 STAMP=1123344046
input archived log thread=1 sequence=109 RECID=170 STAMP=1123421678
input archived log thread=1 sequence=110 RECID=171 STAMP=1123488043
input archived log thread=1 sequence=111 RECID=172 STAMP=1123552815
input archived log thread=1 sequence=112 RECID=173 STAMP=1123624912
input archived log thread=1 sequence=113 RECID=174 STAMP=1123682808
input archived log thread=1 sequence=114 RECID=175 STAMP=1123711727
input archived log thread=1 sequence=115 RECID=176 STAMP=1123769376
input archived log thread=1 sequence=116 RECID=177 STAMP=1123798249
input archived log thread=1 sequence=117 RECID=178 STAMP=1123879090
input archived log thread=1 sequence=118 RECID=179 STAMP=1123945102
input archived log thread=1 sequence=119 RECID=180 STAMP=1123999263
input archived log thread=1 sequence=120 RECID=181 STAMP=1124013717
input archived log thread=1 sequence=121 RECID=182 STAMP=1124013719
input archived log thread=1 sequence=122 RECID=183 STAMP=1124013722
input archived log thread=1 sequence=123 RECID=184 STAMP=1124013723
input archived log thread=1 sequence=124 RECID=185 STAMP=1124013726
input archived log thread=1 sequence=125 RECID=186 STAMP=1124013726
input archived log thread=1 sequence=126 RECID=187 STAMP=1124013816
input archived log thread=1 sequence=127 RECID=188 STAMP=1124067054
input archived log thread=1 sequence=128 RECID=189 STAMP=1131796618
input archived log thread=1 sequence=129 RECID=190 STAMP=1131796893
input archived log thread=1 sequence=130 RECID=191 STAMP=1131797035
input archived log thread=1 sequence=131 RECID=192 STAMP=1131797063
input archived log thread=1 sequence=132 RECID=193 STAMP=1131797105
input archived log thread=1 sequence=133 RECID=194 STAMP=1131797147
input archived log thread=1 sequence=134 RECID=195 STAMP=1131797212
input archived log thread=1 sequence=135 RECID=196 STAMP=1131797265
input archived log thread=1 sequence=136 RECID=197 STAMP=1131799057
input archived log thread=1 sequence=137 RECID=198 STAMP=1131799057
input archived log thread=1 sequence=138 RECID=199 STAMP=1131799060
input archived log thread=1 sequence=139 RECID=200 STAMP=1131799098
input archived log thread=1 sequence=140 RECID=201 STAMP=1131799099
input archived log thread=1 sequence=141 RECID=202 STAMP=1131799102
input archived log thread=1 sequence=142 RECID=203 STAMP=1131799103
input archived log thread=1 sequence=143 RECID=204 STAMP=1131799124
channel ORA_DISK_1: starting piece 1 at 2023:03:1812:48:45
channel ORA_DISK_1: finished piece 1 at 2023:03:1812:49:00
piece handle=/awrdump/rmanback_251nboik_1_1 tag=TAG20230318T123844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2023:03:1812:49:00
Starting backup at 2023:03:1812:49:00
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=00013 name=+DATA/DKF006/DATAFILE/test.280.1107398373
input datafile file number=00003 name=+DATA/DKF006/DATAFILE/sysaux.258.1101088521
input datafile file number=00001 name=+DATA/DKF006/DATAFILE/system.257.1101088487
input datafile file number=00004 name=+DATA/DKF006/DATAFILE/undotbs1.259.1101088537
input datafile file number=00007 name=+DATA/DKF006/DATAFILE/users.260.1101088537
channel ORA_DISK_1: starting piece 1 at 2023:03:1812:49:00
channel ORA_DISK_1: finished piece 1 at 2023:03:1812:49:15
piece handle=/awrdump/rmanback_261nboj4_1_1 tag=TAG20230318T123900 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=00010 name=+DATA/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/DATAFILE/sysaux.273.1101089437
input datafile file number=00011 name=+DATA/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/DATAFILE/undotbs1.271.1101089437
input datafile file number=00009 name=+DATA/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/DATAFILE/system.272.1101089437
input datafile file number=00014 name=+DATA/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/DATAFILE/dkftest.281.1131796673
input datafile file number=00012 name=+DATA/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/DATAFILE/users.275.1101089451
channel ORA_DISK_1: starting piece 1 at 2023:03:1812:49:15
channel ORA_DISK_1: finished piece 1 at 2023:03:1812:49:22
piece handle=/awrdump/rmanback_271nbojj_1_1 tag=TAG20230318T123900 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/DKF006/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.267.1101088791
input datafile file number=00005 name=+DATA/DKF006/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.266.1101088791
input datafile file number=00008 name=+DATA/DKF006/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.268.1101088791
channel ORA_DISK_1: starting piece 1 at 2023:03:1812:49:22
channel ORA_DISK_1: finished piece 1 at 2023:03:1812:49:25
piece handle=/awrdump/rmanback_281nbojq_1_1 tag=TAG20230318T123900 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2023:03:1812:49:25
Starting backup at 2023:03:1812:49:25
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=144 RECID=205 STAMP=1131799166
channel ORA_DISK_1: starting piece 1 at 2023:03:1812:49:26
channel ORA_DISK_1: finished piece 1 at 2023:03:1812:49:27
piece handle=/awrdump/rmanback_291nboju_1_1 tag=TAG20230318T123926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2023:03:1812:49:27
Starting Control File and SPFILE Autobackup at 2023:03:1812:49:27
piece handle=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-2651711443-20230318-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2023:03:1812:49:28
RMAN>
检查表数据信息并进行清理；
SQL> select * from dkf.t1;
ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 d
5 e
SQL>
SQL>
SQL>
SQL> truncate table dkf.t1;
Table truncated.
SQL>
SQL> drop table dkf.t1 purge;
Table dropped.
SQL>
SQL>
SQL> select * from dkf.t1;
select * from dkf.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
创建表恢复需要的目录：
[oracle@dkf ~]$ mkdir /awrdump/dumpfile
[oracle@dkf ~]$
[oracle@dkf ~]$
恢复单表
RMAN> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY'
-------------------
2023-03-18 13:27:21
发起单表恢复命令：
RMAN> RECOVER TABLE dkf.t1 OF PLUGGABLE DATABASE pdkf01
UNTIL TIME "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/awrdump/dumpfile';
检查主机上的数据库实例，在恢复期间，Oracle会自动启动一个辅助实例来进行所需数据文件的恢复：
[oracle@dkf awrdump]$ ps -ef|grep smon
grid 4474 1 0 11:48 ? 00:00:00 asm_smon_+ASM
oracle 5513 1 0 11:56 ? 00:00:01 ora_smon_dkf006
oracle 15710 1 0 13:30 ? 00:00:00 ora_smon_cnEy
oracle 15760 7668 0 13:30 pts/2 00:00:00 grep --color=auto smon
[oracle@dkf awrdump]$
整个恢复的过程如下：
RMAN> RECOVER TABLE dkf.t1 OF PLUGGABLE DATABASE pdkf01
UNTIL TIME "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/awrdump/dumpfile';2> 3>
Starting recover at 2023:03:1813:29:55
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 PDKF01:SYSTEM
Tablespace UNDOTBS1
Tablespace PDKF01:UNDOTBS1
Creating automatic instance, with SID='cnEy'
initialization parameters used for automatic instance:
db_name=DKF006
db_unique_name=cnEy_pitr_pdkf01_DKF006
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1184M
processes=200
db_create_file_dest=/awrdump/dumpfile
log_archive_dest_1='location=/awrdump/dumpfile'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance DKF006
Oracle instance started
Total System Global Area 1241513976 bytes
Fixed Size 9134072 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7643136 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2023:03:1813:30:21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=39 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 /awrdump/c-c-2651711443-20230318-05
channel ORA_AUX_DISK_1: piece handle=/awrdump/c-c-2651711443-20230318-05 tag=TAG20230318T132622
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/awrdump/dumpfile/DKF006/controlfile/o1_mf_l1cx9nty_.ctl
Finished restore at 2023:03:1813:30:30
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;
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
executing command: SET NEWNAME
renamed tempfile 1 to /awrdump/dumpfile/DKF006/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2023:03:1813:30:37
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 /awrdump/dumpfile/DKF006/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /awrdump/dumpfile/DKF006/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /awrdump/dumpfile/DKF006/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /awrdump/rmanback_2e1nbral_1_1
channel ORA_AUX_DISK_1: piece handle=/awrdump/rmanback_2e1nbral_1_1 tag=TAG20230318T132541
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
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 00009 to /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /awrdump/rmanback_2f1nbrb4_1_1
channel ORA_AUX_DISK_1: piece handle=/awrdump/rmanback_2f1nbrb4_1_1 tag=TAG20230318T132541
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 2023:03:1813:31:20
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1131802280 file name=/awrdump/dumpfile/DKF006/datafile/o1_mf_system_l1cx9yyb_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1131802280 file name=/awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_system_l1cxbs16_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1131802280 file name=/awrdump/dumpfile/DKF006/datafile/o1_mf_undotbs1_l1cx9yyf_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1131802280 file name=/awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_undotbs1_l1cxbs15_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1131802280 file name=/awrdump/dumpfile/DKF006/datafile/o1_mf_sysaux_l1cx9yy0_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1131802280 file name=/awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_sysaux_l1cxbs0r_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDKF01' "alter database datafile
9 online";
sql clone "alter database datafile 4 online";
sql clone 'PDKF01' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDKF01' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDKF01":"SYSTEM", "UNDOTBS1", "PDKF01":"UNDOTBS1", "SYSAUX", "PDKF01":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2023:03:1813:31:25
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 146 is already on disk as file /u01/app/oracle/arch/1_146_1101088597.arch
archived log for thread 1 with sequence 147 is already on disk as file /u01/app/oracle/arch/1_147_1101088597.arch
archived log for thread 1 with sequence 148 is already on disk as file /u01/app/oracle/arch/1_148_1101088597.arch
archived log for thread 1 with sequence 149 is already on disk as file /u01/app/oracle/arch/1_149_1101088597.arch
archived log for thread 1 with sequence 150 is already on disk as file /u01/app/oracle/arch/1_150_1101088597.arch
archived log for thread 1 with sequence 151 is already on disk as file /u01/app/oracle/arch/1_151_1101088597.arch
archived log for thread 1 with sequence 152 is already on disk as file /u01/app/oracle/arch/1_152_1101088597.arch
archived log file name=/u01/app/oracle/arch/1_146_1101088597.arch thread=1 sequence=146
archived log file name=/u01/app/oracle/arch/1_147_1101088597.arch thread=1 sequence=147
archived log file name=/u01/app/oracle/arch/1_148_1101088597.arch thread=1 sequence=148
archived log file name=/u01/app/oracle/arch/1_149_1101088597.arch thread=1 sequence=149
archived log file name=/u01/app/oracle/arch/1_150_1101088597.arch thread=1 sequence=150
archived log file name=/u01/app/oracle/arch/1_151_1101088597.arch thread=1 sequence=151
archived log file name=/u01/app/oracle/arch/1_152_1101088597.arch thread=1 sequence=152
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023:03:1813:31:28
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDKF01 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDKF01 open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/awrdump/dumpfile/DKF006/controlfile/o1_mf_l1cx9nty_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1241513976 bytes
Fixed Size 9134072 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7643136 bytes
sql statement: alter system set control_files = ''/awrdump/dumpfile/DKF006/controlfile/o1_mf_l1cx9nty_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1241513976 bytes
Fixed Size 9134072 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7643136 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2023:03:1813:33:05
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=47 device type=DISK
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 00014 to /awrdump/dumpfile/CNEY_PITR_PDKF01_DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_dkftest_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /awrdump/rmanback_2f1nbrb4_1_1
channel ORA_AUX_DISK_1: piece handle=/awrdump/rmanback_2f1nbrb4_1_1 tag=TAG20230318T132541
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 2023:03:1813:33:13
datafile 14 switched to datafile copy
input datafile copy RECID=17 STAMP=1131802394 file name=/awrdump/dumpfile/CNEY_PITR_PDKF01_DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_dkftest_l1cxgq93_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'PDKF01' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDKF01":"DKFTEST", "SYSTEM", "PDKF01":"SYSTEM", "UNDOTBS1", "PDKF01":"UNDOTBS1", "SYSAUX", "PDKF01":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2023:03:1813:33:16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 146 is already on disk as file /u01/app/oracle/arch/1_146_1101088597.arch
archived log for thread 1 with sequence 147 is already on disk as file /u01/app/oracle/arch/1_147_1101088597.arch
archived log for thread 1 with sequence 148 is already on disk as file /u01/app/oracle/arch/1_148_1101088597.arch
archived log for thread 1 with sequence 149 is already on disk as file /u01/app/oracle/arch/1_149_1101088597.arch
archived log for thread 1 with sequence 150 is already on disk as file /u01/app/oracle/arch/1_150_1101088597.arch
archived log for thread 1 with sequence 151 is already on disk as file /u01/app/oracle/arch/1_151_1101088597.arch
archived log for thread 1 with sequence 152 is already on disk as file /u01/app/oracle/arch/1_152_1101088597.arch
archived log file name=/u01/app/oracle/arch/1_146_1101088597.arch thread=1 sequence=146
archived log file name=/u01/app/oracle/arch/1_147_1101088597.arch thread=1 sequence=147
archived log file name=/u01/app/oracle/arch/1_148_1101088597.arch thread=1 sequence=148
archived log file name=/u01/app/oracle/arch/1_149_1101088597.arch thread=1 sequence=149
archived log file name=/u01/app/oracle/arch/1_150_1101088597.arch thread=1 sequence=150
archived log file name=/u01/app/oracle/arch/1_151_1101088597.arch thread=1 sequence=151
archived log file name=/u01/app/oracle/arch/1_152_1101088597.arch thread=1 sequence=152
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023:03:1813:33:21
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDKF01 open';
}
executing Memory Script
sql statement: alter pluggable database PDKF01 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDKF01' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/awrdump/dumpfile''";
# create directory for datapump export
sql clone 'PDKF01' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/awrdump/dumpfile''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/awrdump/dumpfile''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/awrdump/dumpfile''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_cnEy_swtm":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "DKF"."T1" 5.523 KB 5 rows
EXPDP> Master table "SYS"."TSPITR_EXP_cnEy_swtm" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_cnEy_swtm is:
EXPDP> /awrdump/dumpfile/tspitr_cnEy_76326.dmp
EXPDP> Job "SYS"."TSPITR_EXP_cnEy_swtm" successfully completed at Sat Mar 18 13:35:56 2023 elapsed 0 00:00:51
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_cnEy_Cava" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_cnEy_Cava":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "DKF"."T1" 5.523 KB 5 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_cnEy_Cava" successfully completed at Sat Mar 18 13:36:32 2023 elapsed 0 00:00:27
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_temp_l1cxcqbt_.tmp deleted
auxiliary instance file /awrdump/dumpfile/DKF006/datafile/o1_mf_temp_l1cxckcy_.tmp deleted
auxiliary instance file /awrdump/dumpfile/CNEY_PITR_PDKF01_DKF006/onlinelog/o1_mf_3_l1cxh5qh_.log deleted
auxiliary instance file /awrdump/dumpfile/CNEY_PITR_PDKF01_DKF006/onlinelog/o1_mf_2_l1cxh2ql_.log deleted
auxiliary instance file /awrdump/dumpfile/CNEY_PITR_PDKF01_DKF006/onlinelog/o1_mf_1_l1cxh212_.log deleted
auxiliary instance file /awrdump/dumpfile/CNEY_PITR_PDKF01_DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_dkftest_l1cxgq93_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_sysaux_l1cxbs0r_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/datafile/o1_mf_sysaux_l1cx9yy0_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_undotbs1_l1cxbs15_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/datafile/o1_mf_undotbs1_l1cx9yyf_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_system_l1cxbs16_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/datafile/o1_mf_system_l1cx9yyb_.dbf deleted
auxiliary instance file /awrdump/dumpfile/DKF006/controlfile/o1_mf_l1cx9nty_.ctl deleted
auxiliary instance file tspitr_cnEy_76326.dmp deleted
Finished recover at 2023:03:1813:36:34
RMAN>
恢复成功，检查现在数据库中PDB中恢复的数据：
SQL>
SQL> alter session set container=pdkf01;
Session altered.
SQL> select count(1) from dkf.t1;
COUNT(1)
----------
5
SQL>
SQL>
SQL>
SQL> select * from dkf.t1;
ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 d
5 e
SQL>
SQL>
表及数据已完成恢复，数据也是正确无误。
- 分区表恢复：
创建测试用的分区表：
create table dkf.P_T2 (
id int , name varchar2(10) )
partition by range(id)
(partition p1 values less than(3),
partition p2 values less than(5),
partition p3 values less than(8),
partition p4 values less than(maxvalue)
);
insert into dkf.P_T2(id,name) values(1,'a');
insert into dkf.P_T2(id,name) values(2,'b');
insert into dkf.P_T2(id,name) values(3,'c');
insert into dkf.P_T2(id,name) values(5,'d');
insert into dkf.P_T2(id,name) values(7,'e');
insert into dkf.P_T2(id,name) values(10,'f');
SQL> commit;
Commit complete.
SQL> select * from dkf.P_t2;
ID NAME
---------- ------------------------------
1 a
2 b
3 c
5 d
7 e
10 f
6 rows selected.
SQL> select * from dkf.P_t2 partition(p1);
ID NAME
---------- ------------------------------
1 a
2 b
SQL>
SQL>
SQL> select * from dkf.P_t2 partition(p2);
ID NAME
---------- ------------------------------
3 c
SQL> select * from dkf.P_t2 partition(p3);
ID NAME
---------- ------------------------------
5 d
7 e
SQL> select * from dkf.P_t2 partition(p4);
ID NAME
---------- ------------------------------
10 f
SQL>
SQL>
可以看到，每个分区里都是有对应的数据，其中，分区3中有2条数据，5和7，我们将分区3的数据进行删除；
SQL> delete from dkf.P_T2 partition(p3) ;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from dkf.P_T2 partition(p3);
no rows selected
SQL>
创建表恢复需要的目录：
[oracle@dkf ~]$ mkdir /awrdump/tbrecory
[oracle@dkf ~]$ mkdir /awrdump/dumpfile
[oracle@dkf ~]$
[oracle@dkf ~]$
RECOVER TABLE dkf.p_t2:p3 OF PLUGGABLE DATABASE pdkf01
UNTIL TIME "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/awrdump/dumpfile/'
REMAP TABLE 'DKF'.'P_T2':'P3':'P3_PART';
整个恢复的过程如下：
RMAN> RECOVER TABLE dkf.p_t2:p3 OF PLUGGABLE DATABASE pdkf01
UNTIL TIME "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/awrdump/dumpfile/'
REMAP TABLE 'DKF'.'P_T2':'P3':'P3_PART';2> 3> 4> 5>
Starting recover at 2023:03:1813:48:27
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 PDKF01:SYSTEM
Tablespace UNDOTBS1
Tablespace PDKF01:UNDOTBS1
Creating automatic instance, with SID='jEub'
initialization parameters used for automatic instance:
db_name=DKF006
db_unique_name=jEub_pitr_pdkf01_DKF006
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1184M
processes=200
db_create_file_dest=/awrdump/tbrecory/
log_archive_dest_1='location=/awrdump/tbrecory/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance DKF006
Oracle instance started
Total System Global Area 1241513976 bytes
Fixed Size 9134072 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7643136 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2023:03:1813:48:49
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=8 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 /awrdump/c-c-2651711443-20230318-05
channel ORA_AUX_DISK_1: piece handle=/awrdump/c-c-2651711443-20230318-05 tag=TAG20230318T132622
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/awrdump/tbrecory/DKF006/controlfile/o1_mf_l1cyd2bv_.ctl
Finished restore at 2023:03:1813:48:51
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;
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
executing command: SET NEWNAME
renamed tempfile 1 to /awrdump/tbrecory/DKF006/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2023:03:1813:49:02
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 /awrdump/tbrecory/DKF006/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /awrdump/tbrecory/DKF006/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /awrdump/tbrecory/DKF006/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /awrdump/rmanback_2e1nbral_1_1
channel ORA_AUX_DISK_1: piece handle=/awrdump/rmanback_2e1nbral_1_1 tag=TAG20230318T132541
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
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 00009 to /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /awrdump/rmanback_2f1nbrb4_1_1
channel ORA_AUX_DISK_1: piece handle=/awrdump/rmanback_2f1nbrb4_1_1 tag=TAG20230318T132541
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2023:03:1813:49:44
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1131803385 file name=/awrdump/tbrecory/DKF006/datafile/o1_mf_system_l1cydhxw_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1131803385 file name=/awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_system_l1cyf9tb_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1131803385 file name=/awrdump/tbrecory/DKF006/datafile/o1_mf_undotbs1_l1cydhxy_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1131803385 file name=/awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_undotbs1_l1cyf9t8_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1131803385 file name=/awrdump/tbrecory/DKF006/datafile/o1_mf_sysaux_l1cydhxf_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1131803385 file name=/awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_sysaux_l1cyf9t5_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDKF01' "alter database datafile
9 online";
sql clone "alter database datafile 4 online";
sql clone 'PDKF01' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDKF01' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDKF01":"SYSTEM", "UNDOTBS1", "PDKF01":"UNDOTBS1", "SYSAUX", "PDKF01":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2023:03:1813:49:50
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 146 is already on disk as file /u01/app/oracle/arch/1_146_1101088597.arch
archived log for thread 1 with sequence 147 is already on disk as file /u01/app/oracle/arch/1_147_1101088597.arch
archived log for thread 1 with sequence 148 is already on disk as file /u01/app/oracle/arch/1_148_1101088597.arch
archived log for thread 1 with sequence 149 is already on disk as file /u01/app/oracle/arch/1_149_1101088597.arch
archived log for thread 1 with sequence 150 is already on disk as file /u01/app/oracle/arch/1_150_1101088597.arch
archived log for thread 1 with sequence 151 is already on disk as file /u01/app/oracle/arch/1_151_1101088597.arch
archived log for thread 1 with sequence 152 is already on disk as file /u01/app/oracle/arch/1_152_1101088597.arch
archived log file name=/u01/app/oracle/arch/1_146_1101088597.arch thread=1 sequence=146
archived log file name=/u01/app/oracle/arch/1_147_1101088597.arch thread=1 sequence=147
archived log file name=/u01/app/oracle/arch/1_148_1101088597.arch thread=1 sequence=148
archived log file name=/u01/app/oracle/arch/1_149_1101088597.arch thread=1 sequence=149
archived log file name=/u01/app/oracle/arch/1_150_1101088597.arch thread=1 sequence=150
archived log file name=/u01/app/oracle/arch/1_151_1101088597.arch thread=1 sequence=151
archived log file name=/u01/app/oracle/arch/1_152_1101088597.arch thread=1 sequence=152
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023:03:1813:49:53
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDKF01 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDKF01 open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/awrdump/tbrecory/DKF006/controlfile/o1_mf_l1cyd2bv_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1241513976 bytes
Fixed Size 9134072 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7643136 bytes
sql statement: alter system set control_files = ''/awrdump/tbrecory/DKF006/controlfile/o1_mf_l1cyd2bv_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1241513976 bytes
Fixed Size 9134072 bytes
Variable Size 318767104 bytes
Database Buffers 905969664 bytes
Redo Buffers 7643136 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2023:03:1813:51:27
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=47 device type=DISK
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 00014 to /awrdump/tbrecory/JEUB_PITR_PDKF01_DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_dkftest_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /awrdump/rmanback_2f1nbrb4_1_1
channel ORA_AUX_DISK_1: piece handle=/awrdump/rmanback_2f1nbrb4_1_1 tag=TAG20230318T132541
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2023:03:1813:51:31
datafile 14 switched to datafile copy
input datafile copy RECID=17 STAMP=1131803492 file name=/awrdump/tbrecory/JEUB_PITR_PDKF01_DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_dkftest_l1cyk0wn_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2023-03-18 13:27:21','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'PDKF01' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDKF01":"DKFTEST", "SYSTEM", "PDKF01":"SYSTEM", "UNDOTBS1", "PDKF01":"UNDOTBS1", "SYSAUX", "PDKF01":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2023:03:1813:51:32
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 146 is already on disk as file /u01/app/oracle/arch/1_146_1101088597.arch
archived log for thread 1 with sequence 147 is already on disk as file /u01/app/oracle/arch/1_147_1101088597.arch
archived log for thread 1 with sequence 148 is already on disk as file /u01/app/oracle/arch/1_148_1101088597.arch
archived log for thread 1 with sequence 149 is already on disk as file /u01/app/oracle/arch/1_149_1101088597.arch
archived log for thread 1 with sequence 150 is already on disk as file /u01/app/oracle/arch/1_150_1101088597.arch
archived log for thread 1 with sequence 151 is already on disk as file /u01/app/oracle/arch/1_151_1101088597.arch
archived log for thread 1 with sequence 152 is already on disk as file /u01/app/oracle/arch/1_152_1101088597.arch
archived log file name=/u01/app/oracle/arch/1_146_1101088597.arch thread=1 sequence=146
archived log file name=/u01/app/oracle/arch/1_147_1101088597.arch thread=1 sequence=147
archived log file name=/u01/app/oracle/arch/1_148_1101088597.arch thread=1 sequence=148
archived log file name=/u01/app/oracle/arch/1_149_1101088597.arch thread=1 sequence=149
archived log file name=/u01/app/oracle/arch/1_150_1101088597.arch thread=1 sequence=150
archived log file name=/u01/app/oracle/arch/1_151_1101088597.arch thread=1 sequence=151
archived log file name=/u01/app/oracle/arch/1_152_1101088597.arch thread=1 sequence=152
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023:03:1813:51:37
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDKF01 open';
}
executing Memory Script
sql statement: alter pluggable database PDKF01 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDKF01' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/awrdump/dumpfile/''";
# create directory for datapump export
sql clone 'PDKF01' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/awrdump/dumpfile/''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/awrdump/dumpfile/''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/awrdump/dumpfile/''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_jEub_zetl":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "DKF"."P_T2":"P3" 5.5 KB 2 rows
EXPDP> Master table "SYS"."TSPITR_EXP_jEub_zetl" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_jEub_zetl is:
EXPDP> /awrdump/dumpfile/tspitr_jEub_26604.dmp
EXPDP> Job "SYS"."TSPITR_EXP_jEub_zetl" successfully completed at Sat Mar 18 13:53:51 2023 elapsed 0 00:00:55
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_jEub_cpva" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_jEub_cpva":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "DKF"."P3_PART" 5.5 KB 2 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_jEub_cpva" successfully completed at Sat Mar 18 13:54:20 2023 elapsed 0 00:00:23
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_temp_l1cyg7so_.tmp deleted
auxiliary instance file /awrdump/tbrecory/DKF006/datafile/o1_mf_temp_l1cyg21d_.tmp deleted
auxiliary instance file /awrdump/tbrecory/JEUB_PITR_PDKF01_DKF006/onlinelog/o1_mf_3_l1cykf2s_.log deleted
auxiliary instance file /awrdump/tbrecory/JEUB_PITR_PDKF01_DKF006/onlinelog/o1_mf_2_l1cykcb8_.log deleted
auxiliary instance file /awrdump/tbrecory/JEUB_PITR_PDKF01_DKF006/onlinelog/o1_mf_1_l1cykbov_.log deleted
auxiliary instance file /awrdump/tbrecory/JEUB_PITR_PDKF01_DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_dkftest_l1cyk0wn_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_sysaux_l1cyf9t5_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/datafile/o1_mf_sysaux_l1cydhxf_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_undotbs1_l1cyf9t8_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/datafile/o1_mf_undotbs1_l1cydhxy_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/DBCEFC8A3ACE81CBE0550A002762E1A4/datafile/o1_mf_system_l1cyf9tb_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/datafile/o1_mf_system_l1cydhxw_.dbf deleted
auxiliary instance file /awrdump/tbrecory/DKF006/controlfile/o1_mf_l1cyd2bv_.ctl deleted
auxiliary instance file tspitr_jEub_26604.dmp deleted
Finished recover at 2023:03:1813:54:23
RMAN>
恢复完成，检查恢复的数据：
SQL> select * from dkf.P3_PART;
ID NAME
---------- ------------------------------
5 d
7 e
SQL>
数据已导入到临时的表中，现在讲临时表数据插入到待恢复表的分区中；
SQL> select * from dkf.p_t2 partition(p3);
no rows selected
SQL>
SQL>
SQL>
SQL> select * from dkf.P3_PART;
ID NAME
---------- ------------------------------
5 d
7 e
SQL> insert into dkf.p_t2 partition(p3) select * from dkf.P3_PART;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from dkf.p_t2 partition(p3);
ID NAME
---------- ------------------------------
5 d
7 e
SQL> select * from dkf.p_t2;
ID NAME
---------- ------------------------------
1 a
2 b
3 c
5 d
7 e
10 f
6 rows selected.
SQL>
至此，数据恢复完毕。
可以看到，现在对于单表的恢复已经变得非常简单，只需一条命令即可完成单表从备份中的自动恢复，不用人工进行繁琐的操作。
如果你的误操作不是drop、truncate，只是增删改了表中的记录，此时你在recover table时候会出现
RMAN-05112: table “XXX”.“XXXX” already exists
此时你可以dump操作，添加notableimport参数不执行导入操作，然后再手工处理
如果遇到如下报错信息，重新备份下归档日志：
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 03/18/2023 13:01:16
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore
解决办法，重新备份下归档日志：
RMAN> backup archivelog all;
全文完!
===================================================
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
