create table GETSBACK.pre1226invalids as select * from dba_objects where status='INVALID';
select count(*) from GETSBACK.pre1226invalids;
select owner,object_name,object_type,status from dba_objects where status='INVALID' and object_name not in (select object_name from getsback.pre1226invalids where status='INVALID');
- Apply EBS Patches (US)
export PATCH=20128107
export PATCH_HOME=/orasoft/oraApps/OEL6/cfs/R122/patches/patch_${PATCH}
time adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt \
logfile=adp${PATCH}.log patchtop=${PATCH_HOME}/${PATCH} \
driver=u${PATCH}.drv workers=64 interactive=yes
- 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/`tellme apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
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');
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;
select object_type
, object_name
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');
select 'alter index '||owner||'.'||index_name||' rebuild parallel 8;' from dba_indexes where status = 'UNUSABLE'
|
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 |
|
|
|
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 |
|
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>
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
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 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
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
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;
# 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
