Maximum length of command line sql script is 2499 characters.
Sometimes execute SQL scripts from SQL*PLUS, then get below error:
SP2-0027: Input is too long (> 2499 characters) - line ignored
A couple of ways to monitor DataPump job
SQL>select JOB_NAME,OPERATION,JOB_MODE,DEGREE,
ATTACHED_SESSIONS,STATE
from dba_datapump_jobs
JOB_NAME OPERATION JOB_MODE DEGREE ATTAC..TIONS STATE
-------------------- --------- -------- ------ ------------ ------
SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA 4 1 EXECUTING
...
..
.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": directory= ..... PARALLEL=4
SQL> select inst_id, username,target_desc,sofar,
totalwork,TIME_REMAINING
from gV$SESSION_LONGOPS
where TIME_REMAINING>1000;
INST_ID USERNAME TARGET_DESC SOFAR TOTALWORK TIME_REMAINING
------- -------- ----------- ------- ----------- --------------
4 SYS 248779 4294967296 28121732
3 SYS 467021 4294967296 15108237
3 SYS EXPORT 8824 21597 2335
2 SYS 313184 4294967296 22351992
$ expdp ATTACH=SYS_EXPORT_SCHEMA_01 Export> help ... .. . ------------------------------------------------------------------------------ HELP Summarize interactive commands. KILL_JOB Detach and delete job. PARALLEL Change the number of active workers for current job. REUSE_DUMPFILES Overwrite destination dump file if it exists [NO]. START_JOB Start or resume current job. Valid keyword values are: SKIP_CURRENT. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STOP_JOB Orderly shutdown of job execution and exits the client. Valid keyword values are: IMMEDIATE. $status ... .. .
Export> KILL_JOB Are you sure you wish to stop this job ([yes]/no): yes
Configuration file CDXPatch.ini should only include patch IDs to be applied.
Trying to apply the following two patches onto ORMB database, got the error.
08/08/2017 11:52 AM <DIR> 20986229
02/04/2016 04:11 AM <DIR> 22329775
Directory of D:\PATCHES\V4.3.0.1.0-25963443_MultiPlatform\FW.V4.3.0.1.0-25963443\prereqpatches\RollupPack\FW\Database 08/08/2017 11:52 AM <DIR> . 08/08/2017 11:52 AM <DIR> .. 08/08/2017 11:52 AM <DIR> 20986229 02/04/2016 04:11 AM <DIR> 22329775 08/08/2017 11:52 AM 63 CDXPatch.ini 17/05/2016 08:33 PM 1,117 IgDbErr 17/05/2016 08:33 PM 5,115 ouafDatabasePatch.cmd 17/05/2016 08:33 PM 3,846 ouafDatabasePatch.sh 4 File(s) 10,141 bytes 4 Dir(s) 26,984,898,560 bytes free D:\PATCHES\V4.3.0.1.0-25963443_MultiPlatform\FW.V4.3.0.1.0-25963443\prereqpatches\RollupPack\FW\Database>ouafDatabasePatch.cmd "CMDLINE::: d:\java\bin\java.exe -cp D:\dbpatch_tools\lib\*;D:\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch " Folder for 22644310 is missing File 22644310\sfix.ini is missing File 22644310\CDXPatch.lang is missing Folder for 21133947 is missing File 21133947\sfix.ini is missing File 21133947\CDXPatch.lang is missing Folder for 22244788 is missing File 22244788\sfix.ini is missing File 22244788\CDXPatch.lang is missing Folder for 23201172 is missing File 23201172\sfix.ini is missing File 23201172\CDXPatch.lang is missing Folder for 23194604 is missing File 23194604\sfix.ini is missing File 23194604\CDXPatch.lang is missing Unable to load Sfix.ini file: java.io.FileNotFoundException: 22644310\sfix.ini (The system cannot find the path specified)
Configuration file CDXPatch.ini includes other patch ids:
20986229 22329775 21133947 22244788 22644310 23194604 23201172
Edit configuration file CDXPatch.ini to include the right patch ids which will be applied onto ORMB database.
20986229
22329775
... .. . Applying 22329775 ... Writing to log file: ORMB_DEV001\log22329775.log ----------------------------------------------------------- --Applying patch 22329775 at 08-09-2017 14:29:10using $LastChangedRevision: 42479 $ --------------------------------------------------------------- --Copying language information ----------------------------------------------------------- --Patch 22329775 applied successfully at 08-09-2017 14:29:11 Patch applied successfully..
Java layer deadlock —“Dead Lock detected!!”, bounce the agent, and then everything is working fine.
The following alerts are received from racnode1 -“The agent is overloaded [current requests: 128]”
From: oracle Sent: Friday, 4 August 2017 7:07 PM Cc: Subject: EM Event: Warning: racnode1 - Agent Unreachable (REASON = The agent is overloaded [current requests: 128]). Host is reachable. ... .. . Categories=Availability Message=Agent Unreachable (REASON = The agent is overloaded [current requests: 128]). Host is reachable. Severity=Warning Event reported time=Aug 4, 2017 7:06:27 PM AEST ... .. .
Agent is running
Agent upload is not working
Agent reload is not working
OMS heartbeat is not working
$ emctl status agent Oracle Enterprise Manager Cloud Control 12c Release 5 Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved. --------------------------------------------------------------- Agent Version : 12.1.0.5.0 OMS Version : 13.2.0.0.0 Protocol Version : 12.1.0.1.0 .. . Last Reload : 2017-08-04 11:28:59 Last successful upload : 2017-08-04 14:51:03 <--- 5 hours ago Last attempted upload : 2017-08-04 14:51:03 .. . Last attempted heartbeat to OMS : 2017-08-04 14:50:23 Last successful heartbeat to OMS : 2017-08-04 14:50:23 Next scheduled heartbeat to OMS : 2017-08-04 14:51:23
$ emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:The agent is overloaded [current requests: 128]
$ emctl reload agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD reload error:The agent is overloaded [current requests: 128]
Java layer deadlock —“Dead Lock detected!!”
2017-08-04 19:28:59,071 [43:GCThread-13] ERROR -
Dead Lock detected!!
Participating threads:Thread Info Dump:
=================
"HTTP Listener-3592 - /emd/main/ (~Task-free~ OMS.pbs@16398@omsnode=>[150183756670001])" tid=3592 WAITING
> Accumulated wait time (msec): 1372208 (1 times)
"HTTP Listener-2141 - /emd/main/ (~Task-free~ OMS.pbs@13103@omsnode=>[150182243190001])" tid=2141 BLOCKED
> Accumulated wait time (msec): 11036289 (76 times)
> Accumulated blocked time (msec): 16506994 (4 times)
"oracle.dfw.impl.incident.DiagnosticsDataExtractorImpl - Incident Dump Executor (created: Fri Aug 04 14:51:06 EST 2017)" tid=3088 BLOCKED
> Accumulated blocked time (msec): 16672145 (7 times)
"HTTP Listener-1022 - /emd/main/ (~Task-free~ OMS.pbs@16398@omsnode=>[150181021899001])" tid=1022 WAITING
> Accumulated wait time (msec): 28746227 (37 times)
> Accumulated blocked time (msec): 133 (12 times)
"HTTP Listener-1078 - /emd/main/ (DispatchRequests OMS.console@16398@omsnode=>[150181015881006])" tid=1078 WAITING
> Accumulated wait time (msec): 28719225 (44 times)
=================
Thread Info Dump:
=================
"HTTP Listener-3592 - /emd/main/ (~Task-free~ OMS.pbs@16398@omsnode=>[150183756670001])" tid=3592 WAITING
sun.misc.Unsafe.park(Native Method)
- waiting on <0x149717ec> (a java.util.concurrent.locks.ReentrantLock$NonfairSync), which is owned by "HTTP Listener-2141 - /emd/main/ (~Task-free~ OMS.pbs@13103@omsnode=>[150182243190001])" (tid=2141)
java.util.concurrent.locks.LockSupport.park(LockSupport.java:156)
java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:811)
java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireQueued(AbstractQueuedSynchronizer.java:842)
java.util.concurrent.locks.AbstractQueuedSynchronizer.acquire(AbstractQueuedSynchronizer.java:1178)
java.util.concurrent.locks.ReentrantLock$NonfairSync.lock(ReentrantLock.java:186)
...
..
.
$ emctl stop agent Oracle Enterprise Manager Cloud Control 12c Release 5 Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved. Stopping agent ... stopped.
$ emctl start agent Oracle Enterprise Manager Cloud Control 12c Release 5 Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved. Starting agent ............................................ started.
$ emctl upload agent Oracle Enterprise Manager Cloud Control 12c Release 5 Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved. --------------------------------------------------------------- EMD upload completed successfully
$ emctl reload agent Oracle Enterprise Manager Cloud Control 12c Release 5 Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved. --------------------------------------------------------------- EMD reload completed successfully
$ emctl status agent ... .. Last attempted heartbeat to OMS : 2017-08-04 19:53:31 Last successful heartbeat to OMS : 2017-08-04 19:53:31 Next scheduled heartbeat to OMS : 2017-08-04 19:54:32 --------------------------------------------------------------- Agent is Running and Ready
When investigating into client’s Oracle database performance issue, we found the swap space usage is constantly very high on this Linux server.
OS: RHEL 7.3
DB: Oracle 12.2.0.1
Tasks: 352 total, 2 running, 350 sleeping, 0 stopped, 0 zombie Cpu(s): 13.4%us, 4.1%sy, 0.0%ni, 79.3%id, 2.2%wa, 0.3%hi, 0.8%si, 0.0%st Mem: 32172820k total, 32015956k used, 156864k free, 14528k buffers Swap: 16777208k total, 7435428k used, 9341780k free, 11129844k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 137049 oracle 15 0 16.2g 5.3g 5.3g S 20.6 17.4 10:14.17 oracle 72457 oracle 15 0 16.2g 4.7g 4.7g S 15.3 15.3 10:50.15 oracle ... .. .
$ pmap -x 137049 137049: oracleRACTEST1 (LOCAL=NO) Address Kbytes RSS Dirty Mode Mapping 0000000000400000 96356 11704 0 r-x-- oracle 0000000006419000 444 140 4 rwx-- oracle 0000000006488000 148 100 80 rwx-- [ anon ] 000000001966e000 532 176 92 rwx-- [ anon ] 0000000060000000 16779264 5444888 1485768 rwxs- [ shmid=0x670005 ] 00000032b6a00000 112 108 0 r-x-- ld-2.5.so 00000032b6c1c000 4 0 0 r-x-- ld-2.5.so ... .. . 00007fff4c504000 160 136 132 rwx-- [ stack ] 00007fff4c5d2000 12 4 0 r-x-- [ anon ] ffffffffff600000 8192 0 0 ----- [ anon ] ---------------- ------ ------ ------ total kB 16954204 5477952 1490448
$ cat /proc/sys/vm/swappiness
10
Read more of this content when you subscribe today.
Make DBA Life Easy 