Sunday, April 27, 2014

Oracle Apps R12 - Offloading reporting workload with Active Data Guard


Steven Chan's blog references for understanding "Active Data Guard for EBS" :-

https://blogs.oracle.com/stevenChan/entry/adg_ebs12
https://blogs.oracle.com/stevenChan/entry/comparing_oracle_data_guard_vs_active_data_guard_f

Prepare Primary Database for Standby Database Creation

Enable Forced Logging
Place the primary database in FORCE LOGGING mode.
alter database force logging;

Enable Archiving
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Configure Redo Transport Authentication

Copy the password file to the OH to be used by the standby database, once copied, rename the password file accordingly to ‘bind’ it to the standby database’s instances.
N.B. Whenever you grant or revoke the SYSDBA or SYSOPER privileges or change the login password of a user who has these privileges, you must replace the password file at each physical or snapshot standby database in the configuration with a fresh copy of the password file from the primary database.


Initialization Parameters for Primary Database
The list of initialization parameters to be added/changed has been defined based on the following considerations:
-          There won’t be a failover/switchover scenario i.e. the primary database won’t be transitioned to the standby role (thus, won’t need to receive redo data and won’t need relevant initialization parameters).

An example for AJITHRAC, values need to be changed for a different database!
db_name=AJITHRAC          # should be the same on both the primary and the standby
db_unique_name=AJITHRAC   # should be the different on the primary and the standby

log_archive_config='dg_config=(AJITHRAC,AJITHRAC_S)'

log_archive_dest_2='service=ajithrac_s async NOAFFIRM valid_for=(online_logfiles,primary_role) COMPRESSION=ENABLE db_unique_name=ajithrac_s'
log_archive_dest_state_2=enable
log_archive_format=%t_%s_%r.arc
log_archive_max_processes=8

#fal_server=ajithrac_s
#db_file_name_convert=ajithrac_s,ajithrac
#standby_file_management=auto

TNS Descriptors
Add the tns descriptor pointing to the standby database with the same name as defined in the log_archive_dest_2 parameter.

Prepare Standby Instance
Initialization Parameters for Standby Database
Take a pfile dump of the primary database’s spfile, add/modify the following parameters
db_unique_name=AJITHRAC_S
log_archive_config='DG_CONFIG=(AJITHRAC,AJITHRAC_S)'

log_archive_dest_1='location="+DATA_1", valid_for=(ALL_LOGFILES,ALL_ROLES)'
log_archive_dest_state_1=enable

log_archive_format=%t_%s_%r.arc

fal_server=ajithrac
standby_file_management=auto

# alter system set standby_archive_dest='+DATA_1' scope=memory; # deprecated

File Structure
If the standby database on the same file system, set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to place its files to a location different from the primary database one and, thus, to avoid overwriting the primary database’s files.

SPFILE
Create the spfile based on the prepared pfile.
Add database to OCR
srvctl add database -d AJITHRAC_S -o /u01/app/oracle/product/11.2.0/db_1 -r physical_standby

srvctl add instance -d AJITHRAC_S -i AJITHRAC_S2 -n ajithpathiyil1

TNS Descriptors
Add the tns descriptor pointing to the primary database with the same name as defined in the fal_server parameter.
  
Create Standby Database with RMAN
Startup NOMOUNT
Duplicate Primary for Standby
RMAN needs to be connected to the standby database remotely as SYSDBA (even it’s on the same node) e.g.
rman nocatalog target sys/@ajithrac1  auxiliary sys/@ajithrac_s

run {
  ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch2 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch3 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch4 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch5 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch6 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch7 DEVICE TYPE disk;
  ALLOCATE CHANNEL ch8 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch9 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch10 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch11 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch12 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch13 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch14 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch15 DEVICE TYPE disk;
  ALLOCATE AUXILIARY CHANNEL ch16 DEVICE TYPE disk;
  duplicate target database
    for standby
    from active database
    dorecover;
}

Create Standby Redo Logs
The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log. A standby redo log is used to store redo received from another Oracle database. Standby redo logs are structurally identical to redo logs, and are created and managed using the same SQL statements used to create and manage redo logs.

Redo received from another Oracle database via redo transport is written to the current standby redo log group by an RFS foreground process. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an ARCn foreground process.

The process of sequentially filling and then archiving redo log file groups at a redo source database is mirrored at each redo transport destination by the sequential filling and archiving of standby redo log groups.

Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database. For administrative ease, Oracle recommends that all redo log files in the redo log at the redo source database and the standby redo log at a redo transport destination be of the same size.
The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the V$LOG view to determine how many redo log groups are in the redo log at the redo source database and query the V$THREAD view to determine how many redo threads exist at the redo source database.
To create right count of standby redo logs use following script on source database and run created script on standby database

Set serveroutput on
DECLARE
   NUM_THREAD   BINARY_INTEGER := 0;
   NUM_GROUP    BINARY_INTEGER := 0;
   LOG_SIZE     NUMBER;
BEGIN
   SELECT COUNT (DISTINCT thread#) INTO NUM_THREAD FROM v$log;

   SELECT COUNT (DISTINCT group#) + 1
     INTO NUM_GROUP
     FROM v$log
    WHERE thread# = 1;

   -- Assuming the logsize if same for all threads. If not, fix it
   DBMS_OUTPUT.put_line (NUM_THREAD);
   DBMS_OUTPUT.put_line (num_group);

   SELECT DISTINCT bytes INTO LOG_SIZE FROM v$log;

   FOR I IN 1 .. NUM_THREAD
   LOOP
      FOR j IN 1 .. NUM_GROUP
      LOOP
         DBMS_OUTPUT.PUT_LINE ('ALTER DATABASE ADD STANDBY LOGFILE (''+DATA_1'',''+DATA_1'') size ' || LOG_SIZE || ';');
      END LOOP;
   END LOOP;
END;
To check standby logs:
select * from v$standby_log;

Start Recovery
Archive log applying:
alter database recover managed standby database disconnect from session;
Real-time redo applying:
alter database recover managed standby database using current logfile disconnect from session;
  
Configure Standby for RMAN

Register Standby Database in Catalog
In a Data Guard environment, Oracle recommends that you always use RMAN with a recovery catalog. You can use the CONFIGURE command to create persistent RMAN configurations for any individual primary or standby database in the Data Guard environment, with the exception of settings for backup retention policy, tablespace exclusion, and auxiliary names. Thus, the primary and standby databases can have different channel configurations, control file autobackup locations, and so on.

You can use the FOR DB_UNIQUE_NAME clause to configure a database to which RMAN is not connected as TARGET. You can use CONFIGURE DB_UNIQUE_NAME to make a new physical standby database known to the recovery catalog and implicitly register it.

Archive Logs
Specify when archived logs can be deleted.

On the Primary Instance
At least, ensure all archived logs are delivered to all standby destinations.
configure archivelog deletion policy to shipped to all standby;

or even applied

configure archivelog deletion policy to applied on all standby;

On the standby instance
At least, ensure all archived logs are applied on all standby destinations.
configure archivelog deletion policy to applied on all standby;

A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:

alter database recover managed standby database cancel;
alter database open;

alter database recover managed standby database disconnect from session;
# alter database recover managed standby database using current logfile disconnect;
  

Using Active Data Guard Reporting with Oracle E-Business Suite
A physical standby database instance cannot be opened if Redo Apply is active on a mounted

Prerequisites
Oracle E-Business Suite version should be 12.1.3 or higher.
Additional patches should be installed already :
9434627 INFRASTRUCTURE DELIVERY FOR ACTIVE DATA GUARD SUPPORT
9505793  ACTIVE DATA GUARD SUPPORT: CONCURRENT MANAGER
9526837 ACTIVE DATA GUARD SUPPORT: AFMOGBLB.PLS CHANGES FOR REPORTWRITER SUPPORT
In order to use Concurrent Manager Reporting you must be using Parallel Concurrent Processing with new processing nodes set up to handle ADG reports.

Concurrent Manager Reporting
Concurrent Manager support for ADG reporting is currently restricted as follows:
·         Only Oracle Reports programs are supported.
·         For a report to be run on ADG it must have been previously run on the primary.
·         Only Oracle Reports programs that have no direct or indirect DML are supported.
·         FND Debug is not supported.
The process for running ADG reporting is :
Identify reports that can be run on ADG.
Redirect reports to ADG.

Redirecting Reports to Active Data Guard
This can be done in one of two ways:
·         By changing the Session Properties within the Concurrent Program Definition form and specifying an ADG instance.
·         By using automatic redirection. With this option, request submission will automatically push the request to the first available standby [concurrent manager].

Configure Parallel Concurrent Processing and set up an ADG Manager
Follow the steps in Document 406982.1 for cloning an applications tier to set up Parallel Concurrent Processing, registering the node for batch processing only.   

To add one more OEBS node.
adpreclone.pl appsTier
Adding Other Nodes
As per Note 384248.1 ' Sharing The Application Tier File System in Oracle E-Business Suite Release 12'
 
Log in to the node that you want to add, copy the context file from the primary node to the new one. Generate the context file for the new node
mkdir /u01/app/ebs/ajithrac.local
cd /u01/app/ebs/ajithrac/apps_st/comn/clone/bin
perl adclonectx.pl addnode contextfile=
  
An example of adclonectx.pl example for ajithpathiyil02
Target System Hostname (virtual or normal) [ajithpathiyil2] :

It is recommended that your inputs are validated by the program.
However you might choose not to validate them under following circumstances:

        -If cloning a context on source system for a remote system.
        -If cloning a context on a machine where the ports are taken and
         you do not want to shutdown the services at this point.
        -If cloning a context but the database it needs to connect is not available.

Do you want the inputs to be validated (y/n) [n] ? : y

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] :

Target System Other Services [disabled] :

Do you want to preserve the Display [ajithpathiyil1:0.0] (y/n)  : n

Target System Display [ajithpathiyil2:0.0] : localhost:10.0

RC-00217: Warning: Configuration home directory (s_config_home) evaluates to /u01/app/ebs/ajithrac.local/inst/apps/AJITHRAC_ajithpathiyil2. A directory with this name already exists and is not empty.

Do you want to continue (y/n)  : y

Database port is 1521

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : y
Complete port information available at /u01/app/ebs/ajithrac/apps_st/comn/clone/bin/out/AJITHRAC_ajithpathiyil2/portpool.lst

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /u01/appstmp
Choose a value which will be set as APPLPTMP value on the target node [1] : 2

Backing up /u01/app/ebs/ajithrac.local/inst/apps/AJITHRAC_ajithpathiyil2/appl/admin/AJITHRAC_ajithpathiyil2.xml to /u01/app/ebs/ajithrac.local/inst/apps/AJITHRAC_ajithpathiyil2/appl/admin/AJITHRAC_ajithpathiyil2.xml.bak

Do you want to reset custom variable settings(y/n) [n] : n

Creating the new APPL_TOP Context file from :
  /u01/app/ebs/ajithrac/apps_st/appl/ad/12.0.0/admin/template/custom/adxmlctx.tmp

The new APPL_TOP context file has been created :
  /u01/app/ebs/ajithrac.local/inst/apps/AJITHRAC_ajithpathiyil2/appl/admin/AJITHRAC_ajithpathiyil2.xml

Log file located at /u01/app/ebs/ajithrac/apps_st/comn/clone/bin/CloneContext_1026091336.log
contextfile=/u01/app/ebs/ajithrac.local/inst/apps/AJITHRAC_ajithpathiyil02/appl/admin/AJITHRAC_ajithpathiyil2.xml
Check Clone Context logfile /u01/app/ebs/ajithrac/apps_st/comn/clone/bin/CloneContext_1026091336.log for details.

Correct the context file as required. Run the AutoConfig utility with the context file for the new node. AutoConfig will generate the new Instance Home for this node and update the required tables in the database.
/u01/app/ebs/ajithrac/apps_st/appl/ad/12.0.0/bin/adconfig.sh contextfile=
 
Adding Other Nodes - Share Files
There are few files that are maintained manually and not shared by default; they need to be shared with the node being added
SSL wallets
Take backup of existing ones
mkdir -p $INST_TOP/certs/Apache/BAK
mkdir -p $INST_TOP/certs/opmn/BAK

mv $INST_TOP/certs/Apache/cwallet.sso $INST_TOP/certs/Apache/BAK
mv $INST_TOP/certs/Apache/ewallet.p12 $INST_TOP/certs/Apache/BAK

mv $INST_TOP/certs/opmn/cwallet.sso $INST_TOP/certs/opmn/BAK
mv $INST_TOP/certs/opmn/ewallet.p12 $INST_TOP/certs/opmn/BAK
Make links to the wallet files from all locations where it needs to accessed by R12
SSLDIR=/u01/app/ebs/ajithrac/ssl
ln -s $SSLDIR/cwallet.sso $INST_TOP/certs/Apache
ln -s $SSLDIR/ewallet.p12 $INST_TOP/certs/Apache

ln -s $SSLDIR/cwallet.sso $INST_TOP/certs/opmn
ln -s $SSLDIR/ewallet.p12 $INST_TOP/certs/opmn
AutoConfig - All Nodes
Run the AutoConfig utility on all nodes now so that the required configuration files on those are updated with the new topology information.
$ADMIN_SCRIPTS_HOME/adautocfg.sh
   
Close the current ssh session, open a new session as applmgr and reload the environment settings.
A quick and dirty fix
update applsys.fnd_concurrent_queues
   set node_name  = 'ajithpathiyil2'
 where node_name is not null;

update applsys.fnd_concurrent_queues
   set node_name2  = 'ajithpathiyil2'
 where node_name2 is not null;
Start up the application and register a new concurrent manager, assigning it the node co-located with ADG. To ensure that this manager only handles reports destined for ADG, use exclude/include rules. Look at “Configuring Concurrent Managers” section below in this document.

Prepare System for reporting
As the APPS user carry out the following steps.
 
    Disable system - if this is the first time, skip this step.
execute fnd_adg_utility.disable_adg_support;
execute fnd_adg_utility.disable_database_triggers;
execute fnd_adg_utility.switch_rpc_system_off;
execute fnd_adg_utility.clone_clean;
execute fnd_adg_utility.prepare_for_rpc_system;
commit;

Ignore any “ORA-20001: ADG-ERR 18: RPC system needs to be enabled for this operation”   errors.
Create a database link that will be used to connect to the primary. Although it is possible to create the database link dynamically, the standard APPS account does not have the "CREATE PUBLIC DATABASE LINK" privilege. The link must be public and the connect string a TNS alias. It will be validated before it can be used.

sqlplus  '/as sysdba'
create public database link adg_to_ajithrac using 'ajithrac';

Register a database link that will be used to connect to the primary.

begin
  fnd_adg_utility.register_connection(
    p_type => fnd_adg_utility.C_CONNECT_STANDBY_TO_PRIMARY,
    p_link_name => 'adg_to_ajithrac.lab.com',
    p_link_connstr => 'ajithrac');
end;
 
Run following:
execute fnd_adg_utility.switch_rpc_system_on;
Note. This will fail unless the database link registered is valid - i.e. the link must resolve to a READ-WRITE database with the same DBID and the same instance.
Recompile invalid objects

execute fnd_adg_utility.compile_rpc_dependents;  #or use standard RDBMS scripts.
Enable database triggers to log some information.
execute fnd_adg_utility.enable_database_triggers;
ADG support delivers three schema level database triggers

The three triggers are for logon,logoff and servererror:

Logon/Logoff Triggers
These triggers are to support simulation testing. The logon trigger will enable READ ONLY violation trace; the logoff trigger will record the number of violations.
If these triggers are not enabled, the following limitations apply:
·         Trace errors and V$ data are not recorded and simulations will be treated as having no errors. Without these triggers, enabling the simulation option is of limited benefit.
Error Trigger
This trigger is only executed if ORA-16000 is raised - i.e. the trigger does nothing on the primary. If has been enabled then the error count for the concurrent program is incremented.
If the error trigger is not enabled, the following limitations apply:
·         Report failures will not be recorded.
·         Failiures will not lead to "Run on Standby"  being disabled.
Register connections from Primary to Standby
These are the connections used for running the reports. Up to five connections [ ADG databases ] can be specified.
sqlplus  '/as sysdba'
create public database link ajithrac_to_adg using 'ajithrac_s';
 
Register connections from Primary to Standby
begin
fnd_adg_utility.register_connection (
p_type => fnd_adg_utility.C_CONNECT_PRIMARY_TO_STANDBY,
p_link_name => 'ajithrac_to_adg.lab.com',
p_link_connstr => 'ajithrac_s',
p_standby_number => 1);
end;
/
Before a connection can be used it must be validated.
exec fnd_adg_utility.validate_connection(fnd_adg_utility.C_CONNECT_PRIMARY_TO_STANDBY,1);
Success if no errors returned.
There must be at least one C_CONNECT_PRIMARY_TO_STANDBY connection in order for reports to use ADG.
TNS Aliases
There is currently no autoconfig support for ADG aliases so theses aliases need to be set up manually both on the database and middle tier.

So add TNS records (in this example ajithrac_s and ajithrac) to database tnsnames.ora and on R12 to /u01/app/ebs/ajithrac/admin/network/ajithrac_ifile.ora

Configuring Concurrent Managers
Once you have set up a dedicated concurrent processing node for ADG concurrent managers, the managers need to be configured and registered.
To ensure that only ADG managers handle standby requests, you need to designate an include/exclude rule, such that ADG managers are included, all others are excluded. There is an example of setting up an ADG manager:
Log in as System Administrator
Concurrent -> Program -> Types
Create
    Name : ADG
    Application: Application Object Library
    Description : ADG Type for CM
Save to commit.

Concurrent -> Manager -> Define
Create
    Manager: ADG Manager
    Short Name: ADGMGR
    Application: Application Object Library
    Description: ADG Manager
    Type: Concurrent Manager
    Program Library: FNDLIBR
    Primary Node: ADG reporting node
    Save to commit.
    Specialization Rules
        Include/Exclude: Include
        Type: Request Type
        Application: Application Object Library
        Name: ADG [ find for type defined above ]
    Work Shifts
        Standard/24 hours
        Processes: 2
        Sleep seconds : 20
        Save to commit.

Update Specialization Rules for ‘AJITH Standard RAC Node XX Manager’
    Include/Exclude: Exclude

Type: Request Type
    Application: Application Object Library
    Name: ADG [ find for type defined above ]
    Save to commit.

Once configured this rule needs to be registered using fnd_adg_utility.register_standby_cm_class
begin
fnd_adg_utility.register_standby_cm_class (
  P_STANDBY_NUMBER => 1,
  P_REQ_CLASS_APP_ID => 0,
  P_REQ_CLASS_ID => 4);
end;
For a given request type use the following SQL to determine the class ids:
select APPLICATION_ID,REQUEST_CLASS_ID
   from apps.fnd_concurrent_request_class
 where REQUEST_CLASS_NAME='ADG';
All valid requests will now be routed to the designated manager. Conversely, if the ADG manager/database is unavailable, the request will be routed based on existing rules/shifts defined for the program.
Enabling/Disabling ADG support
ADG support must be enabled before it can be used:
execute fnd_adg_utility.enable_adg_support;
Conversely it can be disabled at any time. All other settings are left unchanged.
execute fnd_adg_utility.disable_adg_support;
 
 Disabling ADG support is done for two reasons:
·         To disable all standby/simulation actions.
·         To allow the control APIs to be called.

Simulated Standby

One of the problems with redirecting reports to an ADG database, is determining whether a report will run on a READ ONLY database. To help answer this, it is possible to run a simulation where a report runs on primary but is executed as if it were running on standby. In this simulation mode, SQL that would fail on the standby is logged to both database trace files and V$ views. After a program has completed, the  EBS infrastructure will scan the trace file/V$ views and record the number of violations.  Note that trace file access requires a directory object that resolves to the instance trace directory.
The process of using simulated standby is as follows:
·         Register a Simulated Standby Connection
Use privileged account to create database link. Add AJITHRAC1 tns item to database and apps tiers.
create public database link ADG_TO_SIMULATED_STANDBY using 'AJITHRAC_SIM';
As apps:
begin
  fnd_adg_utility.register_connection(
    p_type => fnd_adg_utility.C_CONNECT_TO_SIMULATED_STANDBY,
    p_link_name => 'ADG_TO_SIMULATED_STANDBY.LAB.COM',
    p_link_connstr => 'AJITHRAC_SIM');
end;
·         Create a database service to identify a simulation connection.
srvctl add service -d AJITHRAC -s AJITHRAC_SIM -r AJITHRAC2
·         Set simulation options
desc fnd_adg_utility.set_simulated_standby_options
PROCEDURE SET_SIMULATED_STANDBY_OPTIONS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_ENABLE_SIMULATED_STANDBY     BOOLEAN                 IN     DEFAULT
 P_ENABLE_AUTO_SIMULATION       BOOLEAN                 IN     DEFAULT
 P_SIMULATED_STANDBY_SERVICE    VARCHAR2                IN     DEFAULT
 P_SIMULATION_ERROR_THRESHOLD   NUMBER                  IN     DEFAULT
 P_TRACE_DIRECTORY_OBJ          VARCHAR2                IN     DEFAULT


begin
fnd_adg_utility.set_simulated_standby_options(
    P_ENABLE_SIMULATED_STANDBY => true,
    P_ENABLE_AUTO_SIMULATION => false,
    P_SIMULATED_STANDBY_SERVICE => 'AJITHRAC_SIM',
    P_SIMULATION_ERROR_THRESHOLD => 5);
end;

Before a connection can be used it must be validated:
exec fnd_adg_utility.validate_connection(fnd_adg_utility.C_CONNECT_TO_SIMULATED_STANDBY);
·         Enable database logon/off triggers
execute fnd_adg_utility.enable_database_triggers;
·         Use the simulated standby connect string for the concurrent program or use redirection.
·         Run the concurrent program.
Read Only Violation Processing

During logoff processing, the following data is recorded :
·         Session level violations in fnd_adg_simulated_stndby_trc
·         Program level violations in fnd_adg_concurrent_program.

If the simulation was successful and no violations were recorded, the program is marked as runnable on standby.

The violation count in fnd_adg_simulated_stndby_trc is the total count; in fnd_adg_concurrent_program it is the count less the threshold.

Managing Concurrent Programs
Previous sections covered enabling and configuring Active Data Guard support. The next step is to manage the programs that can run on an ADG database.
Due to various limitations with ADG, the following has to be true before a report can be run on standby.
·         Report must be the correct type - currently only Oracle Reports programs are supported.
·         Report must have been previously been run on primary.
·         Report must have been run in simulation mode.
·         Report must be marked as runnable on standby - i.e. has no READ ONLY violations.
With these conditions met, and with ADG support enabled a concurrent request will be redirected to the ADG concurrent managers.
The state changes above are handled automatically but customers may wish to override/pre-populate the program rules based on their requirements. This is managed through the manage_concurrent_program API.

Database Triggers
schema level triggers
ADG support delivers three schema level database triggers. They are disabled by default. To enable/disable these triggers use:
    fnd_adg_utility.enable|disable_database_triggers.
The three triggers are for logon,logoff and servererror.
Logon/Logoff Triggers
These triggers are to support simulation testing. The logon trigger will enable READ ONLY violation trace; the logoff trigger will record the number of violations.
If these triggers are not enabled, the following limitations apply:
·         Trace errors and V$ data are not recorded and simulations will be treated as having no errors. Without these triggers, enabling the simulation option is of limited benefit. 
Error Trigger
This trigger is only executed if ORA-16000 is raised - i.e. the trigger does nothing on the primary. If has been enabled then the error count for the concurrent program is incremented.
If the error trigger is not enabled, the following limitations apply:
·         Report failures will not be recorded.
·         Failiures will not lead to "Run on Standby"  being disabled.

Preparation to run on ADG
1.       The program should be executed on primary database. Before this step the request may return no rows
SELECT *
  FROM apps.FND_ADG_CONCURRENT_PROGRAM a, APPS.FND_CONCURRENT_PROGRAMS b
 WHERE a.concurrent_program_id = b.concurrent_program_id
       AND b.concurrent_program_name = 'FNDSCURS';
After execution report, column “has_run_on_primary” should be Y in query:
SQL> SELECT has_run_on_primary, has_run_in_simulated_standby
  FROM apps.FND_ADG_CONCURRENT_PROGRAM a, APPS.FND_CONCURRENT_PROGRAMS b
 WHERE a.concurrent_program_id = b.concurrent_program_id
       AND b.concurrent_program_name = 'FNDSCURS';

HAS_RUN_ON_PRIMARY       HAS_RUN_IN_SIMULATED_STA
------------------------ ------------------------
Y                        N

SQL>
2.       To test execution ability on ADG, the report should be ran in simultaneous mode. Change concurrent program session parameter “target instance” to AJITHRAC_SIM, change request type to ADG in concurrent program definition and run the report again. If succesfull, has_run_in_simulated_standby should be “Y”.
SQL> SELECT has_run_on_primary, has_run_in_simulated_standby, RUN_ON_STANDBY
  FROM apps.FND_ADG_CONCURRENT_PROGRAM a, APPS.FND_CONCURRENT_PROGRAMS b
 WHERE a.concurrent_program_id = b.concurrent_program_id
       AND b.concurrent_program_name = 'XXG_ACCOUNTING_CNDN_REPORT';

HAS_RUN_ON_PRIMARY       HAS_RUN_IN_SIMULATED_STA RUN_ON_STANDBY
------------------------ ------------------------ ------------------------
Y                        Y                        N

SQL>
3.       Now the report can be run on standby database. To check, manually set  concurrent program session parameter “target instance” to AJITHRAC_S. And run the request. After successful finish, “RUN_ON_STANDBY” will set to “Y”
SQL> SELECT has_run_on_primary, has_run_in_simulated_standby, RUN_ON_STANDBY
  FROM apps.FND_ADG_CONCURRENT_PROGRAM a, APPS.FND_CONCURRENT_PROGRAMS b
 WHERE a.concurrent_program_id = b.concurrent_program_id
       AND b.concurrent_program_name = 'XXG_ACCOUNTING_CNDN_REPORT';

HAS_RUN_ON_PRIMARY       HAS_RUN_IN_SIMULATED_STA RUN_ON_STANDBY
------------------------ ------------------------ ------------------------
Y                        Y                        Y

SQL>
To monitor where request is running following SQL, and, of course, look at sessions on standby database.
SELECT  connstr1, sqlnet_string, db_instance, q.concurrent_queue_name, concurrent_program_name, r.*, pg.*, pc.*, q.*,
       pg.concurrent_program_name,
       r.os_process_id req_pid,
       pc.os_process_id mgr_pid,
       q.concurrent_queue_name,
       pc.node_name node,
       r.logfile_name req_log,
       pc.logfile_name mgr_log
  FROM apps.fnd_concurrent_requests r,
       apps.fnd_concurrent_programs pg,
       apps.fnd_concurrent_processes pc,
       apps.fnd_concurrent_queues q
 WHERE     r.controlling_manager = pc.concurrent_process_id
       AND pc.queue_application_id = q.application_id
       AND pc.concurrent_queue_id = q.concurrent_queue_id
       AND r.program_application_id = pg.application_id
       AND r.concurrent_program_id = pg.concurrent_program_id
 and (r.concurrent_program_id in (20641))
 order by request_date desc;

Change program setting to run on ADG using procedure
To avoid steps described in “Preparation to run on ADG”, it is possible to set all fields for running on ADG, by calling procedure:
desc fnd_adg_utility
PROCEDURE MANAGE_CONCURRENT_PROGRAM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_APPLICATION_ID               NUMBER                  IN
 P_CONCURRENT_PROGRAM_ID        NUMBER                  IN
 P_HAS_RUN_ON_PRIMARY           BOOLEAN                 IN     DEFAULT
 P_HAS_RUN_ON_SIMULATED_STANDBY BOOLEAN                 IN     DEFAULT
 P_RUN_ON_STANDBY               BOOLEAN                 IN     DEFAULT
 P_NO_STANDBY_FAILURES          NUMBER                  IN     DEFAULT
 P_MAX_STANDBY_FAILURES         NUMBER                  IN     DEFAULT
 P_NO_SIMULATED_STDBY_FAILURES  NUMBER                  IN     DEFAULT
 P_MAX_SIMULATED_STDBY_FAILURES NUMBER                  IN     DEFAULT
 P_ALWAYS_REDIRECT_IF_VALID     BOOLEAN                 IN     DEFAULT
 P_USE_AUTOMATIC_REDIRECTION    BOOLEAN                 IN     DEFAULT

begin
  fnd_adg_utility.MANAGE_CONCURRENT_PROGRAM(
    P_APPLICATION_ID => 0,
    P_CONCURRENT_PROGRAM_ID => 20650,
    P_HAS_RUN_ON_PRIMARY => true,
    P_HAS_RUN_ON_SIMULATED_STANDBY => true,
    P_RUN_ON_STANDBY => true,
    P_ALWAYS_REDIRECT_IF_VALID => true,
    P_USE_AUTOMATIC_REDIRECTION => true);
end;
/
Redirection to ADG
Concurrent request can be started on ADG on following  conditions:
·         By changing concurrent program session parameter “target instance” to AJITHRAC_S.
or
·         By setting system parameter enable_redirect_if_valid  to true (default is false) and always_redirect_if_valid to true on the program level (default is true).
set serveroutput on
--system wide setting
exec fnd_adg_utility.set_control_options(P_ENABLE_REDIRECT_IF_VALID=>true);

declare
  V_redir       Boolean;
begin
  v_redir := fnd_adg_utility.IS_ENABLE_REDIRECT_IF_VALID;
  if v_redir then
    dbms_output.put_line('IS_ENABLE_REDIRECT_IF_VALID is ENABLED');
  else
    dbms_output.put_line('IS_ENABLE_REDIRECT_IF_VALID is DISABLED');
  end if;
end;
/

--concurrent program setting:
begin
  fnd_adg_utility.MANAGE_CONCURRENT_PROGRAM(
    P_APPLICATION_ID => 0,
    P_CONCURRENT_PROGRAM_ID => 20641,
    P_ALWAYS_REDIRECT_IF_VALID => true)
end;
/

select always_redirect_if_valid from apps.FND_ADG_CONCURRENT_PROGRAM
where application_id=0 and CONCURRENT_PROGRAM_ID=20641;
or
·         By setting system parameter enable_automatic_redirection to true (default is false) and use_automatic_redirection to true on the program level (default is false).
set serveroutput on
--system wide setting
exec fnd_adg_utility.set_control_options(P_ENABLE_AUTOMATIC_REDIRECTION=>true);

declare
  V_redir       Boolean;
begin
  v_redir := fnd_adg_utility.IS_AUTOMATIC_REDIRECTION;
  if v_redir then
    dbms_output.put_line('AUTOMATIC_REDIRECTION is ENABLED');
  else
    dbms_output.put_line('AUTOMATIC_REDIRECTION is DISABLED');
  end if;
end;
/

--concurrent program setting:
begin
  fnd_adg_utility.MANAGE_CONCURRENT_PROGRAM(
    P_APPLICATION_ID => 0,
    P_CONCURRENT_PROGRAM_ID => 20641,
    P_USE_AUTOMATIC_REDIRECTION => true)
end;
/

select USE_AUTOMATIC_REDIRECTION from apps.FND_ADG_CONCURRENT_PROGRAM
where application_id=0 and CONCURRENT_PROGRAM_ID=20641;

HAPPY LEARNING!

8 comments:

  1. Hi Ajith,

    Thanks for sharing. Very much useful post.

    I am following the same doc and stuck in "Prepare the System for Reporting" section while executing the procedure, switch_rpc_system_on. It throws the error like "validate connection - cannot connect via link [standby-->primary...sql not properly ended".

    how to solve this? can you please provide some suggestion.

    Regards,
    krish.

    ReplyDelete
    Replies
    1. Thanks Krish,

      The error looks like the issue with the DB link. If you look at the note below that command was as below.

      execute fnd_adg_utility.switch_rpc_system_on;

      Note. This will fail unless the database link registered is valid - i.e. the link must resolve to a READ-WRITE database with the same DBID and the same instance.

      Verify if your DB-link mentioned in the below section is a valid one

      begin
      fnd_adg_utility.register_connection(
      p_type => fnd_adg_utility.C_CONNECT_STANDBY_TO_PRIMARY,
      p_link_name => 'adg_to_ajithrac.lab.com',
      p_link_connstr => 'ajithrac');
      end;

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Hi Ajith,

      Can you post the TNS entries of primary and standby Database, for reference.

      Regards,
      Krish.

      Delete
    4. Hi Ajith,

      The Issue was resolved after doing R&D in db_domain parameter.

      Regards,
      Krish

      Delete
  2. Hi Ajith,
    Thanks for your steps.
    We have implemented 1 primary database and standby database with ADG enabled.
    Still do we need to create Simulated Standby database, please provide me some suggestions on this.

    My ADG instance is like below.
    1. Node-1 Primary database.
    2. Node-2 Standby database.
    3. Node-3 Primary application node.
    4. Node-4 Secondary application with PCP.

    Thanks,
    Venkat

    ReplyDelete
  3. Hi Ajith,

    I am very much thank full to you as your document is very use full, you have provided step by step procedure which helped us in configuring the ebs for adg reporting. Here I am facing small issue. First I submitted the program ''PERRPRAS'' from primary and "HAS_RUN_ON_PRIMARY" column is updated to 'Y' but
    1) when I submitted the program by changing session parameter of the program to SIMULATED mode both the "HAS_RUN_IN_SIMULATED_STANDBY and "RUN_ON_STANDBY" columns got updated to 'Y'. Is this possible for "RUN_ON_STANDBY" column to get updated to 'Y' with submitting program in ADG mode?

    2)I tried to check weather Program ('PERRPRAS') ran from STANDBY Data Base or not by updating Employee information in "enter and maintain" form and checked in the standby data base info in Per_people_f which showed old information but in program output it showed the Latest information, Is this correct? could you please help correct if I am wrong.

    Thanks in advance,
    Anil

    ReplyDelete
  4. Very nice post Ajith.

    Quick question, is it mandatory to have PCP for ADG Reporting?

    Thank you,
    Amar

    ReplyDelete

Thanks for you valuable comments !