How to create physical standby database

Here will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is installed on standby server.

	PRIMARY	STANDBY
SERVER	primary-host	standby-host
DB_UNIQUE_NAME	PROD	PRODSBY

PRIMARY:

Make sure database is archive log mode, and enable force logging.

 PRIMARY > select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG 

-----  Enable force loggin:
-----  FORCE LOGGING is required, every changes in database will go to redo logs, which will be applied on standby

SQL > ALTER DATABASE FORCE LOGGING;

Database altered.

[PRIMARY]Add standby logfiles;

In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a “standby redo log” allowing complete recovery in case of Primary site is lost.

SQL > select GROUP#,BYTES/1024/1024,thread# from v$log;

    GROUP# BYTES/1024/1024    THREAD#
---------- --------------- ----------
         1             128          1
         2             128          1
         3             128          1
         4             128          1
         5             128          1
         6             128          1
         7             128          1
         8             128          1

8 rows selected.


SQL >col member for a45
SQL > set pagesize 200
SQL > set lines 200
SQL >select GROUP#,MEMBER from v$logfile;
    GROUP# MEMBER
---------- ---------------------------------------------
         1 /uv1249/redo1/PROD/redo_01a.dbf
         1 /uv1249/redo2/PROD/redo_01b.dbf
         2 /uv1249/redo1/PROD/redo_02a.dbf
         2 /uv1249/redo2/PROD/redo_02b.dbf
         3 /uv1249/redo1/PROD/redo_03a.dbf
         3 /uv1249/redo2/PROD/redo_03a.dbf
         4 /uv1249/redo1/PROD/redo_04a.dbf
         4 /uv1249/redo2/PROD/redo_04a.dbf
         5 /uv1249/redo1/PROD/redo_05a.dbf
         5 /uv1249/redo2/PROD/redo_05a.dbf
         6 /uv1249/redo1/PROD/redo_06a.dbf
         6 /uv1249/redo2/PROD/redo_06a.dbf
         7 /uv1249/redo1/PROD/redo_07a.dbf
         7 /uv1249/redo2/PROD/redo_07b.dbf
         8 /uv1249/redo1/PROD/redo_08a.dbf
         8 /uv1249/redo2/PROD/redo_08b.dbf



--- Dynamics query to generate the sqls.

SQL > select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
/  2    3    4    5    6    7

Create Standby redo
------------------------------------------------------------------------------------------------------------------
--------------------------------
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_01a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_01b.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_02a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_02b.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_03a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_03a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_04a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_04a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_05a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_05a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_06a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_06a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_07a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_07b.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_08a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_08b.dbf' size 134217728;

16 rows selected.



SQL> alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_01a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_01b.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_02a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_02b.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_03a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_03a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_04a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_04a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_05a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_05a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_06a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_06a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_07a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_07b.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo1/PROD/stdby_redo_08a.dbf' size 134217728;
alter database add standby logfile '/uv1249/redo2/PROD/stdby_redo_08b.dbf' size 134217728;


database altered

database altered
....

.
.
.
.
.

[PRIMARY]create password file

cd $ORACLE_HOME/dbs

orapwd file=orapwPROD password=prod1234 entries=30

[PRIMARY]Configure listener and tns entries as below.

cat tnsnames.ora
PROD =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PROD)))
PRODSBY =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSBY)))


cat listener.ora

LISTENER_PROD =
   (address_list = 
    (address =
           (protocol = tcp)
           (host     = primary-host)
           (port     = 1571)
      )
)
SID_LIST_LISTENER_PROD =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PROD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = PROD)
    )
)

lsnrctl start LISTENER_PROD

[PRIMARY]Do below changes in spfile of primary

--- log_archive_config parameter enables sending and receiving of archive logs to remote dest 
----DG_CONFIG - need to specify the list of db_unique_name of both primary and standby.
alter system set log_archive_config='DG_CONFIG=(PROD,PRODSBY)' scope=BOTH;

--- archive destination in local(primary server)
alter system set log_archive_dest_1='LOCATION=/uv1249/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'  scope=BOTH;

---log_archive_dest_2 parameter defines the archive destination of standby
--- ASYNC - This is used for maximum performance mode(DEFAULT) .Means redo logs generated in primary need not be shipped/applied in standby
alter system set log_archive_dest_2='SERVICE=PRODSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSBY' scope=BOTH;

alter system set log_archive_dest_state_2='ENABLE'  scope=BOTH;
alter system set log_archive_dest_state_1='ENABLE'  scope=BOTH;

--- Tns entry name for primary 
alter system set fal_client='PROD'  scope=BOTH;

--- tns entry name for primary -- i.e It will fetch the archives from standby .
alter system set fal_server='PRODSBY'  scope=BOTH;

[PRIMARY]So after the changes the spfile in primary will look as below.

strings spfilePROD.ora

*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='/uv1249/data1/PROD/control1.ora','/uv1249/data2/PROD/control2.ora','/uv1249/undo/PROD/control3.ora'
*.core_dump_dest='/uv1249/admin/PROD/cdump'
*.db_block_size=8192
*.db_cache_size=3008M
*.db_file_multiblock_read_count=64
*.db_keep_cache_size=512M
*.db_name='PROD'
*.db_unique_name='PROD'
*.diagnostic_dest='/uv1249/admin/PROD/diag'
*.dml_locks=100
*.fal_client='PROD'
*.fal_server='PRODSBY'
*.instance_name='PROD'
*.job_queue_processes=2
*.log_archive_config='DG_CONFIG=(PROD,PRODSBY)'
*.log_archive_dest_1='LOCATION=/uv1249/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'
*.log_archive_dest_2='SERVICE=PRODSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSBY'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='arch_PROD_%t_%s_%r.arc'
*.log_buffer=31367168# log buffer update
*.log_checkpoint_interval=300000
*.max_dump_file_size='unlimited'
*.nls_date_format='DD-MON-RR'
*.os_authent_prefix='NULL'
*.pga_aggregate_target=1500M
*.processes=600
*.remote_dependencies_mode='SIGNATURE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.sga_max_size=5152M# internally adjusted
*.shared_pool_size=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=30000
*.undo_tablespace='UNDO'

STANDBY:

[STANDBY] Create directory structure same as that of primary

mkdir -p /uv1249/data1/PROD/
mkdir -p /uv1249/data2/PROD/
mkdir -p /uv1249/arch/PROD
mkdir -p /uv1249/undo/PROD
mkdir -p /uv1249/admin/PROD/cdump
mkdir -p /uv1249/admin/PROD/diag

[STANDBY]pfile and password file creation in $ORACLE_HOME/dbs location:

-----copy the password file from production and rename to standby name.
cd $ORACLE_HOME/dbs
scp oracle@primary-host:/u01/app/oracle/product/11.2.0/dbs/orapwPROD orapwPRODSBY

-----create an init file with minimum parameter as below

vi initPRODSBY.ora

DB_NAME=PROD
DB_UNIQUE_NAME=PRODSBY
control_files='/uv1249/data1/PROD/control1.ora','/uv1249/data2/PROD/control2.ora','/uv1249/undo/PROD/control3.ora'
fal_client='PRODSBY'
fal_server='PROD' 
db_file_name_convert='/uv1249/data1/PROD/','/uv1249/data1/PROD/','/uv1249/data2/PROD/','/uv1249/data2/PROD/'
log_file_name_convert = '/uv1249/redo1/PROD/,'/uv1249/redo1/PROD/','/uv1249/redo2/PROD/','/uv1249/redo2/PROD/'
standby_file_management='AUTO'
log_archive_config='DG_CONFIG=(PROD,PRODSBY)'
log_archive_dest_1='LOCATION=/uv1249/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODSBY'
log_archive_dest_2='service=PROD ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PROD'

[STANDBY] Listener and tns setup

cat tnsnames.ora
PROD =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PROD)))
PRODSBY =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSBY)))

cat listener.ora

LISTENER_PRODSBY =
   (address_list = 
    (address =
           (protocol = tcp)
           (host     = standby-host)
           (port     = 1571)
      )
)
SID_LIST_LISTENER_PRODSBY =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRODSBY)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
      (SID_NAME = PRODSBY)
    )
)

lsnrctl start LISTENER_PRODSBY

[STANDBY]start the standby database in nomount state:

SQL > startup nomount
ORACLE instance started.

Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes

[STANDBY]Now check the connectivity between primary and standby using sys password

-----In primary:

$ sqlplus sys/prod1234@PRODSBY as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 15:17:56 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


----- IN STANDBY:

sqlplus sys/prod1234@PROD as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 25 15:17:56 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

STANDBY:
[STANDBY]Now connect to rman and run the duplicate command as mentioned below.

rman target sys/prod1234@PROD auxiliary sys/prod1234@PRODSBY


Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 16 08:42:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 
connected to target database: PROD (DBID=839852638)
connected to auxiliary database: PROD (not mounted)

rman >

run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate channel prmy6 type disk;
allocate channel prmy7 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;
allocate auxiliary channel stby5 type disk;
allocate auxiliary channel stby6 type disk;
allocate auxiliary channel stby7 type disk;
allocate auxiliary channel stby8 type disk;

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE  nofilenamecheck;
}

[STANDBY]Once duplication is done. Start the recovery

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

DATABASE ALTERED.

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

If you wish you open the standby with read only mode as active dataguard, then use below one.

sql> alter datbase open;

sql> alter database recover managed standby database using current logfile disconnect from session;

The physical standby setup is ready for use.

2 thoughts on “How to create physical standby database”

vijay says:

October 18, 2016 at 10:26 am

Excellent doc!

Alok Maurya says:

April 28, 2020 at 7:10 pm

Appreciated, Uncut precise and much informative.
Thanks a lot.

How to create physical standby database

PRIMARY:

STANDBY:

[STANDBY] Create directory structure same as that of primary

About Admin

2 thoughts on “How to create physical standby database”

Leave a Reply Cancel reply

PRIMARY:

STANDBY:

[STANDBY] Create directory structure same as that of primary

About Admin

Related Articles

2 thoughts on “How to create physical standby database”

Leave a Reply Cancel reply