Performing Oracle DB 19c upgrade checks in snapshot standby database
Requirement: If you have any setting in primary which will be reported as failure in preupgrade check and you want to test the fixes for them, you can try fixing them in your snapshot standby database. Here I am conducting preupgrade check itself in snapshot standby in preperation to conduct a unique db upgrade.
In this blog, I will show you the outcomes of the check in both primary and standby site for the preupgrade check.
Primary preupgrade check output: (DV: ON, TDE: ON)
$ORACLE_HOME/jdk/bin/java -jar /home/oracle/dba/UpgradeCheck4/preupgrade.jar FILE DIR /home/oracle/dba/UpgradeCheck4
/vagrant/dbupgdiag.sql [/home/oracle/dba/UpgradeCheck4]
Acutal output:
1* select parameter,value from v$option where upper(parameter) like '%VAULT%'
SQL> /
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
Oracle Database Vault TRUE
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
Oracle Database Vault TRUE
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------------------------ -------------------- --------- --------- ----------
FILE
/u01/app/oracle/admin/GGSRC04T/wallet/
OPEN AUTOLOGIN SINGLE NO 0
SQL>
----------- Preupgrade check output - actual from primary
[oracle@vcentos79-oracle-ggsrc UpgradeCheck4]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2023-09-04T12:11:11
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: GGSRC04T
Container Name: GGSRC04T
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
Oracle Workspace Manager [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 2741
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 1024 MB. There is currently 596 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /oraarch
Limit : 1024 MB
Used : 428 MB
Available : 596 MB
The database has archivelog mode enabled, and the upgrade process will
need free space to generate archived logs to the recovery area specified
by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated
must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that
can cause the upgrade to not proceed.
RECOMMENDED ACTIONS
===================
2. If an auto-login Oracle Transparent Data Encryption (TDE) Keystore is
correctly set up, no action needs to be taken. Otherwise, before starting
up the database in upgrade mode in the new Oracle Database Oracle Home,
either open the TDE Keystore, or ensure that an auto-login TDE Keystore
is configured for the system. If errors are seen while the database
upgrade is running because the TDE Keystore is closed, then open the TDE
Keystore and resume the upgrade (see "catctl.pl -R"). It may be
necessary to open the TDE Keystore as the upgrade progresses in a non-CDB
or CDB (e.g., in CDB$ROOT and PDB) if no auto-login TDE Keystore has been
configured, as the upgrade process can shutdown and startup the
database.
The database is using TDE. The database upgrade process can involve
operations (certain SQL statements and/or database startups) that need to
access the encryption key.
If Oracle Transparent Data Encryption (TDE) is in use, the database
system must have access to its Oracle Transparent Data Encryption Master
Encryption Key during database upgrade. For more information on
configuring Transparent Data Encryption, refer to the 12.2 Oracle
Database Advanced Security Guide, Section 3: Configuring Transparent Data
Encryption.
3. (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.
INFORMATION ONLY
================
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
TEMP 20 MB 150 MB
UNDOTBS1 285 MB 412 MB
Minimum tablespace sizes for upgrade are estimates.
5. Synchronize your standby databases before database upgrade.
The standby database is not currently synchronized with its associated
primary database.
To keep data in the source primary database synchronized with its
associated standby databases, all standby databases must be synchronized
before database upgrade. See My Oracle Support Note 2064281.1 for
details.
6. 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 GGSRC04T
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/dba/UpgradeCheck4/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
7. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
There are user tables dependent on Oracle-Maintained object types.
If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type is
evolved during the upgrade, any dependent tables need to be re-validated
and upgraded to the latest type version AFTER the database upgrade
completes.
8. 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.
9. 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.
10. (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.
11. 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 GGSRC04T
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/dba/UpgradeCheck4/postupgrade_fixups.sql
[oracle@vcentos79-oracle-ggsrc UpgradeCheck4]$
---------
Snapshot Standby preupgrade check output: (DV: OFF, TDE: ON)
Step 1) DB is open in read write mode
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
GGSRC04T MOUNTED
--------- --------------------
GGSRC04T MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
GGSRC04T READ WRITE
NAME OPEN_MODE
--------- --------------------
GGSRC04T READ WRITE
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
Step 2) DB is in intended state (DV: OFF, TDE: ON)
SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault FALSE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault FALSE
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
------------------------------ -------------------- --------- --------- ----------
FILE
/u01/app/oracle/admin/GGSRC04TSB1/wallet/
OPEN AUTOLOGIN SINGLE NO 0
Step 3) Perform preupgrade checks for 19c
$ORACLE_HOME/jdk/bin/java -jar /home/oracle/dba/UpgradeCheck4/preupgrade.jar FILE DIR /home/oracle/dba/SBYUpgradeCheck4
/vagrant/dbupgdiag.sql [/home/oracle/dba/SBYUpgradeCheck4]
Actual output:
[oracle@vcentos79-oracle-ggsrc ~]$ ls -ld /home/oracle/dba/SBYUpgradeCheck4
drwxr-xr-x. 2 oracle oinstall 6 Sep 4 11:59 /home/oracle/dba/SBYUpgradeCheck4
drwxr-xr-x. 2 oracle oinstall 6 Sep 4 11:59 /home/oracle/dba/SBYUpgradeCheck4
[oracle@vcentos79-oracle-ggsrc ~]$ $ORACLE_HOME/jdk/bin/java -jar /home/oracle/dba/UpgradeCheck4/preupgrade.jar FILE DIR /home/oracle/dba/SBYUpgradeCheck4
==================
PREUPGRADE SUMMARY
==================
/home/oracle/dba/SBYUpgradeCheck4/preupgrade.log
/home/oracle/dba/SBYUpgradeCheck4/preupgrade_fixups.sql
/home/oracle/dba/SBYUpgradeCheck4/postupgrade_fixups.sql
PREUPGRADE SUMMARY
==================
/home/oracle/dba/SBYUpgradeCheck4/preupgrade.log
/home/oracle/dba/SBYUpgradeCheck4/preupgrade_fixups.sql
/home/oracle/dba/SBYUpgradeCheck4/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/home/oracle/dba/SBYUpgradeCheck4/preupgrade_fixups.sql
Before upgrade:
Log into the database and execute the preupgrade fixups
@/home/oracle/dba/SBYUpgradeCheck4/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/home/oracle/dba/SBYUpgradeCheck4/postupgrade_fixups.sql
Log into the database and execute the postupgrade fixups
@/home/oracle/dba/SBYUpgradeCheck4/postupgrade_fixups.sql
Preupgrade complete: 2023-09-04T12:13:32
[oracle@vcentos79-oracle-ggsrc ~]$
------- Preupgrade check results from standby site
[oracle@vcentos79-oracle-ggsrc ~]$ cat /home/oracle/dba/SBYUpgradeCheck4/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2023-09-04T12:13:32
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: GGSRC04T
Container Name: GGSRC04T
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
Oracle Workspace Manager [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4084
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 2048 MB. There is currently 1606 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /oraarch
Limit : 2048 MB
Used : 442 MB
Available : 1606 MB
The database has archivelog and flashback enabled, and the upgrade
process will need free space to generate archived and flashback logs to
the recovery area specified by initialization parameter
DB_RECOVERY_FILE_DEST. The logs generated must not overflow the limit
set by DB_RECOVERY_FILE_DEST_SIZE, as that can cause the upgrade to not
proceed.
RECOMMENDED ACTIONS
===================
2. If an auto-login Oracle Transparent Data Encryption (TDE) Keystore is
correctly set up, no action needs to be taken. Otherwise, before starting
up the database in upgrade mode in the new Oracle Database Oracle Home,
either open the TDE Keystore, or ensure that an auto-login TDE Keystore
is configured for the system. If errors are seen while the database
upgrade is running because the TDE Keystore is closed, then open the TDE
Keystore and resume the upgrade (see "catctl.pl -R"). It may be
necessary to open the TDE Keystore as the upgrade progresses in a non-CDB
or CDB (e.g., in CDB$ROOT and PDB) if no auto-login TDE Keystore has been
configured, as the upgrade process can shutdown and startup the
database.
The database is using TDE. The database upgrade process can involve
operations (certain SQL statements and/or database startups) that need to
access the encryption key.
If Oracle Transparent Data Encryption (TDE) is in use, the database
system must have access to its Oracle Transparent Data Encryption Master
Encryption Key during database upgrade. For more information on
configuring Transparent Data Encryption, refer to the 12.2 Oracle
Database Advanced Security Guide, Section 3: Configuring Transparent Data
Encryption.
3. (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.
INFORMATION ONLY
================
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
TEMP 20 MB 150 MB
UNDOTBS1 285 MB 412 MB
Minimum tablespace sizes for upgrade are estimates.
5. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database GGSRC04T
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/dba/SBYUpgradeCheck4/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
6. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
There are user tables dependent on Oracle-Maintained object types.
If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type is
evolved during the upgrade, any dependent tables need to be re-validated
and upgraded to the latest type version AFTER the database upgrade
completes.
7. 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.
8. 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.
9. (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.
10. 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 GGSRC04T
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/dba/SBYUpgradeCheck4/postupgrade_fixups.sql
[oracle@vcentos79-oracle-ggsrc ~]$
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2023-09-04T12:13:32
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: GGSRC04T
Container Name: GGSRC04T
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
Oracle Workspace Manager [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 4084
MB. Check alert log during the upgrade to ensure there is remaining free
space available in the recovery area.
DB_RECOVERY_FILE_DEST_SIZE is set at 2048 MB. There is currently 1606 MB
of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /oraarch
Limit : 2048 MB
Used : 442 MB
Available : 1606 MB
The database has archivelog and flashback enabled, and the upgrade
process will need free space to generate archived and flashback logs to
the recovery area specified by initialization parameter
DB_RECOVERY_FILE_DEST. The logs generated must not overflow the limit
set by DB_RECOVERY_FILE_DEST_SIZE, as that can cause the upgrade to not
proceed.
RECOMMENDED ACTIONS
===================
2. If an auto-login Oracle Transparent Data Encryption (TDE) Keystore is
correctly set up, no action needs to be taken. Otherwise, before starting
up the database in upgrade mode in the new Oracle Database Oracle Home,
either open the TDE Keystore, or ensure that an auto-login TDE Keystore
is configured for the system. If errors are seen while the database
upgrade is running because the TDE Keystore is closed, then open the TDE
Keystore and resume the upgrade (see "catctl.pl -R"). It may be
necessary to open the TDE Keystore as the upgrade progresses in a non-CDB
or CDB (e.g., in CDB$ROOT and PDB) if no auto-login TDE Keystore has been
configured, as the upgrade process can shutdown and startup the
database.
The database is using TDE. The database upgrade process can involve
operations (certain SQL statements and/or database startups) that need to
access the encryption key.
If Oracle Transparent Data Encryption (TDE) is in use, the database
system must have access to its Oracle Transparent Data Encryption Master
Encryption Key during database upgrade. For more information on
configuring Transparent Data Encryption, refer to the 12.2 Oracle
Database Advanced Security Guide, Section 3: Configuring Transparent Data
Encryption.
3. (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.
INFORMATION ONLY
================
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
TEMP 20 MB 150 MB
UNDOTBS1 285 MB 412 MB
Minimum tablespace sizes for upgrade are estimates.
5. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database GGSRC04T
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/dba/SBYUpgradeCheck4/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
6. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
There are user tables dependent on Oracle-Maintained object types.
If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type is
evolved during the upgrade, any dependent tables need to be re-validated
and upgraded to the latest type version AFTER the database upgrade
completes.
7. 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.
8. 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.
9. (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.
10. 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 GGSRC04T
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/dba/SBYUpgradeCheck4/postupgrade_fixups.sql
[oracle@vcentos79-oracle-ggsrc ~]$
------- dbupgdiag.sql output from standby site
[oracle@vcentos79-oracle-ggsrc ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 4 12:18:58 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @/vagrant/dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /home/oracle/dba/SBYUpgradeCheck4
04_Sep_2023_1219 .log
GGSRC04T_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 09-04-2023 12:19:14
===============
Hostname
===============
vcentos79-oracle-ggsrc
===============
Database Name
===============
GGSRC04T
===============
Database Uptime
===============
11:25 04-SEP-23
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
=============
Compatibility
=============
Compatibility is set as 12.2.0
================
Archive Log Mode
================
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/GGSRC04T
SB1/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string OS
unified_audit_sga_queue_size integer 1048576
================
Cluster Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================
OWNER TABLESPACE_NAME
------------ ------------------------------
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
*
ERROR at line 1:
ORA-00942: table or view does not exist
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
0
==========================================
Oracle Label Security is installed or not
==========================================
Oracle Label Security is installed
================
Number of AQ Records in Message Queue Tables
================
SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - ORA$PREPLUGIN_BACKUP_QTB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
26
================
Local Listener
================
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
XS$NULL TEMP SYSTEM
LBACSYS TEMP SYSTEM
OUTLN TEMP SYSTEM
SYS$UMF TEMP SYSTEM
DBSNMP TEMP SYSAUX
APPQOSSYS TEMP SYSAUX
DBSFWUSER TEMP SYSAUX
GGSYS TEMP SYSAUX
ANONYMOUS TEMP SYSAUX
DVSYS TEMP SYSAUX
DVF TEMP SYSAUX
GSMADMIN_INTERNAL TEMP SYSAUX
XDB TEMP SYSAUX
WMSYS TEMP SYSAUX
GSMCATUSER TEMP USERS
DBV_OWNER TEMP USERS
SYSBACKUP TEMP USERS
REMOTE_SCHEDULER_AGENT TEMP USERS
SYSRAC TEMP USERS
AUDSYS TEMP USERS
DIP TEMP USERS
SYSKM TEMP USERS
ORACLE_OCM TEMP USERS
SYSDG TEMP USERS
GSMUSER TEMP USERS
ENCVAULT_TEST TEMP ENCRYPT_TS1
ENCVAULT_TEST_RO TEMP ENCRYPT_TS1
GGADMIN TEMP GG_DATA
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATALOG Oracle Database Catalog Views VALID 12.2.0.1.0
CATPROC Oracle Database Packages and Types VALID 12.2.0.1.0
DV Oracle Database Vault VALID 12.2.0.1.0
OLS Oracle Label Security VALID 12.2.0.1.0
OWM Oracle Workspace Manager VALID 12.2.0.1.0
XDB Oracle XML Database VALID 12.2.0.1.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
-------- -----------------------------------
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ....
COUNT(1)
----------
0
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ....
DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE DBMS_REPCAT_AUTH
DOC>PACKAGE BODY DBMS_REPCAT_AUTH
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#
Querying for password protected roles ....
================
JVM Verification
================
JAVAVM - NOT Installed. Below results can be ignored
================================================
Checking Existence of Java-Based Users and Roles
================================================
DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
No JAVA related Roles
Roles
=========================================
List of Invalid Java Objects owned by SYS
=========================================
There are no SYS owned invalid JAVA objects
DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
select dbms_java.longname('foo') "JAVAVM TESTING" from dual
*
ERROR at line 1:
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
===================================
Oracle Multimedia/InterMedia status
===================================
Oracle Multimedia/interMedia is NOT installed at database level
PL/SQL procedure successfully completed.
*** End of LogFile ***
Upload db_upg_diag_GGSRC04T_04_Sep_2023_1219.log from "/home/oracle/dba/SBYUpgradeCheck4" directory
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@vcentos79-oracle-ggsrc ~]$
>> Both primary and standby came out resembling in the preupgrade check result.
YouTube Video:
Thanks
No comments:
Post a Comment