Can't copy DB using a hot backup

[ericbrunson]

Using the procedure outlined in the Oracle documentation and this article:

http://www.idevelopment.info/data/Oracle/DBA_tips/Backup_and_Recovery/BandR_2.shtml

I spent most of yesterday frustrated trying to make a copy of a database from a hot backup.

The procedure is as follows:
1) Create a script to recreate the control files using: "SQL> backup control file to trace;"
2) Iterate through each of the tablespaces and for each of them:
a) SQL> alter tablespace <whatever> begin backup;
b) copy the datafiles for that tablespace over.
c) SQL> alter tablespace <whatever> end backup;
**note: I'm doing these iteratively rather than locking all tablespaces at once, but this is the only place I deviate from the HOWTO.
3) "SQL> alter system switch logfile;"
4) Copy the backup to trace to a script, edit it to remove the extraneity (is that a word?), change the sid, change "REUSE" to "SET", change "NORESETLOGS" to "RESETLOGS" change all the paths using (basically s/oldsid/newsid/).
5) Rename all the files appropriately so they match up with the new paths in the control files.
6) "SQL> startup nomount"
7) Run the recreate controlfiles script to recreate the control files. This succeeds. If I've missed renaming any paths it fails, so I'm really sure it's working.
7) Copy over the new archive log that was created at the "switch logfile"
8) "SQL> recover database using backup controlfile;" This "suggests" the name of the logfile that I copied over, which is exactly what should happen, so I accept the suggestion, it properly applies the archive and I cancel from the recover.

All this is exactly as it should be from the HOWTO, there's only on final step to complete the procedure:

9) "SQL> alter database open;"

That's where every single document I've read ends, but I get th following:

ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/custdata/ABUJA/oradata/stvl05/system01.dbf'

I'm at a loss, can anyone see the error in what I'm trying to do?

 

[LKBrwnDBA]

On step 4)

Copy the create controlfile script to another name and in this copy change the "SET" and "RESETLOGS" as suggested.

On step 7)

Run the UNMODIFIED copy of the create controlfile (With REUSE and NORESETLOGS)

Do steps #7b and #8.

Once DB is up:

ALTER SYSTEM CHECKPOINT;
SHUTDOWN;
STARTUP MOUNT;
Execute the second copy of the create controlfile (With the SET and RESETLOGS)
ALTER DATABASE OPEN RESETLOGS;

That should do it!




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[DBAwhosaysNIE]

I think your problem is that you need more than just the archived log created by the last (forced) logswitch. officially Oracle will tell you that you need the archived log created from the oldest online redo group in the database through the one created by the forced log switch but I'm pretty sure you just need from the one (archived) associated with the active log group at the time you started the backup.

does that make sense?

 

[ericbrunson]

Both excellent suggestions, I'll look into them both tomorrow and let you know what I find.