关于 CURRENT REDO LOG 丢失恢复的实验：

以下分几种情况测试：

情况一： Archive模式 + 没有 commit + 正常关闭

[oracle@RAC1 ~]$ export ORACLE_SID=hongye2
[oracle@RAC1 ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 4 17:39:05 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

IDEL > conn /as sysdba
Connected.

SYS:145@HONGYE2 > archive log list
Database log mode             Archive Mode
Automatic archival          Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    8
Next log sequence to archive 10
Current log sequence          10

SYS:145@HONGYE2 > @get_allfile

FILE_NAME                                                                SIZE_MB
---------------------------------------------------------------------- ----------
/oracle/oradata/hongye2/system01.dbf                                        300
/oracle/oradata/hongye2/undotbs01.dbf                                        200
/oracle/oradata/hongye2/sysaux01.dbf                                        120
/oracle/oradata/hongye2/users01.dbf                                           5
/oracle/oradata/hongye2/temp01.dbf                                           20
/oracle/oradata/hongye2/redo01.log                                           50
/oracle/oradata/hongye2/redo02.log                                           50
/oracle/oradata/hongye2/redo03.log                                           50
/oracle/oradata/hongye2/control01.ctl                                        0
/oracle/oradata/hongye2/control02.ctl                                        0
/oracle/oradata/hongye2/control03.ctl                                        0

11 rows selected.

SYS:145@HONGYE2 > create pfile='/home/oracle/hongye2/pfile.ora' from spfile;

File created.

SYS:145@HONGYE2 > shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS:145@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@RAC1 ~]$ cd hongye2/
[oracle@RAC1 hongye2]$ ls -l
total 4
drwxr-x---  2 oracle oinstall 4096 Dec  4 17:19 scripts
[oracle@RAC1 hongye2]$ cp /oracle/oradata/hongye2/* .
[oracle@RAC1 hongye2]$ ls -l
total 827420
-rw-r-----  1 oracle oinstall 7061504 Dec  4 17:56 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 17:56 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 17:56 control03.ctl
-rw-r--r--  1 oracle oinstall    964 Dec  4 18:01 pfile.ora
-rw-r-----  1 oracle oinstall  52429312 Dec  4 17:56 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 17:56 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 17:56 redo03.log
drwxr-x---  2 oracle oinstall    4096 Dec  4 17:19 scripts
-rw-r-----  1 oracle oinstall 125837312 Dec  4 17:58 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 17:58 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:58 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 17:58 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 17:58 users01.dbf
[oracle@RAC1 hongye2]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 4 18:02:34 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

IDEL > conn hongye/hongye
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
Database opened.
IDEL > @get_hide_param
Enter value for par: resetlog
old 5: x.indx=y.indx and x.ksppinm like '%&par%'
new 5: x.indx=y.indx and x.ksppinm like '%resetlog%'

NAME                VALUE                         ISDEFAULT DESCRIBE
-------------------- ------------------------------ --------- ----------------------------------------------------------------------
_allow_resetlogs_corruption  FALSE                         TRUE    allow resetlogs even if it will cause corruption
_no_recovery_through_resetlogs  FALSE                         TRUE    no recovery through this resetlogs operation

IDEL > conn /as sysdba
Connected.
SYS:159@HONGYE2 > @get_allfile

FILE_NAME                                                                SIZE_MB
---------------------------------------------------------------------- ----------
/oracle/oradata/hongye2/system01.dbf                                        300
/oracle/oradata/hongye2/undotbs01.dbf                                        200
/oracle/oradata/hongye2/sysaux01.dbf                                        120
/oracle/oradata/hongye2/users01.dbf                                           5
/oracle/oradata/hongye2/temp01.dbf                                           20
/oracle/oradata/hongye2/redo01.log                                           50
/oracle/oradata/hongye2/redo02.log                                           50
/oracle/oradata/hongye2/redo03.log                                           50
/oracle/oradata/hongye2/control01.ctl                                        0
/oracle/oradata/hongye2/control02.ctl                                        0
/oracle/oradata/hongye2/control03.ctl                                        0

11 rows selected.

SYS:159@HONGYE2 > create table test1(id number,name varchar2(100));

Table created.

SYS:159@HONGYE2 > insert into test1 values(1,'before switch log!');

1 row created.

SYS:159@HONGYE2 > alter system switch logfile;

System altered.

SYS:159@HONGYE2 > insert into test1 values(2,'after switch log!');

1 row created.

SYS:159@HONGYE2 > alter system checkpoint;

SYS:159@HONGYE2 > select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       10 52428800       1 NO  CURRENT                151962 04-DEC-10
      2       1       8 52428800       1 YES INACTIVE             131755 04-DEC-10
      3       1       9 52428800       1 YES INACTIVE             146947 04-DEC-10

SYS:159@HONGYE2 > col member for a60
SYS:159@HONGYE2 > select * from v$logfile;

GROUP# STATUS  TYPE MEMBER                                                    IS_
---------- ------- ------- ------------------------------------------------------------ ---
      1       ONLINE  /oracle/oradata/hongye2/redo01.log                         NO
      2       ONLINE  /oracle/oradata/hongye2/redo02.log                         NO
      3       ONLINE  /oracle/oradata/hongye2/redo03.log                         NO

SYS:159@HONGYE2 > shutdown abort
ORACLE instance shut down.
SYS:159@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC1 hongye2]$ cd /oracle/oradata/hongye2/
[oracle@RAC1 hongye2]$ ls -l
total 827416
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:14 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:14 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:14 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:13 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:03 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:12 redo03.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 18:13 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 18:13 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 18:13 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 18:13 users01.dbf
[oracle@RAC1 hongye2]$ mv redo01.log redo01.bak
[oracle@RAC1 hongye2]$ ls -l
total 827416
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:14 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:14 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:14 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:13 redo01.bak
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:03 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:12 redo03.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 18:13 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 18:13 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 18:13 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 18:13 users01.dbf
[oracle@RAC1 hongye2]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 4 18:16:38 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/oradata/hongye2/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

IDEL > alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance hongye2 (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/oracle/oradata/hongye2/redo01.log'

IDEL > alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

IDEL > alter database recover database until cancel;
alter database recover database until cancel
*
ERROR at line 1:
ORA-00275: media recovery has already been started

IDEL > recover database;
ORA-00275: media recovery has already been started

IDEL > alter system set allow_resetlogs_corruption=true;
alter system set allow_resetlogs_corruption=true
               *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

IDEL > alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

IDEL > shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

IDEL > aler database open resetlogs;
SP2-0734: unknown command beginning "aler datab..." - rest of line ignored.
IDEL > alter database open resetlogs;

Database altered.

IDEL > select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

IDEL > select * from test1;

no rows selected

IDEL > select * from v$log
  2  ;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       1 52428800       1 NO  CURRENT                151982 04-DEC-10
      2       1       0 52428800       1 YES UNUSED                      0
      3       1       0 52428800       1 YES UNUSED                      0
===========================================================

情况二：archive 模式+ 有commit（checkpoint）+ 正常关闭

IDEL > insert into test1 values(1,'before switch log');

1 row created.

IDEL > commit;

Commit complete.

IDEL > alter system switch logfile;

System altered.

IDEL > insert into test1 values(1,'after switch log');

1 row created.

IDEL > commit;

Commit complete.

IDEL > alter system checkpoint;

System altered.

IDEL > commit;

Commit complete.

IDEL > select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       1 52428800       1 YES INACTIVE             151982 04-DEC-10
      2       1       2 52428800       1 YES INACTIVE             152136 04-DEC-10
      3       1       3 52428800       1 NO  CURRENT                152139 04-DEC-10

IDEL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
IDEL > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC1 hongye2]$ ls -l
total 878676
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:37 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:37 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:37 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:13 redo01.bak
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:35 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:35 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:37 redo03.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 18:37 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 18:37 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 18:37 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 18:37 users01.dbf
[oracle@RAC1 hongye2]$ rm -f *.bak
[oracle@RAC1 hongye2]$ rm -f redo03.log
[oracle@RAC1 hongye2]$ ls -l
total 776156
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:37 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:37 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:37 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:35 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:35 redo02.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 18:37 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 18:37 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 18:37 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 18:37 users01.dbf
[oracle@RAC1 hongye2]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 4 18:38:44 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/hongye2/redo03.log'

IDEL > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

IDEL > recover database until cancel;
Media recovery complete.
IDEL > alter database open resetlogs;

Database altered.

IDEL > select * from v$log
  2  ;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       2 52428800       1 NO  CURRENT                152359 04-DEC-10
      2       1       0 52428800       1 YES UNUSED                      0
      3       1       1 52428800       1 YES INACTIVE             152358 04-DEC-10

IDEL > select * from test1;

      ID NAME
---------- ----------------------------------------------------------------------------------------------------
      1 before switch log
      1 after switch log

IDEL > insert into test1 values(3,'before checkpoint');

1 row created.

IDEL > commit;

Commit complete.

IDEL > alter system checkpoint;

System altered.

IDEL > insert into test1 values(4,'after checkpoint');

1 row created.

IDEL > commit;

Commit complete.

IDEL > select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       2 52428800       1 NO  CURRENT                152359 04-DEC-10
      2       1       0 52428800       1 YES UNUSED                      0
      3       1       1 52428800       1 YES INACTIVE             152358 04-DEC-10

IDEL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
IDEL > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC1 hongye2]$ ls -l
total 827416
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:50 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:50 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:50 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:50 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:41 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 18:41 redo03.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 18:50 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 18:50 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 18:50 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 18:50 users01.dbf
[oracle@RAC1 hongye2]$ rm -f redo0*
[oracle@RAC1 hongye2]$ ls -l
total 673636
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:50 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:50 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 18:50 control03.ctl
-rw-r-----  1 oracle oinstall 125837312 Dec  4 18:50 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 18:50 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 18:50 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 18:50 users01.dbf
[oracle@RAC1 hongye2]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 4 18:52:03 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

IDEL > startup
ORA-01031: insufficient privileges
IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/oradata/hongye2/redo01.log'

IDEL > recover database until cancel;
Media recovery complete.
IDEL > alter database open resetlogs;

Database altered.

IDEL > select * from test1;

      ID NAME
---------- ----------------------------------------------------------------------------------------------------
      1 before switch log
      1 after switch log
      3 before checkpoint
      4 after checkpoint

IDEL > select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       1 52428800       1 YES INACTIVE             152771 04-DEC-10
      2       1       2 52428800       1 NO  CURRENT                152772 04-DEC-10
      3       1       0 52428800       1 YES UNUSED                      0
======================================================

情况三：noarchive模式 + 正常关闭

IDEL > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
IDEL > startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
IDEL > alter database noarchivelog;

Database altered.

IDEL > archive log list
Database log mode             No Archive Mode
Automatic archival          Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    1
Current log sequence          2
IDEL > alter database open
  2  ;

Database altered.

IDEL > select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

IDEL > select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       1 52428800       1 YES INACTIVE             152771 04-DEC-10
      2       1       2 52428800       1 NO  CURRENT                152772 04-DEC-10
      3       1       0 52428800       1 YES UNUSED                      0

IDEL > select * from test1;

      ID NAME
---------- ----------------------------------------------------------------------------------------------------
      1 before switch log
      1 after switch log
      3 before checkpoint
      4 after checkpoint

IDEL > insert into test1 values(5,'noarc: before checkpoint');

1 row created.

IDEL > commit;

Commit complete.

IDEL > alter system checkpoint;

System altered.

IDEL > insert into test1 values(6,'noarc: after checkpoint');

1 row created.

IDEL > commit
  2  ;

Commit complete.

IDEL > select * from v$log
  2  ;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       1 52428800       1 YES INACTIVE             152771 04-DEC-10
      2       1       2 52428800       1 NO  CURRENT                152772 04-DEC-10
      3       1       0 52428800       1 YES UNUSED                      0

IDEL > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
IDEL > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC1 hongye2]$ ls -l
total 827416
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:37 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:37 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:37 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 19:34 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 19:37 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 19:34 redo03.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 19:37 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 19:37 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 19:37 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 19:37 users01.dbf
[oracle@RAC1 hongye2]$ rm -f *.log
[oracle@RAC1 hongye2]$ ls -l
total 673636
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:37 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:37 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:37 control03.ctl
-rw-r-----  1 oracle oinstall 125837312 Dec  4 19:37 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 19:37 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 19:37 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 19:37 users01.dbf
[oracle@RAC1 hongye2]$ cd /oracle/flash_recovery_area/HONGYE2/archivelog/2010_12_04/
[oracle@RAC1 2010_12_04]$ ls -l
total 18728
-rw-r-----  1 oracle oinstall 60416 Dec  4 18:35 o1_mf_1_1_6hn6c10d_.arc
-rw-r-----  1 oracle oinstall    1024 Dec  4 18:41 o1_mf_1_1_6hn6q007_.arc
-rw-r-----  1 oracle oinstall    1024 Dec  4 18:52 o1_mf_1_1_6hn7cy27_.arc
-rw-r-----  1 oracle oinstall    1024 Dec  4 18:35 o1_mf_1_2_6hn6c5mj_.arc
-rw-r-----  1 oracle oinstall 19073536 Dec  4 18:12 o1_mf_1_9_6hn511jy_.arc
[oracle@RAC1 2010_12_04]$ rm *
[oracle@RAC1 2010_12_04]$ ls -l
total 0
[oracle@RAC1 hongye2]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 4 19:39:45 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/oradata/hongye2/redo01.log'

IDEL > recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

IDEL > recover database until cancel;
Media recovery complete.
IDEL > alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

IDEL > alter database open resetlogs;

Database altered.

IDEL > archive log list;
Database log mode             No Archive Mode
Automatic archival          Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    1
Current log sequence          2
IDEL > select * from test1;

      ID NAME
---------- ----------------------------------------------------------------------------------------------------
      1 before switch log
      1 after switch log
      6 noarc: after checkpoint
      3 before checkpoint
      4 after checkpoint
      5 noarc: before checkpoint

6 rows selected.

IDEL > alter system switch logfile;

System altered.

IDEL > alter system switch logfile;

System altered.

IDEL > alter system checkpoint;

System altered.

IDEL > select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1       1       2 52428800       1 NO  INACTIVE             154205 04-DEC-10
      2       1       4 52428800       1 NO  CURRENT                154324 04-DEC-10
      3       1       3 52428800       1 NO  INACTIVE             154314 04-DEC-10
=============================================

情况四：noarchive模式+ abort 关闭

IDEL > insert into test1 values(9,'noarch + abort + before switch log and checkpoint');

1 row created.

IDEL > commit;

Commit complete.

IDEL > alter system switch logfile;

System altered.

IDEL > alter system checkpoint;

System altered.

IDEL > insert into test1 values(10,'noarch + abort + after switch log and checkpoint');

1 row created.

IDEL > commit;

Commit complete.

IDEL > shutdown abort;
ORACLE instance shut down.
IDEL > !
[oracle@RAC1 hongye2]$ ls -l
total 827416
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:52 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:52 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:52 control03.ctl
-rw-r-----  1 oracle oinstall  52429312 Dec  4 19:45 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 19:51 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Dec  4 19:51 redo03.log
-rw-r-----  1 oracle oinstall 125837312 Dec  4 19:51 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 19:51 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 19:51 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 19:51 users01.dbf
[oracle@RAC1 hongye2]$ rm -f *.log
[oracle@RAC1 hongye2]$ ls -l
total 673636
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:52 control01.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:52 control02.ctl
-rw-r-----  1 oracle oinstall 7061504 Dec  4 19:52 control03.ctl
-rw-r-----  1 oracle oinstall 125837312 Dec  4 19:51 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314580992 Dec  4 19:51 system01.dbf
-rw-r-----  1 oracle oinstall  20979712 Dec  4 17:32 temp01.dbf
-rw-r-----  1 oracle oinstall 209723392 Dec  4 19:51 undotbs01.dbf
-rw-r-----  1 oracle oinstall 5251072 Dec  4 19:51 users01.dbf
[oracle@RAC1 hongye2]$ ls -l /oracle/flash_recovery_area/HONGYE2/archivelog/2010_12_04/
total 0
[oracle@RAC1 hongye2]$ exit
exit

IDEL > startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                1218316 bytes
Variable Size             62916852 bytes
Database Buffers       100663296 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oracle/oradata/hongye2/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

IDEL > recover database until cancel;
ORA-00279: change 154718 generated at 12/04/2010 19:51:43 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/HONGYE2/archivelog/2010_12_04/o1_mf_1_3_%u_.arc
ORA-00280: change 154718 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/HONGYE2/archivelog/2010_12_04/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/oracle/flash_recovery_area/HONGYE2/archivelog/2010_12_04/o1_mf_1_3_%u_.arc'
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: '/oracle/oradata/hongye2/system01.dbf'

IDEL > alter database open resetlogs;

Database altered.

IDEL > select * from test1;

      ID NAME
---------- ----------------------------------------------------------------------------------------------------
      1 before switch log
      1 after switch log
      6 noarc: after checkpoint
      7 noarch:before switch log and checkpoint
      3 before checkpoint
      4 after checkpoint
      5 noarc: before checkpoint
      8 noarch:after switch log and checkpoint
      9 noarch + abort + before switch log and checkpoint

9 rows selected.

ITPub博客

关于 Current redo log丢失恢复情况的实验！

最新文章