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
Monday, November 27, 2006
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 をドメインなしの名前に変更できない
Subscribe to:
Posts (Atom)
