Recover from Redo Group Corruption - Flashback Database On

RECOVERING FROM REDO FILE LOSS/CORRUPTION WITH SOME DATA LOSS

SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 CURRENT 17-APR-09 10:35:05 9860069
2 UNUSED 0
3 UNUSED 0

SYS@orclnew AS SYSDBA>conn test/test
Connected.
TEST@orclnew >create table APR18_2_TAB as select * from dual;

Table created.

TEST@orclnew >conn / as sysdba
Connected.
SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 CURRENT 17-APR-09 10:35:05 9860069
2 UNUSED 0
3 UNUSED 0

SYS@orclnew AS SYSDBA>alter system switch logfile;

System altered.

SYS@orclnew AS SYSDBA>/

System altered.

SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 ACTIVE 17-APR-09 10:35:05 9860069
2 ACTIVE 18-APR-09 07:55:27 9918321
3 CURRENT 18-APR-09 07:55:29 9918324

SYS@orclnew AS SYSDBA>/

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 ACTIVE 17-APR-09 10:35:05 9860069
2 ACTIVE 18-APR-09 07:55:27 9918321
3 CURRENT 18-APR-09 07:55:29 9918324

SYS@orclnew AS SYSDBA>alter system checkpoint;

System altered.

SYS@orclnew AS SYSDBA>commit;

Commit complete.

SYS@orclnew AS SYSDBA>alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-00316: log of thread , type in header is not log file


SYS@orclnew AS SYSDBA>select * from v$instance;
ERROR:
ORA-03114: not connected to ORACLE


SYS@orclnew AS SYSDBA>conn / as sysdba
Connected to an idle instance.
SYS@orclnew AS SYSDBA>startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 343933232 bytes
Database Buffers 721420288 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04000: logical block size mismatch (OS 512)
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 104814879)


SYS@orclnew AS SYSDBA>alter system clear logfile group 3;
alter system clear logfile group 3
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SYS@orclnew AS SYSDBA>alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance orclnew (thread 1)
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'


SYS@orclnew AS SYSDBA>alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance orclnew (thread 1)
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'


SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 UNUSED 17-APR-09 10:35:05 0
3 CURRENT 18-APR-09 07:55:29 9918324
2 INACTIVE 18-APR-09 07:55:27 9918321

SYS@orclnew AS SYSDBA>shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SYS@orclnew AS SYSDBA>startup mount exclusive;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 343933232 bytes
Database Buffers 721420288 bytes
Redo Buffers 7135232 bytes
Database mounted.
SYS@orclnew AS SYSDBA>flashback database to scn 9918321;

Flashback complete.

SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 UNUSED 17-APR-09 10:35:05 0
3 CURRENT 18-APR-09 07:55:29 9918324
2 INACTIVE 18-APR-09 07:55:27 9918321

SYS@orclnew AS SYSDBA>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SYS@orclnew AS SYSDBA>alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SYS@orclnew AS SYSDBA>alter database clear logfile group 3;

Database altered.

SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 UNUSED 17-APR-09 10:35:05 0
3 CURRENT 18-APR-09 07:55:29 9918324
2 INACTIVE 18-APR-09 07:55:27 9918321

SYS@orclnew AS SYSDBA>alter database open resetlogs;

Database altered.

SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 UNUSED 0
2 CURRENT 18-APR-09 08:48:50 9918323
3 UNUSED 0

SYS@orclnew AS SYSDBA>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orclnew AS SYSDBA>startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 348127536 bytes
Database Buffers 717225984 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SYS@orclnew AS SYSDBA>select group#,status,to_char(first_time,'dd-MON-yy hh24:mi:ss'), first_change# from v$log;

GROUP# STATUS TO_CHAR(FIRST_TIME FIRST_CHANGE#
---------- ---------------- ------------------ -------------
1 UNUSED 0
2 CURRENT 18-APR-09 08:48:50 9918323
3 UNUSED 0

SYS@orclnew AS SYSDBA>conn test/test
Connected.
TEST@orclnew >select table_name from user_tables;

TABLE_NAME
------------------------------
APR18_TAB
APR18_2_TAB
TEST_TABLE