本文共 21012 字,大约阅读时间需要 70 分钟。
RMAN的表级和表分区级恢复应用场景: 1、You need to recover a very small number of tables to a particular point in time. In this situation, TSPITR is not the most effective solution because it moves all the objects in the tablespace to a specified point in time. 2、You need to recover tables that have been logically corrupted or have been dropped and purged. 3、Flashback Table is not possible because the desired point-in-time is older than available undo. 4、You want to recover data that is lost after a DDL operation modified the structure of tables. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. Flashback Table cannot rewind tables through structural changes such as a truncate table operation. 译文: 1、您需要将非常少量的表恢复到特定的时间点。在这种情况下,TSPITR 不是最有效的解决方案,因为它将表空间中的所有对象都移动到指定的时间点。 2、您需要恢复已被逻辑损坏或已被删除和清除的表。 3、Flashback Table 不可用,如undo 数据已经被覆盖。 4、恢复在DDL操作修改表结构之后丢失的数据。使用Flashback表是不可能的,因为在需要的时间点和当前时间之间的表上运行一个DDL。闪回表不能通过诸如截断表操作之类的结构更改来倒表。 RMAN的表级和表分区级恢复限制: 1、Tables and table partitions belonging to SYS schema cannot be recovered. 2、Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered. 3、Tables and table partitions on standby databases cannot be recovered. 4、Tables with named NOT NULL constraints cannot be recovered with the REMAP option. RMAN的表级和表分区级恢复前提: 1、The target database must be in read-write mode. 2、The target database must be in ARCHIVELOG mode. 3、You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects. 4、To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher. 恢复方法: 1、SCN 2、Time 3、Sequence number RMAN从备份中自动处理恢复表或者表分区时的步骤: 1、Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery. 2、Determines if there is sufficient space on the target host to create the auxiliary instance that will be used during the table or partition recovery process. If the required space is not available, then RMAN displays an error and exits the recovery operation. 3、Creates an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database. You can specify the location to which the recovered data files are stored in the auxiliary database. 4、Creates a Data Pump export dump file that contains the recovered tables or table partitions. You can specify the name and the location of the export dump file used to store the metadata of the recovered tables or table partitions. 5、(Optional) Imports the Data Pump export dump file into the target instance. You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility. 6、(Optional) Renames the recovered tables or table partitions in the target database. You can also import recovered objects into a tablespace or schema that is different from the one in which they originally existed. 译: 1.确定哪些备份包含需要恢复的表或表分区,根据指定的时间来进行恢复。 2.确定目标主机上是否有足够的空间来创建将在表或分区恢复过程中使用的辅助实例。 如果需要的空间不足,那么RMAN会报错并退出恢复操作。 3.创建一个辅助数据库并恢复指定的表或表分区,并根据指定的时间来恢复指定的表或表分区到辅助数据库中。 可以指定用于存储已恢复表或表分区的元数据的导出转储文件的名称和位置。 4.创建一个数据泵导出转储文件,其中包含已恢复的表或表分区。可以指定用于存储已恢复表或表分区的元数据的导出转储文件的名称和位置。 5. (可选操作)将上一步生产的数据泵文件导入到目标实例中。您可以选择不导入包含已恢复的表或表分区到目标数据库的导出转储文件。如果您不导入导出转储文件作为恢复过程的一部分,那么您必须在稍后使用 impdp 手工导入。 6. (可选操作)在目标数据库中rename 恢复表或表分区。 PDB操作流程: -- 准备测试环境 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NO 4 PDB02 READ WRITE NO SQL> alter session set container=pdb01; Session altered. SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS BBB 6 rows selected. -- 建立测试用户 SQL> show con_name CON_NAME ------------------------------ PDB01 SQL> create user andy identified by andy default tablespace bbb; User created. SQL> grant dba to andy; Grant succeeded. -- 创建测试表: SQL> conn andy/andy@10.219.24.16:1521/pdb01 Connected. SQL> create table andy(id int); Table created. SQL> insert into andy values(1); 1 row created. SQL> commit; Commit complete. SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. -- RMAN 备份CDB --使用如下命令备份CDB的组建:ROOT,SEED,PDBS: [oracle@12c ~]$ rman target / RMAN> backup database plus archivelog; Finished Control File and SPFILE Autobackup at 21-MAY-17 说明: 关于 Oracle 12c 多租户 CDB 与 PDB 备份 请参考 ->http://blog.csdn.net/zhang123456456/article/details/71540927 -- 恢复数据 drop andy purge 表,然后执行恢复操作: SQL> conn andy/andy@10.219.24.16:1521/pdb01 SQL> select current_scn from v$database; CURRENT_SCN ----------- 2088202 SQL> drop table andy purge; Table dropped. SQL> select * from andy; ERROR at line 1: ORA-00942: table or view does not exist -- 创建辅助目录 [oracle@12c ~]$ mkdir -p /tmp/oracle/recover [oracle@12c ~]$ mkdir -p /tmp/recover/dumpfiles -- 恢复时,cdb 与 pdb 都是Open read writer 状态。 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NO 4 PDB02 READ WRITE NO -- 恢复命令 [oracle@12c ~]$ rman target / RMAN> run{ RECOVER TABLE andy.andy of pluggable database pdb01 UNTIL SCN 2088202 AUXILIARY DESTINATION '/home/oracle/tmp/oracle/recover' datapump destination '/home/oracle/tmp/recover/dumpfiles'; } 补充:恢复表不支持公共用户,开始作者使用的是公共用户做实验,报错如下,也没有很明显的提示,后换本地用户没有这类报错。 RMAN>recover table c##andy.andy_recover_t of pluggable database pdb01 until scn 2060046 auxiliary destination '/home/oracle/tmp/oracle/recover' datapump destination '/home/oracle/tmp/recover/dumpfiles'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "datapump": expecting one of: "advise, allocate, alter, analyze, associate statistics, audit, backup, begin, @, call, catalog, change, comment, commit, configure, connect, convert, copy, create, create catalog, create global, create script, create virtual, crosscheck, declare, delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback, flashback table, grant, grant catalog, grant register, host, import, insert, list, lock, merge, mount, noaudit, open, print, purge, quit, recover, register, release, rename, repair, replace, report, " RMAN-01007: at line 1 column 1 file: standard input -- 恢复查看 SQL> select * from andy; ID ---------- 1 >恢复成功 恢复过程:还原system,undo,sysaux表空间,然后read only数据库,然后重启数据库还原表所在表空间,然后expdp导出表,根据需要决定是否导入表到原PDB数据库中,最后删除辅助数据库。 整个过程对原PDB没有影响。 补充:恢复过程监控 [oracle@12c dumpfiles]$ cd /home/oracle/tmp/oracle/recover [oracle@12c recover]$ ll total 8 drwxr-x---. 6 oracle oinstall 4096 May 21 18:26 ANDYCDB drwxr-x---. 4 oracle oinstall 4096 May 21 18:35 PCAS_PITR_PDB01_ANDYCDB [oracle@12c recover]$ cd /home/oracle/tmp/recover/dumpfiles [oracle@12c dumpfiles]$ ll total 164 -rw-r-----. 1 oracle oinstall 167936 May 21 19:12 tspitr_fgxA_79856.dmp [root@12c ~]# ps -ef|grep smon oracle 3838 1 0 17:45 ? 00:00:00 ora_smon_andycdb oracle 5769 1 0 18:58 ? 00:00:00 ora_smon_fgxA root 5941 3772 0 19:03 pts/3 00:00:00 grep smon 说明:辅助实例有启动实例进程fgxA 恢复过程日志如下: RMAN> run{ RECOVER TABLE andy.andy of pluggable database pdb01 UNTIL SCN 2088202 AUXILIARY DESTINATION '/home/oracle/tmp/oracle/recover' datapump destination '/home/oracle/tmp/recover/dumpfiles'; }2> 3> 4> 5> 6> Starting recover at 21-MAY-17 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 PDB01:SYSTEM Tablespace UNDOTBS1 Tablespace PDB01:UNDOTBS1 Creating automatic instance, with SID='fgxA' initialization parameters used for automatic instance: db_name=ANDYCDB db_unique_name=fgxA_pitr_pdb01_ANDYCDB compatible=12.2.0 db_block_size=8192 db_files=200 diagnostic_dest=/home/oracle/app/oracle _system_trig_enabled=FALSE sga_target=692M processes=200 db_create_file_dest=/home/oracle/tmp/oracle/recover log_archive_dest_1='location=/home/oracle/tmp/oracle/recover' enable_pluggable_database=true _clone_one_pdb_recovery=true max_string_size=EXTENDED #No auxiliary parameter file used starting up automatic instance ANDYCDB Oracle instance started Total System Global Area 725614592 bytes Fixed Size 8797008 bytes Variable Size 205522096 bytes Database Buffers 507510784 bytes Redo Buffers 3784704 bytes Automatic instance created contents of Memory Script: { # set requested point in time set until scn 2088202; # 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 Staring restore at 21-MAY-17 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=35 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 /home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/c-4182839949-20170521-00 channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/c-4182839949-20170521-00 tag=TAG20170521T041813 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/home/oracle/tmp/oracle/recover/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl Finished restore at 21-MAY-17 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 scn 2088202; # 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 /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 3 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_temp_%u_.tmp in control file Starting restore at 21-MAY-17 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/0ss4p2c8_1_1 channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/0ss4p2c8_1_1 tag=TAG20170521T041359 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:38 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 /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00011 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_undotbs1_%u_.dbf /ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_sysaux_%u_.dbf 2.2.0/dbhome_1/dbs/0ts4p2eu_1_1 1/dbs/0ts4p2eu_1_1 tag=TAG20170521T041359 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:58 Finished restore at 21-MAY-17 datafile 1 switched to datafile copy ver/ANDYCDB/datafile/o1_mf_system_dl2wqg9o_.dbf datafile 9 switched to datafile copy ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_system_dl2wtl6g_.dbf datafile 4 switched to datafile copy ver/ANDYCDB/datafile/o1_mf_undotbs1_dl2wqgcc_.dbf datafile 11 switched to datafile copy ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_undotbs1_dl2wtlf0_.dbf datafile 3 switched to datafile copy ver/ANDYCDB/datafile/o1_mf_sysaux_dl2wqgc0_.dbf datafile 10 switched to datafile copy ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_sysaux_dl2wtl06_.db contents of Memory Script: { # set requested point in time set until scn 2088202; # online the datafiles restored or switched sql clone "alter database datafile 1 online"; sql clone 'PDB01' "alter database datafile 9 online"; sql clone "alter database datafile 4 online"; sql clone 'PDB01' "alter database datafile 11 online"; sql clone "alter database datafile 3 online"; sql clone 'PDB01' "alter database datafile 10 online"; # recover and open database read only "UNDOTBS1", "SYSAUX", "PDB01":"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 21-MAY-17 using channel ORA_AUX_DISK_1 starting media recovery app/oracle/product/12.2.0/dbhome_1/dbs/arch1_20_943753232.dbf app/oracle/product/12.2.0/dbhome_1/dbs/arch1_21_943753232.dbf _943753232.dbf thread=1 sequence=20 _943753232.dbf thread=1 sequence=21 media recovery complete, elapsed time: 00:01:32 Finished recover at 21-MAY-17 sql statement: alter database open read only contents of Memory Script: { sql clone 'alter pluggable database PDB01 open read only'; } executing Memory Script sql statement: alter pluggable database PDB01 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 = nt= ''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 725614592 bytes Fixed Size 8797008 bytes Variable Size 205522096 bytes Database Buffers 507510784 bytes Redo Buffers 3784704 bytes r/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl'' comment= ''RMAN set'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 725614592 bytes Fixed Size 8797008 bytes Variable Size 205522096 bytes Database Buffers 507510784 bytes Redo Buffers 3784704 bytes sql statement: alter database mount clone database contents of Memory Script: { # set requested point in time set until scn 2088202; # set destinations for recovery set and auxiliary set datafiles set newname for datafile 13 to new; set newname for datafile 14 to new; set newname for datafile 15 to new; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 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 Starting restore at 21-MAY-17 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=35 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 f f f 2.2.0/dbhome_1/dbs/0ts4p2eu_1_1 1/dbs/0ts4p2eu_1_1 tag=TAG20170521T041359 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16 Finished restore at 21-MAY-17 datafile 13 switched to datafile copy x50h8_.dbf datafile 14 switched to datafile copy x50l4_.dbf datafile 15 switched to datafile copy x500q_.dbf contents of Memory Script: { # set requested point in time set until scn 2088202; # online the datafiles restored or switched 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 and open resetlogs TBS1", "PDB01":"UNDOTBS1", "SYSAUX", "PDB01":"SYSAUX" delete archivelog; alter clone database open resetlogs; } executing Memory Script executing command: SET until clause sql statement: alter database datafile 13 online sql statement: alter database datafile 14 online sql statement: alter database datafile 15 online Starting recover at 21-MAY-17 using channel ORA_AUX_DISK_1 starting media recovery app/oracle/product/12.2.0/dbhome_1/dbs/arch1_20_943753232.dbf app/oracle/product/12.2.0/dbhome_1/dbs/arch1_21_943753232.dbf _943753232.dbf thread=1 sequence=20 _943753232.dbf thread=1 sequence=21 media recovery complete, elapsed time: 00:00:18 Finished recover at 21-MAY-17 database opened contents of Memory Script: { sql clone 'alter pluggable database PDB01 open'; } executing Memory Script sql statement: alter pluggable database PDB01 open contents of Memory Script: { # create directory for datapump import sql 'PDB01' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /home/oracle/tmp/recover/dumpfiles''"; # create directory for datapump export sql clone 'PDB01' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /home/oracle/tmp/recover/dumpfiles''"; } executing Memory Script p/recover/dumpfiles'' p/recover/dumpfiles'' Performing export of tables... EXPDP> Starting "SYS"."TSPITR_EXP_fgxA_Fvnl": 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/TABLE rows EXPDP> Master table "SYS"."TSPITR_EXP_fgxA_Fvnl" successfully loaded/unloaded ***** EXPDP> Dump file set for SYS.TSPITR_EXP_fgxA_Fvnl is: EXPDP> /home/oracle/tmp/recover/dumpfiles/tspitr_fgxA_79856.dmp 12:59 2017 elapsed 0 00:02:24 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_fgxA_txhb" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_fgxA_txhb": IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA rows IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 16:32 2017 elapsed 0 00:01:06 Import completed Removing automatic instance Automatic instance removed 31018DB0A1976/datafile/o1_mf_temp_dl2x08jv_.tmp deleted _dl2wzlwf_.tmp deleted inelog/o1_mf_3_dl2x6vbp_.log deleted inelog/o1_mf_2_dl2x6gt1_.log deleted inelog/o1_mf_1_dl2x6gt1_.log deleted 4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x500q_.dbf deleted 4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x50l4_.dbf deleted 4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x50h8_.dbf deleted 31018DB0A1976/datafile/o1_mf_sysaux_dl2wtl06_.dbf deleted ux_dl2wqgc0_.dbf deleted 31018DB0A1976/datafile/o1_mf_undotbs1_dl2wtlf0_.dbf deleted tbs1_dl2wqgcc_.dbf deleted 31018DB0A1976/datafile/o1_mf_system_dl2wtl6g_.dbf deleted em_dl2wqg9o_.dbf deleted l2wpytg_.ctl deleted auxiliary instance file tspitr_fgxA_79856.dmp deleted Finished recover at 21-MAY-17 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31383567/viewspace-2139645/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31383567/viewspace-2139645/