1、查看redo信息
点击(此处)折叠或打开
- SQL> set line 200
- SQL> select * from v$log;
-
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
-
- 1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
- 2 1 3 4194304 1 YES ACTIVE 943456 15-APR-14
- 3 1 4 4194304 1 NO CURRENT 944183 15-APR-14
点击(此处)折叠或打开
- SQL> select checkpoint_change# from v$database;
-
- CHECKPOINT_CHANGE#
- ------------------
-
- 944183
-
- SQL> select name,checkpoint_change# from v$datafile;
-
- NAME CHECKPOINT_CHANGE#
- -------------------------------------------------- ------------------
-
- /u01/oracle/oradata/ora10g/system01.dbf 944183
- /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
- /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
- /u01/oracle/oradata/ora10g/users01.dbf 944183
- /u01/oracle/oradata/ora10g/jk.dbf 944183
-
- SQL> select name,checkpoint_change# from v$datafile_header;
-
- NAME CHECKPOINT_CHANGE#
- -------------------------------------------------- ------------------
-
- /u01/oracle/oradata/ora10g/system01.dbf 944183
- /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
- /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
- /u01/oracle/oradata/ora10g/users01.dbf 944183
- /u01/oracle/oradata/ora10g/jk.dbf 944183
session 1
点击(此处)折叠或打开
- SQL> conn scott/tiger;
- Connected.
- SQL> select * from e01;
-
- ID NAME
- ---------- ----------
-
- 2 scott
- 1 jack
- 3 jerry
-
- SQL> insert into e01 values (4,\'lili\');
-
- 1 row created.
-
- SQL> commit
- 2 ;
-
- Commit complete.
-
- SQL> select * from e01;
-
- ID NAME
- ---------- ----------
-
- 2 scott
- 1 jack
- 4 lili
- 3 jerry
-
- SQL> insert into e01 values (5,\'lucy\');
-
- 1 row created.
-
- SQL>
点击(此处)折叠或打开
- SQL> alter system flush buffer_cache;
-
- System altered.
-
- SQL> select * from v$log;
-
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
-
- 1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
- 2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
- 3 1 4 4194304 1 NO CURRENT 944183 15-APR-14
-
- SQL> alter system switch logfile;
-
- System altered.
-
- SQL> select * from v$log;
-
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
-
- 1 1 5 4194304 1 NO CURRENT 944586 15-APR-14
- 2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
- 3 1 4 4194304 1 YES ACTIVE 944183 15-APR-14
session 2
点击(此处)折叠或打开
- SQL> conn scott/tiger;
- Connected.
- SQL> select * from e01;
-
- ID NAME
- ---------- ----------
-
- 2 scott
- 1 jack
- 4 lili
- 3 jerry
-
- SQL> insert into e01 values (6,\'xiaoming\');
-
- 1 row created.
-
- SQL> commit;
-
- Commit complete.
-
- SQL> select * from e01;
-
- ID NAME
- ---------- ----------
-
- 2 scott
- 6 xiaoming
- 1 jack
- 4 lili
- 3 jerry
在sys 连接的会话中
点击(此处)折叠或打开
- SQL> alter system checkpoint;
-
- System altered.
-
- SQL> select checkpoint_change# from v$database;
-
- CHECKPOINT_CHANGE#
- ------------------
-
- 944688
-
- SQL> select name,checkpoint_change# from v$datafile;
-
- NAME CHECKPOINT_CHANGE#
- -------------------------------------------------- ------------------
-
- /u01/oracle/oradata/ora10g/system01.dbf 944688
- /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
- /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
- /u01/oracle/oradata/ora10g/users01.dbf 944688
- /u01/oracle/oradata/ora10g/jk.dbf 944688
-
- SQL> select name,checkpoint_change# from v$datafile_header;
-
- NAME CHECKPOINT_CHANGE#
- -------------------------------------------------- ------------------
-
- /u01/oracle/oradata/ora10g/system01.dbf 944688
- /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
- /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
- /u01/oracle/oradata/ora10g/users01.dbf 944688
- /u01/oracle/oradata/ora10g/jk.dbf 944688
-
- SQL> select checkpoint_change# from v$database;
-
- CHECKPOINT_CHANGE#
- ------------------
-
- 944688
点击(此处)折叠或打开
- SQL> shutdown abort;
- ORACLE instance shut down.
- [oracle@ora10g ora10g]$ rm -rf redo02.log_bak
- [oracle@ora10g ora10g]$ rm -rf redo03.log_bak
- [oracle@ora10g ora10g]$ mv redo01.log redo01.log_bak
- [oracle@ora10g ora10g]$ mv redo03.log redo03.log_bak
- [oracle@ora10g ora10g]$ mv redo02.log redo02.log_bak
点击(此处)折叠或打开
- ora10g.__db_cache_size=192937984
-
- ora10g.__java_pool_size=4194304
-
- ora10g.__large_pool_size=4194304
-
- ora10g.__shared_pool_size=75497472
-
- ora10g.__streams_pool_size=0
-
- *.audit_file_dest=\\\'/u01/oracle/admin/ora10g/adump\\\'
-
- *.background_dump_dest=\\\'/u01/oracle/admin/ora10g/bdump\\\'
-
- *.compatible=\\\'10.2.0.1.0\\\'
-
- *.control_files=\\\'/u01/oracle/oradata/ora10g/control01.ctl\\\',\\\'/u01/oracle/oradata/ora10g/control02.ctl\\\',\\\'/u01/oracle/oradata/ora10g/control03.ctl\\\'#Restore Controlfile
-
- *.core_dump_dest=\\\'/u01/oracle/admin/ora10g/cdump\\\'
-
- *.db_block_size=8192
-
- *.db_domain=\\\'\\\'
-
- *.db_file_multiblock_read_count=16
-
- *.db_name=\\\'ora10g\\\'
-
- *.dispatchers=\\\'(PROTOCOL=TCP) (SERVICE=ora10gXDB)\\\'
-
- *.job_queue_processes=10
-
- *.log_archive_dest_10=\\\'location=/arcora10g_dest10/\\\'
-
- *.open_cursors=300
-
- *.pga_aggregate_target=94371840
-
- *.processes=150
-
- *.remote_login_passwordfile=\\\'EXCLUSIVE\\\'
-
- *.sga_target=285212672
-
- *.undo_management=\\\'AUTO\\\'
-
- *.undo_tablespace=\\\'UNDOTBS1\\\'
-
- *.user_dump_dest=\\\'/u01/oracle/admin/ora10g/udump\\\'
-
- _allow_resetlogs_corruption=true
使用 pfile开打数据库
在 session 1 中
点击(此处)折叠或打开
- sqlplus / as sysdba
-
- SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 15 15:01:21 2014
-
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
-
- Connected to an idle instance.
-
- SQL> startup mount pfile=/u01/initora10g.ora;
- ORACLE instance started.
-
- Total System Global Area 285212672 bytes
- Fixed Size 1218968 bytes
- Variable Size 83887720 bytes
- Database Buffers 192937984 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- SQL> recover database until cancel
-
- ORA-00279: change 944688 generated at 04/15/2014 14:36:17 needed for thread 1
- ORA-00289: suggestion : /arcora10g_dest10/1_5_844952420.dbf
- ORA-00280: change 944688 for thread 1 is in sequence #5
-
-
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
- ORA-00308: cannot open archived log \'/arcora10g_dest10/1_5_844952420.dbf\'
- ORA-27037: unable to obtain file status
- Linux Error: 2: No such file or directory
- Additional information: 3
-
-
- ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
- ORA-01194: file 1 needs more recovery to be consistent
- ORA-01110: data file 1: \'/u01/oracle/oradata/ora10g/system01.dbf\'
-
-
- SQL> alter database open resetlogs;
-
- alter database open resetlogs
- *
- ERROR at line 1:
- ORA-03113: end-of-file on communication channel
-
- alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA-01034: ORACLE not available
-
-
- SQL> startup mount pfile=/u01/initora10g.ora;
- ORACLE instance started.
-
- Total System Global Area 285212672 bytes
- Fixed Size 1218968 bytes
- Variable Size 83887720 bytes
- Database Buffers 192937984 bytes
- Redo Buffers 7168000 bytes
- Database mounted.
- SQL> alter database open;
-
- Database altered.
点击(此处)折叠或打开
- SQL> conn scott/tiger;
- Connected.
- SQL> select * from e01;
-
- ID NAME
- ---------- ----------
-
- 2 scott
- 6 xiaoming
- 1 jack
- 4 lili
- 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/