How To Know If Email Server For DBMS_SCHEDULER Is Configured

Yesterday I had a question at work and it was if the dbms_scheduler had configured the mail server, as I did not remember how it looked I had to reviewed to documenting, is a very simple command but I leave it here, partly if it suits someone, and partly because as I said in other times I use the blog as a database of knowledge of problems and doubts that appear in my daily life.

 

 
declare 
 IsConfiguredServer   VARCHAR2(64); 
 IsConfiguredSender   VARCHAR2(64); 
 BEGIN 
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', IsConfiguredServer); 
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', IsConfiguredSender); 
 DBMS_OUTPUT.PUT_LINE('server: ' || IsConfiguredServer); 
 DBMS_OUTPUT.PUT_LINE('sender: ' || IsConfiguredSender); 
END; 
/ 

 

HTH – Antonio NAVARRO

 

Example Of Execute SQL Advisor

I like show a basic example (valid only for a single sql_id) of how to execute the SQL ADVISOR from command like. If you prefer a GUI interface you can use EM Console or SQL Developer.

 

i) Create the task but before explain;

time_limit: maximum duration in seconds for the tuning session

The scope:

LIMITED

SQL Tuning Advisor produces recommendations based on statistical checks, access path analysis, and SQL structure analysis. SQL profile recommendations are not generated.

COMPREHENSIVE

SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL profiling.

 

DECLARE
  Output  VARCHAR2(100);
BEGIN
  Output := DBMS_SQLTUNE.create_tuning_task (
            sql_id      => '9v64r4s0n7znc',
            scope       => DBMS_SQLTUNE.scope_comprehensive,
            time_limit  => 500,
            task_name   => 'TAREA ANR 0x0000F2',
            description => 'Anr task for 9v64r4s0n7znc');
  DBMS_OUTPUT.put_line('Task created: ' || Output);
END;
/

 

ii) Execute the task;

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME => 'TAREA ANR 0x0000F2'); 

iii) Show the report;

SET LINESIZE 100
SET LONG 5000 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('TAREA ANR 0x0000F2') FROM DUAL; 

For more information, please refer to the doc at the next link;

SQL ADVISOR Documentation

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

An Easy Way To View System Users With A Default Password

Today I return to work after a few days of vacation. I am currently installing a couple of new engines and creating their corresponding databases and I would like to remember that we should not leave the keys by default, for this we have the view DBA_USERS_WITH_DEFPWD, which tells us that users have the default pass. This is for Oracle’s own users. For the rest of users there are other ways to verify it although we should have key policies, such as a minimum number of alphanumeric, capital letters, etc. Aside from having the keys expire from time to time is a good idea.

Here I leave an example of a newly created database and in which only the pass for the user SYS and SYSTEM has been changed.

 
MODB> column product format a50
MODB> column username format a25   
MODB> select * from dba_users_with_defpwd

USERNAME                  PRODUCT
_________________________ __________________________________________________
DIP                       ø
GSMADMIN_INTERNAL         ø
ORACLE_OCM                ø
APPQOSSYS                 ø
MDDATA                    ø
GSMCATUSER                ø
SPATIAL_WFS_ADMIN_USR     ø
SYSDG                     ø
WMSYS                     ø
SYSKM                     ø
SI_INFORMTN_SCHEMA        ø
SYSMAN                    ø
TSMSYS                    ø
OUTLN                     ø
ORDPLUGINS                ø
OLAPSYS                   ø
ORDSYS                    ø
MDSYS                     ø
SYSBACKUP                 ø
GSMUSER                   ø
SPATIAL_CSW_ADMIN_USR     ø
OJVMSYS                   ø
ORDDATA                   ø

23 rows selected.

HTH – Antonio NAVARRO

Connect String Specific For An Instance (RAC)

In cluster environments, if we connect using connect string or JDBC, we usually connect to a public address that leads us to some of the nodes in the cluster. Sometimes We need to go to a particular node, from applications (of course, you can do this by defining preferred affinity and services) or as in my case for administration issues, where You need to work on a particular node.

Since version 11, if I am not wrong, the connect string includes the parameter instance_name, an example we can see below for a cluster of three nodes, with this we can go directly to the node we need.

sap_1.domain.es =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.public.com)(PORT = 5671))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = SAP)
    (INSTANCE_NAME=SAP_1)
  )
)

sap_2.domain.es =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.public.com)(PORT = 5671))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = SAP)
    (INSTANCE_NAME=SAP_2)
  )
)

sap_3.domain.es =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.public.com)(PORT = 5671))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = SAP)
    (INSTANCE_NAME=SAP_3)
  )
)

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

 

ORA-02065 Changing Max_Dump_File_Size Parameter

 

Yesterday I was generating some traces (1046) in a database in version 10g, when I went to see the dump, in row mode I appeared the hateful message

*** DUMP FILE SIZE IS LIMITED TO 25722880 BYTES ***

Of course, it’s my fault for not looking at it previously, to solve it you can extend it at the session or system level. In my case as I was tracing several sessions, I would like to change it to the system level, so I run;

Alter system set max_dump_file_size = 100m;

This gave me back the following error

ORA-02065: illegal option for ALTER SYSTEM

Doing some memory remember that it is a string of text and has to go in quotes, as in the following example;

alter system set max_dump_file_size= ‘100m’;

Better.
HTH – Antonio NAVARRO