CALL

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 

blog2img.png

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

or

$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

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

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

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

  1. iv) @preupgrd.sql

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

ACTIONS REQUIRED:

  1. Review results of the pre-upgrade checks:

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

  1. Execute in the SOURCE environment BEFORE upgrade:

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

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

 

  1. Gather dictionary status

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

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

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

 

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

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

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

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.

 

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

$ cd $OH/rdbms/admin

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

 

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

  1. iv) Check alert log for any errors

##  POST UPGRADE STEPS  :-

 

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

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

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.

 

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

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

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

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

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

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

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

 

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

                                                                                                ck

 

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

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

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

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

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

 

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

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

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

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

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

  1. ii) connect to vtire of above cataloge server
  2. ii) connect sql

from Catalog db server

SQL> grant execute on UTL_HTTP to rman_oracle;

Grant succeeded.

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

 

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

 

 

 

Leave a comment


Comments...

No Comment Found
Call
Enquiry