INSTALLATION OF Oracle 19C DATABASE

We used below environments for 19c Database installation

   ORACLE_SID=ssodb

     ORACLE_HOME=/d01/oracle/ssodb/19.0.0/dbhome

     ORACLE_BASE=/d01/oracle/ssodb/19.0.0
     

   Copy 19c binaries LINUX.X64_193000_db_home.zip  to ORACLE_HOME

   
  

      Unzip binaries in ORACLE_HOME
        $unzip LINUX.X64_193000_db_home.zip
      

    Run the runInstaller

    $cd /d01/oracle/ssodb/19.0.0/dbhome
    $ ./runInstaller

                                           Click on Ignore All and continue :

                                            Save response file to ORACLE_HOME  directory and continue 

                                            Run the root.sh generated file  using root user

Find Directory Path only from DBA_DATA_FILES in Oracle Database 19c

Run any below sql to find directory path 

1. SQL> select file_name,substr(file_name,1,length(file_name)-        instr(reverse(file_name),'\')) p from dba_data_files;

2. SQL> select substr(file_name, 1, instr(file_name, '/', -1, 1)) from dba_data_files;

3.  SQL>select file_name, regexp_substr(file_name,'^.*\\') dir from dba_data_files;

Query to find duplicates owned by both SYS and SYSTEM

 SELECT object_name, object_type
     FROM dba_objects
     WHERE object_name||'_'||object_type IN
     (SELECT object_name||'_'||object_type
       FROM dba_objects WHERE owner = 'SYS')
       AND owner = 'SYSTEM' AND object_name NOT IN ('AQ$_SCHEDULES',
       'AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH');

SQLNET.ALLOWED_LOGON_VERSION_SERVER values

12a For oracle 12.1.0.2 or later connect to server. Password version is 12c
12 For Oracle 11.2.0.3 or later connect to server. Password version is 11g,12C
11 For Oracle 10g or later connect to server. Password version is 10G, 11G, 12C
10 For Oracle 10g or later connect to server. Password version is 10G, 11G, 12C
9 For Oracle 9i or later connect to server. Password version is 10G, 11G, 12C
8 For Oracle 8i or later connect to server. Password version is 10G, 11G, 12C

MGD (Machine Generated Data) component missing from the dba_registry after 19c upgrade

After 19c Upgrade 

SQL> select con_id,comp_id,comp_name,status from cdb_registry where comp_id='MGD';

no rows selected

Any  upgrade from prior releases to 19c and any new installation of MGD will not be populated in dba_registry . This can be ignored

OLAPSYS invalid objects created after upgrading EBS R12.2.9 Database from 12c to 19c

OLAPSYS Objects found invalid after database upgrade to 19c

OLAPSYS CWM$EXPORT PACKAGE BODY
OLAPSYS ALL$OLAP2_FACTTBLFCTMAPS VIEW
OLAPSYS ALL$OLAP2UFACT_LEVEL_USES VIEW
OLAPSYS ALL$OLAPMR_FACTTBLFCTMAPS VIEW
OLAPSYS DBA$OLAP2_FACTTBLFCTMAPS VIEW
OLAPSYS DBA$OLAP2UFACT_LEVEL_USES VIEW
OLAPSYS DBA$OLAPMR_FACTTBLFCTMAPS VIEW
OLAPSYS CWM2_OLAP_EXPORT PACKAGE BODY
OLAPSYS CWM2_OLAP_DELETE PACKAGE BODY

OLAPSYS CWM2_OLAP_MR_SECURITY_INIT PACKAGE BODY
OLAPSYS CWM2_OLAP_METADATA_REFRESH PACKAGE BODY
OLAPSYS OLAPFACTVIEW PACKAGE BODY
OLAPSYS OLAPDIMVIEW PACKAGE BODY
OLAPSYS ODM$OLAP2UFACT_LEVEL_USES VIEW
OLAPSYS DBMS_ODM PACKAGE BODY
OLAPSYS DBMS_AWM PACKAGE BODY
OLAPSYS CWM2_OLAP_VERIFY_ACCESS PACKAGE BODY

Solution
OLAPSYS invalid objects, as mentioned in Doc ID 2884111.1 - These OLAPSYS DB Objects are not used in Oracle Database 19c hence it can be ignored.

Inside checkPDBPluginViolations()... failed with Status 1

 ====================================
Inside checkPDBPluginViolations()...
====================================
File /d00/oracle/db/tech_st/19c/dbs/TESTPDB_PDBDesc.xml exists.
Generating SQL file : /d00/oracle/db/tech_st/19c/appsutil/log/TXK_CHK_PDB_COMPTABILITY_Fri_Aug_19_11_48_55_2022/check_pdb_plugin_violations.sql
SQL output file : /d00/oracle/db/tech_st/19c/appsutil/log/TXK_CHK_PDB_COMPTABILITY_Fri_Aug_19_11_48_55_2022/check_pdb_plugin_violations.out
==========================
Inside executeSQLFile()...
==========================
Executing the SQL...
Execute SYSTEM command : sqlplus -s /nolog @/d00/oracle/db/tech_st/19c/appsutil/log/TXK_CHK_PDB_COMPTABILITY_Fri_Aug_19_11_48_55_2022/check_pdb_plugin_violations.sql
==============================
Inside searchFileContents()...
==============================
log_file: /d00/oracle/db/tech_st/19c/appsutil/log/TXK_CHK_PDB_COMPTABILITY_Fri_Aug_19_11_48_55_2022/check_pdb_plugin_violations.out
pattern: ERROR
==========
Pattern found...
==========
EXIT STATUS: 1
-----------------------------------------------------------------------------------------------------------
log_file: /d00/oracle/db/tech_st/19c/appsutil/log/TXK_CHK_PDB_COMPTABILITY_Fri_Aug_19_11_48_55_2022/check_pdb_plugin_violations.out

NAME CAUSE TYPE STATUS
---------- -------------------- ---------- ----------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------
PDB$SEED SQL Patch ERROR RESOLVED
Interim patch 31424070/24722481 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Installed in the CDB but not in the PDB
PDB$SEED SQL Patch ERROR RESOLVED
Interim patch 33808367/24680225 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)): Installed in the CDB but not in the PDB
PDB$SEED SQL Patch ERROR RESOLVED
'19.15.0.0.0 Release_Update 2203311254' is installed in the CDB but no release updates are installed in the PDB
TESTPDB Non-CDB to PDB WARNING PENDING
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
TESTPDB OPTION WARNING PENDING
Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.
TESTPDB OPTION WARNING PENDING
Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.
TESTPDB OPTION WARNING PENDING
Database option OWM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

Solution
   ignore warnings and proceed with next step 

Find the PDB violations in 19c Oracle Database

Source CDB environment file
$ sqlplus "/as  sysdba"
SQL> select * from PDB_PLUG_IN_VIOLATIONS;

Source CDB environment file
$ sqlplus "/as  sysdba"
SQL> alter session set container=TESTPDB;
SQL> select * from PDB_PLUG_IN_VIOLATIONS;

Open Wallet on PDB in 19c Oracle Database

 Source CDB environment file

$ sqlplus "/as  sysdba"
SQL> alter session set container=TESTPDB;
SQL>administer key management set keystore open force keystore identified by "<password>";

Open Wallet on CDB in 19c Oracle Database

Source CDB environment file

$ sqlplus "/as  sysdba"

SQL>administer key management set keystore open force keystore identified by "<password>";

Import Wallet on PDB in 19c Oracle Database

 SQL> alter session set container=TESTPDB;

Open the wallet

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<password>";

Import the wallet

SQL> administer key management import encryption keys with secret "<password>" from '/d01/wallet/tde/testpdb.key' identified by "<password>" with backup;

Verify Wallet Status

SQL> select STATUS from V$ENCRYPTION_WALLET;

STATUS
OPEN

Import Wallet on CDB in 19c Oracle Database

Before import, we need to open the wallet.

Open the wallet

 SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<password>";

 Import the wallet

SQL> administer key management import encryption keys with secret "<password>" from '/d01/wallet/tde/testpdb.key' identified by "<password>" with backup;

Verify Wallet Status

SQL> select STATUS from V$ENCRYPTION_WALLET;

           STATUS
    OPEN

Close the Wallet on PDB in 19c Oracle Database

 SQL>administer key management set keystore close identified by "<password>";

Verify the wallet Status

SQL> select status from v$encryption_wallet;

STATUS        

CLOSED            

Close the Wallet on CDB in 19c Oracle Database

 SQL>administer key management set keystore close identified by "<password>";

Verify the wallet Status

SQL> select status from v$encryption_wallet;

STATUS 

        CLOSED              

Find PDB Wallet Status in 19c Oracle Database

$ sqlplus "/as  sysdba"

SQL> alter session set container=TESTPDB;
            Session altered.

SQL> select wrl_parameter, status, wallet_type from v$encryption_wallet;

        WRL_PARAMETER STATUS                                      WALLET_TYPE

                                         OPEN_NO_MASTER_KEY               PASSWORD

Find CDB Wallet Status in 19c Database

 SQL> select wrl_parameter, status, wallet_type from v$encryption_wallet;

    WRL_PARAMETER STATUS                  WALLET_TYPE
        /d01/wallet/tde/  OPEN                           PASSWORD

  

Find all db_links

set pagesize 300
set linesize 300
col created format A10
col host format A15
col owner format A20
col DB_LINK format A40
col USERNAME format A20
select * from dba_db_links;

Create Database link in Oracle Database 19c

 Syntax

SQL>  create database link <custom database link> 

        connect to <user> identified by
<password> using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=                <hostname>)
(PORT=<port number>))(CONNECT_DATA=(SID=<ORACLE_SID>)))';

Example:

  SQL> create database link XSCHLINK connect to aptest identified by aptest123

'(DESCRIPTION =(ADDRESS = (PROTOCOL=tcp)(HOST=apdb001.myorg.com) (PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=ebs_TESTPDB)(INSTANCE_NAME=TESTCDB)))';

Gather Schema Stats in Oracle Database 19c

Begin
dbms_stats.gather_schema_stats(ownname => 'GL',

options =>'GATHER AUTO',

estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 2);
END;

Gather Table Stats in Oracle Database 19c

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'XCSCHEM',

tabname => 'AP_ADDR_ORG',

cascade => true,
method_opt=>'for all indexed columns size 1',
granularity => 'ALL',
estimate_percent =>dbms_stats.auto_sample_size,
degree => 4);
END;
/

How to check Stale statistics of Table

 SQL>select owner,table_name from dba_tab_statistics 

where stale_stats='YES' 

and object_type='TABLE'

OWNER                          TABLE_NAME

APPLSYS                        FND_CONCURRENT_PROCESSES
APPLSYS                        FND_CONCURRENT_QUEUES
APPLSYS                        FND_NODES
APPLSYS                        FND_CONFLICTS_DOMAIN
APPLSYS                        FND_TEMP_FILES
5 rows selected.
SQL>

adop phase=finalize failed with Data dictionary corrupted

ADOP (C.Delta.12)
Session ID: 17
Node: apapp1

Phase: finalize

Log: /d01/oracle/apps/fs_ne/EBSapps/log/adop/17/20220829_153322/adop.log

Verifying existence of context files in database.

Verifying data dictionary.

    [UNEXPECTED]Data dictionary corrupted:

    [UNEXPECTED]Data dictionary corruption - timestamp mismatch

    XCSCHEM         XCSHEMA_ADDRESS_ORG   V_20220821_1154 APPS   CSHEMA_ADDRESS_ORG  V_20220821_1154 TS mismatch: 21-AUG-22 13:14:03 21-AUG-22 13:39:18

    [UNEXPECTED] Follow the instructions in the "Fix data dictionary corruption"

    [UNEXPECTED] section of My Oracle Support Knowledge Document 1531121.1 .

  [UNEXPECTED]Finalize phase has failed.

    Summary report for current adop session:

    Node applapp1: Failed

        - Finalize status:   Failed

    Node applapp2: Completed successfully

      - Finalize status:   Running

    Node applapp3: Completed successfully

        - Finalize status:   Running

 Solution:

     Run below script as apps user as per Doc ID 1531121.1 ( Section 3.1)

sqlplus apps/<pwd> @$AD_TOP/patch/115/sql/adzddtsfix.sql
sqlplus apps/<pwd> @$AD_TOP/patch/115/sql/adzddtsfixout.sql

ADOP Apply Phase Error: You Must Be In Maintenance Mode To Apply Patches

a.     Verify below entry in tnsnames.ora on RUN FS and PATCH FS in R12.2

 TESTCDB =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL=tcp)(HOST=apdb001.myorg.com)(PORT=1521))

  (CONNECT_DATA = (SERVICE_NAME=ebs_TESTPDB) (INSTANCE_NAME=TESTCDB))

 )

 b.    Verify local_listener parameter configured in 19c for R12.2 as

       SQL> alter system set local_listener=TESTCDB.myorg.com:1521 scope=both

 c.    Verify service_names parameter in 19c and must be set to CDB  as

         SQL > show parameter service
                   service_names  TESTCDB

19c Database local_listener parameter misconfigured for R12.2

 Set the parameter as below

 SQL> alter system set local_listener=TESTCDB.myorg.com:1521 scope=both

19c Database service_name parameter misconfigured for R12.2

  For Oracle Database 19c with a single tenant (1 CDB : 1 PDB) configuration, the
    value for the service_names parameter is auto-populated when the CDB is created.
   # You should not modify this parameter manually.
   

    SQL > show parameter service
              service_names  TESTCDB

ebs_ service name was missing in listener file

 In my case PDB_NAME = TESTPDB
                   CDB_NAME=TESTCDB

   While starting the listerner file, ebs_TESTDB was missing.

    Following the below steps, it got resolved

  a.  Set the database service_name parameter container name as 

      $ sqlplus "/as  sysdba"

       SQL> alter System Set SERVICE_NAMES='TESTCDB' SCOPE=BOTH;
       SQL> alter System Register;

  b. Shutdown application services 
         adstpall.sh

  c. Shutdown database listener
         lsnrctl stop TESTCDB

  d. Start database listener
         lsnrctl start TESTCDB

  e. lsnrctl status TESTCDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-AUG-2022 15:35:02

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apdb001.myorg.com)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     TESTCDB

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                22-AUG-2022 16:55:18

Uptime                    1 days 22 hr. 39 min. 43 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /d00/oracle/db/tech_st/19c/network/admin/listener.ora

Listener Log File         /d00/oracle/db/tech_st/19c/network/admin/testcdb.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apdb001.myorg.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=apdb001.myorg.com)(PORT=5500))(Security=(my_wallet_directory=/d00/oracle/db/tech_st/admin/TESTCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "TESTCDB" has 1 instance(s).

  Instance "TESTCDB", status READY, has 1 handler(s) for this service...

Service "TESTCDBXDB" has 1 instance(s).

  Instance "TESTCDB", status READY, has 1 handler(s) for this service...

Service "TESTPDB" has 1 instance(s).

  Instance "TESTCDB", status READY, has 1 handler(s) for this service...

Service "TESTPDB_ebs_patch" has 1 instance(s).

  Instance "TESTCDB", status READY, has 1 handler(s) for this service...

Service "ebs_TESTPDB" has 1 instance(s).

  Instance "TESTCDB", status READY, has 1 handler(s) for this service...

Service "f469d0a189c77322e04377baa8c0a71f" has 1 instance(s).

  Instance "TESTCDB", status READY, has 1 handler(s) for this service...

The command completed successfully

  f. Check the services in PDB (TESTPDB) as

     $ sqlplus "/as  sysdba"

     SQL> select name from v$active_services;

         NAME

         ebs_TRESTPDB

  TESTPDB

  TESTPDB_ebs_patch

Find CPU Patch level in EBS R12.2

SQL> col CPU format a9

SQL> select max(CODELEVEL) "CPU" from AD_TRACKABLE_ENTITIES where ABBREVIATION in ('ebscpu');

CPU

---------

2022.07

adop phase=prepare is showing below warning -adop has detected a configured disaster recovery site

[WARNING]    adop has detected a configured disaster recovery site.

[WARNING]    Follow the instructions in the section "Oracle E-Business Suite

[WARNING]    Maintenance with Standby Database" of Business Continuity for

[WARNING]    Oracle E-Business Suite Release 12.2 depending on the database version used.

Do you want to continue with the prepare phase [Y/N] ?

Solution :

Continue with Y as init<sid>.ora contains Data Guard setup  which is not disabled. There is no harm to continue with "Y" option

adop phase=finalize failed with ORA-06512: at line 7 (DBD ERROR: OCIStmtExecute)

 Abort the patch cycle and run cleanup mode / prepare then finalize as below

   1. adop pahse=abort

   2. adop phase=cleanup cleanup_mode=full

   3. adop phase=prepare

   4. adop phase=finalize finalize_mode=full

   5. adop phase=cutover

   6. adop phase=cleanup cleanup_mode=full

Check Oracle Standby Redo logs

SQL> Select group#,type,member 

            From v$logfile

           Where type=‘STANDBY’

            order by group#

SQL> Select group#,thread#,sequence#,status

           From v$standby_log