Shulou(Shulou.com)06/02 Report--

Today, in the process of creating a physical DataGuard, after the main library adjusts the parameters to start the database, it encounters an ORA-01075 error when connecting to the database, resulting in the database cannot be logged in and the follow-up work cannot be carried out.

This problem is not limited to the DataGuard configuration scenario. Briefly troubleshoot this error for reference.

1. Problem phenomenon

1) error report during login

[oracle@secdb1 ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0-Production on Sun Jul 24 20:01:10 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:

ORA-01075: you are currently logged on

2) even if the login is successful, you will still receive an error when executing the SQL command.

Sys@secdb > select * from dual

Select * from dual

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-00018: maximum number of sessions exceeded

two。 Analysis of problems

The problem cannot be located simply from the ORA-01075 error message itself.

The "ORA-00018: maximum number of sessions exceeded" error message was found in the alert log. After careful examination, it is found that there are a large number of archiving processes in the background at this time, and the processes=50 in the database is due to the error caused by exceeding the session limit.

The process information of the database backend is as follows:

[oracle@secdb1 ~] $ps-ef | grep secdb | grep-v grep

Oracle 10023 10 19:13? 00:00:00 ora_pmon_secdb

Oracle 10025 10 19:13? 00:00:00 ora_psp0_secdb

Oracle 10027 10 19:13? 00:00:00 ora_mman_secdb

Oracle 10029 10 19:13? 00:00:00 ora_dbw0_secdb

Oracle 10031 10 19:13? 00:00:00 ora_lgwr_secdb

Oracle 10033 10 19:13? 00:00:00 ora_ckpt_secdb

Oracle 10035 10 19:13? 00:00:00 ora_smon_secdb

Oracle 10037 10 19:13? 00:00:00 ora_reco_secdb

Oracle 10039 10 19:13? 00:00:00 ora_cjq0_secdb

Oracle 10041 10 19:13? 00:00:00 ora_mmon_secdb

Oracle 10043 10 19:13? 00:00:00 ora_mmnl_secdb

Oracle 10063 10 19:13? 00:00:00 ora_p000_secdb

Oracle 10065 10 19:13? 00:00:00 ora_p001_secdb

Oracle 10067 10 19:13? 00:00:00 ora_p002_secdb

Oracle 10069 10 19:13? 00:00:00 ora_p003_secdb

Oracle 10071 10 19:13? 00:00:00 ora_p004_secdb

Oracle 10077 10 19:13? 00:00:00 ora_arc0_secdb

Oracle 10079 10 19:13? 00:00:00 ora_arc1_secdb

Oracle 10081 10 19:13? 00:00:00 ora_arc2_secdb

Oracle 10083 10 19:13? 00:00:00 ora_arc3_secdb

Oracle 10085 10 19:13? 00:00:00 ora_arc4_secdb

Oracle 10087 10 19:13? 00:00:00 ora_arc5_secdb

Oracle 10089 10 19:13? 00:00:00 ora_arc6_secdb

Oracle 10091 10 19:13? 00:00:00 ora_arc7_secdb

Oracle 10093 10 19:13? 00:00:00 ora_arc8_secdb

Oracle 10095 10 19:13? 00:00:00 ora_arc9_secdb

Oracle 10100 10 19:13? 00:00:00 ora_arca_secdb

Oracle 10103 10 19:13? 00:00:00 ora_arcb_secdb

Oracle 10105 10 19:13? 00:00:00 ora_arcc_secdb

Oracle 10107 10 19:13? 00:00:00 ora_arcd_secdb

Oracle 10109 10 19:13? 00:00:00 ora_arce_secdb

Oracle 10111 10 19:13? 00:00:00 ora_arcf_secdb

Oracle 10113 10 19:13? 00:00:00 ora_arcg_secdb

Oracle 10115 10 19:13? 00:00:00 ora_arch_secdb

Oracle 10117 10 19:13? 00:00:00 ora_arci_secdb

Oracle 10119 10 19:13? 00:00:00 ora_arcj_secdb

Oracle 10121 10 19:13? 00:00:00 ora_arck_secdb

Oracle 10123 10 19:13? 00:00:00 ora_arcl_secdb

Oracle 10125 10 19:13? 00:00:00 ora_arcm_secdb

Oracle 10127 10 19:13? 00:00:00 ora_arcn_secdb

Oracle 10132 10 19:13? 00:00:00 ora_arco_secdb

Oracle 10135 10 19:13? 00:00:00 ora_arcp_secdb

Oracle 10137 10 19:13? 00:00:00 ora_arcq_secdb

Oracle 10139 10 19:13? 00:00:00 ora_arcr_secdb

Oracle 10141 10 19:13? 00:00:00 ora_arcs_secdb

Oracle 10143 10 19:13? 00:00:00 ora_arct_secdb

Oracle 10173 1 29 19:13? 00:00:26 ora_qmnc_secdb

Oracle 10229 10 19:14? 00:00:00 ora_q000_secdb

Oracle 10296 10294 0 19:14-00:00:00 oraclesecdb (DESCRIPTION= (LOCAL=YES) (ADDRESS= (PROTOCOL=beq)

It can be seen that a lot of archiving processes are started in the background, and it is these processes that occupy all 50 session, causing the system to be unable to log in.

3. Problem handling

1) stop the database

Since it is no longer possible to log in to the database, it can only be terminated by using operating system commands.

$ps-ef | grep $ORACLE_SID | grep-v grep | awk'{print $2}'| xargs kill-9

$ipcs-m | grep oracle | awk'{print $2}'| xargs ipcrm shm

Serious warning: the above command is forbidden to try on any database server!

For an explanation of the above two commands, please refer to "[Kill] two Linux commands to completely kill Oracle" (http://space.itpub.net/519536/viewspace-619787)

2) modify the system parameter processes to 500

3) restart the database

The problem is solved.

4. Summary

The problem encountered here is quite coincidental. There are more than 50 session in the backend. After I have adjusted the processes to 500, the number of session in the background is still stable at around 50.

This case tells us: before the project implementation process, we must carefully consider and consider each parameter, do not artificially add difficulties to yourself.

Good luck.

Secooler

10.07.24

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.