Upgrade Oracle database manually from 12c to 19c
Description
In this article I will demonstrate an overview on manually upgrading Oracle database from 12.2.0.1 to 19.3.0.0 on Oracle Linux 7 64bit platform. As the source database version is 12.2.0.1, we can go for a direct upgrade to 19c.
Below Oracle versions can be directly upgraded to 19c. Refer this document
- 11.2.0.4
- 12.1.0.2
- 12.2.0.1
- 18c
Below is the high level steps:
- Installing Oracle 19.3.0.0 binaries
- Executing the preupgrade jar tool
- Performing the preupgrade actions
- Backing up the database / Create a guaranteed restore point
- Upgrade the database
- Perform the postupgrade actions
Environment Details:
Source Hostname: new12c
Database version: 12.2.0.1
Database Name: dev12c
ORACLE_HOME: /u01/app/oracle/product/12.2.0/db_1
Target Hostname: new12c
Databaes Version: 19.3.0.0
Database name: dev12c
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_11. Installing Oracle 19c binaries.
I have already completed the installation of 19c, Refer here for step by step installation of Oracle 19.3.0.0 on Linux 7 64bit.
2. Executing the pre-upgrade jar tool
Pre-upgrade information tool is used to determine the instance readiness before upgrading the database. The pre-upgrade script will generate the fix for many issues before you upgrade to new Oracle home.
The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
Run the pre-upgrade tool
$ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jarMake sure to run the tool from source ORACLE_HOME.
$ORACLE_BASE/product/12.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade
Output:
export ORACLE_SID=db12c
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1
[oracle@new12c db12c]$ $ORACLE_BASE/product/12.2.0/db_1/jdk/bin/java -jar $ORACLE_BASE/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /u01/preupgrade/
==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-11-17T02:21:12
[oracle@new12c db12c]$
Detailed output is generated in DIR path, in my case /u01/preupgrade/preupgrade.log
[oracle@new12c preupgrade]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-17T02:21:12
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: DB12C
Container Name: db12c
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4625
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4096 MB. There is currently 4089 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /u01/app/oracle/fast_recovery_area/db12c
Limit : 4096 MB
Used : 7169 KB
Available : 4089 MB
The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.
RECOMMENDED ACTIONS
===================
2. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
3. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 470 MB 500 MB
SYSTEM 800 MB 912 MB
TEMP 32 MB 150 MB
UNDOTBS1 70 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
5. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DB12C
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
6. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
7. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
8. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
9. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database DB12C
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/preupgrade/postupgrade_fixups.sql
[oracle@new12c preupgrade]$3. Performing the pre-upgrade actions
Run the preupgrade_fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-17 02:21:02
For Source Database: DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. min_recovery_area_size NO Manual fixup required.
2. dictionary_stats YES None.
3. pre_fixed_objects YES None.
4. tablespaces_info NO Informational only.
Further action is optional.
5. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
SQL> SQL>The preupgrade_fixups.sql output lists multiple recommendations which has to be fix manually. We will fix the below recommendations manually and re-run the preupgrade_fixups.sql
1. min_recovery_area_size NO Manual fixup required.
2. dictionary_stats YES None.
3. pre_fixed_objects YES None.
4. tablespaces_info NO Informational only.
Further action is optional.
5. rman_recovery_version NO Informational only.
Further action is optional.Recommendations 1 and 4 has to be fixed manually.
Here I am ignoring point No: 5 rman_recovery_verison as there is no recovery catalog configured in my environment.
Recommendation 1: min_recovery_area_size
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4625
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 4096 MB. There is currently 4089 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /u01/app/oracle/fast_recovery_area/db12c
Limit : 4096 MB
Used : 7169 KB
Available : 4089 MB
The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.Action: Increase the DB_RECOVERY_FILE_DEST_SIZE parameter
sqlplus "/as sysdba"
alter system set db_recovery_file_dest_size=6000M scope=both;Recommendation 4: tablespaces_info
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 470 MB 500 MB
SYSTEM 800 MB 912 MB
TEMP 32 MB 150 MB
UNDOTBS1 70 MB 439 MBAction: increase the datafile size
alter database datafile '/u01/app/oracle/oradata/db12c/system01.dbf' resize 912M;
alter database datafile '/u01/app/oracle/oradata/db12c/sysaux01.dbf' resize 500M;
alter database datafile '/u01/app/oracle/oradata/db12c/undotbs01.dbf' resize 440M;
alter database tempfile '/u01/app/oracle/oradata/db12c/temp01.dbf' resize 200M;Now re-run the preupgrade_fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-17 02:21:02
For Source Database: DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. dictionary_stats YES None.
2. pre_fixed_objects YES None.
3. tablespaces_info NO Informational only.
Further action is optional.
4. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
SQL> exit4. Backing up the database / Create a guaranteed restore point
It is recommended to take a backup of database using RMAN or create a guaranteed restore point. Ensure proper failback plan in place.
Creating a guranteed restore point:
create restore point before_upgrade_19c guarantee flashback database;
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUARANTEE TIME
-------------------- --------- --------------------------------
BEFORE_UPGRADE_19C YES 17-NOV-20 04.07.59.000000000 AM
4. Upgrading the database
Once the pre-upgrade actions are completed, shutdown the database to start the upgrade process
sqlplus /as sysdba
shutdown immediate;
exitCopy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME
[oracle@new12c preupgrade]$ cp $ORACLE_HOME/dbs/orapwdb12c /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c preupgrade]$ cp $ORACLE_HOME/dbs/spfiledb12c.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c preupgrade]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@new12c preupgrade-2]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@new12c dbs]$ ls -l *db12c*
-rw-r-----. 1 oracle oinstall 3584 Nov 17 04:47 orapwdb12c
-rw-r-----. 1 oracle oinstall 3584 Nov 17 04:47 spfiledb12c.oraStop listener running on 11g home and start it from 19c home
[oracle@ol7-dev ~]$ lsnrctl stop
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$ lsnrctl startStart the database from 19c ORACLE_HOME and start the upgrade.
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus /as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;
--output--
[oracle@ol7-dev ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ol7-dev ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ol7-dev ~]$
[oracle@ol7-dev ~]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 8 04:48:01 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 805306368 bytes
Database Buffers 436207616 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name,open_mode,status from v$database, v$instance;
NAME OPEN_MODE STATUS
--------- -------------------- ------------
DB12C READ WRITE OPEN MIGRATE
SQL> exitRun the DB Upgrade utility
$ORACLE_HOME/bin/dbupgradeOutput:
[oracle@new12c db12c]$ $ORACLE_HOME/bin/dbupgrade
Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
.
.
.
<output truncated>
.
.
.
------------------------------------------------------
Phases [0-107] End Time:[2020_11_17 11:56:33]
------------------------------------------------------
Grand Total Time: 3781s
LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/upg_summary.log
Grand Total Upgrade Time: [0d:1h:3m:1s]
[oracle@new12c db12c]$Check the upgrade summary log,
[oracle@new12c db12c]$ cat /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/db12c/upgrade20201117105328/upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 11-17-2020 11:56:0
Database Name: DB12C
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:25:05
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:02:26
Oracle XDK UPGRADED 19.3.0.0.0 00:02:20
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:29
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:22
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:12
Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:38
Oracle Text UPGRADED 19.3.0.0.0 00:01:20
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:01:16
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:03:11
Oracle Multimedia UPGRADED 19.3.0.0.0 00:01:34
Spatial UPGRADED 19.3.0.0.0 00:12:38
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:27
Datapatch 00:07:25
Final Actions 00:07:35
Post Upgrade 00:00:25
Total Upgrade Time: 01:00:39
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Grand Total Upgrade Time: [0d:1h:3m:1s]
[oracle@new12c db12c]$
DB is in shutdown state after the dbupgrade process. Start the database. Check the database component status.
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup
SELECT name, open_mode, status, version from v$database, v$instance;Output:
output:
[oracle@new12c flashback]$ export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@new12c flashback]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@new12c flashback]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 23:50:53 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1157627168 bytes
Fixed Size 8895776 bytes
Variable Size 1040187392 bytes
Database Buffers 100663296 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> SELECT name, open_mode, status, version from v$database, v$instance;
NAME OPEN_MODE STATUS VERSION
--------- -------------------- ------------ -----------------
DB12C READ WRITE OPEN 19.0.0.0.0Execute Post-Upgrade Status Tool, utlusts.sql
@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 11-18-2020 00:15:2
Database Name: DB12C
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:25:05
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:02:26
Oracle XDK UPGRADED 19.3.0.0.0 00:02:20
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:29
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:22
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:12
Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:38
Oracle Text UPGRADED 19.3.0.0.0 00:01:20
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:01:16
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:03:11
Oracle Multimedia UPGRADED 19.3.0.0.0 00:01:34
Spatial UPGRADED 19.3.0.0.0 00:12:38
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:27
Datapatch 00:07:25
Final Actions 00:07:35
Post Upgrade 00:00:25
Total Upgrade Time: 01:00:39
Database time zone version is 32. It meets current release needs.
SQL>Recompile the INVALID Objects using utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql5. Performing the post-upgrade actions
Connect to sqlplus and run the postupgrade_fixups.sql script
sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql
--output--SQL> @/u01/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-17 02:21:12
For Source Database: DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
6. old_time_zones_exist NO Manual fixup recommended.
7. dir_symlinks YES None.
8. post_dictionary YES None.
9. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>Output from postupgrade_fixups.sql recommends the below,
- Upgrade the timezone file version
- Gather statistics on Fixed objects.
Upgrade the database timezone file
Scripts to upgrade Timezone file versions is available under ORACLE_HOME/rdbms/admin directory from Oracle 18c onwards.
To get how much TIMESTAMP WITH TIME ZONE date is there in database using stats info.
$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Get the approximate TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Timezone upgrade check script
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Timezone aply script.
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
Upgrade steps:
sqlplus / as sysdba
SELECT version FROM v$timezone_file;
@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;
--Output:
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
26
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Session altered.
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first ...
Note: empty tables are not listed.
Stat date - Owner.TableName.ColumnName - num_rows
.
.
<output truncated>
.
.
17/11/2020 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Session altered.
.
Estimating amount of TSTZ data using COUNT(*).
This might take some time ...
.
.
<output truncated>
.
.
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total count * of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
SQL>
SQL> @?/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1157627168 bytes
Fixed Size 8895776 bytes
Variable Size 1006632960 bytes
Database Buffers 134217728 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1157627168 bytes
Fixed Size 8895776 bytes
Variable Size 1006632960 bytes
Database Buffers 134217728 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
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 DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
SQL>Check the updated timezone version
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
1 row selected.
Gather statistics on fixed objects
Connect to sqlplus as sys user and execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
sqlplus / as sysdba
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
exit
--output
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
SQL>Now re-run the postupgrade_fixups.sql
sqlplus / as sysdba
@/u01/preupgrade/postupgrade_fixups.sql
exit
--output
SQL> @/u01/preupgrade/postupgrade_fixups.sql
No errors.
No errors.
No errors.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-11-17 02:21:12
For Source Database: DB12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
6. old_time_zones_exist YES None.
7. dir_symlinks YES None.
8. post_dictionary YES None.
9. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
SQL>
Drop the restore point
Once the upgrade is successfull, drop the restore point otherwise at some point you will run out of space.
select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;
--output
SQL> select name from v$restore_point;
NAME
-------------------
BEFORE_UPGRADE_19C
SQL> drop restore point BEFORE_UPGRADE_19C;
Restore point dropped.Update the compatible parameter
Note: After the upgrade, database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible;
--output:
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1157627168 bytes
Fixed Size 8895776 bytes
Variable Size 973078528 bytes
Database Buffers 167772160 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0Database has been successfully upgraded to 19c.
[oracle@ol7-dev ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 13 20:07:34 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name, open_mode, version from v$database, v$instance;
NAME OPEN_MODE VERSION
--------- -------------------- -----------------
ORADEV READ WRITE 19.0.0.0.0
SQL> Hope this helps…
https://oracle-blog.com/upgrade-oracle-database-manually-from-12c-to-19c/
No comments:
Post a Comment