Fun With Database Restore

I was recently given a copy of an Oracle database that I needed to bring up in our environment. The only other requirement I had was to change the database name. Changing the database name means I must create the control files anew and restore using a backup control file. On examination of the database contents, I discovered the copy I was given had everything except for the online redo logs. Since I would be restoring using a backup controlfile, I could open with RESETLOGS and create the online redo logs even though they were missing, so no worries there.

To bring up the database, I copied the datafiles to our database server.I had been given a CREATE CONTROLFILE script, otherwise I would have to generate it by hand (not an impossible feat). The CREATE CONTROLFILE script starts off as:

CREATE CONTROLFILE REUSE SET DATABASE “NEWDB” RESETLOGS NOARCHIVELOG

Since the database is being renamed, the SET DATABASE clause must be used.

At this point, I did the following:

1. sqlplus /nolog

2. connect / as sysdba

3. startup nomount

4. run the CREATE CONTROLFILE script.

The control files were created. Now to proceed with recovery. When recovering using the backup control file (until cancel),  the following could be seen in the Alert Log:

Sun Feb 27 11:10:46 2011
alter database recover using backup controlfile until cancel
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.

The WARNING message repeated for every file in the database. It is obvious that whomever created the copy of the database did not perform a clean shutdown and they did not perform a ALTER DATABASE BEGIN BACKUP command.

Luckily, I was also given the archived redo logs. So we should still be able to open the database. Without the archived redo logs, this database would either not be able to be opened, or would be corrupt if it were open.

The copy of the database had each file with a timestamp of 3:00 pm. When trying to apply recovery, I had to apply redo logs until the first log that completed after 3:00pm. I manually entered each archived redo log when prompted in SQL*Plus in response to the RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL command. After I applied the last redo log that would be needed, I replied:

1. CANCEL
Another warning sign might mouthsofthesouth.com viagra uk not have any spontaneous erections during the night. For most of the men, thinking about sex initiates early, a lot prior puberty age & endures until their final days on this world. mouthsofthesouth.com levitra online Application viagra online cheap of thick ointments. 6. Kamdeepak capsules contain quality aphrodisiacs which enhance levitra order prescription mouthsofthesouth.com secretion of endorphins in the body and treat sexual diseases by inducing euphoria.
2. ALTER DATABASE OPEN RESETLOGS;

Everything preceding this point has been an exercise in restoring a database and it not necessarily blog-worthy as this sort of restore is old-hat to many Oracle professionals. What follows was new to me.

Since the online redo logs were missing, OPEN RESETLOGS noticed the log files were absent and generated errors in the Alert Log.

Wed Mar 02 14:29:35 2011
alter database open resetlogs
Wed Mar 02 14:29:38 2011
RESETLOGS after incomplete recovery UNTIL CHANGE 3989345711
Resetting resetlogs activation ID 4158449604 (0xf7dce7c4)
Wed Mar 02 14:29:38 2011
Errors in file c:\oracle\product\10.2.0\rdbms\trace\olddb_ora_216.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: ‘E:\ORADATA\olddb\REDO01A.LOG’
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

I had never seen the above errors. But this is not a problem since opening with RESETLOGS would recreate these files. Now comes the real troublesome part. The OPEN with RESETLOGS failed with the following error:

Wed Mar 02 14:30:05 2011
Errors in file c:\oracle\product\10.2.0\rdbms\trace\olddb_ora_216.trc:
ORA-30012: undo tablespace ‘UNDOTBS’ does not exist or of wrong type

Wed Mar 02 14:30:05 2011
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012

It turns out the parameter file specified undo_tablespace=undotbs but this tablespace does not exist. Looking at the database copy, I do have a file called “undotbs01.dbf”. But what is the undo tablespace name? I guessed it was UNDOTBS1. So I changed the parameter file. I was still unable to open the database. So I deleted the database files, copied the files from the external media again, and repeated the CREATE CONTROLFILE and applying recovery again. At this point, I was able to open with RESETLOGS successfully.

We cannot control how we receive things, especially when they come from outside our company. If it were up to me, the database copy would have been a cold backup or when the database was in backup mode. If it were up to me, I would have received a parameter file with the database copy. But sometimes, even when it is not the way we prefer, we can still achieve the end-goal which was to open up a copy of this database in our environment.