[To] Modify Oracle Process and Session

2011-04-18  来源:本站原创  分类:Database  人气:69 

java.sql.SQLException: Io exception: Connection refused (DESCRIPTION = (TMP =) (VSNNUM = 169869568) (ERR = 12519) (ERROR_STACK = (ERROR = (CODE = 12519) (EMFI = 4)))) at oracle. jdbc.dbaccess.DBError.throwSqlException

This problem, I let him check the previous procedure, programs have been identified, he said no problem. Later I also had them test the program, the program really is no problem. Then I thought might be the problem of database connections

I use the select * from sys.v_ $ session

SQL> select count (*) from v $ process;

COUNT (*)
----------
44

SQL> select count (*) from v $ session;

COUNT (*)
----------
39

The sql view of what the current number of user connections. This result is also normal, then I think this because of his business is more complex, may be oracle default number of connections is not enough, so we modified the oracle init.ora file configuration. In this and to share with you in the oracle installation directory:% oracle_home% \ admin \ orcl \ pfile \ init.ora file a

We analyze the contents of this file

################################################## ####### Copyright (c) 1991, 2001 by Oracle Corporation
################################################## #######
MTS
dispatchers = "(PROTOCOL = TCP) (SER = MODOSE)", "(PROTOCOL = TCP) (PRE = oracle.aurora.server.GiopServer)", "(PROTOCOL = TCP) (PRE = oracle.aurora.server.SGiopServer ) "
###########################################
# Other
###########################################
compatible = 9.0.0db_name = orcl
###########################################
# Distributed, replication and snapshot

###########################################
db_domain = "" remote_login_passwordfile = EXCLUSIVE
###########################################
# Sort, hash joins, bitmap indexes

###########################################
sort_area_size = 524288
###########################################
# File configuration
###########################################
control_files = ("D: \ oracle \ oradata \ orcl \ CONTROL01.CTL", "D: \ oracle \ oradata \ orcl \ CONTROL02.CTL", "D: \ oracle \ oradata \ orcl \ CONTROL03.CTL")
###########################################
# Pools

###########################################
java_pool_size = 33554432large_pool_size = 1048576shared_pool_size = 33554432
###########################################
# Cursors and Library Cache

###########################################
open_cursors = 300
###########################################
# System administration revoked and rollback segment

###########################################
undo_management = AUTOundo_tablespace = UNDOTBS
###########################################
# Network Registration
###########################################
instance_name = orcl
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest = D: \ oracle \ admin \ orcl \ bdumpcore_dump_dest = D: \ oracle \ admin \ orcl \ cdumptimed_statistics = TRUEuser_dump_dest = D: \ oracle \ admin \ orcl \ udump
###########################################
# Process and session
###########################################
processes = 150
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target = 300
###########################################
# Cache and I / O
###########################################
db_block_size = 4096db_cache_size = 33554432

In this paper processes = 150 us after this value to 260 no problem! However, to note: the maximum number of connections with the user's hardware configuration for you, so be careful

Of course, there are two other ways to modify:

The first method:
Routines in the Oracle Enterprise Manager -> session to view the current connection to the database details

The second method:
SQLPLUS to run: alter system set processes = 250 scope = spfile;

Other:

ORACLE number of connections (sessions) and its parameter file in the number of processes (process) related to their relationship as follows:

sessions = (1.1 * process +5)

But when we increase the number of process, often the database is not started. This is because we missed a unix tune system parameters: it is / etc / system / in semmns, which is unix system semaphore parameters. Each process will take a semaphore. semmns adjustment, the need to restart the unix operating system parameters to take effect. However, its size will be constrained by the hardware memory or ORACLE SGA. Range from 200-2000 dollars.

semmns is calculated as: SEMMNS> processes + instance_processes + system processes = processes the value of the database parameters instance_processes = 5 (smon, pmon, dbwr, lgwr, arch)

system = system occupied by the semaphore.

System occupied by the semaphore can be used to identify the following command: # ipcs-sb

Containing NSEMS display system has been occupied by semaphore.

Connect with other relevant parameters, such as licence_max_sessions, licence_sessions_warning other default settings are zero, that is, no limit.

Transfer from above: http://blog.sina.com.cn/s/blog_53d27b3a01008o5r.html

Starting in Oracle9i, ALTER SYSTEM command adds a new option scope. There are three optional scope parameter values: memory, spfile, and both.

● memory: just change the current instance is running, restart the database after a failure.

● spfile: spfile only change the settings, do not change the current instance is running, restart the database to take effect.

● both: while changing the instance and spfile, the current changes to take effect immediately, restart the database is still valid.

You can import the lead out of the ALTER SYSTEM or change the spfile content.

For RAC environment, ALTER SYSTEM SID can also specify parameters for different instances of a different setting.

Spfile change parameters through the full command is as follows:

alter system set <parameter_name> = <value> scope = memory | spfile | both [sid = <sid_name>]

from offical doc: The official documentation is a good thing, ah, ah read carefully, what are ....

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

SCOPE Clause Description
SCOPE = SPFILE The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is effective at the next startup and is persistent.

For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORY The change is applied in memory only. The effect is as follows:
For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.

For static parameters, this specification is not allowed.

SCOPE = BOTH The change is applied in both the server parameter file and memory. The effect is as follows:
For dynamic parameters, the effect is immediate and persistent.

For static parameters, this specification is not allowed.

It is an error to specify SCOPE = SPFILE or SCOPE = BOTH if the server is not using a server parameter file. The default is SCOPE = BOTH if a server parameter file was used to start up the instance, and MEMORY if a text initialization parameter file was used to start up the instance.

For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

The following statement changes the maximum number of job queue processes allowed for the instance. It includes a comment, and explicitly states that the change is to be made only in memory (that is, it is not persistent across instance shutdown and startup).

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 50 COMMENT = 'temporary change on Nov 29' SCOPE = MEMORY;

Transfer from above: http://blog.sina.com.cn/s/blog_5fbb16590100krtn.html

相关文章
  • [To] Modify Oracle Process and Session 2011-04-18

    java.sql.SQLException: Io exception: Connection refused (DESCRIPTION = (TMP =) (VSNNUM = 169869568) (ERR = 12519) (ERROR_STACK = (ERROR = (CODE = 12519) (EMFI = 4)))) at oracle. jdbc.dbaccess.DBError.throwSqlException This problem, I let him check th

  • Find oracle slow the session and SQL 2010-08-11

    How to find oracle slow the session and SQL Source: 7:00 Testing Network First, how to find a large consumption of resources and implementation of Oracle's session of the SQL statement HP-UX can use glance, top, IBM-AIX can use topas, also can use th

  • oracle process (Section server process) 2010-06-03

    We all know the oracle database instance simply means: the process used by the process + memory (SGA), then the oracle process in the background in the end have done some things? First of all, oracle process is divided into three types: Server proces

  • Maximum number of connections to modify Oracle 2010-10-08

    Maximum number of connections to modify Oracle 1, the maximum number of connections to modify the method Oracle a, logged in sysdba SQLPLUS b, find the current number of connections SQL> show parameter processes NAME TYPE VALUE ----------------------

  • Modify "Oracle" database the number of processes and sessions 2011-05-30

    ◆ 1. Through SQLPlus Oracle's sessions, and processes to modify the relationship is: sessions = 1.1 * processes + 5 Using sys, sysdba privileges to log on: SQL> show parameter processes; NAME TYPE VALUE -------- aq_tm_processes integer 1db_writer_pro

  • View and modify Oracle character set 2011-04-30

    View and modify Oracle character set <! - ALL ADSENSE ADS DISABLED -> View and modify Oracle character set, view and modify Oracle 10G character set; view and modify Oracle 11G character set oracle us to learn some simple modifications and configura

  • Oracle 彻底 kill session 2012-03-09

    --*************************** -- Oracle 彻底 kill session --*************************** kill session 是DBA经常碰到的事情之一.如果kill 掉了不该kill 的session,则具有破坏性,因此尽可能的避免这样的错误发生.同时也应当注意, 如果kill 的session属于Oracle 后台进程,则容易导致数据库实例宕机. 通常情况下,并不需要从操作系统级别杀掉Oracle会话进程,但并非总是如此

  • Modify oracle 150 maximum number of connections 2011-01-06

    Modify oracle 150 maximum number of connections Use sys, sysdba privileges to: c: \ sqlplus / nolog SQL> conn / as sysdba SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- --- ---------------------------

  • How to manually modify Oracle sga scn? 2011-10-19

    Manually modify Oracle sga There are many ways, here only one, is to use oracle debug tool SQL> alter database open read only; Database altered. SQL> col scn for 9999999999999999 SQL> select dbms_flashback.get_system_change_number scn from dual;

  • oracle modify the process 2010-10-12

    In oracle, we should always view the process: View ORACLE maximum number of processes: SQL> select count (*) from v $ session # number of connections SQL> Select count (*) from v $ session where status = 'ACTIVE' # number of concurrent connections S

  • oracle in the session 2010-12-03

    Oracle session connection issues record number and inactive 2009-03-10 15:42:37 oracle learning to read 118 comments 0 Font Size: medium and small subscription. From last week, Oracle database server problems, and less than half a day, will report ma

  • Check oracle process deadlock and end the deadlock 2011-09-30

    Process the SQL statement locks the table view 1: select sess.sid, sess.serial #, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v $ locked_object lo, dba_objects ao, v $ session sess where ao.object_id = lo.object_id and lo

  • Modify Oracle XE HTTP listener port (8080) 2010-09-30

    After installing Oracle XE default, HTTP listener port is 8080, and the tomcat port conflict, modify as follows: Open SQL * Plus console. With the sys or system log. Then run: begin dbms_xdb.sethttpport('8888'); end; / Port that was revised to 8888 S

  • Modify "Oracle" database processes and sessions 2011-05-30

    ◆ 1. By Oracle's SQLPlus sessions and processes to modify the relationship: sessions = 1.1 * processes + 5 Use sys, sysdba privileges to: SQL> show parameter processes; NAME TYPE VALUE -------- aq_tm_processes integer 1db_writer_processes integer 1 j

  • oracle process (II background process) 2010-06-06

    Mentioned oralce database instance consists of two parts: a set of processes and process memory used (SGA). In fact more accurate to say is: SGA and a set of background processes. Then the background process is to complete what function? Background p

  • lock unlock oracle process 2010-06-18

    -- Method 1 select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid; -- Method 2 select

  • Modify oracle database memory settings 2010-07-24

    c:\sqlplus /nolog SQL> conn /as sysdba Connected . SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 58

  • The two kinds of oracle temporary tables, session level and transaction level usage of temporary tables 2011-01-11

    Temporary table features: 1. The independence of multi-user operation: use the same temporary table for the different users, ORACLE will be assigned a separate temporary table, thus avoiding multiple users on a temporary table with the occurrence of

  • Encoding format to view and modify Oracle 2011-07-25

    - See oracle database character set: select userenv ('language') from dual; Results: SIMPLIFIED CHINESE_CHINA.AL32UTF8 - Amend the oracle database character set: (In SQL Plus in) sql> conn / as sysdba; sql> shutdown immediate ;<!-- IWMS_AD_BEGIN

  • Oracle process of writing anonymous 2010-11-16

    PL / SQL block in an Oracle database can be stored as: Function, process, package, trigger Process which can be anonymous, it can be generally stored procedure below is the format of a stored procedure PROCEDURE name [(parameter[,parameter,...])] IS