DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, December 14, 2018

View running processes in Oracle DB

View running processes in Oracle DB

This will show you a list of all running processes:
SET LINESIZE 200
SET PAGESIZE 200
SELECT PROCESS pid, sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';
Identify database SID based on OS Process ID
use the following SQL query, when prompted enter the OS process PID:
SET LINESIZE 100
col sid format 999999
col username format a20
col osuser format a15
SELECT b.spid,a.sid, a.serial#,a.username, a.osuser
FROM v$session a, v$process b
WHERE a.paddr= b.addr
AND b.spid='&spid'
ORDER BY b.spid;
For making sure you are targeting the correct session, you might want to review the SQL associated with the offensive task, to view the SQL being executed by the session you can use the following SQL statement:
SELECT
b.username, a.sql_text
FROM
v$sqltext_with_newlines a, v$session b, v$process c
WHERE
c.spid = '&spid'
AND
c.addr = b.paddr
AND
b.sql_address = a.address;
Killing the session
The basic syntax for killing a session is shown below.
ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete.

 In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. 

If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

Monday, December 10, 2018

DELETE/REMOVE Non Executing Datapump Jobs

Step 1:-
Normally, we can run the below query to find the datapump jobs and get their status:-

SQL> SET lines 150 pages 999
COL OWNER_NAME         for a18
COL JOB_NAME           for a25
COL OPERATION          for a14
COL JOB_MODE           for a15
COL STATE              for a18
COL DEGREE             for 99999
COL ATTACHED_SESSIONS  for 99999
COL DATAPUMP_SESSIONS  for 99999
SELECT *
  FROM dba_datapump_jobs;

OWNER_NAME         JOB_NAME                  OPERATION      JOB_MODE        STATE              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------ ------------------------- -------------- --------------- ------------------ ------ ----------------- -----------------
SYS                SYS_EXPORT_SCHEMA_02      EXPORT         SCHEMA          EXECUTING               1                 1                 3
SYS                SYS_EXPORT_SCHEMA_01      EXPORT         SCHEMA          NOT RUNNING             0                 0                 0

Step 2:-
Now, I want to kill "SYS_EXPORT_SCHEMA_01" which is in "NOT RUNNING" state and in order to kill the job, we can use the below procedure

SQL> DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','SYS');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/
DECLARE
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1852
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5319
ORA-06512: at line 4

Step 3:-
Since the job is already in "NOT RUNNING" state, we received the above error. To remove the job, identify the master tables which are created for this job.

SQL> SET lines 150
col "OBJECT_TYPE" for a20
col "OWNER.OBJECT" for a40
SELECT o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS                 OBJECT_ID OBJECT_TYPE          OWNER.OBJECT
--------------------- ---------- -------------------- ----------------------------------------
VALID                     235524 TABLE                SYS.SYS_EXPORT_SCHEMA_01
VALID                     236371 TABLE                SYS.SYS_EXPORT_SCHEMA_02

Step 4:-
Now, drop the master tables to cleanup the job

SQL> DROP TABLE SYS.SYS_EXPORT_SCHEMA_01;

Table dropped.

Step 5:-
Verify the job is dropped by re-running the statement from Step 1.

Friday, November 30, 2018

Start / Stop / Relocate SCAN listener in Oracle 11gR2 RAC

Start/ Stop / Relocate SCAN listener in Oracle 11gR2 RAC

1) Check listener status ( login to grid home)

a) Check the cluster resource status

$ crsctl stat res -t

verify the output for listener
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS     
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.FRA.dg
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.asm
               ONLINE  ONLINE       node1                    Started           
               ONLINE  ONLINE       node2                    Started           
ora.gsd
               OFFLINE OFFLINE      node1                                       
               OFFLINE OFFLINE      node2                                       
ora.net1.network
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.ons
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
ora.registry.acfs
               ONLINE  ONLINE       node1                                       
               ONLINE  ONLINE       node2                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node2                                       
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                                       
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node1                                       
ora.cvu
      1        ONLINE  ONLINE       node1                                       
ora.node1.vip
      1        ONLINE  ONLINE       node1                                       
ora.node2.vip
      1        ONLINE  ONLINE       node2                                       
ora.PROD.db
      1        ONLINE  ONLINE       node1                    Open               
      2        ONLINE  ONLINE       node2                    Open               
ora.oc4j
      1        ONLINE  ONLINE       node1                                       
ora.scan1.vip
      1        ONLINE  ONLINE       node2                                       
ora.scan2.vip
      1        ONLINE  ONLINE       node1                                       
ora.scan3.vip
      1        ONLINE  ONLINE       node1

b) Check the scan listener status

$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node node1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node node1

c) Check the listener home. That sholud run in grid home:

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 23-NOV-2018 14:02:27
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                29-NOV-2017 21:46:52
Uptime                    115 days 16 hr. 15 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.41)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
The command completed successfully

2) Start scan listener:

a) Start scan listener

$ srvctl start scan_listener

b) If lsnrctl status showing rdbms home, then do the following

$ lsnrctl stop
$ export ORACLE_HOME=/u01/app/grid
$ lsnrctl start


3) Relocate SCAN listener

a) To relocate
When you find all 3 scan listeners are running on single node, then you may relocate any one of the listener

$ srvctl relocate scan_LISTENER -i 1 -n node2

b) Check current status after Relocate SCAN_LISTENER:

bash-3.2$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node node1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node node1

c) inherit status

$ ps -ef|grep inherit
  oracle 49741838  9633998   0 14:10:00  pts/0  0:00 grep inherit
  oracle 18547030        1   0 13:26:56      -  0:00 /u01/app/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
  oracle 31588762        1   0 13:20:20      -  0:14 /u01/app/grid/bin/tnslsnr LISTENER -inherit


So What is difference is between relocating the SCAN using srvctl relocate scan and SCAN_LISTENER  by using srvctl relocate scan_listener command?

Regarding questions; The difference between a SCAN VIP and a normal RAC VIP, is that the RAC VIP has a node it want’s to run on and each node has one (whereas you only have 3 SCANs). If it fails over to another node, the normal VIP exists, but does not accept connections, whereas the SCAN is not fix to a node and can run on any node in the cluster (and will accept connections anytime).

Now that this works, the SCAN VIP will always move with the SCAN listener (otherwise it would not make any sense). Hence there is really no difference in moving the SCAN VIP (because this will trigger a relocate of the listener) or to move the SCAN_Listener (since this will move the VIP it depends on).

3) Checking SCAN IPs

$ srvctl config scan
SCAN name: scandb.production.com, Network: 1/10.20.30.0/255.255.255.192/en8
SCAN VIP name: scan1, IP: /scandb.production.com/10.20.30.42
SCAN VIP name: scan2, IP: /scandb.production.com/10.20.30.43
SCAN VIP name: scan3, IP: /scandb.production.com/10.20.30.44

Tablespace Thresholds and Alerts

How to set tablespace thresholds?

If you have very less no. of targets and you are using dbcontrol for each database, then use DBMS_SERVER_ALERT package to set. If you have OEM Cloud control 12c / 13c, then you can create a new Metric extension and and Rule and apply it. Here we ll discuss how to use DBMS_SERVER_ALERT package.

Use of the DBMS_SERVER_ALERT package as an early warning mechanism for space issues. The DBMS_SERVER_ALERT package as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by the Enterprise Manager (DB Control, Grid Control or Cloud Control).

Setting the OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAME parameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting.

There are two types of tablespace thresholds that can be set.

TABLESPACE_PCT_FULL : Percent full. 

When the warning or critical threshold based on percent full is crossed a notification occurs.

TABLESPACE_BYT_FREE : Free Space Remaining (KB). 

The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example

Cloud Control displays and sets these values in MB.
The thresholds are set using a value and an operator.

OPERATOR_LE : Less than or equal.
OPERATOR_GE : Greater than or equal.


Setting Thresholds:

Note:  You should know of your existing thresholds before changing them, so you know what to set them back to.

The following examples show how to set the different types of alerts.

Example-1:  Database-wide KB free threshold.

Begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
end;
/

Example-2:    Database-wide percent full threshold.

Begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '80',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '90',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
end;
/

Example-3:  Tablespace-specific KB free threshold.

begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
end;
/

Example-4:    Tablespace-specific percent full threshold.

begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '80',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '90',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
end;
/

Example-5: Tablespace-specific reset to defaults ( Set warning and critical values to NULL)

  --DBMS_SERVER_ALERT.set_threshold(
  --  metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
  --  warning_operator        => DBMS_SERVER_ALERT.operator_ge,
  --  warning_value           => NULL,
  --  critical_operator       => DBMS_SERVER_ALERT.operator_ge,
  --  critical_value          => NULL,
  --  observation_period      => 1,
  --  consecutive_occurrences => 1,
  --  instance_name           => NULL,
  --  object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
  --  object_name             => 'USERS');


>> Setting the warning and critical levels to '0' disables the notification.

Displaying Thresholds
The threshold settings can be displayed using the DBA_THRESHOLDS view.

SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;