My confrontations with oracle

November 4, 2008

Index

Filed under: oracle — John Jacob @ 11:43 pm

Make “n” number of connections to your DB using script in windows/unix

Restore the RMAN backup of a RAC to a Single Instance

Get Metadata of an Object in Oracle

Recovery of UNDO tablespace in a Non Archive Log mode Database

No quota on USERS tablespace? Then no EXPDP

Setting up Oracle VM using iSCSI storage

How to identify the type(32 or 64bit) of software installed ?

Recover from loss of Active redo log file

Recovery from the loss of Inactive redo log file

Recovery of non system datafile [ without backup ]

Recovery of Non System datafile [ while the database is closed ]

Recovery of Non System datafile [ while the database is open ]

Recovery of system01.dbf datafile

Tailor your Oracle account

DBA Handbook ?

OPatch failed with error code 73 ??

ORA-12541: TNS:no listener ??

Hardening Oracle Security ??

Who is referencing my table ??

Table Compression in Oracle

Oracle Recovery Scenarios

Securing oracle agents

Oracle RAC installation on Solaris SPARC 64 bit

Setting up APEX on a Standby Database Server

ORA-12560: TNS:protocol adapter error and oradim

Install Oracle in english on a machine with non english OS

Removing the semaphores of a ‘KILL’ed oracle instance

Relink -ing binaries of Oracle RDBMS and Oracle Agent

Process Monitor in Windows

Installing BUG fix 5749953-ONS SIGBUS ERROR AFTER INSTALL PATCHSET 10.2.0.3 FOR CRS

Installing patchset 10.2.0.3 on RAC RDBMS

Update RAC nodelist using runInstaller -updateNodeList

Revoke exp/imp from non privileged oracle users

Empty/Remove Oracle Listener.log files







Recovery from the loss of Inactive redo log file

Filed under: Oracle Backup and Recovery, oracle — John Jacob @ 11:33 pm
Tags: , , ,

LGWR,logwriter writes the redo data from the log buffer cache to the redo log files when,
1] A transaction is commited
2] Every 3 Seconds
3] When redo log buffer is 1/3 full

The LGWR process writes to the redo log file in a circluar fashion. After log group 1 is written, the LGWR will move to log group 2 and meanwhile ARC(archiver) will archive log group 1. The LGWR will complain if one or all of the members of a log group are missing. The database will be  unable to serve any requests if all members of group are missing.

If all the members of a redo log group goes missing before the ARC process could archive it.
Oracle complains with error ORA-00313,ORA-00312,ORA-27037 once all members of the log file group goes missing.
The database halts unable to switch the log file.

1] Shutdown the database
2] Mount the database
startup mount;
3] Check the status of the missing log file group from v$log view.
select * from v$log;
4]
a] If STATUS = INACTIVE and ARCHIVED = YES
alter database clear logfile group <grp number>;
b] If STATUS = INACTIVE and ARCHIVED = NO
STATUS = INACTIVE and ARCHIVED = NO
alter database clear UNARCHIVED logfile group <grp number>;
Since the unarchived log file is cleared, there will NOT be an archive log of that sequence.
Means, if log of seq#10 is cleared. Then archive log seq#10 will be not be created.
It will be like seq#8,seq#9,seq#11,seq#12. So, rollforward using archive logs will not be possible.
Also, all uncommitted transactions will be lost.
5] Open the database
alter database open;

6] Take a good backup

*The step 4(CLEAR) command will create the missing log files at the OS level.

===ARENA===

Below is the error in alert.log

Tue Oct 21 13:39:49 2008
Errors in file /u01/app/oracle/admin/burp/bdump/burp_arc0_23510.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Oct 21 13:39:49 2008

and i SHUTDOWN the Database.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'

The database cannot open as the online log file group 1 is missing. Now, we will check the status of the missing redo log group 1.


SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1        145    5242880 NO  INACTIVE                206100 21-10-08 13:34:50
         2          1        146    5242880 NO  INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 NO  CURRENT                 206304 21-10-08 13:38:48
SQL>

The redo log group 1 is INACTIVE and NOT ARCHIVED, so we will follow step 4b.

SQL> alter database clear unarchived logfile group 1;

Database altered.

The above CLEAR command have created the file /u01/burp/oradata/redo01.log, which was missing.

SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1          0    5242880 YES UNUSED                  206100 21-10-08 13:34:50
         2          1        146    5242880 YES INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 NO  CURRENT                 206304 21-10-08 13:38:48

SQL> alter database open;

Database altered.

SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1        148    5242880 NO  CURRENT                 206670 21-10-08 13:45:09
         2          1        146    5242880 YES INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 YES INACTIVE                206304 21-10-08 13:38:48

SQL> /

Excerpt from alert.log

===============

Tue Oct 21 13:44:12 2008
alter database clear unarchived logfile group 1
Tue Oct 21 13:44:12 2008
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 10/21/2008 13:36:59 (CHANGE 206188) CANNOT BE USED FOR RECOVERY.
Clearing online log 1 of thread 1 sequence number 145
Tue Oct 21 13:44:12 2008
Errors in file /u01/app/oracle/admin/burp/udump/burp_ora_23573.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Completed: alter database clear unarchived logfile group 1
Tue Oct 21 13:44:32 2008
Archiver process freed from errors. No longer stopped
Tue Oct 21 13:45:09 2008
alter database open
Tue Oct 21 13:45:09 2008
Thread 1 advanced to log sequence 148
Thread 1 opened at log sequence 148
  Current log# 1 seq# 148 mem# 0: /u01/burp/oradata/redo01.log
Successful open of redo thread 1

Since we have cleared the seq# 145, there will not be an archive log with sequence# 145. So complete rollforward using archive logs cannot be performed on backups taken prior to seq#145.

[oracle@spade burp]$ ls -ltr arch/
total 36104
-rw-r-----  1 oracle oinstall 5171712 Oct 21 13:30 1_140_667494933.arch
-rw-r-----  1 oracle oinstall 5237760 Oct 21 13:30 1_139_667494933.arch
-rw-r-----  1 oracle oinstall 5168128 Oct 21 13:30 1_141_667494933.arch
-rw-r-----  1 oracle oinstall 5168640 Oct 21 13:30 1_142_667494933.arch
-rw-r-----  1 oracle oinstall 5173248 Oct 21 13:30 1_143_667494933.arch
-rw-r-----  1 oracle oinstall 5169664 Oct 21 13:34 1_144_667494933.arch
-rw-r-----  1 oracle oinstall 5174784 Oct 21 13:44 1_146_667494933.arch
-rw-r-----  1 oracle oinstall  606208 Oct 21 13:45 1_147_667494933.arch
[oracle@spade burp]$
[oracle@spade burp]$

Blog at WordPress.com.