Recovery using backup controlfile

Hi Everyone,

I was working on one of the database(9.2.0.8) refresh where script was doing the
recovery of the database using backup controlfile. Recovery was struck in the middle
with ORA-00283 error messages

Below are the statements that I have found in the test database creation script

startup nomount

SET AUTORECOVERY ON;
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ALTER DATABASE OPEN RESETLOGS;

Error messages I have observed in the log files

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 48:
'/ora08/oradata/prod/data/ora_prod_idx02.dbf'


ORA-01112: media recovery not started


ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/ora01/oradata/test/data/ora_test_system01.dbf'

That gave me hint that media recovery was not complete. Open Resetlogs option was tried where the files are not in consistent state. I have looked at the alert log.

Alert log errors:

Wed Oct 7 19:21:07 2009
Media Recovery Log /app/oracle/admin/test/arch/test_79855.arc
Wed Oct 7 19:21:19 2009
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Oct 7 19:21:19 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Oct 7 19:21:19 2009
Media Recovery Log /app/oracle/admin/test/arch/test_79856.arc
File #48 added to control file as 'UNNAMED00048'. Originally created as:
'/ora08/oradata/prod/data/ora_prod_idx02.dbf'
Errors with log /app/oracle/admin/test/arch/test_79856.arc
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Wed Oct 7 19:21:33 2009
ARC1: Cannot archive online log based on backup controlfile
Wed Oct 7 19:21:35 2009
Media Recovery failed with error 1244
ORA-283 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Oct 7 19:21:35 2009
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Oct 7 19:21:35 2009
ALTER DATABASE OPEN RESETLOGS
ORA-1196 signalled during: ALTER DATABASE OPEN RESETLOGS...

I have added the file in the test machine using the below command

alter database create datafile
'/app/oracle/product/9.2.0.8_64/dbs/UNNAMED00048' as '/ora08/oradata/test/data/ora_test_idx02.dbf';

After that I have restarted the recovery in auto option.
It got to the completion. The issue had arisen because of new datafile addition to the prod system; but, the backup control file had the old structure during the refresh. Roll forward operation had the new file transactions where the control file didn't aware of the file in the test database.

Happy sharing!

13 comments:

Balasubramanian Jambunathan said...

Hello Suresh,

I introduce myself as Balasubramanian, one of the APPS DBA in this ERP world. I am regular to your blog and many times i have been hit though the situations that you have shared in your blog.

I have read the recent post on Recovery Using backup controlfile and i am confused a bit.

"That gave me hint that media recovery was not complete. Open Resetlogs option was tried where the files are not in consistent state. I have looked at the alert log."
..
..

I have added the file in the test machine using the below command

alter database create datafile
'/app/oracle/product/9.2.0.8_64/dbs/UNNAMED00048' as '/ora08/oradata/test/data/ora_test_idx02.dbf';

After that I have restarted the recovery in auto option.
It got to the completion. The issue had arisen because of new datafile addition to the prod system; but, the backup control file had the old structure during the refresh. Roll forward operation had the new file transactions where the control file didn't aware of the file in the test database.

So my understanding is, you have added the datafile in the test and re-executed the recovery in auto mode and the database is recovered successfully.

My Questions:

1. Will this work if i use point in time recovery and i may not be using the auto recovery mode?
2. If i can recover still using manually, until which time i need to recover? Until the time, the datafile is added or till the latest archive?

Hope my questions are clear.

Thanks,
Bala

Suresh Lakshmanan said...

Hi Bala,

Good to hear from you. Thanks for the comment.

1. Will this work if i use point in time recovery and i may not be using the auto recovery mode?

yes. it will.

2. If i can recover still using manually, until which time i need to recover? Until the time, the datafile is added or till the latest archive?

you can do till the latest archive you have. Let me know if you have any questions on this.

Thanks
Suresh

kumarnaidu said...

its very nice to see your blog

Kumar

Suresh Lakshmanan said...

Thank you Kumar

Balasubramanian Jambunathan said...

Hi Suresh,

Thanks for your quick reply.

I persume this scenario occurs not very frequently, as the database gets opened with reset logs frequently. While we check for v$recover_file, we will be notified about the missing000X datafile. I think it we cannot implement this in these cases or can we have an option in that case too?

Thanks,
Bala

Suresh Lakshmanan said...

Hi Bala,

I am not quiet clear with your question. This scenario occurs if you have taken the backup at 10 oclock in the night and added the datafile at 11 oclock. you are restoring backup to new server to create a new database and rolling forward till morning using archived logs. because the backup control file didn't know about the new datafile it will through the error and duirng recovery of 11oclock archive log!! i hope you got it.

Thanks
Suresh

Balasubramanian Jambunathan said...

Hi Suresh,

Yes i understand now. I would like to simulate this and will try the same. I will keep you posted on the results.

Thanks again fr your prompt reply.

Thanks,
Bala

Suresh Lakshmanan said...

sure Bala. Good luck.

Suresh

venuprasad merugu said...

hi suresh

I have checked log file and i got one error when i was running Autoconfig. After R12 installation on app

StackTrace:
java.lang.Exception: oracle.apps.ad.autoconfig.oam.
InDbCtxFileException: Exception : Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; Oracle error -1578: ORA-01578: ORACLE data block corrupted (file # 11, block # 131181)
ORA-01110: data file 11: '/R12/oracle/db/apps_st/data/tx_data53.dbf' has been detected in FND_GSM_UTIL.upload_context_file.
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(FileSysDBCtxMerge.java:685)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:222)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1680)
at oracle.apps.ad.context.CtxValueMgt.main(CtxValueMgt.java:755)

Thanks & regards
prasad

Suresh Lakshmanan said...

Hi Venuprasad,

sorry for the late reply. I had no access to internet forawhile.

It seems block corruption.
what kind of backup do you take?
RMAN block recovery will be easy if you have rman backup.
Check file # 11, block # 131181 belongs to which segment. if index segment you have choice to rebuilding.

Thanks
suresh

Sundaravel said...

Hi Suresh,

How are you? i'm fine and doing good here.

We are in the process of cloning database, file system is RAW file system. if this is the case can i go with RMAN duplicating database concept.

Could you please told me suggestion on this? Before we do RMAN cloning, do we need to create required volumes in storage?

Thanks
R.Sundaravel

Ravi said...

Hello I have written RMAN script which takes incremental backups on weekdays and full on saturday, to test this script whether it helps during some DB crash to recover to the point where my db got crashed , i need to perform a restore,appreciate if you can let me know what are the things i need to do this.
my DB is in archivelog mode running on Linux and

Suresh Lakshmanan said...

Hi Ravi,

Do the testing with your test environment.

Create a test database TESTDB in test server.
Use your RMAN procedures to take the backup as you are planning to do.

Create scenarios yourself.

x. delete a datafile
x. delete a controlfile

or any other scenarios that you think of.

Recover from the backup that you have taken.

Hope that helps you.
Suresh