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;
/