Persiapan Installasi Oracle 10g R2 32 bit pada CentOS 5.4

Referensi cara pengInstallan ada dari 3 website yang paling mutakhir, yaitu :
1. http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php
2. http://ivan.kartik.sk/oracle/install_ora10gR2_redhat.html
3. http://kamranagayev.com/2009/05/01/step-by-step-installing-oracle-database-10g-

PRAKATA
Tulisan ini ditujukan untuk orang yang mencari cara setting awal pada linux/centOs 5.4 untuk dapat Installasi Oracle Database di PC/SERVER/VMWARE/VIRTUALBOX, dan dianggap dalam hal ini untuk Installasi Oracle Database sudah bisa (tanpa perlu panduan).

KRONOLOGIS
Setelah hampir seminggu utak-atik OS linux bernama CentOS di VMWARE, akhirnya pilihan itu jatuh pada referensi no 3, dengan otomatis installation menggunakan shell script. Hanya saja saya memodifikasi script install.sh nya, karena terdapat masalah didalamnya terkait penggunaan karakter ” dan $ . Kemudian perubahan juga terjadi folder ORACLE_BASE-nya, disesuaikan dengan referensi nomor 1, yang terakhir perubahan pada installasi RPM … ditambahkan referensi dari nomor 1.Setelah proses Installasi selesai, ada juga settingan port, hostname pada listener dan tnsnames serta firewall yang harus di exception-kan port 1521.

Script otomatis sendiri terbagi menjadi 2 yaitu : install.sh dan install_zip.sh (pada attachment)
1. Install.sh untuk posisi source database oracle sudah ter-extract
2. Install_zip.sh untuk posisi source database oracle masih posisi dalam bentuk zip file

Langkah-langkah
1. Install CentOS, setelah selesai, rubah ip dengan ip statis, dengan cara click menu pada kiri atas, Click System – Administration – Network. Rubah IP dengan contoh :

Memindahkan Database Oracle dari server A ke server B dengan directory yang berbeda

Contoh :

Lokasi sumber -> SERVER A directory G:\ORACLE\ORADATA\KALTENG

Lokasi tujuan -> SERVER B directory C:\oracle\product\10.2.0\oradata\KALTENG

Langkah-langkah :

1. Buat Database dengan nama yang sama pada SERVER B.

2. Nonaktifkan Database pada server B.

3. Pindahkan/Copy semua file (kecuali Control01.ctl, …, Control03.ctl) pada directory G:\ORACLE\ORADATA\KALTENG di server A ke server B di lokasi C:\oracle\product\10.2.0\oradata\KALTENG

4. Catat nama-nama file yang ada pada directory  G:\ORACLE\ORADATA\KALTENG

Contoh Datafiles

 

 

 

 

 

 

 

 

5. login menggunakan user sys

SQLPLUS / as sysdba

6. Startup database secara exclusive

STARTUP mount exclusive;

7. Pindahkan lokasi directory pada semua data file yang dicatat

alter database rename file ‘G:\ORACLE\ORADATA\KALTENG\REDO01.LOG’ to ‘C:\oracle\product\10.2.0\oradata\KALTENG\REDO01.LOG’;

….

….

alter database rename file ‘G:\ORACLE\ORADATA\KALTENG\USERS01.DBF’ to ‘C:\oracle\product\10.2.0\oradata\KALTENG\USERS01.DBF’; 

8. Recover database (jika diperlukan)

Recover database;

9. Open database

Alter database open; 

 

Referensi :  http://boomslaang.wordpress.com/2008/10/01/recovery-of-system01dbf-datafile/

http://www.orafaq.com/wiki/Move_datafile_to_different_location

https://forums.oracle.com/forums/thread.jspa?threadID=358621

http://blog.vpire.com/?p=872 

Mengaktifkan database dengan menonaktifkan archive log

Archivelog akan membentuk file berupa dbf pada directory yg telah ditentukan, jika terlalu banyak akan memakan space yg besar, untuk me-nonaktifkannya anda dapat menggunakan cara dibawah ini …

login ke oracle menggunakan user sysdba

sqlplus / as sysdba

kemudian lihat dengan menggunakan query dibawah ini, apakah ARCHIVELOG ? :

SELECT log_mode
FROM v$database;

jika ya, maka untuk mengaktifkannya adalah sbb :

SHUTDOWN; atau SHUT IMMEDIATE;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE OPEN;

Cek kembali menggunakan perintah :

SELECT log_mode
FROM v$database; 

referensi : http://psoug.org/reference/archivelog.html

How to know every size on table in oracle

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

example result:

OWNER     TABLE_NAME     MEG
DBADMIN    DD_MASTER_AUDIT    83648
DBADMIN    TRAN_HIST     39266
DBADMIN    GL_INTERFACE_HIST  30459
DBADMIN    LN_MASTER_AUDIT    8832

links : http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle
added example result by sikathabis

exp query oracle

If you are using QUERY for export sometimes confusing with error on the syntax

this is the simple example form parfile and from command

parfile

query=”where to_char(tanggal,’YYYY’)=’2002′”

command

exp user/password@db query=\”where to_char(tanggal,’YYYY’)=’2002′\”

Menambahkan hardisk pada linux

links : http://www.yolinux.com/TUTORIALS/LinuxTutorialAdditionalHardDrive.html
[root]# fdisk /dev/hdb
Command (m for help): m     (Enter the letter "m" to get list of commands)
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
e
Partition number (1-4): 1
First cylinder (1-2654, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2654, default 2654):
Using default value 2654

Command (m for help): p

Disk /dev/hdb: 240 heads, 63 sectors, 2654 cylinders
Units = cylinders of 15120 * 512 bytes

   Device Boot    Start       End    Blocks   Id  System
/dev/hdb1             1      2654  20064208+   5  Extended

Command (m for help): w    (Write and save partition table)

[root]# mkfs -t ext3 /dev/hdb1
mke2fs 1.27 (8-Mar-2002)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
2508352 inodes, 5016052 blocks
250802 blocks (5.00%) reserved for the super user
First data block=0
154 block groups
32768 blocks per group, 32768 fragments per group
16288 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 34 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root]# mkdir /opt2
[root]# mount -t ext3 /dev/hdb1 /opt2

Enter the drive into the fstab file so that it is recognized and mounted upon system boot.

File: /etc/fstab Red Hat 8.0

LABEL=/ / ext3 defaults 1 1 LABEL=/boot /boot ext3 defaults 1 2 none /dev/pts devpts gid=5,mode=620 0 0 none /proc proc defaults 0 0 none /dev/shm tmpfs defaults 0 0 /dev/hda2 swap swap defaults 0 0 /dev/hdb1 /opt2 ext3 defaults 1 2 /dev/cdrom /mnt/cdrom iso9660 noauto,owner,kudzu,ro 0 0 /dev/fd0 /mnt/floppy auto noauto,owner,kudzu 0 0

The digits "1 2" refer to whether the mount point should be backed up when the dumpcommand is used and disk integrity checks using fsck. The "1" states that it should be backed up when the dump command is issued (0=no). The "2" refers to the order in which "fsck" should check the mount points. The digit "1" identifies the root ("/") of the filesystem. All others should be "2". (0=no check)

Also see: fstab man page for a description of all options.

How to create directory on oracle

example (locating DATA_PUMP_DIR into sharing location)

create or replace directory DATA_PUMP_DIR as ‘\\somewhere\dpdump\’;

example (linux)

create or replace directory DATA_PUMP_DIR as ‘/u01/dpdump/’;

example (windows)

create or replace directory DATA_PUMP_DIR as ‘d:\oracle\product\10.2.0\db_1\admin\dpdump\’;

Example count age from microsoft excel

Format

=DATEDIF(“birthdate”,date,”y”)

example 1

=DATEDIF(“1-jan-1970″,TODAY(),”y”)

result 1 => if today 20 dec 2011 then age = 41

example 2

=DATEDIF(“1-jan-1970″,DATE(2012,1,1),”y”)

result 2 => 42

Library Cache Get hit Ratio

SELECT ‘Buffer Cache’ NAME,
ROUND ( (congets.VALUE + dbgets.VALUE – physreads.VALUE)
* 100
/ (congets.VALUE + dbgets.VALUE),
2
) VALUE
FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
WHERE congets.NAME = ‘consistent gets’
AND dbgets.NAME = ‘db block gets’
AND physreads.NAME = ‘physical reads’
UNION ALL
SELECT ‘Execute/NoParse’,
DECODE (SIGN (ROUND ( (ec.VALUE – pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
),
-1, 0,
ROUND ( (ec.VALUE – pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
)
FROM v$sysstat ec, v$sysstat pc
WHERE ec.NAME = ‘execute count’
AND pc.NAME IN (‘parse count’, ‘parse count (total)’)
UNION ALL
SELECT ‘Memory Sort’,
ROUND ( ms.VALUE
/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
* 100,
2
)
FROM v$sysstat ds, v$sysstat ms
WHERE ms.NAME = ‘sorts (memory)’ AND ds.NAME = ‘sorts (disk)’
UNION ALL
SELECT ‘SQL Area get hitrate’, ROUND (gethitratio * 100, 2)
FROM v$librarycache
WHERE namespace = ‘SQL AREA’
UNION ALL
SELECT ‘Avg Latch Hit (No Miss)’,
ROUND ((SUM (gets) – SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
UNION ALL
SELECT ‘Avg Latch Hit (No Sleep)’,
ROUND ((SUM (gets) – SUM (sleeps)) * 100 / SUM (gets), 2)
FROM v$latch;

result for example:

NAME	VALUE
Buffer Cache	99.89
Execute/NoParse	99
Memory Sort	100
SQL Area get hitrate	62.37
Avg Latch Hit (No Miss)	99.99
Avg Latch Hit (No Sleep)	100

How to start Enterprise manager console

Windows

if ORACLE_SID is null then set ORACLE_SID with ORACLE_HOME directory, example

open CMD and then type SET ORACLE_SID=ORA92

emctl start dbconsole