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)
Comments