本文介绍在Oracle 10g数据库归档模式下，如何处理损坏的非当前联机日志文件。通过具体步骤演示了如何使用CLEAR命令重建日志文件，并最终恢复正常数据库操作。
2008/05/08
备份＆恢复之十：损坏非当前联机日志
测试环境：
1 操作系统：Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本：Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 设置成归档模式.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Current log sequence 18
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
4 当前的redo日志为2
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 0 YES UNUSED
2 1 NO CURRENT
3 0 YES UNUSED
5 把日志3移走,移动到文件夹“Old“中
[oracle@mzl orcl]$ mv redo03.log Old
6 关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
7 启动
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
看alert_ORCL.log日志：
Thu May 8 14:55:58 2008
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=6020
Thu May 8 14:55:58 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_5998.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 8 14:55:58 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_5998.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 8 14:55:58 2008
ARC0: STARTING ARCH PROCESSES
Thu May 8 14:55:58 2008
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thu May 8 14:55:58 2008
ORA-313 signalled during: ALTER DATABASE OPEN...
Thu May 8 14:55:58 2008
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=6022
8 用CLEAR命令重建该日志文件
SQL> alter database clear logfile group 3;
Database altered.
打开数据库
SQL> alter database open;
Database altered.
说明：
1、如果损坏的是非当前的联机日志文件，一般只需要clear就可以重建该日志文件，但是如果该数据库处于归档状态但该日志还没有归档，就需要强行clear。
2、建议clear，特别是强行clear后作一次数据库的全备份。
3、此方法适用于归档与非归档数据库
参考itpub上piner的备份与恢复案例：
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在服务器测试成功
备份＆恢复之十：损坏非当前联机日志
测试环境：
1 操作系统：Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本：Oracle10g
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 设置成归档模式.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Current log sequence 18
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18
SQL>
4 当前的redo日志为2
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 0 YES UNUSED
2 1 NO CURRENT
3 0 YES UNUSED
5 把日志3移走,移动到文件夹“Old“中
[oracle@mzl orcl]$ mv redo03.log Old
6 关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
7 启动
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
看alert_ORCL.log日志：
Thu May 8 14:55:58 2008
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=6020
Thu May 8 14:55:58 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_5998.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 8 14:55:58 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_5998.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 8 14:55:58 2008
ARC0: STARTING ARCH PROCESSES
Thu May 8 14:55:58 2008
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thu May 8 14:55:58 2008
ORA-313 signalled during: ALTER DATABASE OPEN...
Thu May 8 14:55:58 2008
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=6022
8 用CLEAR命令重建该日志文件
SQL> alter database clear logfile group 3;
Database altered.
打开数据库
SQL> alter database open;
Database altered.
说明：
1、如果损坏的是非当前的联机日志文件，一般只需要clear就可以重建该日志文件，但是如果该数据库处于归档状态但该日志还没有归档，就需要强行clear。
2、建议clear，特别是强行clear后作一次数据库的全备份。
3、此方法适用于归档与非归档数据库
参考itpub上piner的备份与恢复案例：
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在服务器测试成功
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-263320/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-263320/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
