Disclaimer: I’m not an Oracle DBA, I only play one for work.
Preparing our systems for an Oracle Data Guard configuration, I had to decide how we’d manage the IP resource. How does the primary instance assign the IP address to the server? How does the secondary remove it?
I settled on using the database “AFTER STARTUP”, “BEFORE SHUTDOWN”, and “AFTERÂ DB_ROLE_CHANGE” triggers. Here’s the sqlplus script I settled on:
BEGIN -- Create the database service clients will use to contact the system DBMS_SERVICE.CREATE_SERVICE( service_name=>'&&SERVICE_NAME', network_name=>'&SERVICE_NAME' ); EXCEPTION WHEN DBMS_SERVICE.SERVICE_EXISTS THEN DBMS_OUTPUT.PUT_LINE('Note: service &SERVICE_NAME exists.'); END; / DECLARE does_exist NUMBER; BEGIN -- Remove any existing ICS_ORACLE_IP program and define a new one that calls our shell script. -- Takes one parameter, up or dn. SELECT COUNT(*) INTO does_exist FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'ICS_ORACLE_IP'; IF does_exist = 1 THEN DBMS_SCHEDULER.DROP_PROGRAM('ICS_ORACLE_IP'); END IF; DBMS_SCHEDULER.CREATE_PROGRAM( program_name=>'ICS_ORACLE_IP', program_type=>'EXECUTABLE', program_action=>'/usr/local/bin/oracle-ip.sh', number_of_arguments=>1, comments=>'Manage the oracle IP resource' ); DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT( program_name=>'ICS_ORACLE_IP', argument_position=>1, argument_type=>'VARCHAR2' ); DBMS_SCHEDULER.ENABLE('ICS_ORACLE_IP'); -- Remove any ICS_ORACLE_IPUP job and define a new one. Passes one parameter 'up' to -- the ICS_ORACLE_IP program. SELECT COUNT(*) INTO does_exist FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'ICS_ORACLE_IPUP'; IF does_exist = 1 THEN DBMS_SCHEDULER.DROP_JOB('ICS_ORACLE_IPUP'); END IF; DBMS_SCHEDULER.CREATE_JOB( job_name=>'ICS_ORACLE_IPUP', program_name=>'ICS_ORACLE_IP', auto_drop=>FALSE, comments=>'Bring the oracle IP resource up' ); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name=>'ICS_ORACLE_IPUP', argument_position=>1, argument_value=>'up' ); -- Remove any ICS_ORACLE_IPDN job and define a new one. Passes one parameter 'dn' to -- the ICS_ORACLE_IP program. SELECT COUNT(*) INTO does_exist FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'ICS_ORACLE_IPDN'; IF does_exist = 1 THEN DBMS_SCHEDULER.DROP_JOB('ICS_ORACLE_IPDN'); END IF; DBMS_SCHEDULER.CREATE_JOB( job_name=>'ICS_ORACLE_IPDN', program_name=>'ICS_ORACLE_IP', auto_drop=>FALSE, comments=>'Bring the oracle IP resource down' ); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name=>'ICS_ORACLE_IPDN', argument_position=>1, argument_value=>'dn' ); END; / -- Procedure that checks the database state and enables/disables the service and IP -- address. Logic: -- -- 1. If we are the primary and not shutting down, start the service and IP -- 2. Else (standby, shutdown), stop the service and IP CREATE OR REPLACE PROCEDURE ICS_SERVICE_SELECT (shutdown IN BOOLEAN := FALSE) AS role VARCHAR(30); BEGIN SELECT DATABASE_ROLE INTO role FROM V$DATABASE; IF role = 'PRIMARY' AND NOT shutdown THEN BEGIN DBMS_SERVICE.START_SERVICE('&SERVICE_NAME'); EXCEPTION WHEN DBMS_SERVICE.SERVICE_IN_USE THEN NULL; END; DBMS_SCHEDULER.RUN_JOB('ICS_ORACLE_IPUP'); ELSE BEGIN DBMS_SERVICE.STOP_SERVICE('&SERVICE_NAME'); EXCEPTION WHEN DBMS_SERVICE.SERVICE_NOT_RUNNING THEN NULL; END; DBMS_SCHEDULER.RUN_JOB('ICS_ORACLE_IPDN'); END IF; END ICS_SERVICE_SELECT; / CREATE OR REPLACE TRIGGER ICS_SERVER_STARTUP AFTER STARTUP ON DATABASE BEGIN ICS_SERVICE_SELECT; END; / CREATE OR REPLACE TRIGGER ICS_SERVER_SHUTDOWN BEFORE SHUTDOWN ON DATABASE BEGIN ICS_SERVICE_SELECT(shutdown=>TRUE); END; / CREATE OR REPLACE TRIGGER ICS_SERVER_ROLE_CHANGE AFTER DB_ROLE_CHANGE ON DATABASE BEGIN ICS_SERVICE_SELECT; END; /
Create a script at “/usr/local/bin/oracle-ip.sh” that takes one parameter (“up” or “dn”) and performs the proper IP address management. Remember that it needs to run as the “oracle” user, so use sudo where appropriate.