Home » Oracle » Oracle Database » duplicate target database for standby from active database

duplicate target database for standby from active database

Leave a Comment / By /

A few days back I did create the physical standby database for our production using the new RMAN feature Active database. I thought of sharing the experience with you. So here are detailed steps on How to do a duplicate target database for standby from the active database

What is  duplicate using an Active database :

Active database duplication copies the target database over the network to the destination and then creates the duplicate database. The only difference is you don’t need to have the pre-existing RMAN backups and copies. The duplication work is performed by an auxiliary channel. This channel corresponds to a server session on the auxiliary instance on the auxiliary host.

As part of the duplicating operation, RMAN automates the following steps:

  • Creates a control file for the duplicate database.
  • Restarts the auxiliary instance and mounts the duplicate control file.
  • Creates the duplicate data files and recovers them with incremental backups and archived redo logs.
  • Opens the duplicate database with the reset logs option.
  • For the active database duplication, RMAN will copy the target database data files over the network to the auxiliary instance.

The disadvantage to this approach

  • Very high bandwidth required over the network to perform it
  • Primary database performance will be impacted due to direct copy

I am assuming we have Oracle ASM configured on both the primary and standby site. ASM instance has DATA diskgroup for storing datafile and FLASH for archive log files

Configuration

Production Database Name: TEST
Production Database Instance Name: TEST
Production db_unique_name: TEST
Standby Database Instance Name: TESTSTDY
Standby db_unique_name: TESTSTDY

Primary Production server name: mygoeasy1
Standby server name: mygoeasy2

Step on How to do physical Standby database  creation using active database


Step 1:
On the Primary side Enable Force Logging, so that even the no-logging data uploaded at the primary site is transferred to the Standby site

[oracle@mygoeasy1]$ sqlplus / as sysdba
SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

Step 2
On the primary node, create a staging directory for taking the backup of the production database to create the standby database

[oracle@mygoeasy1]$ mkdir –p /export/home/oracle/backup

Step 3.
Create the same exact path on the standby host:

[oracle@mygoeasy2]$ mkdir -p /export/home/oracle/backup

Step 4:
On the primary node, connect to the primary database and create PFILE from SPFILE in the staging directory. For example:

sqlplus / as sysdba
create pfile='/export/home/oracle/backup/test.ora' from spfile;

Step 5

See also  Multi Language Support in 11i/R12

Copy the ORACLE_HOME from the primary site to the standby site and Prepare the initialization file for standby instance on the server mygoeasy2

[oracle@mygoeasy2]$ mkdir -p /u001/app/oracle/product/11.2/

Tar the ORACLE_HOME from the primary site and copy it to this folder, untar it, and run clone.pl to configure it

[oracle@mygoeasy2]$ mkdir -p /u001/app/oracle/product/11.2/diag
[oracle@mygoeasy2]$
[oracle@mygoeasy2]$ export ORACLE_HOME=/u001/app/oracle/product/11.2
[oracle@mygoeasy2]$ export ORACLE_SID=TESTSTDY ;cd $ORACLE_HOME/dbs
[oracle@mygoeasy2]$orapwd file=orapwTESTSTDY password=oracle ignorecase=Y

The password oracle is the SYS password for both the primary and standby site. The general format for orapwd is

orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n> password=<sys pass>

[oracle@mygoeasy2]$ ls -ltr *TESTSTDY*
-rw-r----- 1 oracle oinstall 3536 Jan 3 13:15 orapwTESTSTDY

[oracle@mygoeasy2]$cd $ORACLE_HOME/dbs
Copy the init.ora from the Primary site and Set the below parameters in init.ora of standby

*.db_name='TEST'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_file_name_convert='+DATA/TEST/datafile/','+DATA/TESTSTDY/datafile/','+DATA/TEST/tempfile/','+DATA/TESTSTDY/tempfile/' *.log_file_name_convert='+DATA/TEST/onlinelog/','+DATA/TESTSTDY/onlinelog/' ,'+FLASH/TEST/onlinelog/','+FLASH/TESTSTDY/onlinelog/'
*.db_unique_name=TESTSTDY

The memory parameter can remain the same as production or we can set it low also

-rw-r----- 1 oracle oinstall 1511 Jan 3 15:55 initTESTSTDY.ora

Step 6.

Create a tnsnames names at the Physical standby database site

[oracle@mygoeasy2]$cd $ORACLE_HOME/network/admin
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mygoeasy1 )(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)

TESTSTDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mygoeasy2)(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTSTDY)
)
)

-rw-r----- 1 oracle oinstall 1511 Jan 3 15:55 tnsnames.ora

You can check the connectivity now

[oracle@mygoeasy2] sqlplus sys/oracle@TEST as sysdba
The above command should connect successfully

[oracle@mygoeasy2] sqlplus sys/oracle@TESTSTDY as sysdba
The above command should connect successfully

Step 7.

Add the following to the  tnsnames.ora on the primary site also

[oracle@mygoeasy1]$cd $ORACLE_HOME/network/admin
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mygoeasy1 )(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)

TESTSTDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mygoeasy2)(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTSTDY)
)
)

-rw-r----- 1 oracle oinstall 1511 Jan 3 15:55 tnsnames.ora

You can check the connectivity now

[oracle@mygoeasy1] sqlplus sys/oracle@TEST as sysdba
The above command should connect successfully

[oracle@mygoeasy1] sqlplus sys/oracle@TESTSTDY as sysdba
The above command should connect successfully

Step 8.

See also  How to Create User in Oracle and Assign privileges

Create the standby database using RMAN duplicate command at the standby site

[oracle@mygoeasy2]$ /u001/app/oracle/product/11.2/bin/rman target sys/oracle@TEST auxiliary sys/oracle@TESTSTDBY

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 3 16:56:09 2016

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

connected to target database: TEST (DBID=758755757)
connected to auxiliary database: TEST (not mounted)

RMAN> duplicate target database for standby for active database;

We can use multiple channel to speed up the process.It would mean more network utilization.

CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

So

RMAN>RUN
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
duplicate target database for standby from active database;
}

The above command will restore the datafiles to the ASM storage

After this, we need to add standby redo logs to the standby database

[oracle@mygoeasy2]$sqlplus / as sysdba

SQL> alter database add standby logfile group 4 '+DATA' size 1000m;

Database altered.

SQL> alter database add standby logfile group 5 '+DATA' size 1000m;

Database altered.

SQL> alter database add standby logfile group 6 '+DATA' size 1000m;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL>

Step 9.

Establish the connectivity  to primary from standby

[oracle@mygoeasy2]$sqlplus / as sysdba

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
TEST PHYSICAL STANDBY

SQL> alter system set standby_archive_dest='+FLASH';

System altered.

SQL> alter system set log_archive_dest_1='location=+FLASH valid_for=(all_logfiles,all_roles) db_unique_name=TESTSTDY';
SQL> alter system set log_archive_config='dg_config=(TEST,TESTSTDY)';

System altered.

SQL> alter system set log_archive_dest_2='service=TEST lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=TEST';

System altered.

SQL> alter system set fal_client=TESTSTDY;

System altered.

SQL> alter system set fal_server=TEST;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.
Step 10.

Create tnsnames or connect strings at Primary server

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mygoeasy1 )(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)

TESTSTDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mygoeasy2)(PORT = 1529))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTSTDY)
)
)

Note:-

TESTSTDY will connect to standby while TEST connects to the primary itself

You can check the connectivity now

[oracle@mygoeasy1] sqlplus sys/oracle@TEST as sysdba
The above command should connect successfully

[oracle@mygoeasy1] sqlplus sys/oracle@TESTSTDY as sysdba
The above command should connect successfully

Step 11.

See also  How to Alter Table in Oracle

Establish the connectivity to standby from primary

SQL> alter system set log_archive_config='dg_config=(TEST,TESTSTDY)';

System altered.

SQL> alter system set log_archive_dest_2='service=TESTSTDY arch async noaffirm valid_for=(online_logfiles,primary_role)';

System altered.

SQL> alter system set fal_client=TEST;

System altered.

SQL> alter system set fal_server=TESTSTDY;

System altered.

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
TEST PRIMARY

SQL>
Step 12.

Check the standby for recovery

After the setting in Step 10 and Step 11, the Standby database will start receiving the archive logs, and recovery will be started

Verify that managed recovery is running:

[oracle@mygoeasy2]$sqlplus / as sysdba

Verify the MRP is running as expected

SQL> select process, status , sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 991
ARCH CLOSING 992
MRP0 APPLYING_LOG 989
RFS IDLE 0
RFS IDLE 0
RFS IDLE 104
and check for the gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

The difference should be zero

Step 13.

Set role transition-specific parameters for the current primary

[oracle@mygoeasy1]$sqlplus / as sysdba

SQL> alter system set db_file_name_convert='+DATA/TESTSTDY/datafile/','+DATA/TEST/datafile/','+DATA/TESTSTDY/tempfile/','+DATA/TEST/tempfile/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='+DATA/TESTSTDBY/onlinelog/','+DATA/TEST/onlinelog/',+FLASH/TESTSTDBY/onlinelog/','+FLASH/TEST/onlinelog/' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

Configure a Standby Redo Log at the primary site also

SQL> alter database add standby logfile group 4 size 1000m;

Database altered.

SQL>alter database add standby logfile group 5 size 1000m

Database altered.

SQL>alter database add standby logfile group 6 size 1000m

Database altered.

All the above steps are to be executed with extreme care and We will have a working standby site after the steps are completed.

Please let me know if you like this post on the duplicate target database for standby from active database. Please do provide the feedback

Related Articles
Snapshot standby database in Oracle
how to find archive log sequence number in oracle
how to check alert log errors in oracle

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top