Differences

This shows you the differences between two versions of the page.

--- oracle_rac_application_continuity [2020/10/22 11:55] – [Service Creation] andonovj
+++ oracle_rac_application_continuity [2020/10/22 12:13] (current) – andonovj
@@ Line 10: / Line 10: @@
-====Service Creation====
+======Service Creation======
-<Code: bash |Create service actest>
+<Code:bash|Create service actest>
 [oracle@enode01 labs]$ srvctl add service -db eastdb \
 -service actest \
@@ Line 24: / Line 24: @@
 -retention 86400 \
 -replay_init_time 1800 \
-<code>
+-notification TRUE
-====Startup====
+</Code>
+=====Status & Startup=====
+We can check the configuration of the service as follows. Remember to put the database name.
-<sxh>
+<Code:bash|Status and Start the service>
-[oracle@lparaca ~]$ srvctl status service -serverpool orclsp
+[oracle@enode01 labs]$ srvctl config service -db eastdb -service actest
-Service actest of database orcl is not running.
+Service name: actest
-[oracle@lparaca ~]$ srvctl start service -db orcl -service actest
+Server pool:
-</sxh>
+Cardinality: 1
+Disconnect: false
+Service role: PRIMARY
+Management policy: AUTOMATIC
+DTP transaction: false
+AQ HA notifications: true
+Global: false
+Commit Outcome: true
+Failover type: TRANSACTION
+Failover method:
+TAF failover retries: 30
+TAF failover delay: 5
+Connection Load Balancing Goal: SHORT
+Runtime Load Balancing Goal: SERVICE_TIME
+TAF policy specification: NONE
+Edition:
+Pluggable database name:
+Maximum lag time: ANY
+SQL Translation Profile:
+Retention: 86400 seconds
+Replay Initiation Time: 1800 seconds
+Session State Consistency: DYNAMIC
+GSM Flags: 0
+Service is enabled
+Preferred instances: eastdb1
+Available instances: eastdb2
+[oracle@enode01 labs]$
+[oracle@enode01 labs]$ srvctl start service -db eastdb -service actest
+[oracle@enode01 labs]$
+[oracle@enode01 labs]$ srvctl status service -db eastdb
+Service actest is running on instance(s) eastdb1
+[oracle@enode01 labs]$
+</Code>
+Now that we have the service, let's run the simple application. The application itself is one, but we will run it with different connection settings:
-====Check & Test====
+=====Application Source=====
-<sxh>
-[oracle@lparaca ~]$ srvctl status service -serverpool orclsp
-Service actest of database orcl is running on nodes: lparaca.
-[oracle@lparaca ~]$ lsnrctl status
-LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-JAN-2018 05:16:01
-Copyright (c) 1991, 2014, Oracle.  All rights reserved.
-Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
-STATUS of the LISTENER
-------------------------
-Alias                     LISTENER
-Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
-Start Date                19-JAN-2018 04:02:17
-Uptime                    0 days 1 hr. 13 min. 44 sec
-Trace Level               off
-Security                  ON: Local OS Authentication
-SNMP                      OFF
-Listener Parameter File   /u01/app/oracle/grid/network/admin/listener.ora
-Listener Log File         /u01/app/oracle/product/diag/tnslsnr/lparaca/listener/alert/log.xml
-Listening Endpoints Summary...
-  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
-  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.50)(PORT=1521)))
-  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.51)(PORT=1521)))
-Services Summary...
-Service "+ASM" has 1 instance(s).
-  Instance "+ASM1", status READY, has 1 handler(s) for this service...
-Service "actest" has 1 instance(s).     <--- **Our new SERVICE**
-  Instance "orcl_1", status READY, has 1 handler(s) for this service...
-Service "orcl" has 1 instance(s).
-  Instance "orcl_1", status READY, has 1 handler(s) for this service...
-Service "orclXDB" has 1 instance(s).
-  Instance "orcl_1", status READY, has 1 handler(s) for this service...
-The command completed successfully
-</sxh>
-Now your service will failover to the other instance and the uncommited changes will be replayed. Therefore, any node failure will be transparent to the end user.
-====Application Changes====
-The application should be changed also be using the UPC = Universal Connection Pooling
 Below you can see the source which I have used:
-<sxh>
+<Code:java|Application Source>
 package act;
 import java.sql.Connection;
 import java.sql.SQLException;
@@ Line 181: / Line 170: @@
 }
+</Code>
+Now that we have the code, we can run it one in a two ways:
+=====Noreplay Mode=====
+We can run that application standard without any application continuity as follows:
-</sxh>
+<Code:bash|No Repluay Mode>
+[oracle@enode01 labs]$ cat runnoreplay.sh
+java -classpath
-Once the code is compiled can be run via:
+./actest.jar:$ORACLE_HOME/ucp/lib/ucp.jar:$ORACLE_HOME/jdbc/lib/
+ojdbc6.jar actest.ACTest actest_noreplay.properties
+</Code>
+Where the connection properties are as follows:
+<Code:bash|Connection properties>
+[oracle@enode01 labs]$ cat actest_noreplay.properties
+username=scott
+password=tiger
+autoCommit=false
+# Use standard 12.1 datasource no replay
+datasource=oracle.jdbc.pool.OracleDataSource
+url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=
+cluster01-
+scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=actest.
+example.com)))
+# UCP setting:
+ucp_pool_size=2
+ucp_validate_connection_on_borrow=true
+ucp_connection_wait_timeout=60
+# Think Time taken to process the results from the database.
+Time in milliseconds.
+# -1 means no sleep.
+thread_think_time=20
+# Number of concurrent threads running in the application
+# UCP is tuned to have MAX and MIN limit set to this
+number_of_threads=6
+verbose=true
+</Code>
+That way, the application WILL NOT take advantage of the Application Continuity and it will fail if the connection gets interrupted:
+<Code:bash|Interrupted Connection>
+[oracle@enode01 labs]$ ./runnoreplay.sh
+######################################################
+Connecting to
+jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clus
+ter01-
+scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=actest.
+example.com)))
+# of Threads : 6
+UCP pool size : 2
+Thread think time : 20 ms
+######################################################
+active connections, avg response time from db 21 ms
+active connections, avg response time from db 11 ms
+... (killed process from 2nd session)
+.Exception occurred while getting connection:
+oracle.ucp.UniversalConnectionPoolException: Cannot get
+Connection from Datasource: java.sql.SQLRecoverableException:
+Listener refused the connection with the following error:
+ORA-12514, TNS:listener does not currently know of service
+requested in connect descriptor
+...
+[oracle@enode01 labs]$
+</Code>
+=====Replay Mode=====
+In order to make the application aware of the feature, we have to start it with the Universal Connection pooling using the replay option. To do that, we can use THE SAME application but with different connection properties:
+<Code:bash|Connection Properties>
+[oracle@enode01 labs]$ cat actest_replay.properties
+username=scott
+password=tiger
+autoCommit=false
+# Use new 12.1 replay datasource
+datasource=oracle.jdbc.replay.OracleDataSourceImpl
+url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=
+cluster01-
+scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=actest.
+example.com)))
+# UCP setting:
+ucp_pool_size=2
+ucp_validate_connection_on_borrow=true
+ucp_connection_wait_timeout=60
+# Think Time taken to process the results from the database.
+Time in milliseconds.
+# -1 means no sleep.
+thread_think_time=20
+# Number of concurrent threads running in the application
+# UCP is tuned to have MAX and MIN limit set to this
+number_of_threads=6
+verbose=true
+</Code>
+The properties are 99.9% the same, with the only difference of the: "datasource=oracle.jdbc.replay.OracleDataSourceImpl" property in the replay one:
+<Code:bash|Difference between Replay and No replay>
+[oracle@enode01 labs]$ diff actest_noreplay.properties
+actest_replay.properties
+,6c5,6
+< # Use standard 12.1 datasource no replay
+< datasource=oracle.jdbc.pool.OracleDataSource
+---
+> # Use new 12.1 replay datasource
+> datasource=oracle.jdbc.replay.OracleDataSourceImpl
+</Code>
+That way, the application will be able to reconnect after interrupted connection:
-<sxh>
+<Code:bash|Interrupted Connection>
-C:\Users\Julien>java -jar -Djava.util.logging.config.file=logging.properties.txt
+[oracle@enode01 labs]$ ./runreplay.sh
- C:\Users\Julien\Documents\NetBeansProjects\actest\dist\actest.jar all_out_loggi
+######################################################
-ng2
+Connecting to
-connection factory set
+jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clus
-Using URL
+ter01-
-jdbc:oracle:thin:@(DESCRIPTION = (TRANSPORT_CONNECT_TIMEOUT=3) (RETRY_COUNT=20)(
+scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=actest.
-FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = lparac-scan.myrac.com)(PORT = 15
+example.com)))
-)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ACTEST)))
+# of Threads : 6
-pool configured, trying to get a connection
+UCP pool size : 2
+Thread think time : 20 ms
+######################################################
+active connections, avg response time from db 304 ms
+active connections, avg response time from db 45 ms
+active connections, avg response time from db 304 ms
+active connections, avg response time from db 45 ms
+active connections, avg response time from db 29 ms
+active connections, avg response time from db 32 ms
+active connections, avg response time from db 29 ms
+active connections, avg response time from db 18 ms
+active connections, avg response time from db 36 ms
+... (Interupted connection)
+active connections, avg response time from db 5962 ms
+active connections, avg response time from db 98 ms
+active connections, avg response time from db 61 ms
+active connections, avg response time from db 72 ms
+[oracle@enode01 labs]$
-C:\Users\Julien>
+</Code>
-</sxh>