ORA-01013: user requested cancel of current operation

Following 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)”, the fololowing actions 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

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 7. But ASM is not using hugepages, while the normal instance is using hugepages. There are enough free hugepages available  as per /etc/meminfo.

Let’s configure ASM to use HugePages.

Subscribe to get access

Read more of this content when you subscribe today.

Install the AWS Command Line Interface on Microsoft Windows

There are two ways to install AWS CLI:

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.

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>

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

Database Size History Report From OEM

DBA is always being asked about the database size growth trend for coming months by a client or reporting manager.

If you can present the following report, that would show your great service.

Now lets’ demonstrate how to get similar report for maximum up to two years period of time from OEM repository.

Subscribe to get access

Read more of this content when you subscribe today.

TFA – Oracle Trace File Analyser

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         |
'----------+---------------+------------+---------+------------+----------------------+------------------'