[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
