List all tablespaces with free space < 10%

Posted by Gavin Soorma
On June 29, 2009
0 Comments

set pagesize 300 set linesize 100 column tablespace_name format a15 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(Kb)' column Pct_Free format 999.99 heading '% Free' column Max_Free format 999,999,999 heading 'Max […]

Posted by Gavin Soorma
On June 29, 2009
0 Comments

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; SELECT * FROM V$RECOVERY_FILE_DEST; col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999 col round(space_used/1048576) heading "Space Used (MB)" format 99999 col name format a40 select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;

Posted by Gavin Soorma
On June 29, 2009
0 Comments

select count(*) child_count, sum(gets) sum_gets, sum(misses) sum_misses, sum(sleeps) sum_sleeps from v$latch_children where name = 'cache buffers chains'; select p1 "File #". p2 "Block #" from v$session_wait where event ='cache buffer chains'; select owner, segment_name, segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1; col object_name format a20 col […]

Posted by Arjun Raja
On June 29, 2009
0 Comments

FLASHBACK DATABASE Flashback database allows you to rollback database to a time in the past. Useful if you have : 1. Dropped user 2. Truncated table 3. Batch job:Partial changes. PRE-REQUISITE: DATABASE MUST BE IN ARCHIVELOG MODE AND YOU MUST SET FLASHBACK ON( BY DEFAULT IT IS OFF) FLASHBACK LOGS ARE CREATED IN YOUR FLASH_RECOVERY_AREA […]

Monitor space used in ASM Disk Groups

Posted by Gavin Soorma
On June 29, 2009
2 Comments

SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name' COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size' COLUMN block_size FORMAT 99,999 HEAD 'Block|Size' COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size' COLUMN state FORMAT a11 HEAD 'State' COLUMN type FORMAT a6 HEAD 'Type' COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)' […]

ARCHIVE AREA FULL – CONNECT INTERNAL UNTIL FREED

Posted by Arjun Raja
On June 29, 2009
0 Comments

When a database is running in ARCHIVELOG mode, if the area which stores the archivelogs on disk gets full, the database comes to a halt and can be accessed only as a sysdba. The error message when you try to connect as a normal user is ‘ CONNECT INTERNAL UNTIL FREED’ If the following parameter […]

Posted by Gavin Soorma
On June 28, 2009
0 Comments

Simulate loss of redo log files [oracle@itlinuxdevblade08 opsdba]$ rm redo*.log Check alert log Errors in file /u02/ORACLE/opsdba/bdump/opsdba_arc1_29898.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u02/ORACLE/opsdba/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Wed Jun […]

Posted by Gavin Soorma
On June 28, 2009
0 Comments

Original Data Guard Environment HQLINUX05 - PRIMARY ITLINUX03 - STANDBY. Perform FAILOVER TO ITLINUX03 (now the new Primary) Next in ITLINUX03 ..... Step 1: On the new primary, run the following query to determine at what SCN it became the new primary. bozo:/u01/ORACLE/bozo> sql SQL*Plus: Release 10.1.0.2.0 - Production on Mon May 9 14:48:57 2005 […]

Posted by Gavin Soorma
On June 28, 2009
0 Comments

From Oracle 9i onwards you can use RMAN to recover only blocks while database is up and running. This could possibly save hours and hours of recovery time as a full database restore is not necessary. Error reported by user pointing to block corruption. POPULATE_MACSDATA - ORA-01578: ORACLE data block corrupted (file # 48, block […]

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

FAST START FAILOVER IS DISABLED BY DEFAULT Configuration Name: streams2.hq.emirates.com Enabled: YES Protection Mode: MaxAvailability Fast-Start Failover: DISABLED Databases: streams2 - Primary database blade08 - Physical standby database Current status for "streams2.hq.emirates.com": SUCCESS PREPARE TO ENABLE TO FAST START FAILOVER DGMGRL> EDIT DATABASE 'streams2' SET PROPERTY FastStartFailoverTarget = 'blade08'; Property "faststartfailovertarget" updated DGMGRL> EDIT DATABASE […]

Posted by Gavin Soorma
On June 25, 2009
0 Comments

PERFORM A FAILOVER USING DATA GUARD BROKER DGMGRL> failover to 'standby_blade07'; Performing failover NOW, please wait... Failover succeeded, new primary is "standby_blade07" On the NEW PRIMARY issue this command to obtain the SCN to FLASHBACK the old primary(new standby) to SQL> conn / as sysdba Connected. SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 2147132 FLASHBACK […]

Posted by Arjun Raja
On June 25, 2009
1 Comments

To clear the saved configuration in rman – example the configuration for Channel 1 RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR; starting full resync of recovery catalog full resync complete old RMAN configuration parameters: CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/ora_export/backup/fin/%U’ MAXPIECESIZE 16 G; old RMAN configuration parameters are successfully deleted starting full […]

Posted by Arjun Raja
On June 25, 2009
0 Comments

This works on AIX – Please test on other systems. If a file name is vague and can’t be removed. Ex – a file name has some hidden characters which prevents it’s removal as the name is not recognized by the rm command. ls -lai sqlnet.log 19462 -rw-r—– 1 oraofsap dba 7741 Jun 4 13:14 […]

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 Arjun Raja
On June 25, 2009
0 Comments

Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly. SCHEMA NAME – TEST Create the table to store the statistics- EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’); Export schema stats – will be stored in the ‘STATS_TABLE’ EXEC DBMS_STATS.export_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’); If […]

11g RMAN REPAIR ADVISOR

Posted by Arjun Raja
On June 25, 2009
1 Comments

USING RMAN REPAIR ADVISOR: Export ORACLE_SID=test1 sqlplus “sys as sysdba” Shutdown immediate. Go to c:\app\bb16872\test1 and rename SYSTEM01.DBF to SYSTEM01.old Next try to startup database…will fail with error that SYSTEM01.DBF is missing. Y:\>rman target / Recovery Manager: Release 11.1.0.6.0 – Production on Wed Apr 30 10:51:03 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. […]

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 […]

Page 2 of 4123 4

News

List all tablespaces with free space < 10%

Monitor the Flashback area space usage

Identify 'hot' objects when faced with 'Cache Buffers Chains Latch' wait event

FLASHBACK DATABASE

Monitor space used in ASM Disk Groups

ARCHIVE AREA FULL – CONNECT INTERNAL UNTIL FREED

Recovery from a backup taken before a RESETLOGS

Using FLASHBACK after a Data Guard Failover

RMAN Block Recovery

Daily DBA Checklist

Managing space in the PERFSTAT tablespace

10g Data Guard Broker Fast Start Failover

Using FLASHBACK to Reinstate a Standby after a FAILOVER

RMAN – Clear Saved Configuration

Unix – remove file with unseen characters

Using the 10g Resource Manager to manage INACTIVE sessions

Export and Import schema statistics

11g RMAN REPAIR ADVISOR

Querying the Alert Log using External Tables

Determing the optimal UNDO tablespace size