SCN HEADROOM

To explain that the SCN HEADROON, we must first explain the SCN. The SCN is a sequence of life of database or as many authors says a Oracle’s clock, that increases every second or every time a commit occurs. This sequence is continuously increasing, however, how far can it increase?, this value is usually determined by power form 2exp42. This is a limit which has the database and would be of the order of trillions. The SCN HEADROON is defined as the space between the current SCN and this limit.

 
HTH – Antonio NAVARRO

 

How To Get The Roles, Privileges And Permissions For A User

Normally to see what permissions have a user in the database usually run the following queries.

 

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘ANR’;
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘ANR’;
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘ANR’;

 

The first query gives us the associated roles, the second gives us the  system privileges and with the third we get the access and permissions on objects.  Now if what we want are the DDL we can get them in a simple way, using  the DBMS_METADATA  package, respectively;

 

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’, ‘ANR’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’ANR’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’ANR’) FROM DUAL;

 

Summarizing;

DBMS_METADATA.GET_GRANTED_DDL (PARAM1, PARAM2)

PARAM1

  • ROLE_GRANT for roles
  • SYSTEM_GRANT for privileges
  • OBJECT_GRANT for permissions

PARAM2

  • The user

 

HTH – Antonio NAVARRO

 

Check Dispatcher

Often in architecture MTS (Multi Thread Server) we need check a specific dispatcher is running so correct. The first thing to do is identify where each dispatcher listens,we can do this with following query

select name, network from v$dispatcher;

 

It will return the name of all active ports where dispatchers are listening;

……

D070 (ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65288))
D071 (ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65289))
D072 (ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65290))
D073 (ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65291))
D074 (ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65292))

75 rows selected.

 

The next thing we should do is see if we arrived, we will use the tnsping utility;

tnsping (ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65286))

 

The last thing to make sure it works properly is to make a connection to the database;

sqlplus USER/PASSWORD@(ADDRESS=(PROTOCOL=tcp)(HOST=dante)(PORT=65286))

 

HTH – Antonio NAVARRO

The DeWitt Clause

Many people don’t know the DeWitt clause and what is its function or purpose. But when licensing policies are accepted, some database providers such as Oracle, Microsoft, to name some of the most known, including the DeWitt clause.

David DeWitt is a university professor that in the eighties to make a benchmark of a database and public the results, this not like to the provider and to court the teacher. Since then many companies including in the license DeWitt clause, which prohibits making benchmarks of their products and make them public. One more thing, the company that took dewitt trial was oracle.

More information at this link;

http://en.wikipedia.org/wiki/David_DeWitt

 

HTH – Antonio NAVARRO

 

Truncate Table Solve Unusable Indexes

The usual way to solve an index that has been unusable state is to perform a rebuild of the index. Another less common way is to perform a truncate of the table that is associated index, of course,  we can not always truncate table.

An example;

 

TEST*ANR>  create table anr222 as select * from dba_objects;

Tabla creada.

 

TEST*ANR> create index anr222_inx on anr222 (object_id);

Índice creado.

 

TEST*ANR> select status from dba_indexes where index_name =’ANR222_INX’;

STATUS

________

VALID

 

TEST*ANR> SELECT COUNT (*) FROM  anr222;

COUNT(*)

__________

59168

 

TEST*ANR> ALTER INDEX ANR222_INX UNUSABLE;

Índice modificado.

 

TEST*ANR>  select status from dba_indexes where index_name =’ANR222_INX’;

STATUS

________

UNUSABLE

 

TEST*ANR> TRUNCATE TABLE anr222;

Tabla truncada.

 

TEST*ANR> SELECT COUNT (*) FROM  anr222;

COUNT(*)

__________

0

 

TEST*ANR> select status from dba_indexes where index_name =’ANR222_INX’;

STATUS

________

VALID

 

 

HTH – Antonio NAVARRO

 

 

RMAN-20242: specification does not match

Restoring archives from tape occurs the following error;

 

Starting restore at 31-MAR-14
released channel: tape2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/31/2014 12:52:44
RMAN-20242: specification does not match any archive log in the recovery catalog

 

The problem is that an archive not have been  inventoried and subsequent in this copy of controlfile. In this case the problem came because  I’m using a controlfile like catalog which is an old copy and has not cataloged all the archives, the solution has been to change the controlfile for a copy of current controlfile, where are catalog the files that I need. Perhaps the best solution would be to have one recovery catalog where always be updated more.

 

HTH – Antonio NAVARRO