Automating Client Failover in a Data Guard Configuration

We target is preventing clients from connecting
to the wrong Database when failover occurs.

we use database services to prevent clients from connecting to the wrong database in the Data Guard configuration
we can arrange client tnsnames.ora so Clients connect to database services instead of database instances
Database services register with listeners
we creating services for the data guard configuration databases
DBMS_SERVICE.CREATE_SERVICE( -
SERVICE_NAME => 'PROD', -
NETWORK_NAME => 'PROD', -
FAILOVER_METHOD => 'BASIC', -
FAILOVER_TYPE => 'SELECT', -
FAILOVER_RETRIES => 120, -
FAILOVER_DELAY => 1);

DBMS_SERVICE.CREATE_SERVICE( -
SERVICE_NAME => 'STBY', -
NETWORK_NAME => 'STBY');

DBMS_SERVICE.CREATE_SERVICE( -
SERVICE_NAME => 'LSBY', -
NETWORK_NAME => 'LSBY');

we use Use a database event trigger to ensure that clients connect to a database in the Data Guard configuration that is in the correct state and role

CREATE TRIGGER MANAGE_SERVICES AFTER STARTUP ON DATABASE
DECLARE
ROLE VARCHAR(30);
OMODE VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO ROLE FROM V$DATABASE;
SELECT OPEN_MODE INTO OMODE FROM V$DATABASE;
IF ROLE = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE ('PROD');
ELSIF ROLE = 'PHYSICAL STANDBY' THEN
IF OMODE = 'READ ONLY' THEN
DBMS_SERVICE.START_SERVICE ('STBY');
END IF;
ELSIF ROLE = 'LOGICAL STANDBY' THEN
DBMS_SERVICE.START_SERVICE ('LSBY');
END IF;
END;


Use the trigger to start database services
PROD: Primary database
STBY: Physical standby database opened in READ ONLY mode
LSBY: Logical standby database


configure client tnsnames.ora
PROD = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = PROD)))

STBY = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = STBY)))


LSBY = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = LSBY)))

Relocating database services to the new primary database as part of a failover operation

Recover OCR Using Physical Backups

The OCR content is critical to Oracle Clusterware.
OCR is automatically backed up physically:
Every four hours: CRS keeps the last three copies.
At the end of every day: CRS keeps the last two copies.
At the end of every week: CRS keeps the last two copies

$ cd $ORACLE_BASE/Crs/cdata/test
$ ls -lt

check Ocr automatic backup location
ocrconfig -showbackup

stop oracleclusterware on all nodes
crsctl stop crs

restore the physical ocr backup
ocrconfig -restore /cdata/day.ocr

restart oracle clusterware on all nodes
crsctl start crs

check ocr integrity
cluvfy comp ocr -n all

Oracle reserved Words (oracle tarafından ayrılmış kelimeler)

IT SELECT ALL EXISTS NOCOMPRESS SESSION
ALTER FILE NOT SET
AND FLOAT NOTFOUND SHARE
ANY FOR NOWAIT SIZE
ARRAYLEN FROM NULL SMALLINT
AS GRANT NUMBER SQLBUF
ASC GROUP OF SUCCESSFUL
AUDIT HAVING OFFLINE SYNONYM
BETWEEN IDENTIFIED ON SYSDATE
BY IMMEDIATE ONLINE TABLE
CHAR IN OPTION THEN
CHECK INCREMENT OR TO
CLUSTER INDEX ORDER TRIGGER
COLUMN INITIAL PCTFREE UID
COMMENT INSERT PRIOR UNION
COMPRESS INTEGER PRIVILEGES UNIQUE
CONNECT INTERSECT PUBLIC UPDATE
CREATE INTO RAW USER
CURRENT IS RENAME VALIDATE
DATE LEVEL RESOURCE VALUES
DECIMAL LIKE REVOKE VARCHAR
DEFAULT LOCK ROW VARCHAR2
DELETE LONG ROWID VIEW
DESC MAXEXTENTS ROWLABEL WHENEVER
DISTINCT MINUS ROWNUM WHERE
DROP MODE ROWS WITH

The following PL/SQL keywords may require special treatment when used in embedded SQL statements.
ABORT BETWEEN CRASH DIGITS
ACCEPT BINARY_INTEGER CREATE DISPOSE
ACCESS BODY CURRENT DISTINCT
ADD BOOLEAN CURRVAL DO
ALL BY CURSOR DROP
ALTER CASE DATABASE ELSE
AND CHAR DATA_BASE ELSIF
ANY CHAR_BASE DATE END
ARRAY CHECK DBA ENTRY
ARRAYLEN CLOSE DEBUGOFF EXCEPTION
AS CLUSTER DEBUGON EXCEPTION_INIT
ASC CLUSTERS DECLARE EXISTS
ASSERT COLAUTH DECIMAL EXIT
ASSIGN COLUMNS DEFAULT FALSE
AT COMMIT DEFINITION FETCH
AUTHORIZATION COMPRESS DELAY FLOAT
AVG CONNECT DELETE FOR
BASE_TABLE CONSTANT DELTA FORM
BEGIN COUNT DESC FROM
FUNCTION NEW RELEASE SUM
GENERIC NEXTVAL REMR TABAUTH
GOTO NOCOMPRESS RENAME TABLE
GRANT NOT RESOURCE TABLES
GROUP NULL RETURN TASK
HAVING NUMBER REVERSE TERMINATE
IDENTIFIED NUMBER_BASE REVOKE THEN
IF OF ROLLBACK TO
IN ON ROWID TRUE
INDEX OPEN ROWLABEL TYPE
INDEXES OPTION ROWNUM UNION
INDICATOR OR ROWTYPE UNIQUE
INSERT ORDER RUN UPDATE
INTEGER OTHERS SAVEPOINT USE
INTERSECT OUT SCHEMA VALUES
INTO PACKAGE SELECT VARCHAR
IS PARTITION SEPARATE VARCHAR2
LEVEL PCTFREE SET VARIANCE
LIKE POSITIVE SIZE VIEW
LIMITED PRAGMA SMALLINT VIEWS
LOOP PRIOR SPACE WHEN
MAX PRIVATE SQL WHERE
MIN PROCEDURE SQLCODE WHILE
MINUS PUBLIC SQLERRM WITH
MLSLABEL RAISE START WORK
MOD RANGE STATEMENT XOR
MODE REAL STDDEV
NATURAL RECORD SUBTYPE