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

This article mainly introduces "High availability example Analysis of oracle11g dg broker configuration Service". In daily operation, I believe that many people have doubts about the high availability example analysis of oracle11g dg broker configuration service. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "High availability example Analysis of oracle11g dg broker configuration Service". Next, please follow the editor to study!

Premise:

Main library: 10.9.21.178 db_name: db2

Dg Library: 10.9.21.179 db_name: db1

1: the overall steps of automatic failover operation for oracle DG service:

1. Configure to create a service dbha first

two。 Create a trigger to automatically start the hbha service in the main library

3. Set the TAF function of service on primary library

4. Modify the url in the program to realize the automatic switching of services

Second: explain several concepts

1.TAF (Transparent Application Failover) is a transparent application failover technology. When there is a problem with the initialization connection, this feature ensures that the application reconnects to the available services. During reconnection, previously active transactions will be rolled back, but TAF can guarantee that the SELECT statement will not be terminated under "specific conditions".

Features of TAF:

1:TAF is a feature provided by the ORACLE client. Using TAF, there are certain requirements for the client environment, such as the JDBC driver of JAVA, the version of the Oracle client, etc. (8i starts to support TAF)

2: generally speaking, TAF can be divided into two types: TAF during connection and TAF after session establishment.

3:TAF itself has nothing to do with the RAC environment, but it is generally used in the RAC environment to minimize the impact of the most application. TAF can also be used in a single instance environment, so that PL/SQL developer can be used to connect to the database. Even if the database instance is restarted, there is no need to reconnect.

4:TAF configuration can be added on the client or server side, that is, when connecting to the database, such as the program url

Or configure FAILOVER_MODE= in tnsname.ora (TYPE = SELECT) (METHOD = BASIC) (RETIRES = 180) (DELAY = 15)), this is the client configuration, then the server configuration is to modify the properties of each service on the server side!

Begin

Dbms_service.modify_service

('dbha'

FAILOVER_METHOD= > 'BASIC'

FAILOVER_TYPE= > 'SELECT'

FAILOVER_RETRIES= > 15

FAILOVER_DELAY= > 5)

End

/

5: TAF will be disabled after configuring the GLOBAL_DBNAME parameter of the listener.ora file

The difference between 6:oracle TAF and scan ip failover is that if the scan ip is connected, if the node where the scan ip is located fails

Then the connection is disconnected, if there is no automatic reconnection mechanism in the program, or the timeout value in the program connection pool is not yet available.

When the time comes, you will have to restart the service in order to reconnect to the database, but TAF will not cause the program to report an error, it will just roll back

Transactions that are not committed will automatically reconnect to another node, and select will not FAILOVER_TYPE= > 'SELECT''.

Stop, continue to find the results at another node!

Three: the specific process is as follows:

Add and start the service in the main library, note that this service is for the program to configure URL to use!

Add the service first, and then start the service. If you don't start it, show parameter service_name will not see it. After starting, a new service appears in the service_name parameters, and the pmon process automatically completes listening to register this service, and after restarting the database, the added service disappears and becomes invalid!

The packages to add services are as follows:

DBMS_SERVICE.CREATE_SERVICE (

Service_name IN VARCHAR2

Network_name IN VARCHAR2

Goal IN NUMBER DEFAULT NULL

Dtp IN BOOLEAN DEFAULT NULL

Aq_ha_notifications IN BOOLEAN DEFAULT NULL

Failover_method IN VARCHAR2 DEFAULT NULL

Failover_type IN VARCHAR2 DEFAULT NULL

Failover_retries IN NUMBER DEFAULT NULL

Failover_delay IN NUMBER DEFAULT NULL

Clb_goal IN NUMBER DEFAULT NULL

Edition IN VARCHAR2 DEFAULT NULL)

-- The network name of the service as used in SQLNet connect descriptors for client connections. This is limited to the NET

Specific implementation: the first dbha is the name of the added service, and the second is the name of the network service

1. Add Servic

Begin

Dbms_service.create_service ('dbha','dbha')

End

/

two。 Start the service

Begin

DBMS_SERVICE.START_SERVICE ('dbha')

End

/

When you check the listening status, you will find that the service you just added has been dynamically registered by the pmon process.

3. Stop the service:

Begin

DBMS_SERVICE.STOP_SERVICE ('dbha')

End

/

4. Delete the service:

Begin

DBMS_SERVICE.DELETE_SERVICE ('dbha')

End

/

3.2. Add a trigger to the main library, which is used to determine that the instance is the main library role (read write), and then start the previously added

Service (dbha)

Create trigger tri_startservice after startup on database

Declare

Dg_role varchar (30)

Begin

Select database_role into dg_role from v$database

If dg_role='PRIMARY' then

DBMS_SERVICE.START_SERVICE ('dbha')

End if

End

/

3.3 enable the TAF feature for the previously added service in the main database:

Begin

Dbms_service.modify_service

('dbha'

FAILOVER_METHOD= > 'BASIC'

FAILOVER_TYPE= > 'SELECT'

FAILOVER_RETRIES= > 15

FAILOVER_DELAY= > 5)

End

/

3.4. Modify the URL of the program

Datasource.url=jdbc:oracle:thin:@ (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=10.9.21.178) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=10.9.21.179) (PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=dbha) (FAILOVER_MODE= (TYPE=SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)))

If it is oracle rac:

Datasource.url=jdbc:oracle:thin:@ (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.216) (PORT = 1521)) (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.217) (PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cbl) (FAILOVER_MODE= (TYPE = SELECT) (METHOD = BASIC) (RETIRES = 180) (DELAY = 15))

So far, the high availability configuration of oracle 11g adg service has been completed.

Summary: this time can be tested, when your master library shutdown abort, your slave library is automatically converted to the master library, then the trigger starts the dbha service, and then pmon automatically registers to listen, and then because you have configured the TAF feature, your service will not report an error (ORA-0133:ORACLE initialization or shutdown in progress error), but automatically connect to the new master library!

Verification process:

1. Client configuration tnsnames.ora

Dbha=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.9.21.178) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.9.21.179) (PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dbha)

)

)

two。 Connect to the database using dbha services

[oracle@beijing-fuli-hadoop-02 admin] $sqlplus liuwenhe/liuwenhe@dbha

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 13 17:11:55 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Confirm that you are connected to the main library db2

SQL > select instance_name from v$instance

INSTANCE_NAME

-

Db2

3. Abnormally close db2

SQL > shu abort

ORACLE instance shut down.

4. The previously connected session continues to query the instance name, not reporting an error but stuck, because the main library db2 has been closed, and the dg library

Db1 is still in the process of automatic conversion, so none of them can provide service (about 10 seconds), and then connect to db1:

SQL > select instance_name from v$instance

INSTANCE_NAME

-

Db2

SQL > select instance_name from vault instance;-db1 is displayed after being stuck for about 10 seconds

INSTANCE_NAME

-

Db1

At this point, the study on "High availability example Analysis of oracle11g dg broker configuration Service" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.