Oracle Study之--基于ASM的TSPITR（基于表空间的完全恢复）
发表于：2025-01-31 作者：千家信息网编辑
千家信息网最后更新 2025年01月31日，Oracle Study之--基于ASM的TSPITR（基于表空间的完全恢复）系统环境：操作系统：AIX5.3-08数据库： Oracle 10gR2Understanding RMAN TSPITR
千家信息网最后更新 2025年01月31日Oracle Study之--基于ASM的TSPITR（基于表空间的完全恢复）
Oracle Study之--基于ASM的TSPITR（基于表空间的完全恢复）
系统环境：
操作系统：AIX5.3-08
数据库： Oracle 10gR2
Understanding RMAN TSPITR
In order to use TSPITR effectively, you need to understand what problems it can solve for you, what the major elements used in TSPITR are, what RMAN does during TSPITR, and limitations on when and how it can be applied.
RMAN TSPITR Concepts
Figure 8-1, "Tablespace Point-in-Time Recovery (TSPITR) Architecture" illustrates the context within which TSPITR takes place, and a general outline of the process.
Figure 8-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture
Description of "Figure 8-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture"
The figure contains the following entities:
The target instance, containing the tablespace to be recovered
The Recovery Manager client
The control file and (optional) recovery catalog, used for the RMAN repository records of backup activity
Archived redo logs and backup sets from the target database, which are the source of the reconstructed tablespace.
The auxiliary instance, an Oracle database instance used in the recovery process to perform the actual work of recovery.
There are four other important terms related to TSPITR, which will be used in the rest of this discussion:
The target time, the point in time or SCN that the tablespace will be left at after TSPITR
The recovery set, which consists of the datafiles containing the tablespaces to be recovered;
The auxiliary set, which includes datafiles required for TSPITR of the recovery set which are not themselves part of the recovery set. The auxiliary set typically includes:
The auxiliary instance has other files associated with it, such as a control file, parameter file, and online logs, but they are not part of the auxiliary set.
A copy of the SYSTEM tablespace
Datafiles containing rollback or undo segments from the target instance
In some cases, a temporary tablespace, used during the export of database objects from the auxiliary instance
The auxiliary destination, an optional location on disk which can be used to store any of the auxiliary set datafiles, control files and online logs of the auxiliary instance during TSPITR. Files stored here can be deleted after TSPITR is complete.
Performing Basic RMAN TSPITR
Having selected your tablespaces to recover and your target time, you are now ready to perform RMAN TSPITR. You have a few different options available to you:
Fully automated TSPITR--in which you specify an auxiliary destination and let RMAN manage all aspects of the TSPITR. This is the simplest way to perform TSPITR, and is recommended unless you specifically need more control over the location of recovery set files after TSPITR or auxiliary set files during TSPITR, or control over the channel configurations or some other aspect of your auxiliary instance.
Customized TSPITR with an automatic auxiliary instance--in which you base your TSPITR on the behavior of fully automated TSPITR, possibly still using an auxiliary destination, but customize one or more aspects of the behavior, such as the location of auxiliary set or recovery set files, or specifying initialization parameters or channel configurations for the auxiliary instance created and managed by RMAN.
TSPITR with your own auxiliary instance--in which you take responsibility for setting up, starting, stopping and cleaning up the auxiliary instance used in TSPITR, and possibly also manage the TSPITR process using some of the methods available in customized TSPITR with an automatic auxiliary instance.
案例分析：
用户在过去的时间点对emp1表做了误操作，需要通过物理备份恢复到过去的数据，本案例采用基于表空间的不完全恢复来实现对表的恢复！
1、案例测试环境
SQL> show parameter nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_name_convert stringdb_name string zsdbdb_unique_name string zsdbglobal_names boolean FALSEinstance_name string zsdblock_name_space stringlog_file_name_convert stringservice_names string zsdbSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------+DG1/zsdb/datafile/system.257.870003801+DG1/zsdb/datafile/undotbs1.260.870004111+DG1/zsdb/datafile/sysaux.258.870003999+DG1/zsdb/datafile/users.262.870004141+DG1/zsdb/datafile/zstb.259.870004085+DG1/zsdb/datafile/testtb.261.8700041256 rows selected.SQL> col member for a50SQL> select group#,member from v$logfile; GROUP# MEMBER---------- -------------------------------------------------- 4 +DG1/zsdb/onlinelog/group_4.266.870004663 4 +RCY1/zsdb/onlinelog/group_4.256.870004669 5 +DG1/zsdb/onlinelog/group_5.267.870004689 5 +RCY1/zsdb/onlinelog/group_5.257.870004693SQL> set linesize 120SQL> r 1* select file_id,file_name,tablespace_name from dba_data_files FILE_ID FILE_NAME TABLESPACE_NAME---------- -------------------------------------------------- ------------------------------ 4 +DG1/zsdb/datafile/users.262.870004141 USERS 3 +DG1/zsdb/datafile/sysaux.258.870003999 SYSAUX 2 +DG1/zsdb/datafile/undotbs1.260.870004111 UNDOTBS1 1 +DG1/zsdb/datafile/system.257.870003801 SYSTEM 5 +DG1/zsdb/datafile/zstb.259.870004085 ZSTB 6 +DG1/zsdb/datafile/testtb.261.870004125 TESTTB6 rows selected.
2、建立测试
SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLETB_EMP TABLETB_DEPT TABLETB1 TABLEEMP1 TABLE8 rows selected.SQL> select table_name,tablespace_name from user_tables;TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------DEPT USERSEMP USERSBONUS USERSSALGRADE USERSTB_EMP TESTTBTB_DEPT TESTTBTB1 USERSEMP1 ZSTB8 rows selected.10:43:58 SQL> insert into emp1 select * from emp;14 rows created.10:44:10 SQL> commit;Commit complete.10:44:12 SQL> select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 9999 SMITH CLERK 7902 17-DEC-80 800 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1015 rows selected.10:44:34 SQL> update emp1 set sal=9000 ; 15 rows updated.10:44:47 SQL> commit;Commit complete.10:44:49 SQL> select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 9999 SMITH CLERK 7902 17-DEC-80 9000 20 7369 SMITH CLERK 7902 17-DEC-80 9000 20 7499 ALLEN SALESMAN 7698 20-FEB-81 9000 300 30 7521 WARD SALESMAN 7698 22-FEB-81 9000 500 30 7566 JONES MANAGER 7839 02-APR-81 9000 20 7654 MARTIN SALESMAN 7698 28-SEP-81 9000 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 9000 30 7782 CLARK MANAGER 7839 09-JUN-81 9000 10 7788 SCOTT ANALYST 7566 19-APR-87 9000 20 7839 KING PRESIDENT 17-NOV-81 9000 10 7844 TURNER SALESMAN 7698 08-SEP-81 9000 0 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7876 ADAMS CLERK 7788 23-MAY-87 9000 20 7900 JAMES CLERK 7698 03-DEC-81 9000 30 7902 FORD ANALYST 7566 03-DEC-81 9000 20 7934 MILLER CLERK 7782 23-JAN-82 9000 1015 rows selected.表被误操作前的scn：SQL> select current_scn from v$database;CURRENT_SCN----------- 988659 10:44:54 SQL> truncate table emp1 ;Table truncated.10:46:28 SQL> select * from emp1;no rows selected10:46:33 SQL> insert into emp1 select * from emp where rownum=1;1 row created.10:46:45 SQL> update emp1 set empno=9999;1 row updated.10:46:53 SQL> commit;Commit complete.10:46:56 SQL> select * from emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 9999 SMITH CLERK 7902 17-DEC-80 800 20
3、检查表空间自包含
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('ZSTB',TRUE);PL/SQL procedure successfully completed.SQL> select * from TRANSPORT_SET_VIOLATIONS t;no rows selected4、通过rman做TSPITR
[10:49:12 oracle@aix217: dbs]$mkdir /home/oracle/zsdb[10:49:23 oracle@aix217: dbs]$cd /home/oracle/zsdb[10:49:30 oracle@aix217: zsdb]$rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 28 10:49:35 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ZSDB (DBID=3945368821)RMAN> recover tablespace zstb until scn 9886592> ;Starting recover at 28-JAN-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=125 devtype=DISKRMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in timeList of tablespaces expected to have UNDO segmentstablespace SYSTEMtablespace UNDOTBS1Creating automatic instance, with SID='pgoi'initialization parameters used for automatic instance:db_name=ZSDBcompatible=10.2.0.1.0db_block_size=8192db_files=200db_unique_name=tspitr_ZSDB_pgoilarge_pool_size=1Mshared_pool_size=110M#No auxiliary parameter file used#No auxiliary destination in use#Use default controlfilestarting up automatic instance ZSDBOracle instance startedTotal System Global Area 205520896 bytesFixed Size 2019680 bytesVariable Size 146804384 bytesDatabase Buffers 50331648 bytesRedo Buffers 6365184 bytesAutomatic instance createdRemoving automatic instanceshutting down automatic instance Oracle instance shut downAutomatic instance removedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 01/28/2015 10:50:09RMAN-05002: aborting point-in-time tablespace recoveryRMAN-05517: temporary file +DG1/zsdb/tempfile/temp.265.870004489 conflicts with file used by target databaseRMAN-05001: auxiliary filename +DG1/zsdb/datafile/undotbs1.260.870004111 conflicts with a file used by the target databaseRMAN-05001: auxiliary filename +DG1/zsdb/datafile/system.257.870003801 conflicts with a file used by the target database
---在做TSPITR时，需要建立Auxiliary Instance，需要转储system、undotbs，应该将Auxiliary Instance的datafile与原库存储到不同的diskgroup。
5、建立ASM diskgroup
[11:05:43 root@aix217: /]#lsvg rootvgVOLUME GROUP: rootvg VG IDENTIFIER: 0000c9a20000d700000001447571be5bVG STATE: active PP SIZE: 128 megabyte(s)VG PERMISSION: read/write TOTAL PPs: 546 (69888 megabytes)MAX LVs: 256 FREE PPs: 85 (10880 megabytes)LVs: 14 USED PPs: 461 (59008 megabytes)OPEN LVs: 12 QUORUM: 2 (Enabled)TOTAL PVs: 1 VG DESCRIPTORS: 2STALE PVs: 0 STALE PPs: 0ACTIVE PVs: 1 AUTO ON: yesMAX PPs per VG: 32512 MAX PPs per PV: 1016 MAX PVs: 32LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: noHOT SPARE: no BB POLICY: relocatable [11:06:27 root@aix217: /]#mklv -y 'lv_aux' -t 'raw' rootvg 30lv_aux[11:07:39 root@aix217: /]#ls -l /dev |grep auxbrw-rw---- 1 root system 10, 15 Jan 28 11:07 lv_auxbrw-rw---- 1 oracle dba 70, 7 Jan 14 16:26 rac_sysauxcrw-rw---- 1 root system 10, 15 Jan 28 11:07 rlv_auxcrw-rw---- 1 oracle dba 70, 7 Jan 14 16:26 rrac_sysaux[11:07:50 root@aix217: /]#chown oracle:dba /dev/*lv_aux[11:08:21 root@aix217: /]#chmod 660 /dev/*lv_aux[11:08:29 root@aix217: /]#ls -l /dev |grep auxbrw-rw---- 1 oracle dba 10, 15 Jan 28 11:07 lv_auxcrw-rw---- 1 oracle dba 10, 15 Jan 28 11:07 rlv_auxSQL> create diskgroup dg2 external redundancy 2 disk '/dev/rlv_aux';Diskgroup created.SQL> select name ,state from v$asm_diskgroup;NAME STATE------------------------------ -----------DG1 MOUNTEDRCY1 MOUNTEDDG2 MOUNTED
6、再次做TSPITR
[11:13:58 oracle@aix217: ~]$rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 28 11:14:07 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT rowRMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rowsORACLE error from target database: ORA-29701: unable to connect to Cluster Managerconnected to target database: ZSDB (DBID=3945368821)RMAN> RUN2> {3> SET NEWNAME FOR DATAFILE 1 to '+dg2';4> SET NEWNAME FOR DATAFILE 2 to '+dg2';5> RECOVER TABLESPACE zstb until scn 9886596> AUXILIARY DESTINATION '+DG2';}7> executing command: SET NEWNAMEusing target database control file instead of recovery catalogexecuting command: SET NEWNAMEStarting recover at 28-JAN-15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=125 devtype=DISKRMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in timeList of tablespaces expected to have UNDO segmentstablespace SYSTEMtablespace UNDOTBS1建立并启动辅助实例：Creating automatic instance, with SID='qauC'initialization parameters used for automatic instance:db_name=ZSDBcompatible=10.2.0.1.0db_block_size=8192db_files=200db_unique_name=tspitr_ZSDB_qauClarge_pool_size=1Mshared_pool_size=110M#No auxiliary parameter file useddb_create_file_dest=+DG2control_files=+DG2/cntrl_tspitr_ZSDB_qauC.fstarting up automatic instance ZSDBOracle instance startedTotal System Global Area 205520896 bytesFixed Size 2019680 bytesVariable Size 146804384 bytesDatabase Buffers 50331648 bytesRedo Buffers 6365184 bytesAutomatic instance createdcontents of Memory Script:{# set the until clauseset until scn 988659;# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online log for tspitr to a resent until timesql 'alter system archive log current';# avoid unnecessary autobackups for structural changes during TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';}executing Memory Scriptexecuting command: SET until clauseStarting restore at 28-JAN-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=33 devtype=DISKchannel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /backup/backupctl_ZSDB_ncptrifa_1_1.ctlchannel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/backupctl_ZSDB_ncptrifa_1_1.ctl tag=BKCTLchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:38output filename=+DG2/cntrl_tspitr_zsdb_qauc.fFinished restore at 28-JAN-15sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;released channel: ORA_DISK_1released channel: ORA_AUX_DISK_1contents of Memory Script:{# generated tablespace point-in-time recovery script# set the until clauseset until scn 988659;plsql <<<-- tspitr_2declare sqlstatement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539);begin sqlstatement := 'alter tablespace '|| 'ZSTB' ||' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement);exception when offline_not_needed then null;end; >>>;# set a destination filename for restoreset newname for datafile 1 to "+DG2";# set a destination filename for restoreset newname for datafile 2 to "+DG2";# set an omf destination tempfileset newname for clone tempfile 2 to new;# set a destination filename for restoreset newname for datafile 5 to "+DG1/zsdb/datafile/zstb.259.870004085";# rename all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 5;switch clone datafile all;#online the datafiles restored or flippedsql clone "alter database datafile 1 online";#online the datafiles restored or flippedsql clone "alter database datafile 2 online";#online the datafiles restored or flippedsql clone "alter database datafile 5 online";# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace "ZSTB", "SYSTEM", "UNDOTBS1" delete archivelog;alter clone database open resetlogs;# PLUG HERE the creation of a temporary tablespace if export fails due to lack# of temporary space.# For example in Unix these two lines would do that:#sql clone "create tablespace aux_tspitr_tmp# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";}executing Memory Scriptexecuting command: SET until clausesql statement: alter tablespace ZSTB offline for recoverexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed temporary file 2 to +DG2 in control fileStarting restore at 28-JAN-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=36 devtype=DISKchannel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00002 to +DG2restoring datafile 00005 to +DG1/zsdb/datafile/zstb.259.870004085channel ORA_AUX_DISK_1: reading from backup piece /backup/inc0_ZSDB_mbptna6f_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc0_ZSDB_maptna6d_1_1 tag=INC0channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 28-JAN-15datafile 1 switched to datafile copyinput datafile copy recid=48 stamp=870175071 filename=+DG2/tspitr_zsdb_qauc/datafile/system.258.870175007datafile 2 switched to datafile copyinput datafile copy recid=49 stamp=870175071 filename=+DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973sql statement: alter database datafile 1 onlinesql statement: alter database datafile 2 onlinesql statement: alter database datafile 5 onlineStarting recover at 28-JAN-15using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_mhptnb2j_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_mhptnb2j_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_miptnb2l_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_miptnb2l_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_moptnbuk_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_moptnbuk_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_mpptnbum_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_mpptnbum_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_mvptncqp_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_mvptncqp_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_n0ptncqr_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_n0ptncqr_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_n6ptridv_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_n6ptridv_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02channel ORA_AUX_DISK_1: starting incremental datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_n7ptrie1_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/inc1_ZSDB_n7ptrie1_1_1 tag=INC1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01starting media recoverychannel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=181channel ORA_AUX_DISK_1: reading from backup piece /backup/arch_ZSDB_nbptriev_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/arch_ZSDB_nbptriev_1_1 tag=BACKCHchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_181_869671369.dbf thread=1 sequence=181channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_181_869671369.dbf recid=568 stamp=870175096channel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=182channel ORA_AUX_DISK_1: reading from backup piece /backup/arch_ZSDB_nhptrjca_1_1channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/backup/arch_ZSDB_nhptrjca_1_1 tag=BACKCHchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_182_869671369.dbf thread=1 sequence=182channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_182_869671369.dbf recid=569 stamp=870175100media recovery complete, elapsed time: 00:00:02Finished recover at 28-JAN-15database openedcontents of Memory Script:{# export the tablespaces in the recovery sethost 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oracleqauC\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=qauC^'\)\)\(CONNECT_DATA=\(SID=qauC\)\)\) as sysdba\" point_in_time_recover=y tablespaces= ZSTB file=tspitr_a.dmp';# shutdown clone before importshutdown clone immediate# import the tablespaces in the recovery sethost 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=tspitr_a.dmp';# online/offline the tablespace importedsql "alter tablespace ZSTB online";sql "alter tablespace ZSTB offline";# enable autobackups in case user does open resetlogs from RMAN after TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';}executing Memory Script从Auxiliary Instance导出恢复后的数据:Export: Release 10.2.0.1.0 - Production on Wed Jan 28 11:18:41 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)Note: table data (rows) will not be exportedAbout to export Tablespace Point-in-time Recovery objects...For tablespace ZSTB .... exporting cluster definitions. exporting table definitions. . exporting table EMP1EXP-00091: Exporting questionable statistics.. . exporting table TEST. exporting referential integrity constraints. exporting triggers. end point-in-time recoveryExport terminated successfully with warnings.host command completedatabase closeddatabase dismountedOracle instance shut downImport: Release 10.2.0.1.0 - Production on Wed Jan 28 11:19:25 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathAbout to import Tablespace Point-in-time Recovery objects...导入恢复后的数据到主库：import done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion). importing SYS's objects into SYS. importing SCOTT's objects into SCOTT. . importing table "EMP1". importing TEST's objects into TEST. . importing table "TEST". importing SYS's objects into SYSImport terminated successfully without warnings.host command completesql statement: alter tablespace ZSTB onlinesql statement: alter tablespace ZSTB offlinesql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;Removing automatic instanceAutomatic instance removedauxiliary instance file +DG2/cntrl_tspitr_zsdb_qauc.f deletedRemoving automatic instanceAutomatic instance removedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 01/28/2015 11:19:50ORA-15045: ASM file name '+DG2' is not in reference form错误信息： 在recovery完成后，会删除Auxiliary Instance的文件，因为文件放在ASM diskgroup下，故删除失败，可以考虑将Auxiliary Instance文件放在文件系统下。 [11:32:21 oracle@aix217: ~]$asmcmdASMCMD> lsDG1/DG2/RCY1/ASMCMD> cd dg2ASMCMD> lsTSPITR_ZSDB_QAUC/ASMCMD> cd TSPITR_ZSDB_QAUC/ASMCMD> lsDATAFILE/ONLINELOG/TEMPFILE/ASMCMD>恢复脚本： RUN { SET NEWNAME FOR DATAFILE 1 to '+dg2'; SET NEWNAME FOR DATAFILE 2 to '+dg2'; RECOVER TABLESPACE zstb until scn 988659 AUXILIARY DESTINATION '/home/oracle/zsdb';}7、验证数据恢复
SQL> conn scott/tigerConnected.SQL> select * from emp1;select * from emp1 *ERROR at line 1:ORA-00376: file 5 cannot be read at this timeORA-01110: data file 5: '+DG1/zsdb/datafile/zstb.259.870004085'SQL> conn /as sysdbaConnected.SQL> alter database datafile 5 online;Database altered.SQL> conn scott/tigerConnected.SQL> select * from emp1;select * from emp1 *ERROR at line 1:ORA-00376: file 5 cannot be read at this timeORA-01110: data file 5: '+DG1/zsdb/datafile/zstb.259.870004085'SQL> alter tablespace zstb online;Tablespace altered.SQL> set linesize 120SQL> r 1* select * from scott.emp1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 9999 SMITH CLERK 7902 17-DEC-80 9000 20 7369 SMITH CLERK 7902 17-DEC-80 9000 20 7499 ALLEN SALESMAN 7698 20-FEB-81 9000 300 30 7521 WARD SALESMAN 7698 22-FEB-81 9000 500 30 7566 JONES MANAGER 7839 02-APR-81 9000 20 7654 MARTIN SALESMAN 7698 28-SEP-81 9000 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 9000 30 7782 CLARK MANAGER 7839 09-JUN-81 9000 10 7788 SCOTT ANALYST 7566 19-APR-87 9000 20 7839 KING PRESIDENT 17-NOV-81 9000 10 7844 TURNER SALESMAN 7698 08-SEP-81 9000 0 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7876 ADAMS CLERK 7788 23-MAY-87 9000 20 7900 JAMES CLERK 7698 03-DEC-81 9000 30 7902 FORD ANALYST 7566 03-DEC-81 9000 20 7934 MILLER CLERK 7782 23-JAN-82 9000 1015 rows selected.
@至此，emp1表中被误删除的数据被恢复完成！
通过RMAN做基于表空间的不完全恢复（TSPTIR),可以在不关闭数据库的情况下快速恢复因为用户误操作而丢失的数据；相比传统的基于整个库的不完全恢复，提高了数据库的可用性和恢复的效率。
数据
空间
文件
数据库
系统
案例
环境
用户
测试
不同
操作系统
传统
信息
再次
可用性
备份
实例
情况
效率
数据恢复
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
js数据库教程
超星软件开发工程师怎么样
招商引资 软件开发
程序代码和数据库代码分别干嘛用
数据库复杂的原因
做软件开发累成狗
解决网络安全的创新性举措
中山无线软件开发报价表
rog数据库好用吗
网络技术对应四级
防控新型网络安全风险
打印机服务器 固网
华为电脑服务器怎么删除
适时修订有关网络安全管理以及
sq数据库服务器映射地址
索引数据库搭建
福建软件开发有限公司
四川联想服务器维修调试多少钱
网络安全手抄报图片壁纸ins
安徽录播服务器安装虚拟主机
ios服务器端开发
古画数据库
2个表格去重复数据库
数据库和ms哪个好考
软件开发准备工作内容
免费数据库下载
微信小程序真的不要数据库吗
北盛科技软件开发
预防和保护青少年网络安全
sql查数据库中有多少表
