student guide

Dataguard:

GLOBAL_DBNAME = <dbuniquename>_DGMGRL.example.com

 Example: listener.ora on Primary Hosts

Static listener entries are needed for Data Guard broker

operations:

[enode01]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = eastdb_DGMGRL.example.com)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

(SID_NAME = eastdb1)))

[enode02]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = eastdb_DGMGRL.example.com)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

(SID_NAME = eastdb2)))

Dynamic service registration allows the LREG process of a database instance to identify its

available services to the listener without entries in the listener.ora configuration file. The

listener then acts as a port mapper for those services.

However, when the database instance is stopped, the listener discards all information for the

dynamic services related to that database. Any attempt to establish a network session to the

unknown service will usually receive the error message “ORA-12514: Listener does not

currently know of service requested in connect descriptor.” Static registration allows the

listener to know of a service, even if the database instance is not running. This is often

important with tools and utilities that try to remotely start and stop a database instance.

To enable DGMGRL to restart instances during the course of broker operations, a static

service must be registered with the local listener and assume a static service name of

db_unique_name_DGMGRL.db_domain.

Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in

Oracle Data Guard broker configurations that are managed by Oracle Restart, RAC On Node

or RAC as the broker will use the clusterware to restart an instance.

Example: listener.ora on Standby Hosts

Static listener entries are needed for Data Guard broker

operations:

[wnode03]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = westdb_DGMGRL.example.com)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

(SID_NAME = westdb1)))

[wnode04]$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = westdb_DGMGRL.example.com)

(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

(SID_NAME = westdb2)))

To enable DGMGRL to restart instances during the course of broker operations, a static

service must be registered with the local listener and assume a static service name of

db_unique_name_DGMGRL.db_domain.

Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in

Oracle Data Guard broker configurations that are managed by Oracle Restart, RAC On Node

or RAC as the broker will use the clusterware to restart an instance.

HOW TO RECREATE DGBROKER CONFIGURATION

Step By Step How to Recreate Dataguard Broker Configuration on Windows and Unix.

Step 1: Stop DG_BROKER on both primary and standby database ( in all instances in case of RAC)

Step 2: Drop old broker configuration files from both primary and standby.

Step 3: Start the DG_BROKER on both primary and standby database

Step 4: create configuration on primary

Step 5: Add standby 

Step 6: Enable configuration

Step 7: Show configuratoin

Step 1: Stop DG_BROKER on both primary and standby database ( in all instances in case of RAC)

ALTER SYSTEM SET DG_BROKER_START=FALSE;

SQL> 

show parameter dg_broker_config_file

NAME                   TYPE   VALUE

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

dg_broker_config_file1 string \<PATH>\<FILE_NAME>.dat

dg_broker_config_file2 string \<PATH>\<FILE_NAME>.dat

Step 2: Drop old broker configuration files from both primary and standby.

ASMCMD

cd <path>

rm <filename.dat>

rm <filename.dat>

SQL>

Step 3: Start the DG_BROKER on both primary and standby database

ALTER SYSTEM SET DG_BROKER_START=TRUE;

Step 4: create configuration on primary

DGMGRL>

connect sys/password

DGMGRL> CREATE CONFIGURATION '<CONFIGURATION_NAME>' AS PRIMARY DATABASE IS '<PRIMARY_DATABASE_NAME>' CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER>;

Configuration "<CONFIGURATION_NAME>" created with primary database "<PRIMARY_DATABASE_NAME>"

Step 5: Add standby 

DGMGRL> 

ADD DATABASE '<STANDBY_DATABASE_NAME>' AS CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER> MAINTAINED AS PHYSICAL;

Database "<STANDBY DATABASE NAME>" added

Step 6: Enable configuration

DGMGRL>

ENABLE CONFIGURATION;

Step 7: Show configuratoin

SHOW CONFIGURATION;



SQL>
ALTER SYSTEM SET DG_BROKER_START=FALSE;SQL>

System altered.

SQL> show parameter dg_broker_config_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr1DB193H1.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr2DB193H1.dat
SQL> !rm /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr1DB193H1.dat /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr2DB193H1.dat

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;

System altered.


DGMGRL> CREATE CONFIGURATION 'db_broker_config' AS PRIMARY DATABASE IS 'db193h1' CONNECT IDENTIFIER IS db193h1;
Configuration "db_broker_config" created with primary database "db193h1"
DGMGRL> ADD DATABASE 'db193h1_stdby' AS CONNECT IDENTIFIER IS db193h1_stdby MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL> ADD DATABASE 'db193h1_stdby' AS CONNECT IDENTIFIER IS db193h1_stdby MAINTAINED AS PHYSICAL;
Database "db193h1_stdby" added
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> ENABLE CONFIGURATION;
Enabled.

Note : For 12c DB Redo log shipping should Not be configured at the Primary and Standby while configuring Broker so you need to set log_archive_dest_n to Null at Primary and Standby.

Else will get the below error while Adding the DB in Broker

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added"

DGMGRL> CREATE CONFIGURATION 'db_broker_config' AS PRIMARY DATABASE IS 'db193h1' CONNECT IDENTIFIER IS db193h1;
Configuration "db_broker_config" created with primary database "db193h1"
DGMGRL> ADD DATABASE 'db193h1_stdby' AS CONNECT IDENTIFIER IS db193h1_stdby MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

RMAN BACKUP ON PHYSICAL STANDBY DATABASE

RMAN BACKUP ON PHYSICAL STANDBY DATABASE

Recovery catalog is required for taking RMAN BACKUP of Physical standby database. This is not optional.

Primary and standby database must use the same recovery catalog.

Its not necessary to register the standby database.

Active Standby Database / Active Dataguard:

Active Standby Database / Active Dataguard:

open read-only and apply the logs at the same time.

recover managed standby database using current logfile disconnect;

SQL> select open_mode from V$database ; OPEN_MODE ————————– READ ONLY WITH APPLY

REAL TIME APPLY:

1. For a realtime apply we need standby redo logs.

2. Log writer is writing to the online redo logs.

3. Log writer network service (LNS) (can have multiple LNSn), will write to the remote file system process.

Snapshot Standby Database

A snapshot standby database is read-write standby database that is created by converting a physical standby database into a snapshot standby database.

A snapshot Standby is open in the read-write mode and can execute DML transactions independently of the primary database.And also it maintains protection by continuing to receive redo data from the production database, archiving it for later use.

Redo data received from the primary database will apply automatically once it is converted back to a physical standby database.

Steps to Convert Physical Standby to Snapshot Standby:

Step 1: Configure flash recovery area as given below

i) Set the size for recovery area.

Alter system set db_recovery_file_dest_size=<size>

ii) Set Flash recovery area.

Alter system set db_recovery_file_dest=<path>

Step 2: Mount the Physical standby database

Step 3: Stop managed recovery if it is active.

Step 4: Convert physical standby database to snapshot standby database.

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Step 5: The database is dismounted during conversion and must be restarted.

Step 6: Once the database is restarted any transaction can be executed .

5. SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

READ WRITE SNAPSHOT STANDBY

When we issue ALTER DATABASE CONVERT TO SNAPSHOT STANDBY an implicit guaranteed restore point is created and this GRP is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

Steps to Convert Snapshot Standby to Physical Standby:

Step 1: Shutdown the snapshot standby database.

Step 2: Mount the snapshot database.

Step 3: Convert back to physical standby database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Step 4: Shutdown the database and mount it .

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PHYSICAL STANDBY

Step 5: Start the media recovery process.

Once a snapshot standby database converted back to physical standby database and restarted, start Redo Apply and all redo received by the snapshot standby database will be applied to the physical standby database.

FOR DATAGUAR BROKER - DGMGRL

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;