top of page
Search

Oracle EBS Database Upgrade 12c to 19c - Part 2

PREPARE for UPGRADE


1. Make sure all are VALID

SQL> set pagesize 500

set linesize 300

col COMP_NAME format a40;

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;SQL> SQL> SQL>


COMP_NAME STATUS VERSION

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

JServer JAVA Virtual Machine VALID 12.1.0.2.0

OLAP Analytic Workspace VALID 12.1.0.2.0

OLAP Catalog OPTION OFF 11.2.0.3.0

Oracle Database Catalog Views VALID 12.1.0.2.0

Oracle Database Java Packages VALID 12.1.0.2.0

Oracle Database Packages and Types VALID 12.1.0.2.0

Oracle Machine Generated Data VALID 12.1.0.2.0

Oracle Multimedia VALID 12.1.0.2.0

Oracle OLAP API VALID 12.1.0.2.0

Oracle Real Application Clusters OPTION OFF 12.1.0.2.0

Oracle Text VALID 12.1.0.2.0

Oracle XDK VALID 12.1.0.2.0

Oracle XML Database VALID 12.1.0.2.0

Spatial VALID 12.1.0.2.0


2. INVALID Object Count

SQL> col OBJECT_NAME format a30;

col OWNER format a20

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type

from dba_objects where status='INVALID' order by owner,object_type;SQL> SQL> 2


OBJECT_NAME OWNER OBJECT_TYPE

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

DDR_R_ORG_BU_DN_MV APPS MATERIALIZED VIEW

MRP_COMPANY_USERS_SN APPS MATERIALIZED VIEW

OZF_EARNING_SUMMARY_MV APPS MATERIALIZED VIEW


sqlplus / as sysdba <<EOF

@$ORACLE_HOME/rdbms/admin/utlrp.sql

SET SERVEROUTPUT ON;

EXECUTE DBMS_PREUP.INVALID_OBJECTS;

exit;

EOF



3. Check TIMEZONE

The timezone should be less than or equal to the target database timezone version

SQL> select version from v$timezone_file;


VERSION

----------

18

The time zone files supplied with Oracle Database 19c is version 32. We can check the pre-upgraded database timezone as, if < 32, then this database upgrade will upgrade the database to Timezone version 32

if > 32, then BEFORE the upgrade you MUST patch the target 19c $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.


4. Verifying Materialized View Refreshes are Complete Before Upgrade

if any materialized view refreshes are still in progress. Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

no rows selected


5. Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

SQL> SELECT * FROM v$recover_file;

no rows selected


6. Gathering Optimizer Statistics to Decrease Oracle Database Downtime

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.


7. Purge DBA_RECYCLEBIN

SQL> select count(*) from dba_recyclebin;

COUNT(*)

----------

0


8. PRE-UPGRADE

$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TXT

[oracle@ebsdb ~]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar

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

PREUPGRADE SUMMARY

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

/u01/app/oracle/cfgtoollogs/PROD/preupgrade/preupgrade.log

/u01/app/oracle/cfgtoollogs/PROD/preupgrade/preupgrade_fixups.sql

/u01/app/oracle/cfgtoollogs/PROD/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@/u01/app/oracle/cfgtoollogs/PROD/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u01/app/oracle/cfgtoollogs/PROD/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2021-11-27T15:48:39



SQL> @/u01/app/oracle/cfgtoollogs/PROD/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script


Auto-Generated by: Oracle Preupgrade Script

Version: 19.0.0.0.0 Build: 11

Generated on: 2021-11-27 15:48:10


For Source Database: PROD

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

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

1. parameter_min_val NO Manual fixup recommended.

2. parameter_obsolete NO Manual fixup recommended.

3. invalid_objects_exist NO Manual fixup recommended.

4. amd_exists NO Manual fixup recommended.

5. duplic_sys_system_objs NO Manual fixup recommended.

6. exclusive_mode_auth NO Manual fixup recommended.

7. olap_page_pool_size NO Manual fixup recommended.

8. case_insensitive_auth NO Manual fixup recommended.

9. mv_refresh NO Manual fixup recommended.

10. hidden_params NO Informational only.

Further action is optional.

11. underscore_events NO Informational only.

Further action is optional.

12. component_info NO Informational only.

Further action is optional.

13. parameter_deprecated NO Informational only.

Further action is optional.

14. rman_recovery_version NO Informational only.

Further action is optional.

15. invalid_ora_obj_info NO Informational only.

Further action is optional.

16. invalid_app_obj_info NO Informational only.

Further action is optional.


PL/SQL procedure successfully completed.




sqlplus / as sysdba <<EOF

@$ORACLE_HOME/olap/admin/catnoamd.sql

exit;

EOF




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');


set pause off

set heading off

set pagesize 0

set feedback off

set verify off

spool dropsys.sql

select 'DROP ' || object_type || ' SYS.' || object_name || ';'

from dba_objects

where object_name not in ('AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','AQ$_SCHEDULES','PRODUCT_USER_PROFILE','SQLPLUS_PRODUCT_PROFILE','PRODUCT_PRIVS') and object_name||object_type in

(select object_name||object_type

from dba_objects

where owner = 'SYS')

and owner = 'SYSTEM';

spool off

exit



Enable ARCHIVE LOG

SQL> Shutdown immediate

SQL>Startup mount

SQL>Alter database archivelog;

SQL> Alter database Open;

Verify Using

SQL> ARCHIVE LOG LIST



Enable flashback and create a restore point

DB should be in MOUNTED

SQL>Alter database flashback on;

SQL> Alter database Open;

Verify Using

SQL> select flashback_on from v$database;


SQL>create restore point BEF_UPG guarantee flashback database;




How to enable FLASH RECOVERY AREA in Oracle Database 12c

show parameter db_recovery_file

select * from V$RECOVERY_FILE_DEST;


SQL> alter system set db_recovery_file_dest_size=30G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=both;

System altered.


SQL> show parameter db_recovery_file

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size big integer 20G


select * from V$RECOVERY_FILE_DEST;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID

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

/dbaclassdb/oradata/FRA 2.1475E+10 0 0 0 0


Make the archivelog destination the same as the flash recovery area:

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oradata

Oldest online log sequence 166

Next log sequence to archive 167

Current log sequence 167


SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;


System altered.


SQL> archive log listl

SP2-0718: illegal ARCHIVE LOG option

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 166

Next log sequence to archive 167

Current log sequence 167

SQL> select * from v$flash_recovery_area_usage



SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> Alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES


SQL> create restore point BEF_UPG guarantee flashback database;

Restore point created.


flashback database to restore point BEF_UPG;



SQL> /


SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID

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

5965200000000 4 YES 1048576000 01-DEC-21 11.04.05.0 YES BEF_UPG 0

00000000 PM



Take RMAN Backup

CONFIGURE CONTROLFILE AUTOBACKUP ON;

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;



To check REDOLOG Groups and MEMBERS and STATUS

SQL> col member format a50

SQL> select GROUP#,TYPE,MEMBER from v$logfile;


SELECT

a.GROUP#,

a.THREAD#,

a.SEQUENCE#,

a.ARCHIVED,

a.STATUS,

b.MEMBER AS REDOLOG_FILE_NAME,

(a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP#


ADD NEW LOG GROUP

alter database add logfile

('/u01/app/oradata/log03a.dbf',

'/u01/app/oradata/log03b.dbf') SIZE 1000M;




Check FRA USAGE

break on report

compute sum of percent_space_used on report

compute sum of percent_space_reclaimable on report

select file_type,

percent_space_used,

percent_space_reclaimable,

number_of_files,

con_id

from v$recovery_area_usage

order by 1

/



col name format a7

clear breaks

clear computes

select name

, round(space_limit / 1024 / 1024) size_mb

, round(space_used / 1024 / 1024) used_mb

, decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used

from v$recovery_file_dest

order by name

/



col name format a7

clear breaks

clear computes

select name

, round(space_limit / 1024 / 1024) space_limit_mb

, round(space_used / 1024 / 1024) space_used_mb

, percent_space_used

, percent_space_reclaimable

, percent_space_not_reclaimable

from v$recovery_file_dest

, ( select sum(percent_space_reclaimable) percent_space_reclaimable

, sum(percent_space_used) percent_space_used

, sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable

from v$recovery_area_usage)

order by name

/


Upgrade step

Sourece 19c env

$ORACLE_HOME/bin/dbua -keepEvents


After completion check below

SQL> select banner from v$version;

BANNER

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production



SQL> @/u01/app/oracle/cfgtoollogs/dbua/upgrade2021-12-03_12-33-58PM/PROD/postupgrade_fixups.sql



SQL> show parameter SEC_CASE

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean TRUE

SQL> !ps -ef | grep pmon

oracle 11575 11542 0 20:40 pts/5 00:00:00 /bin/bash -c ps -ef | grep pmon

oracle 11577 11575 0 20:40 pts/5 00:00:00 grep pmon

oracle 29251 1 0 15:54 ? 00:00:01 ora_pmon_PROD


SQL> show con_name

CON_NAME

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

PROD

SQL> show con_id

CON_ID

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

0

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false;

System altered.

SQL> show parameter SEC_CASE

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean FALSE



SQL> alter trigger SYSTEM.EBS_LOGON compile;


Trigger altered.


***********************************************************************************************************************

ORA-38880: Cannot Advance Compatibility From ... Due To Guaranteed Restore Point (Doc ID 2600985.1)

SQL> column name for a17

SQL> column TIME for a30

SQL> set linesize 375

SQL> set pagesize 375

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

FROM V$RESTORE_POINT

where GUARANTEE_FLASHBACK_DATABASE='YES';

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE

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

GRP_1638515620347 5.9652E+12 03-DEC-21 12.43.40.000000000 P 4 YES 1.2583E+10

M

SQL> drop restore point GRP_1638515620347;

Restore point dropped.

*******************************************************************************************************************************



SQL> show parameter compatible


NAME TYPE VALUE

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

compatible string 12.1.0

noncdb_compatible boolean FALSE

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


System altered.


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


shut immediate

startup


SQL> show parameter compatible;

NAME TYPE VALUE

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

compatible string 19.0.0


$ORACLE_HOME/OPatch/datapatch -verbose


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

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


[applmgr@ebsapp1 ~]$ ls -lrt $APPL_TOP/admin/adgrants.sql

-rwxr-xr-x 1 applmgr oinstall 108545 Nov 5 17:14 /u01/app/ebsapp/fs2/EBSapps/appl/admin/adgrants.sql

[applmgr@ebsapp1 ~]$ scp /u01/app/ebsapp/fs2/EBSapps/appl/admin/adgrants.sql oracle@192.168.56.101:/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/admin/



[oracle@ebsdb dbhome_1]$ ls -lrt /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/admin/adgrants.sql

-rwxr-xr-x 1 oracle oinstall 108545 Dec 3 22:18 /u01/app/oracle/product/12.1.0/dbhome_1/appsutil/admin/adgrants.sql


@adgrants.sql apps


[applmgr@ebsapp1 ~]$ ls -lrt $AD_TOP/patch/115/sql/adctxprv.sql

-rwxr-xr-x 1 applmgr oinstall 1664 Nov 24 2012 /u01/app/ebsapp/fs2/EBSapps/appl/ad/12.0.0/patch/115/sql/adctxprv.sql

[applmgr@ebsapp1 ~]$ echo $RUN_BASE/

/u01/app/ebsapp/fs2/

[applmgr@ebsapp1 ~]$ scp /u01/app/ebsapp/fs2/EBSapps/appl/ad/12.0.0/patch/115/sql/adctxprv.sql oracle@192.168.56.101:/home/oracle



sqlplus apps/Oracle123 @adctxprv.sql Oracle123 CTXSYS


select count(*) from dba_objects where status='INVALID';


sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql


grant text datastore access to public;


[applmgr@ebsapp1 ~]$ ls -lrt $APPL_TOP/admin/adstats.sql

-rwxr-xr-x 1 applmgr oinstall 3450 Nov 5 17:14 /u01/app/ebsapp/fs2/EBSapps/appl/admin/adstats.sql

[applmgr@ebsapp1 ~]$ scp /u01/app/ebsapp/fs2/EBSapps/appl/admin/adstats.sql oracle@192.168.56.101:/home/oracle



alter system enable restricted session;


SQL> @adstats.sql

Connected.

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

--- adstats.sql started at 2021-12-03 22:35:51 ---

Checking for the DB version and collecting statistics ...

PL/SQL procedure successfully completed.

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

--- adstats.sql ended at 2021-12-03 22:43:00 ---

Commit complete.


alter system disable restricted session;



CONVERT TO PDB

Check SYSTEM, SYSAUX, TEMP tablespaces and add space if needed



--Create a PDB Descriptor

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

export ORACLE_SID=<NON CDB SID>

cd $ORACLE_HOME/appsutil/bin

perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -appsuser=apps -dbsid=PROD



cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs

cp PROD_initparam.sql PROD_datatop.txt $19C_ORACLE_HOME/dbs

[oracle@ebsdb dbs]$ ls -lrt PROD_datatop.txt PROD_initparam.sql

-rw-r--r-- 1 oracle oinstall 3408 Dec 4 10:39 PROD_initparam.sql

-rw-r--r-- 1 oracle oinstall 414 Dec 4 10:39 PROD_datatop.txt

[oracle@ebsdb dbs]$ pwd

/u01/app/oracle/product/19.3.0/dbhome_1/dbs


--Update the CDB initialization parameters

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

$ export ORACLE_SID=CDBPROD

$ sqlplus "/ as sysdba"

SQL> Shutdown immediate

SQL> startup nomount;

SQL> @$ORACLE_HOME/dbs/PROD_initparam.sql

SQL> alter system set LOCAL_LISTENER="ebsdb.localdomain:1531" scope=both;

SQL> shutdown;

SQL> startup;



Check for PDB violations

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

$ export ORACLE_SID=CDBPROD

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkChkPDBCompatability.pl -dboraclehome=$ORACLE_HOME \

-outdir=ORACLE_HOME/appsutil/log -cdbsid=CDBPROD \

-pdbsid=PROD -servicetype=onpremise



Create the PDB

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

cd $ORACLE_HOME/appsutil/bin

perl txkCreatePDB.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log \

-cdbsid=CDBPROD -pdbsid=PROD -dbuniquename=CDBPROD -servicetype=onpremise



Run the post-PDB script

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME

perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=$ORACLE_HOME \

-outdir=$ORACLE_HOME/appsutil/log -cdbsid=CDBPROD -pdbsid=PROD \

-appsuser=apps -dbport=1531 -servicetype=onpremise




Run AUTPCONFIG in EBS Tier

EDIT tnsnames.ora file with the below entry

CDBPROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL=tcp)(HOST=ebsdb.localdomain)(PORT=1531))

(CONNECT_DATA = (SERVICE_NAME=ebs_PROD)(INSTANCE_NAME=CDBPROD))

)

Add the below values in CONTEXT_FILE in both RUN and PATCH and run Autoconfig

s_dbport=1531

s_apps_jdbc_url_descriptor=NULL

s_applptmp=/u01/app/oracle/temp/PROD (utl_dir)

36 views0 comments

Recent Posts

See All

留言


Contact Me

Tel: 7989359581

Lakshminarayana0071@gmail.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon

Thanks for submitting!

© 2023 by Phil Steer . Proudly created with Wix.com

bottom of page