If Data Guard is managed using a broker, it is always simple and even easier to manage it from Oracle 11gR2. When both broker and MRP are running on the standby, you can open the database at any time for reporting purposes.
- Check the configuration and state of the database as follows:
DGMGRL> show configuration; Configuration - PACKT Protection Mode: MaxPerformance Databases: turkey_un - Primary database INDIA_UN - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> SQL> select db_unique_name,open_mode from v$database; DB_UNIQUE_NAME OPEN_MODE --------------- -------------------- INDIA_UN MOUNTED SQL> SQL> !ps -ef|grep mrp oracle 4686 1 0 16:31 ? 00:00:00 ora_mrp0_INDIA oracle 4815 3948 0 16:35 pts/1 00:00:00 /bin/bash -c ps -ef|grep mrp Open Database for the use of Active Data Guard SQL> alter database open read only; Database altered. SQL> DGMGRL> show configuration; Configuration - PACKT Protection Mode: MaxPerformance Databases: turkey_un - Primary database INDIA_UN - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL>
From the previous command, it looks as if the database is opened successfully; internally it will perform the following three operations:
- Completed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL - Completed:
ALTER DATABASE OPEN READ-ONLY - Completed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Tip
If you are using Oracle 11gR1 with Data Guard broker, then use a combination of Data Guard broker and SQL *Plus to enable Active Data Guard using the following commands:
DGMGRL> edit database 'INDIA_UN' SET STATE='APPLY-OFF';
SQL> alter database open read only;
DGMGRL> edit database 'INDIA_UN' SET STATE='APPLY-ON';
We've just revised how to enable Active Data Guard using SQL *Plus and also using Data Guard broker.
Tip
After performing the previous steps to enable Active Data Guard, we will see how to find out whether the Active Data Guard feature is enabled or not. There is no direct column in any view/tables to find out whether the Active Data Guard feature is enabled or not. But if we merge two columns of two tables and if both the processes are running, then it is for reporting purpose as shown in the following query:
SQL> select 'YES' Active_DataGuard from v$managed_standby ms, v$database db where ms.process like '%MRP%' and db.open_mode like '%READ ONLY%';
ACTIVE_DATAGUARD
--------------------
YES
We have successfully enabled Active Data Guard on a standby database; no further steps need to be performed on the primary database if real-time apply is running. There are several ways to find whether Active Data Guard is enabled or not.
To determine if Active Data Guard is enabled from the primary, v$archive_dest_status describes the status of all local and remote destinations, including several options such as database role and recovery mode, as follows:
SQL> select dest_name,status,database_mode,recovery_mode from v$archive_dest_status where dest_id=2; DEST_NAME STATUS DATABASE_MODE RECOVERY_MODE ---------------------- --------- -------------- -------------------- LOG_ARCHIVE_DEST_2 VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY
Using the standby database you can check whether the standby database is Mount status Read Only, or READ ONLY WITH APPLY by using v$database as follows:
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY
By using a custom query you can merge the views as follows:
SQL> select 'YES' Active_DataGuard from v$managed_standby ms, v$database db where ms.process like '%MRP%' and db.open_mode like '%READ ONLY%'; ACTIVE_DATAGUARD -------------------- YES SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0; NAME TIME UNIT COUNT LAST_TIME_UPDATED ---------- ---------- ---------------- ---------- -------------------- apply lag 0 seconds 5787 11/06/2012 23:01:28 apply lag 1 seconds 98 11/06/2012 23:01:09 apply lag 2 seconds 8 11/06/2012 22:45:06 apply lag 3 seconds 6 11/06/2012 22:45:37 apply lag 4 seconds 4 11/06/2012 22:43:03 apply lag 5 seconds 4 11/06/2012 22:45:43
Active Data Guard has limitations with several applications; your application may or may not be compatible with Active Data Guard to use its features. Before purchasing the license, you should check the compatibility of the applications. Here we will discuss briefly some of the top applications that are used for business and how it works with Active Data Guard.
From PeopleSoft Version 8.51, reports can be executed in the Active Data Guard database instead of running in the production/primary OLTP database. Specific to PeopleSoft applications, it uses reporting tools such as NVisions. These job queries select against the database and retrieve the results into an Excel spreadsheet as per the user's formatting. Internally it calls a number of batch jobs and some of the queries may use hints such as parallel if they create any SQL profiles and these can cause much load on production. All NVision reports are pure select queries. Only the tree performance tuning parameters/settings that are enabled will have DML statements on Treeselector tables otherwise any NVisions report on PS_LEDGER, PS_LEDGER_BUDG is always pure select process. NVisions are reports that can run on FIN, HR, or EPM(DWH). If you enable Active Data Guard on a standby database, components such as the PSQUERY viewer, the TREE viewer, QAS, G&R, and the XMLP viewer should always run on the standby system. To enhance this feature you must perform configuration changes in both the process scheduler server and the application server. The following diagram explains Active Data Guard with PeopleSoft:
If you perform changes in the component's properties of the application designer, you can run more components on the standby system; even the process scheduler definitions can be configured on the standby system by setting the read-only option in it. With the option of Active Data Guard, database links and remote synonyms that are defined by scripts from PeopleTools can run batch programs on a standby system.