关于 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 © 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 © 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 © 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 © 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 © 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 © 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.
1081
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
