DataGuard: Configuring a Physical Standby


Configure Primary Server





Primary Server Setup


(1) Is database in Archivelog Mode?
Home -> Availability -> Recovery Settings
SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

If database is in Noarchivelog mode, you need to switch it to Archivelog mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;


(2) Is Force Logging enabled?
SQL> select force_logging from v$database;
FOR
---
NO

SQL> alter database force logging;
Database altered.


(3) Edit initialization parameters for the primary role
db_name db_unique_name log_archive_config log_archive_dest_1
log_archive_dest_2 log_archive_dest_state_1 log_archive_dest_state_2 fal_server
db_file_name_convert log_file_name_convert remote_login_passwordfile

Home -> Server -> Initialization Parameters
SQL> select name, db_unique_name from v$database;

NAME   DB_UNIQUE_NAME
--------- ------------------------------
DG11G   atlanta

SQL> show parameter db_name;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_name         string  dg11g

SQL> show parameter db_unique_name;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_unique_name        string  atlanta

SQL> alter system set log_archive_config='dg_config=(atlanta, fresno)';  
System altered.    
    
SQL> show parameter log_archive_config    
    
NAME                         TYPE    VALUE    
---------------------------- ------- ------------------------------    
log_archive_config           string  dg_config=(atlanta, fresno)    
    
Obs.    
   In this configuration, primary=> atlanta; standby=> fresno    


SQL> show parameter log_archive_dest_

NAME        TYPE  VALUE
---------------------------- ----------- ----------------------------
log_archive_dest_            string  location=use_db_recovery_file_
             dest valid_for=(all_logfiles,
      all_roles) db_unique_name=atla
      nta
...
NAME              TYPE    VALUE
--------------------------- ----------- ------------------------------
log_archive_dest_2          string  service=fresno async valid_for
      =(online_logfiles, primary_rol
      e) db_unique_name=fresno
...
Home -> Server -> Initialization Parameters


SQL> show parameter fal_server 
 
NAME         TYPE  VALUE 
------------------------------------ ----------- ------------------------------ 
fal_server        string  fresno 
Home -> Server -> Initialization Parameters


SQL> show parameter remote_login_pa

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile      string  EXCLUSIVE


SQL> alter system set db_file_name_convert='fresno', 'atlanta' scope=spfile; 
System altered. 

SQL> alter system set log_file_name_convert='fresno', 'atlanta' scope=spfile; 
System altered. 

SQL> alter system set standby_file_management=auto; 
System altered. 
Home -> Server -> Initialization Parameters -> Standby Database


(4) Configure Oracle Net for Redo Transport
Redo Transport moves archived redo logs from the primary to the standby database.
The two databases need to be connected through Oracle Net.
For this you can edit tnsnames.ora files in each server. i.e.
# Generated by Oracle configuration tools.

ATLANTA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oe5srv.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DG11G)
    )
  )

FRESNO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oe5clt.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DG11G)
    )
  )


(5) create a backup copy of the primary database datafiles
Using RMAN:

Home -> Server -> Availability -> Schedule Backup
  • Select a whole Database backup and enter host credentials

Home -> Server -> Availability -> Schedule Customized Backup -> Options

Home -> Server -> Availability -> Schedule Customized Backup -> Settings

Home -> Server -> Availability -> Schedule Backup -> Schedule

Home -> Server -> Availability -> Schedule Backup -> Review

Home -> Server -> Availability -> Schedule Backup -> Summary


(6) Create a Control file for the Physical Standby Database
SQL> alter database create standby controlfile as '/tmp/fresno_stdby.ctl';

Database altered.


(7) Create a Parameter File for the Physical Standby Database.
Edit the Parameter file to configure the settings for the Standby
(3.1) Create a PFILE from the SPFILE used by the Primary DB

SQL> create pfile= '/tmp/initfresno.ora' from spfile;
File created.

(3.2) Modify the PFILE to be used on a Physical Standby
$ cat initfresno.ora 
dg11g.__db_cache_size=67108864
dg11g.__java_pool_size=4194304
dg11g.__large_pool_size=4194304
dg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg11g.__pga_aggregate_target=150994944
dg11g.__sga_target=205520896
dg11g.__shared_io_pool_size=0
dg11g.__shared_pool_size=117440512
dg11g.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/fresno/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/fresno/control01.ctl',
                '/u01/app/oracle/flash_recovery_area/fresno/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='atlanta', 'fresno'
*.db_name='dg11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='fresno'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg11gXDB)'
*.fal_server='atlanta'#In case of a switchover, fetch records from the fal_server
*.log_archive_config='dg_config=(atlanta, fresno)'#db_unique_names of the databases in the DG configuration
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) 
                            db_unique_name=fresno'#configure local redo log archiving. Use Fast recovery Area
*.log_archive_dest_2='service=atlanta async valid_for=(online_logfiles, primary_role) 
        db_unique_name=atlanta'#configure remote log archiving. Configure redo transport to physical standby
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='atlanta', 'fresno'
*.memory_target=356515840
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


Standby Server Setup


(1) Create appropriate directories in the Standby server. Copy files from the prymary to the standby system.
(8.0) Create directories for fresno database files
$ mkdir -p oradata/fresno
$ mkdir -p flash_recovery_area/fresno
$ mkdir -p admin/fresno/adump

(8.1) Copy the standby control files to all locations (in the same server)
$ scp oracle@oe5srv:/tmp/fresno.ctl /u01/app/oracle/oradata/fresno/control01.ctl
$ cp /u01/app/oracle/oradata/fresno/control01.ctl /u01/app/oracle/flash_recovery_area/fresno/control02.ctl

(8.2) Copy Archivelogs and backups (in the same server)
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/archivelog  /u01/app/oracle/flash_recovery_area/fresno
$ scp -r oracle@oe5srv:/u01/app/oracle/flash_recovery_area/ATLANTA/backupset  /u01/app/oracle/flash_recovery_area/fresno

(8.3) Copy passwordfile and parameter file
$ scp -r oracle@oe5srv:$ORACLE_HOME/dbs/orapwdg11g $ORACLE_HOME/dbs
$ scp -r oracle@oe5srv:/tmp/initfresno.ora /tmp/initfresno.ora

(2) Create SPFILE from the edited parameter file
oracle@oe5clt:$ echo $ORACLE_SID  
dg11g

oracle@oe5clt:$ sqlplus / as sysdba 
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 8 00:37:44 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
  
Connected to an idle instance.  

SQL> create spfile from pfile='/tmp/initfresno.ora';
  
File created.  

(3) Restore backup
RMAN> startup mount; 
 
Oracle instance started 
database mounted 

RMAN> restore database; 
 
Starting restore at 08-MAR-12 
using channel ORA_DISK_1 
 
channel ORA_DISK_1: starting datafile backup set restore 
channel ORA_DISK_1: specifying datafile(s) to restore from backup set 
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/fresno/system01.dbf 
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/fresno/sysaux01.dbf 
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/fresno/undotbs01.dbf 
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/fresno/users01.dbf 
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/FRESNO/backupset/o1_mf_nnndf_BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp 
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/FRESNO/backupset/o1_mf_nnndf_BACKUP_ATLANTA_000_0_7ojnc7g1_.bkp tag=BACKUP_ATLANTA_000_030712102725 
channel ORA_DISK_1: restored backup piece 1 
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37 
Finished restore at 08-MAR-12 

(4) Create Online and Standby Redo logs on the Standby Database
SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo01.log') size 50m;

Database altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo02.log') size 50m;

Database altered.

SQL> alter database add logfile('/u01/app/oracle/oradata/fresno/online_redo03.log') size 50m;

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.


SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo01.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo02.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo03.log') size 50m;

Database altered.

SQL> alter database add standby logfile('/u01/app/oracle/oradata/fresno/standby_redo04.log') size 50m;

Database altered.


Start Apply Service

SQL> Alter database recover managed standby database disconnect from session;

Database altered.




















No comments:

Post a Comment