Oracle 11g to 12c Upgrade for Enhanced Performance And Feature: Guide

Oracle Database,Oracle E-Business Suite - June 15, 2023

by Rupesh Arbal, Senior Database Admin - DataPatrol Technologies

Know How to Prepare a Database to Upgrade

Prepare the database for Upgrade.

Pre-upgrade Steps:

1) Create a Directory for 12C Oracle

mkdir –p /opt/app/oracle_home/oracle/product/12.1.0.2

2) Run 12c silent installation Here I did a silent installation you can do using GUI and also apply the latest patch.

nohup /oracle_soft/local/bin/ora_silent_install --type=server_12102 --home=/opt/app/oracle_home/oracle/product/12.1.0.2 &

--To verify installation progress

cd local/install

$ps -ef|grep silent

$ cd local/install

$ ls -lrt |tail

-rw-r--r-- 1 oracle oinstall 6808 Mar 27 18:15 ora_silent_install_21705.log

/opt/app/oracle_home/oracle/local/install

grep -i 'Exit Status Code' ora_silent_install_21705.log

3) In the meanwhile, take level 0 RMAN Backup.

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

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

17544 RECVR AREA COMPLETED 03/26/19 22:07 03/26/19 22:08 .02

17555 DB INCR COMPLETED 03/27/19 18:02 03/27/19 18:10 .14

17558 DB INCR COMPLETED 03/27/19 18:23 03/27/19 18:35 .20 << ---Level 0 RMAN backup Completed successfully.

4) Verify the required PSU Patches has been applied successfully on the 12c.

$OH/OPatch/opatch lsinventory

#In case of patches aren't found then apply required PSU on new binaries

5) Make directory to hold the pre and post upgrade related files.

$ mkdir upgrade

cd /opt/app/oracle/upgrade

6) Copy files within dbs directory from 11g Home to 12c Home

cd /opt/app/oracle_home/oracle/product/11.2.0.4/dbs

pwd

cp * /opt/app/oracle_home/oracle/product/12.1.0.2/dbs/

7) Copy the Network Files from 11g Home to 12c Home

cd /opt/app/oracle_home/oracle/product/11.2.0.4/network/admin

pwd

cp * /opt/app/oracle_home/oracle/product/12.1.0.2/network/admin/

--Verify if all the parameters and Network files are copied from 11g to 12c.

8) copy data sql to oracle upgrade directory -

cd /opt/app/oracle/upgrade

cp /opt/app/oracle_home/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql .

cp /opt/app/oracle_home/oracle/product/12.1.0.2/rdbms/admin/utluppkg.sql .

9) Take backup of crontab

crontab -l > /opt/app/oracle/upgrade/cron_pre.lst

10) Comment cron jobs to avoid unwanted job trigger and make sure once upgrade done uncomment cronjobs.

--Comment Cronjobs in Single shot

1. crontab -e

2. ESC:

3. To comment conjobs =

Copy paste " 1,$s/^/##DB_UPGRADE##/g "

4. To uncomment cronjobs

Copy Paste 1,$s/##DB_UPGRADE##//g

11) Take a backup of current SPFILE as PFILE in upgrade directory.

* This is MUST in order to have orginial values of init.ora

* We will NOT modify this file

SQL> create pfile='/opt/app/oracle/upgrade/pfile_org_before_upgrd.ora' from spfile;

12) Now we need to create PFILE for upgrade pusprose where we will remove "Underscore" and other parameters.

$ cp pfile_org_before_upgrd.ora pfile_for_upgrade.ora

13) Run the pre_upgrade_info.lst script (It give destails of invalid object count,component status,character set etc.)

spool /opt/app/oracle/upgrade/pre_upgrade_info.lst

set pagesize 500

set linesize 200

column object_name format a30

column owner format a30

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;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by

owner,object_type order by owner,object_type ;

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

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

select owner, TRIGGER_NAME , status from dba_triggers where owner in ('SYS', 'GGSUSER') order by 1;

select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like ('%GGS%') order by 1;

col comp_name for a36

col status for a8

col VERSION for a15

col COMP_ID for a12

select name,open_mode from v$database;

select comp_id,comp_name,version,status,MODIFIED from dba_registry;

SET linesize 200 pagesize 200

col action_time FOR a28

col version FOR a10

col comments FOR a35

col action FOR a25

col namespace FOR a12

SELECT * FROM sys.registry$history;

show parameter spfile

select TZ_VERSION from registry$database;

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;

SELECT * FROM v$recover_file;

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

select * from dba_2pc_pending;

SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)

FROM v$parameter

WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';

SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';

col name for a70

SELECT name FROM v$controlfile;

col file_name for a70

SELECT file_name FROM dba_data_files;

col member for a70

SELECT group#, member FROM v$logfile;

SELECT GRANTEE,PRIVILEGE

FROM DBA_SYS_PRIVS

WHERE GRANTEE ='CONNECT';

SELECT grantee FROM dba_role_privs

WHERE granted_role = 'CONNECT' and

grantee NOT IN (

'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',

'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',

'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',

'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',

'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',

'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

set pages 0

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)

||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING

'''||L.HOST||''''

||chr(10)||';' TEXT

FROM SYS.LINK$ L, SYS.USER$ U

WHERE L.OWNER# = U.USER#;

select * from dba_tab_privs where table_name in ('UTL_FILE','UTL_TCP','UTL_SMTP','UTL_HTTP','DBMS_RANDOM') and grantee='PUBLIC';

spool off

--Verify the spool file before upgrade.

14) Now give 777 permission for /opt/app/oracle/upgrade

$ ls -lrt

total 584

-rwxrwxrwx 1 oracle oinstall 59118 Mar 27 19:01 pre_upgrade_info.lst

-rwxrwxrwx 1 oracle oinstall 14083 Mar 27 19:12 preupgrd.sql

-rwxrwxrwx 1 oracle oinstall 501555 Mar 27 19:12 utluppkg.sql

-rwxrwxrwx 1 oracle oinstall 6307 Mar 27 19:14 cron_pre.lst

-rwxrwxrwx 1 oracle oinstall 2193 Mar 27 19:19 pfile_org_before_upgrd.ora

-rwxrwxrwx 1 oracle oinstall 2193 Mar 27 19:23 pfile_for_upgrade.ora

15) Create restore point before Upgrade (for fallback plan)

(15a) Flashback must be enabled

SQL> select host_name, name,instance_name,open_mode,LOG_MODE,logins,DATABASE_ROLE,INSTANCE_ROLE,INSTANCE_NUMBER,STATUS from v$database,v$instance;

HOST_NAME NAME INSTANCE_N OPEN_MODE LOG_MODE LOGINS DATABASE_ROLE INSTANCE_ROLE INSTANCE_NUMBER STATUS

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

databasexpert.com databasexpert.com PROD READ WRITE ARCHIVELOG ALLOWED PRIMARY PRIMARY_INSTANCE 1 OPEN

SQL> show parameter db_recovery

NAME TYPE VALUE

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

db_recovery_file_dest string /opt/app/oracle_home/oraflsh01/

db_recovery_file_dest_size big integer 1009G

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

--Check exiting restore point if any

SQL> select name, scn, time, database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

no rows selected

(15b) Now create restore point prior upgrade

SQL> create restore point before_upgrade12_1_0_2_guar guarantee flashback database;

Restore point created.

SQL> select name, scn, time, database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE

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

BEFORE_UPG 1150809477 27-MAR-19 07.57.05.000000000 PM 1 YES 1073741824

RADE12_1_0

_2_GUAR

16) Prepare the DB for Upgrade by executing

-- Purge Recyclebin

SQL> purge recyclebin;

Recyclebin purged.

--check if only 4 duplicate objects exits.

SQL> column object_name format a30

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

OBJECT_NAME OBJECT_TYPE

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

AQ$_SCHEDULES TABLE

AQ$_SCHEDULES_PRIMARY INDEX

DBMS_REPCAT_AUTH PACKAGE

DBMS_REPCAT_AUTH PACKAGE BODY

Remark- Only above 4 duplicate objects should exisit. ok

-- Verify if DBMS_STATS.GET_PREFS is showing FALSE.

SQL> SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')

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

FALSE

--PRE-UPGREADE SCRIPTS

17) Run the PreUpgrade scripts from 'upgrade' location

cd /opt/app/oracle/upgrade

i)@/oracle_soft/local/sql/12cupgrade_scripts/2017/dbupgdiag

--o/p--> share path '/opt/app/oracle/upgrade' for script output.

ii) @/oracle_soft/local/sql/12cupgrade_scripts/2017/hOut.sql

--This script will create package body

1. iii)@/oracle_soft/local/sql/12cupgrade_scripts/2017/hcheck4

o/p-->Found 0 potential problem(s) and 0 warning(s)

iv) @preupgrd.sql

--Verify the outout of '@preupgrd.sql' carefully and check the logs

ACTIONS REQUIRED:

Review results of the pre-upgrade checks:

/opt/app/oracle_home/oracle/cfgtoollogs/preupgrade/preupgrade.log

Execute in the SOURCE environment BEFORE upgrade:

/opt/app/oracle_home/oracle/cfgtoollogs/preupgrade/preupgrade_fixups.sql

Execute in the NEW environment AFTER upgrade:

/opt/app/oracle_home/oracle/cfgtoollogs/preupgrade/postupgrade_fixups.sql

--iv a) Verify the "/opt/app/oracle_home/oracle/cfgtoollogs/preupgrade/preupgrade.log"

--Read this log file carefully if there are any steps needs to be done prior upgrade

Ex:-

IMP --> * All objects are in VALID state

* TABLESPACE MUST be adequate for upgrade

* Timezone value is showing old value ( i.e Timezone would be upgrade from 14 (old) to 18(new) post upgrade)

* --Action required as SYSDBA

EXECUTE dbms_stats.gather_dictionary_stats;

* Hidden Parameters

_datafile_write_errors_crash_instance = FALSE

_db_block_numa = 1

_enable_NUMA_optimization = FALSE

_external_scn_logging_threshold_seconds = 3052

_external_scn_rejection_delta_threshold_minutes = 57600

_external_scn_rejection_threshold_hours = 24

_ktb_debug_flags = 8

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

--- Read the summary section , there must be 0 Errors.

************ Summary ************

0 ERRORS exist in your database.

0 WARNINGS exist in your database.

2 INFORMATIONAL messages that should be reviewed prior to your upgrade.

Gather dictionary status

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Gather Fixed objects stats

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

16) Prepare Upgrade

16a) Shutdown the DB & Other services

16b) Edit /opt/app/oracle/upgrade/pfile_for_upgrade.ora

i) Remove 'underscore parameters' and other parameters startup the instance using pfile_for_upgrade.

ii)sql

SQL> connected to idle instance

SQL> startup pfile='/opt/app/oracle/upgrade/pfile_for_upgrade.ora';

ORACLE instance started.

Total System Global Area 8551575552 bytes

Fixed Size 2270360 bytes

Variable Size 5469375336 bytes

Database Buffers 3070230528 bytes

Redo Buffers 9699328 bytes

Database mounted.

Database opened.

ii) Rerun the @preupgrd.sql script

SQL> @preupgrd.sql

Verify MANUAL ACTION SUGGESTED

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

************* Fixup Summary ************

No fixup routines were executed. --> OK

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

17) Startup db in upgrade modeDB using new db from new 12c home.

i) Startup the db in upgrade mode.. from 12c home

$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 27 22:11:02 2019

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size 3731384 bytes

Variable Size 5469372488 bytes

Database Buffers 3103784960 bytes

Redo Buffers 13045760 bytes

Database mounted.

Database opened.

ii) Run upgrade OS command from'$OH/rdbms/admin/' path

$ cd $OH/rdbms/admin

ii) nohup $ORACLE_HOME/perl/bin/perl catctl.pl -n6 -l $ORACLE_HOME/diagnostics catupgrd.sql &

ii) Open alert log and tail the output

iii) Once upgrade script gets completed, Check errors

cat /opt/app/oracle_home/oracle/product/cfgtoollogs/t1c4d130/upgrade/upg_summary.log

$ cat /opt/app/orale_home/oracle/product/cfgtoollogs/t1c4d130/upgrade/upg_summary.log

---o/p -->

Function created.

Elapsed: 00:00:00.03

Oracle Database 12.1 Post-Upgrade Status Tool 03-27-2019 22:42:34

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:09:45

JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:02:13

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:54

Oracle XDK VALID 12.1.0.2.0 00:00:46

Oracle XML Database VALID 12.1.0.2.0 00:01:35

Oracle Database Java Packages VALID 12.1.0.2.0 00:00:16

Final Actions 00:01:22

Post Upgrade 00:00:02

Total Upgrade Time: 00:18:41

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

Function dropped.

Elapsed: 00:00:00.02

Grand Total Upgrade Time: [0d:0h:21m:29s]

check alert log for any error

---o/p end -->

iv) Check alert log for any errors

## POST UPGRADE STEPS :-

18) DB will be shutdown after upgrade , We need to strartup the DB

a) start the db & run @utlu121s.sql script

cd $OH/rdbms/admin

sql

SQL> startup

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size 3731384 bytes

Variable Size 5502926920 bytes

Database Buffers 3070230528 bytes

Redo Buffers 13045760 bytes

Database mounted.

Database opened.

SQL> @utlu121s.sql

PL/SQL procedure successfully completed.

CATCTL REPORT = /opt/app/oracle_home/oracle/product/cfgtoollogs/upgrade/upg_summary.log

PL/SQL procedure successfully completed.

Function created.

Oracle Database 12.1 Post-Upgrade Status Tool 03-27-2019 23:30:21

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:09:45

JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:02:13

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:54

Oracle XDK VALID 12.1.0.2.0 00:00:46

Oracle XML Database VALID 12.1.0.2.0 00:01:35

Oracle Database Java Packages VALID 12.1.0.2.0 00:00:16

Final Actions 00:01:22

Post Upgrade 00:00:02

Total Upgrade Time: 00:18:41

PL/SQL procedure successfully completed.

Function dropped.

-- Update Summary Table with con_name and endtime.

SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName, con_name = SYS_CONTEXT('USERENV','CON_NAME'), endtime = SYSDATE WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

b) Verify if catuppst.sql has been executed during upgrade.

$ cd $ORACLE_HOME/diagnostics

$ ls -lrt

-rw-r--r-- 1 t1c4d130 oinstall 24860104 Mar 27 22:42 catupgrd0.log

$ grep 'BEGIN catuppst.sql' *.log

catupgrd0.log:22:42:30

SQL> Rem BEGIN catuppst.sql <<---ok .

c) from above path view catupgrd0.log

$view catupgrd0.log

Press '/' and enter 'phase 70' it will search for result ..Ensure the result is as below.

[phase 70] type is 1 with 1 Files -->ok

d) Run the Post Upgrade scripts

cd /opt/app/$LOGNAME/oracle/cfgtoollogs/$LOGNAME/preupgrade

SQL> @postupgrade_fixups.sql

--Read the report /Fixup summary for any Post Upgrade Manual step ( other than Timezon )

e) Complile the INVALID objects by running @utlrp.sql

i) SQL> @?/rdbms/admin/utlrp.sql

ii) Verify the invalid object count

SQL> col owner format a16;

col object_name format a32;

select owner, object_name, object_type from dba_objects where status = 'INVALID';

no rows selected

ok -->

f)run dbupgdiag to view the summary post upgrade check below important points.

SQL> @/oracle_soft/local/sql/12cupgrade_scripts/2017/dbupgdiag

-Invali Objects

-Timezon ->14

-Duplicate objects -> None

-> No Java based objects own by SYS

There are 6 JAVA related roles

All Components ->VALID

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

Component Status

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

Comp ID Component Status Version Org_Version Prv_Version

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

CATALOG Oracle Database Catalog Views VALID 12.1.0.2.0 11.2.0.4.0 11.2.0.4.0

CATJAVA Oracle Database Java Packages VALID 12.1.0.2.0 11.2.0.4.0 11.2.0.4.0

CATPROC Oracle Database Packages and Types VALID 12.1.0.2.0 11.2.0.4.0 11.2.0.4.0

JAVAVM JServer JAVA Virtual Machine VALID 12.1.0.2.0 11.2.0.4.0 11.2.0.4.0

OWM Oracle Workspace Manager VALID 12.1.0.2.0 11.2.0.4.0

XDB Oracle XML Database VALID 12.1.0.2.0 11.2.0.4.0 11.2.0.4.0

XML Oracle XDK VALID 12.1.0.2.0 11.2.0.4.0 11.2.0.4.0

ok -->

g) Verify file permissions :

$ cd $OH/lib

$ ls -l libsqlplus*

-rw-r--r-- 1 oracle oinstall 2237742 Mar 27 18:10 libsqlplus.a <<--OK

-rw-r--r-- 1 oracle oinstall 1546540 Jun 18 2014 libsqlplusO.so <<--OK

-rw-r--r-- 1 oracle oinstall 1548705 Mar 27 18:13 libsqlplus.so <<--OK

SQL> set lines 200

col comp_id format a14;

col comp_name format a36;

col version format a15;

col status format a16;

select name,open_mode from v$database;

select comp_id,comp_name,version,status from dba_registry;

col owner format a20;

col object_name format a32;

select owner, object_name, object_type from dba_objects where status = 'INVALID';

set linesize 200

col action_time for a28

col action for a20

col namespace for a9

col version for a25

col id for 99999999

col comments for a24

col bundle_series for a14

select * from registry$history;

set linesize 200

col patch_id for 99999999

col action for a8

col action_time for a28

col description for a64

col status for a8

col version for a25

col bundle_series for a14

select patch_id, action, action_time, description, status, version, bundle_series from registry$sqlpatch;

set lines 200

col owner for a30

col TRIGGER_NAME for a30

select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like '%GGS%';

col property_name format a30;

col property_value format a20;

col description format a40;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

select name, scn, time, database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

show parameter compa

--O/P

$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 27 23:54:44 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 200

col comp_id format a14;

col comp_name format a36;

col version format a15;

col status format a16;

select name,open_mode from v$database;

select comp_id,comp_name,version,status from dba_registry;

col owner format a20;

NAME OPEN_MODE

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

PROD READ WRITE

SQL>

COMP_ID COMP_NAME VERSION STATUS

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

XDB Oracle XML Database 12.1.0.2.0 VALID

OWM Oracle Workspace Manager 12.1.0.2.0 VALID

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID

CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID

XML Oracle XDK 12.1.0.2.0 VALID

CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID

7 rows selected.

SQL> col object_name format a32;

set linesize 200

col action_time for a28

col action for a20

select owner, object_name, object_type from dba_objects where status = 'INVALID';

no rows selected

SQL> col namespace for a9

col version for a25

col id for 99999999

col comments for a24

col bundle_series for a14

select * from registry$history;

SQL> set linesize 200

col patch_id for 99999999

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES

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

03-NOV-15 08.36.03.742073 PM APPLY SERVER 11.2.0.4 7 PSU 11.2.0.4.7 PSU

03-NOV-15 08.37.09.177350 PM APPLY SERVER 11.2.0.4.4OJVMBP 0 OJVM PSU post-install

03-NOV-15 08.37.09.000000 PM APPLY 21068539 Patch 21068539 applied

03-NOV-15 08.37.10.170493 PM APPLY SERVER 11.2.0.4 7 PSU 11.2.0.4.7 PSU

02-AUG-17 06.22.47.680344 AM jvmpsu.sql SERVER 11.2.0.4.161018OJVMPSU 0 RAN jvmpsu.sql

02-AUG-17 06.22.47.727928 AM APPLY SERVER 11.2.0.4.161018OJVMPSU 0 OJVM PSU post-install

02-AUG-17 06.22.47.732343 AM APPLY 24315821 Patch 24315821 applied

02-AUG-17 06.24.30.754380 AM APPLY SERVER 11.2.0.4 161018 PSU 11.2.0.4.161018 PSU

02-AUG-17 06.30.29.000000 AM APPLY 24491261 Patch 24491261 applied

04-DEC-17 09.55.56.000000 AM ROLLBACK 24491261 Patch 24491261 rolled ba

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES

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

04-DEC-17 10.04.22.160349 AM VIEW INVALIDATE 8289601 view invalidation

04-DEC-17 10.04.27.175525 AM UPGRADE SERVER 11.2.0.4.0 Upgraded from 11.2.0.4.0

04-DEC-17 10.19.56.396737 AM jvmpsu.sql SERVER 11.2.0.4.171017OJVMPSU 0 RAN jvmpsu.sql

04-DEC-17 10.19.56.446014 AM APPLY SERVER 11.2.0.4.171017OJVMPSU 0 OJVM PSU post-install

04-DEC-17 10.19.56.450152 AM APPLY 26635834 Patch 26635834 applied

04-DEC-17 10.24.16.990526 AM APPLY SERVER 11.2.0.4 171017 PSU 11.2.0.4.171017 PSU

04-DEC-17 10.50.11.000000 AM APPLY 26950781 Patch 26950781 applied

13-JUN-18 05.24.57.891089 AM jvmpsu.sql SERVER 11.2.0.4.180116OJVMPSU 0 RAN jvmpsu.sql

13-JUN-18 05.24.57.943183 AM APPLY SERVER 11.2.0.4.180116OJVMPSU 0 OJVM PSU post-install

13-JUN-18 05.24.57.948599 AM APPLY 26925532 Patch 26925532 applied

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES

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

13-JUN-18 05.25.51.367679 AM APPLY SERVER 11.2.0.4 180116 PSU 11.2.0.4.180116 PSU

13-JUN-18 05.32.24.000000 AM APPLY 27216420 Patch 27216420 applied

BOOTSTRAP DATAPATCH 12.1.0.2 RDBMS_12.1.0.2.0DBPSU_LI

NUX.X64_161210

27-MAR-19 10.37.43.583409 PM VIEW INVALIDATE 8289601 view invalidation

27-MAR-19 10.38.44.052846 PM UPGRADE SERVER 12.1.0.2.0 Upgraded from 11.2.0.4.0

25 rows selected.

SQL> col action for a8

col action_time for a28

col description for a64

col status for a8

col version for a25

col bundle_series for a14

select patch_id, action, action_time, description, status, version, bundle_series from registry$sqlpatch;

PATCH_ID ACTION ACTION_TIME DESCRIPTION STATUS VERSION BUNDLE_SERIES

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

27001733 APPLY 27-MAR-19 10.42.29.565927 PM Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018) SUCCESS 12.1.0.2

26925311 APPLY 27-MAR-19 10.42.29.956930 PM DATABASE PATCH SET UPDATE 12.1.0.2.180116 SUCCESS 12.1.0.2 PSU

20713578 APPLY 27-MAR-19 10.42.29.961677 PM NOVPD MODE FOR 12.1.0.2 RMAN CATALOG MIGRATION SUCCESS 12.1.0.2

SQL> set lines 200

col owner for a30

col TRIGGER_NAME for a30

select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like '%GGS%';

col property_name format a30;

col property_value format a20;

col description format a40;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

select name, scn, time, database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

show parameter compa

no rows selected

SQL>

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 14

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

SQL>

NAME SCN

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

TIME DATABASE_INCARNATION# GUA STORAGE_SIZE

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

BEFORE_UPGRADE12_1_0_2_GUAR 1150809477

27-MAR-19 07.57.05.000000000 PM 1 YES 3221225472

SQL>

NAME TYPE VALUE

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

cell_offload_compaction string ADAPTIVE

compatible string 11.2.0.4.0

noncdb_compatible boolean FALSE

plsql_v2_compatibility boolean FALSE

REMARK :- Everything is fine except timezone and Compatible parameter which will be upgraded later.

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

19) Rerun the @utlrp.sql

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

SQL> show parameter spfile

--Keep the RESTORE POINT and Comptaible Parameter as it is till we get confirmation from Application team.

20)Since the upgrade is successful, Drop restore point BEFORE UPGRADE

SQL>drop restore point BEFORE_UPGRADE12_1_0_2_GUAR;

21) Check exiting Compatible parameter.

SQL> show parameter comp

NAME TYPE VALUE

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

cell_offload_compaction string ADAPTIVE

compatible string 11.2.0.4.0

db_index_compression_inheritance string NONE

nls_comp string BINARY

noncdb_compatible boolean FALSE

plsql_v2_compatibility boolean FALSE

22) shutdown & Re-start the DB this time using orginal pfile ( before upgrade)

$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 28 00:23:06 2019

Connected to an idle instance.

SQL> create spfile from pfile='/opt/app/oracle/upgrade/pfile_org_before_upgrd.ora';

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size 3731384 bytes

Variable Size 5469372488 bytes

Database Buffers 3103784960 bytes

Redo Buffers 13045760 bytes

Database mounted.

Database opened.

23) Verify the user account settings post upgrade.

col username format a30;

SELECT username, account_status FROM dba_users ORDER BY username;

24) Prepare to Upgrade the Timezone from 14 to 18

i) $ mkdir /opt/app/oracle/upgrade/timezone

$ cd /opt/app/oracle/upgrade/timezone

ii)Copy or download following files to timezone directory from Oracle Support

countstarTSTZ.sql .

countstatsTSTZ.sql .

upg_tzv_check.sql .

upg_tzv_apply.sql .

iii) give permission

$ chmod 777 -R timezone

iv) create restore point before DST

SQL> create restore point AFTER_upgrade_BEFORE_DST_12102_guar guarantee flashback database;

SQL> select name, scn, time, database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

NAME

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

SCN

----------

TIME

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

DATABASE_INCARNATION# GUA STORAGE_SIZE

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

BEFORE_UPGRADE12_1_0_2_GUAR

1150809477

27-MAR-19 07.57.05.000000000 PM

1 YES 2147483648

NAME

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

SCN

----------

TIME

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

DATABASE_INCARNATION# GUA STORAGE_SIZE

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

AFTER_UPGRADE_BEFORE_DST_12102_GUAR

1151461958

28-MAR-19 12.46.59.000000000 AM

1 YES 1073741824

25) Shutdown the DB & Restart in 'startup upgrade' to Upgrade the Timezone

$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 28 00:48:40 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

--Run the scripts in following sequence only

SQL>countstarTSTZ.sql

SQL>countstatsTSTZ.sql

SQL>upg_tzv_check.sql

SQL>upg_tzv_apply.sql

o/p -->

Number of failures: 0

INFO: Total failures during update of TSTZ data: 0 .

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv18 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this sqlplus session.

INFO: Do not use it for timezone related selects.

26) Clean shutdown and re-run the Post

i)Shutdown and restart the database,

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

ii)Start the DB and re-run the postupgrade_fixups.sql script to check the database.

$ cd /opt/app/$LOGNAME/oracle/cfgtoollogs/$LOGNAME/preupgrade

$ cd /opt/app/oracle_home/oracle/cfgtoollogs/preupgrade

$ ls -lrt

total 16

-rw-r--r-- 1 oracle oinstall 6310 Mar 27 21:56 preupgrade.log

-rw-r--r-- 1 oracle oinstall 2040 Mar 27 21:56 preupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 2266 Mar 27 21:56 postupgrade_fixups.sql

$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 28 01:06:35 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @postupgrade_fixups.sql

-- Verify the Timezone has been upgraded from 14 to 18

SQL> col property_name format a30;

col property_value format a20;

col description format a40;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 18 <<-- ok

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

27) Check and Revoke Privileges

SQL> select * from dba_tab_privs where table_name in ('UTL_FILE','UTL_TCP','UTL_SMTP','UTL_HTTP','DBMS_RANDOM') and grantee='PUBLIC';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE

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

PUBLIC SYS UTL_TCP SYS EXECUTE NO NO NO PACKAGE

PUBLIC SYS UTL_HTTP SYS EXECUTE NO NO NO PACKAGE

PUBLIC SYS UTL_FILE SYS EXECUTE NO NO NO PACKAGE

PUBLIC SYS UTL_SMTP SYS EXECUTE NO NO NO PACKAGE

PUBLIC SYS DBMS_RANDOM SYS EXECUTE NO NO NO PACKAGE

--Perform following

SQL> revoke EXECUTE on UTL_FILE from public;

revoke EXECUTE on UTL_TCP from public;

revoke EXECUTE on UTL_SMTP from public;

revoke EXECUTE on DBMS_RANDOM from public;

revoke EXECUTE on UTL_HTTP from public;

Revoke succeeded.

SQL>

Revoke succeeded.

SQL>

Revoke succeeded.

SQL>

Revoke succeeded.

SQL>

Revoke succeeded.

28) Run following scripts.

@utlrp.sql

@utluiobj.sql

SQL> set lines 200

col comp_id format a14;

SQL> SQL> col comp_name format a36;

SQL> col version format a15;

SQL> col status format a16;

SQL> select name,open_mode from v$database;

select comp_id,comp_name,version,status from dba_registry;

NAME OPEN_MODE

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

PROD READ WRITE

SQL> col owner format a20;

COMP_ID COMP_NAME VERSION STATUS

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

XDB Oracle XML Database 12.1.0.2.0 VALID

OWM Oracle Workspace Manager 12.1.0.2.0 VALID

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID

CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID

XML Oracle XDK 12.1.0.2.0 VALID

CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID

7 rows selected.

SQL> col object_name format a32;

select owner, object_name, object_type from dba_objects where status = 'INVALID';

select * from dba_tab_privs where table_name in ('UTL_FILE','UTL_TCP','UTL_SMTP','UTL_HTTP','DBMS_RANDOM') and grantee='PUBLIC';

no rows selected

O/P -->ends here.

29) Since the Timezone upgrade was successful , We can drop the Timezone restore point.

SQL> drop restore point AFTER_upgrade_BEFORE_DST_12102_guar;

Restore point dropped.

NOTE:- Currently we only have BEFORE Upgrade restore point which we will drop post App Confirmation.

30) Rerun script1_preinfo.sql to check DB status and components

31) Upgrade the RMAN Catalog

a) Connect

$ rman target /

connect catalog user_name/password.

connected to recovery catalog database

PL/SQL package RMAN_ORACLE.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old

RMAN> upgrade catalog;

recovery catalog owner is RMAN_ORACLE

enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> exit

b) We need to connect to Catalog db and give permission

$ tnsping catalog_server(ip or hostname for catalog server)

i)connect session catalog_server

ii) connect to vtire of above cataloge server
ii) connect sql

from Catalog db server

SQL> grant execute on UTL_HTTP to rman_oracle;

Grant succeeded.

c) Now e

RMAN> connect catalog user_name/password

connected to recovery catalog database

ORACLE error from recovery catalog database: ORA-04067: not executed, package body "RMAN_ORACLE" does not exist

ORA-06508: PL/SQL: could not find program unit being called: "RMAN_ORACLE"

RMAN> upgrade catalog;

recovery catalog owner is RMAN_ORACLE

enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 12.01.00.02

DBMS_RCVMAN package upgraded to version 12.01.00.02

DBMS_RCVCAT package upgraded to version 12.01.00.02.

d) Resync with Catalog.

32) uncomment cronjobs & Take backup

33) Connect to sql and verify the final output

set lines 200

col comp_id format a14;

col comp_name format a36;

col version format a15;

col status format a16;

select name,open_mode from v$database;

select comp_id,comp_name,version,status from dba_registry;

col owner format a20;

col object_name format a32;

select owner, object_name, object_type from dba_objects where status = 'INVALID';

set linesize 200

col action_time for a28

col action for a20

col namespace for a9

col version for a25

col id for 99999999

col comments for a24

col bundle_series for a14

select * from registry$history;

set linesize 200

col patch_id for 99999999

col action for a8

col action_time for a28

col description for a64

col status for a8

col version for a25

col bundle_series for a14

select patch_id, action, action_time, description, status, version, bundle_series from registry$sqlpatch;

set lines 200

col owner for a30

col TRIGGER_NAME for a30

select owner, TRIGGER_NAME , status from dba_triggers where TRIGGER_NAME like '%GGS%';

col property_name format a30;

col property_value format a20;

col description format a40;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

select name, scn, time, database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;

show parameter compa

34) Create pfile post upgrade and compare it with the before/original pfile before upgrade.

35) Verify all DB Services are Up and Running.

Comments...

No Comment Found

Oracle 11g to 12c Upgrade for Enhanced Performance And Feature: Guide

Oracle Database,Oracle E-Business Suite - June 15, 2023

Know How to Prepare a Database to Upgrade

Prepare the database for Upgrade.

#In case of patches aren't found then apply required PSU on new binaries

Leave a comment

Comments...

Recent Posts

Contact Us

Navigation