Steps to Create Physical Standby Database in Oracle

Steps to Create Physical Standby Database in Oracle 

Introduction

Here Steps to Create Physical Standby Database in Oracle  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.

SERVER ----->PRIMARY---->primary_host

DB_UNIQUE_NAME---->PRIMARY---->PRODDB

SERVER ----->STANDBY---->standby_host

DB_UNIQUE_NAME---->STANDBY---->PRODSTAN

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 system

 

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             138          1

         2             138          1

         3             138          1

         4             138          1

         5             138          1

         6             138          1

         7             138          1

         8             138          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 /u01/redo1/PROD/redo_01a.dbf

         1 /u01/redo2/PROD/redo_01b.dbf

         2 /u01/redo1/PROD/redo_02a.dbf

         2 /u01/redo2/PROD/redo_02b.dbf

         3 /u01/redo1/PROD/redo_03a.dbf

         3 /u01/redo2/PROD/redo_03a.dbf

         4 /u01/redo1/PROD/redo_04a.dbf

         4 /u01/redo2/PROD/redo_04a.dbf

         5 /u01/redo1/PROD/redo_05a.dbf

         5 /u01/redo2/PROD/redo_05a.dbf

         6 /u01/redo1/PROD/redo_06a.dbf

         6 /u01/redo2/PROD/redo_06a.dbf

         7 /u01/redo1/PROD/redo_07a.dbf

         7 /u01/redo2/PROD/redo_07b.dbf

         8 /u01/redo1/PROD/redo_08a.dbf

         8 /u01/redo2/PROD/redo_08b.dbf

Dynamics query to generate the sqls.

 

SQL > select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'prodstan_'||

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 '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 124217729;

 

16 rows selected.

SQL> alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 134217728;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 134217728;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 134217728;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 134217728;

 

 

 

database altered

[PRIMARY] create password file

cd $ORACLE_HOME/dbs

 

orapwd file=orapwPRODDB password=prod123 entries=30

[PRIMARY]Configure listener and tns entries as below.

 

cat tnsnames.ora

PRODDB =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))

PRODSTAN =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))

 

 

cat listener.ora

 

LISTENER_PRODDB =

   (address_list =

    (address =

           (protocol = tcp)

           (host     = primary-host)

           (port     = 1571)

      )

)

SID_LIST_LISTENER_PRODDB =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PRODDB)

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

      (SID_NAME = PRODDB)

    )

)

 

lsnrctl start LISTENER_PRODDB

 

[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=(PRODDB,PRODSTAN)' scope=BOTH;

 

--- archive destination in local(primary server)

alter system set log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'  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=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN' 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='PRODDB'  scope=BOTH;

 

--- tns entry name for primary -- i.e It will fetch the archives from standby .

alter system set fal_server='PRODSTAN'  scope=BOTH;

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

 strings spfilePRODDB.ora

 

*.audit_trail='DB'

*.compatible='11.2.0'

*.control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'

*.core_dump_dest='/u01/admin/PROD/cdump'

*.db_block_size=8193

*.db_cache_size=3009M

*.db_file_multiblock_read_count=63

*.db_keep_cache_size=512M

*.db_name='PRODDB'

*.db_unique_name='PRODDB'

*.diagnostic_dest='/u01/admin/PROD/diag'

*.dml_locks=100

*.fal_client='PRODDB'

*.fal_server='PRODSTAN'

*.instance_name='PRODDB'

*.job_queue_processes=2

*.log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'

*.log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'

*.log_archive_dest_2='SERVICE=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN'

*.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 point

mkdir -p /u01/data1/PROD/

mkdir -p /u01/data2/PROD/

mkdir -p /u01/arch/PROD

mkdir -p /u01/undo/PROD

mkdir -p /u01/admin/PROD/cdump

mkdir -p /u01/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/orapwPRODDB orapwPRODSTAN

 

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

 

vi initPRODSTAN.ora

 

DB_NAME=PRODDB

DB_UNIQUE_NAME=PRODSTAN

control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'

fal_client='PRODSTAN'

fal_server='PRODDB'

db_file_name_convert='/u01/data1/PROD/','/u01/data1/PROD/','/u01/data2/PROD/','/u01/data2/PROD/'

log_file_name_convert = '/u01/redo1/PROD/,'/u01/redo1/PROD/','/u01/redo2/PROD/','/u01/redo2/PROD/'

standby_file_management='AUTO'

log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'

log_archive_dest_1='LOCATION=/u01/arch/PRODDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODSTAN'

log_archive_dest_2='service=PRODDB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PRODDB'

 [STANDBY] Listener and tns setup  

cat tnsnames.ora

PRODDB =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))

PRODSTAN =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))

 

cat listener.ora

 

LISTENER_PRODSTAN =

   (address_list =

    (address =

           (protocol = tcp)

           (host     = standby-host)

           (port     = 1571)

      )

)

SID_LIST_LISTENER_PRODSTAN =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PRODSTAN)

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

      (SID_NAME = PRODSTAN)

    )

)

 

lsnrctl start LISTENER_PRODSTAN

[STANDBY]start the standby database in nomount state:

SQL > startup nomount

ORACLE instance started.

 

Total System Global Area 5415597569 bytes

Fixed Size                  3170303 bytes

Variable Size             805970240 bytes

Database Buffers         3502926846 bytes

Redo Buffers                3530176 bytes

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

 

 

-----In primary:

 

$ sqlplus sys/prod123@PRODSTAN as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020

 

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/prod123@PRODDB as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020

 

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/prod123@PRODDB auxiliary sys/prod123@PRODSTAN

 

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue June 16 08:42:46 2020

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

connected to target database: PRODDB (DBID=728741429)

connected to auxiliary database: PRODDB (not mounted)

 

rman >

 

run

{

allocate channel primy1 type disk;

allocate channel primy2 type disk;

allocate channel primy3 type disk;

allocate channel primy4 type disk;

allocate channel primy5 type disk;

allocate channel primy6 type disk;

allocate channel primy7 type disk;

allocate auxiliary channel stanby1 type disk;

allocate auxiliary channel stanby2 type disk;

allocate auxiliary channel stanby3 type disk;

allocate auxiliary channel stanby4 type disk;

allocate auxiliary channel stanby5 type disk;

allocate auxiliary channel stanby6 type disk;

allocate auxiliary channel stanby7 type disk;

allocate auxiliary channel stanby8 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 physcial standby setup is ready for use

Note: Info on Physical standby datbase in oracle it may be differ in your environment like production,testing,development and directory structures and na,ming conventions etc 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Dataguard Broker Configuration DG Broker in Oracle 12c

 Dataguard Broker Configuration DG Broker in Oracle 12c 

Introduction

Dataguard BrokerDG Broker in Oracle 12c  is used to automate monitoring and controlling standby setups. it is very useful when we have multiple standby systems

In this DG Broker in Oracle 12c blog we are going to expalin how to enable db broker configuration in Oracle 12c in existing standby setup. before going to start the process dg broker make sure standby setup  is ready

Prerequisites

PRIMARY UNIQUE DB NAME---------->PROCDB

STANDBY UNIQUE DB NAME------------>PROCSTAN

Now let us start the process

Step 1 : Set dg_broker_start to true [ON BOTH PRIMARY DB  AND STANDBY DB]

PROCDB> alter system set dg_broker_start=true sid='*';

 

System altered.

 

 

PROCSTAN> alter system set dg_broker_start=true sid='*';

 

System altered.

Step 2:  Add primary db configuration[ ON PRIMARY]

$dgmgrl

DGMGRL for Solaris: Release 12.2.0.1.0 - Production on Sun May 3 12:22:13 2020

 

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

 

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sysdg

Password:

Connected to "PROCDB"

Connected as SYSDG.

 

DGMGRL> CREATE CONFIGURATION 'procdb_dg' AS PRIMARY DATABASE IS 'PROCDB' CONNECT IDENTIFIER IS PROCDB;

Configuration "procdb_dg" created with primary database "PROCDB"

 

 

DGMGRL> SHOW CONFIGURATION

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB - Primary database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

Step 3: Add standby Configuration[ ON PRIMARY]

DGMGRL>  add database PROCSTAN as connect identifier is PROCSTAN;

Database "procstan" added

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

If you are getting error as ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set, then

To fix this error, disable any log_archive_dest_n parameter set on standby( excluding log_archive_dest_1)

On standby:

SQL> alter system set log_Archive_dest_2=”;

System altered.

Step4: Enable configuration [ON PRIMARY]

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

      Warning: ORA-16792: configurable property value is inconsistent with member setting

 

Fast-Start Failover: DISABLED

 

Configuration Status:

WARNING   (status updated 57 seconds ago)

Now lets trouble shoot the ORA-16792 error, we are getting error standby database

Check the properties for inconsistency

 

DGMGRL> show database 'procstan' InconsistentProperties;

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

         PROCSTAN  StandbyFileManagement               MANUAL                                    MANUAL

         PROCSTAN     ArchiveLagTarget                    0                                         0

         PROCSTAN   LogArchiveMaxProcesses                    4                                         4

         PROCSTAN   LogArchiveMinSucceedDest                    1                                         1

         PROCSTAN   DataGuardSyncLatency                    0                                         0

         PROCSTAN      LogArchiveTrace                    0            (missing)                    0

         PROCSTAN     LogArchiveFormat         %t_%s_%r.dbf            (missing)         %t_%s_%r.dbf

 

Here we can see one critical parameter StandbyFileManagement is set to be MANUAL. to fix the error set to auto and try again

SQL> alter system set standby_file_management=AUTO scope=both;

 

System altered.

Enable the configuration again

DGMGRL> enable configuration

Enabled.

 

DGMGRL> show database 'procstan' InconsistentProperties;

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 46 seconds ago)

Dg broker setup is ready and we can do switchover and failover using dgmgrl  easily

 

Note : Info on Databroker configuration it may  be differ in your environment like production,testing,development and etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME