Saya sharing saja implementasi DataGuard di perusahaan saya. Policy perusahaan
adalah '5 minutes Recovery Point Objective', artinya adalah DB di Primary dan
di Standby harus dalam keadaan sinkron secara data, dan apabila ada Crash yang
terjadi sehingga mengharuskan Switchover / Failover ke Standby DB, data loss
yang ditoleransi oleh perusahaan hanya dibolehkan data hilang 5 menit ke
belakang, DB menggunakan Oracle 10G.
Implementasi DataGuardnya seperti apa?
secara garis besar, implementasinya seperti berikut ini..
I. CONFIGURE INIT.ORA DI DB PRIMARY & DB STANDBY
Di init.ora DB Primary & DB Standby dipasang beberapa parameter-parameter
supaya DB Primary mengenali DB Standby & melakukan sinkronisasi data &
sebaliknya
init.ora primary
log_archive_dest_1 = 'LOCATION=/oradata/archive/ MANDATORY' --> Lokasi
archivelog di Database Primary
log_archive_dest_2 = 'SERVICE=ORADB_STANDBY.ORACLE.COM ARCH ASYNC REOPEN=10
MAX_FAILURE=10' --> nama tnsnames Database Standby yang diset di dalam
tnsnames.ora primary, nantinya akan ditambahkan di tnsnames.ora primary
log_archive_dest_state_1 = ENABLE
log_archive_dest_state_2 = ENABLE
control_file_record_keep_time = 7
fal_server = 'ORADB_STANDBY.ORACLE.COM' --> alamat tnsnames DB Standby
fal_client = 'ORADB_PRIMARY.ORACLE.COM' --> alamat tnsnames DB Primary
init.ora standby
standby_file_management = AUTO
log_archive_dest_1 = 'LOCATION=/oradata/archive MANDATORY' --> lokasi dimana
archivelog dari db primary akan diterima
log_archive_dest_2 = 'SERVICE=ORADB_PRIMARY.ORACLE.COM ARCH ASYNC REOPEN=10
MAX_FAILURE=10' --> nama tnsnames Database Primary
log_archive_dest_state_1 = ENABLE
log_archive_dest_state_2 = DEFER
II. CONFIGURE TNSNAMES.ORA DI DB PRIMARY & DB STANDBY
Di tnsnames.ora di Primary & Standby ditambahkan tnsnames masing masing untuk
connect DB Primary ke Standby dan sebaliknya
ORADB_PRIMARY.ORACLE.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = ORADB)
)
)
ORADB_STANDBY.ORACLE.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = ORADB)
)
)
III. STARTUP PRIMARY & STANDBY UNTUK MULAI MENSINKRONKAN DIRI
Primary Database
SQL> startup;
Standby Database
SQL> startup mount
SQL> alter database mount standby database
SQL> alter database recover managed standby database disconnect from session;
Perhatikan alert log di Primary & Standby database apakah archive log dari
primary terkirim & terapply di Standby database
regards,
adipt
----- Original Message -----
From: wisnu dwi hidayat<mailto:[email protected]>
To: [email protected]<mailto:[email protected]>
Sent: Tuesday, November 24, 2009 11:02 AM
Subject: External Email: --> Re: Bls: External Email: --> Re: External Email:
--> [indo-oracle] Tanya Mirroring 2 server database
Dear All Masters,
Terima kasih atas masukkannya..
Jadi saya harus membeli license untuk menactivekan feature data guard tersebut
sebelum di gunakan..??
Sekedar informasi saya menggunakan Oracle 11g Enterprise Edition Release 1
bukan standard edition tapi memang saya melakukan proses sending archivelog
dari production ke standby secara manual dengan script hingga recover database
.. Saya ini belajar otodidak jadi kurang tahu feature dataguard yg ada di
Oracle 11g... nah jika ada cara otomatis mohon berikan penjelasan lebih
lanjut.. karna standby saya recover database setiap 12 jam sekali dengan
cronjob.. jadi tidak realtime...
Mohon masukan saran atau apa saja yg menjadikan informasi lebih lanjut buat
saya...
Regards,
Wisnu ~Newbie~
________________________________
From: Sulistyo
<[email protected]<mailto:bookonlineworm%40yahoo.com.sg>>
To: [email protected]<mailto:indo-oracle%40yahoogroups.com>
Sent: Mon, November 23, 2009 7:22:20 PM
Subject: Bls: External Email: --> Re: External Email: --> [indo-oracle] Tanya
Mirroring 2 server database
Active data guard berjalan pada physical standby database dimana physical
standby database ini dalam kondisi open read only tetapi proses applying
archivelog tetap berjalan.
Dalam dokumentansi oracle mengenai physical dataguard ini,proses applying nya
adalah block per block data. itu yg kenapa di oracle 9i dan 10g database harus
dalam kondisi mount. di Oracle 11g feature tersebut di enhance, meski db bisa
di open tapi kondisinya bukan read write dengan penekanan bahwa DG adalah
konsep DRC sehingga data harus tetap konsisten dengan primary DB nya.
CMIIW, real time apply adalah proses applying pada data guard agar data guard
segera melakukan proses apply tanpa jeda.
Jika dilihat dari permasalahan mas Wisnu, sepertinya beliau menggunakan oracle
11g standard edition dimana proses sending archivelog untuk dataguard harus
secara manual atau menggunakan database streaming feature dimana proses apply
nya menggunakan log miner.
yang perlu diperhatikan active dataguard sudah terinclude di oracle 11g tapi
license untuk mengactive-kan feature ini terpisah. jika di versi sebelumnya
license oraclenya hanya untuk DRC database, maka utk active dataguard 11g
licensenya plus license active dataguard.
just sharing my opinion, kalo ada salah mohon maaf masi newbie.
____________ _________ _________ __
Dari: Mohammad Arief Pradipto <arief.pradipto@ fif.astra. co.id>
Kepada: indo-oracle@ yahoogroups. com
Terkirim: Sen, 23 November, 2009 15:35:49
Judul: Re: External Email: --> Re: External Email: --> [indo-oracle] Tanya
Mirroring 2 server database
Ngobrolin tentang Standby dan Production,
Mungkin informasi juga untuk Mas Wisnu apakah sudah dapat informasi ini atau
belum, bahwa Oracle 11G menyediakan fitur yang bernama 'Active DataGuard'.
Fitur Active DataGuard ini secara harfiah artinya adalah, bahwa sementara
physical standby database dibuka Read Only mode, database tersebut terus
menerus mengapply transaksi-transaksi yang dilakukan di primary.
Sebenarnya setau saya Fitur tersebut sudah 'tersedia' di Oracle 10G, dengan
nama Oracle DataGuard Real Time Apply, tapi boleh ada yang konfirmasi ngg, saya
ada baca artikel bahwa Active DataGuard harus bayar biaya tambahan. Setau saya
dengan Oracle 10G saya tidak bayar tambahan untuk teknologi DataGuard, mungkin
ada yang bisa kasih informasi?
regards,
adipt
============ =
http://www.dba- oracle.com/ t_active_ data_guard. htm
Oracle active data guard tips
Oracle Tips by Burleson Consulting
July 18, 2008
Question: The Oracle marketing people are at-it again, and I noticed that
within Oracle 11g "active" data guard is an extra cost option! In Oracle 10g,
there was no distinction between an "active" data guard and an inactive one, so
I wonder if Data Guard costs extra in Oracle 11g?
Answer: The only way to know for sure if Data Guard is still free in 11g EE is
to contract your CSR (customer support representative) . Oracle is always
re-naming products, and it's not uncommon for a single feature to have many
names (e.g. Oracle VPD is the same as RLS). As to Data Guard, Oracle is
slicing-off the functionality as Data Guard matures.
You are correct, the Oracle web site says that Data Guard (SQL Apply and Redo
Apply) comes as part of 10g Enterprise Edition (EE).
Active Data Guard - Now an extra cost option?
In 11g, "active data guard" is indeed an "option". The description suggests
that "active" data guard is Data Guard with physical standby, opened for
read-only transactions:
Oracle Active Data Guard—an option for Oracle Database 11g Enterprise
Edition—enhances Quality of Service by offloading resource-intensive activities
from a production database to one or more synchronized standby databases.
Oracle Active Data Guard enables read-only access to a physical standby
database for queries, sorting, reporting, web-based access, etc., while
continuously applying changes received from the production database.
Oracle Active Data Guard also enables the use of fast incremental backups when
offloading backups to a standby database, and can provide additional benefits
of high availability and disaster protection against planned or unplanned
outages at the production site.
The docs also say that active data guard implies that it is essentially redo
apply services used with physical standby:
"If a license for the Oracle Active Data Guard option has been purchased, Redo
Apply Services can also run on a physical standby database instance that is
open."
Let's look at the 11g new features for Data Guard. Oracle 11g Data Guard
introduced several new features. The Oracle 11g New Features
book<http://www.dba- oracle.com/ t_11g_new_ data_guard. htm> notes some Data
Guard Pricing changes:
"As site failures do not happen on a regular occurrence these disaster recovery
(DR) servers can be sitting idle for long periods of time without any use or
value being obtained. Prior to 11g businesses were unable to benefit as greatly
from their DR investment. Oracle’s goal for 11g was to make DR cost effective,
better utilize standby resources and allow for easy testing of DR.
Data Guard is included with the Enterprise Edition of Oracle, but it should be
noted that a named user or processor license may need to be purchased for the
database. It’s always recommended to check with an Oracle sales."
The book also confirms that "active Data Guard" is essentially Data Guard
Physical Standby with read-only access:
"Oracle Active Data Guard allows DBAs and users to run real-time queries on a
physical standby system for reporting and other purposes while still seeing
transactional consistent results. As opposed to previous versions where
applying of logs had to stop while reporting was taking place, it now runs
simultaneously.
Oracle versions 9 and 10 couldn’t have the physical standby in readable
state;11g has this and is fully synced. This ability makes an organizations DR
investment much more valuable. This capability is known as Real-Time Query."
----- Original Message -----
From: wisnu dwi hidayat<mailto:wisnu...@yahoo. com>
To: indo-oracle@ yahoogroups. com<mailto:indo-oracle@ yahoogroups. com>
Sent: Monday, November 23, 2009 12:33 PM
Subject: External Email: --> Re: External Email: --> [indo-oracle] Tanya
Mirroring 2 server database
Dear Henri,
Terima kasih telah merespon email saya.
Saya sudah pakai standby pak dan sdah open read only...
Dan standby saya memang saya gunakan buat reporting.. tapi synchronize archive
file nya manual pakai cron job setiap 12 jam..(OS saya linux RHEL 5)
saya maping directory archive yg di production (primary) ke standby dan saya
copy archive production ke stanby dan saya recover pakai script yg saya
jalankan pakai cronjob setiap 12 jam untuk autorecover di stanby;
ORACLE_HOME= /OraHome/ oracle11g/ product/11. 1.0/db_1; export ORACLE_HOME
ORACLE_LIB_PATH= $ORACLE_HOME/ lib;export ORACLE_LIB_PATH
PATH=$PATH:$ HOME/bin: $ORACLE_HOME/ bin
ORACLE_SID=PROD; export ORACLE_SID
export PATH
sqlplus /nolog <<eof
conn / as sysdba;
shutdown immediate;
startup nomount;
alter database mount standby database;
recover standby database;
auto
alter database open read only;
exit;
eof
Yg saya tanyakan gimana kalo server primary saya down trus saya switch ke
standby yg DRP tanpa delay lama dan data tersebut realtime ...
Kalo support oracle saya blum habis mungkin saya liat metalink..
Saya sudah googling dan ada penjelasan tentang dataguard dan RAC lantas mesti
mana yg saya pakai..?? atau kalo punya link ke situs tutorial RAC atau
dataguard boleh share ke saya..
regards,
Wisnu
____________ _________ _________ __
From: Henri Alia Putra <henr...@fif. astra.co. id<mailto:henriap% 40fif.astra.
co..id>>
To: "indo-oracle@ yahoogroups. com<mailto:indo- oracle%40yahoogr oups.com> "
<indo-oracle@ yahoogroups. com<mailto:indo- oracle%40yahoogr oups.com> >
Sent: Mon, November 23, 2009 10:48:00 AM
Subject: RE: External Email: --> [indo-oracle] Tanya Mirroring 2 server database
Dear Wisnu.
Sebenarnya dengan memakai oracle 11g ,anda sudah mendapatkan future yang sangat
oke untuk dataguard
(di 10g sebenarnya juga sudah ada)
dengan db 10g atau 11g tinggal anda buat standby redolog di kedua db, maka db
anda tidak akan ada delay nya, guarantee oracle no data loss.
kalau di 10g, standby db ,cuma bisa di baca dengan open read only,
tapi kalau anda sudah memakai 11g , maka db standby bisa langsung dibaca,karena
default sudah oen read only, jadi db tsb bisa anda manfaatkan sbg reporting.
untuk cara pembuatannya ,tinggal googling ato menyelam di metalink.
good luck
Henri
From: indo-oracle@ yahoogroups. com<mailto:indo- oracle%40yahoogr oups.com>
[indo-oracle@ yahoogroups. com<mailto:indo- oracle%40yahoogr oups.com> ] On
Behalf Of wisnu dwi hidayat [wisnu...@yahoo. com<mailto:wisnu_ dh%40yahoo. com>]
Sent: Monday, November 23, 2009 10:22 AM
To: indo-oracle@ yahoogroups. com<mailto:indo- oracle%40yahoogr oups.com>
Subject: External Email: --> [indo-oracle] Tanya Mirroring 2 server database
Dear Masters & Members,
Saya mo tanya nih.. gimana buat database server bisa syncrhonize real time
antara 2 server.. seperti mirroring lah.. jadi seperti buat DRP gitu... server
yg satunya akan di pakai jika terjadi gangguan pada yg primary tapi data server
tersebut realtime dengan primary atau setidaknya ada delay brapa second lah...
untuk koneksi kami miliki bandwith 2 mega 1:1..
Saat ini saya menggunakan Oracle 11g..
Saat ini selain production (primary) database saya hanya memiliki standby
database yg synchronizenya tidak realtime( ada delay 12 jam)..
Mohon saran dan bantuan para master sekalian yg telah berpengalaman. ...
Regards,
Wisnu
[Non-text portions of this message have been removed]
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you have received this email by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or
dissemination of this e-mail in whole or in part is strictly prohibited. PT
Federal International Finance, which has its seat at North of Jakarta,
Indonesia, including its affiliated companies, shall not be liable for the
improper or incomplete transmission of the information contained in this e-mail
nor for any delay in its receipt or damage to your system. PT Federal
International Finance (or its affiliated companies) does not guarantee that the
integrity of this communication has been maintained nor that this communication
is free of viruses, interceptions or interference.
------------ --------- --------- ------
--
-----------I. N.D.O - O.R.A.C.L.E- --------- -----
Keluar: indo-oracle- unsubscribe@ yahoogroups. com<mailto:indo-
oracle-unsubscri be%40yahoogroups .com>
Website: http://indooracle. wordpress. com
http://www.facebook .com/group. php?gid=51973053 515
------------ --------- --------- --------- --------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo- thin.blogspot. comYahoo! Groups Links
[Non-text portions of this message have been removed]
____________ _________ _________ __
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you have received this email by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or
dissemination of this e-mail in whole or in part is strictly prohibited. PT
Federal International Finance, which has its seat at North of Jakarta,
Indonesia, including its affiliated companies, shall not be liable for the
improper or incomplete transmission of the information contained in this e-mail
nor for any delay in its receipt or damage to your system. PT Federal
International Finance (or its affiliated companies) does not guarantee that the
integrity of this communication has been maintained nor that this communication
is free of viruses, interceptions or interference.
[Non-text portions of this message have been removed]
------------ --------- --------- ------
--
-----------I. N.D.O - O.R.A.C.L.E- --------- -----
Keluar: indo-oracle- unsubscribe@ yahoogroups. com
Website: http://indooracle. wordpress. com
http://www.facebook .com/group. php?gid=51973053 515
------------ --------- --------- --------- --------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo- thin.blogspot. comYahoo! Groups Links
Berselancar lebih cepat. Internet Explorer 8 yang dioptimalkan untuk Yahoo!
otomatis membuka 2 halaman favorit Anda setiap kali Anda membuka browser.
Dapatkan IE8 di sini!
http://downloads. yahoo.com/ id/internetexplo rer
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
________________________________
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you have received this email by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or
dissemination of this e-mail in whole or in part is strictly prohibited. PT
Federal International Finance, which has its seat at North of Jakarta,
Indonesia, including its affiliated companies, shall not be liable for the
improper or incomplete transmission of the information contained in this e-mail
nor for any delay in its receipt or damage to your system. PT Federal
International Finance (or its affiliated companies) does not guarantee that the
integrity of this communication has been maintained nor that this communication
is free of viruses, interceptions or interference.
[Non-text portions of this message have been removed]
