[20170310]dg環境下線上日誌損壞14.txt

lfree發表於2017-03-10

[20170310]dg環境下線上日誌損壞14.txt

http://blog.itpub.net/267265/viewspace-2134481/
http://blog.itpub.net/267265/viewspace-2134665/
http://blog.itpub.net/267265/viewspace-2134816/
http://blog.itpub.net/267265/viewspace-2134979/

--//連續做了幾個dg環境下線上日誌損壞的修復,這次作為這個系列的最後一篇,當然這篇不涉及dg.
--//而是利用歸檔檔案重新生成日誌檔案,看看是否可行?

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t as  select rownum id , lpad(chr(96+rownum),10,chr(96+rownum)) name from dual connect by level<=20;
Table created.

SCOTT@book> select rowid,t.* from t where rownum=1;
ROWID                      ID NAME
------------------ ---------- --------------------
AAAWD4AAEAAAAIjAAA          1 aaaaaaaaaa

SCOTT@book> @ &r/rowid AAAWD4AAEAAAAIjAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90360          4        547          0  0x1000223           4,547                alter system dump datafile 4 block 547 ;

SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archivelog/book
Oldest online log sequence     697
Next log sequence to archive   699
Current log sequence           699
--//當前seq=699.

--session 1:
update t set name=upper(name) where id=1;
commit;
alter system archive log current;

--session 2:
update t set name=upper(name) where id=2;
commit;

SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archivelog/book
Oldest online log sequence     698
Next log sequence to archive   700
Current log sequence           700
--//當前seq=700.

--session 3:
SYS@book> shutdown abort;
ORACLE instance shut down.

2.啟動到mount:
SYS@book> startup mount
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> @ &r/logfile
GROUP# STATUS TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ------ ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1        ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       698    52428800       512       1 YES INACTIVE     13276932875 2017-03-10 15:34:00  13276933416 2017-03-10 15:34:04
     2        ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       699    52428800       512       1 YES ACTIVE       13276933416 2017-03-10 15:34:04  13276933792 2017-03-10 15:38:14
     3        ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       700    52428800       512       1 NO  CURRENT      13276933792 2017-03-10 15:38:14 2.814750E+14
     4        STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5        STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6        STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7        STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.

--//seq#=699 對應/mnt/ramdisk/book/redo02.log status='ACTIVE'.
--//seq#=700 對應/mnt/ramdisk/book/redo03.log status='CURRENT'.

--//如果直接open,oracle執行的例項恢復,需要讀取/mnt/ramdisk/book/redo02.log,/mnt/ramdisk/book/redo03.log檔案.
--//假如/mnt/ramdisk/book/redo02.log seq#=699損壞,這樣修復實際上需要介入.因為歸檔已經存在

$ ls -l /u01/app/oracle/archivelog/book/1_699_896605872.dbf
-rw-r----- 1 oracle oinstall 421888 2017-03-10 15:38:14 /u01/app/oracle/archivelog/book/1_699_896605872.dbf

--//手工介入可以修復資料庫,但是因為線上日誌已經損壞,需要open resetlogs,本文透過希望透過
--/u01/app/oracle/archivelog/book/1_699_896605872.dbf轉成線上日誌檔案,避免open resetlogs開啟.

3.開啟資料庫:

$ mv /mnt/ramdisk/book/redo02.log /u01/backup/

--//安全起見,其他日誌也做一個備份.
$ cp /mnt/ramdisk/book/redo01.log /u01/backup
$ cp /mnt/ramdisk/book/redo03.log /u01/backup


SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

4.現在做偷樑換柱的把戲.

$ cp /u01/app/oracle/archivelog/book/1_699_896605872.dbf /mnt/ramdisk/book/redo02.log

--//先這樣嘗試看看,當前不是正常redo檔案的大小.

SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00327: log 2 of thread 1, physical size 823 less than needed 102400
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'

--//^_^,大小不一樣.

5.第一步修復os塊.
--// 再次提醒使用dd注意方向.注意不要忘記加conv=notrunc.避免檔案被截斷.
$ dd if=/mnt/ramdisk/book/redo01.log of=/mnt/ramdisk/book/redo02.log count=1 bs=512 conv=notrunc
1+0 records in
1+0 records out
512 bytes (512 B) copied, 2.9835e-05 seconds, 17.2 MB/s

SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00327: log 2 of thread 1, physical size 823 less than needed 102400
ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'

--//依舊不行.
$ ls -l /mnt/ramdisk/book/redo0*
-rw-r----- 1 oracle oinstall 52429312 2017-03-10 15:34:04 /mnt/ramdisk/book/redo01.log
-rw-r----- 1 oracle oinstall   421888 2017-03-10 16:00:41 /mnt/ramdisk/book/redo02.log
-rw-r----- 1 oracle oinstall 52429312 2017-03-10 15:39:14 /mnt/ramdisk/book/redo03.log

--//421888/512=824
--//這樣跳過前面824塊,再次提醒注意dd的引數.小心conv=notrunc ,skip,seek等引數.確認再執行^_^.

$ dd if=/mnt/ramdisk/book/redo01.log skip=824 bs=512 of=/mnt/ramdisk/book/redo02.log seek=824 conv=notrunc
101577+0 records in
101577+0 records out
52007424 bytes (52 MB) copied, 0.222428 seconds, 234 MB/s

SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo02.log' validate;
System altered.
--ok沒有問題.

SYS@book> alter database open ;
Database altered.

--//ok,使用open開啟了資料庫.

--//附上alert:
Fri Mar 10 16:05:13 2017
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 23 processes
Started redo scan
Completed redo scan
read 164 KB redo, 1 data blocks need recovery
Started redo application at
Thread 1: logseq 699, block 605
Recovery of Online Redo Log: Thread 1 Group 2 Seq 699 Reading mem 0
  Mem# 0: /mnt/ramdisk/book/redo02.log
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Recovery of Online Redo Log: Thread 1 Group 3 Seq 700 Reading mem 0
  Mem# 0: /mnt/ramdisk/book/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 700, block 112, scn 13276953855
1 data blocks read, 1 data blocks written, 164 redo k-bytes read
Fri Mar 10 16:05:13 2017
LGWR: STARTING ARCH PROCESSES
Fri Mar 10 16:05:13 2017
ARC0 started with pid=45, OS id=27183
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 701 (thread open)
Thread 1 opened at log sequence 701
  Current log# 1 seq# 701 mem# 0: /mnt/ramdisk/book/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Mar 10 16:05:14 2017
SMON: enabling cache recovery
Archived Log entry 1245 added for thread 1 sequence 700 ID 0x4fb7d86e dest 1:
[26824] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:763654090 end:763654150 diff:60 (0 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 ZHS16GBK
Fri Mar 10 16:05:14 2017
Incremental checkpoint up to RBA [0x2bd.3.0], current log tail at RBA [0x2bd.3f.0]
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
Completed: alter database open
Fri Mar 10 16:05:15 2017
Starting background process CJQ0
Fri Mar 10 16:05:15 2017
CJQ0 started with pid=47, OS id=27197

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2135112/,如需轉載,請註明出處,否則將追究法律責任。

相關文章