Oracle Application DBA Information

Wednesday, 5 October 2016

Check Unlock Schema In EBS R12 & Gather Stats

Unlock Schema
--------------------------------------------------------------------------------
$ run Application Env
$ sqlplus apps/apps

1. run this query for unlock schema checking

SQL> select owner, table_name, stattype_locked
2 from dba_tab_statistics
3 where stattype_locked is not null;

OWNER TABLE_NAME STATT ------------------------------ ------------------------------ ----- SYS SYS$SERVICE_METRICS_TAB ALL SYS KUPC$DATAPUMP_QUETAB ALL SYS AQ_PROP_TABLE ALL SYS AQ$_MEM_MC ALL SYS ALERT_QT ALL SYS SCHEDULER_FILEWATCHER_QT ALL SYS SCHEDULER$_REMDB_JOBQTAB ALL SYS SCHEDULER$_EVENT_QTAB ALL SYS AQ_EVENT_TABLE ALL SYSTEM TBLMIG_MSG_QTAB ALL SYSTEM DEF$_AQERROR ALL SYSTEM DEF$_AQCALL ALL AMV AMV_MATCHING_QUEUE_TBL ALL APPLSYS AQ$_WF_CONTROL_P ALL APPLSYS FND_CP_TM_AQTBL ALL APPLSYS WF_NOTIFICATION_OUT ALL XDP XDP_FA_QTAB ALL
XNP XNP_OUT_MSG_QTAB ALL XNP XNP_IN_TMR_QTAB ALL XNP XNP_IN_MSG_QTAB ALL XNP XNP_IN_EVT_QTAB ALL SYS WRH$_FILESTATXS ALL
79 rows selected.

SQL> set lines 500 pages 5000
SQL> run
1 select 'exec dbms_stats.unlock_table_stats('||''''||owner||''''||','||''''||table_name||''''||')'||';'
2* from dba_tab_statistics where stattype_locked is not null

'EXECDBMS_STATS.UNLOCK_TABLE_STATS('||''''||OWNER||''''||','||''''||TABLE_NAME||''''||')'||';' ------------------------------------------------------------------------------------------------------ exec dbms_stats.unlock_table_stats('SYS','SYS$SERVICE_METRICS_TAB');
exec dbms_stats.unlock_table_stats('SYS','KUPC$DATAPUMP_QUETAB');
exec dbms_stats.unlock_table_stats('SYS','AQ_PROP_TABLE');
exec dbms_stats.unlock_table_stats('SYS','AQ$_MEM_MC');
exec dbms_stats.unlock_table_stats('SYS','ALERT_QT');
exec dbms_stats.unlock_table_stats('SYS','SCHEDULER_FILEWATCHER_QT');
exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_REMDB_JOBQTAB');
exec dbms_stats.unlock_table_stats('SYS','SCHEDULER$_EVENT_QTAB');
exec dbms_stats.unlock_table_stats('SYS','AQ_EVENT_TABLE');
exec dbms_stats.unlock_table_stats('SYSTEM','TBLMIG_MSG_QTAB');
exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQERROR');
exec dbms_stats.unlock_table_stats('SYSTEM','DEF$_AQCALL');
exec dbms_stats.unlock_table_stats('AMV','AMV_MATCHING_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_TM_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_SMTP_O_1_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_OPP_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_TM_RET_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_SAMPLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_JMS_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JAVA_ERROR');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JAVA_DEFERRED');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_REPLAY_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_REPLAY_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_OUTBOUND_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_OUT');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_JMS_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_INBOUND_TABLE');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_IN');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_ERROR');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED_TABLE_M');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_CONTROL');
exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED');
exec dbms_stats.unlock_table_stats('APPLSYS','WF_WS_JMS_OUT');
exec dbms_stats.unlock_table_stats('CCT','CCT_QDE_RESP_Q_TBL');
exec dbms_stats.unlock_table_stats('CCT','CCT_IBME_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IBU','IBU_SUBS_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_STAGING_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_EXCEP_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('JTF','JTF_DEF_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('OKC','OKC_AQ_EV_TAB');
exec dbms_stats.unlock_table_stats('ODM','DMS_QUEUE_TABLE');
exec dbms_stats.unlock_table_stats('APPS','FND_CP_GSM_OPP_AQTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_IH_BULK_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_PF_LOGGING_TABLE');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_Q2_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_Q1_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_M_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_MP_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_ER_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_B_QTBL');
exec dbms_stats.unlock_table_stats('APPS','JTF_FM_RAPID_BP_QTBL');
exec dbms_stats.unlock_table_stats('APPS','ECX_OUTQUEUE');
exec dbms_stats.unlock_table_stats('APPS','ECX_IN_OAG_Q_TABLE');
exec dbms_stats.unlock_table_stats('APPS','ECX_INQUEUE');
exec dbms_stats.unlock_table_stats('APPS','CZ_MESSAGE_QENTRIES');
exec dbms_stats.unlock_table_stats('AR','AR_REV_REC_QT');
exec dbms_stats.unlock_table_stats('ASO','ASO_ORDER_FEEDBACK_T');
exec dbms_stats.unlock_table_stats('CS','CS_SERVICE_REQUEST_OQT');
exec dbms_stats.unlock_table_stats('CS','CS_SERVICE_REQUEST_IQT');
exec dbms_stats.unlock_table_stats('IEM','IEMP_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IEM','IEMPP_QUEUE_TBL');
exec dbms_stats.unlock_table_stats('IEO','IEO_ICSM_QUEUE_TBL_2');
exec dbms_stats.unlock_table_stats('IEO','IEO_ICSM_QUEUE_TBL_1');
exec dbms_stats.unlock_table_stats('XDP','XDP_WORKITEM_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_WF_CHANNEL_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_PENDING_ORDER_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_ORDER_PROCESSOR_QTAB');
exec dbms_stats.unlock_table_stats('XDP','XDP_FA_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_OUT_MSG_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_TMR_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_MSG_QTAB');
exec dbms_stats.unlock_table_stats('XNP','XNP_IN_EVT_QTAB');
exec dbms_stats.unlock_table_stats('SYS','WRH$_FILESTATXS');

79 rows selected.

Gather Schema
--------------------------------------------------------------------------------
*/
FND_STATS.GATHER_SCHEMA_STATS (
schemaname VARCHAR2,
estimate_percent NUMBER DEFAULT NULL,
degree NUMBER DEFAULT NULL,
internal_flag NUMBER DEFAULT NULL,
Errors OUT Error_Out,
request_id NUMBER default null,
hmode VARCHAR2 default 'LASTRUN',
options in VARCHAR2 default 'GATHER',
modpercent NUMBER default 10,
invalidate VARCHAR2 default 'Y'
);

/*
$sqlplus apps/apps

Sql> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10,32,'NOBACKUP', NULL,'LASTRUN','GATHER AUTO', 10, 'N');

How to check Whether Period Of /AP/GL/FA/PO/AR IS Closed

SELECT (SELECT sob.NAME

FROM gl.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",
a.period_name "Period_Name", a.period_num "Period_Num",
a.gl_status "GL_Status", b.po_status "PO_Status",
c.ap_status "AP_Status", d.ar_status "AR_Status",
e.fa_status "FA_Status"
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) gl_status,
set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) po_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ap_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ar_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, 'Open',
'Closed'
) fa_status,fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id=b.set_of_books_id(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1;

2. To Check Period Status (open/close) of different Oracle Apps modules

SELECT ROWID,
       (SELECT application_short_name
        FROM fnd_application fa
        WHERE fa.application_id = gps.application_id)
          application,
       (SELECT name
        FROM gl_sets_of_books gsp
        WHERE gsp.set_of_books_id = gps.set_of_books_id)
          "setofbookname",
       period_name,
       closing_status,
       DECODE (gps.closing_status,
               'O',
               'Open',
               'C',
               'Closed',
               'F',
               'Future',
               'N',
               'Never'
       )
          status,
       period_num,
       period_year,
       start_date,
       end_date
FROM gl_period_statuses gps
WHERE period_year = '2013'
ORDER BY period_year DESC, period_num DESC;

3. SQL query to find Open/Close Periods in Oracle Apps R12

Query to find the Gl Set of Books.

To find SET_OF_BOOKS_ID:

SELECT * FROM gl_sets_of_books

Inventory

SELECT DISTINCT opu.name AS operating_unit
, per.organization_id AS inv_org_id
, par.organization_code AS inv_org_code
, org1.name AS Organization_name
, per.period_name
, per.period_year
, flv.meaning AS status
FROM org_acct_periods per
, fnd_lookup_values flv
, mtl_parameters par
, hr_all_organization_units org1
, hr_all_organization_units_tl otl
, hr_organization_information org2
, hr_organization_information org3
, hr_operating_units opu
WHERE 1 = 1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) =
DECODE (
NVL (per.period_close_date, SYSDATE)
, per.period_close_date, DECODE (
per.open_flag
, 'N', DECODE (summarized_flag
, 'N', 65
, 66)
, 'Y', 4
, 'P', 2
, 4)
, 3)
AND flv.language = 'US'
AND UPPER (flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
AND PER.PERIOD_NAME = '&Period_Name'
and opu.set_of_books_id = '&SOB'
ORDER BY opu.name
, per.organization_id;

To Check whether Periods of AP/AR/GL/FA/PO is closed?

SELECT DISTINCT (SELECT sob.NAME
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id)
"SOB_Name"
, a.period_name "Period_Name"
, a.period_num "Period_Num"
, a.gl_status "GL_Status"
, b.po_status "PO_Status"
, c.ap_status "AP_Status"
, d.ar_status "AR_Status"
, e.fa_status "FA_Status"
FROM (SELECT period_name
, period_num
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
gl_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') a
, (SELECT period_name
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
po_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') b
, (SELECT period_name
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
ap_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') c
, (SELECT period_name
, DECODE (closing_status
, 'O', 'Open'
, 'C', 'Closed'
, 'F', 'Future'
, 'N', 'Never'
, closing_status)
ar_status
, set_of_books_id
FROM gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id = '&sob') d
, (SELECT fdp.period_name
, DECODE (fdp.period_close_date, NULL, 'Open', 'Closed')
fa_status
, fbc.set_of_books_id
FROM fa_book_controls fbc, fa_deprn_periods fdp
WHERE fbc.set_of_books_id = '&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id = b.set_of_books_id(+)
AND a.set_of_books_id = c.set_of_books_id(+)
AND a.set_of_books_id = d.set_of_books_id(+)
AND a.set_of_books_id = e.set_of_books_id(+)
ORDER BY 1;

Source: Internet & Others

Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)

1.3.1) EBS Technology Codelevel Checker

Use EBS Technology Codelevel Checker (ETCC) to check if all required database patches have been applied.

Download patch 17537119. Unzip patch and run script ./checkDBpatch.sh

>>All the required one-offs are present in Oracle Database Home

Run script for APPS tier:

[applmgr@tvmdb19 ETCC]$ ./checkMTpatch.sh

Script reported that patch 22107540 is missing in FMW webtier 11.1.1.9, though DOC 1594274.1 claims that it's included in startCD v51.

Apply patch using opatch, setting necessary environment variables:

[applmgr@tvmdb19 22107540]$ which opatch

/u01/app/apps/fs1/FMW_Home/webtier/OPatch/opatch

[applmgr@tvmdb19 22107540]$ ls

etc files README.txt

[applmgr@tvmdb19 22107540]$ opatch apply

egrep -i "error|fail|ora-|ac-|rc-" /u01/app/apps/fs1/FMW_Home/webtier/cfgtoollogs/opatch/22107540_Mar_22_2016_10_56_20/apply2016-03-22_10-56-19AM_1.log

After that script reports no patches required to be applied:

1.3.2) Pre-patch analysis

[-]20745242 (R12.AD.C.Delta.7)

[-]22123818:R12.AD.C

[-]20784380 (R12.TXK.C.Delta.7)

[-]22363475:R12.TXK.C

[-]22495069:R12.TXK.C

Pre-steps:

20745242, 22123818:

Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

Usage:

1. Create $ORACLE_HOME/appsutil/admin on the database server.

2. Compare the version of adgrants.sql(UNIX) in $APPL_TOP/admin

to that in patch directory.

3. Copy the higher version of adgrants.sql (UNIX) to

$ORACLE_HOME/appsutil/admin.

4. Set the environment to point to ORACLE_HOME on the database server.

5. Use SQL*Plus to run the script:

UNIX:

$ sqlplus /nolog

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>

1.3.3) Patches application

Pre-steps:

20745242, 22123818:

Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

Usage:

1. Create $ORACLE_HOME/appsutil/admin on the database server.

2. Compare the version of adgrants.sql(UNIX) in $APPL_TOP/admin

to that in patch directory.

3. Copy the higher version of adgrants.sql (UNIX) to

$ORACLE_HOME/appsutil/admin.

4. Set the environment to point to ORACLE_HOME on the database server.

5. Use SQL*Plus to run the script:

UNIX:

$ sqlplus /nolog

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>

On the run edition application tier file system, use hotpatch mode to apply Patch 20745242 (R12.AD.C.Delta.7).

$ adop phase=apply patches=20745242 hotpatch=yes

Apply Patch 22123818:R12.AD.C in hotpatch mode.

$ adop phase=apply patches=22123818 hotpatch=yes

Applying patch 22123818.

Log: /u01/app/apps/fs_ne/EBSapps/log/adop/2/apply_20160324_011756/PROD_tvmdb19/22123818/log/u22123818.log

AutoPatch error:

Please apply adgrants.sql file in database tier before applying this patch

Solution:

I applied adgrants.sql script from 20745242 patch. This script was lower version that the one supplied with 22123818. Need to re-execute correct adgrants.sql once more.

Note: The above patches must be applied in the order mentioned above.

On the run edition application tier file system, use hotpatch mode to apply Patch 20784380:R12.TXK.C.Delta.7 merged with Patch 22363475:R12.TXK.C and Patch 22495069:R12.TXK.C (plus additional critical patches - see note box).

$ adop phase=apply patches=20784380,22363475,22495069 hotpatch=yes merge=yes

Note: As per subsection 4.2 Critical TXK Patches, you should also add any applicable TXK patches to the list of patches in this command.

Source the run edition environment file.

UNIX:

$ . <EBS_ROOT>/EBSapps.env run

Update all database tier nodes with the latest code, by performing the following steps:

On the Application Tier (as the APPLMGR user):

Run AutoConfig on the run file system.

Execute the admkappsutil.pl utility to create the appsutil.zip file in <INST_TOP>/admin/out.

$ perl <AD_TOP>/bin/admkappsutil.pl

On the Database Tier (as the ORACLE user):

Source the environment variables for RDBMS ORACLE_HOME.

UNIX

$ cd <RDBMS ORACLE_HOME>

$ . ./<RDBMS ORACLE_HOME>/<CONTEXT_NAME>.env

Copy or FTP the appsutil.zip file to <RDBMS ORACLE_HOME>.

Uncompress appsutil.zip, under <RDBMS ORACLE_HOME>.

$ cd <ORACLE_HOME>

$ unzip -o appsutil.zip

Run AutoConfig on <RDBMS ORACLE_HOME>.

Source: Internet