當前日誌損壞的案例(轉)
[oracle@ts01 oracle]$ sqlplus ''''/ as sysdba''''
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 15 10:35:11 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn / as sysdba
Connected.
SQL>
在另一個SESSION:
[oracle@ts01 oracle]$ cd /oracle/oradata/TSMISC02
[oracle@ts01 TSMISC02]$ ll
total 681820
drwxr-xr-x 2 oracle oinstall 4096 Nov 15 08:52 archive
-rw-r----- 1 oracle oinstall 1662976 Nov 15 10:35 control01.ctl
-rw-r----- 1 oracle oinstall 1662976 Nov 15 10:35 control02.ctl
-rw-r----- 1 oracle oinstall 1662976 Nov 15 10:35 control03.ctl
-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 drsys01.dbf
-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 indx01.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 odm01.dbf
-rw-r----- 1 oracle oinstall 2097664 Nov 15 10:35 redo01.log
-rw-r----- 1 oracle oinstall 2097664 Nov 15 05:48 redo02.log
-rw-r----- 1 oracle oinstall 2097664 Nov 15 08:52 redo03.log
-rw-r----- 1 oracle oinstall 387981312 Nov 15 10:26 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Nov 15 09:23 system02.dbf
-rw-r----- 1 oracle oinstall 42999808 Nov 1 14:16 temp01.dbf
-rw-r----- 1 oracle oinstall 10493952 Nov 15 09:23 tools01.dbf
-rw-r----- 1 oracle oinstall 52436992 Nov 15 10:28 undotbs2.dbf
-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 users01.dbf
-rw-r----- 1 oracle oinstall 47194112 Nov 15 09:23 xdb01.dbf
[oracle@ts01 TSMISC02]$ cat redo01.log| od -x|head
0000000 0200 0000 0200 0000 1000 0000 5c5d 5a5b
0000020 0000 0000 1606 0000 0000 0000 0000 0000
0000040 0000 0000 0000 0000 0000 0000 0000 0000
*
0001000 003e 0000 0001 0000 f0c1 223c 0000 fec1
0001020 0000 0920 0000 0920 98c2 6798 5354 494d
0001040 4353 3230 01ec 0000 1000 0000 0200 0000
0001060 0001 0002 15c2 6798 0000 0000 0000 0000
0001100 0000 0000 0000 0000 0000 0000 0000 0000
0001120 0000 0000 0000 0000 0000 0000 6854 6572
[oracle@ts01 TSMISC02]$ >redo01.log
[oracle@ts01 TSMISC02]$ ll
total 679764
drwxr-xr-x 2 oracle oinstall 4096 Nov 15 08:52 archive
-rw-r----- 1 oracle oinstall 1662976 Nov 15 11:12 control01.ctl
-rw-r----- 1 oracle oinstall 1662976 Nov 15 11:12 control02.ctl
-rw-r----- 1 oracle oinstall 1662976 Nov 15 11:12 control03.ctl
-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 drsys01.dbf
-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 indx01.dbf
-rw-r----- 1 oracle oinstall 20979712 Nov 15 09:23 odm01.dbf
-rw-r----- 1 oracle oinstall 0 Nov 15 11:12 redo01.log
-rw-r----- 1 oracle oinstall 2097664 Nov 15 05:48 redo02.log
-rw-r----- 1 oracle oinstall 2097664 Nov 15 08:52 redo03.log
-rw-r----- 1 oracle oinstall 387981312 Nov 15 10:57 system01.dbf
-rw-r----- 1 oracle oinstall 52436992 Nov 15 09:23 system02.dbf
-rw-r----- 1 oracle oinstall 42999808 Nov 1 14:16 temp01.dbf
-rw-r----- 1 oracle oinstall 10493952 Nov 15 09:23 tools01.dbf
-rw-r----- 1 oracle oinstall 52436992 Nov 15 11:11 undotbs2.dbf
-rw-r----- 1 oracle oinstall 26222592 Nov 15 09:23 users01.dbf
-rw-r----- 1 oracle oinstall 47194112 Nov 15 09:23 xdb01.dbf
[oracle@ts01 TSMISC02]$
回到SESSION2:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 62 2097152 1 NO CURRENT
658443 15-NOV-05
2 1 60 2097152 1 YES INACTIVE
641419 15-NOV-05
3 1 61 2097152 1 YES INACTIVE
649920 15-NOV-05
SQL> set linesize 132
SQL> l
1* select * from v$log
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 2097152 1 NO CURRENT 658443 15-NOV-05
2 1 60 2097152 1 YES INACTIVE 641419 15-NOV-05
3 1 61 2097152 1 YES INACTIVE 649920 15-NOV-05
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
Connected to an idle instance.
SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
啟動資料庫,
'tGbGe.3O2AT4
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: ''''/oracle/oradata/TSMISC02/redo01.log''''
SQL>
SQL> select name,OPEN_MODE from v$database;
NAME OPEN_MODE
--------- ----------
TSMISC02 MOUNTED
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 TSMISC02
ts01
9.2.0.4.0 15-NOV-05 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
SQL> set linesize 132
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 2097152 1 NO INVALIDATED 658443 15-NOV-05
2 1 0 2097152 1 YES UNUSED 0 15-NOV-05
3 1 61 2097152 1 YES INACTIVE 649920 15-NOV-05
SQL> col member for a100
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
1 ONLINE /oracle/oradata/TSMISC02/redo01.log
2 ONLINE /oracle/oradata/TSMISC02/redo02.log
3 ONLINE /oracle/oradata/TSMISC02/redo03.log
SQL>
檢查alert日誌:
Tue Nov 15 11:30:13 2005
Started first pass scan
Tue Nov 15 11:30:13 2005
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_29582.trc:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: ''''/oracle/oradata/TSMISC02/redo01.log''''
ORA-316 signalled during: ALTER DATABASE OPEN...
Tue Nov 15 11:31:09 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Tue Nov 15 11:36:10 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Tue Nov 15 11:41:11 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Tue Nov 15 11:46:12 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Tue Nov 15 11:51:13 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Tue Nov 15 11:56:14 2005
Restarting dead background process QMN0
QMN0 started with pid=9
。。。
相關trace檔案內容:
[oracle@ts01 udump]$ ll tsmisc02_ora_29582.trc
-rw-r----- 1 oracle oinstall 912 Nov 15 11:30 tsmisc02_ora_29582.trc
[oracle@ts01 udump]$ cat tsmisc02_ora_29582.trc
/oracle/admin/TSMISC02/udump/tsmisc02_ora_29582.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /oracle/product/920
System name: Linux
Node name: ts01
Release: 2.4.21-4.EL
Version: #1 Fri Oct 3 18:13:58 EDT 2003
Machine: i686
Instance name: TSMISC02
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 29582, image: oracle@ts01 (TNS V1-V3)
*** SESSION ID:(11.3) 2005-11-15 11:30:13.297
Thread checkpoint rba:0x00003e.00000002.0010 scn:0x0000.000a0c0b
On-disk rba:0x00003e.00000c1e.0000 scn:0x0000.000a257e
Use incremental checkpoint cache-low RBA
Thread 1 recovery from rba:0x00003e.0000095f.0000 scn:0x0000.00000000
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: ''''/oracle/oradata/TSMISC02/redo01.log''''
[oracle@ts01 udump]$
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: ''''/oracle/oradata/TSMISC02/redo01.log''''
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: ''''/oracle/oradata/TSMISC02/redo01.log''''
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/oradata/TSMISC02/archive
Oldest online log sequence 61
Next log sequence to archive 62
Current log sequence 62
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 62 2097152 1 NO INVALIDATED 658443 15-NOV-05
2 1 0 2097152 1 YES UNUSED 0 15-NOV-05
3 1 61 2097152 1 YES INACTIVE 649920 15-NOV-05
SQL>
斷定,管{YoU2Vl軟]1W的;UgXDj_垠(W,網
設定兩個隱含引數:_ALLOW_RESETLOGS_CORRUPTION = TRUE 和 _corrupted_rollback_segments ,因為redo損壞的時候,undo資料也大都不一致了。
使用隱含引數啟動資料庫:
SQL> create pfile=''''/home/oracle/pfile.tmp'''' from spfile;
File created.
SQL>
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@ts01 oracle]$ tail pfile.tmp
*.sort_area_size=524288
*.star_transformation_enabled=''''FALSE''''
*.timed_statistics=TRUE
*.undo_management=''''AUTO''''
*.undo_retention=10800
*.undo_tablespace=''''UNDOTBS2''''
*.user_dump_dest=''''/oracle/admin/TSMISC02/udump''''
_ALLOW_RESETLOGS_CORRUPTION = TRUE
[oracle@ts01 oracle]$
這個引數的解釋:
SQL> select KSPPDESC from X$KSPPI where ksppinm=''''_allow_resetlogs_corruption'''';
KSPPDESC
----------------------------------------------------------------
allow resetlogs even if it will cause corruption
SQL>
[oracle@ts01 oracle]$ sqlplus ''''/ as sysdba''''
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 21 15:57:21 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile=pfile.tmp
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: ''''/oracle/oradata/TSMISC02/redo01.log''''
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 658443 generated at 11/15/2005 08:52:17 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/TSMISC02/archive/1_62.dbf
ORA-00280: change 658443 for thread 1 is in sequence #62
Specify log: {
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ''''/oracle/oradata/TSMISC02/system01.dbf''''
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
。。。
很長時間,C提中絡+o3-7,FX6中CS-q
9"X專{%hKC
Y(v?Lmg5d[的育)[]供(業
就好像hang住了。。。
有過了一會:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
檢查日誌,發現:
[oracle@ts01 bdump]$ tail alert_TSMISC02.log
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_10762.trc:
ORA-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []
Mon Nov 21 16:11:44 2005
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_10762.trc:
ORA-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []
Mon Nov 21 16:11:44 2005
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 10762
ORA-1092 signalled during: alter database open resetlogs...
[oracle@ts01 bdump]$
設定event adjust_scn:
alter session set events ''''10015 trace name adjust_scn level 1'''';
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount pfile=pfile.tmp
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>
SQL> alter session set events ''''10015 trace name adjust_scn level 1'''';
Session altered.
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 658445 generated at 11/21/2005 16:11:43 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/TSMISC02/archive/1_1.dbf
ORA-00280: change 658445 for thread 1 is in sequence #1
Specify log: {
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ''''/oracle/oradata/TSMISC02/system01.dbf''''
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL>
檢查日誌:
[oracle@ts01 bdump]$ tail alert_TSMISC02.log
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_10762.trc:
ORA-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []
Mon Nov 21 16:11:44 2005
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_10762.trc:
ORA-00600: internal error code, arguments: [2662], [0], [658448], [0], [664313], [12582921], [], []
Mon Nov 21 16:11:44 2005
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 10762
ORA-1092 signalled during: alter database open resetlogs...
[oracle@ts01 bdump]$ tail alert_TSMISC02.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/TSMISC02/redo01.log
Mon Nov 21 16:21:41 2005
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_10795.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [91], [69], [], [], [], [], []
Error 607 happened during db open, shutting down database
USER: terminating instance due to error 607
Instance terminated by USER, pid = 10795
ORA-1092 signalled during: alter database open resetlogs...
[oracle@ts01 bdump]$ tail -f alert_TSMISC02.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/TSMISC02/redo01.log
Mon Nov 21 16:21:41 2005
Errors in file /oracle/admin/TSMISC02/udump/tsmisc02_ora_10795.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [91], [69], [], [], [], [], []
Error 607 happened during db open, shutting down database
USER: terminating instance due to error 607
Instance terminated by USER, pid = 10795
ORA-1092 signalled during: alter database open resetlogs...
[oracle@ts01 bdump]$
設定隱含引數:
將undo改變成手工管理的,
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUEL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2
SQL>
[oracle@ts01 oracle]$ tail pfile.tmp
*.undo_management=''''AUTO''''
*.undo_retention=10800
*.undo_tablespace=''''UNDOTBS2''''
*.user_dump_dest=''''/oracle/admin/TSMISC02/udump''''
*._ALLOW_RESETLOGS_CORRUPTION = TRUE
*.undo_management=''''manual''''
[oracle@ts01 oracle]$
[oracle@ts01 oracle]$ exit
exit
SQL> startup pfile=pfile.tmp
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> conn lunar/lunar
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LUNARTEST TABLE
SQL>
總結:
將undo改變成手工管理的,
u無j網-jb},提jV*)
=Q管V8`?IO&E*S供J--|}無EC!cHK供然後設定隱含引數 _ALLOW_RESETLOGS_CORRUPTION = TRUE 和 _corrupted_rollback_segments ,因為redo損壞的時候,undo資料也大都不一致
了。
2,open resetlogs之前,先使用recover database using backup controlfile until cancel;
如果此時又遇到600錯誤,就使用ADJUST_SCN事件來調整當前的SCN,如果SCN相差不多,可以通過多次重起資料庫解決。如果scn相差比較多,
可以使用10015 event:
alter session set events ''''10015 trace name adjust_scn level 1'''';
如果SCN相差比較多,可以設定level 2,。。。level 10等 (level 1是每次開啟時將將scn推進1百萬)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-242353/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 當前日誌損壞的案例
- 當前日誌組全部損壞的恢復
- Oracle online redo log日誌 (當前或非當前日誌) 損壞之後的DB恢復Oracle
- win下oracle9201當前日誌組損壞的修復過程Oracle
- 當前日誌檔案壞了的處理辦法
- 當前聯機日誌檔案損壞
- redo日誌損壞
- 線上日誌損壞
- 備份&恢復之十一:損壞當前聯機日誌
- 丟失活動或當前日誌組的恢復
- 增加日誌檔案,修改當前日誌檔案大小
- Sql server日誌損壞後的資料恢復(轉)SQLServer資料恢復
- 備份&恢復之十:損壞非當前聯機日誌
- Oracle資料庫恢復:歸檔日誌損壞案例一則Oracle資料庫
- Jquery對當前日期的操作(格式化當前日期)jQuery
- 損壞聯機日誌 恢復
- INACTIVE日誌組損壞的修復
- 損壞聯機日誌的恢復方法
- 聯機日誌檔案損壞後的恢復方法[轉帖]
- 某個控制檔案損壞的恢復案例
- 使用flashback快速解決當前日誌檔案丟失
- 重做日誌檔案損壞測試
- 磁碟損壞造成RMAN備份檔案有壞塊的恢復案例
- C#獲取當前日期時間(轉)C#
- ORACLE聯機日誌檔案丟失或損壞的處理方法(轉)Oracle
- Oracle聯機日誌檔案丟失或損壞的處理方法 (轉)Oracle
- 聯機日誌檔案損壞問題
- 聯機日誌損壞恢復實驗
- 線上日誌檔案損壞恢復方法
- [20150705]LOGMINER分析當前日誌注意.txt
- 取當前日期各種資料庫的寫法 (轉)資料庫
- 非歸檔模式下非當前日誌檔案的丟失的恢復模式
- 一次日誌檔案損壞的恢復
- SqlServer獲取當前日期SQLServer
- js獲取當前日期JS
- MySQL 獲得當前日期時間(以及時間的轉換)MySql
- inactive狀態日誌組檔案損壞的恢復
- Oracle資料庫塊的物理損壞與邏輯損壞Oracle資料庫