Steps to upgrade the RAC Database:
----------------------------------
Itz a two node RAC database. before upgrading the database, we need to upgrade the clusterware, that is successfully done on both the participating nodes.
CLUSTERWARE SOFTWARE VERSION:11.1.0.7.0
CLUSTERWARE ACTIVE VERSION:11.1.0.7.0
ORACLE S/W VERSION: 11.1.0.6.0
Before going ahead on anything like upgradation we should have a rollback strategy in plan, Incase if anything fails, and if the failure is unrecoverable then we need to revert back to our original ORACLE_HOME(11.1.0.6.0)
This is the most significant step which most of us would forget to do. Taking backup of the ORACLE_HOME
Now upgrade the ORACLE_HOME to 11.1.0.7.0 before upgrading the database that is running under it.
I'm going to initiate the Upgradation from node1
Step1:Stop all the services running from ORACLE_HOME(Node1)
Step2:srvctl stop instance -d proddb -i proddb1
Step3:srvctl stop asm -n <node1>
It is better to stop the listener, instead we are stopping the node application.
step4:srvctl stop nodeapps -n <node1>
$ cd patchset_directory/Disk1
$ ./runInstaller
STEP1: On the Welcome screen, click Next.
STEP2: In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.
STEP3:Click Next.
STEP4:The Selected Nodes screen appears. The patch set will be installed on these nodes. Click Next.
STEP5:Check for errors, if any, in the Product-Specific Prerequisite Checks and click Next.
STEP6:On the Summary screen, click Install.
It will start the installation on the ORACLE_HOME mentioned above.
Once it is done, it will show a script that we need to execute to complete the installation.
$ORACLE_HOME/root.sh on all the participating nodes
now our ORACLE_HOME is upgraded to 11.1.0.7.0
itz time to upgrade our RAC database that is running from this ORACLE_HOME
we crossed half ocean, but now we are stepping into a turbulent sea.
STEP1: If using ASM, start the asm of the particular node.
$srvctl start asm -n <node>
While starting the asm, an error during that process
1) CRS-215 Srvctl unable to start ASM
workaround:
-----------
login as root
./crsctl stop crs
./crsctl start crs
STEP2: stop all the services running from that node.
STEP3: Start the listener service of the particular node.
$srvctl start listener -n <node>
STEP4: STARTUP UPGRADE
We cannot open our database in normal open mode, we will get an error
ORA-01092: ORACLE instance terminated. Disconnection forced
for more details open the alert log, to find the following error
ORA-39700: database must be opened with UPGRADE option .
STEP5: Execute the Pre-upgrade tool utlu111i.sql
i.e., $ORACLE_HOME/rdbms/admin/utlu111i.sql
After executing the pre-upgrade tool it will show the recommendation along with that i received couple of invalid components, which i felt would be rectified once upgraded to 11.1.0.7.0
or
we can execute the script $ORACLE_HOME/rdbms/admin/catrul.sql
Invalid objects in the components:
--> Rule Manager [upgrade] INVALID
STEP6: It will display some changes we need to make before going ahead with the upgradation, like parameter changes and the components validity etc., We need to change all those requirements before accelerating our database towards the newer version.
STEP7: startup nomount to change an important parameter CLUSTER_DATABASE=FALSE,
It would be TRUE by default in RAC installation.
STEP8: shutdown the database
STEP9: Startup Upgrade
STEP10:spool the proceeding from the sql prompt: spool cat_upgrade.log
STEP11:Execute the script catupgrd.sql to upgrade all the database component to the newer version
sql>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
STEP12:Check for the errors after execution of the above command. Open the spool file to check for any errors, If any errors found re-run the catupgrd.sql
When i executed the following sql statement to find any invalid components, coz during the execution of pre-upgrade script i found some components were invalid.
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;
to my surprise all the components shown invalid were valid except below one
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
OWB OWB 11.1.0.6.0 VALID
Oracle Application Express APEX 3.0.1.00.12 VALID
Oracle Enterprise Manager EM 11.1.0.7.0 VALID
Oracle Ultra Search WK 11.1.0.7.0 VALID
OLAP Catalog AMD 11.1.0.7.0 VALID
Spatial SDO 11.1.0.7.0 VALID
Oracle Multimedia ORDIM 11.1.0.7.0 INVALID
Oracle XML Database XDB 11.1.0.7.0 VALID
Oracle Text CONTEXT 11.1.0.7.0 VALID
Oracle Expression Filter EXF 11.1.0.7.0 VALID
Oracle Rules Manager RUL 11.1.0.7.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
Oracle Workspace Manager OWM 11.1.0.7.0 VALID
Oracle Database Catalog Views CATALOG 11.1.0.7.0 VALID
Oracle Database Packages and T CATPROC 11.1.0.7.0 VALID
JServer JAVA Virtual Machine JAVAVM 11.1.0.7.0 VALID
Oracle XDK XML 11.1.0.7.0 VALID
Oracle Database Java Packages CATJAVA 11.1.0.7.0 VALID
OLAP Analytic Workspace APS 11.1.0.7.0 VALID
Oracle OLAP API XOQ 11.1.0.7.0 VALID
Oracle Real Application Cluste RAC 11.1.0.7.0 VALID
SQL> select object_type,object_name, owner from dba_objects
where status ='INVALID'
order by owner, object_name, object_type;
OBJECT_TYPE OBJECT_NAME OWNER
------------------- ------------------------------ --------
PACKAGE BODY ORD_DICOM_ADMIN_PRV ORDSYS
SQL> alter package ORDSYS.ORD_DICOM_ADMIN_PRV compile body;
Warning: Package Body altered with compilation errors.
SQL> SQL> sho errors
Errors for PACKAGE BODY ORDSYS.ORD_DICOM_ADMIN_PRV:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3824/7 PL/SQL: SQL Statement ignored
3826/11 PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
3839/9 PL/SQL: SQL Statement ignored
3841/13 PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
3854/9 PL/SQL: SQL Statement ignored
3856/13 PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
SQL>
It is a bug, so we need to apply an interim Generic patch while upgrading from 11.1.0.6.0 to 11.1.0.7.0
After applying it we need to execute the scripts again, in order
SQL> startup upgrade
ORACLE instance started.
..
SQL> @?/rdbms/admin/catxdbv.sql
View created.
Grant succeeded.
Synonym created.
Comment created.
Comment created.
..
..
SQL> @?/rdbms/admin/catupgrd.sql
..
..
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>
SQL>
We applied it and after it checked again for the invalid component, Thank God! it was not found.
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
OWB OWB 11.1.0.6.0 VALID
Oracle Application Express APEX 3.0.1.00.12 VALID
Oracle Enterprise Manager EM 11.1.0.7.0 VALID
Oracle Ultra Search WK 11.1.0.7.0 VALID
OLAP Catalog AMD 11.1.0.7.0 VALID
Spatial SDO 11.1.0.7.0 VALID
Oracle Multimedia ORDIM 11.1.0.7.0 VALID
Oracle XML Database XDB 11.1.0.7.0 VALID
Oracle Text CONTEXT 11.1.0.7.0 VALID
Oracle Expression Filter EXF 11.1.0.7.0 VALID
Oracle Rules Manager RUL 11.1.0.7.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
Oracle Workspace Manager OWM 11.1.0.7.0 VALID
Oracle Database Catalog Views CATALOG 11.1.0.7.0 VALID
Oracle Database Packages and T CATPROC 11.1.0.7.0 VALID
JServer JAVA Virtual Machine JAVAVM 11.1.0.7.0 VALID
Oracle XDK XML 11.1.0.7.0 VALID
Oracle Database Java Packages CATJAVA 11.1.0.7.0 VALID
OLAP Analytic Workspace APS 11.1.0.7.0 VALID
Oracle OLAP API XOQ 11.1.0.7.0 VALID
Oracle Real Application Cluste RAC 11.1.0.7.0 VALID
STEP13:After execution of this script the database would be automatically down, startup the database in normal mode to execute utlrp.sql
STEP14:execute the script utlrp.sql to check for the invalid object and compile it.
sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
After the execution of UTLRP.sql script, we found an error.
SQL> @?/rdbms/admin/utlrp
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
DECLARE
*
ERROR at line 1:
ORA-00904: "E"."OBJNUM": invalid identifier
ORA-06512: at line 31
ORDIM INVALID OBJECTS: ORD_DICOM_ADMIN_PRV - 5 - 11
ORDIM registered 0 XML schemas.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
The workaround we did for this is:
SQL> sho user
USER is "SYS"
SQL> desc sys.enabled$indexes
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMANAME NOT NULL VARCHAR2(30)
INDEXNAME NOT NULL VARCHAR2(30)
SQL> drop table sys.enabled$indexes;
Table dropped.
SQL> CREATE TABLE sys.enabled$indexes( schemaname, indexname, objnum )AS select u.name, o1.name, i.obj# from user$ u, obj$ o1, obj$ o2, ind$ i where u.user# = o1.owner# and o1.type# = 1 and o1.obj# = i.obj# and bitand(i.property, 16)= 16 and bitand(i.flags, 1024)=0 and i.bo# = o2.obj# and bitand(o2.flags, 2)=0;
Table created.
SQL> @?/rdbms/admin/utlrp
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
SQL>
After much ordeal successfully upgraded database to 11.1.0.7.0
----------------------------------
Itz a two node RAC database. before upgrading the database, we need to upgrade the clusterware, that is successfully done on both the participating nodes.
CLUSTERWARE SOFTWARE VERSION:11.1.0.7.0
CLUSTERWARE ACTIVE VERSION:11.1.0.7.0
ORACLE S/W VERSION: 11.1.0.6.0
Before going ahead on anything like upgradation we should have a rollback strategy in plan, Incase if anything fails, and if the failure is unrecoverable then we need to revert back to our original ORACLE_HOME(11.1.0.6.0)
This is the most significant step which most of us would forget to do. Taking backup of the ORACLE_HOME
Now upgrade the ORACLE_HOME to 11.1.0.7.0 before upgrading the database that is running under it.
I'm going to initiate the Upgradation from node1
Step1:Stop all the services running from ORACLE_HOME(Node1)
Step2:srvctl stop instance -d proddb -i proddb1
Step3:srvctl stop asm -n <node1>
It is better to stop the listener, instead we are stopping the node application.
step4:srvctl stop nodeapps -n <node1>
$ cd patchset_directory/Disk1
$ ./runInstaller
STEP1: On the Welcome screen, click Next.
STEP2: In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.
STEP3:Click Next.
STEP4:The Selected Nodes screen appears. The patch set will be installed on these nodes. Click Next.
STEP5:Check for errors, if any, in the Product-Specific Prerequisite Checks and click Next.
STEP6:On the Summary screen, click Install.
It will start the installation on the ORACLE_HOME mentioned above.
Once it is done, it will show a script that we need to execute to complete the installation.
$ORACLE_HOME/root.sh on all the participating nodes
now our ORACLE_HOME is upgraded to 11.1.0.7.0
itz time to upgrade our RAC database that is running from this ORACLE_HOME
we crossed half ocean, but now we are stepping into a turbulent sea.
STEP1: If using ASM, start the asm of the particular node.
$srvctl start asm -n <node>
While starting the asm, an error during that process
1) CRS-215 Srvctl unable to start ASM
workaround:
-----------
login as root
./crsctl stop crs
./crsctl start crs
STEP2: stop all the services running from that node.
STEP3: Start the listener service of the particular node.
$srvctl start listener -n <node>
STEP4: STARTUP UPGRADE
We cannot open our database in normal open mode, we will get an error
ORA-01092: ORACLE instance terminated. Disconnection forced
for more details open the alert log, to find the following error
ORA-39700: database must be opened with UPGRADE option .
STEP5: Execute the Pre-upgrade tool utlu111i.sql
i.e., $ORACLE_HOME/rdbms/admin/utlu111i.sql
After executing the pre-upgrade tool it will show the recommendation along with that i received couple of invalid components, which i felt would be rectified once upgraded to 11.1.0.7.0
or
we can execute the script $ORACLE_HOME/rdbms/admin/catrul.sql
Invalid objects in the components:
--> Rule Manager [upgrade] INVALID
STEP6: It will display some changes we need to make before going ahead with the upgradation, like parameter changes and the components validity etc., We need to change all those requirements before accelerating our database towards the newer version.
STEP7: startup nomount to change an important parameter CLUSTER_DATABASE=FALSE,
It would be TRUE by default in RAC installation.
STEP8: shutdown the database
STEP9: Startup Upgrade
STEP10:spool the proceeding from the sql prompt: spool cat_upgrade.log
STEP11:Execute the script catupgrd.sql to upgrade all the database component to the newer version
sql>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
STEP12:Check for the errors after execution of the above command. Open the spool file to check for any errors, If any errors found re-run the catupgrd.sql
When i executed the following sql statement to find any invalid components, coz during the execution of pre-upgrade script i found some components were invalid.
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;
to my surprise all the components shown invalid were valid except below one
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
OWB OWB 11.1.0.6.0 VALID
Oracle Application Express APEX 3.0.1.00.12 VALID
Oracle Enterprise Manager EM 11.1.0.7.0 VALID
Oracle Ultra Search WK 11.1.0.7.0 VALID
OLAP Catalog AMD 11.1.0.7.0 VALID
Spatial SDO 11.1.0.7.0 VALID
Oracle Multimedia ORDIM 11.1.0.7.0 INVALID
Oracle XML Database XDB 11.1.0.7.0 VALID
Oracle Text CONTEXT 11.1.0.7.0 VALID
Oracle Expression Filter EXF 11.1.0.7.0 VALID
Oracle Rules Manager RUL 11.1.0.7.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
Oracle Workspace Manager OWM 11.1.0.7.0 VALID
Oracle Database Catalog Views CATALOG 11.1.0.7.0 VALID
Oracle Database Packages and T CATPROC 11.1.0.7.0 VALID
JServer JAVA Virtual Machine JAVAVM 11.1.0.7.0 VALID
Oracle XDK XML 11.1.0.7.0 VALID
Oracle Database Java Packages CATJAVA 11.1.0.7.0 VALID
OLAP Analytic Workspace APS 11.1.0.7.0 VALID
Oracle OLAP API XOQ 11.1.0.7.0 VALID
Oracle Real Application Cluste RAC 11.1.0.7.0 VALID
SQL> select object_type,object_name, owner from dba_objects
where status ='INVALID'
order by owner, object_name, object_type;
OBJECT_TYPE OBJECT_NAME OWNER
------------------- ------------------------------ --------
PACKAGE BODY ORD_DICOM_ADMIN_PRV ORDSYS
SQL> alter package ORDSYS.ORD_DICOM_ADMIN_PRV compile body;
Warning: Package Body altered with compilation errors.
SQL> SQL> sho errors
Errors for PACKAGE BODY ORDSYS.ORD_DICOM_ADMIN_PRV:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3824/7 PL/SQL: SQL Statement ignored
3826/11 PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
3839/9 PL/SQL: SQL Statement ignored
3841/13 PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
3854/9 PL/SQL: SQL Statement ignored
3856/13 PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
SQL>
It is a bug, so we need to apply an interim Generic patch while upgrading from 11.1.0.6.0 to 11.1.0.7.0
After applying it we need to execute the scripts again, in order
SQL> startup upgrade
ORACLE instance started.
..
SQL> @?/rdbms/admin/catxdbv.sql
View created.
Grant succeeded.
Synonym created.
Comment created.
Comment created.
..
..
SQL> @?/rdbms/admin/catupgrd.sql
..
..
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>
SQL>
We applied it and after it checked again for the invalid component, Thank God! it was not found.
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
OWB OWB 11.1.0.6.0 VALID
Oracle Application Express APEX 3.0.1.00.12 VALID
Oracle Enterprise Manager EM 11.1.0.7.0 VALID
Oracle Ultra Search WK 11.1.0.7.0 VALID
OLAP Catalog AMD 11.1.0.7.0 VALID
Spatial SDO 11.1.0.7.0 VALID
Oracle Multimedia ORDIM 11.1.0.7.0 VALID
Oracle XML Database XDB 11.1.0.7.0 VALID
Oracle Text CONTEXT 11.1.0.7.0 VALID
Oracle Expression Filter EXF 11.1.0.7.0 VALID
Oracle Rules Manager RUL 11.1.0.7.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ------------ -----------
Oracle Workspace Manager OWM 11.1.0.7.0 VALID
Oracle Database Catalog Views CATALOG 11.1.0.7.0 VALID
Oracle Database Packages and T CATPROC 11.1.0.7.0 VALID
JServer JAVA Virtual Machine JAVAVM 11.1.0.7.0 VALID
Oracle XDK XML 11.1.0.7.0 VALID
Oracle Database Java Packages CATJAVA 11.1.0.7.0 VALID
OLAP Analytic Workspace APS 11.1.0.7.0 VALID
Oracle OLAP API XOQ 11.1.0.7.0 VALID
Oracle Real Application Cluste RAC 11.1.0.7.0 VALID
STEP13:After execution of this script the database would be automatically down, startup the database in normal mode to execute utlrp.sql
STEP14:execute the script utlrp.sql to check for the invalid object and compile it.
sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
After the execution of UTLRP.sql script, we found an error.
SQL> @?/rdbms/admin/utlrp
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
DECLARE
*
ERROR at line 1:
ORA-00904: "E"."OBJNUM": invalid identifier
ORA-06512: at line 31
ORDIM INVALID OBJECTS: ORD_DICOM_ADMIN_PRV - 5 - 11
ORDIM registered 0 XML schemas.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
The workaround we did for this is:
SQL> sho user
USER is "SYS"
SQL> desc sys.enabled$indexes
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMANAME NOT NULL VARCHAR2(30)
INDEXNAME NOT NULL VARCHAR2(30)
SQL> drop table sys.enabled$indexes;
Table dropped.
SQL> CREATE TABLE sys.enabled$indexes( schemaname, indexname, objnum )AS select u.name, o1.name, i.obj# from user$ u, obj$ o1, obj$ o2, ind$ i where u.user# = o1.owner# and o1.type# = 1 and o1.obj# = i.obj# and bitand(i.property, 16)= 16 and bitand(i.flags, 1024)=0 and i.bo# = o2.obj# and bitand(o2.flags, 2)=0;
Table created.
SQL> @?/rdbms/admin/utlrp
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
SQL>
After much ordeal successfully upgraded database to 11.1.0.7.0
