Oracle DBA: 12/31/15

Active database Duplication using RMAN 11gR2

Using "Active database Duplication" method Incomplete-recovery "NOT" possible ( SET UNTIL time/sequence/cancel). Because this duplication method only based on the logs are archived in the target database location . Even content in the online redologs from the target database. So we no need to take the backup of target database . but target database must be in archive log mode

if the database size in TB's with network connection between database enabled. we can use this method.

target database a.k.a (primary/source database)
auxiliary database a.k.a (destination/clone database)

Target database name: mydb
Auxiliary database name: orcl

Overview:

1. Copy the password file from Target database to auxiliary database location
2. Create the pfile from target database for Auxilary database( if target has spfile create pfile from spfile)
3. Configure the linster & tnsname on both target database and auxiliary database
4. Create the required directory structure in Auxillary db location (based on pfile )
5. Open the database in mount mode using modified pfile
6. Use rman command, RMAN will perform restore and recover the database.
$rman target sys/oracle@<target_dbname>
RMAN> connect auxiliary sys/oracle@<auxiliary_dbname>
(or)
rman target sys/oracle@<target_dbname> auxiliary sys/oracle@<auxiliary_dbname>
5.RMAN>duplicate target database to <auxiliary_dbname> from active database nofilenamecheck;

After "Active database Duplication"
1.Auxiliary DB has unique DBID
2.Auxiliary DB has Copy of data files & archived log files from target database
3.Recreate the new control files for auxiliary database
4.Recreates the online redo log files.
5.Restart the auxiliary instance.
6.Open the database with RESETLOGS.
7. enable the database in archivelog mode

STEP 1: Copy the password file from Target database to auxiliary database location

-bash-3.2$ scp orapwmydb oracle@192.168.168.2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl
oracle@192.168.168.2's password: mydb
orapwmydb           100%            1536     1.5KB/s       00:00

STEP 2: Create the pfile from target database for Auxilary database( if target has spfile create pfile from spfile)

From Target database location

SQL> conn /as sysdba
Connected.
SQL> show parameter spfile;
NAME                               TYPE                          VALUE
--------------------                          -----------                  ------------------------------
spfile                               string                           /u01/app/oracle/product/11.2.0/db_1/dbs/spfilemydb.ora

SQL> create pfile='/tmp/initorcl.ora' from spfile;
File created.

-bash-3.2$ cd /tmp
-bash-3.2$ scp initorcl.ora oracle@192.168.168.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
oracle@192.168.168.2's password:
initorcl.ora            100%        1478        1.4KB/s   00:00

From Auxiliary database location

-bash-3.2$ vi initorcl.ora
admin.__db_cache_size=130023424

admin.__java_pool_size=16777216

admin.__large_pool_size=4194304

admin.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

admin.__pga_aggregate_target=201326592

admin.__sga_target=754974720

admin.__shared_io_pool_size=83886080

admin.__shared_pool_size=373293056

admin.__streams_pool_size=16777216

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

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

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

*.log_archive_dest_1='location=/u02/archive'

*.memory_target=956301312

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

*.log_file_name_convert='/u01/app/oracle/oradata/mydb/','/u01/app/oracle/oradata/orcl/'

*.db_file_name_convert='/u01/app/oracle/oradata/mydb/','/u01/app/oracle/oradata/orcl/'

STEP 3: Configure the linster & tnsname on both target database and auxiliary database

Listener.ora (following lines need to added in listerner.ora) (In TARGET db location)
-bash-3.2$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

)

ADR_BASE_LISTENER = /u01/app/oracle

-bash-3.2$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-APR-2015 21:57:59

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date 07-APR-2015 08:03:40

Uptime 0 days 13 hr. 54 min. 19 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/standalone/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standalone)(PORT=1521)))

Services Summary...

Service "mydb" has 1 instance(s).

Instance "mydb", status READY, has 1 handler(s) for this service...

Service "mydbXDB" has 1 instance(s).

Instance "mydb", status READY, has 1 handler(s) for this service...

The command completed successfully

tnsnames.ora(following lines need to added in tnsnames.ora) (In TARGET db location)

-bash-3.2$ cat tnsnames.ora
MYDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = mydb)

)

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl) (UR=A)

)

)
-bash-3.2$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 07-APR-2015 22:00:51

Used parameter files:

/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.239.7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR=A)))

OK (40 msec)

Listener.ora (following lines need to added in listerner.ora) (In Auxillary db location)

-bash-3.2$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

-bash-3.2$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2012 18:57:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                26-SEP-2012 18:57:33
Uptime                    0 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/standalone/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standalone)(PORT=1521)))

Services Summary...

Service "orcl" has 1 instance(s).

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

Service "orclXDB" has 1 instance(s).

Instance "orcl", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

tnsnames.ora(following lines need to added in tnsnames.ora (In Auxillary db location)

-bash-3.2$ cat tnsnames.ora

MYDB =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = mydb)

)

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

(UR=A)

)

-bash-3.2$ tnsping mydb

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 07-APR-2015 22:13:04

Used parameter files:

/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.239.5)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydb)))

OK (20 msec)

bash-3.2$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 07-APR-2015 22:13:42

Used parameter files:

/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.239.7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR=A)))

OK (40 msec)

STEP 4: Create the required directory structure in Auxillary db location

create the nessary directory structure based on the init file.
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/oradata/orcl/
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
mkdir -p /u01/app/oracle/oradata/
mkdir -p /u02/archive/

STEP 5. Open the database in mount mode using modified pfile

Note: In Auxillary database location add entry in /etc/oratab

-bash-3.2$ . oraenv
ORACLE_SID = [11gR2_NEW] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

-bash-3.2$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on 07-APR-2015 22:13:42

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             889195488 bytes
Database Buffers          335544320 bytes
Redo Buffers                9007104 bytes
SQL>

STEP 6. Use rman command from Auxiliary database, RMAN will perform restore and recover the database

-bash-3.2$ rman target sys/mydb@mydb

Recovery Manager: Release 11.2.0.2.0 Production on 07-APR-2015 22:13:42

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

connected to target database: MYDB (DBID=3056476564)

RMAN> connect auxiliary sys/mydb@orcl

Recovery Manager: Release 11.2.0.2.0 Production on 07-APR-2015 22:13:42

2> allocate auxiliary channel prmy1 type disk;

3> allocate auxiliary channel prmy2 type disk;

4> allocate auxiliary channel prmy3 type disk;

5> allocate channel prmy4 type disk;

6> allocate channel prmy5 type disk;

7> allocate channel prmy6 type disk;
8> DUPLICATE DATABASE TO orcl FROM ACTIVE DATABASE nofilenamecheck;

9> }

....

.....

.......

....

database opened

Finished Duplicate Db at 07-APR-15

verify the status of back job is running or not [open target database and run following commands]

SQL> select count(*),event from v$session group by event;

COUNT(*) EVENT

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

2 SQL*Net message from client

1 asynch descriptor resize

3 RMAN backup & recovery I/O

1 pmon timer

17 rdbms ipc message

1 smon timer

1 Streams AQ: qmn slave idle wait

1 Space Manager: slave idle wait

1 VKTM Logical Idle Wait

1 Streams AQ: qmn coordinator idle wait

1 Streams AQ: waiting for time management or cleanup tasks

2 DIAG idle wait

12 rows selected.

SQL>select sofar,totalwork from v$session_longops;
SOFAR TOTALWORK

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

254733 403940

97278 106240

97279 0

85760 85760

85761 85761

92030 97280

92031 0

254077 0

8 rows selected.

Verify the status of auxiliary database status

SQL> select name,open_mode from v$database;

NAME             OPEN_MODE
---------         ----------
ORCL             READ WRITE

Oracle DBA

Thursday 31 December 2015

Active Database Duplication

Blog Archive

About Me