[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