Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 18 April 2012

RMAN DUPLICATE DATABASE at a Past Point in Time

As we know that RMAN Duplicate command can be used to create new databases from the backups. But sometimes we require for a copy that contain data for specific time in past in the sense of recovery we are saying it “Time based Recovery”. We can perform the same with RMAN DUPLICATE command by using the “set until time” options just before specifying the auxiliary channels. If no additional options are provided in the RUN block of the RMAN interface, Oracle will build the new database as an exact duplicate of the source database up to the current time.
This duplication example assumes the following:
§         The target database SADHAN and duplicate database SADTEST are on different hosts but have exactly the same directory structure.
§         You want to name the duplicate database files the same as the target files.
§         You are not using a recovery catalog.
§         You are using automatic channels for disk or sbt, which are already configured.
§         You want to recover the duplicate database to specific time or one week ago in order to view the data in prod1 as it appeared at that time (and you have the required backups and logs to recover the duplicate to that point tin time).
Steps:
  1. Take rman full backup and move backup with archive log to the destination host.
  2. Create pfile of production system and copy it into the duplicate host.
  3. Create required folder on the destination host.
  4. Edit and perform required change in pfile (already created)
  5. Create password file for duplicate database.
  6. Created database services and perform tns connectivity for duplicated server.
After successful performing the above mentioned steps, connect rman with target as well as auxiliary database and run the rman duplicate command.
connect  target sys/*****@SADHAN
connect  auxiliary /
run
{
set until time "to_date('Jan 07 2012 08:00:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
duplicate
target database to SADTEST
NOFILENAMECHECK
PFILE=D:\oracle\admin\SADHAN\pfile\initSADHAN.ora
}
-or-
DUPLICATE TARGET DATABASE TO dupdb
    NOFILENAMECHECK UNTIL TIME 'SYSDATE-7';
After successfully duplicating the database you must need to create tempfile for temp tablespace.
Select file_name from dba_data_files, dba_tablespaces where
dba_data_files.tablespace_name = dba_tablespaces.tablespace_name and dba_tablespaces.contents = 'TEMPORARY';
Select * from dba_temp_files;
ALTER TABLESPACE TEMP
ADD TEMPFILE 'D:\ORACLE\ORADATA\sadhan\TEMP02.DBF' SIZE 15G
AUTOEXTEND ON NEXT 5M MAXSIZE 30G;
Now you can Compare and verify both the database parameter:
SELECT INSTANCE_NAME FROM V$INSTANCE;
SELECT DATABASE_NAME FROM V$DATABASE;
SELECT * FROM GLOBAL_NAME;
SELECT * FROM DBA_DATA_FILES;
SELECT * FROM V$LOGFILE;
SELECT * FROM V$LOG;
SELECT LOG_MODE FROM V$DATABASE;
SELECT * FROM HRMS.PAY_PAYMENT_MASTER ORDER BY PAYSLIP_DATE;
SELECT * FROM V$CONTROLFILE;
SELECT * FROM DATABASE_PROPERTIES;

0 comments:

Post a Comment