Wednesday, February 20, 2008

How about if my current redolog is lost?

Some of people might be not aware of losing current redolog and some other might be thinking 'No, We won't be able to do recovery if current redolog is lost?. So the question can we do or can't we do?

Actually yes we can do recover and depends on your database is closed consistently or not. If your database is not closed consistently then you need to restore from backup and perform point in time recovery (you should run your database in archivelog mode) and there might be lost transaction.


Here i did a testing that database is not closed consistently (i presumed i have lost everthing, i mean the box)


First restore from backup, here i used RMAN


RMAN> run {
2> allocate channel ch_1 type disk;
3> restore controlfile to '/backup/restore/dbvdasm/control01.ctl';
4> set newname for datafile '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';
5> set newname for datafile '/backup/restore/dbvd/system01.dbf' to '/backup/restore/dbvdasm/system01.dbf';
6> set newname for datafile '/backup/restore/dbvd/test1.dbf' to '/backup/restore/dbvdasm/test1.dbf';
7> set newname for datafile '/backup/restore/dbvd/undotbs01.dbf' to '/backup/restore/dbvdasm/undotbs01.dbf';
8> set newname for datafile '/backup/restore/dbvd/users01.dbf' to '/backup/restore/dbvdasm/users01.dbf';
9> restore database;
10> set archivelog destination to '/backup/restore/dbvdasm/';
11> restore archivelog all;
12> release channel ch_1;
13> }


allocated channel: ch_1
channel ch_1: sid=142 devtype=DISK

Starting restore at 20-FEB-08

channel ch_1: starting datafile backupset restore
channel ch_1: restoring control file
output filename=/backup/restore/dbvdasm/control01.ctl
channel ch_1: reading from backup piece /backup/restore/df_0mj96lbf_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0mj96lbf_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-FEB-08

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-FEB-08

channel ch_1: starting datafile backupset restore
channel ch_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/restore/dbvdasm/system01.dbf
restoring datafile 00002 to /backup/restore/dbvdasm/undotbs01.dbf
restoring datafile 00003 to /backup/restore/dbvdasm/sysaux01.dbf
restoring datafile 00004 to /backup/restore/dbvdasm/users01.dbf
restoring datafile 00005 to /backup/restore/dbvdasm/test1.dbf
channel ch_1: reading from backup piece /backup/restore/df_0lj96lbe_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0lj96lbe_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:08
Finished restore at 20-FEB-08

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 20-FEB-08

channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=10
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=17
channel ch_1: restoring archive log
archive log thread=1 sequence=18
channel ch_1: restoring archive log
archive log thread=1 sequence=19
channel ch_1: restoring archive log
archive log thread=1 sequence=20
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=11
channel ch_1: restoring archive log
archive log thread=1 sequence=12
channel ch_1: restoring archive log
archive log thread=1 sequence=13
channel ch_1: restoring archive log
archive log thread=1 sequence=14
channel ch_1: restoring archive log
archive log thread=1 sequence=15
channel ch_1: restoring archive log
archive log thread=1 sequence=16
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=21

RMAN>


At this point we have a restored of controlfile, datafiles and archives but remember we still don't have redolog at this moment.


ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1320552
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:05 .
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:12 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:12 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:12 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:12 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:12 sysaux01.dbf
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:12 control01.ctl



Now go to sqlplus and change the location of datafiles and redologs to new location

ora10g@bahamut:/home/ora10g/10.2/dbs> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 20 15:08:02 2008

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

Connected to an idle instance.

SYS@dbvd> startup mount;
ORACLE instance started.

Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> alter database rename file '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';

Database altered.


----------------------------------some more output here-----------------------------------------------------------


Next we need to recover the database

SYS@dbvd> recover database using backup controlfile until cancel;
ORA-00279: change 214846 generated at 02/20/2008 14:57:50 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00280: change 214846 for thread 1 is in sequence #32

Specify log: {=suggested | filename | AUTO | CANCEL}
/backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00279: change 214850 generated at 02/20/2008 14:57:52 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_33_647005853.dbf
ORA-00280: change 214850 for thread 1 is in sequence #33
ORA-00278: log file '/backup/restore/dbvdasm/1_32_647005853.dbf' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SYS@dbvd> alter database open resetlogs;

Database altered.

SYS@dbvd> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE


Last but not least don't forget to re-create temporary tablespace

ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1525368
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:13 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:13 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:13 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:13 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:13 sysaux01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo01.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:13 .
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo02.log
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:13 control01.ctl


But how about if we did shutdown database and suddendly current redolog is corrupt or lost? For this we do not need to perform restore from backup


SYS@dbvd> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dbvd> startup mount;
ORACLE instance started.

Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> select status, group#, members from v$log;

STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
INACTIVE 3 1
INACTIVE 2 1


ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:38 redo01.log

ora10g@bahamut:/backup/restore/dbvd> mv redo01.log ../
ora10g@bahamut:/backup/restore/dbvd> ls -arlt
total 1680960
drwxr-xr-x 6 ora10g dba 256 Feb 18 11:46 dump
-rw-r----- 1 ora10g dba 20979712 Feb 18 12:00 temp01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 18 12:34 redo03.log
-rw-r--r-- 1 ora10g dba 723 Feb 20 09:29 sqlnet.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo02.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo_03.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:37 arc
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:38 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:38 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:38 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:38 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:38 sysaux01.dbf
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 ..
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 .
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control03.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control02.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control01.ctl

As we can see our redo01.log is gone


SYS@dbvd> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


Oppss we need to perform point in time recovery


SYS@dbvd> recover database until cancel;
Media recovery complete.
SYS@dbvd> alter database open resetlogs;

Database altered.

SYS@dbvd> select status, group#, members from v$log;

STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
UNUSED 2 1
UNUSED 3 1


ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:40 redo01.log



Yes, Thanks GOD our database are back now and we can see the redologs are back too. Moral of the story, always make sure you have a valid and good backup


Have a nice day


Cheers
FZheng

1 comment:

sap erp download said...

I can understand your situation if your really lost current redolog. This is surely going to create some problem. But here you find the solution of this problem. I faced the same problem few times but later I got solution for that.