Q

Problems using backup control file

I am running Oracle 7 Server Release 7.1.5.2.3 and the production release of PL/SQL Release 2.1.5.2.0 for my production

environment. I have included my actions and error messages below:

SQLDBA> CONNECT INTERNAL
Connected.
SQLDBA> STARTUP MOUNT

ORACLE instance started. Database mounted.

SQLDBA> RECOVER DATABASE UNTIL BACKUP CONTROLFILE;
ORA-00277: Illegal option to the UNTIL recovery flag BACKUP
SQLDBA> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: Change 127299846 generated at 10/19/04 18:35:34 needed for
thread 1
ORA-00289: Suggestion :
$1$DKD104:[P02_ARCHIVELOGS]P02_t0001S1889.ARC
ORA-00280: Change 127299846 for thread 1 is in sequence #1889 Specify
log: { =suggested | filename | AUTO | FROM logsource | CANCEL}

Applying suggested logfile...

ORA-00308: cannot open archived log
'$1$DKD104:[P02_ARCHIVELOGS]P02_t0001S1889.ARC'
ORA-07528: sfifi: $open error
%RMS-E-FNF, file not found - %SYSTEM-W-NOSUCHFILE, no such file
Specify log: { =suggested | filename | AUTO | FROM logsource | CANCEL}
CANCEL Media recovery cancelled.
SQLDBA> ALTER DATABASE OPEN RESETLOGS;
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'DKD104:[ORACLE7.DB_P02]ORA_SYSTEM.DBS'
SQLDBA> SHUTDOWN
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQLDBA> Exit

When you use a backup control file, the database has no way of knowing what the last archived log file is. So it starts applying the archived log file it knows it needs and then increases the log sequence number one by one. Eventually, there comes a point when the recovery wants to apply a non-existent archived log file. That is your case here. The correct action would be to reply CANCEL at that point. Then open the database with RESETLOGS. So follow these steps:

  1. STARTUP MOUNT
  2. RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
  3. When prompted to apply recover with log sequence number 1889 reply CANCEL.
  4. ALTER DATABASE OPEN RESETLOGS;
  5. SHUTDOWN IMMEDIATELY
  6. Take a good backup of your database

If you did step 3, then when you tried step 4 you would not have received the ORA-01194 error.

Any time you open the database with RESETLOGS, you should shut it down immediately and take a good backup of the database. Technically speaking, the database after a RESETLOGS operation is not the same incarnation as it was before that operation. To save yourself tons of headaches in a very, very difficult restore of a database through a RESETLOGS operation, please take a backup before you start it up again for business.


This was first published in October 2004

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close