oracle11gdataguard中dgmgrl怎么用
小編給大家分享一下oracle 11g dataguard中dgmgrl怎么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
奉新網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)建站！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、APP開(kāi)發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項(xiàng)目制作，到程序開(kāi)發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)建站2013年至今到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)建站。
１ 啟用dgmgrl
相關(guān)參數(shù)：
dg_broker_start
dg_broker_config_file1
配置：alter system set dg_broker_start=true scope=both;
此時(shí)數(shù)據(jù)庫(kù)會(huì)增加dmon進(jìn)程
[oracle@12crac2 ~]$ ps -ef|grep -i _dmon|grep -v grep oracle 2699 1 0 06:57 ? 00:00:01 ora_dmon_db1
對(duì)應(yīng)log:$ORACLE_BASE/diag/rdbms/xxx/xxxx/drc$ORACLE_SID.log
但此時(shí)使用是用不了的，如下所示：
DGMGRL> show configuration ORA-16532: Data Guard broker configuration does not exist
需手動(dòng)配置添加：
DGMGRL> help create DGMGRL> create configuration 'db' as primary database is db connect identifier is db; DGMGRL>help add DGMGRL>add database "SBDB1" as connect identifier is "sbdb1" maintained as physical; DGMGRL>enable configuration
注：add database 'SBDB1' ,這里的dg是指database的dbuniquename,而as connect identifier is sbdb1這里的sbdb1是指tnsname.ora連接到standby database的net service name.注意區(qū)分大小，默認(rèn)為小寫；
同時(shí)檢查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:38 Broker 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)
所有配置完成，這玩意只能用在企業(yè)版
二 檢查配置：
DGMGRL> show database sbdb1 Object "sbdb1" was not found DGMGRL> 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
三 相關(guān)測(cè)試:snapshot standby,switchover
3.1不開(kāi)database flashback on 測(cè)試：
不用dgmrl的方法參見(jiàn)：https://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里看，數(shù)據(jù)庫(kù)并沒(méi)有發(fā)生重啟，只是將會(huì)話殺掉了；
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 ONLY** SQL> 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 database ORA-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"
手動(dòng)啟動(dòng)備庫(kù)到mount狀態(tài)，后面的dgmgrl自動(dòng)恢復(fù)到sbdb1,但在open過(guò)程中經(jīng)常會(huì)死在最后一步，相關(guān)日志如下：
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 3025 Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point Guaranteed restore point dropped Clearing 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 logfiles Use the following SQL commands on the standby database to create standby 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_.log Clearing online log 1 of thread 1 sequence number 3 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u01/app/oracle/oradata/SBDB1/SBDB1/onlinelog/o1_mf_2_fd4wmjq3_.log Clearing online log 2 of thread 1 sequence number 4 Clearing online redo logfile 2 complete Completed: alter database convert to physical standby Wed Apr 04 01:15:50 2018 Primary database is in MAXIMUM PERFORMANCE mode RFS[3]: Assigned to RFS process 3029 RFS[3]: Selected log 5 for thread 1 sequence 70 dbid 1729483220 branch 965667412 Wed Apr 04 01:16:11 2018 RFS[4]: Assigned to RFS process 3031 RFS[4]: Selected log 6 for thread 1 sequence 69 dbid 1729483220 branch 965667412 Wed Apr 04 01:16:11 2018 Expanded controlfile section 11 from 28 to 203 records Requested to grow by 175 records; added 7 blocks of records Archived Log entry 29 added for thread 1 sequence 69 ID 0x6715d4d4 dest 1: Wed Apr 04 01:16:13 2018 ARC2: Becoming the active heartbeat ARCH Wed Apr 04 01:17:13 2018 alter database open Data Guard Broker initializing...
最后一步會(huì)假死一下，沒(méi)關(guān)系，直接shutdown abort，再啟動(dòng)，就可以了；由于dgmgrl的存在,他會(huì)自動(dòng)補(bǔ)上命令,如下圖所示：
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 NO SQL> select count(*) from system.test; COUNT(*) ---------- 15386
為啥會(huì)自動(dòng)，檢查數(shù)據(jù)庫(kù)配置：
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
3.2 啟用Fast start failover
DGMGRL> enable fast_start failover Error: ORA-16651: requirements not met for enabling fast-start failover Failed. DGMGRL> exit [oracle@12crac2 ~]$ oerr ora 16651 16651, 0000, "requirements not met for enabling fast-start failover" // *Cause: The attempt to enable fast-start failover could not be completed // because one or more requirements were not met: // - The Data Guard configuration must be in either MaxAvailability // or MaxPerformance protection mode. // - The LogXptMode property for both the primary database and // the fast-start failover target standby database must be // set to SYNC if the configuration protection mode is set to // MaxAvailability mode. // - The LogXptMode property for both the primary database and // the fast-start failover target standby database must be // set to ASYNC if the configuration protection mode is set to // MaxPerformance mode. // - The primary database and the fast-start failover target standby // database must both have flashback enabled. // - No valid target standby database was specified in the primary // database FastStartFailoverTarget property prior to the attempt // to enable fast-start failover, and more than one standby // database exists in the Data Guard configuration.
簡(jiǎn)單點(diǎn)來(lái)說(shuō)就是：
１確保broker配置為運(yùn)行在Max Availability模式。
２在primary和standby機(jī)器上都啟用flashback database，這個(gè)在reinstate failed的數(shù)據(jù)庫(kù)的時(shí)候要用。
３啟動(dòng)observer
啟用database flashback 再次enable：
DGMGRL> enable fast_start failover Enabled. DGMGRL> show configuration Configuration - db Protection Mode: MaxAvailability Databases: db - Primary database Warning: ORA-16819: fast-start failover observer not started SBDB1 - (*) Physical standby database Warning: ORA-16819: fast-start failover observer not started Fast-Start Failover: ENABLED Configuration Status: WARNING
[oracle@12crac2 ~]$ oerr ora 16819 16819, 0000, "fast-start failover observer not started" // *Cause: The observer for fast-start failover was not started. As a // result, fast-start failover could not happen in the case of // a primary database failure. // *Action: Start the fast-start failover observer by using, for example, the // DGMGRL START OBSERVER command.
啟動(dòng)observer:
DGMGRL> start observer Observer started
再次查看配置：
DGMGRL> show configuration Configuration - db Protection Mode: MaxAvailability Databases: db - Primary database SBDB1 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS
3.3 switchover
手工switchover 步驟：https://blog.51cto.com/snowhill/1951592
現(xiàn)在更簡(jiǎn)化下，用dgmgrl只需一個(gè)命令：SWITCHOVER TO
DGMGRL> switchover to 'SBDB1'; Performing switchover NOW, please wait... Operation requires a connection to instance "SBDB1" on database "SBDB1" Connecting to instance "SBDB1"... Connected. New primary database "SBDB1" is opening... Operation requires startup of instance "db1" on database "db" Starting instance "db1"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "db1" of database "db"
這個(gè)failed不用擔(dān)心，手動(dòng)啟動(dòng)一下原主庫(kù)就可以完成切換了；
切換完了，檢查下：
DGMGRL> show configuration Configuration - db Protection Mode: MaxAvailability Databases: SBDB1 - Primary database db - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS
再切回來(lái)：
DGMGRL> switchover to db Performing switchover NOW, please wait... Operation requires a connection to instance "db1" on database "db" Connecting to instance "db1"... Connected. New primary database "db" is opening... Operation requires startup of instance "SBDB1" on database "SBDB1" Starting instance "SBDB1"...
如還有啥疑問(wèn)看官方參考：https://docs.oracle.com/cd/E11882_01/server.112/e40771/sofo.htm#DGBKR385
四 dgmgrl 調(diào)整參數(shù)：
4.1調(diào)整應(yīng)用延遲edit database 'SBDB1' set property DelayMins=1 ;
其實(shí)就是如下命令：
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)
4.２調(diào)整異步同步模式edit database 'SBDB1' set property LogXptMode=sync;
對(duì)應(yīng)的命令如下：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;
當(dāng)然所有能用broker配置的參數(shù)，不要用sqlplus來(lái)干；
五 相關(guān)報(bào)錯(cuò)處理
broker錯(cuò)誤一般為大小寫不一致，數(shù)據(jù)庫(kù)配置和broker配置不一致引起，啟用broker后，dataguard相關(guān)的配置都最好用broker管理；
5.1 ora-16541
DGMGRL> show configuration ORA-16541: database is not enabled
此錯(cuò)誤出現(xiàn)一個(gè)節(jié)點(diǎn)，一般是由于配置不一致引起
[oracle@12crac1 dbs]$ oerr ora 16541 16541, 00000, "database is not enabled" // *Cause: The database specified in the request was not enabled. // *Action: Select an enabled database and reissue the request.
再另一個(gè)節(jié)點(diǎn)上查看，一般報(bào)的錯(cuò)不一樣
DGMGRL> show configuration Configuration - db Protection Mode: MaxAvailability Databases: db - Primary database Warning: ORA-16792: configurable property value is inconsistent with database setting SBDB1 - Physical standby database Warning: ORA-16792: configurable property value is inconsistent with database setting Fast-Start Failover: DISABLED
此錯(cuò)誤為參數(shù)與dgmgrl 里的不一致引起，檢查啟用dg broker之后的配置，發(fā)現(xiàn)更改改了log_file_name_convert,db_file_name_convert引起，在另一個(gè)節(jié)點(diǎn)做如下配置就可以了
DGMGRL> edit database db set property LogFileNameConvert='/u01/app/oracle/oradata/SBDB1/,+data'; Property "logfilenameconvert" updated DGMGRL> edit database db set property DbFileNameConvert='/u01/app/oracle/oradata/SBDB1/,+data'; Property "dbfilenameconvert" updated DGMGRL> edit database 'SBDB1' set property DbFileNameConvert='+data,/u01/app/oracle/oradata/SBDB1/'; Property "dbfilenameconvert" updated DGMGRL> edit database 'SBDB1' set property LogFileNameConvert='+data,/u01/app/oracle/oradata/SBDB1/'; Property "logfilenameconvert" updated DGMGRL> enable configuration DGMGRL> show configuration Configuration - db Protection Mode: MaxAvailability Databases: db - Primary database SBDB1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
5.2 ora-16820
DGMGRL> show configuration
Configuration - db
Protection Mode: MaxAvailability
Databases:
SBDB1 - Primary database
Error: ORA-16820: fast-start failover observer is no longer observing this database
db - (*) Physical standby database
Error: ORA-16820: fast-start failover observer is no longer observing this database
Fast-Start Failover: ENABLED
Configuration Status:
ERROR
此錯(cuò)誤比較扯淡,stop observer,start observer就可以了;
5.3 ora-16661
啟用fast failover后,非正常關(guān)閉兩個(gè)節(jié)點(diǎn),再次啟動(dòng)數(shù)據(jù)庫(kù),一般會(huì)出現(xiàn)兩個(gè)節(jié)點(diǎn)都是primary 的情況,這時(shí)啟動(dòng)dgmgrl會(huì)報(bào):
DGMGRL> show configuration
Configuration - db
Protection Mode: MaxAvailability
Databases:
SBDB1 - Primary database
Error: ORA-16820: fast-start failover observer is no longer observing this database
db - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLEDConfiguration Status:
ERROR
而另外一節(jié)點(diǎn)報(bào)錯(cuò)如下:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16649: possible failover to another database prevents this database from
being opened
檢查數(shù)據(jù)庫(kù)狀態(tài)也不怎么對(duì):
SQL> select database_role,FLASHBACK_ON from v$database;DATABASE_ROLE FLASHBACK_ON
PRIMARY YES
DGMGRL> show configuration
ORA-16795: the standby database needs to be re-created
看到此錯(cuò)誤也別真去recreated standby,在確認(rèn)你的配置上沒(méi)有問(wèn)題后,直接在另外一個(gè)節(jié)點(diǎn):
DGMGRL> reinstate database db
Reinstating database "db", please wait...
Reinstatement of database "db" succeeded
這時(shí)后啟動(dòng)的節(jié)點(diǎn)會(huì)變成physical standby,再次stop observer,start observer就可以了;
5.4 Error: ORA-16525: the Data Guard broker is not yet available
主庫(kù)或者備的庫(kù)的db_broker_start參數(shù)設(shè)置不對(duì)；
5.5 ORA-16797: database is not using a server parameter file
沒(méi)用spfile啟動(dòng)數(shù)據(jù)庫(kù)
5.6 ora-16610 主庫(kù)或者備庫(kù)的dg_broker_config_file_1/2參數(shù)設(shè)置不對(duì)
DGMGRL> show configuration Configuration - 1 Protection Mode: MaxPerformance Databases: prod2 - Primary database standby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ORA-16610: command "Broker operation" in progress DGM-17017: unable to determine configuration status
查看drcstandby.log
drcx: cannot open configuration file "/home/oracle/broker/standby2.data" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 08/02/2018 10:52:38 drcx: cannot open configuration file "/home/oracle/broker/standby1.dat" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 DMON Registering service standby_DGB with listener(s) 08/02/2018 10:52:41
解決辦法：
dgmgrl>disable configuration; sql>alter system set dg_broker_start=false; sys@standby>alter system set dg_broker_config_file1='/home/oracle/broker/standby1.dat'; sys@standby>alter system set dg_broker_config_file2='/home/oracle/broker/standby2.dat'; DGMGRL> enable configuration DGMGRL> show configuration Configuration - 1 Protection Mode: MaxPerformance Databases: prod2 - Primary database standby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
以上是“oracle 11g dataguard中dgmgrl怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
文章名稱：oracle11gdataguard中dgmgrl怎么用
當(dāng)前路徑：http://m.jiaotiyi.com/article/ishhds.html