Oracle Technology Stack: November 2014

Monday, November 24, 2014

R12-Custom Top Creation

Make the directory structure for your custom application files.

cd $APPL_TOP
mkdir xxtest
mkdir xxtest/12.0.0
mkdir xxtest/12.0.0/admin
mkdir xxtest/12.0.0/admin/sql
mkdir xxtest/12.0.0/admin/odf
mkdir xxtest/12.0.0/sql
mkdir xxtest/12.0.0/bin
mkdir xxtest/12.0.0/reports
mkdir xxtest/12.0.0/reports/US
mkdir xxtest/12.0.0/forms
mkdir xxtest/12.0.0/forms/US
mkdir xxtest/12.0.0/lib
mkdir xxtest/12.0.0/out
mkdir xxtest/12.0.0/log

Add the custom module into the application context file

vi $CONTEXT_FILE
<au_top oa_enabled="FALSE" oa_type="PROD_TOP" oa_var="s_autop">/u01/TEST/apps/apps_st/appl/au/12.0.0</au_top>
<xxtest_top oa_enabled="FALSE" oa_type="PROD_TOP" oa_var="s_xxtesttop">/u01/TEST/apps/apps_st/appl/xxtest/12.0.0</xxtest_top>

3)
Stop the application and run autoconfig

cd $ADMIN_SCRIPTS_HOME
sh adstpall.sh apps/apps
sh adautocfg.sh
sh adstrtal.sh apps/apps

Log out from the session and login and verify whether custom top is viewable

$echo $XXTEST_TOP
/u01/TEST/apps/apps_st/appl/xxtest/12.0.0

Create Tablespace for Custom top

SQL>create tablespace XXTEST
datafile '/u01/TEST/db/apps_st/data/>01.dbf' size 2048M
autoextend on next 256M maxsize 20480M;

Create User for Custom top

create user XXTEST
identified by XXTEST
default tablespace XXTEST
temporary tablespace TEMP
quota unlimited on XXTEST;

User created.

SQL> grant connect,resource to XXTEST;

Navigate to Application->Register

Application - TEST Custom Application

Short Name - XXTEST

Basepath - XXTEST_TOP

Description - TEST Custom Application

Naviate to Security->Oracle->Register

DB User Name - XXTEST

Password - XXTEST

Privilege - Enabled

Install Group - 0

Description - XXTEST Custom Application User

Add Application to a Data Group Navigate to Security->Oracle->DataGroup

Application - TEST Custom Application

Oracle ID - APPS

Create .env file with custom path

cd $APPL_TOP
vi customTEST_test.env
export XXTEST_TOP="/u01/TEST/apps/apps_st/appl/xxtest/12.0.0"

10)

Add custom path into form file.

cd $ORACLE_CONFIG_HOME
vi forms/server/default.env
XXTEST_TOP=/u01/TEST/apps/apps_st/appl/xxtest/12.0.0

Thursday, November 20, 2014

Resolving Concurrent Manager Issues in R12

Concurrent Managers did not start properly

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Stop the database.

3. Start the database.

4. Go to $FND_TOP/bin and run below

$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDFS"
$ adrelink.sh force=y link_debug=y "fnd FNDCRM"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"

5. Run the following queries connect with apps user.

$ sqlplus apps/
SQL> @ CMCLEAN.SQL (download script from note 134007.1) 
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
SQL> commit;

6. Execute the following SQL to Verify the Concurrent Manager definition

SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

Difference between fnd_conc_clone.setup_clean and cmclean.sql

fnd_conc_clone.setup_clean

fnd_conc_clone is a package own by apps schema. This procedure cleanup fnd_nodes table in the target to clear source node information as part of cloning. When we execute fnd_conc_clone.setup_clean it deletes the information from the below tables.

fnd_concurrent_queue_size
fnd_concurrent_queues_tl
fnd_concurrent_queues
fnd_nodes;

Steps to proceed

sqlplus apps/
SQL> exec fnd_conc_clone.setup_clean;
SQL> commit;

cmclean.sql

If we stop conc manager using abort options then concurrent requests will be in running state ,Next when we start concurrent manager the processes will not start properly.
To cleanup running and pending requests we use cmclean.sql this script will update the below tables.

fnd_concurrent_processes
fnd_concurrent_queues
fnd_concurrent_requests
fnd_conflicts_domain
fnd_concurrent_conflict_sets

Note: cmclean.sql and fnd_conc_clone.setup_clean touch different set of concurrent request tables. Its better to run both during the clone.

Related Oracle Notes

Concurrent Processing - Concurrent Manager Recovery Troubleshooting Wizard (Doc ID 134007.1)

Concurrent Processing - After Cloning all the Concurrent Managers do not start for the cloned Instance (Doc ID 555081.1)

Concurrent Processing - R12 Output Post Processor Service Not Coming Up. (Doc ID 460578.1)

After Clone Concurrent Manager Will Not Come Up and Columns in fnd_concurrent_queues Table are Not Getting Updated With Node Names (Doc ID 1646026.1)

Concurrent Processing - Output Post Processor is Down with Actual Process is 0 And Target Process is 1 (Doc ID 858813.1)

Wednesday, November 19, 2014

R12-Script for RMAN and Cold Backup

RMAN Backup

#!/bin/sh

find /findata/backup/PROD/RMAN/ -type d -mtime +3 -exec rm -rf {} \;
log=/findata/backup/PROD/RMAN/`date +%Y%m%d`/`date +%Y%m%d`.log
mkdir /findata/backup/PROD/RMAN/`date +%Y%m%d`

chmod 777 /findata/backup/PROD/RMAN/`date +%Y%m%d`

su - oracle -c "
. /finsys/db/tech_st/11.1.0/PROD_ofprod.env
rman <<EOF

connect target /

RUN {
ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '/findata/backup/PROD/RMAN/%T/%d_DB_%u_%s_%p';
ALLOCATE CHANNEL ch2 TYPE DISK FORMAT '/findata/backup/PROD/RMAN/%T/%d_DB_%u_%s_%p';
ALLOCATE CHANNEL ch3 TYPE DISK FORMAT '/findata/backup/PROD/RMAN/%T/%d_DB_%u_%s_%p';
ALLOCATE CHANNEL ch4 TYPE DISK FORMAT '/findata/backup/PROD/RMAN/%T/%d_DB_%u_%s_%p';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE ;

backup archivelog FROM time 'sysdate-1' FORMAT '/findata/backup/PROD/RMAN/%T/%d_Archivelog_%u_%s_%p';

BACKUP CURRENT CONTROLFILE FORMAT '/findata/backup/PROD/RMAN/%T/%d_Controlfile_%u_%s_%p';

RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;

#crosscheck backup;
#crosscheck archivelog all;
#DELETE NOPROMPT ARCHIVELOG ALL completed before 'sysdate - 1';
#delete NOPROMPT obsolete redundancy 1;
#DELETE NOPROMPT EXPIRED BACKUP;
}EXIT;" 2>&1 >> $log

Cold Backup

#!/bin/sh

find /findata/backup/PROD -type d -mtime +5 -exec rm -rf {} \;
mkdir /findata/backup/PROD/`date +%Y%m%d`
log=/findata/backup/PROD/`date +%Y%m%d`/`date +%Y%m%d`.log
tarlog=/findata/backup/PROD/`date +%Y%m%d`/tar_`date +%Y%m%d`.log
echo "Running preclone for application tier on `date`" 2>&1 >> $log
su - applmgr -c "perl /finsys/inst/apps/PROD_ofprod/admin/scripts/adpreclone.pl appsTier" 2>&1 >> $log
echo "Stopping the application services on `date`" 2>&1 >> $log
su - applmgr -c "sh /home/applmgr/stopAPPS.sh" 2>&1 >> $log
echo "Running preclone for database tier on `date`" 2>&1 >> $log
su - oracle -c "perl /finsys/db/tech_st/11.1.0/appsutil/scripts/PROD_ofprod/adpreclone.pl dbTier pwd=apps" 2>&1 >> $log
echo "Stopping the database on `date`" 2>&1 >> $log
su - oracle -c "sh /home/oracle/stopDB.sh" 2>&1 >> $log
cd /findata/
tar -cvzf /findata/backup/PROD/`date +%Y%m%d`/findata_db_`date +%Y%m%d`.tar db 2>&1 >> $tarlog
cd /finsys/
tar -cvzf /findata/backup/PROD/`date +%Y%m%d`/finsys_db_`date +%Y%m%d`.tar db 2>&1 >> $tarlog
tar -cvzf /findata/backup/PROD/`date +%Y%m%d`/finsys_apps_`date +%Y%m%d`.tar apps 2>&1 >> $tarlog
tar -cvzf /findata/backup/PROD/`date +%Y%m%d`/finsys_inst_`date +%Y%m%d`.tar inst 2>&1 >> $tarlog
echo "Starting the database on `date`" 2>&1 >> $log
su - oracle -c "sh /home/oracle/startDB.sh" 2>&1 >> $log
echo "Starting the Application services on `date`" 2>&1 >> $log
su - applmgr -c "sh /home/applmgr/startAPPS.sh" 2>&1 >> $log

stopAPPS.sh

. /finsys/apps/apps_st/appl/APPSPROD_ofprod.env
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh apps/apps

stopDB.sh

. /finsys/db/tech_st/11.1.0/PROD_ofprod.env
sqlplus / as sysdba <<EOF
Shutdown immediate;
EOF

startAPPS.sh

. /finsys/apps/apps_st/appl/APPSPROD_ofprod.env
cd $ADMIN_SCRIPTS_HOME
./adstrtal.sh apps/apps

startDB.sh

. /finsys/db/tech_st/11.1.0/PROD_ofprod.env
sqlplus / as sysdba <<EOF
startup;
EOF

Monday, November 17, 2014

Find out the Applied AD patches and Current Patch level in R12

To Find the Applied AD Patches

From Application

1. Login to Sysadmin

2. Navigate to the System Administrator->Oracle Applications Manager ->Patching and Utilities

3. Then Search the patch with relevant parameter and click 'Go' Button

From SQL

1.

SELECT DISTINCT 
  e.patch_name Patch_NO, 
  d.patch_abstract Patch,
  e.CREATION_DATE Applied_Date
FROM 
  ad_patch_drivers d , 
  ad_applied_patches e 
WHERE 
  d.applied_patch_id = e.applied_patch_id
ORDER BY e.CREATION_DATE desc;

SELECT DISTINCT 
  a.bug_number, 
  e.patch_name, 
  d.patch_abstract,
  e.CREATION_DATE,
  c.end_date, 
  b.applied_flag
FROM 
  ad_bugs a, 
  ad_patch_run_bugs b, 
  ad_patch_runs c, 
  ad_patch_drivers d , 
  ad_applied_patches e 
WHERE 
  a.bug_id = b.bug_id AND 
  b.patch_run_id = c.patch_run_id AND 
  c.patch_driver_id = d.patch_driver_id AND 
  d.applied_patch_id = e.applied_patch_id AND 
  b.applied_flag='Y'
order by e.CREATION_DATE desc

To Find The Current Patch Level

From Application

1. Login to Sysadmin

2. Navigate to the System Administrator->Oracle Applications Manager and open one of Java Page

3. Click Support Cart –> Applications Signature –> Collect->Check “Product Information” box and Click on “View” (eyeglasses) to run the patch set level report

From SQL

SELECT
a.application_name, a.APPLICATION_SHORT_NAME,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM
apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;

To Verify the Code Level

select * from fnd_product_installations where patch_level like '%AD%';

Friday, November 14, 2014

'Application Diagnostics is not a valid responsibility for the current user...' Error in R12.

Problem

When try to run Oracle Diagnostic the error prompt that 'Application Diagnostics is not a valid responsibility for the current user'

Solution

Must have one of the below roles to be assigned to the User to run Oracle Diagnostics.

Diagnostics Super user
Application Super user
Application End User

1. Login to the system as Sysadmin User

2. Select the "User Management" responsibility

3. Click 'Users' in the right hand side menu

4. Enter 'User Name' and click 'Go'

5. Click 'Update' on the correct user

6. Click 'Assign Roles' button

7. Enter role name (or partial with %) you want to assign. You can enter any of the seeded roles (Diagnostics Super User Role, Application Super User Role, Application End User Role), or your own one

8. Check the box before to the appropriate role and click 'Select' Button

9. Click 'Save' and 'Apply' Button.

10. Run Concurrent Program "Workflow Directory Services User/Role Validation" with parameters

Batch Size - 100000

Fix dangling users - Yes

Add missing user/role assignments - Yes

Update WHO columns in WF tables - No

11. Log off and Login again and check the issue, If it is not resolved then Bounce the apache with clear cache and retest the same.

Thursday, November 13, 2014

Currency Format Not Separated By Commas in R12

Problem

Currency format not separated by commas for all Module (PO,AR,AP,INV,CSD....) in R12.

Solution

Set Following Profile options for site level to overcome the issue.

1. ICX: Numeric characters

2. Currency:Thousands Separator

3. JTF: Use ICX Numeric Characters

Tuesday, November 11, 2014

Physical DR Configuration

Prerequisite

1. Must have password file for both Primary and Standby database with same SYS password

 $orapwd file=orapwTESTDR entries=30 ignorecase=Y  
SQL> select * from V$PWFILE_USERS;

2. Both Primary and Standby should has same DB_NAME and Different DB_UNIQUE_NAME.

3. The standby redo log must have at least one or more redo log group than the redo log at the primary database.

4. Size of standby redolog files should be same as primary online redolog files.

5. Oracle recommends to keep standby redologs on both primary and standby database for make switchover quick.

Primary DB Configuration

1. Check that the primary database is in archivelog mode.

SQL> select log_mode from v$database;

If not, switch to archivelog mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

2. Enabled forced logging by issuing following command.

SQL> ALTER DATABASE FORCE LOGGING;

3. Check the setting for DB_NAME & DB_UNIQUE_NAME parameters.

SQL> show parameter db_name;
SQL> show parameter db_unique_name;
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ebs' SCOPE=SPFILE;

4. Verify the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary database(DB_UNIQUE_NAME for both primary and standby DB)

SQL> show parameter LOG_ARCHIVE_CONFIG;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ebs,ebs_st)';

5. Set suitable remote archive log destinations.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ebs_st NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ebs_st';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

6. The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters set to appropriate values and REMOTE_LOGIN_PASSWORDFILE set to exclusive.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

7. It is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters.

SQL> ALTER SYSTEM SET FAL_SERVER=ebs_st(standby db name);
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

8. Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.

ebs=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebst.aitkenspence.lk)(PORT=1526))
(CONNECT_DATA= (SID=ebs) ) )

ebs_st=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=demo.astech.lk)(PORT=1526))
(CONNECT_DATA= (SID=ebs) ) )

9. Listener file "$ORACLE_HOME/network/admin/listener.ora" of Primary DB.

ebs =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=ebst.aitkenspence.lk)(PORT=1526)) ) )

SID_LIST_ebs =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/ebs/db/tech_st/11.1.0)
(SID_NAME = ebs) ) )

10. Create a Standby controlfile for the standby database by issuing the following command on the primary database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ebs_stby.ctl';

11. Create standby logfile.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/ebs/db/apps_st/data/standby_redo01.log') SIZE 50M;

12. Take a backup of the primary database.

Standby DB Configuration

1. Restore database using backup of primary DB.

2. Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters.

SQL> show parameter db_name;
SQL> show parameter db_unique_name;
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ebs_st' SCOPE=SPFILE;

3. Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ebs,ebs_st)';

4. It is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters.

SQL> ALTER SYSTEM SET FAL_SERVER=DB11G; (primary db name)
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

5. Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers.

ebs_st =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=demo.astech.lk)(PORT=1526))
(CONNECT_DATA=(SID=ebs)) )

ebs=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebst.aitkenspence.lk)(PORT=1526))
(CONNECT_DATA= (SID=ebs) ) )

6. Listener file "$ORACLE_HOME/network/admin/listener.ora" of Standby DB.

ebs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP)(HOST=demo.astech.lk)(PORT=1526)) )

SID_LIST_EBS =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ebs_st)
(ORACLE_HOME = /u01/ebs/db/tech_st/11.1.0)
(SID_NAME = ebs) ) )

7. Create Redo logfile and standby logfile on standby server.

SQL> ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/ebs/online_redo01.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/ebs/db/apps_st/data/standby_redo01.log') SIZE 50M;

8. Startup standby database in mount mode using standby controlfile (created from primary).

SQL> startup nomount;
SQL> alter database mount standby database;

9. Start the apply process on standby server.

SQL> alter database recover managed standby database;
SQL> alter database recover managed standby database disconnect;
For Real-Time Apply
SQL> alter database recover managed standby database using current logfile disconnect;

10. Checking errors in both servers.

SQL> SELECT DEST_ID "ID",STATUS "DB_status",DESTINATION "Archive_dest",ERROR "Error" FROM V$ARCHIVE_DEST;

SQL> select MESSAGE,to_char(timestamp,'DD-MON-YYYY hh24.mi.ss') date_time from v$dataguard_status;

11. Check the new archived redo log has arrived at the standby server and been applied.

SQL>SELECT sequence#,first_time,next_time,archived,applied FROM v$archived_log ORDER BY sequence#;

SQL>ALTER SYSTEM SWITCH LOGFILE;

Reference
Business Continuity for Oracle E-Business Release 12 Using Oracle 11g Release 2 and later Physical Standby Database (Doc ID 1070033.1)

Friday, November 7, 2014

AWR & Statspack Report

AWR Report

Snapshots

For AWR report By default snapshots of the relevant data are taken every hour and retained for 7 days.

we can alter these using following (Current value retained if NULL)

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 14400,
interval => 30);
END;
/

Creating snapshot manually

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Dropping existing snapshot

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 100, 
    end_snap_id   => 120,
    baseline_name => 'test baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted. if cascade TRUE then Deletes associated snapshots.

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => 'test baseline',
cascade => FALSE);
END;
/

AWR Related Views

The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.

Statspack Report

Configure statspack

cd <$ORACLE_HOME>/rdbms/admin
sqlplus / as sysdba
sql> @spcreate.sql

The variables are:
perfstat_password -> for the password
default_tablespace -> for the default tablespace (SYSAUX)
temporary_tablespace -> for the temporary tablespace (TEMP)

If there any errors then de-install and install again

SQL> @spdrop
SQL> @spcreate

Gathering data - Taking a snapshot

For statspack report snapshots did not taken automatically and We have to take this manually.

SQL> connect perfstat/perfstat_password
SQL> exec statspack.snap;

Find the snapshot

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

Generate SP Report

cd <$ORACLE_HOME>/rdbms/admin
sqlplus / as sysdba
SQL> @spreport.sql

you have to provide at the prompt: BEGIN_SNAP, END_SNAP, REPORT_NAME (we can use a default value as well)

then sp report will generate the same name that you given (or else default report name)

Wednesday, November 5, 2014

Defining New Concurrent Manager in R12

We can execute concurrent request through newly define concurrent manager without using Standard Manager.

Responsibility Needed - System Administrator

1. Define Custom Workshifts (Optional)

Navigation

System Administrator->Concurrent->Manager->Workshifts

Click + icon on the menu bar (Top left) to insert new record.

Enter details and save.

2. Define Concurrent Manager

Navigation

System Administrator->Concurrent->Manager->Define

Enter details for following

Manager - New Concurrent Manager Name
Short Name - Short Name
Application Name - Usually Custom Application Name or Existing Application (Ex : Application Object Library)
Type - Concurrent Manager
Cache Size - 1
Node - If you use multi node configuration, you can differentiate primary node for this manager from standard manager such as follows otherwise just keep them blank so that EBS automatically find the right node (Primary - XX2,Secondary - XX1)
Program Library Name - FNDLIBR
Save the Record

3. Define Workshifts for manager

Click 'Workshifts' button on Manager form

Enter following

Select Work shifts from drop down define in step 1 or select standard workshifts
Processes - 10
Sleep Seconds - 30
Save Details

4. Define Specializtion Rule for manager

Click 'Specialization Rules' button on Manager form

Enter what need to be run on new manager

Include/Exclude
Type - Program (Can include Program) Oracle ID (Can include Module) User (Can include Program execute by particular User )
Application - Application Name for Program
Name - Name of Program/User
Save the Record

5. Exclude from Standard Manager

(Otherwise both New Concurrent Manager and Standard Manager handle objects)

Navigation

System Administrator->Concurrent->Manager->Define

Query 'Standard Manager' and Click 'Specialization Rules' button

Exclude the Program/User defined in Step 4

Save the Details

Note : In Specialization Rules of 'Standard Manager', never use "Include" definition. Standard Manager is supposed to deal with all requests. If you define any Include record here, Standard Manager only deal with that "Include" object as if it is a special manager for that object and all other concurrent requests will be kept in "No Manager" status

6. Activate Newly Created Concurrent Manager

Navigation

System Administrator->Concurrent->Manager->Administer

Scroll down the record until find the newly created concurrent manager

Click the row and click 'Activate' Button

By Clicking 'Refresh' Button at regular interval, Monitor Actual and Target Processes are displayed as 10 which defined in step 3

7. Bounce the Standard Manager to reflect the latest definition

Navigation

System Administrator->Concurrent->Manager->Administer

Scroll down the record until find the 'Standard Manager' concurrent manager

Click the row and click 'Deactivate' Button

By Clicking 'Refresh' Button at regular interval, Monitor Status Column, Once Status column become Deactivated then Click 'Activate' button to start the Standerd Manager.

8. Check If the concurrent request are running with newly created concurrent manager

select b.USER_CONCURRENT_QUEUE_NAME,

e.USER_CONCURRENT_PROGRAM_NAME

from fnd_concurrent_processes a,

fnd_concurrent_queues_vl b, fnd_concurrent_requests c,

FND_CONC_REQ_SUMMARY_V d,FND_CONCURRENT_PROGRAMS_TL e

where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and a.CONCURRENT_PROCESS_ID = c.controlling_manager

and c.request_id=d.request_id

and d.concurrent_program_id=e.concurrent_program_id

and c.request_id =<Request_id>;

Execute above script with relevant request_id to get the details.

Monday, November 3, 2014

Send E-Mail through 'UTL_SMTP' package for DB Monitor

For send text Mail

declare
l_mailhost VARCHAR2(64) := '192.168.0.0';
l_from VARCHAR2(64) := 'oracle@test.com';
l_to VARCHAR2(64) := 'someone@gmail.com';
l_subject VARCHAR2(64) := 'Test';
ts VARCHAR2(64);
l_mail_conn UTL_SMTP.connection;

BEGIN

l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);
--/*

UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || '1'|| UTL_TCP.CRLF);

UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);

END;
/

For Send Mail with HTML Table (Tablespace Usage)

DECLARE
l_mailhost VARCHAR2(64) := '192.168.0.0';
l_from VARCHAR2(64) := 'oracle@test.com';
l_to VARCHAR2(64) := 'someone@gmail.com';
l_subject VARCHAR2(64) := 'Tablespace Alert';
I_message VARCHAR2(32000);
l_mail_conn UTL_SMTP.connection;

BEGIN

l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);

I_message:='<center><h2><font color=#500909>Tablespace Usage Details</font></h2></center><br>'||utl_tcp.CRLF;
I_message:=I_message||'<table style="border: solid 0px #cccccc" cellspacing="1" cellpadding="1" width="100%"><tr BGCOLOR=#000099>';
I_message:=I_message||'<td><b><font color=white>Tablespace</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Size(MB)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Used(MB)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Free(MB)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Used(%)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Free(%)</font></td></tr>'||utl_tcp.CRLF;

FOR I in
(
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
100-ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) FREE_PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
having (ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100)) >80)

LOOP

I_message:=I_message||'<tr><td>'||I.tablespace_name||'</td><td>'||I.CUR_SZ_MB||'</td><td>'||I.CUR_USE_MB||'</td><td>'||I.FREE_SPACE_MB||'</td><td>'||I.CUR_PCT_FULL||'</td><td>'||I.FREE_PCT_FULL||'</td></tr>'||utl_tcp.CRLF;

END LOOP;

I_message:=I_message||'</table></body></html>'||utl_tcp.CRLF;



UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn,'content-type: text/html;');
UTL_SMTP.write_data(l_mail_conn,'MIME-Version: 1.0'||utl_tcp.CRLF);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);

UTL_SMTP.write_data(l_mail_conn,'<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; ">'||
utl_tcp.CRLF||'<content="MSHTML 6.00.2800.1276" name=GENERATOR>'||utl_tcp.CRLF||'<HTML><BODY>');
UTL_SMTP.write_data(l_mail_conn,I_message);

UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);

END;

/

For Send mail with multiple HTML Tables (Tablespace Usage and Invalid Objects)

DECLARE
l_mailhost VARCHAR2(64) := '192.168.0.0';
l_from VARCHAR2(64) := 'oracle@test.com';
l_to VARCHAR2(64) := 'someone@gmail.com';
l_subject VARCHAR2(64) := 'Daily Health Monitor - KIA';
I_message VARCHAR2(32000);
l_mail_conn UTL_SMTP.connection;
I_invalid_obj number(10);

BEGIN

l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);

I_message:='<center><h2><font color=#500909>Tablespace Usage Details</font></h2></center><br>'||utl_tcp.CRLF;
I_message:=I_message||'<table style="border: solid 0px #cccccc" cellspacing="1" cellpadding="1" width="100%"><tr BGCOLOR=#000099>';
I_message:=I_message||'<td><b><font color=white>Tablespace</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Size(MB)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Used(MB)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Free(MB)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Used(%)</font></td>';
I_message:=I_message||'<td><b><font color=white>Total Free(%)</font></td></tr>'||utl_tcp.CRLF;

FOR I in
(
select * from
(
SELECT tablespace_name, ROUND(SUM(total_mb)) CUR_SZ_MB, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL,
100-ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) FREE_PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name

union

SELECT tablespace_name,round((tablespace_size/1024)/1024) CUR_SZ_MB,
round(((tablespace_size-free_space)/1024)/1024) CUR_USE_MB, round((free_space/1024)/1024) FREE_SPACE_MB,
round(((((tablespace_size-free_space)/1024)/1024)/((tablespace_size/1024)/1024))*100) CUR_PCT_FULL,
round((((free_space/1024)/1024)/((tablespace_size/1024)/1024))*100) FREE_PCT_FULL
FROM dba_temp_free_space

)
where CUR_PCT_FULL >80
order by CUR_PCT_FULL desc
)

LOOP

I_message:=I_message||'<tr><td>'||I.tablespace_name||'</td><td>'||I.CUR_SZ_MB||'</td><td>'||I.CUR_USE_MB||'</td><td>'||I.FREE_SPACE_MB||'</td><td>'||I.CUR_PCT_FULL||'</td><td>'||I.FREE_PCT_FULL||'</td></tr>'||utl_tcp.CRLF;

END LOOP;
select count(*) into I_invalid_obj from dba_objects where status='INVALID';

I_message:=I_message||'</table><br/><br/><br/>'||utl_tcp.CRLF;
I_message:=I_message||'<center><h2><font color=#500909>Invalid Objects</font></h2></center>'||utl_tcp.CRLF;
I_message:=I_message||'<h3><font color=#D00505>Total Number of Invalid Objects : '||I_invalid_obj||'</font></h3>'||utl_tcp.CRLF;
I_message:=I_message||'<table style="border: solid 0px #cccccc" cellspacing="1" cellpadding="1" width="100%"><tr BGCOLOR=#000099>';
I_message:=I_message||'<td><b><font color=white>Owner</font></td>';
I_message:=I_message||'<td><b><font color=white>Object Name</font></td>';
I_message:=I_message||'<td><b><font color=white>Object Type</font></td>';
I_message:=I_message||'<td><b><font color=white>Status</font></td></tr>'||utl_tcp.CRLF;


FOR II in
(
select owner,object_name,object_type,status from dba_objects where status='INVALID')

LOOP

I_message:=I_message||'<tr><td>'||II.owner||'</td><td>'||II.object_name||'</td><td>'||II.object_type||'</td><td>'||II.status||'</td></tr>'||utl_tcp.CRLF;

END LOOP;

I_message:=I_message||'</table></body></html>'||utl_tcp.CRLF;



UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn,'content-type: text/html;');
UTL_SMTP.write_data(l_mail_conn,'MIME-Version: 1.0'||utl_tcp.CRLF);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || UTL_TCP.CRLF);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);

UTL_SMTP.write_data(l_mail_conn,'<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; ">'||
utl_tcp.CRLF||'<content="MSHTML 6.00.2800.1276" name=GENERATOR>'||utl_tcp.CRLF||'<HTML><BODY>');
UTL_SMTP.write_data(l_mail_conn,I_message);

UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);

END;

/