The following scripts will let you recover your database to the last SCN in the archived redo logs.

1, Connect to RMAN

rman catalog rman/rman@rman target sys/change@prod

2, Restore Control file

startup nomount;
restore controlfile;
alter database mount;

3. Find out the last SCN

SQL> SELECT archivelog_change#-1 FROM v$database;

ARCHIVELOG_CHANGE#-1
——————–
2028371

1 row selected.

4. Restore and Recover using RMAN

run {
set until scn 2028371;
restore database;
recover database;
alter database open resetlogs;
}

5. Add temporary files.

sql "ALTER TABLESPACE TEMP ADD
TEMPFILE ”P:\Oracle\oradata\crm\temp01.dbf”
SIZE 2000M
AUTOEXTEND ON NEXT 64K”;

6. Register the newly recovered database as a new incarnation.

list incarnation;
reset database to incarnation x;


7. Backup your database now!

The following script will recover your database to a specific time in the past.

rman catalog rman/rman@rman target sys/change@prod

run
{
shutdown immediate;
startup mount;
set until time “to_date(’03/15/2006 15:00:00″), ‘mm/dd/yyyy hh24:mi:ss’)”;
# set until scn 1000; # alternatively, you can specify SCN
# set until sequence 4578; # alternatively, you can specify log sequence number
restore database;
recover database;
alter database open resetlogs;
}

Steps to restore RMAN backup to different host, for example, RMAN backup from Production server to Test server.

Presumptions

• Production database: Server A.
• Standby and RMAN database: Server B.
• Test database: Server C
• tnsnames.ora

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.mycompany.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
(SERVER = DEDICATED)
)
)

RMAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stb.mycompany.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = rman)
(SERVER = DEDICATED)
)
)

PRO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod.mycompany.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = prod)
(SERVER = DEDICATED)
)
)

STB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stb.mycompany.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = stb)
(SERVER = DEDICATED)
)
)

• All servers have identifical directory structure.

1, Copy backup control file and init.ora file from Prod to Test.

2, Start Test server.

sqlplus /nolog
SQL>connect sys/change_on_install@test as sysdba
SQL> startup nomount PFILE=’C:\Oracle\admin\pfile\init.ora’;
ORACLE instance started.

Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes

3. Copy RMAN backup files from Prod to Test, place them in the same directory as Prod.

4. Connect to RMAN

rman TARGET SYS/xxx@PROD CATALOG rman/rman@rman AUXILIARY SYS/change_on_install@test

5, Duplicate database

RUN
{
allocate auxiliary channel c1 DEVICE TYPE disk;
DUPLICATE TARGET DATABASE to test nofilenamecheck ;
}

6, Duplicate database before current time

RUN
{
allocate auxiliary channel c1 DEVICE TYPE disk;
DUPLICATE TARGET DATABASE to test nofilenamecheck UNTIL TIME “TO_DATE(’03/25/2007′,’MM/DD/YYYY’)”;
}