Duplicate database from server A to server B (Non ASM)
=============================================================
Assumed database names:
Primary Database SID: PROD
Duplicate Database SID: AUX
RMAN Catalog SID: RMAN
Steps
1. Backup the primary database.
2. Determine how much disk space will be required.
3. Ensuring you have enough space on your target server.
4. Making the backup available for the duplicate process.
5. Creating init.ora & admin directories for the duplicate database.
6. Checking connections to primary database and RMAN catalog.
7. Prepare RMAN duplicate script.
8. Execute the RMAN script.
=========================================================
Backup of the primary database
Host A (Target)
Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on host A and take a full backup including archive logs and current controlfile.
If you are planning to duplicate a subset of the database refer to second script illustrates the RMAN command to backing up certain tablespaces.
[A]This command will perform a full database backup including archivelogs and the current controlfile
[oracle@linux] export ORACLE_SID=PROD
[oracle@linux] rman target=/ catalog=rman/rman@RMAN
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
[B]This command will perform a tablespace backup ( SYSTEM, SYSAUX, UNDO & USERS) including archive logs and the current controlfile
[oracle@linux] export ORACLE_SID=PROD
[oracle@linux] rman target=/ catalog=rman/rman@RMAN
RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' tablespace SYSTEM, SYSAUX, UNDO, USERS;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
2. Determine how much disk space will be required
[A] This query will calculte the size of the whole DB instead of subset of the Database...If you are going for Subset then pls check the Script..B
select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
Output will be like this:-
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
13491 75 22.140625 13588.1406
[B] This script will calculate the Size of the subset of the database..only for the sapce required for these tablespaces ['SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS']
select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
Output will be like this
DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
1062 75 22.140625 1159.14063
You can check the diff in the output of the script related to the size of files and Database..
3. Ensuring you have enough space on your target server [HOST B]
Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database
--Sample output showing the space available on your filesystem--
[oracle@linux] df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/vg01/root 9.9G 2.8G 6.6G 30% /
/dev/sda1 145M 16M 122M 12% /boot
none 999M 0 999M 0% /dev/shm
/dev/vg01/tmp 2.0G 383M 1.5G 20% /tmp
/dev/vg01/u01 20G 12G 7.0G 62% /u01
/dev/vg01/u02 4.9G 1010M 3.6G 22% /u02
/dev/vg01/backups 5.9G 1.2G 4.4G 22% /backups
/dev/vg01/oradata 15G 13G 2.0G 87% /oradata
4. Making the backup available for the duplicate process
If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created. In the example below the backup piece resides in ‘/backups/PROD’ these files need to be copied into the same directory on host B.
Also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery
RMAN> list backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 529M DISK 00:00:51 2006/05/16 11:12:54
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203
Piece Name: /backups/PROD/df_t590584323_s23_p1
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf
2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf
3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf
4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
24 48M DISK 00:00:06 2006/05/16 11:13:07
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301
Piece Name: /backups/PROD/al_t590584381_s25_p1
List of Archived Logs in backup set 24
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19
1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20
1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11
1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59
1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05
1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00
1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00
1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00
5. Creating the init.ora & administration directories for the duplicate database
Host B(AUX)
Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database with bare minimum settings.
Note you may require to set parameters which are the same as your production database (refer to you primary init.ora)
# +----------------------------------------+
# | FILE : initAUX.ora |
# | DATABASE NAME : AUX |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.
audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
cluster_database=false (in case the production is a rac environment)
# Set the below to the location of the duplicate clone control file.
control_files =('/oradata/AUX/control01.ctl','/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')
# Set the below for the from and to location for all data files / redo
# logs to be cloned.
db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#Set the below to the same as the production target
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 10.2.0.1.0
------------EOF----------
Following the creation of the initAUX.ora startup nomount the auxiliary instance.
[oracle@linux]export ORACLE_SID=AUX
[oracle@linux] sqlplus '/as sysdba'
SQLPLUS> startup nomount;
6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.
Host B(AUX)
Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.
[oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’
[oracle@linux]% sqlplus rman/rman@RMAN (not mandatory)
7. Prepare RMAN duplicate script.
In a working directory on Host B create an RMAN script file duplicate.rcv. The example below
[A] shows the command for a complete duplicate
run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
[B] skips the tablespaces which are not required in the duplicate and
run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX skip tablespace ABC, XYZ;
}
[C] provide the syntax required for a point in time duplicate.
run {
set until time "to_date('Jan 01 2000 12:00:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
8. Execute the RMAN script.
Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
[oracle@linux] export ORACLE_SID=AUX
[oracle@linux] rman target sys/sys@PROD catalog rman/rman@rman auxiliary /
RMAN> @duplicate.rcv
Vijay Kumar...
No comments:
Post a Comment