How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

1)From V$LICENSE View. It shows USER sessions only excluding database system sessions.

SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license;

SESSIONS_CURRENT SESSIONS_HIGHWATER 
---------------- ------------------ 
 771             1346 

-- SESSIONS_CURRENT   :Current number of concurrent user sessions 
-- SESSIONS_HIGHWATER :Highest number of concurrent user sessions 
 since the instance started.

2) From v$resource_limit view. It shows both users and database system sessions.

SQL> select 
            RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE 
     from 
            v$resource_limit 
     where 
            RESOURCE_NAME in ('processes','sessions');

RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------- ------------------- --------------- ---------------
processes      842                 1414            4040
sessions       865                 1439            6096

-- CURRENT_UTILIZATION : Number of (resources, locks, or processes) 
                         currently being used.
-- MAX_UTILIZATION : Maximum consumption of this resource since 
                     the last instance start-up

3)From AWR.

col STARTT format a20
col FINISHH format a20
col RESOURCE_NAME format a13

SQL>select     
           a.snap_id SNAP_ID,
           to_char(b.begin_interval_time,'YYYYMMDD-HH24:MI:SS')  STARTT,
           to_char(b.end_interval_time,'YYYYMMDD-HH24:MI:SS') FINISHH,
           a.resource_name,
           a.CURRENT_UTILIZATION,
           a.max_utilization ,
           a.INITIAL_ALLOCATION,
           a.LIMIT_VALUE
from       
           DBA_HIST_RESOURCE_LIMIT a, 
           DBA_HIST_SNAPSHOT b
where      
          ( a.resource_name like '%process%' or a.resource_name like '%session%')
and        a.snap_id=b.snap_id
and         a.instance_number=b.instance_number
and         a.instance_number=1
and         b.begin_interval_time > sysdate - 2
order by
         a.snap_id,
         a.resource_name;

SNAP_ID    STARTT               FINISHH              RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- -------------------- -------------------- ------------- ------------------- --------------- ---------- ----------
 63141     20171214-10:00:13    20171214-11:00:15    processes     845 1414 4040 4040
 63141     20171214-10:00:13    20171214-11:00:15    sessions      869 1439 6096 6096
 63142     20171214-11:00:15    20171214-12:00:18    processes     847 1414 4040 4040
 63142     20171214-11:00:15    20171214-12:00:18    sessions      871 1439 6096 6096
 63143     20171214-12:00:18    20171214-13:00:21    processes     845 1414 4040 4040
 63143     20171214-12:00:18    20171214-13:00:21    sessions      870 1439 6096 6096
 63144     20171214-13:00:21    20171214-14:00:23    processes     842 1414 4040 4040
 63144     20171214-13:00:21    20171214-14:00:23    sessions      866 1439 6096 6096
 63145     20171214-14:00:23    20171214-15:00:26    processes     843 1414 4040 4040
 63145     20171214-14:00:23    20171214-15:00:26    sessions      869 1439 6096 6096
 63146     20171214-15:00:26    20171214-16:00:29    processes     852 1414 4040 4040
 63146     20171214-15:00:26    20171214-16:00:29    sessions      879 1439 6096 6096
 63147     20171214-16:00:29    20171214-17:00:32    processes     849 1414 4040 4040
 63147     20171214-16:00:29    20171214-17:00:32    sessions      873 1439 6096 6096
 63148     20171214-17:00:32    20171214-18:00:35    processes     848 1414 4040 4040
 63148     20171214-17:00:32    20171214-18:00:35    sessions      872 1439 6096 6096
 63149     20171214-18:00:35    20171214-19:00:02    processes     840 1414 4040 4040
 63149     20171214-18:00:35    20171214-19:00:02    sessions      864 1439 6096 6096
 63150     20171214-19:00:02    20171214-20:00:05    processes     841 1414 4040 4040
 63150     20171214-19:00:02    20171214-20:00:05    sessions      865 1439 6096 6096
 63151     20171214-20:00:05    20171214-21:00:08    processes     845 1414 4040 4040
 63151     20171214-20:00:05    20171214-21:00:08    sessions      871 1439 6096 6096
 63152     20171214-21:00:08    20171214-22:00:12    processes     850 1414 4040 4040
 63152     20171214-21:00:08    20171214-22:00:12    sessions      875 1439 6096 6096
 63153     20171214-22:00:12    20171214-23:00:15    processes     854 1414 4040 4040
 63153     20171214-22:00:12    20171214-23:00:15    sessions      880 1439 6096 6096
 63154     20171214-23:00:15    20171215-00:00:17    processes     849 1414 4040 4040
 63154     20171214-23:00:15    20171215-00:00:17    sessions      873 1439 6096 6096
 63155     20171215-00:00:17    20171215-01:00:21    processes     851 1414 4040 4040
 63155     20171215-00:00:17    20171215-01:00:21    sessions      877 1439 6096 6096
 63156     20171215-01:00:21    20171215-02:00:24    processes     837 1414 4040 4040
 63156     20171215-01:00:21    20171215-02:00:24    sessions      861 1439 6096 6096
 63157     20171215-02:00:24    20171215-03:00:27    processes     847 1414 4040 4040
 63157     20171215-02:00:24    20171215-03:00:27    sessions      872 1439 6096 6096
 63158     20171215-03:00:27    20171215-04:00:01    processes     846 1414 4040 4040
 63158     20171215-03:00:27    20171215-04:00:01    sessions      872 1439 6096 6096
 63159     20171215-04:00:01    20171215-05:00:04    processes     841 1414 4040 4040
 63159     20171215-04:00:01    20171215-05:00:04    sessions      865 1439 6096 6096
 63160     20171215-05:00:04    20171215-06:00:06    processes     839 1414 4040 4040
 63160     20171215-05:00:04    20171215-06:00:06    sessions      864 1439 6096 6096
 63161     20171215-06:00:06    20171215-07:00:09    processes     847 1414 4040 4040
 63161     20171215-06:00:06    20171215-07:00:09    sessions      871 1439 6096 6096
 63162     20171215-07:00:09    20171215-08:00:12    processes     843 1414 4040 4040
 63162     20171215-07:00:09    20171215-08:00:12    sessions      866 1439 6096 6096
 63163     20171215-08:00:12    20171215-09:00:14    processes     852 1414 4040 4040
 63163     20171215-08:00:12    20171215-09:00:14    sessions      878 1439 6096 6096

46 rows selected.

4) From OEM Repository.

 col COLUMN_LABEL format a30
 col MINIMUM format 99.99
 col MAXIMUM format 99.99
 col AVERAGE format 99.99

SQL>select 
           to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
           MINIMUM,MAXIMUM,AVERAGE 
    from   MGMT$METRIC_DAILY 
   where   TARGET_NAME='RACTEST' 
     and   COLUMN_LABEL in ('Session Limit Usage (%)') 
   order by 
           ROLLUP_TIMESTAMP,
           COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Session Limit Usage (%)        2.87    6.40    5.22
2017-07-16 Session Limit Usage (%)        6.45   14.57   13.67
2017-07-17 Session Limit Usage (%)        7.96   14.40   14.07
2017-07-18 Session Limit Usage (%)       13.99   14.71   14.34
2017-07-19 Session Limit Usage (%)       14.14   14.94   14.63
2017-07-20 Session Limit Usage (%)       14.55   15.04   14.80
...
..
.
2017-12-04 Session Limit Usage (%)       14.88   15.88   15.47
2017-12-05 Session Limit Usage (%)       14.45   15.16   14.80
2017-12-06 Session Limit Usage (%)       13.99   14.98   14.58
2017-12-07 Session Limit Usage (%)       13.86   14.32   14.09
2017-12-08 Session Limit Usage (%)       13.88   14.37   14.12
2017-12-09 Session Limit Usage (%)       13.80   14.40   14.04
2017-12-10 Session Limit Usage (%)       13.91   14.44   14.06
2017-12-11 Session Limit Usage (%)       13.88   14.35   14.07
2017-12-12 Session Limit Usage (%)       13.96   14.30   14.12
2017-12-13 Session Limit Usage (%)       13.98   14.44   14.18
2017-12-14 Session Limit Usage (%)       14.09   14.42   14.24

--
--

SQL> select 
            to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
            MINIMUM,MAXIMUM,AVERAGE 
       from MGMT$METRIC_DAILY 
      where TARGET_NAME='RACTEST' 
        and COLUMN_LABEL in ('Process Limit Usage (%)') 
   order by ROLLUP_TIMESTAMP,
            COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Process Limit Usage (%)        3.76    9.16    7.33
2017-07-16 Process Limit Usage (%)        9.21   21.31   20.00
2017-07-17 Process Limit Usage (%)       11.34   21.09   20.60
2017-07-18 Process Limit Usage (%)       20.50   21.49   21.00
2017-07-19 Process Limit Usage (%)       20.74   21.83   21.44
2017-07-20 Process Limit Usage (%)       21.36   22.00   21.71
...
..
.
2017-12-09 Process Limit Usage (%)       20.25   20.99   20.58
2017-12-10 Process Limit Usage (%)       20.40   21.16   20.61
2017-12-11 Process Limit Usage (%)       20.37   21.01   20.63
2017-12-12 Process Limit Usage (%)       20.47   20.97   20.70
2017-12-13 Process Limit Usage (%)       20.52   21.06   20.78
2017-12-14 Process Limit Usage (%)       20.72   21.11   20.89

 

Advertisements
Posted in OEM12c, OEM13c, SQL Scripts For DBA | Tagged , , , , , , , , , , , , , | Leave a comment

How to Change or Remove Partner Agent In OEM 13.2

Environment :

OEM OMS  : 13.2
OEM Agent: 13.2
Monitored Agent:           racnode1.virtuallab:3872
Current Partner Agent:  host1.virtuallab:3872
New Partner Agent:        host2.virtuallab:3872

Check Associated Partner Agent

1)On  OEM Agent Home Page.

2) Run below SQL against OEM Repository Database.

SQL> select target_agent.target_name "Target Agent",
           partner_agent.target_name "Partner Agent"
      from
           EM_AGENT_BUDDY_MAP eabm,
                 mgmt_targets target_agent,
                 mgmt_targets partner_agent
      where
          eabm.buddy_target_guid = partner_agent.target_guid and
          eabm.agent_target_guid = target_agent.target_guid and
         target_agent.target_name = 'racnode1.virtuallab:3872';

Target Agent               Partner Agent
-------------------------  ---------------------------------
racnode1.virtuallab:3872   host1.virtuallab:3872

Change Associated Partner Agent

1)Logon EMCLI

$ emcli login -username=sysman
Enter password

Login successful

2) Remove Current Associated Agent.

$emcli manage_agent_partnership -remove_agent_partnership -monitored_agent=racnode1.virtuallab:3872 -partner_agent=host1.virtuallab:3872
Manage Agent Partnership operation completed successfully

3) Add New Associated Agent.

$ emcli manage_agent_partnership -add_agent_partnership -monitored_agent=racnode1.virtuallab:3872 -partner_agent=host2.virtuallab:3872
Manage Agent Partnership operation completed successfully

Check Associated Partner Agent Again

1)On  OEM Agent Home Page.

2) Run below SQL against OEM Repository Database.

SQL> select target_agent.target_name "Target Agent",
           partner_agent.target_name "Partner Agent"
      from
           EM_AGENT_BUDDY_MAP eabm,
                 mgmt_targets target_agent,
                 mgmt_targets partner_agent
      where
          eabm.buddy_target_guid = partner_agent.target_guid and
          eabm.agent_target_guid = target_agent.target_guid and
         target_agent.target_name = 'racnode1.virtuallab:3872';

Target Agent               Partner Agent
-------------------------  ---------------------------------
racnode1.virtuallab:3872   host2.virtuallab:3872
Posted in OEM13c | Tagged , , | Leave a comment

How to Check Network Port Open on Linux

1) Telnet.

$ telnet ractest 1234
Trying 10.3.2.14...
telnet: connect to address 10.3.2.14: Connection refused

$ telnet ractest 3872
Trying 10.3.2.14...
Connected to ractest.
Escape character is '^]'.


Connection closed by foreign host.

2) cURL

$ curl -v telnet://ractest:1234
* About to connect() to ractest port 1234 (#0)
* Trying 10.3.2.14... Connection refused
* couldn't connect to host
* Closing connection #0
curl: (7) couldn't connect to host


$ curl -v telnet://ractest:3872
* About to connect() to ractest port 3872 (#0)
* Trying 10.3.2.14... connected
* Connected to ractest (10.3.2.14) port 3872 (#0)
* Closing connection #0

3) Bash

$ cat < /dev/tcp/10.3.2.14/1234
-bash: connect: Connection refused
-bash: /dev/tcp/10.3.2.14/1234: Connection refused


$ cat < /dev/tcp/10.3.2.14/3872
^C

4) Python

$ python
Python 2.6.6 (r266:84292, Sep 4 2013, 07:46:00)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import socket
>>> clientsocket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
>>> clientsocket.connect(('10.3.2.14',1234))
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
 File "<string>", line 1, in connect
socket.error: [Errno 111] Connection refused
>>>
>>>
>>> import socket
>>> clientsocket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
>>> clientsocket.connect(('10.3.2.14',3872))
>>> clientsocket.send('\n')
1

5) Perl

$ perl
use IO::Socket::INET;
$| = 1;
my $socket = new IO::Socket::INET(PeerHost => '10.3.2.14',
PeerPort =>'1234',
Proto => 'tcp',
);
die "cannot connect to the server $!\n" unless $socket;
print "connected to the server\n";
^D
cannot connect to the server Connection refused

$ perl
use IO::Socket::INET;
$| = 1;
my $socket = new IO::Socket::INET(PeerHost => '10.3.2.14',
PeerPort =>'3872',
Proto => 'tcp',
);
die "cannot connect to the server $!\n" unless $socket;
print "connected to the server\n";
^D
connected to the server

6) On server to check port is listening

$ netstat -na|grep 1521|grep -i listen
tcp 0 0 10.3.2.14:1521 0.0.0.0:* LISTEN

7) On server to check connections through listening port

$ lsof -iTCP:1521

$ lsof -i :1521
COMMAND   PID  USER   FD  TYPE DEVICE    SIZE/OFF NODE NAME
ora_lreg_ 5396 oracle 49u IPv4 935666084 0t0      TCP  ractest:23228->ractest-vip:ncube-lm (ESTABLISHED)
...
..
.
Posted in Linux | Tagged , , , , , , , , , , , | Leave a comment

“ORA-01013: user requested cancel of current operation”– OEM Slow Performance SQL Metrics SQL statement (SQL ID: 4d43by1zzjfna)

Below OEM alerts are received from databases of 11R2 to 12R2:

...
..
.
Target name=RACTEST
Incident creation time=28/11/2017 9:48:17 PM AEDT 
Last updated time=28/11/2017 9:48:17 PM AEDT 
Message= ORA-01013: user requested cancel of current operation 
Severity=Critical 
Incident ID=294770 
...
..
.

CAUSES

SQL ID 4d43by1zzjfna runs slowly until failed with ‘ORA-01013’ error.

SOLUTION

As per “low Performance Oracle Enterprise Manager SQL Metrics SQL statement (SQL ID: 4d43by1zzjfna) Automatically Run (Doc ID 2266608.1)”, below steps are taken :

(a) Stopping EM agent monitoring the RAC instance(s)

$AGENT_HOME/bin/emctl stop agent

(b) Test the current execution time ( 36 s ),  and record the execution plan via SQL PLUS:

SQL> set timing on
SQL> set autotrace on
SQL> WITH last_run AS
 (
 SELECT all_runs.OWNER ,
 all_runs.JOB_NAME,
 all_runs.STATUS
 FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs,
 (
 SELECT OWNER ,
 JOB_NAME,
 MAX(ACTUAL_START_DATE) AS START_DATE
 FROM DBA_SCHEDULER_JOB_RUN_DETAILS
 GROUP BY OWNER,
 JOB_NAME
 )
 latest_runs
 WHERE all_runs.OWNER = latest_runs.OWNER
 AND all_runs.JOB_NAME = latest_runs.JOB_NAME
 AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE
...
..
.

SUM(BROKEN) SUM(FAILED)
----------- -----------
 0          0

Elapsed: 00:00:36.49

Execution Plan
----------------------------------------------------------
Plan hash value: 204035645
...
..
.
Statistics
----------------------------------------------------------
 338   recursive calls
 0     db block gets
 58842 consistent gets
 0     physical reads
 0     redo size
 603   bytes sent via SQL*Net to client
 524   bytes received via SQL*Net from client
 2     SQL*Net roundtrips to/from client
 8671  sorts (memory)
 0     sorts (disk)
 1 rows processed

c)Purge any job history that is no longer needed to reduce the size of job related tables:

SQL> exec DBMS_SCHEDULER.PURGE_LOG(7,which_log=>'JOB_LOG');

PL/SQL procedure successfully completed.
Elapsed: 00:00:04.95

SQL> exec DBMS_SCHEDULER.PURGE_LOG(7,which_log=>'WINDOW_LOG');

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03

SQL> exec DBMS_SCHEDULER.PURGE_LOG(log_history => 7, job_name => 'job1, sys.class2');

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','7');

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

d) Regather statistics to ensure that the CBO has the right understanding of cardinality/cost for execution plans involving JOB related tables:

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.
Elapsed: 00:01:26.37

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.
Elapsed: 00:01:35.82

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

PL/SQL procedure successfully completed.
Elapsed: 00:10:53.24

e) rerun the same sql_id=’4d43by1zzjfna’. We can see there is a new execution plan. The running time is shorted from 36 seconds from 1 second.

SQL> WITH last_run AS
 2 (
 3 SELECT all_runs.OWNER ,
 4 all_runs.JOB_NAME,
 5 all_runs.STATUS
 6 FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs,
 7 (
 8 SELECT OWNER ,
 9 JOB_NAME,
 10 MAX(ACTUAL_START_DATE) AS START_DATE
 11 FROM DBA_SCHEDULER_JOB_RUN_DETAILS
 12 GROUP BY OWNER,
 13 JOB_NAME
 14 )
 15 latest_runs
 16 WHERE all_runs.OWNER = latest_runs.OWNER
 17 AND all_runs.JOB_NAME = latest_runs.JOB_NAME
 18 AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE
 19 )
 20 SELECT SUM(broken),
 21 SUM(failed)
 22 FROM
 23 (
 24 SELECT DECODE(broken, 'N', 0, 1) broken,
 25 DECODE(NVL(failures,0), 0, 0, 1) failed
 26 FROM dba_jobs
 27 UNION ALL
 28 SELECT DECODE(STATE,'BROKEN',1,0) broken,
 29 DECODE(STATUS ,'FAILED',DECODE(STATE,'BROKEN',0,'DISABLED',0,1),0) failed
 30 FROM
 31 (
 32 SELECT all_jobs.OWNER ,
 33 all_jobs.JOB_NAME,
 34 last_run.STATUS ,
 35 all_jobs.STATE
 36 FROM last_run,
 37 DBA_SCHEDULER_JOBS all_jobs
 38 WHERE last_run.OWNER =all_jobs.OWNER
 39 AND last_run.JOB_NAME=all_jobs.JOB_NAME
 40 )
 41 )
 42 /

SUM(BROKEN) SUM(FAILED)
----------- -----------
 0           0

Elapsed: 00:00:01.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2148836375
...
..
.
Statistics
----------------------------------------------------------
 50   recursive calls
 0    db block gets
 3585 consistent gets
 0    physical reads
 0    redo size
 603  bytes sent via SQL*Net to client
 524  bytes received via SQL*Net from client
 2    SQL*Net roundtrips to/from client
 51   sorts (memory)
 0    sorts (disk)
 1 rows processed

(f) Re-enable EM Agents monitoring the RAC instance(s):

$AGENT_HOME/bin/emctl start agent
Posted in OEM12c, OEM13c | Tagged , , , , , , , | Leave a comment

How to Make ASM Instance to Use HugePages

The ASM instance is configured in the same way as normal databases instance for using hugepages in Linux 6. But ASM is not using hugepages, while the normal instance is using hugepages. There are enough free hugepages available  as per /etc/meminfo.

1)  AMM is the default behaviour for ASM.

Set both sga_target and sga_max_size parameters in spfile of ASM as below. For ASM instance, it won’t use hugepages by just setting those two parameters for a ASM instance.

*.sga_target=4G
*.sga_max_size=4g
*.pga_aggregate_target=4G

Then bounce the ASM instance, we can see both memory_target and memory_max_target set by ASM by default.

SQL> show parameter memory_

NAME TYPE VALUE
------------------ -----------  ------------
memory_max_target   big integer 4096M
memory_target       big integer 4096M

2)  Set parameter memory_target = 0 and memory_max_target=0 explicitly in spfile of a ASM instance.

*.sga_target=4G
*.sga_max_size=4g
*.memory_target=0
*.memory_max_target=0
*.pga_aggregate_target=4G

Bounce the instance, we can see hugepages are used  as per in ‘alert_+ASM1.log”.

PAGESIZE  AVAILABLE_PAGES EXPECTED_PAGES   ALLOCATED_PAGES ERROR(s)
4K                 Configured                    10                                     10                                      NONE
2048K          56864                            2048                                2048                                 NONE

Posted in ASM, GI and RAC | Tagged , , , , , , , , , , , | Leave a comment

Install the AWS Command Line Interface on Microsoft Windows

There are two ways to install AWS CLI:

1. MSI Installer

a) Download the appropriate MSI installer 64-bit or 32-bit.

b) Run the downloaded MSI installer, and follows the instructions on screen.
  By default, the CLI is installed onto:

C:\Program Files\Amazon\AWSCLI (64-bit) or
C:\Program Files (x86)\Amazon\AWSCLI (32-bit)

c) Test the installation.

C:\>aws --version 
aws-cli/1.11.84 Python/3.6.2 Windows/7 botocore/1.5.47

d)  Updating an MSI Installation.

To update to the latest version, download and run the MSI installer again as detailed above.

e) Uninstall AWS CLI Components.

Control Panel -> All Control Panel Items -> Programs and Features. Select the entry named AWS Command Line Interface and click Uninstall to launch the uninstaller.

2. Install Python, pip, and the AWS CLI

a) To install Python 3.6 and pip (Windows).

i. Download the Python 3.6 Windows x86-64 executable installer from the downloads page of Python.org.

ii. Run the installer, then click “Install Now” ( C:\Program Files\Python36 )
Tick “Install Launcher for all users ( recommended)
Tick “Add Python 3.6 to PATH”

iii. Until see the below “Setup was successful” screen.

Python_installation

b) To install the AWS CLI with pip (Windows).

i. Open Command Prompt, and verify Python and pip are both installed successfully.

C:\>python --version
Python 3.6.3

c:\>pip --version
pip 9.0.1 from c:\program files\python36\lib\site-packages (python 3.6)

ii. Install the AWS CLI using pip, Run as Administrator user if not Administrator user.

C:\>pip install awscli

Collecting awscli
  Downloading awscli-1.11.185-py2.py3-none-any.whl (1.2MB)
    100% |████████████████████████████████| 1.2MB 930kB/s

Collecting docutils>=0.10 (from awscli)
  Downloading docutils-0.14-py3-none-any.whl (543kB)
    100% |████████████████████████████████| 552kB 1.8MB/s

Collecting rsa<=3.5.0,>=3.1.2 (from awscli)
  Downloading rsa-3.4.2-py2.py3-none-any.whl (46kB)
    100% |████████████████████████████████| 51kB 6.4MB/s

Collecting colorama<=0.3.7,>=0.2.5 (from awscli)
  Downloading colorama-0.3.7-py2.py3-none-any.whl

Collecting botocore==1.7.43 (from awscli)
  Downloading botocore-1.7.43-py2.py3-none-any.whl (3.7MB)
    100% |████████████████████████████████| 3.7MB 286kB/s

Collecting PyYAML<=3.12,>=3.10 (from awscli)
  Downloading PyYAML-3.12.tar.gz (253kB)
    100% |████████████████████████████████| 256kB 3.5MB/s

Collecting s3transfer<0.2.0,>=0.1.9 (from awscli)
  Downloading s3transfer-0.1.11-py2.py3-none-any.whl (54kB)
    100% |████████████████████████████████| 61kB 6.8MB/s

Collecting pyasn1>=0.1.3 (from rsa<=3.5.0,>=3.1.2->awscli)
  Downloading pyasn1-0.3.7-py2.py3-none-any.whl (63kB)
    100% |████████████████████████████████| 71kB 6.0MB/s

Collecting python-dateutil<3.0.0,>=2.1 (from botocore==1.7.43->awscli)
  Downloading python_dateutil-2.6.1-py2.py3-none-any.whl (194kB)
    100% |████████████████████████████████| 194kB 3.8MB/s

Collecting jmespath<1.0.0,>=0.7.1 (from botocore==1.7.43->awscli)
  Downloading jmespath-0.9.3-py2.py3-none-any.whl

Collecting six>=1.5 (from python-dateutil<3.0.0,>=2.1->botocore==1.7.43->awscli)
  Downloading six-1.11.0-py2.py3-none-any.whl

Installing collected packages: docutils, pyasn1, rsa, colorama, six, python-date
util, jmespath, botocore, PyYAML, s3transfer, awscli

  Running setup.py install for PyYAML ... done

Successfully installed PyYAML-3.12 awscli-1.11.185 botocore-1.7.43 colorama-0.3.
7 docutils-0.14 jmespath-0.9.3 pyasn1-0.3.7 python-dateutil-2.6.1 rsa-3.4.2 s3tr
ansfer-0.1.11 six-1.11.0

iii. Verify that the AWS CLI is installed correctly.

c:\>aws --version
aws-cli/1.11.185 Python/3.6.3 Windows/2012ServerR2 botocore/1.7.43

Vi. To upgrade to the latest version, run the installation command again:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>pip install --user --upgrade awscli
Requirement already up-to-date: awscli in c:\program files\python36\lib\site-pac
kages
Requirement already up-to-date: rsa<=3.5.0,>=3.1.2 in c:\program files\python36\
lib\site-packages (from awscli)
Requirement already up-to-date: colorama<=0.3.7,>=0.2.5 in c:\program files\pyth
on36\lib\site-packages (from awscli)
Requirement already up-to-date: docutils>=0.10 in c:\program files\python36\lib\
site-packages (from awscli)
Requirement already up-to-date: botocore==1.7.43 in c:\program files\python36\li
b\site-packages (from awscli)
Requirement already up-to-date: s3transfer<0.2.0,>=0.1.9 in c:\program files\pyt
hon36\lib\site-packages (from awscli)
Requirement already up-to-date: PyYAML<=3.12,>=3.10 in c:\program files\python36
\lib\site-packages (from awscli)
Requirement already up-to-date: pyasn1>=0.1.3 in c:\program files\python36\lib\s
ite-packages (from rsa<=3.5.0,>=3.1.2->awscli)
Requirement already up-to-date: jmespath<1.0.0,>=0.7.1 in c:\program files\pytho
n36\lib\site-packages (from botocore==1.7.43->awscli)
Requirement already up-to-date: python-dateutil<3.0.0,>=2.1 in c:\program files\
python36\lib\site-packages (from botocore==1.7.43->awscli)
Requirement already up-to-date: six>=1.5 in c:\program files\python36\lib\site-p
ackages (from python-dateutil<3.0.0,>=2.1->botocore==1.7.43->awscli)

C:\Windows\system32>

3.  Adding the AWS CLI Executable to your Command Line Path

After installing with pip, add the aws executable to your OS’s PATH environment variable. With an MSI installation, this should happen automatically, but you may need to set it manually if the aws command is not working.

  • Python 3.6 and pip%USERPROFILE%\AppData\Local\Programs\Python\Python36\Scripts
  • MSI installer (64-bit)C:\Program Files\Amazon\AWSCLI
  • MSI installer (32-bit)C:\Program Files (x86)\Amazon\AWSCLI
Posted in AWS | Leave a comment

Database Size History Report From OEM

A DBA is always asked about the database size growth trend for coming months  of one database  or databases, which possibly face storage or performance issue.  We can only get current database size from the database, or the size of previous couple of days ago from DBA_HIST_* , which requires license.

Here we use another way to extract database history size data from OEM 13c repository.

1) OEM repository view MGMT$METRIC_DAILY stores all the database size data daily.

SQL> desc MGMT$METRIC_DAILY
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 TARGET_NAME                               NOT NULL VARCHAR2(256)
 TARGET_TYPE                               NOT NULL VARCHAR2(64)
 TARGET_GUID                               NOT NULL RAW(16)
 METRIC_NAME                               NOT NULL VARCHAR2(64)
 METRIC_COLUMN                             NOT NULL VARCHAR2(64)
 METRIC_GUID                               NOT NULL RAW(16)
 METRIC_LABEL                                       VARCHAR2(64)
 COLUMN_LABEL                                       VARCHAR2(256)
 KEY_VALUE                                 NOT NULL VARCHAR2(256)
 KEY_VALUE2                                NOT NULL VARCHAR2(256)
 KEY_VALUE3                                NOT NULL VARCHAR2(256)
 KEY_VALUE4                                NOT NULL VARCHAR2(256)
 KEY_VALUE5                                NOT NULL VARCHAR2(256)
 KEY_VALUE6                                NOT NULL VARCHAR2(256)
 KEY_VALUE7                                NOT NULL VARCHAR2(256)
 ROLLUP_TIMESTAMP                          NOT NULL DATE
 SAMPLE_COUNT                              NOT NULL NUMBER(38)
 AVERAGE                                            NUMBER
 MINIMUM                                            NUMBER
 MAXIMUM                                            NUMBER
 STANDARD_DEVIATION                                 NUMBER
 TIMEZONE_REGION                                    VARCHAR2(64)

2)  Get one certain database size history from OEM repository view  MGMT$METRIC_DAILY.

SQL> select 
      target_name,
      to_char(rollup_timestamp,'YYYY-MM-DD') DAY ,
      average                                SIZE_IN_GB_TD,
      LAG(average) OVER( order by rollup_timestamp ) SIZE_IN_GB_YTD,
      average - LAG(average) OVER( order by rollup_timestamp ) SIZE_IN_GB_INCREASED
    from 
          mgmt$metric_daily
    where target_name ='&DB_NAME'
      and target_type='rac_database' -- or target_type='oracle_database' if the target is non-rac database
      and column_label = 'Allocated Space(GB)'  -- or column_label ='Used Space(GB)'
 order by 
      target_name,
      rollup_timestamp;
Enter value for db_name: RACTEST
old 7: where target_name ='&DB_NAME'
new 7: where target_name ='RACTEST'

TARGET_NAME      DAY        SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB_INCREASED
---------------- ---------- ------------- -------------- --------------------
RACTEST          2017-08-24 153.77
RACTEST          2017-08-25 153.77         153.77        0
RACTEST          2017-08-26 154.88         153.77        1.11
RACTEST          2017-08-27 155.46         154.88         .58
...
..
.
RACTEST          2017-11-06 170.24         170.24        0
RACTEST          2017-11-07 170.24         170.24        0

75 rows selected.

The TARGET_NAME can be available from :

SQL> select target_type, target_name 
       from mgmt_targets 
      where target_type='rac_database' 
         or target_type='oracle_database' 
      order by 1,2;

TARGET_TYPE              TARGET_NAME
------------------------ ----------------
oracle_database          ORCL
rac_database             RACTEST

3)  Get all databases size increasing from OEM repository view  MGMT$METRIC_DAILY for past 10 days.

SQL> select
          target_name,
          min(average) keep ( DENSE_RANK FIRST ORDER BY to_char(rollup_timestamp,'YYYY-MM-DD') ) FIRST,
          max(average) keep ( DENSE_RANK  LAST ORDER BY to_char(rollup_timestamp,'YYYY-MM-DD') )   LAST
     from 
         mgmt$metric_daily
    where 
         target_type in ('rac_database', 'oracle_database' )
     and column_label = 'Allocated Space(GB)'
     and rollup_timestamp >sysdate -10
 group by 
        target_name
order by 
        target_name;

TARGET_NAME      FIRST      LAST
---------------- ---------- ----------
ORCL             19.96      22.08
RACTEST         162.18     170.24
Posted in OEM12c, OEM13c, SQL Scripts For DBA | Tagged , , | Leave a comment

TFA – Oracle Trace File Analyser Installation

Uninstall TFA

1) Check TFA_HOME.

$ grep TFA_HOME= /etc/init.d/init.tfa
TFA_HOME=/u01/app/12.2.0.1/grid/tfa/racnode1/tfa_home

2) Uninstall TFA as root user.

[root@racnode1 bin]# ./tfactl -h

Usage : /u01/app/12.2.0.1/grid/bin/tfactl <command> [options]
 commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa
For detailed help on each command use:
 /u01/app/12.2.0.1/grid/bin/tfactl <command> -help

[root@racnode1 bin]# /u01/app/12.2.0.1/grid/bin/tfactl uninstall

TFA will be uninstalled on node racnode1 :

Removing TFA from racnode1 only
Please remove TFA locally on any other configured nodes

Notifying Other Nodes about TFA Uninstall...
TFA is not yet secured to run all commands
FAIL
Sleeping for 10 seconds...
Stopping TFA Support Tools...
Stopping TFA in racnode1...
Shutting down TFA
Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.
. . . . .
. . .
Successfully shutdown TFA..

Deleting TFA support files on racnode1:
Removing /u01/app/grid/tfa/racnode1/database...
Removing /u01/app/grid/tfa/racnode1/log...
Removing /u01/app/grid/tfa/racnode1/output...
Removing /u01/app/grid/tfa/racnode1...
Removing /u01/app/grid/tfa...
Removing /etc/rc.d/rc0.d/K17init.tfa
Removing /etc/rc.d/rc1.d/K17init.tfa
Removing /etc/rc.d/rc2.d/K17init.tfa
Removing /etc/rc.d/rc4.d/K17init.tfa
Removing /etc/rc.d/rc6.d/K17init.tfa
Removing /etc/init.d/init.tfa...
Removing /u01/app/12.2.0.1/grid/bin/tfactl...
Removing /u01/app/12.2.0.1/grid/tfa/bin...
Removing /u01/app/12.2.0.1/grid/tfa/racnode1...
Removing /u01/app/12.2.0.1/grid/tfa...

Install TFA

1)Install TFA locally as root user on each RAC node.

[root@racnode1 TFA]# ./installTFA-LINUX -tfabase /u01/app/grid -javahome /u01/app/12.2.0.1/grid/jdk
TFA Installation Log will be written to File : /tmp/tfa_install_49362_2017_08_31-11_35_54.log

Starting TFA installation

TFA Version: 122122 Build Date: 201707270831

Running Auto Setup for TFA as user root...

Would you like to do a [L]ocal only or [C]lusterwide installation ? [L|l|C|c] [C] : L
Installing TFA now...

Discovering Nodes and Oracle resources

Starting Discovery...

Getting list of nodes in cluster . . . . .

List of nodes in cluster:
racnode1
racnode2

CRS_HOME=/u01/app/12.2.0.1/grid

Searching for running databases...
1. RACDEV1
2. RACTEST1

Searching out ORACLE_HOME for selected databases...
Getting Oracle Inventory...
ORACLE INVENTORY: /u01/app/oraInventory
Discovery Complete...

TFA Will be Installed on racnode1...
Checking JAVA Status on all nodes ...
TFA will scan the following Directories
++++++++++++++++++++++++++++++++++++++++++++

.------------------------------------------------------------------.
|                            racnode1                              |
+-------------------------------------------------------+----------+
| Trace Directory                                       | Resource |
+----------------------------------------------- -------+----------+
| /u01/app/12.2.0.1/grid/cfgtoollogs                    | CFGTOOLS |
| /u01/app/12.2.0.1/grid/crf/db/racnode1                | CRS      |
| /u01/app/12.2.0.1/grid/crs/log                        | CRS      |
| /u01/app/12.2.0.1/grid/css/log                        | CRS      |
| /u01/app/12.2.0.1/grid/cv/log                         | CRS      |
| /u01/app/12.2.0.1/grid/evm/admin/log                  | CRS      |
| /u01/app/12.2.0.1/grid/evm/admin/logger               | CRS      |
| /u01/app/12.2.0.1/grid/evm/log                        | CRS      |
| /u01/app/12.2.0.1/grid/install                        | INSTALL  |
| /u01/app/12.2.0.1/grid/inventory/ContentsXML          | INSTALL  |
| /u01/app/12.2.0.1/grid/log                            | CRS      |
| /u01/app/12.2.0.1/grid/network/log                    | CRS      |
| /u01/app/12.2.0.1/grid/opmn/logs                      | CRS      |
| /u01/app/12.2.0.1/grid/racg/log                       | CRS      |
| /u01/app/12.2.0.1/grid/rdbms/log                      | ASM      |
| /u01/app/12.2.0.1/grid/scheduler/log                  | CRS      |
| /u01/app/12.2.0.1/grid/srvm/log                       | CRS      |
| /u01/app/grid/cfgtoollogs                             | CFGTOOLS |
| /u01/app/grid/crsdata/racnode1/acfs                   | ACFS     |
| /u01/app/grid/crsdata/racnode1/afd                    | ASM      |
| /u01/app/grid/crsdata/racnode1/chad                   | CRS      |
| /u01/app/grid/crsdata/racnode1/core                   | CRS      |
| /u01/app/grid/crsdata/racnode1/crsconfig              | CRS      |
| /u01/app/grid/crsdata/racnode1/crsdiag                | CRS      |
| /u01/app/grid/crsdata/racnode1/cvu                    | CRS      |
| /u01/app/grid/crsdata/racnode1/evm                    | CRS      |
| /u01/app/grid/crsdata/racnode1/output                 | CRS      |
| /u01/app/grid/crsdata/racnode1/trace                  | CRS      |
| /u01/app/grid/diag/asm/+asm/+ASM1/cdump               | ASM      |
| /u01/app/grid/diag/crs/racnode1/crs/cdump             | CRS      |
| /u01/app/grid/diag/crs/racnode1/crs/trace             | CRS      |
| /u01/app/grid/diag/rdbms/_mgmtdb/-MGMTDB/cdump        | RDBMS    |
| /u01/app/grid/diag/tnslsnr/racnode1/listener/cdump    | TNS      |
...
..
.
| /u01/app/oraInventory/ContentsXML                     | INSTALL |
| /u01/app/oraInventory/logs                            | INSTALL |
...
..
.

Installing TFA on racnode1:
HOST: racnode1 TFA_HOME: /u01/app/grid/tfa/racnode1/tfa_home

.-----------------------------------------------------------------------------.
| Host     | Status of TFA | PID   | Port | Version    | Build ID             |
+----------+---------------+-------+------+------------+----------------------+
| racnode1 |    RUNNING    | 51354 | 5000 | 12.2.1.2.2 | 12212220170727083130 |
'----------+---------------+-------+------+------------+----------------------'

Running Inventory in All Nodes...
Enabling Access for Non-root Users on racnode1...
Adding default users to TFA Access list...

Summary of TFA Installation:
.-----------------------------------------------------------.
|                 racnode1                                  |
+---------------------+-------------------------------------+
| Parameter           |            Value                    |
+---------------------+-------------------------------------+
| Install location    | /u01/app/grid/tfa/racnode1/tfa_home |
| Repository location | /u01/app/grid/tfa/repository        |
| Repository usage    | 0 MB out of 10240 MB                |
'---------------------+-------------------------------------'

TFA is successfully installed...

Usage : /u01/app/12.2.0.1/grid/bin/tfactl <command> [options]
 commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa|syncnodes
For detailed help on each command use:
 /u01/app/12.2.0.1/grid/bin/tfactl <command> -help

2) Startup OSWatcher with gzip option.

[root@racnode1 TFA]# /u01/app/12.2.0.1/grid/bin/tfactl
 tfactl> status oswbb
Check run status of TFA process
Usage : /u01/app/12.2.0.1/grid/bin/tfactl status

 tfactl> stop oswbb
Stopped OSWatcher

 tfactl> start oswbb 15 168 gzip
Starting OSWatcher

[root@racnode1 TFA]# ps -eaf | grep -i osw |grep -v grep
grid 19631 1 0 11:52 pts/3 00:00:00 /bin/sh ./OSWatcher.sh 15 168 gzip /u01/app/grid/tfa/repository/suptools/racnode1/oswbb/grid/archive

Synchronize TFA between RAC nodes.

[root@racnode1 ~]# /u01/app/12.2.0.1/grid/bin/tfactl syncnodes

Current Node List in TFA :
1. racnode1

Node List in Cluster :
1. racnode1
2. racnode2

Node List to sync TFA Certificates :
 1 racnode2
 
Do you want to update this node list? [Y|N] [N]: Y

Please Enter all the remote nodes you want to sync...

Enter Node List (seperated by space) : racnode1 racnode2

Node List to sync TFA Certificates :
 1 racnode2
 
Syncing TFA Certificates on racnode2 :

TFA_HOME on racnode2 : /u01/app/grid/tfa/racnode2/tfa_home

Copying TFA Certificates to racnode2...
root@racnode2's password:
Copying SSL Properties to racnode2...
root@racnode2's password:

Restarting TFA on racnode2...
root@racnode2's password:
Restarting TFA..
Killing TFA running with pid 17697
Waiting up to 120 seconds for TFA to be re-started..
. . . . . . . . . .
. . . . . . . . . .
. . . . . . . . . .
Successfully re-started TFA..

.--------------------------------------------------------------------------------------------------------.
| Host     | Status of TFA | PID        | Port    | Version    | Build ID             | Inventory Status |
+----------+---------------+------------+---------+------------+----------------------+------------------+
| racnode1 | RUNNING       | 117654     | 5000    | 12.2.1.2.2 | 12212220170727083130 | COMPLETE         |
| racnode2 | RUNNING       | 114949     | 5000    | 12.2.1.2.2 | 12212220170727083130 | COMPLETE         |
'----------+---------------+------------+---------+------------+----------------------+------------------'
Posted in 12c new features, Configuration | Tagged , | Leave a comment

ORMB Batch Scheduling Job Hangs At F1-FLUSH with “PL/SQL Lock Timer” Wait Event

Overnight ORMB Batch Scheduling Job has been running for more than twenty hours, user wants to know why ?

1) Check which job at what step is still running and hanging.

SQL> select OWNER,JOB_NAME,JOB_SUBNAME,SESSION_ID,ELAPSED_TIME,CPU_USED 
     from dba_scheduler_running_jobs;

OWNER   JOB_NAME    JOB_SUBNAME    SESSION_ID ELAPSED_TIME          CPU_USED
------- ------------ ------------- ---------- --------------------  ---------
SCHADM   DAILY_JOBS  F1_FLUSH_JOB  1006       +000 21:47:00.08 +000 00:00:22.15

2) Check session status.

SQL> select USERNAME,STATUS,EVENT,sql_id,LAST_CALL_ET from gv$session where sid=1006;

USERNAME  STATUS EVENT               SQL_ID           LAST_CALL_ET
--------- ------ ------------------  ---------------- -----------
SCHADM    ACTIVE PL/SQL lock timer   fnfa105xssacx    78949

3) Check which sql is the running with ‘PL/SQL lock timer’.

SQL> set long 20000
SQL> select SQL_FULLTEXT from v$sql where sql_id='fnfa105xssacx';

SQL_FULLTEXT
--------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
 ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window
_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(
14) := :chainid; credential_owner varchar2(30) := :credown; credential_name
varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destinat
ion_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_i
d number := :log_id; BEGIN DECLARE
 BATCH_CODE CHAR(8);
 USER_ID CHAR(8);
 THREAD_COUNT NUMBER;
 BUSINESS_DATE DATE;
 MAX_TIMEOUT_MINS NUMBER;
 RERUN_NUMBER NUMBER;
 THREAD_POOL VARCHAR2(254);
 MAIL_ID VARCHAR2(254);
 MIN_RUN_STATUS CHAR(2);
 RAISE_ERROR BOOLEAN;
 THREAD_NOTIFICATIONS BOOLEAN;
 SOFT_PARM_MAP CISADM.OUAF_BATCH.PARM_MAP_T;
 SOFT_PARM_LIST CISADM.OUAF_BATCH.PARM_LIST_T;
BEGIN
 BATCH_CODE := 'F1-FLUSH';
 USER_ID := 'JAMES';
 --THREAD_COUNT := 0;
 --BUSINESS_DATE := to_date('2016-01-31','yyyy-MM-dd');
 MAX_TIMEOUT_MINS := 0;
 RERUN_NUMBER := 0;
 THREAD_POOL := 'ORMB_BATCH';
 --MIN_RUN_STATUS := NULL;
 --RAISE_ERROR := NULL;
 -- THREAD_NOTIFICATIONS := true;
 --SOFT_PARM_MAP := NULL;
 --SOFT_PARM_LIST := CISADM.OUAF_BATCH.PARM_LIST_T('DIST-THD-POOL','');
 OUAF_BATCH.SUBMIT_JOB(
 BATCH_CODE => BATCH_CODE,
 USER_ID => USER_ID,
 THREAD_COUNT => THREAD_COUNT,
 BUSINESS_DATE => BUSINESS_DATE,
 MAX_TIMEOUT_MINS => MAX_TIMEOUT_MINS,
 RERUN_NUMBER => RERUN_NUMBER,
 THREAD_POOL => THREAD_POOL,
 MIN_RUN_STATUS => MIN_RUN_STATUS,
 RAISE_ERROR => RAISE_ERROR,
 THREAD_NOTIFICATIONS => THREAD_NOTIFICATIONS,
 SOFT_PARM_MAP => SOFT_PARM_MAP,
 SOFT_PARM_LIST => SOFT_PARM_LIST
 );
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

4) ‘PL/SQL Lock Timer’ event ( Doc ID 1476088.1 )

"PL/SQL Lock Timer"

This event is called through the DBMSLOCK.SLEEP procedure or USERLOCK.SLEEP procedure. This event is most likely to come from user written stored procedures. The ‘idle’ event ‘PL/SQL lock timer’ is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.
While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event.

This event is called through the DBMSLOCK.SLEEP procedure or 
USERLOCK.SLEEP procedure. This event is most likely to come from 
user written stored procedures.

5) Sent the details to vendor why F1-Flush has “PL/SQL lock timer’  wait event forever.

Posted in dbms_scheduler, ORMB | Tagged , , , , , | Leave a comment

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes the default attributes require to be modified for future partitions, or sub-partitions, specially in the situation when tables and indexes are refreshed from different environment.

Modifying Default Attributes of a Table

SQL>alter table OWNER.TABLE_NAME modify default attributes 
    tablespace TABLESPACE_NAME;

Modifying Default Attributes of a Index

SQL>alter index OWNER.INDEX_NAME modify default attributes 
    tablespace TABLESPACE_NAME;

Modifying Default Attributes of Table Partitions

SQL>alter table OWNER.TABLE_NAME modify default attributes 
    for partition PARTITION_NAME tablespace TABLESPACE_NAME;

Modifying Default Attributes of Index Partitions

SQL>alter index OWNER.INDEX_NAME modify default attributes 
 for partition PARTITION_NAME tablespace TABLESPACE_NAME;

Check Default Attributes of Tables

SQL> desc dba_part_tables;
 Name                        Null?     Type
 -----------------------     --------  ---------------------------------------------------------------
 OWNER                                        VARCHAR2(30)
 TABLE_NAME                                   VARCHAR2(30)
 PARTITIONING_TYPE                            VARCHAR2(9)
 SUBPARTITIONING_TYPE                         VARCHAR2(9)
 PARTITION_COUNT                              NUMBER
 DEF_SUBPARTITION_COUNT                       NUMBER
 PARTITIONING_KEY_COUNT                       NUMBER
 SUBPARTITIONING_KEY_COUNT                    NUMBER
 STATUS                                       VARCHAR2(8)
 DEF_TABLESPACE_NAME                          VARCHAR2(30)
 DEF_PCT_FREE                                 NUMBER
 DEF_PCT_USED                                 NUMBER
 DEF_INI_TRANS                                NUMBER
 DEF_MAX_TRANS                                NUMBER
 DEF_INITIAL_EXTENT                           VARCHAR2(40)
 DEF_NEXT_EXTENT                              VARCHAR2(40)
 DEF_MIN_EXTENTS                              VARCHAR2(40)
 DEF_MAX_EXTENTS                              VARCHAR2(40)
 DEF_MAX_SIZE                                 VARCHAR2(40)
 DEF_PCT_INCREASE                             VARCHAR2(40)
 DEF_FREELISTS                                NUMBER
 DEF_FREELIST_GROUPS                          NUMBER
 DEF_LOGGING                                  VARCHAR2(7)
 DEF_COMPRESSION                              VARCHAR2(8)
 DEF_COMPRESS_FOR                             VARCHAR2(12)
 DEF_BUFFER_POOL                              VARCHAR2(7)
 DEF_FLASH_CACHE                              VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                         VARCHAR2(7)
 REF_PTN_CONSTRAINT_NAME                      VARCHAR2(30)
 INTERVAL                                     VARCHAR2(1000)
 IS_NESTED                                    VARCHAR2(3)
 DEF_SEGMENT_CREATION                         VARCHAR2(4)

Check Default Attributes of Indexes

SQL> desc dba_part_indexes
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(30)
 INDEX_NAME                    NOT NULL VARCHAR2(30)
 TABLE_NAME                    NOT NULL VARCHAR2(30)
 PARTITIONING_TYPE                      VARCHAR2(9)
 SUBPARTITIONING_TYPE                   VARCHAR2(9)
 PARTITION_COUNT               NOT NULL NUMBER
 DEF_SUBPARTITION_COUNT                 NUMBER
 PARTITIONING_KEY_COUNT        NOT NULL NUMBER
 SUBPARTITIONING_KEY_COUNT              NUMBER
 LOCALITY                               VARCHAR2(6)
 ALIGNMENT                              VARCHAR2(12)
 DEF_TABLESPACE_NAME                    VARCHAR2(30)
 DEF_PCT_FREE                  NOT NULL NUMBER
 DEF_INI_TRANS                 NOT NULL NUMBER
 DEF_MAX_TRANS                 NOT NULL NUMBER
 DEF_INITIAL_EXTENT                     VARCHAR2(40)
 DEF_NEXT_EXTENT                        VARCHAR2(40)
 DEF_MIN_EXTENTS                        VARCHAR2(40)
 DEF_MAX_EXTENTS                        VARCHAR2(40)
 DEF_MAX_SIZE                           VARCHAR2(40)
 DEF_PCT_INCREASE                       VARCHAR2(40)
 DEF_FREELISTS                 NOT NULL NUMBER
 DEF_FREELIST_GROUPS           NOT NULL NUMBER
 DEF_LOGGING                            VARCHAR2(7)
 DEF_BUFFER_POOL                        VARCHAR2(7)
 DEF_FLASH_CACHE                        VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                   VARCHAR2(7)
 DEF_PARAMETERS                         VARCHAR2(1000)
 INTERVAL                               VARCHAR2(1000)
Posted in Partitioning | Tagged , , , , , , , , , , , , , | Leave a comment