Wikipedia

Search results

MMON Reports ORA-600 [6810] In Alert Log File

ORA-00600: internal error code, arguments: [6810], [162640], [168472]

Call Stack:

kdngnl kdnini2 keltinitseq keltgseq keltfill keltpost kelr_issue_alert 382 kelr_monitor_metric ksbcti ksbabs kebm_mmon_main


This issue is reported during MMON's alerting mechanism. Could not identify any known issue at this stage.

Bug 5088977 is related,but doesn't seems this is the cause.
Details:

MMON may dump in keltfill.

The workaround mentioned in the bug is helping.

Solution :

MMON is dumping the trace and objects accessed are related to MMON's alerting mechanism.

SYS.WRI$_ALERT_SEQUENCE
WRI$_ALERT_OUTSTANDING
WRI$_ALERT_THRESHOLD

These all are related to alerting mechanism and call stack also further confirm the same.

Workaround is to disable the MMON alerting functionality by setting the following:

If using init.ora:
_threshold_alerts_enable=0

If using spfile:
SQL> alter system set "_threshold_alerts_enable"=0 scope=spfile sid='*';

and restart the instance.

This disables alerting mechanism.
This should not have any impact to other normal functionality of the database.

Oracle release Update and Release Update Revisions for Database Proactive Patch Program

Beginning in the second half of calendar year 2017, Oracle will be transitioning to a more flexible and responsive strategy for the database software release process.  These changes only affect Database and Grid Infrastructure release 12.2 or later. 



Overview

Beginning in the second half of calendar year 2017, Oracle will be transitioning to a more flexible and responsive strategy for the database software release process.  These changes only affect Database and Grid Infrastructure release 12.2 or later.  The primary goals of this strategy are twofold:
1. Embrace a less complex software release process
a. Oracle wants to get new features to market every year instead of waiting several years
b. Oracle desires to improve database release quality by reducing the number of software changes released at one time
2. Provide customers with a more flexible way to both:
a. Efficiently adopt Bug fixes when needed (like the DB Proactive BP currently provides)
b. Efficiently keep current with Quarterly Security Updates once their environment becomes stable (like the PSU currently provides)
To accomplish the goals of this strategy, the following database software changes are being implemented: 

Please note that the changes described in this NOTE are only for Database and Grid Infrastructure release 12.2 and beyond.  Database version 12.1 and 11.2 will continue to use the legacy PSU/BP process and version numbering systems.




Patching Changes

Beginning with the next Database release (originally designated 12.2.0.2) planned for 2018, new feature releases of the database product will be provided annually, and patchsets will no longer be released.  To support both security-related fixes and high-priority non-security fixes to each annual release, quarterly Release Updates (RUs) will be provided each January, April, July and October. To allow customers to keep current on just security-related fixes once their feature environment becomes stable, quarterly Release Updates Revisions (RURs) will be provided each January, April, July and October for the RUs from the past 6 months.  This RU and RUR strategy replaces the legacy Patchset Update (PSU) strategy for 12.2.0.1 database software, beginning in July of 2017.
• RUs are proactive, highly tested bundles of critical fixes which enable customers to avoid known issues. They are specific to a particular annual release.
• RURs contain security and regression fixes to a RU that extend the RU’s lifetime up to two quarters. They are specific to a particular RU.
• The legacy terms ‘Patchset’, 'Patchset Update', and 'Database Bundle Patch' will no longer be meaningful for 12.2 database software.
This strategy allows a more conservative approach to maintenance while still getting fast access to critical security and proactive stabilization content. Customers can switch back and forth between RUs and RURs, whereas this is not possible with PSUs and BPs. Both RUs and RURs contain all security fixes, thus eliminating any 'security' versus 'stability' tradeoff.

Figure 1: 12.2.0.1 Database Release - Naming convention for RU/RUR
  • Release Update (RU) - Database Release Update 12.2.0.1.<build-date>
  • Release Update Revision (RUR) - Database <Quarter> Release Update Revision 12.2.0.1.<build-date>




Lifetime Support Policy for Annual Database Releases

Most annual releases will be supported for 3 years. Periodically, a release will be designated as an "Extended Support Release" and be supported for 8 years. The support life of each release will be documented in the Lifetime Support Policy document.




Release Version Numbering Changes

Beginning in 2018, a new numbering schema for the database software will be implemented. Instead of a legacy nomenclature such as 12.2.0.2, a three (3) field format consisting of: Year.Update.Revision will be used, such as 18.1.0. This will allow clear indication of:
• the annual release designation of the database software
• which quarterly RU (the second field) applies to which annual release.
• which quarterly RUR (the third field) extends which quarterly RU.

Disclaimer:

The dates published in this document are for planning and discussion purposes only. It is intended solely to help you plan your I.T. projects. The dates are not a firm development plan. The release and timing of any platforms are subject to change at any time and at Oracle's sole discretion.

Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Software License and Service Agreement, which has been executed and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced or distributed to anyone outside of the licensed organization without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.


Figure 2: Release and RU Timeline



Please note that the changes described in this NOTE are only for Database and Grid Infrastructure release 12.2 and beyond.  Database version 12.1 and 11.2 will continue to use the legacy PSU/BP process and version numbering systems.


Reference  - Release Update Introduction and FAQ (Doc ID 2285040.1)

Release Roadmap

To help you plan ahead, below is a roadmap of patch sets for Oracle Database major releases 11.1 and beyond, showing planned release dates and the duration of their support lives in relationship to the overall release life. The chart is by nature somewhat simplified so be sure to read the details below it to help you interpret it correctly.
Information is shown for current major releases since 11.2. Patchsets and releases come out on different platforms over time. Once released on the first platforms (usually Linux), the schedule for release on other platforms will be added to the table above.
NOTE: Some users may not be able to see the roadmap image below. Click here to download the image.


Chart showing roadmap for current and future DB release
Fig 1 - Database Release Roadmap
Release 12.2: New releases will be annual and the version will be the last two digits of the release year. The release originally planned as 12.2.0.2 will now be release 18, and the release originally planned as 12.2.0.3 will be release 19. Releases 18 and 19 will be treated as under the umbrella of 12.2 for Lifetime Support purposes. The current plan is for Oracle Database 19 to be the last release for 12.2. This may change in the future to Oracle 20 as the last release for 12.2.  

Requirements for Installing Oracle Database 12.2 on OL6 or RHEL6 64-bit (x86-64)

This note explains the requirements that need to be met for a successful installation of Oracle Database 12.2 release on Oracle Linux 6 (or higher 6.x version) or Red Hat Enterprise Linux 6.0 64-bit (x86-64).

It is NOT the purpose of this NOTE to repeat every "how-to" step that is presented in the Oracle Database 12.2 Installation Guide. For example this NOTE does not include how to create the Linux OS account named "oracle", nor does it cover how to set environment variables. Both are adequately covered in "Oracle® Database Installation Guide 12c Release 2 (12.2) for Linux"

SCOPE :


This procedure is meant for those Planning / Installing Oracle Database 12.2 on OL 6.0 on the 64-bit (x86-64) platform. Since it is the expressed goal to keep Oracle Linux (OL) functionally IDENTICAL to RHEL, this NOTE is also completely applicable to 64-bit (x86-64) RHEL 6.0


This procedure is not meant for those Planning / Installing Grid Infrastructure (GI) or any other Oracle products.


DETAILS:



Requirements for installing Oracle Database 12.2 release 64-bit on OL6 or RHEL6 64-bit (x86_64)
Before you proceed with Installation, please take time to review every below requirement carefully to avoid any obvious issues during Installation of binaries.

1. Download the Oracle Database 12.2 Software

- Download the Oracle Database 12.2 software from My Oracle Support
Note 1194734.1 Where do I find that on My Oracle Support (MOS) [Video]

- After you download the Software, Verify the integrity of the Downloaded Software
Note 549617.1 How To Verify The Integrity Of A Patch/Software Download? [Video]
- If you have downloaded the Software on other Machine, please transfer the zip file on the Server being Installed using Binary Mode and unzip with the Oracle Software user.

I. Hardware:

  1. Minimum Hardware Requirements
      a.) Ensure that your system meets the following Physical Memory requirements:
          Minimum: 1 GB of RAM
          Recommended: 2 GB of RAM or more

      b.) Swap disk space proportional to the system's physical memory as follows:
RAMSwap Space
Between 1 GB and 2 GB1.5 times the size of RAM
Between 2 GB and 16 GBEqual to the size of RAM
More than 16 GB16 GB
     NOTE: The above recommendations (from the Oracle® Database Installation Guide 12c Release 2 (12.2) for Linux) are MINIMUM recommendations for installations. Further RAM and swap space may be required to tune/improve RDBMS performance.

     c.) 1.0 GB (1024MB) of disk space (and less than 2TB of disk space) in the /tmp directory.
         If the free space available in the /tmp directory is less than what is required, then complete one of the following steps:
         - Delete unnecessary files from the /tmp directory to meet the disk space requirement.
         - Set the TMP and TMPDIR environment variables when setting the oracle user's environment.
     d.) Approximately 6.5 GB of local disk space for the Database Software Files.
     e.) If you intend to create a preconfigured database during the installation, then the file system (or file systems) that you choose must have at least 2 GB of free disk space.
         For production databases, you must estimate the disk space requirement depending on the use of the database.

  2. Oracle Database is supported on ext2, ext3 and ext4 file systems (see Note:236826.1 for further information)

II. Software:

   1. Certified Linux Operating Systems
       Oracle Linux 6
       Red Hat Enterprise Linux 6
   2. Oracle recommends that you install the Linux operating system with the default software packages (RPMs) and do not customize the RPMs during installation. For additional  information on "default-RPMs", please see Note 376183.1, "Defining a "default RPMs" installation of the RHEL OS" or Note 401167.1, "Defining a "default RPMs" installation of the Oracle Enterprise Linux (OEL) OS".
   3. Linux Kernel Requirements
Oracle Linux 6.4 with the Unbreakable Enterprise kernel 2: 2.6.39-400.211.1.el6uek.x86_64or later
Oracle Linux 6.4 with the Red Hat Compatible kernel: 2.6.32-358.el6.x86_64 or later
Red Hat Enterprise Linux 6.4: 2.6.32-358.el6.x86_64 or later
Note 1508516.1 Is It Mandatory To Install UEK Kernel Under RHEL?
   4. Required OS Components (per Release Notes, and Install Guide)
a.) The exact version number details of this list are based upon 64-bit (x86_64) RHEL 6.4. When a higher "update" level is used, the RPM release numbers may be slightly different. Since updates of RHEL 6 are certified, this is fine so long as you are still using 64-bit Linux (x86_64) RHEL 6 RPMs.    
b.) Some of the Install Guide requirements will already be present from the "default-RPMs" foundation of Linux that you started with:
  1. binutils-2.20.51.0.2-5.36.el6 (x86_64)
  2. glibc-2.12-1.107.el6 (x86_64)
  3. libgcc-4.4.7-3.el6 (x86_64)
  4. libstdc++-4.4.7-3.el6 (x86_64)
  5. libaio-0.3.107-10.el6 (x86_64)
  6. libXtst-1.2.1-2.el6 (x86_64)
  7. libX11-1.5.0-4.el6 (x86_64)
  8. libXau-1.0.6-4.el6 (x86_64)
  9. libxcb-1.8.1-1.el6 (x86_64)
  10. libXi-1.3 (x86_64)
  11. make-3.81-20.el6 (x86_64)
  12. sysstat-9.0.4-20.el6 (x86_64)
 c.) The remaining Install Guide requirements will have to be installed:
  1. compat-libcap1-1.10-1 (x86_64)
  2. compat-libstdc++-33-3.2.3-69.el6 (x86_64)
  3. glibc-devel-2.12-1.107.el6 (x86_64)
  4. ksh  <== any version of ksh is acceptable
  5. libstdc++-devel-4.4.7-3.el6 (x86_64)
  6. libaio-devel-0.3.107-10.el6 (x86_64)
d.) Customers wishing to install the Oracle Database 12.2 32-bit Client software should also install these packages:
  1. compat-libstdc++-33-3.2.3-69.el6 (i686)
  2. glibc-2.12-1.107.el6 (i686)
  3. glibc-devel-2.12-1.107.el6 (i686)
  4. libgcc-4.4.7-3.el6 (i686)
  5. libstdc++-4.4.7-3.el6 (i686)
  6. libstdc++-devel-4.4.7-3.el6 (i686)
  7. libaio-0.3.107-10.el6 (i686)
  8. libaio-devel-0.3.107-10.el6 (i686)
  9. libXtst-1.2.1-2.el6 (i686)
  10. libX11-1.5.0-4.el6 (i686)
  11. libXau-1.0.6-4.el6 (i686)
  12. libxcb-1.8.1-1.el6 (i686)
  13. libXi-1.6.1-3.el6 (i686)
   5. Additional Required OS Components (per the runInstaller OUI)
       a.) intentionally blank
   6. Additional Required OS Components (per this NOTE)
a.) Please do not rush, skip, or minimize this critical step. This list is based upon a "default-RPMs" installation of 64-bit (x86_64) RHEL Server 6. Additional RPMs (beyond anything known to Oracle) may be needed if a "less-than-default-RPMs" installation of 64-bit (x86_64) RHEL Server 6 is performed. For more information, please refer to Note 376183.1, "Defining a "default RPMs" installation of the RHEL OS"  
 b.) Several RPMs will be required as prerequisites to those listed in section II.3.c:
            cloog-ppl.x86_64 0:0.15.7-1.2.el6
            cpp.x86_64 0:4.4.6-4.el6
            glibc-headers.x86_64 0:2.12-1.80.el6
            kernel-headers.x86_64 0:2.6.32-279.el6
            mpfr.x86_64 0:2.4.1-6.el6
            ppl.x86_64 0:0.10.2-11.el6
            redhat-release-6Server-1.noarch (only for RHEL)
  7. Oracle Global Customer Support has noticed a recent trend with install problems that originates from installing too many RPMs. For example:      
a.) Installing your own JDK version (prior to execute the Oracle Software runInstaller) is not needed on Linux, and is not recommended on Linux. A pre-existing JDK often interferes with the correct JDK that the Linux Oracle Software runInstaller will place and use.      
Oracle Database 12c Release 2 (12.2) and later does not require the compiler packages gcc and gcc-c++ on Oracle Linux and Red Hat Enterprise Linux for Oracle Database installations.
   8. All of the RPMs in section II. are on the Red Hat Enterprise Linux 6 64-bit (x86_64) distribution media.

III. Environment:

   1. Below are the Minimum Recommended Kernel Parameter settings required for Database Software Installation. Modify your kernel settings in /etc/sysctl.conf as follows.
NOTE: If the current value for any parameter is higher than the value listed, do not change the value of that parameter.
kernel.shmall = 1/2 of physical memory in pages, this will be the value 2097152. See Note 301830.1 for more information.
kernel.shmmax = 1/2 of physical memory in bytes. This would be the value 2147483648 for a system with 4GB of physical RAM.

For 32-bit Linux Systems :
    Minimum: 536870912 (512 MB)
    Maximum: A value that is 1 byte less than 4 GB, or 4294967295
    Recommended: More than half the physical memory

For 64-bit Linux Systems :
    Minimum: 536870912 (512 MB)
    Maximum: A value that is 1 byte less than the physical memory
    Recommended: More than half the physical memory

See My Oracle Support Note 567506.1 for additional information about configuring shmmax.

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576

Note: This value limits concurrent outstanding requests and should be set to avoid I/O subsystem failures.

net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

Note: The below Kernel Parameter "panic_on_oops=1" is being Introduced and required from 12.1.0.2.0 onwards.

kernel.panic_on_oops=1
   2. To activate these new settings into the running kernel space, run the "sysctl -p" command as root.

   3. Set Shell Limits for the oracle User. Assuming that the "oracle" Unix user will perform the installation, do the following:
a.) Add the following settings to /etc/security/limits.conf configuration file for the installation owner.
oracle              soft    nproc    2047
oracle              hard   nproc   16384
oracle              soft    nofile    1024
oracle              hard   nofile    65536
oracle              soft    stack    10240
oracle              hard   stack    10240
NOTE: When the limits.conf file is changed, these changes take effect immediately. However, if the oracle users are logged in, then these changes do not take effect until you log these users out and log them back in. You must do this before you use these accounts for installation.
b.) Verify the latest version of PAM is loaded, then add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required pam_limits.so

   4. The hostname command should return the fully qualified hostname as shown below:
% hostname
hostname.domainname
   5. If any Java packages are installed on the system, unset the Java environment variables, for example JAVA_HOME.
   6. The oracle account that is used to install Oracle Database 12.2, should not have the Oracle install related variables set by default. For example setting ORACLE_HOME, PATH, LD_LIBRARY_PATH to include Oracle binaries in .profile, .login file and /etc/profile.d should be completely avoided.
a.) Setting $ORACLE_BASE (not $ORACLE_HOME) is recommended, since it eases a few prompts in the OUI runInstaller tool.
b.) Following the successful install, it is recommended to set $ORACLE_HOME, and to set $PATH to include $ORACLE_HOME/bin at the beginning of the $PATH string.
   7. By default, RHEL 6 x86_64 Linux is installed with SELinux as "enforcing". This is fine for the Oracle Database 12.2 installation process.

   8. By default, your operating system includes an entry in /etc/fstab to mount /dev/shm. However, if your Cluster Verification Utility (CVU) or Oracle Universal Installer (OUI) checks fail, ensure that the /dev/shm mount area is of type tmpfs and is mounted with the following options:
       a.) rw and exec permissions set on it
       b.) Without noexec or nosuid set on it
   9. Log in as Oracle user and start the installation as follows:
./runInstaller
a.) It is best practice not to use any form of "su" to start the runInstaller, in order to avoid potential display-related problems.
b.) When performing the Oracle Database 12.2 installation, make sure to use the "runInstaller" version that comes with Oracle Database 12.2 software.
c.) When performing any subsequent 12.2.0.x patchset, make sure to use the "runInstaller" version that comes with the patchset.

ADDITIONAL NOTES

1. Starting from Oracle Database 12.1 32-bit Oracle Database Software is not available.
2. Supported distributions of the 32-bit (x86) Oracle Database Client Linux OS can run on on AMD64/EM64T and Intel Processor Chips that adhere to the x86_64 architecture. Oracle 32-bit Database Client running on AMD64/EM64T with 64-bit OS is expected to be supported, but is NOT covered by this NOTE.
3. Asynchronous I/O on ext2 and ext3 file systems is supported if your scsi/fc driver supports that functionality.
Note : Asynchronous I/O on Ext4 file system is supported with Oracle 10g onwards on OEL5.6 and later.
Reference : Oracle Linux, Filesystem & I/O Type Supportability (Note 279069.1)
4. No extra patch is required for the DIRECTIO support for x86_64.

5. No LD_ASSUME_KERNEL value should be used with the Oracle Database 12.2 product.

6. The following rpm command can be used to distinguish between a 32-bit or 64-bit package.
# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep glibc-devel
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (i686)

7. Pre-requisite RPM’s related to libXi* required inorder to resolve java dependency for OUI and avoid the known issue mentioned in
Note 1569369.1 libXi.so.6: cannot open shared object file: No such file or directory

Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2)

This post uses as guideline and checklist when manually upgrading from Oracle 10gR2 (10.2) , Oracle 11gR1 (11.1) or Oracle 11gR2 (11.2) or Oracle 12c Release 1 (12.1) to Oracle 12c Release 2 (12.2)

Step 1: Upgrade Path for 12.2 Oracle database

Minimum version of the database that can be directly upgraded to Oracle 12c Release 1 (12.2)


Source DatabaseTarget Database
11.2.0.3 / 11.2.0.412.2.x
12.1.0.1 / 12.1.0.212.2.x

Intermediate upgrades needs to be carried for following releases

Source Database Intermediate upgrade path Target database
11.2.0.1 / 11.2.0.2-->11.2.0.4-->12.2.x
11.1.0.6 / 11.1.0.7-->11.2.0.4-->12.2.x
10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5-->11.2.0.4 / 12.1.0.2-->12.2.x
10.1.0.5-->11.2.0.4 / 12.1.0.2-->12.2.x
9.2.0.8-->11.2.0.3 / 11.2.0.4-->12.2.x
For example
  • If you are upgrading from release 11.2.0.2 or 11.1.0.7, then you must first upgrade to Oracle Database 11g release 2 (11.2.0.4).
  • If you are upgrading from release 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 or 10.1.0.5, then you must first upgrade to release 11.2.0.4 or 12.1.0.2.
  • If you are upgrading from release 9.2.0.8, then you must first upgrade to a sequence of intermediate Oracle Database releases:
Upgrade from release 9.2.0.8 to release 11.2.0.3 or 11.2.0.4. Then upgrade from release 11.2.0.4 to 12.2

Step 2: Requirements and recommendations for source database

  • Take a cold or hot back up of the source database.
  • Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
  • Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 12c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 12c to define the data security roles again.
  • If you upgrade an 11g database to Oracle Database 12c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 12c database.
  • Timezone should less than or equal to target database timezone version.
  • IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB

Step3: Requirements and recommendations for target ORACLE_HOME

  • Verify the whether your operating system is certified for 12.2.  Click here to launch certification portal
  • Install 12.2.0.1, verify there are no installation related issues.
  • Download and install latest PSU if any
  • Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
  • Remove any _parameter, obsolete and deprecated parameters in pfile
  • Note min value of COMPATIBLE parameter to upgrade 12.2 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
  • Review patch recommendations as given in the article "Patches to apply before upgrading Oracle GI and DB to 12.2.0.1 (Doc ID 2180188.1)"

Step 4: Check the health of the source database

  • Execute dbupgdiag.sql (refer note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support.
  • Execute utlrp.sql multiple times and verify there are no INVALID objects.

Step 5: Pre-upgrade checks

Clean up database

Empty the recycle bin
Check for INVALID objects in SYS and SYSTEM
Check for duplicate objects in SYS and SYSTEM
Check for INVALID, mandatory, obsolete components

Check materialized views

Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views.
Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

1. Run the following SQL query:
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;  

Performance

Preserve performance statistics
Check network performance
Gather Optimizer statistics
To decrease the amount of downtime, gather statistics. Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Checking Time zone settings

Default time zone for Oracle database 12.2 is V26
Time zone should less than or equal to target database time zone version. If source is having higher time zone, then apply time zone patch on target ORACLE_HOME to match the source.

Back up the database

Back up the database, create a guaranteed flashback restore point, or both.
Test your fallback strategies at least once before your upgrade window.
Ensure that you have fallback strategies for issues both during upgrade, and after upgrade.
Connect to RMAN
rman "target / nocatalog"

Execute RMAN command to backup

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
}

Ensure no files are in Back up mode before starting the upgrade

Run the following statement:
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';  

Purge Recycle bin

To empty the database recycle bin, run the following command:
SQL> PURGE DBA_RECYCLEBIN
Note: The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

Save Oracle EM DB Control Configuration and Data

If you plan to downgrade database after upgrading to 12.2, then before starting the upgrade save the DB Control configuration and data using emdwgrd utility, so that you can restore the files after downgrading.
Steps to save data
1. Install the software for the new Oracle Database 12c release.
2. Set ORACLE_HOME to your old Oracle home.
3. Set ORACLE_SID to the SID of the database being upgraded.
4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home.
5. Change directory to the new Oracle Database release Oracle home.
6. Run emdwgrd
Single-instance databases:
emdwgrd[sh|bat] -save -sid old_SID -path save_directory
Oracle Real Application Clusters (Oracle RAC) databases:
Remote copy must be enabled across all cluster member nodes. Use EM_REMCP environment variable, to indicate which remote copy is configured, for example: export EM_REMCP /usr/bin/scp
emdwgrd -save -cluster -sid old_SID -path save_directory
7. Enter the SYS password for the database that you want to upgrade.

Manually remove DB control with emremove.sql

Stop/shutdown DB control
emctl stop dbconsole
  
Login as sysdba
SQL>SET ECHO ON
SQL>SET SERVEROUTPUT ON
SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin
Manually remove ORACLE_HOME/HOSTNAME_SID/ and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directory from the system
On windows delete DB Console service OracleDBConsoleSID

Drop JSON-Enabled Context search Indexes

If you created a JSON search index using Oracle Database 12c Release 1 (12.1.0.2) then Oracle recommends that you drop that index and create a new search index for use with later releases.

Check the accounts use Case-Insensitive password version

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.

Remove Unified Auditing Schema and Roles

Note: If you have created AUDIT_ADMIN and AUDIT_VIEWER users or roles with Oracle Database 12c release 1 (12.1) or if the database was created in 12.1, then you do not drop these roles and AUDSYS user.
Log into SQL*Plus as SYS with SYSDBA privilege.  Drop AUDSYS schema if exists. Start DB in migrate mode and drop AUDSYS user
SQL> startup migrate pfile=$T_WORK/t_init1.ora
ORACLE instance started.
SQL> drop user audsys cascade;

Drop AUDIT_ADMIN and AUDIT_VIEWER roles
DROP ROLE AUDIT_ADMIN;
DROP ROLE AUDIT_VIEWER;

Put Schema based tablespaces offline during upgrade

Note down the table space names which needs to be offline during upgrade.  Use -T option followed by table space name
dbupgrade –T 
Starting with Oracle Database 12c release 2, you can run the Parallel Upgrade Utility with the -T option to have schema-based tablespaces (user tablespaces) taken offline during the upgrade. Taking these tablespaces offline can reduce the necessity of backing up before upgrades. The Parallel Upgrade Utility (catctl.pl) analyzes tablespaces, and automatically selects the right set of tablespaces to set to read only.  The utility does not set to READ ONLY any tablespaces that contain Oracle-Maintained objects

Preserve Downgrade capability

If you have any plans to downgrade the database to its previous version, then you must have patch 20898997 installed in source ORACLE_HOME, else downgrade will not be possible.
On source ORACLE_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i "20898997"
  
If patch is not installed, download patch 20898997 from MOS and install

Audit table preupgrade requirements


If upgrading from pre 12.1 release and using Oracle database Vault, Oracle Label Security, then you must first run olspreupgrade.sql.  Copy $ORACLE_HOME/rdbms/admin/olspreupgrade.sql from target home (12.2).  Connect as DVOWNER to the source db
SQL> GRANT DV_PATCH_ADMIN to SYS;
Grant DV_PATCH_ADMIN to SYS.  Connect SYS as SYSDBA
CONNECT SYS AS SYSDBA
Execute olspreupgrade.sql
SQL>ORACLE_HOME/rdbms/admin/olspreupgrade.sql
After completion, connect as DVOWNER and revoke DV_PATCH_ADMIN to SYS
SQL> REVOKE DV_PATCH_ADMIN from SYS;

Cluster DB Requirements

Upgrade GI first before upgrading the database.  If RAC, then database set CLUSTER_DATABASE to false in the init parameter file.

Other Checks

Ensure you have enough space in ARCHIVE_LOG and FLASHBACK location.

Step 6: Preupgrade step

Execute Preupgrade script from source home
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir
FILE - Use this option to direct output to a file
TEXT - Use this option to specify log should be in Text format (other option is to have XML output)
DIR - Logs will be created under <output_dir>
It is recommended to execute pre-upgrade fixup script, if any, which are AUTO FIXABLE

Dependencies on Network Utility Packages

Execute the following query
SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment.  After the upgrade, grant specific required privileges. Access is based on the usage in the original database.

Check Time zone version

Check if target database's time zone version is lower than the source database time zone version.  If yes, before starting upgrade time zone should be upgrade without fail.  RDBMS DST patches are available in Note 412160.1

Step7: Upgrade Database to 12.2

Set the environment variables to point to target ORACLE_HOME
export ORACLE_HOME=<path to Oracle 12.2>
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=<path to Oracle_Base set during installation>
  
Start DB in upgrade mode from target ORACLE_HOME
CONNECT / AS SYSDBA
SQL> startup upgrade;
SQL> exit
On Linux/Unix
cd $ORACLE_HOME/bin
./dbupgrade  
On Windows
cd %ORACLE_HOME%\bin
dbupgrade
Execute Post-Upgrade Status Tool, utlu122s.sql and review the upgrade spool log file.  You run the Post-Upgrade Status Tool in the environment of the new release.
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu122s.sql
Verify the upgrade log whether catuppst.sql has been executed or not.  If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory
SQL> @catuppst.sql  
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
Check the state of the Oracle Data Dictionary for diagnosing upgrades and migrations.  The dbupgdiag.sql script collects upgrade and migration diagnostic information about the current state of the data dictionary.  You can run the script in SQL*Plus on the upgraded database as the SYS user.  Refer Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.  After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database.  Run srvctl for Oracle Database 12c to upgrade the database. For example:
ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME

Step 8: Post-upgrade 

Setting Environment variables on Linux and Unix

Confirm that the following environment variables point to the directories of the new Oracle home:
ORACLE_HOME
PATH

Update oratab entries

Modify the corresponding entry in the /etc/oratab file to point to the new ORACLE_HOME location.

Post-upgrade fixup script

Execute post-upgrade fixup scripts generated by the pre-upgrade script.

Upgrading Tables Dependent on Oracle-Maintained Types

Starting with Oracle Database 12c release 2 (12.2), you must manually upgrade user tables that depend on Oracle-Maintained types.
To identify tables that need to be upgraded after the database upgrade, connect AS SYSDBA and run the following query:
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;
Execute utluptabdata.sql script either with a user account with the privileges to ALTER all of the tables dependent on Oracle-Maintained types, or with a user granted the SYSDBA system privileges that is logged in AS SYSDBA.
SET SERVEROUTPUT ON
@utluptabdata.sql

Enabling the New Extended Data Type Capability

Enabling a system to take advantage of the new extended data types requires specific upgrade actions.
Oracle Database 12c introduces MAX_STRING_SIZE to control the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. Setting MAX_STRING_SIZE = EXTENDED enables the 32767 byte limit introduced in Oracle Database 12c.
You must set the COMPATIBLE initialization parameter to 12.0.0.0 or higher to be able to set MAX_STRING_SIZE = EXTENDED.

Recovery Catalog Upgrade

If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command
Please refer to Oracle documentation under "Upgrading the Recovery Catalog" for complete information and steps

Upgrade the Time Zone File Version After Upgrading Oracle Database

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade,
then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.
Follow the procedure in Oracle documentation under "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" and Note 1509653.1 "Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST"

Upgrading Statistics Tables

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running DBMS_STATS.UPGRADE_STAT_TABLE.  In the following example, SYS is the owner of the statistics table and 'dictstattab' is the name of the statistics table.
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS', 'dictstattab');
Perform this procedure for each statistics table.

Upgrade Externally Authenticated SSL Users

If you are upgrading from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), and you are using externally authenticated SSL users, then you must run the SSL external users conversion (extusrupgrade) script to upgrade those users.
ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring hostname:port_no:sid --dbuser db_admin --dbuserpassword password -a

Install Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database

The Oracle Text-supplied knowledge bases are part of the companion products for Oracle Database 12c and are not immediately available after an upgrade to Oracle Database 12c. Any Oracle Text features dependent on the supplied knowledge bases which were available before the upgrade do not function after the upgrade. To re-enable such features, you must install the Oracle Text supplied knowledge bases from the installation media.

Update Oracle Application Express Configuration After Upgrading Oracle Database

If the Oracle Database release that you upgrade includes Oracle Application Express release 3.2 or later, then you do not need to carry out additional configuration after upgrading to Oracle Database 12c. However, if Oracle Application Express is in the registry, so that Oracle Application Express is included in the upgrade, then set the open_cursors parameter to a minimum of 200.

Configure Access Control Lists (ACLs) to External Network Services

if you have applications that use UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages, then after upgrading Oracle Database you must configure network access control lists (ACLs) in the database before these packages can work as they did in earlier releases. Without the ACLs, your applications may fail with the error "ORA-24247: network access denied by access control list (ACL)."

Enabling Oracle Database Vault After Upgrading Oracle Database

Refer to the following documents for enabling Oracle Database Vault:
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS

Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior

Connections to Oracle Database from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol. Refer to Oracle documentation for fixing the ORA-28040: No matching authentication protocol.

Known issues


For known issues refer to Oracle Database Upgrade Known issues - 12.2 (Doc ID 2243613.1)