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

ORACLE DATAGUARD

Oracle Dataguard - DR Disaster Server:

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

           Oracle Data guard ensures high availabilty data protection,and disaster recovery for enterprise data,datagurad provides a comprehensive set of services that create,maintain,manage and monitor one or more stand by databases to enable production oracle databases to survive disaster and data corruptions,datgurad physical standbysetup using the dataguard broker in oracle database 12c release 1 Dataguard is the name for oracle standby database solution,used for disaster recovery and high  availability

    LNS process of primary database  captures redo from redolog buffer  send it to RFS .RFS process of stand by database through ORACLE NET ,RFS process then writes that redo information to standby redolog files,MRP applies information from the archived redologs to the standby database,when performing managed receovery operations,log apply services automatically apply archived redologs to mainatained transactional synchronization with primary database  

  

Primary system :

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

configure standby server up to 30 for a single primary

standby replica of primary

       DDL / DML changes in primary will replicate to standby

Features - standby :

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

called as dataguard from 9i

prior to 9i , called as standby system

media failure/disk failure/power/disaster - 

purpose is to protect primary database

Two types:

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

Physical Standby - using redo apply with archives are shipped

Logical Standby - using sql apply

11g introduced - snapshot standby

         Standalone system

        Primary Database

        For Disaster Purpose

        any media failure

       For that we need a standby setup for Primary

       The standby environment is same has Primary.

    Using Primary Archives - shipped to Standby Server.

     In Standby - RFS will receive and MRP will apply the shipped archives.

RFS - Remote file server process

MRP - Managed recovery Process.

In Primary , LNS wil send the changes made to standby.

        LNS ---- >Log network service

From 9i

Primary -> LNS -> Ship -> standby ->RFS receive -> MRP apply

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

Prior to Oracle 9i - we called as standby

From 9i - its Dataguard

In 8i , a DBA intervention is required to manage archive shipping from primary to standby by using a crontab script

 with a SCP Command.

From 9i , the LNS service will ship the archives.The gap automatically resolved.

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

Dataguard license is only availiable with

        Enterprise Edition/SE2

    But not with standard edition

Standby Database Types :

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

    Physical Standby - using redo apply

    Logical Standby - using sql apply

Physical Standby ---> Using archives (a copy of block) is going

to apply in standby mount stage - Media recovery mode

Logical Standby -- >using sql statements

    degrades the performance of primary.

    Not recommended.

A logical standby database works in a different manner which keeps in sync with the primary by transforming redo data received from the primary database into logical SQL statements and then executes those SQL statements against the standby database.

With a logical standby database, the standby remains open for user access in read/write mode while still receiving and applying logical records from the primary.

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

Dataguard Setup - Physical Standby

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

Primary Configuration - Min down time is needed to configure.

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

1. Enable Archivelog

sql>archive log list

2. Create Password file - OS

    cd $ORACLE_HOME/dbs

    ls -ltr

    $ mv orapwprod orapwprod_old

An encrypted sys password stored in OS  Password file.

    $orapwd file=orapwprod password=sys123

For standby DB - Copy paswd file (must be same)

    $cp orapwprod  orapwstan

3. Enable Forced Logging

In case, developer enable table nologging for faster inserts/updates of bulk changes. with this no redo will generate on that table. For that we are missing changes in archives. So will enable globally force logging, so those tables are logged forcebly.

    sql>ALTER DATABASE FORCE LOGGING;

    SQL> select force_logging from v$database;

Parameter file configuration - Primary

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

Lets add few parameters

sql>create pfile from spfile;

sql>shutdown immediate

sql>exit

cd $ORACLE_HOME/dbs

vi initprod.ora

#append the parameters - following

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stan)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/archives/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'

*.LOG_ARCHIVE_DEST_2='SERVICE=stan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stan'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.db_unique_name='prod'

#remove existing log_archive_dest_1

Save it

start the DB with pfile

sqlplus '/as sysdba'

sql>startup pfile='$ORACLE_HOME/dbs/initprod.ora'

sql>create spfile from pfile;

sql>shutdown immediate

sql>startup

Q)Why nologging option is needed in DG config for primary ?

A) nologging option on those tables - they are inserting/updating/delete

using nologging , redo will not generate for that table

if no redo , missing changes in archives...

missing  - will not sync with standby from primary

We enable - globally 

force logging

will log forcebly the changes to archives...

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

 Standby Configuration:

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

In stanby Server , we dont have database.

Using RMAN, Will duplicate/Cloning the database from primary to standby.

Preparation

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

1. password file - orapwstan (copy from orapwprod)

    must be same as primary.

2. Create pfile from primary

Node1

------

$export ORACLE_SID=prod

$sql>create pfile from spfile;

$cd $ORACLE_HOME/dbs

$cp initprod.ora initstan.ora

$export ORACLE_SID=stan

$echo $ORACLE_SID

Edit the pfile for stan

$vi initstan.ora

 #do following changes.

 #also change the path for audit,controlfiles

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

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

#Verify following parameters as follows.

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

*.db_name='prod' (Must be same on both nodes)

*.db_unique_name='stan' (not similar)

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

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(stan,prod)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/archives/stan VALID_FOR=

(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stan'

*.LOG_ARCHIVE_DEST_2='SERVICE=prod LGWR ASYNC VALID_FOR=

(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

#Append additional parameters for stanby

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

*.db_file_name_convert='/oradata/prod/','/oradata/stan/'

*.log_file_name_convert='/oradata/prod/','/oradata/stan/'

*.fal_server='prod'

*.fal_client='stan'

*.standby_file_management='auto'

*.instance_name='stan'

*.standby_archive_dest='/archives/stan/'

#remove memory_target if on same node (use comment#)

Save the file...

Create following directories for Stan.

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

mkdir -p /u01/app/oracle/admin/stan/adump

mkdir -p /u01/app/oracle/oradata/stan

mkdir -p /u01/app/oracle/fast_recovery_area/stan

mkdir -p /oradata/stan/

mkdir -p /archives/stan

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

Network Configuration

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

    register the stan in Listener and tnsnames.

$netmgr

    Click Listener -  > - Database services

            Add Database -> stan

    Click Service name - > Edit - Create - Service name

                stan...

save

$lsnrctl start LISTENER

$tnsping prod

$tnsping stan

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

Make sure - primary is up and running . ps -ef | grep pmon

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

OPen New Terminal - Another - for stan

$export ORACLE_SID=stan

$echo $ORACLE_SID

sqlplus '/as sysdba'

sql>startup nomount

sql>exit

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

Connect using rman to primary and auxiliary standby instance.

$rman target sys/sys123@prod auxiliary sys/sys123@stan

Use duplicate command to create standby DB.

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

This can be used for Cloning a DB with SID (instead of standby)

rman>

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  NOFILENAMECHECK;

rman>exit

$export ORACLE_SID=stan

$sqlplus '/as sysdba'

sql>select open_mode,name,database_role from v$database;

    mounted physical_standby

Keep in managed recovery  mode - so RFS and MRP will start.

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

Verify - v$managed_standby - Monitor

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

sql>select * from v$archive_gap;

or

v$archive_log,v$log_history

Finding errors in log for standyby:

SQL> select message from v$dataguard_status;

Shutdown - Standby

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

$export ORACLE_SID=stan

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

sql>shutdown immediate

For startup - standby

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

$export ORACLE_SID=stan

sql>startup mount

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

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

Process started,receiving and applying using v$managed_standby.

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

Protection Modes

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

Three protection Modes

Max Performance Mode - default

Max Availability Mode

Max Protection Mode

sql>select database_role,protection_mode from v$database;

Maximum Protection -

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

    Zero Dataloss ,Two sided Protected , Sync,AFFIRM,DB_UNIQUE_NAME

Transactions do not commit until written in atleast one standby server. Wait 

for acknowledgement from standby

If standby is down, primary will also down.

Maximum Availability-

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

    Zero Dataloss,Single sided Protected , Sync,AFFIRM,DB_UNIQUE_NAME

Transactions do not commit until written in atleast one standby server.Wait for 

acknowledgement from standby.

If standby is down, primary will change the mode to performance mode.

Maximum Performance(default) -

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

    least Dataloss,Async,NOAFFIRM,DB_UNIQUE_NAME

Transaction will commit then transfer to standby to avoid performance issue. will not Wait for acknowledgement from standby.

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

Execute the following SQL statement on the primary database:

On Prod - Node1

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

eg:

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

SQL> ALTER DATABASE OPEN;

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

Two New Features in Standby - 11g

    Active Dataguard - read only - reporting db

    Snapshot dataguard - read/write - for test cases 

Active Dataguard

=================

    In Mount, we cannot read/write the data.

    To run the reports, can use Active dataguard.

    Converting physical standby to Active DG. 

    ADG will be in read only mode.

    The archives logs will not apply in read only mode.

 

To switch the standby database into read-only mode, do the following.

On Node2 - stan

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

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

SQL>ALTER DATABASE OPEN READ ONLY;

SQL> select open_mode,database_role,protection_mode from v$database;

OPEN_MODE

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

READ ONLY

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Snapshot dataguard - read/write

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

    For test cases, we can use physical standby converting to snapshot dataguard.

    The changes can be made and rollbacked after keeping in managed recovery mode.

    Converting - ? Mount to read write.-> mount

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

sql>ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> select DATABASE_ROLE,open_mode,protection_mode from v$database;

sql>alter database open;

sql>select open_mode from v$database;

SQL> select DATABASE_ROLE,open_mode,protection_mode from v$database;

To convert in to physical standby from snapshot

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

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

alter database convert to physical standby;

shutdown immediate

startup mount

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

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

startup/shutdown steps - stan - cancel-shut

    for start- mount-last disconnect

v$managed_standby - process started-MRP/RFS

v$archive_log - finding the gap - applied

v$archive_gap - finding the gap

Protection modes - max-avai,per,pro

Snapshot - read write - will undo after - con-phy

ADG - readonly

rman - auxiliary-only instance

duplicate db from active -

If too many archives found gap ? how to fix ?

    RMAN incremental backup from prod - catalogue and apply in standby.

In case no archives - but still too much gap?

    rebuild the standby

Brief  - Prod and Stan - parameters

Primary is Live production server - changes happen

Physical standby - is Standby system - where changes from primary to standby will receive by archives and applied in mount stage

Active Dataguard - is a standby system converted to read only to read/run reports

Snapshot dataguard - is a standby , converted to read/write for test cases

   once converted to physical standby,the changes are rollbacked.

Primary - read/write

Physical - Mounted

Active Dataguard - read-only

Snapshot DG - read/write

NOTE: info on dataguard it may differ on your environment like production,testing,development

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES VIST MY BLOG

https://chaitanyaoracledba.blogspot.com/