, , ,

CONCSUB Utility – Run Oracle Apps Concurrent Program from OS Level

The concurrent request can be submitted via shell script. This can be done by a standard utility named CONSUB. It can be used to schedule a request too.
The syntax for CONCSUB is given below:
CONCSUB <APPS username>/<APPS password>
<responsibility application short name>
<responsibility name>
<username>
[WAIT=N|Y|<n seconds>]
CONCURRENT
<program application short name>
<program name>
[PROGRAM_NAME=<description>]
 
[ORG_ID=<#>] – R12 onwards only
[REPEAT_TIME=<resubmission time>]
[REPEAT_INTERVAL= <number>]
[REPEAT_INTERVAL_UNIT=< resubmission unit>]
[REPEAT_INTERVAL_TYPE=< resubmission type>]
[REPEAT_END=<resubmission end date and time>]
[START=<date>]
[IMPLICIT=< type of concurrent request>
[<parameter 1> … <parameter n>]
These are the compulsory Parameters:
PARAMETER
DESCRIPTION
ORACLE ID
Username and Password of the ORACLE ID for Applications, separated by a slash (“/”). This would be apps/appspass
Responsibility Application Short Name
Enter the short name of the application for your responsibility. This name, along with your responsibility name, will be used to select a responsibility for your request to run in. eg : SYSADMIN
Responsibility Name
This name, along with your responsibility application short name, will be used to select a responsibility for your concurrent request to run in.
eg : ”System Administrator”
User name
Enter the name of your Application Object Library user. This name will be used to update the Who information for any data your concurrent manager changes.
eg : SYSADMIN
CONCURRENT
A flag that separates the program–specific parameters from the operating system parameters.
Program application short name
The application short name of your concurrent program. 
eg : FND
Program name
The uppercase name of your program. It must be the short name that you enter in the Concurrent Programs window when defining a concurrent program.

Here is an example of the command to run CONCSUB:

$ CONCSUB APPS/APPS
SYSADMIN
System Administrator
SYSADMIN
WAIT=N
CONCURRENT
FND
FNDFMRTC
PROGRAM_NAME=Register Custom Tables Weekly
REPEAT_INTERVAL=7
REPEAT_INTERVAL_UNIT=DAYS
REPEAT_INTERVAL_TYPE=START
START='”08“JUN96 23:55:00€”‘
CGL
APPLSYS
ALL
CGL
Notes:
1] For parameters that follow the CONCURRENT parameter and include spaces, enclose the parameter argument in double quotes, then again in single quotes. Oracle Application Object Library requires this syntax because it parses the argument string twice.
2] The above example uses the UNIX line continuation character ”, not all operating systems support the use of this character.  If the line continuation character is not supported then the CONCSUB command should be submitted as one continuous line.
3] COCNSUB is also used to shutdown the concurrent managers, however to start the concurrent manager you have to use startmgr utility.
CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND SHUTDOWN
CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND ABORT
4] The CONCSUB executable is located at $FND_TOP/bin/CONCSUB.

/by
, , , , , ,

How to Get IP address and host address in Oracle

This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

UTL_INADDR
SYS_CONTEXT
V$INSTANCE
V$SESSION

    UTL_INADDR

    The UTL_INADDR provide a means of retrieving host names and IP addresses of remote hosts from PL/SQL.
    The GET_HOST_ADDRESS function returns the IP address of the specified host name.
    SQL> SELECT UTL_INADDR.get_host_address('TEST') FROM dual;

    UTL_INADDR.GET_HOST_ADDRESS('TEST')
    --------------------------------------------------------------------------------
    192.167.1.56

    SQL>

     The IP address of the database server is returned if the specified host name is NULL or is omitted.

    SQL> SELECT UTL_INADDR.get_host_address from dual;

    GET_HOST_ADDRESS
    --------------------------------------------------------------------------------
    192.161.1.55

    SYS_CONTEXT

    The SYS_CONTEXT function is able to return the following host and IP address information for the current session:
    • TERMINAL – An operating system identifier for the current session. This is often the client machine name.
    • HOST – The host name of the client machine.
    • IP_ADDRESS – The IP address of the client machine.
    • SERVER_HOST – The host name of the server running the database instance.
    SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

    SYS_CONTEXT('USERENV','TERMINAL')
    --------------------------------------------------------------------
    TEST10

    SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

    SYS_CONTEXT('USERENV','IP_ADDRESS')
    --------------------------------------------------------------------
    192.167.1.55

    SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

    SYS_CONTEXT('USERENV','SERVER_HOST')
    --------------------------------------------------------------------
    Z4210gr11

    V$INSTANCE

    The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.
    SQL> SELECT host_name FROM v$instance;

    HOST_NAME
    ------------------------------------------------
    Z4210gR11

    V$SESSION

    The V$SESSION view contains the following host information for all database sessions:

    TERMINAL – The operating system terminal name for the client. This is often set to the client machine name.

    MACHINE – The operating system name for the client machine. This may include the domain name if present.

    The following examples show the typical output for each column.

    SQL> SELECT terminal, machine FROM v$session WHERE username = 'OEAG';

    TERMINAL                       MACHINE
    ------------------------------ ----------------------------------------------------
    TEST10                          ORACLE-BASETEST10

    /by
    , , , ,

    R12 Clone using rman method

    This article details the steps used to clone R12 RAC database to Single instance database using RMAN duplicate command, Notice that we used the control file as a catalog database and the Production will keep up and running.

    The databases used here are:
    Production: PROD
    Cloned: CPCLONE
    1- On the source system; Run the preclone script on both database and apps tiers.
    Database:
    $ cd ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/
    $ perl adpreclone.pl
    Application:
    $ cd $ADMIN_SCRIPTS_HOME/appsutil/scripts/$CONTEXT_NAME/
    $ perl adpreclone.pl
    2- Archive and move the Apps tier and Database home form the source to the target server:
    For the application tier:
    $ cd $APPL_TOP/../../..
    $ tar cvf – apps| gzip > apps_PROD.tar.gz
    For the database tier
    $ cd $ORACLE_HOME/..
    $ tar cvf – db | gzip > db_PROD.tar.gz
    3- Create full RMAN backup for the source database:
    $ export ORACLE_SID=PROD
    $ rman target /
    run
    {
    allocate channel d1 type disk;
    allocate channel d2 type disk;
    backup format ‘/backup/MASTER/ERP/df_prodt%t_s%s_p%p.bak’ database;
    sql ‘alter system archive log current’;
    backup format ‘/backup/MASTER/ERP/df_archive%t_s%s_p%p.arc’ archivelog all;
    release channel d1; release channel d2;
    }
    4- Move the backup pieces generated from Rman to the target server to the same path or make a soft link as the same backup original path to let Rman see it.
    Example $ ln –f -s /u02/CPCLONE/backup /backup/MASTER/ERP.
    5- Extract the archive files on the Target servers
    For the application tier:
    $ tar cvf – apps| gzip > apps_PROD.tar.gz
    For the database tier
    $ tar cvf – db | gzip > db_PROD.tar.gz
    Note: The current path depends on your configuration.
    6- On the target system; Run the preclone script on the database tier.
    $ cd $ORACLE_HOME/appsutil/clone/bin
    $ perl adcfgclone.pl dbTechStack
    7- Edit the new created init file under $ORACLE_HOME/dbs to include the following parameters; Note we are using ASM as data storage:
    _no_recovery_through_resetlogs=TRUE
    db_file_name_convert =(‘+DATA1/prod’, ‘/u01/CPCLONE/oradata’)
    log_file_name_convert =(‘+DATA1/prod’, ‘/u01/CPCLONE/oradata’)
    and edit the following parameters if not have those values
    undo_management =AUTO
    undo_tablespace =UNDOTBS1
    db_name =cpclone
    instance_name =cpclone
    8- On the target system; Startup the database on nomount state:
    $ export $ORACLE_SID=CPCLONE
    $ sqlplus / as sysdba
    SQL> startup nomount pfile=initCPCLONE.ora;
    9- Make the production database accessible from the clone database:
    $ cd $TNS_ADMIN
    $ vi tnsnames.ora
    And insert the follow entries:
    PROD= (
    DESCRIPTION=
    (
    ADDRESS=(PROTOCOL=tcp)(HOST=gfmisdb1)
    (PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=PROD)
    (INSTANCE_NAME=PROD1)
    )
    )

    10- Test the connection to the production database:
    $ tnsping PROD
    $ sqlplus apps/apps@PROD
    11- Lunch rman to start the duplicate process:
    $ export $ORACLE_SID=CPCLONE
    $ rman target sys/sys@prod auxiliary /
    run
    {
    allocate auxiliary channel C1 device type disk;
    allocate auxiliary channel C2 device type disk;
    duplicate target database to cpclone;
    release channel C1;
    release channel C2;
    }
    12- Shutdown the cpclone database and edit the parameter file as:
    $ sqlplus / as sysdba
    $ shutdown abort;
    Edit the following parameters:
    undo_management to =MANUAL
    undo_tablespace=UNDOTBS1
    13- Open the database in with resetlogs option:
    $ sqlplus / as sysdba
    Sql> startup mount;
    Sql> alter database open resetlogs;
    Sql> drop tablespace APPS_UNDOTS1 including contents;
    Sql> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE
    ‘/u02/CPCLONE/oradata/datafile/undo01.dbf’ SIZE 4000M AUTOEXTEND
    ON NEXT 100M MAXSIZE 7000M
    ONLINE
    RETENTION NOGUARANTEE
    BLOCKSIZE 8K
    FLASHBACK ON;
    Then shutdown the database
    Sql> shutdown immediate;
    14- Edit the parameter file:
    Remove _no_recovery_through_resetlogs=TRUE and edit
    undo_management=AUTO
    undo_tablespace=APPS_UNDOTS1
    15- Change the database mode to be in no archive log mode:
    $ sqlplus / as sysdba
    Sql> startup mount;
    Sql> alter database noarchivelog;
    Sql> alter database open;
    Execute the following script
    Sql> @ $ORACLE_HOME/appsutil/install/[CONTEXT NAME]/ adupdlib.sql so
    Sql> create spfile from pfile.
    Sql> shutdown immediate;
    Sql> startup;
    16- After the database opened; execute the following script:
    cd $ORACLE_HOME/appsutil/clone/bin
    perl adcfgclone.pl dbconfig $ORACLE_HOME/appsutil/$CONTEXT_NAME.xml
    17- On the application tier run the following script:
    cd $COMMON_TOP/clone/bin
    perl adcfgclone.pl appsTier
    /by
    , , , ,

    Change Applications Tier hostname using AutoConfig

    1. Deregister the current Applications server (Required)
    De-register the current Applications server node, run the following command as the owner of the Oracle Applications file system and current database instance as follow:
    perl $AD_TOP/bin/adgentns.pl appspass= contextfile= -removeserver
    2. Shutdown the Applications Tier Services (Required):
    $COMMON_TOP/admin/scripts//adstpall.sh apps

    3. Change the hostname as required:
    Change the hostname in /etc/hosts file
    4. Change the hostname as required:
    Change the hostname in /etc/hosts file
    5. Reboot the Node
    /by
    , , , , ,

    Tracing techniques in oracle applications

    This is one of the good reference documents from metalink.

    WHAT TO SET UP BEFORE GENERATING THE TRACE

    These steps must be performed by the DBA on the database server.

    1. Set TIMED_STATISTICS to TRUE.
       For performance issues, make sure TIMED_STATISTICS is turned on, before attempting to generate the trace.

       Set the following in the init.ora file:
          TIMED_STATISTICS=TRUE
          OR
          in SQL*Plus: ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

    2. Set the location of the trace output.
       Set the following in the init.ora file: USER_DUMP_DEST =

    3. Create the PLAN_TABLE to hold the output of the explain plan. Run the SQL script called UTLXPLAN.SQL to create this in the apps schema. This script is usually in $ORACLE_HOME/rdbms/admin.

    4. If the init.ora file has been updated, you must shut down and restart the database before the changes will take effect.
       TYPES OF TRACE – HOW TO TURN TRACE ON
       Regardless of the type of trace file you create, make sure you note the time that you create it.

    1] Form Trace

    Toggle trace on/off on the form, to trace specific application functions. Make sure you go in fresh (sign off/on to the application), since somequeries are cached and may not be executed on subsequent visits to theform.

    From the menu, select Help–>Diagnostics–>Trace to turn trace on (when checked, it is on).
       Perform the action to be traced.
       From the menu, turn trace off, by selecting Help–>Diagnostics–>Trace (it should now be unchecked).

    2] Concurrent Program Trace
       A. This will turn trace on for each execution of this program. In Release 11.0 and lli, check the Enable Trace checkbox for the concurrent program on the Concurrent Programs form. After running the program to be traced, make sure you uncheck the Enable Trace checkbox.
       Select the System Administrator responsibility.
       Navigation = Concurrent -> Programs -> Define. Query the concurrent program you want to trace. Check the Enable Trace checkbox and save.
       OR
       B. How to generate a raw trace file with binds and/or waits for 11.5.10:
    1. Log into applications as System Administrator and Navigate to the System Profile Values Form. Select the profile called Concurrent: Allow Debugging and change the value to Yes at the appropriate level.
    2. Allow pop-ups on your browser.
    3. Navigate to the Submit a New Request form and select a job and enter all parameters for that job
    4. Select the Debug Options Button and this will take you to Create Debug Rule in Oracle Application Manager
    5. Select the appropriate Debug Option Value for SQL Trace only and then check the box
    6. Hit OK twice and then Submit the job
    7. Raw trace file with options selected will be located in the appropriate directory.

    3] Database Level Trace
       This will turn trace on for all processes that are running in the instance and should only have to be used in Release 10.7, for concurrent programs. (This has to be done by the DBA.)
       Set the following in the init.ora file:SQL_TRACE=TRUE
       Shut down and restart the database.After generating the trace file, shut down and restart the database with the original init.ora.

    4] Report Trace
    If you are on 10.7 and need to trace an Oracle Report, you can modify the report to turn trace on for that session.
       a. Convert the report from rdf to rex: $ORACLE_HOME/bin/r25convm batch=yes userid=> stype=rdffile source=REPORT_NAME.rdf dtype=rexfile overwrite=yes
       b. Edit the rexfile and search for the beforerep trigger in the report.
          Locate the following code:
             IF (:p_trace_switch = ‘Y’) THEN
                SRW.DO_SQL(‘alter session set sql_trace TRUE’);
             END IF;
          Comment out the IF and END IF lines.
       c. Save the report.
       d. Convert the report from rex to rdf: $ORACLE_HOME/bin/r25convm batch=yes userid= > stype=rexfile source=REPORT_NAME.rex dtype=rdffile overwrite=yes

    5] Self Service page (like a Forms trace, but for self service web apps)
       a. Set the FND:Diagnostics profile: Responsibility = System Administrator
          Navigation: Profile > System User: Enter User name
          Query the Profile: ‘FND:Diagnostics’
          Set the ‘FND:Diagnostics’ profile to Yes at User level

       b. Login to Self Service under the same user the profile was set for.
       c. Turn Trace on: Click the Diagnostic link at the top of the page.
          It shows two options: Show Log and Set Trace Level
          Select ‘Set Trace Level’
          Click Go.
          Select one of the following options:
          Disable Trace – used to end the trace
             Trace (Regular) – just like a forms trace
             Trace with Binds – record the bind variables in the trace
             Trace with waits – Good for performance issues
             Trace with binds and waits – combines both of the above
          Click Save.
        
       d. Perform the action to be traced in Self Service.
          Multiple trace files may be generated in the usual trace directory.
       e. Turn trace off: Select the Diagnostic link
          Click on option: Set Trace Level
          Note all of the trace numbers listed
          Click Go
          Select: Disable Trace
          Click Save

    WHAT TO DO AFTER GENERATING THE TRACE FILE

    These steps should be performed by the DBA, on the database server.

    1. Find the trace directory. Get the location of user_dump_dest. Log into SQL*Plus as the apps user.
       select value from V$PARAMETER where name = ‘user_dump_dest’
    2. Find the trace file for your process.Go to the directory you found in step 1 (in UNIX, use cd). Look for a file (.trc) that was created at the time you started your process (in UNIX, use ls -ltr).
    3. Run tkprof with explain plan. Go to a directory in which you have write privilege (in UNIX, use cd).

    Run tkprof:
    tkprof explain=

    HOW TO GET AN EXPLAIN PLAN FOR A SQL STATEMENT

    Sometimes you may have a need to get an explain plan for a specific SQL statement. If you have the sql statement, you can get the explain plan for it.
    This should be run on the same instance that the sql statement came from.
    In your apps account —
    Run the following script:
    delete from plan_table
    where statement_id = ‘tmp’

    explain plan

    set statement_id = ‘tmp’
    for

    /
    set pages 100
    col operation format a36
    col options format a11
    col object_name format a30

    select lpad(‘ ‘,2*(level-1))operation operation,

    options, object_name
    from plan_table
    where statement_id = ‘tmp’
    connect by prior id = parent_id
    and statement_id = ‘tmp’
    start with id = 1
    and statement_id = ‘tmp’
    order by id
    /
    delete from plan_table
    where statement_id = ‘tmp’
    /
    commit;

    /by