Add datafile on RAC on rawdisk

Assume you have two servers srv1 & srv2.
You want to add 5GB datafile on your tablespace DATA
1. Check the server mode. Which one is the master
vxdctl -c mode
root@srv2 # vxdctl -c mode
mode: enabled: cluster active – SLAVE
master: srv1
srv1 is a master
Check volume
TY NAME ASSOC [...]

Read Full Post »

How to hide primary user information from another user on Oracle

Sometimes you have to create user for your colleague/client that have privillege(s) to do select on primary user’s table. Unfortunately, your primary user’s password is not secure enough and you can’t change that password because many application connect to that database using that user, so you think you want to hide information about that primary [...]

Read Full Post »

Can’t Start Oracle Listener

Someone on indo-oracle mailing list ask why he/she can’t start his/her listener
LSNRCTL> START
Starting /oracle/app/oracle/product/920/bin/tnslsnr: please wait…
TNSLSNR for HPUX: Version 9.2.0.6.0 – Production
System parameter file is /oracle/app/oracle/product/920/network/admin/listener.ora
Log messages written to /oracle/app/oracle/product/920/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.177.6.32)(PORT=1521)))
Error listening on: (ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.177.6.32)(PORT=1521)))
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
HPUX Error: 13: Permission denied
Listener failed to start. See the [...]

Read Full Post »

Can’t Remote Login on Oracle

Sometimes you want to log on to your database server from another server. For example in Oracle Dataguard you want to log on to standby from the primary server.
When you want to log on, you may usually type
#sqlplus sys@STANDBY as sysdba
What if you can’t log on to STANBY instance like this ?
oracle@standby-server$ sqlplus /nolog
SQL*Plus: Release [...]

Read Full Post »

Check usage resource in Oracle

Sometimes you want to check, which process that make your oracle database heavy and what program is it ?
You can use this script to check your Oracle DB process

SELECT s.status “Status”, s.serial# “Serial#”, s.TYPE “Type”, s.username
“DB User”, [...]

Read Full Post »

Alter Datafile from autoextend on to off

Sometimes you want to disable automatic extend on your datafile and extend its size. You may use this script
For example :
Datafile : your_dbfile.dbf size 2G autoextend on
You want to change it into 5G and disable the autoextend
Here we are

alter database datafile ‘your_dbfile.dbf ‘ resize 5G
alter database datafile ‘your_dbfile.dbf ‘ autoextend off
Just it

Read Full Post »

Check Freespace in Tablespace

Sometimes you want to see your freespace in your tablespace.
Here we are
column total_space format 9,999,999,999,999
column freespace format 9,999,999,999
(select tablespace,
datafile total_space,
freespace freespace
from
(select tablespace_name tablespace
from dba_tablespaces where status=’ONLINE’) a,
(select tablespace_name tablespace1, sum(bytes) datafile
from dba_data_files group by tablespace_name) b,
(select tablespace_name tablespace2, sum(bytes) freespace
from dba_free_space group by tablespace_name) c
where tablespace=tablespace1 and
tablespace1=tablespace2(+));

Read Full Post »