1. Oracle
RAC Installation and Configuration
Refer to Oracle Base for details of Oracle RAC 12c Installation and Configuration.
2. Configuration Environment DB Version
3. Configure auto start in PDB level
PDBs will start autmatically on CDB restart if PDB level auto start is configured.
--Specify instance name while opening PDBs.
ALTER pluggable DATABASE TAF_PDB OPEN instances=('ORCL1','ORCL2');
--Open PDB in all the available instances
ALTER pluggable DATABASE TAF_PDB OPEN instances=ALL;
4. Save the state of PDBs
5. Add a service as oracle user
srvctl add service -d ORCL -pdb TAF_PDB -s taf_pdb_svc -r ORCL1 -a ORCL2 -P BASIC -y AUTOMATIC -q TRUE -j LONG -z 180 -w 5 -e SELECT -m BASIC
6. Start the service
srvctl start service -d ORCL -s taf_pdb_svc
7. Check the status of the service
Login to CDB and check the status of service created above.
9. Create TNS entry in tnsnames.ora file
TAFPDB_SVC =
In case of RAC environments TNS entry has to be created in all the nodes participating in RAC environment.
10. Test TAF (Transparent Application Failover)
11. Find pid of pmon of connected instance
Remember the pid could be different in different systems. It does not remain the same.
12. Kill the process of pmon
[oracle@racsrv001 admin]$ kill -9 569
13. Check the instance name again in the previous connection.
Refer to Oracle Base for details of Oracle RAC 12c Installation and Configuration.
2. Configuration Environment DB Version
SQL> SET LINESIZE 250
COLUMN DESCRIPTION FORMAT A60
SELECT patch_id, version, flags, action, status, description
FROM dba_registry_sqlpatch
ORDER BY patch_id;
SQL> SQL> 2 3
PATCH_ID VERSION FLAGS
ACTION
STATUS DESCRIPTION
--------- ---------- ------ --------
-------- ----------------------------------------------------
19769480 12.1.0.2 NB APPLY SUCCESS Database Patch Set Update : 12.1.0.2.2
(19769480)
19877336 12.1.0.2 NJJ APPLY SUCCESS Database PSU 12.1.0.2.2, Oracle JavaVM
Component (
SQL>
3. Configure auto start in PDB level
PDBs will start autmatically on CDB restart if PDB level auto start is configured.
--Specify instance name while opening PDBs.
ALTER pluggable DATABASE TAF_PDB OPEN instances=('ORCL1','ORCL2');
--Open PDB in all the available instances
ALTER pluggable DATABASE TAF_PDB OPEN instances=ALL;
4. Save the state of PDBs
--Specify
instance name while saving PDBs state.
ALTER pluggable DATABASE TAF_PDB SAVE state instances=('ORCL1','ORCL2');
--Save
PDBs state in all the available instances
ALTER pluggable DATABASE TAF_PDB SAVE state instances=ALL;
--Specify
instance name while discarding PDBs state.
ALTER PLUGGABLE DATABASE TAF_PDB DISCARD STATE instances=ALL;
--Discard
PDBs state in all the available instances
ALTER PLUGGABLE DATABASE TAF_PDB DISCARD
STATE instances = ('ORCL1','ORCL2');5. Add a service as oracle user
srvctl add service -d ORCL -pdb TAF_PDB -s taf_pdb_svc -r ORCL1 -a ORCL2 -P BASIC -y AUTOMATIC -q TRUE -j LONG -z 180 -w 5 -e SELECT -m BASIC
6. Start the service
srvctl start service -d ORCL -s taf_pdb_svc
7. Check the status of the service
[oracle@cornswjagp001 ~]$ srvctl config service
-d ORCL
Service name: taf_pdb_svc
Server pool:
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: MANUAL
DTP transaction: false
AQ HA notifications: true
Global: false
Commit Outcome: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name: TAF_PDB
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: ORCL1
Available instances: ORCL2
[oracle@cornswjagp001
~]$
8. Check the service values in dba_services
Login to CDB and check the status of service created above.
[oracle@cornswjagp001 ~]$ sqlplus '/as
sysdba'
SQL*Plus: Release 12.1.0.2.0 Production
on Fri Oct 2 14:17:04 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application
Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application
Testing options
SQL>
Session altered.
SQL> column name format a30
select name, service_id from dba_services
where name = 'taf_pdb_svc';
SQL>
NAME SERVICE_ID
---------------------------- ----------
taf_pdb_svc 2
SQL>
SQL> col name format a15
SQL> col failover_method format a11
heading 'METHOD'
SQL> col failover_type format a10
heading 'TYPE'
SQL> col failover_retries format
9999999 heading 'RETRIES'
SQL> col goal format a10
SQL> col clb_goal format a8
SQL> col AQ_HA_NOTIFICATIONS format a5
heading 'AQNOT'
SQL> SQL> SELECT name,
failover_method, failover_type, failover_retries,goal, clb_goal, aq_ha_notifications
2 FROM dba_services
3 WHERE service_id = 2;
NAME METHOD TYPE
RETRIES GOAL CLB_GOAL AQNOT
------------ ----------- ----------
-------- ---------- -------- -----
taf_pdb_svc BASIC
SELECT 180 NONE LONG
YES
9. Create TNS entry in tnsnames.ora file
TAFPDB_SVC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ractest-scan)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taf_pdb_svc)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)In case of RAC environments TNS entry has to be created in all the nodes participating in RAC environment.
10. Test TAF (Transparent Application Failover)
Connect to the database and check the instance of connected session.
[oracle@cornswjagp001 ~]$ sqlplus
system@taf_pdb_svc
SQL*Plus: Release 12.1.0.2.0 Production
on Fri Oct 2 11:34:52 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri Oct 02
2015 11:23:41 +10:00
Connected to:
Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application
Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application
Testing options
SQL> select instance_name from
v$instance;
INSTANCE_NAME
----------------
ORCL1
11. Find pid of pmon of connected instance
The PID of ORCL1
instance is 569 in host racsrv001.
[oracle@racsrv001 admin]$ ps -ef|grep
pmon
oracle 569
1 0 Sep24 ? 00:02:08 ora_pmon_ORCL1
oracle
4706 16879 0 11:35 pts/3 00:00:00 grep pmon
grid
17236 1 0 10:47 ? 00:00:00 mdb_pmon_-MGMTDB
grid
32326 1 0 Sep24 ? 00:01:36 asm_pmon_+ASM1
[oracle@racsrv001 admin]$
Remember the pid could be different in different systems. It does not remain the same.
12. Kill the process of pmon
Kill the pmon process in racsrv001
for instance ORCL1.
13. Check the instance name again in the previous connection.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ORCL2
SQL>
The
connection automatically failed over to second surviving node. Testing successful.
14. The
pmon process starts again
After some time the pmon process of the killed database
instance starts automatically
[oracle@cornswjagp001 admin]$ ps -ef|grep pmon
oracle 4842
1 0 11:35 ? 00:00:00 ora_pmon_ORCL1
oracle 5363
16879 0 11:36 pts/3 00:00:00 grep pmon
grid
17236 1 0 10:47 ? 00:00:00 mdb_pmon_-MGMTDB
grid
32326 1 0 Sep24 ? 00:01:36 asm_pmon_+ASM1
[oracle@cornswjagp001 admin]$
15. Trick in DB 12c for TAF
The trick in Oracle Database 12c is that if you
issue SHUTDOWN ABORT command in SQL*plus CRS detects through the agent that it
was the user that issued the SHUTDOWN ABORT command, so the state changes to
PLANNED_OFFLINE and the label to "Instance Shutdown, Stable". If it
was detected as a real crash, it would say OFFLINE and "Abnormal
Termination,Cleaning" respectively.
If the user wants to test the failover in a
real case they should set the database policy to NORESTART and kill the
instance's PMON:
$ srvctl modify database -d ORCL -policy NORESTART
accept the prompt and then kill PMON
If you set the database policy to NORESTART
the pmon process will not restart automatically. For the pmon to restart
automatically you have to set database policy back to AUTOMATIC.
$ srvctl modify database -d ORCL -policy
AUTOMATIC
Point no 15 was copied and edited from service request raised from Oracle Support.
Hope this helps.
