What To Do and Not To Do When 'shutdown immediate' Hangs

Subject: What To Do and Not To Do When 'shutdown immediate' Hangs
Doc ID: Note:375935.1 Type: HOWTO
Last Revision Date: 02-JUN-2007 Status: MODERATED

In this Document
Goal
Solution
References



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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2
Information in this document applies to any platform.

Goal
What to do when shutdown immediate appears to hang.
Sometimes, the message 'Waiting for smon to disable tx recovery' is posted in the alert log.
This note only addresses situations when the apparent hang occurs when the database is going from OPEN to MOUNT, which is actually the most common situation.
If the apparent hang occurs at a different step, then this note does not apply.

Solution
The big problem in these situations is that it is noticed only after the shutdown immediate has been issued.
This kind of situation is mostly caused by 2 things:
1. a large query running at the shutdown moment.
2. a large transaction running at the shutdown moment.
Both have to complete in order for the database to be brought down when shutdown immediate is issued.
Actually, the files cannot be closed consistently because of one of the 2 possibilities above and, as such, the transition from OPEN to MOUNT is postponed until the files are closed, which means that either the large query completes or the large transaction is rolled back. This is not a hang, it is the expected behavior.

So, before issuing the shutdown immediate, it would be recommended to check the following views, especially when the database needs to be brought down for a very short period of time:
1. for large queries:

select count(*) from v$session_longops where time_remaining>0;
2. for large transactions:

select sum(used_ublk) from v$transaction;
A result greater than 0 for the first query and a large value returned for the second one would mean a relatively long time to wait until the shutdown immediate completes.
For the second situation, please also check step 9 in Note 117316.1 to "guestimate" the time to rollback the transactions.

1. For the large queries situation, when the shutdown immediate is hanging, you can just bring down the database using: shutdown abort, as the database could be easily brought to a consistent state by:
startup restrict followed by shutdown immediate.
One should take the backup and/or do whatever else need to be done after the shutdown immediate.
2. For the second situation, the workaround cannot be applied, especially when it's needed to take a cold backup. The database must be closed in a consistent state in order to do this and the consistent state cannot be achieved until all the transactions have completed one way or another (commit/rollback).

As such, it's up to the local personnel to decide what to do, depending on the local needs.
It is very important to realize that: BY SHUTTING DOWN A DATABASE YOU DO NOT SOLVE A PERFORMANCE PROBLEM CAUSED BY A LARGE TRANSACTION. You are only making things worse.
There are situations when the database is brought down even when a large transaction/large recovery is taking place. Then it's brought up again and a new shutodwn is tried. Again, the shutdown immediate is hanging, for a very simple reason - the large recovery is still going on.
At this moment, the v$transaction view is not displaying anything.
Hoever, it is still possible to check the recovery operation by checking the:

select * from v$fast_start_transactions;and/orselect * from v$fast_start_servers;
views. They are the ones that display the recovery status.
As such, when a large transaction is taking place, do not try successive shutdown aborts, startups and shutdown immediate. The hang will reoccur. The database must be consistent when the database is dismounted - performing successive shutdowns/startups is not helping at all, it's only making the recovery even more lengthy.

You should prevent these situations by notifying the users a shutdown will be done and no large operations should be started.
If a large operation has already started at the moment when you want to shutdown immedate, assess what would be faster - rollback the current situation or allow it to complete.

rman 远程备份（windows）

rman 远程备份（windows）
DB machine（A） Destination machine（B）

1.A和B的administrator密码要一致。
2.进入A机services面板，将相应的listener和db服务中，log on属性从local system account改成this account，用户是administrator
3.重启listener和db服务。
4.注意指定路径时，如果时windows2003，不要用drive mapping，使用主机名格式，例如\\machineB\path\%d_%s_%p

安装配置ocfs（所有node，并且使用root用户）

安装配置ocfs（所有node，并且使用root用户）
1．安装3个ocfs的rpm包
2．运行ocfstool，"Tasks" --> "Generate Config..."，选择eth1，node名称rac1local.us.oracle.com，点击“OK”，然后退出（生成/etc/ocfs.conf文件）。
3．运行depmod –a，然后运行load_ocfs（查看是否加载lsmod|grep ocfs）。
4．【只在一个node上完成】
用fdisk格式化2G的那个共享磁盘，分一个主分区。
5．【只在一个node上完成】
选择 "Tasks" --> "Format..."。

i. 设备：从下拉菜单中选择 "/dev/sda1"
ii.卷标：输入 "ocfs"。
iii.挂载点：输入 "/ocfs"。
iv.用户：从下拉菜单中选取 "oracle"。
v. 组：从下拉菜单中选择 "oinstall"。
vi.单击 "OK"
6．编辑/etc/fstab，加入“/dev/sda1 /ocfs ocfs _netdev 0 0”，不包括引号。
7．Mkdir /ocfs
8．重启所有node，确保ocfs内核及ocfs文件系统可以自动加载。

Problem: AgentCA: Install Fails with OUI-25031 error during Grid Control 10.2.0.1 installation

Subject: Problem: AgentCA: Install Fails with OUI-25031 error during Grid Control 10.2.0.1 installation on SuSE SLES9
Doc ID: Note:405308.1 Type: PROBLEM
Last Revision Date: 27-MAR-2007 Status: PUBLISHED

In this Document
Symptoms
Cause
Solution
References



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



Applies to:
Enterprise Manager Grid Control - Version: 10.2.0.1
Linux x86

Symptoms
Installing Enterprise Manager Grid Control 10.2.0.1 on SuSE SLES9, with 'Using a New Database' or 'Using an Existing Database' option.

Near the end of the installation, Agent Configuration Assistant fails with OUI-25031 error:
Configuration assistant "Agent Configuration Assistant" failed
-----------------------------------------------------------------------------
The "/oracle/app/oracle/product/10.2/agent10g/cfgtoollogs/configToolFailedCommands" script contains all commands that failed , were skipped or were cancelled.
This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
-----------------------------------------------------------------------------
Command = oracle.sysman.emcp.agent.AgentPlugIn has failed

The following error messages can be found in $AGENT_HOME/cfgtoollogs/oui/installActionsTimestamp.log:
Command = oracle.sysman.emcp.agent.AgentPlugIn has failed
Exception : java.lang.Exception:
The Management Agent Configuration Assistant has failed. The following failures were recorded:
configureAgent:discovery on non RAC: Host discovery failed.
Oracle Database discovery failed.



The following solutions have been tested, but none of them work:
1. No symbolic link directories have been created for grid control installation destination.
2. No environment parameters contain a semicolon or space in the value.
3. No IPv6 entries in /etc/hosts file.
4. Set environment variable OPATCH_NO_FUSER to FALSE.

Cause
OPatch needs to use 'fuser' command when applying some interim patches during Agent configuration, but 'fuser' cannot be found in $PATH environment variable.
Solution
1. Clean up the failed installation.



2. Find OS 'fuser' command, and normally it is in '/bin' directory. Add this directory to PATH environment variable for oracle user. For example: export PATH=$PATH:/bin

AND

Set environment variable OPATCH_NO_FUSER=TRUE for oracle user.



3. Reinstall Enterprise Manager Grid Control 10.2.0.1.

References
Bug 5136795 - INSTALLING 10.2 AGENT OUI-25031 AGENT CONFIGURATION FAILED
Note 352357.1 - Problem: AgentCA: 10.2 Gc Install Failed Near End (Agentplugin)
Note 352507.1 - Problem: AgentCA: Installing 10.2.0.1 Grid fails on AgentPlugIn with Oui-25031
Note 380507.1 - Problem: AgentCA: OUI-25031: Environment Settings Preventing AgentCA to Complete Successfully

Errors
OUI-25031

Keywords
'GRID~CONTROL' 'INSTALLATION~PROBLEM' 'INSTALLATION~FAILS' 'ENTERPRISE~MANAGER'

How to Move from ANALYZE to DBMS_STATS - Introduction

DBMS_STATSを使うことにより、EMPTY_BLOCKSを0にすることも可能です。

DBMS_STATSはDBA_TABLESの中のEMPTY_BLOCKS列を0にすることができるにもかかわらず，
MetaLinkの中にて記述された内容によりますと，正確にEMPTY_BLOCKSの統計情報を収集
する方法はANALYZE文との一つしかありません。

次に引用されているのはMetaLink(Note#237293.1)の一部です：
---------------------------------------------------------------------------------
If statistics unrelated to the cost based optimizer are required, then these
must still be collected using the the ANALYZE command. These statistics include:

- Space Usage information :

. EMPTY_BLOCKS,
. AVG_SPACE,
. CHAIN_CNT
---------------------------------------------------------------------------------

附：Note#237293.1


Subject: How to Move from ANALYZE to DBMS_STATS - Introduction
Doc ID: Note:237293.1 Type: FAQ
Last Revision Date: 16-FEB-2007 Status: PUBLISHED


PURPOSE
=======

This note is an introduction to a set of notes which helps DBA through examples
to move gathering statistics from ANALYZE or DBMS_UTILITY to DBMS_STATS.

SCOPE & APPLICATION
===================

This set of Notes is aimed at anyone wishing to gather statistics for use by the Cost Based Optimizer (CBO).


How to Move From ANALYZE/DBMS_UTILITY.ANALYZE_* to DBMS_STATS
=============================================================

Cost-Based optimization relies on accurate statistics to determine the relative
cost of execution plans. In Oracle 7, there was two methods to gather statistics:

o ANALYZE
o DBMS_UTILITY

Since the Oracle Version 8.1.5, a single package has been provided to gather
statistics:

o DBMS_STATS package

It is strongly recommended to use the DBMS_STATS package and all the different
functions that are defined by it to handle the collect of statistics.
Using DBMS_STATS is the only way to access the new sampling functionality
provided in later releases since ANALYZE is not being enhanced.

- Note 236935.1 Global statistics - DBMS_STATS versus ANALYZE
- estimate_percent => dbms_stats.auto_sample_size
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
- degree => DBMS_STATS.DEFAULT_DEGREE, -
- options => 'GATHER AUTO', -


For an overview of sampling recommendations see:

Note 114671.1 Gathering Statistics for the Cost Based Optimizer

For specific package related detail, consult the Oracle9i Supplied PL/SQL
Packages and Types Reference documentation



Moving from Legacy sampling (ANALYZE and DBMS_UTILITY) to DBMS_STATS
--------------------------------------------------------------------

Examples of various sampling scenarios are provided in the following aricles:

Note 237537.1 How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples
Note 237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples
Note 237539.1 How to Move from ANALYZE to DBMS_STATS on Composite-Partitioned Tables - Some Examples
Note 237397.1 How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS:

Difference in statistics collection between ANALYZE and DBMS_STATS ?
--------------------------------------------------------------------

The DBMS_STATS package only gathers statistics for use by the CBO wheras the
legacy ANALYZE command gathered statistics for other purposes. The following
statistics are gathered:

- Table statistics -
****************

. Number of rows : NUM_ROWS
. Number of blocks : BLOCKS
. The Average row length : AVG_ROW_LEN
. Sample size : SAMPLE_SIZE
. Last analyzed : LAST_ANALYZED
. Global statistics : GLOBAL_STATS
. User statistics : USER_STATS

. These are visible in the following Dictionary Views :

- DBA_TABLES, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS


- Column statistics -
*****************

. Number of distinct values (NDV) in column : NUM_DISTINCT
. Number of null values in column : NUM_NULLS
. Average column length : AVG_COL_LEN
. Number of Buckets : NUM_BUCKETS
. Low Value : LOW_VALUE
. High Value : HIGH_VALUE
. Sample size : SAMPLE_SIZE
. Last analyzed : LAST_ANALYZED
. Global statistics : GLOBAL_STATS
. User statistics : USER_STATS
. Data distribution : See note below


. Standard Column statistics are visible in the following Dictionary Views :

- DBA_TAB_COL_STATISTICS, DBA_PART_COL_STATISTICS, DBA_SUBPART_COL_STATISTICS

. Column Data distribution statistics are stored separately in the DBA_*_HISTOGRAMS views:

- DBA_HISTOGRAMS, DBA_PART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS


- Index statistics -
****************

. Number of leaf blocks : LEAF_BLOCKS
. Number of Levels : BLEVEL
. Clustering factor : CLUSTERING_FACTOR
. Number of Distinct Keys : DISTINCT_KEYS
. The Average Leaf Index Blocks per Key : AVG_LEAF_BLOCKS_PER_KEY
. The Average Data Blocks per Key : AVG_DATA_BLOCKS_PER_KEY
. Sample size : SAMPLE_SIZE
. Last analyzed : LAST_ANALYZED
. Global statistics : GLOBAL_STATS
. User statistics : USER_STATS

. These are visible in the following Dictionary Views :

- DBA_INDEXES, DBA_IND_PARTITIONS, DBA_IND_SUBPARTITIONS

If statistics unrelated to the cost based optimizer are required, then these
must still be collected using the the ANALYZE command. These statistics include:

- Space Usage information :

. EMPTY_BLOCKS,
. AVG_SPACE,
. CHAIN_CNT

- Information on freelist blocks

. AVG_SPACE_FREELIST_BLOCKS,
. NUM_FREELIST_BLOCKS


RELATED DOCUMENTS
=================

Note 236935.1 Global statistics - DBMS_STATS versus ANALYZE
Note 237537.1 How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples
Note 237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples
Note 237539.1 How to Move from ANALYZE to DBMS_STATS on Sub-Partitioned Tables - Some Examples
Note 237397.1 How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS

Note 31412.1 SCRIPT - Select to show Optimizer Statistics for CBO

Note 210673.1 Different Values For EMPTY_BLOCKS and AVG_SPACE Using DBMS_STATS and ANALYZE
Bug 1874139 STATISTICS (EMPTY_BLOCKS & AVG_SPACE) ARE DIFFERENT AFTER EXPORT/IMPORT:
Bug 1759544 DBMS_STATS PACKAGE REPORTS '0' FOR EMPTY_BLOCKS:
Bug 2932639 WRONG TABLE STATISTICS CALCULATED IN AUTO SEGMENT SPACE MANAGED TABLESPACE:

Note 35272.1 Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?
Note 245051.1 Gathering Data Dictionary Statistics

Bug 2222368 DBMS_UTILITY.ANALYZE_DATABASE ALSO ANALYZES SYS
Bug 1422285 DBMS_STATS.GATHER_DATABASE_STATISTICS COLLECTS STATISTICS FOR SYS OBJECTS
Bug 2453682 DBMS_STATS.GATHER_SCHEMA_STATS DOES NOT GATHER STATISTICS FOR OBJECT TABLES

Discoverer中Choose connection模式，connection信息在哪里保存

Discoverer中Choose connection模式，connection信息在哪里保存
<質問内容>
ディスカバラーの接続で、接続選択と直接接続があるが、
接続選択の場合だと１度ユーザID等を登録すると、
次に接続する時にはその登録した情報が使用できる。
この登録した情報はどこに保持しているのか教えてほしい。
(クライアントなのかアプリケーションサーバなのか)


<回答内容>
接続選択の場合，根据是否使用Single Sign-On分为两种情况。
Single Sign-On が有効になっていない場合、接続信息在Cookie中保存（クライアント）。
Single Sign-On 有効的場合，接続信息在Single Sign-On database中保存（サーバ）。

详细信息请参考：
------------------------------------------------------
Oracle® Business Intelligence Discoverer
構成ガイド
10g リリース2（10.1.2.0.0）
部品番号: B15635-01
4-3 プライベート接続

Oracle® Application Server Single Sign-On
管理者ガイド
10g リリース2（10.1.2）
部品番号: B15823-01
1-2 外部アプリケーション
------------------------------------------------------

audit, fga, vpd and sys

audit, fga, vpd and sys

sys永远免疫vpd
The database user SYS is thus always exempt from VPD or Oracle Label Security enforcement, regardless of the export mode, application, or utility used to extract data from the database.

sys免疫普通的audit
AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records are written to the operating system's audit trail.

sys免疫fga，这里有个bug需要注意
Subject: Bug 3450991 - FGA does not work if SQL is run by the SYS user
Doc ID: Note:3450991.8 Type: PATCH
Last Revision Date: 10-AUG-2005 Status: PUBLISHED
Click here for details of sections in this note.

Bug 3450991 FGA does not work if SQL is run by the SYS user
This note gives a brief overview of bug 3450991.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 10.2
Versions confirmed as being affected 9.2.0.4
10.1.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.7 (Server Patch Set)
10.1.0.4 (Server Patch Set)
10.2.0.1 (Base Release)


Symptoms: Related To:
(None Specified)
Security ( Authentication / Privileges / Auditing )


Description
Once a SELECT statement has been run by SYS then other users sharing
that cursor do not get audited if there is an FGA policy on the table.

Workaround:
Do not run SQL from SYS user for FGA objects.

How to add swap space to rhel 3

How to add swap space to rhel 3

这只是几种可选方法中的一种。

1.使用fdisk创建新的分区(例如创建sdb1)
2.mkswap /dev/sdb1
3.在/etc/fstab文件中加入"/dev/sdb1 swap swap defaults 0 0"这一行
4.swapon -va
5.使用cat /proc/swaps或者cat /proc/meminfogrep Swap或者free令名确认

=================================================
附：How to remove swap space to rhel3

这种方法只是针对上面的增加swap的方法

1.swapoff -v /dev/sdb1
2.如果想要永久的删除swap空间，去掉/etc/fstab文件中相应的行。
3.使用cat /proc/swaps或者cat /proc/meminfogrep Swap或者free令名确认

STATISTIC "cache hit ratio" - Reference Note

Subject: STATISTIC "cache hit ratio" - Reference Note
Doc ID: Note:33883.1 Type: REFERENCE
Last Revision Date: 12-FEB-2004 Status: PUBLISHED


*** See Note 130955.1 for a description of the "STATISTIC" reference articles

Definition:
~~~~~~~~~~~
The "cache hit ratio" is a derived statistic referred to in a number of
manuals and articles. There is more than one definition of the cache hit
ratio in existence.
The hit ratio is intended to give an indication of how often the various
processes accessing the data buffers find the blocks in the Oracle buffer
cache. The exact value of the hit ratio is of less importance than the
ability to monitor it over time to notice any significant changes in the
profile of activity on the database.

*** Important: A very high hit ratio (close to 100%) is not necessarily good
The reasons for this are explained later.


Calculation:
~~~~~~~~~~~~
The most common formula in circulation for the hit ratio for the buffer cache
for Oracle7/8 is:

hit ratio = 1 - ( physical reads )
-----------------------------------
( consistent gets + db block gets )

This is often expressed as a percentage (by multiplying the result by 100).
However, in 7.3.4 and Oracle8 onwards the definition of the "physical reads"
statistic changed to include direct block reads as well as reads to get data
into the buffer cache. Hence the above formula only gives an lower bound for
the hit ratio on these releases.


A better formula in Oracle8i/9i is:

hit ratio =

1 - ( physical reads - (physical reads direct + physical reads direct (lob)) )
--------------------------------------------------------------------------
( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )


In Oracle8.0 onwards it is possible to use multiple buffer pools (by setting
the BUFFER_POOL_KEEP or BUFFER_POOL_RECYCLE init.ora parameters). Multiple
buffer pools are not discussed here but the hit ratios for each pool can be
seen using the V$BUFFER_POOL_STATISTICS view (which is created by the
CATPERF.SQL script in Oracle8 and is a standard view in Oracle8i onwards)
thus:

SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets ) ) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0
;

(Note that this view shows 0 for CONSISTENT_GETS and DB_BLOCK_GETS in 8.1.7 and so
the above select is useless in this release - See Bug 1491213 )


The "Miss Ratio"
~~~~~~~~~~~~~~~~
Occasionally you may see reference to the "miss ratio". This is just

Miss ratio = 100% - Hit Ratio (expressed as a percentage)



Notes about the Hit Ratio
~~~~~~~~~~~~~~~~~~~~~~~~~
A good hit ratio is expected for OLTP type systems but decision support type
systems may have much lower hit ratios. Use of parallel query will make the
hit ratio less meaningful if using the first form of calculation based on
"physical reads" only.

A hit ratio close to 100% does not mean the application is good. It is quite
possible to get an excellent hit ratio by using a very unselective index in a
heavily used SQL statement.
Eg: Consider a statement like:

SELECT * FROM employee WHERE empid=1023 AND gender='MALE';

If EMPLOYEE is a large table and this statement always uses the GENDER index
rather than the EMPID index then you scan LOTS of blocks (from the GENDER
index) and find nearly all of them in the cache as everyone is scanning this
same index over and over again. The hit ratio is very HIGH but performance
is very BAD. A common 'variation' on an "unselective" index is a heavily
skewed index where there are a large number of entries with one particular
value (eg: a workflow status code of CLOSED) - the index may perform well for
some queries and very poorly for the most common value.


A few comments:
~~~~~~~~~~~~~~~
- The "good" hit ratio is generally considered to be one >80%
There is probably still scope for tuning if it is <90% *BUT*
note that the hit ratio is not the best measure of performance.

- The ratio can be artificially high in applications making
poor use of an UNSELECTIVE index.

- In Oracle8.1 onwards "physical reads direct" are recorded

- Some documentation incorrectly reports hit ratio to be:

Hit Ratio = Logical Reads / ( Logical Reads + Physical Reads )

this is incorrect for any version of Oracle.


Related:
~~~~~~~~
Bug to change "physical reads" to include direct reads in Bug 404802
Documented Hit Ratio is incorrect Bug 2002504
7.3.4/8.0 onwards
reference note Note 47420.1

Differences between sysdba and sysoper

概要 SYSDBA, SYSOPER 権限について
内容:
[Problem]
connect / as sysdba (sysoper) で SYSDBA や SYSOPER としてアクセスできますが、
SYSOPER 権限と SYSDBA 権限は、どう違うのですか？
SYSOPER権限にはどういった制限があるのでしょうか。


[To reproduce]
SYSOPERは、以下のような作業を行なうことを許されています：
startup
shutdown
alter database open | mount
alter database backup controlfile
recover database
alter database archivelog
restricted session
SYSDBAは、これに加えて、以下の権限を持ちます：
sysoper 権限 with admin option
create database
recover database until

SYSOPERは startup/shutdown 等の基本的な管理業務を行なえますが、
ユーザのデータを見る権限を持ちません。これに対して、SYSDBAは
無制限の権限が与えられます。

connect / as sysdba で接続するとSYSスキーマに、
connect / as sysoper で接続すると、PUBLICスキーマに接続されます。

[更新履歴]
2007/01/22 SYSOPER権限で「alter tablespace begin/end backup」は実行できないため削除

A Guide to 10G CMAN configuration

Subject: A Guide to 10G CMAN configuration
Doc ID: Note:298916.1 Type: BULLETIN
Last Revision Date: 28-JUL-2005 Status: MODERATED


PURPOSE
-------

This document intends to give a comprehensive guide to 10G CMAN configuration.


SCOPE & APPLICATION
-------------------

Database administrators who want to configure 10G CMAN. This document is applicable
only to 10G CMAN and does not applies to earlier version of CMAN.


A Guide to 10G CMAN configuration
-----------------------------

1. Configuring CMAN.ORA

The first step towards configuring the 10G CMAN is to create a cman.ora in the
$ORACLE_HOME/network/admin directory on UNIX and in the ORACLE_HOME\network\admin
directory on Windows.

1.A. Configuring Listening endpoint (ADDRESS)

In 10G CMAN, a listener process listenes for all incoming requests. Unlike older
versions, you do not require to specify seperate listening endpoint list for the
CMGW gateway process and CMADMIN administrative process. We just require to configure
the listening endpoint for the Oracle Connection Manager listener.CMADMIN, the Oracle
Connection Manager monitoring process,uses this address to register information about
gateway processes with the listener. The database server, in turn, uses the address to
register service information at the Oracle Connection Manager node.

Example CMAN.ORA

CMAN1=(CONFIGURATION=
(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1522))
(RULE_LIST=
(RULE=(SRC=X.Y.0.0/16)(DST=)(SRV=*)(ACT=accept))
(rule=(src=test)(dst=127.0.0.1)(srv=cmon)(act=accept))))

Here the CMAN listener has been configured to listen to the port 1522.


1.B. Configuring Access control rule list (RULE_LIST)

The parameter RULE to specify an access control rule list to filter incoming
connections. A rule list specifies which connections are accepted, rejected, or
dropped.

Syntax :
(RULE_LIST=
(RULE=
(SRC= host)
(DST= host)
(SRV= service_name)
(ACT={accept|reject|drop})
(ACTION_LIST=AUT=on|off)(CONN_
STATS=yes|no)(MCT= time)(MIT= time)(MOCT= time)))
(RULE= ...))

Subparameters
The RULE parameter filters a connection or group of connections using the
following subparameters:
SRC: Specify the source host name or IP address in dot notation of the client.
DST: Specify the destination server host name or IP address in dot notation of the
database server.
SRV: Specify database service name of the Oracle Database 10g, Oracle9i, or
Oracle8i database (obtained from the SERVICE_NAME parameter in the initialization
parameter file).
ACT: Specify accept to accept incoming requests or reject to reject incoming
requests.
ACTION_LIST: Specify rule-level parameter settings for some parameters. These
parameters are as follows:

AUT Oracle Advanced Security authentication on client side
CONN_STATS log input and output statistics
MCT maximum connect time
MIT maximum idle timeout
MOCT maximum outbound connect time

Rule-level parameters override their global counterparts.

Please note that there should be at least one rule for client connections and one
rule for CMCTL connections as shown in the Example CMAN.ORA

(RULE_LIST=
(RULE=(SRC=X.Y.0.0/16)(DST=)(SRV=*)(ACT=accept))
(rule=(src=test)(dst=127.0.0.1)(srv=cmon)(act=accept))))

The first rule allows incoming connection from X.Y.0.0/16 subnet to the db_server
for any database service name.

The second rule allows CMCTL connections from the host "test" ( this is actually
the host where CMAN is running).

Wildcard usage: CMAN.ORA supports usage of "*" as wildcards. An askterisk can only be used to include or exlclude an entire value for a RULE
parameter. Partial values are not allowed.
Example you can use the following rule

(RULE=(SRC=*)(DST=)(SRV=*)(ACT=accept))
This rule will allow incoming connection from client with any ip address to the db_server
for any database service name.

But the following rule is not allowed

(RULE=(SRC=X.Y.0.*)(DST=)(SRV=*)(ACT=accept))

Instead you can use ip_address/nn notation for subnet addresses. For example a
rule like following will alow incoming connection from any client ip whose first
16 bit matches with 162.158.

(RULE=(SRC=162.158.0.0/16)(DST=)(SRV=*)(ACT=accept))

1.C. Configuring Parameter list (PARAMETER_LIST)

Parameters in the Oracle Connection manager are of two types global and rule level.
A global parameter applies to all Oracle Connection Manager connections, unless a
rule-level parameter overrides it. To change a global parameters default setting,
enter it into the PARAMETER_LIST, together with an allowable value.
A rule-level parameter is enabled in the ACTION_LIST section of the RULE_LIST
and applies only to connections specified by the rule. It overrides its global
counterpart.
show parameter command in the CMCTL> prompt shows all the current parameters.
Following is the command for our example CMAN.ora. It shows all the default value as we have not
configuerd any parameter_list for our CMAN.ORA
________________________________________________________________
CMCTL:cman1> show parameters
listener_address | (ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1522))
aso_authentication_filter | OFF
connection_statistics | OFF
event_group | OFF
log_directory | \network\log\
log_level | SUPPORT
max_connections | 256
idle_timeout | 0
inbound_connect_timeout | 0
session_timeout | 0
outbound_connect_timeout | 0
max_gateway_processes | 16
min_gateway_processes | 2
max_cmctl_sessions | 4
password | OFF
remote_admin | OFF
trace_directory | \network\trace\
trace_level | OFF
trace_timestamp | OFF
trace_filelen | 0
trace_fileno | 0
The command completed successfully.
_________________________________________________________________

Lets set two parameters IDLE_TIMEOUT and MAX_CONNECTIONS for the CMAN.
We will set IDLE_TIME=600 so that if a connection remain idle for 600 sec
(i.e no data exchange takes place in 600 sec) CMAN will disconnect the session.
We will also set MAX_CONNECTIONS=512 so that the maximum connections that can be
handled by a gateway process increased to 512.

CMAN.ORA


CMAN1=(CONFIGURATION=
(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1522))
(RULE_LIST=
(RULE=(SRC=X.Y.0.0/16)(DST=)(SRV=*)(ACT=accept))
(rule=(src=test)(dst=127.0.0.1)(srv=cmon)(act=accept))))
(PARAMETER_LIST=
(IDLE_TIMEOUT=600)
(MAX_CONNECTIONS=512)))

Please refer to Oracle Database Net Services Administrators Guide 10 g Release 1 (10.1)
Part No. B10775-01 for a complete list of parameters and detailed information on them.

2. Configuring Local_listener and Remote_listener parameters of the database

LOCAL_LISTENER and REMOTE_LISTENER parameter need to be configuered in the database
so that the database register itself both with the local listener and the CMAN.
LOCAL_LISTENER should point to the listener running in the database server and
REMOTE_LISTENER parameter should point to the CMAN listener port like

ALTER SYSTEM SET LOCAL_LISTENER="(address=(protocol=tcp)(host=)(port=
))"

ALTER SYSTEM SET REMOTE_LISTENER="(address=(protocol=tcp)(host=)(port=))
"

Following command the CMCTL> prompt after starting the CMAN show all the database
services registered with the CMAN.

CMCTL:cman1> show services

3. Configuring Clients for Oracle Connection Manager
Please update the client tnsnames.ora with the Oracle Connection Manager
entry

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1522))
(CONNECT_DATA =
(SERVER = dedicated)
(SERVICE_NAME = )
)
)

As you can see the tnsanems.ora entry is just like another normal entry,
the only difference is you mention the CMAN listener ip address and port no
instead of database listener.

4. Starting and administering the CMAN
To administer a CMAN instance first we need to give the
administer
in the CMCTL> prompt.
Then we can start the CMAN.
In our exaple we can start the CMAN like
$CMCTL
CMCTL> administer CMAN1
CMCTL> startup

Please ensure that the database services are registered with the CMAN by show services
Now you can test the connectivity from the client to the database server.

Setting up a password in the CMAN:

Oracle recomends setting a password in the CMAN to secure it.
Password can be set up in the following way

CMCTL:cman1> set password
Old password:
New password:
Reenter new password:

eg:
Old password:
New password: passwd
Reenter new password: passwd
The command completed successfully.

Just hit key for old password since no previuos password is set.
The passwords you entered will not be echoed.
This procedure does not save the password in the CMAN.ORA.
For this you have to execute save_passwd as below:

CMCTL:cman1> save_passwd
The command completed successfully.
CMCTL:cman1>

NOTE: The password is encrypted by default and can be set only by the above
method.


Remote administration:

You can adminster the CMAN from a remote machine also. To enable remote administration
you have to set REMOTE_ADMIN to yes in the CMAN.ORA and also add a rule to allow the
remote machine to access CMAN. Please note that for this rule also it needs to be
dst=127.0.0.1, otherwise the connection from remote machine will fail.

Example CMAN.ORA

CMAN1=(CONFIGURATION=
(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1522))
(RULE_LIST=
(RULE=(SRC=X.Y.0.0/16)(DST=)(SRV=*)(ACT=accept))
(rule=(src=test)(dst=127.0.0.1)(srv=cmon)(act=accept))
(rule=(src=)(dst=127.0.0.1)(srv=cmon)(act=accept)))
(PARAMETER_LIST=
(IDLE_TIMEOUT=60)
(MAX_CONNECTIONS=512)
(REMOTE_ADMIN=yes)))

In the remote machine tnsnames.ora an entry need to be added to resolve the remote CMAN address like the following

CMANR=(ADDRESS=(protocol=TCP)(host=test)(port=1522))

Reload the CMAN and now you will be able to access the CMAN from the remote machine

CMCTL> administer CMANR

5. Configure the database server for session multiplexing.

To enable Connection Manager to take advantage of session multiplexing, set the
MULTIPLEX=on in the DISPATCHERS parameter in the initialization parameter file.

Example:

DISPATCHERS="(PROTOCOL=tcp)(MULTIPLEX=on)"

6.CMAN log files.

Oracle Connection Manager generates four types of log files: one each for its
listener, gateway, and CMADMIN processes and one for alerts.

i) CMAN-instance-name_ pid.log : This file is log file for the CMAN listener. like normal
listener.log it shows the service registration, sevice update and connect informations.

ii)CMAN-instance-name_cmgw_pid.log This is the log file for the gateway process

iii) CMAN-instance-name_cmadmin_pid.log This is log file for CMADMIN processes.

iv) CMAN-instance-name_alert.log CMAN alert log alert log captures information about
instance startup and shutdown and also chronologically record of all critical errors.



RELATED DOCUMENTS
-----------------

Oracle Database Net Services Administrators Guide 10 g Releas


.




==================================

Subject: Troubleshooting Guide for the CMAN in 10g(10.1.0.3.0)
Doc ID: Note:305220.1 Type: BULLETIN
Last Revision Date: 07-SEP-2005 Status: MODERATED

Troubleshooting Guide for the CMAN in 10g(10.1.0.3.0)



These are the most common problems you face when using a 10g CMAN:



1) TNS-04012 when starting CMAN



2) TNS-04006: Invalid password When administering CMAN



3) TNS-04005: Unable to resolve address for



4) TNS-12538 when administering a CMAN



5) NL-00857 when Administer using command



6) Starting CMAN on windows just hangs with no errors







TNS-04012: Unable to start Oracle Connection Manager instance


When starting the CMAN on Windows machine you are receing the errors:

CMCTL:CMAN_indl040ad> startup

Failed to start service, error 997.

TNS-04012: Unable to start Oracle Connection Manager instance.

CMCTL:CMAN_indl040ad>

Or on Unix machine you are receiving the errors:

CMCTL:CMAN_incq048ad.idc.oracle.com> startup

TNS-04012: Unable to start Oracle Connection Manager instance.

CMCTL:CMAN_incq048ad.idc.oracle.com>



You receive this error usually when there is some syntax errors in the cman.ora file.

The usual causes are:



1.1:

The rule_list missing the line that is used to accept administration commands

from the local host. Either of the following entries in the RULE_LIST must exist in order to start the cman.

(rule=(src=indl040ad)(dst=127.0.0.1)(srv=cmon)(act=accept))

or

(rule=(src=*)(dst=*)(srv=*)(act=accept))



1.2:

The TRACE_LEVEL parameter in PARAMETER_LIST is configured with a numeric value. Fact is that in earlier releases cman tracing was enabled by the parameter TRACING=YES | NO under the PARAMETER_LIST in cman.ora. From 10g onwards, the tracing is enabled as done for listener or client or server sqlnet tracing. i.e the parameter TRACE_LEVEL in the PARAMETER_LIST has to be included for this. The parameter TRACING = YES | NO has been stopped.



But again, there is one more interesting fact about this.

The allowed values for this are “off | user | admin | support“

Note: only the above string values are allowed and the numeric equivalent of these values are not allowed.

If the numeric values equivalent for these are used in cman.ora, the cman service doesnot startup



1.3:

The ADDRESS_LIST parameter is used in the cman.ora file as below:

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1630))

)

This clause is not needed. Change it to the entry as below:



(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1630))





TNS-04006: Invalid password


CMCTL> administer

Enter CMAN Password:

TNS-04006: Invalid password

CMCTL>



This is the error that you receive when you are attempting to administer a cman instance that is password protected, is already up and running and enter an invalid password for this operation.

However, if the CMAN is not started yet, the administer command succeeds but when the startup command is issued it prompts for the password. If an incorrect password is entered, it starts up the CMAN service but also throws the above error.



TNS-04005: Unable to resolve address for


If you give an instance name that doesnot exist or if you commit a typo when issuing an ADMINISTER command you receive the following error.



CMCTL> administer test

TNS-04005: Unable to resolve address for test

CMCTL>



TNS-12538 when administering a CMAN


If you have the word PROTO instead of the key word PROTOCOL in the cman.ora file, you receive the error message as below:



CMCTL> administer

TNS-12538: TNS:no such protocol adapter

CMCTL>



The keyword has to be the complete word PROTOCOL and not just PROTO



NL-00857 when Administer using command


The ADMINISTER command is used to choose an instance of Oracle Connection Manager.



Syntax

From the Oracle Connection Manager Control utility:

CMCTL> ADMINISTER [-c instance_name] using [password]



Strangely this does not work as below:



CMCTL> administer -c cman_indl040ad using jayu123

NL-00857: wrong number (0 - 3 needed) of arguments to "administer"



Correct syntax that works is:

CMCTL> administer cman_indl040ad using jayu123

Current instance cman_indl040ad is already started

Connections refer to (address=(protocol=TCP)(host=indl040ad)(port=1560)).

The command completed successfully.

CMCTL:cman_indl040ad>



A bug 4297538 has been filed for the same to make modifications



Starting CMAN on windows just hangs with no errors on Win2k


Sometimes the starting of CMAN from a command prompt hangs without returning any errors on windows platform.



CMCTL> administer cman_indl040ad using jayu123

Current instance cman_indl040ad is already started

Connections refer to (address=(protocol=TCP)(host=indl040ad)(port=1560)).

The command completed successfully.

CMCTL:cman_indl040ad> shutdown

The command completed successfully.

CMCTL:cman_indl040ad> startup





You have to issue a CTRL + C for coming out of this.

The services panel indicates that the services are started for this instance but show all command from CMCTL gives the output that instance is not running as illustrated below:



CMCTL> administer

Current instance CMAN_indl040ad is not yet started

Connections refer to (address=(protocol=TCP)(host=indl040ad)(port=1560)).

The command completed successfully.

CMCTL:CMAN_indl040ad> show all

TNS-04011: Oracle Connection Manager instance not yet started.

CMCTL:CMAN_indl040ad>

The trace files aslo doesnot indicate any errors:

Excerpt from Cmadmin trace file:

[05-JAN-2005 13:23:50:374] sntpinherit: Attempting to make pipe \\.\PIPE\ORANTP50C.3B0.w

[05-JAN-2005 13:23:50:374] sntpinherit: Listening on pipe \\.\PIPE\ORANTP50C.3B0.w

[05-JAN-2005 13:23:50:374] sntpinherit: Attempting to make pipe \\.\PIPE\ORANTP50C.3B0

[05-JAN-2005 13:23:50:374] sntpinherit: Listening on pipe \\.\PIPE\ORANTP50C.3B0

[05-JAN-2005 14:53:40:613] nttaddr2bnd: entry

[05-JAN-2005 14:53:40:623] nttaddr2bnd: exit

[05-JAN-2005 14:53:40:623] nsgllsn: Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=indl040ad.idc.oracle.com)(PORT=1560)))

…

and goes on in a loop with entries similar to below:



[05-JAN-2005 14:53:40:623] nsiorecv: entry

[05-JAN-2005 14:53:40:623] nttmrd: entry

[05-JAN-2005 14:53:40:623] nttrd: entry

[05-JAN-2005 14:53:40:623] ntt2err: entry

[05-JAN-2005 14:53:40:623] ntt2err: soc 396 error - operation=5, ntresnt[0]=524, ntresnt[1]=36, ntresnt[2]=0

[05-JAN-2005 14:53:40:623] ntt2err: exit

[05-JAN-2005 14:53:40:623] nttrd: exit

[05-JAN-2005 14:53:40:623] nsiorecv: exit (0)

….

[05-JAN-2005 14:53:40:623] nsiorecv: entry

[05-JAN-2005 14:53:40:623] nttmrd: entry

[05-JAN-2005 14:53:40:623] nttrd: entry

[05-JAN-2005 14:53:40:623] ntt2err: entry

[05-JAN-2005 14:53:40:623] ntt2err: soc 396 error - operation=5, ntresnt[0]=524, ntresnt[1]=36, ntresnt[2]=0

[05-JAN-2005 14:53:40:623] ntt2err: exit

[05-JAN-2005 14:53:40:623] nttrd: exit

[05-JAN-2005 14:53:40:623] nsiorecv: exit (0)



The services cannot be stopped, gives an error and status remains as STOPPING.

We will have to reboot the machine for this so that the services are either started automatically if properties are set to AUTOMATIC or not started if set to MANUAL option.



But if this operation is tried by just issuing administer, no problem is encountered as below:



CMCTL> administer

Enter CMAN Password:

Current instance CMAN_indl040ad is already started

Connections refer to (address=(protocol=TCP)(host=indl040ad)(port=1560)).

The command completed successfully.

CMCTL:CMAN_indl040ad> startup

Starting Oracle Connection Manager instance CMAN_indl040ad. Please wait...

Enter CMAN Password:

CMAN for 32-bit Windows: Version 10.1.0.3.0 - Production

Status of the Instance

….

The command completed successfully.

CMCTL:CMAN_indl040ad> exit



Though it indicates that it is administering the proper instance, the reason why the startup hang as indicated above example is due to the fact that the case used for cman_instance in the administer command is different than in cman.ora. This indicates that cman.ora is case sensitive for the instance name.



This works if proper case is given for the command as illustrated below:



CMCTL> administer CMAN_indl040ad using jayu123

Current instance CMAN_indl040ad is not yet started

Connections refer to (address=(protocol=TCP)(host=indl040ad)(port=1560)).

The command completed successfully.

CMCTL:CMAN_indl040ad> startup

Starting Oracle Connection Manager instance CMAN_indl040ad. Please wait...

CMAN for 32-bit Windows: Version 10.1.0.3.0 - Production

Status of the Instance

…

The command completed successfully.

CMCTL:CMAN_indl040ad> shutdown

The command completed successfully.

CMCTL:CMAN_indl040ad> exit



This case sensitiveness behaviour is not shown in unix platform (linux tested).

flashback的一些限制

用户SYS的表不能flashback table
SYSTEM表空间的表不能flashback drop

how to remove crs after a failed instllation

Subject: 10g RAC: How to Clean Up After a Failed CRS Install
Doc ID: Note:239998.1 Type: BULLETIN
Last Revision Date: 07-DEC-2006 Status: PUBLISHED


PURPOSE
-------

The purpose of this document is to help DBA's and support analysts understand how
to clean up a failed CRS (Cluster Ready Services) install for 10g RAC.


SCOPE & APPLICATION
-------------------

DBA's and Support Analysts


10g RAC: How to Clean Up After a Failed CRS Install
---------------------------------------------------

Not cleaning up a failed CRS install can cause problems like node reboots.
Follow these steps to clean up a failed CRS install:

1. Run the rootdelete.sh script then the rootdeinstall.sh script from the
$ORA_CRS_HOME/install directory. Running these scripts should be sufficent
to clean up your CRS install. If you have any problems with these scripts
please open a service request.

If for some reason you have to manually remove the install due to problems
with the scripts, continue to step 2:

2. Stop the Nodeapps on all nodes:

srvctl stop nodeapps -n

3. Prevent CRS from starting when the node boots. To do this issue the following
as root:

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
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

Linux:

rm /etc/oracle/*
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
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

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/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

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
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

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
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot
the node:

ps -ef | grep crs
kill
ps -ef | grep evm
kill
ps -ef | grep css
kill

Do not kill any OS processes, for example icssvr_daemon process !

5. If there is no other Oracle software running (like listeners, DB's, etc...),
you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:

rm -f /var/tmp/.oracle

or

rm -f /tmp/.oracle

6. Remove the ocr.loc
Usually the ocr.loc can be found at /etc/oracle

7. De-install the CRS home in the Oracle Universal Installer

8. Remove the CRS install location:

rm -Rf /*

9. Clean out the OCR and Voting Files with dd commands. Example:

dd if=/dev/zero of=/dev/rdsk/V1064_vote_01_20m.dbf bs=8192 count=2560
dd if=/dev/zero of=/dev/rdsk/ocrV1064_100m.ora bs=8192 count=12800

If you placed the OCR and voting disk on a shared filesystem, remove them.

If you are removing the RDBMS installation, also clean out any ASM disks if
they have already been used.

10.If you would like to re-install CRS, follow the steps in the RAC Installation manual.

lsnrctl services => state:blocked

lsnrctl services => state:blocked
ORA-12519

增加参数processes

When LOG_ARCHIVE_FORMAT is ignored

Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 1 (10.1)
Part Number B10734-01
Filenames for Archived Redo Log Files in the Flash Recovery Area

The generated filenames for the archived redo logs in the flash recovery area are
Oracle Manged Filenames and are not determined by LOG_ARCHIVE_FORMAT.




Oracle® Database Administrator's Guide
10g Release 1 (10.1)
Part Number B10739-01
Some additional initialization parameter considerations:
LOG_ARCHIVE_FORMAT is ignored if a disk group is specified for LOG_ARCHIVE_DEST
(for example, LOG_ARCHIVE_DEST = +dgroup1).

rootユーザにてOS認証を使用してSYSDBA権限で接続しようとするとORA-1031が発生

文書番号 96826 最終更新日 2005-02-17 公開区分 契約顧客のみ
製品名(バージョン)[コンポーネント] Oracle Server - Enterprise Edition (ALL - ALL) [ユーザー管理]
プラットフォーム(バージョン) すべてのプラットフォーム (UNIX - )
関連文書 　

概要 rootユーザにてOS認証を使用してSYSDBA権限で接続しようとするとORA-1031が発生
内容:
[質問]
dba グループに root ユーザを追加し、環境変数を正しく設定しているにも関わ
らずAOS 認証を使用して SYSDBA 権限で SQL*Plus より接続しようとすると、
以下のエラーが出ます。なぜでしょうか。

　ORA-01031: insufficient privileges


[対象リリース]
すべてのリリース


[対象プラットフォーム]
UNIX プラットフォーム


[回答]
root ユーザは特別なユーザとなるため、dba グループに追加しても、OS 認証を
使用して SYSDBA 権限で接続することはできません。この動作は製品の仕様によ
るものです。


[更新履歴]
2005/02/20 本文書を公開

key words: OS authentication, root, ora-01031

インストール時に設定したSYSDBA・SYSOPERに属するOS groupの変更方法(UNIX)

文書番号 17161 最終更新日 2003-08-29 公開区分 契約顧客のみ
製品名(バージョン)[コンポーネント] Oracle Server - Enterprise Edition ( - ) [RDBMS]
プラットフォーム(バージョン) すべてのプラットフォーム ( - )
関連文書 50508.12 　

概要 インストール時に設定したSYSDBA・SYSOPERに属するOSグループの変更方法(UNIX)
内容:
[Problem]
インストール時にSYSDBA,SYSOPERにOSのユーザグループ dba でなく oinstall を
間違えて設定してしまいました。
再インストール以外に設定変更の方法を教えてください。

[Action]
下記の手順で設定変更できます。

(1)データベース及びリスナーなどを正常終了させる。

(2)$ORACLE_HOME/rdbms/lib 以下にconfig.c/config.sのどちらのファイルが存在
するかによって、変更方法が異なります。

i) $ORACLE_HOME/rdbms/lib/config.cが存在する場合、以下を変更して下さい。

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "oinstall" <= "dba" に変更してください。
#define SS_OPER_GRP "oinstall"　<= "dba" に変更してください。

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};


ii) $ORACLE_HOME/rdbms/lib/config.sが存在する場合、以下を変更して下さい。


.L12:
/* 0x0008 15 */ .ascii "oinstall\0" <= "dba\0" に変更してください。
/* 0x0014 20 */ .align 4


.L13:
/* 0x0014 22 */ .ascii "oinstall\0" <= "dba\0" に変更してください。


.csect H.12.NO_SYMBOL{RO}, 3
.string "oinstall" <= "dba" に変更してください。
# End csect H.12.NO_SYMBOL{RO}

-- 以下が存在する場合
.csect H.14.NO_SYMBOL{RO}, 3
.string "oinstall" <= "dba" に変更してください。
# End csect H.14.NO_SYMBOL{RO}
.long 0x00000000

※上記はdbaグループに設定する例。"dba"がOSのユーザグループが必ず存在することのご確認の上
　設定下さい。


(3)再リンクを実行

　 cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk config.o
make -f ins_rdbms.mk ioracle

※ 補足:
64bit環境において config.o をリンクする際に以下のようなエラーが発生する
場合があります。
% make -f ins_rdbms.mk config.o
/usr/ccs/bin/as: "config.s", line 12: error: cannot use v9 instructions in a non -v9 target binary
/usr/ccs/bin/as: "config.s", line 14: error: cannot use v9 instructions in a non -v9 target binary

　　その場合は、以下を実行下さい。一緒にconfig.oもリンクされます。
% make -f ins_rdbms.mk ioracle


キーワード：

SYSDBA SYSOPER DBA CONFIG.C CONFIG.S INTERNAL パスワード

Alert Log: Shutdown Waiting for Active Calls to Complete

Alert Log: Shutdown Waiting for Active Calls to Complete
Doc ID: Note:1039389.6 Type: PROBLEM
Last Revision Date: 25-OCT-2005 Status: PUBLISHED


Problem Description:
====================

You are attempting to shut down the database and the database hangs. The
alert log contains the following message:


SHUTDOWN: Waiting for active calls to complete


There are no other error messages in the alert log.


Solution Description:
=====================

Locate and kill any client connections to the database at the Unix level, as
follows:

1. Locate any client connections to the database using ps, and grep for any
processes belonging to this .

Example: ps -ef | grep V733

2. Look for processes that include a 'Local=No' designation.

Example: osupport 6235 1 0 Nov 24 0:01 oracleV733 (LOCAL=NO)

3. Kill the Unix process(es) with the 'Local=No' designation.

Example: Kill -9 6235


Explanation:
============

The database is waiting for pmon to clean up processes, but pmon is unable to
clean them. The client connections to the server are causing the shutdown
immediate or normal to hang. Killing them allows pmon to clean up and release
the associated Oracle processes and resources.

external table access parameter "enclosed by"

(abc) => enclosed by '(' and ')'
"abc" => enclosed by '"' and '"'
'abc' => enclosed by "'" and "'"

until sequence clause

recover database until sequence 4;

产生的结果是：archived log sequence 3 被应用而archived log sequence 4 不被应用。