Re: Point in time recovery

I haven't done this in quite some time, early Oracle 7. I too had added a datafile after backup. Here's a document I used to recover. Note that my objective was to recover a dropped table but went through what you are talking about. Hope this helps...

Joe

How to recover a table from a full database backup.

This document incorporates the steps from the Oracle Bulletin: 105867.682 written by RPATEL.

This recovery was done on the same machine as the database being recovered.

1. 1) set your ORACLE_SID to the correct database.
2. select * from sys.dba_data_files order by tablespace_name;
3. select * from v$logfile;

    This will give you a list of log and datafiles that are part of     the database.

2. If you have a log file from the last good backup, print it.

3. Shut down the database you need to recover.

4. Get a good backup of the database you need to recover.

5. Restore all datafiles, log files to new locations. You must

    make sure that you don't overwrite an existing file. For     example, created a RESTORE directory under several filesystems.

6. Restore all archive log files that you'll need from the time

    of the last backup to the point in time you need to recover     too.

7. Copy the initSID.ora from the database you need to recover to

    the RESTORE directory.

8. Rename the initSID.ora to initTEMP.ora.

9. If you are using a configSID.ora copy that as well to the

    RESTORE directory.

1. Rename the configSID.ora to configTEMP.ora if you have a configSID.ora.
2. Note: The Oracle Bulletin 105867.682 assumed that you hadn't added any new datafiles to the database after the backup you are going to restore from.

    If you didn't, then use the control file from the backup     you are restoring from.

    If you did add a new datafile to the database after the backup you are     going to restore from and the following is true:     The datafile(s) are part of SYSTEM, ROLLBACK or your table data     may be in this/these datafile(s), then you must use the current control     file from the database and not the one from the backup.

    The reason for this is: if you use the control from the backup     tape, that control file doesn't know about the new datafile(s). The     act of rolling forward to a point in time after the datafile(s) was     added updates the data dictionary with information about the added     datafile(s). When you try to open the database, it will complain about     missing datafile(s).

    If you didn't add any new datafiles after the backup you are restoring     from, use the cntrlSID.dbf file from tape. Restore it into the     RESTORE directory. Rename it cntrlTEMP.dbf.   

    If you did add a or any new datafiles after the backup you are     restoring from, use the current databases control file. Copy     the current file into the RESTORE directory. Rename it cntrlTEMP.dbf.

1. Edit the initTEMP.ora and/or the configTEMP.ora and make the following changes:
2. control_files=/RESTORE/cntrlTEMP.dbf
3. comment out the rollback_segments parameter by prefixing the line with a #. It should look like: #rollback_segments=(RBS1,RBS2...)
4. Change your ORACLE_SID to TEMP (Ex. setenv ORACLE_SID TEMP), Type "echo $ORACLE_SID" and make sure it is TEMP.
5. Go into SQLDBA, CONNECT INTERNAL.
6. In SQLDBA, type STARTUP pfile=/RESTORE/initTEMP.ora MOUNT
7. In SQLDBA, type ALTER DATABASE DATAFILE 'full pathname filename' OFFLINE DROP for each file which belongs to the tablespace that DO NOT need to be recovered and are NOT part of the SYSTEM or ROLLBACK tablespace.

    Also offline any file which is part of the tablespace holding the     table to be recovered but does not contain ANY PART of the table to     be recovered. For instance, if tablespace USERS has three files,     user1.dbf, user2.dbf, user3.dbf and you know that the table to be     restored is contained entirely in user2.dbf, you can offline datafiles     user1.dbf and user3.dbf. This way, the rcovery will only be applied     to user2.dbf and will be much faster. If you only know which     tablespace the table is contained in, do not offline any of the files     in that tablespace.

1. If you DID NOT add any new datafiles as described in step 10 above, skip this step and continue with step 17.

    If you added any datafiles as described in step 10 above you must     issue the following command for each of the datafiles added from     within SQLDBA:

    ALTER DATABASE CREATE DATAFILE 'full pathname filename of the datafile     as it exists in the database you are recovering' AS 'full pathname     filename in the RESTORE directory';

    It is EXTREMELY important that the first file name is the pathname     and file name of the actual file in the database you are recovering     and the second file name is the RESTORE filename.

1. In SQLDBA, type ALTER DATABASE RENAME FILE 'filename' TO 'RESTORE/filename' for each file which is needed for recovery. This includes files belonging to the tablespace containing the table to be restored, files from SYSTEM and ROLLBACK tablespaces, and the REDOLOGS.
2. Use Point-in-Time (Incomplete) recovery to recover the database to a point in time before when the table to be restored was dropped. You would use a command like: RECOVER DATABASE UNTIL TIME '1996-08-27:16:45:00' USING BACKUP CONTROLFILE
3. If you have restored your ARCHIVE LOGS to their ARCHIVE DESTINATION directory at the recover prompt you can choose AUTO to automatically apply the archive logs. If your ARCHIVE DESTINATION can not hold all of the ARCHIVE LOGS needed for recovery, you can remove the logs no longer needed for recovery after they are applied. This will allow you to copy the ARCHIVE LOGS still needed into your ARCHIVE DESTINATION directory.
4. Once recovery is complete, issue the command ALTER DATABASE OPEN RESETLOGS from within SQLDBA. Export the table you want. Note that the resetlogs will create the redologs as renamed in step 17. Sufficient space on the disk must be ensured for this.
5. Once the necessary tables have been exported, the TEMP instance can be SHUTDOWN and all associated files removed.
6. Import the table(s) into the appropriate databases.

On Tue, 30 May 2000 ddorr.cs_at_clearstream.com wrote:

> I am trying to recover a database up to a certain point in time.
> Restored my disks from a backup (disk to disk copy with raw devices on
> disks), and the archived redo logs from tape,
> started a RECOVER database until time <timespec> using backup controlfile;
> and finally got :
>
> ORA-00283: Recovery session canceled due to errors
> ORA-01244: unnamed datafile(s) added to controlfile by media recovery
> ORA-01110: data file 66: '/dev/rdsk/RTS_BS2/CRE_DATA01_03'
> ORA-01110: data file 65: '/dev/rdsk/RTS_BS2/CRE_DATA01_02'
> ORA-01110: data file 64: '/dev/rdsk/RTS_BS2/CRE_DATA01_01'
>
> What happend is that we added datafiles after the backup, but I thought
> Oracle - during the roll forward process - could handle the add datafile
> process and update the controlfile, like in normal operation.
>
> Any thoughts ?
> --
> Author:
> INET: ddorr.cs_at_clearstream.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Tue May 30 2000 - 14:53:19 CDT