terça-feira, abril 11, 2017

Aplicar Patch upgradation from to Upgrade Oracle 10g Release 2 from to

This post demonstrate a step by step guide to apply oracle patchset (patch number 8202632) on database. My current environment is Oracle 10gR2 ( installed on Redhat Enterprise Linux 5 update 4 32-bit.

Preparing for the upgrade to

Stop all oracle components running like LISTENER, EM , ISQLPLUS and DB itself etc.
/* Stop the isqlplus if running */
$ isqlplusctl stop
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

/* Stop the EM dbconsole */

$ emctl stop dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release  
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
Stopping Oracle Enterprise Manager 10g Database Control ... 
 ...  Stopped. 

/* Stop the listener */

$ lsnrctl stop

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

The command completed successfully

/* Shutdown the database itself */

$ sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Take a backup of Oracle Home and Database

Once the oracle database is shutdown, take a cold backup of your database and a backup of your ORACLE_HOME.
$cd /u01/apps/oracle/oradata/

 All my data files , control files and log files are in a directory ora10g
 at the location /u01/apps/oracle/oradata/.
 I am going to make a tar archive of ora10g directory. If these files
 are at separate locations then add all those locations into the tar archive.
 And since this is just a test database and is very small in size so tar archive
 works much better then every thing else. But if it would be a production db and 
 is big in size then I would consider other faster ways to take a cold backup of
 my data files.

$ tar czf /home/oracle/ora10g.tar.gz ora10g


 This is my ORACLE_HOME so I would make a tar archive of "db_1" directory.

$ cd /u01/apps/oracle/product/10.2.0/
$ tar czf /home/oracle/oraHomeBackup.tar.gz db_1

Manage your data with TimeZone before upgrade

(Only perform this step if you have data or Scheduler jobs with TZ info)
From 9i onwards Oracle has 2 datatypes that may have data stored affected by a update of the RDBMS DST (Daylight Saving Time) definitions, those are TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ). If you have TZ data stored in your database you need to go through the following steps to ensure the integrity of your data while the database upgrade.
Check which TIMEZONE version file you are currently using.

SQL> select version from v$timezone_file;

If this gives 4 then you may simply proceed with the upgrade even if you have TZ data.
If this gives higher then 4, look at the meta link note: Note 553812.1
If this gives lower then 4, perform the following steps:
Download utltzpv4.sql and run it.

SQL> @utltzpv4.sql    
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Your current timezone version is 2!
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
data is affected by version 4 transition rules.
Any table with YES in the nested_tab column (last column) needs
a manual check as these are nested tables.

PL/SQL procedure successfully completed.

Commit complete.


/* Once the script finishes successfully execute the following query */

column table_owner format a4
column column_name format a18
select * from sys_tzuv2_temptab;

TABL TABLE_NAME                     COLUMN_NAME          ROWCOUNT NES
---- ------------------------------ ------------------ ---------- ---
SYS  SCHEDULER$_JOB                 LAST_ENABLED_TIME           3
SYS  SCHEDULER$_JOB                 LAST_END_DATE               1
SYS  SCHEDULER$_JOB                 LAST_START_DATE             1
SYS  SCHEDULER$_JOB                 NEXT_RUN_DATE               1
SYS  SCHEDULER$_JOB                 START_DATE                  1
SYS  SCHEDULER$_WINDOW              LAST_START_DATE             2
SYS  SCHEDULER$_WINDOW              NEXT_START_DATE             2

9 rows selected.
If it returns no rows, there is nothing that needs to be done. Just proceed with the upgrade.
If it retunrs the detail of columns that contain TZ data which may be affected by the upgrade, see metalink note: Note 553812.1
The Note 553812.1 states that if you see SYS owned SCHEDULER objects then it is safe to ignore them and proceed with the upgrade. But if you see user data or user created jobs here then you need to take a backup of data before upgrade and restore it back after the upgrade. Remove any user created jobs and re-create them after the upgrade.

Install the patchset

Now come back to the oracle user console and move to the directory where you downloaded the patch and unzip the file.
$ cd /home/oracle
$ unzip p8202632_10205_Linux-x86.zip
$ cd Disk1/
$ ./runInstaller
The first screen is welcome screen.
Oracle patchset Installer Welcome page
Provide the Oracle home details here (The oracle home).
Oracle patchset Installer Orale Home Details
The installer will perform prerequisite checks on this screen. Make sure you see the message "The overall result of this check is passed" in the output.
Oracle patchset Installer Prerequisites Checks
Oracle configuration Manager allows you to associate your configuration with your metalink support account. You may skip this.
Oracle patchset Installer Configuration Manager
Installation Summary.
Oracle patchset Installer Summary
Installation progress.
Once progress shows 100%, you will be asked to perform some root specific actions.
Oracle patchset Installer Root specific
Login as root
# which dbhome
   this shows the location of dbhome, oraenv and coraenv files
   rename them for as the root.sh create new ones for
# cd /usr/local/bin/
# mv dbhome dbhome_10201
# mv oraenv oraenv_10201
# mv coraenv coraenv_10201

/* Now execute the script suggested by the installer. */

# /u01/apps/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/apps/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   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.sh script.
Now product-specific root actions will be performed.
When root.sh finishes successfully come back to installer and press ok. Then you should see the End of Installation page as below.
Welcome Oracle patchset Installer End of Installation
Press exit and your ORACLE_HOME is patched with patchset. All your db's working under this ORACLE_HOME will become unusable unless you upgrade your database to 10.2.05 as well.
$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268896 bytes
Variable Size             171967328 bytes
Database Buffers          427819008 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit
As you can see the database is unable to open and throwing the error ORA-01092. Lets look at the alert log file to know what actually happened.
$ tail -f /u01/apps/oracle/admin/ora10g/bdump/alert_ora10g.log 
SMON: enabling cache recovery
Fri Jul  2 15:30:15 2010
Errors in file /u01/apps/oracle/admin/ora10g/udump/ora10g_ora_12856.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Fri Jul  2 15:30:15 2010
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12856
ORA-1092 signalled during: ALTER DATABASE OPEN...
The alert log states that the database has to be upgraded first using UPGRADE option to be able to OPEN normally.

Upgrade the database from to

Now startup the database with upgrade option and run the pre-upgrade information tool to see if the database is okay for the upgrade and if there is some thing to be changed before starting the upgrade.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

SQL> startup upgrade

SQL> spool pre_upgrade.log

SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    07-02-2010 17:39:25
--> name:       ORA10G
--> version:
--> compatible:
--> blocksize:  8192
SQL> spool off

The output from utlu102i.sql shows that every thing is fine, no changes are required and the database is ready for upgrade.
Lets start the upgrade process.
SQL> spool upgrade.log
SQL> @catupgrd.sql
SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
When the upgrade script catupgrd.sql finishes shutdown the database and open the spool of the upgrade process and try to find out if any thing failed. If you see something failed try to fix it and re-run the upgrade process.
The upgrade process may leave many objects invalid in the database. Perform a normal startup and run the utlrp.sql script to recompile any invalid objects.

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

SQL> spool recompile.log

SQL> @utlrp.sql


SQL> spool off

  When the script utlrp.sql completes go ahead and verify if all the components are 
  upgraded to

SQL> select * from v$version

Oracle Database 10g Enterprise Edition Release - Prod
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production
The above query shows that the database components are at version now.

Sem comentários:


Related Posts Widget for Blogs by LinkWithin