Oracle Diagnostic Cases - SGA and SWAP

xiaoxiao2021-03-06 93

LINK:

http://www.eygle.com/case/sga1.htm

Case Description: User report, after a server starts a period of time, after a few minutes, after a few minutes, it cannot be connected to the system again.

1. Login system Sunos 5.8login: Rootpassword: Last Login: Tue Mar 23 13:51sun Microsystems Inc. Sunos 5.8 Generic Patch October 2001YOU HAVE NEW MAIL.2.SU Check the Oracle process for Oracle users Discover the background process is normal, there is a certain amount of user connection

wapplatform: /> su - oracleSun Microsystems Inc. SunOS 5.8 Generic Patch October 2001You have new mail./export/home1/oracle>lsadmin codesyndealt31 exp.sh local.cshrc local.profile oraclebak oui v6_databaseapp exp.log jre local.login nsmail oradata Swanexport / Home1 / Oracle> CD Admin / Export / Home1 / Oracle / Admin> PS - EF | GREP ORAOracle 25269 25258 0 13:58:36 PTS / 3 0:00 GREP ORAOracle 25257 24906 0 13:58:31 PTS / 4 0:00 Vi alert_hswapdb.logoracle 25267 1 1 13:58:34? 0:00 Oraclehswapdb (local = no) Oracle 25184 1 0 13:56:57? 0:00 ora_p007_hswapdboracle 25182 1 0 13:56:57? 0: 00 ora_p006_hswapdboracle 25193 1 0 13:57:03? 0:01 Oraclehswapdb (local = no) Oracle 25209 1 0 13:57:09? 0:00 Oraclehswapdb (local = no) Oracle 25176 1 0 13:56:57? 0 : 00 ORA_P003_HSWAPDBORACLE 25180 1 0 13:56:57? 0:00 ora_p005_hswapdboracle 25172 1 0 13:56:56? 0:00 ora_p001_hswapdboracle 25178 1 0 13:56:57? 0:00 ORA_P004_HSWAPDBOracle 25170 1 0 13:56:56 ? 0:00 ora_p000_hswapdboracle 24254 24240 0 12:08:25 PTS / 2 0:00 -kshoracle 2517 4 1 0 13:56:56? 0:00 ora_p002_hswapdboracle 25244 1 1 13:58:23? 0:00 oraclehswapdb (local = no) Oracle 25218 1 0 13:57:23? 0:00 OracleHswDb (local = no) Oracle 25159 1 0 13:56:42? 0:02 ora_QMN0_HSWAPDBORACLE 25230 1 0 13:57:40? 0:01 oraclehswapdb (local =

No) Oracle 25161 1 0 13:56:42? 0:00 ORA_S000_HSWAPDBORACLE 25149 1 0 13:56:41? 0:01 Ora_LGWR_HSWAPDBORACLE 25157 1 0 13:56:42? 0:00 Ora_CJQ0_HSWAPDBOracle 24906 3698 0 13:47:47 PTS / 4 0:00 -kshoracle 25153 1 0 13:56:42? 0:01 ora_smon_hswapdboracle 25058 7464 0 13:55:14 PTS / 1 0:00 -kshoracle 25163 1 0 13:56:42? 0:00 ora_d000_hswapdboracle 25155 1 0 13:56:42? 0:00 ora_reco_hswapdboracle 25151 1 0 13:56:41? 0:00 ora_ckpt_hswapdboracle 25145 1 0 13:56:41? 0:00 ora_dbw0_hswapdboracle 25199 1 15 13:57:04? 0: 49 ORA_J000_HSWAPDBORACLE 4149 4146 0 12:05:11 PTS / 5 0:00 -kshoracle 25232 1 0 13:57:41? 0:00 Oraclehswapdb (local = no) Oracle 25119 1 0 13:56:29? 0:00 OraclehswaPDB (Local = no) Oracle 25075 1 0 13:55:34? 0:00 / export / home1 / oracle / app / bin / tnslsnr listener -inheritoracle 24374 4149 0 12:21:56 PTS / 5 0:00 SQLPLUS / NOLOGOGOGoGoracle 25143 1 0 13:56:41? 0:00 Ora_PMON_HSWAPDBORACLE 25258 25242 0 13:58:31 PTS / 3 0:00 -ksh / export / home1 / oracle / admin>

PS-EF | grep ora_oracle 25275 25258 0 13:58:52 PTS / 3 0:00 grep ora_oracle 25184 1 0 13:56:57? 0:00 ora_p007_hswapdboracle 25182 1 0 13:56:57? 0:00 ora_p006_hswapdboracle 25176 1 0 13:56:57? 0:00 ora_p003_hswapdboracle 25180 1 0 13:56:57? 0:00 ora_p005_hswapdboracle 25172 1 0 13:56:56? 0:00 ora_p001_hswapdboracle 25178 1 0 13:56:57? 0:00 ORA_P004_HSWAPDBOracle 25170 1 0 13:56:56? 0:00 ora_p000_hswapdboracle 25174 1 0 13:56:56? 0:00 ora_p002_hswapdboracle 25159 1 0 13:56:42? 0:02 ora_QMN0_HSWAPDBORACLE 25161 1 0 13:56:42? 0: 00 ora_s000_hswapdboracle 25149 1 0 13:56:41? 0:01 Ora_LGWR_HSWAPDBORACLE 25157 1 0 13:56:42? 0:00 ora_cjq0_hswapdboracle 25153 1 0 13:56:42? 0:01 Ora_SMON_HSWAPDBOracle 25163 1 0 13:56:42? 0:00 Ora_D000_HSWAPDBOracle 25155 1 0 13:56:42? 0:00 ora_reco_hswapdboracle 25151 1 0 13:56:41? 0:00 ora_ckpt_hswapdboracle 25145 1 0 13:56:41? 0:00 ora_dbw0_hswapdboracle 25199 1 13 13:57: 04? 0:51 ora_j000_hswapdboracle 25143 1 0 13:56:41? 0:00 ora_pmon_hswapdb3. Check the alert.log alert log file

/ Export / home1 / oracle / admin> lshswapdb / export / home1 / oracle / admin> cd * / export / home1 / oracle / admin / hswapdb> lsbdump cdump create pfile udump / export / home1 / oracle / admin / hswapdb> cd bdump / export / home1 / oracle / admin / hswapdb / bdump>

/ export / home1 / oracle / admin / hswapdb / bdump> ls -l * .log

-rw-r - r - r - r - 1 Oracle DBA 813396 Mar 23 13:57 Alert_hswapdb.log / export / home1 / oracle / admin / hswapdb / bdump> vi * .log "alert_hswapdb.log" 18888 LINES, 813396 Characters (115 null) Tue Jun 24 21:17:14 2003Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 3Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.3.0.System parameters with non-default values : processes = 400timed_statistics = TRUEshared_pool_size = 117440512large_pool_size = 83886080java_pool_size = 33554432control_files = /export/home1/oracle/oradata/hswapdb/control01.ctl,/export/home1/oracle/oradata/hswapdb/control02.ctl,/export/home1/oracle/ oradata / hswapdb / control03.ctldb_block_size = 8192db_cache_size = 352321536compatible = 9.2.0.0.0db_file_multiblock_read_count = 16fast_start_mttr_target = 300undo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile = EXCLUSIVEdb_domain = eygle.cominstance_name = hswapdbdispatchers = (PROTOCOL = TCP) (SERVICE = hswapdbXDB) job_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = / export / home1 / oracle / admin / hswapdb / bdumpuser_dump_dest = / export / home1 / oracle / admin / hswapdb / udumpcore_dump_dest = / export / home1 / oracle / admin / hswapdb / cdumpsort_area_size = 524288db_name = hswapdbopen_cursors = 300star_transformation_enabled = FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = 154140672aq_tm_processes = 1

.................

Tue Mar 23 13:40:45 2004skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 11, op = fork, loc = skgpspawn5skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142 , depinfo = 12, op = fork, loc = skgpspawn3Tue Mar 23 13:42:02 2004skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork , LOC = Skgpspawn3Skgpspawn failed: category = 27142, Depinfo = 12, op = fork, LOC = SKGPSPAWN3SKGPSPAWN FAILED: CATEGORY = 27142, depin fo = 12, op = fork, loc = skgpspawn3Tue Mar 23 13:55:38 2004Starting ORACLE instance (normal) Shutting down instance: further logons disabledTue Mar 23 13:56:20 2004Shutting down instance (abort) License high water mark = 26Instance terminated by USER, pid = 25112Tue Mar 23 13:56:37 2004Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 3Using log_archive_dest parameter default valueLICENSE_MAX_USERS =

0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.3.0.System parameters with non-default values: processes = 400timed_statistics = TRUEshared_pool_size = 117440512large_pool_size = 83886080java_pool_size = 33554432control_files = /export/home1/oracle/oradata/hswapdb/control01.ctl, /export/home1/oracle/oradata/hswapdb/control02.ctl,/export/home1/oracle/oradata/hswapdb/control03.ctldb_block_size = 8192db_cache_size = 352321536compatible = 9.2.0.0.0db_file_multiblock_read_count = 16fast_start_mttr_target = 300undo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile = EXCLUSIVEdb_domain = eygle.cominstance_name = hswapdbdispatchers = (PROTOCOL = TCP) (SERVICE = hswapdbXDB) remote_dependencies_mode = SIGNATUREjob_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = / export / home1 / oracle / admin / hswapdb / bdumpuser_dump_dest = / export / home1 / oracle / admin / hswapdb / UDumpcore_dump_dest = / export / home1 / oracle / admin / hswapdb / cdumpsort_area_size = 524288db_name = hswapdbopen_cursors = 300star_transformation_enabled = FALSEparallel_automatic_tuning = TRUEquery_rewrite_enabled = FALSEpga_aggregate_target = 154140672aq_tm_processes = 1PMON started with pid = 2DBW0 started with pid = 3LGWR started with pid = 4CKPT started with pid = 5SMON started with pid = 6RECO started with pid = 7CJQ0 started with pid = 8QMN0 Started with pid = 9tue mar 23 13:56:42 2004Starting Up 1 Shared Server (s) ... Tue Mar 23 13:56:42 2004Starting Up 1 dispatcher (s) for network address' (address = (Partial = YES) (Protocol = tcp)) '

... Tue Mar 23 13:56:43 2004alTer Database Mounttue Mar 23 13:56:47 2004Successful Mount Of Redo Thread 1 Database MountTue Mar 23 13:56:47 2004current log # 2 SEQ # 2136 MEM # 0: /EXPORT/HOME1/Oracle/Ordata/hswapdb/redo02.logsuccessful Open of Redo Thread 1.Tue Mar 23 12:24:54 2004SMON: enabling cache recoveryTue Mar 23 12:24:56 2004Undo Segment 1 OnlinedUndo Segment 2 OnlinedUndo Segment 3 OnlinedUndo Segment 4 OnlinedUndo Segment 5 OnlinedUndo Segment 6 OnlinedUndo Segment 7 OnlinedUndo Segment 8 OnlinedUndo Segment 9 OnlinedUndo Segment 10 OnlinedSuccessfully onlined Undo Tablespace 1.Tue Mar 23 12 : 24: 56 2004SMON: enabling tx recoveryTue Mar 23 12:24:56 2004Database Characterset is ZHS16GBKTue Mar 23 12:25:01 2004SMON: Parallel transaction recovery triedTue Mar 23 12:25:01 2004replication_dependency_tracking turned off (no async multimaster replication found) Completed: Alter Database OpenTue Mar 23 12:28:26 2004 / * Oracle '/Export/Home1/oracle/oradata/hswapdb/users01.dbf' Resize 2501760ktue Mar 23 12:28:26 2004 Mar 23 12:28:26 2004 Mar- 3297 Signalled Database: / * Oracleoem * / Alter Database datafile '/export/h...tue mar 23 12:28:32 2004 / * Oracleoem * / Alter Database datafile' /export/home1/oracle/roadata/hswapdb/users01.dbf 'resize 2501760kora- 3297 Signalled Database: / * Oracleoem * / Alter Database DataFile '/Export/h...tue mar 23 12:28:53 2004 / * Oracleoem * / alter Database datafile' / export / home1 / oracle / oraData / hswapdb / users01 .dbf 'resize 3501760ktue mar 23 12:28:53 2004 Mar-3297 Signalled During: / * Oracleoem * / Alter Database DataFile'

/export/h...Tue Mar 23 13:40:45 2004skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 11, op = fork, loc = skgpspawn5skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3Tue Mar 23 13:42:02 2004skgpspawn failed: category = 27142, depinfo = 12, op = fork, loc = skgpspawn3: q times discovery database Sub-weight and record some error messages This prompt indicates that the database cannot spawn a new session.

Quote Yong Huang's Comment:

The number in "skgpspawn failed: category = 27142" is probably ORA error: $ oerr ora 2714227142, 0000, "could not create new process" // * Cause: OS system call // * Action: check errno and if possible increase the Number of ProcesSosSd (OS-Dependent) Errors Are Almost Always Shown As an Skg ... Error (Probably Means "System, kernel generic").

I don't know what "depinfo = 12" means.

4. Try to connect to the database to receive an error message, unable to connect the database

$ SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.3.0 - Production ON March 23 14:14:06 2004

Error: ORA-12540: TNS: Benefits Internal Limit

Please enter your username: Error: ORA-12540: TNS: Beyond internal restrictions, please enter your username: Error: ORA-12540: TNS: Beyond internal limit

SP2-0157: Cannot connect to Oracle after 3 trials, exit SQL * Plus

Internal restrictions exceeded, usually indicated that certain system resources are insufficient.

5. Check the listener

Discovery partial connection is rejected

/ export / home1 / oracle> LSNRCTL SERVICES

LSNRCTL for Solaris: Version 9.2.0.3.0 - Production on 23- March-2004 14:37:23

Connecting to (deScription = (address = (protocol = IPC)) service summary .. Service "PLSextProc" contains 1 routine. Routines "PLSextProc", state unknown, 1 handler containing this service ... Handle: "Dedicated" has been established: 0 has been rejected: 0Local Server service "hswapdb.eygle.com" contains 2 routines. Routines "HSWAPDB", status unknown, including 1 handler for this service ... Handle: "Dedicated" has been established: 6 has been rejected: 0Local Server routine "hswapdb", status Ready, containing this service 1 Processor ... Handle: "Dedicated" has been established: 21 Rejected: 6 Status: ReadyLocal Server Service "HSwapdbXDb.eygle.com" contains 1 routine. Routines "HSWAPDB", status ready, 1 handler containing this service ... Handle: "d000" has been established: 0 has been rejected: 0 Current: 0 Maximum: 972 Status: ReadyDispatcher (Address = (protocol = tcp) (port = wapplatform) (port = 32869)) command execution success

Find relevant error messages in Listener.log

23-3 / 324/302 -2004 12:19:40 * (Connect_Data = (SID = HSWAPDB) (CID = (Program = C: /Winnt/MICROFT.NET/Framework/v1.1.4322/ASPNET_WP.EXE) (Host = SWAN) (user = system))) * (address = (protocol = TCP) (Host = 172.16.30.125) (port = 1291)) * Establish * HSWAPDB * 12500TNS-12500: TNS / 243/272/274/340 / 314/375/306/367/334/306/364/266/257/327/250/323/303/265/304/267/376/316/361/306/367/275 / 370/263 / 314TNS-12540: TNS / 243/272/363/254/263/366/304/332/262/277/274/253/317/336/317/336/326 / 306TNS-12560: TNS : / 320/255/312/351/312/312/305/344/306/367/364/355/316 / 363TNS-00510: / 363/254/263/363/304/332/2/2/304/332/262/277/274 / 253/317/336/317/336/326 / 306Solaris error: 12: NOT ENOUGH SPACE23-3 / 324/302 -2004 12:19:50 * (connect_data = (SID = HSWAPDB) (CID = (Program = C) : / Program files / plsql development / plsqldev.exe) (user = administrator))) * (address = (protocol = tcp) (host = 172.16.30.125) (port = 1292)) * Establish * hswapdb * 12500TNS-12500: TNS / 243/272/274/340/314/375/304/367/334/304/304/334/304/364/266/257/347/250/323/303/265/304 / 267/376 / 316/361/306/363 / 314TNS-12540: TNS / 243/272/263/254/273/366/304/332/262/277/332/253/317/336/253/317/336/317 / 336/326 / 306TNS-12560: TNS: / 320/255/312/351/312/312/305/344/306/367/264/355/316 / 363Tns-00510: / 363TNS-00510: / 363TNS-00510: / 363/254/263/366 / 304/332/262/277/274/253/317/336/317/336/336/317/336/326 / 306Solaris error: 12: NOT Error Space / Export / Home1 / Oracle / App / Network / Log> GREP -W 12 / USR /include/sys/errno.h#define enomem 12 / * Not Enough Corequote Yong Huang's Comment:

$ grep -w 12 /usr/include/sys/errno.h#define enoad * / here "Core" Means Memory, Including REAL RAM MEMORY AND SWAP SPACE.

6. Exit Oracle User Check Check System Log Information and find that a large number of failures have insufficient reports in SWAP.

/ export / home1 / oracle / admin / hswapdb / bdump> EXITWAPPLATFORM: /> DMESG

March 23, 2004 14:00 32:36 Wapplatform Elfexec: [ID 700856 Kern.notice] PS: Cannot Find ^? ELF ^ A ^ b ^ Amar 22 22:53:00 Wapplatform UFS: [ID 845546 kern.notice] NOTICE: ALLOC: / EXPORT / HOME1: File System Fullmar 22 22:53:09 Wapplatform Elfexec: [ID 700856 Kern.notice] W: Cannot Find ^? ELF ^ A ^ b ^ AMAR 22 22:53:53 Wapplatform Last Message Repeated 4 Timesmar 22 22:56:28 Wapplatform Elfexec: [ID 700856 Kern.notice] ipnat: Cannot Find ^? ELF ^ B ^ b ^ Amar 22 22:58:00 Wapplatform UFS: [ID 845546 kern.notice] NOTICE: ALLOC: / EXPORT / HOME1: File System Fullmar 22 22:59:54 Wapplatform Elfexec: [ID 700856 Kern.notice] ipnat: cannot find ^? ELF ^ b ^ b ^ Amar 22 23 : 02: 26 Wapplatform uform: [ID 845546 Kern.notice] NOME1: File System Fullmar 22 23:03:00 Wapplatform Last Message Repeated 1 Timemar 22 23:08:00 Wapplatform Ufs: [ID 845546 Kern.notice] NOTICE: Alloc: / Export / Home1: File System Fullmar 22 23:08:34 Wapplatform Elfexec: [ID 700856 Kern.notice] ipnat: cannot find ^? ELF ^ B ^ B ^ Amar 22 23:10:27 Wapplatform Last Message Repeated 3 Timesmar 22 23:11:49 Wapplatform Elfexec: [ID 700856 Kern.notice] ipnat: Cannot Find ^?

ELF ^ b ^ b ^ Amar 22 23:11:52 Wapplater 22 23:13:01 Wapplater 22 23:13:01 Wapplater 22 23:13:01 Wapplater 22 23:13:01 Wapplater 22 23:13:01 Wapplatform Uf: [ID 845546 Kern.notice] NOTSTEOC: / EXPORT / HOME1: File System Fullmar 22 23: 18:01 wapplatform last message repeated 1 timeMar 22 23:23:01 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: / export / home1: file system fullMar 22 23:28:01 wapplatform last message repeated 1 timeMar 22 23:33:01 Wapplatform Ufs: [ID 845546 Kern.notice] NOTICE: Alloc: / Export / Home1: File System Fullmar 22 23:38:01 Wapplater 22 23:38:01 Wapplater 22 23:38:01 Wapplater 22 23:38:01 Wapplater 22 23:38:01 Wapplater 22 23:38:43:01 Wapplatform uform UFS: [ID 845546 Kern.notice] NOME1: File System Fullmar 22 23:48:01 Wapplatform Last Message Repeated 1 Timemar 22 23:53:01 Wapplatform Ufs: [ID 845546 Kern.notice] NOTICE: ALLOC: / export / home1: file system fullMar 22 23:58:01 wapplatform last message repeated 1 timeMar 23 00:00:00 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg / export / home1: file system fullMar 23 00:00 : 00 Wapplatform Sendma il [3075]: [ID 702911 mail.crit] My unqualified host name (wapplatform) unknown; sleepingfor retryMar 23 00:01:00 wapplatform sendmail [3075]: [ID 702911 mail.alert] unable to qualify my own domain name ( Wapplatform) -

using short nameMar 23 00:02:36 wapplatform ufs: [ID 845546 kern.notice] NOTICE: alloc: / export / home1: file system fullMar 23 00:03:02 wapplatform last message repeated 1 timeMar 23 00:08:02 wapplatform UFS: [ID 845546 kern.notice] NOTICE: Alloc: / Export / Home1: File System Full ....

Mar 23 10:18:15 Wapplatform uform: [ID 845546 kern.notice] NOTICE: Alloc: / export / home1: file system Fullmar 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 Wapplater 23 10:20:41 WAPPLATMAR 23 / home1: File System Fullmar 23 10:20:47 Wapplamed 1 Timemar 23 10:23:15 Wapplatform uform: [ID 845546 Kern.notice] NOTICE: Alloc: / Export / Home1: File System Fullmar 23 10:24 : 38 wapplatform ufs: [ID 213553 kern.notice] NOTICE: realloccg / export / home1: file system fullMar 23 10:24:43 wapplatform last message repeated 1 timeMar 23 10:24:55 wapplatform ufs: [ID 213553 kern.notice ] NOTICE: realloccg / export / home1: file system fullMar 23 10:25:06 wapplatform last message repeated 2 timesMar 23 11:09:31 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack For PID 3118 (SU) Mar 23 11:09:39 Wapplaterform Genunix: [ID 47050 Kern.warning] Warning: Sorry, No Swap Space to Grow Stack for PI 3121 (SU) Mar 23 11:10:48 Wapplatform Genunix: [ ID 470503 kern.warning] Warning: Sorry, No Swap Space to Grow Stack for Pid 3137 (SU) Mar 23 11:18:02 Wapplatform Sshd [3620]: [IRROR] ERROR: GRANTPT: NOT ENOUGH SPAPPLATMARM 23 11:18:02 Wapplatform SSHD [3620]: [ID 800047 Auth.Error] error: session_pty_req: session 0 alloc failedmar 23 11:18:43 Wapplatform genunix: [ID 470503 kern.warning] Warning: Sorry, No Swap space to get stack for pid 3636 ( SU) Mar 23 11:19:47 Wapplatform Genunix: [ID 470503 kern.warning] Warning: Sorry, No Swap Space to Grow Stack for PID 3672 (SU) Mar 23 11:20:20 Wapplatform Genunix: [ID 470503 Kern. Warning] WARNING: Sorry, No Swap Space to Grow Stack for Pid 3694 (SU) Mar 23 11:22:23 Wapplatform Genunix: [ID 470503 Kern.Warning] WARNING:

SpoM23 11:23:40 wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow stack for pid 3804 (su) Mar 23 11:23:40 wapplatform last message repeated 8 timesMar 23 11:23:56 wapplatform genunix: [ID 470503 Kern.warning] Warning: Sorry, No Swap Space to Grow Stack for PID 3806 (PS) Mar 23 11:23:56 Wapplatform Last Message Repeated 12 Timesmar 23 11:24:01 Wapplatform Genunix: [ID 470503 kern.warning] Warning: Sorry, No Swap Space To Grow Stack for Pid 3808 (W) Mar 23 11:24:01 Wapplatform Last Message Repeated 8 Timesmar 23 13:40:56 Wapplatform Su: [ID 810491 Auth.crit] 'su root' failed For root on / dev / pts / 2mar 23 13:46:26 Wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, No Swap Space to get stack for pid 24888 (sqlplus) mar 23 13:49:18 Wapplaterm Su : [ID 810491 Auth.crit] 'Su Oracle' F Ailed for root on / dev / pts / 6mar 23 13:54:03 Wapplatform genunix: [ID 470503 kern.warning] WARNING: Sorry, No Swap Space to Grow Stack for Pid 25035 (SU) Mar 23 13:54:08 Wapplatform Genunix: [ID 470503 kern.warning] WARNING: Sorry, No Swap Space to Grow Stack for PID 25036 (SU)

It is now basically possible to determine the problem of the switching area, of course, and the Oracle SGA settings are related.

7. Check the system memory and switching area

/ export / home1 / oracle / admin / hswapdb / bdump> exit

Wapplatform: /> dmesg

It is now basically possible to determine the problem of the switch area, of course, and the Oracle SGA settings are related to. 7. Check the system memory and switching area

$ TOP

Last Pid: 25456; Load Averages: 0.67, 0.70, 0.69

14:10:03

93 Processes: 91 Sleeping, 2 on CPU

CPU States: 72.7% iDle, 14.9% User, 2.7% Kernel, 9.7% iowait, 0.0% SWAP

Memory: 1024m REAL, 34M Free, 752M SWAP in Use, 10M Swap Free

Pid Username THR PRI Nice Size Res State Time CPU Command

25199 Oracle 1 40 0 674M 631M CPU / 2 8:03 16.32% Oracle

25209 Oracle 1 30 0 675M 630M Sleep 0:03 0.13% Oracle25159 Oracle 1 48 0 674M 628M Sleep 0:03 0.06% Oracle

25384 Oracle 1 58 0 2632K 1736K CPU / 0 0:01 0.05% Top

25145 Oracle 143 58 0 682M 630M Sleep 0:01 0.03% Oracle

25446 Oracle 1 58 0 674M 628M SLEEP 0:00 0.03% Oracle

25149 Oracle 15 58 0 682M 626M SLEEP 0:00 0.02% Oracle

25075 Oracle 1 48 0 17M 7208K Sleep 0:00 0.01% TNSLSNR

25151 Oracle 11 58 0 676M 624M Sleep 0:00 0.01% Oracle

25366 Oracle 1 10 0 674M 628M SLEEP 0:00 0.00% Oracle

25356 Oracle 1 18 0 674M 628M SLEEP 0:00 0.00% Oracle

25360 Oracle 1 20 0 674M 628M SLEEP 0:00 0.00% Oracle

25364 Oracle 1 20 0 674M 628M SLEEP 0:00 0.00% Oracle

25362 Oracle 1 20 0 674M 628M SLEEP 0:00 0.00% Oracle

25330 Oracle 1 28 0 674M 628M Sleep 0:00 0.00% Oracle

It has found that the physical memory is only 1G, the free portion is 34m, the switch area uses 752M, only 10M Free system is less insufficient, the SWAP area is not enough

8. Check the SGA settings of the database found SGA settings to: 622299344 BYtes close to 600M

Wapplatform: /> su - Oraclesun Microsystems Inc. Sunos 5.8 Generic Patch October 2001YOU Have New Mail./Export/Home1/oracleSqlplus "/ as sysdba"

SQL * Plus: Release 9.2.0.3.0 - Production ON March 23 14:02:30 2004

Connect to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit ProductionWith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.3.0 - Production

SQL> SHOW SGA

Total System Global Area 622299344 Bytesfixed Size 731344 Bytesvariable Size 268435456 Bytesdatabase Buffers 352321536 BYTESSSQL>

For systems for RAM less than 1G, in Dedicated mode, Oracle's SGA generally should not exceed 1/2 physical memory.

9. The first step is adjusted to reduce SGA, keep sufficient memory for the system. 10. Increase SWAP area

Wapplatform: /> DF -K file system kiloby with available capacity hooks / dev / dsk / c0t1d0 3099093 105421 2931691 4% // dev / dsk / c0t2d0 248625760 8359637 1862866 82% / usr / proc 0 0 0 0 0 0 0 0 % / procfd 0 0 0 0 0 0% / etc / mnTtab / DEV / DSK / C0T1D0S3 1018382 285914 671366 30% / Varswap 3904 24 3880 1% / var / Runswap 3936 56 3880 2% / TMP / dev / dsk / c0t1d0s5 1671823 459202 1162467 29% / opt / dev / dsk / c0t2d0s7 7087473 6068462 948137 87% / export / home / dev / dsk / c2t1d0s7 17413250 15900222 1338896 93% / export / home2 / dev / dsk / c0t3d0s7 17413250 13749782 3489336 80% / export / home1 / dev / dsk / c0t1d0s1 771110 382410 334723 54% / usr / openwin / export / home / wapgw / luke7087473 6068462 948137 87% / home / wapwapplatform: / var / swap> cd / export / home1wapplatform : / export / home1> lsTT_DB lost found oracle oracli9wapplatform: / export / home1> mkdir swapwapplatform: / export / home1> cd swapwapplatform: / export / home1 / swap> mkfile -v 1g swapfile1swapfile1 1073741824 byteswapplatform: / export / home1 / swap > iDUID = 0 (root) gid = 1 (Other) Wapplatform: / export / home1 / swap> swap -a / export / home1 / swap / swapfile1wapplatform: / export / home1 / swap> SWAP -S total: Assign 623160k bytes reserved 162704K = 785864K, 1010936K available

11. The connection test system returns to normal, problem solving

Wapplatform: / Export / Home1 / Swap> Su - Oraclesun Microsystems Inc. Sunos 5.8 Generic Patch October 2001YOU Have New Mail./EXPORT/HOME1/OrCleSqlplus "/ as sysdba"

SQL * Plus: Release 9.2.0.3.0 - Production on Thursday March 25 11:56:28 2004

Connect to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit ProductionWith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.3.0 - Production

SQL> exit 9.2.0.3.0 From Oracle9i Enterprise Edition Release - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - Production disconnect / export / home1 / oracle> toplast pid: 5372; load averages : 0.25, 0.22, 0.29

11: 57: 58148 Processes: 137 Sleeping, 9 Zombie, 2 on CPucpu States: 98.8% iDLE, 0.2% User, 0.7% Kernel, 0.2% iowait, 0.0% SwapMemory: 1024m REAL, 17M Free, 824M SWAP in Use, 934M SWAP FREE

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND5363 root 1 58 0 2680K 1736K sleep 0:00 0.24% top5370 oracle 1 58 0 514M 469M sleep 0:00 0.18% oracle5366 oracle 1 28 0 514M 469M sleep 0:00 0.11% oracle5341 Oracle 1 58 0 2680K 1736K CPU / 2 0:00 0.10% TOP5372 Oracle 1 48 0 61M 3288K CPU / 3 0:00 0.06% Oracle1288 Oracle 1 48 0 514M 468M Sleep 5:33 0.05% Oracle607 Root 12 48 0 2768k 2312K Sleep 1:48 0.03% MIBIISA25075 ORACLE 1 48 0 17M 7208K Sleep 0:16 0.02% TNSLSNR1278 Oracle 15 58 0 522M 466M Sleep 0:49 0.02% Oracle374 Root 11 53 0 3504K 2888K Sleep 0:16 0.01% NSCD1280 Oracle 19 58 0 518M 466M Sleep 0:28 0.00% Oracle5361 Root 1 46 0 1024K 680k Sleep 0:00 0.00% Sleep5362 Root 1 46 0 1024K 680K Sleep 0:00 0.00% Sleep5469 Root 1 36 0 1952K 1176K Sleep 30:09 0.00% Monithttp4167 Oracle 1 40 0 515m 471M Sleep 29:38 0.00% Oracle

Question Summary: The resolution of Oracle Database Problem This is not allowed to diagnose and solve the problem by means of operating system levels. The recommended value of the SWAP area is 2XRAM if RAM is large, not necessarily Do not set SWAP to 2xSwap but usually set at least SWAP = RAM If the SWAP area is too small, a large number of exchanges can't be changed to disk during system busy, there is a problem. If this case is like this. In addition, if the system RAM is small, SGA <1/2 RAM is set to reserve sufficient memory space for Server Process and OS.

转载请注明原文地址:https://www.9cbs.com/read-123688.html

9cbs

New Post(0)