Oracle Fast-Start Failover - Step by step setup

At this step, as root, we're supposed to :
lvcreate -n lv_orafra_oracle_sid -L 100g data_vg
mkfs -t ext4 /dev/data_vg/lv_orafra_oracle_sid
lvdisplay /dev/mapper/data_vg-lv_orafra_oracle_sid

mkdir /appli/oracle/ORACLE_SID/orafra
mount /dev/mapper/data_vg-lv_orafra_oracle_sid /appli/oracle/ORACLE_SID/orafra
chown -R oracle:oinstall /appli/oracle/ORACLE_SID/orafra
chmod 750 /appli/oracle/ORACLE_SID/orafra/

Then append to /etc/fstab :
/dev/data_vg/lv_orafra_oracle_sid /appli/oracle/ORACLE_SID/orafra ext4 defaults 1 2

NB : 100GB is an estimated size...

==> But this can be done more easily with the "createFs" tools.

don't forget to create the FlashBack filesystem on both servers !

sqlplus '/ as sysdba' << EOSQL
alter system set db_recovery_file_dest_size = 100G;
alter system set db_recovery_file_dest='/appli/oracle/$ORACLE_SID/orafra' scope=both;
alter system set db_flashback_retention_target=480 scope=both;
column type format a11;
show parameter db_recovery
show parameter db_flashback
EOSQL

System altered.

System altered.

System altered.


NAME                          TYPE            VALUE
----------------------------- --------------- ------------------------------
db_recovery_file_dest         string          /appli/oracle/ORACLE_SID/orafra
db_recovery_file_dest_size    big integer     100G


NAME                             TYPE       VALUE
-------------------------------- ---------- --------
db_flashback_retention_target    integer    480

don't forget to declare the FlashBack filesystem on both servers !

1. On primaryServer : echo 'alter database flashback on;' | sqlplus -s / as sysdba

   Database altered.

2. On standbyServer :

   sqlplus -s '/ as sysdba' << EOSQL
   alter database recover managed standby database cancel;
   alter database flashback on;
   alter database recover managed standby database using current logfile disconnect from session;
   EOSQL

   Database altered.
   
   Database altered.
   
   Database altered.

3. Then, on both servers :

   sqlplus -s '/ as sysdba' << EOSQL
   column name format a15;
   column flashback_on format a20;
   select name, flashback_on from v\$database;
   EOSQL

   NAME          FLASHBACK_ON
   ------------- ---------------
   ORACLE_SID    YES

sqlplus -s '/ as sysdba' << EOSQL
CREATE OR REPLACE TRIGGER start_DGservice after startup on database
DECLARE
    mirole VARCHAR(30);
    miservicenames varchar2(2000);
    miservice varchar2(100) := '$ORACLE_SID';
    milen number := 0;
BEGIN
    SELECT DATABASE_ROLE INTO mirole FROM V\$DATABASE;
    IF mirole = 'PRIMARY' THEN
        DBMS_SERVICE.START_SERVICE(miservice);
        select trim(replace(value,miservice)) into miservicenames from v\$parameter where name = 'service_names';
        milen := length(miservicenames);
        if (milen > 0) then
            if (substr(miservicenames,milen,1) = ',') then
                miservicenames:= substr(miservicenames,1,milen-1);
            end if;
        end if;
        execute immediate 'alter system set service_names='''||miservicenames||''' scope=spfile';
    end if;
END;
/
EOSQL

1. sqlplus / as sysdba
2. alter system set service_names='ORACLE_SID' scope=both;

   System altered.

3. exec dbms_service.start_service('ORACLE_SID');

   PL/SQL procedure successfully completed.

   If this returns :

   ERROR at line 1:
   ORA-44305: service ORACLE_SID is running
   ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
   ORA-06512: at "SYS.DBMS_SERVICE", line 454
   ORA-06512: at "SYS.DBMS_SERVICE", line 375
   ORA-06512: at line 1

   Try :
   exec dbms_service.stop_service('ORACLE_SID');

   PL/SQL procedure successfully completed.

   Then retry the "start" command above. This weird behavior is the reason why this whole step doesn't work from the command line with a giant heredoc

4. set linesize 200
   COL "TRIGGER_NAME" FORMAT A20
   COL "TRIGGERING_EVENT" FORMAT A20
   select TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, STATUS, ACTION_TYPE from dba_triggers where TRIGGER_NAME like '%DG%';
   

   TRIGGER_NAME       TRIGGER_TYPE    TRIGGERING_EVENT    STATUS     ACTION_TYPE
   ------------------ --------------- ------------------- ---------- --------------
   START_DGSERVICE    AFTER EVENT     STARTUP             ENABLED    PL/SQL

1. Pick a wallet password from the generated list :
   source /home/afh/livraison/05.00.00/functions.sh; for i in {1..5}; do generatePassword 12 oracle; done
2. Create the wallet directory :
   walletDirectory="$TNS_ADMIN/wallet"; mkdir -m 755 -p "$walletDirectory"; mkstore -wrl "$walletDirectory" -create

   Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
   Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
   
   Enter password: walletPassword
   Enter password again: walletPassword

   TNS_ADMIN's value is /home/oracle/network/admin.
3. Add credentials for sys@ORACLE_SID_PRIMARYDATACENTER :
   mkstore -wrl "$walletDirectory" -createCredential ORACLE_SID_PRIMARYDATACENTER sys 'sysPassword'

   Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
   Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
   
   Enter wallet password: walletPassword
   Create credential oracle.security.client.connect_string1

4. Same job for STANDBYDATACENTER :
   mkstore -wrl "$walletDirectory" -createCredential ORACLE_SID_STANDBYDATACENTER sys 'sysPassword'

   Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
   Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
   
   Enter wallet password: walletPassword
   Create credential oracle.security.client.connect_string2

5. Check :
   mkstore -wrl "$walletDirectory" -listCredential

   Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
   Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
   
   Enter wallet password: walletPassword
   List credential (index: connect_string username)
   2: ORACLE_SID_STANDBYDATACENTER sys
   1: ORACLE_SID_PRIMARYDATACENTER sys

Still as oracle on primaryServer :

1. Does sqlnet.ora already exist ?
   ls /home/oracle/network/admin/sqlnet.ora
   We'll use that information later.

2. cat << EOF >> /home/oracle/network/admin/sqlnet.ora
   WALLET_LOCATION =
       (SOURCE =
           (METHOD = FILE)
           (METHOD_DATA =
               (DIRECTORY = $walletDirectory)
           )
       )
   
   DIAG_ADR_ENABLED = OFF
   SQLNET.WALLET_OVERRIDE = TRUE
   EOF

Let's check we can make a passwordless connection to the broker using the wallet, as oracle on primaryServer

1. dgmgrl
2. connect /@ORACLE_SID_PRIMARYDATACENTER

   Connected.

3. connect /@ORACLE_SID_STANDBYDATACENTER

   Connected.

4. If both connections succeeded, undo/comment the changes made earlier to sqlnet.ora. If the file has been created at that time, it can safely be purged : > /home/oracle/network/admin/sqlnet.ora

Technically speaking, you should disconnect from the 1^st connection, leave DGMGRL, then try the 2^nd connection. Because after the 1^st connection succeeded, you're connected to ORACLE_SID_PRIMARYDATACENTER. No big deal anyway, since this is where we're testing from.