current online redo logfile 丟失的處理方法

current online redo logfile 丟失的處理方法

前面（見）做了rm -rf操作後的恢複演練，而且是在沒有任何備份的情況下。今天在做破壞性操作前，做了個rman全備，然後線上刪除全部資料庫檔案，包括控制檔案，資料檔案，線上記錄檔，歸檔檔案等。來看看有什麼方法可以讓資料庫恢複運行，由於是current redo logfile丟失，那麼本次測試是會遺失資料的，所以可以作為不完全恢複的一個案例。 

--登入資料庫進行dml操作
 [Oracle@ora10g ~]$ sqlplus / as sysdba
 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 13:40:37 2014
 

Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 With the Partitioning, OLAP and Data Mining options
 

SQL> select * from aaron8219.test1;
 

      INT
 ----------
          1
          2
 

SQL> insert into aaron8219.test1 values(3);
 

1 row created.
 

SQL> select group#,status,sequence# from v$log;
 

    GROUP# STATUS              SEQUENCE#
 ---------- ---------------- ----------
          1 INACTIVE                  1
          2 CURRENT                2
          3 INACTIVE                  0
 

--不提交，另開一個session進行rm -rf操作
 [root@ora10g ~]# cd /u01/app/oracle/oradata
 [root@ora10g oradata]# ll
 total 4
 drwxr-x--- 2 oracle oinstall 4096 Aug 25 16:09 ora10g
 [root@ora10g oradata]# rm -rf ora10g/
 [root@ora10g oradata]# ll
 total 0
 

--原session直接abort關閉，類比current online redo logfile丟失
 SQL> shutdown abort
 

--用之前剛產生的rman全備來還原資料庫各類檔案
 [oracle@ora10g ~]$ rman target /
 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 26 13:44:58 2014
 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 

connected to target database: ora10g (not mounted)
 

RMAN> restore controlfile from '/rmanbak/full_ORA10G_4175411955_20140826_02pgtq5h_1_1.bak';
 

Starting restore at 26-AUG-14
 using channel ORA_DISK_1
 

channel ORA_DISK_1: restoring control file
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
 output filename=/u01/app/oracle/oradata/ora10g/control01.ctl
 output filename=/u01/app/oracle/oradata/ora10g/control02.ctl
 output filename=/u01/app/oracle/oradata/ora10g/control03.ctl
 Finished restore at 26-AUG-14
 

RMAN> alter database mount;
 

database mounted
 released channel: ORA_DISK_1
 

RMAN> restore database;
 

Starting restore at 26-AUG-14
 Starting implicit crosscheck backup at 26-AUG-14
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=156 devtype=DISK
 Crosschecked 1 objects
 Finished implicit crosscheck backup at 26-AUG-14
 

Starting implicit crosscheck copy at 26-AUG-14
 using channel ORA_DISK_1
 Finished implicit crosscheck copy at 26-AUG-14
 

searching for all files in the recovery area
 cataloging files...
 cataloging done
 

List of Cataloged Files
 =======================
 File Name: /u01/app/oracle/flash_recovery_area/ORA10G/autobackup/2014_08_26/o1_mf_s_856615092_9zr3snsq_.bkp
 

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 /u01/app/oracle/oradata/ora10g/system01.dbf
 restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf
 restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf
 restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf
 restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf
 channel ORA_DISK_1: reading from backup piece /rmanbak/full_ORA10G_4175411955_20140826_01pgtq36_1_1.bak
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/rmanbak/full_ORA10G_4175411955_20140826_01pgtq36_1_1.bak tag=TAG20140826T123653
 channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
 Finished restore at 26-AUG-14
 

RMAN> exit
 

Recovery Manager complete.
 

由於rman並不備份線上記錄檔，剛才的rman只是做了個全庫備份和控制檔案的備份，可以看到redo01.log，redo02.log，redo03.log這3個檔案並不存在
 

[oracle@ora10g ~]$ sqlplus / as sysdba
 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 14:52:35 2014
 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 

 

Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 With the Partitioning, OLAP and Data Mining options
 

SQL> select open_mode from v$database;
 

OPEN_MODE
 ----------
 MOUNTED
 

SQL> col member for a60
 SQL> select group#,member from v$logfile;
 

    GROUP# MEMBER
 ---------- ------------------------------------------------------------
          3 /u01/app/oracle/oradata/ora10g/redo03.log
          2 /u01/app/oracle/oradata/ora10g/redo02.log
          1 /u01/app/oracle/oradata/ora10g/redo01.log
 
 SQL> alter database open resetlogs;
 alter database open resetlogs
 *
 ERROR at line 1:
 ORA-01152: file 1 was not restored from a sufficiently old backup
 ORA-01110: data file 1: '/u01/app/oracle/oradata/ora10g/system01.dbf'
 

由於只是restore了資料庫，還未recover，資料庫是無法開啟的
 

SQL> recover database
 ORA-00283: recovery session canceled due to errors
 ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 

 

SQL> recover database until cancel;
 ORA-00283: recovery session canceled due to errors
 ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 

 

SQL> recover database using backup controlfile;
 ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
 ORA-00289: suggestion :
 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc
 ORA-00280: change 502729 for thread 1 is in sequence #3
 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 auto
 ORA-00308: cannot open archived log
 '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc'
 ORA-27037: unable to obtain file status
 Linux Error: 2: No such file or directory
 Additional information: 3
 

 

ORA-00308: cannot open archived log
 '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc'
 ORA-27037: unable to obtain file status
 Linux Error: 2: No such file or directory
 Additional information: 3
 

 

SQL> recover database using backup controlfile until cancel;
 ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
 ORA-00289: suggestion :
 /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc
 ORA-00280: change 502729 for thread 1 is in sequence #3
 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 cancel
 Media recovery cancelled.
 SQL>
 

由於之前的歸檔沒有用rman備份，而現在也不存在了，無法recover資料庫，就算用resetlogs也無法open資料庫。此時，只有通過隱含參數_allow_resetlogs_corruption來open資料庫了
 

SQL> show parameter spfile
 

NAME                                TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 spfile                              string      /u01/app/oracle/product/10.2.0
                                                  /db_1/dbs/spfileora10g.ora
 

--建立pfile 
 SQL> create pfile from spfile;
 

File created.
 

--編輯$ORACLE_HOME/dbs/initora10g.ora初始化參數檔案，在檔案末尾添加*._allow_resetlogs_corruption = true
 [oracle@ora10g ora10g]$ vi $ORACLE_HOME/dbs/initora10g.ora
 

ora10g.__db_cache_size=180355072
 ora10g.__java_pool_size=4194304
 ora10g.__large_pool_size=4194304
 ora10g.__shared_pool_size=92274688
 ora10g.__streams_pool_size=0
 *.audit_file_dest='/u01/app/oracle/admin/ora10g/adump'
 *.background_dump_dest='/u01/app/oracle/admin/ora10g/bdump'
 *.compatible='10.2.0.1.0'
 *.control_files='/u01/app/oracle/oradata/ora10g/control01.ctl','/u01/app/oracle/oradata/ora10g/control02.ctl','/u01/app/oracle/oradata/ora10g/control03.ctl'#Restore Controlfile
 *.core_dump_dest='/u01/app/oracle/admin/ora10g/cdump'
 *.db_block_size=8192
 *.db_domain=''
 *.db_file_multiblock_read_count=16
 *.db_name='ora10g'
 *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
 *.db_recovery_file_dest_size=2147483648
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gXDB)'
 *.job_queue_processes=10
 *.log_archive_format='%t_%s_%r.dbf'
 *.nls_language='SIMPLIFIED CHINESE'
 *.nls_territory='CHINA'
 *.open_cursors=300
 *.pga_aggregate_target=94371840
 *.processes=150
 *.remote_login_passwordfile='EXCLUSIVE'
 *.sga_target=285212672
 *.undo_management='AUTO'
 *.undo_tablespace='UNDOTBS1'
 *.user_dump_dest='/u01/app/oracle/admin/ora10g/udump'
 *._allow_resetlogs_corruption=TRUE
 

--關閉資料庫執行個體，用pfile啟動
 SQL> shutdown immediate
 ORA-01109: database not open
 

Database dismounted.
 ORACLE instance shut down.
 SQL> startup pfile=$ORACLE_HOME/dbs/initora10g.ora
 ORACLE instance started.
 

Total System Global Area  285212672 bytes
 Fixed Size                  1218992 bytes
 Variable Size            100664912 bytes
 Database Buffers          180355072 bytes
 Redo Buffers                2973696 bytes
 Database mounted.
 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 

--最後用resetlogs開啟資料庫
 SQL> alter database open resetlogs;
 

Database altered.
 

SQL>
 

[oracle@ora10g ora10g]$ ll
 total 1051020
 -rw-r----- 1 oracle oinstall  7061504 Aug 26 16:03 control01.ctl
 -rw-r----- 1 oracle oinstall  7061504 Aug 26 16:03 control02.ctl
 -rw-r----- 1 oracle oinstall  7061504 Aug 26 16:03 control03.ctl
 -rw-r----- 1 oracle oinstall 104865792 Aug 26 16:03 example01.dbf
 -rw-r----- 1 oracle oinstall  52429312 Aug 26 16:03 redo01.log
 -rw-r----- 1 oracle oinstall  52429312 Aug 26 16:03 redo02.log
 -rw-r----- 1 oracle oinstall  52429312 Aug 26 16:03 redo03.log
 -rw-r----- 1 oracle oinstall 251666432 Aug 26 16:03 sysaux01.dbf
 -rw-r----- 1 oracle oinstall 503324672 Aug 26 16:03 system01.dbf
 -rw-r----- 1 oracle oinstall  20979712 Aug 26 16:03 temp01.dbf
 -rw-r----- 1 oracle oinstall  31465472 Aug 26 16:03 undotbs01.dbf
 -rw-r----- 1 oracle oinstall  5251072 Aug 26 16:03 users01.dbf
 [oracle@ora10g ora10g]$
 

再次查看資料檔案，發現重建了3個online redo logfile了
 

--resetlogs把SEQUENCE#序號重新變成1
 SQL> select group#,sequence# from v$log;
 

    GROUP#  SEQUENCE#
 ---------- ----------
          1          0
          2          1
          3          0
 

--測試表只留下了原來的資料庫，因為沒有commit，新插入的第3條記錄丟失，就算commit，也是會丟資料的，因為redo logfile也被刪除了 

SQL> select * from aaron8219.test1; 

      INT
 ----------
          1
          2

注意，最後要把剛才設定的隱含參數_allow_resetlogs_corruption = true去掉，並重新建立spfile，否則資料庫將來會有引起不一致的風險，設定該隱含參數只是下下策，為了能讓資料庫open而不得已為之，遺失資料庫是肯定的了，因為這是在極端情況下得測試，通常只有在丟失了狀態為current的online redo logfile才會遺失資料，如果只是inactive的redo logfile，就算使用alter database open resetlogs;也不意味著100%丟資料。另外，要注意的是，當使用using backup controlfile關鍵詞時，必須要配合使用open resetlogs來開啟資料庫。以resetlogs方式open資料庫後，必須重新做一次資料庫全備，因為一旦incarnation改變之後，資料庫原來的備份組就失效了。