RESTORING A PHYSICAL STANDBY TO DIFFERENT LOCATION

Restoring a PHYSICAL STANDBY database to a different location …

First make memory usage of your system
You do this, in the case we need to remove shared memory or semaphore.
# ipcs -b

Assuming that you have already use RMAN to backup your standby database into flashback recovery area (db_recovery_file_dest).
                             
So, if your  db_recovery_file_dest=’/recovery_area’, then your backup will be at the following location by default. On the new server create the

recovery area and restore the backup.

Default:
/recovery_area//backupset
/recovery_area//autobackup

On the empty server, we assume you have already cloned the Oracle binaries and set your global variables such as ORACLE_HOME, ORACLE_SID, etc.

Create external password:
# orapwd file=@ORACLE_HOME/dbs/orapw password=your-sys-pass entries=3

Run the RMAN tool and login as sys. Then set your DBID to restore spfile and controlfiles.  If you don’t know where your controlfiles are located

first restore spfile then create pfile and check the location and then build it if they are not exist.

# rman target /
RMAN> SET DBID xxxxxxxxxxxx;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> show all;    --  to display default rman configurations.

To restore spfile:
RMAN> restore spfile from autobackup
            db_recovery_file_dest=’/recovery_area’ db_unique_name=’xxxx’_stdby;
--or—
RMAN> restore spfile from autobackup
            db_recovery_file_dest=’/recovery_area’ db_name=’xxxx’;
Now, you can goto SQLPLUS and create your parameter file.
--or—
If your spfile and controlfile were backup on $ORACLE_HOME/dbs, then do the following:
RMAN> restore spfile from autobackup;
SQL> create pfile from spfile;


To restore controlfiles:
Shutdown, make sure all the directories including audit file and then startup with no mount.
RMAN> shutdown immediate;
--Make sure all directories exist including “adump.”
RMAN> startup nomount;
RMAN> restore controlfile from autobackup
           db_recovery_file_dest=’/recovery_area’ db_unique_name=’xxxx_stdby’;
--or--
RMAN> restore controlfile from autobackup
          db_recovery_file_dest=’/recovery_area’ db_name=’xxxx’;
--or--
If your spfile and controlfile were backup on $ORACLE_HOME/dbs, then do the following:
RMAN> restore controlfile from autobackup;

Mout the instance:
SQL> shutdown immediate;
SQL> startup mount;

Locate the database files destinations:
Note: if your controlfiles’ destination were not set, rman will copy one to $ORACLE_HOME/dbs/cntrl.dbf.
Now, from parameter file, check the diagnostic_dest, audit_file_dest, db_recovery_file_dest, etc.
Use the trace file by backing controlfile to trace to locate other data files destinations.
SQL> ALTER DATABASE BACKUP controlfile TO TRACE; -- located at udump.

Restore the database:
If the database is not mounted:
SQL> alter database mount;
RMAN> restore database;

If you have missing logs do the following:
Now go to your archivelog location and check your last archivelog sequence number. It should be more than value from the following query.
SQL> SELELCT * FROM v$log;

Then recover until the last sequence number;
RMAN> recover database until sequence your-number;

If you have get flashback recovery on/off error message, try to turn it off.
SQL> ALTER DATABASE flashback OFF;
SQL> shutdown immediate;
SQL> startup mount;
--or--
SQL> recover database;  --if you have all logs
--or--
SQL> recover database until sequence 52;
--or--
SQL> recover database using backup controlfile until sequence 52;
--or--
SQL> recover database using backup controlfile until cancel;

Important:
After the recover make sure that you have all your redo logs plus standby.
SQL> SELECT * FROM gv$logfile;
To drop it we you are on the mount mode:
SQL>ALTER DATABASE DROP LOGFILE group nnn;

Add them if there are not there in directory. You may see them on query but not physically on disk.
Example:
SQL> alter database add standby logfile
  2  '/oradata/your-db_name/stndby_redo/stndby04.log' size 50m reuse;


SQL> ALTER DATABASE OPEN resetlogs;

If you hav all your archivelogs:
Go to primary database and switch logfile and copy all archivelogs to the new server. Then create trace file with noresetlogs option.
# scp arch*.log oracle@yournew-server:$DB_RECOVERY_FILE_AREA//archivelog/.

On SQLPLUS:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/home/oracle/myprim-cntl.sql' NORESETLOGS;

Copy the myprim-cntl.sql file to your new server. And edit it to replace the existing controlfiles.

Shutdown the database.
SQL> shutdown immediate;
SQL> @myprim-cntl.sql

Notice:
If you get hange, make sure you check the memory usage:
# ipcs –b

Use the following command to remove them from shared memory or semaphores if needed.
# ipcrm –m
# ipcrm –s