Time for action – patch set upgrade of physical standby

For upgrading a patch set from 11.2.0.1 to 11.2.0.3 in the Data Guard environment with the SQL* Plus command line, execute the following steps:

  1. Install 11.2.0.3 on the primary and standby server. Download Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER from http://support.oracle.com, which comes with seven zipped files of total 5 GB, and unzip filesystem can be downloaded from https://edelivery.oracle.com/. Ensure that the unzipped directory's owner is Oracle. From the database directory, initiate runInstaller from the primary database server, as shown in the following screenshot:
    Time for action – patch set upgrade of physical standby
  2. Once the GUI is launched, you will have several options, if you would like to get security updates by adding the e-mail address, installation options, grid installation options, product languages, and so on. In these, you must choose a new ORACLE_HOME directory outside the existing ORACLE_HOME location for installation. In the installation options, you must opt for Enterprise Edition to enable the feature of Data Guard.
  3. Before you start the actual installation, runInstaller performs the prerequisite check for RPM's version, kernel settings, and swap memory settings. If any of these are not adequate, you should fix them prior to the installation from the GUI. These fixes differ from one OS to the other. Note that if some of the RPMs are of a higher version, then you can acknowledge them by ignoring them and then go ahead with the installation.
  4. After copying the files, linking the libraries, and setting up the files, you have to run the /u01/home/oracle/product/11.2.0/db_2/root.sh script from the root user, as shown in the following screenshot:
    Time for action – patch set upgrade of physical standby
  5. Open a new terminal as the root user and run the following script:
    [root@oracle-primary ~]# /u01/home/oracle/product/11.2.0/db_2/root.sh
Performing root user operation for Oracle 11g 
The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/home/oracle/product/11.2.0/db_2
Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@oracle-primary ~]#
  6. Run the pre-upgrade scripts from 11.2.0.1 home of the primary database. From the previous 11.2.0.1 home of the database, spool the $ORACLE_HOME/rdbms/admin/utlu112i.sql script of 11.2.0.3 to run the pre-upgrade check as follows:
    SQL> @/u01/home/oracle/product/11.2.0/db_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-16-2012 19:54:41
Script Version: 11.2.0.3.0 Build: 001
******************************************************************
Database:
******************************************************************
--> name:          ORCL
--> version:       11.2.0.1.0
--> compatible:    11.2.0.0.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V11
*****************************************************************
Recommendations
******************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;
*****************************************************************
SQL>
  7. Running utlu112i.sql is mandatory even if you are upgrading manually or using DBUA. Review the spool logfile and fix it if there are any errors and warnings; for example, invalid objects, invalid registry components, tablespaces' thresholds, and on clearing recycle bin objects.
  8. Now use the script to collect the database's upgrade diagnostic information (dbupgdiag.sql) from MOS note:556610.1. If any invalid objects are found, run the $ORACLE_HOME/rdbms/admin/utlrp.sql script multiple times to validate these invalid objects in the database until there is no change in the number of invalid objects, shown as follows:
    SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
-----------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2012-12-16 21:16:01
........................
ERRORS DURING RECOMPILATION
---------------------------
                          0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
  9. Disable the log transport and stop MRP in the standby database. Check the synchronization between the primary and standby databases and then proceed to defer the remote destination to send redo transport as follows:
    SQL> alter system set log_archive_dest_state_2='defer';
System altered.
SQL>
  10. Now stop MRP in the standby database from SQL* Plus as follows:
    SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
  11. Take a complete backup of the database and stop the primary and standby databases, including listener services.
  12. Take a backup of the entire database, either cold or hot backup, using RMAN. No need to perform a backup of ORACLE_HOME because we are installing a new 11.2.0.3 home outside 11.2.0.1 home. Now shut down the primary and standby services, including the listener services.
  13. Change the environment variable's settings and run the Upgrade script in the primary database.
  14. Ensure that you have modified the environment variables ORACLE_HOME and LIBRARY_PATH,PATH, and they are pointing to the newly installed home 11.2.0.3. Copy INIT/SPFILE and the network configuration files and run the catupgrade.sql script to upgrade the data dictionary objects as follows:
    [oracle@oracle-primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 16 21:26:26 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> spool /home/oracle/upgrade.log
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2238099456 bytes
Fixed Size                  2230312 bytes
Variable Size            1056966616 bytes
Database Buffers         1174405120 bytes
Redo Buffers                4497408 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> @?/rdbms/admin/catupgrd.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catupgrd.sql /st_rdbms_11.2.0/3 2011/05/18 15:07:25 cmlim Exp $
SQL> Rem
SQL> Rem catupgrd.sql
.........
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM                This forces user to start a new sqlplus session in order
SQL> REM                to connect to the upgraded db.
SQL> exit
  15. Start the database in the normal mode and run the following scripts:
    SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql;
TIMESTAMP
------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2012-12-16 22:26:56
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
...                                       ...
... Completed Moving the Baseline Data    ...
...                                       ...
.................
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '11.2.0.3',
  9     0,
 10     'PSU',
 11     'Patchset 11.2.0.2.0'),
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/home/oracle/product/11.2.0/db_2/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2012Dec16_22_27_18.log
SQL>
  16. Run the utlrp.sql script to compile invalid objects as follows:
    SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;
TIMESTAMP
------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2012-12-16 22:28:48
ERRORS DURING RECOMPILATION
---------------------------
                          0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
  17. Post the upgrade scripts in the primary database. Now upgrade the time zone to the latest version using DBMS_DST, upgrade the recovery catalog, and upgrade the statistics table if it is created by the DBMS_STATS package.
  18. Synchronize the standby database with the primary database. After upgrading the primary database successfully, enable remote destination to send redo transport as follows:
    SQL> alter system set log_archive_dest_state_2='enable';
System altered.
SQL>
DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED LOG_GAP
---------- -------------- ------------ ----------- -------
ORCL       ORACLE-PRIMARY          969         943      26
  19. We do have around 26 archive gaps after the upgrade. Now start MRP to apply archives on the standby database. Depending on the gaps between the primary and standby databases, it will take time to synchronize.
    SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED LOG_GAP
---------- -------------- ------------ ----------- -------
ORCL       ORACLE-PRIMARY          972         971       1
sSun Dec 16 23:03:46 2012
RFS[1]: Selected log 10 for thread 1 sequence 973 dbid 1316772835 branch 788992101
Archived Log entry 51 added for thread 1 sequence 972 ID 0x4eede1f7 dest 3:
Recovery of Online Redo Log: Thread 1 Group 10 Seq 973 Reading mem 0
  Mem# 0: /u02/app/oracle/oradata/orcl/standby_redo01.log

What just happened?

We have seen how to install an out-of-place upgrade of a database from 11.2.0.1 patch set level to 11.2.0.3 patch set, including the physical standby database.

Have a go hero – in-place patch set installation

We can perform a patch set installation either in-place or out-of-place. We have just seen how to perform an out-of-place upgrade. To do an in-place patch set installation, perform the following steps:

  1. Back up INIT/SPFILE and the network configuration files.
  2. Detach ORACLE_HOME from the database as ./runInstaller -detachHome ORACLE_HOME= /u01/home/oracle/product/11.2.0/db_1.
  3. Remove old ORACLE_HOME (11.2.0.1).
  4. Install a new patch set level, 11.2.0.3.
  5. Copy INIT/SPFILE and the network configuration files to the new ORACLE_HOME directory.
  6. Upgrade your database (catupgrd.sql or DBUA).
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.217.147.193