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
--------------------------------------------------

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 修正情報を正しいものに変更

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'
/

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.

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

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

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

查看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

Detailed information of windows

winmsd.exe

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;
/

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]

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.

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 をドメインなしの名前に変更できない

ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE

<質問内容>
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE;

を実行時にアウトライン適用対象のSQLが実行中であった場合影響は
ありますでしょうか？


<回答内容>
こちらにつきましては Parse の動作を行う以前にアウトラインの
使用が決定するという動作になっております。

そのため　Parse の動作を行う以前にアウトラインが有効であれば
アウトラインを使用し、Parse が開始されていればアウトラインは
使用しないということになります。

使用exp/imp移动database时ORA-00001发生

如下的错误可以忽略
ORA-00001: 一意制約(SYSTEM.HELP_TOPIC_SEQ)に反しています

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

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 本文書を公開

サーバの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の接続文字列の確認を忘れないようにしてください。

CPU family

http://processorfinder.intel.com/default.aspx
To check the current CPU information run winmsd.exe in command line mode

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

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

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列

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.下载

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 users with USERNAMEs of the form "OPS$\".
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

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 をドメインなしの名前に変更できない

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;

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".

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などの設定や断片化によるオーバヘッドがありえるため、完全なデータサイズではありません。
あくまでも実際にデータを含んでいるデータブロックを計算しています。

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';
用户已更改。

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;

Incrementally Updated Backups: A Basic Example

To create incremental backups for use in an incrementally updated backups strategy, you must use the BACKUP... FOR RECOVER OF COPY WITH TAG form of the BACKUP command. How the command works is best understood in the context of an example script that would implement the strategy.

This script, run on a regular basis, is all that is required to implement a strategy based on incrementally updated backups:
RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;
}

The syntax used in the script does not, however, make it clear how the strategy works. To understand the script and the strategy, it is necessary to understand the effects of these two commands when no datafile copies or incremental backups exist.


The RECOVER COPY OF DATABASE WITH TAG... command causes RMAN to apply any available incremental level 1 backups to a set of datafile copies with the specified tag.

If there is no incremental backup or no datafile copy, the command generates a message but does not generate an error.

The first time the script runs, this command has no effect, because there is neither a datafile copy nor a level 1 incremental backup.

The second time the script runs, there is a datafile copy (created by the first BACKUP command), but no incremental level 1 backup, so again, the command has no effect.

On the third run and all subsequent runs, there is a datafile copy and a level 1 incremental from the previous run, so the level 1 incremental is applied to the datafile copy, bringing the datafile copy up to the checkpoint SCN of the level 1 incremental.

---------------------------------------------------------------------------

如果不想使用这种循环执行的方式，而是希望手动完成：
1.创建lv0的image copy
backup as copy incremental level 0 database
2.创建lv1的backup set
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY DATABASE

这样，当需要更新image copy的时候运行：
RECOVER COPY OF DATABASE

（以上3条语句可以加上tag参数）

How to manage flash recovery area and restore point

Oracle
oracle 10.2.0.2

Restore point and flash recovery area space management

Definition
-------------

The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that
provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area.
RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery.

Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark
or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However,
they also provide specific functionality related to the use of the Flashback Database feature.


About the Flashback Database Window
---------------------------------------------------------

DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may
be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the
flash recovery area.

The flashback retention target is a target, not an absolute guarantee that Flashback Database will be available.
If your flash recovery area is not large enough to hold both the flashback logs and files that must be retained to meet the
retention policy, such as archived redo logs and other backups, then the flashback logs from the earliest SCNs may be
deleted to make room in the flash recovery area for other files.

When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN and
V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME as shown in this example:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;


Requirements for Using Guaranteed Restore Points
--------------------------------------------------------------------------

The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your
database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.

A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed
restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required
logs in the flash recovery area.

If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed
restore point (or if all previously created guaranteed restore points have been dropped).

Note: There are no special requirements for using normal restore points.


Age Out Rules of Restore Points
-----------------------------------------------

Normal restore points eventually age out of the control file, even if not explicitly dropped.
The rules governing retention of restore points in the control file are:
-The most recent 2048 restore points are always kept in the control file, regardless of their age.
-Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained,
regardless of how many restore points are defined.
Normal restore points that do not meet either of these conditions may age out of the control file.

Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.


About Logging for Flashback Database and Guaranteed Restore Points
-------------------------------------------------------------------------------------------------------

If no files are eligible for deletion from the flash recovery area because of the requirements imposed by your retention policy
and the guaranteed restore point, then the database behaves as if it has encountered a disk full condition. In many
circumstances, this causes your database to halt.

・Logging for Guaranteed Restore Points With Flashback Logging Disabled

The available block images can be used to re-create the datafile contents at the time of a guaranteed restore point using
FLASHBACK DATABASE, but you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed
restore points and the current time, as is possible when logging for Flashback Database is enabled. If you need to return
the database to an intermediate point in time, your only option is database point-in-time recovery.

Because each block that changes is only logged once, disk space usage for logging for guaranteed restore points when
flashback logging is disabled is generally considerably less than normal flashback logging. You could maintain a guaranteed
restore point for days or even weeks without concern over the ongoing growth of flashback logs that occurs if logging for
Flashback Database is enabled. The performance overhead of logging for a guaranteed restore point without flashback
database logging is generally lower as well.

・Logging for Flashback Database With Guaranteed Restore Points Defined

If Flashback Database is enabled and one or more guaranteed restore points is defined, then the database performs normal
flashback logging, which causes some performance overhead and, depending upon the pattern of activity on your database,
can cause signifcant space pressure in the flash recovery area. However, unlike normal logging for Flashback Database,
the flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back
as the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure,
if they are required to satisfy the guarantee.


Estimating Disk Space Requirements for Flashback Database Logs
--------------------------------------------------------------------------------------------------

The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area
for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the
database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the
following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG


Rules for Retention and Deletion of Flashback Logs
--------------------------------------------------------------------------

The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:

A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space
in the flash recovery area.

A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.

If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the
oldest flashback log is reused instead.

If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make
space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of
FLASHBACK DATABASE are also deleted.


Obtain Information, Space Management Rules and How to Resolve Space Pressure
------------------------------------------------------------------------------------------------------------------------

You can query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space
reclaimable by deleting files, and total number of files in the Flash Recovery Area.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose.
The following rules apply for files to become eligible for deletion from the Flash Recovery Area:

-Any backups which have become obsolete as per the retention policy.
-Any files in the Flash Recovery Area which has been already backed up to a tertiary device such as tape.
-Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.

There are a number of choices on how to resolve a full Flash Recovery Area when there are NO files eligible for deletion:

-Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space.
-Use the command BACKUP RECOVERY AREA, to back up the contents of the Flash Recovery Area to a tertiary
device such as tape.
-Delete unnecessary files from the Flash Recovery Area using the RMAN delete command.
-You may also need to consider changing your backup retention policy
-When using Data Guard, consider changing your archivelog deletion policy.


Reference:

Oracle® Database Backup and Recovery Basics
10g Release 2 (10.2)
Part Number B14192-03
(Data Protection with Restore Points and Flashback Database)

Oracle® Database Reference
10g Release 2 (10.2)
Part Number B14237-02
(DB_FLASHBACK_RETENTION_TARGET)

MetaLink Note:305812.1 Flash Recovery area - Space management Warning & Alerts

MetaLink Note:315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.

RMAN: Full Recovery When the Recovery Catalog and Controlfile are Lost

当数据库使用controlfile存放repository而没有使用catalog的时候，丢失所有的controlfile就等于丢失repository。

前提是有controlfile的自动备份，或者知道手动备份的备份集
对应方法：
1.startup nomount
2.rman target /
3.set dbid=XXXXXXX
(如果不知道dbid，第4步时需要手动在备份集中指定controlfile的备份)
4.restore controlfile from autobackup;
(如果第3步没有设置dbid，restore controlfile from 'backupset_name';)
注意：rman只会搜索windows的％ORACLE_HOME%/database或unix的$ORACLE_HOME/dbs目录
5. sql 'alter database mount'
6.restore database
7.recover database
(如果你的online redo log也损坏了，需要指定until条件)
8.sql 'alter database open resetlogs'
9.重新备份数据库

guidelines for db_block_size

Verdict
~~~~~
Use db_block_size = 2048
only if you really know what you are doing. The best use of 2KB database blocks that I know is in stress tests in which you are trying to drive server workload artificially high so you can analyze the bottlenecks less expensively (i.e., without generating mountains of test data and test transactions).

Use db_block_size = 8192
for most large transactional processing systems. This represents a good balance between advantages and the disadvantage for undo segments.

Use db_block_size of larger than 8KB
for OLTP systems in which your data structures drive the block size to a naturally larger size. This will help to to avoid chained and migrated rows.

Use db_block_size of larger values than 8KB
for systems in which your undo generation is not a meaningful part of your workload. Data warehouses fit this profile. With bigger blocks, you reduce total system I/O setup costs dramatically, yet you incur none of the disadvantages that you would incur in an OLTP system because people generally are not executing transactions (inserts, updates, deletes, and selects for update).

The maximum size of a single index entry is approximately one-half the data block size(8i only).
block size maximum size of a single index entry
(tested on RHEL2.1, oracle8.1.7)
~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4KB 1578bytes
8KB 3218bytes
16KB 6498bytes

Data block size affacts the size of SGA(8i only)
The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:
DB_BLOCK_SIZE: The size, in bytes, of a single data block and database buffer.

DB_BLOCK_BUFFERS: The number of database buffers, each the size of DB_BLOCK_SIZE, allocated for the SGA. The total amount of space allocated for the database buffer cache in the SGA is DB_BLOCK_SIZE times DB_BLOCK_BUFFERS.

Please make a full and up-to-date backup of your database before export and import

changes of data types between 9.2 and 10.2

9.2
char column maximum size: 2000 bytes
long raw column maximum size: 2G
long column maximum size: 2G
raw column maximum size:2000 bytes
blob, clob, nclob maximum size: 4G
bfile maximum size: 4G
10.2
char column maximum size: 2000 bytes
long raw column maximum size: 2G
long column maximum size: 2G
raw column maximum size:2000 bytes
blob, clob, nclob maximum size: 8T~128T
bfile maximum size: 4G

Database Hangs for DML Activity ORA-16038, ORA-00354, ORA-00312

fact: Oracle Server - Enterprise Edition 8.1.7.4.0
fact: Oracle Server - Enterprise Edition 9.0.1
fact: Oracle Server - Enterprise Edition 9.2
symptom: Database Hangs for DML Activity ORA-16038, ORA-00354, ORA-00312
symptom: Database hangs for dml (insert, update and delete) activity
symptom: ORA-16038: log %s sequence# %s cannot be archived
symptom: ORA-00354: corrupt redo log block header
symptom: ORA-00312: online log %s thread %s: '%s'
cause: Most probable a hardware failure caused an online redolog corruption.

If database is open then try for log number that is corrupted (check from error or v$log):
1 alter database clear logfile group ;
When not working (unlikely to succeed) then:

2 alter database clear unarchived logfile group ;
If this is successfully, the db is running (not hanging for dml activity anymore) but you miss an archive. This means you have to make a backup first, as long as the backup is not complete you cannot perform media recovery using previous backup as you miss an archive.

3 if 1 and 2 both do not work you will have to perform an incomplete recovery（注1）. Shutdown db, restore all datafiles (only datafiles) of a backup and startup mount and issue recover database until cancel and recover until corrupted logfile, then open with alter database open resetlogs; You have dataloss (last corrupted logfile) and you first have to make a new backup after db opens).If the database is closed and cannot be opened you only have option 3.


注1：
以上是metalink提供的解决办法，缺点是情况3时会丢失数据。
实际情况中发生类似3的时候，如果能够正常关闭数据库（immediate），可以先冷备数据库，尝试不用restore，直接recover database until cancel，然后open resetlogs。
这样不会丢失数据。

Points to Consider for Full Database Exports and Imports

A full database export and import can be a good way to replicate or clean up a database. However, to avoid problems be sure to keep the following points in mind:

1.A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before or after the full import. Oracle recommends that you re-create them after the import in case they define actions that would impede progress of the import.

2.If possible, before beginning, make a physical copy of the exported database and the database into which you intend to import. This ensures that any mistakes are reversible.

3.Before you begin the export, it is advisable to produce a report that includes the following information:
(1)A list of tablespaces and datafiles
(2)A list of rollback segments
(3)A count, by user, of each object type such as tables, indexes, and so on
This information lets you ensure that tablespaces have already been created and that the import was successful

4.If you are creating a completely new database from an export, remember to create an extra rollback segment in SYSTEM and to make it available in your initialization parameter file (init.ora)before proceeding with the import.

5.When you perform the import, ensure you are pointing at the correct instance. This is very important because on some UNIX systems, just the act of entering a subshell can change the database against which an import operation was performed.

6.Do not perform a full import on a system that has more than one database unless you are certain that all tablespaces have already been created. A full import creates any undefined tablespaces using the same datafile names as the exported database. This can result in problems in the following situations:
(1)If the datafiles belong to any other database, they will become corrupted. This is especially true if the exported database is on the same system, because its datafiles will be reused by the database into which you are importing.
(2)
If the datafiles have names that conflict with existing operating system files.

Removing a Node from a 10g RAC Cluster

Metalink：Note:269320.1

PURPOSE
-------------
The purpose of this note is to provide the user with a document that can be used as a guide to remove a cluster node from an Oracle 10g RealApplications environment.

REMOVING A NODE FROM A 10g RAC CLUSTER
--------------------------------------------------------------
If you have to remove a node from a RAC 10g database, even if the node will no longer be available to the environment, there is a certain amount of cleanup that needs to be done. The remaining nodes need to be informed of the change of status of the departing node.

The most important 3 steps that need to be followed are;

A. Remove the instance using DBCA.
B. Remove the node from the cluster.
C. Reconfigure the OS and remaining hardware.

Here is a breakdown of the above steps.

A. Remove the instance using DBCA.
--------------------------------------
1.Verify that you have a good backup of the OCR (Oracle Configuration Repository) using ocrconfig -showbackup.
2. Run DBCA from one of the nodes you are going to keep. Leave the database up and also leave the departing instance up and running.
3. Choose "Instance Management"
4. Choose "Delete an instance"
5.On the next screen, select the cluster database from which you will delete an instance. Supply the system privilege username and password.
6. On the next screen, a list of cluster database instances will appear. Highlight the instance you would like to delete then click next.
7. If you have services configured, reassign the services. Modify the services so that each service can run on one of the remaining instances. Set "not used" for each service regarding the instance that is to be deleted. Click Finish.
8. If your database is in archive log mode you may encounter the following errors:
ORA-350
ORA-312
This may occur because the DBCA cannot drop the current log, as it needs archiving. This issue is fixed in the 10.1.0.3 patchset. But previous to this patchset you should click the ignore button and when the DBCA completes, manually archive the logs for the deleted instance and dropt the log group.
SQL> alter system archive log all;
SQL> alter database drop logfile group 2;
9. Verify that the dropped instance's redo thread has been removed by querying v$log. If for any reason the redo thread is not disabled then disable the thread.
SQL> alter database disable public thread 2;
10.Verify that the instance was removed from the OCR (Oracle Configuration Repository) with the following commands:
srvctl config database -d
$ORA_CRS_HOME/bin/./crs_stat
11. If this node had an ASM instance and the node will no longer be a part of the cluster you will now need to remove the ASM instance with:
srvctl stop asm -n
srvctl remove asm -n
Verify that asm is removed with:
srvctl config asm -n

B. Remove the Node from the Cluster
---------------------------------------------

Once the instance has been deleted. The process of removing the node from the cluster is a manual process. This is accomplished by running scripts on the deleted node to remove the CRS install, as well as scripts on the remaining nodes to update the node list. The following steps assume that the node to be removed is still functioning.

1.First as the root user determine the node name and node number on each node as stored in the Cluster Registry.
From the CRS_HOME/bin
olsnodes -n
1
2
2. To delete node number 2 first stop and remove the nodeapps on the node you are removing. Assuming that you have removed the ASM instance as the root user on a remaining node;
srvctl stop nodeapps -n
3.Run NETCA. Choose "Cluster Configuration".
4. Only select the node you are removing and click next.
5. Choose "Listener Configuration" and click next.
6. Choose "Delete" and delete any listeners configured on the node you are removing.
7. Run $ORA_CRS_HOME/bin/crs_stat. Make sure that all database resources are running on nodes that are going to be kept.
For example:
NAME=ora..db
TYPE=application
TARGET=ONLINE
STATE=ONLINE on
Ensure that this resource is not running on a node that will be removed. Use $ORA_CRS_HOME/bin/crs_relocate to perform this.
Example:
crs_relocate ora..db
8. As the root user, remove the nodeapps on the node you are removing.
srvctl remove nodeapps -n
9. Next as the Oracle user run the installer with the updateNodeList option on the node you are deleting.
a. DISPLAY=ipaddress:0.0; export DISPLAY
This should be set even though the gui does not run.
b. $ORACLE_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/10g CLUSTER_NODES=, ,
This should be run as one command. With this command we are defining the nodes that now are part of the cluster.
10. Change to the root user to finish the removal on the node that is being removed(在准备删除的节点上运行). This command will stop the CRS stack and delete the ocr.loc file on the node to be removed. The nosharedvar option assumes the ocr.loc file is not on a shared file sytem. If it does exist on a shared file system then specify sharedvar instead. Run the rootdelete.sh script from $ORA_CRS_HOME/install.
Example:
$ORA_CRS_HOME/install/./rootdelete.sh remote nosharedvar
11. On a node that will be kept, the root user should run the rootdeletenode.sh script from the $ORA_CRS_HOME/install directory. When running this script from the CRS home specify both the node name and the node number. The node name and the node number are visiable in olsnodes -n. Also do NOT put a space after the comma between the two.
olsnodes -n
1
2
$ORA_CRS_HOME/install> ./.rootdeletenode.sh ,2
12. Confirm success by running OLSNODES.
$ORA_CRS_HOME/bin>: ./olsnodes -n
1
13. Now switch back to the oracle user account and run the same runInstaller command as before. Run it this time from the ORA_CRS_HOME instead of the ORACLE_HOME. Specify all of the remaining nodes.
a. DISPLAY=ipaddress:0.0; export DISPLAY
b. $ORA_CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME= CLUSTER_NODES=, , CRS=TRUE
14. Once the node updates are done you will need to manually delete the $ORACLE_HOME and $CRS_HOME from the node to be expunged, unless, of course, either of these is on a shared file system that is still being used.
a. $ORACLE_HOME>: rm -rf *
b. $CRS_HOME> : rm -rf * (as root)
15. Next, as root, from the deleted node, verify that all init scripts and soft links are removed:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd

Linux:
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr

HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd

HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd

IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
16. You can also remove the /etc/oracle directory, the /etc/oratab file, and the Oracle inventory (if desired)
17.To remove an ADDITIONAL ORACLE_HOME or EM_HOME from the inventory on all remaining nodes, run the installer to update the node list. Example (if removing node 2):
runInstaller -updateNodeList -local ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=node1,node3,node4

the way to know which transaction is using a large number of undo space

select used_ublk from v$transaction order by used_ublk desc;