Bu kurulum hem sun solaris 10 hem de OEL (Oracle Linux Server release 6.4) üzerine oracle 11.2.0.2 binary leriyle gerçekleştirildi. Makalede solarisi baz almaya çalıştım ama linux tarafından da eklemeler oldu. Kuruluma geçmeden önce tüm makaleyi okumanızı tavsiye ederim. Eğer single instance tan single instance a data guard ve RAC kurulumları yaptı iseniz adımlar daha kolay gelecektir. Eksik ya da yanlış olan yerleri bildirirseniz sevinirim.
|
PRIMARY |
STANDBY |
|
|
Hostname/s |
dbk01, dbk02 | stddbk |
|
Database Name |
mx |
mx |
|
Database Unique Name |
mx |
stdmx |
|
Net Service Name |
mx.sysdba.org |
stdmx.sysdba.org |
|
Clusterware |
11g R2 Grid Infrastructure (11.2.0.2) |
11g R2 Grid Infrastructure (11.2.0.2) |
|
Cluster Nodes |
node1, node2 (2-node RAC) |
Single instance |
|
SCAN |
mx-scan |
– |
|
SCAN listener Host/port |
SCAN VIPs (port 1521) |
|
|
DB Instances |
mx1, mx2 | stdmx |
|
DB STORAGE |
ASM | ASM |
|
File Management |
||
|
ASM diskgroup for DB files |
DATA | DATA |
|
ASM diskgroup for recovery files |
FRA | FRA |
|
GRID_HOME |
/u01/11.2.0/grid | /u01/11.2.0/grid |
|
ORACLE_HOME |
/u01/app/oracle/product/11.2.0/db_1 | /u01/app/oracle/product/11.2.0/db_1 |
|
OS |
SUN 10u10 | SUN 10u10 |
|
RDBMS Version |
11.2.0.2 | 11.2.0.2 |
|
Grid Version |
11.2.0.2 | 11.2.0.2 |
– Standby Server (Grid Inf., stand alone server, kurulur. Asmca ile DATA ve FRA disk gruplari oluşturulur.)
– RDBMS software only olarak kurulur. (database oluşturulmaz)
– Primary de redologlarin DATA ve FRA’ da olmak üzere her grubun 2 üyesi oldugu kontol edilir yok ise eklenir.
Kurulumda kullanılan .bash_profile/.profile, listener.ora, tnsnames.ora ve başlangıç parametre (pfile) dosyalarını linkinden indirebilirsiniz.
oracle ve grid kullanıcılarının hangi gruplara dahil edildiği önemli maalesef guide larda yanlışlık ya da bug dan kaynaklı sorunlar olabiliyor. Bu kurulumda grup üyelikleri aşağıdaki gibiydi.
|
1 2 3 4 |
dba::101:oracle,grid asmadmin::102:grid asmdba::103:grid,oracle asmoper::104:grid |
|
1 2 3 |
set linesize 1200 col member format a70 select * from v$logfile; |
|
1 2 |
column member format a50 select f.member,l.bytes/1024/1024 as "Size in MB" from v$log l,v$logfile f where l.group#=f.group#; |
Yararlanilan kaynaklar :
http://tahiti.oracle.com
http://asanga-pradeep.blogspot.com/2010/11/rac-to-single-instance-physical-standby.html
http://easyoradba.com/2012/11/20/rac-to-single-instance-dataguard-oracle-11gr2-physical-standby/
http://www.cozumpark.com/blogs/oracle/archive/2011/02/26/oracle-11g-r2-11-2-0-2-rac-mimarisinde-data-guard-kurulumu-ve-y-netimi-d-nyada-lk-ve-tek.aspx
http://dpmappsdba.blogspot.com/p/rac-to-single-instance-physical-standby.html
http://unixoracledba.wordpress.com/2012/03/29/how-to-create-a-single-instance-physical-standby-dataguard-for-a-rac-database/
Primary Database’ deki Adımlar
1- Primary DB archivelog moda alınır.
|
1 2 3 4 5 6 |
SQL>archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 407 Current log sequence 408 |
|
1 2 3 4 |
alter system set db_recovery_file_dest_size=40g scope=both sid='*'; alter system set db_recovery_file_dest='+fra' scope=both sid='*'; alter system set log_archive_dest_1='location=+fra' scope=both sid='*'; alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile; |
Tüm instancelar srvctl ile kapatılır
|
1 |
$ srvctl stop database -d mx |
|
1 2 |
$ sqlplus "/ as sysdba" SQL>startup mount |
|
1 2 |
SQL> alter database archivelog; SQL> shutdown immedite |
DB’ leri açalım
|
1 |
$ srvctl start database -d mx |
Kontrol edelim
|
1 |
SQL>archive log list |
2. Enable Force Logging. (fast start fail over kullanılacaksa) ve servis tanimlanir.
|
1 |
SQL>alter database force logging; |
|
1 |
alter system set service_names='mx,mx.sysdba.org' scope=both sid='*'; |
3. Standby Redo log ları oluşturalım.
StandBY redolog ları aşağıdaki gibi alies ile yapmayın. ASM kullanılan yerlerde bu şekilde (+FRA/mx/onlinelog/STDredo030b.log) ne online redo log ne de standby redo logları alias ile oluşturmayın. Aslında izin de vermemesi lazım madem tavsiye etmiyor.
Ek olarak üye de oluşturmamakta fayda var performans için.
Her nodda 2 adet redo log var, her nod icin, nod sayisinin bir fazlasi ini olusturuyoruz (yedekli olarak).
|
1 2 3 4 |
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 30 ('+DATA/mx/onlinelog/STDredo030a.log','+FRA/mx/onlinelog/STDredo030b.log') SIZE 100m, GROUP 40 ('+DATA/mx/onlinelog/STDredo040a.log','+FRA/mx/onlinelog/STDredo040b.log') SIZE 100m, GROUP 50 ('+DATA/mx/onlinelog/STDredo050a.log','+FRA/mx/onlinelog/STDredo050b.log') SIZE 100m; |
|
1 2 3 4 |
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 60 ('+DATA/mx/onlinelog/STDredo060a.log','+FRA/mx/onlinelog/STDredo060b.log') SIZE 100m, GROUP 70 ('+DATA/mx/onlinelog/STDredo070a.log','+FRA/mx/onlinelog/STDredo070b.log') SIZE 100m, GROUP 80 ('+DATA/mx/onlinelog/STDredo080a.log','+FRA/mx/onlinelog/STDredo080b.log') SIZE 100m |
Bu şekilde oluşturulması gerekir.
|
1 2 3 4 |
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 100M, GROUP 6 SIZE 100M, GROUP 7 SIZE 100M; |
|
1 2 3 4 |
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 SIZE 100M, GROUP 9 SIZE 100M, GROUP 10 SIZE 100M; |
Not: DG kurulduktan sonra olusturulsa daha iyi olacak sanirim ikinci kurulumda denenecek,. (hem Primary da hem de standby da olusturulacak)
|
1 2 3 4 |
col member format a90 set lines 132 set trims on select group#,type,member from v$logfile; |
|
1 |
select bytes from v$log; |
|
1 |
select group#, member from v$logfile ; |
4. Password (orapw$instance_ismi) dosyalarını oluşturalım.(Password bütün makinelerde aynı olmalı).
Bütün nodlarda
|
1 2 3 |
orapwd file=$ORACLE_HOME/dbs/orapwstdmx password=0racle ignorecase=y force=y orapwd file=$ORACLE_HOME/dbs/orapwmx1 password=0racle ignorecase=y force=y orapwd file=$ORACLE_HOME/dbs/orapwmx2 password=0racle ignorecase=y force=y |
–ignore case is important parameter here since from 11gR2 onwards passwords are case-sensitive
PING[ARC2]: Heartbeat failed to connect to standby ‘mx’. Error is 16191.
Error 1017 received logging on to the standby
These are common errors associated with password files. If you encounter the above errors, recreate the password files.
5. listener.ora dosyasına SID bilgileri girilir.(Bütün makinelerde standby lar dahil)
Primary (RAC-GRID HOME 2 node da da ayni)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent #################################################### stdmx = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stddbk.sysdba.org)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = mx.sysdba.org) (ORACLE_HOME = /u01/11.2.0/grid) (SID_NAME = mx) ) (SID_DESC = (GLOBAL_DBNAME = stdmx.sysdba.org) (ORACLE_HOME= /u01/11.2.0/grid) (SID_NAME = stdmx) ) (SID_DESC = (GLOBAL_DBNAME = mx_DGMGRL) (ORACLE_HOME= /grid/11.2.0/grid) (SID_NAME = mx) ) (SID_DESC = (GLOBAL_DBNAME = stdmx_DGMGRL) (ORACLE_HOME= /u01/11.2.0/grid) (SID_NAME = stdmx) ) ) |
StandBY ve Primary de “lsnrctl status” de instance ların kayıtlı olduğu görülmeli. Gerekirse local_listener parametresi set edilip (alter local_listener ” / register)
(listener.ora ile oynamadan da çalışmıştı ?)
6. tnsnames.ora
Butun nodlarda ve stanby da
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mx = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = murex-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mx.sysdba.org) ) ) stdmx = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stddbk.sysdba.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdmx.sysdba.org) ) ) |
|
1 2 3 4 5 6 7 8 |
$tnsping stdmx TNS Ping Utility for Solaris: Version 11.2.0.2.0 - Production on 25-APR-2013 18:33:54 Copyright (c) 1997, 2010, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stddbk.sysdba.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdmx.sysdba.org))) OK (160 msec) |
7. init.ora Parametrelerini DataGuard için ayarlıyoruz (primary için)
Öncesinde yedek pfile oluşturulur. (bir kopyasi da standby a gonderilecek)
|
1 |
SQL> create pfile='/opt/oracle/stage/initprimary_bck.ora from spfile; |
|
1 2 3 4 5 6 7 8 9 10 11 |
alter system set log_archive_config='dg_config=(mx,stdmx)' scope=both sid='*'; alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=mx' scope=both sid='*'; alter system set log_archive_dest_2='service=stdmx LGWR SYNC AFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stdmx' scope=both sid='*'; alter system set log_archive_dest_state_1='enable' scope=both sid='*'; alter system set log_archive_dest_state_2='enable' scope=both sid='*'; alter system set fal_server='stdmx' scope=both sid='*'; alter system set fal_client='mx' scope=both sid='*'; alter system set log_archive_max_processes=10 scope=both sid='*'; alter system set standby_file_management='AUTO' scope=both sid='*'; alter system set db_file_name_convert='+DATA/STDMX/','+DATA/MX/' scope=spfile sid='*'; alter system set log_file_name_convert='+FRA/STDMX/','+FRA/MX/','+DATA/STDMX/','+DATA/MX/' scope=spfile sid='*'; |
Kapatıp açıp, güncel değerler kontrol edilir.
Full halini aynı dizinde primaryinit.ora_for_std.ora adinda
|
1 |
create pfile='/opt/oracle/stage/initprimary_for_std.ora from spfile; |
Bu pfile standby a gönderilip , düzenlemeler yapılacak
|
1 2 3 |
$vi /u01/app/oracle/product/11.2.0/db_1/dbs/initstdmx.ora --icerigi asagidaki gibi olmali SPFILE='+DATA/stdmx/spfilestdmx.ora' |
spfile’ ın nerede olduğunu adresliyor. Aksi halde spfile ı bulamadığı için DB’yi açamaz
8. RMAN backup için geçici “stage” dizini oluşturulur. Primary/standby (oracle kullanicisi ile)
|
1 2 3 |
$mkdir /opt/oracle/stage $chmod -R g+w /opt/oracle $mkdir -p /opt/oracle/stage |
9. Primary Database de Standby için rman yedek alıyoruz.
|
1 2 3 4 5 6 |
$rman target / backup device type disk format '/opt/oracle/stage/mx_%u' database plus archivelog; --Yer sıkıntısı ya da lokasyonlar arası erişim hızı düşükse sıkıştırılmış yedek alınabilir. backup as compressed backupset database format '/opt/oracle/stage/mx_dbf_%u_%t_%s.rman' plus archivelog format '/opt/oracle/stage/mx_arc_%d_%t_%s.rman' ; backup device type disk format '/opt/oracle/stage/mx_%u' current controlfile for standby; |
|
1 |
$scp /opt/oracle/stage/mx_* stddbk:/opt/oracle/stage/ |
Not : lokasyonlar arası erişim hızı düşük ise ve DB boyutu büyük ise rman backup ın alınıp karşı tarafa başka bir şekilde göndermek gerekebilir.
*****************************************************************************************************
10. Klasörler oluşturulur.
|
1 2 3 4 5 6 |
mkdir -p /oracle/app/oracle/admin/stdmx/adump asmcmd> asmcmd> mkdir +DATA/STDMX/ asmcmd> mkdir +DATA/MX/ asmcmd> mkdir +FRA/STDMX/ asmcmd> mkdir +FRA/MX/ |
11. init.ora Parametrelerini DataGuard için ayarlıyoruz
(standby için primaryinit.ora_for_std.ora dosyasında gerekli değişiklikler yapılır)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
*.DB_UNIQUE_NAME='stdmx' *.log_archive_config='dg_config=(mx,stdmx)' *.log_archive_dest_1='LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdmx' *.log_archive_dest_2='service=mx LGWR AFFIRM SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=mx' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' *.db_file_name_convert='+DATA/MX/','+DATA/STDMX/' *.log_file_name_convert='+DATA/MX/','+DATA/STDMX/','+FRA/MX/','+FRA/STDMX/' *.remote_login_passwordfile='exclusive' *.STANDBY_FILE_MANAGEMENT='AUTO' *.fal_client='STDMX *.fal_server='MX' *.sec_case_sensitive_logon=FALSE |
12. Password (orapw$instance_ismi) dosyalarını oluşturuyoruz. (4. adımda standby için de oluşturdu iseniz gerek yok)
|
1 |
$orapwd file=$ORACLE_HOME/dbs/orapwstdmx password=0racle ignorecase=y force=y |
|
1 2 3 4 |
SQL>select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS -------------------------- SYS TRUE TRUE FALSE |
13. listener.ora ya SID bilgileri girilir
not _1 : herhangi bir değisiklik yapmadan testlerde sorunsuz calıştı lakin makalelerde asagidaki listener girişleri yapılmış.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
# listener.ora Network Configuration File: /u01/11.2.0/grid/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stddbk.sysdba.org)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON stdmx = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stddbk.sysdba.org)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = mx.sysdba.org) (ORACLE_HOME = /u01/11.2.0/grid) (SID_NAME = mx) ) (SID_DESC = (GLOBAL_DBNAME = stdmx.sysdba.org) (ORACLE_HOME= /u01/11.2.0/grid) (SID_NAME = stdmx) ) (SID_DESC = (GLOBAL_DBNAME = mx_DGMGRL) (ORACLE_HOME= /grid/11.2.0/grid) (SID_NAME = mx) ) (SID_DESC = (GLOBAL_DBNAME = stdmx_DGMGRL) (ORACLE_HOME= /u01/11.2.0/grid) (SID_NAME = stdmx) ) ) |
Not_2 : satir baslarini solda bosluk birakmadan listener.ora ya yazilirsa sytax hatalari veriyor
14. tnsnamas.ora ya primary rac ın bilgileri girilir.(Primary ile aynı yapılır)
Hepsi ayni olacak.
$tnsping ile kontrol edilecek.
15. StandBY database i oluşturalım.
|
1 2 |
create SPFILE='+DATA/stdmx/spfilemx.ora' from pfile='/opt/oracle/stage/primaryinit.ora_for_std.ora'; startup nomount |
StandBY da
|
1 |
rman target sys/***sifre_sifre***@mx auxiliary / |
|
1 2 3 |
rman>duplicate target database for standby nofilenamecheck dorecover; veya rman>duplicate target database for standby dorecover; |
mount modda açıldığında standby redolog lar oluşturulur.
|
1 2 3 4 |
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 100M, GROUP 6 SIZE 100M, GROUP 7 SIZE 100M; |
|
1 2 3 4 |
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 SIZE 100M, GROUP 9 SIZE 100M, GROUP 10 SIZE 100M; |
|
1 2 3 4 5 6 7 8 9 10 |
shutdown immediate startup shutdown immediate startup nomount alter database mount standby database; alter database recover managed standby database using current logfile disconnect; veya alter database recover managed standby database disconnect; |
*****************************************************************************************************
Primary
|
1 2 |
select sequence#, first_time, next_time from v$archived_log order by sequence#; |
Standby
|
1 |
select thread#,sequence#,applied from v$archived_log; |
|
1 2 3 4 5 6 7 |
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; |
|
1 |
select max(sequence#) as STANDBY,applied from v$archived_log group by applied ; |
|
1 |
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2; |
|
1 |
Select process,status from v$managed_standby; |
|
1 |
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; |
|
1 |
SELECT MESSAGE FROM V$DATAGUARD_STATUS; |
|
1 |
select sequence#, first_time, next_time from v$archived_log order by sequence#; |
|
1 2 3 4 |
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'; SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; |
|
1 2 3 4 5 |
SELECT THREAD#, SEQUENCE#, APPLIED, TO_CHAR(FIRST_TIME, 'MM/DD/YY HH24:MI:SS') FIRST, TO_CHAR(NEXT_TIME, 'MM/DD/YY HH24:MI:SS') NEXT, TO_CHAR(COMPLETION_TIME, 'MM/DD/YY HH24:MI:SS') COMPLETION FROM V$ARCHIVED_LOG ORDER BY FIRST_TIME |
Arşivlenmiş (archived) son redologları görmek için
|
1 |
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; |
|
1 2 3 4 5 6 7 |
col DESTINATION format a40; col status format a40; col ARCHIVED_THREAD# format 999999; col ARCHIVED_SEQ# format 9999; SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# - FROM V$ARCHIVE_DEST_STATUS - WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE' |
|
1 2 3 4 5 6 |
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM - (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) - LOCAL WHERE - LOCAL.SEQUENCE# NOT IN - (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND - THREAD# = LOCAL.THREAD#) |
|
1 2 |
SELECT FREQUENCY, DURATION FROM - V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1; |
Primary
|
1 2 |
SELECT max(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM - WHERE DEST_ID=2 AND FREQUENCY>1; |
Primary
|
1 2 |
SELECT min( DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM - WHERE DEST_ID=2 AND FREQUENCY>1; |
|
1 2 3 4 5 |
COLUMN FILE_NAME FORMAT a55 SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L - WHERE NEXT_CHANGE# NOT IN - (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#) - ORDER BY THREAD#, SEQUENCE#; |
|
1 2 3 4 |
COLUMN NAME FORMAT A24 COLUMN VALUE FORMAT A16 COLUMN DATUM_TIME FORMAT A24 SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS; |
|
1 2 3 |
SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER; |
*****************************************************************************************************
Primary veritabanı için – her nodda;
node 1’de
|
1 2 3 |
alter system set dg_broker_config_file1='+DATA/MX/mx1.dat' scope=both sid='*'; alter system set dg_broker_config_file2='+DATA/MX/mx2.dat' scope=both sid='*'; alter system set dg_broker_start=true scope=both sid='*'; |
Standby veritabanı için ise her nodda;
|
1 2 3 |
alter system set dg_broker_config_file1='+DATA/STDMX/stdmx1.dat' scope=both; alter system set dg_broker_config_file2='+DATA/STDMX/stdmx2.dat' scope=both; alter system set dg_broker_start=true scope=both sid='*'; |
|
1 2 3 4 5 6 7 8 9 10 |
dgmgrl connect / CREATE CONFIGURATION 'dg_broker' AS PRIMARY DATABASE IS 'mx' CONNECT IDENTIFIER IS mx; ADD DATABASE 'stdmx' AS CONNECT IDENTIFIER IS stdmx; EDIT DATABASE 'mx' SET PROPERTY FastStartFailoverTarget='stdmx'; EDIT DATABASE 'stdmx' SET PROPERTY FastStartFailoverTarget='mx'; EDIT DATABASE "mx" SET PROPERTY 'LogXptMode'='SYNC'; EDIT DATABASE "stdmx" SET PROPERTY 'LogXptMode'='SYNC'; EDIT DATABASE 'mx' SET PROPERTY NetTimeout=180 ; EDIT DATABASE 'stdmx' SET PROPERTY NetTimeout=180 ; |
11.2.0.2.0 sürümündeki bug dan dolayi NetTimeout=180 yapildi
Bu ikisi dikkatli kullanilmali protection modelarin durumuna gore db yi kapatabiliyor..
|
1 2 |
enable database 'stdmx' ; enable configuration ; |
|
1 |
enable fast_start failover; |
Bilgi
|
1 2 3 4 5 6 7 8 |
select FS_FAILOVER_OBSERVER_HOST from v$database; show configuration ; show configuration verbose; SHOW DATABASE VERBOSE 'mx' ; SHOW DATABASE VERBOSE 'stdmx' ; show instance verbose stdmx; show instance verbose mx1; show instance verbose mx2; |
|
1 2 |
DISABLE FAST_START FAILOVER force ; REMOVE CONFIGURATION; |
Log
|
1 2 |
/u01/app/oracle/diag/rdbms/mx/mx1/trace/drcmx1.log SwitchOVER |
|
1 |
switchover to 'stdmx'; (o andaki standBY DB nin ismi yazilir.) |
Not ; switCHOVER dan sonra dgmgrl show configuration da bazi hatalar gorunebiliyor ama bir sure sonra kendini topluyor ve hatalar gidiyor. Iki tarafa da basarili bir sekilde switchoVER gerceklesti.
*****************************************************************************************************
GAP resulation
|
1 |
SELECT * FROM V$ARCHIVE_GAP; |
|
1 |
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; |
|
1 2 |
SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND - DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10; |
|
1 |
ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_7.arc'; |
|
1 |
SELECT * FROM V$ARCHIVE_GAP; |
ArchiveLOG ların ASM’ye alınıp kayıt edilmesi
|
1 2 3 |
catalog archivelog '/cluster/home/oracle/o1_mf_1_245440_8dp02lsr_.arc'; copy archivelog '/tmp/thread_1_seq_105.345.813673069' to '+fra'; ALTER DATABASE REGISTER LOGFILE '+FRA/stdmx/archivelog/2013_04_26/thread_1_seq_105.317.813752707' ; |
***************************************************************************************************
|
1 2 3 |
col DATABASE_ROLE for a20 col SWITCHOVER_STATUS for a20 select DATABASE_ROLE,switchover_status from v$database; |
|
1 |
alter system set dg_broker_start=true scope=both sid='*'; (yapmakta fayda var butun node larda) |
SWITCHOVER yapmak için
Primary
|
1 2 |
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; SHUTDOWN IMMEDIATE; |
Standby;
MRP durduğunda
|
1 2 |
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; ALTER DATABASE OPEN; |
Orjinal Primary (Şu an standby)
|
1 2 |
STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; |
Error
|
1 |
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; |
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY *
ORA-01105: mount is incompatible with mounts by other instances
Action: Query the GV$INSTANCE view as follows to determine which instances are
causing the problem:
|
1 2 |
SQL> SELECT INSTANCE_NAME, HOST_NAME FROM GV$INSTANCE WHERE INST_ID <> (SELECT INSTANCE_NUMBER FROM V$INSTANCE); |
INSTANCE_NAME————-INST2
HOST_NAME—————–standby2
In the previous example, the identified instance must be manually shut down before
the switchover can proceed. You can connect to the identified instance from your
instance and issue the SHUTDOWN statement remotely, for example:
|
1 2 3 4 |
SQL> CONNECT SYS@standby2 AS SYSDBA Enter Password: SQL> SHUTDOWN; SQL> EXIT |
*****************************************************************************************
Protection modes
|
1 |
SELECT protection_mode FROM v$database; |
|
1 |
select database_role, open_mode, name, db_unique_name from v$database; |
Primary
|
1 |
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdmx AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdmx' scope=both sid='*'; |
Standby
|
1 |
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=mx AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mx' scope=both sid='*'; |
Maximum Availability
|
1 2 |
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; |
Maximum Performance
|
1 2 |
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; |
Maximum Protection
|
1 2 3 4 5 |
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; ALTER DATABASE OPEN; |
*****************************************************************************************************
Primary de log_archive_dest_state “defer” moda alınır.
|
1 |
alter system set log_archive_dest_state_2=defer sid = '*'; |
|
1 |
alter system switch logfile ; |
|
1 |
shutdown immediate |
MRP process standby da kapatılır.
|
1 2 |
alter database recover managed standby database cancel; shutdown immediate; |
|
1 |
startup nomount; |
|
1 2 |
alter database mount standby database; alter database recover managed standby database using current logfile disconnect; |
veya
|
1 |
alter database recover managed standby database disconnect from session; |
primary database açılır log_archive_dest_state aktif (enable) edilir ve
|
1 2 |
alter system switch logfile ; alter system set log_archive_dest_state_2=enable scope = both sid = '*'; |
SHUTDOWN STANDBY DATABASE
step 1: Disable standby archive writing:
Primary/production database
|
1 |
alter system set log_archive_dest_state_2=defer scope = both sid = '*'; |
step 2: auto recovery standby de deaktif edilir.
|
1 |
alter database recover managed standby database cancel; |
PS: In case of RAC: above command should run on just ONE of the standby RAC node.
|
1 |
SQL> shutdown immediate |
RAC ortamında tüm stanby node larda uygulanmalı
STARTUP STANDBY DATABASE
Gerek olursa archivelog lar standby a taşınır.
|
1 |
$scp /opt/oracle/archive/*.arc oracle@DR-Site-IP:/opt/oracle/archive |
Step 2: standby database açılır ve automatic recovery başlatılır.
|
1 2 3 |
SQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session; |
PS: RAC ortamında bütün standby node larda uygulanır.
|
1 2 |
SQL> startup nomount SQL> alter database mount standby database; |
step 3: standby archive writing aktif edilir (enable)
primary/production database
|
1 |
alter system set log_archive_dest_state_2=enable scope = both sid = '*'; |
RAC ortamında sadece bir node da çalıştırılması yeterli (sid=’*’).
*****************************************************************************************************
Bozulmadan önleyelim. Ayrıntılı bilgi için Doc ID 1302539.1
primary
|
1 2 3 |
alter system set DB_BLOCK_CHECKSUM=FULL scope=both sid='*'; alter system set DB_BLOCK_CHECKING=FULL scope=both sid='*'; alter system set DB_LOST_WRITE_PROTECT=TYPICAL scope=both sid='*'; |
standby
|
1 2 3 |
alter system set DB_BLOCK_CHECKSUM=FULL scope=both sid='*'; alter system set DB_BLOCK_CHECKING=FULL scope=both sid='*'; alter system set DB_LOST_WRITE_PROTECT=TYPICAL scope=both sid='*'; |
1
|
1 2 3 4 5 6 7 |
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA001" ORA-27041: unable to open file Linux-x86_64 Error: 13: Permission denied Additional information: 9 SUCCESS: diskgroup DATA was mounted Errors in file /u01/app/oracle/diag/rdbms/stdhira/stdhira/trace/stdhira_ora_26491.trc (incident=7361): ORA-00600: |
Hatasi alinirsa
root ile
|
1 |
/u01/11.2.0/grid/bin/setasmgid o=/u01/app/oracle/product/11.2.0/db_1/bin/oracle |
2
Deletion of Applied Archivelogs
If the archivelog files are being managed in the Fast Recovery Area then automatic deletion of backed up and applied archivelog files can be setup by issuing the following RMAN configure command on the primary database:
|
1 |
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY BACKED UP <#> TIMES TO DISK; |
Creating a physical standby from ASM primary [ID 787793.1]
http://www.datadisk.co.uk/html_docs/oracle_dg/troubleshooting.htm
http://www.datadisk.co.uk/html_docs/oracle_dg/troubleshooting.htm
http://www.oracledba.org/”>http://www.oracledba.org
3
Data Guard Broker Status Summary:
Type Name Severity Status
|
1 2 3 |
Configuration dg_broker Warning ORA-16608 Primary Database hira Success ORA-00000 Physical Standby Database stdhira Warning ORA-16792 |
hata varsa
show database ‘stdhira’ ‘InconsistentProperties’;
ile farkli degerler bulunup (bazan degerler ayni olsa da ) scope=spfile/both ile degistirince düzeliyor.
4
|
1 2 3 4 5 6 |
SQL Execution error=604, sql=[alter system set log_archive_dest_1='']. See error stack below. ORA-00604: error occurred at recursive SQL level 1 ORA-02097: parameter cannot be modified because specified value is invalid ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires ORA-00604 ORA-02097 ORA-16028 BUG 12535659 - BROKER OVERRIDES MANDATORY/LOG_ARCHIVE_MIN_SUCCEED_DEST |
Solution
Ozet
o anki primary de
|
1 |
remove configuration preserve destinations; |
RAC
|
1 |
alter system set log_archive_dest_1='location=+fra' scope=both sid='*'; |
single instance
|
1 |
alter system set log_archive_dest_1='location=+fra' scope=both ; |
metalink te ise asagidaki adimlar anlatilmis.
a. Use DGMGRL to gather information about the Broker configuration.
— From current Primary
|
1 2 |
DGMGRL> show configuration verbose; DGMGRL> show database verbose <db_unique_name>; |
# This should be done for the primary and each standby.
b. Remove the Broker configuration.
— From current Primary:
|
1 2 |
DGMGRL> connect sys/password DGMGRL> remove configuration preserve destinations; |
# This will ensure standby operations continue while the Broker
# configuration is being re-created.
c. Change the LOG_ARCHIVE_DEST_n destinations defined with LOCATION to remove DB_UNIQUE_NAME.
|
1 |
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch'; |
# This must be done on all databases in the Data Guard environment
# that Broker will manage.
d. Re-create the Broker configuration.
— From current Primary:
|
1 2 |
DGMGRL> connect sys/password DGMGRL> create configuration .... |
# Use the info from Step #1 to re-create the Broker configuration
# and modify the properties on each database before enabling.
e.
ORA-16826: apply service state is inconsistent with the DelayMins property
dgmgrl configurasyonunu disable/enable edince düzeliyor.
|
1 2 |
disable configuration enable configuration |
5 . Bağlantı sorunları yaşandığında bakılacak yerler
|
1 2 3 4 |
show parameter LOCAL_LISTENER ---> değer görülmeli ALTER SYSTEM REGISTER; ALTER SYSTEM set LOCAL_LISTENER=LISTENER alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=stddbk.sysdba.org)(PORT=1521))' scope=both; |
6.Exception in thread “main” java.lang.UnsatisfiedLinkError: /tmp/OraInstall2013-12-30_01-26-43PM/jdk/jre/lib/sparcv9/motif21/libmawt.so: ld.so.1: java: fatal: libXm.so.4: open failed: No such file or directory….
Çözüm
|
1 |
export AWT_TOOLKIT=XToolkit |
*****************************************************************************************************
RMAN ile farklı...
12 Mart 2019