Complete checklist for manual Container database upgrade from 12.1.0.1 to 12.1.0.N

Complete checklist for manual Container database upgrade from 12.1.0.1 to 12.1.0.N (Full CDB Upgrade) (Doc ID 1932762.1)

APPLIES TO:

Oracle Database - Standard Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

PURPOSE

This document is created for use as a guideline and checklist when upgrading 12c multitenant database to higher version, 12.1.0.2 or higher

SCOPE

Database Administrators, Support

DETAILS

Step 1: Recommendations for source PDB database

1) Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.

2) Disable the custom triggers that would fire before/after DDL and enable them after the upgrade is complete.

3) If APEX is enabled at your environment, upgrade it to 4.2.5. to reduce database upgrade time, APEX can be manually before starting DB upgrade.

MOS Note 1088970.1 for information on APEX installation upgrades.

4) Check the status of PDB. It should be in open with Read write mode

These commands will also make sure the Multitenant Option is enabled.

Example:

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

 NAME      Multitenant Option ?       OPEN_MODE                CON_ID

--------- -------------------------- -------------------- ----------

TESTCDB  Multitenant Option enabled READ WRITE                    0

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

     CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 TESTPDB                         READ WRITE

Step 2 Take backup of PDB database

It is always advisable to take backup of database before performing upgrade.

Start RMAN and connect as a common user with the SYSBACKUP or SYSDBA privilege.

Issue a BACKUP PLUGGABLE DATABASE command at the RMAN prompt.

rman nocatalog target /

BACKUP PLUGGABLE DATABASE  TAG pdb_backup;

Else you can take full CDB backup as below

BACKUP DATABASE TAG before_upgrade;

BACKUP CURRENT CONTROLFILE TO '';

Step 3 Check for the integrity of the database

Check for the integrity of the PDB database prior to starting the upgrade by downloading and running the dbupgdiag.sql script from the My Oracle Support article below:

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.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> alter session set container=

SQL> @utlrp.sql

If the Oracle owned objects cannot be validated then it is suggested to post a community thread or open an SR to investigate why the object/objects cannot be validated.

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

Ensure that you do not have duplicate objects in the SYS and SYSTEM schema

The following duplicate objects are permissible duplicate objects:

OBJECT_NAME OBJECT_TYPE

--------------------------- ------------------

AQ$_SCHEDULES TABLE

AQ$_SCHEDULES_PRIMARY INDEX

DBMS_REPCAT_AUTH PACKAGE

DBMS_REPCAT_AUTH PACKAGE BODY

Remove other duplicate objects at source database.

It is advisable to do a Health Check using "hcheck.sql" script .Please refer following article to download the script .

Note 136697.1 hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g

Step 4 Pre-Upgrade Steps

In this section all the steps need to be performed after having set the environment of the previous version of the Oracle Database.

Note that the database must be running in normal mode in the old version.

Step 4.1 Preupgrade script execution

To download and use the latest Pre-Upgrade Information Tool see the following:

How to Download and Run Oracle's Database Pre-Upgrade Utility Note 884522.1

Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

Log into the system as the owner of Target Oracle Database 12c Release 1 (12.1.0.2 or higher) Oracle Home directory.

Copy the Pre-Upgrade Information Tool (preupgrd.sql and utluppkg.sql) from the Oracle Database 12c Release 1 (12.1.0.2 or higher) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory on your system.

Check PDB status, it should be in open read write state before executing script.

Its status can be checked using

sql> show pdbs;

To make it read write

sql> alter pluggable database  open;

Preupgrade script can be executed at CDB & PDB level at once using below method

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl -d  -l  -c  -b  preupgrd.sql

Note 1932340.1 How to execute sql scripts in Multitenant environment (catcon.pl)

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

sql> /preupgrd.sql

sql> alter session set container=;

sql> /preupgrd.sql

Step 4.2 Collect Statistics

Oracle recommends collecting statistics the night before starting the upgrade to decrease the amount of downtime

To decrease the amount of downtime incurred when collecting statistics, Oracle recommends to collect statistics prior to performing the actual database upgrade.

For example, you can enter the following:

$ sqlplus "/as sysdba"

SQL> exec dbms_stats.gather_dictionary_stats;

Step 4.3 Verify That Materialized View Refreshes Have Completed Before Upgrading database

Verify That Materialized View Refreshes Have Completed Before Upgrading

Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Run the following query at all PDBs to determine if there are any materialized view refreshes still in progress:

SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

Step 4.4 Ensure That No Files Need Media Recovery Before Upgrading

Make ensure that there are no files requiring media recovery by executing the following query .

SQL> select * from v$recover_file;

Step 4.5 Ensure That No Files Are in Backup Mode Before Upgrading

Execute the following query to verify that No Files in backup mode when upgrading Oracle Database

SQL> select * from  v$backup WHERE status != 'NOT ACTIVE';

Step 4.6 Resolve Outstanding Distributed Transactions Before Upgrading

To resolve outstanding distributed transactions:

1. Issue the following statement:

SQL> Select * from dba_2pc_pending;

2. If the query in the previous step returns any rows, then issue the following

statements:

SQL> select  local_tran_id FROM dba_2pc_pending;

SQL> execute  dbms_transaction.purge_lost_db_entry('');

SQL> commit;

Step 4.7 Purge the Database Recycle Bin Before Upgrading

4.12 Purge the Database Recycle Bin Before Upgrading :

To empty the database recycle bin, issue the following command:

SQL> purge dba_recyclebin;

Step 4.8 Disable all batch and cron jobs.

For jobs initiated by Oracle then packages DBMS_JOB, DBMS_SCHEDULER can be used.

For cron jobs (external jobs controlled at the OS level) then this is a task for your Unix administrator.

Step 4.9 Verify SYS and SYSTEM Default tablespace.

Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

You must have sufficient space in the 'SYSTEM' tablespace or set extents to unlimited.

SQL> alter session set container=;

SQL> SELECT username, default_tablespace

     FROM dba_users

     WHERE username in ('SYS','SYSTEM');

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:

SQL> alter user SYS default tablespace SYSTEM;

SQL> alter user SYSTEM default tablespace SYSTEM;

Step 4.10 Review and Remove any unnecessary hidden/underscore parameters

Please review and remove any unnecessary hidden/underscore parameters prior to upgrading. It is strongly recommended that these be removed before upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the init.ora or spfile.To view existing hidden parameters execute the following command while connected AS SYSDBA

SQL> alter session set container=;

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Step 5 Requirements and recommendations for target environment

Check the certification of Oracle 12cR1 with your Platform/Operating system before downloading and installing Oracle 12cR1. Please check the certification information on My Oracle Support.
Ensure that newly installed Oracle Home doesn’t have relinking errors.
Install the latest available Patchset from Metalink (if available).
Install the latest opatch available for your platform and database version (if available).
Either take a Cold or Hot backup of your source database (advisable to have cold backup).

Step 6 Preparing new oracle home for upgrading

1) Copy configuration files from the Oracle home of the database being upgraded to the new Oracle home for Oracle Database 12c.

Oracle looks for the parameter file in the ORACLE_HOME/dbs directory on Linux or UNIX platforms and in the ORACLE_HOME\database directory on Windows operating systems.

If your parameter file is a text-based initialization parameter file with either an IFILE (include file) or a SPFILE (server parameter file) entry, and the file specified in the IFILE or SPFILE entry resides within the old environment's Oracle home, then copy the file specified by the IFILE or SPFILE entry to the new Oracle home. The file specified in the IFILE or SPFILE entry contains additional initialization parameters.

2) If you have a password file that resides within the old environment's Oracle home, then move or copy the password file to the new Oracle home in Oracle Database 12c.

The name and location of the password file are operating system-specific. On Linux or UNIX platforms, the default password file is orapwSID, located in the ORACLE_HOME/dbs directory. On Windows operating systems, the default password file is pwdSID.ora, located in the ORACLE_HOME\database directory. In both cases, SID is your Oracle instance ID.

3) Adjust your parameter file in Oracle Database 12c by completing the following steps:

Adjust the values of the initialization parameters to at least the minimum values indicated by the Pre-Upgrade Information Tool.
Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.
If you are upgrading a cluster database, then you may need to modify the SPFILE or initORACLE_SID.ora files.set the CLUSTER_DATABASE initialization parameter to false. After the upgrade, you must set this initialization parameter back to true.

Prerequisites for Preparing Oracle Home on Windows

For security reasons, different Windows user accounts configured as Oracle home users for different Oracle homes are not allowed to share the same Oracle Base.

Recommendations before upgrade Oracle Database on Windows platforms:

Database upgrade is supported when the same Windows user account is used as the Oracle home user in both the source and destination Oracle homes.

The Oracle home user may not have access to files outside its own Oracle Base and Oracle home. Therefore, if you choose a different Oracle Base during upgrade,

Oracle Database services may not have access to the files in the older Oracle Base.

Step 7 Upgrade steps

Shut down the database. (The syntax is the same for a non-CDB and a CDB.) and stop oracle executables

SQL> SHUTDOWN IMMEDIATE;

In case target database is at different server copy database file, control file and parameter file to target server.

For Windows create a new SID (This steps is specific to Windows)

If your operating system is Windows then complete the actions in this step, else skip to the next step. Set the environment to Source/Previous version (12.1)

Stop the OracleServiceSID Oracle service of the database you are upgrading, where SID is the instance name.

For example, if your SID is ORCL, then enter the following at a command prompt:

a). Stop the Oracle database service.

C:\> NET STOP OracleServiceORCL

b). Delete Oracle service using ORADIM binary from which the database is upgraded to 12.1.

C:\> ORADIM -DELETE -SID ORCL

c). Create the Oracle Database 12c Release 1 (12.1.0.2) service at a command prompt using the ORADIM command of the new Oracle Database release:

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT.ORA

For Instance,

C:\> ORADIM -NEW -SID ORCL -INTPWD -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT.ORA

Make sure the following environment variables point to the Oracle 12c Release 1 (12.1) directories

- ORACLE_BASE

- ORACLE_HOME

- PATH, LD_LIBRARY_PATH and SHLIB_PATH

$ export ORACLE_HOME=

$ export PATH=$ORACLE_HOME/bin:$PATH

$ export ORACLE_BASE=

Note : If ORACLE_BASE is not known after setting PATH to 12.1.0.2 Oracle Home, execute 'orabase' which will point the location of the base.

$ orabase

/uo1/app/oracle

Update the oratab entry to set the new ORACLE_HOME pointing to ORCL and disable automatic startup.

Sample : cat /etc/oratab

#orcl:/opt/oracle/product/12.1.0.1/db_1:N

orcl:/opt/oracle/product/12.1.0.2/db_1:N

Note: After /etc/oratab is updated to have SID and Oracle Home (12.1.0.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment.

The input has to be the SID which is entered in /etc/oratab against the 12cR1 home.

For example:

[oracle@localhost ~]$ . oraenv

ORACLE_SID = [orcl] ? orcl

The Oracle base for ORACLE_HOME=/opt/oracle/product/12.1/db_1 is /u01/app/oracle

Connect to the database to be upgraded using an account with DBA privileges:

SQL> CONNECT / AS SYSDBA

Start the instance by issuing the following command in SQL*Plus:

Startup CDB in ugprade mode

SQL> startup upgrade pfile=

Start all PDB in upgrade mode

SQL> alter pluggable database all open upgrade;

Exit SQL*Plus before proceeding to the next step.

SQL> exit;

To upgrade an entire CDB, Run the catctl.pl script from the new Oracle home as described in this step. The Parallel Upgrade Utility, catctl.pl, provides parallel upgrade options that reduce downtime.

To run catctl.pl on Linux:

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

To run catctl.pl on Windows:

cd %ORACLE_HOME%\rdbms\admin

%ORACLE_HOME%\perl\bin\perl catctl.pl catupgrd.sql

Logs location can be specified using -l option

Parallelism can be specified using –n option

Complete options are listed at Note : Oracle Database 12c Release 1 (12.1) Upgrade New Features (Doc ID 1515747.1)

catupgrade logs will be generated separately for Seed database and each PDB

Important: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process.

Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

Run catuppst.sql as follows:

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catuppst -d '''.''' catuppst.sql

Run catcon.pl to invoke utlrp.sql to recompile any remaining stored PL/SQL and Java code. Use the following syntax:

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

Run utlu121s.sql to verify that all issues have been fixed

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlu121s -d '''.''' utlu121s.sql

Shutdown and start the database in normal mode

SQL> shutdown immediate;

Step 8 Post-Upgrade steps

Step 8.1 Execute post-upgrade script

Followed by a startup all PDBs must be opened now for recompilation

SQL> startup pfile=;

SQL> alter pluggable database all open;

Execute the postupgrade_fixups.sql:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d  -l  -b postupgrade_fixups postupgrade_fixups.sql

Script will be executed at CDB and all PDBs

Step 8.2 Environment Variables and oratab file

Make sure the following environment variables point to the Oracle 12c Release 1 (12.1.0.2) directories

- ORACLE_BASE

- ORACLE_HOME

- PATH, LD_LIBRARY_PATH and SHLIB_PATH

Ensure that your oratab file and any client scripts that set the value of ORACLE_HOME point to the new Oracle home that is created for the new Oracle Database 12c release (12.1.0.2)

Step 8.3 Initialization parameter file

Edit init.ora

If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE

Migrate your initialization parameter file to a server parameter file.

Create a server parameter file with an initialization parameter file.

SQL> create spfile from pfile;

This will create an spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).

Step 8.4 Upgrade recovery catalog after upgrading database

You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command

Please refer Upgrading the Recovery Catalog for complete information and steps

Step 8.5 Upgrade timezone after database upgrade

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade(i.e, 12.1.0.1 database time zone version was not upgraded 18), then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.

(Note 1585343.1 : Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . )

Step 8.6 Statistics tables created by the DBMS_STATS package

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');

In the example, 'SYS' is the owner of the statistics table and 'dictstattab' is the name of the statistics table.

Step 8.7 Identify Invalid objects with the utluiobj script

Execute this script at upgraded PDB.

Before the upgrade, the list of invalid SYS/SYSTEM objects is written to registry$sys_inv_objs and non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs by the Pre-Upgrade Information Tool

After the upgrade, run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.Execute utlrp.sql to revalidate invalid objects.

Known Issue

Once database is upgraded to 12c, it will be a non-multitenant database. In case you want to convert this upgraded database to Pluggable database(PDB) and plugin into existing 12c Container database(CDB), check for characterset of CDB and PDB

In Oracle Database 12c with multitenant architecture, all pluggable databases (PDBs) in a container database (CDB) must have the same Database character set (NLS_CHARACTERSET) or the NLS_CHARACTERSET need to be a (Plug-in compatible) binary subset of the CDB NLS_CHARACTERSET the same National character set (NLS_NCHAR_CHARACTERSET) as the CDB's root container

If you have PDBs with Unicode characterset, its recommended to create the CDB with characterset as AL32UTF8. Please note we cannot migrate the CDB's characterset using DMU.

Refer:12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set

( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)

Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET ) (Doc ID 225912.1) [Section E]

======================================================================

Oracle DBA Information at one place

Search This Blog