Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
July | 2009 | Oracle DBA – Tips and Techniques | Page 4
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Access Control Lists (ACL) and Sending Mail From APEX

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 3 Comments
In Oracle 11g, security has been enhanced by restricting access to packages that were used in earlier releases like UTL_SMTP and UTL_HTTP to send emails and connect over the network to mail servers etc. By default, attempt to use these packages will result in an ORA-24247 (network access denied by access control list). Using Access […]
Read More
 

10g MERGE statement

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
The MERGE statement (UPSERT, combination of INSERT/UPDATE) was introduced in Oracle9i. In 10g it has been enhanced to include conditional INSERT/UPDATE/DELETE clauses as part of the MERGE statement. Let us use the EMP and BONUS table in SCOTT schema to illustrate the use of this statement EMP Table has got 14 rows SQL> select * […]
Read More
 

Script – What Wait Events Are Sessions Waiting On

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
set linesize 120 col username format a10 col event format a30   select sid, serial#,username, event, seconds_in_wait, wait_time from v$session where state = 'WAITING' and wait_class != 'Idle' order by event ;
Read More
 

Script – Sessions Waiting On A Particular Wait Event

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
SELECT count(*), event FROM v$session_wait WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager', 'pmon timer','rdbms ipc message', 'SQL*Net message from client') GROUP BY event ORDER BY 1 DESC;
Read More
 

Using Oracle Data Vault – some examples

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
This is a very powerful and easy to use security tool and can be easily deployed to enforce security and also comply with audit requirements. In the first case study, a user with all database privileges (DBA) can still be 'locked out' and prevented from accessing certain tables in a particular schema. In the second […]
Read More
 1

10g RAC Installation and Configuration – A Practical Guide

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 2 Comments
This note will describe in detail the steps required to install and configure a 10g RAC environment on a Red Hat Linux platform. It will discuss the installation of the Clusterware, OCFS, ASM, using DBCA to create the RAC database, configuring services using srvctl and Transparent Application Failover (TAF). Download …
Read More
 

How To Migrate OID Users Between Two 10g iAS Environments

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
This note will describe how we can migrate a set of user accounts whose credentials are stored in the Oracle Internet Directory (OID). This may be required for example when we want to move users from a development 10g iAS environment to a production one. Download …
Read More
 

10g Flashback and Oracle Data Guard

  • Posted by Arjun Raja
  • On July 1, 2009
  • 0 Comments
FLASHBACK INSTEAD OF DELAYING APPLYING OF LOGS ON STANDBY: If you have multiple standby sites, you may utilize the DELAY option in Data Guard to prevent physical/logical corruption or user errors in your primary database. For example If your primary database has only one standby database , a logical or physical corruption in the primary […]
Read More
 

Script – Datafiles with highest I/O activity

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
col name format a40 set linesize 140 select * from ( select name,phyrds, phywrts,readtim,writetim from v$filestat a, v$datafile b where a.file#=b.file# order by readtim desc) where rownum <6;
Read More
 

10g EM Grid Control Installation

  • Posted by Arjun Raja
  • On July 1, 2009
  • 0 Comments
The document below gives a clear idea of how to install 10g EM grid control and upgrade the environment to 10.2.0.3 There are 3 components in a grid control installation. 1. Oracle Management Server – OMS 2. Creation of Grid control repository database. 3. Oracle grid control agent View the note to read a step […]
Read More
 

10g EM Grid Control Agent Push Deployment

  • Posted by Arjun Raja
  • On July 1, 2009
  • 0 Comments
It is possible to deploy the Grid Control OEM agent to various machines you want monitored via the push method. This method installs the agent on other machines without the need to copy the software to those machines. This is very useful in case there are many machines to be monitored by Grid Control. Download […]
Read More
 

Script – Find SQL being executed by a OS Process ID (PID)

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 1 Comments
prompt "Please Enter The UNIX Process ID" set pagesize 50000 set linesize 30000 set long 500000 set head off select s.username su, substr(sa.sql_text,1,540) txt from v$process p, v$session s, v$sqlarea sa where p.addr=s.paddr and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) and spid=&SPID;
Read More
 

Script – List SQL being executed by a particular SID

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
col sql_text format a100 heading "Current SQL" select q.sql_text from v$session s , v$sql q WHERE s.sql_address = q.address and s.sql_hash_value + DECODE (SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value AND s.sid=&1;
Read More
 

Script – Monitor sessions with high Physical Reads

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
set linesize 120 col osuser format a10 col username format 10 select OSUSER osuser, username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID order by PHYSICAL_READS;
Read More
 

Script – Query the RMAN catalog to list backup completion status

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
Note – run this query connected as the owner of the RMAN 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 and b.bck_type is not null and b.bs_key not in(Select […]
Read More
 

Script – List all recent DDL modifications

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
set pagesize 200 colsep ' ' col Owner format a20 col Object_Name format a30 col Last_DDL format a20 break on Owner select Owner, Object_type Object_Name, Timestamp Last_DDL from DBA_OBJECTS where SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1 order by Owner, Object_Name /
Read More
 

Script – List Objects being accessed by a particular SID

  • Posted by Gavin Soorma
  • On July 1, 2009
  • 0 Comments
set pagesize 300 select sid,serial#,username,status from v$session WHERE USERNAME IS NOT NULL order by STATUS DESC; col sid format 999 col owner format a10 col object format a20 col type format a10 set linesize 300 /* ENTER THE SID TO CHECK */ select sid,owner,object,type from v$access where sid = &sid order by owner,type ;
Read More