Recover database

Monday evening I was told a company needed some help recovering an important database.
They were not able to get the database back online. The next morning, after the 'oke' from my manager, I visited the company. It seemed the just moved to a new building, stopped all servers and started them again in the new building. The Oracle Dba who used to work over there was gone, so nowbody really knew what to do or how i should work.
The database that could not be started was a online clone, using snapshots, from a production database. The production database could not be stopped, so they used the online snapshot technology to clone the database. So, what was the problem. Using a online backup for your clone means recovery, so I first checked the status of the database. It seemed the status was ' mounted', so the control creation succeeded ?
Next I tried a to open the database, but this command resulted in the following error..


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/ORA/ONTW/DATA/system01.dbf'

More recovery ??
Because I did not know what happened exactly, I decided to clone again. This way I could see with my own eyes what was going wrong.
During the copy fase of cloning, I also decided to create a new backup of the controlfile. The script used for the new controlfile creation was already a year old. On the production database I created a new backup using the ' alter database backup control file to trace' statement. I was going to use this one instead of the old one. Maybe they had removed or added some datafiles...
After the copy was ready I could start the actual clone.

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jan 9 09:24:30 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1060074720 bytes
Fixed Size 738528 bytes
Variable Size 922746880 bytes
Database Buffers 134217728 bytes
Redo Buffers 2371584 bytes
SQL> @control_ontw.sql
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select status, checkpoint_change#,
2 to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as
3 checkpoint_time,
4 count(*)
5 from v$datafile_header
6 group by status, checkpoint_change#, checkpoint_time
7 order by status, checkpoint_change#, checkpoint_time;

STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
------- ------------------ -------------------- ----------
ONLINE 1.2965E+10 09-JAN-2008 08:45:49 43


In the create controlfile I used the resetlogs, which is needed when renaming the database, and the query showed me the datafiles were in sync.
So, now lets try to recover..

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 12964929028 generated at 01/09/2008 08:45:49 needed for
thread 1
ORA-00289: suggestion : /ORA/ONTW/ARCH/arch60558.dbf
ORA-00280: change 12964929028 for thread 1 is in sequence #60558


Specify log: {=suggested filename AUTO CANCEL}

Strange, the archive logfile suggested is not copied. And on the production server, the file isn't there neither ??? So, the recover process is asking me for a future archive log ?? This seems strange, the data that's missing must be in the online log files.
Searching the net showed me the solution. Just enter the path to your online log files !!
The first logfile didn't fix the problem, but the second one did..

Specify log: {=suggested filename AUTO CANCEL}
/ORA/ONTW/DATA/redo02a.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

The database is open and ready to use.