10G R2 Data Guard Kurulumu
Bu makalede , Maxavailability modda ve fast start fail over aktif edilecek DG ve observer kurulumu anlatılacaktır. Var olan bir DG ortamına Standby kurulumu yapılacaksa standby logların kontrol edilmesi gerekir. Eğer database boyutu büyük ise yedeği ayarlara geçmeden başlatmakta fayda var.
/home/oracle/.bash_profile dosyasına aşağıdaki alias lar eklenirse kurulum sırasında kolaylık olur.
|
1 2 3 4 5 6 |
alias oh='cd /u01/app/oracle/product/10g' alias admin='cd /u01/app/oracle/admin/' alias dbs='cd /u01/app/oracle/product/10g/dbs' alias sql='sqlplus "/ as sysdba"' alias network='cd /u01/app/oracle/product/10g/network/admin' alias alert='tail -200f /u01/app/oracle/admin/FSM/bdump/alert_FSM.log ' |
ADIM_1
Primary archive log modda olmalı
|
1 2 3 4 5 |
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM' SCOPE=spfile; alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile; shutdown immediate startup mount alter database archivelog |
ADIM_2
Primary Flashback aktif aktif olmalı
|
1 2 3 4 5 6 7 8 9 10 11 |
shutdown immediate; startup mount; alter database flashback on; alter database open; SELECT flashback_on, log_mode FROM v$database; SELECT flashback_on, log_mode FROM v$database; FLASHBACK_ON LOG_MODE ------------------ --------------------- YES ARCHIVELOG |
ADIM_3
Force logging aktif olmalı
|
1 2 3 4 5 |
ALTER DATABASE FORCE LOGGING; SELECT force_logging FROM v$database; FOR --- YES |
ADIM_4
Standby DB
1-Oracle ın sadece yazılım ve patch yüklenir. Primary ile aynı dizinlere ve haklara sahip ama DB oluşturulmamış.
Primary
Oracle_Unique_Name FSM_DG1
Standby
Oracle_Unique_Name FSM_DG2
ADIM_5
Primary de standby redo loglar oluşturulur. (primary de nasıl eklendi ise standby da da aynısı eklenecek.)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Drop StanBy Log ALTER DATABASE DROP STANDBY LOGFILE GROUP 5; ALTER DATABASE DROP STANDBY LOGFILE GROUP 6; ALTER DATABASE DROP STANDBY LOGFILE GROUP 7; ALTER DATABASE DROP STANDBY LOGFILE GROUP 8; ALTER DATABASE DROP STANDBY LOGFILE GROUP 9; tek Standby log ekleme ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log') SIZE 100M reuse; üyeli standby log ekleme ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log','/u02/app/oracle/oradata/FSM/stdby_log01_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log','/u02/app/oracle/oradata/FSM/stdby_log02_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log','/u02/app/oracle/oradata/FSM/stdby_log03_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log','/u02/app/oracle/oradata/FSM/stdby_log04_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log','/u02/app/oracle/oradata/FSM/stdby_log05_2.log') SIZE 100M reuse; |
ADIM_6
primari DB nin pfile ında aşağıdaki değişikler yapılmalı sonra değişiklikler spfile a alınıp spfile ile DB açılmalı
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
ALTER SYSTEM SET DB_UNIQUE_NAME='FSM_DG1' SCOPE=SPFILE; + ALTER SYSTEM SET FAL_CLIENT='FSM_DG1' SCOPE=SPFILE; ALTER SYSTEM SET FAL_SERVER='FSM_DG2' SCOPE=SPFILE; ALTER SYSTEM SET Log_archive_config='DG_CONFIG=(FSM_DG1,FSM_DG2)' SCOPE=SPFILE; Alter system set Log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FSM_DG1' Alter system set Log_archive_dest_2='Service=FSM_DG2 lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG2' Alter system set Standby_File_Management='AUTO' scope=spfile; Alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=spfile; shutdown immediate startup |
pfile oluşturup manuel yapılmak istenirse
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
*.db_name='FSM' *.db_unique_name='FSM_DG1' *.FAL_Client='FSM_DG1' ----> Primary DB unique name *.FAL_Server='FSM_DG2' ----> StandBY DB unique name *.Log_archive_config='DG_CONFIG=(FSM_DG1,FSM_DG2)' ----> (Primary DB unique name,StanBY DB unique name) *.Log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FSM_DG1' ----> (Primary DB unique name,StanBY DB unique name) *.Log_archive_dest_2='Service=FSM_DG2 lgwr async ----> (StandBY DB unique name) VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG2' ----> (StandBY DB unique name) *.Log_archive_dest_state_1='ENABLE' *.Log_archive_dest_state_2='ENABLE' *.Standby_File_Management='AUTO' *.LOG_ARCHIVE_MAX_PROCESSES=30 |
ADIM_7
standby da gerekli klasörleri oluşturuyoruz eksik klasör olursa "ORA-07446: sdnfy: bad value " for parameter" şeklinde hata veriyor.
|
1 2 3 4 5 |
mkdir -p /u01/app/oracle/admin/FSM/adump mkdir -p /u01/app/oracle/admin/FSM/bdump mkdir -p /u01/app/oracle/admin/FSM/cdump mkdir -p /u01/app/oracle/admin/FSM/udump mkdir -p /u01/app/oracle/archive/FSM |
ADIM_8
StandBY için Pfile oluşturuyoruz.
Primary’de, pfile oluşturulup standby ın $ORACLE_HOME/dbs e gönderilir.
|
1 2 |
create pfile from spfile ; scp initFSM.ora sdb:/u01/app/oracle/product/10g/dbs/ |
ADIM_9
Standby sunucuda initFSM.ora da aşağıdaki değişiklikler yapılır.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
*.db_name='FSM' *.db_unique_name='FSM_DG2' *.FAL_Client='FSM_DG2' ----> StandBY DB unique name *.FAL_Server='FSM_DG1' ----> Primary DB unique name *.Log_archive_config='DG_CONFIG=(FSM_DG1,FSM_DG2)' ----> (Primary DB unique name,StanBY DB unique name) *.Log_archive_dest_1='LOCATION=/u01/app/oracle/archive/FSM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=FSM_DG2' ----> (Primary DB unique name,StanBY DB unique name) *.Log_ar]chive_dest_2='Service=FSM_DG1 lgwr async ----> StandBY DB unique name VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG1' ----> StandBY DB unique name *.Log_archive_dest_state_1='ENABLE' *.Log_archive_dest_state_2='ENABLE' *.Standby_File_Management='AUTO' *.LOG_ARCHIVE_MAX_PROCESSES=30 |
ADIM_10
Standby DB nomount modda açılır.
|
1 2 3 |
$sqlplus / as sysdba create spfile from pfile; startup nomount |
ADIM_11
standby de password file oluşturulur (sys şifresi primary ile aynı olmalı).
|
1 |
/u01/app/oracle/product/10g/bin/orapwd file=$ORACLE_HOME/dbs/orapwFSM password=sys |
ADIM_12
primary de rman ile yedek alınır rman yedek standby a gonderilir (aynı lokasyona)
|
1 2 3 4 5 6 7 8 |
$rman target / run { BACKUP CURRENT CONTROLFILE FOR STANDBY format '/u02/backup/standbyctl' ; ALLOCATE CHANNEL C1 TYPE DISK MAXPIECESIZE 15G; BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u02/BACKUP/DBF_%D_%U_%S_%P.RMAN' TAG='RMAN_FULLYEDEK_FOR_STANDBY'; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL C1; } |
veya
|
1 2 3 4 5 6 7 8 |
run { BACKUP CURRENT CONTROLFILE FOR STANDBY format '/u02/backup/standbyctl' ; ALLOCATE CHANNEL C1 DEVICE TYPE DISK MAXPIECESIZE 15G; BACKUP DATABASE FORMAT '/u02/BACKUP/DBF_%U_%D_%S_%P'TAG='RMAN_FULLYEDEK_FOR_STANDBY';; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL C1; } |
yedek standby da /u02/backup dizinine gönderilir. Kopyalama uzun sürecekse.NFS servisi çalıştırılıp (service nfs start) mount edilerek kopyalama işinden kurtuluruz.
|
1 |
mount -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp PrimaryDB_IP:/u02/backup /u02/backup |
ADIM_13
Rman yedek standby da restore edilir. Bunun için Primary DB de
|
1 2 |
$rman target / auxiliary sys/sys@FSM_DG2 DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK; |
komutu verilerek standby db de rman backup restore edilir
tail -f ile alertlog dökümü izlenir.
ADIM_14
Standby da redo loglar oluşturulur (size larına dikkat)
|
1 2 3 4 5 6 7 8 |
select bytes from v$log; select bytes/1048576 as MB from v$log; select group#, member from v$logfile ; col member format a70 set lines 132 set trims on select group#,type,member from v$logfile order by type; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Drop StanBy Log ALTER DATABASE DROP STANDBY LOGFILE GROUP 5; ALTER DATABASE DROP STANDBY LOGFILE GROUP 6; ALTER DATABASE DROP STANDBY LOGFILE GROUP 7; ALTER DATABASE DROP STANDBY LOGFILE GROUP 8; ALTER DATABASE DROP STANDBY LOGFILE GROUP 9; tek Standby log ekleme ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log') SIZE 100M reuse; üyeli standby log ekleme ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log01_1.log','/u02/app/oracle/oradata/FSM/stdby_log01_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log02_1.log','/u02/app/oracle/oradata/FSM/stdby_log02_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log03_1.log','/u02/app/oracle/oradata/FSM/stdby_log03_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log04_1.log','/u02/app/oracle/oradata/FSM/stdby_log04_2.log') SIZE 100M reuse; ALTER DATABASE ADD STANDBY LOGFILE ('/u02/app/oracle/oradata/FSM/stdby_log05_1.log','/u02/app/oracle/oradata/FSM/stdby_log05_2.log') SIZE 100M reuse; |
ADIM_15
Primary de oluşan redoları stanby a uygulanması için , standby da
|
1 |
alter database recover managed standby database disconnect from session; |
Kontrol Edelim
primary de
|
1 |
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#; |
standby da
|
1 |
select thread#,max(sequence#) from v$archived_log where applied ='YES' group by thread#; |
REDO APPLY
|
1 |
alter database recover managed standby database disconnect; |
REDO APPLY USING REAL-TIME APPLY
|
1 |
alter database recover managed standby database using current logfile disconnect; |
rman duplicate yerine manuel de oluşturulabilir. primary mount moda alınıp stanby için kontrol file oluşturulup orjinal lokasyonlarına aynı isimlerde kopyalanır. datafile ve log dosyaları da aynı şekilde.
DG Kontrol
|
1 |
SELECT SWITCHOVER_STATUS,PROTECTION_MODE,DATABASE_ROLE from v$database; |
PRIMARY
|
1 |
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; |
STANDBY
|
1 |
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
column "First Time" format A40 column applied format A10 column "Next Time" format A40 set linesize 120 set pagesize 1000 select thread#, sequence#, applied, to_char(first_time,'DD-MON-YY:HH24:MI:SS') "First Time", to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Next Time" from v$archived_log UNION select NULL,NULL,' ',NULL,null FROM DUAL UNION select null,null, db_unique_name, database_role, open_mode from v$database / |
PRIMARY de standby archive hedef kontrolü
S
|
1 2 3 |
select Status, Error from v$Archive_dest where dest_id=2; |
Redo switch Sıklığı
|
1 2 3 4 5 6 7 8 9 10 |
COL DAY FORMAT a15; COL HOUR FORMAT a4; COL TOTAL FORMAT 999; SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, TO_CHAR(FIRST_TIME,'HH24') HOUR, COUNT(*) TOTAL FROM V$LOG_HISTORY GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24') ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24') ASC; |
İşlenmeyen archivelog varsa
|
1 |
select * from v$archive_gap; |
|
1 2 3 4 5 |
SELECT name FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN Low_Sequence and High_Sequence; |
Eksik archiveloglar primary den stanby a kopyalanıp
|
1 2 3 |
ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/archive/FSM/arc_38792_5711453.log'; ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/archive/FSM/arc_38793_5711453.log'; ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/archive/FSM/arc_38794_5711453.log'; |
MAXAVAILABILITY için, flashback aktif olması gerekir
|
1 2 3 |
SELECT flashback_on, log_mode FROM v$database; mount modda alter database flashback on; |
Primaryde de stanbyda da
|
1 |
Alter system set dg_broker_start=True scope=both; |
Observer ın çalışığı makinenin tespiti
|
1 |
select FS_FAILOVER_OBSERVER_HOST from v$database; |
Observer ın çalışığı makinenin tespiti
|
1 2 3 4 5 6 7 8 |
show configuration verbose SHOW DATABASE 'FSM_DG2'; SHOW DATABASE 'FSM_DG1'; SHOW DATABASE VERBOSE 'FSM_DG1'; SHOW DATABASE VERBOSE 'FSM_DG2'; SHOW INSTANCE FSM_DG1; SHOW INSTANCE VERBOSE FSM_DG1; SHOW INSTANCE FSM_DG1 'TopWaitEvents'; |
Primary
db_name---> FSM
db_unique_name-> FSM_DG1
StandBy
db_name---> FSM
db_unique_name-> FSM_DG2
log
tail -300f /u01/app/oracle/admin/FSM/bdump/drcFSM.log |more
primariye baglanıyoruz
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$dgmgrl connect sys/sys@FSM_DG1; show configuration verbose CREATE CONFIGURATION FSM_BROKER as PRIMARY DATABASE IS "FSM_DG1" CONNECT IDENTIFIER IS "FSM_DG1"; ADD DATABASE "FSM_DG2" AS CONNECT IDENTIFIER IS "FSM_DG2" MAINTAINED AS PHYSICAL ; show configuration EDIT DATABASE "FSM_DG1" SET PROPERTY 'LogXptMode'='SYNC'; EDIT DATABASE "FSM_DG2" SET PROPERTY 'LogXptMode'='SYNC'; set property FastStartFailoverThreshold = 15; EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY; Succeeded. EDIT DATABASE 'FSM_DG1' SET PROPERTY FastStartFailoverTarget='FSM_DG2'; Property "faststartfailovertarget" updated EDIT DATABASE 'FSM_DG2' SET PROPERTY FastStartFailoverTarget='FSM_DG1'; |
Buraya dikkat konfigurasyon enable edilirken db leri kapatıp açıyor. Burada aslında sistemin uyarı vermesi lazım ama vermiyor enable eder etmez DB yi kapatıyor tekrar açıyor.
|
1 2 3 4 5 |
********************>DB yi kapatıyooooorrrrrrrr!!!!!!!!!!!! ********************>DB yi kapatıyooooorrrrrrrr!!!!!!!!!!!! enable configuration; ********************> DB yi kapatıyooooorrrrrrrr!!!!!!!!!!!! ********************> DB yi kapatıyooooorrrrrrrr!!!!!!!!!!!! |
|
1 |
ENABLE FAST_START FAILOVER; |
her iki db ye de
|
1 2 |
sqlplus sys/sys@FSM_DG1 sqlplus sys/sys@FSM_DG2 |
şeklinde bağlanılabilmesi lazım
aksi halde failover olduğunda db yi açamıyor.
Çeşitli
standby da
|
1 2 3 4 |
Alter System set Log_archive_dest_2='Service=FSM LGWR AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=FSM_DG1'; |
primari
mount moda alınıp
|
1 |
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; |
Disable etmek için
|
1 2 |
disable fast_start failover; disable fast_start failover force |
|
1 |
switchover to 'FSM_DG1' ; |
(o andaki standby yazılır.)
(rolleri değiştirir pri->std , std da prima olur)
REINSTATE DATABASE 'FSM_DG2′;
standbyarchivelocation
|
1 2 3 |
show database 'FSM_DG1' standbyarchivelocation ; show database 'FSM_DG2' standbyarchivelocation ; edit database FSM_DG2 set property StandbyArchiveLocation = '/u01/app/oracle/archive/FSM'; |
Data Guard ortamındaki Database lerin kapatılıp açılması
Kapanış adımları
|
1 2 3 |
$dgmgrl connect sys/sys@FSM_DG1 stop observer |
Primari
|
1 2 3 |
alter system set log_archive_dest_state_2=defer; alter system switch logfile ; shutdown immediate |
Standby
|
1 2 |
alter database recover managed standby database cancel; shutdown immediate; |
Açılış Adımları
Stanby
|
1 2 3 |
startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session; |
primary
|
1 2 3 |
startup alter system set log_archive_dest_state_2=enable; alter system switch logfile; |
