Oracle Data Guard Session4 Hariprasath Rajaram Training Objective

Oracle Data Guard Session-4 Hariprasath Rajaram

Training Objective Ø Ø Ø Ø 12 c Dataguard New Features Far Sync Instances Data Guard Active Standby Database Dataguard Broker configure Plugging An Oracle 12 c Pluggable Database Into A Data Guard Container Database SYSDG Privilege Recover Standby Database Using Service Names

Outcome Expected Purely Understanding Real Time Environment By Having Training In Realistic Way Of Dealing Issues. Use Data Guard Standby Databases To Support Production Functions Such As Reporting, Querying, Testing, And Performing Backups Create And Manage Physical And Logical Standby Databases Use Enterprise Manager Grid Control And The Data Guard Command-line Interface (Dgmgrl) To Maintain A Data Guard Configuration Use Data Guard To Achieve A Highly Available Oracle Database

Far Sync Instances

Training Objective

Oracle Data Guard Far Sync Instance An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration. A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database. Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license. A far sync instance consumes very little disk and processing resources, yet provides the ability to failover to a terminal destination with zero data loss, as well as offload the primary database of other types of overhead (for example, redo transport).

Oracle Data Guard Far Sync Instance All redo transport options available to a primary when servicing a typical standby destination are also available to it when servicing a far sync instance. And all redo transport options are available to a far sync instance when servicing terminal destinations (for example, performing redo transport compression, if you have a license for the Oracle Advanced Compression option). Many configurations have a primary database shipping redo to a standby database using asynchronous transport at the risk of some data loss at failover time. Using synchronous redo transport to achieve zero data loss may not be a viable option because of the impact on the commit response times at the primary due to network latency between the two databases. Creating a far sync instance close to the primary has the benefit of minimizing impact on commit response times to an acceptable threshold (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection guarantees -- if the primary were to fail, and assuming the far sync instance was synchronized at the time of the failure, the far sync instance and the terminal standby would coordinate a final redo shipment from the far sync instance to the standby to ship any redo not yet available to the Standby and then perform a zero-data-loss failover.

Relevant configuration parameters » Primary (primary): log_archive_dest_2= service="farsync", SYNC NOAFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=5 db_unique_name="farsync" net_timeout=8, alternate=LOG_ARCHIVE_DEST_3 valid_for=(online_logfile, all_roles) log_archive_dest_3 =service="standby", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=5 db_unique_name="standby“ alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile, all_roles) log_archive_dest_state_2=ENABLE log_archive_dest_state_3=ALTERNATE log_archive_config= dg_config=(primary, farsync, standby) fal_server= standby

Relevant configuration parameters » Primary Far Sync “A” (farsync) log_archive_dest_2=” service="standby", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=5 db_unique_name="standby" net_timeout=8, valid_for=(standby_logfile, all_roles) log_archive_dest_state_2=ENABLE log_archive_config= dg_config=(primary, farsync, standby) fal_server=primary » Standby (standby): log_archive_dest_2= service="primary" ASYNC reopen=5 db_unique_name="primary" valid_for=(online_logfile, all_roles) log_archive_dest_state_2=ENABLE log_archive_config= dg_config=(primary, farsync, standby) fal_server=farsync, primary

Far sync instance 2 side In situation 1 ENT 12 C 1 is the primary database while ENT 12 C 1 S is the standby and FS 12 C 1 is the far sync instance the primary database ships redo synchronously. After a role switch when the ENT 12 C 1 S becomes the new primary then it uses FS 12 C 1 S as the far sync instance to transport redo synchronously. In both situation there exists a direct redo transport path between primary and standby and this will transport redo asynchronously in case of far sync instance failure. Once the far sync is backup again the data guard configuration will revert to using the far sync instance for redo transport. If the standby redo logs were created on the primary then when far sync instances are in use for redo transport standby redo logs will be created automatically for them.

Far sync instance Demo

Oracle 12 c Data Guard Active Standby Database

Oracle 12 c Data Guard Active Standby Database We will be creating the data guard configuration in a 12 c Container Database. Remember – in 12 c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files. Primary database db_unique_name is CONDB 1 Active Standby database db_unique_name is CONDB 1_DR. On Primary SQL> alter database force logging;

Oracle 12 c Data Guard Active Standby Database

Plugging an Oracle 12 c Pluggable database into a Data Guard container database

Training Objective In a previous post we had seen how to setup and create an Oracle 12 c Data Guard Physical Standby Database. Remember Data Guard is set up at the CONTAINER database level and not at the PLUGGABLE database level. In this example we will see how we can simply unplug a database from a non Data Guard container database and plug it into the container database where Data Guard has been configured and automatically the pluggable database will become part of a highly available environment. Before 12 c if we had 10 databases we needed to setup Data Guard for we would need to go through all the Data Guard setup procedures ten times, have 10 separate Data Guard Broker configurations and so on.

Training Objective Here in Oracle 12 c we setup and configure Data Guard just once at the container database level and as and when we need to have a database to become part of this highly available environment we just plug it to the container database and we are good to go. So in our previous example we had a container database where we had set up Data Guard called CONDB 1 and a pluggable database PDB 1. Now we have another pluggable database PDB 2 part of a non Data Guard container database CONDB 2 and we need to move it into the existing Data Guard container database CONDB 1. The first thing we need to do is to unplug the pluggable database PDB 2 from it’s current container and plug it in to CONDB 1.

Plugging an Oracle 12 c Pluggable database into a Data Guard container database

SYSDG Privilege

SYSDG Privilege User granted the SYSDG privilege can execute all data guard Operations in SQL*Plus or using all the Data Guard Broker commands via DGMGRL. The following is a list of commands a user granted the SYSDG privilege can run startup shutdown alter database alter session alter system create and drop restore point (including guaranteed restore points) create session flashback database select any dictionary (dba_ views) select x$ tables v$ and gv$ views appqossys. wlm_classifier_plan delete appqossys. wlm_classifier_plan execute sys. dbms_drs

SYSDG Privilege SYSDG is in place to separate the Data Guard related operations from other activities. Here you can find a complete list of SYSDG privileges you are assigned when logged in with the SYSDG administrative privilege.

SYSDG Privilege Using Administrative Privileges When you install the database software the "Privileged Operating System groups" screen gives you the ability to associate these groups withe relevant privilege.

SYSDG Privilege Identify Users with Administrative Privileges (V$PWFILE_USERS) The V$PWFILE_USERS view allows you to quickly identify users with admin privileges. orapwd file='+DATA/labdb/orapwlabdb' entries=20 sysbackup=y sysdg=y syskm=y dbuniquename=labdb SELECT * FROM v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID --------------- ----- -----SYS TRUE FALSE 0 SYSDG FALSE TRUE FALSE 1 SYSBACKUP FALSE TRUE FALSE 1 SYSKM FALSE FALSE TRUE 1

Recover Standby database using service names

Recover Standby database using service names