Pre-requisites
1.Target database must be mounted or open
$ sqlplus "/ as sysdba"
SQL> startup open
ORACLE instance started.
Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
2.To create the tablespace and the table in the Target
SQL>Create tablespace TSB
Datafile ‘/ora1/ora9i/oradata/ora9i/tsb.dbf’ size 40m.
SQL>Create table tb_name (slno number (3), name varchar2 (20), mob number (10));
3.A valid full database backup of the target database
$rman target /
Rman> backup database;
Rman>backup archivelog all;
4. Drop the table in the Target database
SQL>Drop table tb_name;
====================================================================
NOTE: If you drop the table you have to note the timing.
====================================================================
Steps Required
Create password file
1. Create Password File for Auxiliary Database
$ orapwd file=/ora1/ora9i/9.2.0.4/dbs/orapwAUX password=welcome
Create PFILE FOR AUXILIARY DataBase
2. Create an Initialization Parameter for the Auxiliary Database.
Copy the initialization parameter from the target database and make the necessary changes for the duplicated database.
$ export ORACLE_SID=oracle
$ sqlplus "/ as sysdba"
SQL> create pfile='/ora1/ora9i/9.2.0.4/dbs/initAUX.ora' from spfile;
3.After creating the initialization parameter for the duplicate database, change at least the following parameters:
db_file_name_convert = ('/ora1/ora9i/oradata/oracle', '/ora2/ora9i/oradata/aux')
log_file_name_convert = ('/ora1/ora9i/oradata/oracle', '/ora2/ora9i/oradata/aux')
# Control_file must be different than the production instance
control_files = '/ora2/ora9i/oradata/aux/control01.ctl'
, '/ora2/ora9i/oradata/aux/control02.ctl'
, '/ora2/ora9i/oradata/aux/control03.ctl'
# db_name must be sane as in the production instance
db_name = 'oracle'
instance_name = 'oracle'
audit_file_dest = '/ora2/ora9i/admin/aux/adump'
background_dump_dest = '/ora2/ora9i/admin/aux/bdump'
core_dump_dest = '/ora2/ora9i/admin/aux/cdump'
user_dump_dest = '/ora2/ora9i/admin/aux/udump'
Create / Start the Auxiliary Instance
4. Create all needed directories for the duplicate database:
$ mkdir –p /ora2/ora9i/admin/aux
$ mkdir –p /ora2/ora9i/admin/aux/adump
$ mkdir –p /ora2/ora9i/admin/aux/bdump
$ mkdir –p /ora2/ora9i/admin/aux/cdump
$ mkdir –p /ora2/ora9i/admin/aux/create
$ mkdir -p /ora2/ora9i/admin/aux/pfile
$ mkdir –p /ora2/ora9i/admin/aux/scripts
$ mkdir –p /ora2/ora9i/admin/aux/udump
$ mkdir –p /ora2/ora9i/oradata/aux
5. Ensure Oracle Net Connectivity to Auxiliary Database
Modify both the listener.ora and tnsnames.ora file to be able to connect to the auxiliary database. After making changes to the networking files, test the connection keeping in mind that you must be able to connect to the auxiliary instance with SYSDBA privileges, so a valid password file must exist.
6. Start the Listener and Mount or Open the Target Database
As mentioned in the pre-requisites section of this article, the target database should be either opened or mounted.
$ lsnrctl start
$ export ORACLE_SID=oracle
$ sqlplus "/ as sysdba"
SQL> startup open
7. Ensure You Have the Necessary Backups and Archived Redo Log Files
As mentioned in the pre-requisites section of this article, ensure that you have a current backup that you wish to use to create the duplicate database.
8. We have create a environment file(aux.env) and Run the file
*********************************aux.env contains*******************************
export ORACLE_BASE=/ora1/ora9i
export ORACLE_HOME=$ORACLE_BASE/9.2.0.4.0
export ORACLE_SID=aux
export LD_ASSUME_KERNEL=2.4.19
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
**********************************END*************************************
9.Start the auxiliary instance in nomount mode
$ . aux.env
$sqlplus ‘/as sysdba’
SQL>startup pfile=/ora2/ora9i/9.2.0.4/dbs/initAUX.ora NOMOUNT
SQL>Connect sys/welcome@aux as sysdba;
10. Then to perform the RMAN setps
[mt@of71 ~]$ rman
Recovery Manager: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect auxiliary sys/welcome@aux
connected to auxiliary database: ora9i (not mounted)
RMAN> connect target sys/welcome@ora9i
connected to target database: ORA9I (DBID=2462175651)
RMAN> run
2> {
3> recover tablespace tbs1 until time
4> "TO_DATE('2008-AUG-12:15:35:00','YYYY-MON- DD:HH24:MI:SS')";
5> }
Starting recover at 2008-AUG-12:15:58:38
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=15 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=12 devtype=DISK
printing stored script: Memory Script
{
# set the until clause
set until time "TO_DATE('2008-AUG-12:15:35:00','YYYY-MON- DD:HH24:MI:SS')";
# restore the controlfile
restore clone controlfile to clone_cf;
# replicate the controlfile
replicate clone controlfile from clone_cf;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing script: Memory Script
executing command: SET until clause
Starting restore at 2008-AUG-12:15:58:38
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=12 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=13 devtype=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: sid=14 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
output filename=/d01/mt/9i/oradata/aux/control01.ctl
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/d01/mt/9i/9.2.0.4/dbs/c-2462175651-20080812-02 tag=null params=NUL L
channel ORA_AUX_DISK_1: restore complete
Finished restore at 2008-AUG-12:15:58:40
replicating controlfile
input filename=/d01/mt/9i/oradata/aux/control01.ctl
output filename=/d01/mt/9i/oradata/aux/control02.ctl
output filename=/d01/mt/9i/oradata/aux/control03.ctl
sql statement: alter database mount clone database
sql statement: alter system archive log current
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. exporting table definitions
. . exporting table tb_name
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 9.2.0.4.0 - Production on Tue Aug 12 16:03:17 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character
import server uses AL32UTF8 character set (possible charset conversion)
. importing SAM's objects into SAM
. . importing table "BONUS"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace TBS1 online
sql statement: alter tablespace TBS1 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
sql statement: alter tablespace TBS2 online
sql statement: alter tablespace TBS2 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Finished recover at 2008-AUG-12:16:03:26
No comments:
Post a Comment