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.

Dynamically SGA increase ORACLE 10G Database

Step 1:
Login in
>export ORACLE_SID=test

>sqlplus /nolog

> conn / as sysdba or conn sys/password as sysdba


>ALTER SYSTEM SET sga_max_size = 1711276032 COMMENT='internally adjusted' SCOPE=SPFILE;

>ALTER SYSTEM SET sga_target = 1711276032 SCOPE=BOTH;



Step 2:

>shutdown immediate

>startup

>show sga


Finally you can see your expected SGA size

Identified Every day Database Growth Size

Step 1: Create table

CREATE TABLE DB_GROWTH

(

DAY DATE,

DATABASE_SIZE_MB NUMBER,

DAILY_GROWTH_MB NUMBER

);

Step 2:

Create Store procedure , which generated automatic database growth size

CREATE OR REPLACE PROCEDURE database_growth

AS

today_size NUMBER;

yesterday_size NUMBER;

growth_size NUMBER;

cnt NUMBER;

BEGIN

SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED where tablespace_name <> 'UNDOTBS2';

SELECT COUNT(1) INTO cnt FROM db_growth ;

IF cnt > 0

THEN

SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(day,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');

ELSE

yesterday_size:=today_size;

END IF;

growth_size := today_size - yesterday_size;

INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);

EXCEPTION

WHEN no_data_found THEN

INSERT INTO db_growth VALUES(sysdate,today_size,0);

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Step 3:

Create DBMS database job, which one execute every day

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X

,what => 'database_growth ;'

,next_date => to_date('26/04/2010 21:32:06','dd/mm/yyyy hh24:mi:ss')

,interval => 'SYSDATE+30/24'

,no_parse => TRUE

);

SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

END;

/

commit;

Note: if you needed are change job execution date and time manually

Step 4: Now see your CREATE TABLE DB_GROWTH, this (DAILY_GROWTH_MB) column are provide daily growth in database.

Resolve the problem "ORA-00031: session marked for kill"

If get "ORA-00031: session marked for kill" from oracle database then Follow the

Simulation Steps 1:

SQL> ALTER SYSTEM KILL SESSION '205,32449' IMMEDIATE;
ALTER SYSTEM KILL SESSION '205,32449' IMMEDIATE
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> SELECT s.sid,
s.serial#,
s.osuser,
s.program
FROM v$session s;

Select from query your desire sid and serial# no from query

Steps 2:

Find out the your desire thread id (needed to be os level kill session)

SQL>select
spid,
osuser,
s.program
from v$process p,
v$session s
where p.addr=s.paddr



For Windows Operating System

C:> orakill ORACLE_SID spid

note: ORACLE_SID means oracle unique database name

like>

C:> orakill test 1236

Finally your database session and OS child level session has been killed.

For UNIX Operating System

Check the SPID from UNIX Process

Test# % ps -ef | grep ora

Then Run following:

Test# % kill -9 spid