Oracle APPS DBA pieces: 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

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

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

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

Version 19.3.0.0.0

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

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> 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.

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

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> 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> @?/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 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> @?/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 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 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>

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

Oracle APPS DBA pieces

Pages

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

No comments:

Post a Comment

Traffic