ORACLE connection overrun modification, ORA-00064, ORA-12571, ORA-24324 and user connection View

1) for ORACLE connection overrun modification, we need to adjust the maximum number of connections of the oracle database. The adjustment of the number of connections is made in the init. ora file in the dbs directory of the oacle.
The number of ORACLE connections (sessions) is related to the number of processes in the parameter file. Their relationships are as follows:
Sessions = (1.1 * process + 5)
However, when we increase the number of processes, the database often cannot be started. This is because a unix system parameter is missing: semmns is the core parameter, which is the semaphore parameter of the unix system. Each process occupies a semaphore. After the semmns is adjusted, You need to restart the unix operating system to make the parameter take effect. However, its size is subject to hardware memory or oracle sga. Range: 200--2000. However, the Processes modification should not only be adjustedInit <sid>. oraFile parameters, and OS kernel parameters should be adjusted, such as AIX, HPUX, Solaris, SCO, and UNIXWare. OS adjustments need to be restarted, this parameter cannot be set simply based on how many terminals are connected to this server. The most important thing is to consider how many concurrent sessions will be connected when using some middleware with shared connections, in general, it does not need to be too large). Of course, we need to consider some Oracle background processes, and some system maintenance work requires more connections. 1. When the database connection exceeds the limit, the new connection will fail. Check the number of allowed connections in the database. SQL> show parameter processesNAME TYPE
----------------------------------------------------------
VALUE
------------------------------
Aq_tm_processes integer
1
Db_writer_processes integer
1
Job_queue_processes integer
10
Log_archive_max_processes integer
2
Processes integerNAME TYPE
----------------------------------------------------------
VALUE
------------------------------
150# -------------- The number of database connections is 150, and the number of connections is modified.
SQL>
SQL> alter system set processes = 5000 scope = spfile; the system has been changed. SQL> alter system set sessions = 5000 scope = spfile; the system has been changed. SQL> shutdown immediate
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled. SQL> startup
ORA-00064: object is too large to allocate on this O/S)
SQL> startup
ORA-00064: object is too large to allocate on this O/S)# ----------------------------- Failed to modify the maximum number of connections. The maximum number of connections is related to OS,# ------------------------------- Modification may fail if the OS is the maximum number of connections.For example, the following Unix instance (derived from the http://blog.csdn.net/javagarden/article/details/2434486)

The following figure shows how to adjust the key parameters:SHMMAX 1000000000 SHMMIN 1 SHMMNI 200 SHMSEG 15 SEMMNI 1000 SEMMSL 300 SEMMNS 230 SEMOPM 20 among which semmni, semmns, semmsl should increase, at least 18 larger than processes; SEMMNI10, 10000; 150 ): specifies the number of signals recognized in the core. This is the number of unique signals that can be activated at any given time. The default value is 150. The maximum value is automatically adjusted by the system. SEMMSL25, 300; 150): specifies the maximum semaphore value for each signal recognition. The default value is 25. The sum of the PROCESSES of all the databases except the maximum db of SEMMNS + 2 * the PROCESSES of the maximum db + 10 * The number of instances. For example, if the number of processes in the three instances is 100, 100, and 200 respectively, then = 100 + 100) + 2*200 + 10*3 = 630SEMOPM10, 20; 10 ): specifies the maximum number of signal operations that can be performed in each system call semop. The default value is 10. SHMMAX131072, 1073741824; 524288): specifies the maximum size of the shared memory. Equal to 0.5 x number of physical memory bytes SHMMNI10, 1000; 100): specifies the maximum value of the shared memory identifier within the system range. SHMSEG6, 15; 6): specifies the number of shared memory blocks or identifiers associated with each process. The default value is 6. The maximum value of the shared memory block associated with each process is related to the unused space of the process. Therefore, although a process has a shared memory block less than the SHMSEG value, it may not be associated with other processes because of its limited space.Init. oraTo:Processes = 50 # SMALL# Processes = 100 # MEDIUM# Processes = 200 # LARGE

SQL> startup
--------------- Restart orcl in services. msc, resulting in wrong ORA-00064
ORA-12571: TNS: Package write failed
SQL> startup
ORA-24324: Service handle not initialized
ORA-01041: Internal error, hostdef extension does not exist
SQL> startup
ORA-24324: Service handle not initialized
ORA-01041: Internal error, hostdef extension does not exist
---------------------------------------------- 2. An error occurred while modifying the maximum number of connections. ORA-00064,The database cannot be started # -------------------- (1) oracle sys dba connection, because ORA-12571Error. Other users cannot connect
SQL> conn sys/oracle @ orcl as sysdba
Already connected to the idle routine. # ---------------------- (2) Start Database SQL> startup pfile = 'd: \ oracle \ admin \ orcl \ pfile \ init. ora.105201216493 'open
The ORACLE routine has been started. Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened. # ---------------------- (3) re-create the spfile, Which is started properly by oracle.
SQL> create spfile from pfile = 'd: \ oracle \ admin \ orcl \ pfile \ init. ora.105201216493 '; the file has been created. # ---------------------- Change the maximum number of connections to SQL> show parameter processes; NAME TYPE
----------------------------------------------------------
VALUE
------------------------------
Aq_tm_processes integer
1
Db_writer_processes integer
1
Job_queue_processes integer
10
Log_archive_max_processes integer
2
Processes integerNAME TYPE
----------------------------------------------------------
VALUE
------------------------------
150
SQL>
/* ---------------- 3. to modify the processes and sessions values, you must restart the oracle server to apply the number of ORACLE connections (sessions). The relationship between the number of processes in the parameter file is as follows: sessions = (1.1 * process + 5) ---------------------- */# -------------------- (4) restart the database to make the rebuilt SPFILE take effect. SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> startup
The ORACLE routine has been started. Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened. # ---------------------- (5) modify the maximum number of connections and spfile, and restart to take effect. If the connection takes effect, scope = both
SQL> alter system set processes = 300 scope = spfile; the system has been changed. SQL>
SQL> alter system set sessions = 335 scope = spfile; the system has been changed. SQL>
SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> startup
The ORACLE routine has been started. Total System Global Area 139533192 bytes
Fixed Size 453512 bytes
Variable Size 113246208 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened. # ---------------------- (6) view the maximum number of connections SQL> show parameter processesNAME TYPE
----------------------------------------------------------
VALUE
------------------------------
Aq_tm_processes integer
1
Db_writer_processes integer
1
Job_queue_processes integer
10
Log_archive_max_processes integer
2
Processes integerNAME TYPE
----------------------------------------------------------
VALUE
------------------------------
300
SQL> # ------------------ the number of database connections is 300 (2) how to view Oracle cursor usage select * from v $ open_cursor where user_name = 'cursor name'; (3) how to query Oracle sessions
Select * from v $ sessionselect username, sid, serial # from v $ session; to stop a connection
SQL> alter system kill session sid, serial #;
If this command does not work, find the number of processes
SQL> select pro. spid from v $ session ses, v $ process pro where ses. sid = 21 and ses. paddr = pro. addr; 4) check which users are using data: select osuser,. username, cpu_time/executions/1000000 |'s, SQL _fulltext, machinefrom v $ session a, v $ sqlarea bwhere. SQL _address = B. addressorder by cpu_time/executions desc; Note: one session of a UNIX user corresponds to an operating system process, while Windows is embodied in a thread. 5)

This article is from the "nest" blog, please be sure to keep this source http://cswggod.blog.51cto.com/4569505/1050978