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 3
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

RMAN unregister database from catalog

  • Posted by Arjun Raja
  • On July 7, 2009
  • 0 Comments
1. Go to the database you wish to unregister and get its DBID: system@HERML> select dbid,name from v$database; DBID NAME —————– —————————— 2082566940 HERML 2. Connect to the recovery catalog-owning schema and determine the DB_KEY of the database in the recovery catalog: rman10@RMAN10P> select db_key,dbid,name from rc_database 2 where dbid = 2082566940; DB_KEY DBID NAME […]
Read More
 

10g RMAN restore on another machine

  • Posted by Arjun Raja
  • On July 7, 2009
  • 0 Comments
TASK: Restoring a tape backup onto another machine and bringing up the database. ORIGINAL DATABASE: RPSP ON ITLINUX25 AND TARGET IS RPSP ON ITLINUX26. PRE-REQUISITES: Make sure that the tdpo.opt file uses the same TDP library pointer on the new machine as the original machine – only then will RMAN restore the backup from the […]
Read More
 

Script – Database structure and file location

  • Posted by Gavin Soorma
  • On July 7, 2009
  • 0 Comments
set pagesize 500 set linesize 130 Prompt Prompt Control Files Location >>>> col name format a60 heading "Control Files" select name from sys.v_$controlfile / Prompt Prompt Redo Log File Locations >>>> Prompt col Grp format 9999 col member format a50 heading "Online REDO Logs" col File# format 9999 col name format a50 heading "Online REDO […]
Read More
 

Script – Tablespace free space and fragmentation

  • Posted by Gavin Soorma
  • On July 7, 2009
  • 1 Comments
set linesize 150 column tablespace_name format a20 heading 'Tablespace' column sumb format 999,999,999 column extents format 9999 column bytes format 999,999,999,999 column largest format 999,999,999,999 column Tot_Size format 999,999 Heading 'Total| Size(Mb)' column Tot_Free format 999,999,999 heading 'Total Free(MB)' column Pct_Free format 999.99 heading '% Free' column Chunks_Free format 9999 heading 'No Of Ext.' column […]
Read More
 1

Data Guard Switchover Unix shell script

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 1 Comments
The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database. It is very important that the scripts are run in the correct order and on right machine. These scripts are based on a few customisations. The *.sh scripts are located in a directory called “/var/opt/oracle/dataguard” […]
Read More
 

Script – Latch Contention (top 5 latches)

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will display the top 5 latches with the most sleeps. Script can be changed to even sort the display on misses instead. set linesize 120 col name format a30 select * from (select name, gets,misses, sleeps from v$latch order by sleeps desc) where rownum < 6;
Read More
 

Script – Top SQL (Physical Reads)

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 5 SQL statements sorted by the most number of physical reads set serverout on size 1000000 set feedback off declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc; begin dbms_output.put_line('Reads'||' '||' Text'); dbms_output.put_line ('-----'||' '||'----------------------------------------------------'); dbms_output.put_line(' '); open […]
Read More
 

Script – Top SQL (Buffer Gets)

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads set serverout on size 1000000 declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select buffer_gets,substr(sql_text,1,4000) from v$sqlarea order by buffer_gets desc; begin dbms_output.put_line('Reads'||' '||' Text'); dbms_output.put_line ('-----'||' '||'---------------------------------------------------'); dbms_output.put_line(' '); open […]
Read More
 

Script – Top database wait events

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 5 database wait events since instance startup sorted by time waited set linesize 120 col event format a30 select * from ( select event, total_waits, total_timeouts, time_waited from v$system_event where event not like 'SQL*Net%' and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer') order by time_waited desc ) […]
Read More
 

Script – Segments with highest I/O activity

  • Posted by Gavin Soorma
  • On July 6, 2009
  • 0 Comments
This script will list the top 10 segments in the database that have the most number of physical reads against them. Script can also be changed to query on 'physical writes' instead. set pagesize 200 setlinesize 120 col segment_name format a20 col owner format a10 select segment_name,object_type,total_physical_reads from ( select owner||'.'||object_name as segment_name,object_type, value as […]
Read More
 1

Data Guard – Adding and Resizing a datafile on the Primary database

  • Posted by Gavin Soorma
  • On July 4, 2009
  • 3 Comments
In a Data Guard environment, care should be taken while performing tasks like adding or resizing datafiles on the primary database. Ideally, the primary and standby sites should be identical in terms of file system structure. Also disk space allocated should be identical, or we could be faced with a case when resizing a datafile […]
Read More
 

Script – List status of all submitted DBMS jobs

  • Posted by Gavin Soorma
  • On July 3, 2009
  • 0 Comments
set pagesize 100 set linesize 120 ttitle - center 'Submitted DBMS Jobs' skip 2 col job format 99999 heading 'job#' col subu format a10 heading 'Submitter' trunc col lsd format a5 heading 'Last|Ok|Date' col lst format a5 heading 'Last|Ok|Time' col nrd format a5 heading 'Next|Run|Date' col nrt format a5 heading 'Next|Run|Time' col fail format 999 […]
Read More
 

EM Grid Agent targets.xml Modification After Database Upgrade

  • Posted by Arjun Raja
  • On July 3, 2009
  • 0 Comments
AGENT RECONFIGURE AFTER DB UPGRADE. If you upgrade an Oracle database or start the database from a new Oracle Home, the following needs to be done for GC to recognise the change and for the database to be monitored via the GRID. Shutdown the agent on the box – cd AGENT_HOME/bin ./emctl stop agent. Next […]
Read More
 

RMAN recovery from loss of all control files

  • Posted by Gavin Soorma
  • On July 3, 2009
  • 0 Comments
SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG) To simulate this scenario, we will create and populate a table with some records, recover the database from a loss of all control files and then perform an incomplete recovery until the point of failure. SQL> insert into myobjects select * from myobjects; 919664 rows created. SQL> […]
Read More
 

Oracle Wait Events causes and resolutions

  • Posted by Gavin Soorma
  • On July 3, 2009
  • 11 Comments
This note will discuss the various common Wait Events like db file sequential read, db file scattered read, log file sync etc , their possible causes and the likely actions which can be taken to resolve the problem. Download …
Read More
 1

RMAN recovery from loss of all online redo log files

  • Posted by Gavin Soorma
  • On July 3, 2009
  • 1 Comments
MAA best practices dictates that to avoid such scenarios, we should be multiplexing the online redo log files. Each group should have at least 2 members and each member should be located on a different physical disk. Loss of a single current online redo log file will require us to restore the entire database and […]
Read More
 

Recovery From Loss Of Datafile For Which No Backup Is Available

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 3 Comments
SCENARIO – 10g Database Loss of datafile which has not been backed up. CREATE NEW TABLESPACE TEST SQL> create tablespace test datafile ‘/u02/oradata/testdb/test.dbf’ size 10m; Tablespace created. TAKE A BACKUP OF THE DATABASE AT THIS POINT >>>>>>>>> ADD DATAFILE TO TEST TABLESPACE SQL> alter tablespace test add datafile ‘/u02/oradata/testdb/test01.dbf’ size 10m; Tablespace altered. SIMULATE FAILURE […]
Read More
 

10g RAC Administration Using SRVCTL

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 0 Comments
Status of all instances and services $ srvctl status database -d orcl Instance orcl1 is running on node linux1 Instance orcl2 is running on node linux2 Status of a single instance $ srvctl status instance -d orcl -i orcl2 Instance orcl2 is running on node linux2 Status of a named service globally across the database […]
Read More