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

关于 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.

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页