Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use dgmgrl in oracle 11g dataguard, I believe most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

1 enable dgmgrl

Related parameters:

Dg_broker_start

Dg_broker_config_file1

Configuration:

Alter system set dg_broker_start=true scope=both

At this point, the database will increase the dmon process.

[oracle@12crac2 ~] $ps-ef | grep-I _ dmon | grep-v greporacle 2699 1 0 06:57? 00:00:01 ora_dmon_db1

Corresponding log:$ORACLE_BASE/diag/rdbms/xxx/xxxx/drc$ORACLE_SID.log

However, it is not available for use at this time, as follows:

DGMGRL > show configurationORA-16532: Data Guard broker configuration does not exist

Need to manually configure and add:

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

Note: add database 'SBDB1', the dg here refers to the dbuniquename of database, while the sbdb1 of as connect identifier is sbdb1 refers to the net service name that tnsname.ora connects to the standby database. Note that it is size-sensitive. The default is lowercase.

At the same time, check the alert_$ORACLE_SID.log log to see:

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='*'

Check drc$ORACLE_SID.log:

Creating Data Guard Broker Monitor Process (DMON) 04Compact 03Accord 2018 23:26:33 > > Starting Data Guard Broker bootstrap show database SBDB1 Object "sbdb1" was not found

Pay attention to case

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

Three related tests: snapshot standby,switchover

3.1No database flashback on test:

For methods that do not use dgmrl, see: 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

Here, from the alert_sbdb1.log, the database is not restarted, but the session is killed.

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 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

Manually start the slave to the mount state, and the subsequent dgmgrl automatically recovers to the sbdb1, but often dies in the last step in the open process. The related logs are as follows:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SBDB1) Killing 3 processes with pids 3013 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 not alter DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800 srl3.f' SIZE alter DATABASE ADD STANDBY LOGFILE' srl3.f' SIZE 52428800 ter 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 onlinelog 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 onlinelog 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...

The last step will fake death for a while, never mind, just shutdown abort, and then start it. Because of the existence of dgmgrl, he will automatically add the command, as shown in the following figure:

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

Why do you automatically check the database configuration:

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 enable Fast start failover

DGMGRL > enable fast_start failoverError: ORA-16651: requirements not met for enabling fast-start failoverFailed.DGMGRL > exit [oracle@12crac2 ~] $oerr ora 1665116651, 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.

To put it simply:

1 ensure that broker is configured to run in Max Availability mode.

2 enable flashback database on both primary and standby machines, which is used in reinstate failed's database.

3 start observer

Enable database flashback to enable again:

DGMGRL > enable fast_start failoverEnabled.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 1681916819, 0000, "fast-start failover observer not started" / / * Cause: The observer for fast-start failover was not started. As / 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.

Start observer:

DGMGRL > start observerObserver started

View the configuration again:

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

Manual switchover step: https://blog.51cto.com/snowhill/1951592

Now to simplify it, you only need one command to use dgmgrl: 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 databaseORA-12514: TNS:listener does not currently know of service requested in connect descriptorFailed.Warning: You are no longer connected to ORACLE.Please complete the following steps to finish switchover: startup instance "db1" of database "db"

Don't worry about this failed. Start the original main library manually and you can complete the switch.

When the switch is over, check:

DGMGRL > show configuration Configuration-db Protection Mode: MaxAvailability Databases: SBDB1-Primary database db-(*) Physical standby database Fast-Start Failover: ENABLED Configuration Status:SUCCESS

Then cut it back:

DGMGRL > switchover to dbPerforming 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".

If you have any questions, please see the official reference: https://docs.oracle.com/cd/E11882_01/server.112/e40771/sofo.htm#DGBKR385

Four dgmgrl adjustment parameters:

4.1 adjust application latency

Edit database 'SBDB1' set property DelayMins=1

In fact, it is the following command:

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.2 adjust the asynchronous synchronous mode

Edit database 'SBDB1' set property LogXptMode=sync

The corresponding commands are as follows:

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

Of course, all the parameters that can be configured with broker, do not use sqlplus to do it.

5. Related error reporting processing

Broker errors are generally caused by case inconsistencies, database configuration and broker configuration inconsistencies. After broker is enabled, dataguard-related configurations are best managed by broker.

5.1 ora-16541

DGMGRL > show configuration ORA-16541: database is not enabled

This error has a node, which is usually caused by inconsistent configuration

[oracle@12crac1 dbs] $oerr ora 1654116541, 00000, "database is not enabled" / / * Cause: The database specified in the request was not enabled.// * Action: Select an enabled database and reissue the request.

If you look at it on another node, the error reported is generally different.

DGMGRL > show configurationConfiguration-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

This error is caused by the inconsistency between the parameters and the dgmgrl. Check the configuration after dg broker is enabled and find that the change is caused by a change in log_file_name_convert,db_file_name_convert. The following configuration is fine on another node.

DGMGRL > edit database db set property LogFileNameConvert='/u01/app/oracle/oradata/SBDB1/,+data';Property "logfilenameconvert" updatedDGMGRL > edit database db set property DbFileNameConvert='/u01/app/oracle/oradata/SBDB1/,+data';Property "dbfilenameconvert" updatedDGMGRL > edit database 'SBDB1' set property DbFileNameConvert='+data,/u01/app/oracle/oradata/SBDB1/';Property "dbfilenameconvert" updatedDGMGRL > edit database' SBDB1' set property LogFileNameConvert='+data,/u01/app/oracle/oradata/SBDB1/' Property "logfilenameconvert" updatedDGMGRL > enable configurationDGMGRL > 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

This mistake is bullshit. Stop observer,start observer is fine.

5.3 ora-16661

When fast failover is enabled, the two nodes are shut down abnormally, and the database is started again. Generally, both nodes are primary. When dgmgrl is enabled, it will report:

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: ENABLED

Configuration Status:

ERROR

The other node reports an error as follows:

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-16649: possible failover to another database prevents this database from

Being opened

It's not right to check the status of the database:

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

Don't actually go to recreated standby if you see this error. After confirming that there is no problem with your configuration, go directly to another node:

DGMGRL > reinstate database db

Reinstating database "db", please wait...

Reinstatement of database "db" succeeded

At this point, the launched node will become physical standby, and stop observer,start observer will be fine again.

5.4 Error: ORA-16525: the Data Guard broker is not yet available

The db_broker_start parameters of the main library or standby library are not set correctly

5.5 ORA-16797: database is not using a server parameter file

Do not start the database with spfile

5.6 the dg_broker_config_file_1/2 parameters of ora-16610 master or slave database are not set correctly

DGMGRL > show configurationConfiguration-1 Protection Mode: MaxPerformance Databases: prod2-Primary database standby-Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:ORA-16610: command "Broker operation" in progressDGM-17017: unable to determine configuration status

View 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: 308 10:52:38drcx: 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: 3DMON Registering service standby_DGB with listener (s) 08 10:52:38drcx: 2012 ORA-27037

Solution:

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 configurationDGMGRL > show configuration Configuration-1 Protection Mode: MaxPerformance Databases: prod2-Primary database standby-Physical standby database Fast-Start Failover: DISABLED Configuration Status:SUCCESS is all the content of this article "how to use dgmgrl in oracle 11g dataguard". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.