Thursday, March 29, 2007

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

Tuesday, March 27, 2007

flashback的一些限制

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

Friday, March 23, 2007

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.

Monday, March 19, 2007

lsnrctl services => state:blocked

lsnrctl services => state:blocked
ORA-12519

增加参数processes

Wednesday, March 14, 2007

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

Wednesday, March 07, 2007

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 パスワード

Sunday, March 04, 2007

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.

Friday, March 02, 2007

external table access parameter "enclosed by"

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

Thursday, March 01, 2007

until sequence clause

recover database until sequence 4;

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