Februari 20, 2009

Local Area Network (LAN)

Local Area Network atau biasa disebut LAN, merupakan jaringan komputer yang terbatas hanya dalam satu gedung. Bisa kantor, kampus, sekolah, rumah sakit atau area yang lebih sempit lagi...misalna rumah. Jaringan ini ngga' menggunakan media komunikasi publik kaya' telpon, tv kabel atau yang laennya. Teknologi LAN saat ini kebanyakan berbasis pada standar internasional IEEE 802.3 mengenai Ethernet. Ethernet sendiri merupakan skenario pengkabelan dan pemrosesan sinyal untuk data jaringan komputer yang dikembangkan oleh Robert Metcalfe dan David Boggs di Xerox Palo Alto Research Center (PARC) pada tahun 1972 (sumber : id.wikipedia.org).

Hmm...mo ngomong palagi ya, ampe lupa :p. Oh ya, untuk membuat jaringan lokal lo musti punya komponen²-nya yaitu :

*

Workstation

Bahasa gampangnya komputer...bisa mainframe, PC atau laptop. Biasanya salah satu dari workstation ini nanti ada yang berfungsi sebagai server. Yang tugasnya mengatur semua sistem yang ada di dalam sebuah jaringan.
*

Network Interface Card (NIC)

Yaitu kartu jaringan yang dipasang pada komputer sebagai media penghubung antara komputer dengan kabel jaringan.
*

Kabel Penghubung

Untuk saat ini kabel yang paling banyak digunakan adalah kabel UTP (Unshielded Twisted Pair) cat 3 / cat 5. UTP kategori 3 support untuk transmisi data hingga 10 Mbps, sedangkan kategori 5 hingga 100 Mbps.
*

Software Jaringan

Terdiri atas sistem operasi, driver adapter dan protokol jaringan.
*

Peralatan Pendukung Jaringan

Seperti hub, switch, repeater, bridge dan router. Peralatan tersebut dipake kalo' di dalam jaringan tersebut ada lebih dari 2 buah komputer.

Selain hal² di atas, masih ada lagi yang perlu diperhatikan dalam membuat suatu jaringan yaitu topologi-nya. Topologi jaringan adalah hubungan geometris antara unsur² dasar penyusun jaringan yaitu node, link dan station. Ada beberapa macam topologi jaringan yang mempunyai kelebihan dan kekurangan masing², yaitu :

*

Topologi Bus

Setiap node/komputer pada topologi ini dihubungkan dengan sebuah kabel tunggal yang disebut trunk, backbone atau segment. Media koneksi yang biasa digunakan pada topologi ini adalah kaebl CoAX (RG58) atau 10Base-2. Topologi bus sudah sangat jarang dipakai. Kelebihan : hemat kabel, mudah dikembangkan. Kekurangan : kemungkinan terjadinya collision/tabrakan data sangat besar, apabila salah satu node putus maka akan mengganggu kinerja dan trafik seluruh jaringan.
*

Topologi Ring

Topologi ini membentuk lingkaran seperti cincin tanpa memiliki ujung. Dimana setiap node terhubung ke 2 titik atau node yang lain melalui sebuah kabel tunggal. Media koneksi yang biasa digunakan adalah kabel UTP cat 3 ata token ring. Kelebihan dan kekurangan hampir sama kaya' topologi bus, cuman untuk pengembangan pada topologi ring lebih susah.
*

Topologi Star

Masing² node pada topologi ini dikonsentrasikan ke sebuah hub/switch/multipoint sehingga membentuk seperti bintang. Media koneksi yang biasa dipakai adalah kabel UTP (10/100/1000Base-T). Kelebihan : jika satu saluran rusak maka saluran yang lain tidak akan terganggu, pengembangan jaringan mudah, tahan terhadap lalu lintas data yang sibuk. Kekurangan : boros kabel, kalo' titik/node pusat (hub/switch/multipoint) rusak maka seluruh jaringan bakal terhenti.

Pemilihan topologi jaringan akan berpengaruh pada :

1.Tipe peralatan jaringan yang dibutuhkan.
2.Kemampuan peralatan.
3.Pertumbuhan jaringan.
4.Penanganan manajemen jaringan.

Proxy dengan menggunakan SQUID

Apakah trasparan cache itu ?
Penjelasan istilah trasparan cache dan trasparan proxy tergantung pada contohnya,
tapi kami mengasumsikan konteks disini yaitu HTTP Proxy/cache dengan trasparan hijacking dari port 80 yang mana merupakan jalur HTTP yang gagal di internet. Perbedaannya yaitu bahwa cache memasukkan cache, tapi proxy hanya proxy-proxy tanpa caching .Trasparan Overload mempunyai arti yang berbeda-beda tergantung situasinya . Kata tersebut dapat diartikan setup yang membajak jalur port 80 dimana klientnya mencoba untuk pergi ke server yang lain, juga bisa diartikan sebuah trasparan proxy yang tak dapat mengubah arti / isi permintaan . tak ada alat seperti trasparan proxy , hanya semi trasparan dan tak ada alat seperti trasnparan cache. Squid dapat dikonfigurasikan untuk bertindak secara trasparan. Dalam mode ini klient tak disyaratkan untuk mengkonfigurasikan browser mereka untuk mengakses cache tapi squid akan menjemput paket yang tepat dan permintaan cache secara transparan. Hal yang bisa memecahkan masalah terbesar dengan menggunakan caching : menganjurkan para pemakai untuk menggunakan cache server.

Apakah squid itu ?
Squid adalah sebuah penampilan yang bagus bagi dari server cacking proxy untuk
klient web, pendukung FTP, gopher dan obyek data HTTP. Tak seperti software cacking
tradisional , squid menangani semua permintaan dalam bentuk singgle , non bloking , proses I/O driven. Squit menyimpan data meta dan khususnya obyek panas yang tersembunyi dalam RAM, menyembunyikan DNS lookups, mendukung DNS lookups yang tak memihak, dan cacking negatif dari permintaan yang digagalkan . Squid mendukung SSL,kontrol akses yang extensif dan loging permintaan penuh. Dengan menggunakan ukuran berat internet cache protokol, squid dapat disusun dalam sebuah hirarki untuk pengamanan bandwidth extra squid terdiri dari sebuah squid program main server, sebuah dnsserver program lookups Domain Name Systim, beberapa program untuk menulis kembali permintaan-permintaan dan keoutentikan penampilan , dan beberapa menegemen dan alat-alat klient.

II. Langkah-Langkah Praktikum 1

Agar konfigurasi kembali normal seperti semula maka ketikkan :
debian:/home/student# dpkg -p squid apache2
Package: squid
Priority: optional
Section: web
Installed-Size: 1608
Maintainer: Luigi Gangitano
Architecture: i386
Version: 2.6.5-6etch1
Replaces: squid-novm
Depends: libc6 (>= 2.3.6-6), libdb4.4, libldap2 (>= 2.1.17-1), libpam0g (>= 0.76), netbase, adduser, logrotate (>= 3.5.4-1), squid-common (= 2.6.5-6etch1), coreutils, lsb-base
Pre-Depends: debconf (>= 1.2.9) | debconf-2.0
Suggests: squidclient, squid-cgi, logcheck-database, resolvconf (>= 0.40), smbclient
Conflicts: squid-novm, sarg (<< 1.1.1-2)
Size: 654734
…………………………………………………….


1. Installasi squid
# apt-get install squid
debian:/home/student# apt-get install squid
Reading package lists... Done
Building dependency tree... Done
squid is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Keterangan :
Install terlebih dahulu squid sebelum melakukan program berikutnya.

2. Catatlah di direktori mana saja aplikasi squid diinstall
debian:/home/student# dpkg -L squid
/.
/etc
/etc/squid
/etc/logrotate.d
/etc/logrotate.d/squid
/etc/init.d
/etc/init.d/squid
/etc/resolvconf
/etc/resolvconf/update-libc.d
/etc/resolvconf/update-libc.d/squid
/usr
/usr/bin
/usr/sbin
/usr/sbin/squid
/usr/share
……………………………………………………………………………………………………………
Keterangan :
Tempat didirectory mana squid telah terinstall.


3. Edit file konfigurasi

debian:/home/student# vim /etc/squid/squid.conf
Edit parameter berikut ini:
visible_hostname
Keterangan :
Membuat host_name baru (dengan nama sesuai keinginan).

4. Buat file direktori swap untuk squid
debian:/home/student# squid -z
2008/10/08 15:07:08| Squid is already running! Process ID 2959



5. Tambahkan ACL untuk network tertentu
debian:/home/student# vim /etc/squid/squid.conf

Masukkan script ini ke dalam squid.conf
acl jar src 10.252.108.0/255.255.255.0
acl blok src 10.252.108.24/255.255.255.255
http_access allow jar !blok

6. Restart squid
# /etc/init.d/squid restart
# /etc/init.d/squid restart
debian:/home/student# /etc/init.d/squid restart
Restarting Squid HTTP proxy: squid Waiting.....................done.


7. Ujicoba di client, ganti setting proxy pada brower dengan ip dari proxy
server kemudian lakukan akses ke http://www.eepis-its.edu

Konfigurasi DHCP di debian

1. Masuk ke root, cek ip kita (server) dan cek koneksi
student@debian:~$ su
Password:

debian:/home/student# ifconfig
eth0 Link encap:Ethernet HWaddr 00:13:D4:E8:72:20
inet addr:10.252.108.80 Bcast:10.252.108.255 Mask:255.255.255.0
inet6 addr: 2001:470:8049:108:213:d4ff:fee8:7220/64 Scope:Global
inet6 addr: fe80::213:d4ff:fee8:7220/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:358 errors:0 dropped:0 overruns:0 frame:0
TX packets:220 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:257280 (251.2 KiB) TX bytes:25651 (25.0 KiB)

eth1 Link encap:Ethernet HWaddr 00:1B:11:47:D4:AE
UP BROADCAST MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
Interrupt:66 Base address:0xa800

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:8 errors:0 dropped:0 overruns:0 frame:0
TX packets:8 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:560 (560.0 b) TX bytes:560 (560.0 b)


debian:/home/student# ping 10.252.1.1
PING 10.252.1.1 (10.252.1.1) 56(84) bytes of data.
64 bytes from 10.252.1.1: icmp_seq=1 ttl=254 time=5.39 ms
64 bytes from 10.252.1.1: icmp_seq=2 ttl=254 time=1.89 ms
64 bytes from 10.252.1.1: icmp_seq=3 ttl=254 time=2.34 ms
64 bytes from 10.252.1.1: icmp_seq=4 ttl=254 time=2.15 ms

2. Hapus dhcp3-server pada komputer, walaupun tidak ada tetap hapus aja untuk cari aman
dpkg -P dhcp3-server
3. Kemudian install kembali dhcp3-server
debian:/home/student# apt-get install dhcp3-server
Reading package lists... Done
Building dependency tree... Done
The following NEW packages will be installed:
dhcp3-server
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 0B/292kB of archives.
After unpacking 836kB of additional disk space will be used.
Preconfiguring packages ...
Selecting previously deselected package dhcp3-server.
(Reading database ... 66576 files and directories currently installed.)
Unpacking dhcp3-server (from .../dhcp3-server_3.0.4-13_i386.deb) ...
Setting up dhcp3-server (3.0.4-13) ...
Generating /etc/default/dhcp3-server...
Starting DHCP server: dhcpd3 failed to start - check syslog for diagnostics.
invoke-rc.d: initscript dhcp3-server, action "start" failed.

4. konfigurasi dhcp server
vim /etc/dhcp3/dhcpd.conf
subnet 10.252.108.0 netmask 255.255.255.0{
range 10.252.108.50 10.252.108.250;
option domain-name-servers 202.154.187.2,202.154.187.3;
option domain-name"eepis-its.edu";
option routers 10.252.108.1;
option broadcast-address 10.252.108.255;}
5. restart dhcp
debian:/home/student# /etc/init.d/dhcp3-server restart
Stopping DHCP server: dhcpd3.
Starting DHCP server: dhcpd3.
6. setting dhcp pada client --> pada komputer client
dhclient
ket : device diisi dengan network device yang aktif pada client dengan cara ketik : mii-tool.
pilih eth yang linknya ok
cth : dhclient eth1
7. cek dhcp server leases
more /var/lib/dhcp/dhcpd.leases
8. edit file config dhcpd3
vim /etc/dhcp3/dhcpd.conf
subnet 10.252.108.0 netmask 255.255.255.0{
range 10.252.108.50 10.252.108.250;
option domain-name-servers 202.154.187.2,202.154.187.3;
option domain-name"eepis-its.edu";
option routers 10.252.108.1;
option broadcast-address 10.252.108.255;
host mycomp{
hardware ethernet 01:f0:4d:63:52:66;
fixed-address 10.252.108.255;
option domain-name-servers 202.154.187.2,202.154.187.3;
option domain-name"eepis-its.edu";
option broadcast-address 10.252.108.255;
}
}
9. uji coba pada client --> pada komputer client
dhclient eth1 atau eth0

konfigurasi web mail dengan debian

KONFIGURASI :
1. Install postfix MTA (Mail Transfer Agent)
#apt-get install postfix postfix-tls libsasl2 sasl2-modules popa3d
selama proses installasi postfix akan memberikan sedikit pertanyaan seperti : nama
dari server, domain dan memilih jaringan postfix itu sendiri apakah akan di setting
internet ataukah local. biasanya untuk domain menyesueikan dengan nama domain
kita yang ada di DNS server, sedangkan nama server bisa diisi terserah.
file konfigurasi postfix terletak di /etc/postfix/main.cf
2. Merestart Postfix
#/etc/init.d/postfix restart
3. Install Dovecot
apt-get install dovecot-imapd dovecot-pop3d dovecot-common
4. Konfigurasi Dovecot
Setelah install, sebelum melakukan proses selanjutnya perlu merubah konfigurasi file.
Diantaranya :
file dovecot.conf di : /etc/dovecot/dovecot.conf
Kemudian rubahlah menjadi seperti ini :
# specify protocols = imap imaps pop3 pop3s
protocols = pop3 imap
kemudian buka comment #disable_plaintext_auth = yes menjadi :
disable_plaintext_auth = no
pop3_uidl_format = %08Xu%08Xv
5. Menambahkan user baru
User ini akan dipakai untuk authentikasi masuk email :
#adduser user_name
6. Restart Dovecot
#/etc/init.d/dovecot restart
7. Mengkonfigurasi SASL Authentikasi dengan TLS
Konfigurasi file main.cf pada : /etc/postfix/main.cf
file ini digunakan untuk me enable authentikasi user
tambahakanlah script dibawah ini :
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = yourdomain.com
smtpd_recipient_restrictions =
permit_mynetworks,permit_sasl_authenticated,reject_unauth_destination
smtpd_sasl_security_options = noanonymous
secara default postfix tidak dapat berkomunikasi dengan saslauthd, agar dapat
berkomunikasi maka perlu menambahkan perintah seperti dibawah ini :
#rm -r /var/run/saslauthd/
#mkdir -p /var/spool/postfix/var/run/saslauthd
#ln -s /var/spool/postfix/var/run/saslauthd /var/run
#chgrp sasl /var/spool/postfix/var/run/saslauthd
#adduser postfix sasl
untuk devecot kita juga membutuhkan spesifikasi dovecot authentkasi daemon socket
yakni dengan menambahkan script dibawah ini :
socket listen {
client {
path = /var/spool/postfix/private/auth
mode = 0660
user = postfix
group = postfix
}
}
8. Restart Service
Kemudian setelah semua konfigurasi selesai maka restart semua service yang
bersangkutan :
#/etc/init.d/postfix restart
#/etc/init.d/saslauthd restart
#/etc/init.d/dovecot restart
9. Memforward email
Untuk memforward email maka ketikkan perintah ini :
echo 'destination_email_address' >.forward
10.Menginstall Squirrelmail, aphace dan php
#apt-get install squirrelmail apache2 libapache2-mod-php5 php5-cli php5-common
php5-cgi
11.Restart apache
...............................................
Kemudian jalankan di browser dengan mengetikkan http://localhost/squirrelmail

RMAN Backup dan Recovery Oracle 10g (3)

Beberapa kasus RMAN
24. Kasus hilangnya datafile
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/system01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: ‘/u02/oradata/iscdd/system01.dbf’
SQL> exit;

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes

[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 19 10:13:18 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)

RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes

RMAN> restore datafile 1;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:05:47
Finished restore at 19-AUG-08

RMAN> recover database;
Starting recover at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-AUG-08

RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$

[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 19 10:26:21 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

25. Kasus hilangnya datafile pada saat transaksi
a. session I menambah transaksi
SQL> insert into DEPT values(’50′,’IT’,'SBY’);
SQL> insert into DEPT values(’51′,’IT’,'SBY’);
SQL> insert into DEPT values(’52′,’IT’,'SBY’);
SQL> COMMIT;
SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC
———- ————– ————-
50 IT SBY
51 IT SBY
52 IT SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7 rows selected.

b. Session II data rusak / terhapus
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/users01.dbf

c. Session III database restart
SQL> shutdown abort;
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: ‘/u02/oradata/iscdd/users01.dbf’

d. penyelamatan
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 19 10:36:30 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995, not open)
using target database control file instead of recovery catalog

RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 222299736 bytes
Database Buffers 771751936 bytes
Redo Buffers 7163904 bytes

RMAN> restore datafile 4;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 19-AUG-08

RMAN> recover database;
Starting recover at 19-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-AUG-08

RMAN> alter database open;
database opened

RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$
[oracle@iscdd ~]$ sqlplus scott/tiger@iscdd
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 19 10:38:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
50 IT SBY
51 IT SBY
52 IT SBY
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7 rows selected.

26. Kasus hilangnya controlfile
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/*.ctl

[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 19 10:43:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 226494040 bytes
Database Buffers 767557632 bytes
Redo Buffers 7163904 bytes
ORA-00205: error in identifying control file, check alert log for more info

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 19 10:44:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: iscdd (not mounted)
using target database control file instead of recovery catalog

RMAN> set dbid 2055978995;
executing command: SET DBID

RMAN> restore controlfile from autobackup;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
recovery area destination: /u02/oradata/flash_recovery_area/
database name (or database unique name) used for search: ISCDD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154943_4bndrlqh_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/iscdd/control01.ctl
output filename=/u02/oradata/iscdd/control02.ctl
output filename=/u02/oradata/iscdd/control03.ctl
Finished restore at 19-AUG-08

RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area 1002438656 bytes
Fixed Size 1223080 bytes
Variable Size 226494040 bytes
Database Buffers 767557632 bytes
Redo Buffers 7163904 bytes

RMAN> restore database;
Starting restore at 19-AUG-08
Starting implicit crosscheck backup at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 19-AUG-08

Starting implicit crosscheck copy at 19-AUG-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-AUG-08

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154943_4bndrlqh_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/system01.dbf
restoring datafile 00002 to /u02/oradata/iscdd/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/iscdd/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/iscdd/users01.dbf
restoring datafile 00005 to /u02/oradata/iscdd/example01.dbf
restoring datafile 00006 to /u02/oradata/iscdd/rman_ts01.dbf
restoring datafile 00007 to /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /u02/oradata/iscdd/senin_ts01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 19-AUG-08

RMAN> recover database;
Starting recover at 19-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
destination for restore of datafile 00002: /u02/oradata/iscdd/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/iscdd/sysaux01.dbf
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
destination for restore of datafile 00005: /u02/oradata/iscdd/example01.dbf
destination for restore of datafile 00006: /u02/oradata/iscdd/rman_ts01.dbf
destination for restore of datafile 00007: /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
destination for restore of datafile 00002: /u02/oradata/iscdd/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/iscdd/sysaux01.dbf
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
destination for restore of datafile 00005: /u02/oradata/iscdd/example01.dbf
destination for restore of datafile 00006: /u02/oradata/iscdd/rman_ts01.dbf
destination for restore of datafile 00007: /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery
archive log thread 1 sequence 9 is already on disk as file /u02/archive/archive011_9_662546934.dbf
archive log thread 1 sequence 10 is already on disk as file /u02/oradata/iscdd/redo03.log
archive log thread 1 sequence 11 is already on disk as file /u02/oradata/iscdd/redo01.log
archive log thread 1 sequence 12 is already on disk as file /u02/oradata/iscdd/redo02.log
archive log filename=/u02/archive/archive011_9_662546934.dbf thread=1 sequence=9
archive log filename=/u02/oradata/iscdd/redo03.log thread=1 sequence=10
archive log filename=/u02/oradata/iscdd/redo01.log thread=1 sequence=11
archive log filename=/u02/oradata/iscdd/redo02.log thread=1 sequence=12
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-AUG-08

RMAN> alter database open resetlogs;
database opened

RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$

27. script untuk melakukan backup
[oracle@iscdd ~]$ mkdir /home/oracle/script -p
[oracle@iscdd ~]$ vi /home/oracle/script/daily_incremental.sh
#! /bin/bash
# Daily incremental backup
#
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd $ORACLE_HOME/bin
./rman @/home/oracle/script/daily_incremental.rman LOG /home/oracle/script/daily_incremental.rman.log

[oracle@iscdd ~]$ chmod +x /home/oracle/script/daily_incremental.sh
[oracle@iscdd ~]$ vi /home/oracle/script/daily_incremental.rman
CONNECT TARGET sys/oracle0@orcl6
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG=daily_incremental;
EXIT;

[oracle@iscdd ~]$ vi /home/oracle/script/weekly_full.sh
#! /bin/bash
# Weekly full backup
#
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd $ORACLE_HOME/bin
./rman @/home/oracle/script/weekly_full.rman LOG /home/oracle/script/weekly_full.rman.log

[oracle@iscdd ~]$ chmod +x /home/oracle/script/weekly_full.sh
[oracle@iscdd ~]$ vi /home/oracle/script/weekly_full.rman
CONNECT TARGET sys/ora@iscdd
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG=full_backup;
delete noprompt obsolete;
EXIT;

28. crontab untuk melakukan backup
[root@iscdd ~]# crontab -e
0 10 * * 1-6 /home/oracle/script/daily_incremental.sh
0 10 * * 7 /home/oracle/script/weekly_full.sh

29. hasil crontab untuk backup
[root@iscdd ~]# date
Tue Aug 19 11:03:42 WIT 2008
[root@iscdd ~]# ls -la /home/oracle/script/
total 24
drwxr-xr-x 2 oracle oinstall 4096 Aug 19 11:01 .
drwx—— 18 oracle oinstall 4096 Aug 19 11:01 ..
-rw-r–r– 1 oracle oinstall 95 Aug 19 10:59 daily_incremental.rman
-rwxr-xr-x 1 oracle oinstall 220 Aug 19 10:58 daily_incremental.sh
-rw-r–r– 1 oracle oinstall 115 Aug 19 11:01 weekly_full.rman
-rwxr-xr-x 1 oracle oinstall 203 Aug 19 11:00 weekly_full.sh

[root@iscdd ~]# date -s 23:55:00

RMAN Backup dan Recovery Oracle 10g

Sebagai acuan untuk prosedur backup dan recovery dengan RMAN

1. RMAN dapat melakukan back up pada semua file database yang dibutuhkan untuk recovery pada saat terjadi kerusakan. RMAN support back up file-file
a. Datafile, dan image copy dari datafiles
b. Control file, dan image copy dari control file
c. Archived redo logs
d. file parameter server
e. Backup pieces, yang berisi backup lain yang dibuat oleh RMAN

2. Menggunakan RMAN O/S authentication
[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> exit
Recovery Manager complete.

3. Menggunakan RMAN Password authentication
[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd ~]$ ./rman target system/ora NOCATALOG
RMAN> exit
Recovery Manager complete.

4. Konfigurasi RMAN agar dapat diakses oleh user tertentu.
SQL> create tablespace rman_ts
datafile ‘/u02/oradata/iscdd/rman_ts01.dbf’
size 5M
segment space management auto;

SQL> alter tablespace rman_ts
Add datafile ‘/u02/oradata/iscdd/rman_ts02.dbf’
size 5M;

SQL> create user rman_user identified by rman123
default tablespace rman_ts
quota unlimited on rman_ts;

SQL> grant connect, resource, recovery_catalog_owner to rman_user;
SQL> exit

5. Konfigurasi RMAN, setting catalog dengan user tertentu.
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 15 10:25:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995)
connected to recovery catalog database
RMAN> create catalog tablespace rman_ts;
recovery catalog created
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit
Recovery Manager complete.

6. Untuk mengetahui apakah sudah ter-rigister
[oracle@iscdd bin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 15 13:56:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from rman_user.rc_database;
NAME
——–
ISCDD
SQL> exit

7. Melihat konfigurasi RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
RMAN> SHOW ALL;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/10.1.0/db_1/dbs/snapcf_edxx.f’; # default

8. Konfigurasi RMAN untuk menyimpan control file secara otomatis
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

9. Konfigurasi RMAN untuk set obsolote backup, default 1 hari misal diganti menjadi 2 hari.
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN> CONFIGURE RETENTION POLICY to recovery window of 2 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored

RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

10. Konfigurasi RMAN agar archive log disimpan pada folder tertentu sesuai dengan DB_RECOVERY_FILE_DEST.
[oracle@iscdd bin]$ sqlplus / as sysdba
SQL> host
[oracle@iscdd ~]$ mkdir /u02/archive
[oracle@iscdd ~]$ exit

SQL> alter system set log_archive_dest_1=’location=/u02/archive/archive01′;
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/archive01
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6

11. Konfigurasi RMAN pada database harus dalam mode archive log.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/archive01
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> exit

12. Lewat RMAN untuk mengetahui tablespace dan datafilenya.
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 15 14:08:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995)
connected to recovery catalog database
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM YES /u02/oradata/iscdd/system01.dbf
2 25 UNDOTBS1 YES /u02/oradata/iscdd/undotbs01.dbf
3 230 SYSAUX NO /u02/oradata/iscdd/sysaux01.dbf
4 5 USERS NO /u02/oradata/iscdd/users01.dbf
5 100 EXAMPLE NO /u02/oradata/iscdd/example01.dbf
6 5 RMAN_TS NO /u02/oradata/iscdd/rman_ts01.dbf
7 5 RMAN_TS NO /u02/oradata/iscdd/rman_ts02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 20 TEMP 32767 /u02/oradata/iscdd/temp01.dbf

13. Setup backup optimization, agar file yang tidak mengalami perubahan tidak perlu dibackup lagi
RMAN> configure backup optimization on;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

14. Tempat penyimpanan backup, sesuai dengan parameter CHANNEL, jika tidak diisi secara default disimpan di DEFAULT DEVICE TYPE, Jika menggunakan flash recovery area maka secara default disimpan di DB_RECOVERY_FILE_DEST.
RMAN> configure channel device type disk format ‘/home/oracle/backup/%d-id-%I-%s.bkp’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/home/oracle/backup/%d-id-%I-%s.bkp’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
%d : database name
%I : nomer DBID
%s : nomer sequence

15. RMAN backup controlfile
[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 13 23:26:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> CONNECT TARGET sys/ora@iscdd
connected to target database: ISCDD (DBID=2055978995)
RMAN> backup current controlfile;
Starting backup at 15-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-1.bkp tag=TAG20080815T103342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08 piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812428_4b9y8zdr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08

16. RMAN backup controlfile
RMAN> backup current controlfile format
‘/home/oracle/backup/control.bkp’;

Starting backup at 15-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/control.bkp tag=TAG20080815T103649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08 piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812616_4b9ygv40_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08

17. RMAN backup spfile
RMAN> backup spfile format ‘/home/oracle/backup/spfile.bkp’;
Starting backup at 15-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/spfile.bkp tag=TAG20080815T103938 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08
RMAN> exit
Recovery Manager complete.

18. Copy spfile
SPFILE, Server Parameter File, adalah sebuah file binary yang digunakan oracle sebagai parameter initialization. Parameter dapat diubah menggunakan ALTER SYSTEM SET, dimana parameter hanya berubah pada SPFILE.
PFILE adalah traditional text based dari parameter file init.ora. Umumnya disimpan pada directory $ORACLE_BASE/admin/SID/pfile, dan symbolic link berada di directory $ORACLE_HOME/dbs.
[oracle@iscdd bin]$ sqlplus sys/ora@iscdd as sysdba;
SQL> CREATE PFILE = ‘/home/oracle/backup/initorcl.ora’ FROM SPFILE;
SQL> exit
[oracle@iscdd bin]$ cat /home/oracle/backup/initorcl.ora | more
iscdd.__db_cache_size=713031680
iscdd.__java_pool_size=4194304
iscdd.__large_pool_size=4194304
iscdd.__shared_pool_size=264241152
iscdd.__streams_pool_size=8388608
*.audit_file_dest=’/u01/app/oracle/admin/iscdd/adump’
*.background_dump_dest=’/u01/app/oracle/admin/iscdd/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/iscdd/control01.ctl’,'/u02/oradata/iscdd/control02
.ctl’,'/u02/oradata/iscdd/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/iscdd/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’iscdd’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area/’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=iscddXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/archive01′
*.open_cursors=300
*.pga_aggregate_target=333447168
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1002438656
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/iscdd/udump’

19. Melakukan proses backup
a. full backup
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG=full_backup;
Starting backup at 15-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/rman_ts01.dbf
input datafile fno=00007 name=/u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-9.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:22
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08

b. backup incremental
RMAN> backup incremental level 1 tag ‘level_1′ database;
Starting backup at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/rman_ts01.dbf
input datafile fno=00007 name=/u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: starting piece 1 at 19-AUG-08
channel ORA_DISK_1: finished piece 1 at 19-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
Finished backup at 19-AUG-08
Starting Control File and SPFILE Autobackup at 19-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153295_4bnc521b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08

c. strategi backup
Minggu : full backup ? backup incremental level 0
Senin – Sabtu : block – block yang diperbarui ? backup
incremental level 1
Hari minggu :
RMAN> backup incremental level 0 tag ‘level_0′ database;
Hari senin - sabtu :
RMAN> backup incremental level 1 tag ‘level_1′ database;

Remote Install Oracle (GUI Linux)


1. download Xming-mesa-6-9-0-31-setup.exe
http://sourceforge.net/project/downloading.php? group_id=156984&filename=Xming-mesa-6-9-0-31-setup.exe
2. download putty.exe
http://the.earth.li/~sgtatham/putty/0.60/x86/putty.exe
3. In the PuTTY configuration window, select “Connection->SSH->X11″ and make sure the “Enable X11 forwarding box is checked
4. edit /etc/ssh/sshd_config
[root@bb ~]# vi /etc/ssh/sshd_config
# X11 tunneling options
X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes
5. reload SSH
[root@bb ~]# service sshd reload
Reloading sshd: [ OK ]
6. running xming seperti gambar
7. running putty
seperti gambar

Oracle 11g RAC (ASM) to single instance (non ASM) - clone database

Clone database from Oracle 11g RAC (ASM) to single instance (non ASM)
===============================================================
komputer source
===============================================================
1. Setting RMAN
[oracle@node1 bin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 11.1.0.6.0 - Production on Tue Sep 23 15:57:07 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NODE (DBID=711660360)
using target database control file instead of recovery catalog
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> host;
[oracle@node1 bin]$ su - root
Password:
[root@node1 ~]# mkdir /u03/backup/node/ -p
[root@node1 ~]# chown oracle.oinstall /u03 -R
[root@node1 ~]# exit
exit
host command complete
RMAN> configure channel device type disk format ‘/u03/backup/node/%d-id-%I-%s.bkp’;
RMAN> exit;

2. Test dengan menambahkan record
a. tambahan record.
SQL> conn scott/tiger;
SQL> insert into dept values(61,’IT’,’surabaya’);
SQL> insert into dept values(62,’IT’,'madiun’);
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
SQL> exit;
c. list tablespace
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> column tablespace_name format a10
SQL> column file_name format a55
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE FILE_NAME
———- ——————————————————-
USERS +DATA1/node/datafile/users.259.666184225
UNDOTBS1 +DATA1/node/datafile/undotbs1.258.666184225
SYSAUX +DATA1/node/datafile/sysaux.257.666184225
SYSTEM +DATA1/node/datafile/system.256.666184225
EXAMPLE +DATA1/node/datafile/example.267.666184357
UNDOTBS2 +DATA1/node/datafile/undotbs2.268.666184499
6 rows selected.
SQL> exit;
3. Lakukan backup ditambah dengan archive log
a. lakukan full backup
[oracle@node1 bin]$ cd $ORACLE_HOME/bin
[oracle@node1 bin]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Sep 26 11:12:43 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NODE (DBID=711660360)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE plus archivelog delete input TAG=full_backup_260908;

b. delete obsolote backup dan lihat daftar backupset
RMAN> delete noprompt obsolete;
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
11 Full 1.16G DISK 00:00:41 26-SEP-08
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20080926T111320
Piece Name: /home/oracle/clone/0bjri7qg_1_2
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 1033174 26-SEP-08 +DATA1/node/datafile/system.256.666184225
2 Full 1033174 26-SEP-08 +DATA1/node/datafile/sysaux.257.666184225
3 Full 1033174 26-SEP-08 +DATA1/node/datafile/undotbs1.258.666184225
4 Full 1033174 26-SEP-08 +DATA1/node/datafile/users.259.666184225
5 Full 1033174 26-SEP-08 +DATA1/node/datafile/example.267.666184357
6 Full 1033174 26-SEP-08 +DATA1/node/datafile/undotbs2.268.666184499
7 Full 1033174 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
15 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20080926T111741
Piece Name: /home/oracle/clone/controlfile
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1034682 Ckp time: 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
21 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115138
Piece Name: +DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1041704 Ckp time: 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
24 Incr 0 1.16G DISK 00:00:41 26-SEP-08
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115816
Piece Name: /u03/backup/node/NODE-id-711660360-25.bkp
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/system.256.666184225
2 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/sysaux.257.666184225
3 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/undotbs1.258.666184225
4 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/users.259.666184225
5 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/example.267.666184357
6 0 Incr 1066400 26-SEP-08 +DATA1/node/datafile/undotbs2.268.666184499

BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
25 32.00K DISK 00:00:00 26-SEP-08
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: FULL_BACKUP_260908
Piece Name: /u03/backup/node/NODE-id-711660360-26.bkp

List of Archived Logs in backup set 25
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 59 1066370 26-SEP-08 1066474 26-SEP-08
2 44 1066373 26-SEP-08 1066471 26-SEP-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
26 Full 17.98M DISK 00:00:02 26-SEP-08
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20080926T115910
Piece Name: +DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353
SPFILE Included: Modification time: 26-SEP-08
SPFILE db_unique_name: NODE
Control File Included: Ckp SCN: 1066483 Ckp time: 26-SEP-08

c. copykan backupset fullbackup ke local file system, dimana terlihat pada langkah diatas
BS (Backup Set) no 11 adalah BS full backup
[oracle@node1 bin]$ mkdir /home/oracle/clone/ -p
[oracle@node1 bin]$ exit
RMAN> backup backupset 24 format ‘/home/oracle/clone/%U’;

d. juga lakukan backup secara manual spfile dan control file
backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
RMAN> backup spfile include current controlfile format ‘/home/oracle/clone/controlfile’;
RMAN> exit
e. periksa hasil backup backupset dan controlfile yang akan dibutuhkan untuk clone
[oracle@node1 bin]$ ls /home/oracle/clone/
0pjriaep_1_2 controlfile
f. pindahkan hasil backup ke komputer target
[oracle@node1 bin]$ cd /home/oracle/clone/
[oracle@node1 clone]$ scp 0pjriaep_1_2 controlfile 172.20.141.153:/home/oracle/clone
oracle@172.20.141.153’s password:

===============================================================
komputer target
Sudah diinstall oracle database software
===============================================================
4. Menyiapkan directory admin oracle, datafile oracle dan oratab.
a. menyiapkan folder admin oracle clon
e
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/adump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/bdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/cdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/dpdump
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/pfile
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/scripts
[oracle@test ~]$ mkdir -p /u01/app/oracle/admin/node/udump
[oracle@test ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
b. menyiapkan folder datafile dan archive oracle clone
[oracle@test ~]$ mkdir -p /u01/oradata/node
[oracle@test ~]$ mkdir -p /u01/oradata/node/controlfile/

[oracle@test ~]$ mkdir -p /u01/archive/node
c. menyiapkan oratab
[oracle@test db_1]$ echo “node:/u01/app/oracle/product/11.1.0/db_1:N” >> /etc/oratab
[oracle@test db_1]$ ORACLE_SID=node; export ORACLE_SID
[oracle@test db_1]$ oraenv
ORACLE_SID = [node] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle

5. Menyiapkan Oracle Password File oracle clone
[oracle@test db_1]$ orapwd file=$ORACLE_HOME/dbs/orapwnode password=ora
6. Restore SPFILE
[oracle@test ~]$ rman
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Sep 26 04:38:49 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target;
connected to target database (not started)
RMAN> restore spfile from ‘/home/oracle/clone/controlfile’;
Starting restore at 26-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/clone/controlfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-SEP-08

RMAN> exit
Recovery Manager complete.
[oracle@test ~]$ sqlplus / as sysdba
SQL> create pfile from spfile;
File created.
SQL> exit

7. Edit PFILE
a. asli pfile

[oracle@test ~]$ vi $ORACLE_HOME/dbs/initnode.ora
node2.__db_cache_size=322961408
node1.__db_cache_size=281018368
node2.__java_pool_size=4194304
node1.__java_pool_size=12582912
node2.__large_pool_size=4194304
node1.__large_pool_size=4194304
node1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
node2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
node2.__pga_aggregate_target=293601280
node1.__pga_aggregate_target=343932928
node2.__sga_target=562036736
node1.__sga_target=511705088
node2.__shared_io_pool_size=0
node1.__shared_io_pool_size=0
node2.__shared_pool_size=222298112
node1.__shared_pool_size=205520896
node2.__streams_pool_size=0
node1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/node/adump’
*.audit_trail=’db’
*.cluster_database_instances=2
*.cluster_database=true
*.compatible=’11.1.0.0.0′
*.control_files=’+DATA1/node/controlfile/current.261.666184329′,’+DATA1/node/controlfile/current.260.666184329′
*.db_block_size=8192
*.db_create_file_dest=’+DATA1′
*.db_domain=”
*.db_name=’node’
*.db_recovery_file_dest=’+DATA1′
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=nodeXDB)’
node1.instance_number=1
node2.instance_number=2
*.log_archive_dest_1=’location=/u03/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=852492288
*.open_cursors=300
*.processes=150
*.remote_listener=’LISTENERS_NODE’
*.remote_login_passwordfile=’exclusive’
node2.thread=2
node1.thread=1
node1.undo_tablespace=’UNDOTBS1′
node2.undo_tablespace=’UNDOTBS2′
b. del semua informasi, sisakan 1 node (example del node2.*)
c. del informasi cluster

*.cluster_database_instances=2
*.cluster_database=true
*.remote_listener=’LISTENERS_NODE’
node1.instance_number=1
node1.thread=1
d. add ASM to non-ASM
*.db_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.log_file_name_convert=’+DATA1′,’/u01/oradata/node’
e. menjadi :
*.__db_cache_size=281018368
*.__java_pool_size=12582912
*.__large_pool_size=4194304
*.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/node/adump’
*.compatible=’11.1.0.0.0′
*.control_files=’/u01/oradata/node/controlfile/control01.ctl’,'/u01/oradata/node/controlfile/control02.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u01/oradata/node’
*.db_domain=”
*.db_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.db_name=’node’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=nodeXDB)’
*.log_file_name_convert=’+DATA1′,’/u01/oradata/node’
*.open_cursors=300
*.__pga_aggregate_target=343932928
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.__sga_target=511705088
*.__shared_io_pool_size=0
*.__shared_pool_size=205520896
*.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
*.audit_trail=’db’
*.diagnostic_dest=’/u01/app/oracle’
*.log_archive_dest_1=’location=/u01/archive/node/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=852492288
*.undo_tablespace=’UNDOTBS1′
8. create SPFILE dari PFILE yang sudah diedit
[oracle@test ~]$ ORACLE_SID=node; export ORACLE_SID
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 26 07:19:04 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 566234420 bytes
Database Buffers 281018368 bytes
Redo Buffers 5160960 bytes
SQL> exit

9. Restore Controlfile
[oracle@test ~]$ su - root
Password:
[root@test ~]# chown oracle.oinstall /u01 -R
[oracle@test ~]$ rman target /
RMAN> restore controlfile from ‘/home/oracle/clone/controlfile’;
Starting restore at 26-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
output file name=/u01/oradata/node/controlfile/control01.ctl
output file name=/u01/oradata/node/controlfile/control02.ctl
Finished restore at 26-SEP-08
10. Restore Database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 26-SEP-08
Starting implicit crosscheck backup at 26-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 26-SEP-08

Starting implicit crosscheck copy at 26-SEP-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-SEP-08

searching for all files in the recovery area
cataloging files…
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA1/node/datafile/system.256.666184225
channel ORA_DISK_1: restoring datafile 00002 to +DATA1/node/datafile/sysaux.257.666184225
channel ORA_DISK_1: restoring datafile 00003 to +DATA1/node/datafile/undotbs1.258.666184225
channel ORA_DISK_1: restoring datafile 00004 to +DATA1/node/datafile/users.259.666184225
channel ORA_DISK_1: restoring datafile 00005 to +DATA1/node/datafile/example.267.666184357
channel ORA_DISK_1: restoring datafile 00006 to +DATA1/node/datafile/undotbs2.268.666184499
channel ORA_DISK_1: reading from backup piece /home/oracle/clone/0pjriaep_1_2
channel ORA_DISK_1: piece handle=/home/oracle/clone/0pjriaep_1_2 tag=TAG20080926T115816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:17:56
Finished restore at 26-SEP-08
RMAN> exit
Recovery Manager complete.

11. Recover Database
[oracle@test ~]$ sqlplus / as sysdba
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1066400 generated at 09/26/2008 11:58:17 needed for thread 1
ORA-00289: suggestion : /u01/archive/node/archive011_59_666184331.dbf
ORA-00280: change 1066400 for thread 1 is in sequence #59
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

12. test apakah data terakhir sudah dipindahkan ?
SQL> conn scott/tiger
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit

13. membuat listener.ora
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER_NODE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.141.153)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

14. menghidupkan listener.ora
[oracle@test ~]$ lsnrctl start LISTENER_NODE

15. membuat tnsnames.ora
[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
NODE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.141.153)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = node)
(INSTANCE_NAME = node)
)
)

16. test connection
[oracle@test ~]$ sqlplus scott/tiger@node
17. bersih - bersih data rman di target komputer
RMAN> delete noprompt force backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
15 11 1 2 EXPIRED DISK /home/oracle/clone/0bjri7qg_1_2
17 15 1 1 EXPIRED DISK /home/oracle/clone/controlfile
23 21 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901
26 24 1 1 EXPIRED DISK /u03/backup/node/NODE-id-711660360-25.bkp
29 24 1 2 AVAILABLE DISK /home/oracle/clone/0pjriaep_1_2
27 25 1 1 EXPIRED DISK /u03/backup/node/NODE-id-711660360-26.bkp
28 26 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353
30 27 1 1 EXPIRED DISK +DATA1/node/autobackup/2008_09_26/s_666446563.339.666446565
31 28 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/NODE/autobackup/2008_09_29/o1_mf_s_666680379_4fzzlpmm_.bkp
deleted backup piece
backup piece handle=/home/oracle/clone/0bjri7qg_1_2 RECID=15 STAMP=666443807
deleted backup piece
backup piece handle=/home/oracle/clone/controlfile RECID=17 STAMP=666443862
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666445898.325.666445901 RECID=23 STAMP=666445900
deleted backup piece
backup piece handle=/u03/backup/node/NODE-id-711660360-25.bkp RECID=26 STAMP=666446299
deleted backup piece
backup piece handle=/home/oracle/clone/0pjriaep_1_2 RECID=29 STAMP=666446518
deleted backup piece
backup piece handle=/u03/backup/node/NODE-id-711660360-26.bkp RECID=27 STAMP=666446349
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666446350.334.666446353 RECID=28 STAMP=666446352
deleted backup piece
backup piece handle=+DATA1/node/autobackup/2008_09_26/s_666446563.339.666446565 RECID=30 STAMP=666446565
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/NODE/autobackup/2008_09_29/o1_mf_s_666680379_4fzzlpmm_.bkp RECID=31 STAMP=666680390
Deleted 9 objects

RMAN> delete noprompt force copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
List of Archived Log Copies for database with db_unique_name NODE
=====================================================================
Key Thrd Seq S Low Time
——- —- ——- - ———
102 2 1 A 29-SEP-08
Name: /u01/archive/node/archive012_1_666680175.dbf

deleted archived log
archived log file name=/u01/archive/node/archive012_1_666680175.dbf RECID=102 STAMP=666680349
Deleted 1 objects

RMAN> exit
sumber :
http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_30.shtml

oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql

oracle 11g RAC : Starting and Stopping Instances and RAC databases with srvctl and sql

==================
A. srvctl
==================
1. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2

2. Stopping Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl stop database -d node

3. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is not running on node node1
Instance node2 is not running on node node2

4. Starting Instances and RAC databases with srvctl
[oracle@node1 ~]$ srvctl start database -d node

5. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2

6. Stopping Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl stop instance -d node -i node2

7. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is not running on node node2

8. Starting Instances and RAC databases with srvctl (not all instance)
[oracle@node1 ~]$ srvctl start instance -d node -i node2

9. status database with srvctl
[oracle@node1 ~]$ srvctl status database -d node
Instance node1 is running on node node1
Instance node2 is running on node node2

==================
B. sql command
==================
10. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node1
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node1 node1 STARTED 1 OPEN
node2 node2 STARTED 2 OPEN
sql> exit;
11. Stopping Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
sql> shutdown;
sql> exit;
[oracle@node1 ~]$

12. status database with srvctl
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
sql> exit;
[oracle@node1 ~]$

13. Starting Instances and RAC databases with sql (not all instance, in ex : node1)
[oracle@node1 ~]$ sqlplus sys/oracle0@node1 as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Sep 23 13:46:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor

Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 853716992 bytes
Fixed Size 1303244 bytes
Variable Size 557845812 bytes
Database Buffers 289406976 bytes
Redo Buffers 5160960 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node1 ~]$

=========================================
Note use command : sqlplus / as sysdba
=========================================
ERROR:
ORA-12521: TNS:listener does not currently know of instance requested in
connect descriptor

14. status database with sql
[oracle@node1 ~]$ sqlplus system/oracle0@node2
sql> column instance_name format a10
sql> column host_name format a10
sql> column archiver format a10
sql> column status format a10
sql> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_N HOST_NAME ARCHIVER THREAD# STATUS
———- ———- ———- ———- ———-
node2 node2 STARTED 2 OPEN
node1 node1 STARTED 1 OPEN

Pemrograman PostgreSQL

1. start, shutdown postgresql
[root@bb bb]# service postgresql start
Initializing database: [ OK ]
Starting postgresql service: [ OK ]
[root@bb bb]# chkconfig postgresql on
[root@bb bb]# su potsgres
su: user potsgres does not exist
[root@bb bb]# su - postgres

2. createdb, dropdb
-bash-3.1$ createdb test
CREATE DATABASE
-bash-3.1$ psql test
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# \q

-bash-3.1$ dropdb test
DROP DATABASE

3. createuser, dropuser
-bash-3.1$ createuser bb
Shall the new role be a superuser? (y/n) y
CREATE ROLE

-bash-3.1$ dropuser bb
DROP ROLE

4. createuser & createdb
-bash-3.1$ createuser bb -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE

-bash-3.1$ createdb bb
CREATE DATABASE
-bash-3.1$ exit
logout

[root@bb ~]# su - bb
[bb@bb ~]$ psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

-bash-3.1$ createuser admin -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE

5. edit configuration
error : postgresql fatal: ident authentication failed for user

[bb@bb ~]$ su - root
Password:
[root@bb ~]# vi /var/lib/pgsql/data/pg_hba.conf
# “local” is for Unix domain socket connections only
local all all password
host all all 127.0.0.1/32 password
host all all 172.20.0.0/16 password
host all all ::1/128 trust

[root@bb ~]# vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = ‘*’

[root@bb ~]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
[root@bb ~]# exit

6. general command in PostgreSQL
\l :List databases
\c database-name :List databases
\d :List tables in database
\d table-name :D escribe table
select * from table-name :List table contents

[bb@bb ~]$ psql -U bb -d bb -W
Password for user bb:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
bb=# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
bb | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test2 | bb | UTF8
(5 rows)

bb=# \c
Password:
You are now connected to database “bb” as user “bb”.
bb=# \d
No relations found.

7. select database
bb=# \c test2
Password for user bb:
You are now connected to database “test2″.
test2=# \d
List of relations
Schema | Name | Type | Owner
——–+——————-+———-+——-
public | barang | table | bb
public | barang_id_seq | sequence | bb

test2=# \q
[bb@bb ~]$

8. test connection
[admin@bb ~]$ psql -U admin -d test2 -W
Password for user admin:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
test2=# \q

9. test connection from ip
[admin@bb ~]$ psql -U admin -d test2 -h 172.20.88.88 -W
Password for user admin:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
test2=# \q

clone database oracle

===============================================================
source
===============================================================
1. Konfigurasi RMAN pada database harus dalam mode archive log.
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2. Konfigurasi RMAN agar archive log disimpan pada folder tertentu sesuai dengan DB_RECOVERY_FILE_DEST.
SQL> host;
[oracle@iscdd ~]$ mkdir /u02/archive/iscdd
[oracle@iscdd ~]$ exit
SQL> alter system set log_archive_dest_1=’location=/u02/archive/iscdd/archive01′;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/iscdd/archive01
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> exit;

3. Setting RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 12 21:51:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
using target database control file instead of recovery catalog
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> host;
[oracle@iscdd bin]$ mkdir /home/oracle/backup/iscdd/ -p
[oracle@iscdd bin]$ exit
RMAN> configure channel device type disk format ‘/home/oracle/backup/iscdd/%d-id-%I-%s.bkp’;
RMAN> exit;

4. Test
a. tambahan tablespace
[oracle@iscdd ~]$ ORACLE_SID=iscdd; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> create tablespace senin_ts
datafile ‘/u02/oradata/iscdd/iscdd/senin_ts01.dbf’
size 2M
segment space management auto;

b. create pfile
SQL> create pfile=’$ORACLE_HOME/dbs/initclone.ora’ from spfile;

c. tambahkan record.
SQL> conn scott/tiger;
SQL> insert into dept values(61,’IT’,’surabaya’);
SQL> insert into dept values(62,’IT’,'madiun’);
SQL> commit;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
SQL> exit;

5. Pastikan RMAN
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> delete noprompt obsolete;
RMAN> backup database plus archivelog delete input TAG=clone_backup;
RMAN> exit;

===============================================================
target
===============================================================
6. Menyiapkan directory admin oracle, datafile oracle dan oratab.
Misalkan diinginkan SID bernama clone
[oracle@iscdd ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)

a. menyiapkan folder admin oracle clone
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/adump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/bdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/cdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/dpdump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/pfile
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/scripts
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/admin/clone/udump
[oracle@iscdd ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

b. menyiapkan folder datafile dan archive oracle clone
[oracle@iscdd ~]$ mkdir -p /u02/oradata/clone/iscdd
[oracle@iscdd ~]$ mkdir -p /u02/archive/clone

c. menyiapkan oratab
[oracle@iscdd ~]$ echo “clone:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ oraenv
ORACLE_SID = [clone] ?

7. Menyiapkan Oracle Password File oracle clone
[oracle@iscdd ~]$ orapwd file=$ORACLE_HOME/dbs/orapwclone password=ora

8. Menyiapkan file init untuk clone
[oracle@iscdd ~]$ vi $ORACLE_HOME/dbs/initclone.ora

tambahkan dan edit, jika folder berbeda dengan perintah :
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’

======================
selengkapnya :
====================

clone.__db_cache_size=717225984
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__shared_pool_size=268435456
clone.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/clone/adump’
*.background_dump_dest=’/u01/app/oracle/admin/clone/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/clone/clone/control01.ctl’,'/u02/oradata/clone/clone/control02.ctl’,'/u02/oradata/clone/clone/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/clone/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.log_file_name_convert=’/u02/oradata/iscdd’,'/u02/oradata/clone’
*.db_name=’clone’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=cloneXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/clone/archive01′
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=333447168
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1002438656
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/clone/udump’

9. Create SPFILE clone
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 12 14:21:20 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/initclone.ora’;

10. Startup nomount clone
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> exit

11. RMAN Clone
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ rman TARGET sys/ora@iscdd AUXILIARY /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 15 22:30:11 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2058697900)
connected to auxiliary database: CLONE (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO “CLONE”;
RMAN> exit;

12. test apakah data terakhir sudah dipindahkan ?
[oracle@iscdd ~]$ ORACLE_SID=clone; export ORACLE_SID
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> conn scott/tiger
Connected.

SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit

13. Configure tnsnames.ora and listner.ora instance clone
[oracle@iscdd ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
clone =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clone)
)
)

[oracle@iscdd ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = iscdd)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = clone)(PORT = 1521))
)
)

[oracle@iscdd iscdd]$ lsnrctl reload

14. test apakah listener ok ?
[oracle@iscdd iscdd]$ sqlplus scott/tiger@clone
SQL> select * from dept;
DEPTNO DNAME LOC
—— ————– ————-
61 IT surabaya
62 IT madiun
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

6 rows selected.
SQL> exit

Automatic Startup - Shutdown LISTENER ORACLE 11g

1. create script status listener
[oracle@mkt ~]$ cd $ORACLE_HOME/bin
[oracle@mkt bin]$ vi statuslsnr
#!/bin/sh
# Start the oracle listener
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=mkt
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=ubs
export ORACLE_TERM=xterm
$ORACLE_HOME/bin/lsnrctl status
exit 0

[oracle@mkt ~]$ chmod u+x statuslsnr

2. create script start listener
[oracle@mkt ~]$ cd $ORACLE_HOME/bin
[oracle@mkt bin]$ vi startlsnr
#!/bin/sh
# Start the oracle listener
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=mkt
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=ubs
export ORACLE_TERM=xterm
$ORACLE_HOME/bin/lsnrctl start
exit 0

[oracle@mkt bin]$ chmod u+x startlsnr

3. create script stop listener
[oracle@mkt ~]$ cd $ORACLE_HOME/bin
[oracle@mkt bin]$ vi stoplsnr
#!/bin/sh
# Start the oracle listener
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=mkt
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=ubs
export ORACLE_TERM=xterm
$ORACLE_HOME/bin/lsnrctl stop
exit 0

[oracle@mkt bin]$ chmod u+x stoplsnr

4. create script status,start dan stop listener
[oracle@mkt ~]$ cd $ORACLE_HOME/bin
[oracle@mkt bin]$ vi listener11g
#!/bin/sh
#
# chkconfig: 345 51 49
# description: startup and shutdown the Oracle 11g listener
#
echo “Oracle 11g listener start/stop/status”
ORA_OWNER=oracle
ORA_BASE=/u01/app/oracle
ORA_HOME=$ORA_BASE/product/11.1.0/db_1

case “$1″ in
’start’)
# Start the listener
echo -n “Starting the Listener for 11g: “
su - $ORA_OWNER -c $ORA_HOME/bin/startlsnr
echo
;;

’stop’)
# Stop the listener
echo -n “Shutting down Listener for 11g: “
su - $ORA_OWNER -c $ORA_HOME/bin/stoplsnr
echo
;;

’status’)
# Status the listener
echo -n “Status Listener for 11g: “
su - $ORA_OWNER -c $ORA_HOME/bin/statuslsnr
echo
;;

‘restart’)
# Restart the Oracle databases:
echo -n “Restarting Listener for 11g:”
$0 stop
$0 start
echo
;;
*)

echo “Usage: listener11g [ status | start | stop | restart }"
exit 1
esac
exit 0

[oracle@ubs bin]$ chmod u+x listener11g

4. copy script listener11g to /etc/rc.d/init.d/
[oracle@mkt bin]$ su - root
Password:
[root@mkt ~]# cp /u01/app/oracle/product/11.1.0/db_1/bin/listener11g /etc/rc.d/init.d/
[root@mkt ~]# chmod 700 /etc/rc.d/init.d/listener11g

5. test script listener11g login as root
[root@mkt ~]# cd /etc/rc.d/init.d
[root@mkt init.d]# ./listener11g
Oracle 11g listener start/stop/status
Usage: listener11g [ status | start | stop | restart }

[root@mkt init.d]# ./listener11g status
Oracle 11g listener start/stop/status
Status Listener for 11g:
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 09-DEC-2008 14:41:00
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mkt.localdomain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 09-DEC-2008 07:55:38
Uptime 0 days 6 hr. 45 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/mkt/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkt.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “mkt” has 1 instance(s).
Instance “mkt”, status READY, has 1 handler(s) for this service…
Service “mktXDB” has 1 instance(s).
Instance “mkt”, status READY, has 1 handler(s) for this service…
Service “mkt_XPT” has 1 instance(s).
Instance “mkt”, status READY, has 1 handler(s) for this service…
The command completed successfully

[root@mkt init.d]# ./listener11g restart
Oracle 11g listener start/stop/status
Restarting Listener for 11g:Oracle 11g listener start/stop/status
Shutting down Listener for 11g:
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 09-DEC-2008 14:42:02

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mkt.localdomain)(PORT=1521)))
The command completed successfully

Oracle 11g listener start/stop/status
Starting the Listener for 11g:
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 09-DEC-2008 14:42:07

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/mkt/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkt.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mkt.localdomain)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 09-DEC-2008 14:42:07
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/mkt/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkt.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

6. chkconfig script listener11g
[root@mkt init.d]# /sbin/chkconfig –add listener11g
[root@mkt init.d]# /sbin/chkconfig –list listener11g
listener11g 0 :o ff 1 :o ff 2 :o ff 3 :o n 4 :o n 5 :o n 6 :o ff
[root@mkt init.d]# /sbin/chkconfig listener11g on