Sunday, December 27, 2020

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 2

upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)-Part 2


In Continuation to my previous posts https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-1.html, in this post, we will see how to upgrade the database using dbua.


Complete patching cycle and remove adop created editions


As the owner of the source administration server, run any of the following commands not previously run. This will clean up the editions created by previous adop patching cycles. These commands also complete any open patching cycle.

On the current run file system:
$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
On the new run file system:
$ adop phase=cleanup cleanup_mode=full

Store the UTL_FILE_DIR parameter values

Starting in Oracle Database 19c Release Update 3, calls to the UTL_FILE package can specify either a directory object or a physical directory path, which must also have a corresponding directory object within the database.
To simplify the upgrade to Oracle Database 19c, Oracle E-Business Suite has introduced new apps.v$parameter and apps.v$parameter2 views in the APPS schema that provide a supplemental UTL_FILE_DIR parameter. This new parameter can be referenced similarly to the way the former UTL_FILE_DIR database initialization parameter was referenced. The views in the APPS schema contain the same columns and rows as the standard v$parameter and v$parameter2 views in the SYS schema and also contain an additional row or rows for the supplemental UTL_FILE_DIR parameter. The apps.v$parameter view contains a row storing the UTL_FILE_DIR value as a comma-separated list of directories, while the apps.v$parameter2 view contains a separate row for each directory.
Oracle 12c environment source
[oracle@apps ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/install/APPS/12.1.0/appsutil/ebsdb_apps.xml -oraclehome=/u01/install/APPS/12.1.0 -outdir=/home/oracle -upgradedhome=/u01/install/APPS/19.3.0 -mode=getUtlFileDir -servicetype=opc
Enter the APPS Password:


Script Name    : txkCfgUtlfileDir.pl
Script Version : 120.0.12020000.11
Started        : Fri Dec  4 13:51:42 GMT 2020

Log File       : /home/oracle/TXK_UTIL_DIR_Fri_Dec_4_13_51_39_2020/txkCfgUtlfileDir.log

Context file: /u01/install/APPS/12.1.0/appsutil/ebsdb_apps.xml exists.

==============================================================================

Successfully generated the below file with UTL_FILE_DIR content:
/u01/install/APPS/12.1.0/dbs/ebsdb_utlfiledir.txt

==============================================================================


Completed        : Fri Dec  4 13:51:43 GMT 2020


Successfully Completed the script
ERRORCODE = 0 ERRORCODE_END
[oracle@apps ~]$


To store the directory path values in the database:

in 12c OH

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \
-oraclehome=<11g/12c ORACLE_HOME> -outdir=<Output/Log location> \
-upgradedhome=<19c ORACLE_HOME> -mode=setUtlFileDir -servicetype=onpremise|opc [ -skipdirvalidation=Yes ]

With this command, the script reads the <ORACLE_HOME>/dbs/<DB_NAME>_utlfiledir.txt file that you created previously and validates the physical directory paths listed in that file. If you are creating your Oracle Database 19c instance on a different server and cannot validate the 19c Oracle home directory, then you should pass the -skipdirvalidation=Yes parameter in the txkCfgUtlfileDir.pl script command. The -servicetype parameter defaults to -servicetype=onpremise, but when running on Oracle Cloud, you must specify -servicetype=opc for this parameter.


Shut down the application tier server processes

Shut down the database listener

On the database tier server node, shut down the Oracle Net or Net8 database listener in the old Oracle home.
Note: To ensure that the database does not inadvertently point to a non-existent listener during the upgrade, verify that you do not have the LOCAL_LISTENER initialization parameter set.

Comment out the following parameters

###*.olap_page_pool_size=4194304
##Local listener parameter

Add the parameter
 EVENT='10946 trace name context forever, level 8454144'

Upgrade the database instance

Run the preupgrade process
Connect to 12c home and run

Connect to the 12c Database and run the command as below:


[oracle@apps dbs]$ $ORACLE_HOME/jdk/bin/java -jar /u01/install/APPS/19.1.3/rdbms/admin/preupgrade.jar TERMINAL TEXT


[oracle@apps dbs]$ $ORACLE_HOME/jdk/bin/java -jar /u01/install/APPS/19.1.3/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-12-14T15:13:23

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  EBSDB
     Container Name:  ebsdb
       Container ID:  0
            Version:  12.1.0.2.0
     DB Patch Level:  DATABASE PATCH SET UPDATE 12.1.0.2.180116
         Compatible:  12.1.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  28
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  1.  Remove initialization parameters that Oracle has obsoleted or removed.
      This action may be done now or when starting the database in upgrade mode
      using the target ORACLE HOME.

      Parameter
      ---------
      O7_DICTIONARY_ACCESSIBILITY
      optimizer_adaptive_features
      utl_file_dir

      If parameters that are obsolete or removed from the target release are
      present in the pfile/spfile, the Oracle database may not start, or it may
      start with an ORA- error.

  2.  Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
      objects.  You can view the individual invalid objects with

        SET SERVEROUTPUT ON;
        EXECUTE DBMS_PREUP.INVALID_OBJECTS;

      11 objects are INVALID.

      There should be no INVALID objects in SYS/SYSTEM or user schemas before
      database upgrade.

  3.  Remove OLAP Catalog by running the 12.1.0.2.0 SQL script
      $ORACLE_HOME/olap/admin/catnoamd.sql script.

      The OLAP Catalog component, AMD, exists in the database.

      Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
      desupported and will be automatically marked as OPTION OFF during the
      database upgrade if present. Oracle recommends removing OLAP Catalog
      (OLAP AMD) before database upgrade.  This step can be manually performed
      before the upgrade to reduce downtime.

  4.  Perform one of the following:
       1) Expire user accounts that use only the old 10G password version and
      follow the procedure recommended in Oracle Database Upgrade Guide under
      the section entitled, "Checking for Accounts Using Case-Insensitive
      Password Version".
       2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19
      SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short
      term approach and is not recommended because it will retain known
      security risks associated with the 10G password version.)

      Your database system has at least one account with only the 10G password
      version (see the PASSWORD_VERSIONS column of DBA_USERS).

      Starting with Oracle Database release 12.2.0.1, Exclusive Mode is the new
      default password-based authentication mode. All Exclusive Mode
      login/authentication attempts will fail for preexisting user accounts
      which only have the 10G password version and neither the 11G or 12C
      password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,
      refer to "Understanding Password Case Sensitivity and Upgrades" in the
      Oracle Database Upgrade Guide.

  5.  Remove the SEC_CASE_SENSITIVE_LOGON instance initialization parameter
      setting, to configure your system to use case sensitive password
      authentication by default.

      Your database system is configured to enforce case insensitive password
      authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization
      parameter setting is FALSE).

      Starting with Oracle Database release 12.2, Exclusive Mode is the default
      password-based authentication mode. Case insensitive password
      authentication is not supported in Exclusive Mode. If your system needs
      to use case insensitive password authentication, Exclusive Mode must be
      switched off prior to the upgrade. See the Network Reference Manual
      chapter about the SQLNET.ORA parameter
      SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.

  6.  Remove Streams setup. For detailed steps, refer to the section "Removing
      an Oracle Streams Configuration" in the Oracle Streams Concepts and
      Administration Guide specific for the Oracle release from which you are
      removing. For versions pre-12.1.0.2, the procedure
      dbms_streams_adm.remove_streams_configuration must not be used as may
      lead to unwanted results. Instead, use the other procedures
      (dbms_capture_adm.drop_capture, dbms_apply_adm.drop_apply,
      dbms_streams_adm.remove_queue, etc). For 12.1.0.2 and higher, procedure
      dbms_streams_adm.remove_streams_configuration can be safely used.

      Oracle Streams feature is configured in the database.

      Starting with Oracle Database 19, Oracle Streams is desupported. It is
      strongly advised to remove any streams configuration manually.

  7.  Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
      empty before doing upgrade, unless you have strong business reasons not
      to do so. You can use dbms_mview.refresh() to refresh the MVs except
      those stale ones  to be kept due to business need. If there are any stale
      MVs depending on changes in sys.sumdelta$, do not truncate it, because
      doing so will cause wrong results after refresh.

      There is one or more non-fresh MV in the database or sumdelta$ is not
      empty.

      Oracle recommends that all materialized views (MV's) are refreshed before
      upgrading the database because this will clear the MV logs and the
      sumdelta$ table, and make the UPGRADE process faster. If you choose to
      not refresh some MVs, the change data for those MV's will be carried
      through the UPGRADE process. After UPGRADE, you can refresh the MV's and
      MV incremental refresh should work in normal cases.

  8.  Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.

      The database contains the following initialization parameters whose name
      begins with an underscore:

      _system_trig_enabled
      _sort_elimination_cost_ratio
      _b_tree_bitmap_plans
      _fast_full_scan_enabled
      _like_with_bind_as_equality
      _optimizer_autostats_job
      _trace_files_public

      Remove hidden parameters before database upgrade unless your application
      vendors and/or Oracle Support state differently.  Changes will need to be
      made in the pfile/spfile.

  9.  Review and remove any unnecessary EVENTS.

      The database contains events.

      There are events set that should be removed before upgrade, unless your
      application vendors and/or Oracle Support state differently.  Changes
      will need to be made in the pfile/spfile.

  10. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.1.0.2
      Oracle Database SQL Tuning Guide.

  11. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
      empty before doing upgrade, unless you have strong business reasons not
      to do so. You can use dbms_mview.refresh() to refresh the MVs except
      those stale ones  to be kept due to business need. If there are any stale
      MVs depending on changes in sys.sumdelta$, do not truncate it, because
      doing so will cause wrong results after refresh.

      There is one or more non-fresh MV in the database or sumdelta$ is not
      empty.

      Oracle recommends that all materialized views (MV's) are refreshed before
      upgrading the database because this will clear the MV logs and the
      sumdelta$ table, and make the UPGRADE process faster. If you choose to
      not refresh some MVs, the change data for those MV's will be carried
      through the UPGRADE process. After UPGRADE, you can refresh the MV's and
      MV incremental refresh should work in normal cases.

  INFORMATION ONLY
  ================
  12. Consider removing the following deprecated initialization parameters.

      Parameter
      ---------
      sec_case_sensitive_logon

      These deprecated parameters probably will be obsolete in a future release.

  13. Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database EBSDB
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/preupgrade_fixups
    .sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  14. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
      $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
      to VALIDATE and UPGRADE any user tables affected by changes to
      Oracle-Maintained types.

      There are user tables dependent on Oracle-Maintained object types.

      If the -T option is used to set user tablespaces to READ ONLY during the
      upgrade, user tables in those tablespaces, that are dependent on
      Oracle-Maintained types, will not be automatically upgraded. If a type is
      evolved during the upgrade, any dependent tables need to be re-validated
      and upgraded to the latest type version AFTER the database upgrade
      completes.

  15. Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 28 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  16. To identify directory objects with symbolic links in the path name, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed, using path names that contain no
      symbolic links.

      Some directory object path names may currently contain symbolic links.

      Starting in Release 18c, symbolic links are not allowed in directory
      object path names used with BFILE data types, the UTL_FILE package, or
      external tables.

  17. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  18. Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.1.0.2
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  19. Check the Oracle documentation for the identified components for their
      specific upgrade procedure.

      The database upgrade script will not upgrade the following Oracle
      components:  Oracle Machine Generated Data,OLAP Catalog

      The Oracle database upgrade script upgrades most, but not all Oracle
      Database components that may be installed.  Some components that are not
      upgraded may have their own upgrade scripts, or they may be deprecated or
      obsolete.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database EBSDB
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/postupgrade_fixup
    s.sql


==================
PREUPGRADE SUMMARY
==================
  /u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/preupgrade.log
  /u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/preupgrade_fixups.sql
  /u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-12-14T15:13:23


Run dbua from 19c OH

Make sure the DB is listed in /etc/oratab




In the first screen, cross verify the source and target database name.






Don't create the listener.


Once the upgrade is completed. check the dba registry components

[oracle@apps ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 5 05:08:37 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ebscdb  READ WRITE

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
         0

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------


SQL>

SQL> COLUMN comp_id FORMAT A10
COLUMN comp_name FORMAT A35
COLUMN version FORMAT A14SQL> SQL>
SQL>
SQL>
SQL>
SQL> SELECT comp_id,comp_name,version,status from dba_registry;

COMP_ID    COMP_NAME                           VERSION        STATUS
---------- ----------------------------------- -------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views       19.0.0.0.0     VALID
CATPROC    Oracle Database Packages and Types  19.0.0.0.0     VALID
RAC        Oracle Real Application Clusters    19.0.0.0.0     OPTION OFF
JAVAVM     JServer JAVA Virtual Machine        19.0.0.0.0     VALID
XML        Oracle XDK                          19.0.0.0.0     VALID
CATJAVA    Oracle Database Java Packages       19.0.0.0.0     VALID
APS        OLAP Analytic Workspace             19.0.0.0.0     VALID
XDB        Oracle XML Database                 19.0.0.0.0     VALID
OWM        Oracle Workspace Manager            19.0.0.0.0     VALID
CONTEXT    Oracle Text                         19.0.0.0.0     VALID
ORDIM      Oracle Multimedia                   19.0.0.0.0     VALID

COMP_ID    COMP_NAME                           VERSION        STATUS
---------- ----------------------------------- -------------- --------------------------------------------
SDO        Spatial                             19.0.0.0.0     VALID
XOQ        Oracle OLAP API                     19.0.0.0.0     VALID
OLS        Oracle Label Security               19.0.0.0.0     VALID
DV         Oracle Database Vault               19.0.0.0.0     VALID

15 rows selected.




run the post upgrade script now

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> @/u01/install/APPS/12.1.0/cfgtoollogs/ebsdb/preupgrade/postupgrade_fixups.sql;

 

Session altered.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

Package created.

 

No errors.

 

Package body created.

 

 

PL/SQL procedure successfully completed.

 

No errors.

 

 

 

 

 

Package created.

 

No errors.

 

Package body created.

 

No errors.

 

Executing Oracle POST-Upgrade Fixup Script

 

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 19.0.0.0.0 Build: 1

Generated on:            2020-12-15 08:26:07

 

For Source Database:     EBSDB

Source Database Version: 12.1.0.2.0

For Upgrade to Version:  19.0.0.0.0

 

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

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

    9.  depend_usr_tables         YES         None.

   10.  old_time_zones_exist      YES         None.

   11.  dir_symlinks              YES         None.

   12.  post_dictionary           YES         None.

   13.  post_fixed_objects        NO          Informational only.

                                              Further action is optional.

   14.  upg_by_std_upgrd          YES         None.

 

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database upgrade is not

fully complete.  To resolve the outstanding issues, start by reviewing

the postupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

 

PL/SQL procedure successfully completed.

 

 

Session altered.

 

SQL> SQL>

 

SQL> SQL> alter trigger SYSTEM.EBS_LOGON compile;

 

Trigger altered.

 

SQL> show parameter SEC_CASE_SENSITIVE_LOGON;

 

NAME                                 TYPE        VALUE

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

sec_case_sensitive_logon             boolean     TRUE

 

 

SQL> alter system set compatible='19.0.0' scope=spfile;

 

System altered.

Perform patch post-install instructions

SQL>

SQL> @?/rdbms/admin/dbmsxdbschmig.sql;

 

Session altered.

 

SQL> SET FEEDBACK 1

SQL> SET NUMWIDTH 10

SQL> SET LINESIZE 80

SQL> SET TRIMSPOOL ON

SQL> SET TAB OFF

SQL> SET PAGESIZE 100

SQL>

SQL> CREATE OR REPLACE PACKAGE sys.xdb_migrateschema IS

  2

  3  -- Procedures to move an xml schema from user A to user B

  4  -- see impl for comments

  5  PROCEDURE moveSchemas;

  6  PROCEDURE cleanup;

  7  end xdb_migrateschema;

  8  /

 

Package created.

 

SQL> show errors;

No errors.

SQL>

SQL>

SQL> BEGIN

  2   execute immediate ('DROP TABLE xdb$moveSchemaTab');

  3   EXCEPTION

  4      WHEN OTHERS THEN NULL;

  5  END;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> BEGIN

  2   execute immediate ('CREATE TABLE xdb$moveSchemaTab (schema_url VARCHAR2(4000),

  3                                  schemaOwnerFrom VARCHAR2(128),

  4                                  schemaOwnerTo VARCHAR2(128),

  5                                  schema CLOB,

  6    CONSTRAINT xdb$moveSchemaTabC1 UNIQUE (schema_url, schemaOwnerFrom))

  7  ');

  8   EXCEPTION

  9      WHEN OTHERS THEN NULL;

 10  END;

 11  /

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> show errors;

No errors.

SQL>

SQL>

SQL> @?/rdbms/admin/sqlsessend.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/3 2018/07/25 13:50:02 surman Exp $

SQL> Rem

SQL> Rem sqlsessend.sql

SQL> Rem

SQL> Rem Copyright (c) 2013, 2018, Oracle and/or its affiliates.

SQL> Rem All rights reserved.

SQL> Rem

SQL> Rem    NAME

SQL> Rem      sqlsessend.sql - SQL session end

SQL> Rem

SQL> Rem    DESCRIPTION

SQL> Rem      Any commands which should be run at the end of all oracle

SQL> Rem      supplied scripts.

SQL> Rem

SQL> Rem    NOTES

SQL> Rem      See sqlsessstart.sql for the corresponding start script.

SQL> Rem

SQL> Rem    BEGIN SQL_FILE_METADATA

SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/sqlsessend.sql

SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/sqlsessend.sql

SQL> Rem    SQL_PHASE: MISC

SQL> Rem    SQL_STARTUP_MODE: NORMAL

SQL> Rem    SQL_IGNORABLE_ERRORS: NONE

SQL> Rem    END SQL_FILE_METADATA

SQL> Rem

SQL> Rem    MODIFIED   (MM/DD/YY)

SQL> Rem    surman      05/04/18 - 27464252: Update SQL_PHASE

SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts

SQL> Rem    surman      03/08/13 - Created

SQL> Rem

SQL>

SQL> alter session set "_ORACLE_SCRIPT" = false;

 

Session altered.

 

SQL>

SQL>

SQL> @?/rdbms/admin/prvtxdbschmig.plb;

SQL> @@?/rdbms/admin/sqlsessstart.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/3 2018/07/25 13:50:02 surman Exp $

SQL> Rem

SQL> Rem sqlsessstart.sql

SQL> Rem

SQL> Rem Copyright (c) 2013, 2018, Oracle and/or its affiliates.

SQL> Rem All rights reserved.

SQL> Rem

SQL> Rem    NAME

SQL> Rem      sqlsessstart.sql - SQL session start

SQL> Rem

SQL> Rem    DESCRIPTION

SQL> Rem      Any commands which should be run at the start of all oracle

SQL> Rem      supplied scripts.

SQL> Rem

SQL> Rem    NOTES

SQL> Rem      See sqlsessend.sql for the corresponding end script.

SQL> Rem

SQL> Rem    BEGIN SQL_FILE_METADATA

SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/sqlsessstart.sql

SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/sqlsessstart.sql

SQL> Rem    SQL_PHASE: MISC

SQL> Rem    SQL_STARTUP_MODE: NORMAL

SQL> Rem    SQL_IGNORABLE_ERRORS: NONE

SQL> Rem    END SQL_FILE_METADATA

SQL> Rem

SQL> Rem    MODIFIED   (MM/DD/YY)

SQL> Rem    surman      05/04/18 - 27464252: Update SQL_PHASE

SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts

SQL> Rem    surman      03/08/13 - Created

SQL> Rem

SQL>

SQL> alter session set "_ORACLE_SCRIPT" = true;

 

Session altered.

 

SQL> SET ECHO ON

SQL> SET FEEDBACK 1

SQL> SET NUMWIDTH 10

SQL> SET LINESIZE 80

SQL> SET TRIMSPOOL ON

SQL> SET TAB OFF

SQL> SET PAGESIZE 100

SQL> CREATE OR REPLACE PACKAGE BODY sys.xdb_migrateschema wrapped

  2  a000000

  3  1

  4  abcd

  5  abcd

  6  abcd

  7  abcd

  8  abcd

  9  abcd

 10  abcd

 11  abcd

 12  abcd

 13  abcd

 14  abcd

 15  abcd

 16  abcd

 17  abcd

 18  abcd

 19  b

 20  75aa 1ada

 21  ymGCKKaQ83ScxG5JOOOWugBr5Kwwg81MHr+DYG+aZD0PfwzCW31rtFNV371sOy/CO28vjaIG

 22  zwYvyAufQmKPev8pLJ2/QeyI8BxXLRNhCDFIZ0W/i/+QmhBHt/fN5CcPU00e/odr/k6xtz0G

 23  10gj4/4DEzAq6YcR953Mj9pIIHZTFl/hII57nLnd4rOq1DUsQoTSFctuDNpX9sqZiLUWDbXA

 24  fTllEKTqJTKWNKilgUXWuAbQjLNXJxWqt7x49fY9czjEtEDDUZWSzYudCHZMIJ/jnq0ZYsyU

 25  c/na0wZ3BW+r0COfNga8nhPq7bmGN7UVY7vvmG3meOB3TdYnohlQzZjcRuI5cxZLB+6+LHRU

 26  +BUbc+ohdOs0qzdxJPnSPw2iIIZVPy2Tv8U7i95ElR4xybxUnSnD1LM6eK7FJAAXcSS8mpZP

 27  zSgspwj5G0ooq/2OoBkkHfHcQ4u4tCLarUL+uUxOVcTX17nXoR4p67BJ6ji1FIMusRIROuWs

 28  V8bkDY/Krqk4e7rU1zHJMuGW1X2+gZQmI8cStBkcUMUiaH6LkJ4v6p6iW0xyhoFrXjovu9VJ

 29  NG6ENIPR+6gRKgBHSHuNT2unFwowlBC01ETD1jBDO5xtMvNVluvE6wlG7iAQnm3gexcDcadR

 30  Yw6bmjuGpOhdfdfJdcax6wVDqOy8h9RjJ1OH3JqOPvUQmKvN3wXm7ZocuA3pLpJLB8FEonht

 31  rdKf+7MeKSLUwJTIYjPBE0M02OFrv54SEg1U+AMxzdGuqOuxjxboA4RSO7Ft/gg0dX7tVNFO

 32  PZgZR4ajc/5PneKmRGxYeWEspR+SeUVV0STPajEzESyWhoh1d3IPtdxCoTEPczBmLSWWbsE6

 33  6sPykiFSynIKgSNOFfBuvJoQOP72MhR2u0KPKN2Ng7alw7aL2y+1nLbtNPUt2upxaDb4m9wG

 34  WmzqSCgS4865Sl3GHmfKnIxOul/M4KjCMl1JedmVFKFx3rojUDdnfzS4cvgz6V3+EFujEnF9

 35  Pstn5/fAlWqjHoq9RvOrnFZRo2+oFpB4X8+Ss5exxFzc628Jw5TfJnXsUWXvXYrJRT+2T9YL

 36  9N9z135FwwZdO3/myUS6qxfegi0KI2BC+tZ79zeVK+MKFGHCWqvTpDVweayrqd1IBsuZa7Kc

 37  uVxdWzPZo2KbSWoIDh45KvDlSrw1O+sjAMzq67v54ZJBMADzjEIfhbtUnLfRUEQnGntjv5Wy

 38  3cOZNENVHZpivTolE1a27pFASgrZIQ3iH6+dMeURw5oDnSftN6OasWt8zqpRQsYsdDRNczrK

 39  wiEYUNKSLx2OxMel3nsaSx1aMVe/HfEs52eKmZZfaI/ciE25ROOnkdTxzw4RVap8hTgXaNGF

 40  yb9SL52TZ6IZ3j7NONafirLgvnlCA2yfvFvc9IQh8aVShmvx01NLcnZ8iPrCx4iyYFpAss+W

 41  N+xGxWpOhkn0Xh1NJb/X2NzRpbuhliqeKRZ82nKzvlUoVeCSmb/jx/kvA6KvUxQHVc1ep/EN

 42  fLYS4qzw0kGR5LSj9QwRHujmEKAkkvB0XEoNt9qIobwCcBhSbSVwnfCG+1L2XZVY7um8iMyu

 43  BiC1v6k3B9O5Cd+M1iwSv6y1okFWTz5pT5cQfL6L7y1/9aKVQoFBVe0ZhZbilLUoXi1n12ml

 44  VKv+khSPPsAWzDftH5I3SNMtW4auN+oFNu1B2uHmv30wSDCw9c3/QUUE9C/SqDNhL7LdeQhd

 45  WSPTYBndAB3SNboONwHdtG3Efxxlcm/IKZyiFMPk46ysmemDng8cA8uIgx0fMomTng5PcMDk

 46  6Mp3Hq8Z4wts/xNpjdO+NYHvDD88j3sSj2+pNsk6Q90akeLhE2RNLl9LVtB7UaSMvUhaBDHW

 47  zAcN9JcOL2F0P63wc5979K876KxESH3QHUnZ5udxCIKGSxTar1Su6n7FZuP+EwVV2OfQDohN

 48  Q1galuRH2oANuGgCa5M7u51GWZS2eKOy0h3/oQp+gyYY/BZF9QfxoZsVFpb0aK5aYlTpGUKf

 49  b+9gxVNGFAWv9ABc/epUOyPCtUska5Ml+ZEhuTMAwUYDbzBvgiDWw55Ew95c7cXzZ/BFQOLv

 50  pM8iHHv3wShratHKW6D4v1lpx8+HpOXQ1K8vMhCdu4NTxdmIYPQfhk1wG4vOhjjD+EXceY5e

 51  bzkTX6jLYdEM2czIrwJUo59cpJJc2OYbv/Ba+HaQPCbfXFzeNRT6NdDr7gg1jBZU462mhne/

 52  asj2guv6rObs6WXKjEwUq92lom0A7Gaj3/1Jw5McjYLMJXzLnmn32glHp9IBn5/iXFBaYy1U

 53  9HzUDnkUHbu44NBg7pF2QnNLoZDBRhSDDeQVq6mSIsOh0/YxxLBlGeAtaRV53KPyb6gSGDa8

 54  q+KAN5nQkA8p2Te/84+poqmLX9ZQKDB21iB6eck5czL/FvCRVtgopJHoOuqPnRopq/Ffukan

 55  u9yurh6XmPptR8T0N95rocSwNLF1D+JqVOYYqUZx1prV9RJYGGrU8vLk8w1dVBtLnnE2Udfd

 56  M+a/4XLvv3xS1t0oJ5rPtVW/rryx19tbdqEAMRu2X/laMA7HeMDRjeLc1IZQ1s3bmL7qh0ZV

 57  A3FonUA/9cvNiqH1odAYfMtEoTFpBZeb9zk53s9IM7eTeveug661widGIkQ/UaPadDcmKcuq

 58  /fhw+LQfYy5Pk61l2Qm8IYp4U8ZaKWKYdbxbudwzY72YayatfInowcZp1uwsjFnXF+5ew5Oe

 59  sDuwO0mTNammYjW7SX691GoAxNbXyJGgBrSLEfr3MD6yxusQbA2EpHXvYmP5iRjOM94e+Kp4

 60  yPuNLGMC2uX7GL4Kb7jyMBdicP741hpEhKfq4dXO759QISP91vUnyrGZz6fLl4jZf7d2v27u

 61  FoVUkxw3AhUzWra9GtgO0srWTnDbCU66LllenCvMhd1VB8wcc7jUJPWqvbolF+nme+2OPvDX

 62  BLPEq5Mg/9jSFZEKWAAI6OKubioD6JfDRadSrGpUcBhpOKeTzxvBjNzw3gQSlYDJWzHMBSvq

 63  p8jkA9qJ9clW9J95Wx/0JBG7uJwA/KC8Qw0Yu0Uhh7tSSSdgls9ksO2sCABSzqnRbvDvjGFD

 64  WEHL6fPzW4hcQegiDF4LeevegtvqOSr+G6UuSX78xfMyuCV2yCtgkVvIwEKzbPz0OkHIPCYJ

 65  e/m44woevOGc7jfXzCM2UQMYXoNdwCRujxe/027XeTjfe7sGhcvpHPJnDdZal7P6Z/uUR8zX

 66  v/1hjJm7/cVukGYpoMOdbq5OGKIrO1cidhlUHTD5dZF3wG0aiYPx5hvedl0CXIrNvR3lcILb

 67  fo3JApxpNcEidG4KeY3VuIAQBTd2jrx4aY2CK4JOS3Rgh7J8wtRpxNdTR7c53Ze9aNUR7xwX

 68  yzlIctZw6O0yaPNB7cKiXyxmLOHAL/FLbg0pcEWHIMvnv2xmbXwahiWb+6MFJ+NDgY07+hAk

 69  bcQHlcmyVzV79VnINRRf9vtFO8YgfL7j8DZqrtpeWzh1//NtNSf3ANt+v4MimtNNoRjCWpnI

 70  VN2U2sqPOt2kYH2XzW36AM5W36up02AvLA8qgHw/HZzVbwI918iVdqe4DNSXjVZgBUE4owo9

 71  gtbVQvsOX3q0wKXYKjrU7Ukz7VoxBa9Osxtd8bdrgq813O//ctINZuttsvFcY2lBALPq8EQl

 72  mlXbqYEwiCxvHnh9I8ST7nO+0O6vxYcswnZsLSoKjmCKFp400TOe6s4LrLbkYrPH0WfCoS7d

 73  UCfk24+F8/iW0Bgp7ku6NLwA2zpTae0H3UCNGTVDsmAQe7aZqKGHRm1l9/eRIc+z2pc3YKuk

 74  J5CSzrA2IsKo8Q4UXj/vokrpod4O5PdUMZYrBQF6qPPamm2JNm2GhkqYUQDBhRFSSmdtx3aj

 75  oPK7vdH/H1NH3wTRuwwGYzZxij+qZaXuKsJb0yuKNULYkbPJcOniCEGp8T5zmlsW60PAwoPd

 76  ThVoGl+uvtIAxhBwbhGNPzeX7TqJE9fgb6p7oEa5IAu/zo8woVz4RH/+p6DbtZjwsUN8t5mI

 77  s0kfNyQcLiMOnW1BNpxth0JTkbPJ3eniCEHI8T5zmls+IJ8TBc2RHA+qe8g2KMvxj5laHKYU

 78  mObIiYS9QUIwML9RsZJEYv6tmJuhVfIlMq8s4XWWuUNrpl6K4PGTbxSuO/zB4VYBEIfhdLrF

 79  KpZJl9km6GPb0mBwKWSKSpxu6xaB37qA1aOBZxOxtUKiDaIFuhcT6pEMJZwkbGJLgdnLhv/g

 80  PPWwCM8hdm90dH8gXK/P9DQLw5PwJ7Ar5bkINKl9Al1LZ/Qd4ZfcOvgiYqnZb0n7aNWWXxXA

 81  Sfv88nlYzbAzmAvNeCoPSwu9EQ+NiWd59H3ANyEK5BgKCqguu9/vaARTWbBvoMc+MAvYeM84

 82  /u0cGVelLQeK0NqgjQMmvfeMy+hMLqddKYelA2jbBWweTNvZLckITMF1eq0zZ1jwW4zbk9q+

 83  4VaEB9j66tc29epZ00tYX3ULZvjgoWcjRXcJZiY4hLafbb74f6ABFxcELXK029wFK//JssOK

 84  qFmn9w2MadJn/BR14h+t8j7l+uOuvje9k+naLx4lQSBqGWrhlCG4TSNfBoE4/vt0uZ8AgFxC

 85  2i2tz+8ytz7M9GGwJXaF2dubwO402AbChiPy9EOU6MafrwpEwdjdbuinxDUBXaZDtGCTfvxK

 86  P74vc1G9KTaNhRBYg5slSVFMkK9LTCtuNGP6BpzXaSbXafGac27+omPbWXnwwrlUZPGIigY9

 87  i3mKO5rcnG2+tZIkrfQ1xX3EjRK/pa1qCH3HekH1/h1a6AOEQpx1dH3wMIWT1OuBzadX+J2B

 88  CEF3WOH6KXBq2YNvDwapDvwtXrpOm0pkXfD3FMJuVv7nOp7U9L43zH89fuNsF45dJyqcAuXr

 89  bLzRQBmlQOKcxPj9oJ1YuQY6kZgxVgqqSIdlPODW0c+mNiXOTUIZLAeca/1ADvlyqcMymucP

 90  +45FGCDKxP3MroMrclfr1my0WeH+dvn94HcJKGCbphpfkKlK8HldsD+dxvF0OilQECYAXLkx

 91  d0ilBtrfTJ8CH7lh0twMzwVmT/q2Q0nWTeFKotw6GjA5H6FipUjSGtfpcZJxjImAA6ZEe+IC

 92  Msc5F96twNyH37Mgt1ejbUqandYblOKMoPSttYmrLt9fg/TczRpuz2fsvYIhsC8fPtn/bu75

 93  djLDNsR+BmVFxR3icqEP/NSvogHck1Stxt2XrTkr/yywG8AXNz52iO9vYkSRsIiHydvFpBNW

 94  I2K2yNAIlHD6Yll1KxJdcTMbMItgvO29BJak27nL+Gv/rYEXVLu8nzp1fZkBDqm2cM/oLnRF

 95  tynGvf/B9kCPJ1fUbC46WCBNFFkNRn9JdhhRCU5NArwTP16wDsh/SD5LPhh9R9GFdvl8iMJv

 96  IWBXX3JjNeXZ1SfDg1EDBfcBUDoGsHqJRWwxij9GtDLeENU1LCJRKYDSdegGx3VW9VJvEo6B

 97  ebzBykY/UB0MfbzyJ7fc2mxOajBFgHGpxmKQGXZ3u66lBcih10lAkWSD/xbj+AdRrEZyoUjB

 98  cOhxtjJ3Yzg0Y0cOLtsvQRKZe6JpG3Ibl4UAF+0vqHsZagzcMErjVUCXbSsrIo36gW9PcRcc

 99  aVmSJZUdQsEAGOzudcpu78raik3bzxUJCe2V3B5OI0+MXkLYuvlu+b9+cApxrnAp1RQiu3G5

100  4k4FWCHMJ2tT4oNQ0yOGFJir95bnA1apQe3JqftwKmnjsl0FXfRxQsk70FQNC2SwoGXBm+8V

101  eDj7nxVk5vw6KugCYKw4RKiLNRSCUEDIO/dNqFwJZT9Dauo5F1JYGsllZbXWE0fX9aqCBUcp

102  OEbxM8eWUwkQGE0eJcuWpJ3BG0HLsKga0SMu4U7Xln2kE1kEkxGnRWee4BAyIZEwgXZ45EBc

103  GP5dHGmoyQexnEkfVCSMkhcHxCa4OKSdMVbBGsYvuWU8XokPTCt4ZWSGvz7JG2CDt12MVNJw

104  8bRSEU6ZXsdJgV/m8QdTlnqFFKWuQLTzMr+34s3kq0Rhhhgej0amwBYbgpLsIBbSUa6rZTxr

105  7nNQAMLjsLI+Oq6gFvDhd44PpESmr3zx6DD3euFpm7XDua6J5cFzi45u/+gm3v5zFzH6EBuq

106  JnH9cwHCzVHLkviLaFqYbYyfYJxHMNHRB7gBlfyM1mFUHxtB+8Hu5zQRAnsSBh2lyS3yA1E2

107  J87YMocNrA5I1IB6RO4Ki7A59TiCk5guZFePnM3kLzcPKT8unUWzKIz47WHMGutsH4ApmoCW

108  91FHTwB/Vhm0c+eSAkCOoqfmaefYSCBlm3M/DXOwj8UNNtIOokS/YKe6adEDddiMSb+tOs5j

109  kaST6Zn6RdcBVsYGE/0K9RdsD6QqmNaln99t/vgeBWys+2rsXwTF/Dxb/MOlcnmFROgQ1rxA

110  nMK1YToVBqWWHh/bEQPGAGHCxMhbq4aBQio/B3oa9s4fM34jD9+9NlwiQDjObeJAPWExZvHt

111  OSVAbcIFYpvNaiW/o4l8yJjNYVc8WsLdQXQYeM7+/DN4bTvSauM5aMHN3US3ylsExegPyD6P

112  6UcOCTRQeqsVlRFyNEa45Btf5k6wnZH0/pN+4tfRDsWyZr4pE19PGXVg/o+hLn3uP/azLcGk

113  /7KJ/XrJeCq0VpxksAO1UWqy+XO3hDQFVUgoCHreVyAPpj540QMQaBYIKd5FgkcFAquIwf1w

114  tNX1PfdJsinwpYOQ+pvfUYlkO1U6U//G/DFrupJaITinDbHWxn+jeaHFCWSeSb2wDMtiNagY

115  a6C1CKpi6wfh

116

117  /

 

Package body created.

 

SQL> show errors

No errors.

SQL> @?/rdbms/admin/sqlsessend.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/3 2018/07/25 13:50:02 surman Exp $

SQL> Rem

SQL> Rem sqlsessend.sql

SQL> Rem

SQL> Rem Copyright (c) 2013, 2018, Oracle and/or its affiliates.

SQL> Rem All rights reserved.

SQL> Rem

SQL> Rem    NAME

SQL> Rem      sqlsessend.sql - SQL session end

SQL> Rem

SQL> Rem    DESCRIPTION

SQL> Rem      Any commands which should be run at the end of all oracle

SQL> Rem      supplied scripts.

SQL> Rem

SQL> Rem    NOTES

SQL> Rem      See sqlsessstart.sql for the corresponding start script.

SQL> Rem

SQL> Rem    BEGIN SQL_FILE_METADATA

SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/sqlsessend.sql

SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/sqlsessend.sql

SQL> Rem    SQL_PHASE: MISC

SQL> Rem    SQL_STARTUP_MODE: NORMAL

SQL> Rem    SQL_IGNORABLE_ERRORS: NONE

SQL> Rem    END SQL_FILE_METADATA

SQL> Rem

SQL> Rem    MODIFIED   (MM/DD/YY)

SQL> Rem    surman      05/04/18 - 27464252: Update SQL_PHASE

SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts

SQL> Rem    surman      03/08/13 - Created

SQL> Rem

SQL>

SQL> alter session set "_ORACLE_SCRIPT" = false;

 

Session altered.

 

SQL>

SQL>

SQL>


Ensure that the trigger SYSTEM.EBS_LOGON is valid by connecting to the database using SQL*Plus as SYSDBA and running the following compile command:


SQL> alter trigger SYSTEM.EBS_LOGON compile;


Perform patch post-install instructions


SQL> @?/rdbms/admin/dbmsxdbschmig.sql

SQL> @?/rdbms/admin/prvtxdbschmig.plb


This completes the upgrade process using dbua. In my next post,  https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-3.html we will see the post upgrade steps which are very much important in terms of converting the db to single tenant architecture




No comments:

Post a Comment