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
在服务器测试成功