1) Download and copy on the server AutoUpgrade.jar
Download the most recent version from MOS Note: 2485457.1 – AutoUpgrade Tool
2) Check the version of java and autoupgrade.jar using 12c Home
$ORACLE_HOME/jdk/bin/java -version
$ORACLE_HOME/jdk/bin/java -jar /acfs01/backup/db/hrms_
3) Take Backup of exiting autoupgrade.jar and copy latest autoupgrade.jar to 19c and 12c home
cd /u01/app/oracle/product/19.0.
mv autoupgrade.jar autoupgrade.jar_bkp_10122023
cp /acfs01/backup/db/hrms_
ls -l autoupgrade.jar
cd /u01/app/oracle/product/12.2.
ls -l autoupgrade.jar
mv autoupgrade.jar autoupgrade.jar_bkp_10122023
cp /acfs01/backup/db/hrms_
4) Create and adjust the config file for AutoUpgrade
** To Create sample file **
/u01/app/oracle/product/12.2.
** To check target DB version **
/u01/app/oracle/product/19.
mkdir -p /acfs01/backup/db/hrms_
cd /acfs01/backup/db/hrms_
vi CHRMSP1.cfg
global.autoupg_log_dir=/
#
# Database number 1
#
CHRMSP1.dbname=CHRMSP1
CHRMSP1.start_time=NOW
CHRMSP1.source_home=/u01/app/
CHRMSP1.target_home=/u01/app/
CHRMSP1.sid=CHRMSP11
CHRMSP1.log_dir=/acfs01/
CHRMSP1.upgrade_node=indxp1.
CHRMSP1.target_version=19.18
CHRMSP1.run_utlrp=yes
CHRMSP1.timezone_upg=yes
5) Prechecks
a) Stop application - On Actual Upgrade Day
b) Stop cron/ other apps jobs - On Actual Upgrade Day
c) Create Blackout in OEM - On Actual Upgrade Day
d) Take expdp backup of all pdbs using script, incase if anything goes wrong with single PDB so backups
can be restored
e) Check timezone file Version
select * from gv$timezone_file;
f) other prechecks
** Check FRA **
select SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)",
SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,
SPACE_RECLAIMABLE/1024/1024/
((SPACE_LIMIT/1024/1024/1024) - (SPACE_USED/1024/1024/1024)) +
(SPACE_RECLAIMABLE/1024/1024/
((((SPACE_LIMIT/1024/1024/
(SPACE_RECLAIMABLE/1024/1024/
(SPACE_LIMIT/1024/1024/1024) "%_FREE_SPACE"
from v$recovery_file_dest;
archive log list
srvctl config database -d CHRMSP1 => Check if database is in auto start mode or not
#srvctl modify database -d CHRMSP1 -startoption OPEN
** To check if multimedia objects exists or not **
select con_id,count(u.table_name) from
sys.cdb_tab_columns u
where u.table_name not in (select object_name from sys.dba_recyclebin)
and u.data_type IN
('ORDIMAGE','
'ORDDOC','ORDSOURCE','
'SI_STILLIMAGE','SI_COLOR','
'SI_POSITIONALCOLOR','SI_
'SI_FEATURELIST')
and (u.data_type_owner IN ('ORDSYS', 'PUBLIC'))
and (u.owner <> 'PM')
group by con_id;
** To remove streams from all pdbs, if configured **
exec dbms_streams_adm.remove_
** To compile Invalid objects **
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catco
** To Check Tablespace size of SYSTEM, UTILITYTBS and UNDO **
set lines 200;
set pages 50;
select a.con_id,
a.tablespace_name,
round(maxbytes/1024/1024,2) MAX_SIZE,
round(a.bytes_alloc / 1024 / 1024, 2) MB_ALLOC,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) MB_USED,
--round(nvl(b.bytes_
round((maxbytes - (a.bytes_alloc - nvl(b.bytes_free, 0)) ) / 1024 / 1024, 2)
MB_FREE,100 - round (( (a.bytes_alloc - nvl(b.bytes_free, 0)) / maxbytes) *
100,2) "PCT_FREE(FROM_MAX_SIZE)",
--round(( (a.bytes_alloc - nvl(b.bytes_free, 0)) / maxbytes ) * 100,2)
"PCT_USED(FROM_MAX_SIZE)",
round((nvl(b.bytes_
"PCT_FREE(FROM_MB_ALLOC)"
--100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)
"PCT_USED(FROM_MB_ALLOC)"
from (select f.CON_ID,f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes))
maxbytes from cdb_data_files f
where (f.tablespace_name in ('SYSAUX','SYSTEM'))
group by f.CON_ID,tablespace_name) a,
(select f.CON_ID,f.tablespace_name,
sum(f.bytes) bytes_free from cdb_free_space f
group by f.CON_ID,tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
and a.con_id = b.con_id (+)
order by a.con_id, a.tablespace_name;
** Check Invalid registry **
set lines 150 pages 1000
col comp_name format a40
col status format a20
col CON_ID format 999999
col version format a20
select comp_id, comp_name, status, version, CON_ID from cdb_registry where status !='VALID';
g) DG Configuration changes
Stop MRP Process
DGMGRL> show configuration;
DGMGRL> show database CHRMSP1
DGMGRL> edit database CHRMSP1 set state=TRANSPORT-OFF;
DGMGRL> show database CHRMSS1
DGMGRL> edit database CHRMSS1 set state=APPLY-OFF;
DGMGRL> show database CHRMSS1
DGMGRL> disable configuration
DGMGRL> show configuration
h) Defer archive log transfer to standby
SQL> alter system set dg_broker_start=false scope=both sid='*';
SQL> alter system set log_archive_dest_state_3=defer scope=both sid='*';
SQL> set lines 150 pages 150
SQL> col name format a30
SQL> col value format a30
SQL> select INST_ID,NAME,VALUE from gv$parameter where name in
'dg_broker_start','log_
i) check if any resource_manager_plan is set, If yes then remove it during upgrade
SQL> show parameter resource_manager_plan
SQL> col pluggable_database for a30
SQL> select pluggable_database, shares, utilization_limit
from dba_cdb_rsrc_plan_directives
where plan = (select name from v$rsrc_plan where is_top_plan = 'TRUE' and
con_id = 1);
SQL> alter system set resource_manager_plan ='' scope=both sid='*';
j) Stop Standby Database
srvctl config database -d CHRMSS1
srvctl stop database -d CHRMSS1
srvctl status database -d CHRMSS1
k) Check RMAN Backup Status
6) AutoUpgrade: Execute ANALYZE, FIXUPS and DEPLOY modes
a) Analyze Mode / Preupgrade Checks
/u01/app/oracle/product/12.2.
** To check running jobs **
lsj
status -job <job_number>
** To resume job **
resume -job 100
** Check analyzed summary report
/acfs01/backup/db/hrms_
/acfs01/backup/db/hrms_
Note:- check log files and try to Fix those errors before proceeding further.
b) Fixup Mode / Autofix
** Create pfile before autofix **
create pfile='/acfs01/backup/db/hrms_
** Create GRP **
SQL>SET PAGESIZE 60
SQL>SET LINESIZE 150
SQL>SET VERIFY OFF
SQL>COLUMN scn FOR 999999999999999999999999
SQL>COLUMN Incar FOR 99
SQL>COLUMN name FOR A25
SQL>COLUMN storage_size FOR 999999999999999
SQL>COLUMN guarantee_flashback_database FOR A3
SQL>COL TIME FORMAT A35
SQL>SELECT database_incarnation# as Incar, scn, name, time, storage_size,
guarantee_flashback_database, PDB_RESTORE_POINT, CON_ID,
PDB_INCARNATION#
FROM v$restore_point ORDER BY 4;
SQL> create restore point Before_UpgradeTo19c guarantee flashback database;
** Execute autoupgrade.jar with fixups mode **
/u01/app/oracle/product/12.
** To check running jobs **
lsj
status -job <job_number>
** To resume job **
resume -job 100
** Check analyzed summary report
/acfs01/backup/db/hrms_
/acfs01/backup/db/hrms_
/acfs01/backup/db/hrms_
c) Deploy Mode (Downtime Require)
Note: Run Deploy command from 19c DB Home
Make sure we have double FRA and system and sysaux tablespace is having
enough space in the tablespace at least 20GB
** Create pfile before deploy **
create pfile='/acfs01/backup/db/hrms_
** unset 12c variable and set 19c env variable **
export ORACLE_SID=CHRMSP11
export ORACLE_HOME=/u01/app/oracle/
export PATH=$ORACLE_HOME/bin:$PATH:.
export LD_LIBRARY_PATH=$ORACLE_HOME/
** Execute autoupgrade.jar with deploy mode **
$ORACLE_HOME/jdk/bin/java -version
/u01/app/oracle/product/19.0.
** To check running jobs **
lsj
status -job <job_number>
** To resume job **
resume -job 100
7) Post Upgradation Task - Check parameters
show parameter cluster
show parameter spfile
8) Verify Upgradation details at DB level
set lines 150 pages 1000
col comp_name format a40
col status format a20
col CON_ID format 999999
col version format a20
select comp_id, comp_name, status, version, CON_ID from cdb_registry where status !='VALID';
select COMP_ID,Comp_name,VERSION,
select to_char(action_time,'DD-MON-
select patch_id,target_version,
select patch_id, count(*) from cdb_registry_sqlpatch group by patch_id;
9) Compatible parameter change on Primary and restart database
create pfile='/acfs01/backup/db/hrms_
ALTER SYSTEM SET COMPATIBLE='19.0.0.0' SCOPE=SPFILE SID='*';
srvctl stop database -d CHRMSP1
srvctl start database -d CHRMSP1
10) Reconfigure Standby Database
export ORACLE_SID=CHRMSS1
export ORACLE_HOME=/u01/app/oracle/
export PATH=$ORACLE_HOME/bin:$PATH:.
export LD_LIBRARY_PATH=$ORACLE_HOME/
srvctl upgrade database -d CHRMSS1 -o /u01/app/oracle/product/19.0.
srvctl config database -d CHRMSS1
srvctl start database -d CHRMSS1 -o nomount
srvctl status database -d CHRMSS1
Modify the COMPATIBLE initialization parameter
SQL> show parameter compatible
alter system set compatible='19.0.0.0' scope=spfile sid='*';
srvctl stop database -d CHRMSS1
srvctl start database -d CHRMSS1
10.1) Reconfigure Primary for log shipping
alter system set dg_broker_start=TRUE scope=both sid='*';
alter system set log_archive_dest_state_3=
col pluggable_database for a30
select pluggable_database, shares, utilization_limit
from dba_cdb_rsrc_plan_directives
where plan = (select name
from v$rsrc_plan
where is_top_plan = 'TRUE'
and con_id = 1);
alter system set resource_manager_plan = 'HRD_CDB_PLAN' scope=both sid='*';
10.2) Start the MRP Process
show configuration;
enable configuration
show configuration;
show database CHRMSP1
edit database CHRMSP1 set state=TRANSPORT-ON;
show database CHRMSS1
edit database CHRMSS1 set state=APPLY-ON;
show database CHRMSS1
show configuration
11) Drop GRP from CHRMSP1: (Check all restore created on database)
Drop restore point Before_UpgradeTo19c;
12) Remove Blackout
13) Change OEM Monitoring Target Oracle Home from 12c to 19c
14) Resume cron/other apps jobs
No comments:
Post a Comment