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

   

Environment Settings

Close the current ssh session, open a new session as applmgr and reload the environment settings.

 CM Queue

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!