Maximum Availability Architecture

PREREQUISITES :

1. The primary RAC database in archivelog mode.
2. Grid Infrastructure and Oracle software were installed on standby node.
3. spfile on shared filesystem.
4. Create dump directories adump etc on standby node if needed.

1. Place the primary database in force logging.
SQL> alter database force logging;

PREPARE PRIMARY DATABASE


Configure the primary database initialization parameters to support both the primary and standby roles.

SQL> alter system set log_archive_config='dg_config=(ORCL,ORCLS)' scope=both ;
SQL> alter system set log_archive_dest_1='LOCATION=/u05/ORCL/arch MANDATORY REOPEN=30 VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=ORCL' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=ORCLS LGWR ASYNC reopen=15 max_failure=20 net_timeout=15 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLS' scope=both;

SQL> alter system set log_archive_dest_state_1='enable' scope=both;
SQL> alter system set log_archive_dest_state_2=defer scope=both;
SQL> alter system set log_archive_max_processes=10 scope=both;

SQL> alter system set fal_server='ORCLS' scope=both;
SQL> alter system set fal_client='ORCL1' scope=both sid='ORCL1'; --- Deprecated in 11gR2
SQL> alter system set fal_client='ORCL2' scope=both sid='ORCL2'; --- Deprecated in 11gR2

SQL> alter system set db_file_name_convert='/u3/baeifspdat','/baeifst_dbf/oradata/baeifst' scope=spfile;
SQL> alter system set log_file_name_convert='/u1/baeifst/redo',/baeifst_log1/oradata/baeifst','/u2/baeifst/redo','/baeifst_log2/oradata/baeifst' scope=spfile;
SQL> alter system set standby_file_management='AUTO' scope=both;

SQL> alter system set db_unique_name='ORCL';

SQL> alter database set standby database to maximize availability;



3. Create pfile on primary and copy password file and pfile to secondary.

SQL> create pfile from spfile;


PREPARE STANDBY DATABASE

Modify db_unique_name,fal_server, Log_archive dest, audit_file_dest,control_files,service_names etc.

set db_file_name_convert='/baeifst_dbf/oradata/baeifst','/u3/baeifspdat' scope=spfile;

set log_file_name_convert='/baeifst_log1/oradata/baeifst','/u1/baeifst/redo','/baeifst_log2/oradata/baeifst','/u2/baeifst/redo' scope=spfile;


SQL> startup nomount pfile=

SQL> create spfile from pfile;

SQL> shutdown immediate

CREATE STANDBY DATABASE

Copy datafiles in hot backup mode and stand by controlfile to standby.


ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control_stby.ctl';


6. Add databse resouce to cluster.
$ srvctl add database –d -o -p ;
$ srvctl add instance –d –i –n ;
$ srvctl add instance –d –i –n ;

7. Mount standby database.
$ srvctl start -d -o mount

Create the standby redo logs to support the standby role on both primary and standby. The recommended number of standby redo logs is one more than the number of online redo logs for each thread.
SQL> alter database add standby logfile thread 1 group 11 ('redo11a','redo11b') size 512M; --> same size of online redo log. One extra group upto group 14.
SQL> alter database add standby logfile thread 2 group 15 ('redo15a','redo15b') size 512M; --> One extra group upto group 18.



8. Start managed recovery on standby database.
SQL> recover managed standby database disconnect from session;

9. Enable log shipping on primary.
alter system set log_archive_dest_state_2=enable scope=both;



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

Check the number and group numbers of the redo logs.
SQL> select * from v$log;

Check the standby redo logs.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Verify Destinations are valid on both.

SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <= 2;



Verify redo apply on primary.

SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <= 2;



Verify gaps in redo on primary.

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;



Verify Error on both.

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;





select thread#,sequence#,archived,applied from v$archived_log order by 1,2;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;