Friday, April 15, 2011

How to Patch/Upgrade Oracle Database 9i ?

This will guide you to patch or upgrade oracle 9.2.0.1.0 to 9.2.0.8.0. If you don't know which patchset number is this, for your help, it is 4547809. Download it from oracle support Link.

  1. After download extract the zip file.
  2. Stop all running oracle services.
  3. double click on setup.exe under extract file/disk1
  4. follow the on screen instruction. for example
  5. On the Welcome screen, click Next.
  6. On the Specify File Locations screen, click Browse next to the Path field in the Source section.
  7. Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. For example:
Oracle_patch\stage\products.xml
  1. In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
  2. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.
10.  After installation complete, Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

  1. Use SQL*Plus to login to the database as the SYS user with SYSDBA privileges:
12. C:\> sqlplus /NOLOG
13. SQL> CONNECT SYS/SYS_password AS SYSDBA

  1. Start the database:
15. SQL> STARTUP

  1. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
17. SQL> SHOW PARAMETER PFILE;

This command displays the name and location of the server parameter file or the initialization parameter file.
  1. Determine the current values of these parameters:
19. SQL> SHOW PARAMETER SHARED_POOL_SIZE
20. SQL> SHOW PARAMETER JAVA_POOL_SIZE

  1. If the system is using a server parameter file:
    1. If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
b.    SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

    1. If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
d.    SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

  1. If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
  2. Shut down the database:
24. SQL> SHUTDOWN


Enter the following SQL*Plus commands:

1.    SQL> STARTUP MIGRATE
2.    SQL> SPOOL patch.log
3.    SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catpatch.sql
4.    SQL> SPOOL OFF

  1. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
  2. This list provides the version and status of each SERVER component in the database.
  3. If necessary, rerun the catpatch.sql script after correcting any problems.
  4. Restart the database:
9.    SQL> SHUTDOWN
10. SQL> STARTUP
  1. Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
12. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql


U’r Done…..

Tuesday, April 12, 2011

Display current date

go to column properties then set initial value $$date$$ for user machine sysdate or use $$dbdate$$ for server machine sysdate.

If need sysdate with time then write $$datetime$$ or $$dbdatetime$$