Upgrade Oracle Database Manually from 12.2.0.1 to 19c: In this article we will see how Upgrade Oracle Database Manually from 12.2.0.1 to 19c.
Follow the below Steps for this activity:
Environment :
Hostname : vm-3.localdomain
Database Name : ORCL
Source DB VERSION : 12.2.0.1.0
CDB : non-cdb, Single Instance
Source DB Home Path : /u01/app/oracle/product/12.2.0.1/db_1
Datafile Location : /u01/app/oracle/oradata/ORCL/
Target DB VERSION : 19c (19.3.0.0.0)
Target DB Path : /u01/app/oracle/product/19.0.0/dbhome_1
Upgrade Method : ManualPre-Upgrade Tasks :
Step-1 Take Backup :
In this case we take Database RMAN full backup.
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Dec 21 12:16:56 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1587465486)
RMAN> backup database plus archivelog;
Starting backup at 21-DEC-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1059739452
input archived log thread=1 sequence=2 RECID=2 STAMP=1059739972
input archived log thread=1 sequence=3 RECID=3 STAMP=1059740035
input archived log thread=1 sequence=4 RECID=4 STAMP=1059740225
channel ORA_DISK_1: starting piece 1 at 21-DEC-20
channel ORA_DISK_1: finished piece 1 at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2020_12_21/o1_mf_annnn_TAG20201221T121708_hy0k7ff7_.bkp tag=TAG20201221T121708 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 21-DEC-20
Starting backup at 21-DEC-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-DEC-20
channel ORA_DISK_1: finished piece 1 at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2020_12_21/o1_mf_nnndf_TAG20201221T121716_hy0k7p7o_.bkp tag=TAG20201221T121716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 21-DEC-20
Starting backup at 21-DEC-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=5 STAMP=1059740283
channel ORA_DISK_1: starting piece 1 at 21-DEC-20
channel ORA_DISK_1: finished piece 1 at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/backupset/2020_12_21/o1_mf_annnn_TAG20201221T121803_hy0k93s9_.bkp tag=TAG20201221T121803 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-20
Starting Control File and SPFILE Autobackup at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/ORCL/autobackup/2020_12_21/o1_mf_s_1059740285_hy0k95w8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-DEC-20
RMAN> exit
Recovery Manager complete.
[oracle@vm-3 ~]$Take Backup of Network files, init files, and Password file.
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
[oracle@vm-3 dbs]$ ls -ltrh
total 11M
-rw-r--r--. 1 oracle oinstall 3.1K May 15 2015 init.ora
-rw-r-----. 1 oracle oinstall 24 Dec 20 20:41 lkORCL
-rw-r-----. 1 oracle oinstall 3.5K Dec 20 20:59 orapwORCL
-rw-r--r--. 1 oracle oinstall 1.1K Dec 20 21:21 initORCL.ora
-rw-rw----. 1 oracle oinstall 1.6K Dec 21 12:04 hc_ORCL.dat
-rw-r-----. 1 oracle oinstall 3.5K Dec 21 12:15 spfileORCL.ora
-rw-r-----. 1 oracle oinstall 11M Dec 21 12:18 snapcf_ORCL.f
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ cp orapwORCL initORCL.ora spfileORCL.ora /u01/backup/
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ cd ..
[oracle@vm-3 db_1]$ cd network/admin/
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ ls -ltr
total 24
-rw-r--r--. 1 oracle oinstall 1441 Aug 28 2015 shrept.lst
drwxr-xr-x. 2 oracle oinstall 64 Dec 20 20:04 samples
-rw-r--r--. 1 oracle oinstall 182 Dec 20 20:36 sqlnet2012208PM3657.bak
-rw-r--r--. 1 oracle oinstall 342 Dec 20 20:36 listener2012208PM3658.bak
-rw-r--r--. 1 oracle oinstall 342 Dec 20 20:37 listener.ora
-rw-r--r--. 1 oracle oinstall 182 Dec 20 20:37 sqlnet.ora
-rw-r-----. 1 oracle oinstall 420 Dec 20 20:59 tnsnames.ora
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/network/admin
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ cp sqlnet.ora listener.ora tnsnames.ora /u01/backup/
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ cd /u01/backup/
[oracle@vm-3 backup]$
[oracle@vm-3 backup]$ ls -ltrh
total 24K
drwxr-xr-x. 2 oracle oinstall 43 Dec 21 12:16 rman
-rw-r-----. 1 oracle oinstall 3.5K Dec 21 12:22 orapwORCL
-rw-r--r--. 1 oracle oinstall 1.1K Dec 21 12:22 initORCL.ora
-rw-r-----. 1 oracle oinstall 3.5K Dec 21 12:22 spfileORCL.ora
-rw-r--r--. 1 oracle oinstall 182 Dec 21 12:23 sqlnet.ora
-rw-r--r--. 1 oracle oinstall 342 Dec 21 12:23 listener.ora
-rw-r-----. 1 oracle oinstall 420 Dec 21 12:23 tnsnames.ora
[oracle@vm-3 backup]$If you need to create password file then you can create password file by this command.
[oracle@vm-3 dbs]$ orapwd file=orapw<SID NAME> password=Welcome_123 entries=100 ignorecase=YStep-2 Check Invalid Objects :
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>Step-3 Run preupgrade script :
[oracle@vm-3 ~]$ /u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE 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-12-21T12:29:19
[oracle@vm-3 ~]$Step-4 View Preupgrade log :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cat /u01/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-12-21T12:29:18
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: ORCL
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
================
None
RECOMMENDED ACTIONS
===================
1. (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.
2. (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
================
3. 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.
4. 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 ORCL
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
===================
5. 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.
6. 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.
7. (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.
8. 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 ORCL
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/preupgrade/postupgrade_fixups.sql
[oracle@vm-3 ~]$*** From this preupgrade.log we can findout what we need to change. In here we found need to extend tablespaces size for SYSAUX, SYSTEM, TEMP and UNDOTBS1. If autoextend is on for this tablespaces and have available free space so, there is no issue if autoextend is off then you need to extend size.
Step-5 Check and Verify tablespace sizes for upgrade :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 21 12:31:55 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set lines 400 pages 400
SQL> col FILE_NAME for a60
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
------------------------------------------------------------ ---------- ------------------------------ --------------- ---
/u01/app/oracle/oradata/ORCL/users01.dbf 7 USERS 5 YES
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 4 UNDOTBS1 70 YES
/u01/app/oracle/oradata/ORCL/system01.dbf 1 SYSTEM 800 YES
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 3 SYSAUX 470 YES
SQL>
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
------------------------------------------------------------ ---------- ------------------------------ --------------- ---
/u01/app/oracle/oradata/ORCL/temp01.dbf 1 TEMP 32 YES
SQL>
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' resize 1g;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/system01.dbf' resize 1g;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' resize 1g;
Database altered.
SQL> alter database tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' resize 1g;
Database altered.
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
------------------------------------------------------------ ---------- ------------------------------ --------------- ---
/u01/app/oracle/oradata/ORCL/users01.dbf 7 USERS 5 YES
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 4 UNDOTBS1 1024 YES
/u01/app/oracle/oradata/ORCL/system01.dbf 1 SYSTEM 1024 YES
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 3 SYSAUX 1024 YES
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
------------------------------------------------------------ ---------- ------------------------------ --------------- ---
/u01/app/oracle/oradata/ORCL/temp01.dbf 1 TEMP 1024 YES
SQL>Step-6 Update intialization parameters :
In this case, nothing to update initialization parameters as per preupgrade.log. Hence no action taken.
Step-7 Gather Dictionary Stats and Purge Recyclebin :
SQL>
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL>
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL>Step-8 Refresh Materialized Views :
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
SQL>
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
SQL>
SQL> declare
2 list_failures integer(3) :=0;
3 begin
4 DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>Step-9 Run preupgrade_fixups.sql :
SQL>
SQL> @/u01/preupgrade/preupgrade_fixups.sql
SQL> REM
SQL> REM Oracle PRE-Upgrade Fixup Script
SQL> REM
SQL> REM Auto-Generated by: Oracle Preupgrade Script
SQL> REM Version: 19.0.0.0.0 Build: 1
SQL> REM Generated on: 2020-12-21 12:29:04
SQL> REM
SQL> REM Source Database: ORCL
SQL> REM Source Database Version: 12.2.0.1.0
SQL> REM For Upgrade to Version: 19.0.0.0.0
SQL> REM
SQL>
SQL> REM
SQL> REM Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-12-21 12:29:04
For Source Database: ORCL
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 YES None.
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>Step-10 Verify archive log destination size :
Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations.
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area/ORCL
db_recovery_file_dest_size big integer 8016M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL> !df -h /u01/app/oracle/fast_recovery_area/ORCL
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 40G 24G 17G 59% /u01
SQL>Step-11 Stop LISTENER :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ ps -ef | grep tns
root 15 2 0 11:52 ? 00:00:00 [netns]
oracle 8318 1 0 13:11 ? 00:00:00 /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr LISTENER -inherit
oracle 8365 2641 0 13:12 pts/0 00:00:00 grep --color=auto tns
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ lsnrctl stop LISTENER
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-DEC-2020 13:12:28
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm-3.localdomain)(PORT=1521)))
The command completed successfully
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ ps -ef | grep tns
root 15 2 0 11:52 ? 00:00:00 [netns]
oracle 8384 2641 0 13:12 pts/0 00:00:00 grep --color=auto tns
[oracle@vm-3 ~]$Step-12 Create Flashback Guaranteed Restore Point :
- NO need to enable Flashback Database from 11.2.0.1 onwards.
- Database MUST be in Archive Log mode.
- MUST NOT change the compatible parameter to higher version.
SQL>
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL>
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
-------------------- -------------------- ------------
ORCL READ WRITE ARCHIVELOG
SQL>
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area/ORCL
db_recovery_file_dest_size big integer 8016M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>
SQL> select * from V$restore_point;
no rows selected
SQL>
SQL>
SQL> create restore point pre_upgrade guarantee flashback database;
Restore point created.
SQL>
SQL> col name for a20
SQL> col GUARANTEE_FLASHBACK_DATABASE for a10
SQL> col TIME for a60
SQL> set lines 190
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE YES 21-DEC-20 01.13.45.000000000 PM
SQL>If recovery destination not set then,
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>!mkdir -p /u01/app/oracle/fast_recovery_area
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>Upgrade Tasks :
Step-1 Shutdown the database :
SQL>
SQL> select name, database_role, open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
-------------------- ---------------- --------------------
ORCL PRIMARY READ WRITE
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>Step-2 Copy init and password files from 12c to 19c $ORACLE_HOME/dbs location :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ ls -ltr
total 10376
-rw-r--r--. 1 oracle oinstall 3079 May 15 2015 init.ora
-rw-r-----. 1 oracle oinstall 24 Dec 20 20:41 lkORCL
-rw-r-----. 1 oracle oinstall 3584 Dec 20 20:59 orapwORCL
-rw-r-----. 1 oracle oinstall 10600448 Dec 21 12:18 snapcf_ORCL.f
-rw-r--r--. 1 oracle oinstall 1092 Dec 21 13:29 initORCL.ora
-rw-r-----. 1 oracle oinstall 3584 Dec 21 13:29 spfileORCL.ora
-rw-rw----. 1 oracle oinstall 1544 Dec 21 13:29 hc_ORCL.dat
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ cp orapwORCL initORCL.ora spfileORCL.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 3584 Dec 21 13:30 orapwORCL
-rw-r--r--. 1 oracle oinstall 1092 Dec 21 13:30 initORCL.ora
-rw-r-----. 1 oracle oinstall 3584 Dec 21 13:30 spfileORCL.ora
[oracle@vm-3 dbs]$Step-3 Startup DB in Upgrade mode from 19c home :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ export TMP=/tmp
[oracle@vm-3 ~]$ export TMPDIR=$TMP
[oracle@vm-3 ~]$ export ORACLE_SID=ORCL
[oracle@vm-3 ~]$ export ORACLE_UNQNAME=ORCL
[oracle@vm-3 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@vm-3 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
[oracle@vm-3 ~]$ export ORA_INVENTORY=/u01/app/oraInventory
[oracle@vm-3 ~]$ export PATH=/usr/sbin:/usr/local/bin:$PATH
[oracle@vm-3 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@vm-3 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
[oracle@vm-3 ~]$ export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ which sqlplus
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 21 13:35:19 2020
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 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 620756992 bytes
Database Buffers 440401920 bytes
Redo Buffers 3674112 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
ORCL READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
SQL>Step-4 Run dbupgrade :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$ ls -ltr dbupgrade
-rwxr-x---. 1 oracle oinstall 3136 Apr 17 2019 dbupgrade
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$ nohup ./dbupgrade &
[1] 10367
[oracle@vm-3 bin]$ nohup: ignoring input and appending output to ‘nohup.out’
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$ tail -100f nohup.out
Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]
Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20201221133738]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201221133738/catupgrd_catcon_10372.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201221133738/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201221133738/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = ORCL
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/ORCL/upgrade20201221133751/catupgrd_catcon_10372.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/ORCL/upgrade20201221133751/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/ORCL/upgrade20201221133751/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/ORCL/upgrade20201221133751]
Parallel SQL Process Count = 4
Components in [ORCL]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2020_12_21 13:38:03]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ORCL] Files:1 Time: 157s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ORCL] Files:5 Time: 217s
Restart Phase #:2 [ORCL] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ORCL] Files:19 Time: 155s
Restart Phase #:4 [ORCL] Files:1 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [ORCL] Files:7 Time: 71s
***************** Catproc Start ****************
Serial Phase #:6 [ORCL] Files:1 Time: 78s
***************** Catproc Types ****************
Serial Phase #:7 [ORCL] Files:2 Time: 48s
Restart Phase #:8 [ORCL] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [ORCL] Files:67 Time: 142s
Restart Phase #:10 [ORCL] Files:1 Time: 3s
************* Catproc Package Specs ************
Serial Phase #:11 [ORCL] Files:1 Time: 285s
Restart Phase #:12 [ORCL] Files:1 Time: 3s
************** Catproc Procedures **************
Parallel Phase #:13 [ORCL] Files:94 Time: 60s
Restart Phase #:14 [ORCL] Files:1 Time: 2s
Parallel Phase #:15 [ORCL] Files:120 Time: 106s
Restart Phase #:16 [ORCL] Files:1 Time: 2s
Serial Phase #:17 [ORCL] Files:22 Time: 15s
Restart Phase #:18 [ORCL] Files:1 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [ORCL] Files:32 Time: 144s
Restart Phase #:20 [ORCL] Files:1 Time: 1s
Serial Phase #:21 [ORCL] Files:3 Time: 54s
Restart Phase #:22 [ORCL] Files:1 Time: 2s
Parallel Phase #:23 [ORCL] Files:25 Time: 824s
Restart Phase #:24 [ORCL] Files:1 Time: 3s
Parallel Phase #:25 [ORCL] Files:12 Time: 420s
Restart Phase #:26 [ORCL] Files:1 Time: 2s
Serial Phase #:27 [ORCL] Files:1 Time: 0s
Serial Phase #:28 [ORCL] Files:3 Time: 17s
Serial Phase #:29 [ORCL] Files:1 Time: 0s
Restart Phase #:30 [ORCL] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [ORCL] Files:1 Time: 3s
Restart Phase #:32 [ORCL] Files:1 Time: 2s
Serial Phase #:34 [ORCL] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ORCL] Files:293 Time: 117s
Serial Phase #:36 [ORCL] Files:1 Time: 0s
Restart Phase #:37 [ORCL] Files:1 Time: 2s
Serial Phase #:38 [ORCL] Files:6 Time: 27s
Restart Phase #:39 [ORCL] Files:1 Time: 2s
*************** Catproc DataPump ***************
Serial Phase #:40 [ORCL] Files:3 Time: 245s
Restart Phase #:41 [ORCL] Files:1 Time: 3s
****************** Catproc SQL *****************
Parallel Phase #:42 [ORCL] Files:13 Time: 442s
Restart Phase #:43 [ORCL] Files:1 Time: 2s
Parallel Phase #:44 [ORCL] Files:11 Time: 69s
Restart Phase #:45 [ORCL] Files:1 Time: 2s
Parallel Phase #:46 [ORCL] Files:3 Time: 10s
Restart Phase #:47 [ORCL] Files:1 Time: 3s
************* Final Catproc scripts ************
Serial Phase #:48 [ORCL] Files:1 Time: 37s
Restart Phase #:49 [ORCL] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [ORCL] Files:1 Time: 12s
************ Upgrade Component Start ***********
Serial Phase #:51 [ORCL] Files:1 Time: 3s
Restart Phase #:52 [ORCL] Files:1 Time: 2s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [ORCL] Files:2 Time: 1164s
***************** Upgrading XDB ****************
Restart Phase #:54 [ORCL] Files:1 Time: 1s
Serial Phase #:56 [ORCL] Files:3 Time: 25s
Serial Phase #:57 [ORCL] Files:3 Time: 12s
Parallel Phase #:58 [ORCL] Files:10 Time: 13s
Parallel Phase #:59 [ORCL] Files:25 Time: 26s
Serial Phase #:60 [ORCL] Files:4 Time: 31s
Serial Phase #:61 [ORCL] Files:1 Time: 0s
Serial Phase #:62 [ORCL] Files:32 Time: 15s
Serial Phase #:63 [ORCL] Files:1 Time: 0s
Parallel Phase #:64 [ORCL] Files:6 Time: 14s
Serial Phase #:65 [ORCL] Files:2 Time: 83s
Serial Phase #:66 [ORCL] Files:3 Time: 102s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [ORCL] Files:1 Time: 2s
Serial Phase #:69 [ORCL] Files:1 Time: 6s
Parallel Phase #:70 [ORCL] Files:2 Time: 123s
Restart Phase #:71 [ORCL] Files:1 Time: 1s
Parallel Phase #:72 [ORCL] Files:2 Time: 5s
Serial Phase #:73 [ORCL] Files:2 Time: 6s
***************** Upgrading SDO ****************
Restart Phase #:74 [ORCL] Files:1 Time: 1s
Serial Phase #:76 [ORCL] Files:1 Time: 145s
Serial Phase #:77 [ORCL] Files:2 Time: 9s
Restart Phase #:78 [ORCL] Files:1 Time: 3s
Serial Phase #:79 [ORCL] Files:1 Time: 168s
Restart Phase #:80 [ORCL] Files:1 Time: 1s
Parallel Phase #:81 [ORCL] Files:3 Time: 288s
Restart Phase #:82 [ORCL] Files:1 Time: 3s
Serial Phase #:83 [ORCL] Files:1 Time: 20s
Restart Phase #:84 [ORCL] Files:1 Time: 2s
Serial Phase #:85 [ORCL] Files:1 Time: 36s
Restart Phase #:86 [ORCL] Files:1 Time: 2s
Parallel Phase #:87 [ORCL] Files:4 Time: 507s
Restart Phase #:88 [ORCL] Files:1 Time: 2s
Serial Phase #:89 [ORCL] Files:1 Time: 5s
Restart Phase #:90 [ORCL] Files:1 Time: 1s
Serial Phase #:91 [ORCL] Files:2 Time: 32s
Restart Phase #:92 [ORCL] Files:1 Time: 2s
Serial Phase #:93 [ORCL] Files:1 Time: 3s
Restart Phase #:94 [ORCL] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [ORCL] Files:1 Time: 57s
Restart Phase #:96 [ORCL] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [ORCL] Files:1 Time: 7s
************* Final Upgrade scripts ************
Serial Phase #:98 [ORCL] Files:1 Time: 851s
******************* Migration ******************
Serial Phase #:99 [ORCL] Files:1 Time: 3s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [ORCL] Files:1 Time: 3s
Serial Phase #:101 [ORCL] Files:1 Time: 0s
Serial Phase #:102 [ORCL] Files:1 Time: 56s
***************** Post Upgrade *****************
Serial Phase #:103 [ORCL] Files:1 Time: 60s
**************** Summary report ****************
Serial Phase #:104 [ORCL] Files:1 Time: 4s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [ORCL] Files:1 Time: 3s
Serial Phase #:106 [ORCL] Files:1 Time: 0s
Serial Phase #:107 [ORCL] Files:1 Time: 28s
------------------------------------------------------
Phases [0-107] End Time:[2020_12_21 15:47:02]
------------------------------------------------------
Grand Total Time: 7751s
LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/ORCL/upgrade20201221133751/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/ORCL/upgrade20201221133751/upg_summary.log
Grand Total Upgrade Time: [0d:2h:9m:11s]
^C
[1]+ Done nohup ./dbupgrade
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$ ps -ef | grep pmon
oracle 23107 2952 0 15:56 pts/1 00:00:00 grep --color=auto pmon
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 bin]$ export ORACLE_SID=ORCL
[oracle@vm-3 bin]$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH
[oracle@vm-3 bin]$ which sqlplus
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
[oracle@vm-3 bin]$
[oracle@vm-3 bin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 21 15:57:35 2020
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 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 864026624 bytes
Database Buffers 197132288 bytes
Redo Buffers 3674112 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
ORCL READ WRITE NO 19.0.0.0.0 OPEN
SQL>
SQL> set lines 200 pages 200
SQL> col comp_id for a10
SQL> col version for a15
SQL> col status for a10
SQL> col comp_name for a37
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- --------------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
CATPROC Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 UPGRADED
XML Oracle XDK 19.0.0.0.0 UPGRADED
CATJAVA Oracle Database Java Packages 19.0.0.0.0 UPGRADED
APS OLAP Analytic Workspace 19.0.0.0.0 UPGRADED
RAC Oracle Real Application Clusters 19.0.0.0.0 UPGRADED
XDB Oracle XML Database 19.0.0.0.0 UPGRADED
OWM Oracle Workspace Manager 19.0.0.0.0 UPGRADED
CONTEXT Oracle Text 19.0.0.0.0 UPGRADED
ORDIM Oracle Multimedia 19.0.0.0.0 UPGRADED
SDO Spatial 19.0.0.0.0 UPGRADED
XOQ Oracle OLAP API 19.0.0.0.0 UPGRADED
OLS Oracle Label Security 19.0.0.0.0 UPGRADED
DV Oracle Database Vault 19.0.0.0.0 UPGRADED
15 rows selected.
SQL>Post-Upgrade Tasks :
Step-1 Run utlrp.sql :
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
2290
SQL>
SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');
COUNT(*)
----------
698
SQL>
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-12-21 16:02:05
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 2020-12-21 16:24:53
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>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>Step-2 Run postupgrade_fixups.sql ;
SQL>
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-12-21 12:29:17
For Source Database: ORCL
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
------ ------------------------ ---------- --------------------------------
5. old_time_zones_exist NO Manual fixup recommended.
6. dir_symlinks YES None.
7. post_dictionary YES None.
8. 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>Step-3 Upgrade Timezone :
For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory.
*** The following scripts get delivered with Oracle Database 18c onward
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Time zone upgrade check script
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
Time zone apply script. Warning: This script will restart the database and adjust time zone data.
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ ls -ltr utltz_upg_check.sql utltz_upg_apply.sql
-rw-r--r--. 1 oracle oinstall 33684 Sep 9 2017 utltz_upg_check.sql
-rw-r--r--. 1 oracle oinstall 21526 Sep 9 2017 utltz_upg_apply.sql
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 21 16:59:54 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>
SQL> select version from v$timezone_file;
VERSION
----------
26
SQL>
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/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>
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/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 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 931135488 bytes
Database Buffers 130023424 bytes
Redo Buffers 3674112 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 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 931135488 bytes
Database Buffers 130023424 bytes
Redo Buffers 3674112 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"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
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>Step-4 Run utlusts.sql :
*** Note: utluNNNs.sql is replaced by utlusts.sql in 19c version
*** Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql
*** Run utlusts.sql as many times as you want, at any time after the upgrade is completed.
*** utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the database components.
SQL>
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 12-21-2020 17:10:4
Database Name: ORCL
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 01:03:16
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:05:56
Oracle XDK VALID 19.3.0.0.0 00:04:40
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:55
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:58
Oracle Label Security VALID 19.3.0.0.0 00:00:30
Oracle Database Vault VALID 19.3.0.0.0 00:01:23
Oracle Text VALID 19.3.0.0.0 00:02:22
Oracle Workspace Manager VALID 19.3.0.0.0 00:02:31
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:02
Oracle XML Database VALID 19.3.0.0.0 00:05:14
Oracle Multimedia VALID 19.3.0.0.0 00:02:15
Spatial VALID 19.3.0.0.0 00:20:19
Oracle OLAP API VALID 19.3.0.0.0 00:00:54
Datapatch 00:13:59
Final Actions 00:14:14
Post Upgrade 00:00:55
Post Compile 00:22:48
Total Upgrade Time: 02:30:02
Database time zone version is 32. It meets current release needs.
SQL>Step-5 Run catuppst.sql :
You must run this script, either through DBUA or manually, if you perform a manual upgrade. DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades.
Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.
Caution: If you perform a manual upgrade, and you do not run catuppst.sql, then your database suffers performance degradation over time.
SQL>
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catuppst.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2020-12-21 17:14:56
DBUA_TIMESTAMP DBRESTART FINISHED 2020-12-21 17:14:56
DBUA_TIMESTAMP DBRESTART NONE 2020-12-21 17:14:56
TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2020-12-21 17:14:56
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2020-12-21 17:14:56
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2020-12-21 17:14:56
DBUA_TIMESTAMP POSTUP_BGN NONE 2020-12-21 17:14:56
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2020-12-21 17:14:56
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2020-12-21 17:14:56
DBUA_TIMESTAMP CATREQ_BGN NONE 2020-12-21 17:14:56
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2020-12-21 17:14:56
DBUA_TIMESTAMP CATREQ_END FINISHED 2020-12-21 17:14:56
DBUA_TIMESTAMP CATREQ_END NONE 2020-12-21 17:14:56
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2020-12-21 17:14:56
DBUA_TIMESTAMP POSTUP_END FINISHED 2020-12-21 17:14:56
DBUA_TIMESTAMP POSTUP_END NONE 2020-12-21 17:14:56
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2020-12-21 17:14:56
DBUA_TIMESTAMP CATUPPST FINISHED 2020-12-21 17:14:56
DBUA_TIMESTAMP CATUPPST NONE 2020-12-21 17:14:56
SQL>Step-6 Re-Run postupgrade_fixups.sql :
SQL>
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-12-21 12:29:17
For Source Database: ORCL
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
------ ------------------------ ---------- --------------------------------
5. old_time_zones_exist YES None.
6. dir_symlinks YES None.
7. post_dictionary YES None.
8. 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>Step-7 Reverify Invalid Objects :
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>Step-8 Drop Restore point :
SQL>
SQL> col name for a20
SQL> col GUARANTEE_FLASHBACK_DATABASE for a10
SQL> col TIME for a60
SQL> set lines 190
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE YES 21-DEC-20 01.13.45.000000000 PM
SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/ORCL/ORCL/flashback
total 1228848
-rw-r-----. 1 oracle oinstall 209723392 Dec 21 13:57 o1_mf_hy0nkkk2_.flb
-rw-r-----. 1 oracle oinstall 209723392 Dec 21 14:43 o1_mf_hy0nkq80_.flb
-rw-r-----. 1 oracle oinstall 209723392 Dec 21 14:56 o1_mf_hy0q3q4d_.flb
-rw-r-----. 1 oracle oinstall 209723392 Dec 21 15:40 o1_mf_hy0srz9l_.flb
-rw-r-----. 1 oracle oinstall 209723392 Dec 21 15:40 o1_mf_hy0x3x4l_.flb
-rw-r-----. 1 oracle oinstall 209723392 Dec 21 17:30 o1_mf_hy0tks7z_.flb
SQL>
SQL> drop restore point PRE_UPGRADE;
Restore point dropped.
SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/ORCL/ORCL/flashback
total 0
SQL>Step-9 Set compatible parameter value to 19.0.0 :
Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.
If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL>
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 931135488 bytes
Database Buffers 130023424 bytes
Redo Buffers 3674112 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>Step-10 Verify DBA_REGISTRY :
SQL>
SQL> col COMP_ID for a10
SQL> col COMP_NAME for a40
SQL> col VERSION for a15
SQL> set lines 180
SQL> set pages 999
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
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.
SQL>Step-11 Add TNS Entries in 19c TNS home :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ ls -ltr
total 16
-rw-r--r--. 1 oracle oinstall 1536 Feb 14 2018 shrept.lst
drwxr-xr-x. 2 oracle oinstall 64 Apr 17 2019 samples
-rw-r-----. 1 oracle oinstall 184 Dec 21 16:18 sqlnet.ora
-rw-r-----. 1 oracle oinstall 582 Dec 21 16:23 listener.ora
-rw-r--r--. 1 oracle oinstall 336 Dec 21 16:23 tnsnames.ora
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vm-3.localdomain)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vm-3.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES)
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-DEC-2020 17:49:38
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/vm-3/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm-3.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm-3.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 21-DEC-2020 17:49:38
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/vm-3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm-3.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$
[oracle@vm-3 admin]$ tnsping ORCL
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-DEC-2020 17:50:46
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vm-3.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)
[oracle@vm-3 admin]$Step-12 Password File :
*** REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*** Password file orapwCID copied automatically during upgrade process.
No action taken.
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ ls -ltr orapwORCL
-rw-r-----. 1 oracle oinstall 3584 Dec 21 15:58 orapwORCL
[oracle@vm-3 dbs]$
[oracle@vm-3 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 21 17:55: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> show parameter password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>Step-13 Edit oratab entry :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cat /etc/oratab | grep -i ORCL
ORCL:/u01/app/oracle/product/12.2.0.1/db_1:N
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ vi /etc/oratab
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cat /etc/oratab | grep -i ORCL
#ORCL:/u01/app/oracle/product/12.2.0.1/db_1:N
ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@vm-3 ~]$Step-14 Backup the Database :
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Dec 21 21:14:01 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1587550169)
RMAN>
RMAN> backup database plus archivelog;
Starting backup at 21-DEC-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=454 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1059772474
channel ORA_DISK_1: starting piece 1 at 21-DEC-20
channel ORA_DISK_1: finished piece 1 at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_21/o1_mf_annnn_TAG20201221T211438_hy1jq77p_.bkp tag=TAG20201221T211438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 21-DEC-20
Starting backup at 21-DEC-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-DEC-20
channel ORA_DISK_1: finished piece 1 at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_21/o1_mf_nnndf_TAG20201221T211446_hy1jqhw5_.bkp tag=TAG20201221T211446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 21-DEC-20
Starting backup at 21-DEC-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=1059772553
channel ORA_DISK_1: starting piece 1 at 21-DEC-20
channel ORA_DISK_1: finished piece 1 at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_21/o1_mf_annnn_TAG20201221T211554_hy1jslfg_.bkp tag=TAG20201221T211554 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-20
Starting Control File and SPFILE Autobackup at 21-DEC-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_21/o1_mf_s_1059772555_hy1jsng7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-DEC-20
RMAN> exit
Recovery Manager complete.
[oracle@vm-3 ~]$I hope my written content will helped you. Your suggestions/feedback are most welcome orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.