Thursday, August 15, 2013

Converting 11gR2 RAC from Standard Edition to Enterprise Edition

The existing system is a 11gR2 (11.2.0.3.7) Standard Edition (SE) RAC with role separation on RHEL 6. The blog post lists the steps for converting the SE RAC to Enterprise Edition (EE) RAC. The SE home is dbhome_1 and EE home is dbhome_2.
1.Take a full backup of the database as precaution in case have to revert back to the standard edition setup again.
2. Since the database home will be updated in the OCR take a backup of the OCR and also take copies of backup files created automatically from all cluster nodes.
[root@rhel6m1 grid]# ocrconfig -manualbackup
rhel6m2     2013/08/14 14:57:27     /opt/app/11.2.0/grid/cdata/rhel6m-cluster/backup_20130814_145727.ocr
3. Get the status of registry components, so validity of these components could be compared before and after the conversion. There are no invalid components before the conversion.
SQL> select comp_id,comp_name,version,status,schema from dba_registry;

COMP_ID  COMP_NAME                                VERSION                        STATUS SCHEMA
-------- ---------------------------------------- ------------------------------ ------ ------
XDB      Oracle XML Database                      11.2.0.3.0                     VALID  XDB
OWM      Oracle Workspace Manager                 11.2.0.3.0                     VALID  WMSYS
EXF      Oracle Expression Filter                 11.2.0.3.0                     VALID  EXFSYS
RUL      Oracle Rules Manager                     11.2.0.3.0                     VALID  EXFSYS
CATALOG  Oracle Database Catalog Views            11.2.0.3.0                     VALID  SYS
CATPROC  Oracle Database Packages and Types       11.2.0.3.0                     VALID  SYS
JAVAVM   JServer JAVA Virtual Machine             11.2.0.3.0                     VALID  SYS
XML      Oracle XDK                               11.2.0.3.0                     VALID  SYS
CATJAVA  Oracle Database Java Packages            11.2.0.3.0                     VALID  SYS
RAC      Oracle Real Application Clusters         11.2.0.3.0                     VALID  SYS

10 rows selected.
4. Find out the patch levels on SE Oracle Home and GI Home. Once the EE is installed and before moving the DB to EE home it must be patched to the same level as the SE Home and not to a higher level than the GI home. The current patches on SE home are
[oracle@rhel6m1 ~]$ /opt/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory -local | grep Patch
Oracle Interim Patch Installer version 11.2.0.3.4
OPatch version    : 11.2.0.3.4
Patch  16619892     : applied on Wed Jul 31 13:43:23 BST 2013
Unique Patch ID:  16346737
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Patch  16619898     : applied on Wed Jul 31 13:42:07 BST 2013
Unique Patch ID:  16376391
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.7 (16742216)"
OPatch succeeded.
Patches on GI home are
[grid@rhel6m1 ~]$ /opt/app/11.2.0/grid/OPatch/opatch lsinventory -local | grep Patch
Oracle Interim Patch Installer version 11.2.0.3.4
OPatch version    : 11.2.0.3.4
Patch  16619892     : applied on Wed Jul 31 13:54:30 BST 2013
Unique Patch ID:  16346737
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Patch  16619898     : applied on Wed Jul 31 13:52:27 BST 2013
Unique Patch ID:  16376391
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.7 (16742216)"
OPatch succeeded.
5. Install the EE across the cluster.
Select install software only option.
Select all the nodes where SE was installed
Select Enterprise Edition
Give the EE home a new location (dbhome_2 where as SE is dbhome_1)
Summary Page

6. Copy init file, password file and sqlnet.ora file (if used for COST) to appropriate location in the new EE home.
[oracle@rhel6m2 ~]$ cd /opt/app/oracle/product/11.2.0/dbhome_2/dbs
[oracle@rhel6m2 dbs]$  cp /opt/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstd11g22 .
[oracle@rhel6m2 dbs]$  cp /opt/app/oracle/product/11.2.0/dbhome_1/dbs/initstd11g22.ora .

[oracle@rhel6m2 ~]$ cd /opt/app/oracle/product/11.2.0/dbhome_2/network/admin
[oracle@rhel6m2 dbs]$  cp /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora .
Make sure this is done on all the nodes.




7. Patch the newly installed EE home (refer Case 2.1 section of the metalink note 1494646.1). Use GI_Home to run the opatch auto to avoid the following
Invoking utility "saveconfigurationsnapshot"
 UtilSession failed: This utility only be supported for GI home. Please specify the Oracle Home to the Grid Infrastructure (GI) Home "/opt/app/11.2.0/grid" by option -oh or set it in ORACLE_HOME environment variable
Following could be seen when patch is successfully applied.
/opt/app/11.2.0/grid/OPatch/opatch auto `pwd` -oh /opt/app/oracle/product/11.2.0/dbhome_2 -ocmrf ocm.rsp
 
 Using configuration parameter file: /opt/app/11.2.0/grid/crs/install/crsconfig_params
patch /usr/local/patches/16619898/custom/server/16619898  apply successful for home  /opt/app/oracle/product/11.2.0/dbhome_2
patch /usr/local/patches/16619892  apply successful for home  /opt/app/oracle/product/11.2.0/dbhome_2
Compare the patch applied on EE home is same as SE home checked earlier on step 4
[oracle@rhel6m1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -local -oh /opt/app/oracle/product/11.2.0/dbhome_2 | grep Patch
Oracle Interim Patch Installer version 11.2.0.3.4
OPatch version    : 11.2.0.3.4
Patch  16619892     : applied on Thu Aug 15 10:34:09 BST 2013
Unique Patch ID:  16346737
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Patch  16619898     : applied on Thu Aug 15 10:27:19 BST 2013
Unique Patch ID:  16376391
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.7 (16742216)"
OPatch succeeded.
8. Verify the oracle executable in the EE home has the correct permissions. The oracle executable permission in the SE home were
oracle@rhel6m1 bin]$ ls -l oracle*
-rwsr-s--x. 1 oracle asmadmin 220193582 Jul 31 13:44 oracle
-rwsr-s--x. 1 oracle asmadmin 220113336 Apr 23 17:37 oracleO
while the newly installed EE had the wrong group ownership.
[oracle@rhel6m1 bin]$ ls -l oracle*
-rwsr-s--x. 1 oracle oinstall 232617534 Aug 15 10:35 oracle
-rwsr-s--x. 1 oracle oinstall 232399041 Aug 14 15:45 oracleO
Having this wrong group permission could result in following error when starting the database with the EE home (output from alert log)
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database std11g2 and diskgroup resource ora.DATA.dg is established
Errors in file /opt/app/oracle/diag/rdbms/std11g2/std11g21/trace/std11g21_ora_24840.trc  (incident=67209):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/app/oracle/diag/rdbms/std11g2/std11g21/incident/incdir_67209/std11g21_ora_24840_i67209.trc
Use ADRCI or Support Workbench to package the incident.
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 24840
To fix the issue set correct ownerships on the oracle executable.
[grid@rhel6m1 ~]$ $GI_HOME/bin/setasmgidwrap o=/opt/app/oracle/product/11.2.0/dbhome_2/bin/oracle
[grid@rhel6m1 ~]$ $GI_HOME/bin/setasmgidwrap o=/opt/app/oracle/product/11.2.0/dbhome_2/bin/oracleO

[oracle@rhel6m1 bin]$ ls -l oracle*
-rwsr-s--x. 1 oracle asmadmin 232617534 Aug 15 10:35 oracle
-rwsr-s--x. 1 oracle asmadmin 232399041 Aug 14 15:45 oracleO
More on kfioTranslateIO03 is available on the metalink note ORA-00600 [kfioTranslateIO03] [17090] (Doc ID 1336846.1)
9. Stop all but one instance.
[oracle@rhel6m1 ~]$ srvctl stop instance -d std11g2 -i std11g22
10. Set cluster_database=false to start the database in exclusive mode in the next start up.
SQL> alter system set cluster_database=FALSE scope=spfile sid='*';
System altered.
Once the change is done stop the remaining instance as well.
11. Change the oracle home associated with the database in the OCR.
[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_1 <--- SE Home

[oracle@rhel6m1 ~]$ srvctl modify database -d std11g2 -o /opt/app/oracle/product/11.2.0/dbhome_2

[oracle@rhel6m1 ~]$ srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
Oracle home: /opt/app/oracle/product/11.2.0/dbhome_2  <--- EE Home
12. Change environment variables (eg. ORACLE_HOME, PATH values) to point to EE home and start the database in exclusive mode. Make sure sqlplus used is from EE home.
[oracle@rhel6m1 ~]$ which sqlplus
/opt/app/oracle/product/11.2.0/dbhome_2/bin/sqlplus

[oracle@rhel6m1 ~]$ sqlplus / as sysdba
SQL> startup
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp
Monitor the alert log for any start up issues. Verify there are no object with errors or issues while compiling during the utrl execute.
SQL> select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;

OBJECTS WITH ERRORS
-------------------
                  0
1 row selected.

SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION
---------------------------
                          0
1 row selected.
Also verify the registry component status
SQL> select comp_id,comp_name,version,status,schema from dba_registry;

COMP_ID    COMP_NAME                                VERSION                        STATUS     SCHEMA
---------- ---------------------------------------- ------------------------------ ---------- --------
XDB        Oracle XML Database                      11.2.0.3.0                     VALID      XDB
OWM        Oracle Workspace Manager                 11.2.0.3.0                     VALID      WMSYS
EXF        Oracle Expression Filter                 11.2.0.3.0                     VALID      EXFSYS
RUL        Oracle Rules Manager                     11.2.0.3.0                     VALID      EXFSYS
CATALOG    Oracle Database Catalog Views            11.2.0.3.0                     VALID      SYS
CATPROC    Oracle Database Packages and Types       11.2.0.3.0                     VALID      SYS
JAVAVM     JServer JAVA Virtual Machine             11.2.0.3.0                     VALID      SYS
XML        Oracle XDK                               11.2.0.3.0                     VALID      SYS
CATJAVA    Oracle Database Java Packages            11.2.0.3.0                     VALID      SYS
RAC        Oracle Real Application Clusters         11.2.0.3.0                     VALID      SYS
Fix any issues that exists.
13. Set cluster_database to true and shutdown the instance.
SQL> alter system set cluster_database=true scope=spfile sid='*'; 
SQL> shutdown immediate;
Start all the instance using srvctl. Make sure using the srvctl from EE home
[oracle@rhel6m1 ~]$ which srvctl
/opt/app/oracle/product/11.2.0/dbhome_2/bin/srvctl

[oracle@rhel6m1 ~]$ srvctl start database -d std11g2
Once the db is started /etc/oratab will be upated with the new oracle home
std11g2:/opt/app/oracle/product/11.2.0/dbhome_2:N               # line added by Agent
14. Remove standard edition specific settings or configuration that are no longer needed. For example drop any triggers used for enabling use of stored outlines or scheduling of statspack snapshots.
15. If diagnostic and tuning pack license are available make sure it's set
SQL> show parameter control
control_management_pack_access       string      DIAGNOSTIC+TUNING
Also verify that AWR snapshots are taken each hour.
16. Use an EE option to verify EE is being used by the database. Easiest way is to create a bitmap index which only available with EE.
SQL> create bitmap index aidx on x(a);
Index created.
If it is on SE following error message will be shown
SQL> create bitmap index aidx on x(a);
create bitmap index aidx on x(a)
*
ERROR at line 1:
ORA-00439: feature not enabled: Bit-mapped indexes
17. Finally remove the old SE homes either using deinstall tool or detaching from inventory and deleting
$ORACLE_HOME/oui/bin/runInstaller -detachHome ORACLE_HOME=SE HOME
rm -rf SE HOME
Useful metalink notes
How to Convert Database from Standard to Enterprise Edition ? [117048.1]
Converting An Enterprise Edition Database To Standard Edition [139642.1]
How to Convert a RAC database from Standard Edition (SE) to Enterprise Edition (EE)? [451981.1]