当redo丢失时使用_allow_resetlogs_corruption=true打开数据库并验证事务一制性

1、查看redo信息

点击(此处)折叠或打开

1. SQL> set line 200
   
2. SQL> select * from v$log;
   
3. 
   
4.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
   
5. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   
6. 
   
7.          1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
   
8.          2 1 3 4194304 1 YES ACTIVE 943456 15-APR-14
   
9.          3 1 4 4194304 1 NO CURRENT 944183 15-APR-14

 2、查看控制文件、数据文件的scn

点击(此处)折叠或打开

1. SQL> select checkpoint_change# from v$database;
   
2. 
   
3. CHECKPOINT_CHANGE#
   
4. ------------------
   
5. 
   
6.             944183
   
7. 
   
8. SQL> select name,checkpoint_change# from v$datafile;
   
9. 
   
10. NAME CHECKPOINT_CHANGE#
    
11. -------------------------------------------------- ------------------
    
12. 
    
13. /u01/oracle/oradata/ora10g/system01.dbf 944183
    
14. /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
    
15. /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
    
16. /u01/oracle/oradata/ora10g/users01.dbf 944183
    
17. /u01/oracle/oradata/ora10g/jk.dbf 944183
    
18. 
    
19. SQL> select name,checkpoint_change# from v$datafile_header;
    
20. 
    
21. NAME CHECKPOINT_CHANGE#
    
22. -------------------------------------------------- ------------------
    
23. 
    
24. /u01/oracle/oradata/ora10g/system01.dbf 944183
    
25. /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
    
26. /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
    
27. /u01/oracle/oradata/ora10g/users01.dbf 944183
    
28. /u01/oracle/oradata/ora10g/jk.dbf 944183

打一个新的会话
session 1

点击(此处)折叠或打开

1. SQL> conn scott/tiger;
   
2. Connected.
   
3. SQL> select * from e01;
   
4. 
   
5.         ID NAME
   
6. ---------- ----------
   
7. 
   
8.          2 scott
   
9.          1 jack
   
10.          3 jerry
    
11. 
    
12. SQL> insert into e01 values (4,\'lili\');
    
13. 
    
14. 1 row created.
    
15. 
    
16. SQL> commit
    
17.   2 ;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> select * from e01;
    
22. 
    
23.         ID NAME
    
24. ---------- ----------
    
25. 
    
26.          2 scott
    
27.          1 jack
    
28.          4 lili
    
29.          3 jerry
    
30. 
    
31. SQL> insert into e01 values (5,\'lucy\');
    
32. 
    
33. 1 row created.
    
34. 
    
35. SQL>

在  sys连接的会话中

点击(此处)折叠或打开

1. SQL> alter system flush buffer_cache;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> select * from v$log;
   
6. 
   
7.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
   
8. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
   
9. 
   
10.          1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
    
11.          2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
    
12.          3 1 4 4194304 1 NO CURRENT 944183 15-APR-14
    
13. 
    
14. SQL> alter system switch logfile;
    
15. 
    
16. System altered.
    
17. 
    
18. SQL> select * from v$log;
    
19. 
    
20.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
    
21. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
    
22. 
    
23.          1 1 5 4194304 1 NO CURRENT 944586 15-APR-14
    
24.          2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
    
25.          3 1 4 4194304 1 YES ACTIVE 944183 15-APR-14

再打一个会话
  session 2

点击(此处)折叠或打开

1. SQL> conn scott/tiger;
   
2. Connected.
   
3. SQL> select * from e01;
   
4. 
   
5.         ID NAME
   
6. ---------- ----------
   
7. 
   
8.          2 scott
   
9.          1 jack
   
10.          4 lili
    
11.          3 jerry
    
12. 
    
13. SQL> insert into e01 values (6,\'xiaoming\');
    
14. 
    
15. 1 row created.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> select * from e01;
    
22. 
    
23.         ID NAME
    
24. ---------- ----------
    
25. 
    
26.          2 scott
    
27.          6 xiaoming
    
28.          1 jack
    
29.          4 lili
    
30.          3 jerry

在sys 连接的会话中

点击(此处)折叠或打开

1. SQL> alter system checkpoint;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> select checkpoint_change# from v$database;
   
6. 
   
7. CHECKPOINT_CHANGE#
   
8. ------------------
   
9. 
   
10.             944688
    
11. 
    
12. SQL> select name,checkpoint_change# from v$datafile;
    
13. 
    
14. NAME CHECKPOINT_CHANGE#
    
15. -------------------------------------------------- ------------------
    
16. 
    
17. /u01/oracle/oradata/ora10g/system01.dbf 944688
    
18. /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
    
19. /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
    
20. /u01/oracle/oradata/ora10g/users01.dbf 944688
    
21. /u01/oracle/oradata/ora10g/jk.dbf 944688
    
22. 
    
23. SQL> select name,checkpoint_change# from v$datafile_header;
    
24. 
    
25. NAME CHECKPOINT_CHANGE#
    
26. -------------------------------------------------- ------------------
    
27. 
    
28. /u01/oracle/oradata/ora10g/system01.dbf 944688
    
29. /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
    
30. /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
    
31. /u01/oracle/oradata/ora10g/users01.dbf 944688
    
32. /u01/oracle/oradata/ora10g/jk.dbf 944688
    
33. 
    
34. SQL> select checkpoint_change# from v$database;
    
35. 
    
36. CHECKPOINT_CHANGE#
    
37. ------------------
    
38. 
    
39.             944688

点击(此处)折叠或打开

1. SQL> shutdown abort;
   
2. ORACLE instance shut down.
   
3. [oracle@ora10g ora10g]$ rm -rf redo02.log_bak
   
4. [oracle@ora10g ora10g]$ rm -rf redo03.log_bak
   
5. [oracle@ora10g ora10g]$ mv redo01.log redo01.log_bak
   
6. [oracle@ora10g ora10g]$ mv redo03.log redo03.log_bak
   
7. [oracle@ora10g ora10g]$ mv redo02.log redo02.log_bak

修改 pfile添加隐藏参数

点击(此处)折叠或打开

1. ora10g.__db_cache_size=192937984
   
2. 
   
3. ora10g.__java_pool_size=4194304
   
4. 
   
5. ora10g.__large_pool_size=4194304
   
6. 
   
7. ora10g.__shared_pool_size=75497472
   
8. 
   
9. ora10g.__streams_pool_size=0
   
10. 
    
11. *.audit_file_dest=\\\'/u01/oracle/admin/ora10g/adump\\\'
    
12. 
    
13. *.background_dump_dest=\\\'/u01/oracle/admin/ora10g/bdump\\\'
    
14. 
    
15. *.compatible=\\\'10.2.0.1.0\\\'
    
16. 
    
17. *.control_files=\\\'/u01/oracle/oradata/ora10g/control01.ctl\\\',\\\'/u01/oracle/oradata/ora10g/control02.ctl\\\',\\\'/u01/oracle/oradata/ora10g/control03.ctl\\\'#Restore Controlfile
    
18. 
    
19. *.core_dump_dest=\\\'/u01/oracle/admin/ora10g/cdump\\\'
    
20. 
    
21. *.db_block_size=8192
    
22. 
    
23. *.db_domain=\\\'\\\'
    
24. 
    
25. *.db_file_multiblock_read_count=16
    
26. 
    
27. *.db_name=\\\'ora10g\\\'
    
28. 
    
29. *.dispatchers=\\\'(PROTOCOL=TCP) (SERVICE=ora10gXDB)\\\'
    
30. 
    
31. *.job_queue_processes=10
    
32. 
    
33. *.log_archive_dest_10=\\\'location=/arcora10g_dest10/\\\'
    
34. 
    
35. *.open_cursors=300
    
36. 
    
37. *.pga_aggregate_target=94371840
    
38. 
    
39. *.processes=150
    
40. 
    
41. *.remote_login_passwordfile=\\\'EXCLUSIVE\\\'
    
42. 
    
43. *.sga_target=285212672
    
44. 
    
45. *.undo_management=\\\'AUTO\\\'
    
46. 
    
47. *.undo_tablespace=\\\'UNDOTBS1\\\'
    
48. 
    
49. *.user_dump_dest=\\\'/u01/oracle/admin/ora10g/udump\\\'
    
50. 
    
51. _allow_resetlogs_corruption=true

 使用 pfile开打数据库

点击(此处)折叠或打开

1. sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 15 15:01:21 2014
   
4. 
   
5. Copyright (c) 1982, 2005, Oracle. All rights reserved.
   
6. 
   
7. Connected to an idle instance.
   
8. 
   
9. SQL> startup mount pfile=/u01/initora10g.ora;
   
10. ORACLE instance started.
    
11. 
    
12. Total System Global Area 285212672 bytes
    
13. Fixed Size 1218968 bytes
    
14. Variable Size 83887720 bytes
    
15. Database Buffers 192937984 bytes
    
16. Redo Buffers 7168000 bytes
    
17. Database mounted.
    
18. SQL> recover database until cancel
    
19. 
    
20. ORA-00279: change 944688 generated at 04/15/2014 14:36:17 needed for thread 1
    
21. ORA-00289: suggestion : /arcora10g_dest10/1_5_844952420.dbf
    
22. ORA-00280: change 944688 for thread 1 is in sequence #5
    
23. 
    
24. 
    
25. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
26. ORA-00308: cannot open archived log \'/arcora10g_dest10/1_5_844952420.dbf\'
    
27. ORA-27037: unable to obtain file status
    
28. Linux Error: 2: No such file or directory
    
29. Additional information: 3
    
30. 
    
31. 
    
32. ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    
33. ORA-01194: file 1 needs more recovery to be consistent
    
34. ORA-01110: data file 1: \'/u01/oracle/oradata/ora10g/system01.dbf\'
    
35. 
    
36. 
    
37. SQL> alter database open resetlogs;
    
38. 
    
39. alter database open resetlogs
    
40. *
    
41. ERROR at line 1:
    
42. ORA-03113: end-of-file on communication channel
    
43. 
    
44. alter database open;
    
45. alter database open
    
46. *
    
47. ERROR at line 1:
    
48. ORA-01034: ORACLE not available
    
49. 
    
50. 
    
51. SQL> startup mount pfile=/u01/initora10g.ora;
    
52. ORACLE instance started.
    
53. 
    
54. Total System Global Area 285212672 bytes
    
55. Fixed Size 1218968 bytes
    
56. Variable Size 83887720 bytes
    
57. Database Buffers 192937984 bytes
    
58. Redo Buffers 7168000 bytes
    
59. Database mounted.
    
60. SQL> alter database open;
    
61. 
    
62. Database altered.

在  session 1  中

点击(此处)折叠或打开

1. SQL> conn scott/tiger;
   
2. Connected.
   
3. SQL> select * from e01;
   
4. 
   
5.         ID NAME
   
6. ---------- ----------
   
7. 
   
8.          2 scott
   
9.          6 xiaoming
   
10.          1 jack
    
11.          4 lili
    
12.          3 jerry

总结：当使用隐藏参数_allow_resetlogs_corruption=true  打开数据库时，未提交的事务修改的数据未发生变化（由于互联网上有多篇文章说明dbwr 进程会将未提交的数据从buffer_cache 中写入到数据文件中，所以推测在resetlog打开数据库时做Instance Recovery时，使用undo 文件回滚了未提交的数据），此文未在产生检查点后未做事务并提交，如果有，还是会丢失。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23748560/viewspace-1142590/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23748560/viewspace-1142590/