Tracing SRVCTL Command From Shell

In a simple way I like show how to enable tracing for srvctl command from shell, other option is by using parameters

 
script /tmp/my_execution.log 
export SRVM_TRACE=true 
export SRVM_TRACE_LEVEL=2 
date;srvctl start instance -db WOLF -i WOLF1 
exit    #this exit is for script command

HTH – Antonio NAVARRO

Advertisements

AWR tool is not supported on Active Data Guard

I have recently been asked for performance reports from a database in ADG (Active Data Guard), the problem has been that AWRs are not available in this architecture until version 12.2 of Oracle. In my case this is a 12.1 so it can not be used.

As an alternative Oracle proposes to use adapted version of the statpack, known as standby statspack. For more information about this process you can see the note ML / MOS¬† “Installing and Using Standby Statspack (Doc ID 454848.1)”.

HTH – Antonio NAVARRO

Installing and Using OSWatcher (Release 8.1.2)

For those who do not know this tool, tell you that it is basically a shell script that collects a series of unix commands about the time dimension.

OSWatcher only use the next operating systems commands to recopile information;
VMSTAT
IOSTAT
MPSTAT
IFCONFIG
NETSTAT
TOP
TRACEROUTE

 

To install the latest version you must download it from ML / MOS, you need a license, even the most basic one.

To start it (in an easy way, many options are avaliables);

./startOSWbb.sh

To stop it;

./stopOSWbb.sh

To see the data;

Normally these traces are used to send information to Oracle, but you can see the data graphically with the following command

java -jar $OSWATCHER_HOME/oswbba.jar -i $OSWATCHER_HOME/archive

 

HTH – Antonio NAVARRO

 

 

DBMS_LOCK Must Be Declared In Swingbench

Today I was simulating workload with Swingbench (Version 2.5). I fan of this free tool it is a workload simulator for Oracle databases, of course, HammerDB is a worderful tool too. When executed the first workload I received lots of errors from event log into the Console. It says me that SOE.ORDERENTRY package body was invalid.

When I tryed compile it get the next error;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Advertencia: Cuerpo del paquete modificado con errores de compilación.

Transcurrido: 00:00:00.34
XXXXX>  show err
Errores para PACKAGE BODY SOE.ORDERENTRY:

LINE/COL ERROR
________ _________________________________________________________________
79/13    PL/SQL: Statement ignored
79/13    PLS-00201: identifier 'DBMS_LOCK' must be declared
82/13    PL/SQL: Statement ignored
82/13    PLS-00201: identifier 'DBMS_LOCK' must be declared

Of course, I usually never use the sys user to install anything, in my case I installed Swingbench with system user. The solution is so easy like

 

 
os11> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 17 16:06:38 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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

SQL>
SQL> grant execute on dbms_lock to soe;

Grant succeeded.

 

And recompile the package body again;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Cuerpo del paquete modificado.

Transcurrido: 00:00:00.42
XXXXX>  show err
No hay errores.

HTH – Antonio NAVARRO

 

Get Stats Preferences

In a previous post I showed the way to get the stats preferences for all database. Today I post the method for all levels (Table, Schema and Database);

TABLE Level:

 
-- Replace  OWNER_USER  by owner user
-- Replace  TABLE_NAME by table name
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'INCREMENTAL'),         
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'GRANULARITY'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'STALE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'ESTIMATE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'CASCADE')
FROM DUAL
/

SCHEMA Level:

   
-- Replace OWNER_USER  by owner user             
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'INCREMENTAL'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'GRANULARITY'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'STALE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'ESTIMATE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'CASCADE') 
FROM DUAL
/

DATABASE Level:

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'INCREMENTAL'),
  DBMS_STATS.get_prefs(pname=>'GRANULARITY'),
  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'CASCADE')
FROM DUAL
/

For all cases you can see histograms like show below;

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt   
FROM DUAL
/

HTH – Antonio NAVARRO

 

List Essential Performance Reports Scritps

Today I like list (and remember in some case) the basics performance reports for the more powerfull utilities in Oracle, ASH, ADDM and AWR. I put where it is and if is for RAC or single instance.

AWR Single instance

STA @$ORACLE_HOME/rdbms/admin/awrrpt.sql

AWR RAC

STA @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

 

ADDM Single instance

@STA $ORACLE_HOME/rdbms/admin/addmrpt.sql

ADDM RAC

@STA $ORACLE_HOME/rdbms/admin/addmrpti.sql

 

ASH Single instance

@STA $ORACLE_HOME/rdbms/admin/ashrpt.sql

ASH RAC

@STA $ORACLE_HOME/rdbms/admin/ashrpti.sql

HTH – Antonio NAVARRO

How To Install SQL Trace

In this post I want show how to install SQLT, this tool was designed by Carlos Sierra (https://carlos-sierra.net/) and Mauro Pagano (https://mauro-pagano.com/). It is a powerfull to optimize and analyze SQL.

 

The first of all you need download it from Oracle. Remember SQLT is free but you can need Tuning and/or Diagnostic license. Of course Mauro Pagano has developed other similar tool, and Carlos Sierra has implemented several tools with similar functions, but I will tald about it in other post.

After download and unzip the file connect as sysdba and execute the sqcreate.sql file

 
SQL> connect / as sysdba
SQL> @/users/anr/sqlt/install/sqcreate.sql

This is the output;

       
Ignore errors from here until @@@@@ marker as this is to test for NATIVE PLSQL Code Type
@@@@ marker . You may ignore prior errors about NATIVE PLSQL Code Type
old   1: ALTER SESSION SET PLSQL_CODE_TYPE = &&plsql_code_type
new   1: ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE

Session altered.

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD):                           

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN:
Re-enter password:

PL/SQL procedure successfully completed.

The next step is to choose the tablespaces to be used by SQLTXPLAIN

The Tablespace name is case sensitive.

Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?

Type YES or NO [Default NO]:                       
... please wait

TABLESPACE                     FREE_SPACE_MB
------------------------------ -------------

 * * * * * * * * * * * * 
    R E C O R T ED 
 * * * * * * * * * * * * 

Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]:

PL/SQL procedure successfully completed.

... please wait

TABLESPACE
------------------------------
TEMP_TBS

Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP_TBS

PL/SQL procedure successfully completed.

The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: CLOUD

PL/SQL procedure successfully completed.

SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: T

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

TADOBJ completed.

SQDOLD completed. Ignore errors from this script
  adding: 180216132555_01_sqcreate.log (deflated 96%)

SQCUSR completed. Some errors are expected.

Procedure created.

No errors.
  adding: 180216132956_02_sqcusr.log (deflated 95%)

TAUTLTEST completed.
  adding: 180216132959_09_tautltest.log (deflated 89%)

SQUTLTEST completed.
  adding: 180216132959_10_squtltest.log (deflated 90%)

no rows selected

TACOBJ completed.
  adding: 180216132959_03_tacobj.log (deflated 86%)

SQL> PRO Dropping Libraries for TRCA
Dropping Libraries for TRCA
SQL> SET TERM OFF;
tool_repository_schema: "SQLTXPLAIN"
tool_administer_schema: "SQLTXADMIN"
role_name: "SQLT_USER_ROLE"
Creating Procedures
Creating Package Specs TRCA$G
No errors.
Creating Package Specs TRCA$P
No errors.
Creating Package Specs TRCA$T
No errors.
Creating Package Specs TRCA$I
No errors.
Creating Package Specs TRCA$E
No errors.
Creating Package Specs TRCA$R
No errors.
Creating Package Specs TRCA$X
No errors.
Creating Views
Creating Package Body TRCA$G
No errors.
Creating Package Body TRCA$P
No errors.
Creating Package Body TRCA$T
No errors.
Creating Package Body TRCA$I
No errors.
Creating Package Body TRCA$E
No errors.
Creating Package Body TRCA$R

No errors.
Creating Package Body TRCA$X
No errors.
Creating Grants on Libraries

Tool Version
----------------
12.2.171004

Install Date
----------------
20180216

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID)      /XXXXXXXXXXXXXXX/CLOUD/trace
TRCA$INPUT2(VALID)      /XXXXXXXXXXXXXXX/CLOUD/trace
TRCA$STAGE(VALID)       /XXXXXXXXXXXXXXX/CLOUD/trace
user_dump_dest
background_dump_dest

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 12.1.14 2015/12/06 carlos.sierra mauro.pagano abel.macias@oracle.com
$ */

VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 12.1.14 2015/12/06 carlos.sierra mauro.pagano abel.macias@oracle.com
$ */

VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.5.8 2013/05/10 carlos.sierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 12.1.160429 2016/04/29 carlos.sierra abel.macias@oracle.com$ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
TACPKG completed.

PL/SQL procedure successfully completed.

  adding: 180216133013_04_tacpkg.log (deflated 80%)

SQCOBJ completed. Some errors are expected.
  adding: 180216133042_05_sqcobj.log (deflated 93%)

SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring, Automatic Workload Repository
(AWR) and SQL Tuning Sets (STS).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

pack_license: "T"
enable_tuning_pack_access

PL/SQL procedure successfully completed.

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key

connect_identifier: ""

PL/SQL procedure successfully completed.

Table truncated.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Procedure created.

No errors.

Table truncated.

PL/SQL procedure successfully completed.

Procedure dropped.

Commit complete.

SQSEED completed.
  adding: 180216133243_07_sqseed.log (deflated 80%)

... dropping packages for SQLT
... creating package specs for SQLT$A
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$a;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$C
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$c;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$D
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$d;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$E
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$e;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$H
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$h;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$I
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$i;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$M
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$m;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$R
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$r;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$S
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$s;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$T
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$t;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating views
... creating package body for SQLT$A
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$a;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$C
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$c;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$D
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$d;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$E
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$e;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$H
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$h;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$I
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$i;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$M

SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$m;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$R
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$r;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$S
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$s;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$T
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$t;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;

Creating Grants on Packages ...

LIBRARIES
----------------------------------------------------------------
VALID   PACKAGE      12.2.171004  OSQLT$A
VALID   PACKAGE      12.1.10       SQLT$C
VALID   PACKAGE      12.1.11       SQLT$D
VALID   PACKAGE      12.1.10       SQLT$E
VALID   PACKAGE      12.1.10       SQLT$H
VALID   PACKAGE      12.1.10       SQLT$I
VALID   PACKAGE      12.1.10       SQLT$M
VALID   PACKAGE      12.1.160429   SQLT$R
VALID   PACKAGE      12.1.10       SQLT$S
VALID   PACKAGE      12.1.10       SQLT$T
VALID   PACKAGE      11.4.5.0      TRCA$E
VALID   PACKAGE      11.4.5.0      TRCA$G
VALID   PACKAGE      11.4.5.0      TRCA$I
VALID   PACKAGE      11.4.5.0      TRCA$P
VALID   PACKAGE      11.4.5.0      TRCA$R
VALID   PACKAGE      11.4.5.0      TRCA$T
VALID   PACKAGE      11.4.5.0      TRCA$X
VALID   PACKAGE BODY 12.2.171004  OSQLT$A
VALID   PACKAGE BODY 12.1.14       SQLT$C
VALID   PACKAGE BODY 12.2.171004  OSQLT$D
VALID   PACKAGE BODY 12.1.160429   SQLT$E

LIBRARIES
----------------------------------------------------------------
VALID   PACKAGE BODY 12.1.160429   SQLT$H
VALID   PACKAGE BODY 12.2.171004  OSQLT$I
VALID   PACKAGE BODY 12.2.171004  OSQLT$M
VALID   PACKAGE BODY 12.2.171004  OSQLT$R
VALID   PACKAGE BODY 12.1.10       SQLT$S
VALID   PACKAGE BODY 12.2.171004  OSQLT$T
VALID   PACKAGE BODY 11.4.5.0      TRCA$E
VALID   PACKAGE BODY 12.1.14       TRCA$G
VALID   PACKAGE BODY 12.1.14       TRCA$I
VALID   PACKAGE BODY 11.4.5.8      TRCA$P
VALID   PACKAGE BODY 11.4.5.0      TRCA$R
VALID   PACKAGE BODY 12.1.160429   TRCA$T
VALID   PACKAGE BODY 11.4.5.0      TRCA$X

Deleting CBO statistics for SQLTXPLAIN objects ...                 

13:33:53    0 sqlt$a: -> delete_sqltxplain_stats
13:33:58    5 sqlt$a: 
SQL>

 

Ready for service.

HTH – Antonio NAVARRO