How To Deinstall SQLTRACE

In this post I like show the correct way to remove the sqltrace schemas and procedures. First of all move to <location_for_sqltrace_packages>/install and connect / as sysdba to the database and execute @sqdrop.sql;

output for the command;

 

 
shone-1/cloud/prod/server102/ADM/SQLT/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jan 29 12:17:22 2018

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

... uninstalling SQLT, please wait

TADOBJ completed.

PL/SQL procedure successfully completed.

SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    my_count INTEGER;
  3
  4  BEGIN
  5    SELECT COUNT(*)
  6      INTO my_count
  7      FROM sys.dba_users
  8     WHERE username = 'TRCADMIN';
  9
 10    IF my_count = 0 THEN
 11      BEGIN
 12        EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
 13      EXCEPTION
 14        WHEN OTHERS THEN
 15          DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
 16      END;
 17
 18      FOR i IN (SELECT directory_name
 19                  FROM sys.dba_directories
 20                 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
 21      LOOP
 22        BEGIN
 23          EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
 24          DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
 25        EXCEPTION
 26          WHEN OTHERS THEN
 27            DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
 28        END;
 29      END LOOP;
 30    END IF;
 31  END;
 32  /
Dropped directory SQLT$STAGE.
Dropped directory TRCA$STAGE.
Dropped directory SQLT$UDUMP.
Dropped directory SQLT$BDUMP.
Dropped directory TRCA$INPUT1.
Dropped directory TRCA$INPUT2.

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER SQLTXADMIN CASCADE

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER SQLTXPLAIN CASCADE

User dropped.

SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE SQLT_USER_ROLE

Role dropped.

SQL>
SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.
SQL>
SQL>

 

HTH – Antonio NAVARRO

Advertisements

ORA-32004 And ORA-27102 After Increase Memory

Today I was increasing the Memory_target for a database when I get the next error;

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 12851347456

The problem looks clear, Not enough space, of course, in my case (using Solaris 11.3 with ZFS) this is a well know problem, if ZFS is on default it is very easy use all physical memory;

You can see the memory consume from root user executin the next command

echo ::memstat | mdb -k

The output is below, in this case the 56% of memroy is used by ZFS

Page Summary                 Pages             Bytes  %Tot
----------------- ----------------  ----------------  ----
Kernel                     1551129             11.8G   18%
Defdump prealloc            182104              1.3G    2%
ZFS Metadata                258844              1.9G    3%
ZFS File Data              4657165             35.5G   56%
Anon                        637853              4.8G    8%
Exec and libs                97157            759.0M    1%
Page cache                   57244            447.2M    1%
OSM                         469504              3.5G    6%
Free (cachelist)             14532            113.5M    0%
Free (freelist)             530445              4.0G    6%
Total                      8388608               64G

From ZFS Doc;

zfs_arc_max

Description
Determines the maximum size of the ZFS Adaptive Replacement Cache (ARC). However, see user_reserve_hint_pct.

Default
75% of memory on systems with less than 4 GB of memory

physmem minus 1 GB on systems with greater than 4 GB of memory

Free the memory in used by ZFS and limit it solve the problem.

 

HTH – Antonio NAVARRO

 

How To Purge A SQL In 10.2.0.4 And 10.2.0.5

Today I was purge a SQL in an old 10.2.0.5 database, I used the DBMS_SHARED_POOL, but it don’t work. Look for notes in ML/MOS I find in this version there is a bug, you can workaround it by set the event 5614566.

I posted a entry some time ago about how to purge simple sql from buffer cache, you can see it following the nex link;

https://sql1.wordpress.com/2014/03/20/get-out-of-here/

Below I show an example using this event;

 
SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         1

1 fila seleccionada.

SBBD*ANTO> alter session set events '5614566 trace name context forever';

Sesión modificada.

SBBD*ANTO> exec SYS.DBMS_SHARED_POOL.PURGE ('0000000507C52778,720914062', 'S');

Procedimiento PL/SQL terminado correctamente.

SBBD*ANTO> SELECT COUNT (*) FROM V$SQL WHERE SQL_ID ='069zdawgddf0y';

  COUNT(*)
__________
         0

 

HTH – Antonio NAVARRO

 

Better To Move Than To Copy

Yesterday a person was copying a file, in order to change location, in this case it is a unix system, but in windows it happens the same. In this example we are going to see copy times (cp command) versus move (mv command), to see the difference. This post is designed for people who start and do not know very well the difference between copy and move. For example I have used the compressed oracle engine, but for large files you can go the time from minutes to hours.

Copying the file to another path, we see that in this case it takes almost 18 seconds;

oracle12@mortages-1:~/soft_12c_sparc$ time cp solaris.sparc64_12102_database_1of2.zip ./soft_12c_sparc

real    0m17.82s
user    0m0.00s
sys     0m4.67s

Move the same file to the same path, we see that the time is 0 seconds;

oracle12@mortages-1:~/soft_12c_sparc$ time mv solaris.sparc64_12102_database_1of2.zip ./soft_12c_sparc

real    0m0.00s
user    0m0.00s
sys     0m0.00s

The explanation is simple. The cp moves the file physically (from the first byte to the last) to the new location. With mv what we do is leave the file where it is and change in index of directories (at OS level) the logical direction that we see.

HTH – Antonio NAVARRO

How To Trace A DBlink Connection (Local And Remote)

In many occasions we need to trace a session, possibly the most used in Oracle is 10046, this is a powerfull tool to see the execution times, but we usually use it on a single server.

Today I want to show a little trick to trace when we use a db link between two servers and generate this trace (can be used with others) in the two servers, the local and the remote. This can be usefull for example for snapshots between different databases.

In the remote server we create the following procedure within the user schema with which we are going to connect through the database link

 

REM *************************************
REM  You need create procedure privilege 
REM *************************************
CREATE OR REPLACE PROCEDURE set_on_trace
AS
BEGIN 

   EXECUTE IMMEDIATE 'alter session set TRACEFILE_IDENTIFIER = ''MY_TRACE_ANR'' '; 
   EXECUTE IMMEDIATE 'alter session set MAX_DUMP_FILE_SIZE=unlimited'; 
   EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12'' '; 

END; 
/

On the local server, where we are going to execute the call to the database link

 
alter session set STATISTICS_LEVEL=ALL; 
alter session set events '10046 trace name context forever, level 12';

alter session set TRACEFILE_IDENTIFIER = 'MY_TRACE_ANR'; 
exec set_on_trace@my_database_link
select count (*) from siebel.orders@my_database_link;

 

HTH – Antonio NAVARRO

How To Find Out If TFA Is Installed And If So Which Version

TFA (Trace File Analyzer) is a very useful tool, mainly used by Oracle support, although we can always use it to extract information from a system. I also recommend taking a look at the information it contains. I am currently updating to the latest version of TFA so I post this post as an aid for those who do not know and also as a reminder for me.

To know if it is installed (in this case Solaris 11, varies depending on the platform);

Grep TFA_HOME = /etc/init.d/init.tfa

He would return something similar to this;

TFA_HOME = / app / oracle / grid / 121 / tfa / node-1 / tfa_home

If it is installed, then to know what version we have;

Export TFA_HOME = / app / oracle / grid / 121 / tfa / node-1 / tfa_home
$ TFA_HOME / bin / tfactl print status

I will return something like this


.-----------------------------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+----------+---------------+------+------+------------+----------------------+------------------+
| node-1 | RUNNING | 1834 | 5000 | 12.1.2.7.0 | 12127020160304140533 | COMPLETE |
| node-2 | RUNNING | 1810 | 5000 | 12.1.2.7.0 | 12127020160304140533 | COMPLETE |
'----------+---------------+------+------+------------+----------------------+------------------'

HTH – Antonio NAVARRO

 

How To Put Comments Using Spfile

More than once I have been asked, co-workers, or people in a forum if you can put comments on an SPFILE. In the old times it was very usual to comment when changing a parameter (with the date of the change and the old value in case there were performance problems) or when you put some new parameter. Seeing these changes was easy, since the init.ora is at the end a text file. The problem we can have with SPFILE since this is a bianrio file, ofcourse to you can make a strings to SPFILE and see some things. The correct thing is to dump SPFILE to a text file.

So, to put a comment in SPFILE the same alter sentence allows us with the comment clause, look at the example below;

alter system set “optimizer_adaptive_features”=true comment =’21.06.2017 Changed by Antonio NAVARRO’ scope=both sid=’*’;

HTH – Antonio NAVARRO