oracle 11g dataguard之dgmgrl

標籤：dgmgrl snapshot

環境概述：搭好Dataguard,搭建參考：http://blog.51cto.com/snowhill/1923591
源：db_name:db rac
備：db_name:sbdb1 單機

１　啟用dgmgrl
相關參數：

dg_broker_start
dg_broker_config_file1

配置：
alter system set dg_broker_start=true scope=both;
此時資料庫會增加dmon進程

[[email protected] ~]$ ps -ef|grep -i _dmon|grep -v greporacle     2699      1  0 06:57 ?        00:00:01 ora_dmon_db1

但此時使用

DGMGRL> show configurationORA-16532: Data Guard broker configuration does not exist 需配置：DGMGRL> help create DGMGRL> create configuration ‘db‘ as primary database is db connect identifier is db;DGMGRL>help addDGMGRL>add database "SBDB1" as connect identifier is "sbdb1"  maintained as physical;DGMGRL>enable configuration

add database ‘SBDB1‘ ,這裡的dg是指database的db_unique_name,而as connect identifier is sbdb1這裡的sbdb1是指tnsname.ora串連到standby database的net service name.
注意區分大小，預設為小寫；

檢查alert_$ORACLE_SID.log日誌可以看到：

RSM0 started with pid=48, OS id=34551 ALTER SYSTEM SET log_archive_dest_1=‘location=USE_DB_RECOVERY_FILE_DEST‘,‘valid_for=(ALL_LOGFILES, ALL_ROLES)‘ SCOPE=BOTH;ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID=‘db1‘;ALTER SYSTEM SET log_archive_format=‘%t_%s_%r.dbf‘ SCOPE=SPFILE SID=‘db1‘;ALTER SYSTEM SET standby_file_management=‘AUTO‘ SCOPE=BOTH SID=‘*‘;ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID=‘*‘;ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID=‘*‘;ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID=‘*‘;

檢查drc$ORACLE_SID.log:

Creating Data Guard Broker Monitor Process (DMON)04/03/2018 23:26:33>> Starting Data Guard Broker bootstrap <<Broker Configuration File Locations:      dg_broker_config_file1 = "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1SBDB1.dat"      dg_broker_config_file2 = "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2SBDB1.dat"04/03/2018 23:26:38Broker Configuration:       "db"      Protection Mode:            Maximum Performance      Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0      Primary Database:           db (0x01010000)      Standby Database:           SBDB1, Enabled Physical Standby (0x02010000)

二　檢查配置：

DGMGRL> show database sbdb1Object "sbdb1" was not foundDGMGRL> show database SBDB1  Object "sbdb1" was not found

注意大小寫

DGMGRL> show database ‘SBDB1‘ Database - SBDB1   Role:            PHYSICAL STANDBY  Intended State:  APPLY-ON  Transport Lag:   0 seconds (computed 0 seconds ago)  Apply Lag:       0 seconds (computed 0 seconds ago)  Apply Rate:      0 Byte/s  Real Time Query: ON  Instance(s):    SBDB1 Database Status:SUCCESS

三　snapshot standby 測試

不用dgmrl的方法參見：http://blog.51cto.com/snowhill/2047857
DGMGRL> convert database ‘SBDB1‘ to snapshot standby;
Converting database "SBDB1" to a Snapshot Standby database, please wait...
Database "SBDB1" converted successfully
這裡從alert_sbdb1.log裡看，資料庫並沒有發生重啟，只是將會話殺掉了；
3.1不開database flashback on 測試：

SQL> select open_mode, database_role, protection_mode,flashback_on from v$database; OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      FLASHBACK_ON-------------------- ---------------- -------------------- ------------------READ WRITE           SNAPSHOT STANDBY MAXIMUM PERFORMANCE  RESTORE POINT ONLYSQL> truncate table system.test; Table truncated. SQL> select count(*) from system.test;   COUNT(*)----------         0               DGMGRL>convet database ‘SBDB1‘ to physical standby;Unable to connect to databaseORA-12545: Connect failed because target host or object does not exist Failed.Warning: You are no longer connected to ORACLE. Please complete the following steps and reissue the CONVERT command:        shut down instance "SBDB1" of database "SBDB1"        start up and mount instance "SBDB1" of database "SBDB1"

手動啟動備庫到mount狀態，後面的dgmgrl自動回復到sbdb1,但在open過程中經常會死在最後一步，相關日誌如下：

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SBDB1)Killing 3 processes with pids 3013,3017,3019 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 3025Flashback Restore StartFlashback Restore CompleteDrop guaranteed restore point Guaranteed restore point  droppedClearing standby activation ID 1736364983 (0x677ed3b7)The primary database controlfile was created using the‘MAXLOGFILES 192‘ clause.There is space for up to 188 standby redo logfilesUse the following SQL commands on the standby database to createstandby redo logfiles that match the primary database:ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f‘ SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f‘ SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f‘ SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f‘ SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE ‘srl5.f‘ SIZE 52428800;Waiting for all non-current ORLs to be archived...All non-current ORLs have been archived.Clearing online redo logfile 1 /u01/app/oracle/oradata/SBDB1/SBDB1/onlinelog/o1_mf_1_fd4wmho5_.logClearing online log 1 of thread 1 sequence number 3Clearing online redo logfile 1 completeClearing online redo logfile 2 /u01/app/oracle/oradata/SBDB1/SBDB1/onlinelog/o1_mf_2_fd4wmjq3_.logClearing online log 2 of thread 1 sequence number 4Clearing online redo logfile 2 completeCompleted: alter database convert to physical standbyWed Apr 04 01:15:50 2018Primary database is in MAXIMUM PERFORMANCE modeRFS[3]: Assigned to RFS process 3029RFS[3]: Selected log 5 for thread 1 sequence 70 dbid 1729483220 branch 965667412Wed Apr 04 01:16:11 2018RFS[4]: Assigned to RFS process 3031RFS[4]: Selected log 6 for thread 1 sequence 69 dbid 1729483220 branch 965667412Wed Apr 04 01:16:11 2018Expanded controlfile section 11 from 28 to 203 recordsRequested to grow by 175 records; added 7 blocks of recordsArchived Log entry 29 added for thread 1 sequence 69 ID 0x6715d4d4 dest 1:Wed Apr 04 01:16:13 2018ARC2: Becoming the active heartbeat ARCHWed Apr 04 01:17:13 2018alter database openData Guard Broker initializing...

最後一步會假死一下，沒關係，直接shutdown abort，再啟動，就可以了；由於dgmgrl的存在,他會自動補上命令,如所示：

SQL> select open_mode, database_role, protection_mode,flashback_on from v$database; OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      FLASHBACK_ON-------------------- ---------------- -------------------- ------------------READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOSQL> select count(*) from system.test;   COUNT(*)----------     15386

為啥會自動，檢查資料庫配置：

DGMGRL> show database verbose ‘SBDB1‘; Database - SBDB1   Role:            PHYSICAL STANDBY  Intended State:  APPLY-ON  Transport Lag:   0 seconds (computed 1 second ago)  Apply Lag:       0 seconds (computed 1 second ago)  Apply Rate:      0 Byte/s  Real Time Query: ON

四　dgmgrl 調整參數：
4.1調整應用延遲
DGMGRL> edit database ‘SBDB1‘ set property DelayMins=1 ;
其實就是如下命令：

ALTER SYSTEM SET log_archive_dest_2=‘service="sbdb1"‘,‘LGWR ASYNC NOAFFIRM delay=1 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="SBDB1" net_timeout=30‘,‘valid_for=(all_logfiles,primary_role)‘ SCOPE=BOTH;ALTER SYSTEM SWITCH ALL LOGFILE start (db1)ALTER SYSTEM SWITCH ALL LOGFILE complete (db1)DGMGRL> edit database ‘SBDB1‘ set property LogXptMode=sync;

4.２調整非同步同步模式
DGMGRL> edit database ‘SBDB1‘ set property LogXptMode=sync;
對應的命令如下：
ALTER SYSTEM SET log_archive_dest_2=‘service="sbdb1"‘,‘LGWR SYNC AFFIRM delay=1 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="SBDB1" net_timeout=30‘,‘valid_for=(all_logfiles,primary_role)‘ SCOPE=BOTH;

oracle 11g dataguard之dgmgrl