BUILDING STANDBY USING ACTIVE DATAGURAD

Posted by

 

Primary : 

Hostname: primaryhost.domain.com
DB_NAME: ORCL
DB_UNIQUE_NAME: ORCL
SERVICE_NAME: ORCL

Standby : 

Hostname: standbyhost.domain.com
DB_NAME: ORCL
DB_UNIQUE_NAME: ORCLSBY
SERVICE_NAME: ORCLSBY

Prerequisites for setting up active standby

  • Oracle software release must be same for both primary and standby databases. 
  • The operating system running on primary and standby must be same but operating system release can be different. 
  • The Primary Database must be running in ARCHIVELOG mode. 
  • DB_NAME should be same on both primary and standby database.
  • db_unique_name should be different on primary and standby databases.

On Primary :

1a. Check if database is in archivelog mode or not. 

SQL> select name,database_role from v$database; 
NAME  DATABASE_ROLE
--------- ----------------
ORCL  PRIMARY 
SQL> select log_mode from v$database; 
LOG_MODE
------------
ARCHIVELOG

1b. Enable force logging on primary. 

SQL> ALTER DATABASE FORCE LOGGING;
Database altered. 

2.  Check the password file and create if it's not present. 

orapwd file=orapw$ORACLE_SID password=  ****** entries=10 ignorecase=y 

3.  Configure Oracle Net communication on primary.

cd $TNS_ADMIN 
Configure tnsnames.ora and listener.ora on primary. 
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
ORCLSBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLSBY)
    )
  ) 
 4.  Set primary database initialization parameters on Primary Database.  
 
db_unique_name='ORCL'
log_archive_dest_1='LOCATION=/oracle/archive/ORCL'
log_archive_dest_2='SERVICE=ORCLSBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSBY'
log_archive_config='dg_config=(ORCL,ORCLSBY)'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='defer'
fal_server= ORCLSBYSBY
fal_client= ORCLPRY
log_archive_format='%t_%s_%r.arc'
standby_file_management='auto'

If updating from sqlplus.  

SQL> alter system set log_archive_dest_2='SERVICE=ORCLSBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSBY'
System altered.
SQL> alter system set log_archive_config='dg_config=(ORCL,ORCLSBY)';
System altered.
SQL> alter system set log_archive_dest_state_2='defer';
System altered.
SQL> alter system set fal_server='ORCLSBY';
System altered.
SQL> alter system set fal_client='ORCL';
System altered.
SQL> alter system set log_archive_format=%t_%s_%r.arc';
System altered.
SQL> alter system set standby_file_management='auto';
System altered.
5.  Create Standby redo logs on Primary. 
 
SQL> alter database add standby logfile '/oracle/oradata/orcl/redo_stdby01.log' size 50M;
SQL> alter database add standby logfile  '/oracle/oradata/orcl/redo_stdby02.log’ size 50M;
SQL> alter database add standby logfile  '/oracle/oradata/orcl/redo_stdby03.log' size 50M;

On Standby :

1. Configure Oracle Net communication on standby.

cd $TNS_ADMIN 

Configure tnsnames.ora and listener.ora on standby.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )
ORCLSBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLSBY)
    )
  ) 

2. Copy password file form primary to standby. 

3. Test connectivity from standby to primary.

sqlplus 'sys/password'@ORCL as sysdba
sqlplus 'sys/password'@ORCLSBY as sysdba

4. Copy pfile from primary to standby and update parameter values for standby.

$ cat initorclsby.ora
*.audit_file_dest='/u01/app/oracle/admin/orclsby/adump'
*.audit_trail='db'
*.compatible='19.0.0.0.0'
*.control_files='/oracle/oradata/orclsby/control01.ctl','/oracle/fast_recovery_area/orclsby/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10000M
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLSBYXDB)'
*.open_cursors=1000
*.pga_aggregate_target=1000M
*.processes=3000
*.sessions=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3200M
*.db_cache_size = 2000M
*.shared_pool_size = 800M
*.shared_pool_reserved_size = 100M
*.undo_tablespace='UNDOTBS1'
*.diagnostic_dest = /u01/admin
*.standby_file_management=AUTO
log_archive_dest_1='LOCATION=/oracle/archive/orclsby
log_archive_config='DG_CONFIG=(ORCL,ORCLSBY)'
fal_server=ORCLPRY
fal_client=ORCLSBY
db_unique_name=ORCLSBY
db_file_name_convert=('/oracle/oradata/orcl,'/oracle/oradata/orclsby)
log_file_name_convert=('/oracle/oradata/orcl,'/oracle/oradata/orclsby)

5. Start the database in nomount state. 

$ sqlplus '/as sysdba'
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initorclsby.ora 

6. Duplicate database using rman on Standby.

$ rman target sys/****** @ORCL auxiliary sys/***** @ORCLSBY
RMAN>run{
         duplicate target database for standby from active database
        }

7. Enable below parameter from Primary Node.

SQL> alter system set log_archive_dest_state_2='ENABLE'; 

 8. Start MRP Process on standby node.

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE 
-------------------
READ ONLY WITH APPLY

9. Perform a few log switches on primary

SQL> alter system switch logfile;

10. On standby database verify that new redo data was received,archived and applied.

SQL> select sequence#, first_time,next_time,applied from v$archived_log order by sequence#; 


Comments

Post a Comment