【お問合せ内容】
以下の内容についてご教授お願い致します。
------------------------------------------------------------------
「Oracle非対話形式インストールについて」
現在複数のマシンにOracleクライアント製品をインストールする必要が
あります。その際に以下の手順でインストールを行なう事を考えております。
手順１．インストール用のレスポンス・ファイルを作成
インストーラから-record オプションを使用して作成する。
手順２．作成したレスポンスファイルを使用して各クライアントに
非対話形式モードでインストールを行なう。
上記手順で行なう際に以下の点についてお教え下さい。
Ｑ１．作成したレスポンス・ファイルを使用して各クライアントに
製品をインストールする際に Oracle Universal Installerが
必要になるかと思います。その際に共有マシンにOUIをイン
ストールし、各クライアントからアクセスする手法で問題
ないでしょうか？
Ｑ２．またレスポンス・ファイルの以下のパラメータの指定が
作成後はインストールＣＤの products.jarファイルのパスが
指定されていますが、クライアントにインストールする際は
共有にインストールＣＤを一式コピーし、その products.jar
ファイルのパスを指定すれば良いのでしょうか？
(添付:ora920.rsp）
#パラメータ: FROM_LOCATION
Ｑ３．KROWNの文書番号：63262に
「-record オプションで作成したレスポンス・ファイルで
インストールするとサマリー画面で製品数が減少する。」
の事例がありました。
OUI 2.2.0.18.0を使用すれば上記内容は回避するのでしょうか？
環境情報
・WindowsXP
・Oracle9i 9.2.0.1
・Oracle Universal Installer 2.2.0.12.0
<回答内容>
弊社でテストを行いましたが、OUI2.2.0.18を利用して9.2.0.1クライアントを
インストールする時不具合がございます。
ご要望することを実現するために、PSR9206のOUIを利用してEnglishOS上で作成した
レスポンスファイルとPSR9206の中のOUIを利用します。
添付の中にe9206.rspはご要望によって、Oracle netとsql*plusのみインストールします。（※1）
PSR9206のOUIのみインストール方法をご提供されませんので、
毎クライアントでインストールした後に再利用できません。
替わりにPSR9206を共有して、サーバから利用します。
操作手順：
1.PSR9206をdownloadして、unzipした後に共有ディスクに格納します。
2.添付ファイルe9206.rspをdownloadして、共有ディスクに格納します。
3.Oracle9.2.0.1のCDをコピーして順番的に共有ディスクに格納します。
4.e9206.rspを編集して、ご要望によって、FROM_LOCATION、ORACLE_HOME、
ORACLE_HOME_NAMEパラメータの修正がある程度有ります。
ダブルクォーテーションマークの利用をご注意ください。
5.サイレントインストールを起動します：
例： Z:\p3948480_9206_WINNT\Disk1>setup -silent -responseFile Z:\e9206.rsp
6.インストールが成功するかどうかをinstallActions.logとsilentInstall.logで確認します。
※1：ご要望したコンポーネントを変更する場合に、EnglishOS上でレスポンスファイルを再作成します。
又は、弊社とご連絡いただけませんでしょうか。
参考情報：
-----------------------------------------------------------------------------------------------
MetaLink#123022.1 Silent Installation Fails: No Forced Value Specified for Variable ORACLE_HOME
MetaLink#3571124 SILENT INSTALLATION FAILS WITH ''NO FORCED VALUE SPECIFIED FOR THE VARIABLE''
-----------------------------------------------------------------------------------------------
Sunday, December 24, 2006
Viewing All Indexes Being Monitored Under Another User's Schema
PURPOSE
To show how to view all indexes being monitored.
SCOPE & APPLICATION
Instructional.
Viewing All Indexes Being Monitored Under Another User's Schema:
=================================
V$OBJECT_USAGE does not display rows for all indexes in the database whose
usage is being monitored.
'ALTER INDEX MONITORING USAGE' places an entry in V$OBJECT_USAGE for
that particular index to help determine if the index is being used or not. The
V$OBJECT_USAGE view uses the username logged into database when the 'ALTER
INDEX MONITORING USAGE' is issued. This will not enable any user other
than the user who issued the 'ALTER INDEX MONITORING USAGE' to view if
index is being monitored or not.
The view structure may be changed slightly (see below) in order to expand its
scope system-wide (see below) so that you may see all indexes being monitored.
For example:
Showing User Scott monitoring his Index on EMP table:
SQL> connect scott/tiger
SQL> set LONG 30000
SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';
TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
SQL> select index_name, table_name, uniqueness, status from user_indexes
where table_name = 'EMP';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
PK_EMP EMP UNIQUE VALID
SQL> alter index PK_EMP monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35
Then connect as another user to view indexes being monitored:
SQL> connect / as sysdba;
Connected.
SQL> select * from v$object_usage;
no rows selected
To be able to view them do the following:
SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
View created.
SQL> select * from v$all_object_usage;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
SCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35
Related Documents:
==================
Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring
Index Usage)
Note 144070.1 Identifying unused indexes in Oracle9i
To show how to view all indexes being monitored.
SCOPE & APPLICATION
Instructional.
Viewing All Indexes Being Monitored Under Another User's Schema:
=================================
V$OBJECT_USAGE does not display rows for all indexes in the database whose
usage is being monitored.
'ALTER INDEX
that particular index to help determine if the index is being used or not. The
V$OBJECT_USAGE view uses the username logged into database when the 'ALTER
INDEX
than the user who issued the 'ALTER INDEX
index is being monitored or not.
The view structure may be changed slightly (see below) in order to expand its
scope system-wide (see below) so that you may see all indexes being monitored.
For example:
Showing User Scott monitoring his Index on EMP table:
SQL> connect scott/tiger
SQL> set LONG 30000
SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';
TEXT
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
SQL> select index_name, table_name, uniqueness, status from user_indexes
where table_name = 'EMP';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
PK_EMP EMP UNIQUE VALID
SQL> alter index PK_EMP monitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
PK_EMP EMP YES NO 10/12/2001 06:42:35
Then connect as another user to view indexes being monitored:
SQL> connect / as sysdba;
Connected.
SQL> select * from v$object_usage;
no rows selected
To be able to view them do the following:
SQL> create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
View created.
SQL> select * from v$all_object_usage;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
SCOTT PK_EMP EMP YES NO 10/12/2001 06:42:35
Related Documents:
==================
Oracle9i Database Administrator's Guide Volume 1 (Managing Indexes: Monitoring
Index Usage)
Note 144070.1 Identifying unused indexes in Oracle9i
Wednesday, December 20, 2006
responseFile, remote, installation
「Oracle非対話形式インストールについて」
現在複数のマシンにOracleクライアント製品をインストールする必要が
あります。その際に以下の手順でインストールを行なう事を考えております。
手順１．インストール用のレスポンス・ファイルを作成
インストーラから-record オプションを使用して作成する。
手順２．作成したレスポンスファイルを使用して各クライアントに
非対話形式モードでインストールを行なう。
上記手順で行なう際に以下の点についてお教え下さい。
Ｑ１．作成したレスポンス・ファイルを使用して各クライアントに
製品をインストールする際に Oracle Universal Installerが
必要になるかと思います。その際に共有マシンにOUIをイン
ストールし、各クライアントからアクセスする手法で問題
ないでしょうか？
Ｑ２．またレスポンス・ファイルの以下のパラメータの指定が
作成後はインストールＣＤの products.jarファイルのパスが
指定されていますが、クライアントにインストールする際は
共有にインストールＣＤを一式コピーし、その products.jar
ファイルのパスを指定すれば良いのでしょうか？
(添付:ora920.rsp）
#パラメータ: FROM_LOCATION
Ｑ３．KROWNの文書番号：63262に
「-record オプションで作成したレスポンス・ファイルで
インストールするとサマリー画面で製品数が減少する。」
の事例がありました。
OUI 2.2.0.18.0を使用すれば上記内容は回避するのでしょうか？
<回答内容>
Ｑ１．共有マシンでインストールしたOUIを利用できません。インストールscourse filesのOUIのみを利用できます。（※1）
弊社でインストールscourse filesのOUIでテストしましたが、問題がございません。
Ｑ２．弊社でテストしましたが、問題がございません。
Ｑ３．OUI 2.2.0.18.0を利用してKROWN#63262で記載した内容が回避できます。
注意事項：お客様がresponsefileを作成する時、2.2.0.18.0のOUIの必要があります。
※1： リモートからインストールしたOUIを利用する時、「java runtime envirronment was not found」が出ました。
インストールＣＤのOUI又はPSR付けているOUIをdiskへコピーして利用します。上記の問題が出ません。
なお、もう一点をご提示させていただけませんでしょうか。
Oracle側にはCDの場所を探す仕組みが二つございます。
1.Location_For_Disk{DiskNumber} パラメータ
例:
LOCATION_FOR_DISK2="F:\teststage\cd\Disk2"
2.Location_For_Disk{DiskNumber} パラメータの値がない又は必要なファイルが存在しない場合に
Oracleが../../Disk{DiskNumer}/stage (from products.jar)下で探します。
だがら、レスポンス・ファイルで探す場所を指定します。または
インストールＣＤの内容を diskへコピーします。
例:
E:/Disk1/stage
E:/Disk2/stage
KEY WORD: responseFile remote installation
現在複数のマシンにOracleクライアント製品をインストールする必要が
あります。その際に以下の手順でインストールを行なう事を考えております。
手順１．インストール用のレスポンス・ファイルを作成
インストーラから-record オプションを使用して作成する。
手順２．作成したレスポンスファイルを使用して各クライアントに
非対話形式モードでインストールを行なう。
上記手順で行なう際に以下の点についてお教え下さい。
Ｑ１．作成したレスポンス・ファイルを使用して各クライアントに
製品をインストールする際に Oracle Universal Installerが
必要になるかと思います。その際に共有マシンにOUIをイン
ストールし、各クライアントからアクセスする手法で問題
ないでしょうか？
Ｑ２．またレスポンス・ファイルの以下のパラメータの指定が
作成後はインストールＣＤの products.jarファイルのパスが
指定されていますが、クライアントにインストールする際は
共有にインストールＣＤを一式コピーし、その products.jar
ファイルのパスを指定すれば良いのでしょうか？
(添付:ora920.rsp）
#パラメータ: FROM_LOCATION
Ｑ３．KROWNの文書番号：63262に
「-record オプションで作成したレスポンス・ファイルで
インストールするとサマリー画面で製品数が減少する。」
の事例がありました。
OUI 2.2.0.18.0を使用すれば上記内容は回避するのでしょうか？
<回答内容>
Ｑ１．共有マシンでインストールしたOUIを利用できません。インストールscourse filesのOUIのみを利用できます。（※1）
弊社でインストールscourse filesのOUIでテストしましたが、問題がございません。
Ｑ２．弊社でテストしましたが、問題がございません。
Ｑ３．OUI 2.2.0.18.0を利用してKROWN#63262で記載した内容が回避できます。
注意事項：お客様がresponsefileを作成する時、2.2.0.18.0のOUIの必要があります。
※1： リモートからインストールしたOUIを利用する時、「java runtime envirronment was not found」が出ました。
インストールＣＤのOUI又はPSR付けているOUIをdiskへコピーして利用します。上記の問題が出ません。
なお、もう一点をご提示させていただけませんでしょうか。
Oracle側にはCDの場所を探す仕組みが二つございます。
1.Location_For_Disk{DiskNumber} パラメータ
例:
LOCATION_FOR_DISK2="F:\teststage\cd\Disk2"
2.Location_For_Disk{DiskNumber} パラメータの値がない又は必要なファイルが存在しない場合に
Oracleが../../Disk{DiskNumer}/stage (from products.jar)下で探します。
だがら、レスポンス・ファイルで探す場所を指定します。または
インストールＣＤの内容を diskへコピーします。
例:
E:/Disk1/stage
E:/Disk2/stage
KEY WORD: responseFile remote installation
Monday, December 18, 2006
How to Recover a Database Having Added a Datafile Since Last Backup(10g)
最后一次备份数据库之后，又添加了数据文件，崩溃以后如果恢复
1.最新的控制文件
（1）rman
restore database
recover database
（2）sqlplus
create the datafile（alter database create datafile XXXX）
recover database
2.备份的旧的控制文件
(1)rman
restore controlfile from autobackup;
restore database
recover database
（2）sqlplus
restore from backup set(including the old controlfile)
recover database
create the datafile when error prompts you
-------------------------------------------------------------------------
ORA-00283: Recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 5: '/u01/app//oracle/oradata/testtbs.dbf'
Recreate the missing datafile. To do this, select the relevant filename
from v$datafile:
SQL> select name from v$datafile where file#=5;
NAME
-------------------------------------------------------
UNNAMED0005
Now recreate the file:
SVRMGR> alter database create datafile
2> 'UNNAMED0005'
3> as
4> '/u01/app//oracle/oradata/testtbs.dbf';
-------------------------------------------------------------------------
restart the recovery of database
1.最新的控制文件
（1）rman
restore database
recover database
（2）sqlplus
create the datafile（alter database create datafile XXXX）
recover database
2.备份的旧的控制文件
(1)rman
restore controlfile from autobackup;
restore database
recover database
（2）sqlplus
restore from backup set(including the old controlfile)
recover database
create the datafile when error prompts you
-------------------------------------------------------------------------
ORA-00283: Recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 5: '/u01/app//oracle/oradata/testtbs.dbf'
Recreate the missing datafile. To do this, select the relevant filename
from v$datafile:
SQL> select name from v$datafile where file#=5;
NAME
-------------------------------------------------------
UNNAMED0005
Now recreate the file:
SVRMGR> alter database create datafile
2> 'UNNAMED0005'
3> as
4> '/u01/app//oracle/oradata/testtbs.dbf';
-------------------------------------------------------------------------
restart the recovery of database
Sunday, December 17, 2006
How to Relink Oracle Database Software on UNIX
(COPIED from metalink)
Subject: How to Relink Oracle Database Software on UNIX
Doc ID: Note:131321.1 Type: BULLETIN
Last Revision Date: 06-OCT-2006 Status: PUBLISHED
PURPOSE
-------
Provide relinking instructions for Oracle Database software
on UNIX platforms.
SCOPE & APPLICATION
-------------------
Anyone who maintains Oracle RDBMS software on a UNIX platform.
Relinking Oracle
================
Background:
Applications for UNIX are generally not distributed as complete executables.
Oracle, like many application vendors who create products for UNIX, distribute
individual object files, library archives of object files, and some source
files which then get ?relinked? at the operating system level during
installation to create usable executables. This guarantees a reliable
integration with functions provided by the OS system libraries.
Relinking occurs automatically under these circumstances:
- An Oracle product has been installed with an Oracle provided installer.
- An Oracle patch set has been applied via an Oracle provided installer.
The following information has been added to the 'Certify' section of Metalink:
General Notes For Oracle Database - Enterprise Edition:
O/S Information:
The vendors guarantee operating system binary compatibility; therefore, no
reinstall or relink of the Oracle software is required when upgrading these
operating systems unless specifically stated otherwise.
Historically, relinking Oracle manually was suggested under these circumstances:
- An OS upgrade has occurred.
- A change has been made to the OS system libraries. This can occur during
the application of an OS patch.
- A new install failed during the relinking phase.
- Individual Oracle executables core dump during initial startup.
- An individual Oracle patch has been applied (however, explicit relink
instructions are usually either included in the README or integrated into
the patch install script)
Customers who want to (or have been advised to) manually relink should follow
the procedure below.
[Step 1] Log into the UNIX system as the Oracle software owner
==============================================================================
Typically this is the user 'oracle'.
[STEP 2] Verify that your $ORACLE_HOME is set correctly:
===============================================================================
For all Oracle Versions and Platforms, perform this basic environment check
first:
% cd $ORACLE_HOME
% pwd
...Doing this will ensure that $ORACLE_HOME is set correctly in your current
environment.
[Step 3] Verify and/or Configure the UNIX Environment for Proper Relinking:
===============================================================================
For all Oracle Versions and UNIX Platforms:
The Platform specific environment variables LIBPATH, LD_LIBRARY_PATH, &
SHLIB_PATH typically are already set to include system library locations like
'/usr/lib'. In most cases, you need only check what they are set to first,
then add the $ORACLE_HOME/lib directory to them where appropriate.
i.e.: % setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
(see Note 131207.1 How to Set UNIX Environment Variables for help with
setting UNIX environment variables)
If on AIX with:
--------------
Oracle 7.3.X:
- Set LIBPATH to include $ORACLE_HOME/lib
Oracle 8.0.X:
- Set LIBPATH to include $ORACLE_HOME/lib
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib and
$ORACLE_HOME/network/lib (Required when using Oracle products that
use Java)
- Set LINK_CNTRL to L_PTHREADS_D7 if using AIX 4.3. ('oslevel' verifies
OS version)
Oracle 8.1.X, 9.X.X or 10.X.X:
- For 8.1.5, set LINK_CNTRL to L_PTHREADS_D7
- If not 8.1.5, ensure that LINK_CNTRL is not set
- Set LIBPATH to include $ORACLE_HOME/lib
If on DATA GENERAL AVIION (DG) with:
-----------------------------------
Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- ensure TARGET_BINARY_INTERFACE is unset
Oracle 8.1.X:
- Set LD_LIBRARY_PATH to include
$ORACLE_HOME/lib:$ORACLE_HOME/JRE/lib/PentiumPro/native_threads
If on HP-UX with:
----------------
Oracle 7.3.X, 8.0.X, 8.1.X;
- Set SHLIB_PATH to $ORACLE_HOME/lib
If using 64bit 8i Oracle, also
- Set LD_LIBRARY_PATH to $ORACLE_HOME/lib64
- ensure LPATH is unset
Oracle 9.X.X or 10.X.X;
- ensure LPATH is unset
If on NCR with:
--------------
Oracle 7.3.X, 8.0.X or 8.1.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib:/usr/ccs/lib
If on SCO UNIXware with:
-----------------------
Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
Oracle 8.1.X:
- Set LD_LIBRARY_PATH to include
$ORACLE_HOME/lib:$ORACLE_HOME/JRE/lib/x86at/native_threads
If on SGI with:
--------------
32bit Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- Set SGI_ABI to -32
64bit Oracle 8.0.X or 8.1.X (8i is only available in 64bit):
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- Set SGI_ABI to -64
- If one does not already exist, create the file compiler.defaults and
set the COMPILER_DEFAULTS_PATH variable:
In the Oracle software owner's $HOME directory, create a file called
'compiler.defaults':
% cd $HOME
% echo "-DEFAULT:abi=64:isa=mips3:proc=r10k" > compiler.defaults
Then set the environment variable COMPILER_DEFAULTS_PATH to point to the
$HOME directory.
% setenv COMPILER_DEFAULTS_PATH $HOME
If this is not set, relinking will fail because the compiler defaults to
MIPS4 objects although Oracle requires MIPS3.
- Set LD_LIBRARY64_PATH to include the $ORACLE_HOME/lib and the
$ORACLE_HOME/javavm/admin directories.
- Set LD_LIBRARYN32_PATH to include the $ORACLE_HOME/lib32 directory.
NOTE: LD_LIBRARY64_PATH & LD_LIBRARYN32_PATH must be undefined when
installing software with Oracle Universal Installer.
If on SOLARIS (Sparc or Intel) with:
------------------------------------
Oracle 7.3.X, 8.0.X, or 8.1.X:
- Ensure that /usr/ccs/bin is before /usr/ucb in $PATH
% which ld ....should return '/usr/ccs/bin/ld'
If using 32bit(pre 9i) Oracle,
- Set LD_LIBRARY_PATH=$ORACLE_HOME/lib
If using 64bit(pre 9i) Oracle,
- Set LD_LIBRARY_PATH=$ORACLE_HOME/lib
- Set LD_LIBRARY_PATH_64=$ORACLE_HOME/lib64
Oracle 9.X.X or higher:
- LD_LIBRARY_PATH & LD_LIBRARY_PATH_64 do not need to be set to include
a reference to $ORACLE_HOME/lib or $ORACLE_HOME/lib64 for a generic database
software installation.
(However they should not contain a reference to the ORACLE_HOME of another Oracle version)
If on Digital/Tru64, IBM/Sequent PTX, Linux or any other UNIX Platform not
mentioned above with:
------------------------------------------------------------------------------
Oracle 7.3.X, 8.0.X, 8.1.X, 9.X.X or 10.X.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
[Step 4] For all Oracle Versions and UNIX Platforms:
===============================================================================
Verify that you performed Step 2 correctly:
% env|pg ....make sure that you see the correct absolute path for
$ORACLE_HOME in the variable definitions.
[Step 5] For all Oracle Versions and UNIX Platforms:
===============================================================================
Verify umask is set correctly:
% umask
This must return 022. If it does not, set umask to 022.
% umask 022
% umask
[Step 6] Run the OS Commands to Relink Oracle:
===============================================================================
Important Note: Before relinking Oracle, shut down both the database and the
listener.
Important Note: The following commands will output a lot of text to your
session window.
Important Note: If relinking a client installation, it's expected that some
aspects of the following commands will fail if the components
were not originally installed.
For all UNIX platforms:
Oracle 7.3.x
------------
For executables: oracle, exp, imp, sqlldr, tkprof
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install
For executables: svrmgrl, svrmgrm
% cd $ORACLE_HOME/svrmgr/lib
% make -f ins_svrmgr.mk linstall minstall <- linstall is for svrmgrl,
minstall is for svrmgrm
For executables: sqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install
For executables: dbsnmp, oemevent, oratclsh
% cd $ORACLE_HOME/network/lib
% make -f ins_agent.mk install
For executables: names, namesctl
% cd $ORACLE_HOME/network/lib
% make -f ins_names.mk install
For executables: tnslsnr, lsnrctl, tnsping, csmnl, trceval, trcroute
% cd $ORACLE_HOME/network/lib
% make -f ins_network.mk install
Oracle 8.0.x
------------
For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman,
svrmgrl, ogms, ogmsctl
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install
For executables: sqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install
For executables: dbsnmp, oemevent, oratclsh, libosm.so
% cd $ORACLE_HOME/network/lib
% make -f ins_oemagent.mk install
For executables: tnslsnr, lsnrctl, namesctl, names, osslogin, trcasst,
trcroute
% cd $ORACLE_HOME/network/lib
% make -f ins_network.mk install
Oracle 8.1.X, 9.X.X or 10.X.X
------------------------------
*** NEW IN 8i AND ABOVE ***
A 'relink' script is provided in the $ORACLE_HOME/bin directory.
% cd $ORACLE_HOME/bin
% relink ...this will display all of the command's options.
usage: relink
accepted values for parameter: all, oracle, network, client,
client_sharedlib, interMedia, precomp, utilities, oemagent, ldap
Note: ldap option is available only from 9i. In 8i, you would have to manually relink
ldap.
You can relink most of the executables associated with an Oracle Server Installation
by running the following command:
% relink all
This will not relink every single executable Oracle provides(you can
discern which executables were relinked by checking their timestamp with
'ls -l' in the $ORACLE_HOME/bin directory). However, 'relink all' will
recreate the shared libraries that most executables rely on and thereby
resolve most issues that require a proper relink.
-or-
Since the 'relink' command merely calls the traditional 'make' commands, you
still have the option of running the 'make' commands independently:
For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman,
svrmgrl, ogms, ogmsctl
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install
NOTE: After relinking the oracle executable, make sure that the
permissions on the executable are 6751 (-rwsr-s--x). If they are
not, run the following command as the Oracle software owner:
% cd $ORACLE_HOME/bin
% chmod 6751 oracle
For executables: sqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install
For executables: isqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus install_isqlplus
For executables: dbsnmp, oemevent, oratclsh
% cd $ORACLE_HOME/network/lib
% make -f ins_oemagent.mk install
NOTE: After relinking the dbsnmp executable, it is necessary to run
the following commands as root (so that the ownership/permissions on
the executable are correct):
# cd $ORACLE_HOME/bin
# chown root dbsnmp
# chmod 6750 dbsnmp
@ References: Note 233559.1 and Bug 2858326
For executables: names, namesctl
% cd $ORACLE_HOME/network/lib
% make -f ins_names.mk install
For executables: osslogin, trcasst, trcroute, onrsd, tnsping
% cd $ORACLE_HOME/network/lib
% make -f ins_net_client.mk install
For executables: tnslsnr, lsnrctl
% cd $ORACLE_HOME/network/lib
% make -f ins_net_server.mk install
For executables related to ldap (for example Oracle Internet Directory):
% cd $ORACLE_HOME/ldap/lib
% make -f ins_ldap.mk install
How to Tell if Relinking Was Successful:
===============================================================================
If relinking was successful, the make command will eventually return to the OS
prompt without an error. There will NOT be a 'Relinking Successful' type
message.
If You Receive an Error Message During Relinking:
===============================================================================
Confirm that the message you received is an actual fatal error and not a
warning. Relinking errors usually terminate the relinking process and contain
verbage similar to the following:
'Fatal error', 'Ld: fatal', 'Exit Code 1'
While warnings will look similar to: 'ld: warning: option -YP appears more than
once, first setting taken' and can most often be ignored.
If you receive an error that terminates the relinking process, your first step
should be to extract the relevant information about the error from the make
output:
This can be broken down into three basic steps:
1. Identify the OS utility that is returning the error.
'ld', 'make', 'cc', 'mv', 'cp', 'ar' are common sources.
2. Identify the type of error:
'Permission Denied', 'Undefined Symbol', 'File Not Found' are common types.
3. Identify the files or symbols involved.
Using the information from above as keywords, search Oracle's Metalink
repository (MetaLink.oracle.com) for previous occurrences of the same error.
If no previous occurances are found or a solution is not provided, generate an
iTAR that includes the complete error text.
Help setting environment variables.
==============================================================================
See Note 131207.1 How to Set UNIX Environment Variables
for help with setting UNIX environment variables.
Relinking with Orainst:
===============================================================================
For Oracle7 & Oracle8 only, the following document illustrates how to relink
with the 'orainst' utility:
Note 1032747.6 HOW TO RELINK ORACLE USING THE 7.3.X INSTALLER
While 'orainst' will run the same commands as [Step 4], performing [Step 4]
manually from a UNIX shell is the preferred approach.
RELATED DOCUMENTS
-----------------
Note 131207.1 How to Set UNIX Environment Variables
Note 109621.1 HP/UX: LD_LIBRARY_PATH and SHLIB_PATH
Note 1032747.6 HOW TO RELINK ORACLE USING THE 7.3.X INSTALLER
Bug 1337908 THE $ORACLE_HOME/BIN/RELINK SCRIPT DOES NOT RELINK EXP, IMP
SQLLOADER
.
Subject: How to Relink Oracle Database Software on UNIX
Doc ID: Note:131321.1 Type: BULLETIN
Last Revision Date: 06-OCT-2006 Status: PUBLISHED
PURPOSE
-------
Provide relinking instructions for Oracle Database software
on UNIX platforms.
SCOPE & APPLICATION
-------------------
Anyone who maintains Oracle RDBMS software on a UNIX platform.
Relinking Oracle
================
Background:
Applications for UNIX are generally not distributed as complete executables.
Oracle, like many application vendors who create products for UNIX, distribute
individual object files, library archives of object files, and some source
files which then get ?relinked? at the operating system level during
installation to create usable executables. This guarantees a reliable
integration with functions provided by the OS system libraries.
Relinking occurs automatically under these circumstances:
- An Oracle product has been installed with an Oracle provided installer.
- An Oracle patch set has been applied via an Oracle provided installer.
The following information has been added to the 'Certify' section of Metalink:
General Notes For Oracle Database - Enterprise Edition:
O/S Information:
The vendors guarantee operating system binary compatibility; therefore, no
reinstall or relink of the Oracle software is required when upgrading these
operating systems unless specifically stated otherwise.
Historically, relinking Oracle manually was suggested under these circumstances:
- An OS upgrade has occurred.
- A change has been made to the OS system libraries. This can occur during
the application of an OS patch.
- A new install failed during the relinking phase.
- Individual Oracle executables core dump during initial startup.
- An individual Oracle patch has been applied (however, explicit relink
instructions are usually either included in the README or integrated into
the patch install script)
Customers who want to (or have been advised to) manually relink should follow
the procedure below.
[Step 1] Log into the UNIX system as the Oracle software owner
==============================================================================
Typically this is the user 'oracle'.
[STEP 2] Verify that your $ORACLE_HOME is set correctly:
===============================================================================
For all Oracle Versions and Platforms, perform this basic environment check
first:
% cd $ORACLE_HOME
% pwd
...Doing this will ensure that $ORACLE_HOME is set correctly in your current
environment.
[Step 3] Verify and/or Configure the UNIX Environment for Proper Relinking:
===============================================================================
For all Oracle Versions and UNIX Platforms:
The Platform specific environment variables LIBPATH, LD_LIBRARY_PATH, &
SHLIB_PATH typically are already set to include system library locations like
'/usr/lib'. In most cases, you need only check what they are set to first,
then add the $ORACLE_HOME/lib directory to them where appropriate.
i.e.: % setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
(see Note 131207.1 How to Set UNIX Environment Variables for help with
setting UNIX environment variables)
If on AIX with:
--------------
Oracle 7.3.X:
- Set LIBPATH to include $ORACLE_HOME/lib
Oracle 8.0.X:
- Set LIBPATH to include $ORACLE_HOME/lib
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib and
$ORACLE_HOME/network/lib (Required when using Oracle products that
use Java)
- Set LINK_CNTRL to L_PTHREADS_D7 if using AIX 4.3. ('oslevel' verifies
OS version)
Oracle 8.1.X, 9.X.X or 10.X.X:
- For 8.1.5, set LINK_CNTRL to L_PTHREADS_D7
- If not 8.1.5, ensure that LINK_CNTRL is not set
- Set LIBPATH to include $ORACLE_HOME/lib
If on DATA GENERAL AVIION (DG) with:
-----------------------------------
Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- ensure TARGET_BINARY_INTERFACE is unset
Oracle 8.1.X:
- Set LD_LIBRARY_PATH to include
$ORACLE_HOME/lib:$ORACLE_HOME/JRE/lib/PentiumPro/native_threads
If on HP-UX with:
----------------
Oracle 7.3.X, 8.0.X, 8.1.X;
- Set SHLIB_PATH to $ORACLE_HOME/lib
If using 64bit 8i Oracle, also
- Set LD_LIBRARY_PATH to $ORACLE_HOME/lib64
- ensure LPATH is unset
Oracle 9.X.X or 10.X.X;
- ensure LPATH is unset
If on NCR with:
--------------
Oracle 7.3.X, 8.0.X or 8.1.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib:/usr/ccs/lib
If on SCO UNIXware with:
-----------------------
Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
Oracle 8.1.X:
- Set LD_LIBRARY_PATH to include
$ORACLE_HOME/lib:$ORACLE_HOME/JRE/lib/x86at/native_threads
If on SGI with:
--------------
32bit Oracle 7.3.X or 8.0.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- Set SGI_ABI to -32
64bit Oracle 8.0.X or 8.1.X (8i is only available in 64bit):
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- Set SGI_ABI to -64
- If one does not already exist, create the file compiler.defaults and
set the COMPILER_DEFAULTS_PATH variable:
In the Oracle software owner's $HOME directory, create a file called
'compiler.defaults':
% cd $HOME
% echo "-DEFAULT:abi=64:isa=mips3:proc=r10k" > compiler.defaults
Then set the environment variable COMPILER_DEFAULTS_PATH to point to the
$HOME directory.
% setenv COMPILER_DEFAULTS_PATH $HOME
If this is not set, relinking will fail because the compiler defaults to
MIPS4 objects although Oracle requires MIPS3.
- Set LD_LIBRARY64_PATH to include the $ORACLE_HOME/lib and the
$ORACLE_HOME/javavm/admin directories.
- Set LD_LIBRARYN32_PATH to include the $ORACLE_HOME/lib32 directory.
NOTE: LD_LIBRARY64_PATH & LD_LIBRARYN32_PATH must be undefined when
installing software with Oracle Universal Installer.
If on SOLARIS (Sparc or Intel) with:
------------------------------------
Oracle 7.3.X, 8.0.X, or 8.1.X:
- Ensure that /usr/ccs/bin is before /usr/ucb in $PATH
% which ld ....should return '/usr/ccs/bin/ld'
If using 32bit(pre 9i) Oracle,
- Set LD_LIBRARY_PATH=$ORACLE_HOME/lib
If using 64bit(pre 9i) Oracle,
- Set LD_LIBRARY_PATH=$ORACLE_HOME/lib
- Set LD_LIBRARY_PATH_64=$ORACLE_HOME/lib64
Oracle 9.X.X or higher:
- LD_LIBRARY_PATH & LD_LIBRARY_PATH_64 do not need to be set to include
a reference to $ORACLE_HOME/lib or $ORACLE_HOME/lib64 for a generic database
software installation.
(However they should not contain a reference to the ORACLE_HOME of another Oracle version)
If on Digital/Tru64, IBM/Sequent PTX, Linux or any other UNIX Platform not
mentioned above with:
------------------------------------------------------------------------------
Oracle 7.3.X, 8.0.X, 8.1.X, 9.X.X or 10.X.X:
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
[Step 4] For all Oracle Versions and UNIX Platforms:
===============================================================================
Verify that you performed Step 2 correctly:
% env|pg ....make sure that you see the correct absolute path for
$ORACLE_HOME in the variable definitions.
[Step 5] For all Oracle Versions and UNIX Platforms:
===============================================================================
Verify umask is set correctly:
% umask
This must return 022. If it does not, set umask to 022.
% umask 022
% umask
[Step 6] Run the OS Commands to Relink Oracle:
===============================================================================
Important Note: Before relinking Oracle, shut down both the database and the
listener.
Important Note: The following commands will output a lot of text to your
session window.
Important Note: If relinking a client installation, it's expected that some
aspects of the following commands will fail if the components
were not originally installed.
For all UNIX platforms:
Oracle 7.3.x
------------
For executables: oracle, exp, imp, sqlldr, tkprof
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install
For executables: svrmgrl, svrmgrm
% cd $ORACLE_HOME/svrmgr/lib
% make -f ins_svrmgr.mk linstall minstall <- linstall is for svrmgrl,
minstall is for svrmgrm
For executables: sqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install
For executables: dbsnmp, oemevent, oratclsh
% cd $ORACLE_HOME/network/lib
% make -f ins_agent.mk install
For executables: names, namesctl
% cd $ORACLE_HOME/network/lib
% make -f ins_names.mk install
For executables: tnslsnr, lsnrctl, tnsping, csmnl, trceval, trcroute
% cd $ORACLE_HOME/network/lib
% make -f ins_network.mk install
Oracle 8.0.x
------------
For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman,
svrmgrl, ogms, ogmsctl
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install
For executables: sqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install
For executables: dbsnmp, oemevent, oratclsh, libosm.so
% cd $ORACLE_HOME/network/lib
% make -f ins_oemagent.mk install
For executables: tnslsnr, lsnrctl, namesctl, names, osslogin, trcasst,
trcroute
% cd $ORACLE_HOME/network/lib
% make -f ins_network.mk install
Oracle 8.1.X, 9.X.X or 10.X.X
------------------------------
*** NEW IN 8i AND ABOVE ***
A 'relink' script is provided in the $ORACLE_HOME/bin directory.
% cd $ORACLE_HOME/bin
% relink ...this will display all of the command's options.
usage: relink
accepted values for parameter: all, oracle, network, client,
client_sharedlib, interMedia, precomp, utilities, oemagent, ldap
Note: ldap option is available only from 9i. In 8i, you would have to manually relink
ldap.
You can relink most of the executables associated with an Oracle Server Installation
by running the following command:
% relink all
This will not relink every single executable Oracle provides(you can
discern which executables were relinked by checking their timestamp with
'ls -l' in the $ORACLE_HOME/bin directory). However, 'relink all' will
recreate the shared libraries that most executables rely on and thereby
resolve most issues that require a proper relink.
-or-
Since the 'relink' command merely calls the traditional 'make' commands, you
still have the option of running the 'make' commands independently:
For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman,
svrmgrl, ogms, ogmsctl
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk install
NOTE: After relinking the oracle executable, make sure that the
permissions on the executable are 6751 (-rwsr-s--x). If they are
not, run the following command as the Oracle software owner:
% cd $ORACLE_HOME/bin
% chmod 6751 oracle
For executables: sqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus.mk install
For executables: isqlplus
% cd $ORACLE_HOME/sqlplus/lib
% make -f ins_sqlplus install_isqlplus
For executables: dbsnmp, oemevent, oratclsh
% cd $ORACLE_HOME/network/lib
% make -f ins_oemagent.mk install
NOTE: After relinking the dbsnmp executable, it is necessary to run
the following commands as root (so that the ownership/permissions on
the executable are correct):
# cd $ORACLE_HOME/bin
# chown root dbsnmp
# chmod 6750 dbsnmp
@ References: Note 233559.1 and Bug 2858326
For executables: names, namesctl
% cd $ORACLE_HOME/network/lib
% make -f ins_names.mk install
For executables: osslogin, trcasst, trcroute, onrsd, tnsping
% cd $ORACLE_HOME/network/lib
% make -f ins_net_client.mk install
For executables: tnslsnr, lsnrctl
% cd $ORACLE_HOME/network/lib
% make -f ins_net_server.mk install
For executables related to ldap (for example Oracle Internet Directory):
% cd $ORACLE_HOME/ldap/lib
% make -f ins_ldap.mk install
How to Tell if Relinking Was Successful:
===============================================================================
If relinking was successful, the make command will eventually return to the OS
prompt without an error. There will NOT be a 'Relinking Successful' type
message.
If You Receive an Error Message During Relinking:
===============================================================================
Confirm that the message you received is an actual fatal error and not a
warning. Relinking errors usually terminate the relinking process and contain
verbage similar to the following:
'Fatal error', 'Ld: fatal', 'Exit Code 1'
While warnings will look similar to: 'ld: warning: option -YP appears more than
once, first setting taken' and can most often be ignored.
If you receive an error that terminates the relinking process, your first step
should be to extract the relevant information about the error from the make
output:
This can be broken down into three basic steps:
1. Identify the OS utility that is returning the error.
'ld', 'make', 'cc', 'mv', 'cp', 'ar' are common sources.
2. Identify the type of error:
'Permission Denied', 'Undefined Symbol', 'File Not Found' are common types.
3. Identify the files or symbols involved.
Using the information from above as keywords, search Oracle's Metalink
repository (MetaLink.oracle.com) for previous occurrences of the same error.
If no previous occurances are found or a solution is not provided, generate an
iTAR that includes the complete error text.
Help setting environment variables.
==============================================================================
See Note 131207.1 How to Set UNIX Environment Variables
for help with setting UNIX environment variables.
Relinking with Orainst:
===============================================================================
For Oracle7 & Oracle8 only, the following document illustrates how to relink
with the 'orainst' utility:
Note 1032747.6 HOW TO RELINK ORACLE USING THE 7.3.X INSTALLER
While 'orainst' will run the same commands as [Step 4], performing [Step 4]
manually from a UNIX shell is the preferred approach.
RELATED DOCUMENTS
-----------------
Note 131207.1 How to Set UNIX Environment Variables
Note 109621.1 HP/UX: LD_LIBRARY_PATH and SHLIB_PATH
Note 1032747.6 HOW TO RELINK ORACLE USING THE 7.3.X INSTALLER
Bug 1337908 THE $ORACLE_HOME/BIN/RELINK SCRIPT DOES NOT RELINK EXP, IMP
SQLLOADER
.
Is Relinking Of Oracle (Relink All) Required After Patching OS?
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.0 to 10.2.0.2
Information in this document applies to any platform.
GoalIs
relinking of Oracle binaries required after upgrading or patching of OS?
In Metalink Certify section says that it is not required.
"General Notes For Oracle Database - Enterprise Edition:
O/S Information:
The vendors guarantee operating system binary compatibility; therefore, no
reinstall or relink of the Oracle software is required when upgrading these
operating systems unless specifically stated otherwise."
Now why relinking is required after OS upgrade/patching?
Solution
Oracle software come as object files or source files which generally get relinked with OS
libraries at the time of installation to get the executables.
If you upgrade or apply the patches to the operating system, the libraries which are linked with
oracle binaries may be get updated. Ideally speaking there should not be any problem because
updated libraries also would work fine with Oracle binaries as OS vendors maintain backward
compatibility. This is only specified as "The vendors guarantee operating system binary
compatibility".
But in some cases it is possible that new libraries may not compatible with old libraries (because
of bug in OS libraries itself).
In some cases it is also possible that Oracle software may not work with new libraries as it may
take advantage of a flaw in old OS libraries which is fixed new OS libraries.
In some cases because of bug in Oracle software itself it may not work as expected with new
libraries.
So, to ensure that Oracle software is not broken with updated libraries, it is recommended to
relink the Oracle binaries even though ideally it is not required.
ReferencesNote 131321.1 - How to Relink Oracle Database Software on UNIX
Oracle Server - Enterprise Edition - Version: 9.2.0.0 to 10.2.0.2
Information in this document applies to any platform.
GoalIs
relinking of Oracle binaries required after upgrading or patching of OS?
In Metalink Certify section says that it is not required.
"General Notes For Oracle Database - Enterprise Edition:
O/S Information:
The vendors guarantee operating system binary compatibility; therefore, no
reinstall or relink of the Oracle software is required when upgrading these
operating systems unless specifically stated otherwise."
Now why relinking is required after OS upgrade/patching?
Solution
Oracle software come as object files or source files which generally get relinked with OS
libraries at the time of installation to get the executables.
If you upgrade or apply the patches to the operating system, the libraries which are linked with
oracle binaries may be get updated. Ideally speaking there should not be any problem because
updated libraries also would work fine with Oracle binaries as OS vendors maintain backward
compatibility. This is only specified as "The vendors guarantee operating system binary
compatibility".
But in some cases it is possible that new libraries may not compatible with old libraries (because
of bug in OS libraries itself).
In some cases it is also possible that Oracle software may not work with new libraries as it may
take advantage of a flaw in old OS libraries which is fixed new OS libraries.
In some cases because of bug in Oracle software itself it may not work as expected with new
libraries.
So, to ensure that Oracle software is not broken with updated libraries, it is recommended to
relink the Oracle binaries even though ideally it is not required.
ReferencesNote 131321.1 - How to Relink Oracle Database Software on UNIX
Thursday, December 14, 2006
How to View the Meaning of Linux OS Error Codes
Problem Description:
====================
How to find the meaning of Linux OS error codes?
Solution Description:
=====================
All OS error codes can be found in:
（tested under RHEL 3）
/usr/src/linux-版本号/include/asm/errno.h
====================
How to find the meaning of Linux OS error codes?
Solution Description:
=====================
All OS error codes can be found in:
（tested under RHEL 3）
/usr/src/linux-版本号/include/asm/errno.h
Tuesday, December 12, 2006
invalid object automatically recompile
＜質問＞
INVALIDになったプロシージャを自動的にリコンパイルするといった
仕組みは Oracleで提供されていないでしょうか？
初回アクセス時にリコンパイルされる仕様だと理解していますが、
DBLink越しでの初回アクセスエラーで返るといった問題が発生して
います。
そこで、INVALIDになったときに即座に自動的にリコンパイルされる
ような仕組みがないか、調査をお願いします。
<回答内容>
はい、INVALIDになったプロシージャを自動的にリコンパイルする
といった仕組みはOracleで提供されています。
しかしながら、DBLink越しでの初回アクセス時にリコンパイルさ
れません。だから、お客様の環境でそのエラーが発生しました。
つまり、DBLink越しでの初回アクセス時にはORA-4062が発生しますが、
2度目のアクセス時には成功します。
1回目のORA-4062の回避方法は、プロシージャが存在しているサーバ上で、
変更したプロシージャを手動でリコンパイルすることです。
参考情報
-----------------------------------------------------------------------
KROWN#15543 ORA-4062の対処方法、リモート依存性について（Forms/Reports）
-----------------------------------------------------------------------
这种机制在oracle手册《概念》这本书中有描述。
针对本件中的问题，简单的说，就是当调用无效存储过程的时候，
oracle会自动尝试编译无效的存储过程。
详情请参考：
--------------------------------------------------
Oracle9i Database Concepts
Release 2 (9.2)
Part Number A96524-01
15 Dependencies Among Schema Objects
Introduction to Dependency Issues
Resolution of Schema Object Dependencies
Local and Remote Dependency Management
--------------------------------------------------
INVALIDになったプロシージャを自動的にリコンパイルするといった
仕組みは Oracleで提供されていないでしょうか？
初回アクセス時にリコンパイルされる仕様だと理解していますが、
DBLink越しでの初回アクセスエラーで返るといった問題が発生して
います。
そこで、INVALIDになったときに即座に自動的にリコンパイルされる
ような仕組みがないか、調査をお願いします。
<回答内容>
はい、INVALIDになったプロシージャを自動的にリコンパイルする
といった仕組みはOracleで提供されています。
しかしながら、DBLink越しでの初回アクセス時にリコンパイルさ
れません。だから、お客様の環境でそのエラーが発生しました。
つまり、DBLink越しでの初回アクセス時にはORA-4062が発生しますが、
2度目のアクセス時には成功します。
1回目のORA-4062の回避方法は、プロシージャが存在しているサーバ上で、
変更したプロシージャを手動でリコンパイルすることです。
参考情報
-----------------------------------------------------------------------
KROWN#15543 ORA-4062の対処方法、リモート依存性について（Forms/Reports）
-----------------------------------------------------------------------
这种机制在oracle手册《概念》这本书中有描述。
针对本件中的问题，简单的说，就是当调用无效存储过程的时候，
oracle会自动尝试编译无效的存储过程。
详情请参考：
--------------------------------------------------
Oracle9i Database Concepts
Release 2 (9.2)
Part Number A96524-01
15 Dependencies Among Schema Objects
Introduction to Dependency Issues
Resolution of Schema Object Dependencies
Local and Remote Dependency Management
--------------------------------------------------
Monday, December 11, 2006
statspack bug
[問題]
statspack.snapを実行時にora-1401が発生する
SQL> exec statspack.snap
BEGIN statspack.snap; END;
*
1行でエラーが発生しました。
ORA-01401: 列に挿入した値が大きすぎます。
ORA-06512: "PERFSTAT.STATSPACK", 行 978
ORA-06512: "PERFSTAT.STATSPACK", 行 1612
ORA-06512: "PERFSTAT.STATSPACK", 行 71
ORA-06512: 行 1
[発生条件]
・ 統計情報として格納され得るマルチバイト文字を使用したSQL文を発行しているシステム
（マルチバイト・キャラクタの環境(character set、NLS_LANGがJA16SJISなど)で、
OBJECT名やカラム名にマルチバイト文字を使用したSQL文を発行している）
・ statspack.snapのlevelが5以上(Default level 5)
[原因]
この問題は8.1.7からstatspack.snap内のinsert into stats$sql_summary select ..
文中に以下のカラムが追加された為に発生する。
TEXT_SUBSET VARCHAR2(31)
select文中でSUBSTR(SQL_TEXT,1,31)とした値をTEXT_SUBSETに対してinsertを行う
処理が存在するが、マルチバイト・キャラクタが使用されているとSUBSTRの為、バイトでは
なく文字数で情報を取得してしまう。
その為、VARCHAR2(31)より大きくなり、ORA-1401が発生する。
[対処]
問題を修正したリリース
Oracle9i Database Release2 (9.2.0)
問題を修正した/修正予定のPSR
8.1.7.3
9.0.1.2
[回避策]
$ORACLE_HOME/rdbms/admin/spcpkg.sql内のinsert into stats$sql_summeryで使用されている
SUBSTRの部分をSUBSTRBに変更して、perfstat userでscriptを流し直す。
spcpkg.sqlより
insert into stats$sql_summary
( snap_id
, dbid
, instance_number
, text_subset
, sharable_mem
, sorts
, module
, loaded_versions
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, address
, hash_value
, version_count
)
select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31) <- ここをSUBSTRBに変更して下さい。(10 for example)
.
.
[Error#]
ORA-1401
[更新履歴]
2004/01/29 「問題を修正した/修正予定のリリース」を「問題を修正したリリース」へ変更
2004/01/27 修正情報を正しいものに変更
statspack.snapを実行時にora-1401が発生する
SQL> exec statspack.snap
BEGIN statspack.snap; END;
*
1行でエラーが発生しました。
ORA-01401: 列に挿入した値が大きすぎます。
ORA-06512: "PERFSTAT.STATSPACK", 行 978
ORA-06512: "PERFSTAT.STATSPACK", 行 1612
ORA-06512: "PERFSTAT.STATSPACK", 行 71
ORA-06512: 行 1
[発生条件]
・ 統計情報として格納され得るマルチバイト文字を使用したSQL文を発行しているシステム
（マルチバイト・キャラクタの環境(character set、NLS_LANGがJA16SJISなど)で、
OBJECT名やカラム名にマルチバイト文字を使用したSQL文を発行している）
・ statspack.snapのlevelが5以上(Default level 5)
[原因]
この問題は8.1.7からstatspack.snap内のinsert into stats$sql_summary select ..
文中に以下のカラムが追加された為に発生する。
TEXT_SUBSET VARCHAR2(31)
select文中でSUBSTR(SQL_TEXT,1,31)とした値をTEXT_SUBSETに対してinsertを行う
処理が存在するが、マルチバイト・キャラクタが使用されているとSUBSTRの為、バイトでは
なく文字数で情報を取得してしまう。
その為、VARCHAR2(31)より大きくなり、ORA-1401が発生する。
[対処]
問題を修正したリリース
Oracle9i Database Release2 (9.2.0)
問題を修正した/修正予定のPSR
8.1.7.3
9.0.1.2
[回避策]
$ORACLE_HOME/rdbms/admin/spcpkg.sql内のinsert into stats$sql_summeryで使用されている
SUBSTRの部分をSUBSTRBに変更して、perfstat userでscriptを流し直す。
spcpkg.sqlより
insert into stats$sql_summary
( snap_id
, dbid
, instance_number
, text_subset
, sharable_mem
, sorts
, module
, loaded_versions
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, address
, hash_value
, version_count
)
select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31) <- ここをSUBSTRBに変更して下さい。(10 for example)
.
.
[Error#]
ORA-1401
[更新履歴]
2004/01/29 「問題を修正した/修正予定のリリース」を「問題を修正したリリース」へ変更
2004/01/27 修正情報を正しいものに変更
Thursday, December 07, 2006
buffer cache hit ratio
buffer cache hit ratio
select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
/
select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
/
Sunday, December 03, 2006
Steps to Create and Run a Sample External Procedure Program(OS command)
Doc ID: Note:312564.1 Type: HOWTO
Last Revision Date: 03-MAY-2006 Status: PUBLISHED
(OS command)
The information in this document applies to:
Oracle Net Services - Version: 8.1.7.4 to 10.1.0.4
Information in this document applies to any platform.
Prerequisites: a C compiler (This note is written with Solaris and Linux compilers gcc and cc)
Goal
The goal of this document is to create a sample program that calls OS Commands from PL/SQL using extproc.
The Oracle demos for extproc depend on the scott schema being installed as well as the sample "emp" and "dept" tables being created. Most customers do not choose to utilize either option (or both) at install time thereby resulting in no efficient way to test extproc and its configuration.
The information provided in this note is a quick way of testing extproc (or extproc32) and should work with all versions of Oracle on any platform. Only steps 6 and 7 are specific to Sun and Linux. If you are using a different OS, it is your responsibility to find and correctly use a C compiler and a link editor.
Solution
1. Configure the Oracle configuration files.
Following these steps exactly will ensure extproc will work. While you may have different listener.ora and tnsnames.ora files, remember, the goal here is to get extproc working. After that, a custom configuration can be put in place. If problems arise, this base configuration can be revisited for reference.
In the $ORACLE_HOME/network/admin directory, backup any current listener.ora file and create a new file with these contents:
listener_extproc=
(address=(protocol=ipc)(key=extproc))
sid_list_listener_extproc=
(sid_list=
(sid_desc=
(sid_name=extproc)
(oracle_home=)
(program=extproc)
)
)
NOTE: If using extproc32, set (program=extproc32)
In the $ORACLE_HOME/network/admin directory, backup any current tnsnames.ora file and create a new file with these contents:
extproc_connection_data=
(description=
(address=(protocol=ipc)(key=extproc))
(connect_data=(sid=extproc)))
In the $ORACLE_HOME/network/admin directory, move any existing sqlnet.ora file to sqlnet.bak
2. Set the TNS_ADMIN environment variable.
In the $ORACLE_HOME/network/admin directory, issue the command:
For k shell
export TNS_ADMIN=`pwd`
For c shell
setenv TNS_ADMIN `pwd`
NOTE: the ` character surrounding `pwd` is the grave accent key, usually to the left of the "1" key on a standard 107 key PS/2 keyboard. It is also the paired on the same key as the tilde (~).
3. Start the listener.
lsnrctl start listener_extproc
4. Use tnsping to test the extproc configuration. This step has to succeed.
tnsping extproc_connection_data
If an error message is received, revisit steps 5-7 or contact support
5. Create a file with the following C code (it is called shell.c for this note)
#include
#include
#include
void sh(char *command) {
int num;
num = system(command);
}
6. Compile the code
For Solaris: To create a 64 bit file for extproc, use:
gcc -G -c -m64 shell.c
For Solaris: To create a 32 bit file for extproc32, use:
gcc -G -c shell.c
For Linux 32bit: To create a 32 bit file for extproc, use:
cc -c shell.c
7. Generate the shared object and change permissions
Solaris:
ld -r -o shell.so shell.o
chmod 775 shell.so
Linux 32bit:
ld -shared -o shell.so shell.o
chmod 775 shell.so
8. Place shell.so in the $ORACLE_HOME/lib directory.
This will bypass the need for adding ENVS and EXTPROC_DLLS parameters to the listener.ora file. Remember, tweaking can be done later; for now, we want the configuration to be as simple as possible.
9. Create a LIBRARY definition in Oracle
Logon to Oracle using a common user without using Oracle Net:
sqlplus system/manager
CREATE LIBRARY shell_lib is '/lib/shell.so';
/
10. Create the PL/SQL Wrapper(This procedure can be used in single line mode,
create a function instead if you want to use in pl/sql block)
CREATE OR REPLACE PROCEDURE shell(command IN char)
AS EXTERNAL
NAME "sh"
LIBRARY shell_lib
LANGUAGE C
PARAMETERS (command string);
/
11. Test the stored procedure:
exec shell('ls');
All you should see is "PL/SQL procedure successfully completed." which is good enough for this test and proves extproc is working properly. To see the output of the OS command passed to extproc, redirect the output to a text file:
exec shell('ls>output.txt');
A file called "output.txt" will be createdhich contains the output of the 'ls' command.
If there is no output, supply a full path to an output file:
exec shell('ls>/u01/app/oracle/output.txt');
At this point, extproc is functional and is proven to work. Customization can now take place.
============================================================================
Thanks goes to an internet forum posting by J. Hunter, Sr. Database Administrator, for providing the shell code and the correct command syntax for many of the commands.
Last Revision Date: 03-MAY-2006 Status: PUBLISHED
(OS command)
The information in this document applies to:
Oracle Net Services - Version: 8.1.7.4 to 10.1.0.4
Information in this document applies to any platform.
Prerequisites: a C compiler (This note is written with Solaris and Linux compilers gcc and cc)
Goal
The goal of this document is to create a sample program that calls OS Commands from PL/SQL using extproc.
The Oracle demos for extproc depend on the scott schema being installed as well as the sample "emp" and "dept" tables being created. Most customers do not choose to utilize either option (or both) at install time thereby resulting in no efficient way to test extproc and its configuration.
The information provided in this note is a quick way of testing extproc (or extproc32) and should work with all versions of Oracle on any platform. Only steps 6 and 7 are specific to Sun and Linux. If you are using a different OS, it is your responsibility to find and correctly use a C compiler and a link editor.
Solution
1. Configure the Oracle configuration files.
Following these steps exactly will ensure extproc will work. While you may have different listener.ora and tnsnames.ora files, remember, the goal here is to get extproc working. After that, a custom configuration can be put in place. If problems arise, this base configuration can be revisited for reference.
In the $ORACLE_HOME/network/admin directory, backup any current listener.ora file and create a new file with these contents:
listener_extproc=
(address=(protocol=ipc)(key=extproc))
sid_list_listener_extproc=
(sid_list=
(sid_desc=
(sid_name=extproc)
(oracle_home=
(program=extproc)
)
)
NOTE: If using extproc32, set (program=extproc32)
In the $ORACLE_HOME/network/admin directory, backup any current tnsnames.ora file and create a new file with these contents:
extproc_connection_data=
(description=
(address=(protocol=ipc)(key=extproc))
(connect_data=(sid=extproc)))
In the $ORACLE_HOME/network/admin directory, move any existing sqlnet.ora file to sqlnet.bak
2. Set the TNS_ADMIN environment variable.
In the $ORACLE_HOME/network/admin directory, issue the command:
For k shell
export TNS_ADMIN=`pwd`
For c shell
setenv TNS_ADMIN `pwd`
NOTE: the ` character surrounding `pwd` is the grave accent key, usually to the left of the "1" key on a standard 107 key PS/2 keyboard. It is also the paired on the same key as the tilde (~).
3. Start the listener.
lsnrctl start listener_extproc
4. Use tnsping to test the extproc configuration. This step has to succeed.
tnsping extproc_connection_data
If an error message is received, revisit steps 5-7 or contact support
5. Create a file with the following C code (it is called shell.c for this note)
#include
#include
#include
void sh(char *command) {
int num;
num = system(command);
}
6. Compile the code
For Solaris: To create a 64 bit file for extproc, use:
gcc -G -c -m64 shell.c
For Solaris: To create a 32 bit file for extproc32, use:
gcc -G -c shell.c
For Linux 32bit: To create a 32 bit file for extproc, use:
cc -c shell.c
7. Generate the shared object and change permissions
Solaris:
ld -r -o shell.so shell.o
chmod 775 shell.so
Linux 32bit:
ld -shared -o shell.so shell.o
chmod 775 shell.so
8. Place shell.so in the $ORACLE_HOME/lib directory.
This will bypass the need for adding ENVS and EXTPROC_DLLS parameters to the listener.ora file. Remember, tweaking can be done later; for now, we want the configuration to be as simple as possible.
9. Create a LIBRARY definition in Oracle
Logon to Oracle using a common user without using Oracle Net:
sqlplus system/manager
CREATE LIBRARY shell_lib is '
/
10. Create the PL/SQL Wrapper(This procedure can be used in single line mode,
create a function instead if you want to use in pl/sql block)
CREATE OR REPLACE PROCEDURE shell(command IN char)
AS EXTERNAL
NAME "sh"
LIBRARY shell_lib
LANGUAGE C
PARAMETERS (command string);
/
11. Test the stored procedure:
exec shell('ls');
All you should see is "PL/SQL procedure successfully completed." which is good enough for this test and proves extproc is working properly. To see the output of the OS command passed to extproc, redirect the output to a text file:
exec shell('ls>output.txt');
A file called "output.txt" will be createdhich contains the output of the 'ls' command.
If there is no output, supply a full path to an output file:
exec shell('ls>/u01/app/oracle/output.txt');
At this point, extproc is functional and is proven to work. Customization can now take place.
============================================================================
Thanks goes to an internet forum posting by J. Hunter, Sr. Database Administrator, for providing the shell code and the correct command syntax for many of the commands.
Subscribe to:
Posts (Atom)
