- 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
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
- 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
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.
- 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.
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
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
- 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
- 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.
Tell us how we can help!
Advertisement
Recent Posts
December 9, 2024
A Guide to Oracle EBS 12.2.13: Boosting ERP Performance and Security
November 18, 2024
Case Study: MongoDB Replication and Disaster Recovery Implementation
+91 9970177046
info@datapatroltech.com