check tablespace usage

kalo gak ada toad, ato tools yang mendukung untuk check tablespace
gimana caranya ya, ya pake sqlplus sajah n jalankan query berikut :

col "Tablespace" for a20
col "Allocated" for 999999999999

select ddf.TABLESPACE_NAME "Tablespace",
ddf.BYTES "Allocated",
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) "Used %",
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) "Free %"
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc
/

Moving Segement to another tablespace

Moving table atau index diperlukan dengan tujuan biasanya untuk menangani masalah I/O
atau juga masalah fragmentasi di database yang dapat juga berpengaruh pada performance

cara nya
1. untuk segment type nya index

alter index (owner).(index name) rebuild tablespace (destination tablespace);

2. untuk segment type table

alter table (owner).(table name) MOVE TABLESPACE (destination tablespace);

Register plugin on Oracle Grid Control

bagaimana caranya register plugin supaya bisa dilihat dan digunakan
dalam grid control, begini nich cara nya :



























kemudian check di main page Oracle Grid Control, di sana harus nya ada tab baru yang sudah kita register untuk di display

Unix For DBA

terkadang kita bingung dalam environtment unix server, script atau
tool apa saja siich yang harus di gunakan untuk kepentingan monitoring
server terkait dengan oracle database, saya sedikit share yang mudah2an membantu

Virtual memory Usage
$ vmstat 5 3

CPU Usage
$ sar -u 10 8

$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 2 times):

$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system

display top cpu consumers
$ps -ef|sort +6|tail

cpu currently usage by each process in %
$ps auxgw|sort +2|tail

display server device values in HP-UX
$ lsdev -C

display system kernel on HP-UX
$kmtune


on AIX :
$lsattr -El sys0

display ram size on unix

uerf -r 300|grep -i mem

display ram size on HP

using $glance or $sar

display ram size on solaris

$prtconf|grep -i mem

on Aix

$lsdev -C|grep -i mem
output :
mem0 available 00-00
$lsattr -El mem0

on AIX mem Utilization :
#svmon

Display Allocated Memory Segments :
$ ipcs -pmb

================================ CPU number

Display the Number of CPUs
AIX
lsdev -C|grep Process|wc -l
Solaris
psrinfo -v|grep "Status of processor"|wc -l
to see detail cpu
psrinfo -v

Count Used Semaphores
on HP-UX
kmtune|grep sem
semaphores to be used
ipcs -sa|grep oracle

===============================================
Show Server Log in HP-UX
$ grep error /var/adm/syslog/syslog.log|more

Show Server Log in AIX
$ errpt -a|more

IO stat
#iostat 3
using >> pstack

query check disk IO

Untuk melihat IO disk kita bisa melihat secara realtime dengan menggunakan query berikut :

select substr(NAME,1,instr(NAME,'/',2)-1) name,
sum(fs.PHYBLKRD + fs.PHYBLKWRT) TOT--"Total Blocks I/O"
from (select sum(PHYRDS) phys_reads,
sum(PHYWRTS) phys_wrts
from sys.v_$filestat) pd,
v$datafile df,
v$filestat fs
where df.FILE# = fs.FILE#
group by substr(NAME,1,instr(NAME,'/',2)-1)
order by 2

berguna bagi yang akan menambah datafile harus di split ke mana, tentunya ke disk yang IO nya paling rendah. IO juga mempengaruhi pada performance database
jika I/O nya merata di semua disk maka cukup mempengaruhi pula pada performace database

register ip in dalam router computer

ketika kita mengatifkan modem terkadang koneksi kita dengan jaringan
akan terputus secara otomatis dan ini tidak diharapkan karena terkait dengan kerjaan
kadang modem kita mengalah dengan menon aktifkan nya, ada cara lain yaitu dengan
menset route nya

route add 198.11.3.213 10.98.10.1
(ip yang dituju) (gateway-nya)

kemudian coba untuk koneksi ke internet dengan modem dan kemudian koneksi ke

Requirements for Installing Oracle 11gR1 RDBMS on Solaris 10 SPARC 64-bit

1. Hardware Requirements

Oracle software requires a minimum of 1024 MB of RAM for successful installation.

* The following table describes the relationship between installed RAM and the configured swap space requirement:
RAM Swap Space
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM
* 275 Mb of free space in /tmp

* The disk space requirements for software files for each installation type:
Enterprise Edition - 4.68 GB
Standard Edition - 4.62 GB
Custom Edition - 4.71 GB (maximum)
* Between 1.5 GB and 2 GB of disk space is required for preconfigured database that uses file system storage (optional).

2. Software Requirements

* Solaris 10

* The following packages (or later versions) must be installed:
# SUNWarc
# SUNWbtool
# SUNWhea
# SUNWlibC
# SUNWlibm
# SUNWlibms
# SUNWsprot
# SUNWtoo
# SUNWi1of
# SUNWi1cs
# SUNWi15cs
# SUNWxwfnt
# SUNWuiu8 - refer Note 290034.1
# SUNWulcf - refer Note 290034.1
* The following are the compiler requirements for Pro*C/C++, Oracle Call Interface, Oracle C++ Call Interface, and Oracle XML Developer's Kit (XDK), and GNU Compiler Collection (GCC) with Oracle Database 11g Release 1:
Sun One Studio 11
* The following patches are required:
# 127111-02 SunOS 5.10: libc patch
# 137111-04 SunOS 5.10: kernel patch

3. OS Environment

* Modify your kernel settings in /etc/system as well as using the resource control.

Parameter Replaced by Resource Control Minimum Value

noexec_user_stack	NA	1
semsys:seminfo_semmni	project.max-sem-ids	100
semsys:seminfo_semmns	NA	1024
semsys:seminfo_semmsl	process.max-sem-nsems	256
semsys:seminfo_semvmx	NA	32767
shmsys:shminfo_shmmax	project.max-shm-memory	4294967296
shmsys:shminfo_shmmni	project.max-shm-ids	100

* The 'umask' setting for the "oracle" user has to be 022.

* Hostname command should return the fully qualified hostname as shown below:
% hostname
hostname.domainname

Implementation of Oracle Data Guard 11g

I. Keterangan Umum

server

home primary database = ORADEV1-DB (20.138.3.61)

home standby database = ORADEV2-DB (20.138.3.63)

database unique name

db unique name primary database = oradb

db unique name primary database = oradrc

Note :

Khusus untuk instance atau service name dibuat sama yaitu oradb dengan tujuan untuk digunakan pada saat koneksi di sisi aplikasi agar transparan dimana ketika terjadi failover atau switchover dari sisi database (primary database ke standby database) maka tidak diperlikukan kembali konfigurasi dari sisi client. Cukup dengan menggunakan connection string sebagai berikut :

ORADEV =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV1-DB)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV2-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

II. Konfigurasi Koneksi

ORADEV1-DB

a. konfigurasi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oradb)

(ORACLE_HOME = /oracle/product/orahome/11.1.0/db_1)

(SID_NAME = oradb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV-DB)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

b. configure tnsnames.ora

ORADB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV1-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

ORADRC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV2-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

ORADEV2-DB

a. configure listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oradb)

(ORACLE_HOME = /oracle/product/orahome/11.1.0/db_1)

(SID_NAME = oradb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV-DB)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

b. configure tnsnames.ora

ORADB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV1-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

ORADRC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV2-DB)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = oradb)

)

)

III. Inisialisasi instance standby database

ORADEV1-DB

a. backup spfile to pfile:

SQL> create pfile='/oracle/product/orahome/11.1.0/db_1/dbs/pfileora.ora' from spfile;

b. copy file password orapworadb database dari primary ke standby

ORADEV2-DB

a. create inittemp di ORADV2-DB

/oracle/product/orahome/11.1.0/db_1/dbs/inittemp.ora

bash-3.00$ cat inittemp.ora

b. dengan isi dari init temporary nya adalah

db_name=oradb

service_names=oradb

db_unique_name=oradrc

c. naikan instance standby database dengan status nomount state

SQL> startup pfile='/oracle/product/orahome/11.1.0/db_1/dbs/inittemp.ora' nomount;

Note : menyesuaikan mountpoint antara ORADDEV1-DB dengan ORADEV2-DB

dimana directory disamakan baik itu directory datafile , control file maupun logfile

IV. Konfigurasi parameter untuk standby database

ORADEV1-DB

Setting parameter untuk oracle dataguard konfigurasi :

alter system set db_unique_name='oradb' scope=spfile;

Alter system set fal_client='ORADB' scope=spfile;

Alter system set fal_server='ORADRC scope=spfile;

Alter system set standby_file_management='AUTO' scope=spfile;

Alter system set log_archive_config='dg_config=(ORADRC,ORADB)' scope=spfile;

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oradata01/archive/' scope=spfile;

ALTER SYSTEM SET log_archive_dest_state_1='ENABLE'scope=spfile;

ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORADRC ARCH ASYNC DB_UNIQUE_NAME=ORADRC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=spfile;

ALTER SYSTEM SET log_archive_dest_state_2= 'DEFER' scope=spfile;

alter system set log_archive_format='log_%s_%t_%r.arc' scope=spfile;

alter system set log_file_name_convert='/oradata01/redolog','/oradata01/redolog',

'/oradata02/redolog','/oradata02/redolog',

'/oradata03/redolog','/oradata03/redolog',

'/oradata04/redolog','/oradata04/redolog' scope=spfile;

Create logfile standby database :

Split logfile di semua mountpoint dan dibuat roundrobin pada saat database mengalami switchlog dengan menggunakan script sebagai berikut ;

alter database add standby logfile group 5 '/oradata04/redolog/redo_stdby01.log' size 209715200;

alter database add standby logfile group 6 '/oradata03/redolog/redo_stdby02.log' size 209715200;

alter database add standby logfile group 7 '/oradata02/redolog/redo_stdby03.log' size 209715200;

alter database add standby logfile group 8 '/oradata01/redolog/redo_stdby04.log' size 209715200;

alter database add standby logfile group 9 '/oradata04/redolog/redo_stdby05.log' size 209715200;

V. Cloning database standby dengan menggunakan rman

ORADEV1-DB

Untuk cloning rman dapat melakukan secara online dengan transfer semua komponen database melalui network.

bash$ rman

rman > connect target sys/oracle@ORADB

rman > connect auxiliary sys/oracle@ORADRC

rman > RUN

{

ALLOCATE CHANNEL d1 TYPE DISK;

ALLOCATE CHANNEL d2 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL cnv1 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL cnv2 TYPE DISK;

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

SPFILE

SET db_unique_name='oradrc'

SET service_names='oradb'

SET control_files='/oradata03/control_file/stdby_ctl01.ctl'

SET log_archive_dest_1='LOCATION=/oradata01/archive/'

SET log_archive_dest_state_1='ENABLE'

SET log_archive_dest_2='SERVICE=ORADB ARCH ASYNC DB_UNIQUE_NAME=ORADB VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'

SET log_archive_dest_state_2 = 'DEFER'

SET log_archive_format='log_%s_%t_%r.arc'

SET fal_client='ORADRC'

SET fal_server='ORADB'

SET standby_file_management='AUTO'

SET log_file_name_convert='/oradata01/redolog','/oradata01/redolog',

'/oradata02/redolog','/oradata02/redolog',

'/oradata03/redolog','/oradata03/redolog',

'/oradata04/redolog','/oradata04/redolog'

SET log_archive_config='dg_config=(ORADB,ORADRC)'

NOFILENAMECHECK;

}

ORADEV2-DB

1. Aktifkan recover standby database

Sql > alter database recover managed standby database disconnect from session;

2. Check status database

col DB_UNIQUE_NAME for a15

col flashback_on for a14

select DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FLASHBACK_ON from v$database;

DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE FLASHBACK_ON

--------------- ---------- -------------------- ---------------- --------------

oradrc MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NO

3. Check status MRP

SQL> select process, status from v$managed_standby;

PROCESS STATUS

--------- ------------

ARCH CLOSING

ARCH CLOSING

ARCH CLOSING

ARCH CLOSING

RFS IDLE

MRP0 WAIT_FOR_GAP

RFS IDLE

7 rows selected.

4. Check Gap

SQL> select * from v$archive_gap;

no rows selected

5. check applied archive

check :

SQL> col name for a50

SQL> /

NAME S APP

-------------------------------------------------- - ---

/oradata01/archive/log_6_1_690475917.arc A YES

/oradata01/archive/log_7_1_690475917.arc A YES

/oradata01/archive/log_8_1_690475917.arc A YES

/oradata01/archive/log_9_1_690475917.arc A YES

/oradata01/archive/log_10_1_690475917.arc A YES

/oradata01/archive/log_11_1_690475917.arc A YES

/oradata01/archive/log_12_1_690475917.arc A YES

/oradata01/archive/log_13_1_690475917.arc A YES

ORADB A YES

/oradata01/archive/log_14_1_690475917.arc A YES

ORADB A YES

/oradata01/archive/log_15_1_690475917.arc A YES

ORADB A YES

/oradata01/archive/log_16_1_690475917.arc A YES

ORADB A NO

/oradata01/archive/log_17_1_690475917.arc A YES

ORADB A NO

/oradata01/archive/log_18_1_690475917.arc A YES

/oradata01/archive/log_19_1_690475917.arc A YES

/oradata01/archive/log_20_1_690475917.arc A YES

/oradata01/archive/log_21_1_690475917.arc A YES

21 rows selected.

6. check sequence

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata01/archive/

Oldest online log sequence 19

Next log sequence to archive 0

Current log sequence 22

ORADEV1-DB

1. check status pengiriman (shipping) archive yang sudah dicreate oleh primary

col destination format a25

col error format a20

select dest_id, destination, status, error from v$archive_dest;

DEST_ID DESTINATION STATUS ERROR

---------- ------------------------- --------- --------------------

1 /oradata01/archive/ VALID

2 ORADRC VALID

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

dari hasil query di atas maka status oke

2. check sequence archive primary (nilainya sama yaitu max # 22)

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oradata01/archive/

Oldest online log sequence 19

Next log sequence to archive 22

Current log sequence 22

VI. Kesimpulan

Standby database oradb 11g sudah selesai dan proses sinkronisasi sudah berjalan sebagaimana mestinya, tidak ada gap antara kedua database oradb dan oradrc. Maintenance standby database diperlukan untuk masalah archive. Dimana archive dapat dilakukan rutin delete archive untuk mencegah jika terjadi penuh mount point. Dengan adanya standby database 11g kita dapat menggunakan standby database untuk kepentingan reporting dengan cara mengaktifkan active dataguard.