Mas Rony’s Notes

Upgrade database oracle sebaiknya dilakukan di mesin testing terlebih dahulu sebelum dilakukan upgrade yang sesungguhnya di mesin production, untuk meminimalisir kesalahan atau error di database production. Untuk list patch set terbaru oracle silahkan anda berkunjung ke website resmi oracle disini. Testing disini menggunakan OS Windows 2003 SP2 dengan oracle versi 10.2.0.1 dalam kondisi fresh install.

Persiapan Sebelum Upgrade

• Download patch set number 6810189 dari metalink oracle.
  tentunya anda juga harus mempunyai account suppot identifier oracle (CSI) yang valid.
• Buat instan database
  Buat instan database dengan spesifikasi yang sama dengan database production. Dalam contoh ini saya menggunakan SID : ORCL
• Import database
  Import database dari backup server production, dengan cara yang biasa anda lakukan di environment anda, dan pastikan telah bekerja dengan baik.
• Matikan semua service oracle
  
  C:\> set oracle_sid=ORCL
  C:\> isqlplusctl stop

  iSQL*Plus 10.2.0.1.0
  Copyright (c) 2003, 2005, Oracle.  All rights reserved.
  Stopping iSQL*Plus ...
  iSQL*Plus stopped.

  C:\> emctl stop dbconsole

  Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
  Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
  
  https://jtim:1158/em/console/aboutApplication
  
  The OracleDBConsoleORCL service is stopping.............
  The OracleDBConsoleORCL service was stopped successfully.

  C:\> lsnrctl stop

  LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-APR-2009 21:26:17
  Copyright (c) 1991, 2005, Oracle.  All rights reserved.
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jtim)(PORT=1521))
  )
  The command completed successfully

  C:\> sqlplus /nolog

  SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 5 21:27:38 2009
  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
  SQL>

  SQL> conn / as sysdba
  Connected.
  SQL> shutdown abort

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

  apabila ingin shutdown semua service menggunakan windows service :

  C:\> net stop OracleOraDB10g_Home1iSQL*Plus
  C:\> net stop OracleDBConsoleORCL
  C:\> net stop OracleOraDB10g_Home1TNSListener
  C:\> net stop OracleServiceORCL
• Matikan juga service Mirosoft Distributed Transaction Coordinator apabila ada atau berjalan
  C:\> net stop msdtc

  The Distributed Transaction Coordinator service is stopping.
  The Distributed Transaction Coordinator service was stopped successfully.

Upgrade database Oracle 10.2.0.4
Sebelum melakukan upgrade, silahkan baca terlebih dahulu dokumentasi yang telah disertakan di patch upgrade tersebut.

• Jalankan Oracle Universal Installer (oui.exe) dari instalasi oracle 10.2.0.4 di folder install, dan setelah welcome screen muncul, tekan Next :



• Pilih home Oracle oracle yang akan diupgrade (jangan membuat home directory baru lagi) kemudian tekan Next :



• Oracle Installer akan melakukan pengecekan system requirement, tekan Next untuk melanjutkan ke screen berikutnya :



• Masukkan data customer identification number (CSI) anda, kemudian tekan Next :



• Summary installation, tekan Install untuk melakukan instalasi :



• Installasi upgrade patch Oracle telah selesai. Tekan Exit :

Testing Instalasi Upgrade Oracle

• Start database dalam mode upgrade (testing upgrade) :
  C:\>sqlplus /nolog

  SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 6 15:39:00 2009
  Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
  SQL> 

  SQL> conn / as sysdba

  Connected to an idle instance.
  SQL>

  SQL> startup upgrade

  ORACLE instance started.
  Total System Global Area  289406976 bytes
  Fixed Size                  1296308 bytes
  Variable Size             142608460 bytes
  Database Buffers          138412032 bytes
  Redo Buffers                7090176 bytes
  Database mounted.
  Database opened.
  SQL>

• Jalankan script test patch di $ORACLE_HOME\RDBMS\ADMIN\catupgrd.sql dengan spool diaktifkan terlebih dahulu.
  SQL> spool patch.log
  SQL> @D:\oracle\product\10.2.0\RDBMS\ADMIN\catupgrd.sql

  SQL> @D:\oracle\product\10.2.0\RDBMS\ADMIN\catupgrd.sql
  DOC>######################################################################
  DOC>######################################################################
  DOC>	The following statement will cause an "ORA-01722: invalid number"
  DOC>	error if the user running this script is not SYS.  Disconnect
  DOC>	and reconnect with AS SYSDBA.
  DOC>######################################################################
  DOC>######################################################################
  DOC>#
  no rows selected
  DOC>######################################################################
  DOC>######################################################################
  DOC>	The following statement will cause an "ORA-01722: invalid number"
  DOC>	error if the database server version is not correct for this script.
  DOC>	Shutdown ABORT and use a different script or a different server.
  DOC>######################################################################
  DOC>######################################################################
  DOC>#
  no rows selected
  DOC>#######################################################################
  DOC>#######################################################################
  DOC>   The following statement will cause an "ORA-01722: invalid number"
  DOC>   error if the database has not been opened for UPGRADE.
  DOC>
  DOC>   Perform a "SHUTDOWN ABORT"  and
  DOC>   restart using UPGRADE.
  DOC>#######################################################################
  DOC>#######################################################################
  DOC>#
  no rows selected
  DOC>#######################################################################
  DOC>#######################################################################
  DOC>	The following statements will cause an "ORA-01722: invalid number"
  DOC>	error if the SYSAUX tablespace does not exist or is not
  DOC>	ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
  DOC>	SEGMENT SPACE MANAGEMENT AUTO.
  DOC>
  DOC>	The SYSAUX tablespace is used in 10.1 to consolidate data from
  DOC>	a number of tablespaces that were separate in prior releases.
  DOC>	Consult the Oracle Database Upgrade Guide for sizing estimates.
  DOC>
  DOC>	Create the SYSAUX tablespace, for example,
  DOC>
  DOC>	 create tablespace SYSAUX datafile 'sysaux01.dbf'
  DOC>	     size 70M reuse
  DOC>	     extent management local
  DOC>	     segment space management auto
  DOC>	     online;
  DOC>
  DOC>	Then rerun the catupgrd.sql script.
  DOC>#######################################################################
  DOC>#######################################################################
  DOC>#
  no rows selected
  no rows selected
  no rows selected
  no rows selected
  no rows selected
  Session altered.
  Session altered.
  Table created.
  2 rows deleted.
  1 row created.
  Commit complete.
  TIMESTAMP
  ------------------------------------------------------------
  COMP_TIMESTAMP UPGRD__BGN 2009-04-06 16:43:38 2454928 60218
  - - - - - dipotong sampe disini coz panjang bgt - - - -

  Proses ini akan memakan waktu yang lumayan lama bergantung besar database dan resource hardware anda. Apabila proses test sudah selesai periksa terlebih dahulu hasil test tersebut (patch.log) baik berupa error ataupun komponen yang gagal terupdate. Apabila sudah dilakukan perbaikan silahkan dites ulang menggunakan script diatas.

• Restart database ke mode seperti biasa (normal) dan jalankan script $ORACLE_HOME\RDBMS\ADMIN\utlrp.sql untuk mengkompile ulang semua packages yang invalid (optional) :
  SQL> shutdown immediate

  Database closed.
  Database dismounted.
  ORACLE instance shut down.

  SQL> startup

  ORACLE instance started.
  Total System Global Area  289406976 bytes
  Fixed Size                  1296308 bytes
  Variable Size             192940108 bytes
  Database Buffers           88080384 bytes
  Redo Buffers                7090176 bytes
  Database mounted.
  Database opened.

  SQL> @D:\oracle\product\10.2.0\RDBMS\ADMIN\utlrp.sql

  TIMESTAMP
  -------------------------------------------------------------------------------
  COMP_TIMESTAMP UTLRP_BGN  2009-04-06 17:21:12
  DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
  DOC>   objects in the database. Recompilation time is proportional to the
  DOC>   number of invalid objects in the database, so this command may take
  DOC>   a long time to execute on a database with a large number of invalid
  DOC>   objects.
  DOC>
  DOC>   Use the following queries to track recompilation progress:
  DOC>
  DOC>   1. Query returning the number of invalid objects remaining. This
  DOC>      number should decrease with time.
  DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
  DOC>
  DOC>   2. Query returning the number of objects compiled so far. This number
  DOC>      should increase with time.
  DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
  DOC>
  DOC>   This script automatically chooses serial or parallel recompilation
  DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
  DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
  DOC>   On RAC, this number is added across all RAC nodes.
  DOC>
  DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
  DOC>   recompilation. Jobs are created without instance affinity so that they
  DOC>   can migrate across RAC nodes. Use the following queries to verify
  DOC>   whether UTL_RECOMP jobs are being created and run correctly:
  DOC>
  DOC>   1. Query showing jobs created by UTL_RECOMP
  DOC>         SELECT job_name FROM dba_scheduler_jobs
  DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
  DOC>
  DOC>   2. Query showing UTL_RECOMP jobs that are running
  DOC>         SELECT job_name FROM dba_scheduler_running_jobs
  DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
  DOC>#
  TIMESTAMP
  -------------------------------------------------------------------------------
  COMP_TIMESTAMP UTLRP_END  2009-04-06 17:22:10
  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
  SQL>

Proses patch upgrade telah selesai dilakukan silahkan restart seluruh service atau reboot komputer anda.

written by Mas Rony \\ tags: Oracle, oracle 10g, oracle patch, oracle update, oracle upgrade