We recently migrated from Oracle 11gR2 to 19c for our Oracle Applications (12.2.10) & decided to continue with old RMAN plans. Run a shell script, call RMAN as “Oracle” user, copy current day backups to a remote server & finalizing the activities with email sent.
Our previous database compressed RMAN backups were a maximum of 142GB per day, that grew to 190-192GB (few datafiles were added) after the upgrade. This made us to reduce the retention period from 7 to 5 and next day, while going through the RMAN logs, I found the following error.
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c4 channel at 02/10/2022 22:24:51
ORA-19571: archived log RECID 416 STAMP 1095631214 not found in control file
RMAN>
Recovery Manager complete.
So, I went through the previous days logs and found this error started being logged from 9th of Feb and rest were all okay.
I checked database “control_file_record_keep_time” parameter and found the value set as “7” which was more than our current backup retention period.
So the next factor to check was the RMAN script that we were using over a period of 6-7 years without a single failure.
export PATH=$ORACLE_HOME/bin:$PATH
export LAST_RMANLOG_FILE="rman_dailyfull`date +%d%m%y`.log"
echo "Starting Full Daily Database Backup of oracle ebsr12 PROD database"
date
rman target / << EOF >/u03/RMAN/log/rman_dailyfull`date +%d%m%y`.log
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
crosscheck archivelog all;
crosscheck backup;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u03/RMAN/DAILYBKP/rman_comp_%d_lvl0_%U' TAG "dailyfull_db_lvl0_bkp" INCLUDE CURRENT CONTROLFILE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '/u03/RMAN/DAILYBKP/archive_%d_lvl0_%U';
DELETE NOPROMPT archivelog all completed before'sysdate-7';
backup current controlfile format '/u03/RMAN/DAILYBKP/bkpcontrol_file.ctl_%d_%T';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 5 DAYS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}
EOF
After a close reading, I found the typo. I was deleting the obsolete backups within a window of 5 days & trying to delete logs which were “7” days old.
DELETE NOPROMPT archivelog all completed before'sysdate-5';
That’s all. No more RMAN-03009, ORA-19571, or archived log RECID <NNN> STAMP <NNNNNNNNNN> not found in control file errors.
I apologize for not including the datapatch part with the post. I don’t know how did I miss it entirely, which is not me usually. Amending the datapatch part by the bottom of the article.
Much of the times I use latest Oracle technologies for learning & they hardly make to any of the PRODUCTION environments those I support at work. Recently I wanted to see how to patch Oracle 19c installation on Windows and to be frank, it was buttery smooth.
Let us quickly see how to apply a patch to a Windows installation of Oracle 19c database.
Shutdown all instances & Windows services for Oracle. Make sure you have taken adequate number of backups for the database(s).
Check you computer’s PATH variable. Insure you have %ORACLE_HOME%\perl\bin, in my case “D:\Oracle\19c\perl\bin” as the first entry for PERL.
This is how the PATH information before I make the above changes to it.
Here 11g 32Bit client is the first Oracle software in the PATH, we will modify it like below
Once the patching done, you can rollback the PATH variable according to your requirements.
Visit Oracle support and download the patch, the latest patch for Oracle Database 19c is “32409154”. Patch is only available to customers with a valid support contract.
Extract the archive.
I’ve extracted the archive within the Download folder and the path is
As usual, based on your computer’s hardware capabilities, the patch would finish sooner or later. There will be 2 prompts those you need to say Yes in order to progress the patching.
Once OPatch completes, it is time for us to execute datapatch for the databases that are going to get upgraded to the latest version that you just patched.
Make sure that you have restarted the Windows Services for Oracle. Patching restarts the database, regardless I would suggest you to shutdown and restart the database once after Oracle services are restarted. Insure the PDBs are opened.
Move to $ORACLE_HOME/OPatch folder & execure “datapatch.bat -verbose”
PS D:\Oracle19c\OPatch> .\datapatch.bat -verbose
SQL Patching tool version 19.3.0.0.0 Production on Wed Mar 9 18:32:28 2022
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: D:\Oracle19c\cfgtoollogs\sqlpatch\sqlpatch_12584_2022_03_09_18_32_28\sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.11.0.0.0 Release_Update 210417094636: Installed
PDB CDB$ROOT:
No release update patches installed
PDB PDB$SEED:
No release update patches installed
PDB SCT:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED SCT
No interim patches need to be rolled back
Patch 32409154 (Windows Database Bundle Patch : 19.11.0.0.210420 (32409154)):
Apply from 19.1.0.0.0 Feature Release to 19.11.0.0.0 Release_Update 210417094636
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 32409154 apply (pdb CDB$ROOT): SUCCESS
logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\32409154\24073904/32409154_apply_SCTCDB_CDBROOT_2022Mar09_18_33_46.log (no errors)
Patch 32409154 apply (pdb PDB$SEED): SUCCESS
logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\32409154\24073904/32409154_apply_SCTCDB_PDBSEED_2022Mar09_18_39_19.log (no errors)
Patch 32409154 apply (pdb SCT): SUCCESS
logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\32409154\24073904/32409154_apply_SCTCDB_SCT_2022Mar09_18_39_19.log (no errors)
SQL Patching tool complete on Wed Mar 9 18:45:01 2022
PS D:\Oracle19c\OPatch>
Now we have to check for invalid objects. My database didn’t have any objects, it was a default installation with 0 custom objects in the SCT. However, if you were having a PRODUCTION database, you must confirm that there are no invalid objects post patching. We will see how to accomplish this at next step.
C:\Windows\system32>SET ORACLE_HOME=D:\Oracle19c
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 9 18:54:54 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-03-09 18:55:49
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 2022-03-09 18:55:50
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> alter session set container=PDB$SEED;
Session altered.
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.
SQL> alter pluggable database pdb$seed OPEN READ WRITE;
Pluggable database altered.
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-03-09 19:06:20
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 2022-03-09 19:06:22
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> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.
SQL> alter pluggable database pdb$seed OPEN READ ONLY;
Pluggable database altered.
SQL> alter session set "_oracle_script"=FALSE;
Session altered.
SQL> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.
SQL> alter pluggable database pdb$seed OPEN READ ONLY;
Pluggable database altered.
SQL> alter session set container=SCT;
Session altered.
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-03-09 18:56: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 2022-03-09 18:56:36
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>
Use SQL Developer & check whether there are any invalid objects. In addition to invalid objects, you must check plugged in database violations also. We will see how to in next few steps.
You need to check PDB violations on both root and pdb containers (CDB$ROOT, SCT (my pdb name)). Warnings could be addressed later, TYPE “ERROR” should be immediately addressed before you open the PDB for read write operations.
Fix the errors following instructions available from either Oracle support or blog posts (better & less complex)
Simple & neat right? Share your experience in the comments area.
Our business application is hosted on Linux. I love the way the OS is fine tuned by Oracle for the database & without arguments, I must say, Oracle is perfect on Linux.
Does that stop me from installing the latest Oracle database & experimenting them on Windows? Nay, from the known days I’ve used Windows OS for PC & I am sure, will be retiring using one. I’ve made many attempts to install and configure many unsupported Oracle products on Windows & have a VERY good success record ;)
Yet, I never ever upgraded Oracle databases on Windows to next version as I never had a database that needed to be backed up or restored when a new version was tried out. This time I decided to change that habit and to upgrade my existing 12c CDB database to 19c following some documents specifically written for Linux!
Well, regardless whether you are a Linux or Windows person, trust me, the instructions provided for either platform is 99% applicable on both. So, translating the instructions on Linux for Windows are NOT truly difficult, in case if you are IN for some geeky stuffs.
So let us quickly assess my environment. I had 12c CDB with one PDB, which I don’t remember whether I created during the software installation or later. As usual, the following instructions are for a lab setup. That means, you must make sure that you make proper backups prior moving them to a Production environment. My TEST database didn’t have any data, just the sample schemas.
Install Oracle Database 19c. Choose “Software Only” option. That means do not create a database along with the installation. You may refer my earlier post for Oracle 19c installation here.
Now, let us see how to upgrade our 12c database to 19c. There are many prerequisites for a successful upgrade. Oracle provides many tools to insure a successful upgrade.
Start your 12c database and open an elevated command prompt.
From your 12c database home aka %ORACLE_HOME%\bin folder execute prerequisites collection program. Example
D:\Oracle\product\12.2.0\dbhome_1\bin>java -jar D:\Oracle\product\19.3.0\rdbms\admin\preupgrade.jar TEXT TERMINAL
TEXT TERMINAL will force the program to output all the instructions to the current console. You should copy the output to a text file & go through the findings. Against the pre-upgrade instructions, you must satisfy each one of them & re-run the prerequisites collection once again. If you are upgrading a Container database with pluggable databases, the post-upgrade activities will be listed per database.
A Sample output as below:
D:\Oracle\product\12.2.0\dbhome_1\bin>java -jar D:\Oracle\product\19.3.0\rdbms\admin\preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:45:58
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: CDB12C
Container Name: CDB$ROOT
Container ID: 1
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
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 915 MB
TEMP 33 MB 150 MB
Minimum tablespace sizes for upgrade are estimates.
4. No action needed.
Using default parallel upgrade options, this CDB with 2 PDBs will first
upgrade the CDB$ROOT, and then upgrade at most 2 PDBs at a time using 2
parallel processes per PDB.
The number of PDBs upgraded in parallel and the number of parallel
processes per PDB can be adjusted as described in Database Upgrade Guide.
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 CDB12C container CDB$ROOT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@D:\Oracle\cfgtoollogs\cdb12c\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 CDB12C container CDB$ROOT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:46:22
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: CDB12C
Container Name: PDB$SEED
Container ID: 2
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: TRUE
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 330 MB 500 MB
SYSTEM 250 MB 358 MB
TEMP 64 MB 150 MB
UNDOTBS1 100 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDB12C container PDB$SEED
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. 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.
5. 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.
6. (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.
7. 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 CDB12C container PDB$SEED
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-20T17:46:22
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: CDB12C
Container Name: SCT
Container ID: 3
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
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 350 MB 500 MB
SYSTEM 250 MB 358 MB
TEMP 64 MB 150 MB
UNDOTBS1 100 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDB12C container SCT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. 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.
5. 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.
6. (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.
7. 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 CDB12C container SCT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@D:\Oracle\cfgtoollogs\cdb12c\preupgrade\/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade.log
D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade_fixups.sql
D:\Oracle\cfgtoollogs\cdb12c\preupgrade\postupgrade_fixups.sql
Execute fixup scripts across the entire CDB:
Before upgrade:
1. Execute preupgrade fixups with the below command
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l D:\Oracle\cfgtoollogs\cdb12c\preupgrade\ -b preup_cdb12c D:\Oracle\cfgtoollogs\cdb12c\preupgrade\preupgrade_fixups.sql
2. Review logs under D:\Oracle\cfgtoollogs\cdb12c\preupgrade\
After the upgrade:
1. Execute postupgrade fixups with the below command
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\rdbms\admin %ORACLE_HOME%\rdbms\admin\catcon.pl -l D:\Oracle\cfgtoollogs\cdb12c\preupgrade\ -b postup_cdb12c D:\Oracle\cfgtoollogs\cdb12c\preupgrade\postupgrade_fixups.sql
2. Review logs under D:\Oracle\cfgtoollogs\cdb12c\preupgrade\
Preupgrade complete: 2020-11-20T17:46:32
D:\Oracle\product\12.2.0\dbhome_1\bin>
Once all the prerequisites are met, we can use the GUI tool “Database Upgrade Assistant” to upgrade our existing database(s). For this exercise, I will be upgrading my 12c Container database with one PDB
Select the 12c database that you want to upgrade. Please note, my database hardly had any data & was not on archive log mode. If you are planning to upgrade a production database that has archive log enabled, take proper backups prior proceeding.
I’ve an exclusive user account “oracle” for all Oracle installations. If you have used another user account, provide it at this step.
As my database doesn’t have archive logging, my best choice is to create an Offline RMAN backup. You may choose another option from the available.
My 12c database had a LISTENER that is listening to 1521 & by mistake I have selected a PORT 5500. Please adjust your PORT number.
Above I said how I wrongly selected a PORT number for LISTENER, I’ve selected the same PORT number for the Enterprise Manager also. Well, later I corrected the mistake. So be careful while you select PORT numbers.
Sit back, watch a movie or go for a walk. The following activities are going to take considerable time. Bigger your database, more time needed to complete the upgrade. I’ve pretty a beast as desktop and whole upgrade took almost couple of hours. Based on your available resources, the upgrade might take longer times.
Okay, I missed taking the last Screenshot. Once the upgrade finished, you can start with post-upgrade activities as they are listed with the prerequisites output.
That’s all folks. Have comments? Please send them to me.
Once downloaded, you have to extract the package to a folder. Please note, the extracted folder or the folder from which you will execute “Setup.exe” is going to be 19c Oracle Home. So, name the folder wisely. For example, I have extracted the files to a folder D:\Oracle\19c
You can start the installation by right clicking the Setup.exe and running as “Administrator”.
This will kick start the installation.
We will not create a database along with the software installation. Hence select “Set Up Software Only”
I suggest you to create a normal user (no administrator privileges) “oracle”, that will be used for all your future Oracle software installations. If you don’t have an account explicitly created yet for Oracle, the installation gives you an interface to create one. It’s your choice.
Please remember, earlier I said, the folder where you have extracted the installation media is going to be Oracle 19c home, aka Oracle Home. Rename the folder with an appropriate name before going ahead with the installation.
You will be asked to provide a location for Oracle Base. To content within the Oracle Home, you can specify a path like “D:\Oracle\19c\database” or “D:\Oracle\19c\db” or a folder name of your choice.
After few basic checks, the installation will proceed now. I strongly suggest you to assign a STATIC IP for your computer, prior continuing.
That’s it. No hacks, no modifications to config files or anything such to get the software installed on Windows!.
You can use “Database Configuration Assistant” for creating new database. We will see how to create a new database now. This time we will be creating a CDB (Container Database) and one PDB (Pluggable Database). You may go ahead with creating a single instance database when asked to choose between.
Select “Advanced Configuration”
You can choose between a normal database or Container database with PDB at next step. If you don’t wish to create the database as a container, uncheck the option “Create as Container database”. If you chose to continue with creating your database as container, I suggest you to leave the defaults as it is, other than the PDB name. If you need more PDBs, may add later to the container.
Leave the defaults, unless you know what you are doing.
If you are setting up the database for the LAB/Learning purposes, you may ignore these. However, for production instances, you must configure the Recovery area & setup archiving.
You can create a LISTENER for your database now. If you other Oracle database LISTENERs already running, choose a port number that wouldn’t raise a conflict.
Optionally you can setup Database Vault & Label Security at this step ;)
Okay, we need your attention here. Oracle automatically allocates 40% of total available physical memory for the newly created database. As I mentioned in the previous step, if you have more than one database running from the same computer, you must adjust the available memory to avoid choking the resources.
You may leave the defaults untouched here & you can change this value as sys admin when needed.
WebLogic Repos insist upon “AL32UTF8” Character Set & unless you have specific reasons to change the Character set, leave the defaults. If you are planning to migrate a legacy application that uses a different character set like Arabic (AR8MSWIN1256), please read this post
As per the post, you can use a different character set for a PDB starting from Oracle database versions 12.2.
We do love Scott bit too much, if yes, go with sample schemas (Trust me, you need it)
Enterprise Manager configuration is totally optional. If you choose to, will install an Express edition of Oracle Enterprise Manager (means more resources)
As usual, for a LAB environment I will go with same password for all accounts.
I would suggest you to accept the defaults at next step & change the passwords and unlock accounts as and when needed.
That should complete the installation and creation of database for 19c. Have comments? Please post them to me.
