Recovering Through an Added Datafile with a Backup Control File: Scenario
Background:
Restore a production database with 1 week ago tape backup media and recover / roll forward archived log files until up-to-date. However a new datafile just added to a tablespace in yesterday. As a result, media recovery stopped when applying the archive log files which contains add tablespace operation.
Scenario:
1. Restored a database with backup control file from tape.
2. Roll forward recover database with archived log files.
3. Archive log files contain “Add datafile to tablespace information”
The Errors #1
Unnamed datafile added to backup control file during database recovery.
ALTER DATABASE RECOVER FROM '/oradata2/arch/eservice/prod/' CONTINUE DEFAULT
Wed Dec 23 12:25:02 2009
Media Recovery Log /oradata2/arch/eservice/prod/1_80668_629050579.dbf
File #10 added to control file as 'UNNAMED00010'. Originally created as:
'/oradata2/eservice/eservice_housekeep3.dbf'
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may faileservice
Wed Dec 23 12:25:05 2009
Media Recovery failed with error 1244
ORA-283 signalled during: ALTER DATABASE RECOVER FROM '/oradata2/arch/eservice/prod/'
CONTINUE DEFAULT ...
The Error #2
The datafile creation was failed by some reason, maybe insufficient disk space.
ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 10 is unknown - rename to correct file ORA-01110: data file 10: '/opt/oracle/product/dbs/UNNAMED00010' ORA-01157: cannot identify/lock data file 10 - see DBWR trace file ORA-01111: name for data file 10 is unknown - rename to correct file ORA-01110: data file 10: '/opt/oracle/product/dbs/UNNAMED00010'
The Solution:
1. If you just got Error #1, you may just rename the datafile to correct location. And then recover the database again. Ensure no data integrity issue persist.
ALTER DATABASE RENAME FILE '/opt/oracle/product/dbs/UNNAMED00010' TO
'/oradata2/eservice/eservice_housekeep3.dbf';
2. If you got both, you have to recreate the datafile before you resume the recovery.
alter database create datafile '/opt/oracle/product/dbs/UNNAMED00010' as '/oradata2/eservice/eservice_housekeep3.dbf'
3. Recover again
SET AUTOrecovery on; set logsource /oradata2/arch/eservice/prod/; RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
Recovery performed successfully
ORA-00279: change 606139213 generated at 12/24/2009 02:15:30 needed for thread 1 ORA-00289: suggestion : /oradata2/arch/eservice/prod/1_80741_629050579.dbf ORA-00280: change 606139213 for thread 1 is in sequence #80741 ORA-00278: log file '/oradata2/arch/eservice/prod/1_80740_629050579.dbf' no longer needed for this recovery ORA-00308: cannot open archived log '/oradata2/arch/eservice/prod/1_80741_629050579.dbf' ORA-27037: unable to obtain file status HPUX-ia64 Error: 2: No such file or directory Additional information: 3
In case your recovery was failed and tablespace is inconsistent, you will get this error.
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01245: offline file 10 will be lost if RESETLOGS is done ORA-01110: data file 10: '/oradata2/eservice/eservice_housekeep3.dbf'
Reference:
http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/backup.102/b14191/recoscen002.htm
Aplink, 
Recovering Through an Added Datafile with a Backup Control File … | Computer File Recovery 5:23 am on December 24, 2009 Permalink |
[...] more here: Recovering Through an Added Datafile with a Backup Control File … Tags: backup, data recovery, fun, howto, iphone, mac, media, networking, ora, oracle, photography, [...]
Natalie White 9:09 am on May 26, 2010 Permalink |
Data Recovery is a very costly option that is why you should always check your storage media for any signs of wear and tear..;`
Allison Hill 10:51 pm on July 15, 2010 Permalink |
data recovery is a very costly option, always make a regular backups.:.’
Abigail Clark 12:00 am on August 31, 2010 Permalink |
data recovery is very very expensive so do make regular backups of important files,-:
Vai 1:20 am on October 7, 2010 Permalink |
Hello Levin,
Thank you for the detailed solution provided. We had the scenario like this – During annual Disaster Recovery Drill, we restored the latest successful online backup on a server and started applying logs. There had been tablespace extension activity on the next day of the successful backup. Due to this, some of the redo logs had that information. During recovery, we got error that File# added to control file as ‘unnamedNNN’. This was strange as the structure was available for the newly added data files. Though we had error same as shown in scenario 1, only renaming did not help as recovery gave error = ORA-19909: datafile NNN belongs to an orphan incarnation. We created the files as mentioned in other solution and it worked. Thank you for your help.
levin 3:03 am on October 7, 2010 Permalink |
Hi Vai, sounds you had this error during rman restore operation right?
Metallic Sandals : 12:23 am on October 23, 2010 Permalink |
i had my 1TB hard drive crashed and data recovery was horrendously expensive,.”
Mavis Brindamour 5:47 am on October 26, 2010 Permalink |
Hello. See my site
Summer Camps 10:17 am on October 30, 2010 Permalink |
Maybe you should make changes to the post name Recovering Through an Added Datafile with a Backup Control File: Scenario | what the blog? to something more suited for your blog post you write. I liked the post nevertheless.
Aplink 10:30 am on October 30, 2010 Permalink |
what’s your suggestion?
RFID Reader 12:55 am on December 13, 2010 Permalink |
of course data entry services are very expensive that is why always make a backup of your files -,”