第八章：REDO日志（2）
8、日志恢复
1、多元化成员中，单个成员丢失
05:10:06 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 9 10485760 2 NO INACTIVE 384007 02-AUG-11 3 1 8 10485760 2 NO INACTIVE 384005 02-AUG-11 2 1 10 10485760 2 NO CURRENT 385481 02-AUG-1105:10:12 SQL> !
[oracle@oracle ~]$ ls /disk2/lx01/oradata/ control03.ctl redo01a.log redo02a.log redo03a.log redo04a.log redo05a.log [oracle@oracle ~]$ exit exit05:14:31 SQL> shutdown immediate
Database closed. Database dismounted. ORACLE instance shut down. 05:14:41 SQL> ! [oracle@oracle ~]$ rm /disk2/lx02/oradata/redo01a.log [oracle@oracle ~]$ !sql sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:15:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:15:02 SQL> startup
ORACLE instance started.Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes Variable Size 125830908 bytes Database Buffers 121634816 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. 05:15:12 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 9 10485760 2 NO INACTIVE 384007 02-AUG-11 3 1 8 10485760 2 NO INACTIVE 384005 02-AUG-11 2 1 10 10485760 2 NO CURRENT 385481 02-AUG-1105:15:24 SQL> desc v$logfile;
Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3） 05:15:43 SQL> col member for a50 05:15:48 SQL> r 1* select group#,member ,status from v$logfileGROUP# MEMBER STATUS
---------- -------------------------------------------------- ------- 2 /disk2/lx02/oradata/redo02a.log 1 /disk2/lx02/oradata/redo01a.log INVALID 3 /disk2/lx02/oradata/redo03a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 3 /disk1/lx02/oradata/redo03b.log6 rows selected.
05:15:48 SQL>
告警日志：
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9105.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/disk2/lx02/oradata/redo01a.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3解决：
05:15:48 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 05:17:47 SQL> ! [oracle@oracle ~]$ cp /disk1/lx02/oradata/redo01b.log /disk2/lx02/oradata/redo01a.log [oracle@oracle ~]$ !sql sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:18:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:18:02 SQL> startup
ORACLE instance started.Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes Variable Size 125830908 bytes Database Buffers 121634816 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. 05:18:14 SQL> col member for a50 05:18:26 SQL> select group#,member ,status from v$logfile 05:18:29 2 ;GROUP# MEMBER STATUS
---------- -------------------------------------------------- ------- 2 /disk2/lx02/oradata/redo02a.log 1 /disk2/lx02/oradata/redo01a.log INVALID 3 /disk2/lx02/oradata/redo03a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 3 /disk1/lx02/oradata/redo03b.log6 rows selected.
05:18:31 SQL> alter system switch logfile;
System altered.
05:18:37 SQL> /
System altered.
05:18:39 SQL> select group#,member ,status from v$logfile
05:18:40 2 ;GROUP# MEMBER STATUS
---------- -------------------------------------------------- ------- 2 /disk2/lx02/oradata/redo02a.log 1 /disk2/lx02/oradata/redo01a.log 3 /disk2/lx02/oradata/redo03a.log 1 /disk1/lx02/oradata/redo01b.log 2 /disk1/lx02/oradata/redo02b.log 3 /disk1/lx02/oradata/redo03b.log6 rows selected.
05:18:42 SQL>
2、非当前日志组所有成员丢失
05:19:42 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 12 10485760 2 NO CURRENT 386507 02-AUG-11 3 1 11 10485760 2 NO INACTIVE 386505 02-AUG-11 2 1 10 10485760 2 NO INACTIVE 385481 02-AUG-1105:19:45 SQL>
05:19:45 SQL> shutdown ORA-01109: database not open Database dismounted. ORACLE instance shut down. 05:19:59 SQL> ! [oracle@oracle ~]$ rm /disk2/lx02/oradata/redo02a.log [oracle@oracle ~]$ rm /disk1/lx02/oradata/redo02b.log [oracle@oracle ~]$ !sql sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:20:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:20:22 SQL> startup
ORACLE instance started.Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes Variable Size 125830908 bytes Database Buffers 121634816 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/disk2/lx02/oradata/redo02a.log' ORA-00312: online log 2 thread 1: '/disk1/lx02/oradata/redo02b.log' 05:20:29 SQL> alter database clear logfile group 2;Database altered.
05:21:00 SQL> alter database open;
Database altered.
05:21:08 SQL>
3、当前日志组丢失（正常关库 shutdown immediate） 05:22:16 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 12 10485760 2 NO INACTIVE 386507 02-AUG-11 3 1 14 10485760 2 NO CURRENT 386751 02-AUG-11 2 1 13 10485760 2 NO ACTIVE 386654 02-AUG-1105:22:17 SQL>
05:22:17 SQL> shutdown immediate
Database closed. Database dismounted. ORACLE instance shut down. 05:22:36 SQL> ! [oracle@oracle ~]$ rm /disk2/lx02/oradata/redo03a.log [oracle@oracle ~]$ rm /disk1/lx02/oradata/redo03b.log [oracle@oracle ~]$ !sql sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:23:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
05:23:03 SQL> startup
ORACLE instance started.Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes Variable Size 125830908 bytes Database Buffers 121634816 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log' ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log' 05:23:10 SQL> 告警日志： Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9314.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Tue Aug 2 05:23:10 2011 Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9314.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-313 signalled during: ALTER DATABASE OPEN...解决：
05:23:10 SQL> alter database clear logfile group 3;
alter database clear logfile group 3 * ERROR at line 1: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 --------对于当前日志组不能clear05:24:04 SQL> recover database until cancel;
Media recovery complete. 05:24:23 SQL> alter database open resetlogs;Database altered.
05:24:41 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 2 10485760 2 NO CURRENT 386892 02-AUG-11 3 1 1 10485760 2 NO INACTIVE 386891 02-AUG-11 2 1 0 10485760 2 YES UNUSED 005:24:44 SQL> alter system switch logfile;
System altered.
05:26:28 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 2 10485760 2 NO ACTIVE 386892 02-AUG-11 3 1 1 10485760 2 NO INACTIVE 386891 02-AUG-11 2 1 3 10485760 2 NO CURRENT 387003 02-AUG-1105:26:29 SQL>
--------------非正常关库，当前日志组文件丢失
修改pfile文件，添加_allow_resetlogs_corruption=TRUE
[oracle@work dbs]$ vi inittest.ora
_allow_resetlogs_corruption=TRUE
*.background_dump_dest='$ORACLE_BASE/admin/test/bdump' *.control_files='/u01/app/oracle/oradata/test/control01.ctl','/disk1/oradata/test/control02.ctl','/disk2/oradata/test/control03.ctl' *.core_dump_dest='$ORACLE_BASE/admin/test/cdump' *.db_block_size=8192 *.db_cache_size=80M#DEMO *.db_file_multiblock_read_count=16 *.db_name='test' *.optimizer_mode='choose' *.parallel_threads_per_cpu=4#SMALL *.pga_aggregate_target=10485760 *.query_rewrite_enabled='true' *.query_rewrite_integrity='trusted' *.shared_pool_size=60M#DEMO *.star_transformation_enabled='true' *.undo_management='auto' *.undo_tablespace='rtbs' *.user_dump_dest='$ORACLE_BASE/admin/test/udump' ---------再以pfile 启动instance 到mount 然后 alter database open resetlogs