Setting Up FSFO on Oracle 12c

Last updated
Save as PDF

The following steps are intended as a reference, not a comprehensive guide.

Before you begin, you should already have experience configuring FSFO and Data Guard.

1. Modify the hosts file

Server

Primary

Standby

Edit the hosts file on both primary and standby servers so that they each recognize each other.

su - root
vi /etc/hosts

Primary Server Example

127.0.0.1            localhost.localdomain            localhost
10.1.19.194            <fully qualified hostname>          <host alias>    amcom1
10.7.2.50              <fully qualified hostname>          <host alias>    amcom2

Standby Server Example

127.0.0.1            localhost.localdomain                    localhost
10.7.2.50            <fully qualified hostname>          <host alias>    amcom2
10.1.19.194          <fully qualified hostname>          <host alias>    amcom1

2. Modify listener and tnsnames

Server

Primary

Standby

The update_fsfo_files.sh script will do this on your behalf.

su - root
cd /opt/amcom/staging
sh ./update_fsfo_files.sh

Then restart the listener.

su - oracle
. oraenv
    #respond with amcom
lsnrctl stop
lsnrctl start
lsnrctl stat

3. Prepare the primary database

Server

Primary

Launch SQL*Plus and log in as sysdba.

sqlplus #log in as sysdba

Run all ALTER SYSTEM statements to configure DataGuard and set MAXIMUM AVAILABILITY

shutdown immediate;
startup mount;
alter database set standby database to maximize availability;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Confirm that flashback is on.

SELECT flashback_on FROM v$database;

If not YES, then enable flashback:

ALTER DATABASE FLASHBACK ON;

Continue configuration:

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=amcom2 valid_for=(online_logfiles,primary_role)  db_unique_name=amcom2 LGWR SYNC AFFIRM NET_TIMEOUT=60';
alter system set DB_UNIQUE_NAME=amcom1 scope=spfile;
alter system set FAL_SERVER=amcom2;
alter system set FAL_CLIENT=amcom1;
alter system set LOG_ARCHIVE_CONFIG='dg_config=(amcom1, amcom2)';
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
shutdown immediate;
startup;
alter database force logging;

Create online standby redo logfiles on the primary. There should be one more standby redo logfile than the number of online redo log file groups on the primary database.

alter database add standby logfile group 4 size 200M;
alter database add standby logfile group 5 size 200M;
alter database add standby logfile group 6 size 200M;
alter database add standby logfile group 7 size 200M;
EXIT;

Move password file over.

scp $ORACLE_HOME/dbs/orapwamcom oracle@amcom2:$ORACLE_HOME/dbs
# when prompted, enter your Oracle user password.

4. Prepare the standby database

Server

Standby

Launch SQL*Plus and log in as sysdba.

su - oracle
sqlplus #log in as sysdba

Run all ALTER SYSTEM statements to configure Data Guard and set MAXIMUM AVAILABILITY

shutdown abort;
startup mount;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Confirm that flashback is on.

SELECT flashback_on FROM v$database;

If not then enable flashback:

ALTER DATABASE FLASHBACK ON;

Continue configuration:

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=amcom1 valid_for=(online_logfiles,primary_role)  db_unique_name=amcom1 LGWR SYNC AFFIRM NET_TIMEOUT=60';
alter system set DB_UNIQUE_NAME=amcom2 scope=spfile;
alter system set FAL_SERVER=amcom1;
alter system set FAL_CLIENT=amcom2;
alter system set LOG_ARCHIVE_CONFIG='dg_config=(amcom1, amcom2)';
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
shutdown immediate;
startup nomount;
alter system set db_name=amcom1 scope=spfile;
shutdown normal;
startup nomount;
show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      AMCOM1
exit;

Remove all current data files.

cd /u02/oradata
rm -R amcom
cd /u02/oradata/AMCOM2/datafile
rm -rf *.*
cd /u03/oradata
rm -R amcom
cd /u03/oradata/AMCOM2/onlinelog
rm -rf *.*
cd /u03/oradata/AMCOM2/controlfile
rm -rf *.*
cd /u04/oradata
rm -R amcom
cd /u04/oradata/AMCOM2/onlinelog
rm -rf *.*
cd /u04/oradata/AMCOM2/controlfile
rm -rf *.*
cd /u06/app/oracle/flash_recovery_area/AMCOM2/flashback
rm -rf *.*
cd /u06/app/oracle/flash_recovery_area/AMCOM2/archivelog
rm -R 20*

5. Duplicate standby

Server

Primary

$ORACLE_HOME/bin/rman target sys/Oracle_Admin_04@AMCOM1 auxiliary sys/Oracle_Admin_04@AMCOM2
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
exit;

6. Start Data Guard process

Server

Standby

Launch SQL*Plus and log in as sysdba.

sqlplus #log in as sysdba

 recover managed standby database using current logfile disconnect from session;
 exit

Verify that systems are in sync after recovery command by tailing the alert log.

 tail -f /u01/app/oracle/diag/rdbms/amcom2/amcom/trace/alert_amcom.log

After reviewing the alert log and waiting a sufficient amount of time for lots to be applied, launch SQL*Plus again and log in as sysdba.

If the system is not actively changing data, no new log files might come across. In this case, continue to the next step.

If the system is actively changing data, wait for a pause in logging before continuing.

sqlplus #log in as sysdba

alter database recover managed standby database cancel;
shutdown immediate
startup mount;
ALTER DATABASE FLASHBACK ON;
exit;

7. Start Data Guard broker

Server

Primary

Standby

Launch SQL*Plus and log in as sysdba.

sqlplus #log in as sysdba

select name, db_unique_name from v$database;

Verify the output from the previous command returns the following:

Primary Server

NAME            DB_UNIQUE_NAME
---------       ------------------------------
AMCOM1          AMCOM1

Standby Server

NAME            DB_UNIQUE_NAME
---------       ------------------------------
AMCOM1          AMCOM2

Then run the following:

alter system set dg_broker_start=TRUE scope=both;
exit;

8. Create broker configuration

Server

Primary

dgmgrl
connect sys/Oracle_Admin_04@amcom1

CREATE CONFIGURATION AMCOM_DG AS
PRIMARY DATABASE IS AMCOM1
CONNECT IDENTIFIER IS AMCOM1;

ADD DATABASE AMCOM2 AS
CONNECT IDENTIFIER IS AMCOM2
MAINTAINED AS PHYSICAL;

If the above statements generate the following error:

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Complete the steps outlined under Error_ORA-16698 below.

dgmgrl
connect sys/Oracle_Admin_04@amcom1

EDIT DATABASE AMCOM1 SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE AMCOM2 SET PROPERTY 'LogXptMode'='SYNC';

ENABLE CONFIGURATION;

edit database amcom1 set property FastStartFailoverTarget = amcom2;
edit database amcom2 set property FastStartFailoverTarget = amcom1;
EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 30;

EXIT

9. Create fan service

Server

Primary

Launch SQL*Plus and log in as sysdba.

sqlplus #log in as sysdba

 BEGIN
     DBMS_SERVICE.CREATE_SERVICE (
         service_name => 'amcomfan',   
         network_name => 'amcomfan',
         aq_ha_notifications => true,
         failover_method => 'BASIC',
         failover_type => 'SELECT',
         failover_retries => 180,
         failover_delay => 1);
END;
/


CREATE OR REPLACE TRIGGER manage_OCIservice
    after startup on database
    DECLARE
        role VARCHAR(30);
        service_in_use       EXCEPTION;
        service_not_running  EXCEPTION;
        PRAGMA EXCEPTION_INIT(service_in_use, -44305);
        PRAGMA EXCEPTION_INIT(service_not_running, -44311);
    BEGIN
        SELECT DATABASE_ROLE INTO role
            FROM V$DATABASE;
        IF role = 'PRIMARY'
            THEN
                BEGIN
                    DBMS_SERVICE.START_SERVICE('amcomfan');
                EXCEPTION
                    WHEN service_in_use then
                        NULL;
                END;
            
            ELSE
                BEGIN
                    DBMS_SERVICE.STOP_SERVICE('amcomfan');
                EXCEPTION
                    WHEN service_not_running then
                        NULL;
                END;
        
        END IF;
    END;
/

CREATE OR REPLACE TRIGGER manage_service
    after DB_ROLE_CHANGE on database
    DECLARE
        role VARCHAR(30);
        service_in_use       EXCEPTION;
        service_not_running  EXCEPTION;
        PRAGMA EXCEPTION_INIT(service_in_use, -44305);
        PRAGMA EXCEPTION_INIT(service_not_running, -44311);
    BEGIN
        SELECT DATABASE_ROLE INTO role
            FROM V$DATABASE;
        IF role = 'PRIMARY'
            THEN
                BEGIN
                    DBMS_SERVICE.START_SERVICE('amcomfan');
                EXCEPTION
                    WHEN service_in_use then
                        NULL;
                END;
            
            ELSE
                BEGIN
                    DBMS_SERVICE.STOP_SERVICE('amcomfan');
                EXCEPTION
                    WHEN service_not_running then
                        NULL;
                END;
        
        END IF;
    END;        
/

exec DBMS_SERVICE.START_SERVICE('amcomfan');

exit;

10. Enable FSFO

Server

Primary

dgmgrl
connect sys/Oracle_Admin_04@amcom1

enable fast_start failover;
exit;

11. Modify tnsnames and jdbc files

Server

Application

su - root
cd /opt/amcom/staging
sh ./update_fsfo_files.sh
sh ./config_infra1_fsfo.sh
cd /home/amcom/webutil
sh ./ds_fsfo

12. Set up observers

Server

Application

Do not perform this step if the customer does not want failover functionality.

On each application server that will run the observer (maximum of 3 servers), complete the following steps:

cd /home/amcom/FSFO_observer_amcom/
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/client_1
./install.sh
systemctl start FSFO_observer_amcom

13. Verify observers

Server

Primary

Returning to the primary server, check that all observers are present and connected (only necessary if you completed the previous step):

dgmgrl
connect sys/Oracle_Admin_04@amcom1
show observer

Output should show one master observer and zero or more backup observers (depending on how many application servers are set up to have observers).

14. Add email notifications

Server

Primary

Standby

Launch SQL*Plus and log in as sysdba.

sqlplus #log in as sysdba

 ALTER SYSTEM SET SMTP_OUT_SERVER='localhost';

If you do not know the mail server and mail is setup to go out of the OS, you can set the parameter to localhost and this will allow it to be sent from the local OS.

Server

Primary

On the primary server only, provide an email address to receive alerts.

In the lines below, replace <admin email address> with a valid email address, and replace <Spok DB server> with the appropriate IP address:

CREATE OR REPLACE TRIGGER FSF_SWITCHOVER_ALERT
  after DB_ROLE_CHANGE on database
DECLARE
  role varchar(30);
  unique_name varchar(30);
BEGIN
  SELECT DB_UNIQUE_NAME INTO unique_name
    FROM V$DATABASE;
  SELECT DATABASE_ROLE INTO role
    FROM V$DATABASE;
 
  IF role = 'PRIMARY'
  
  -- Replace <admin email address> and <Spok DB server>
    
    THEN UTL_MAIL.SEND (sender => 'oracle',recipients => '<admin email address>',subject => 'Database has switched to primary role',message => unique_name||' -'||' FSF - database role change to PRIMARY on <Spok DB server> or <Spok DB server>');
    
    ELSE UTL_MAIL.SEND (sender => 'oracle',recipients => '<admin email address>',subject => 'Database has switched to primary role',message => unique_name||' -'||' FSF - database role change to STANDBY on <Spok DB server> or <Spok DB server>');
  
  END IF;

END;
/

15. Update the amcomenv site parameter

Server

Primary

Standby

su
vi /home/amcom/bin/amcomenv

Check with customer and add their DL to the failover alert trigger if customer provides a DL list to alert

Troubleshooting

Error ORA-16698

Complete the following steps:

On the primary server:

sqlplus #log in as sysdba

select value from v$parameter where name = 'log_archive_dest_2';

-- store the value for this parameter:
primary:  SERVICE=amcom2 valid_for=(online_logfiles,primary_role)
db_unique_name=amcom2 LGWR SYNC AFFIRM NET_TIMEOUT=60

On the standby server:

sqlplus #log in as sysdba

select value from v$parameter where name = 'log_archive_dest_2';

-- store the value for this parameter:
standby:  SERVICE=amcom1 valid_for=(online_logfiles,primary_role)
db_unique_name=amcom1 LGWR SYNC AFFIRM NET_TIMEOUT=60

On the primary server:

dgmgrl
connect sys/Oracle_Admin_04@amcom1
remove configuration;
exit

sqlplus #log in as sysdba

alter system set log_archive_dest_2='' scope=both sid='*';

On the standby server:

sqlplus #log in as sysdba

alter system set log_archive_dest_2='' scope=both sid='*';

On the primary server:

alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';

On the standby server:

alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';

On the primary server:

dgmgrl
connect sys/Oracle_Admin_04@amcom1

CREATE CONFIGURATION AMCOM_DG AS
PRIMARY DATABASE IS AMCOM1
CONNECT IDENTIFIER IS AMCOM1;

ADD DATABASE AMCOM2 AS
CONNECT IDENTIFIER IS AMCOM2
MAINTAINED AS PHYSICAL;

exit;

sqlplus #log in as sysdba

alter system set log_archive_dest_2='SERVICE=amcom2 
valid_for=(online_logfiles,primary_role)  db_unique_name=amcom2 LGWR SYNC AFFIRM 
NET_TIMEOUT=60';
exit;

On the standby server:

sqlplus #log in as sysdba

alter system set log_archive_dest_2='SERVICE=amcom1 
valid_for=(online_logfiles,primary_role) db_unique_name=amcom1 LGWR SYNC AFFIRM 
NET_TIMEOUT=60';
exit;

Return to Create broker configuration and resume steps.

Email Notifications Failing

Try changing the UTL_MAIL.SEND sender parameter to include the domain name.

For example:

UTL_MAIL.SEND (sender => 'oracle@spok.com',