【お問合せ内容】
以下の内容についてご教授お願い致します。
------------------------------------------------------------------
「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.
Monday, November 27, 2006
Database creation using DBCA on WIN 2003 PDC fails with DIM-19
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2
Microsoft Windows Server 2003
Symptoms
Creating a database on Windows 2003 which is domain Controller (PDC ) using DBCA fails with the error :
DIM-00019: create service error
O/S-Error: (OS-1388) A new member could not be added to a local group because the member has the wrong account type.
Cause
Windows 2003 Server configured as Primary Domain Controller does not allow "NT Authority\System" to be added to ORA_DBA group. Windows 2003 Server PDC allows only "Built-In" objects to have the object type as "NT Authority\System" and User objects can not have the above role.
Solution
"NT Authority\System" account boils down to System name or the machine name hence manually add HostName to the ORA_DBA group and rerun the DBCA.
Also the bug 3540159 addresses this issue and is fixed in 10.1.0.4 and 10.2 .
How to add the PDC machine to the ORA_DBA group?
Via the Windows Start Menu:
Programs
Administrative Tools
Active Directory Users and Computers
- Select Users in the left pane
- Select the ORA_DBA group in the right pane
- Select properties with the rigth mouse button
- Select the members tab (which for a server also lists the machine name)
- Click the Add button
- Select and add the Machine's name to the group
- Confirm with OK
Other Workaround:
When you hit DIM-19 error Start the OracleService manually and then continue with the DBCA.
References
Bug 3540159 - Dbca Fails To Create Database Dim-19
Oracle Server - Enterprise Edition - Version: 10.1.0.2
Microsoft Windows Server 2003
Symptoms
Creating a database on Windows 2003 which is domain Controller (PDC ) using DBCA fails with the error :
DIM-00019: create service error
O/S-Error: (OS-1388) A new member could not be added to a local group because the member has the wrong account type.
Cause
Windows 2003 Server configured as Primary Domain Controller does not allow "NT Authority\System" to be added to ORA_DBA group. Windows 2003 Server PDC allows only "Built-In" objects to have the object type as "NT Authority\System" and User objects can not have the above role.
Solution
"NT Authority\System" account boils down to System name or the machine name hence manually add HostName to the ORA_DBA group and rerun the DBCA.
Also the bug 3540159 addresses this issue and is fixed in 10.1.0.4 and 10.2 .
How to add the PDC machine to the ORA_DBA group?
Via the Windows Start Menu:
Programs
Administrative Tools
Active Directory Users and Computers
- Select Users in the left pane
- Select the ORA_DBA group in the right pane
- Select properties with the rigth mouse button
- Select the members tab (which for a server also lists the machine name)
- Click the Add button
- Select and add the Machine's name to the group
- Confirm with OK
Other Workaround:
When you hit DIM-19 error Start the OracleService manually and then continue with the DBCA.
References
Bug 3540159 - Dbca Fails To Create Database Dim-19
Thursday, November 23, 2006
Compare strings
<質問内容>
SQL文で文字列の比較をしたい。
比較条件として 文字列 A と 文字列 B の どこか2文字が一致すれば
真として、レコードが返るようにしたい。
どのような条件指定を行えばよいか。
・Windows2000 / R8.1.7
・文字列長 不定 (最大100文字程度)
・合致箇所 不定 (先頭2文字 や 末尾 2文字 ではなく、どこか2文字としたい)
<回答内容>
The function created by the following script can help you do what you want.
The return value 1 means TRUE and 0 means FALSE.
Please note that this function is case-sensitive.
--------------------------------------------------------------------------------
create or replace function compare(sA in varchar2, sB in varchar2)
return varchar2
is result varchar2(200);
begin
if (length(sA)>=2) then
for n in 1..length(sA) loop
select to_char(count(*)) into result from dual where sB like '%'||substr(sA,n,2)||'%';
if result='1' then
result:=sA;
return(result);
end if;
end loop;
end if;
result:='Not match';
return(result);
end;
/
--------------------------------------------------------------------------------
For example:
SQL> select compare('abcdefghijklmnopqrstuvwxyz','11111111111111de111111111111')
2 as flag from dual;
FLAG
----------
1
SQL> select compare('abcdefghijklmnopqrstuvwxyz','12345678901234567890')
2 as flag from dual;
FLAG
----------
0
SQL文で文字列の比較をしたい。
比較条件として 文字列 A と 文字列 B の どこか2文字が一致すれば
真として、レコードが返るようにしたい。
どのような条件指定を行えばよいか。
・Windows2000 / R8.1.7
・文字列長 不定 (最大100文字程度)
・合致箇所 不定 (先頭2文字 や 末尾 2文字 ではなく、どこか2文字としたい)
<回答内容>
The function created by the following script can help you do what you want.
The return value 1 means TRUE and 0 means FALSE.
Please note that this function is case-sensitive.
--------------------------------------------------------------------------------
create or replace function compare(sA in varchar2, sB in varchar2)
return varchar2
is result varchar2(200);
begin
if (length(sA)>=2) then
for n in 1..length(sA) loop
select to_char(count(*)) into result from dual where sB like '%'||substr(sA,n,2)||'%';
if result='1' then
result:=sA;
return(result);
end if;
end loop;
end if;
result:='Not match';
return(result);
end;
/
--------------------------------------------------------------------------------
For example:
SQL> select compare('abcdefghijklmnopqrstuvwxyz','11111111111111de111111111111')
2 as flag from dual;
FLAG
----------
1
SQL> select compare('abcdefghijklmnopqrstuvwxyz','12345678901234567890')
2 as flag from dual;
FLAG
----------
0
Wednesday, November 22, 2006
Operations requiring sorts
Operations that Require Temporary Segments
The following statements sometimes require the use of a temporary segment:
CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT . . . UNION
SELECT ... INTERSECT
SELECT ... MINUS
Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments.
手册：conceptes
The following statements sometimes require the use of a temporary segment:
CREATE INDEX
SELECT ... ORDER BY
SELECT DISTINCT ...
SELECT ... GROUP BY
SELECT . . . UNION
SELECT ... INTERSECT
SELECT ... MINUS
Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments.
手册：conceptes
查看file system的block size
linux => tune2fs -l /dev/sda1
linux => dumpe2fs /dev/sda1 (内容很多，建议使用 |grep 'Block size')
windows => fsutil (detail: fsutil fsinfo ntfsinfo C:)
windows => chkdsk (没有确认)
windows的cluster size就是block size
linux => dumpe2fs /dev/sda1 (内容很多，建议使用 |grep 'Block size')
windows => fsutil (detail: fsutil fsinfo ntfsinfo C:)
windows => chkdsk (没有确认)
windows的cluster size就是block size
Tuesday, November 21, 2006
How to recompile invalid objects （Script）
本脚本用于重新编译当前用户下的无效的object，经测试，可在10.2.0.2下运行
CREATE OR REPLACE PROCEDURE RecompileInvalid IS
CURSOR getlist IS SELECT object_type, object_name FROM
user_objects WHERE status = 'INVALID' AND
object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE',
'PACKAGE BODY' );
schemaname VARCHAR2(100);
CURSOR geterr ( objname VARCHAR2, objtype VARCHAR2 ) IS
SELECT text, line, position FROM user_errors WHERE
name = objname AND type = objtype;
BEGIN
SELECT username INTO schemaname FROM user_users;
FOR getlistrec IN getlist LOOP
dbms_output.put_line( 'attempting compile on ' ||
getlistrec.object_name );
dbms_ddl.alter_compile( getlistrec.object_type,
schemaname, getlistrec.object_name );
END LOOP;
FOR getlistrec IN getlist LOOP
dbms_output.put_line( '-*-*-ERROR-*-*-' );
dbms_output.put_line( 'compile failed on ' ||
getlistrec.object_name );
FOR geterrrec IN geterr( getlistrec.object_name,
getlistrec.object_type ) LOOP
dbms_output.put_line( 'line: ' || geterrrec.line ||
' col: ' || geterrrec.position );
dbms_output.put_line( substr( geterrrec.text, 1, 100 ));
END LOOP;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE RecompileInvalid IS
CURSOR getlist IS SELECT object_type, object_name FROM
user_objects WHERE status = 'INVALID' AND
object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE',
'PACKAGE BODY' );
schemaname VARCHAR2(100);
CURSOR geterr ( objname VARCHAR2, objtype VARCHAR2 ) IS
SELECT text, line, position FROM user_errors WHERE
name = objname AND type = objtype;
BEGIN
SELECT username INTO schemaname FROM user_users;
FOR getlistrec IN getlist LOOP
dbms_output.put_line( 'attempting compile on ' ||
getlistrec.object_name );
dbms_ddl.alter_compile( getlistrec.object_type,
schemaname, getlistrec.object_name );
END LOOP;
FOR getlistrec IN getlist LOOP
dbms_output.put_line( '-*-*-ERROR-*-*-' );
dbms_output.put_line( 'compile failed on ' ||
getlistrec.object_name );
FOR geterrrec IN geterr( getlistrec.object_name,
getlistrec.object_type ) LOOP
dbms_output.put_line( 'line: ' || geterrrec.line ||
' col: ' || geterrrec.position );
dbms_output.put_line( substr( geterrrec.text, 1, 100 ));
END LOOP;
END LOOP;
END;
/
How to find ocr and voting disk of your rac
ocr => RHEL /etc/oracle/ocr.loc
voting disk => $ocrdump myfile => vi myfile => [SYSTEM.css.diskfile]
voting disk => $ocrdump myfile => vi myfile => [SYSTEM.css.diskfile]
Monday, November 20, 2006
discuss about parameters in sqlnet.ora
You can not restrict the combination of login and IP.
You can only block all the connections from a range of IPs,by using parameters "TCP.EXCLUDED_NODES",
"TCP.INVITED_NODES", "TCP.VALIDNODE_CHECKING" in sqlnet.ora.
Or, you can restrict login alone through passwords.
You can only block all the connections from a range of IPs,by using parameters "TCP.EXCLUDED_NODES",
"TCP.INVITED_NODES", "TCP.VALIDNODE_CHECKING" in sqlnet.ora.
Or, you can restrict login alone through passwords.
database link的domain部分省略用法
<質問内容>
CREATE DATABASE LINK KDB.WORLD
CONNECT TO USER01 IDENTIFIED BY
USING 'K-DB1'
/
上記のデータベースリンクがあり、
以前は
select * from test@KDB
といった形式でアクセスしていたのですが、
select * from test@KDB.WORLD
といったようにドメイン名を入れないと接続できなくなりました。
以前のようにドメイン名を省略してリモートDBにアクセスしたいのですが、
どうすればよいでしょうか？
<回答内容>
データベースリンクのドメインが global_name と同じ場合は、データベース
リンク使用時にドメイン名を省略した書式（@）で使用することが
可能となります。
例えば次のような場合です。
まずドメイン名を省略したいデータベースリンクを確認します。
SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
-------------------- --------------------
SYSTEM DB1.WORLD
~~~~~~
ドメイン名を省略したいデータベースリンクが存在しているデータベース
の global_name を調べます。
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------
DB2.WORLD
~~~~~~
上記のデータベースリンクのドメイン名は global_name と同じドメイン名
"WORLD" のため データベースリンク "DB1.WORLD" を使用する場合に
"select * from v$instance@db1" のように省略することが可能となります。
お客様の環境では以前にドメイン名を省略できていたとのことですので
global_name の変更が行われた可能性がございます。
global_name の変更は下記のalter database文にて可能ですが
global_name を変更した目的や変更しても問題なかなどをDBA管理者または
global_name を変更された方へ確認したのちに変更されることをお奨め致します。
-----------------------------------------
ALTER DATABASE RENAME GLOBAL_NAME TO db_name.db.domain;
-----------------------------------------
参考情報
MetaLink#117759.1 DATABASE LINK (DBLINK) TROUBLESHOOTING
KROWN#85279 GLOBAL_NAME をドメインなしの名前に変更できない
CREATE DATABASE LINK KDB.WORLD
CONNECT TO USER01 IDENTIFIED BY
USING 'K-DB1'
/
上記のデータベースリンクがあり、
以前は
select * from test@KDB
といった形式でアクセスしていたのですが、
select * from test@KDB.WORLD
といったようにドメイン名を入れないと接続できなくなりました。
以前のようにドメイン名を省略してリモートDBにアクセスしたいのですが、
どうすればよいでしょうか？
<回答内容>
データベースリンクのドメインが global_name と同じ場合は、データベース
リンク使用時にドメイン名を省略した書式（@
可能となります。
例えば次のような場合です。
まずドメイン名を省略したいデータベースリンクを確認します。
SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
-------------------- --------------------
SYSTEM DB1.WORLD
~~~~~~
ドメイン名を省略したいデータベースリンクが存在しているデータベース
の global_name を調べます。
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------
DB2.WORLD
~~~~~~
上記のデータベースリンクのドメイン名は global_name と同じドメイン名
"WORLD" のため データベースリンク "DB1.WORLD" を使用する場合に
"select * from v$instance@db1" のように省略することが可能となります。
お客様の環境では以前にドメイン名を省略できていたとのことですので
global_name の変更が行われた可能性がございます。
global_name の変更は下記のalter database文にて可能ですが
global_name を変更した目的や変更しても問題なかなどをDBA管理者または
global_name を変更された方へ確認したのちに変更されることをお奨め致します。
-----------------------------------------
ALTER DATABASE RENAME GLOBAL_NAME TO db_name.db.domain;
-----------------------------------------
参考情報
MetaLink#117759.1 DATABASE LINK (DBLINK) TROUBLESHOOTING
KROWN#85279 GLOBAL_NAME をドメインなしの名前に変更できない
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE
<質問内容>
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE;
を実行時にアウトライン適用対象のSQLが実行中であった場合影響は
ありますでしょうか？
<回答内容>
こちらにつきましては Parse の動作を行う以前にアウトラインの
使用が決定するという動作になっております。
そのため Parse の動作を行う以前にアウトラインが有効であれば
アウトラインを使用し、Parse が開始されていればアウトラインは
使用しないということになります。
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE;
を実行時にアウトライン適用対象のSQLが実行中であった場合影響は
ありますでしょうか？
<回答内容>
こちらにつきましては Parse の動作を行う以前にアウトラインの
使用が決定するという動作になっております。
そのため Parse の動作を行う以前にアウトラインが有効であれば
アウトラインを使用し、Parse が開始されていればアウトラインは
使用しないということになります。
materialized view truncate vs delete
9i时
单独一个materialized view或者一个refrsh group中只有一个materialized view，使用complete refresh，内部机制是truncate＋insert，refresh过程中对视图查询会返回0条。
包含两个或多个materialized view的refresh group使用complete refresh的时候，内部机制delete＋insert，会产生undo，得到读一致性的效果，refresh过程中对视图查询会得旧的版本。
10g时
内部机制一律是delete＋insert
单独一个materialized view或者一个refrsh group中只有一个materialized view，使用complete refresh，内部机制是truncate＋insert，refresh过程中对视图查询会返回0条。
包含两个或多个materialized view的refresh group使用complete refresh的时候，内部机制delete＋insert，会产生undo，得到读一致性的效果，refresh过程中对视图查询会得旧的版本。
10g时
内部机制一律是delete＋insert
Sunday, November 19, 2006
Oracle MIB(SNMP) on RH linux
テスト環境>
OS : Red Hat Enterprise Linux ES release 3 (Taroon Update 7)
DB : Oracle Database 10g R2 10.2.0.1
Agent : Oracle Agent 10g R2 10.2.0.1
<テスト環境にてKROWN#89798を検証した結果>
手順を開始する前に$ORACLE_HOMEの設定をAgentをインストールした$ORACLE_HOMEに設定すること。
※KROWN中の$ORACLE_HOMEの記述はAgentをインストールした$ORACLE_HOMEを表している。
(install agent => start_peer -a(using root) => start subagent)
KROWN#89789 => 10g R2(10.2.0.1)では概要しない。
KROWN#89779 => KROWNと同様の現象が発生するためKROWNの対策必要。(touch sqlnet.ora)KROWN#89774 => KROWNと同様の現象が発生するためKROWNの対策必要。(user dbsnmp account unlock, set password to "dbsnmp")
KROWN#89792 => 10g R2(10.2.0.1)では概要しない。
KROWN#89781 => 10g R2(10.2.0.1)では概要しない。
KROWN#89780 => 10g R2(10.2.0.1)では概要しない。
========================================
[概要]
10g での SNMP MIB の取得方法について
[対象リリース]
Oracle Management Agent 10.1.0.3 -
[対象プラットフォーム]
すべての プラットフォーム
[詳細]
10.1.0 をご利用の場合は、10g Grid Control に付属の Management
Agent を MIB を取得したいホストにインストールし、10g Grid
Cotnrol パッチセット 10.1.0.3 に含まれた Management Agent
10.1.0.3パッチセット以降が適用されている必要がございます。
(Windowsの場合は 10.1.0.5 パッチセット以降が適用されている必要
があります。)
また、Linux では、KROWN 89789 の方法で HP-UX (PA-RISC) では
KROWN 90157 の方法で、start_peer スクリプトを修正頂く必要が
ございます。
=============================================
Solaris/Linux/HP-UX (PA-RISC)の場合の設定方法
=============================================
1. $ORACLE_HOME/network/snmp/peer ディレクトリの master_peer encap_peer
start_peer のパーミッションを変更します。
% cd $ORACLE_HOME/network/snmp/peer
% chmod 750 *peer
2. ルートユーザーで Oracle Master Agent を起動します。
# cd $ORACLE_HOME/network/snmp/peer
# start_peer -a
3. オラクルユーザーで $ORACLE_HOME/network/admin/sqlnet.ora を作成します。
% cd $ORACLE_HOME/network/admin/
% touch sqlnet.ora
4. オラクルユーザーで emsubagent を起動します。
% emctl start subagent
5. MIB が取得できるかどうかについて確認します。
以下は、WindowsNT Resource Kit に付属の snmputil を使用し表領域の情報
を取得した場合の例です。
D:\usr\bin>snmputil walk pires public .1.3.6.1.4.1.111.4.1.2.1.2
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.1
Value = OCTET STRING - TEST
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.2
Value = OCTET STRING - USERS
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.3
Value = OCTET STRING - CATTAB
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.4
Value = OCTET STRING - SYSAUX
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.5
Value = OCTET STRING - SYSTEM
...
...
=======================
AIX 5L の場合の設定方法
=======================
AIX 5L では OS の SNMP デーモンが Peer アーキテクチャを兼ね備えている為、
OS の SNMP デーモンと emsubagent が直接連携を行う形となる為、構成手順が
若干異なります。
1. /etc/snmpd.conf または /etc/snmpdv3.conf に以下の設定を行います。
smux 0.0 "" <サーバーの IP ADDRESS>
2. root ユーザーで、OS の SNMPD を再起動します。
# stopsrc -s snmpd
# startsrc -s snmpd
3. オラクルユーザーで $ORACLE_HOME/network/admin/sqlnet.ora を作成します。
% cd $ORACLE_HOME/network/admin/
% touch sqlnet.ora
4. オラクルユーザーで環境変数 LIBPATH に $ORACLE_HOME/lib32 を指定します。
bsh/ksh の場合
% export LIBPATH=${ORACLE_HOME}/lib32
csh の場合
% setenv LIBPATH ${ORACLE_HOME}/lib32
5. オラクルユーザーで emsubagent を起動します。
% emctl start subagent
6. MIB が取得できるかどうかについて確認します。
以下は、WindowsNT Resource Kit に付属の snmputil を使用し表領域の情報
を取得した場合の例です。
D:\usr\bin>snmputil walk pires public .1.3.6.1.4.1.111.4.1.2.1.2
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.1
Value = OCTET STRING - TEST
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.2
Value = OCTET STRING - USERS
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.3
Value = OCTET STRING - CATTAB
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.4
Value = OCTET STRING - SYSAUX
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.5
Value = OCTET STRING - SYSTEM
...
...
========================
Windows の場合の設定方法
========================
(10.1.0 をご利用の場合は PSR 10.1.0.5 以降を適用する必要があります。)
1. 以下のサービスを起動します。
SNMP
SNMP Trap Service
OracleSNMPPeerMasterAgent
OracleSNMPPeerEncapsulator
2. コマンドプロンプトより以下を実行します。
set EMDROOT=
emctl start subagent
3. MIB が取得できるかどうかについて確認します。
以下は、WindowsNT Resource Kit に付属の snmputil を使用し表領域の情報
を取得した場合の例です。
D:\usr\bin>snmputil walk pires public .1.3.6.1.4.1.111.4.1.2.1.2
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.1
Value = OCTET STRING - TEST
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.2
Value = OCTET STRING - USERS
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.3
Value = OCTET STRING - CATTAB
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.4
Value = OCTET STRING - SYSAUX
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.5
Value = OCTET STRING - SYSTEM
...
...
[補足事項]
以下の問題にはご注意下さい。
KROWN 89789 linux で、start_peer -a を実行時エラー発生
KROWN 90157 HP-UX で、start_peer -a を実行時エラー発生
KROWN 89774 emctl start subagent を実行しても emsubagent が起動しない
KROWN 89779 emsubagent.trc に ORA-1017 が発生し、MIB の取得が行えない
KROWN 89780 10g の DB に対して OraDBSysParseCount を取得すると generr が発生する
KROWN 89781 rdbmsDbInfoVersion にアクセスすると、emsubagent がコアダンプする
KROWN 89792 oraDbConfigTable の一部の MIB にアクセスすると、GenErr が発生
[FAQ]
Q1.10g では SNMP サポートリファレンスマニュアルは存在しないのでしょうか。
A1.10.2 より、SNMP サポートリファレンスが提供されております。
Q2.Windows 版での対応状況はどうなっているのでしょうか。
A2.Windows 版では、emsubagent の動作に問題がある為、10.1.0.4 以前の
リリースでは MIB の取得が行えませんでした。(Bug:3625444 Bug:4418194)
本問題は PSR 10.1.0.5 にて修正されております。
[更新履歴]
2006/05/11 Windows 版の対応状況を追記
2005/07/27 AIX 5L での構成方法を追記
2005/06/10 FAQ Q2 に、BUG:4418194 を追記
2005/01/17 FAQ に Q2 を追加
2004/10/27 FAQ を追加
2004/10/15 本文書を公開
OS : Red Hat Enterprise Linux ES release 3 (Taroon Update 7)
DB : Oracle Database 10g R2 10.2.0.1
Agent : Oracle Agent 10g R2 10.2.0.1
<テスト環境にてKROWN#89798を検証した結果>
手順を開始する前に$ORACLE_HOMEの設定をAgentをインストールした$ORACLE_HOMEに設定すること。
※KROWN中の$ORACLE_HOMEの記述はAgentをインストールした$ORACLE_HOMEを表している。
(install agent => start_peer -a(using root) => start subagent)
KROWN#89789 => 10g R2(10.2.0.1)では概要しない。
KROWN#89779 => KROWNと同様の現象が発生するためKROWNの対策必要。(touch sqlnet.ora)KROWN#89774 => KROWNと同様の現象が発生するためKROWNの対策必要。(user dbsnmp account unlock, set password to "dbsnmp")
KROWN#89792 => 10g R2(10.2.0.1)では概要しない。
KROWN#89781 => 10g R2(10.2.0.1)では概要しない。
KROWN#89780 => 10g R2(10.2.0.1)では概要しない。
========================================
[概要]
10g での SNMP MIB の取得方法について
[対象リリース]
Oracle Management Agent 10.1.0.3 -
[対象プラットフォーム]
すべての プラットフォーム
[詳細]
10.1.0 をご利用の場合は、10g Grid Control に付属の Management
Agent を MIB を取得したいホストにインストールし、10g Grid
Cotnrol パッチセット 10.1.0.3 に含まれた Management Agent
10.1.0.3パッチセット以降が適用されている必要がございます。
(Windowsの場合は 10.1.0.5 パッチセット以降が適用されている必要
があります。)
また、Linux では、KROWN 89789 の方法で HP-UX (PA-RISC) では
KROWN 90157 の方法で、start_peer スクリプトを修正頂く必要が
ございます。
=============================================
Solaris/Linux/HP-UX (PA-RISC)の場合の設定方法
=============================================
1. $ORACLE_HOME/network/snmp/peer ディレクトリの master_peer encap_peer
start_peer のパーミッションを変更します。
% cd $ORACLE_HOME/network/snmp/peer
% chmod 750 *peer
2. ルートユーザーで Oracle Master Agent を起動します。
# cd $ORACLE_HOME/network/snmp/peer
# start_peer -a
3. オラクルユーザーで $ORACLE_HOME/network/admin/sqlnet.ora を作成します。
% cd $ORACLE_HOME/network/admin/
% touch sqlnet.ora
4. オラクルユーザーで emsubagent を起動します。
% emctl start subagent
5. MIB が取得できるかどうかについて確認します。
以下は、WindowsNT Resource Kit に付属の snmputil を使用し表領域の情報
を取得した場合の例です。
D:\usr\bin>snmputil walk pires public .1.3.6.1.4.1.111.4.1.2.1.2
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.1
Value = OCTET STRING - TEST
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.2
Value = OCTET STRING - USERS
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.3
Value = OCTET STRING - CATTAB
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.4
Value = OCTET STRING - SYSAUX
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.5
Value = OCTET STRING - SYSTEM
...
...
=======================
AIX 5L の場合の設定方法
=======================
AIX 5L では OS の SNMP デーモンが Peer アーキテクチャを兼ね備えている為、
OS の SNMP デーモンと emsubagent が直接連携を行う形となる為、構成手順が
若干異なります。
1. /etc/snmpd.conf または /etc/snmpdv3.conf に以下の設定を行います。
smux 0.0 "" <サーバーの IP ADDRESS>
2. root ユーザーで、OS の SNMPD を再起動します。
# stopsrc -s snmpd
# startsrc -s snmpd
3. オラクルユーザーで $ORACLE_HOME/network/admin/sqlnet.ora を作成します。
% cd $ORACLE_HOME/network/admin/
% touch sqlnet.ora
4. オラクルユーザーで環境変数 LIBPATH に $ORACLE_HOME/lib32 を指定します。
bsh/ksh の場合
% export LIBPATH=${ORACLE_HOME}/lib32
csh の場合
% setenv LIBPATH ${ORACLE_HOME}/lib32
5. オラクルユーザーで emsubagent を起動します。
% emctl start subagent
6. MIB が取得できるかどうかについて確認します。
以下は、WindowsNT Resource Kit に付属の snmputil を使用し表領域の情報
を取得した場合の例です。
D:\usr\bin>snmputil walk pires public .1.3.6.1.4.1.111.4.1.2.1.2
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.1
Value = OCTET STRING - TEST
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.2
Value = OCTET STRING - USERS
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.3
Value = OCTET STRING - CATTAB
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.4
Value = OCTET STRING - SYSAUX
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.5
Value = OCTET STRING - SYSTEM
...
...
========================
Windows の場合の設定方法
========================
(10.1.0 をご利用の場合は PSR 10.1.0.5 以降を適用する必要があります。)
1. 以下のサービスを起動します。
SNMP
SNMP Trap Service
Oracle
Oracle
2. コマンドプロンプトより以下を実行します。
set EMDROOT=
emctl start subagent
3. MIB が取得できるかどうかについて確認します。
以下は、WindowsNT Resource Kit に付属の snmputil を使用し表領域の情報
を取得した場合の例です。
D:\usr\bin>snmputil walk pires public .1.3.6.1.4.1.111.4.1.2.1.2
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.1
Value = OCTET STRING - TEST
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.2
Value = OCTET STRING - USERS
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.3
Value = OCTET STRING - CATTAB
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.4
Value = OCTET STRING - SYSAUX
Variable = .iso.org.dod.internet.private.enterprises.111.4.1.2.1.2.1.5
Value = OCTET STRING - SYSTEM
...
...
[補足事項]
以下の問題にはご注意下さい。
KROWN 89789 linux で、start_peer -a を実行時エラー発生
KROWN 90157 HP-UX で、start_peer -a を実行時エラー発生
KROWN 89774 emctl start subagent を実行しても emsubagent が起動しない
KROWN 89779 emsubagent.trc に ORA-1017 が発生し、MIB の取得が行えない
KROWN 89780 10g の DB に対して OraDBSysParseCount を取得すると generr が発生する
KROWN 89781 rdbmsDbInfoVersion にアクセスすると、emsubagent がコアダンプする
KROWN 89792 oraDbConfigTable の一部の MIB にアクセスすると、GenErr が発生
[FAQ]
Q1.10g では SNMP サポートリファレンスマニュアルは存在しないのでしょうか。
A1.10.2 より、SNMP サポートリファレンスが提供されております。
Q2.Windows 版での対応状況はどうなっているのでしょうか。
A2.Windows 版では、emsubagent の動作に問題がある為、10.1.0.4 以前の
リリースでは MIB の取得が行えませんでした。(Bug:3625444 Bug:4418194)
本問題は PSR 10.1.0.5 にて修正されております。
[更新履歴]
2006/05/11 Windows 版の対応状況を追記
2005/07/27 AIX 5L での構成方法を追記
2005/06/10 FAQ Q2 に、BUG:4418194 を追記
2005/01/17 FAQ に Q2 を追加
2004/10/27 FAQ を追加
2004/10/15 本文書を公開
Wednesday, November 08, 2006
サーバのHOST名、IPの変更に伴う作業(COPIED FROM KROWN)
[Problem]
SERVERのホスト名とIPアドレスの変更を行なった場合に
Oracle側で必要となる作業を教えて下さい。
[Action]
SERVERのホスト名とIPアドレスの変更を行なう場合には、
以下のファイルの変更が必要となります。
ただし、IP の変更だけで設定ファイルへのホスト情報の記述が
IP ではなく、ホスト名で記述されている場合は必要ありません。
(hosts ファイルや DNS などでIP 情報が解決できるため)
SERVER
-------
・listener.ora
host = の部分
server = の部分
・tnsnames.ora
host = の部分
・init.ora
以下のパラメータが記述されており、HOST= の記述が設定されている場合
mts_listener_address
mts_dispatchers
local_listener
・sqlnet.ora:
host = の部分
・cman.ora: (Connection Managerを使用の場合)
host = の部分
src = の部分
dst = の部分
・names.ora: (Oracle Names Serverを使用の場合)
host = の部分
names.server_name = の部分
接続するCLIENT
-------------
・tnsnames.ora
・sqlnet.ora:
host = の部分
また、Oracle Enterprise Manager ジョブ機能、イベント機能を御利用頂いている場合は、
Intelligent Agent が起動されているマシンのホスト名を変更された場合、その内部ファイル
を初期化する必要がございます。詳細は KROWN 文書番号 2392 をご参照下さい。
<注意`>
database linkをご使用の場合は変更となるサーバ本体のループバック用の
database linkや別サーバ上のdatabase linkの接続文字列の確認を忘れないようにしてください。
SERVERのホスト名とIPアドレスの変更を行なった場合に
Oracle側で必要となる作業を教えて下さい。
[Action]
SERVERのホスト名とIPアドレスの変更を行なう場合には、
以下のファイルの変更が必要となります。
ただし、IP の変更だけで設定ファイルへのホスト情報の記述が
IP ではなく、ホスト名で記述されている場合は必要ありません。
(hosts ファイルや DNS などでIP 情報が解決できるため)
SERVER
-------
・listener.ora
host = の部分
server = の部分
・tnsnames.ora
host = の部分
・init
以下のパラメータが記述されており、HOST= の記述が設定されている場合
mts_listener_address
mts_dispatchers
local_listener
・sqlnet.ora:
host = の部分
・cman.ora: (Connection Managerを使用の場合)
host = の部分
src = の部分
dst = の部分
・names.ora: (Oracle Names Serverを使用の場合)
host = の部分
names.server_name = の部分
接続するCLIENT
-------------
・tnsnames.ora
・sqlnet.ora:
host = の部分
また、Oracle Enterprise Manager ジョブ機能、イベント機能を御利用頂いている場合は、
Intelligent Agent が起動されているマシンのホスト名を変更された場合、その内部ファイル
を初期化する必要がございます。詳細は KROWN 文書番号 2392 をご参照下さい。
<注意`>
database linkをご使用の場合は変更となるサーバ本体のループバック用の
database linkや別サーバ上のdatabase linkの接続文字列の確認を忘れないようにしてください。
Monday, November 06, 2006
CPU family
http://processorfinder.intel.com/default.aspx
To check the current CPU information run winmsd.exe in command line mode
To check the current CPU information run winmsd.exe in command line mode
Sunday, November 05, 2006
Rectify Differences Between Two Tables in a Replicated Environment（COPIED FROM METALINK）
Abstract
Script to Rectify Differences between two tables in a Replicated Environment
Product Name, Product Version
Oracle Server Version 7.x, 8.x, 9.x
Platform Platform Independent
Date Created 22-Nov-2002
Instructions
Execution Environment:
SQL, SQL*Plus, iSQL*Plus
Access Privileges:
Requires DBA or RepAdmin privileges
Usage:
sqlplus/
Instructions:
Run the scripts in the following order:
1. DBMSR_CREATE.SQL
2. DBMSR_DIFF.SQL
3. DBMSR_RECTIFY.SQL
The scripts will prompt for input from the user so you should read the
beginning of each script carefully. Pay special attention to the script
DBMSR_RECTIFY.SQL, this is the script that determines what records will be
deleted or added to the sites specified as the COMPARISON site.
The following data types are supported by the scripts:
- CHAR
- DATE
- NUMBER
- RAW
- ROWID
- VARCHAR2
The following data types are not supported by the scripts:
- LOBS (CLOB or BLOB)
- Columns based on user-defined types
The following init.ora parameters are required for optimal performance of
the scripts:
- SORT_DIRECT_WRITES = TRUE
- SORT_WRITE_BUFFERS =
- SORT_WRITE_BUFFER_SIZE =
- SORT_AREA_SIZE = 1M or higher
- compatibility = 7.3 or higher
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected. The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
The following set of scripts will allow you to rectify data differences between
two master to master replicated environment database objects in Oracle version
7.3 and higher. You can use these procedures to resynchronize the contents of
replicated objects that have data discrepancies within your replication
environment.
The scripts contained in this document used the DBMS_RECTIFIER_DIFF package
the procedures contained in the DBMS_RECTIFIER_DIFF package are:
DIFFERENCES:
The DIFFERENCES procedure compares two replicas of a table, and determines
all rows in the first replica that are not in the second and all rows in the
second that are not in the first. The output of this procedure is stored in
two user-created tables. The first table stores the values of the missing
rows, and the second table is used to indicate which site contains each row.
RECTIFY:
The RECTIFY procedure uses the information generated by the DIFFERENCES
procedure to rectify the two tables. Any rows found in the first table and
not in the second are inserted into the second table. Any rows found in the
second table and not in the first are deleted from the second table.
Guidelines for using the DBMS_RECTIFIER_DIFF package:
1. Make sure that each missing_rows table is cleaned up after each rectify.
2. Multiple users should not use the same missing_rows tables.
3. Designate one of the two sites as the truth or reference site.
4. Using this utility could cause problems with referential integrity.
5. It is NOT a requirement that you call dbms_repcat.suspend_master_activity
before running the dbms_rectifier_diff.differences procedure. However, not
quiescing can result in inconsistencies in your data.
DBMS_RECTIFIER_DIFF.DIFFERENCES
-------------------------------
This procedure can take quite a while to run and identifies differences, which
then need to be processed with the DBMS_RECTIFIER_DIFF.RECTIFY procedure. If
the volume of data is large it may be easier to simple reinstantiate the
comparison table by importing an export of the table in question.
The procedure header:
DBMS_RECTIFIER_DIFF.DIFFERENCES(
SNAME1 IN VARCHAR2,
ONAME1 IN VARCHAR2,
REFERNECE_SITE IN VARCHAR2,
SNAME2 IN VARCHAR2,
ONAME2 IN VARCHAR2,
COMPARISON_SITE IN VARCHAR2,
WHERE_CLAUSE IN VARCHAR2,
COLUMN_LIST IN VARCHAR2
| ARRAY_COLUMNS IN DBMS_UTILITY.NAME_ARRAY},
MISSING_ROWS_SNAME IN VARCHAR2,
MISSING_ROWS_ONAME1 IN VARCHAR2,
MISSING_ROWS_ONAME2 IN VARCHAR2,
MISSING_ROWS_SITE IN VARCHAR2,
MAX_MISSING IN INTEGER,
COMMIT_ROWS IN INTEGER);
Parameters:
1. Sname1 - Name of the schema that owns oname1.
2. Oname1 - Name of the table at the reference site.
3. Reference_site - Global_name of the site that will contain the truth
table. If this is set to null it will be assigned the
value of the local site.
4. Sname2 - Schema that owns oname2.
5. Oname2 - Comparison table.
6. Comparison_site - Global_name of the site that contains comparison table.
7. Where_clause - Optional parameter used to limit the number of rows that
will be compared.
8. Column_list - Comma separated list of one or more columns whose values
will be compared.
9. Missing_rows_sname - Schema that owns missing_rows_oname1.
10. Missing_rows_oname1 - Table containing rows that don't exist on both sites
11. Missing_rows_oname2 - Table that holds information stating which table
owns each row listed in missing_rows_oname1.
12. Missing_rows_site - Global name of the site that contains
missing_rows_oname1 and missing_rows_oname2.
13. Max_missing - Maximum number of rows that can be inserted into
missing_rows_oname1. This can be set to any number that
is greater than 1.
14. Commit_rows - A commit will be issued after this many rows have been
inserted into missing_row_oname1.
The execution of this procedure may generate the following exceptions:
ORA-23377 - oname1 is not the same as missing_rows_oname1.
ORA-23368 - either sname, oname, missing_rows_sname or missing_rows_oname
is null.
ORA-23366 - the max_missing parameter is set to anumber less than 1.
ORA-23302 - a remote site is not accessible.
ORA-23308 - tables oname1, oname2, missing_rows_oname1, or missing_rows_oname2
do not exist.
ORA-23365 - the reference_site, comparison_site, or missing_rows_site does
not contain a site name.
DBMS_RECTIFIER_DIFF.RECTIFY
---------------------------
This procedure synchronizes the reference table(oname1). Before executing this
procedure make sure that any updates that will be performed on the comparison
table(oname2) will not violate any constraints. This procedure does not modify
the reference table.
Procedure header:
DBMS_RECTIFIER_DIFF.RECTIFY(
SNAME1 IN VARCHAR2,
ONAME1 IN VARCHAR2,
REFERENCE_SITE IN VARCHAR2,
SNAME2 IN VARCHAR2,
ONAME2 IN VARCHAR2,
COMPARISON_SITE IN VARCHAR2,
{COLUMN_LIST IN VARCHAR2
| ARRAY_COLUMNS IN DBMS_UTILITY.NAME_ARRAY},
MISSING_ROWS_SNAME IN VARCHAR2,
MISSING_ROWS_ONAME1 IN VARCHAR2,
MISSING_ROWS_ONAME2 IN VARCHAR2,
MISSING_ROWS_SITE IN VARCHAR2,
COMMIT_ROWS IN INTEGER);
Parameters.
1. sname1 - Schema that owns oname1.
2. oname1 - The truth Table at the reference_site.
3. reference_site - Global_name of the site that contains the truth table.
4. sname2 - Schema that owns oname2.
5. oname2 - Comparison table.
6. comparison_site - Global name of the site that contains comparison table.
7. column_list - Comma-separated list of the columns that are being
compared.
8. array_columns - A PL/SQL table of column names, either column_list or
array_columns can be passed.
9. missing_rows_sname - Schema that owns missing_rows_oname1.
10. missing_rows_sname1 - Table containing records that do not exist in both
the truth table and comparison table.
11. missing_rows_sname2 - Table that holds information that states which table
contains each missing record.
12. missing_rows_site - Global name of the site where tables
missing_rows_oname1 and missing_rows_oname2 reside.
13. commit_rows - Forces a commit after so many rows have been inserted into
missing_row_oname1.
The execution of the DBMS_RECTIFIER_DIFF.RECTIFY procedure can generate the
following exceptions:
ORA-23368 - sname, oname, missing_rows_sname or missing_rows_oname is
set to NULL.
ORA-23366 - parameter max_missing is set to a number that is less than 0.
ORA-23302 - remote site is not accessible.
ORA-23308 - oname1, oname2, missing_rows_oname1, or missing_rows_oname2 does
not exist.
ORA-02094 - replication is not linked to the kernel.
ORA-23365 - reference_site, comparison_site, or missing_rows_site does not
contain a site name.
Restrictions on the execution of this procedure.
1. If duplicate rows exist in the reference table but not in the comparison
table they will be inserted into the comparison table.
2. If duplicate rows exist in the comparison table but not in the reference
table they will deleted from the comparison table.
References
Distributed Systems Volume II (Oracle7) or Oracle8 Replication manuals
for a complete explanation of how to use package DBMS_RECTIFIER_DIFF
prior to running these scripts.
Script
rem script DBMSR_CREATE.SQL
rem RUN THIS SCRIPT FIRST!
rem
set echo off
set verify off
set concat on
prompt #################################################################
prompt CREATE MISSING_ROWS_DATA AND MISSING_ROWS_LOCATION TABLES
prompt #################################################################
prompt
prompt #################################################################
prompt #
prompt # This script will ask for input to create two different
prompt # tables that will be used to store and rectify differences
prompt # between two replicas in the replicated environment.
prompt #
prompt # INPUT:
prompt #
prompt # 1) SCHEMA to hold the MISSING ROWS tables.
prompt # 2) Table1 >MISSING_ROWS_DATA..(Data for missing rows)
prompt # 3) Table2 >MISSING_ROWS_LOCATION..(Location of missing rows)
prompt # 4) REFERENCE Table, the replication table that is being
prompt # Rectified.
prompt #
prompt #################################################################
prompt
accept SCHEMA prompt 'Enter SCHEMA to hold the MISSING ROWS table: '
prompt
prompt
prompt Example: MR_DATA_
accept MR_DATA1 prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept MR_LOC1 prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
prompt Example:
accept Table1 prompt 'Enter name of the REFERENCE table: '
create table &SCHEMA..&MR_DATA1
as select * from &TABLE1 where
0=1;
create table &SCHEMA..&MR_LOC1
(
present VARCHAR(128),
absent VARCHAR(128),
r_id ROWID
);
commit;
---------------------------------cut----------------------------------
rem script DBMSR_DIFF.SQL
rem RUN THIS SCRIPT SECOND!
rem
set echo off
set verify off
prompt
REM DBMS_RECTIFIER_DIFF.DIFFERENCES procedure compares two replicas of a
REM table, and determines all rows in the FIRST (REFERENCE) replica that
REM are not in the SECOND (COMPARISON) and all rows in the second that
REM are not in the first. The output of this procedure is stored in two
REM USER-CREATED tables. Table A stores the values of the missing rows,
REM and Table B is used to indicate which site contains each row.
prompt #############################################################
prompt DBMS_RECTIFIER_DIFF.DIFFERENCES
prompt #############################################################
prompt
prompt The REFERENCE site will be used to update all other replicas
prompt of the table as needed
prompt #############################################################
prompt
accept SCHEMA1 prompt 'Enter name of schema on REFERENCE site: '
accept OBJECT1 prompt 'Enter name of table on REFERENCE site: '
accept REFERENCE1 prompt 'Enter name of REFERENCE site: '
prompt
accept SCHEMA2 prompt 'Enter name of schema on COMPARISON site: '
accept OBJECT2 prompt 'Enter name of table on COMPARISON site: '
accept COMPARISON2 prompt 'Enter name of COMPARISON site: '
prompt
accept MR_SCHEMA prompt 'Enter schema holding missing rows tables: '
prompt Example: MR_DATA_
accept MR_DATA2 prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept MR_LOC2 prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
accept SITE prompt 'Enter name of site that contains MISSING ROWS tables: '
prompt
BEGIN DBMS_RECTIFIER_DIFF.DIFFERENCES(
sname1 => '&SCHEMA1',
oname1 => '&OBJECT1',
reference_site => '&REFERENCE1',
sname2 => '&SCHEMA2',
oname2 => '&OBJECT2',
comparison_site => '&COMPARISON2',
where_clause => '',
column_list => '',
missing_rows_sname => '&MR_SCHEMA',
missing_rows_oname1 => '&MR_DATA2',
missing_rows_oname2 => '&MR_LOC2',
missing_rows_site => '&SITE',
max_missing => 100,
commit_rows => 50);
END;
/
prompt
prompt ################################################################
prompt
prompt You may now review your MISSING ROWS DATA table and your MISSING
prompt ROWS LOCATION table to determine where the differences are
prompt
prompt ################################################################
---------------------------------cut----------------------------------
rem script DBMSR_RECTIFY.SQL
rem RUN THIS SCRIPT LAST!
rem
set echo off
set verify off
prompt
REM DBMS_RECTIFIER_DIFF.RECTIFY resolves the differences between two
REM tables. It uses the information generated by the DIFFERENCES
REM procedure to rectify the two tables. Any rows found in the FIRST
REM (REFERENCE) table and not in the SECOND(COMPARISON) table are
REM INSERTED into the SECOND table. Any rows found in the SECOND table
REM and not in the first are DELETED from the SECOND table.
prompt #############################################################
prompt DBMS_RECTIFIER_DIFF.RECTIFY
prompt #############################################################
prompt
prompt The REFERENCE site will be used to update all other replicas
prompt of the table as needed
prompt #############################################################
prompt
accept SCHEMA1R prompt 'Enter name of schema on REFERENCE site: '
accept OBJECT1R prompt 'Enter name of table on REFERENCE site: '
accept REFERENCE1R prompt 'Enter name of REFERENCE site: '
prompt
accept SCHEMA2R prompt 'Enter name of schema on COMPARISON site: '
accept OBJECT2R prompt 'Enter name of table on COMPARISON site: '
accept COMPARISON2R prompt 'Enter name of COMPARISON site: '
prompt
accept MR_SCHEMAR prompt 'Enter schema holding missing rows tables: '
prompt
prompt Example: MR_DATA_
accept MR_DATA1R prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept LOC1R prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
accept SITER prompt 'Enter name of site that contains MISSING ROWS tables: '
prompt
BEGIN DBMS_RECTIFIER_DIFF.RECTIFY(
sname1 => '&SCHEMA1R',
oname1 => '&OBJECT1R',
reference_site => '&REFERENCE1R',
sname2 => '&SCHEMA2R',
oname2 => '&OBJECT2R',
comparison_site => '&COMPARISON2R',
column_list => '',
missing_rows_sname => '&MR_SCHEMAR',
missing_rows_oname1 => '&MR_DATA1R',
missing_rows_oname2 => '&LOC1R',
missing_rows_site => '&SITER',
commit_rows => 50);
END;
/
Sample Output
Before you begin, you need to decide if you want to quiesce the replication
system that you are trying to rectify. It is NOT a requirement that you call
suspend_master_activity before running the dbms_rectifier_diff.differences
procedure (script 2). However, not quiescing can result in inconsistencies
in your data.
Before we begin, this is how my tables appear. They are out of synch.
SQL> SELECT * FROM SCOTT.DEPT@R1734.WORLD;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ESCD ORLANDO
60 RDBMS ORLANDO
6 rows selected.
SQL> SELECT * FROM SCOTT.DEPT@R2734.WORLD;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
Here I run the first script which creates the tables I need to find the
differences of the 2 sites:
SQL> @create
#################################################################
CREATE MISSING_ROWS_DATA AND MISSING_ROWS_LOCATION TABLES
#################################################################
#################################################################
# #
# This script will ask for input to create two different #
# tables that will be used to store and rectify differences #
# between two replicas in the replicated environment. #
# #
# INPUT: #
# #
# 1) SCHEMA to hold the MISSING ROWS tables. #
# 2) Table1 >MISSING_ROWS_DATA..(Data for missing rows) #
# 3) Table2 >MISSING_ROWS_LOCATION..(Location of missing rows) #
# 4) REFERENCE Table, the replication table that is being #
# Rectified. #
# #
#################################################################
Enter SCHEMA to hold the MISSING ROWS table:REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Example:
Enter name of the REFERENCE table: SCOTT.DEPT
Table created.
Table created.
Commit complete.
SQL> spool off
Here I run the second script that finds the differences of sites: R1734.WORLD
and R2734.WORLD
SQL> @diff
#############################################################
DBMS_RECTIFIER_DIFF.DIFFERENCES
#############################################################
The REFERENCE site will be used to update all other replicas
of the table as needed
#############################################################
Enter name of schema on REFERENCE site: SCOTT
Enter name of table on REFERENCE site: DEPT
Enter name of REFERENCE site: R2734.WORLD
Enter name of schema on COMPARISON site: SCOTT
Enter name of table on COMPARISON site: DEPT
Enter name of COMPARISON site: R1734.WORLD
Enter schema holding missing rows tables: REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Enter name of site that contains MISSING ROWS tables: R1734.WORLD
PL/SQL procedure successfully completed.
##################################################################
You may now review your MISSING ROWS DATA table and your
MISSING ROWS LOCATION table to determine where the differences are
##################################################################
SQL> SPOOL OFF
Here I run the third and final script that will rectify the differences
between both sites. I chose site: R2734.WORLD as my reference site.
SQL> @rectify
#############################################################
DBMS_RECTIFIER_DIFF.RECTIFY
#############################################################
The REFERENCE site will be used to update all other replicas
of the table as needed
#############################################################
Enter name of schema on REFERENCE site: SCOTT
Enter name of table on REFERENCE site: DEPT
Enter name of REFERENCE site: R2734.WORLD
Enter name of schema on COMPARISON site: SCOTT
Enter name of table on COMPARISON site: DEPT
Enter name of COMPARISON site: R1734.WORLD
Enter schema holding missing rows tables: REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Enter name of site that contains MISSING ROWS tables: R1734.WORLD
PL/SQL procedure successfully completed.
Here is the output of my tables after it has been rectified. They are now
in synch.
SQL> select * from scott.dept@r1734.world;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
SQL> select * from scott.dept@r2734.world;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
After a successful execution of the the rectify procedure, the MISSING ROWS
DATA and MISSING ROWS LOCATION tables should be empty:
SQL> select * from missing_rows;
no rows selected
SQL> select * from location;
no rows selected
SQL> spool off
Script to Rectify Differences between two tables in a Replicated Environment
Product Name, Product Version
Oracle Server Version 7.x, 8.x, 9.x
Platform Platform Independent
Date Created 22-Nov-2002
Instructions
Execution Environment:
SQL, SQL*Plus, iSQL*Plus
Access Privileges:
Requires DBA or RepAdmin privileges
Usage:
sqlplus
Instructions:
Run the scripts in the following order:
1. DBMSR_CREATE.SQL
2. DBMSR_DIFF.SQL
3. DBMSR_RECTIFY.SQL
The scripts will prompt for input from the user so you should read the
beginning of each script carefully. Pay special attention to the script
DBMSR_RECTIFY.SQL, this is the script that determines what records will be
deleted or added to the sites specified as the COMPARISON site.
The following data types are supported by the scripts:
- CHAR
- DATE
- NUMBER
- RAW
- ROWID
- VARCHAR2
The following data types are not supported by the scripts:
- LOBS (CLOB or BLOB)
- Columns based on user-defined types
The following init
the scripts:
- SORT_DIRECT_WRITES = TRUE
- SORT_WRITE_BUFFERS =
- SORT_WRITE_BUFFER_SIZE =
- SORT_AREA_SIZE = 1M or higher
- compatibility = 7.3 or higher
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected. The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
The following set of scripts will allow you to rectify data differences between
two master to master replicated environment database objects in Oracle version
7.3 and higher. You can use these procedures to resynchronize the contents of
replicated objects that have data discrepancies within your replication
environment.
The scripts contained in this document used the DBMS_RECTIFIER_DIFF package
the procedures contained in the DBMS_RECTIFIER_DIFF package are:
DIFFERENCES:
The DIFFERENCES procedure compares two replicas of a table, and determines
all rows in the first replica that are not in the second and all rows in the
second that are not in the first. The output of this procedure is stored in
two user-created tables. The first table stores the values of the missing
rows, and the second table is used to indicate which site contains each row.
RECTIFY:
The RECTIFY procedure uses the information generated by the DIFFERENCES
procedure to rectify the two tables. Any rows found in the first table and
not in the second are inserted into the second table. Any rows found in the
second table and not in the first are deleted from the second table.
Guidelines for using the DBMS_RECTIFIER_DIFF package:
1. Make sure that each missing_rows table is cleaned up after each rectify.
2. Multiple users should not use the same missing_rows tables.
3. Designate one of the two sites as the truth or reference site.
4. Using this utility could cause problems with referential integrity.
5. It is NOT a requirement that you call dbms_repcat.suspend_master_activity
before running the dbms_rectifier_diff.differences procedure. However, not
quiescing can result in inconsistencies in your data.
DBMS_RECTIFIER_DIFF.DIFFERENCES
-------------------------------
This procedure can take quite a while to run and identifies differences, which
then need to be processed with the DBMS_RECTIFIER_DIFF.RECTIFY procedure. If
the volume of data is large it may be easier to simple reinstantiate the
comparison table by importing an export of the table in question.
The procedure header:
DBMS_RECTIFIER_DIFF.DIFFERENCES(
SNAME1 IN VARCHAR2,
ONAME1 IN VARCHAR2,
REFERNECE_SITE IN VARCHAR2,
SNAME2 IN VARCHAR2,
ONAME2 IN VARCHAR2,
COMPARISON_SITE IN VARCHAR2,
WHERE_CLAUSE IN VARCHAR2,
COLUMN_LIST IN VARCHAR2
| ARRAY_COLUMNS IN DBMS_UTILITY.NAME_ARRAY},
MISSING_ROWS_SNAME IN VARCHAR2,
MISSING_ROWS_ONAME1 IN VARCHAR2,
MISSING_ROWS_ONAME2 IN VARCHAR2,
MISSING_ROWS_SITE IN VARCHAR2,
MAX_MISSING IN INTEGER,
COMMIT_ROWS IN INTEGER);
Parameters:
1. Sname1 - Name of the schema that owns oname1.
2. Oname1 - Name of the table at the reference site.
3. Reference_site - Global_name of the site that will contain the truth
table. If this is set to null it will be assigned the
value of the local site.
4. Sname2 - Schema that owns oname2.
5. Oname2 - Comparison table.
6. Comparison_site - Global_name of the site that contains comparison table.
7. Where_clause - Optional parameter used to limit the number of rows that
will be compared.
8. Column_list - Comma separated list of one or more columns whose values
will be compared.
9. Missing_rows_sname - Schema that owns missing_rows_oname1.
10. Missing_rows_oname1 - Table containing rows that don't exist on both sites
11. Missing_rows_oname2 - Table that holds information stating which table
owns each row listed in missing_rows_oname1.
12. Missing_rows_site - Global name of the site that contains
missing_rows_oname1 and missing_rows_oname2.
13. Max_missing - Maximum number of rows that can be inserted into
missing_rows_oname1. This can be set to any number that
is greater than 1.
14. Commit_rows - A commit will be issued after this many rows have been
inserted into missing_row_oname1.
The execution of this procedure may generate the following exceptions:
ORA-23377 - oname1 is not the same as missing_rows_oname1.
ORA-23368 - either sname, oname, missing_rows_sname or missing_rows_oname
is null.
ORA-23366 - the max_missing parameter is set to anumber less than 1.
ORA-23302 - a remote site is not accessible.
ORA-23308 - tables oname1, oname2, missing_rows_oname1, or missing_rows_oname2
do not exist.
ORA-23365 - the reference_site, comparison_site, or missing_rows_site does
not contain a site name.
DBMS_RECTIFIER_DIFF.RECTIFY
---------------------------
This procedure synchronizes the reference table(oname1). Before executing this
procedure make sure that any updates that will be performed on the comparison
table(oname2) will not violate any constraints. This procedure does not modify
the reference table.
Procedure header:
DBMS_RECTIFIER_DIFF.RECTIFY(
SNAME1 IN VARCHAR2,
ONAME1 IN VARCHAR2,
REFERENCE_SITE IN VARCHAR2,
SNAME2 IN VARCHAR2,
ONAME2 IN VARCHAR2,
COMPARISON_SITE IN VARCHAR2,
{COLUMN_LIST IN VARCHAR2
| ARRAY_COLUMNS IN DBMS_UTILITY.NAME_ARRAY},
MISSING_ROWS_SNAME IN VARCHAR2,
MISSING_ROWS_ONAME1 IN VARCHAR2,
MISSING_ROWS_ONAME2 IN VARCHAR2,
MISSING_ROWS_SITE IN VARCHAR2,
COMMIT_ROWS IN INTEGER);
Parameters.
1. sname1 - Schema that owns oname1.
2. oname1 - The truth Table at the reference_site.
3. reference_site - Global_name of the site that contains the truth table.
4. sname2 - Schema that owns oname2.
5. oname2 - Comparison table.
6. comparison_site - Global name of the site that contains comparison table.
7. column_list - Comma-separated list of the columns that are being
compared.
8. array_columns - A PL/SQL table of column names, either column_list or
array_columns can be passed.
9. missing_rows_sname - Schema that owns missing_rows_oname1.
10. missing_rows_sname1 - Table containing records that do not exist in both
the truth table and comparison table.
11. missing_rows_sname2 - Table that holds information that states which table
contains each missing record.
12. missing_rows_site - Global name of the site where tables
missing_rows_oname1 and missing_rows_oname2 reside.
13. commit_rows - Forces a commit after so many rows have been inserted into
missing_row_oname1.
The execution of the DBMS_RECTIFIER_DIFF.RECTIFY procedure can generate the
following exceptions:
ORA-23368 - sname, oname, missing_rows_sname or missing_rows_oname is
set to NULL.
ORA-23366 - parameter max_missing is set to a number that is less than 0.
ORA-23302 - remote site is not accessible.
ORA-23308 - oname1, oname2, missing_rows_oname1, or missing_rows_oname2 does
not exist.
ORA-02094 - replication is not linked to the kernel.
ORA-23365 - reference_site, comparison_site, or missing_rows_site does not
contain a site name.
Restrictions on the execution of this procedure.
1. If duplicate rows exist in the reference table but not in the comparison
table they will be inserted into the comparison table.
2. If duplicate rows exist in the comparison table but not in the reference
table they will deleted from the comparison table.
References
Distributed Systems Volume II (Oracle7) or Oracle8 Replication manuals
for a complete explanation of how to use package DBMS_RECTIFIER_DIFF
prior to running these scripts.
Script
rem script DBMSR_CREATE.SQL
rem RUN THIS SCRIPT FIRST!
rem
set echo off
set verify off
set concat on
prompt #################################################################
prompt CREATE MISSING_ROWS_DATA AND MISSING_ROWS_LOCATION TABLES
prompt #################################################################
prompt
prompt #################################################################
prompt #
prompt # This script will ask for input to create two different
prompt # tables that will be used to store and rectify differences
prompt # between two replicas in the replicated environment.
prompt #
prompt # INPUT:
prompt #
prompt # 1) SCHEMA to hold the MISSING ROWS tables.
prompt # 2) Table1 >MISSING_ROWS_DATA..(Data for missing rows)
prompt # 3) Table2 >MISSING_ROWS_LOCATION..(Location of missing rows)
prompt # 4) REFERENCE Table, the replication table that is being
prompt # Rectified.
prompt #
prompt #################################################################
prompt
accept SCHEMA prompt 'Enter SCHEMA to hold the MISSING ROWS table: '
prompt
prompt
prompt Example: MR_DATA_
accept MR_DATA1 prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept MR_LOC1 prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
prompt Example:
accept Table1 prompt 'Enter name of the REFERENCE table: '
create table &SCHEMA..&MR_DATA1
as select * from &TABLE1 where
0=1;
create table &SCHEMA..&MR_LOC1
(
present VARCHAR(128),
absent VARCHAR(128),
r_id ROWID
);
commit;
---------------------------------cut----------------------------------
rem script DBMSR_DIFF.SQL
rem RUN THIS SCRIPT SECOND!
rem
set echo off
set verify off
prompt
REM DBMS_RECTIFIER_DIFF.DIFFERENCES procedure compares two replicas of a
REM table, and determines all rows in the FIRST (REFERENCE) replica that
REM are not in the SECOND (COMPARISON) and all rows in the second that
REM are not in the first. The output of this procedure is stored in two
REM USER-CREATED tables. Table A stores the values of the missing rows,
REM and Table B is used to indicate which site contains each row.
prompt #############################################################
prompt DBMS_RECTIFIER_DIFF.DIFFERENCES
prompt #############################################################
prompt
prompt The REFERENCE site will be used to update all other replicas
prompt of the table as needed
prompt #############################################################
prompt
accept SCHEMA1 prompt 'Enter name of schema on REFERENCE site: '
accept OBJECT1 prompt 'Enter name of table on REFERENCE site: '
accept REFERENCE1 prompt 'Enter name of REFERENCE site: '
prompt
accept SCHEMA2 prompt 'Enter name of schema on COMPARISON site: '
accept OBJECT2 prompt 'Enter name of table on COMPARISON site: '
accept COMPARISON2 prompt 'Enter name of COMPARISON site: '
prompt
accept MR_SCHEMA prompt 'Enter schema holding missing rows tables: '
prompt Example: MR_DATA_
accept MR_DATA2 prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept MR_LOC2 prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
accept SITE prompt 'Enter name of site that contains MISSING ROWS tables: '
prompt
BEGIN DBMS_RECTIFIER_DIFF.DIFFERENCES(
sname1 => '&SCHEMA1',
oname1 => '&OBJECT1',
reference_site => '&REFERENCE1',
sname2 => '&SCHEMA2',
oname2 => '&OBJECT2',
comparison_site => '&COMPARISON2',
where_clause => '',
column_list => '',
missing_rows_sname => '&MR_SCHEMA',
missing_rows_oname1 => '&MR_DATA2',
missing_rows_oname2 => '&MR_LOC2',
missing_rows_site => '&SITE',
max_missing => 100,
commit_rows => 50);
END;
/
prompt
prompt ################################################################
prompt
prompt You may now review your MISSING ROWS DATA table and your MISSING
prompt ROWS LOCATION table to determine where the differences are
prompt
prompt ################################################################
---------------------------------cut----------------------------------
rem script DBMSR_RECTIFY.SQL
rem RUN THIS SCRIPT LAST!
rem
set echo off
set verify off
prompt
REM DBMS_RECTIFIER_DIFF.RECTIFY resolves the differences between two
REM tables. It uses the information generated by the DIFFERENCES
REM procedure to rectify the two tables. Any rows found in the FIRST
REM (REFERENCE) table and not in the SECOND(COMPARISON) table are
REM INSERTED into the SECOND table. Any rows found in the SECOND table
REM and not in the first are DELETED from the SECOND table.
prompt #############################################################
prompt DBMS_RECTIFIER_DIFF.RECTIFY
prompt #############################################################
prompt
prompt The REFERENCE site will be used to update all other replicas
prompt of the table as needed
prompt #############################################################
prompt
accept SCHEMA1R prompt 'Enter name of schema on REFERENCE site: '
accept OBJECT1R prompt 'Enter name of table on REFERENCE site: '
accept REFERENCE1R prompt 'Enter name of REFERENCE site: '
prompt
accept SCHEMA2R prompt 'Enter name of schema on COMPARISON site: '
accept OBJECT2R prompt 'Enter name of table on COMPARISON site: '
accept COMPARISON2R prompt 'Enter name of COMPARISON site: '
prompt
accept MR_SCHEMAR prompt 'Enter schema holding missing rows tables: '
prompt
prompt Example: MR_DATA_
accept MR_DATA1R prompt 'Enter name of MISSING ROWS DATA table: '
prompt
prompt Example: MR_LOCATION_
accept LOC1R prompt 'Enter name of the MISSING ROWS LOCATION table: '
prompt
accept SITER prompt 'Enter name of site that contains MISSING ROWS tables: '
prompt
BEGIN DBMS_RECTIFIER_DIFF.RECTIFY(
sname1 => '&SCHEMA1R',
oname1 => '&OBJECT1R',
reference_site => '&REFERENCE1R',
sname2 => '&SCHEMA2R',
oname2 => '&OBJECT2R',
comparison_site => '&COMPARISON2R',
column_list => '',
missing_rows_sname => '&MR_SCHEMAR',
missing_rows_oname1 => '&MR_DATA1R',
missing_rows_oname2 => '&LOC1R',
missing_rows_site => '&SITER',
commit_rows => 50);
END;
/
Sample Output
Before you begin, you need to decide if you want to quiesce the replication
system that you are trying to rectify. It is NOT a requirement that you call
suspend_master_activity before running the dbms_rectifier_diff.differences
procedure (script 2). However, not quiescing can result in inconsistencies
in your data.
Before we begin, this is how my tables appear. They are out of synch.
SQL> SELECT * FROM SCOTT.DEPT@R1734.WORLD;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ESCD ORLANDO
60 RDBMS ORLANDO
6 rows selected.
SQL> SELECT * FROM SCOTT.DEPT@R2734.WORLD;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
Here I run the first script which creates the tables I need to find the
differences of the 2 sites:
SQL> @create
#################################################################
CREATE MISSING_ROWS_DATA AND MISSING_ROWS_LOCATION TABLES
#################################################################
#################################################################
# #
# This script will ask for input to create two different #
# tables that will be used to store and rectify differences #
# between two replicas in the replicated environment. #
# #
# INPUT: #
# #
# 1) SCHEMA to hold the MISSING ROWS tables. #
# 2) Table1 >MISSING_ROWS_DATA..(Data for missing rows) #
# 3) Table2 >MISSING_ROWS_LOCATION..(Location of missing rows) #
# 4) REFERENCE Table, the replication table that is being #
# Rectified. #
# #
#################################################################
Enter SCHEMA to hold the MISSING ROWS table:REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Example:
Enter name of the REFERENCE table: SCOTT.DEPT
Table created.
Table created.
Commit complete.
SQL> spool off
Here I run the second script that finds the differences of sites: R1734.WORLD
and R2734.WORLD
SQL> @diff
#############################################################
DBMS_RECTIFIER_DIFF.DIFFERENCES
#############################################################
The REFERENCE site will be used to update all other replicas
of the table as needed
#############################################################
Enter name of schema on REFERENCE site: SCOTT
Enter name of table on REFERENCE site: DEPT
Enter name of REFERENCE site: R2734.WORLD
Enter name of schema on COMPARISON site: SCOTT
Enter name of table on COMPARISON site: DEPT
Enter name of COMPARISON site: R1734.WORLD
Enter schema holding missing rows tables: REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Enter name of site that contains MISSING ROWS tables: R1734.WORLD
PL/SQL procedure successfully completed.
##################################################################
You may now review your MISSING ROWS DATA table and your
MISSING ROWS LOCATION table to determine where the differences are
##################################################################
SQL> SPOOL OFF
Here I run the third and final script that will rectify the differences
between both sites. I chose site: R2734.WORLD as my reference site.
SQL> @rectify
#############################################################
DBMS_RECTIFIER_DIFF.RECTIFY
#############################################################
The REFERENCE site will be used to update all other replicas
of the table as needed
#############################################################
Enter name of schema on REFERENCE site: SCOTT
Enter name of table on REFERENCE site: DEPT
Enter name of REFERENCE site: R2734.WORLD
Enter name of schema on COMPARISON site: SCOTT
Enter name of table on COMPARISON site: DEPT
Enter name of COMPARISON site: R1734.WORLD
Enter schema holding missing rows tables: REPADMIN
Example: MR_DATA_
Enter name of MISSING ROWS DATA table: MISSING_ROWS
Example: MR_LOCATION_
Enter name of the MISSING ROWS LOCATION table: LOCATION
Enter name of site that contains MISSING ROWS tables: R1734.WORLD
PL/SQL procedure successfully completed.
Here is the output of my tables after it has been rectified. They are now
in synch.
SQL> select * from scott.dept@r1734.world;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
SQL> select * from scott.dept@r2734.world;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 RDBMS ORLANDO
70 UNIX ORLANDO
6 rows selected.
After a successful execution of the the rectify procedure, the MISSING ROWS
DATA and MISSING ROWS LOCATION tables should be empty:
SQL> select * from missing_rows;
no rows selected
SQL> select * from location;
no rows selected
SQL> spool off
Execution of LOGON trigger results in ORA-12841（COPIED from METALINK）
The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
This problem can occur on any platform.
Symptoms
A LOGON trigger has been created to enable a PARALLEL DML during the session
i.e.
CREATE OR REPLACE TRIGGER LOG_ON AFTER
LOGON ON DATABASE
BEGIN
execute immediate 'alter session enable parallel dml';
END;
LOGON trigger throws the following error:
ORA-604: error occurred at recursive SQL level 1
ORA-12841: Cannot alter the session parallel DML state within a transaction
ORA-6512: at line 2
Cause
Parallel DML cannot be enabled during the transaction.
i.e.
SQL> insert into emp values ('fdfg');
1 row created.
SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction
-- The transaction should be completed before enabling the PARALLEL DML
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
Logon trigger executes as an autonomous transaction and that is why the error ORA-12841 occurs.
Fix
1) invoke "alter session enable parallel dml" in other autonomous transaction through a function.
2) call a function in a trigger
e.g.
create or replace function myfunc return number is
PRAGMA AUTONOMOUS_TRANSACTION;
x number:=0;
begin
execute immediate 'alter session enable parallel dml';
return x;
end;
create or replace trigger log_on after
logon on database
declare
x number;
begin
x := myfunc;
end;
References
Bug 3129124 - Logon Trigger Works In 8i But Fails In 9i With Ora-12841
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
This problem can occur on any platform.
Symptoms
A LOGON trigger has been created to enable a PARALLEL DML during the session
i.e.
CREATE OR REPLACE TRIGGER LOG_ON AFTER
LOGON ON DATABASE
BEGIN
execute immediate 'alter session enable parallel dml';
END;
LOGON trigger throws the following error:
ORA-604: error occurred at recursive SQL level 1
ORA-12841: Cannot alter the session parallel DML state within a transaction
ORA-6512: at line 2
Cause
Parallel DML cannot be enabled during the transaction.
i.e.
SQL> insert into emp values ('fdfg');
1 row created.
SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction
-- The transaction should be completed before enabling the PARALLEL DML
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
Logon trigger executes as an autonomous transaction and that is why the error ORA-12841 occurs.
Fix
1) invoke "alter session enable parallel dml" in other autonomous transaction through a function.
2) call a function in a trigger
e.g.
create or replace function myfunc return number is
PRAGMA AUTONOMOUS_TRANSACTION;
x number:=0;
begin
execute immediate 'alter session enable parallel dml';
return x;
end;
create or replace trigger log_on after
logon on database
declare
x number;
begin
x := myfunc;
end;
References
Bug 3129124 - Logon Trigger Works In 8i But Fails In 9i With Ora-12841
Find differences between two tables
DBMS_RECTIFIER_DIFF.DIFFERENCES()
注意：
1.missing_rows_oname1，missing_rows_oname2要手工创建。
2.If this package does not exist in the database, run script $ORACLE_HOME/rdbms/admin/catrep.sql as sys
missing_rows_oname1结构和2个数据表相同。
missing_rows_oname2有present varchar2(100),absent varchar2(100),r_id rowid这3列
注意：
1.missing_rows_oname1，missing_rows_oname2要手工创建。
2.If this package does not exist in the database, run script $ORACLE_HOME/rdbms/admin/catrep.sql as sys
missing_rows_oname1结构和2个数据表相同。
missing_rows_oname2有present varchar2(100),absent varchar2(100),r_id rowid这3列
Friday, November 03, 2006
updates.oracle.com
如果知道补丁号，可以使用ftp：//updates.oracle.com来下载。
方法：
1.ftp updates.oracle.com
2.输入metalink的帐号密码
3.cd patch#（e.g. cd 123456）
4.ls,选择你要的OS版本
5.下载
方法：
1.ftp updates.oracle.com
2.输入metalink的帐号密码
3.cd patch#（e.g. cd 123456）
4.ls,选择你要的OS版本
5.下载
Thursday, November 02, 2006
sysdba and sysoper privileges
0) Introduction
~~~~~~~~~~~~~~~
This article describes the different ways you can connect to Oracle as an
administrative user.
It describes the options available to connect as SYSDBA or SYSOPER.
Oracle 8.1 is expected to be the last release to support the 'CONNECT INTERNAL'
syntax : it is therefore advisable to use SYSDBA or SYSOPER privileges instead.
1) Administrative Users
~~~~~~~~~~~~~~~~~~~~~~~
There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
These are special privileges as they allow access to a database instance even
when it is not running and so control of these privileges is totally outside of
the database itself.
SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.
SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously
connected INTERNAL.
2) Password or Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~
Unless a connection to the instance is considered 'secure' then you MUST use a
password to connect with SYSDBA or SYSOPER privilege.
Users can be added to a special 'password' file using either the 'ORAPWD'
utility, or 'GRANT SYSDBA to USER' command.
Such a user can then connect to the instance for administrative purposes using
the syntax:
CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER
This is described in more detail in section (5) below.
Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the connection to the instance is local or 'secure' then it is possible to
use the operating system to determine if a user is allowed SYSDBA or SYSOPER
access.
In this case no password is required.
The syntax to connect using operating system authentication is:
CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER
Oracle determines if you can connect thus:
On Unix: On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.
These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.
When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if
your Unix logon is a member of the 'OSOPER' group and if so allows you
to connect.
Similarly to connect as SYSDBA your Unix logon should be a member of
the Unix 'OSDBA' group.
The OSDBA groups is the same group as has been historically used to
allow CONNECT INTERNAL.
On NT: On NT the OSOPER and OSDBA groups are hard coded groups thus:
Group Name Oracle uses this as...
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
ORA_OPER OSOPER group for all instances
ORA_DBA OSDBA group for all instances
or
ORA_sid_OPER OSOPER group for a specific Oracle SID
ORA_sid_DBA OSDBA group for a specific Oracle SID
When you issue a 'CONNECT / AS SYSDBA' , Oracle checks if your NT logon is a
member of the 'ORA_sid_DBA' or 'ORA_DBA' group.
3) OSDBA & OSOPER Groups on Unix
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'.
These groups are compiled into the 'oracle' executable and so are the same for
all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:
cd $ORACLE_HOME/rdbms/lib
cat config.[cs]
The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.
If you wish to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer.
Eg: For an OSDBA group of 'mygroup'
If your platform has config.c (this is the case for HP-UX, Compaq Tru64
Unixware and Linux):
Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"
If your platform has config.s:
Due to the way different compilers under different architectures generate
assembler code, it's not possible to give a universal rule.
Here are some examples:
Sun SPARC Solaris:
------------------
Change both ocurrences of
.ascii "dba\0"
to
.ascii "mygroup\0"
IBM AIX/Intel Solaris:
----------------------
Change both ocurrences of
.string "dba"
to
.string "mygroup"
To effect any changes to the groups and to be sure you are using the groups
defined in this file relink the Oracle executable.
Be sure to shutdown all databases before relinking:
Eg:
rm config.o
make -f ins_rdbms.mk config.o ioracle
For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:
- Be compiled into the Oracle executable
- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being
used)
- It CANNOT be the group called 'daemon'
Note: The commands above are examples and may vary between platforms.
Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER
roles using group names of the form 'ORA_sid_OSDBA'.
This functionality has not been implemented on Unix (See Bug 224071)
Important notes about 'CONNECT / AS SYSDBA'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Unix systems a user may be a member of more than one group.
To connect as an administrative user without supplying a password:
- One of the groups of which the user is a member should be either the OSDBA or
OSOPER groups as defined in config.c (config.s on some platforms) and as
linked into the 'oracle' executable.
- The group must be a valid group as defined in /etc/group (or as defined in NIS
by 'ypcat group')
- The users PRIMARY group (Ie: the one shown by the 'id' command) cannot be the
special group 'daemon'.
It is quite common for the 'root' user to be required to have SYSDBA or SYSOPER
privilege.
Unfortunately it is also common for the root users' primary group to be the
group 'daemon' which may prevent it from being allowed to connect without a
password.
There are two ways to tackle this problem:
a) Make the root users PRIMARY group the OSDBA group
OR
b) Where available use the 'newgrp' command to change the users primary group to
the DBA group.
Eg: $ newgrp dbagroup
$ svrmgrl
SVRMGR> connect / as sysdba
This can also be used in shellscripts thus:
:
newgrp dbagroup\".
This is described in detail in Note 60634.1
5) Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~
Remote connections require the database to be configured to allow remote DBA
operations.
The remote user will have to supply a password in order to connect as either
SYSDBA or SYSOPER.
The only real exception to this is on NT where remote connections may be secure.
Ie: To perform a remote connect as SYSDBA or SYSOPER you must use the syntax
'CONNECT username/password AS SYSDBA'
To allow remote administrative connections you must:
- Set up a password file for the database on the server
- Set up any relevant init.ora parameters
5.1) Setting up a Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The SYSDBA/SYSOPER password protection is controlled by an Oracle 'Password'
file.
The basic concept is that a special file is created to hold the 'SYSDBA' and
'SYSOPER' passwords.
Users with SYSDBA or SYSOPER privilege granted in the password file can be seen
in the view V$PWFILE_USERS.
To create a password file log in as the Oracle software owner and issue the
command:
orapwd file= password= entries=
using the required password.
The file name is important and should be specified as above.
-WINDOWS PWD+sid
-UNIX orapw+sid
You should create this file when the database is shut down.
To change a password:
- Shut down the database,
- Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file,
- Issue a new ORAPWD command with a new password
5.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote administrative connections set the init.ora parameters thus:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
EXCLUSIVE forces the password file to be tied exclusively to a single instance.
To disable remote administrative connections set REMOTE_LOGIN_PASSWORDFILE=NONE
Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as
SYSDBA or SYSOPER from a remote machine.
Note: Some (old) documentation may indicate SQL*Net needs configuring to connect
from remote machines.
In particular the following are NOT used:
SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are
irrelevant
6) Bugs and Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~
Common Errors
~~~~~~~~~~~~~
ORA-01031: insufficient privileges
Connect Internal has been issued with no password.
For local connections the user is NOT in the DBA group as compiled
into the 'oracle' executable.
For remote connections you must always supply a password.
This error can also occur after a successful connect internal/password if there
REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.
ORA-01017: invalid username/password; logon denied
This is a fairly general error that indicates one of the following:
- REMOTE_LOGIN_PASSWORDFILE is set to NONE
- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started
Deleting/Changing the 'orapw$SID' File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you delete the Oracle password file while the instance is running you will
NOT be able to connect internal from remote machines, even if you re-create the
file.
You must:
- Shutdown the instance (using a local connection)
- Create the new password file
- You can now connect remotely and restart the instance
~~~~~~~~~~~~~~~
This article describes the different ways you can connect to Oracle as an
administrative user.
It describes the options available to connect as SYSDBA or SYSOPER.
Oracle 8.1 is expected to be the last release to support the 'CONNECT INTERNAL'
syntax : it is therefore advisable to use SYSDBA or SYSOPER privileges instead.
1) Administrative Users
~~~~~~~~~~~~~~~~~~~~~~~
There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
These are special privileges as they allow access to a database instance even
when it is not running and so control of these privileges is totally outside of
the database itself.
SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks without the ability to look at user data.
SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when previously
connected INTERNAL.
2) Password or Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~
Unless a connection to the instance is considered 'secure' then you MUST use a
password to connect with SYSDBA or SYSOPER privilege.
Users can be added to a special 'password' file using either the 'ORAPWD'
utility, or 'GRANT SYSDBA to USER' command.
Such a user can then connect to the instance for administrative purposes using
the syntax:
CONNECT username/password AS SYSDBA
or
CONNECT username/password AS SYSOPER
This is described in more detail in section (5) below.
Operating System Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the connection to the instance is local or 'secure' then it is possible to
use the operating system to determine if a user is allowed SYSDBA or SYSOPER
access.
In this case no password is required.
The syntax to connect using operating system authentication is:
CONNECT / AS SYSDBA
or
CONNECT / AS SYSOPER
Oracle determines if you can connect thus:
On Unix: On UNIX the Oracle executable has two group names compiled into it,
one for SYSOPER and one for SYSDBA.
These are known as the OSOPER and OSDBA groups.
Typically these can be set when the Oracle software is installed.
When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if
your Unix logon is a member of the 'OSOPER' group and if so allows you
to connect.
Similarly to connect as SYSDBA your Unix logon should be a member of
the Unix 'OSDBA' group.
The OSDBA groups is the same group as has been historically used to
allow CONNECT INTERNAL.
On NT: On NT the OSOPER and OSDBA groups are hard coded groups thus:
Group Name Oracle uses this as...
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
ORA_OPER OSOPER group for all instances
ORA_DBA OSDBA group for all instances
or
ORA_sid_OPER OSOPER group for a specific Oracle SID
ORA_sid_DBA OSDBA group for a specific Oracle SID
When you issue a 'CONNECT / AS SYSDBA' , Oracle checks if your NT logon is a
member of the 'ORA_sid_DBA' or 'ORA_DBA' group.
3) OSDBA & OSOPER Groups on Unix
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'.
These groups are compiled into the 'oracle' executable and so are the same for
all databases running from a given ORACLE_HOME directory.
The actual groups being used for OSDBA and OSOPER can be checked thus:
cd $ORACLE_HOME/rdbms/lib
cat config.[cs]
The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.
If you wish to change the OSDBA or OSOPER groups this file needs to be modified
either directly or using the installer.
Eg: For an OSDBA group of 'mygroup'
If your platform has config.c (this is the case for HP-UX, Compaq Tru64
Unixware and Linux):
Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"
If your platform has config.s:
Due to the way different compilers under different architectures generate
assembler code, it's not possible to give a universal rule.
Here are some examples:
Sun SPARC Solaris:
------------------
Change both ocurrences of
.ascii "dba\0"
to
.ascii "mygroup\0"
IBM AIX/Intel Solaris:
----------------------
Change both ocurrences of
.string "dba"
to
.string "mygroup"
To effect any changes to the groups and to be sure you are using the groups
defined in this file relink the Oracle executable.
Be sure to shutdown all databases before relinking:
Eg:
rm config.o
make -f ins_rdbms.mk config.o ioracle
For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:
- Be compiled into the Oracle executable
- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being
used)
- It CANNOT be the group called 'daemon'
Note: The commands above are examples and may vary between platforms.
Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER
roles using group names of the form 'ORA_sid_OSDBA'.
This functionality has not been implemented on Unix (See Bug 224071)
Important notes about 'CONNECT / AS SYSDBA'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Unix systems a user may be a member of more than one group.
To connect as an administrative user without supplying a password:
- One of the groups of which the user is a member should be either the OSDBA or
OSOPER groups as defined in config.c (config.s on some platforms) and as
linked into the 'oracle' executable.
- The group must be a valid group as defined in /etc/group (or as defined in NIS
by 'ypcat group')
- The users PRIMARY group (Ie: the one shown by the 'id' command) cannot be the
special group 'daemon'.
It is quite common for the 'root' user to be required to have SYSDBA or SYSOPER
privilege.
Unfortunately it is also common for the root users' primary group to be the
group 'daemon' which may prevent it from being allowed to connect without a
password.
There are two ways to tackle this problem:
a) Make the root users PRIMARY group the OSDBA group
OR
b) Where available use the 'newgrp' command to change the users primary group to
the DBA group.
Eg: $ newgrp dbagroup
$ svrmgrl
SVRMGR> connect / as sysdba
This can also be used in shellscripts thus:
:
newgrp dbagroup
This is described in detail in Note 60634.1
5) Password Authentication
~~~~~~~~~~~~~~~~~~~~~~~~~~
Remote connections require the database to be configured to allow remote DBA
operations.
The remote user will have to supply a password in order to connect as either
SYSDBA or SYSOPER.
The only real exception to this is on NT where remote connections may be secure.
Ie: To perform a remote connect as SYSDBA or SYSOPER you must use the syntax
'CONNECT username/password AS SYSDBA'
To allow remote administrative connections you must:
- Set up a password file for the database on the server
- Set up any relevant init.ora parameters
5.1) Setting up a Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The SYSDBA/SYSOPER password protection is controlled by an Oracle 'Password'
file.
The basic concept is that a special file is created to hold the 'SYSDBA' and
'SYSOPER' passwords.
Users with SYSDBA or SYSOPER privilege granted in the password file can be seen
in the view V$PWFILE_USERS.
To create a password file log in as the Oracle software owner and issue the
command:
orapwd file=
using the required password.
The file name is important and should be specified as above.
-WINDOWS PWD+sid
-UNIX orapw+sid
You should create this file when the database is shut down.
To change a password:
- Shut down the database,
- Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file,
- Issue a new ORAPWD command with a new password
5.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote administrative connections set the init.ora parameters thus:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
EXCLUSIVE forces the password file to be tied exclusively to a single instance.
To disable remote administrative connections set REMOTE_LOGIN_PASSWORDFILE=NONE
Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as
SYSDBA or SYSOPER from a remote machine.
Note: Some (old) documentation may indicate SQL*Net needs configuring to connect
from remote machines.
In particular the following are NOT used:
SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are
irrelevant
6) Bugs and Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~
Common Errors
~~~~~~~~~~~~~
ORA-01031: insufficient privileges
Connect Internal has been issued with no password.
For local connections the user is NOT in the DBA group as compiled
into the 'oracle' executable.
For remote connections you must always supply a password.
This error can also occur after a successful connect internal/password if there
REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.
ORA-01017: invalid username/password; logon denied
This is a fairly general error that indicates one of the following:
- REMOTE_LOGIN_PASSWORDFILE is set to NONE
- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started
Deleting/Changing the 'orapw$SID' File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you delete the Oracle password file while the instance is running you will
NOT be able to connect internal from remote machines, even if you re-create the
file.
You must:
- Shutdown the instance (using a local connection)
- Create the new password file
- You can now connect remotely and restart the instance
About database link
To know the settings of the database to which the dblink belongs,
issue the following statements:
SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
-------------------- --------------------
SYS DB1.WORLD <=====This column shows the dblink name
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------
XXX.YYY <=====Here is the current database global name in "[DB_NAME](.[DB_DOMAIN])" format,
and the ".[DB_DOMAIN]" may be empty.
If ".[DB_DOMAIN]" in global_name matches the ".world" in dblink name,
the ".world" can be omitted when using this dblink.
So the reason why a dblink which can be used without ".world" in the past must be used in full format now
may be that someone has changed the database global name to XXX.YYY and ".YYY" doesn't match ".world".
To fix this problem:
1.Verify the GLOBAL_NAME matches the "DB_NAME.WORLD".
To check the current value, issue the following commands using SQL*Plus:
SQL> SHOW PARAMETER DB_NAME
SQL> SELECT * FROM GLOBAL_NAME;
2.Issue the following statement using SQL*Plus:
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO db_name.world;
----------------------------------------------------------------
Note: Contact your DBA before changing the global_name,
because someone may have changed this for certain purpose.
----------------------------------------------------------------
Reference:
MetaLink#117759.1 DATABASE LINK (DBLINK) TROUBLESHOOTING
KROWN#85279 GLOBAL_NAME をドメインなしの名前に変更できない
issue the following statements:
SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
-------------------- --------------------
SYS DB1.WORLD <=====This column shows the dblink name
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------
XXX.YYY <=====Here is the current database global name in "[DB_NAME](.[DB_DOMAIN])" format,
and the ".[DB_DOMAIN]" may be empty.
If ".[DB_DOMAIN]" in global_name matches the ".world" in dblink name,
the ".world" can be omitted when using this dblink.
So the reason why a dblink which can be used without ".world" in the past must be used in full format now
may be that someone has changed the database global name to XXX.YYY and ".YYY" doesn't match ".world".
To fix this problem:
1.Verify the GLOBAL_NAME matches the "DB_NAME.WORLD".
To check the current value, issue the following commands using SQL*Plus:
SQL> SHOW PARAMETER DB_NAME
SQL> SELECT * FROM GLOBAL_NAME;
2.Issue the following statement using SQL*Plus:
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO db_name.world;
----------------------------------------------------------------
Note: Contact your DBA before changing the global_name,
because someone may have changed this for certain purpose.
----------------------------------------------------------------
Reference:
MetaLink#117759.1 DATABASE LINK (DBLINK) TROUBLESHOOTING
KROWN#85279 GLOBAL_NAME をドメインなしの名前に変更できない
Tuesday, October 31, 2006
show_sql
column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Tuesday, October 24, 2006
9.2.0.6=>9.2.0.7 upgrade on windows platform
Error occurs when progress goes to 51%.
To fix this error, stop the service "Distributed Transaction coordinator".
To fix this error, stop the service "Distributed Transaction coordinator".
Sunday, October 22, 2006
How to obtain the number of the blocks that really contain data
実際にデータを含んでいるデータブロック数を調べる方法はANALYZE TABLEがよく使用されますが、これよりも早い方法があります。
実際に全件検索を行って数えてしまう方法です。
以下のSQLで実現できます。
oracle7.X
select count(distinct(substr(rowid,1,8)||substr(rowid,15,4))) blocks_used from [テーブル名];
oracle8.X
select count(distinct(substr(rowid,1,15))) blocks_used from [テーブル名];
これにより、実データサイズを算出することが出来ます。
実データ＝上記SELECT文のblock数 * ブロックサイズ(初期化パラメータdb_block_size)ただし、PCTFREE/PCTUSEDなどの設定や断片化によるオーバヘッドがありえるため、完全なデータサイズではありません。
あくまでも実際にデータを含んでいるデータブロックを計算しています。
実際に全件検索を行って数えてしまう方法です。
以下のSQLで実現できます。
oracle7.X
select count(distinct(substr(rowid,1,8)||substr(rowid,15,4))) blocks_used from [テーブル名];
oracle8.X
select count(distinct(substr(rowid,1,15))) blocks_used from [テーブル名];
これにより、実データサイズを算出することが出来ます。
実データ＝上記SELECT文のblock数 * ブロックサイズ(初期化パラメータdb_block_size)ただし、PCTFREE/PCTUSEDなどの設定や断片化によるオーバヘッドがありえるため、完全なデータサイズではありません。
あくまでも実際にデータを含んでいるデータブロックを計算しています。
Thursday, October 19, 2006
How to change password back
SQL> SELECT PASSWORD FROM DBA_USERS WHERE USERNAME = 'test';
PASSWORD
------------------------------
F84870711C9D9E90
SQL> ALTER USER test IDENTIFIED BY ABC;
用户已更改。
SQL> ALTER USER DAIJC IDENTIFIED BY VALUES 'F84870711C9D9E90';
用户已更改。
PASSWORD
------------------------------
F84870711C9D9E90
SQL> ALTER USER test IDENTIFIED BY ABC;
用户已更改。
SQL> ALTER USER DAIJC IDENTIFIED BY VALUES 'F84870711C9D9E90';
用户已更改。
tablespace usage
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
Subscribe to:
Posts (Atom)
