current redo log group 삭제시 복구

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
  1     1        2   52428800      1 YES    INACTIVE          469524 24-NOV-10
  2     1        3   52428800      1 YES    INACTIVE          469526 24-NOV-10
  3     1        4   52428800      1 NO     CURRENT          469528 24-NOV-10

SQL> select * from v$logfile;

    GROUP# STATUS   TYPE   MEMBER             IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
  3    ONLINE  /u01/app/oracle/oradata/orcl/redo02.log        NO
  2    ONLINE  /u01/app/oracle/oradata/orcl/redo03.log        NO
  1    ONLINE  /u01/app/oracle/oradata/orcl/redo01.log        NO

SQL> !rm /u01/app/oracle/oradata/orcl/redo02.log

SQL> !ls /u01/app/oracle/oradata/orcl/redo02.log
ls: /u01/app/oracle/oradata/orcl/redo02.log: No such file or directory

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     92276304 bytes
Database Buffers   188743680 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: '/u01/app/oracle/oradata/orcl/redo02.log'

 

SQL> alter database backup controlfile to trace as '/u01/app/oracle/con_bak.sql';
Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@test app]$ cd /u01/app/oracle/

[oracle@test oracle]$ ls
admin  bak_con.sql  con_bak.sql  flash_recovery_area  oraInventory  oradata  product  test_ts02.dbf  test_ts03.dbf

[oracle@test oracle]$ vi con_bak.sql
"con_bak.sql" 147L, 5792C-- The following are current System-scope REDO Log Archival related

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16   
    MAXLOGMEMBERS 3   
    MAXDATAFILES 100   
    MAXINSTANCES 8   
    MAXLOGHISTORY 292
    LOGFILE 
      GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M, 
      GROUP 2 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M,
      GROUP 3 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M
   DATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf',
           '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
           '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
           '/u01/app/oracle/oradata/orcl/users01.dbf',
           '/u01/app/test_ts01.dbf'
CHARACTER SET KO16KSC5601;                                                                                                                                           

[oracle@test oracle]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 24 23:40:31 2010

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     92276304 bytes
Database Buffers   188743680 bytes
Redo Buffers      2973696 bytes

SQL> @con_bak
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27038: created file already exists
Additional information: 1

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@test oracle]$ cd /u01/app/oracle/oradata/orcl

[oracle@test orcl]$ ls -lrt con*
-rw-r-----  1 oracle dba 7389184 Nov 24 23:38 control03.ctl
-rw-r-----  1 oracle dba 7389184 Nov 24 23:38 control02.ctl
-rw-r-----  1 oracle dba 7389184 Nov 24 23:38 control01.ctl

[oracle@test orcl]$ rm con*

[oracle@test orcl]$ cd /u01/app/oracle

[oracle@test oracle]$ ls
admin  bak_con.sql  con_bak.sql  flash_recovery_area  oraInventory  oradata  product  test_ts02.dbf  test_ts03.dbf

[oracle@test oracle]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 24 23:41:13 2010

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @con_bak

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     92276304 bytes
Database Buffers   188743680 bytes
Redo Buffers      2973696 bytes
Database mounted.
Database opened.


SQL> show lines
linesize 80


SQL> set lines 200

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
  1     1        0   52428800      1 YES    UNUSED        0
  2     1        1   52428800      1 NO     CURRENT          489578 24-NOV-10
  3     1        0   52428800      1 YES    UNUSED        0

SQL> col member for a60

SQL> /

    GROUP# STATUS   TYPE   MEMBER             IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
  3    ONLINE  /u01/app/oracle/oradata/orcl/redo02.log        NO
  2    ONLINE  /u01/app/oracle/oradata/orcl/redo03.log        NO
  1    ONLINE  /u01/app/oracle/oradata/orcl/redo01.log        NO