PeopleSoft with Active Data Guard require DB links because they will update the processes' tables using DB links so that remote synonyms are required to give access to the standby system. But a very detailed analysis is required if you consider implementing Active Data Guard. Perform the following steps to implement Active Data Guard with PeopleSoft:
- Create a standby database and enable Active Data Guard.
- Add a new database service for accessing Active Data Guard on the primary database in case there is any maintenance on the standby server.
- Confirm that Oracle Net Services is configured between the Active Guard database and the application servers; also ensure that the Oracle net configuration points to the database service instead of a specific instance and includes both the primary and standby listeners, so that PeopleSoft can connect to any of the services that are not started or running also.
- In PeopleSoft, we do not have much control to use any custom scripts, and we must always go for the derived scripts from PeopleSoft. To enhance this Active Data Guard feature with PeopleSoft, ensure that the following scripts are available:
psadmin.sql($PS_HOME/scripts/unix) createlocalsynonyms.sql($PS_HOME/scripts) createremotesynonyms.sql($PS_HOME/scripts) createdblinktoprimarydb.sql($PS_HOME/scripts)
- Create a secondary access ID as
ACCESS_IDon the primary database using the scriptpsadmin.sql.ACCESS_IDis the RDBMS ID with which PeopleSoft applications are connected to the database so that it will create an ownerACCESSIDbesides the default userSYSADM. - Insert the corresponding database name and username in the table
PSDBOWNERand perform a commit after the insert. - From the application designer, add a new
SYMBOLICIDforACCESSID. - Create a dedicated application user attached to the secondary
SYMBOLICIDattribute. - Create a database link to the primary database using the following query:
SQL> create database link Prim_ADG connect to sysadm identified by password using 'TURKEY_UN';
- Create a local synonym using the derived script
createlocalsynonyms.sql. - Create a remote synonym using the derived script
createremotesynonyms.sql. - Confirm that the standby system is able to synchronize it all the time without any delay and also check for the newly created database link.
- Now configure the batch server on both the primary and standby systems.
- Modify the
psprcs.cfgfile withStandbyDBname,StandbyDBType,StandbyUserId, andStandbyUserPsswd. - After modifying the
PRCSDOMbatch server, reconfigure it using the$PS_HOME/appserv/psadminscript. - Now start the batch server
PRCSDOMafter all the modifications and confirm the standby connections usingv$session. - By setting
DDDAUDITto read-only, you can perform the tests.
Active Data Guard can be implemented on EBS but there are some limitations specific to EBS R12; you should meet software and patches requirements, as discussed in the following table:
|
Oracle products |
Minimum version |
Additional patches |
|---|---|---|
|
Oracle EE 11gR1 |
> = 11.1.0.7 |
Recording ADG violations: <patch 10070167> patch |
|
Oracle EE 11gR2 |
>=11.2.0.2 |
Included in a patch set; no additional patches required |
|
Oracle EBS |
>=12.1.3 |
Infrastructure patch Enabling patch and patch |
If you want to use concurrent manager reporting, you must use parallel concurrent processing with new processing nodes that are set up to handle Active Data Guard reports. Ensure that there is no network latency between the primary and standby systems. In Active Data Guard, the concurrent manager connects to the primary database and only the reports will be connected to the Active Data Guard database. However, no DMLs are allowed on Active Data Guard; DML will be executed via database links to the primary database. Hence, it is applicable to both the user and the dictionary DML.
In brief, first clone an application tier to set up parallel concurrent processing and then register the node for batch processing only. Now start the application and register a new concurrent manager, assigning it the node co-located with Active Data Guard. To ensure that this manager only handles reports for meeting the requirements of Active Data Guard, use the exclude/include rules. Customers may use Active Data Guard instances to execute SQL that does not require a write activity. In terms of the use of E-Business Suite with Active Data Guard, if the concurrent program is not on the list of supported reports, then it is not certified by Oracle Development and is considered a customization. For more configuration limitations over Active Data Guard, refer to the installation documents.
Oracle TopLink is a part of Oracle Fusion Middleware; it's an advanced framework that provides development tools and runtime capabilities so that the development and maintenance efforts are reduced, thereby increasing the application functionality. It can successfully transform object-oriented data into relation data or Extensible Markup Language(XML). TopLink can address the difference between Java objects and data sources. Its engine has a great mechanism to use read pool for all non-transitional transactions and write pool for the actual transactions. The same concept can be implemented with Active Data Guard by processing read-only operations to a standby database and read-write transactions to a primary database; the high-level steps are as follows:
- TopLink can read objects using the read-only database connection and it uses a locking mechanism so that users have the option to choose and update the object later. It can detect a conflict.
- Once an object is processed to higher application layers, it will be converted into the HTML format and some of the attributes will be hidden, and they will be visible once the form is submitted.
- In the next level that the object will be passed to, the application server acts as the TopLink object and along with its changes, it will be saved in the database through the read-write connection pool.
- After the commit of the
UPDATEstatement, the redo data will of course be transported to the physical standby database and it will be applied to the same.
Oracle Business Intelligence Suite EE Plus is an element of Enterprise BI products. From 11g onwards, OBIEE is based on the web service oriented, unified architecture. OBIEE 11g delivers ad hoc queries and analysis, OLAP, and its functionality. It can access multiple enterprise sources including Oracle and also non-Oracle data, and it has advanced enterprise reporting and publishing features.
OBIEE 10.1.3.4 has been certified with Oracle Active Data Guard 11g. The OBIEE server is mostly related to the read-only application server and the read-only operations that we can run on the Active Data Guard standby database with some configuration changes. Hence, we can share the load with the standby database and avoid many read-only operations on the primary database. By enabling Active Data Guard, scalability can be enhanced significantly. To improve query performance, OBIEE has the mechanism to create temporary tables; so we have to disable OBIEE from creating temporary tables and from modifying scripts to use the primary connection pool explicitly for any DML statements. The high-level steps to use OBIEE with Active Data Guard are as follows:
- Create a database connection to the Active standby database
- Disable temporary table creation
- Using the OBIEE server administrator tool, create a write-back connection pool that points to the primary database for any DML transactions
- To monitor the queries and their elapsed time, the OBIEE server has been provided with the Usage Tracking functionality and you can mention the OBIEE server to write in tables using the primary connection to modify
NQSConfig.INIfile of yourSA_HOMEconfigdirectory. - OBIEE has another feature known as Event Polling, which has the mechanism to notify the cache system to invalidate the outdated data cache
Thus, OBIEE with Oracle 11g Active Data Guard provides high-scalable solutions, and with proper configurations, the OBIEE repository can adapt OBIEE for read-only requirements of an Active Data Guard standby database.
Many DBAs seem to have the misconception that Active Data Guard can be configured for SAP systems. They must be aware of the fact that Active Data Guard cannot be used to run any SAP system against the standby database for any reporting purpose. Active Data Guard allows only read-only access to the standby database. But SAP systems are never read-only. Therefore this would not work. Of course, you can run any administrative task against the standby server that is read-only. Since you cannot start a SAP system against an Active Data Guard standby system, you are limited to pure Oracle-related administration tasks.
In the license of Active Data guard, apart from read-only operations we have some more features and a couple of examples that we are going to discuss on how to use Active Data Guard more than just named Read-Only. Most of the tasks that run on the standby and primary database will be used only if DML operations are required. Also, we can offload operations to physical standby databases and hence we can put more additional processing on production databases, thereby we can eliminate the contention between read-only and read-write operations.
In the previous section, we created database links on the primary database and we created remote synonyms to be used by the physical standby database. In this procedure we will be performing all the operations in the standby database only in the case of creating or updating master tables after which it routes transactions to the primary database via database links.
Apart from application usage, we can use Active Data Guard even for fully exporting the database. For a huge OLTP transactional database of size 600 GB to 700 GB, the elapsed ETA to export a full backup is around 5 hours to 6 hours using high parallelism of 32. During the export job, it is always expected to have a high load average that can cause much CPU busy depending on the hardware configurations. Hence, for almost 5-6 hours there will be huge activity both on the database and also at the server level because CPU cores are serving the EXPDP job. If you want to use EXPDP from a standby database, you must schedule the job from the primary database because it has to create a master table, and all the database reads will be performed from the standby database so that disk I/Os can be reduced on the primary database.