An another way to upgrade a database from lower version to higher version is we have to manually upgrade a database (ie) we have to verify whether it meets all the pre-requests manually instead of using dbua (GUI) / datapump.
Step: 1 Create directory backup, upgrade for taking backup of listener.ora , tnsnames.ora and running the preupgrade.jar files respectively.
Backup TNS Files listener spfile and passwordfile .
[oracle@orcldbs ~]$ mkdir -p /u01/backup
[oracle@orcldbs u01]$ cd backup/
[oracle@orcldbs backup]$ ll
total 20
-rwxr-xr-x. 1 oracle oinstall 343 Jan 30 23:32 listener21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 555 Jan 30 23:32 listener.ora
-rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet.ora
-rwxr-xr-x. 1 oracle oinstall 614 Jan 30 23:32 tnsnames.ora
[oracle@orcldbs backup]$
[oracle@orcldbs ~]$ /u01/app/oracle/product/12.2.0/db_1/jdk/bin/java -jar
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar
FILE DIR /u01/upgrade
==================
PREUPGRADE SUMMARY
==================
/u01/upgrade/preupgrade.log
/u01/upgrade/preupgrade_fixups.sql
/u01/upgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/upgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/upgrade/postupgrade_fixups.sql
Step:2 Login to the database & Run the preupgrade_fixups.sql
Step:3 Gather the dictionary statistics it should be gathered before and after performing the upgrade.
SET ECHO ON;
SET SERVEROUTPUT ON;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Step:4 Purge the Recyclebin,it gains some storage space and on other hand we have choosen the recovery options.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged
Step:5 Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/
PL/SQL procedure successfully completed.
Step:6 Verify archive log mode and enable it ,if not
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 1
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size 8623592 bytes
Variable Size 654314008 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8151040 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> show parameter rec
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_recycle_cache_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
ldap_directory_access string NONE
ldap_directory_sysauth string no
recovery_parallelism integer 0
recyclebin string on
remote_recovery_file_dest string
In the Above snippet db_recovery_file_dest,db_recovery_file_dest_size is not set, here I am setting the db_recovery_file_dest_size to 10g and db_recovery_file_dest=’/u01/app/oracle/fra’
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
System altered.
SQL> show parameter rec
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 10
db_recycle_cache_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
ldap_directory_access string NONE
ldap_directory_sysauth string no
recovery_parallelism integer 0
recyclebin string on
remote_recovery_file_dest string
Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
AUSTRALIA READ WRITE ARCHIVELOG
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G
SQL> create restore point pre_upgrade guarantee flashback database;
Restore point created.
Step:8 Copy init and password files from 12c home to 19c home
[oracle@orcldbs ~]$ cd $ORACLE_HOME/dbs
[oracle@orcldbs dbs]$ cp spfileaustralia.ora orapwaustralia
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
oracle@orcldbs dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@orcldbs dbs]$ ls
hc_oraprod.dat lkORAPROD orapworaprod spfileaustralia.ora
init.ora orapwaustralia snapcf_oraprod.f spfileoraprod.ora
Step:9 Startup DB in Upgrade mode from 19c home
[oracle@orcldbs ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 01:24:40 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
AUSTRALIA READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
Step:10 Run dbupgrade, using $ORACLE_HOME/bin/dbupgrade file (or) by catupgrade(catupgrd.sql). Here starts the manual upgrade process.
Database upgrade completed.
Step:11 Startup DB from 19c home
[oracle@orcldbs whileupgrade]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 02:26:12 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 637534208 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted
Database opened
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
AUSTRALIA READ WRITE NO 19.0.0.0.0 OPEN
Step:-12. Run catcon.pl(it is used used to execute the command in all PDBs and CDB database) to start utlrp.sql to recompile invalid objects.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
2552
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-01-31 02:30:35
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-01-31 02:34:29
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
Step:13 Upgrade the time zone by running the following scripts,
SQL> @?/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> @?/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 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 637534208 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 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 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 637534208 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 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_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
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> SELECT version FROM v$timezone_file;
VERSION
----------
32
1 row selected.
Step:14 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.
SQL> @?/rdbms/admin/utlusts.sql text
Oracle Database Release 19 Post-Upgrade Status Tool 01-31-2021 02:43:1
Database Name: AUSTRALIA
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:18:34
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:01:34
Oracle XDK VALID 19.3.0.0.0 00:00:47
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:13
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:12
Oracle Label Security VALID 19.3.0.0.0 00:00:06
Oracle Database Vault VALID 19.3.0.0.0 00:00:20
Oracle Text VALID 19.3.0.0.0 00:00:31
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:27
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:01:31
Oracle Multimedia VALID 19.3.0.0.0 00:00:47
Spatial VALID 19.3.0.0.0 00:05:33
Oracle OLAP API VALID 19.3.0.0.0 00:00:10
Datapatch 00:03:27
Final Actions 00:03:35
Post Upgrade 00:00:17
Post Compile 00:03:53
Total Upgrade Time: 00:39:14
Database time zone version is 32. It meets current release needs.
Step:15 Drop Restore point as we have successfully completed the manual upgradation.
SQL> drop restore point PRE_UPGRADE;
Restore point dropped.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
no rows selected
Step:16 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 637534208 bytes
Database Buffers 1778384896 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
Step:17 Verify DBA_REGISTRY
SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
SQL>
COMP_ID COMP_NAME VERSION STATUS
------------------------------ ---------------------------------------- --------------- --------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
Step:18 Add TNS Entries in 19c TNS home
[oracle@orcldbs admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:33
Copyright (c) 1991, 2019, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been starte
[oracle@orcldbs admin]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:38
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:41
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-JAN-2021 02:56:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "australia" has 1 instance(s).
Instance "australia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:50
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-JAN-2021 02:56:41
Uptime 0 days 0 hr. 0 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "australia" has 1 instance(s).
Instance "australia", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Step:19 Edit oratab,Put an entry of current database in the vi /etc/oratab file.
[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/12.2.0/db_1:N
[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/19.0.0/dbhome_1:Y
australia:/u01/app/oracle/product/12.2.0/db_1:N
