DB Parameters Validate

 

1.1.1.1         Verify StandBy and FlasBack Parameters

On Primary Database Node as oracle,

$ sqlplus / as sysdba

select NAME ||'='||VALUE from v$parameter

 where NAME in ('db_unique_name',

                'log_archive_config',

                'log_archive_dest_2',

                'log_archive_min_succeed_dest',

                'log_archive_dest_state_1',

                'log_archive_dest_state_2',

                'remote_login_passwordfile',

                'fal_server',

                'standby_file_management');

 

SQL>alter database flashback on;

SQL> select NAME,FLASHBACK_ON from v$database;

SQL> archive log list

select NAME ||'='||VALUE from v$parameter

 where NAME in ('undo_retention',

                'undo_management',

                'db_flashback_retention_target',

                'db_recovery_file_dest',

                'db_recovery_file_dest_size');

Review some Init Parameters

 sqlplus / as sysdba

COLUMN name FORMAT a35 HEADING 'Parameter Name'

COLUMN value FORMAT a110 HEADING 'Value'

SET PAGESIZE 50000 LINES 200

select distinct name,value

from gv$parameter

where name in

('service_names',

'resource_manager_plan',

'shared_pool_size',

'shared_pool_reserved_size',

'job_queue_processes',

'utl_file_dir',

'timed_statistics')

order by 1;

Invalid Objects

 select object_type

, object_name
, owner
from dba_objects
where status = 'INVALID'
and owner = 'DEMANTRA'
order by object_type, object_name;

SQL> set pagesize 200 linesize 132
SQL> SELECT owner, object_type, object_name, status
     FROM all_objects
     WHERE status = 'INVALID'
     ORDER BY owner;

SELECT CASE WHEN object_type = 'SYNONYM' AND owner = 'PUBLIC' THEN
    'alter ' || owner || ' ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    ELSE
    'alter ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || owner || '.' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
    END "SQL_COMMANDS"
    FROM dba_objects
    WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SYNONYM')
    AND status = 'INVALID' 
    ORDER BY DECODE(object_type, 'TRIGGER', '99', '00');










column owner format A9
column object_name format A31
column object_type format A15
column last_ddl_time format A10
spool invalids.lst
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
from dba_objects where status='INVALID'
order by owner;

Oracle upgrade high level

 

You can generate and rebuild all Unusable indexes with the following script

 select 'alter index '||owner||'.'||index_name||' rebuild parallel 8;' from dba_indexes where status = 'UNUSABLE'

union all
select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where status = 'UNUSABLE';

Oracle E-Business Suite Release 12 Release-Specific Database Initialization Parameters for 19c

 

Oracle E-Business Suite Release 12  Release-Specific Database Initialization Parameters for 19c
S.No	Release-Specific Database Initialization Parameters for Oracle 19c	Required Parameters	Information about the Parameter
1	compatible = 19.0.0 #MP	EBS
2	optimizer_adaptive_plans = TRUE #MP	EBS
3	optimizer_adaptive_statistics = FALSE #MP	EBS
4	pga_aggregate_limit = 0 #MP	EBS
5	temp_undo_enabled = FALSE	EBS
6	_pdb_name_case_sensitive = TRUE #MP	EBS
7	event='10946 trace name context forever, level 8454144' #MP	EBS
8	_optimizer_gather_stats_on_conventional_dml = FALSE #MP	EBS
9	_optimizer_use_stats_on_conventional_dml = FALSE #MP	EBS
10	optimizer_real_time_statistics = FALSE #MP	EBS
Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2
11	recyclebin = off #MP	EBS
12	service_names=%s_dbSid%, <s_patch_service_name or ebs_patch>  # Based on AD/TXK Code level	EBS
13	local_listener=%s_dbSid%_LOCAL	EBS
14	result_cache_max_size = 600M	EB                S

egrep 'compatible|optimizer_adaptive_plans|optimizer_adaptive_statistics|pga_aggregate_limit|temp_undo_enabled|_pdb_name_case_sensitive|event|_optimizer_gather_stats_on_conventional_dml|_optimizer_use_stats_on_conventional_dml|optimizer_real_time_statistics|recyclebin|service_names|local_listener|result_cache_max_size' intiSID.ora

EBS DATABASE MULTITENANT CLONE

 s_undo_tablespace=<Source (PDB) system undo tablespace name>

s_db_oh=<Location of new ORACLE_HOME>

s_dbhost=<Target hostname>

s_dbSid=<Target PDB name>

s_pdb_name=<Target PDB name>

s_cdb_name=<Target CDB SID>

s_base=<Base directory for DB Oracle Home>

s_dbuser=<DB User>

s_dbgroup=<DB group> (Not applicable to Windows)

s_dbhome1=<Data directory>

s_display=<Display>

s_dbCluster=false

s_isDBCluster=n

s_dbport=<DB port>

s_port_pool=<Port pool number>

egrep -i 's_undo_tablespace|s_db_oh|s_dbhost|s_dbSid|s_pdb_name|s_cdb_name|s_base|s_dbuser|s_dbgroup|s_dbhome1|s_display|s_dbCluster|s_isDBCluster|s_dbport|s_port_pool'   

$ cd <NEW ORACLE_HOME>/appsutil/clone/bin

$ perl adclonectx.pl \

contextfile=<Source database context file> \

template=<NEW ORACLE_HOME>/appsutil/template/adxdbctx.tmp \

[pairsfile=<Pairs file generated in Section 4.1.2>]

If the pairsfile.txt file is not passed, you will be presented with the following questions:

Provide the values required for creation of the new Database Context file.

Target System Hostname (virtual or normal) [<Current hostname] :

Do you want the inputs to be validated (y/n) [n] ? :

Target Instance is RAC (y/n) [n] : n

Target System CDB Name : <Name of the Container database>

Target System PDB Name : <Name of the EBS PDB database>

Target System Base Directory : <Complete path to the base directory for the target system>

Oracle OS User [<Current OS User>]] :

Oracle OS Group [<Current OS User group] :

Role separation is supported y/n [n] ? : <If the answer to this prompt is 'y', enter values for the next two prompts also>

Specify value for OSOPER group [<Oracle OS Group>] :

Specify value for OSASM group [ ] :

Number of DATA_TOP's on the Target System [<Number of data tops on the source database>] : <Number of data tops for the target database>

Target System DATA_TOP Directory 1 [<data top directory>] : <If there is more than one data top, there will be additional prompts for the remaining data tops>

Specify value for OSBACKUPDBA group [<Oracle OS group>] :

Specify value for OSDGDBA group [<Oracle OS group>] :

Specify value for OSKMDBA group [<Oracle OS group>] :

Specify value for OSRACDBA group [<Oracle OS group>] :

Target System RDBMS ORACLE_HOME Directory[]:

Do you want to preserve the Display [localhost:10.0] (y/n) : n

Target System Display [<Target hostname>:0.0] :

Do you want the target system to have the same port values as the source system (y/n) [y] ? : <If the answer to this prompt is 'n' or if the source port pool is not free on the current node, enter the value for the next prompt>

Target System Port Pool [0-99] : <Port pool>

New context path and file name [<ORACLE_HOME>/appsutil/<CONTEXT_NAME>.xml] :

4.1.4 Configure the Database Technology Stack

Configure the database technology stack copied by running the following steps:

Navigate to <ORACLE_HOME>/appsutil/clone/bin and run Rapid Clone (adcfgclone.pl utility) with the following parameters to configure the database technology stack:

$ perl adcfgclone.pl dbTechStack <Complete path to the target context file>

4.1.5 Create the listener.ora and tnsnames.ora for the Target Database

Create the listener.ora and tnsnames.ora files for the target CDB by running the following commands:

Set the environment.

On UNIX:

$ cd <ORACLE_HOME>/appsutil

$ source ./txkSetCfgCDB.env -dboraclehome=<ORACLE_HOME>

On Windows:

C:\> cd <ORACLE_HOME>\appsutil

C:\> txkSetCfgCDB.cmd dboraclehome=<ORACLE_HOME>

Generate the listener.ora and tnsnames.ora.

$ cd <ORACLE_HOME>/appsutil/bin

$ perl txkGenCDBTnsAdmin.pl -dboraclehome=<ORACLE_HOME> -cdbname=<Name of the target container database> \

-cdbsid=<SID of the target container database> -dbport=<Target DB port> -outdir=$ORACLE_HOME/appsutil/log \

-israc=<yes/no> [-virtualhostname=<virtual hostname>]

where:

Parameter Description

cdbname Name of the target container database.

cdbsid

Oracle SID of the target container database instance.

For single-node database, the value is same as the cdbname.

For Oracle RAC database, it is the Instance name of the target database. israc Provide the value 'yes' for an Oracle RAC database.

Provide the value 'no' for a single-node database. virtualhostname Virtual hostname for the Oracle RAC database. For a single-node database, this parameter should not be used.

Start the listener for the target container database as follows:

On UNIX:

$ cd <ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>

$ ./adcdblnctl.sh start <CDB SID>

DB Parameters

 egrep -i 'parameter_value_convert|db_unique_name|db_create_file_dest|db_recovery_file_dest|db_recovery_file_dest_size|control_files|log_archive_max_processes|fal_client|fal_server|standby_file_management|log_archive_config|log_archive_dest_2|valid_for|db_unique_name'

Script to Check Profile Options Related to Debugging, Tracing, and Logging in EBS 12.2

 Script to Check Profile Options Related to Debugging, Tracing, and Logging in EBS 12.2

SELECT po.user_profile_option_name,

po.profile_option_name "NAME" ,

DECODE (TO_CHAR (pov.level_id), '10001', 'SITE' , '10002', 'APP', '10003', 'RESP',

'10004', 'USER', '???') "LEV",

DECODE (TO_CHAR (pov.level_id) , '10001', '', '10002', app.application_short_name ,

'10003', rsp.responsibility_key, '10004', usr.user_name, '???') "CONTEXT",

pov.profile_option_value "VALUE"

FROM fnd_profile_options_vl po,

fnd_profile_option_values pov,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp

WHERE (upper(po.profile_option_name) like '%DEBUG%' or upper(po.profile_option_name) like

'%TRACE%' or upper(po.profile_option_name) like '%LOG%')

AND pov.application_id = po.application_id

AND pov.profile_option_id = po.profile_option_id

AND usr.user_id(+) = pov.level_value

AND rsp.application_id(+) = pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+) = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE"

verify the patches

 - Verify applied patches

sqlplus / as sysdba

SELECT DISTINCT bug_number,language,creation_date 

  FROM apps.ad_bugs 

  WHERE bug_number IN ('20128107')

ORDER BY bug_number,language,creation_date

/

  - Disable “Maintenance Mode”

On Primary Application Node:

sqlplus -s apps/`apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

applying patches

 

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt  workers=36 logfile=u_18712060.log patchtop=/orasoft/oraApps/OEL6/ccc/R122/Add-On_Localizations/CCC/18712060 driver=u18712060.drv

Patch Applied Query

 

SELECT bug_number,to_char(creation_date,'dd-MON-yyyy hh24:mi:ss') FROM apps.ad_bugs WHERE bug_number IN ('10350522','12539637','20621314','16289505','16541956','22071026','5233248','10231107','5259121','15969486') GROUP BY bug_number,creation_date order by creation_date;

 

Color Codes

 # Reset

Color_Off='\033[0m'       # Text Reset

# Regular Colors

Black='\033[0;30m'        # Black

Red='\033[0;31m'          # Red

Green='\033[0;32m'        # Green

Yellow='\033[0;33m'       # Yellow

Blue='\033[0;34m'         # Blue

Purple='\033[0;35m'       # Purple

Cyan='\033[0;36m'         # Cyan

White='\033[0;37m'        # White

# Bold

BBlack='\033[1;30m'       # Black

BRed='\033[1;31m'         # Red

BGreen='\033[1;32m'       # Green

BYellow='\033[1;33m'      # Yellow

BBlue='\033[1;34m'        # Blue

BPurple='\033[1;35m'      # Purple

BCyan='\033[1;36m'        # Cyan

BWhite='\033[1;37m'       # White

# Underline

UBlack='\033[4;30m'       # Black

URed='\033[4;31m'         # Red

UGreen='\033[4;32m'       # Green

UYellow='\033[4;33m'      # Yellow

UBlue='\033[4;34m'        # Blue

UPurple='\033[4;35m'      # Purple

UCyan='\033[4;36m'        # Cyan

UWhite='\033[4;37m'       # White

# Background

On_Black='\033[40m'       # Black

On_Red='\033[41m'         # Red

On_Green='\033[42m'       # Green

On_Yellow='\033[43m'      # Yellow

On_Blue='\033[44m'        # Blue

On_Purple='\033[45m'      # Purple

On_Cyan='\033[46m'        # Cyan

On_White='\033[47m'       # White

# High Intensity

IBlack='\033[0;90m'       # Black

IRed='\033[0;91m'         # Red

IGreen='\033[0;92m'       # Green

IYellow='\033[0;93m'      # Yellow

IBlue='\033[0;94m'        # Blue

IPurple='\033[0;95m'      # Purple

ICyan='\033[0;96m'        # Cyan

IWhite='\033[0;97m'       # White

# Bold High Intensity

BIBlack='\033[1;90m'      # Black

BIRed='\033[1;91m'        # Red

BIGreen='\033[1;92m'      # Green

BIYellow='\033[1;93m'     # Yellow

BIBlue='\033[1;94m'       # Blue

BIPurple='\033[1;95m'     # Purple

BICyan='\033[1;96m'       # Cyan

BIWhite='\033[1;97m'      # White

# High Intensity backgrounds

On_IBlack='\033[0;100m'   # Black

On_IRed='\033[0;101m'     # Red

On_IGreen='\033[0;102m'   # Green

On_IYellow='\033[0;103m'  # Yellow

On_IBlue='\033[0;104m'    # Blue

On_IPurple='\033[0;105m'  # Purple

On_ICyan='\033[0;106m'    # Cyan

On_IWhite='\033[0;107m'   # White

ORA-01652: unable to extend temp error, this may be an indication that your temporary tablespace is too small.

When Oracle throws the ORA-01652: unable to extend temp error, this may be an indication that your temporary

tablespace is too small. However, Oracle may throw that error if it runs out of space because of a one-time event, such

as a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts

of sort space in the temporary tablespace warrants adding space.

To view the space a session is using in the temporary tablespace, run this query:

 SELECT s.sid, s.serial#, s.username

,p.spid, s.module, p.program

,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used

,su.tablespace

FROM v$sort_usage su

,v$session s

,dba_tablespaces tbsp

,v$process p

WHERE su.session_addr = s.saddr

AND su.tablespace = tbsp.tablespace_name

AND s.paddr = p.addr

GROUP BY

s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,

p.program, tbsp.block_size, su.tablespace

ORDER BY s.sid;

If you determine

catupgrd.sql spooled file

In the case of a manual upgrade, were there errors reported in the spooled output of catupgrd.sql?

    No:
    -----
    Proceed to check 4.

    Yes:
    -----
    Work through the following checks

The following commands can be used to scan the output file for possible problems:

grep "PL/SQL" .log
grep -i "ora-" .log
grep -i "mgr-" .log
grep -i "oci-" .log
grep -i "sp2-" .log
grep -i "sp1-" .log
grep -i "sp3-" .log
grep -i "pls-" .log
grep -i error .log
grep -i severe .log
grep -i fatal .log
grep -i fail .log
grep -i stop .log

Or you can use the following command:

egrep -i '^pl/sql|^ora-|^mgr-|^oci-|^sp3-|^sp2-|^sp1-|^pls-|^error|^severe|^fatal|^fail|^stop' .log

How to Configure : Configure Parallel Concurrent Processing

Configure Parallel Concurrent Processing

Check prerequisites for setting up Parallel Concurrent Processing
Parallel Concurrent Processing (PCP) spans two or more nodes. If you need to add nodes, follow the relevant instructions in My Oracle Support Knowledge Document 1383621.1, Cloning Oracle Applications Release 12 with Rapid Clone.

Note:  If you are planning to implement a shared Application tier file system, refer to My Oracle Support Knowledge Document 1375769.1.1, Sharing the Application Tier File System in Oracle E-Business Suite Release 12, for configuration steps. If you are adding a new Concurrent Processing node to the application tier, you will need to set up load balancing on the new application by repeating steps in Section 4.7.2.
Set Up PCP
Edit the applications context file via Oracle Applications Manager, and set the value of the variable APPLDCP to ON.

Source the Applications environment.

Execute AutoConfig by running the following command on all concurrent processing nodes:
$
      $INST_TOP/admin/scripts/adautocfg.sh
Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes.

Restart the Applications listener processes on each application tier node.

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility. Navigate to the Install > Nodes screen, and ensure that each node in the cluster is registered.

Verify that the Internal Monitor for each node is defined properly, with correct primary node specification, and work shift details. For example, Internal Monitor: Host1 must have primary node as host1. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator.

Set the $APPLCSF environment variable on all the Concurrent Processing nodes to point to a log directory on a shared file system.

Set the $APPLPTMP environment variable on all the CP nodes to the value of the UTL_FILE_DIR entry in init.ora on the database nodes. (This value should be pointing to a directory on a shared file system.)

Set profile option 'Concurrent: PCP Instance Check' to OFF if database instance-sensitive failover is not required. By setting it to 'ON', a concurrent manager will fail over to a secondary Application tier node if the database instance to which it is connected becomes unavailable for some reason.
Set Up Transaction Managers
Shut down the application services (servers) on all nodes

Shut down all the database instances cleanly in the Oracle RAC environment, using the command:
SQL>shutdown immediate;
Edit $ORACLE_HOME/dbs/_ifile.ora. Add the following parameters:
_lm_global_posts=TRUE
_immediate_commit_propagation=TRUE
Start the instances on all database nodes, one by one.

Start up the application services (servers) on all nodes.

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator responsibility. Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type' to ‘QUEUE', and verify that the transaction manager works across the Oracle RAC instance.

Navigate to Concurrent > Manager > Define screen, and set up the primary and secondary node names for transaction managers.

Restart the concurrent managers.

If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator.
Set Up Load Balancing on Concurrent Processing Nodes
Edit the applications context file through the Oracle Applications Manager interface, and set the value of Concurrent Manager TWO_TASK (s_cp_twotask) to the load balancing alias (_balance>).

Note:  Windows users must set the value of "Concurrent Manager TWO_TASK" (s_cp_twotask context variable) to the instance alias.

Execute AutoConfig by running $INST_TOP/admin/scripts/adautocfg.sh on all concurrent nodes.
Note: For further details on Concurrent Processing, refer to the Product Information Center (PIC) (Doc ID 1304305.1).

mailer

set linesize 100
col Component format a40
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';


set linesize 150
set pagesize 9999
col COMPONENT_NAME format a50
col COMPONENT_STATUS format a50
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;

workflow status

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not
Running',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE

The following SQL script can be run in sqlplus connected as apps/ to determine the profile Option settings in the Database opposed to checking them in the Application:

The following SQL script can be run in sqlplus connected as apps/ to determine the profile Option settings in the Database opposed to checking them in the Application:

set linesize 155;
set pagesize 200;
set verify off;
col Profile format a20;
 col Level format a14;
col Value format a10;
col App format a3;
col Responsibility format a30;
col USER format a8;
col UPDATED_BY format a8;
select distinct fpo.profile_option_name Profile,
      fpov.profile_option_value Value,
      decode(fpov.level_id, 10001,'Site',
                       10002,'Application',
                       10003,'Responsibility',
                       10004,'User',
                       10005,'Server',
                       10006,'Organization')"LEVEL",
      fa.application_short_name App,
      fr.responsibility_name Responsibility,
      fu.user_name "USER"
from fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
fnd_responsibility_vl fr,
fnd_user fu,
fnd_logins fl
where fpo.profile_option_id=fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.application_id(+)=fpov.level_value_application_id
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN
and fpov.profile_option_value is not Null
and fpo.profile_option_name in
('APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT', 'APPS_WEB_AGENT',
'APPS_SERVLET_AGENT', 'ICX_FORMS_LAUNCHER') order by 1,3;

https values

col PROFILE_OPTION_NAME format a60

set lines 2000 pages 2000

SELECT A.PROFILE_OPTION_NAME,B.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTIONS A,FND_PROFILE_OPTION_VALUES B

where a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID and b.profile_option_value like 'http:/%';