Tracing A Schema By Using Triggers

Many times we trace user sessions and processes on live, but sometimes we need to trace all time or for periods of time, all connections of a scheme. The solution could be to create a trigger “AFTER LOGON ON DATABASE”, but in large environments is costly (disk space, cpu usage)and can be almost impossible. Oracle provides a solution with “AFTER LOGON ON SCHEMA” event.

 

In the example below we can to trace all connections from SIEBEL user, for example during the weekend, without losing any trace. The code in this case was created from user SYS with this permits to avoid grant to the user SIEBEL the CREATE TRIGGER privilege. If running on RAC traces are in DUMP_DEST or on corresponding location if we use diagnostic_dest.

 

 ---- TRACE FOR SIEBEL SCHEMA ---
CREATE OR REPLACE TRIGGER SIEBEL.TRACE_ON
AFTER LOGON ON SIEBEL.schema
BEGIN
    EXECUTE IMMEDIATE 'alter session set statistics_level=ALL'; 
    EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END TRACE_ON;
/

 

HTH – Antonio NAVARRO

 

Advertisements

Permission Of Update For A Specific Column

Development team requested permission to update on a table. This table has very sensitive information, apart it need the appropriate authorization, and after talking with the development team is determined to need only modify two columns of this table. So I propose to give them updat privilege only on these two columns. The person from development put a strange face and told me; “I ignored that could be given access to the columns, we always request access on the entire table”.

Often as easy or simple for all parties is to give access to the entire table, but this is not a good rule of security. It is also true that there must be a security (often this task is transfered to the DBA) but for the administrator of the database will not be easy to identify which columns have sensitive data and what not, especially in large environments. DBA is the responsibility of the management of the data, but no knowledge thereof.

The following command gives the OK to select the columns col1 and col4 test table;

 

 -- desde el usuario propietario de la tabla
grant UPDATE (COL2, COL4) 
ON PROBE2 
TO SCOTT;

 
If we try to access another column in the table that we have permission we will return error;

 

ORA-01031: privilegios insuficientes

 
Other ways are using VPD, VIEWS (Simple views allow update them).

HTH – Antonio NAVARRO

 

ORA-02020 “Too many database links in use”

Today we have been reported this error, if you execute “oerr ora 2020” command at unix prompt you can see the solution proposed By Oracle;

 

// * Action: Increase the open_links limit, or free up some open links by
// Committing or rolling back the transaction and canceling open
// Cursors That reference remote databases.

 

 

Normally, these problems are the management of database links, you must to close them explicity . The command is;

 

alter session close database link <DBLINK-NAME>

 

After running the command, usually need to close the transaction (commit or rollback). On the other hand when performed commit or rollback in the session that has opened a database link, remains the session, but closes when it opens another database link.

 

One more thing, to see the database links opened you can use the next statement.

 

 SELECT db_link, in_transaction FROM v$dblink

 

HTH – Antonio NAVARRO

 

12.1.0.2.0 Is Here

Oracle has announced that it is available the new Release of 12c, exactly 12.1.0.2.0, for now only seems to be the Enterprise Edition, for the Standard Edition and Standard Edition One we will have to wait. On Linux and Solaris platforms.

 
The most significant and it can be pretty interesting is about performance, the new feature In-Memory Database. This is possibly the feature more in vogue in the world of databases. Microsoft introduced to market SQL Server 2014, this version to include In-Memory OLTP Engine.

 

You can download it at OTN, follow this link

 

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

 

I hope you enjoy it.

HTH – Antonio NAVARRO

 

RMAN-08120 Error

There is a Rman process that delete archives  applied to a DataGuard instance. Has begun to appear this error, always with the last one sent you the parent instance. This never happened before and had been going on since a couple of days, when the unix box,  primary and the standby went shutdown. Of course,  I could occur in a point in time, when I run cron erasing some of the archives could be an archive just arrived. but  this is controlled in this case only archives are generated in primary in a given time and only the archives are deleted on standby at another time than the first, which should reduce the possibility of collision to zero.

 

Researching My Oracle Support (formerly Metalink),  I’ve only seen the note 1369630.1, ‘APPLIED’-Column not updated if Heartbeat-ARCH hangs, is what more similar to my problem, but it’s not the same, besides the database version was 11.2.0.2, so it would be out  of this BUG

 

‘APPLIED’-Column not updated if Heartbeat hangs-ARCH (Doc ID 1369630.1)

 

Executing the following select

 SELECT
  NAME,
  'Applied:' || to_char (next_time, 'dd-Mon-yy:hh24:mi:ss') Time
FROM
  V$ARCHIVED_LOG

 

It indicates that the archive is applied. So what is happening?

 

Examining the last bounce, I see is in real-time recovery, this is done with the command;

 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

 

Looking at the documentation (V10.2);

 

By default, the redo data is applied from archived redo log files. When performing Redo Apply, a physical standby database can use the real-time apply feature to apply redo from the standby Directly redo log files are being written Peak Gust by the RFS process.

 

After see “they are being written”, I think that as it will receive will apply redo vectors, maybe it could not know if it’s really the end (???) or may have to write more or RFS process had some  pointer to archive file … The archive appears really like  applied in the V$ARCHIVED_LOG, but RMAN does not delete because the think is necessary. To check this reasoning I stop the recovery mode in real-time and start the classic, using the command;

 

recovery mode in real time and get the classic, using the command;

 

This should be applied when the archive is “complete”. I run the cron and it works perfectly.

 

HTH – Antonio NAVARRO

 

 

 

 

 

 

 

 

Reserved Words

Today I have been reported a error by development team. when to create a table in a development environment they received the following error

 

ERROR at line 1:
ORA-00903: invalid table name

 
The error is relatively clear, in this case has the table name as a word of the SQL language. I was askedn if there is a list, website, manual, etc..  where see  the reserved words of language. In Oracle database the issue is relatively easy,  because there is a view named $RESERVED_WORDS that gives the list all language elements that are reserved and can not be used (though there is in some cases it did). Of course,  you need to give more privilege level to access v$ views for some users.
On the 12c version, this table includes a new column named  CON_ID to indicate use in NON-CBD or CBD.  For more information, refer to Oracle Documentation.

 

HTH – Antonio NAVARRO

Estimate Index Size

Many times when we have to calculate an index size, or should calculate the space we need because we could having to extend the tablespace, of course, you can always run the create index command and if it fails to add more datafiles and reexecute. In this post we will remember that Oracle has a tool to help with this issue, using the DBMS_SPACE  package.

We just pass the create command “CREATE INDEX ON TAB1_INX TAB1 (COL1)”

 
Run from sqlplus

 

SET SERVEROUTPUT ON SIZE UNLIMITED
VARIABLE A_Bytes NUMBER
VARIABLE E_Bytes NUMBER
exec dbms_space.create_index_cost( ‘CREATE INDEX TAB1_INX ON TAB1 (COL1)’, –
:A_Bytes, :E_Bytes );
print E_Bytes

 

Very important (From documentation)

  • The table on which the index is created must already exist.
  • The computation of the index size depends on statistics gathered on the segment.
  • It is imperative that the table must have been analyzed recently.
  • In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.

 

HTH – Antonio NAVARRO