Search my Blog

Follow me on Twitter

The Patch and the Furious ( Patching Grid Infra 11.2.0.3. and Rdbms 11.2.0.3 with PSU April 2014)

Introduction:

In June 2014 I have been upgrading quite a few environments in our billing environment which was still 11.2.0.5.0. for both for Grid infra structure and Rdbms on RedHat Linux. My first scenario after reading and setting up a first environment was to install a mandatory 12539000 (well mandatory for that patch level) One off patch and then upgraded to 11.2.0.3 with PSU April 2014. But of course a Dba should be flexible in his approaches so as a form of adapting while doing I decided that it would be best to use PSU Oct 2012 in full and then Migrate to 11.2.0.3. on both my Grid Infra and Rdbms environment. Since I had an agreement with customer that the latest PSU patch would be applied I aimed for implementing April 2014.

By request I got the following Documents on Mos as a must read before going to the upgrade:

Of course cluvfy is you friend so you should use it as part of preparationruncluvfy.sh stage -pre crsinst -upgrade -n mysrvr01hr,mysrvr02hr -rolling -src_crshome /opt/crs/product/112_ee_64/crs -dest_crshome /opt/crs/product/11203_ee_64/crs -dest_version 11.2.0.3.0 -fixup -fixupdir /tmp -verbose>/tmp/11203Upgrade.lst

Orachk script pointed out I would need this One off patch before migrate:PATCH 12539000
The patch must be applied to GI home and is recommended for 11.2.0.2 database homes. It’s available on top of all DB PSUs which are part of corresponding GI PSU.
It’s recommended to be applied with “opatch auto” with latest 11.2 opatch (patch 6880880)as the ROOT user:export PATH=/opt/crs/product/112_ee_64/crs/OPatch/:$PATHwhich opatch$GRID_HOME/OPatch/opatch auto /opt/oracle/stage -oh /opt/crs/product/112_ee_64/crs$GRID_HOME/OPatch/opatch auto /opt/oracle/stage -oh /opt/oracle/product/112_ee_64/db

When opatch asks the following question, enter ‘yes’ without quote:
Enter ‘yes’ if you have unzipped this patch to an empty directory to proceed (yes/no):yes

Since my ORACLE_BASE (/opt/oracle) is the same as my Home of the Oracle User (/opt/oracle) I had issue that after the patching the owner of /opt/oracle became root!!:
drwxr-x— 31 root dba 4096 May 23 09:21 oracleThis meant trouble if i would end session and perform new ssh session from my admin box(being unable to log in) so I added a check before sign off , cause this has to be oracle:dba

Patching the Grind Infra structure:

As they say always better to be safe the sorry so make a copy of the Crs software before start
## as root:tar -c –recursion -pP –file=/opt/oracle/export/CRSbackup.tar /opt/crs/product/

If you have had an existing installation on your system, and you are using the same user account to install this installation, then unset the following environment variables: ORA_CRS_HOME; ORACLE_HOME; ORA_NLS10; TNS_ADMIN; and any other environment variable set for the Oracle installation user that is connected with Oracle software homes.

unset ORA_CRS_HOMEunset ORACLE_HOMEunset ORA_NLS10unset TNS_ADMIN

In RAC environments Upgrade the Grid infra structure Node wise.

Checks you can perform before and after upgrade:crsctl query has softwareversioncrsctl query crs softwareversioncrsctl query has releaseversioncrsctl query crs releaseversion

/opt/oracle/admin/tools/cSpfile.ksh

This shell script is creating a copy of spfile to Init.ora.

After that I created Migration pfiles with larger settings for shared_pool.

Run Opatch
Note on dry run the system would not come down since instances still open . I used my tools to stop them after which patching continued
Opatch had ISSUE with Fact that rdbms 11203 had no resources ( it is software only so the patching crashed). Recommendation is to run this part of PSU after the mig. DO NOT run During the GI upgrade !!!!

You may check results with opatch ls inventory when you are pointing to the GRID_HOME or with “orachk” utility. Reference: ORAchk 11.2.0.3 Upgrade Readiness Assessment (Doc ID 1457357.1)./opt/oracle/product/orachk -u -o post

Upgrade the Databases:

Perform MANUAL upgrade for the databases in scope.

During an earlier upgrade DBUA messed up by adding local listener to Init.ora and continued altering the oratab by the Grid agent. That is why I recommend against the DBU for bulk upgrades . I would script the Upgrade using a Fixed Oracle_HOME ( the new one ) and a dedicated initora / spfile for the MIG.

Steps for Manual Upgrade:

Preferred WAY !

utlu112i.sql AND dbupgdiag.sql ( both located in /opt/oracle/admin/tools

dbupdiag.sql can be obtained from MOS.

Create a new pfile from spfile and check if there are parameteres that need increase ( shared_pool_size might be a good candidate, and job_queue_processes needs 0 during the migration. OR if all is fine well then simply make job_queue_processes = 0 in your spfile.

spfile has production size no alter needed for shared_pool_size. Job_queue_processes = 20 is needed.

1) Start sqlplus and run catupgrd.sql script from the NEW $ORACLE_HOME/rdbms/admin

As a preparatation make sure you have listener.ora , all init.oras ( spfiles) password file present in the proper Subdirectories on your NEW ORACLE_HOME.

sqlplus ” / as sysdba “

spool /tmp/upgrade<DB>.log

startup upgrade;

If you needed to prepare a init<sid>.mig as a migration pfile you need to say: startup upgrade pfile=’/ / /init<sid>.mig .When you perform a startup upgrade these parameters are altered by Oracle:ALTER SYSTEM enable restricted session;ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;Autotune of undo retention is turned off.

After applying a PSU patch this is needed For each database using the patched ORACLE_HOME. BUT it was not needed in my scenario where I had installed and patched the empty ORACLE_HOME 11.2.0.3 and migrated the databases using the new ORACLE_HOME after that.

FallBack scenarios:

Grid Infra fall back :

Downgrading to a release 11.2.0.1 or later release:

Use the command syntax Grid_home/perl/bin/perl rootcrs.pl -downgrade -oldcrshome oldGridHomePath -version oldGridversion, where oldGridhomepath is the path to the previous release Oracle Grid Infrastructure home, and oldGridversion is the release to which you want to downgrade. For example:

If you want to stop a partial or failed 11g release 2 (11.2) installation and restore the previous release Oracle Clusterware, then use the -force flag with this command.

After the rootcrs.pl -downgrade script has completed on all remote nodes, on the local node use the command syntax Grid_home/crs/install/rootcrs.pl -downgrade -lastnode -oldcrshome pre11.2_crs_home -version pre11.2_crs_version [-force], where pre11.2_crs_home is the home of the earlier Oracle Clusterware installation, and pre11.2_crs_version is the release number of the earlier Oracle Clusterware installation.

This script downgrades the OCR. If you want to stop a partial or failed 11g Release 2 (11.2) installation and restore the previous release Oracle Clusterware, then use the -force flag with this command.

Log in as the Grid infrastructure installation owner, and run the following commands, where /u01/app/grid is the location of the new (upgraded) Grid home (11.2):

The database software 11.2.0.2 should still be in place so no need for extra action.

If you are downgrading to Oracle Database 11g Release 1 (11.1), then the COMPATIBLE initialization parameter must be set to 11.0.0 or lower.

Remove Unsupported Parameters from Server Parameter File (SPFILE)

Perform a Full Backup of Oracle Database Before Downgrading

Downgrading Oracle Database to an Earlier Release

1. If you have enabled Oracle Database Vault on your database, then you must:

Grant the Database Vault DV_PATCH_ADMIN role for the SYS account.

Disable Database Vault before downgrading the database.

If you previously had upgraded the database and then used the DBMS_DST PL/SQL package to update the database time zone version, then you must apply the patch for the same time zone file version into the earlier release’s Oracle home before downgrading.

As an example scenario, assume that a release 10.2.0.4 database on Linux x64 using DSTv4 had been upgraded to release 11.2.0.2, and DBMS_DST was then run to update this database to DSTv14. Then, before downgrading from release 11.2.0.3 to 10.2.0.4, you need to apply on the release 10.2.0.4 side the DSTv14 patch for 10.2.0.4 for Linux x64. This ensures that your TIMESTAMPWITHTIMEZONE data is not logically corrupted during retrieval.

To find which time zone file version your database is using, run:

SELECT value$ FROM sys.props$ WHERE NAME = ‘DST_PRIMARY_TT_VERSION’;

If you had set the ORA_TZFILE environment variable to the full path name of the timezone.dat file when you upgraded to Oracle Database 11g Release 2 (11.2), then you must unset it if you subsequently downgrade your database.

Two time zone files are included in the Oracle home directory:

The default time zone file at

$ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat

A smaller time zone file at

$ORACLE_HOME/oracore/zoneinfo/timezone.dat

If you do not unset the ORA_TZFILE variable, then connecting to the database using the smaller time zone file might produce the following errors:

SP2-1503: Unable to initialize Oracle call interface

SP2-0152: ORACLE may not be functioning properly

4. If you have Oracle Application Express on your database, then you must copy the apxrelod.sql file from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/apex/ directory to a directory outside of the Oracle home, such as the temporary directory on your system.

Make a note of the new location of this file.

5. If you have created objects based on fixed objects, then drop these objects to avoid possible ORA-00600 errors. You can re-create these objects after the downgrade.

6. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

This step is required only if Enterprise Manager Database Control is already configured for the database.

Stop Database Control, as follows:

Set the ORACLE_UNQNAME environment variable to the database unique name.

Run the following command:

ORACLE_HOME/bin/emctl stop dbconsole

If the database being downgraded is an Oracle RAC database, then perform this step on all instances.

If you are downgrading an Oracle RAC database to 10g Release 1 (10.1), then you must remove extra voting disks before shutting down the Oracle Clusterware stack.

1. To see the number of voting disks used and to list voting disk paths, run the following command:

Oracle_Clusterware_Home/bin/crsctl query css votedisk

2. Remove each additional voting disk you find in the previous step by running the following command, where path is the voting disk path listed in the File Name column displayed in the previous step:

9. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

Note:

If you are downgrading a cluster database, then shut down the instance completely and change the CLUSTER_DATABASE initialization parameter to FALSE. After the downgrade, you must set this parameter back to TRUE.

10. Using SQL*Plus, connect to the database instance as a user with SYSDBA privileges.

11. Start up the instance in DOWNGRADE mode:

SQL> STARTUP DOWNGRADE

You might be required to use the PFILE option to specify the location of your initialization parameter file.

12. If you have Enterprise Manager configured in your database, then drop the Enterprise Manager user:

DROP USER sysman CASCADE;

13. Set the system to spool results to a log file so you can track the changes and issues:

SQL> SPOOL downgrade.log

14. Run catdwgrd.sql:

SQL> @catdwgrd.sql

The following are notes about running the script:

You must use the version of the script included with Oracle Database 11g Release 2 (11.2).

You must run the script in the Oracle Database 11g Release 2 (11.2) environment.

The script downgrades all Oracle Database components in the database to the major release or Oracle Database 11g patch release from which you originally upgraded.

If you encounter any problems when you run the script, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the script. You can rerun any of the scripts described in this chapter as many times as necessary.

If the downgrade for a component fails, then an ORA-39709 error is displayed and the SQL*Plus session terminates without downgrading the Oracle Database data dictionary. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded. You must identify and fix the problem before rerunning the catdwgrd.sql script.

15. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Then, check the spool file and verify that there were no errors generated during the downgrade. You named the spool file in Step 13 and the suggested name was downgrade.log. Correct any problems you find in this file and rerun the downgrade script if necessary.

Note:

If you want to save the results of the first time you ran the downgrade script, then before you rerun it be sure to rename downgrade.log to something else.

16. Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

17. Exit SQL*Plus.

18. If your operating system is Linux or UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:

ORACLE_HOME

PATH

You should also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the downgraded Oracle home.

Delete the Oracle service at a command prompt by issuing the ORADIM command. For example, if your SID is ORCL, then enter the following command:

C:\> ORADIM -DELETE -SID ORCL

Create the Oracle service of the database that you are downgrading at a command prompt using the ORADIM command.

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS

-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

This syntax includes the following variables:

Variable

SID

PASSWORD

USERS

ORACLE_HOME

For example, if you are downgrading to Oracle Database 10g Release 2 (10.2), if your SID is ORCL, your PASSWORD is TWxy5791, the maximum number of USERS is 10, and the ORACLE_HOME directory is C:\ORANT, then enter the following command:

C:\> ORADIM -NEW -SID ORCL -INTPWD TWxy5791 -MAXUSERS 10

-STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA

Note:

You are not required to change any Windows Registry settings when downgrading a database. The ORADIM utility makes all necessary changes automatically.

20. Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.

If this is an Oracle RAC database, execute the following command to return the database to single instance mode:

set CLUSTER_DATABASE=FALSE

Note:

If you are downgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured. Set the CLUSTER_DATABASE initialization parameter to FALSE. After the downgrade, you must set this initialization parameter back to TRUE.

21. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the previous release.

22. Start SQL*Plus.

23. Connect to the database instance as a user with SYSDBA privileges.

24. Start up the instance:

SQL> STARTUP UPGRADE

25. Set the system to spool results to a log file to track changes and issues:

SQL> SPOOL reload.log

26. Run catrelod.sql:

SQL> @catrelod.sql

The catrelod.sql script reloads the appropriate version of all of the database components in the downgraded database.

27. If you are downgrading to Oracle Database 11g Release 1 (11.1.0.6), run the xsrelod.sql script:

SQL> @xsrelod.sql

Running the xsrelod.sql script avoids the following error:

PLS-00306: wrong number or types of arguments in call

to ‘INVALIDATE_DSD_CACHE’ DBMS_XS_DATA_SECURITY_EVENTS

PL/SQL: Statement ignored

Note:

The PLS-00306 error is not an issue for Oracle Database release 11.2.0.7 or later.

28. If you are downgrading to Oracle Database 10g Release 1 (10.1.0.5) and you have XDB in your database, then run the dbmsxdbt.sql script:

@dbmsxdbt.sql

Running the dbmsxdbt.sql script avoids the following errors when reloading XDB.DBMS_XDBT package:

PLS-00201: identifier ‘CTXSYS.CTX_OUTPUT’ must be declaredPLS-00201: identifier ‘CTX_DDL’ must be declared

SQL> @apxrelod.sql

Running the apxrelod.sql script avoids package APEX_030200.WWV_FLOW_HELP being INVALID due to the following error:

PLS-00201: identifier ‘CTX_DDL’ must be declared

30. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

31. Shut down and restart the instance for normal operation:

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

You might be required to use the PFILE option to specify the location of your initialization parameter file.

Perform this step if the database is configured for Oracle Label Security and you are downgrading to Oracle Database 10g Release 1 (10.1).

Copy the olstrig.sql script from the Oracle Database 11g Release 2 (11.2) Oracle home to the Oracle home of the version to which the database is to be downgraded.

Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.

[where db-unique-name is the database name (not the instance name), old_ORACLE_HOME is the location of the old Oracle home in which the downgraded database will be running. In this example, to_relnum is the database release number to which the database is being downgraded. (For example: 11.2.0.1.0.)]

Caution:

By default, any named user may create a server pool. To restrict the operating system users that have this privilege, Oracle strongly recommends that you add specific users to the CRS Administrators list.

See Also: Oracle Clusterware Administration and Deployment Guide for more information about adding users to the CRS Administrators list.

Looking back at these environments now they are patches my recommendation can only be that these environments should be patched more often but as we all know that is sometimes easier said then done.
As always don’t believe it and test it first !!!

PS. the fallback scenario has not been used nor implemented it has been added here for reference .