联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、案例说明
利用rman备份数据库后,因为人工误删除表空间,现在需要使用非完全恢复来找回被误删除的表空间
二、环境准备
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 12:35:14 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS XFF ODU TEMP 7 rows selected. SQL> select name from v$datafile; NAME --------------------------------------------------------------- /opt/oracle/oradata/test/system01.dbf /opt/oracle/oradata/test/undotbs01.dbf /opt/oracle/oradata/test/sysaux01.dbf /opt/oracle/oradata/test/users01.dbf /opt/oracle/oradata/test/user32g.dbf /opt/oracle/oradata/test/xifenfei01.dbf /opt/oracle/oradata/test/user02.dbf /opt/oracle/oradata/test/odu02.dbf /opt/oracle/oradata/test/odu01.dbf /opt/oracle/oradata/test/odu03.dbf /opt/oracle/oradata/test/xifenfei02.dbf 11 rows selected. SQL> create tablespace xifenfei datafile 2 '/opt/oracle/oradata/test/t_xifenfei01.dbf' size 10m ; Tablespace created. SQL> create table chf.t_xifenfei tablespace xifenfei 2 as 3 select * from dba_objects; Table created. SQL> select count(*) from chf.t_xifenfei; COUNT(*) ---------- 50476 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 14 12:43:35 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: TEST (DBID=2056006906) RMAN> list backup summary; using target database control file instead of recovery catalog RMAN> backup database format '/tmp/test_full_%U'; Starting backup at 2011-11-14 12:44:32 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=134 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00008 name=/opt/oracle/oradata/test/odu02.dbf input datafile fno=00002 name=/opt/oracle/oradata/test/undotbs01.dbf input datafile fno=00001 name=/opt/oracle/oradata/test/system01.dbf input datafile fno=00003 name=/opt/oracle/oradata/test/sysaux01.dbf input datafile fno=00009 name=/opt/oracle/oradata/test/odu01.dbf input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf input datafile fno=00005 name=/opt/oracle/oradata/test/user32g.dbf input datafile fno=00007 name=/opt/oracle/oradata/test/user02.dbf input datafile fno=00010 name=/opt/oracle/oradata/test/odu03.dbf input datafile fno=00011 name=/opt/oracle/oradata/test/xifenfei02.dbf input datafile fno=00012 name=/opt/oracle/oradata/test/t_xifenfei01.dbf input datafile fno=00004 name=/opt/oracle/oradata/test/users01.dbf channel ORA_DISK_1: starting piece 1 at 2011-11-14 12:44:33 channel ORA_DISK_1: finished piece 1 at 2011-11-14 12:48:59 piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:04:26 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 2011-11-14 12:49:02 channel ORA_DISK_1: finished piece 1 at 2011-11-14 12:49:03 piece handle=/tmp/test_full_02mrkqlr_1_1 tag=TAG20111114T124433 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 2011-11-14 12:49:03 RMAN> exit Recovery Manager complete. [oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 12:50:53 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop tablespace xifenfei including contents and datafiles; Tablespace dropped. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ECP-UC-DB1 ~] cd /opt/oradata [oracle@ECP-UC-DB1 oradata]$ mv test test_bak [oracle@ECP-UC-DB1 oradata]$ mkdir test [oracle@ECP-UC-DB1 oradata]$ ll total 16 drwxr-x--- 3 oracle oinstall 4096 Aug 12 21:50 ecp drwxr-x--- 3 oracle oinstall 4096 Jun 25 14:23 ecp_bak drwxr-xr-x 2 oracle oinstall 4096 Nov 14 12:53 test drwxr-x--- 3 oracle oinstall 4096 Nov 14 12:51 test_bak
通过alert日志,查找出删除表空间xifenfei的时间:Mon Nov 14 12:49:102011
三、恢复测试
[oracle@ECP-UC-DB1 oradata]$ $ORACLE_HOME/bin/rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 14 12:58:47 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> startup Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 11/14/2011 12:58:56 ORA-00205: error in identifying control file, check alert log for more info RMAN> restore controlfile from '/tmp/test_full_02mrkqlr_1_1'; Starting restore at 2011-11-14 12:59:15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output filename=/opt/oracle/oradata/test/control01.ctl output filename=/opt/oracle/oradata/test/control02.ctl output filename=/opt/oracle/oradata/test/control03.ctl Finished restore at 2011-11-14 12:59:19 RMAN> restore database; Starting restore at 2011-11-14 13:00:16 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 11/14/2011 13:00:16 ORA-01507: database not mounted RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore database; Starting restore at 2011-11-14 13:00:32 Starting implicit crosscheck backup at 2011-11-14 13:00:32 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK Crosschecked 1 objects Finished implicit crosscheck backup at 2011-11-14 13:00:33 Starting implicit crosscheck copy at 2011-11-14 13:00:33 using channel ORA_DISK_1 Finished implicit crosscheck copy at 2011-11-14 13:00:33 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /opt/oracle/oradata/test/system01.dbf restoring datafile 00002 to /opt/oracle/oradata/test/undotbs01.dbf restoring datafile 00003 to /opt/oracle/oradata/test/sysaux01.dbf restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf restoring datafile 00005 to /opt/oracle/oradata/test/user32g.dbf restoring datafile 00006 to /opt/oracle/oradata/test/xifenfei01.dbf restoring datafile 00007 to /opt/oracle/oradata/test/user02.dbf restoring datafile 00008 to /opt/oracle/oradata/test/odu02.dbf restoring datafile 00009 to /opt/oracle/oradata/test/odu01.dbf restoring datafile 00010 to /opt/oracle/oradata/test/odu03.dbf restoring datafile 00011 to /opt/oracle/oradata/test/xifenfei02.dbf restoring datafile 00012 to /opt/oracle/oradata/test/t_xifenfei01.dbf channel ORA_DISK_1: reading from backup piece /tmp/test_full_01mrkqdh_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433 channel ORA_DISK_1: restore complete, elapsed time: 00:07:08 Finished restore at 2011-11-14 13:07:42 RMAN> run 2> { 3> sql 'alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"'; 4> set until time='2011-11-14 12:49:10'; 5> recover database; 6> } sql statement: alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss" executing command: SET until clause Starting recover at 2011-11-14 13:18:09 using channel ORA_DISK_1 starting media recovery unable to find archive log archive log thread=1 sequence=248 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/14/2011 13:18:10 RMAN-06054: media recovery requesting unknown log: thread 1 seq 248 lowscn 11517136 --另外打开一个会话查询当前最大的归档日志seq#情况 [oracle@ECP-UC-DB1 archivelog]$ ll -thr|tail -10 -rw-r----- 1 oracle oinstall 45M Nov 5 19:00 1_238_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 7 10:00 1_239_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 8 02:25 1_240_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 8 22:25 1_241_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 9 22:26 1_242_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 10 22:01 1_243_757860476.dbf -rw-r----- 1 oracle oinstall 46M Nov 11 22:01 1_244_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 12 00:00 1_245_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 13 07:26 1_246_757860476.dbf -rw-r----- 1 oracle oinstall 45M Nov 14 07:27 1_247_757860476.dbf --证明最大的seq为247,而恢复需要日志的seq为248,就是说需要应用未归档的redo log --那么我们采用在sqlplus中恢复 RMAN> exit Recovery Manager complete. [oracle@ECP-UC-DB1 oradata]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 13:21:24 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss" 2 ; Session altered. SQL> recover database until time '2011-11-14 12:49:10' using backup controlfile; ORA-00279: change 11517136 generated at 11/14/2011 12:44:33 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/test/archivelog1_248_757860476.dbf ORA-00280: change 11517136 for thread 1 is in sequence #248 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /opt/oracle/oradata/test_bak/redo01.log ORA-00310: archived log contains sequence 247; sequence 248 required ORA-00334: archived log: '/opt/oracle/oradata/test_bak/redo01.log' SQL> recover database until time '2011-11-14 12:49:10' using backup controlfile; ORA-00279: change 11517136 generated at 11/14/2011 12:44:33 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/test/archivelog1_248_757860476.dbf ORA-00280: change 11517136 for thread 1 is in sequence #248 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /opt/oracle/oradata/test_bak/redo02.log Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX USERS XFF ODU TEMP XIFENFEI 8 rows selected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------- /opt/oracle/oradata/test/system01.dbf /opt/oracle/oradata/test/undotbs01.dbf /opt/oracle/oradata/test/sysaux01.dbf /opt/oracle/oradata/test/users01.dbf /opt/oracle/oradata/test/user32g.dbf /opt/oracle/oradata/test/xifenfei01.dbf /opt/oracle/oradata/test/user02.dbf /opt/oracle/oradata/test/odu02.dbf /opt/oracle/oradata/test/odu01.dbf /opt/oracle/oradata/test/odu03.dbf /opt/oracle/oradata/test/xifenfei02.dbf /opt/oracle/oradata/test/t_xifenfei01.dbf 12 rows selected. SQL> select count(*) from chf.t_xifenfei; COUNT(*) ---------- 50476