目前分類：Oracle (4)

瀏覽方式： 標題列表 簡短摘要
來源 : http://atozoracle.blogspot.tw/2009/01/how-to-check-any-deadlock-and-other.html
 
Normally deadlock generates dump file and automatically is released by oracle system process
 
1) check v$session
sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null
 
 
2) To kill a locked session, first need to find sid, serial and use
 
sqlplus>alter system kill session 'sid, serial#';
 
*** you need have dba priviledge to kill sessions
 
 
3. To find which SQL has lock wait
 
sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece

migasun 發表在 痞客邦 留言(0) 人氣()

▲top

ORACLE AS OPMN 無法重新啟動解決方法
ps -ef | grep 'opmn -d' | grep -v grep | awk '{print $2}' | xargs kill -9

1.stopall
/OracleAS/opmn/bin/opmnctl stopall
2.query dead process
ps -ef|grep 'opmn -d'|grep -v 'grep'
3.kill all
kill -9
4.stopall
/OracleAS/opmn/bin/opmnctl opmnctl stopall
5.
/OracleAS/opmn/bin/opmnctl status -l

http://forums.oracle.com/forums/thread.jspa?threadID=388156&start=15&tstart=0

Hi all,

Please find below details steps

Cause

You have previously started the opmn process as an incorrect user (such as
root) but are now logged in as the unix user that owns the Application Server
installation. The AS unix owner then experiences permission related errors
communicating with the root initiated OPMN process.

It can be confirmed that this has taken place by listing the ownership of the
running "opmn -d" processes:
$ ps -ef|grep 'opmn -d'|grep -v 'grep'
root 31011 1 0 09:08 ? 00:00:00
/as1013/app/oracle/product/mid101300a/opmn/bin/opmn -d
root 31012 31011 0 09:08 ? 00:00:00
/as1013/app/oracle/product/mid101300a/opmn/bin/opmn -d
Solution

To implement the solution, please execute the following steps:

1. Capture output from "ps -ef|grep 'opmn -d'|grep -v 'grep'" to identify
current opmn daemon processes to be terminated.
2. Capture output from "opmnctl status -l" for future reference
- it may be necessary to manually kill processes that the current "opmn -d"
processes have started
3. Log in to the UNIX account that owns the current "opmn -d" processes (this
may require root privileges).
4. Use "opmnctl stopall" to stop the opmn daemon and all processes
5. Use "ps -ef|grep 'opmn -d'|grep -v 'grep'" to confirm the two "opmn -d"
processes have been terminated
- use "kill -9
" on each PID to force this if necessary (again, this may
need root privileges)
6. Review the PID column from the "opmnctl status -l" and confirm that each
process has been stopped.
- again use "kill -9
" on each PID to force this if necessary (again,
this may need root privileges)
7. For the directory of each OC4J instance that exists below
$ORACLE_HOME/j2ee,
rename the "persistence" directory to "persistence.old"
8. Confirm if any files under the $ORACLE_HOME have been created under the
incorrect unix account:

$ find $ORACLE_HOME -user root -print
With the exception of files below the $ORACLE_HOME/Apache/Apache/bin
directory, use "chown" to reset the owner and group of each files that has
changed.

For example:

$ cd $ORACLE_HOME/opmn/conf
$ ls -ld1 .*
drwx------ 3 mid101300a oinstall 4096 Sep 7 09:08 .
drwx------ 8 mid101300a oinstall 4096 May 30 09:00 ..
-r-------- 1 root root 21 Sep 7 09:08 .formfactor
Use the ownership of the directory guide the "chown" command needed:

$ chown mid101300a:oinstall .formfactor
9. Log out from the incorrect UNIX account and log back in as the UNIX user
owning the AS installation.
10. Start opmn and desired managed processes:

$ opmnctl start

$ opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...

$ opmnctl startproc process-type=home
opmnctl: starting opmn managed processes...
11. Confirm via "ps -ef|grep 'opmn -d'|grep -v 'grep' " that opmn daemon
processes are owned by the expected user
12. Use "opmnctl status -l" to confirm all processes have started correctly.

Regards,
Raza

migasun 發表在 痞客邦 留言(0) 人氣()

▲top
  • Dec 09 Thu 2010 22:08

  • OpenID

OpenID Authentication 2.0
http://openid.net/specs/openid-authentication-2_0.html

migasun 發表在 痞客邦 留言(0) 人氣()

▲top

Oracle Application Server connection pool 參數






























































































Attribute Name Description Default
name (Required) The name of the connection pool; must be unique None
min-connections The minimum number of connections that the connection pool will maintain 0
max-connections The maximum number of connections that can be open at any given time; a value of 0 or less indicates that there is no maximum limit. 0
initial-limit Sets the size of the connection cache when the cache is initially created or reinitialized. When this property is set to a value greater than 0, that many connections are pre-created and are ready for use. This property is typically used to reduce the "ramp-up" time in priming the cache to its optimal size. 0
used-connection-wait-timeout The amount of time to wait (in seconds) for a used connection to be released by a client.  This attribute applies only when the maximum number of connections have been retrieved from the data source and are in use. In this case, when a client tries to borrow a connection from the pool and all connections are in use, the connection pool will wait for a connection to be released back to the pool. 60
inactivity-timeout The amount of time (in seconds) that an unused connection is inactive before it is removed from the pool 60
login-timeout The maximum amount of time (in seconds) that this data source will wait while attempting to connect to a database. A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout. 0
connection-retry-interval The interval to wait (in seconds) before retrying a failed connection attempt; used in conjunction with "max-connnect-attempts" 1
max-connect-attempts The number of times to retry making a connection; used in conjunction with "connection-retry-interval" 3
validate-connection Indicates whether a connection, when borrowed from the pool, will be validated against the database.  A value of "true" indicates that when a connection is borrowed from the connection pool, the statement, stated by "validate-connection-statement," is executed to verify that the connection is valid.  A value of "false" means that no statement will be executed when a connection is borrowed from the pool. Used in conjunction with "validate-connection-statement." false
validate-connection-statement The SQL statement to execute when a connection is borrowed from the pool; used in conjunction with "validate-connection" None
num-cached-statements The maximum number of statements that should be cached for each connection.  Any value greater than 0 automatically enables statement caching for the data source. 0
time-to-live-timeout The maximum time, in seconds, a used connection may be active. When this timeout expires, the used connection is unconditionally closed, the relevant statement handles are canceled, and the connection is returned to the connection pool.  A value of –1 means that the feature is not enabled. -1
abandoned-connection-timeout Oracle databases only.  Abandoned-connection-timeout is similar to the inactivity-timeout, but on a logical connection. When set, JDBC monitors SQL database activity on this logical connection (connection borrowed from the cache by the user). For example, when a stmt.execute() is invoked on this connection, a heart beat is registered to convey that this connection is active. The heart beats are monitored only at places (to lower the cost of monitoring), that result in database execute calls. If a connection has been inactive for the specified amount of time, the underlying PooledConnection is reclaimed and returned to the cache for reuse. The default value is -1, meaning, this feature is not in effect. -1
disable-server-connection-pooling Determines whether or not to disable the application server's connection pool.  This attribute is available because some JDBC drivers provide connection pooling inside the driver. When the JDBC driver is Oracle and the driver is using the Implicit Connection Cache, then this attribute will be ignored. false
property-check-interval Oracle databases only. The time interval (in seconds) for the cache daemon thread to enforce the time out limits. 900
lower-threshold-limit Oracle databases only. The lower threshold limit on the connection pool. Defaults to 20% of the max-connections. 20%

參考資料:http://www.oracle.com/technology/pub/notes/technote_ds_caching.html

migasun 發表在 痞客邦 留言(0) 人氣()

▲top