Search My Oracle Blog

Custom Search

29 May, 2010

RECOVER DATABASE starts with an update

There have been a few questions on my blog about the RECOVER DATABASE command :
Suppose I dont need incomplete recovery but I issued "recover database using backup controlfile"
I notice that I cannot issue normal recover command anymore.
Does this statement modifies something?

UPDATE : I've realised that there were some incorrect references to an "incomplete recovery" in the first version of this blog post. I have marked UPDATE wherever I changed this post.

UPDATE : This is a test that demonstrates that "something is modified".
Note, however, that once I issue RECOVER DATABASE USING BACKUP CONTROLFILE, Oracle knows that it will have to re-synchronise the controlfile -- which is only done by a RESETLOGS.

Here is a very simple test :

I STARTUP MOUNT and check the timestamps of datafiles and controlfiles :

ora10204>date
Sat May 29 19:49:34 SGT 2010
ora10204>ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:40 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:40 sysaux01.dbf
ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 29 19:49:44 2010

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 218104776 bytes
Database Buffers 654311424 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> !date
Sat May 29 19:50:03 SGT 2010

SQL> !ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:40 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:40 sysaux01.dbf

SQL> !ls -ltr cont*
-rw-r----- 1 ora10204 dba 7389184 May 29 19:50 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:50 control01.ctl

SQL>


I find that the MOUNT updates the controlfile but not the datafiles.

I then issue a RECOVER DATABASE USING BACKUP CONTROLFILE :

SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}


From another terminal, I list the datafiles and controlfiles :

[root@linux64 ORT24FS]# date
Sat May 29 19:51:01 SGT 2010
[root@linux64 ORT24FS]# pwd
/oracle_fs/Databases/ORT24FS
[root@linux64 ORT24FS]# ls -ltr
total 3069876
-rw-r----- 1 ora10204 dba 131080192 May 29 19:25 temp01.dbf
-rw-r----- 1 ora10204 dba 52429312 May 29 19:33 redo01.dbf
-rw-r----- 1 ora10204 dba 52429312 May 29 19:34 redo02.dbf
-rw-r----- 1 ora10204 dba 52429312 May 29 19:40 redo03.dbf
-rw-r----- 1 ora10204 dba 1340874752 May 29 19:50 users01.dbf
-rw-r----- 1 ora10204 dba 104865792 May 29 19:50 undotbs.dbf
-rw-r----- 1 ora10204 dba 985669632 May 29 19:50 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:50 sysaux01.dbf
-rw-r----- 1 ora10204 dba 104865792 May 29 19:50 example01.dbf
-rw-r----- 1 ora10204 dba 7389184 May 29 19:51 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:51 control01.ctl
[root@linux64 ORT24FS]#


Aah ! The RECOVER DATABASE command has updated all the datafiles. (the controlfile is always updated by a heartbeat every 3 seconds -- so it's timestamp may continue to be updated even if I don't issue any further commands -- but that is a different matter).

Returning to the RECOVER DATABASE, I CANCEL it and check timestamps :

SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> !date
Sat May 29 19:52:16 SGT 2010

SQL> !ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:50 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:50 sysaux01.dbf

SQL> !ls -ltr cont*
-rw-r----- 1 ora10204 dba 7389184 May 29 19:52 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:52 control01.ctl

SQL>


So, the datafiles are not updated now.

Of course, I cannot OPEN the database :

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL>


UPDATE : "an incomplete recovery" corrected to "use an older controlfile" in the line below.
So, remember : A USING BACKUP CONTROLFILE, once initiated, is an attempt to use an older controlfile and must always be succeeded by an OPEN RESETLOGS.



As a continuation of the exercise, I complete the RECOVERy, applying what was my CURRENT online RedoLog file (redo03.dbf) :

SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 218104776 bytes
Database Buffers 654311424 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
alter database openMedia recovery cancelled.
SQL> resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/Databases/ORT24FS/redo03.dbf
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 218104776 bytes
Database Buffers 654311424 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open resetlogs;

UPDATE : This paragraph rewritten :
What is normally an INCOMPLETE RECOVERY is done as COMPLETE RECOVERY because I have applied the last CURRENT Online Redo Log as well. The RESETLOGS is required because I initiated a "USING BACKUP CONTROLFILE".


.
.
.

2 comments:

csmdba said...

I see a little difference in your result and mine

See the below exercise i performed

RMAN> restore controlfile from autobackup;

Starting restore at 16-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=193 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=6 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=67 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=130 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/oradata/orcl/backup/ctrl_c-2799503914-20130616-02
channel ORA_DISK_6: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_6: skipped, AUTOBACKUP already found
channel ORA_DISK_2: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_DISK_3: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_3: skipped, AUTOBACKUP already found
channel ORA_DISK_4: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_4: skipped, AUTOBACKUP already found
channel ORA_DISK_5: looking for AUTOBACKUP on day: 20130616
channel ORA_DISK_5: skipped, AUTOBACKUP already found
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/oradata/orcl/backup/ctrl_c-2799503914-20130616-02
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/database/DB11203/control01.ctl
output file name=/u01/app/oracle/database/DB11203/control02.ctl
Finished restore at 16-JUN-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6

Then

SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 5442233 generated at 06/16/2013 03:08:48 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/11.2.0.3/dbs/arch/1_1_818219325.dbf
ORA-00280: change 5442233 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Then

RMAN> recover database;

Starting recover at 16-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=193 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=6 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=63 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=129 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=194 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/database/DB11203/redo01.log
archived log file name=/u01/app/oracle/database/DB11203/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUN-13

RECOVER COMPLETED

and

RMAN> alter database open resetlogs;

database opened



In my case, the recover was just successful after issuing "RECOVER DATABASE" - Oracle applied the REDO log automatically!!

In your case it didn't...You have to manually apply it...

Any idea on why this is happening??

Hemant K Chitale said...

csmdba,
You used RMAN, I used SQLPlus.

There are slight differences in recovery between the two. (e.g. the RECOVER command in RMAN does NOT have the "USING BACKUP CONTROLFILE" clause ... I leave it to you to figure out what RMAN does).

A good DBA should be familiar with both tools.

Nevertheless, the point of this post was to show that as soon as you issue a RECOVER DATABASE, the controlfile is updated.

Hemant

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com