Here are steps to make a connection from Oracle to SQL Server using Oracles' heterogeneous Services ODBC agent.
Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. Step-by-Step setup to query from a SQL Server database.
1. Define a Data Source Name (DSN) for SQL Server
--> The first step is to define a system DSN within the Windows ODBC Data Sources.
a.From the start menu click on Settings -> Control Panel and double click the ODBC icon.
b.Click on the System DSN tab and then click the Add button.
c.Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.
d.Key in any name you would like to reference this ODBC data source. I have chose SS for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my remote SQL Server that I will be connecting to. Click Next to continue.
e.I selected Sql Server authentication and also provided Login ID and Password below. Then click Next to continue.
f.Typically, this window is populated with the default SQL Server database of "master." Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the my test Remshr database. Click Next to continue.
g.I also left current window alone and clicked Finish.
h.The next window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.
i.TEST COMPLETED SUCCESSFULLY appears, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.
j.The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link to activate the DSN. Click OK to exit the DSN administrator.
2. Create a Heterogeneous Services Initialization File
Oracle has provided a sample heterogeneous services init file within the %ORACLE_HOME\hs\admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name that corresponds to my DSN name.
%ORACLE_HOME\hs\admin\inithsodbc.ora sample file
(ORACLE_HOME=D:\oracle\product\10.1.0\Db_1)
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = (odbc data_source_name)
HS_FDS_TRACE_LEVEL = (trace_level)
#
# Environment variables required for the non-Oracle system
#
#set (env var)=(value)
%ORACLE_HOME\hs\admin\initSS.ora altered file
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SS
HS_FDS_TRACE_LEVEL = OFF
3. Alter your listener.ora file
Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..
Created my own listener name of LISTENERSS
Changed the Port number to 1522
Changed the SID_NAME to my DSN (SS)
Changed the ORACLE_HOME location
Changed the PROGRAM to hsodbc
%ORACLE_HOME\network\admin\listener.ora altered file
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)
LISTENERSS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENERSS=
(SID_LIST=
(SID_DESC=
(SID_NAME=SS)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM=hsodbc)
)
)
4. Alter your tnsnames.ora file
Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.
1. I made four distinct changes.Created a TNS entry named SS
2. Changed the Port number to 1522
3. Changed the SID to my DSN (SS)
4. Added OK to the HS= parameter
%ORACLE_HOME\network\admin\tnsnames.ora altered file
SS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SS))
(HS=OK)
)
5. Start the new Listener
This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.
D:\>lsnrctl start listenerss
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 16:54:55
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Log messages written to d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias listenerss
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 15-FEB-2008 16:55:09
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Services Summary...
Service "SS" has 1 instance(s).
Instance "SS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
6. Validate the connection to your DSN
You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.
D:\>tnsping ss
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 17:14:57
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=SS)) (HS=OK))
OK (10 msec)
7. Create a Database Link within Your Oracle Database
Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.
SQL> create database link ss
connect to sa identified sa by using 'SS';
Database link created.
This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.
SQL> desc tblemp@ss
Name Null? Type
----------------------------------------- -------- ----------------------------
EmpNo NOT NULL VARCHAR2(4)
EmpInit NOT NULL VARCHAR2(4)
EmpName VARCHAR2(50)
Salutation VARCHAR2(4)
Nationality VARCHAR2(15)
Sex VARCHAR2(1)
DOB DATE
DOW DATE
Qualification VARCHAR2(15)
-------
-------
-------
SQL>select "EmpNo","EmpInit","EmpName" from tblemp@ss;
Source of this article:
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1
Saturday, February 16, 2008
Wednesday, February 06, 2008
How to apply a patch ?
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar cvf - $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
How to Rollback a patch?
opatch rollback -id (Patch Number)
I messed up my Oracle home/ opatch rollback is failing , what to do now?
Remember, we took the backup of the ORACLE_HOME and main importantly the Oracle inventory, you can just restore (uncompress) the software and the inventory and you are all set. Also for fixing the corrupt inventory, please refer to this OracleMetaLink Note:298906.1
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar cvf - $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
How to Rollback a patch?
opatch rollback -id (Patch Number)
I messed up my Oracle home/ opatch rollback is failing , what to do now?
Remember, we took the backup of the ORACLE_HOME and main importantly the Oracle inventory, you can just restore (uncompress) the software and the inventory and you are all set. Also for fixing the corrupt inventory, please refer to this OracleMetaLink Note:298906.1
Critical Patch Update JAN 2008 Linux
Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory
Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.
Patch Installation Procedures for Oracle Database Release 11.1.0.6.0
Patch Number : p6646866_111060_LINUX.zip
Download it from OracleMetaLink
SYSTEM Overview
Platforms : Oracle Enterprise Linux 5
Database : 11.1.0.6.0
Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
OPatch Utility Information
You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from
OracleMetaLink.
Check OPatch Version
Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608, select the Oracle RDBMS version and download the patch.
[oracle@oracle11gr1]$ cd $ORACLE_HOME
[oracle@oracle11gr1 db_1]$ cd OPatch/
[oracle@oracle11gr1 OPatch]$ sh opatch version
Invoking OPatch 11.1.0.6.2
OPatch Version: 11.1.0.6.2
OPatch succeeded.
[oracle@oracle11gr1 OPatch]$ sh opatch napply /u01/app/oracle/product/11.1.0/db_1/6646866/ -skip_subset -skip_duplicate
Invoking OPatch 11.1.0.6.2
[Output Edited]
OPatch continues with these patches: 6646866 6650132 6650135 6731395
Do you want to proceed? [y|n]
Y
User Responded with: Y
Running prerequisite checks...
[Output Edited]
Is the local system ready for patching? [y|n]
Y
User Responded with: Y
[Output Edited]
Execution of 'sh /u01/app/oracle/product/11.1.0/db_1/6646866/6646866/custom/scripts/post -apply 6646866 ':
Return Code = 0
The local system has been patched and can be restarted.
UtilSession: N-Apply done.
OPatch succeeded.
Post Installation Instructions
[oracle@oracle11gr1 OPatch]$
After installing the patch, perform the following actions:
Loading Modified .sql Files into the Database
There are no steps to perform, because the CPU for Release 11.1.0.6 does not use catcpu.sql or related scripts.
You can go directly to Recompiling Views in the Database
You must recompile views for all databases except the following:
Databases created with Release 11.1.0.6 or later
Databases created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied
The time required to recompile the views and related objects depends on the total number of objects and on your system configuration. In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes.
If you want to check whether view recompilation has already been performed for the database, execute the following statement.
SELECT * FROM registry$history where ID = '6452863';
[oracle@oracle11gr1]$ sqlplus /nolog
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL>
SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected
If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.
To recompile the views in the database, follow these steps:
-->Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
Running precheck.sql...
Number of views to be recompiled :2715
-----------------------------------------------------------------------
Number of objects to be recompiled :5523
Please follow the README.txt instructions for running viewrecomp.sql
PL/SQL procedure successfully completed.
The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.
-->Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
No. of Invalid Objects is :31
Please refer to README.html to for instructions on validating these objects
PL/SQL procedure successfully completed.
Logfile for the current viewrecomp.sql session is : vcomp_ORCL_04Feb2008_16_10_52.log
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> exit
If any invalid objects were reported, run the utlrp.sql script as follows:
[oracle@oracle11gr1 view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle11gr1 admin]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-04 16:18:22
[Output Edited]
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-04 16:18:50
PL/SQL procedure successfully completed.
[Output Edited]
OBJECTS WITH ERRORS
-------------------
0
[Output Edited]
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> SELECT * FROM registry$history where ID = '6452863';
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
04-FEB-08 04.13.43.702093 PM CPU 6452863 CPUJan2008
Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;
OracleCPU Advisory
Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.
Patch Installation Procedures for Oracle Database Release 11.1.0.6.0
Patch Number : p6646866_111060_LINUX.zip
Download it from OracleMetaLink
SYSTEM Overview
Platforms : Oracle Enterprise Linux 5
Database : 11.1.0.6.0
Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
OPatch Utility Information
You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from
OracleMetaLink.
Check OPatch Version
Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608, select the Oracle RDBMS version and download the patch.
[oracle@oracle11gr1]$ cd $ORACLE_HOME
[oracle@oracle11gr1 db_1]$ cd OPatch/
[oracle@oracle11gr1 OPatch]$ sh opatch version
Invoking OPatch 11.1.0.6.2
OPatch Version: 11.1.0.6.2
OPatch succeeded.
[oracle@oracle11gr1 OPatch]$ sh opatch napply /u01/app/oracle/product/11.1.0/db_1/6646866/ -skip_subset -skip_duplicate
Invoking OPatch 11.1.0.6.2
[Output Edited]
OPatch continues with these patches: 6646866 6650132 6650135 6731395
Do you want to proceed? [y|n]
Y
User Responded with: Y
Running prerequisite checks...
[Output Edited]
Is the local system ready for patching? [y|n]
Y
User Responded with: Y
[Output Edited]
Execution of 'sh /u01/app/oracle/product/11.1.0/db_1/6646866/6646866/custom/scripts/post -apply 6646866 ':
Return Code = 0
The local system has been patched and can be restarted.
UtilSession: N-Apply done.
OPatch succeeded.
Post Installation Instructions
[oracle@oracle11gr1 OPatch]$
After installing the patch, perform the following actions:
Loading Modified .sql Files into the Database
There are no steps to perform, because the CPU for Release 11.1.0.6 does not use catcpu.sql or related scripts.
You can go directly to Recompiling Views in the Database
You must recompile views for all databases except the following:
Databases created with Release 11.1.0.6 or later
Databases created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied
The time required to recompile the views and related objects depends on the total number of objects and on your system configuration. In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes.
If you want to check whether view recompilation has already been performed for the database, execute the following statement.
SELECT * FROM registry$history where ID = '6452863';
[oracle@oracle11gr1]$ sqlplus /nolog
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL>
SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected
If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.
To recompile the views in the database, follow these steps:
-->Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
Running precheck.sql...
Number of views to be recompiled :2715
-----------------------------------------------------------------------
Number of objects to be recompiled :5523
Please follow the README.txt instructions for running viewrecomp.sql
PL/SQL procedure successfully completed.
The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.
-->Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
No. of Invalid Objects is :31
Please refer to README.html to for instructions on validating these objects
PL/SQL procedure successfully completed.
Logfile for the current viewrecomp.sql session is : vcomp_ORCL_04Feb2008_16_10_52.log
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> exit
If any invalid objects were reported, run the utlrp.sql script as follows:
[oracle@oracle11gr1 view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle11gr1 admin]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-04 16:18:22
[Output Edited]
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-04 16:18:50
PL/SQL procedure successfully completed.
[Output Edited]
OBJECTS WITH ERRORS
-------------------
0
[Output Edited]
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> SELECT * FROM registry$history where ID = '6452863';
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
04-FEB-08 04.13.43.702093 PM CPU 6452863 CPUJan2008
Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;
Critical Patch Update JAN 2008 on Windows
Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory
Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.
Patch Installation Procedures for Oracle Database Release 10.1.0.5
Patch Number : p6637274_10105_WINNT.zip
SYSTEM Overview
Platforms : Windows XP SP2
Database : 10.1.0.5.0
Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
OPatch Utility Information
You must use the OPatch utility release 1.0.0.0.53 or later. You can download it from
OracleMetaLink with patch 2617419.
Check Oracle Inventory Setup
Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608 , select the Oracle RDBMS version and download the patch.
set ORACLE_SID=ARIES
set ORACLE_HOME=D:\oracle\product\10.1.0\Db_1
set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%
D:\patchset10gR1\6637274>cd D:\oracle\product\10.1.0\db_1\opatch
D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__02-05-2008_10-57-56.log"
Result:
There is no Interim Patch
OPatch succeeded.
OPatch returns with error code = 0
D:\oracle\product\10.1.0\Db_1\OPatch>
==================
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\patchset10gR1\6637274
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\6637274\Apply_6637274_02-05-2008_11-06-12.log"
[Output Edited]
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\patchset10gR1\6637274\custom\scripts\pre.bat)...
Applying patch 6637274...
[Output Edited]
Updating inventory...
Backing up comps.xml ...
********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Executing the Apply post-patch script (D:\patchset10gR1\6637274\custom\scripts\post.bat)...
OPatch succeeded.
OPatch returns with error code = 0
D:\oracle\product\10.1.0\Db_1\OPatch>
====================================
CD D:\oracle\product\10.1.0\db_1\CPU\cpujan2008
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$instance;
INSTANCE_NAME
---------------
aries
SQL> shutdown immediate;
SQL> STARTUP MIGRATE
ORACLE instance started.
Database opened.
SQL>@CATCPU.SQL
[OUTPUT EDITED]
Package body created.
Package body created.
Package body created.
Session altered.
1 row selected.
Session altered.
OWA_MESSAGE
--------------------------------------------------------------------------------
Installed OWA version is: 9.0.4.0.2
Backing up previous OWA packages to: MODPLSQL\owa_restore_aries.sql
OWA_MESSAGE
-------------------
OWALOAD File: MODPLSQL\owa_all\90\owaload_patch.sql
Logfile: MODPLSQL\owa_patch_aries.log
OWA_DBG_MSG
-------------------
Installed OWA version is: 9.0.4.0.2;
Shipped OWA version is : 9.0.4.0.6;
OWA packages v9.0.4.0.6 will be installed into your database v101050
Will install owadummy.sql and owacomm.sql
[Output Edited]
No errors.
No. of Invalid Objects is :115
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ARIES_05Feb2008_11_53_51.log
not spooling currently
SQL> SHUTDOWN IMMEDIATE
SQL> exit
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>
If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows
cd D:\oracle\product\10.1.0\db_1\rdbms\admin
D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL> @utlprp.sql 0
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-05 12:08:43
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-05 12:08:43
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';
no rows selected
SQL> exit
Run the CATCPU.sql and UTLRP.sql on all the instances in the ORACLE_HOME individually.
To view the details about the patch applied :
select * from dba_registry_history;
SELECT * FROM registry$history where ID = '6452863';
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
05-FEB-08 12.30.36.406000 PM CPU 6637274 CPUJan2008
Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;
OracleCPU Advisory
Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.
Patch Installation Procedures for Oracle Database Release 10.1.0.5
Patch Number : p6637274_10105_WINNT.zip
SYSTEM Overview
Platforms : Windows XP SP2
Database : 10.1.0.5.0
Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
OPatch Utility Information
You must use the OPatch utility release 1.0.0.0.53 or later. You can download it from
OracleMetaLink with patch 2617419.
Check Oracle Inventory Setup
Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608 , select the Oracle RDBMS version and download the patch.
set ORACLE_SID=ARIES
set ORACLE_HOME=D:\oracle\product\10.1.0\Db_1
set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%
D:\patchset10gR1\6637274>cd D:\oracle\product\10.1.0\db_1\opatch
D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__02-05-2008_10-57-56.log"
Result:
There is no Interim Patch
OPatch succeeded.
OPatch returns with error code = 0
D:\oracle\product\10.1.0\Db_1\OPatch>
==================
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\patchset10gR1\6637274
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\6637274\Apply_6637274_02-05-2008_11-06-12.log"
[Output Edited]
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\patchset10gR1\6637274\custom\scripts\pre.bat)...
Applying patch 6637274...
[Output Edited]
Updating inventory...
Backing up comps.xml ...
********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Executing the Apply post-patch script (D:\patchset10gR1\6637274\custom\scripts\post.bat)...
OPatch succeeded.
OPatch returns with error code = 0
D:\oracle\product\10.1.0\Db_1\OPatch>
====================================
CD D:\oracle\product\10.1.0\db_1\CPU\cpujan2008
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$instance;
INSTANCE_NAME
---------------
aries
SQL> shutdown immediate;
SQL> STARTUP MIGRATE
ORACLE instance started.
Database opened.
SQL>@CATCPU.SQL
[OUTPUT EDITED]
Package body created.
Package body created.
Package body created.
Session altered.
1 row selected.
Session altered.
OWA_MESSAGE
--------------------------------------------------------------------------------
Installed OWA version is: 9.0.4.0.2
Backing up previous OWA packages to: MODPLSQL\owa_restore_aries.sql
OWA_MESSAGE
-------------------
OWALOAD File: MODPLSQL\owa_all\90\owaload_patch.sql
Logfile: MODPLSQL\owa_patch_aries.log
OWA_DBG_MSG
-------------------
Installed OWA version is: 9.0.4.0.2;
Shipped OWA version is : 9.0.4.0.6;
OWA packages v9.0.4.0.6 will be installed into your database v101050
Will install owadummy.sql and owacomm.sql
[Output Edited]
No errors.
No. of Invalid Objects is :115
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ARIES_05Feb2008_11_53_51.log
not spooling currently
SQL> SHUTDOWN IMMEDIATE
SQL> exit
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>
If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows
cd D:\oracle\product\10.1.0\db_1\rdbms\admin
D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL> @utlprp.sql 0
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-05 12:08:43
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-05 12:08:43
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';
no rows selected
SQL> exit
Run the CATCPU.sql and UTLRP.sql on all the instances in the ORACLE_HOME individually.
To view the details about the patch applied :
select * from dba_registry_history;
SELECT * FROM registry$history where ID = '6452863';
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
05-FEB-08 12.30.36.406000 PM CPU 6637274 CPUJan2008
Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;
Subscribe to:
Posts (Atom)
