Loss of Current Redo Log Files (Sin tener ningún backup)

Pueden existir dos situaciones, una donde se baje la base de datos consistentemente o donde se baje de manera inconsistente. Para ambas situaciones se simulo la perdida de todos los redologs

0) rm /u01/app/oracle/oradata/CPorcl9/*.log

1) Cuando se baja de manera consistente.

SQL> select GROUP#,THREAD#,SEQUENCE#,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# ARC STATUS
1 1 20 YES INACTIVE
2 1 21 NO CURRENT
3 1 19 YES INACTIVE

SQL> !
oracle@greta(06/03/08@14:14:42) # rm /u01/app/oracle/oradata/CPorcl9/*.log
oracle@greta(06/03/08@14:14:42) # exit
exit

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

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/CPorcl9/redo01.log'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 404416 generated at 06/03/2008 17:33:25 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/CPorcl9/archive/CPorcl9_0000000021.arc
ORA-00280: change 404416 for thread 1 is in sequence #21


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

2) Cuando se baja de manera inconsistente.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
1 1 2 104857600 1 YES INACTIVE 403465 03-JUN-08
2 1 3 104857600 1 YES INACTIVE 403467 03-JUN-08
3 1 4 104857600 1 NO CURRENT 403470 03-JUN-08

SQL> !
oracle@greta(06/03/08@14:14:42) # rm /u01/app/oracle/oradata/CPorcl9/*.log
oracle@greta(06/03/08@14:14:42) # exit
exit

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 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/CPorcl9/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 403470 generated at 06/03/2008 17:16:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/CPorcl9/archive/CPorcl9_0000000004.arc
ORA-00280: change 403470 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
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: '/u01/app/oracle/oradata/CPorcl9/system01.dbf'


ORA-01112: media recovery not started


SQL> CREATE PFILE FROM SPFILE;

Edit pfile and add parameter *._allow_resetlogs_corruption=true

SQL> STARTUP MOUNT PFILE='/u01/app/oracle/product/9.2.0/dbs/initCPorcl9.ora'

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> SHUTDOWM IMEMDIATE

SQL> STARTUP

ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.