Showing posts with label Standby. Show all posts
Showing posts with label Standby. Show all posts

Data Guard: Switchover to a Physical Standby (II)









Posted by Minima-Imagem 0 comments
Labels: , ,

Q&As: Data Guard, Role Transitions (I)



(Q) Which 6 steps must be performed to prepare for a role transition?

(1) Verify that each database is properly configured for the role that it is about to assume
  • Check that necessary initialization parameters are properly configured in each database
    • FAL_SERVER, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT
    • LOG_ARCHIVE_DEST_n and LOG_ARVHIVE_DEST_STATE_n
  • Check that ONLINE REDO LOGs and STANDBY REDO LOGs exist and are properly configured on all databases
SQL> select status, type, member, group#
  2* from v$logfile;

STATUS TYPE MEMBER         GROUP#
------- ------- ------------------------------------------------------------ ----------
 ONLINE /u01/app/oracle/oradata/fresno/redo03.log         3
 ONLINE /u01/app/oracle/oradata/fresno/redo02.log         2
 ONLINE /u01/app/oracle/oradata/fresno/redo01.log         1
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo01.log        4
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo02.log        5
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo03.log        6
 STANDBY /u01/app/oracle/oradata/fresno/standby_redo04.log        7

7 rows selected.


(2) Verify that there are no Redo Transport errors or Redo Gaps at the standby database
  • For LOG_ARCHIVE_DEST_2, STATUS should be VALID and GAP_STATUS should be NO GAP
(On the Physical Standby:)
SQL> select dest_name, status, type, gap_status
  2  from v$archive_dest_status;

DEST_NAME            STATUS    TYPE            GAP_STATUS
------------------- --------- --------------- ----------
LOG_ARCHIVE_DEST_1   VALID     LOCAL
LOG_ARCHIVE_DEST_2   VALID     UNKNOWN         NO GAP
...
STANDBY_ARCHIVE_DEST VALID     UNKNOWN         NO GAP

(On the primary database)
SQL> select dest_name, status, type, gap_status
  2* from v$archive_dest_status;

DEST_NAME            STATUS    TYPE           GAP_STATUS
-------------------- --------- -------------- ------------------------
LOG_ARCHIVE_DEST_1   VALID     LOCAL
LOG_ARCHIVE_DEST_2   VALID     PHYSICAL       NO GAP
LOG_ARCHIVE_DEST_3   INACTIVE  LOCAL


(3) Verify that temporary files exist on the Standby that match the temporary files on the primary database


(4) Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;


(5) Before performing a switchover from an Oracle RAC primary databsae to a physical standby, shut down all but one primary db instance


(6) If the physical standby that will become primary on the switchover is in real-time query mode, consider bringing all instances of the standby database to the mounted but not open state. This will allow:
  • (a) the fastest possible role transition and/li>
  • (b) to cleanly terminate any user sessions connected to the physical standby prior to the role transition


Posted by Minima-Imagem 0 comments
Labels: , , ,

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.




















Posted by Minima-Imagem 0 comments
Labels: , ,
Subscribe to: Posts (Atom)