Script for Sessions with high physical reads


There is script to check session with have high physical reads :

set linesize 120

col os_user format a10

col username format a15

col pid format 9999999999

PROMPT SESSIONS SORTED BY PHYSICAL READS

PROMPT

select

OSUSER os_user,username,

PROCESS pid,

ses.SID sid,

SERIAL#,

PHYSICAL_READS,

BLOCK_CHANGES

from v$session ses,

v$sess_io sio

where ses.SID = sio.SID

and username is not null

and status=’ACTIVE’

order by PHYSICAL_READS;

 

thanks to gavin soorma for share this script

Advertisements

ORACLE DATAGUARD 10g WITH ASM CONFIGURATION


Oracle Dataguard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

this is step by step to configure ODG on ASM configuration (copy this artikel to notepad or editor to easy read ):

Prereq :

– 2 virtual machine(DBSERVER and DBSERVERDRC) with OEL 5.5 and Oracle database 10gR2 (ASM Storage : +DATA and +FRA).

+DATA for database file and +FRA for recovery area.

– Database with SID : “blackowl” are configured on DBSERVER and at DBSERVERDRC just software oracle only

Continue reading

Oracle sqlplus query output into file


Quite useful, I had the case where I needed to provide a CSV file to an external party from our Oracle 10gdatabase. No problem, there are loads of CSV export tools out there. But it then transpired that the CSV’s that I was generating were using quote (“) marks to identify fields e.g. “field1″,”field2″ and so on…

When I ran the script in something like SQL Developer it would spool the file and also spool the query as well into the file no matter what I specified. Along with this it also wouldn’t recognise usage of variables in SQL Developer. Basically what I’m saying here is write the query in something like SQL Developer by all means but for writting and debugging a CSV spooler etc… just save the hassle and go straight to SQL*Plus.

First of all lets start with how to spool the CSV file without leading line breaks, no quote marks etc… the below is the SQL script to run, we’ll need to save this in order to run it later.

c:\edit tes.sql

set echo off
set termout off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ”
set trimspool on
set show off
spool c:\emp.csv

select * from mas_pasien;

spool off
exit;

Continue reading

ORA-15041: diskgroup space exhausted


PROBLEM:
The following are the errors seen during tablespace creation in an Oracle DB with ASM.

ERRORS:
NOTIFICATION rcu: oracle.sysman.assistants.rcu.backend.action.JDBCAction::perform: Performing Inline Action Command: CREATE TABLESPACE “RRWEBC_IAS_WEBCENTER” EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO DATAFILE ‘+DATA’ SIZE 250M AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED
2010-02-09 23:27:35.187 ERROR rcu: oracle.sysman.assistants.rcu.backend.action.AbstractAction::handleNonIgnorableError: Received Non-Ignorable Error: ORA-01119: error in creating database file ‘+DATA’
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup space exhausted

SOLUTION:
The related disk group could have run out of space, due to more number of tablespaces, in this case there were 84. Firstly look for space, and appropriately cleanup some of them or just new disks.

To Check:
select NAME, OS_MB, TOTAL_MB, FREE_MB from v$asm_disk;

To Cleanup:
drop tablespace <NAME> including contents and datafiles;

To Increase Disk Space:
alter diskgroup DISK1 rebalance power 11;
alter diskgroup DISK2 add disk ‘path’;
alter diskgroup test_dg add disk ‘/dev/hdb10′,’/dev/hdb11’;

asmcmd Utility


Moving a datafile from File System to ASM


Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.

These are the steps:

1. Check where to build a new file system based tablespace:

[oracle@rac1 ~]$ cd /u01/oradata/racdb
[oracle@rac1 ~]$ df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/vx/dsk/u01
31457280  17540576  13819976  56% /u01

2. Connect to sqlplus and create a new tablespace

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Jun 19 06:07:50 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create tablespace trtst datafile ‘/u01/oradata/racdb/trtst01.dbf’ size 150M;

Tablespace created.
Continue reading

Moving from ASM storage back to filesystem


Whilst it is not very common to move from ASM to filesystem I needed to prepare a regression plan for the migration of a 450 datafile database in the event of problems during the migration or shortly afterwards.

The example below is from a small database called TEST and I used filestore in /app/oracle whereas normally I would have a /oradata/SID/ filesystem

The details below should be enough to get someone through the migration and the sql scripts can be modified to be more generic if necessary but it is not too hard to use them as they are now.

List the datafiles and we will add a second datafile to the users tablespace to provide an example of an issue that can occur.

SYS@TEST SQL>select file_name from dba_data_files;  +DATA/test/datafile/undotbs1.301.697649965
+DATA/test/datafile/sysaux.300.697649963
+DATA/test/datafile/system.299.697649963
+DATA/test/datafile/users.302.697649965
+DATA/test/datafile/users.309.697650601

mkdir /app/oracle/TEST

Some tablespaces may have multiple datafiles which would end up with the same name using the code above. However as a quick circumvention I manually identified those tablespaces using this sql and then edited the new data file names

select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2

Continue reading