[20170302]正常关闭数据库日志丢失3.txt-白红宇

[20170302]正常关闭数据库日志丢失3.txt

--//上午写了一篇[20170302]什么是fuzzy.txt ,链接

--//到最后恢复结束时fuzzy=NO,这时scn=13276966782.也就是将只有恢复到结束,oracle才会认为数据文件一致的.

--//我本来想删除日志文件看看是否能open resetlogs的,结果报错.

--//下午重新探究看看:

1.环境:

SYS@book> @ &r/ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//正常关闭数据库,做一个冷备份,然后删除日志文件.

$ rm /mnt/ramdisk/book/r*.log

2..测试:

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> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;

FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME

----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ------------------------------

1 13276932032 2017-03-02 14:57:02 7 925702 ONLINE 842 NO /mnt/ramdisk/book/system01.dbf SYSTEM

2 13276932032 2017-03-02 14:57:02 1834 925702 ONLINE 831 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX

3 13276932032 2017-03-02 14:57:02 923328 925702 ONLINE 752 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1

4 13276932032 2017-03-02 14:57:02 16143 925702 ONLINE 837 NO /mnt/ramdisk/book/users01.dbf USERS

5 13276932032 2017-03-02 14:57:02 952916 925702 ONLINE 748 NO /mnt/ramdisk/book/example01.dbf EXAMPLE

6 13276932032 2017-03-02 14:57:02 13276257767 925702 ONLINE 216 NO /mnt/ramdisk/book/tea01.dbf TEA

6 rows selected.

--//FUZZY=NO.

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 695 52428800 512 1 YES INACTIVE 13276910949 2017-02-28 14:40:12 13276931102 2017-03-02 14:56:08

2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 696 52428800 512 1 NO CURRENT 13276931102 2017-03-02 14:56:08 2.814750E+14

3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 694 52428800 512 1 YES INACTIVE 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12

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.

SYS@book> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

--//嗯,^_^不能通过open resetlogs;,才想起来要采用alter database clear logfile group N的方式,没有归档执行:

--//alter database clear unarchived logfile group N ;这样不用open resetlogs.

3.恢复:

--//我记忆里使用上面的方法我以前是失败的,我采用建立新的控制文件方式(resetlogs)方式建立.

--//那个时候刚刚学oracle,什么都不懂,完全是依葫芦画瓢.再次使用alter database clear logfile group N方式.

--//说明:当前的情况 status=CURRENT,是group#=2.

SYS@book> alter database clear logfile group 1 ;

Database altered.

SYS@book> alter database clear logfile group 2 ;

alter database clear logfile group 2

ERROR at line 1:

ORA-00350: log 2 of instance book (thread 1) needs to be archived

ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'

--//这个必须归档,不能这样执行.

SYS@book> alter database clear unarchived logfile group 2 ;

Database altered.

SYS@book> alter database clear logfile group 3 ;

Database altered.

--//ok,以前为什么不行呢?我仔细看了我以前做的文档,才明白我的错误.

4.重复测试:

--//从冷备份恢复,删除redo.

SYS@book> alter database clear logfile group 2 ;

alter database clear logfile group 2

ERROR at line 1:

ORA-00350: log 2 of instance book (thread 1) needs to be archived

ORA-00312: online log 2 thread 1: '/mnt/ramdisk/book/redo02.log'

SYS@book> alter database clear unarchived logfile group 2 ;

alter database clear unarchived logfile group 2

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

--//注意看提示,当时就是没有看提示,提示无法获得/mnt/ramdisk/book/redo03.log的状态.实际上如果你执行很快,一样报错.

$ cat /tmp/a.txt

alter database clear logfile group 1 ;

alter database clear unarchived logfile group 2 ;

--//注:没有包括clear logfile group 3 ;

SYS@book> @ /tmp/a.txt

Database altered.

alter database clear unarchived logfile group 2

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Database altered.

--//依旧是报无法获得/mnt/ramdisk/book/redo03.log状态.

--//而这个时候你在手工执行:

SYS@book> alter database clear unarchived logfile group 2 ;

alter database clear unarchived logfile group 2

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

--//一样报错.

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 0 52428800 512 1 YES UNUSED 1.3277E+10 2017-02-28 14:40:12 1.3277E+10 2017-03-02 14:56:08

2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 NO CLEARING_C 1.3277E+10 2017-03-02 14:56:08 2.8147E+14

URRENT

3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 0 2017-02-28 14:40:06 0 2017-02-28 14:40:12

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.

SYS@book> alter database clear logfile group 3 ;

Database altered.

SYS@book> alter database clear unarchived logfile group 2 ;

Database altered.

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 697 52428800 512 1 NO CURRENT 13276931103 2017-03-02 15:26:41 2.814750E+14

2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276931102 2017-03-02 14:56:08 13276931103 2017-03-02 15:26:41

3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 0 2017-02-28 14:40:06 0 2017-02-28 14:40:12

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.

--//SEQUENCE#=697使用group#=1.实际上执行alter database clear unarchived logfile group 2 ;要找一个新的redo分配seq,而不知

--//道为什么oracle一定要先/mnt/ramdisk/book/redo03.log,当然这个是开始SEQUENCE#=694最小的.

--//当时的错误就是没有认真看提示.

5.再重复测试:

--//从冷备份恢复,删除redo.

$ cat /tmp/a.txt

alter database clear logfile group 1 ;

alter database clear unarchived logfile group 2 ;

alter database clear logfile group 3 ;

SYS@book> @ /tmp/a.txt

Database altered.

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 697 52428800 512 1 NO CURRENT 13276931103 2017-03-02 15:33:48 2.814750E+14

2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276931102 2017-03-02 14:56:08 13276931103 2017-03-02 15:33:48

3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 0 52428800 512 1 YES UNUSED 13276910486 2017-02-28 14:40:06 13276910949 2017-02-28 14:40:12

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.

--//OK!!

--//我反复测试多次,只要执行时没有报如下错误.什么执行都是ok的.一旦报了这个错误必须先清除group 3,再执行alter database

--//clear unarchived logfile group 2.才会ok,当时太不注意看提示了.^_^.

alter database clear unarchived logfile group 2

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/mnt/ramdisk/book/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Database altered.

5.再再重复测试:

--//从冷备份恢复,删除redo.

$ cat /tmp/a.txt

alter database clear logfile group 3 ;

alter database clear unarchived logfile group 2 ;

alter database clear logfile group 1 ;

SYS@book> @ /tmp/a.txt

Database altered.

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 0 52428800 512 1 YES UNUSED 13276910949 2017-02-28 14:40:12 13276931102 2017-03-02 14:56:08

2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 0 52428800 512 1 YES UNUSED 13276931102 2017-03-02 14:56:08 13276931103 2017-03-02 15:44:59

3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 697 52428800 512 1 NO CURRENT 13276931103 2017-03-02 15:44:59 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.

--//这时的SEQUENCE#=697,是GROUP#=3.

--//也就是清除顺序最好按照SEQUENCE#的顺序,从小到大(694,695,696),最后清除没有归档的在线日志.当然你也可以重新组织顺序.

--//再次说明提示要认真看.教训啊.

SYS@book> alter database open ;

Database altered.

--//ok,正常打开.