Calculate the size of the RMAN backup

Yesterday I went to see the backup team to see the ways we can reduce the cost of the backup ( recession time ...cost cutting ;)

I was told by backup team that oracle is taking lot of resources w.r.t tape ..thus they asked me to find out for each database what is the size of the backup ..

It is very difficult to get the size of the rman backup's directly but following SQL will help u to get the approx size in TB about the rman backup sets in tape . This is valid only for 9i ..It may work in 10G also but not checked and tested !!!

select sum(blocks*block_size)/1024/1024/1024/1024 from rc_backup_datafile ;

select sum(blocks*block_size)/1024/1024/1024/1024 from RC_BACKUP_REDOLOG ;

Above SQL's have to be run in sqlplus in RMAN catalog database ..

Hope this helps to determine the cost of tape's

How to Block TOAD session in Production Database

Hi,



As the TOAD usuage increases , There are issues with performance of the database . We have seen that TOAD users are one of the reasons for causing performance issue. Users , I mean developers, Login to Production database using TOAD and fire N number of Queries . If the Query uses Parallelism , Then Parallel processes are linked to the users . Thus when a process requires a parallel process , Oracle is not able to allocate the necessary parallel processes as requested .. thus performance issue . Also we have seen TOAD users can use locking issue which may impact the overnight batch issues



Thus , I was asked to see how to restrict the TOAD access to the Production databases . There are two ways to do this :

• Not to allow TOAD session


• To kill the TOAD session after some interval

I think first option is the best option ....

To do this , We have to write a simple trigger ....

CREATE OR REPLACE TRIGGER block_toad
AFTER LOGON
ON DATABASE
DECLARE
--Declare a cursor to find out the program
--the user is connecting with.
CURSOR user_prog IS
SELECT program FROM v$session
WHERE audsid=sys_context('USERENV','SESSIONID');
--Assign the cursor to a PL/SQL record.
user_rec user_prog%ROWTYPE;
BEGIN
OPEN user_prog;
FETCH user_prog INTO user_rec;
IF user_rec.program IN ('TOAD.exe')
THEN
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login with TOAD');
END IF;
CLOSE user_prog;
END;
/

Similar process can be used to block other tools ...

Hope this helps DBA to manage the TOAD access to Production databases ;)