How to replicate and recover oracle database`s

This quick procedure will discuss what the commands needed to run on oracle in cases you might be using a script to create a snapshoted database and start the oracle instance on a different server.

In this case you need to put the source database in backup mode, take a snapshot, take it out of backup mode, copy the archive files to the target database, recover and open the database.

1. Put source database in backup mode:

 ALTER SYSTEM ARCHIVE LOG CURRENT;
 SELECT NEXT_CHANGE#-1 FROM V$ARCHIVED_LOG WHERE SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);
 ALTER DATABASE BEGIN BACKUP;

Please notice that in the second line i keep the sequence number for later use in determining which archive files i will need.

2. Check that the entire database is in backup mode:

SELECT STATUS FROM V$BACKUP;

3. Now that the database is in backup mode we can take the snapshot and take the database out of backup mode:

 ALTER DATABASE END BACKUP;
 ALTER SYSTEM ARCHIVE LOG CURRENT;
 ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/backup.ctl';
 ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/backup_trace.ctl';
 SELECT NEXT_CHANGE#-1 FROM V$ARCHIVED_LOG WHERE SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);

Please notice that here i also take backup for the control file to be used in the recovery process, and also i keep now another sequence number, the one of the end of the backup.

4. Now i mount the target database`s mount points and i need to find which archive files i have to copy to the target database:

SELECT NAME FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND NEXT_CHANGE# BETWEEN $S_BEGIN_SEQ AND (SELECT MAX(NEXT_CHANGE#) FROM V$ARCHIVED_LOG);

Also dont forget to copy the backedup control file over the target databases own control files!

5. Now lets do the recover itself:

 STARTUP MOUNT;
 RECOVER AUTOMATIC DATABASE UNTIL CHANGE <last sequence number> USING BACKUP CONTROLFILE;

 ALTER DATABASE OPEN RESETLOGS;

At this point the database has gone a recovery and is ready to open.

It is important to remember that the database was copied in backup mode and therefore is in backup mode in the target database, the recovery process will take out the database from backup mode right after it will open the database and reset the logs.