• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to create physical Standby database in Oracle

How to create physical Standby database in Oracle

January 23, 2016 by techgoeasy Leave a Comment

Few days back I did create the physical standby database for our Oracle production database.I thought of sharing the experience with you.So here are detailed steps on How to do physical Standby creation 

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
Standy Database Instance Name: TESTSTDY
Standy db_unique_name: TESTSTDY

Primary Production servername: mygoeasy1
Standby servername: mygoeasy2

Step on How to do physical Standby creation
Step 1:
On Primary side Enable Force Logging, so that even the nologging data uploaded at primary site is transferred to Standby site

[[email protected]]$ 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

[[email protected]]$ mkdir –p /export/home/oracle/backup

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

[[email protected]]$ 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:
Take the backup of primary site using rman backup command

RMAN> run
{
backup device type disk format '/export/home/oracle/backup/%U' database;
backup device type disk format '/export/home/oracle/backup/%U' current controlfile for standby;
}

Starting backup at 02-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=30 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA/TEST/datafile/system.2271.57967558991
input datafile fno=00003 name=+DATA/TEST/datafile/sysaux.273.57967558991
input datafile fno=00002 name=+DATA/TEST/datafile/undo1.472.57967558991
input datafile fno=00004 name=+DATA/TEST/datafile/undo2.472.57967558991
input datafile fno=00005 name=+DATA/TEST/datafile/undo3.372.57967558991
channel ORA_DISK_1: starting piece 1 at 02-JAN-16

Copy the backup created on site mygoeasy1 to mygoeasy to the same directory

Step 6

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

[[email protected]]$ mkdir -p /u001/app/oracle/product/11.2/

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

[[email protected]]$ mkdir -p /u001/app/oracle/product/11.2/diag
[[email protected]]$
[[email protected]]$ export ORACLE_HOME=/u001/app/oracle/product/11.2
[[email protected]]$ export ORACLE_SID=TESTSTDY ;cd $ORACLE_HOME/dbs
[[email protected]]$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 orapw
is

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

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

[[email protected]]$cd $ORACLE_HOME/dbs

Copy the init.ora from 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 same as production or we can set it low also

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

Step 7.

Create a tnsnames names at the standby site

[[email protected]]$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

[[email protected]] sqlplus sys/[email protected] as sysdba
The above command should connect successfully

[[email protected]] sqlplus sys/[email protected] as sysdba
The above command should connect successfully

Step 8.

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

[[email protected]]$ /u001/app/oracle/product/11.2/bin/rman target sys/[email protected] auxiliary /

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;
The above command will restore the datafiles to the ASM storage

After this we need to Add standby redo logs to standby database

[[email protected]]$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

[[email protected]]$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 primary itself

You can check the connectivity now

[[email protected]] sqlplus sys/[email protected] as sysdba
The above command should connect successfully

[[email protected]1] sqlplus sys/[email protected] as sysdba
The above command should connect successfully

Step 11.

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, Standby database will start receiving the archive logs, and recovery will be started

Verify that managed recovery is running:

[[email protected]]$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 current primary

[[email protected]]$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 working  standby site after the steps are completed.

Please let me know if you like this post on how to create physical standby database .Please do provide the feedback

Filed Under: Oracle, Oracle Database Tagged With: How to do physical Standby creation, physical standby database

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us