Database Upgrade:
Multitenant :
Upgrading to Oracle Database 19c
Upgrade Oracle
Database from 18c to 19c
PURPOSE: All
documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test
environment before to move on to production environment.
Pretasks:
Pretasks:
Login as 18c DB
create pfile='/oracle/download/initDB_18c.ora' from spfile;
NOTE: don't change this parameter keep as it is. You can do later.
*.compatible
Check the current time zone Version.
select *
from v$timezone_file;
FILENAME VERSION CON_ID
--------------------
---------- ----------
timezlrg_31.dat 31 0
I am using the GUI for Installation. Just Installing Software.
Create new Oracle_Home
directory und unzip the files as follow.
unzip
LINUX.X64_193000_grid_home.zip -d /oracle/app/home/product/19.0.0/dbhome_19
Install just Software.
export
DISPLAY=xx.xx.xx.xx:0.0
unset
ORACLE_BASE
unset
ORACLE_HOME
unset
ORACLE_SID
./runInstaller
=>>
Set Up Software Only
=>>
Single Instance database installation
=>>
Enterprise Edition
=>>
keep the same Oracle Base
=>>
chose the group you have.
=>>
Keep as it is Uncheck "Automatically run configuration script"
=>> Click
on Install
Execute the root
script as root user.
/oracle/app/home/product/19.0.0/dbhome_19/root.sh
Performing root user operation.
The following environment variables are set
as:
ORACLE_OWNER= oracle
ORACLE_HOME=
/oracle/app/home/product/19.0.0/dbhome_19
Enter the full pathname of the local bin
directory: [/usr/local/bin]:
The contents of "dbhome" have not
changed. No need to overwrite.
The contents of "oraenv" have not
changed. No need to overwrite.
The contents of "coraenv" have
not changed. No need to overwrite.
Entries will be added to the /etc/oratab
file as needed by
Database Configuration Assistant when a
database is created
Finished running generic part of root
script.
Now product-specific root actions will be
performed.
Oracle Trace File Analyzer (TFA -
Standalone Mode) is available at :
/oracle/app/home/product/19.0.0/dbhome_19/bin/tfactl
Note :
1. tfactl will use TFA Service if that
service is running and user has been granted access
2. tfactl will configure TFA Standalone
Mode only if user has no access to TFA Service or TFA is not installed
Execute
Preupgrade scropt.
/oracle/app/home/product/18.0.0/dbhome_18/jdk/bin/java
-jar /oracle/app/home/product/19.0.0/dbhome_19/rdbms/admin/preupgrade.jar FILE TEXT
==================
PREUPGRADE SUMMARY
==================
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/preupgrade.log
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/preupgrade_fixups.sql
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/postupgrade_fixups.sql
Execute fixup scripts across the entire
CDB:
Before upgrade:
1. Execute preupgrade fixups with the below
command
$ORACLE_HOME/perl/bin/perl
-I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin
$ORACLE_HOME/rdbms/admin/catcon.pl -l
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/ -b preup_kardbprd
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/preupgrade_fixups.sql
2. Review logs under
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/
After the upgrade:
1. Execute postupgrade fixups with the
below command
$ORACLE_HOME/perl/bin/perl
-I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin
$ORACLE_HOME/rdbms/admin/catcon.pl -l /oracle/app/home/cfgtoollogs/kardbprd/preupgrade/
-b postup_kardbprd
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/postupgrade_fixups.sql
2. Review logs under
/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/
Preupgrade complete: 2019-12-11T11:59:57
4. Run the preupgrade_fixups.sql script
@/oracle/app/home/product/19.0.0/dbhome_19/rdbms/admin/utlrp.sql
@/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/preupgrade_fixups.sql
Check invalid
Objects.
select
comp_id,status from dba_registry;
Stop Database.
Since I have
Pluggable database. Need to stop as follow.
col NAME format a30
col OPEN_TIME format a40
set linesize 200
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM
V$PDBS;
NAME OPEN_MODE RES OPEN_TIME
------------------------------ ---------- ---
-------------------------------------
PDB$SEED READ ONLY NO
10-DEC-19 12.00.07.535 PM +01:00
KARDBPDB READ WRITE NO 10-DEC-19 12.08.29.191 PM +01:00
alter pluggable database KARDBPDB close immediate;
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM
V$PDBS;SQL> SQL> SQL>
NAME OPEN_MODE RES OPEN_TIME
------------------------------ ---------- ---
-------------------------------------
PDB$SEED READ ONLY NO
10-DEC-19 12.00.07.535 PM +01:00
KARDBPDB MOUNTED 11-DEC-19 11.33.32.935 AM +01:00
Shutdown DB
srvctl
status database -d KARDBPRD
Database
is running.
srvctl
stop database -d KARDBPRD
Shutdown
Listener.
srvctl
status listener -l LISTENER
srvctl
stop listener -l LISTENER
Set the
ORACLE_HOME as 19 and login into the DB.
Edit the
file initDB_18c.ora which we created earlier and create spfile from pfile.
create
spfile from pfile='/oracle/download/initDB_18c.ora';
Start DB in
Upgrade mode.
SQL>
startup upgrade;
ORACLE
instance started.
Total
System Global Area 2281700816 bytes
Fixed
Size 9137616 bytes
Variable
Size 671088640 bytes
Database
Buffers 1593835520 bytes
Redo
Buffers 7639040 bytes
Database
mounted.
Database
opened.
cd
$ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl
catctl.pl -n 8 catupgrd.sql
...
...
...
------------------------------------------------------
Phases [0-107] Start Time:[2019_12_11 13:53:48]
Container Lists Inclusion:[CDB$ROOT]
Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial
Phase #:0 [CDB$ROOT]
Files:1 Time: 17s
...
...
...
Grand Total Upgrade Time: [0d:0h:47m:10s]
If needed add
the new location to the srvcl tools
Add DB to srvctl
srvctl
upgrade database -d KARDBPRD -o /oracle/app/home/product/19.0.0/dbhome_19
Execute the
postupgrade script and the other command.
@/oracle/app/home/cfgtoollogs/kardbprd/preupgrade/postupgrade_fixups.sql
@$ORACLE_HOME/rdbms/admin/utldirsymlink.sql
EXECUTE
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Edit entry in
the /etc/oratab
Updating TZ
Execute
following commands:
You need
following commands:
@?/rdbms/admin/utltz_countstar.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql
SQL>
select TZ_VERSION from registry$database;
TZ_VERSION
----------
32
1 row
selected.
SQL>
SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
--------------------
---------- ----------
timezlrg_32.dat 32 0
1 row
selected.
At this point, we are done with the Upgrade steps:
