加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle 12.1新特性----使用RMAN从备份中实现recover table

发布时间：2020-12-12 14:41:02 所属栏目：百科 来源：网络整理
导读：在Oracle12c版本之前，使用RMAN能恢复的级别为数据库级别和表空间级别，如果只有一张表需要恢复，而在数据库级别或表空间级别做恢复，影响范围就太大了。因此12.2版本中提供了一个新特性使用RMAN在表级别做恢复，并且恢复过程中不影响数据库的正常使用。这一

在Oracle12c版本之前，使用RMAN能恢复的级别为数据库级别和表空间级别，如果只有一张表需要恢复，而在数据库级别或表空间级别做恢复，影响范围就太大了。因此12.2版本中提供了一个新特性使用RMAN在表级别做恢复，并且恢复过程中不影响数据库的正常使用。这一功能不仅可以恢复表，还可以恢复表分区。

To recover a table or table partition,you must have a full backup of undo,SYSTEM,0);line-height:1.615;background-color:transparent;padding:0px;">SYSAUX,and the tablespace that contains the table or table partition.

使用recover table的一些限制条件：

When you use theRECOVERcommand to recover tables or table partitions contained in an RMAN backup,the following limitations exist.

  • Tables and table partitions belonging toSYSschema cannot be recovered.

  • Tables and table partitions fromSYSTEMandSYSAUXtablespaces cannot be recovered.

  • Tables and table partitions on standby databases cannot be recovered.

  • Tables with namedNOT NULLconstraints cannot be recovered with theREMAPoption.

下面在12.2版本上做表级别恢复的实验

sys@ORA12C>select*fromv$version;

BANNER										CON_ID
------------------------------------------------------------------------------------------
OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction		0
PL/SQLRelease12.2.0.1.0-Production							0
CORE	12.2.0.1.0	Production								0
TNSforLinux:Version12.2.0.1.0-Production						0
NLSRTLVersion12.2.0.1.0-Production							0

1、创建一个数据库的全备

RMAN>backupdatabase;

Startingbackupat2017072017:12:05
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=36devicetype=DISK
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00001name=+DATA/ORA12C/DATAFILE/system.256.949764433
channelORA_DISK_1:startingpiece1at2017072017:12:07
channelORA_DISK_1:finishedpiece1at2017072017:12:22
piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1tag=TAG20170720T171206comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:15
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00004name=+DATA/ORA12C/DATAFILE/users.266.949764465
channelORA_DISK_1:startingpiece1at2017072017:12:22
channelORA_DISK_1:finishedpiece1at2017072017:12:23
piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1tag=TAG20170720T171206comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00002name=+DATA/ORA12C/DATAFILE/sysaux.261.949764491
channelORA_DISK_1:startingpiece1at2017072017:12:23
channelORA_DISK_1:finishedpiece1at2017072017:12:38
piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1tag=TAG20170720T171206comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:15
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00005name=+DATA/ORA12C/DATAFILE/examples.265.949764515
channelORA_DISK_1:startingpiece1at2017072017:12:38
channelORA_DISK_1:finishedpiece1at2017072017:12:39
piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/12s9ras6_1_1tag=TAG20170720T171206comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01
channelORA_DISK_1:startingfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00008name=/tmp/FY_RST_DATA.DAT
inputdatafilefilenumber=00007name=/tmp/FY_REC_DATA.DAT
inputdatafilefilenumber=00003name=+DATA/ORA12C/DATAFILE/undotbs1.264.949764541
inputdatafilefilenumber=00006name=+DATA/ORA12C/DATAFILE/t_move.dbf
channelORA_DISK_1:startingpiece1at2017072017:12:40
channelORA_DISK_1:finishedpiece1at2017072017:12:41
piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1tag=TAG20170720T171206comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01
Finishedbackupat2017072017:12:41

StartingControlFileandSPFILEAutobackupat2017072017:12:41
piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00comment=NONE
FinishedControlFileandSPFILEAutobackupat2017072017:12:42

2、创建测试表，插入测试数据并记录中间的scn号

zx@ORA12C>createtablet2(idnumber,namevarchar2(10),birthdaydate);

Tablecreated.

zx@ORA12C>insertintot2values(1,'zx',sysdate);

1rowcreated.

zx@ORA12C>commit;

Commitcomplete.

zx@ORA12C>selectcurrent_scnfromv$database;

CURRENT_SCN
-----------
650101

zx@ORA12C>insertintot2values(2,'lx',sysdate);

1rowcreated.

zx@ORA12C>commit;

Commitcomplete.

zx@ORA12C>select*fromt2;

	IDNAMEBIRTHDAY
-------------------------------------
	1zx	2017072017:18:52
	2lx	2017072017:19:34

3、执行表级别恢复，使用remap table参数不覆盖原表，恢复成t2_r表

RMAN>recovertablezx.t2untilscn650101remaptablezx.t2:t2_rauxiliarydestination'/tmp';

Startingrecoverat2017072017:23:50
currentlogarchived
usingchannelORA_DISK_1
RMAN-05026:warning:presumingfollowingsetoftablespacesappliestospecifiedpoint-in-time

ListoftablespacesexpectedtohaveUNDOsegments
TablespaceSYSTEM
TablespaceUNDOTBS1

Creatingautomaticinstance,withSID='htzD'

initializationparametersusedforautomaticinstance:
db_name=ORA12C
db_unique_name=htzD_pitr_ORA12C
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1712M
processes=200
db_create_file_dest=/tmp
log_archive_dest_1='location=/tmp'
#Noauxiliaryparameterfileused


startingupautomaticinstanceORA12C

Oracleinstancestarted

TotalSystemGlobalArea1795162112bytes

FixedSize8621760bytes
VariableSize436207936bytes
DatabaseBuffers1342177280bytes
RedoBuffers8155136bytes
Automaticinstancecreated

contentsofMemoryScript:
{
#setrequestedpointintime
setuntilscn650101;
#restorethecontrolfile
restoreclonecontrolfile;

#mountthecontrolfile
sqlclone'alterdatabasemountclonedatabase';

#archivecurrentonlinelog
sql'altersystemarchivelogcurrent';
}
executingMemoryScript

executingcommand:SETuntilclause

Startingrestoreat2017072017:24:12
allocatedchannel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1:SID=18devicetype=DISK

channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:restoringcontrolfile
channelORA_AUX_DISK_1:readingfrombackuppiece/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00
channelORA_AUX_DISK_1:piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00tag=TAG20170720T171241
channelORA_AUX_DISK_1:restoredbackuppiece1
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
outputfilename=/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl
Finishedrestoreat2017072017:24:14

sqlstatement:alterdatabasemountclonedatabase

sqlstatement:altersystemarchivelogcurrent

contentsofMemoryScript:
{
#setrequestedpointintime
setuntilscn650101;
#setdestinationsforrecoverysetandauxiliarysetdatafiles
setnewnameforclonedatafile1tonew;
setnewnameforclonedatafile3tonew;
setnewnameforclonedatafile2tonew;
setnewnameforclonetempfile1tonew;
#switchalltempfiles
switchclonetempfileall;
#restorethetablespacesintherecoverysetandtheauxiliaryset
restoreclonedatafile1,3,2;

switchclonedatafileall;
}
executingMemoryScript

executingcommand:SETuntilclause

executingcommand:SETNEWNAME

executingcommand:SETNEWNAME

executingcommand:SETNEWNAME

executingcommand:SETNEWNAME

renamedtempfile1to/tmp/ORA12C/datafile/o1_mf_temp_%u_.tmpincontrolfile

Startingrestoreat2017072017:24:19
usingchannelORA_AUX_DISK_1

channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00001to/tmp/ORA12C/datafile/o1_mf_system_%u_.dbf
channelORA_AUX_DISK_1:readingfrombackuppiece/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1
channelORA_AUX_DISK_1:piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1tag=TAG20170720T171206
channelORA_AUX_DISK_1:restoredbackuppiece1
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:45
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00002to/tmp/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channelORA_AUX_DISK_1:readingfrombackuppiece/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1
channelORA_AUX_DISK_1:piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1tag=TAG20170720T171206
channelORA_AUX_DISK_1:restoredbackuppiece1
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:35
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00003to/tmp/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf
channelORA_AUX_DISK_1:readingfrombackuppiece/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1
channelORA_AUX_DISK_1:piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1tag=TAG20170720T171206
channelORA_AUX_DISK_1:restoredbackuppiece1
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:15
Finishedrestoreat2017072017:25:55

datafile1switchedtodatafilecopy
inputdatafilecopyRECID=16STAMP=949857956filename=/tmp/ORA12C/datafile/o1_mf_system_dq0xp4jt_.dbf
datafile3switchedtodatafilecopy
inputdatafilecopyRECID=17STAMP=949857956filename=/tmp/ORA12C/datafile/o1_mf_undotbs1_dq0xrnq2_.dbf
datafile2switchedtodatafilecopy
inputdatafilecopyRECID=18STAMP=949857956filename=/tmp/ORA12C/datafile/o1_mf_sysaux_dq0xqkm0_.dbf

contentsofMemoryScript:
{
#setrequestedpointintime
setuntilscn650101;
#onlinethedatafilesrestoredorswitched
sqlclone"alterdatabasedatafile1online";
sqlclone"alterdatabasedatafile3online";
sqlclone"alterdatabasedatafile2online";
#recoverandopendatabasereadonly
recoverclonedatabasetablespace"SYSTEM","UNDOTBS1","SYSAUX";
sqlclone'alterdatabaSEOpenreadonly';
}
executingMemoryScript

executingcommand:SETuntilclause

sqlstatement:alterdatabasedatafile1online

sqlstatement:alterdatabasedatafile3online

sqlstatement:alterdatabasedatafile2online

Startingrecoverat2017072017:26:02
usingchannelORA_AUX_DISK_1

startingmediarecovery

archivedlogforthread1withsequence2isalreadyondiskasfile/u01/app/oracle/product/12.2/db_home1/dbs/arch1_2_949077789.dbf
archivedlogforthread1withsequence3isalreadyondiskasfile/u01/app/oracle/product/12.2/db_home1/dbs/arch1_3_949077789.dbf
archivedlogfilename=/u01/app/oracle/product/12.2/db_home1/dbs/arch1_2_949077789.dbfthread=1sequence=2
archivedlogfilename=/u01/app/oracle/product/12.2/db_home1/dbs/arch1_3_949077789.dbfthread=1sequence=3
mediarecoverycomplete,elapsedtime:00:00:02
Finishedrecoverat2017072017:26:07

sqlstatement:alterdatabaSEOpenreadonly

contentsofMemoryScript:
{
sqlclone"createspfilefrommemory";
shutdowncloneimmediate;
startupclonenomount;
sqlclone"altersystemsetcontrol_files=
''/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl''comment=
''RMANset''scope=spfile";
shutdowncloneimmediate;
startupclonenomount;
#mountdatabase
sqlclone'alterdatabasemountclonedatabase';
}
executingMemoryScript

sqlstatement:createspfilefrommemory

databaseclosed
databasedismounted
Oracleinstanceshutdown

connectedtoauxiliarydatabase(notstarted)
Oracleinstancestarted

TotalSystemGlobalArea1795162112bytes

FixedSize8621760bytes
VariableSize436207936bytes
DatabaseBuffers1342177280bytes
RedoBuffers8155136bytes

sqlstatement:altersystemsetcontrol_files=''/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl''comment=''RMANset''scope=spfile

Oracleinstanceshutdown

connectedtoauxiliarydatabase(notstarted)
Oracleinstancestarted

TotalSystemGlobalArea1795162112bytes

FixedSize8621760bytes
VariableSize436207936bytes
DatabaseBuffers1342177280bytes
RedoBuffers8155136bytes

sqlstatement:alterdatabasemountclonedatabase

contentsofMemoryScript:
{
#setrequestedpointintime
setuntilscn650101;
#setdestinationsforrecoverysetandauxiliarysetdatafiles
setnewnamefordatafile4tonew;
#restorethetablespacesintherecoverysetandtheauxiliaryset
restoreclonedatafile4;

switchclonedatafileall;
}
executingMemoryScript

executingcommand:SETuntilclause

executingcommand:SETNEWNAME

Startingrestoreat2017072017:27:18
allocatedchannel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1:SID=23devicetype=DISK

channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00004to/tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_%u_.dbf
channelORA_AUX_DISK_1:readingfrombackuppiece/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1
channelORA_AUX_DISK_1:piecehandle=/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1tag=TAG20170720T171206
channelORA_AUX_DISK_1:restoredbackuppiece1
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:25
Finishedrestoreat2017072017:27:44

datafile4switchedtodatafilecopy
inputdatafilecopyRECID=20STAMP=949858064filename=/tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_dq0xvq9q_.dbf

contentsofMemoryScript:
{
#setrequestedpointintime
setuntilscn650101;
#onlinethedatafilesrestoredorswitched
sqlclone"alterdatabasedatafile4online";
#recoverandopenresetlogs
recoverclonedatabasetablespace"USERS","SYSTEM","SYSAUX"deletearchivelog;
alterclonedatabaSEOpenresetlogs;
}
executingMemoryScript

executingcommand:SETuntilclause

sqlstatement:alterdatabasedatafile4online

Startingrecoverat2017072017:27:44
usingchannelORA_AUX_DISK_1

startingmediarecovery

archivedlogforthread1withsequence2isalreadyondiskasfile/u01/app/oracle/product/12.2/db_home1/dbs/arch1_2_949077789.dbf
archivedlogforthread1withsequence3isalreadyondiskasfile/u01/app/oracle/product/12.2/db_home1/dbs/arch1_3_949077789.dbf
archivedlogfilename=/u01/app/oracle/product/12.2/db_home1/dbs/arch1_2_949077789.dbfthread=1sequence=2
archivedlogfilename=/u01/app/oracle/product/12.2/db_home1/dbs/arch1_3_949077789.dbfthread=1sequence=3
mediarecoverycomplete,elapsedtime:00:00:01
Finishedrecoverat2017072017:27:48

databaSEOpened

contentsofMemoryScript:
{
#createdirectoryfordatapumpimport
sql"createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''
/tmp''";
#createdirectoryfordatapumpexport
sqlclone"createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''
/tmp''";
}
executingMemoryScript

sqlstatement:createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''/tmp''

sqlstatement:createorreplacedirectoryTSPITR_DIROBJ_DPDIRas''/tmp''

Performingexportoftables...
EXPDP>Starting"SYS"."TSPITR_EXP_htzD_mhEh":
EXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA
EXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE
EXPDP>..exported"ZX"."T2"5.898KB1rows
EXPDP>Mastertable"SYS"."TSPITR_EXP_htzD_mhEh"successfullyloaded/unloaded
EXPDP>******************************************************************************
EXPDP>DumpfilesetforSYS.TSPITR_EXP_htzD_mhEhis:
EXPDP>/tmp/tspitr_htzD_98436.dmp
EXPDP>Job"SYS"."TSPITR_EXP_htzD_mhEh"successfullycompletedatThuJul2017:29:482017elapsed000:01:05
Exportcompleted


contentsofMemoryScript:
{
#shutdownclonebeforeimport
shutdowncloneabort
}
executingMemoryScript

Oracleinstanceshutdown

Performingimportoftables...
IMPDP>Mastertable"SYS"."TSPITR_IMP_htzD_bhqf"successfullyloaded/unloaded
IMPDP>Starting"SYS"."TSPITR_IMP_htzD_bhqf":
IMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE
IMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/TABLE_DATA
IMPDP>..imported"ZX"."T2_R"5.898KB1rows
IMPDP>ProcessingobjecttypeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP>Job"SYS"."TSPITR_IMP_htzD_bhqf"successfullycompletedatThuJul2017:30:102017elapsed000:00:10
Importcompleted


Removingautomaticinstance
Automaticinstanceremoved
auxiliaryinstancefile/tmp/ORA12C/datafile/o1_mf_temp_dq0xskvm_.tmpdeleted
auxiliaryinstancefile/tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_3_dq0xwo3d_.logdeleted
auxiliaryinstancefile/tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_2_dq0xwo3d_.logdeleted
auxiliaryinstancefile/tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_1_dq0xwo0q_.logdeleted
auxiliaryinstancefile/tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_dq0xvq9q_.dbfdeleted
auxiliaryinstancefile/tmp/ORA12C/datafile/o1_mf_sysaux_dq0xqkm0_.dbfdeleted
auxiliaryinstancefile/tmp/ORA12C/datafile/o1_mf_undotbs1_dq0xrnq2_.dbfdeleted
auxiliaryinstancefile/tmp/ORA12C/datafile/o1_mf_system_dq0xp4jt_.dbfdeleted
auxiliaryinstancefile/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctldeleted
auxiliaryinstancefiletspitr_htzD_98436.dmpdeleted
Finishedrecoverat2017072017:30:13

4、验证结果，查询t2_r表

zx@ORA12C>select*fromt2_r;

	IDNAMEBIRTHDAY
-------------------------------------
	1zx	2017072017:18:52

5、即使表t2做了DDL操作修改了表结构，也可以用这种方法进行恢复

--表t2添加一个字段
zx@ORA12C>altertablet2addaddressvarchar2(10);

Tablealtered.
--恢复表到t2_r2
RMAN>recovertablezx.t2untilscn650101remaptablezx.t2:t2_r2auxiliarydestination'/tmp';
Startingrecoverat2017072017:57:00
usingchannelORA_DISK_1
RMAN-05026:warning:presumingfollowingsetoftablespacesappliestospecifiedpoint-in-time
......
Finishedrecoverat2017072018:02:03
--验证表t2_r2
zx@ORA12C>select*fromt2_r2;

	IDNAMEBIRTHDAY
-------------------------------------
	1zx	2017072017:18:52
	
--恢复成功

这一功能很大程度减小了数据恢复的影响范围。

参考：http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同