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

 

How Set To Broken Jobs From Other Users

Of course, you should not use dbms_job any more, but how many times are we working on an installation or a database that already has them? It would be best to migrate these jobs to dbms_scheduler. In this case I will post a small script to put all jobs to broken, without having to log in as the owner of the same.

 
SET SERVEROUTPUT ON

DECLARE
   UserID        NUMBER;
   Sentence      VARCHAR2 (500);
   Result        PLS_INTEGER;
   HandleCursor  PLS_INTEGER;
   debug         BOOLEAN := FALSE;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);

  FOR i IN (SELECT JOB, SCHEMA_USER FROM DBA_JOBS WHERE BROKEN != 'Y') LOOP
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (JOB)         : ' || I.JOB);  end if;
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (SCHEMA_USER) : ' || I.SCHEMA_USER);  end if;

      SELECT USER_ID INTO UserId FROM DBA_USERS WHERE USERNAME = RTRIM (LTRIM (i.SCHEMA_USER));
      Sentence := 'BEGIN DBMS_JOB.BROKEN (' || i.JOB ||', TRUE); END; ';

      HandleCursor := sys.dbms_sys_sql.open_cursor ();
      sys.dbms_sys_sql.parse_as_user (HandleCursor, Sentence, dbms_sql.native, UserId);
      Result := sys.dbms_sys_sql.execute (HandleCursor);
      sys.dbms_sys_sql.close_cursor(HandleCursor);

      DBMS_OUTPUT.PUT_LINE ('Executing :   ' || Sentence);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE (CHR (10) || 'Please, remember execute commit to close the actual transaction...');
END;
/

The trick here is to make the parseo as the owner user.

HTH – Antonio NAVARRO

ORA-31617 Error When Executing Expdp

Today I get the next error when executiong expdp in a RAC;


ORA-31693: Table data object "FOLE"."HISTI_MOV_QCATCH" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-31617: unable to open dump file "/mntfc/FLSHPRD070317Dop3_05.dmp" for write
ORA-19505: failed to identify file "/mntwfc/FLSHPRD070317Dop3_05.dmp"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory

After of search on ML/MOS I have discovered a bug that was the problem. In this version 11.2.0.1, when execute expdp with parallel option, parallel is executed from all actives instances in the cluster. I was trying execute from only one node and I have NFS (Network File System) mount in this node.

Solutions

1- Set cluster_database=false (From doc.) I prefer if is possible stop the other instances.

2- Mount the NFS in all nodes of the cluster, (This was my solution).

HTH – Antonio NAVARRO

How To Install Statspack On 9i Windows-box

Today I have been installing a statspack package for a older database, version 9i, maybe it is not relevant for many people but I want to post it in this entry because of I use the blog like a work diary and documentation place.

First of all we need to create a new tablespace where create the new objects for perfstat user, of course, you can use an existing tablepaspace but it is not a good idea;

 


CREATE TABLESPACE "ESTADISTICAS" DATAFILE
'X:\ORACLE\DATOS\ESTADISTICAS.DBF' SIZE 2147483648 -- 2 Gigas
LOGGING ONLINE PERMANENT BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

After we need the spcreate.sql scritp, locate on %ORACLE_HOME%\rdbms\admin

foto1

Connect to database as sysdba and execute de sql;

foto2

This sql script call the following scripts;

  • SPCUSR.SQL: Creates the user and grants privileges
  • SPCTAB.SQL: Creates the tables
  • SPCPKG.SQL: Creates the package

Ask me for the password for user perfstat (in clear text);

foto3.PNG

Next ask me for tablespace where locate tables with the data are recolected from the system. You must write down the tablespace name create before. You need a temporary tablespace too;

foto5.PNG

if all is okay you get a reply similar to the next window, however it is necessary to review the spcpkg.lis file for any errors;

foto7.PNG

Now you must to schedule the job to pick the statistics, with the spauto it pick each hour;

foto7

And the output;

foto8

HTH – Antonio NAVARRO

 

How To See Active Mviews

This is other way, more interesnting in my opinion, to see active mviews, I use it from many years, I don’t remember where I get it, maybe ML/MOS, of course, you can see it as usual by query the dba_jobs_running

 

REM
REM Active Mviews Refresh
REM 

SELECT 
   S.OBJ#,
   O.OBJ#,
   S.CONTAINEROBJ#,
   LASTREFRESHDATE,
   PFLAGS,
   XPFLAGS,
   O.NAME,
   O.OWNER#, 
   BITAND(S.MFLAGS, 8) 
FROM 
   OBJ$ O, 
   SUM$ S
WHERE 
   O.OBJ# = S.OBJ# AND 
   O.TYPE# = 42 AND 
   BITAND(S.MFLAGS, 8) = 8;                                                         
/

HTH – Antonio NAVARRO