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>