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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016