Skip to main content

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!

Comments

Post a Comment

Popular posts from this blog

Rman encryption and other techniques

Today I will demonstrate Recovery Manager features, especially encryption and some other techniques. First of all let me note about RMAN backup encryption. Staring from Oracle 10g RMAN now creates encrypted backups that cannot be restored by unauthorized people. There are 3 modes of backup encryption: * Transparent encryption * Password encryption * Dual-mode encryption using either transparent or password encryption All RMAN backups are not encrypted but you can encrypt any RMAN backup in the form of a backup set. In this tutorial I will show you how to configure Password encryption. Let`s finish talking and start to demonstrate. check parameters with "SHOW ALL" command, by default encryption is OFF RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTR...
4 comments
Read more

ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Today when I want to change max size of datafile I got strange error. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed. After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB). Or you may use 31GB too. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;
Post a Comment
Read more

Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open. SQL> select name from v$database; NAME --------- TEST SQL> shut abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 6597406720 bytes Fixed Size 2265664 bytes Variable Size 3204451776 bytes Database Buffers 3372220416 bytes Redo Buffers 18468864 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6552 Session ID: 191 Serial number: 3  What`s wrong?  SQL> alter database open resetlogs; ERROR:    ORA-03114: not connected to ORACLE    SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...
9 comments
Read more