same directory structure - active database duplication server to server - (11g).pdf

8/9/2019 SAME DIRECTORY STRUCTURE - ACTIVE DATABASE DUPLICATION SERVER TO SERVER - (11g).pdf

http://slidepdf.com/reader/full/same-directory-structure-active-database-duplication-server-to-server-11gpdf 1/17

ACTIVE DATABASE DUPLICATION - SAME DIRECTORY STRUCTURE

Exploring the Oracle DBA Technology by Gunasekaran , Thiyagu

RMAN ACTIVE DATABASE CLONING

RMAN has the ability duplicate a database from a backup or from an active database. Using RMAN

possible to create a duplicate database on a remote server with the same directory structure and

different directory structure or else the local server with a different directory structure.

Oracle 11g has introduced active database duplication, we can create a duplicate of the target

database without any backups. Duplication is performed over the network. Let us do active database

duplication in auxiliary server with different directory structure.

Source server database name : CRMS

Auxiliary server database name : CRMS

Source database path : /u02/app/oracle/oradata/crms/

Auxiliary database path : /u02/app/oracle/oradata/crms/

SOURCE SERVER 192.168.1.130 (SERVER1.ORACLE.COM) SERVER1 PRODUCTION

AUXILIARY SERVER 192.168.1.131 (SERVER2.ORACLE.COM) SERVER2 DEVELOPMENT

NETWORK AVAILABILITY BETWEEN TWO SERVERS

FROM SOURCE SERVER





CONNECTING SOURCE DATABASE

SYS>select name, dbid from v$database;

NAME DBID

--------- ----------

CRMS 1570419964

SYS>show parameter db_domain;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_domain string server1.com

SYS>show parameter service_names;

NAME TYPE VALUE

------------------------------------ ----------- ----------------------

service_names string crms.server1.com

PARAMETER FILE BACKUP FOR AUXILIARY DATABASE

SYS>show parameter pfile;

NAME TYPE VALUE

------- --------- ------------------------------------------------------

spfile string /u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfilecrms.ora

SENDING PFILE TO THE AUXILIARY SERVER

TARGET DATABASE PFILE

crms.__db_cache_size=419430400

crms.__java_pool_size=16777216

crms.__large_pool_size=16777216

crms.__oracle_base='/u02/app/oracle' # ORACLE_BASE set from environment

crms.__pga_aggregate_target=503316480

crms.__sga_target=754974720

crms.__shared_io_pool_size=0

crms.__shared_pool_size=285212672





crms.__streams_pool_size=0

*.audit_file_dest='/u02/app/oracle/admin/crms/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u02/app/oracle/oradata/crms/control01.ctl','/u02/app/oracle/flash_recovery_area/crms/control02.ctl'

*.db_block_size=8192

*.db_domain='server1.com'

*.db_name='crms'

*.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u02/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=crmsXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1244659712

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

AUXILIARY DATABASE PFILE

crms.__db_cache_size=419430400

crms.__java_pool_size=16777216

crms.__large_pool_size=16777216

crms.__oracle_base='/u02/app/oracle' # ORACLE_BASE set from environment

crms.__pga_aggregate_target=503316480

crms.__sga_target=754974720

crms.__shared_io_pool_size=0

crms.__shared_pool_size=285212672

crms.__streams_pool_size=0

*.audit_file_dest='/u02/app/oracle/admin/crms/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u02/app/oracle/oradata/crms/control01.ctl', '/u02/app/oracle/flash_recovery_area/crms/control02.ctl'

*.db_block_size=8192

*.db_domain='server2.com'

*.db_name='crms'

*.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u02/app/oracle'





*.dispatchers='(PROTOCOL=TCP) (SERVICE=crmsXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1244659712

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

# Below Parameters are used for from where to where the DATAFILES & LOGFILES will be cloned.

db_file_name_convert=('/u02/app/oracle/oradata/crms/', '/u02/app/oracle/oradata/crms/’)

log_file_name_convert=('/u02/app/oracle/oradata/crms/', '/u02/app/oracle/oradata/crms/')

If the source database directory structure & clone database directory structure differs, then only

you need to use db_file_name_convert & log_file_name_convert parameters. I am using here.

CREATE DIRECTORY STRUCTURE FOR AUXILIARY DATABASE

$ mkdir -p /u02/app/oracle/oradata/crms/

$ mkdir -p /u02/app/oracle/admin/crms/adump/

$ mkdir -p /u02/app/oracle/admin/crms/dpdump/

$ mkdir -p /u02/app/oracle/flash_recovery_area/crms #(for control file)

LISTENER.ORA ON TARGET SERVER 192.168. 1.130

# listener.ora Network Configuration File: /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME=crms)

(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)

# (GLOBAL_DBNAME=crms)

)

)

ADR_BASE_LISTENER = /u02/app/oracle

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.241.130)(PORT=1521))

)

)





STATUS of the LISTENER

------------------------------

Alias listener

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 07-JAN-2015 10:48:48

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u02/app/oracle/diag/tnslsnr/SERVER1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER1.ORACLE.COM)(PORT=1521)))

Services Summary...

Service "crms" has 1 instance(s).

Instance "crms", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

CHECKING TNS CONNECTIVITY ON TARGET SEVER

[oracle@SERVER1 admin]$ tnsping crmsdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-JAN-2015 10:51:42

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = PROTOCOL= TCP)(HOST = 192.168.1.130)

(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = crms)))OK (30 msec)

[oracle@SERVER1 admin]$ tnsping to_crmsdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-JAN-2015 10:52:23


Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST =(ADDRESS =(PROTOCOL= TCP)(HOST = 192.168.1.131)

(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = crms)))OK (50 msec)

CONFIGURE PASSWORD FILE ON AUXILIARY SERVER

$ cd $ORACLE_HOME/dbs

$ export ORACLE_SID=crms

[oracle@SERVER2 dbs]$ orapwd file=orapwcrms password=oracle entries=3 force=y

Password must be same on Target database & Auxiliary database. When you connect RMAN to the

Source database as TARGET, must specify a password, even if RMAN uses OS authentication.





SET THE SAME PASSWORD OF CLONE DATABASE INTO THE TARGET DATABASE

SYS> alter user sys identified by oracle;

(or)

$ cd $ORACLE_HOME/dbs$ export ORACLE_SID=crms

[oracle@SERVER2 dbs]$ orapwd file=orapwcrms password=oracle entries=3 force=y

To create a password in target server, we can use alter user statement or orapwd utlity.

STARTUP AUXILIARY DATABASE AT NOMOUNT STAGE & EXIT

[oracle@SERVER2 ~]$ export ORACLE_SID=crms

[oracle@SERVER2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 7 12:03:05 2015


Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1255473152 bytes

Fixed Size 1336232 bytes

Variable Size 822086744 bytes

Database Buffers 419430400 bytes

Redo Buffers 12619776 bytesSQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

POINTS TO NOTE :

Once I started my auxiliary instance in NOMOUNT state, i have disconnected SYS as ‘NO SESSION’

before duplicating a database. It is must and should.

DUPLICATING TARGET DATABASE

In order to clone with the source database open and must be on archivelog mode. From 11g we canclone databases using the “from active” database option. In this case we do NOT require an existing

backup, the active datafiles will be used as the source for the clone.

DUPLICATE TARGET DATABASE COMMAND PERFORMS THE FOLLOWING STEPS.

Creates an SPFILE.

Shuts down the instance and restarts it with the new spfile.

Restores the controlfile from the backup.

Mounts the database. Restore all datafiles.

RMAN creates all files in the converted names.

Recovers the datafiles up to the time specified and opens the database for availability.





CONNECT TO THE TARGET (SOURCE) & AUXILIARY (NEW CLONE) DATABASES USING RMAN


$ rman target sys/oracle@CRMSDB auxiliary sys/orac1e@TO_CRMSDB

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jan 7 12:04:55 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: CRMS (DBID=1570419964)

connected to auxiliary database: HRMS (not mounted)

RMAN> duplicate target database to 'hrms' from active database nofilenamecheck;

Starting Duplicate Db at 07-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory


{

shutdown clone immediate;

startup clone nomount;

}


Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started





Redo Buffers 12619776 bytes


{

sql clone "alter system set db_name =

''CRMS'' comment=

''Modified by RMAN duplicate'' scope=spfile";





sql clone "alter system set db_unique_name = ''CRMS'' comment= ''Modified by RMAN duplicate

'' scope=spfile";


startup clone force nomount

backup as copy current controlfile auxiliary format

'/u02/app/oracle/oradata/crms/control01.ctl ';

restore clone controlfile to '/u02/app/oracle/flash_recovery_area/crms/control02.ctl' from

'/u02/app/oracle/oradata/crms/control01.ctl';

alter clone database mount;

}


sql statement: alter system set db_name = ''CRMS'' comment= ''Modified by RMAN duplicate''

scope= spfile

sql statement: alter system set db_unique_name = ''CRMS'' comment= ''Modified by RMAN

duplicate'' scope=spfile








Starting backup at 07-JAN-15allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_crms.f

tag=TAG20150107T191625 R ECID=5 STAMP=868389386

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 07-JAN-15

Starting restore at 07-JAN-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 07-JAN-15

database mounted


{

set newname for datafile 1 to

"/u02/app/oracle/oradata/crms/system01.dbf";






"/u02/app/oracle/oradata/crms/sysaux01.dbf";


"/u02/app/oracle/oradata/crms/undotbs01.dbf";


"/u02/app/oracle/oradata/crms/users01.dbf";backup as copy reuse

datafile 1 auxiliary format

"/u02/app/oracle/oradata/crms/system01.dbf" datafile

2 auxiliary format

"/u02/app/oracle/oradata/crms/sysaux01.dbf" datafile

3 auxiliary format

"/u02/app/oracle/oradata/crms/undotbs01.dbf" datafile

4 auxiliary format

"/u02/app/oracle/oradata/crms/users01.dbf" ;

sql 'alter system archive log current';

}


executing command: SET NEWNAME




Starting backup at 07-JAN-15

using channel ORA_DISK_1


input datafile file number=00001 name=/u02/app/oracle/oradata/crms/system01.dbf

output file name=/u02/app/oracle/oradata/crms/system01.dbf tag=TAG20150107T191635



input datafile file number=00002 name=/u02/app/oracle/oradata/crms/sysaux01.dbf

output file name=/u02/app/oracle/oradata/crms/sysaux01.dbf tag=TAG20150107T191635



input datafile file number=00003 name=/u02/app/oracle/oradata/crms/undotbs01.dbf

output file name=/u02/app/oracle/oradata/crms/undotbs01.dbf tag=TAG20150107T191635



input datafile file number=00004 name=/u02/app/oracle/oradata/crms/users01.dbf

output file name=/u02/app/oracle/oradata/crms/users01.dbf tag=TAG20150107T191635



sql statement: alter system archive log current






{

backup as copy reuse

archivelog like

"/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_bbtg 7w9n_.arc"

auxiliary format

"/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}


Starting backup at 07-JAN-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=11 RECID=10 STAMP=868389460

output file

name=/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_0pps54ik _.arc

RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01


searching for all files in the recovery area

List of Files Unknown to the Database

=====================================

File Name:

/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_0pps54ik_.arcFile Name:

/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_8_0jps50sk_.arc

File Name:

/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_5_0dps4g2i_.arc

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name:

/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_0pps54ik_.arc

File Name:

/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_8_0jps50sk_.arc

File Name:

/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_5_0dps4g2i_.arc

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=868389462 file name=/u02/app/oracle/oradata/crms/system01.dbf


input datafile copy RECID=6 STAMP=868389462 file name=/u02/app/oracle/oradata/crms/sysaux01.dbf


input datafile copy RECID=7 STAMP=868389462 file name=/u02/app/oracle/oradata/crms/undotbs01.dbf






input datafile copy RECID=8 STAMP=868389462 file name=/u02/app/oracle/oradata/crms/users01.dbf


{

set until scn 897001;

recover

clone database

delete archivelog

;

}


executing command: SET until clause

Starting recover at 07-JAN-15

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file

/u02/app/oracle/flash_recover y_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_0pps54ik_.arc

archived log file

name=/u02/app/oracle/flash_recovery_area/CRMS/archivelog/2015_01_07/o1_mf_1_11_0p ps54ik_.arc

thread=1 sequence=11

media recovery complete, elapsed time: 00:00:01

Finished recover at 07-JAN-15


{



sql clone "alter system set db_name =

''CRMS'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";



}


database dismounted













sql statement: alter system set db_name = ''CRMS'' comment= ''Reset to original value by

RMAN'' s cope=spfile

sql statement: alter system reset db_unique_name scope=spfile









sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CRMS" RESETLOGS ARCHIVELOG

MAXLOGFILES 16MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u02/app/oracle/oradata/crms/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u02/app/oracle/oradata/crms/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u02/app/oracle/oradata/crms/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u02/app/oracle/oradata/crms/system01.dbf'

CHARACTER SET WE8MSWIN1252


{

set newname for tempfile 1 to

"/u02/app/oracle/oradata/crms/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/u02/app/oracle/oradata/crms/sysaux01.dbf",

"/u02/app/oracle/oradata/crms/undotbs01.dbf","/u02/app/oracle/oradata/crms/users01.dbf";

switch clone datafile all;

}



renamed tempfile 1 to /u02/app/oracle/oradata/crms/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/u02/app/oracle/oradata/crms/sysaux01.dbf RECID=1 STAMP=868389480cataloged datafile copy

datafile copy file name=/u02/app/oracle/oradata/crms/undotbs01.dbf RECID=2 STAMP=868389480





cataloged datafile copy

datafile copy file name=/u02/app/oracle/oradata/crms/users01.dbf RECID=3 STAMP=868389480


input datafile copy RECID=1 STAMP=868389480 file name=/u02/app/oracle/oradata/crms/sysaux01.dbf


input datafile copy RECID=2 STAMP=868389480 file name=/u02/app/oracle/oradata/crms/undotbs01.dbf


input datafile copy RECID=3 STAMP=868389480 file name=/u02/app/oracle/oradata/crms/users01.dbf


{

Alter clone database open resetlogs;

}


database opened

Finished Duplicate Db at 07-JAN-15

CONNECTING AUXILIARY DATABASE


$ rlsqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 7 12:58:06 2015


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select name, dbid, open_mode from v$database;

NAME DBID OPEN_MODE

--------- ---------- ----------

CRMS 1570458918 READ WRITE

same directory structure - active database duplication server to server - (11g).pdf

Documents