DBA-Technology for us: Part 3: Grid Infrastructure and Database Upgrade from 11gR2 11.2.0.3 to 12C 12.1.0.2

Upgrade Database to 12.1.0.2

Three things need to do:

1. Upgrade Grid Infra from 11gR2(11.2.0.3) to 12C(12.1.0.2)
2. Install Database Software12C(12.1.0.2)
3. Upgrade DB from 11gR2 to 12C

Check Invalid Object:

set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

To VALIDATE the invalid objects:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

Ref : Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)

Collect DB Upgrade/Migrate Diagnostic Information:

cd <location of the script>
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit

Ref: Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Doc ID 556610.1)

Check What User Should Run Statistics:

$ sqlplus / as sysdba

SQL> @check_stale_stats.sql

Example:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);

Disable Oracle Database Vault & Oracle Label Security

Ref : Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)

Drop EM DB Console Repository from 11gR2 DB

[oracle@ol6-112-rac1 ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Nov 7, 2016 4:09:00 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Enter the following information:

Database SID: RAC1

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: y

Nov 7, 2016 4:10:45 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/RAC/emca_2016_11_07_16_09_00.log.

Nov 7, 2016 4:10:46 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be performed. Some of the possible reasons may be:

1) EM is configured with different hostname then physical host. Set environment variable ORACLE_HOSTNAME=<hostname> and re-run EMCA script

2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script

Nov 7, 2016 4:10:46 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Nov 7, 2016 4:14:24 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Enterprise Manager configuration completed successfully

FINISHED EMCA at Nov 7, 2016 4:14:24 PM

[oracle@ol6-112-rac1 ~]$

For alternative solution please see step in:

Ref : Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)

Verify cluster status

Perform an extra check on the status of the Grid Infrastructure post upgrade by executing the following command from one of the compute nodes:

(root)# /u01/app/12.1.0.2/grid/bin/crsctl check cluster -all

Change Custom Scripts and environment variables to Reference the 12.1.0.2 Grid Home

Start all primary instances in restricted mode

DBUA requires all RAC instances to be running from the current database home before starting the upgrade. To prevent an application from accidentally connecting to the primary database and performing work causing the standby to fall behind, startup the primary database in restricted mode, as follows:

(oracle)$ srvctl start database -d PRIM -o restrict

Upgrade the Database with Database Upgrade Assistant (DBUA)

NOTE: Before starting the Database Upgrade Assistant it is required change the preference for 'concurrent statistics gathering' on the current release if the current setting is not set to 'FALSE'.

First, while still on the 11.2. release, obtain the current setting:

SQL> SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

When 'concurrent statistics gathering' is not not set to 'FALSE', change the value to 'FALSE before the upgrade.

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/

NOTE: Before starting the database upgrade assistant all databases that will be upgraded having services configured with PRECONNECT as option for 'TAF Policy specification' should have these services stopped and disabled. Once a database upgrade is completed, services can be enabled and brought online. Not disabling services having the PRECONNECT option for 'TAF Policy specification' will cause an upgrade to fail.

For each database being upgraded use the srvctl command to determine if a 'TAF policy specification' with 'PRECONNECT' is defined. Example as follows:

(oracle)$ srvctl config service -d <db_unique_name> | grep -i preconnect | wc –l

For each database being upgraded the output of the above command should be 0. When the output of the above command is not equal to 0, find the specific service(s) for which PRECONNECT is defined. Example as follows:

(oracle)$ srvctl config service -d <db_unique_name> -s <service_name>

Those services found need to be stopped and disabled before proceeding the upgrade. Example as follows:

(oracle)$ srvctl stop service -d <db_unique_name> -s "<service_name_list>"
(oracle)$ srvctl disable service -d <db_unique_name> -s "<service_name_list>"

NOTE: Prior to running dbua and upgrading the primary database, remove DIAG_ADR_ENABLED=off from sqlnet.ora (or) either set sqlnet.ora parameter "DIAG_ADR_ENABLED" to ON (the default value). This is further described in Document 1969684.1 "DBCA & DBUA Does not Identify Disk Groups When DIAG_ADR_ENABLED=OFF In sqlnet.ora"

Oracle recommends removing the value for the init.ora parameter 'listener_networks' before starting DBUA. The value will be restored after running DBUA. Be sure to obtain the original value before removing, as follows:

SYS@PRIM1> set lines 200
SYS@PRIM1> select name, value from v$parameter where name='listener_networks';

If the value for parameter listener_networks was set, then the value needs to be removed as follows:

SYS@PRIM1> alter system set listener_networks='' sid='*' scope=both;

Run DBUA from the new 12.1.0.2 ORACLE_HOME as follows:

[oracle@ol6-112-rac1 ~]$ which dbua

/u01/app/oracle/product/12.1.0.2/db_1/bin/dbua

[oracle@ol6-112-rac1 ~]$

[oracle@ol6-112-rac1 ~]$ dbua