Oracle Applications APPSLab: June 2008

Saturday, June 28, 2008

Size UNDO tablespace for Automatic Undo Management

Sizing an UNDO tablespace requires three pieces of data. (Note:Overall consideration for peak/heavy vs normal system activity should be taken into account when peforming the calculations.)

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.

The following formula calculates the total number of blocks generated and divides
it by the amount of time monitored, in seconds:

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400)
FROM v$undostat;

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.

The result of the query returns the number of undo blocks per second. This value needs to be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE.

The following query calculates the number of bytes needed:

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), (select block_size as DBS from dba_tablespaces where tablespace_name= (select upper(value) from v$parameter where name = 'undo_tablespace'));

Thursday, June 26, 2008

New Background Processes In 11g

Here are the new background processes in 11g :-

* ACMS (atomic control file to memory service) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure in an Oracle RAC environment.

* DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.

* DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.

* DIAG (diagnosability) process performs diagnostic dumps and executes global oradebug commands.

* EMNC (event monitor coordinator) is the background server process used for database event management and notifications.

* FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows.

FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred.

* GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment.

* KATE performs proxy I/O to an ASM metafile when a disk goes offline.

* MARK marks ASM allocation units as stale following a missed write to an offline disk.

* SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.

* VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority).

Tuesday, June 24, 2008

DataPump - How to Specify a Query

The examples below are based on the following demo schema's:

user SCOTT created with script: $ORACLE_HOME/rdbms/admin/scott.sql
user HR created with script: $ORACLE_HOME/demo/schema/human_resources/hr_main.sql

The Export Data Pump and Import Data Pump examples that are mentioned below are based on the directory my_dir. This directory object needs to refer to an existing directory on the server where the Oracle RDBMS is installed. Example:

-- for Windows platforms:

CONNECT system/manager 
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export'; 
GRANT read,write ON DIRECTORY my_dir TO public; 

-- for Unix platforms:

CONNECT system/manager  
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';  
GRANT read,write ON DIRECTORY my_dir TO public;

1. QUERY in Parameter file.

Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and
from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.

File: expdp_q.par
-----------------
DIRECTORY =&nb p;my_dir
DUMPFILE  = exp_query.dmp
LOGFILE   = exp_query.log
SCHEMAS  &nbs ;= hr, scott
INCLUDE   = TABLE:"IN ('EMP', 'DEPARTMENTS')"
QUERY &n sp;   = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000"
 place following 3 lines on one single line:
QUERY     = hr.departments:"WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)"

-- Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par

Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.

2. QUERY on Command line.

The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

table scott.dept; and
from table scott.emp all employees whose name starts with an 'A'

-- Example Windows platforms:
-- Note that the double quote character needs to be 'escaped'
-- Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\"

-- Example Unix platforms:
-- Note that all special characters need to be 'escaped'

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"

Note that with the original export client two jobs were required:

-- Example Windows platforms: 
-- Place following statement on one single line: 

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp 
QUERY=\"WHERE ename LIKE 'A%'\" 

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

-- Example Unix platforms: 

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \ 
QUERY=\"WHERE ename LIKE \'A\%\'\" 

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

3. QUERY in Oracle Enterprise Manager Database Console.

The QUERY can also be specified in the Oracle Enterprise Manager Database Console. E.g.:

Login to the Oracle Enterprise Manager 10g Database Console, e.g.: http://my_node_name:5500/em
Click on link 'Maintenance'
Under 'Utilities', click on link 'Export to Files'
Answer questions on the following pages.
At 'step 2 of 5' (the page with the Options), click on link 'Show Advanced Options'
At the end of the page, under the QUERY option, click on button 'Add'
At the next page, choose the table name (SCOTT.EMP)
And specify the SELECT statement predicate clause to be applied to tables being exported, e.g.: WHERE ename LIKE 'A%'
Continue with the remaining options, and submit the job.

4. Import Data Pump parameter QUERY.

Similar to previous examples with Export Data Pump, the QUERY parameter can also be used during the import. An example of how to use the QUERY parameter with Import Data Pump:

-- In source database:
-- Export the schema SCOTT:

%expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott

-- In target database: 
-- Import all employees of department 10:

%impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \ 
LOGFILE=impdp_s.log TABLES=emp TABLE_EXISTS_ACTION=append \ 
QUERY=\"WHERE deptno = 10\" CONTENT=data_only

Note that this feature was not available with the original import client (imp). Also note that the parameter TABLE_EXISTS_ACTION=append is used to allow the import into an existing table and that CONTENT=data_only is used to skip importing statistics, indexes, etc.

Copy Database Schemas To A New Database With Same Login Password ?

How to copy database users from one database to another new database and keep the

1. Oracle10g and above: Use Data Pump.

In Oracle10g you can use the Export DataPump and Import DataPump utilities. Example:

-- Step 1: In source database, run a schema level Export Data Pump 
-- job and connect with a user who has the EXP-FULL_DATABASE role: 

% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \
LOGFILE=exp_scott.log SCHEMAS=scott 

-- Step 2: In target database, run a schema level Import Data Pump 
-- job which will also create the user in the target database: 

% impdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \
LOGFILE=imp_scott.log SCHEMAS=scott

Or you can create a logfile (SQLFILE) with all relevant statements. E.g.:

impdp system/manager directory=my_dir dumpfile=exp_scott.dmp logfile=imp_scott.log schemas=scott sqlfile=imp_user.sql

or:

2. In Oracle9i and above: query the Data Dictionary in the source database to obtain the required information to pre-create the user in the target database. Example:

2.1. Obtain the CREATE USER statement in the source database. E.g.:

SET long 200000000
SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;

2.2. Run other queries in the source database to determine which privileges, grants, roles, and tablespace quotas are granted to the users. E.g.:

SET lines 120 pages 100
SELECT * FROM dba_sys_privs WHERE grantee='SCOTT';
SELECT * FROM dba_role_privs WHERE grantee='SCOTT';
SELECT * FROM dba_tab_privs WHERE grantee='SCOTT';
SELECT * FROM dba_ts_quotas WHERE username='SCOTT';

2.3. Create a script file that contains the CREATE USER, CREATE ROLE statements, the GRANT statements, and the ALTER USER statements for tablespace quotas.

2.4. Pre-create the tablespaces for this user with SQL*Plus in the target database. Note that the original CREATE TABLESPACE statement can be obtained in the source database with DBMS_METADATA.GET_DDL. E.g.:

SET long 200000000
SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual;

2.5. Run the script of step 2.3. to create the user in the target database.

2.6. Run a user level export. E.g.:

exp system/manager file=exp_scott.dmp log=exp_scott.log owner=scott

2.7. Import this export dumpfile into the target database. E.g.:

imp system/manager file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott

or:

3. If all users need to be copied into the new target database: use a full database export from the source database and a full database into the target database. Example:

3.1. Run a full database export. E.g.:

exp system/manager file=exp_f.dmp log=exp_f.log full=y
or in Oracle10g:
expdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log full=y

3.2. Pre-create the tablespaces with SQL*Plus in that target database if they have a different directory structure on the target server. If the directory structure on the target is the same as on the source, ensure that this directory structure is in place (import does not create directories when creating tablespaces). Note that the original CREATE TABLESPACE statement can be obtained with dbms_metadata.get_ddl (see the example in step 2.4. above).

3.3. Import the data into the target database with:

imp system/manager file=exp_f.dmp log=imp_f.log full=y
or in Oracle10g:
impdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=impdp_f.log full=y

Friday, June 20, 2008

SQL*Plus command line history completion

The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input

for any other command. This is a really handy addition to SQL*Plus and RMAN .

Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/

Unzip and install the software using the following commands.

gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Run the following commands, or better still append then to the ".bash_profile" of the

oracle software owner.

alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'

You can now start SQL*Plus or RMAN using "sqlplus" and "rman" respectively, and you will have

a basic command history and the current line will be editable using the arrow and delete keys.

Analyze your Oracle Statspack Report

StatspackAnalyzer is a true expert system that seeks to codify expert DBA knowledge and advice against any

STATSPACK or AWR report. The well-structured decision rules of expert Oracle tuning specialists were collected,

quantified and then generalized and validated against real-world STATSPACK and AWR reports.

While no automated tool can fully replicate the decision processes of a human DBA tuning expert, this tool

makes observations about exceptional conditions within the STATSPACK or AWR report. This tool was never

intended to replace the human intuition of an Oracle performance expert, and all observations from

statspackanalyzer should be validated with a human expert.

Thursday, June 19, 2008

Script to Monitor Concurrent Jobs and Hanging Sessions

Here is a monitoring system to monitor all concurrent jobs, concurrent managers and hung sessions every

hour proactively and take appropriate action immediately. It gives the following reports

1. List of Concurrent Jobs that completed with error in last one hour.
2. List of Concurrent Jobs running for more then 1 hour.
3. List of concurrent Jobs completed with Warning in last one hour
4. List of Jobs that are Pending Normal for more than 10 Minutes.
5. List of Hung sessions or Orphan sessions.
6. List of Concurrent managers with Pending Normal jobs.
7. Critical Jobs completed in last one hour with completion time.

SELECT A FROM

(

select 'CONCURRENT PROGRAMS COMPLETED WITH ERROR STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'A' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'A' B,1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'A' B,1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'A' B, 1.4 SRT from fnd_conc_req_summary_v conc

where actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'E'

UNION

select RPAD('-',125,'-') A, 'A' B,1.6 SRT from dual

UNION

SELECT ' ', 'A', 1.8 FROM DUAL

UNION

SELECT ' ', 'A', 1.86 FROM DUAL

-----------------------------------------------------------

UNION

select 'CONCURRENT PROGRAMS COMPLETED WITH WARNING STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'D' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'D' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'D' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'D' B, 1.4 SRT from fnd_conc_req_summary_v conc where

actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'G'

and concurrent_program_id not in (47654,31881,47737)

UNION

select RPAD('-',125,'-') A, 'D' B, 1.8 SRT from dual

-----------------------------------------------------------

UNION

SELECT ' ', 'D', 1.86 FROM DUAL

UNION

SELECT ' ', 'D', 1.88 FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT ARE PENDING NORMAL FOR THE PAST 10 MINUTES ' A, 'E' B,1 SRT

from dual

UNION

select RPAD('-',125,'-') A, 'E' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'E' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'E' B, 2 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - REQUEST_DATE > 0.00694444444444444 AND REQUESTED_START_DATE < SYSDATE

AND PHASE_CODE = 'P' AND STATUS_CODE = 'Q'

UNION

select RPAD('-',125,'-') A, 'E' B, 3 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'E' B, 4.4 SRT FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT STARTED BEFORE '||to_char(sysdate - (1/24),'dd-mon-yyyy hh24:mi:ss')

||' AND ARE STILL RUNNING ' A, 'B' B,4.6 SRT FROM DUAL

UNION

SELECT RPAD('-',125,'-') A, 'B' B, 4.8 SRT FROM DUAL

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'B' B, 4.84 SRT FROM DUAL

UNION

SELECT to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),'-'),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'B' B, 4.86 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - ACTUAL_START_DATE > 0.0416666666666667 AND PHASE_CODE = 'R' AND STATUS_CODE = 'R'

-----------------------------------------------------------------------

UNION

SELECT RPAD('-',125,'-') A, 'C' B, 1.1 SRT FROM DUAL

UNION

SELECT ' ', 'C', 1.2 FROM DUAL

UNION

SELECT ' ', 'C', 5.8 FROM DUAL

UNION

select ' FOLLOWING ARE THE DETAILS OF HUNG OR ORPHAN SESSIONS AS OF '||to_char(sysdate ,

'dd-mon-yyyy hh24:mi:ss') A, 'C' B,1.5 SRT from dual

UNION

select RPAD('-',125,'-') A, 'C' B, 1.6 SRT from dual

UNION

SELECT to_char(rpad(to_char('SID'),5) ||' '||rpad('PROCESS',12)|| ' ' ||rpad('MODULE',10)||' '||rpad('ACTION',

25)||' '||rpad('USERNAME',15)||' '||rpad('PROGRAM',20)||' '||rpad('EVENT',25)) A, 'C' B, 5.2 FROM DUAL

UNION

select to_char(rpad(nvl(to_char(a.sid), ' '),7,' ')||' '||rpad(nvl(a.process, ' '),19,' ')||' '||rpad(nvl(a.module,

' '),10)||' '||rpad(nvl(a.action, ' '),20)||' '||rpad(nvl(a.username, ' '),15)||' '||rpad(nvl(a.program, ' '),20)||' '||

rpad(c.event,25)) A,'C' B, 5.4 SRT from gv$session a, gv$process b, gv$session_Wait c where c.event not

like 'SQL%' and c.event not in ('pmon timer','rdbms ipc message','pipe get','queue messages','smon timer',

'wakeup time manager','PL/SQL lock timer','jobq slave wait','ges remote message','async disk IO','gcs remote

message','PX Deq: reap credit','PX Deq: Execute Reply') and a.paddr=b.addr and a.sid=c.sid and a.inst_id=

c.inst_id and a.inst_id=b.inst_id and a.last_call_et >1800

UNION

select RPAD('-',125,'-') A, 'C' B, 5.6 SRT from dual

UNION

-----------------------------------------------------------------------

SELECT ' ', 'F', 1.01 FROM DUAL

UNION

select 'PENDING NORMAL MANAGERS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A, 'F' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'F' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT MANAGER NAME',35) || rpad('ACTUAL',25)|| ' ' ||rpad('TARGET',

20)||' '||rpad('RUNNING',25)||' '||'PENDING'), 'F' B, 1.2 FROM DUAL

UNION

select to_char (

decode (

fcq.USER_CONCURRENT_QUEUE_NAME,

'XXXXXX: High Workload',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,53),

'XXXXXX: Standard Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: MRP Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: Payroll Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

'XXXXXX: Fast Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Workflow',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Critical Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Inventory Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Conflict Resolution Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

null)

||' '||rpad(TO_CHAR(NVL(FCQ.RUNNING_PROCESSES,0)),30)||' '||

rpad(to_char(nvl(FCQ.MAX_PROCESSES,0)),30) ||' '||rpad(to_char(NVL(running,0)),30) || ' '||

to_char(NVL(PENDING,0))) A, 'F' B, 1.3 SRT

from

apps.fnd_concurrent_queues_vl FCQ,

(SELECT nvl(count(*),0) Running, fcwr.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwr

WHERE fcwr.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwr.phase_code = 'R')

AND fcwr.hold_flag != 'Y'

AND SYSDATE - fcwr.requested_start_date >= 0.00694444444444444

group by fcwr.concurrent_queue_id ) RUNNING ,

( SELECT nvl(count(*),0) Pending, fcwp.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwp

WHERE fcwp.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwp.phase_code = 'P')

AND fcwp.hold_flag != 'Y'

AND sysdate-fcwp.requested_start_date >= 0.00694444444444444

group by fcwp.concurrent_queue_id ) PENDING

WHERE FCQ.concurrent_queue_id=RUNNING.concurrent_queue_id(+)

AND FCQ.concurrent_queue_id=PENDING.concurrent_queue_id(+)

AND fcQ.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

UNION

select RPAD('-',125,'-') A, 'F' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'F' B, 1.5 SRT FROM DUAL

UNION

-----------------------------------------------------------------------

SELECT ' ', 'G', 1.01 FROM DUAL

UNION

select 'CRITICAL PROGRAMS STATUS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A , 'G' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'G' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT PROGRAM NAME',55) || rpad('AVG TIME',20)|| ' ' ||rpad

('CURR MAX TIME',16)||' '|| 'REQUEST_ID'), 'G' B, 1.1 FROM DUAL

UNION

SELECT to_char (

decode (PROGRAM_NAME,

'AutoCreate Configuration Items',RPAD(PROGRAM_NAME,70),

'Memory-based Snapshot',RPAD(PROGRAM_NAME,71),

'Order Import',RPAD(PROGRAM_NAME,80),

'Workflow Background Process',RPAD(PROGRAM_NAME,69),

PROGRAM_NAME) ||' '||

rpad(TO_CHAR(STATIC.AVG_TIME),25) || ' '||

rpad(TO_CHAR(DYNAMIC.CURR_MAX_TIME),25) || ' '||

to_char(NVL(REQUEST_ID,NULL))) A, 'G' B, 1.2 SRT

FROM

(SELECT

CONCURRENT_PROGRAM_ID,

USER_CONCURRENT_PROGRAM_NAME,

REQUEST_ID,

ROUND((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,0) CURR_MAX_TIME

FROM APPS.FND_CONC_REQ_SUMMARY_V fcr

WHERE CONCURRENT_PROGRAM_ID IN (36888,

48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

AND REQUEST_ID IN (

SELECT MAX(REQUEST_ID) FROM APPS.FND_CONC_REQ_SUMMARY_V fcr WHERE CONCURRENT_PROGRAM_ID

IN (36888,48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

GROUP BY CONCURRENT_PROGRAM_ID) ) DYNAMIC ,

(select distinct CONCURRENT_PROGRAM_ID "CONCURRENT_PROGRAM_ID",

USER_CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",

DECODE ( CONCURRENT_PROGRAM_ID,36888,39442,33137,31881,10,NULL) AVG_TIME

FROM APPS.FND_CONCURRENT_PROGRAMS_TL fcr

WHERE CONCURRENT_PROGRAM_ID IN

(36888,39442,33137,31881)

AND LANGUAGE='US' ) STATIC

WHERE DYNAMIC.CONCURRENT_PROGRAM_ID(+)=STATIC.CONCURRENT_PROGRAM_ID

UNION

select RPAD('-',125,'-') A, 'G' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'G' B, 1.5 SRT FROM DUAL

-----------------------------------------------------------------------

) TEMP

ORDER BY B, SRT, A

Prevent Concurrent Requests Executing on New Cloned Environment

To prevent concurrent requests executing on a newly cloned environment 2 steps will be required.

1) Prevent Concurrent Managers from starting as part of the Clone

2) Change data concerning concurrent requests so they do not execute when the concurrent managers start.

To prevent Applications Services (including Concurrent Managers) from starting automatically during Rapid Clone an enhancement request has been raised to see if this can be considered an option during the clone.

Until this feature is available there is the following workaround.

1. Edit the $COMMON_TOP/clone/bin/adcfgclone.pl

2. Go to the end of the file

3. Change the following lines:-

print "\n Starting application Services for $s_dbSid:\n";
print "Running:\n";
print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/<appspwd>\n");
system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

TO:

print "\nNOT Starting application Services for $s_dbSid:\n";
#print "Running:\n";
#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/<appspwd>\n");
#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

4. Run "perl adcfgclone.pl appsTier" as normal.

The services will not start Automatically when the clone completes allowing data to be changed safely.

There is a need to change 2 sets of concurrent requests to prevent execution

a) Terminate 'Running' Requests
b) Set Pending jobs to 'On Hold'

a) Set Terminating or Running to Completed/Terminated
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code ='T'
OR phase_code = 'R'
/

b) Place Pending/(Normal/Standby) to On Hold
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
/

Once these changes have been committed then the Application services including concurrent manager can be restarted and the concurrent jobs will not be executed.

Queries Related to Concurrent Requests in 11i Applications

As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.
Note: These queries needs to be run from APPS schema.

Scheduled concurrent requests

Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).

The below query will return all the concurrent requests which are scheduled using any of the above methods:

SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' || cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, cr.resubmit_interval, 
NVL2 (cr.resubmit_interval, 
'PERIODICALLY', 
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') 
) schedule_type, 
DECODE (NVL2 (cr.resubmit_interval, 
'PERIODICALLY', 
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') 
), 
'PERIODICALLY', 'EVERY ' 
|| cr.resubmit_interval 
|| ' ' 
|| cr.resubmit_interval_unit_code 
|| ' FROM ' 
|| cr.resubmit_interval_type_code 
|| ' OF PREV RUN', 
'ONCE', 'AT :' 
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 
'EVERY: ' || fcr.class_info 
) schedule, 
fu.user_name, requested_start_date 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu, 
apps.fnd_conc_release_classes fcr 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fcr.release_class_id(+) = cr.release_class_id 
AND fcr.application_id(+) = cr.release_class_app_id;

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.

Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.

Note: This query will return even though the request was submitted using a different responsibility.

SELECT request_id, NAME, argument_text, user_name 
FROM (SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' || cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, fu.user_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fu.user_name NOT LIKE 'PPG%') t1 
WHERE EXISTS ( 
SELECT 1 
FROM (SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' 
|| cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, fu.user_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = 
cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fu.user_name NOT LIKE 'PPG%') t2 
WHERE t1.NAME = t2.NAME 
AND t1.argument_text = t2.argument_text 
AND t1.user_name = t2.user_name 
GROUP BY NAME, argument_text, user_name 
HAVING COUNT (*) > 1) 
ORDER BY user_name, NAME

Average pending time per request

This is a very useful query to check the performance of the concurrent managers.

Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, 
concurrent_queue_name, 
(SUM ( ( actual_start_date 
- (CASE 
WHEN requested_start_date > request_date 
THEN requested_start_date 
ELSE request_date 
END 
) 
) 
* 24 
* 60 
* 60 
) 
) 
/ COUNT (*) "Wait_Time_per_Req_in_Secs" 
FROM apps.fnd_concurrent_requests cr, 
apps.fnd_concurrent_processes fcp, 
apps.fnd_concurrent_queues fcq 
WHERE cr.phase_code = 'C' 
AND cr.actual_start_date IS NOT NULL 
AND cr.requested_start_date IS NOT NULL 
AND cr.controlling_manager = fcp.concurrent_process_id 
AND fcp.queue_application_id = fcq.application_id 
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id 
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name 
ORDER BY 2

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.

SELECT user_concurrent_program_name, user_concurrent_queue_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_queue_content cqc, 
apps.fnd_concurrent_queues_tl cq 
WHERE cqc.type_application_id(+) = cp.application_id 
AND cqc.type_id(+) = cp.concurrent_program_id 
AND cqc.type_code(+) = 'P' 
AND cqc.include_flag(+) = 'I' 
AND cp.LANGUAGE = 'US' 
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id 
AND NVL (cqc.queue_application_id, 0) = cq.application_id 
AND cq.LANGUAGE = 'US'

To see all the pending / Running requests per each manager wise

SELECT request_id, phase_code, status_code, user_name, 
user_concurrent_queue_name 
FROM apps.fnd_concurrent_worker_requests cwr, 
apps.fnd_concurrent_queues_tl cq, 
apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') 
AND cwr.hold_flag != 'Y' 
AND cwr.requested_start_date <= SYSDATE 
AND cwr.concurrent_queue_id = cq.concurrent_queue_id 
AND cwr.queue_application_id = cq.application_id 
AND cq.LANGUAGE = 'US' 
AND cwr.requested_by = fu.user_id 
ORDER BY 5

Note: The same information can be seen in Administer Concurrent Manager form for each manager.

Checking the incompatibilities between the programs

The below query can be used to find all incompatibilities in an application instance.

SELECT a2.application_name, a1.user_concurrent_program_name, 
DECODE (running_type, 
'P', 'Program', 
'S', 'Request set', 
'UNKNOWN' 
) "Type", 
b2.application_name "Incompatible App", 
b1.user_concurrent_program_name "Incompatible_Prog", 
DECODE (to_run_type, 
'P', 'Program', 
'S', 'Request set', 
'UNKNOWN' 
) incompatible_type 
FROM apps.fnd_concurrent_program_serial cps, 
apps.fnd_concurrent_programs_tl a1, 
apps.fnd_concurrent_programs_tl b1, 
apps.fnd_application_tl a2, 
apps.fnd_application_tl b2 
WHERE a1.application_id = cps.running_application_id 
AND a1.concurrent_program_id = cps.running_concurrent_program_id 
AND a2.application_id = cps.running_application_id 
AND b1.application_id = cps.to_run_application_id 
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id 
AND b2.application_id = cps.to_run_application_id 
AND a1.language = 'US' 
AND a2.language = 'US' 
AND b1.language = 'US' 
AND b2.language = 'US'

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.

Wednesday, June 18, 2008

Reference : Unix, Solaris, Linux, AIX, HP-UX Commands

As you know, there exists vast array of commands that enable you to do a multitude of tasks.

Depending on what you need to accomplish, you use only a certain subset of these commands.

These subsets differ from user to user and from need to need. However, there are a commands

that you commonly use. You need these commands either to answer your own questions or to

provide answers to the queries of the support professionals.

Linux Command Reference :

http://www.oracle.com/technology/pub/articles/calish_file_commands.html

http://www.oracle.com/technology/pub/articles/advanced-linux-commands/part1.html

http://www.oracle.com/technology/pub/articles/advanced-linux-commands/part2.html

Common Linux Commands Pocket Guide

HP-UX Command Reference :

http://docs.hp.com/en/hpuxman_pages.html

AIX Command Reference :

http://www.ibm.com/developerworks/aix/library/au-dutta_cmds.html

Solaris Command Reference :

http://www.sun.com/bigadmin/shellme

Unix Command Reference :

Common UNIX Commands Pocket Guide

Command Reference for all Operating System :

http://www.tomshardware.com/ucg

Comparative Study of Commands in all Platforms :

http://www.unixguide.net/unixguide.shtml

Admittedly, a list such as this can be helpful in quickly answering some of your own questions. However,

it does not cover everything that you might need. You can extend the usefulness of such a list by adding

other commands that answer additional questions not addressed here.

Create a User Event Trace in 11i / R12

A user event trace is very handy for tracing sql operations to debug various issues. The benefit of a user event trace is that it is linked to a specific user so that only code run by this user is traced. This makes diagnosis easier when compared to similar tracing methods at the database level where all user calls are traced.

Step 1
Login to Oracle Applications and select the System Administrator responsibility.
Choose Profile - System

Step 2
In the find profile field select the user which you wish to trace. In the profile field enter 'Initialization SQL Statement - Custom"
Select find

Step 3
In the find profile results form copy and paste the following into the 'user' field. Do not update the site level field.

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');end;

Note: copy and paste the above as one line. Failure to paste the values properly or pasting the values with incorrect syntax will result in this user not being able to login

Step 4
Save the profile option change. Stop and start the java virtual machine for the change to take effect
11i: use adapcctl.sh
r12: use adoacorectl.sh

Step 5
Login to the application and reproduce the issue. Then quickly log off. Try and avoid any un-necessary keystrokes as this simply makes the log files larger and the issue hard to pinpoint in the logs.

Step 6
login to unix/windows as the oracle user. Navigate to the user dump destination which us usually $ORACLE_HOME/admin/<context>/udump
You will see a series of trace files generated during the time of your issue reproduction. Tkprof all of the generated files and upload both the raw and tkprof files to your service request

You can also check the user dump destination via the following:
sql> show parameter user_dump_dest;

Wednesday, June 11, 2008

Versions of APPS Technology Stack Components

How to find the versions of technology stack components (Forms, iAS, Framework, JDK, OJSP, etc.)?

Establish the needed environment parameters by sourcing the Applications environment file as the owner of the application tier file system.

Ensure "APPLRGF" variable is set in environment. If not, set it to the same value as "APPLTMP".

Navigate to <FND_TOP>/patch/115/bin. Run the utility as follows:

Operation System	Command Line
Unix or Linux	perl $FND_TOP/patch/115/bin/TXKScript.pl \ -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP \ -contextfile=$CONTEXT_FILE \ -appspass=apps \ -outfile=$APPLTMP/Report_Inventory.html
Windows	perl %FND_TOP%\patch\115\bin\TXKScript.pl -script=%FND_TOP%\patch\115\bin\txkInventory.pl -txktop=%APPLTMP% -contextfile=%CONTEXT_FILE% -appspass=apps -outfile=%APPLTMP%\Report_Inventory.html

Where:

txktop	Temporary working directory use by Perl Modules. Should not be an empty string.
contextfile	Location of the context file. If not passed, default is picked from the environment.
appspass	APPS schema password. If not passed, default password is used.
outfile	Location for the report being generated. If not passed, the default location is <APPLTMP>/TXK

To generate the report in text format, the parameter "-reporttype=text" needs to be passed to the above commands. For example:

outfile=$APPLTMP/Report_Inventory.html -reporttype=text

Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script
Upload this file to Oracle Support for review.

Friday, June 6, 2008

Oracle Apps Interview Questions - Part I

Here are some of the Oracle Apps Interview Questions. These Questions cover from Operating

System Installation, Pre-requisites for Apps, Apps Installation, Architecture, File System. In the

next part of Questions , I will cover more into advanced apps topics. For any doubts you may post

comment so that i could help you.

1) How to Check the memory of the system while the time of Linux Installation ?
2) How to assign hostname to a node ?
3) How to assign IP to a node ?
4) which are the files where kernel settings and security limits are stored ?
5) How to check the swap space and physical memory ?
6) How to do ftp from one node to another node ? Give Eg
7) How to do copy between two machines ? Give Eg
8) Give steps to enable ftp and telnet services ?
9) How to do confiugre nfs server and do a nfs mount from the client machine ?
10) what's the use of TOP, ps commands ?
11) How to create user and groups in Linux ?
12) How to see if user and group already exists ?
13) How to check the release version of OS ?
14) How to check the kernel version of OS ?
15) How to check OS is 32bit/64bit?
16) How will you check whether required os level software has installed?
17) what is the command to check required rpms are installed?
18) How will install and upgrade rpms in linux?
19) What is oraInventory?default location for aix , Linux ?
20) what is oratab ?
21) How can you check the groups to a user belong ?
22) What is configuration file?(config.txt) and where is the default location
23) What is rapid install?
24) Explain Single Node and Multi Node installation ? ( Exp preinstall and postinstall tasks )
25) Explain single user and multi user installation ?
26) What is difference between express install and advanced install ?
27) What is difference between fresh database and vision database install types ?
28) What is minimum / approx. disk requirement for 11.5.10 for vis and fresh and stage ?
29) What is staging area ? How you set up staging area ?
30) Is it possible to install apps without staging area ?
31) How will check perl, java versions?
32) where is location of staging logs ?
33) How can you clear the system after an incomplete installation ?
34) What's the difference between ORACLE_BASE and ORACLE_HOME
35) what is default port pool? what is default range and till what range it support?
36) If some of pre-install test has failed what will you do?
37) If the installtion has terminated before completion What will you do?
38) What are post installtion tests done by rapidwiz?
39) How will you check rapidwiz version?
40) Describe how will you do multinode installtion?
41) What are things will be checked during post installation tests?
42) What are required in post installtion steps additionally?
43) Where is the location of the Rapidwiz logs?
44) How could you Install technology stack alone?
45) What are the software require to maintain oracle application on windows ?
46) What are the Technology Stack Components?
47) What are the software required for Install Oracle Application on unix or linux machines ?
48) How can you check the version of Oracle Apps ?
49) What is Oracle Apps ?
50) Explain three tier architecture in Oracle Apps ( Including login workflow and middle tier services )
51) What is OATM ?
52) What is Oracle Jinitiator ? How can you check the jinitiator version ?
53) Where is location of Oracle Apache Cache and modplsql Cache ?
54) How many oracle Homes are there in 11i and what are they?
55) What is oracle applications technology layer ? what and why are these compnonents used for ?
56) what is Oracle Application Object Library ? How it differs for end user, developer and system admin ?
57) what is OAM ? What is OAM Login URL ?
58) What is Oracle Applications URL and Ebusiness Suite Apps Login URL ?
59) Which is the script location is Oracle apps for middle tier and db tier ?
60) where is the concurrent manger log and out location ?
61) what are environment files ? what are the different types of env files, and locations on both tiers ?
62) what are context files and locations for both tiers
63) what is APPLPTMP and APPLTMP ?
64) what are they key environment file parameters ?
65) which is the script used to start and stop apache and concurrent manager ? Tell the steps
66) Brief out the file system for Oracle Applications Ebusiness Suite ( and explain each directory structure,
      its contents )
67) Whats the difference between APPS,APPLSYS,APPLSYSPUB users ?
68) what are the diff types of users available is oracle apps ?
69) What is Multiple Organization ? How can you check if MO is enabled ?
70) How can you check if multiple languages are installed with oracle apps ?
71) How can you check if an oracle apps installations in multi node or single node and in which
      node each services are running ?
72) How can you check how many database are up and running in both linux and windows machines
73) How can you check if concurrent manager and apache server is up and running
74) Where is apache access log and error log location
75) Where is the location of Oracle Alert logfiles and Trace files ( Give complete path )

Thursday, June 5, 2008

RMAN - Duplicate Database on the same host

Primary DB : ORCL

Clone DB : AUX

Production Database should be archive enabled.

Startup mount;

alter database archivelog;

alter database open;

archive log list;

Recovery catalog for RMAN

Creating the Recovery Catalog Owner

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant

it the recovery_catalog_owner role. Look at this example:

% sqlplus '/ as sysdba'

SQL> CREATE USER rman IDENTIFIED BY rman
     DEFAULT TABLESPACE tools
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON tools;

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman

Creating the Recovery Catalog

% rman catalog rman/rman@ORCL

RMAN> CREATE CATALOG;

Registering the target database

% rman TARGET / CATALOG rman/rman@ORCL

RMAN> REGISTER DATABASE;

Reference : RMAN: How to Query the RMAN Recovery Catalog ( Note:98342.1 )

Example Source Listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /newpart//product/10.2.0/)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = ORCL)

(ORACLE_HOME = /newpart//product/10.2.0)

(SID_NAME = ORCL)

)

(SID_DESC =

(GLOBAL_DBNAME = AUX)

(ORACLE_HOME = /newpart//product/10.2.0)

(SID_NAME = AUX)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

Example Source tnsnames.ora

AUX =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = AUX)(UR=A)

)

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

Note : Make use of netca and netmgr to configure listener and tnsnames

# Find Production Database Files:

SQL> select name from v$datafile;

Create the Auxiliary Database directories needed

cd $ORACLE_HOME/dbs

create parameter file initAUX.ora

db_file_name_convert = ('/old/path1', '/new/path1',

'/old/path2', '/new/path2',

'/old/path3', '/new/path3')

log_file_name_convert = ('/old/path1', '/new/path1',

'/old/path2', '/new/path2',

'/old/path3', '/new/path3')

eg:-

db_name = aux

db_block_size = 8192

compatible = 10.2.0.1.0

remote_login_passwordfile = exclusive

control_files = ('/newpart/oradata/aux/control01.ctl',

'/newpart/oradata/aux/control02.ctl')

db_file_name_convert = ('/newpart/oradata/orcl',

'/newpart/oradata/aux')

log_file_name_convert = ('/newpart/oradata/orcl',

'/newpart/oradata/aux')

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

# create a passwordfile for remote connections as sysdba

% orapwd password=<sys_pwd> file=orapwAUX

% sqlplus /nolog

SQL> connect / as sysdba

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora

SQL> exit

Start the Duplication

ORACLE_SID=AUX; export ORACLE_SID # ksh

sqlplus /nolog

SQL> connect / as sysdba

Connected to an idle instance

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initAUX.ora

SQL> exit

# Set your SID back to the TARGET for duplication.

> rman trace.log

Recovery Manager: Release 10.2.0.1.0 - Production

RMAN> connect target

connected to target database: V10GREL4 (DBID=2510891965)

RMAN>backup database;

RMAN>sql 'alter system switch logfile';

RMAN> connect auxiliary sys/pwd@AUX

connected to auxiliary database: AUX (not mounted)

RMAN> duplicate target database to AUX device type disk;

Once this is done, login to duplicate database with alter database open resetlogs.

RMAN - Duplicate Database on a New host

How To Create A Production Duplicate On a New Host using RMAN

Primary Database SID:       ORCL
Duplicate Database SID:    AUX
RMAN Catalog SID:          RMAN

Backup of the primary database.

Host A (Target)

# export ORACLE_SID=ORCL

# rman target=/ catalog=rman/rman@ORCL

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}

This command will perform a full database backup including archivelogs

and the current controlfile.

Host B (Aux)

Making the backup available for the duplicate process.

If your backup resides on disk you will need to copy this back up from

host A to host B. Ensure you place it in the same directory as where it

was created.

RMAN> list backup;

Create same directory of host b and give appropriate permissions for

the oracle user.

Create the pfile initAUX.ora parameter file in the $ORACLE_HOME/dbs

directory for the auxiliary database.

----------------------------------------------------------------------

db_name = aux

db_block_size = 8192

compatible = 10.2.0.1.0

remote_login_passwordfile = exclusive

control_files = ('/d02/oradata/aux/control01.ctl')

db_file_name_convert = ('/newpart/oradata/orcl',

'/d02/oradata/aux')

log_file_name_convert = ('/newpart/oradata/orcl',

'/d02/oradata/aux')

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

------------------------------------------------------------------------------

Following the creation of the initAUX.ora startup nomount the auxiliary instance

export ORACLE_SID=AUX

sqlplus '/as sysdba'

startup nomount;

Ensuring SQL*NET connections to primary database and RMAN catalog are working

Host B(AUX)

sqlplus 'sys/oracle@PROD as sysdba'

sqlplus rman/rman@PROD (not mandatory)

Add tnsnames.ora entry - Eg:

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test.oneapps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

Prepare RMAN duplicate script

run {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}

Save it as dup.sql

Execute the RMAN script

Start RMAN, connect to the production target, the catalog instance and also

the auxiliary clone. Run the RMAN duplicate script as shown below. Before

doing this ensure that the Oracle SID environment variable is set to the duplicate

clone database.

# export ORACLE_SID=AUX

# rman target sys/pwd@ORCL catalog rman/rman@ORCL auxiliary /

RMAN> @dup.sql

After this, login to aux database and alter database open with resetlogs option.

Personalizing The Login Page

1. Attributes of the login page

It is possible to control the display of some attributes of the login page, for instance user name or password hints, language switchers, forgot password link, corporate policy message, etc.

For this, you need to set the profile option 'Local Login Mask' (FND_SSO_LOCAL_LOGIN_MASK) with a number being the sum of the mask values described in the table below:

Description	Mask value
Hint for Username (USERNAME_HINT)	01
Hint for Password (PASSWORD_HINT)	02
Cancel button (CANCEL_BUTTON)	04
Forgot Password link (FORGOT_PASSWORD_URL)	08
Registration link (REGISTER_URL)	16
Language Images (LANGUAGE_IMAGES)	32
Corporate Policy Message (SARBANES_OXLEY_TEXT)	64

* For instance the value 32 (default) displays only the language icons and value 127 will show all the attributes on the page.

* The change takes effect immediately after re-login to E-Business Suite.

2. Message texts

It is possible to modify or add text on the login page by changing the value of some messages.
The following table shows the related messages and their default value:

Description	Default value
FND_SSO_WELCOME	Login
FND_SSO_EBIZ_SUITE	E-Business Suite
FND_SSO_COPYRIGHT_TEXT	Copyright (c) 2007, Oracle. All rights reserved.
FND_SSO_SARBANES_OXLEY_TEXT	Corporate Policy Message

To change the value of a message:

1. Go to "Application Developer" responsibility 2. Select "Messages" from the menu 3. Query the message name and then enter your message text in the

"Current Message Text" field 4. Save changes and exit 5. Clear cache and bounce Apache to see the change

* Note that some messages can be used elsewhere that in the login page and can be updated by a patch

* If you want to change also the default branding 'E-Business Suite' on other pages, to match the text on the login page (defined by FND_SSO_EBIZ_SUITE message), then follow the steps below:

a. Login with System Administrator responsibility
b. Navigate: Application ---> Function
c. Query the function 'FWK_HOMEPAGE_BRAND'
d. Replace the value of the 'User Function Name' with the desired text
c. Logout and login to see the change (you shouldn't need to clear caches and bounce apache)

* The 'FND_SSO_SARBANES_OXLEY_TEXT' message is only displayed when the mask
value 64 is added to the profile option 'Local Login Mask'.

3. Corporate branding logo

The Oracle logo is displayed on various E-Business Suite pages and can be changed by setting the 'Corporate Branding Image for Oracle Applications' (FND_CORPORATE_BRANDING_IMAGE) profile option to the full path name of an image file (.gif) that contains your corporate image.

However it is not possible to use this method for AppsLocalLogin.jsp since it is hard coded with the Oracle logo image file 'FNDSSCORP.gif'.
The non supported solution consists in:

 1. Go to the $OA_HTML directory
2. Backup the AppsLocalLogin.jsp file
3. Copy your own corporate branding image under $OA_MEDIA directory
4. Edit the AppsLocalLogin.jsp file :

  from :
 ImageBean imgBean1 = new ImageBean("/OA_MEDIA/FNDSSCORP.gif", FND_ORACLE_LOGO);
   to :
 ImageBean imgBean1 = new ImageBean("/OA_MEDIA/<your image file name>", FND_ORACLE_LOGO);
 5. Clear caches and bounce Apache to see the change

4. Other modifications

AppsLocalLogin.jsp being a Java Server Page you can change the HTML or Java code (for instance with JDeveloper), create you own messages in the Messages Dictionary thru AOL responsibility, etc., if you want to add other customizations. This is considered a customization and thus not supported by Oracle. If you apply patches replacing AppsLocalLogin.jsp the file will be overwritten.

Blog Archive

March (3)
May (2)
December (1)
March (1)
September (2)
June (2)
March (4)
June (2)
February (5)
August (1)
July (1)
June (23)
May (3)
April (11)
March (6)
February (1)
January (5)
November (12)
October (18)