Daily DBA Checklist

Posted by Gavin Soorma
On June 27, 2009
1 Comments

• Ensure that previous night's backup is complete and there are no RMAN errors in the backup logs. • Ensure that any exports which are part of the backup are complete and the dump files compressed. • Check the alert log for any ORA- errors - also for messages like 'Checkpoint not complete etc'. • […]

Managing space in the PERFSTAT tablespace

Posted by Gavin Soorma
On June 27, 2009
0 Comments

You can create the following procedure in your perfstat schema if you want to delete the old statistics and rebuild the objects to free up the space in PERFSTAT tablespace. create or replace procedure perfstat_freespace is cursor c1 is SELECT table_name from user_tables ; cursor c2 is SELECT index_name from user_indexes ; l_str varchar2(200) ; […]

Posted by Gavin Soorma
On June 25, 2009
0 Comments

CREATE THE PLAN begin dbms_resource_manager.create_pending_area(); end; / begin dbms_resource_manager.create_plan( plan => 'TEST_PLAN', comment => 'Resource plan/method for Idle time kill sessions'); end; / CREATE THE CONSUMER GROUPS begin dbms_resource_manager.create_consumer_group( consumer_group => 'LONG_RUNNING', comment =>'Privileged Users'); dbms_resource_manager.create_consumer_group( consumer_group => 'SHORT_RUNNING', comment => 'Under Privileged Users'); end; / CREATE DIRECTIVES FOR THE PLAN Consumer Group LONG_RUNNING will […]

Posted by Gavin Soorma
On June 25, 2009
0 Comments

Run this PL/SQL block as SYS. This will create the Directory, grant the required privileges on the Directory and then create the External Table in the SYSTEM schema. DECLARE BDumploc VARCHAR2(200); ORASID VARCHAR2(16); ObjectExists EXCEPTION; PRAGMA EXCEPTION_INIT(ObjectExists,-955); BEGIN SELECT value INTO BDumploc FROM v$parameter WHERE name='background_dump_dest'; -- create the directory for the bdump dir EXECUTE […]

Posted by Gavin Soorma
On June 24, 2009
0 Comments

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]“, SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]“, (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) “NEEDED UNDO SIZE [MByte]“ FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name […]

Posted by Gavin Soorma
On June 23, 2009
1 Comments

This script will query the RMAN catalog and report on the backup status of every database in the catalog. set lines 80 set pages 250 ttitle "Daily Backup........" select DB NAME,dbid, NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP, NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP from ( select a.name DB,dbid, decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db, decode(b.bck_type,'L', max(b.completion_time)) BACKUPTYPE_arch from rc_database a,bs b where a.db_key=b.db_key […]

Posted by Gavin Soorma
On June 21, 2009
0 Comments

CREATE OR REPLACE PROCEDURE SEND_MAIL ( msg_to varchar2, msg_subject varchar2, msg_text varchar2 ) IS c utl_smtp.connection; rc integer; msg_from varchar2(50) := ‘Gavin’; mailhost VARCHAR2(30) := ‘harpoon.hq.emirates.com’; — local database host BEGIN c := utl_smtp.open_connection(mailhost, 25); — SMTP on port 25 utl_smtp.helo(c, mailhost); utl_smtp.mail(c, msg_from); utl_smtp.rcpt(c, msg_to); utl_smtp.data(c,’From: Oracle Database’ || utl_tcp.crlf || ‘To: ‘ || […]

Posted by Gavin Soorma
On June 20, 2009
0 Comments

In order to prevent you from the task from running a recompile script several times so as compile all objects that are dependant on other objects, we need to take into account the dependency of objects – this is maintained by the view DEPENDENCY$ in the SYS schema. This script was taken by me from […]

Posted by Gavin Soorma
On June 19, 2009
0 Comments

This example takes VPD a bit further and also restricts access at the column level as well as shown in the example below – the earlier example will work in 8i and 9i, but this is an enhancement in 10g Release 2. SQL> conn sysadmin_vpd/sysadmin Connected. CREATE THE SECURITY FUNCTION SQL> create […]

Posted by Gavin Soorma
On June 18, 2009
1 Comments

CREATE A PROGRAM begin dbms_scheduler.create_program ( program_name => ‘UPDATE_VERITAS’, program_type => ‘EXECUTABLE’, program_action => ‘/opt/oracle/scripts/blade08/update_veritas.sh’, enabled => TRUE, comments => ‘Check the Time’ ); end; / CREATE A SCHEDULE begin dbms_scheduler.create_schedule ( schedule_name => ‘EVERY_6_HOURS’, repeat_interval => ‘FREQ=HOURLY; INTERVAL=6?, comments => ‘Every 6 Hours’ ); end; / CREATE A JOB begin dbms_scheduler.create_job […]

Posted by Gavin Soorma
On June 18, 2009
0 Comments

CREATE OR REPLACE TRIGGER manage_OCIservice after startup on database DECLARE role VARCHAR(30); BEGIN SELECT DATABASE_ROLE INTO role FROM V$DATABASE; IF role = ‘PRIMARY’ THEN DBMS_SERVICE.START_SERVICE(‘apex_dg’); ELSE DBMS_SERVICE.STOP_SERVICE(‘apex_dg’); END IF; END; /

Posted by Gavin Soorma
On June 18, 2009
0 Comments

BEGIN sys.dbms_scheduler.create_job( job_name => ‘”HR”.”ALTER_INDX001″‘, job_type => ‘PLSQL_BLOCK’, job_action => ‘begin EXECUTE IMMEDIATE ”alter index HR.EMP_NAME_IX coalesce”; end;’, start_date => systimestamp at time zone ‘Asia/Muscat’, job_class => ‘DEFAULT_JOB_CLASS’, comments => ‘This job will coalesce index HR.EMP_NAME_IX on the EMPLOYEES table’, auto_drop => FALSE, enabled => FALSE); sys.dbms_scheduler.set_attribute( name => ‘”HR”.”ALTER_INDX001″‘, attribute => ‘restartable’, value => […]

10g Datapump – quick introduction

Posted by Gavin Soorma
On June 18, 2009
1 Comments

In 10g you can continue to use the earlier exp and imp versions to export/import data. However if you export data using exp, then the import has to be with imp utility. You cannot export using exp and import using impdp The 2 new utilites in 10g are EXPDP and IMPDP. EXPORT USING DATAPUMP (EXPDP). […]

Check if block change tracking file is being used

Posted by Gavin Soorma
On June 18, 2009
0 Comments

select file#, avg(datafile_blocks), avg(blocks_read), avg(blocks_read/datafile_blocks) * 100 as "% read for backup" from v$backup_datafile where incremental_level > 0 and used_change_tracking = 'YES' group by file# order by file#;

Check for dependant foreign keys before dropping Primary Key

Posted by Gavin Soorma
On June 18, 2009
0 Comments

SELECT A.owner foreign_owner, a.table_name foreign_table, b.owner primary_owner, b.table_name primary_table from dba_constraints a, dba_constraints b where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R' and b.constraint_type = 'P' and b.table_name = 'DEPT';

Posted by Gavin Soorma
On June 18, 2009
0 Comments

Business requirement User MILLER should only see records in the EMP table that match his name. User ADAMS should only see records in the EMP table that match his name User KING should be able to access all records in the EMP table Solution Overview Oracle Virtual Private Database (VPD) enables you to create security […]

Page 10 of 10«‹8 910

News

Daily DBA Checklist

Managing space in the PERFSTAT tablespace

Using the 10g Resource Manager to manage INACTIVE sessions

Querying the Alert Log using External Tables

Determing the optimal UNDO tablespace size

Query the RMAN catalog to check backup status

Using UTL_SMTP to send email alerts and Resumable Operations

Recompiling Objects based on dependencies

Virtual Private Database – part 2

Using DBMS_SCHEDULER to run a UNIX shell script

Trigger to use with Data Guard to change service name

Creating a job using Oracle 10g Scheduler

10g Datapump – quick introduction

Check if block change tracking file is being used

Check for dependant foreign keys before dropping Primary Key

Virtual Private Database – Part 1