Creating Far Sync instance step by step

These are the steps of creating Far Sync instance in the command line:
  • Create 12.1 primary db

  • Create 12.1 physical standby

  • Create regular standby DG broker configuration

  • modify network file (on primary and standby) and add entries for the far_sync instance, add to tnsnames.ora on Primary hosts and standby hosts:

  • FarSyncInst =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = FarSyncHost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = FarSyncInst)
          (UR=A)
        )
      )
        Note: (UR=A) is needed to connect to a BLOCKED unmounted database instance.
  • Copy tnsnames.ora to new farsync server – ensure all hosts mentioned in tnsnames are written also in /etc/hosts

  • Add to listener.ora in far sync server

  • LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0 )(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    SID_LIST_LISTENER=
            (SID_LIST=
                    (SID_DESC=
                            (SID_NAME=FarSyncInst)
                            (ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1)
                            (GLOBAL_DBNAME=FarSyncInst)
                    )
                    (SID_DESC=
                            (SID_NAME=FarSyncInst)
                            (ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1)
                            (GLOBAL_DBNAME=FarSyncInst_DGMGRL)
                    )
                    (SID_DESC=
                            (SID_NAME=FarSyncInst)
                            (ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1)
                            (GLOBAL_DBNAME=FarSyncInst_DGB)
                    )
            )
    ADR_BASE_LISTENER = /oracle
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
    VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET
  • Install on Far Sync host 12.1 database software, On primary database:

  • SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS  '/u01/app/oracle/product/12.1.0.2/db/dbs/farsync_FarSyncInst_control.ctl';
    SQL> create pfile='/u01/app/oracle/product/12.1.0.2/db/dbs/initFarSyncInst.ora' from spfile;
  • edit initFarSyncInst.ora, Add the following lines:

  • *.db_unique_name=FarSyncInst
    *.cpu_count=1
  • Change the following lines:

  • *.log_archive_config='DG_CONFIG=(PrimaryDB,StandbyDB,FarSyncInst)'
    *.audit_file_dest='/oracle/admin/FarSyncInst/adump'
    *.fal_server='PrimaryDB','StandbyDB'
    *.log_archive_dest_1 ='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
    *.control_files='/oracle/product/12.1.0.2/dbhome_1/dbs/farsync_FarSyncInst_control.ctl'
    *.undo_tablespace='UNDOTBS1'
    *.sga_target=384m
    *.db_create_file_dest='+DATA'
    *.db_recovery_file_dest='+DATA'
    *.db_recovery_file_dest_size=50g
    *.db_file_name_convert='+DATA/PrimaryDB/', '+DATA/FarSyncInst/'
    *.log_file_name_convert='+DATA/PrimaryDB/', '+DATA/FarSyncInst/'
    *.dg_broker_config_file1='+DATA/FarSyncInst/dr1FarSyncInst.dat'
    *.dg_broker_config_file2='+DATA/FarSyncInst/dr2FarSyncInst.dat'
    *.diagnostic_dest='/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=FarSyncInstXDB)'
  • Delete the following lines:

  • .__*
    .instance_number
    .log_archive_format
    .log_archive_trace
    .thread
    .undo_tablespace
    log_archive_max_processes
    memory_target
    LOG_ARCHIVE_DEST_2
    LOG_ARCHIVE_DEST_3
    cluster_database
    processes
    open_cursors
    memory_target
    remote_listener
    db_writer_processes
    gcs_server_processes
    sessions?
    resource_manager_plan?
  • On Primary - Copy pfile, created controlfile file  to far sync server (with the instance name).

  • cd $ORACLE_HOME/dbs
    scp initFarSyncInst.ora farsync_FarSyncInst_control.ctl FarSyncHost:/oracle/product/12.1.0.2/dbhome_1/dbs/
  • On Far Sync Server - Create required directories

  • mkdir -p $ORACLE_HOME/rdbms/audit
    mkdir -p $ORACLE_BASE/admin/FarSyncInst/adump
  • create grid resource

  • srvctl add database -d FarSyncInst  -o /oracle/product/12.1.0.2/dbhome_1 -n FarSyncInst  -i FarSyncInst  -y automatic -r FAR_SYNC
  • copy password file from primary

  • ASM> pwget --dbuniquename PrimaryDB
    +DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271
    ASM> pwcopy +DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271 /tmp/
    copying +DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271 -> /tmp/pwdPrimaryDB.276.913723271
    $ scp /tmp/pwdPrimaryDB.276.913723271 FarSyncHost: /tmp
  • Get password file back to ASM on FarSyncHost

  • ASM> mkdir +DATA/FarSyncInst
    ASM> pwcopy --dbuniquename FarSyncInst /tmp/pwdPrimaryDB.276.913723271 +DATA/FarSyncInst/pwdFarSyncInst
    $ export ORACLE_SID=FarSyncInst
    SQL> startup nomount
    SQL> create spfile from pfile='/oracle/product/12.1.0.2/dbhome_1/dbs/initFarSyncInst.ora';
    SQL> shutdown abort
  • Create .bashrc on the far sync server and define Oracle environment parameters.

  • Recommended: move the redo + archives to the correct location on far sync server

  • Start far sync standby database

  • SQL> startup nomount
  • Restore controlfile from to ASM.

  • $ rman nocatalog
    RMAN> connect target;
    RMAN> restore controlfile to '+DATA' from '/oracle/product/12.1.0.2/dbhome_1/dbs/farsync_FarSyncInst_control.ctl';
  • Find the ASM full name of the controlfile.

  • $ asmcmd
    ASMCMD [+] > cd DATA
    ASMCMD [+DATA] > find --type controlfile . *
    +DATA/FarSyncInst/CONTROLFILE/current.622.902053967
  • Update spfile with the new controlfile found in the last step.

  • SQL> alter system set control_files='+DATA/FarSyncInst/CONTROLFILE/current.622.902053967' scope=spfile;
    SQL> create pfile='/oracle/product/12.1.0.2/dbhome_1/dbs/initFarSyncInst.ora' from spfile;
    SQL> create spfile='+DATA/FarSyncInst/spfileFarSyncInst.ora' from pfile='/oracle/product/12.1.0.2/dbhome_1/dbs/initFarSyncInst.ora';
  • Create init.ora file calling to the ASM spfile

  • $ mv initFarSyncInst.ora _initFarSyncInst.ora
    $ mv spfileFarSyncInst.ora _spfileFarSyncInst.ora
    $ vi initFarSyncInst.ora
    SPFILE='+DATA/FarSyncInst/spfileFarSyncInst.ora'
  • Restart instance to load with the new spfile

  • SQL> shutdown abort
    SQL> startup mount
  • Setup listenerand far sync instance are starting automatically using Oracle Restart (Grid Infrastructure)

  • srvctl add listener -l LISTENER -o /oracle/12.1.0/grid
    srvctl start listener
    $ srvctl add database -d FarSyncInst -o /oracle/product/12.1.0.2/dbhome_1 -n FarSyncInst -i FarSyncInst -y automatic -r FAR_SYNC
    $ srvctl start database -d FarSyncInst
  • Create standby log on the far sync (if it was not already created on the primary):

  • set serveroutput on;
    declare
      log_num number;
      log_size number;
      log_num_standby number;
    begin
    for j in  (select inst_id i from gv$instance) loop
             select count(*) into log_num from gv$log where inst_id=j.i ;
             select count(*) into log_num_standby from gv$standby_log where inst_id=j.i ;
             select max(BYTES) into log_size from gv$log where inst_id=j.i  ;
            for i in 1..(log_num+1-log_num_standby) loop
            --execute immediate 'ALTER DATABASE ADD STANDBY LOGFILE THREAD '||j.i ||' SIZE '||log_size;
            dbms_output.put_line('ALTER DATABASE ADD STANDBY LOGFILE THREAD '||j.i ||' SIZE '||log_size||';');
            end loop;
    end loop;
    end;
    /
  • Check if you need to create more threads if the primary is in RAC

  • select GROUP#,THREAD# from v$standby_log;
  • If needed add more standby redo logs at the same size to the other threads manually as much as it needed

  • ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 52428800
  • Verify database role is Far Sync

  • SQL> select database_role from v$database;
    DATABASE_ROLE
    ----------------
    FAR SYNC
    SQL> select DB_UNIQUE_NAME,DEST_ROLE from  V$DATAGUARD_CONFIG;
    DB_UNIQUE_NAME                 DEST_ROLE
    ------------------------------ -----------------
    FarSyncInst                         FAR SYNC INSTANCE
    PrimaryDB                           UNKNOWN
    StandbyDB                           UNKNOWN
  • On primary database:

  • SQL> ALTER SYSTEM Set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PrimaryDB,StandbyDB,FarSyncInst)';
    SQL> Alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
    SQL> ALTER SYSTEM Set  LOG_ARCHIVE_DEST_2=' ';
    SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;
  • Add to data guard broker setup

  • $ dgmgrl /
    DGMGRL> ADD FAR_SYNC FarSyncInst AS CONNECT IDENTIFIER IS FarSyncInst;
    DGMGRL> EDIT FAR_SYNC FarSyncInst SET PROPERTY RedoRoutes = '(PrimaryDB : StandbyDB ASYNC)';
    DGMGRL> EDIT DATABASE StandbyDB SET PROPERTY LogXptMode = 'SYNC';
    DGMGRL> EDIT DATABASE PrimaryDB SET PROPERTY MaxFailure = 0;
    DGMGRL> EDIT FAR_SYNC FarSyncInst SET PROPERTY MaxFailure = 1;
    DGMGRL> EDIT DATABASE StandbyDB SET PROPERTY MaxFailure = 0;
    DGMGRL> EDIT DATABASE StandbyDB SET PROPERTY RedoRoutes = '(LOCAL : PrimaryDB ASYNC)';
  • Oracle 12.2 Priority

  • DGMGRL> ENABLE CONFIGURATION;
    DGMGRL> EDIT DATABASE PrimaryDB SET PROPERTY RedoRoutes = '(LOCAL : ( FarSyncInst SYNC PRIORITY=1, StandbyDB ASYNC PRIORITY=2 ) )';
  • Oracle 12.1 Alternate

  • DGMGRL> EDIT DATABASE PrimaryDB SET PROPERTY RedoRoutes = '(LOCAL : FarSyncInst SYNC ALT =(StandbyDB ASYNC FALLBACK))';
  • Change the mode Maximum Availability

  • DGMGRL> DISABLE CONFIGURATION;
    DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
    DGMGRL> ENABLE CONFIGURATION;

    Comments

    Popular posts from this blog

    Fixing & Registering ORACLE_HOMES in Central Inventory

    Oracle 18c - New Features for Active Data Guard

    List RMAN backups size