oralce 的redo 日誌包括online 的與非線上日誌，非線上日誌損壞已在本blog 裡說明了，相對簡單，現在就online 日誌的損壞進行了，試驗

create 一個test 的表

SQL> create table test as select * from dba_objects;

Table created.

SQL> insert into test select * from test;

74510 rows created.

SQL> /

149020 rows created.

SQL> /

298040 rows created.

SQL> commit;

Commit complete.

檢視一下日誌情況，redo3 為線上日誌

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------- ---------
STATUS                                           FIRST_CHANGE# FIRST_TIME
------------------------------------------------ ------------- ---------------
NEXT_CHANGE# NEXT_TIME
------------ ---------------
         1          1          1   52428800        512          1 NO
INACTIVE                                                995548 03-DEC-11
      998757 03-DEC-11

         2          1          2   52428800        512          1 NO
ACTIVE                                                  998757 03-DEC-11
     1006077 03-DEC-11

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------- ---------
STATUS FIRST_CHANGE# FIRST_TIME
------------------------------------------------ ------------- ---------------
NEXT_CHANGE# NEXT_TIME
------------ ---------------

3 1 3 52428800 512 1 NO
CURRENT 1006077 03-DEC-11
2.8147E+14

刪除線上日誌redo3.log

rm -rf redo3.log

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

資料庫不能正常開啟
SQL> startup;
ORACLE instance started.

Total System Global Area 296456192 bytes
Fixed Size                  2227784 bytes
Variable Size             109052344 bytes
Database Buffers          180355072 bytes
Redo Buffers                4820992 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 8956
Session ID: 1 Serial number: 5

-----檢視alter 日誌
SQL> host
:~> cd /opt/oracle/diag/rdbms/mos5200/mos5200/trace/
> tail -400 alert_mos5200.log
Sat Dec 03 03:20:14 2011
Starting ORACLE instance (normal)
****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total Shared Global Region size is 286 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 143 2048 KB Large Pages (286 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle/product/11g/db/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11g/db
System name:    Linux
Node name:      rac1
Release:        2.6.16.60-0.21-default
Version:        #1 Tue May 6 12:41:02 UTC 2008
Machine:        x86_64
VM name:        VMWare Version: 6
Using parameter settings in server-side spfile /opt/oracle/product/11g/db/dbs/spfilemos5200.ora
System parameters with non-default values:
processes                = 150
sga_target               = 284M
control_files            = "/opt/oracle/oradata/mos5200/control01.ctl"
control_files            = "/opt/oracle/oradata/mos5200/control02.ctl"
db_block_size            = 8192
compatible               = "11.2.0.0.0"
undo_tablespace          = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = ""
dispatchers              = "(PROTOCOL=TCP) (SERVICE=mos5200XDB)"
audit_file_dest          = "/opt/oracle/admin/mos5200/adump"
audit_trail              = "DB"
db_name                  = "mos5200"
open_cursors             = 300
pga_aggregate_target     = 108M
diagnostic_dest          = "/opt/oracle"
Sat Dec 03 03:20:16 2011
PMON started with pid=2, OS id=8844
Sat Dec 03 03:20:16 2011
PSP0 started with pid=3, OS id=8846
Sat Dec 03 03:20:17 2011
VKTM started with pid=4, OS id=8848 at elevated priority
VKTM running at (4)millisec precision with DBRM quantum (100)ms
Sat Dec 03 03:20:17 2011
GEN0 started with pid=5, OS id=8852
Sat Dec 03 03:20:17 2011
DIAG started with pid=6, OS id=8854
Sat Dec 03 03:20:17 2011
DBRM started with pid=7, OS id=8856
Sat Dec 03 03:20:17 2011
DIA0 started with pid=8, OS id=8858
Sat Dec 03 03:20:17 2011
MMAN started with pid=9, OS id=8860
Sat Dec 03 03:20:17 2011
DBW0 started with pid=10, OS id=8862
Sat Dec 03 03:20:17 2011
LGWR started with pid=11, OS id=8864
Sat Dec 03 03:20:17 2011
CKPT started with pid=12, OS id=8866
Sat Dec 03 03:20:17 2011
SMON started with pid=13, OS id=8868
Sat Dec 03 03:20:17 2011
RECO started with pid=14, OS id=8870
Sat Dec 03 03:20:17 2011
MMON started with pid=15, OS id=8872
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Dec 03 03:20:17 2011
MMNL started with pid=16, OS id=8874
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Sat Dec 03 03:20:17 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 3313002097
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Sat Dec 03 03:20:21 2011
ALTER DATABASE OPEN
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /opt/oracle/oradata/mos5200/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[8884] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:22913766 end:22914126 diff:360 (3 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Dec 03 03:20:25 2011
QMNC started with pid=20, OS id=8886
Completed: ALTER DATABASE OPEN
Sat Dec 03 03:20:28 2011
Starting background process CJQ0
Sat Dec 03 03:20:28 2011
CJQ0 started with pid=22, OS id=8898
Sat Dec 03 03:20:45 2011
Starting background process SMCO
Sat Dec 03 03:20:45 2011
SMCO started with pid=24, OS id=8904
Sat Dec 03 03:21:06 2011
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /opt/oracle/oradata/mos5200/redo03.log
Sat Dec 03 03:21:39 2011
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Sat Dec 03 03:22:02 2011
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089
Stopping background process CJQ0
Sat Dec 03 03:22:07 2011
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 1
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Sat Dec 03 03:22:11 2011
SMON: disabling tx recovery
SMON: disabling cache recovery
Sat Dec 03 03:22:11 2011
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 3
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sat Dec 03 03:22:12 2011
Stopping background process VKTM
Sat Dec 03 03:22:14 2011
Instance shutdown complete
Sat Dec 03 03:22:36 2011
Starting ORACLE instance (normal)
****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total Shared Global Region size is 286 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 143 2048 KB Large Pages (286 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle/product/11g/db/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11g/db
System name:    Linux
Node name:      rac1
Release:        2.6.16.60-0.21-default
Version:        #1 Tue May 6 12:41:02 UTC 2008
Machine:        x86_64
VM name:        VMWare Version: 6
Using parameter settings in server-side spfile /opt/oracle/product/11g/db/dbs/spfilemos5200.ora
System parameters with non-default values:
processes                = 150
sga_target               = 284M
control_files            = "/opt/oracle/oradata/mos5200/control01.ctl"
control_files            = "/opt/oracle/oradata/mos5200/control02.ctl"
db_block_size            = 8192
compatible               = "11.2.0.0.0"
undo_tablespace          = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = ""
dispatchers              = "(PROTOCOL=TCP) (SERVICE=mos5200XDB)"
audit_file_dest          = "/opt/oracle/admin/mos5200/adump"
audit_trail              = "DB"
db_name                  = "mos5200"
open_cursors             = 300
pga_aggregate_target     = 108M
diagnostic_dest          = "/opt/oracle"
Sat Dec 03 03:22:36 2011
PMON started with pid=2, OS id=8916
Sat Dec 03 03:22:36 2011
PSP0 started with pid=3, OS id=8918
Sat Dec 03 03:22:37 2011
VKTM started with pid=4, OS id=8920 at elevated priority
VKTM running at (4)millisec precision with DBRM quantum (100)ms
Sat Dec 03 03:22:37 2011
GEN0 started with pid=5, OS id=8924
Sat Dec 03 03:22:37 2011
DIAG started with pid=6, OS id=8926
Sat Dec 03 03:22:37 2011
DBRM started with pid=7, OS id=8928
Sat Dec 03 03:22:37 2011
DIA0 started with pid=8, OS id=8930
Sat Dec 03 03:22:37 2011
MMAN started with pid=9, OS id=8932
Sat Dec 03 03:22:37 2011
DBW0 started with pid=10, OS id=8934
Sat Dec 03 03:22:37 2011
LGWR started with pid=11, OS id=8936
Sat Dec 03 03:22:37 2011
CKPT started with pid=12, OS id=8938
Sat Dec 03 03:22:37 2011
SMON started with pid=13, OS id=8940
Sat Dec 03 03:22:38 2011
RECO started with pid=14, OS id=8942
Sat Dec 03 03:22:38 2011
MMON started with pid=15, OS id=8944
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Dec 03 03:22:38 2011
MMNL started with pid=16, OS id=8946
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Sat Dec 03 03:22:38 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 3313000190
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Sat Dec 03 03:22:42 2011
ALTER DATABASE OPEN
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_lgwr_8936.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_lgwr_8936.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_ora_8956.trc:

線上日誌redo3.log 丟失
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
Sat Dec 03 03:22:44 2011
Errors in file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_m000_8958.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/mos5200/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
System state dump requested by (instance=1, sid=8956), summary=[abnormal instance termination].
System State dumped to trace file /opt/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_diag_8926.trc
USER (ospid: 8956): terminating the instance due to error 313
Dumping diagnostic data in directory=[cdmp_20111203032244], requested by (instance=1, sid=8956), summary=[abnormal instance termination].
Checker run found 2 new persistent data failures
Instance terminated by USER, pid = 8956
> exit
exit

開啟資料庫到mount 狀態

SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
:~> export ORACLE_SID=mos5200
:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 3 03:25:25 2011

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 296456192 bytes
Fixed Size                  2227784 bytes
Variable Size             109052344 bytes
Database Buffers          180355072 bytes
Redo Buffers                4820992 bytes
Database mounted.

-----修改_allow_resetlogs_corruption 為true
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 296456192 bytes
Fixed Size                  2227784 bytes
Variable Size             109052344 bytes
Database Buffers          180355072 bytes
Redo Buffers                4820992 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1006366 generated at 12/03/2011 03:22:11 needed for thread 1
ORA-00289: suggestion : /opt/oracle/product/11g/db/dbs/arch1_3_768885075.dbf
ORA-00280: change 1006366 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL>

資料庫開啟

以上是經較幸運的開啟了，如果有ora-600 常見的2662 錯誤就要採用一些其它的方法開啟。

線上日誌損壞

相關文章