Standby database configuration - 11G/12c/19c

* Archival should be Enabled to start the below configurations

 
In this Document Primary DB Unique Name is 'KANDY' and Standby DB Unique Name is 'COLOMBO'

- Execute below command on the Primary DB

alter database force logging; 

 
select FORCE_LOGGING from v$database; ( Result should be 'YES' )

alter system set log_archive_config='dg_config=(kandy,colombo)';

( kandy and colombo are the DB_UNIQUE_NAME's of the primary and standby databases)


alter system set log_archive_dest_2='service=TNS_COL LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=colombo' scope=both;

( service is tns alies in the standby db db_unique_name is standby db_unique_name )

 
alter system set log_archive_dest_state_2=enable;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set fal_server=TNS_COL; 

( fal_server is the TNS allies of the Standby DB)

 
alter system set db_file_name_convert='colombo','kandy' scope=spfile;

alter system set log_file_name_convert='colombo','kandy' scope=spfile;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

-- Restart the PRIMARY DB
-- ADD TNS Entry for both DR and LIVE server accordingly


TNS_KANDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = KANDY)
    )
  )

TNS_COL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.12)(PORT = 1521))
    )
    (CONNECT_DATA =
   (SERVICE_NAME = COLOMBO)
    )
  )



-- Take a rman backup of DB and Controlfile

RMAN --> 

backup database format '/vol0/RMAN/%U';

backup archivelog all format '/vol0/RMAN/%U';

SQL  --> 

alter database create standby controlfile as 

'/vol0/RMAN/stdcontrol.ctl';

-- Copy backups to standby db Server

scp /vol0/RMAN/* 172.16.10.12:/vol0/RMAN/

-- Copy the pwd file to standby db Server

scp /vol0/app/oracle/product/11.2.0.4/db_1/dbs/orapwKANDY 172.16.10.12:/vol0/app/oracle/product/11.2.0.4/db_1/dbs/orapwCOLOMBO


-- GO TO Standby DB Server

export ORACLE_SID=COLOMBO
-- Create a pfile and add below Entries.
-----------------------
*.compatible='11.2.0.4.0'
*.db_file_name_convert='KANDY','COLOMBO'  
*.log_file_name_convert='KANDY','COLOMBO'
*.db_name='KANDY'
*.db_unique_name='COLOMBO'
*.fal_server='TNS_COL'
*.log_archive_config='dg_config=(kandy,colombo)' 
*.log_archive_dest_2='service=TNS_KANDY LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=kandy'
*.log_archive_dest_state_2='ENABLE'
*.standby_file_management='AUTO'


-------------------------------

-- create spfile from the pfile
-- Startup nomount

RMAN --> 

Restore controlfile from '/vol0/RMAN/stdcontrol.ctl';
RMAN --> alter database mount;
RMAN --> restore database;

-- Set the size of the standby logfiles as same as the redo logfiles
-- Add standby logfile groups as much as the redo logfile groups

alter database add standby logfile thread 1 group 4 ('/vol0/oradata/COLOMBO/stnadby_redo04.log') size 50m;
alter database add standby logfile thread 1 group 5 ('/vol0/oradata/COLOMBO/stnadby_redo05.log') size 50m;
alter database add standby logfile thread 1 group 6 ('/vol0/oradata/COLOMBO/stnadby_redo06.log') size 50m;

select member from v$logfile where type ='STANDBY';
select member from v$logfile;

-- log into primary DB and create standby redo log files

alter database add standby logfile thread 1 group 4 ('/vol0/oradata/KANDY/stnadby_redo04.log') size 50m;
alter database add standby logfile thread 1 group 5 ('/vol0/oradata/KANDY/stnadby_redo05.log') size 50m;
alter database add standby logfile thread 1 group 6 ('/vol0/oradata/KANDY/stnadby_redo06.log') size 50m;

alter system set log_archive_dest_state_2=enable; 

select member from v$logfile where type ='STANDBY';
select member from v$logfile;

-- Issue the bellow command from standby DB

alter database recover managed standby database disconnect from session;

-- if you need to cancel applying, execute below command from standby DB
-- alter database recover managed standby database cancel ;

-- From the Primary db Execute below

alter system switch logfile;
select max(sequence#) from V$ARCHIVED_LOG group by thread#;
select max(sequence#) from V$ARCHIVED_LOG where applied='YES' group by thread#;