ORACLE DBA TIPS & TRICKS

ORACLE DBA TIPS & TRICKS:

1.Block User with IP address from production server.

its a based on trigger.

SQL> CREATE OR REPLACE TRIGGER BLOCK_USER
AFTER LOGON
ON DATABASE
declare
adr1 varchar2(100);
usr1 varchar2(30);
begin

select trim(sys_context('userenv','ip_address')) into adr1 from dual;

if adr1=' 10.0.0.1' /*** any IP Adress ***/
then
RAISE_APPLICATION_ERROR(-20000, 'You are not Authoried to Access Database');

end if;

end;
/


Then try to connect with restricted IP address.


If need to disable this trigger the run >>>>>

SQL>ALTER TRIGGER BLOCK_USER DISABLE;



2. Block third party /Development tools from your production database .

Like "TOAD,SQL EDITOR,pl/sql developers,Excel Etc.


Step> CREATE OR REPLACE TRIGGER BLOCK_TOOLS_FROM_PROD
AFTER LOGON ON DATABASE
DECLARE
v_prog v$session.program%TYPE;

BEGIN

SELECT program INTO v_prog
FROM v$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

--EXCEPTION WHEN NO_DATA_FOUND THEN

IF (v_prog) LIKE 'toad.exe'-- OR --UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad --or put the perameters " name /PLSQLDEV.exe/TOAD.exe/"which you are need to restricted

THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/


If you Need disable this trigger the>>

SQL>ALTER TRIGGER BLOCK_TOOLS_FROM_PROD DISABLE;

Resolved ORACLE Error "ORA-12514"

This is an oracle error "ORA-12514"
this error rise several things on this one of them are resolved

if windows SQL>set ORACLE_SID=DBNAME
linux SQL>export ORACLE_SID=DBNAME
SQL> conn scott/tiger
connected
SQL>conn scott/tiger@tnsname
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

Many Solution over here..

1. check database service is up
2. tnsnames.ora file are db name is correctly input.
3.
SQL>show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl.oracleworld.com

if appear this output

then edit the tnsnames.ora

Before :

orcl =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.20.7.50)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.oracleworld.com ) <<<<--------------this line is edited.
)
)

After :

orcl =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.20.7.50)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME= orcl.oracleworld.com ) <<<<--------------this line is edited.
)
)

Then reconnect the database.