Sunday, July 15, 2012

What will happen if you lost all online log files...

I know you know what will happen if you lost one or all your online redo files.
Always multiplex your Redo logs (controlfile too) and store it several partitions.
So, one of student did not it and requested to me what to do. For him I prepare this tutorial.:)
If your Redo log files are multiplexed and you lost one of the member of redo file do not panic, just copy another member of same group and paste it with lost member place.
Suppose you have 3 groups (redo1, redo2, redo3) and all group has three members (redo1a, redo1b, redo1c, ...) If you lost redo1a.log just take redo1b.log and copy it where should be redo1a.log and do not forget to rename it to redo1a.log.
/u01/oradata/mydb/
redo1a.log redo2a.log redo3a.log
/u02/oradata/mydb/
redo1b.log redo2b.log redo3b.log
/u03/oradata/mydb/      
redo1c.log redo2c.log redo3c.log

If your redo logs were not multiplexed and you lost redo files then follow me:

[oracle@localhost ~]$ hostname
localhost.localdomain
[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 03:33:56 EDT 2009 i686 i686 i386 GNU/Linux


[oracle@localhost ARCH]$ sql

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 16 08:09:45 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


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

SQL>

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


SQL> select name from v$database;

NAME
---------
MYDB


SQL> select group#, member from v$logfile order by 1;

    GROUP#   MEMBER
-------------  -----------------------------------------------------------------
         1           /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log
         2           /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo02.log
         3           /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo03.log





SQL> alter system switch logfile;


System altered.



SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL>

[oracle@localhost mydb]$ rm redo*.log

Check alert log

[oracle@localhost bdump]$ tail alert_mydb.log 

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jul 16 08:49:21 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jul 16 08:49:21 2012
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Jul 16 08:49:21 2012
ORACLE Instance mydb - Archival Error
Mon Jul 16 08:49:21 2012
ORA-16038: log 1 sequence# 4 cannot be archived
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
Mon Jul 16 08:49:21 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-16038: log 1 sequence# 4 cannot be archived
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
Mon Jul 16 08:50:11 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jul 16 08:50:11 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1          4   52428800          1 NO  ACTIVE
       527044 16-JUL-12

         2          1          5   52428800          1 NO  CURRENT
       527110 16-JUL-12

         3          1          3   52428800          1 YES INACTIVE
       526520 16-JUL-12


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
             5


Restart database (of course database will not open and will stay in mount stage:) )

SQL> startup force;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1267068 bytes
Variable Size              92277380 bytes
Database Buffers          188743680 bytes
Redo Buffers                2924544 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


[oracle@localhost ARCH]$ ls -lrt
total 336
-rw-r----- 1 oracle oinstall 338432 Jul 16 08:46 arch__1_3_788775848.arc
[oracle@localhost ARCH]$ 

Before this I already took whole backup of database and archivelogs. Now using backup I try to restore.

[oracle@localhost ARCH]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jul 16 08:56:41 2012

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

connected to target database: MYDB (DBID=2703699908, not open)

RMAN> run
2> {
3> set until sequence 4;
4> restore database;
5> recover database;
6> }

executing command: SET until clause
using target database control file instead of recovery catalog

Starting restore at 16-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/users01.dbf
restoring datafile 00005 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/example01.dbf
channel ORA_DISK_1: reading from backup piece /flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_nnndf_FULL_BACKUP_16072012_8073026n_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_nnndf_FULL_BACKUP_16072012_8073026n_.bkp tag=FULL_BACKUP_16072012
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 16-JUL-12

Starting recover at 16-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH/arch__1_3_788775848.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: reading from backup piece /flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_annnn_TAG20120716T083556_807310q0_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_annnn_TAG20120716T083556_807310q0_.bkp tag=TAG20120716T083556
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH/arch__1_2_788775848.arc thread=1 sequence=2
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH/arch__1_3_788775848.arc thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-JUL-12

RMAN>

Try to open database (but you have to open it with resetlogs option!)


RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/16/2012 08:59:24
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>

I have said! :)

RMAN> alter database open resetlogs;

database opened

RMAN> 


Check files.

[oracle@localhost mydb]$ ls -lrt redo0*
-rw-r----- 1 oracle oinstall 52429312 Jul 16 08:59 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul 16 08:59 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 16 09:00 redo01.log
[oracle@localhost mydb]$ 


That`s all. Even now make redolog multiplex and of course after restore/recover (open resetlog) take whole backup of your database!

No comments:

Post a Comment

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...