2019 ~ Dilli's Oracle/MySQL Linux Blog

Environment

OS: CEntOS 6.10

Oracle: Oracle Database 11.2.0.4 SE with JUL2019 PSU Applied

Memory: 4G

HDD: 100G

Prerequisites

Verify all prerequisites packages are installed on the server. Install oracle-database-preinstall-18c package before upgrading. If you have internet access on the server you can download and install the package with yum command or you can download package from internet and install manually. Use link below to download it manually.

Execute yum command to download and install pre-install package.

yum -y install oracle-database-preinstall-18c

Create new Oracle Home for Oracle 18c.

mkdir -p /u01/app/oracle/product/18.0.0/dbhome_1

chown -R oracle:oinstall /u01/app/oracle/product/18.0.0/dbhome_1/

Extract 18c source file to newly created Oracle Home.

su - oracle

cd /media/sf_Softwares/Oracle

unzip LINUX.X64_180000_db_home.zip -d /u01/app/oracle/product/18.0.0/dbhome_1

Add new Oracle Home setting to /etc/oratab. Make sure to comment or remove the older OH.

vi /etc/oratab

Modify response file.

Once extract is completed, change directory to 18c Oracle Home. Change environment variables and set recently created directory as ORACLE_HOME. Copy response file to some other location. This will preserve original respose file. Modify respose file till the Grid Option.

Set all the parameters value till you reach Grid Options.

. oraenv

cd $ORACLE_HOME

cd install/response/

cp db_install.rsp ~oracle/

cd ~oracle/

vi db_install.rsp

Modify response file and fill-up all the required parameter values. Before updating response file it has contents as below.

Once it has been modified and updated it looks as below.

Execute command below to start installation

./runInstaller -silent -ignorePrereq -waitforcompletion -responseFile ~oracle/db_install.rsp SECURITY_UPDATES_VIA_MYORACLESUPPORT=false DECLINE_SECURITY_UPDATES=true

Execute root.sh as user root described in installation output.

/u01/app/oracle/product/18.0.0/dbhome_1/root.sh

Download and extract pre-upgrade file to new Oracle home location.

Download the latest "preupgrade.jar" file from MOS 884522.1.

cd /media/sf_Softwares/Oracle/

unzip preupgrade_181_cbuild_7_lf.zip -d /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/

Executed pre-upgrade jar before upgrade. Make sure all issue reported should be fixed before upgrading database to latest version.

Output should be something like below. Fixes are available at the buttom.

Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 Build: 6 on 2019-09-01T11:16:07

Upgrade-To version: 18.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: R1

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
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] INVALID
Oracle Enterprise Manager Repository [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
Expression Filter [to be upgraded] VALID
Rule Manager [to be upgraded] VALID
Oracle OLAP API [to be upgraded] INVALID

==============
BEFORE UPGRADE
==============

REQUIRED ACTIONS
================
1. Contact Oracle Support for instructions on how to resolve this error.
ERROR: ORA-29548 ORA-29548: Java system class reported: could not
identify release specified in classes.bin

There is a problem with the JAVAVM component and database upgrade cannot
be performed until it is corrected.

JAVAVM component must be functioning properly before database upgrade.

RECOMMENDED ACTIONS
===================
2. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
This action may be done now or when starting the database in upgrade mode
using the 18.0.0.0.0 ORACLE HOME.

Parameter Currently 18.0.0.0.0 minimum
--------- --------- ------------------
*memory_target 729808896 1203765248
processes 150 300

The database upgrade process requires certain initialization parameters
to meet minimum values. The Oracle upgrade process itself has minimum
values which may be higher and are marked with an asterisk. After
upgrading, those asterisked parameter values may be reset if needed.

3. Remove the EM repository.

- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
18.0.0.0.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.

Step 1: If database control is configured, stop EM Database Control,
using the following command

$> emctl stop dbconsole

Step 2: Connect to the database using the SYS account AS SYSDBA

SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql

Without the set echo and serveroutput commands, you will not be able to
follow the progress of the script.

The database has an Enterprise Manager Database Control repository.

Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.

4. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.

The OLAP Catalog component, AMD, exists in the database.

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.

5. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.

The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least
version 5.1.3.00.05.

Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.

6. (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 11.2.0.4
Oracle Database Performance Tuning Guide.

7. Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. You can list those triggers using "SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIM(BASE_OBJECT_TYPE)='DATABASE'
AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE='ADMINISTER DATABASE TRIGGER')"

There is one or more database triggers whose owner does not have the
right privilege on the database.

The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.

8. (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 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
9. 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 510 MB 720 MB
SYSTEM 760 MB 1195 MB
TEMP 20 MB 150 MB
UNDOTBS1 50 MB 446 MB

Minimum tablespace sizes for upgrade are estimates.

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/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
10. (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.

11. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 14 and the target 18.0.0.0.0
release ships with time zone file version 31.

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 18.0.0.0.0 Oracle Database
Globalization Support Guide.

12. (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.

13. 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 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
14. Check the Oracle documentation for the identified components for their
specific upgrade procedure.

The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB

The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.

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/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-09-01T11:16:07
[oracle@oratest dbhome_1]$

Fix all the issues and execute preupgrade_fixups.sql

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

Recompile all invalid objects

@?/rdbms/admin/utlrp

Stop database and listener process that is running from 11g

shutdown immediate

lsnrctl stop

Copy network configuration files, spfile and password files to new ORACLE_HOME.

Start listener and database in upgrade mode

Change environments to 18c

Start listener

Start database in upgrade mode

lsnrctl start

sqlplus / as sysdba

startup upgrade

Set following environments. Without these environment variable I had encountered error.

cat >> .bashrc

export LANG=en_US.UTF-8

export LC_ALL="en_US.UTF-8"

source .bash_profile

echo $LANG

echo $LC_ALL

Error I have encountered:

[oracle@oratest ~]$ cat /u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20190625090048/catupgrd_datapatch_upgrade.log

SQL Patching tool version 18.0.0.0.0 Production on Tue Jun 25 09:23:57 2019

Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18753_2019_06_25_09_23_57/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Bootstrapping registry and package to current versions...done

Error: prereq checks failed!

verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18753_2019_06_25_09_23_57/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Tue Jun 25 09:24:04 2019

[oracle@ oratest ~]$

Start Oracle database upgrading

Change directory to 18c ORACLE_HOME/bin

execute dbupgrade

cd $ORACLE_HOME/bin

./dbupgrade

Start database once up gradation is completed. Check timezone file version. If version of the file is less than 31 then apply timezone fix.

sqlplus / as sysdba

startup

SELECT * FROM v$timezone_file;

Top apply timezone fix start database in upgrade mode.

shutdown immediate

startup upgrade

Execute Timezone fix

SET SERVEROUTPUT ON

DECLARE

l_tz_version PLS_INTEGER;

BEGIN

l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.begin_upgrade(l_tz_version);

END;

Start database normally and apply upgrades

shutdown immediate

startup

SET SERVEROUTPUT ON

DECLARE

l_failures PLS_INTEGER;

BEGIN

DBMS_DST.upgrade_database(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

DBMS_DST.end_upgrade(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

Verify timezone file version.

SELECT * FROM v$timezone_file;

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A20

SELECT property_name, property_value

FROM database_properties

WHERE property_name LIKE 'DST_%'

ORDER BY property_name;

Execute post upgrade fixups once upgrade completes.

@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

Voting Disk

Voting Disk is a file resides on shared storage and manages cluster members. It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster.

The voting disk is used as a central reference for all nodes and keeps the heartbeat information between nodes. If any of node is unable to ping the voting disk, the cluster immediately recognizes the communication failure and evicts the node from cluster. Voting disk reassigns cluster ownership between the nodes in case of failure. Minimum 1 and maximum 15 copy of voting disk is possible.

It can be seen that number of voting disks whose failure can be tolerated is same for (2n-1) as well as 2n voting disks where n can be 1, 2 or 3. Hence to save a redundant voting disk, (2n-1) i.e. an odd number of voting disks are desirable.

# View voting disk location

crsctl query css votedisk

# Backup voting disk

The voting disk data is automatically backed up in OCR as part of any configuration change so you do not have to perform manual backups of the voting disk.

# Adding voting disk.

You cannot directly add voting disk from Oracle Database 11g Release 2 onwards. Instead we can add new diskgroup with desired redundancy and relocate it to new diskgroup. This will provide additional voting disk.

# Deleting voting disk

Addition and deletion of votedisk is not allowed on ASM. You can always create new diskgroup with different redundancy group to reduce number of voting disk.

Note:

There is 1 voting disk if DG with external redundancy

There are 3 voting disks if DG with normal redundancy

There are 5 voting disks if DG with high redundancy

# Relocate voting disk, or recover voting disk

crsctl replace votedisk

Modifying redundancy level of Diskgroup containing voting disk.

Let's say we have DG VDISK and it is configured with external redundancy.

If we want to increase level of redundancy to Normal or High then we need to go through following steps.

Create diskgroup with desired redundancy.
Add another disk to the diskgroup and mark it as quorum disk. The quorum disk is one small Disk (500 MB should be on the safe side here, since the Voting File is only about 280 MB in size) to keep one Mirror of the Voting File. In case of normal redundancy you need one quorum disk. Other two disks will contain each one Voting File and all the other stripes of the Database Area as well, but quorum will only get that one Voting File. For high redundancy you need two quorum disks. QUORUM disks can contain the voting file for Cluster Synchronization Services (CSS). REGULAR disks, or disks in non-quorum failure groups, can contain any files.
Now try to relocate the voting disk from exiting disk-group to newly created disk-group.

Checking current voting disk location

Checking available asm disks

Create asm disk group with desired redundancy.

Set diskgroup compatible.asm attribute to 11.2.0.0.0

Add quorum disk to disk group. We need to add 2 quorum disk for DG with high redundancy.

Validate asm diskgroup.

Relocate votedisk to newly created Diskgroup

Query and validate changes.

OCR (Oracle Cluster Registry)

OCR (Oracle Cluster Registry) – resides on shared storage and it is accessed by all nodes in the cluster. It maintains information about cluster configuration and information about cluster database.

OCR contains information like which database instances run on which nodes and which services runs on which database. OCR is created during the time of Grid Installation. It stores information to manage Oracle clusterware and it’s component such as RAC database, listener, VIP, Scan IP & Services. Minimum 1 and maximum 5 copy of OCR is possible.

# Check OCR file details

ocrcheck

OCR Backup:

Oracle automatically takes backup every 4 hrs on master node. You can also take backup using ocrconfig export utility.

Oracle11g R2 and higher releases simplified OCR and Voting file management by storing the OCR and Voting files in ASM (Automatic Storage Management). ASM automatically maintains the number of OCR/Voting disks based on the underlying Diskgroup redundancy further reducing manual DBA file management tasks. Additionally the Clusterware stack also initiated periodic automatic backups of these files.

To determine OCR file location

more /etc/oracle/ocr.loc

Adding new location

ocrconfig -add <DiskGroup>

Deleting location

ocrconfig -delete <DiskGroup>

View ocr backup location

ocrconfig -showbackup

Manually backup

ocrconfig -manualbackup

Dump backup of ocr file

ocrconfig -export ocr_backup_$(date +%Y_%m_%d).dmp

Restore OCR

ocrconfig -restore

Following are New Features from Oracle 11g R2 onward.

OCR And Voting disk can be stored on ASM or certified cluster file system.
Voting disk and OCR can be dynamically added or replaced.
Voting disk and OCR can be keep in same disk-group or different disk-group
Voting disk and OCR automatic backup kept together in a single file.
Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week
Administer access: root or sudo privilege are required for managing account.

Step by step restoring OCR and voting disk in case DG with Voting disks and OCR fails.

If there is no voting disk and or diskgroup containing Voting disk failed to mount due to insufficient disk members then the only way to recover OCR and voting disk is to create new DG and start recovery. Error message as below will be noticed on alert log file.

gpnpd(3183)]CRS-2328:GPNPD started on node rac01.

2019-08-25 12:45:39.548

[cssd(3253)]CRS-1713:CSSD daemon is started in clustered mode

2019-08-25 12:45:41.343

[ohasd(3025)]CRS-2767:Resource state recovery not attempted for 'ora.diskmon' as its target state is OFFLINE

2019-08-25 12:45:43.641

[cssd(3253)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /u01/app/11.2.0/grid/log/rac01/cssd/ocssd.log

Please following following steps to restore OCR and Voting disk.

1. Create new disk with desired redundancy. ASM attribute compatible.asm should be 11.2.0.0.0 or higher and there should be sufficient quorum failure groups as per redundancy level. For normal redundancy there should be 1 quorum failure group and 2 quorum failure groups are required for high redundancy.

2. Stop crs with -f force option.

crsctl stop crs -f

3. Start crs in exclusive mode without crs. Check crs status with -init option.

crsctl start crs -excl -nocrs

4. Check ocr location from ocr.loc file. This file contains the diskgroup where OCR file is resides. If cluster is already running we can use ocrconfig command to modify the location. Since cluster is offline this file need to modify manually. Replace newly created diskgroup.

cat /etc/oracle/ocr.loc

vi /etc/oracle/ocr.loc