Search My Oracle Blog

Custom Search

04 June, 2010

RECOVER DATABASE starts with an update -- 2

Continuing my previous post where I showed that a RECOVER DATABASE USING BACKUP CONTROLFILE starts with an Update

I STARTUP MOUNT the database :

ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 4 22:55:11 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>


From another session, I check the type of controlfile :

SQL> select controlfile_type from v$database;

CONTROL
-------
CURRENT

SQL>

So, the controlfile "type" is "CURRENT" , this really being the current controlfile.

I then issue my RECOVER .. USING BACKUP CONTROLFILE command :

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3632262 generated at 06/04/2010 22:54:20 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_06_04/o1_mf_1_1_%u_.arc
ORA-00280: change 3632262 for thread 1 is in sequence #1


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


I then re-check the type of controlfile :

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP

SQL>

Aah ! The Controlfile itself has been updated and is now a BACKUP file, not the CURRENT file.
This will be the status regardless of whether ReCOVERy succeeds or fails.

CANCEL
Media recovery cancelled.
SQL>

From the other session :

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP

SQL>

Can I open the database, knowing that no file are inconsitent (had files been inconsistent, I would have recived a warning "file %s needs more recovery") :

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


SQL>

The status remains a BACKUP :

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP

SQL>

Since I cannot OPEN without resetting logs when I have a Backup Controlfile (the RESETLOGS will synchronise the controlfile with the "latest" current information from the Datafiles, even as updated by application of Redo (if any)), I must RESETLOGS :

SQL> alter database open resetlogs;

Database altered.

SQL>

The status now is CURRENT:

SQL> select controlfile_type from v$database;

CONTROL
-------
CURRENT

SQL>


.
.
.

1 comment:

Anonymous said...

Thank you!!!!!

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