Online Redo log / Archive file Lost
SQL> select member from V$logfile;
MEMBER
------------------------------------------
/oracle/product/oradata/sec_cdb/redo03.log
/oracle/product/oradata/sec_cdb/redo02.log
/oracle/product/oradata/sec_cdb/redo01.log
SQL> ! rm -f
/oracle/product/oradata/sec_cdb/redo03.log
/oracle/product/oradata/sec_cdb/redo02.log
/oracle/product/oradata/sec_cdb/redo01.log
SQL> shut abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 658505728 bytes
Fixed Size 2927864 bytes
Variable Size 490734344 bytes
Database Buffers 159383552 bytes
Redo Buffers 5459968 bytes
Database mounted.
ORA-00313: open failed for members of log group
1 of thread 1
ORA-00312: online log 1 thread 1:
'/oracle/product/oradata/sec_cdb/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE#
BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
NEXT_TIME ---------- ----------
---------- ---------- ---------- ---------- --- ---------------- -------------
--------- ------------ ---------
1 1 7
52428800 512 1 NO
CURRENT 3330132
25-AUG-15 2.8147E+14
3 1 6
52428800 512 1 NO
INACTIVE 3330129
25-AUG-15 3330132 25-AUG-15
2 1 5
52428800 512 1 NO
INACTIVE 3329468
25-AUG-15 3330129 25-AUG-15
SQL> select file#, CHECKPOINT_CHANGE# from
V$datafile_header;
FILE# CHECKPOINT_CHANGE#
----------------------------------------
----------------------------------------
1 3330132
3 3330132
4 3330132
5 1751698
6 3330132
7 1751698
8 3330132
9 3330132
10 3330132
11 3330132
SQL> select sequence#, FIRST_CHANGE#,
NEXT_CHANGE# from V$archived_log order by 1;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
--------- -------------- --------------
1 3226094 3328904
2 3328904 3329431
3 3329431 3329435
4 3329435 3329468
SQL> select CONTROLFILE_CHANGE# from V$database;
CONTROLFILE_CHANGE#
----------------------------------------
3330215
When the database open the data files scn in the header has
to match with the scn in the control file. Here we could see that the data file
scn is 3330132 but the control file scn is 3330215 around 100 scn
difference. All the changes are recorded
in the online redo log files which are now missing.
Also we are missing our 5, 6 and 7 sequence of archived/redo
logs as well. We can see that we have archive log until sequence 4 checkpoint
until 3329468-1=3329467.
This is the scn we need to recover. Recover
using until change 3329467.
> startup nomount;
> restore controlfile from autobackup;
> alter database mount;
> run
{
set until scn 3329467;
restore database;
recover database;
alter database open resetlogs;
}
No comments:
Post a Comment