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.