How To See CRs And Remove Them (Sybase)

I have a problem with CR (windows format) characters in a Sybase IQ system because of this I like show how to see them. A way is use convert function with binary. PleaseĀ  look at the next query;

SELECT convert(binary(10), COLUMN_N) FROM MY_TABLE

This return the 10 first bytes of the column COLUMN_N in hexadecimal format;

0x20200d20486f6c61206d

0x is the prefix. 20 is a blank space, the next 20 is other blank space and 0dĀ  is the CR.

If you want remove it (in my case was necessary for app requisites) you can use the next update;

update MY_TABLE set COLUMN_ n = str_replace(COLUMN_n, char(13), null)

HTH – Antonio NAVARRO

Advertisements

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

 

 

 

Where Dump Files TFA?

Many times We need use TFA to collect information. In this post I want show how to locate the directory where TFA dump the recollected information.

From tfactl, with print repository command, in this case is a RAC two-node, like show below;

tfactl> print repository
.-------------------------------------------------.
|                     motif-2                     |
+----------------------+--------------------------+
| Repository Parameter | Value                    |
+----------------------+--------------------------+
| Location             | /app/grid/tfa/repository |
| Maximum Size (MB)    | 10240                    |
| Current Size (MB)    | 0                        |
| Free Size (MB)       | 10240                    |
| Status               | OPEN                     |
'----------------------+--------------------------'

.-------------------------------------------------.
|                     motif-1                     |
+----------------------+--------------------------+
| Repository Parameter | Value                    |
+----------------------+--------------------------+
| Location             | /app/grid/tfa/repository |
| Maximum Size (MB)    | 10240                    |
| Current Size (MB)    | 654                      |
| Free Size (MB)       | 9586                     |
| Status               | OPEN                     |
'----------------------+--------------------------'

HTH – Antonio NAVARRO

 

Essential Maintance Of MGMTDB

Today I want to talk about the most basic maintenance we can do of the MGMTDB (MGMTDB is new database instance which is used for storing Cluster Health Monitor (CHM) data). Let’s see the minimum commands that we must keep in mind to work with this database;

To check database;

srvctl status mgmtdb

To start database;

srvctl start mgmtdb [-startoption start_options] [-node node_name]

To set up database;

srvctl config mgmtdb

Like an Oracle database, it has its own listener, and you can perform the same operations, for example (from the owner of the GRID);

lsnrctl status MGMTLSNR

To connect to the database with sqlplus we must first point to the ORACLE_SID (from the owner of the GRID);

export ORACLE_SID=-MGMTDB
sqlplus “/ as sysdba”

HTH – Antonio NAVARRO

 

Solaris 11.4 (Beta)

Oracle has released a fresh beta of his famous operating system Solaris. This is the last release of this OS. There are many (very importants) persons says that this release (11) would be the last because of Oracle is planning abandoned it in favour of Oracle Linux. Will see we a 12 release?.

If you want try it, please click in the next link;

Solaris 11.4 Beta download

HTH – Antonio NAVARRO

A Easy Way Of Configure Timezone In Databases From Unix

This morning talking with a partner raised the issue of having several databases at different timeszones in the same Unix machine. There are many ways to do this, some more correct than others. In this case I want show an example using the operating system (in this example I use a Solaris 11.3) this will make us independent of the database engine (Oracle, Sybase, SQL Server on linux) that we use, although all the databases have a mechanism for perform this function from inside the engine of database and that is a mandatory feature of the ISO 9075 standard (or ANSI SQL for the USA).

In Solaris by default, if we do not specify anything, when we create a user and we do not tell him anything he will use the machine, we can see it in the /etc/TIMEZONE file

 
Ghost> cat /etc/TIMEZONE
#
# Copyright 1992, 1999-2002 Sun Microsystems, Inc.  All rights reserved.
# Use is subject to license terms.
#
#ident  "@(#)init.dfl   1.7     02/12/03 SMI"
#
# This file is /etc/default/init.  /etc/TIMEZONE is a symlink to this file.
# This file looks like a shell script, but it is not.  To maintain
# compatibility with old versions of /etc/TIMEZONE, some shell constructs
# (i.e., export commands) are allowed in this file, but are ignored.
#
# Lines of this file should be of the form VAR=value, where VAR is one of
# TZ, LANG, CMASK, or any of the LC_* environment variables.  value may
# be enclosed in double quotes (") or single quotes (').
#
TZ=UTC
CMASK=022

 

If we execute a date at the level of the prompt;

Ghost> date
Tue Feb 13 16:14:00 UTC 2018

 

If we login to the same machine but with another user and we see his file .profile where we have defined the variable TZ;

Ghost> cat .profile

export TZ=Europe/Madrid

If we execute a date at the level of the prompt;

Ghost> date
Tue Feb 13 17:15:17 CET 2018

 

Once we have defined the variable TZ we can create/start the database and it must take this time.

HTH – Antonio NAVARRO