donderdag 23 oktober 2014

How to create a physical standby database

Assumptions

This document has been tested on OUL 5 with Oracle 11.2.0.2 and 11.2.0.3
The Primary database PRIMARY_DB is in archivelog-mode and force-logging is enabled. This database is running on node1.
The Physical standby database is STANDBY_DB and runs on node2

Configuration

On Primary database

Set following parameters :
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(PRIMARY_DB,STANDBY_DB)';
ALTER SYSTEM SET log_archive_dest_1='LOCATION=+ORA_FRA_DG1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_DB';
ALTER SYSTEM SET log_archive_dest_2='SERVICE=STANDBY_DB NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_DB' SCOPE=MEMORY;
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE';
ALTER SYSTEM SET fal_server='STANDBY_DB';
ALTER SYSTEM SET fal_client='PRIMARY_DB';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=MEMORY;
Make sure that all required entries are present in the tnsnames.ora file on BOTH servers :
PRIMARY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = node1)(Port = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PRIMARY_DB)
    )
  )
STANDBY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = node2)(Port = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = STANDBY_DB )
    )
  )
Check connectivity using tnsping. Also make sure that the the tnsnames in the GRID-home has all required entries.
Put following entries in the listener.ora and restart the listener. The listener.ora on standby_server should have following 2 blocks. The second block is required for the dgmrl :
(SID_DESC =
 (GLOBAL_DBNAME = STANDBY_DB )
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
 (SID_NAME = FLEETTST_STBY)
)
(SID_DESC =
 (GLOBAL_DBNAME = STANDBY_DB_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
 (SID_NAME = FLEETTST_STBY)
)
Reload the listener on the standby server
[oracle@node2 admin]$ su - grid
Password:
[grid@node2 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /u01/app/oracle
[grid@node2 ~]$ lsnrctl reload listener
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-MAR-2014 15:42:39
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbnode5)(PORT=1521)))
The command completed successfully
[grid@node2 ~]$
Copy the password-file from the source database to the standby-database and rename the file to reflect the SID of the standby database :
On the primary_server
scp -p orapwPRIMARY_DB node2:/u01/app/oracle/product/11.2.0.2/db_1/dbs

On the standby_server

mv orapwPRIMARY_DB orapwSTANDBY_DB


create an initial init.ora file for the standby database with the following content. Make sure that the DB_BLOCK_SIZE is the same as on the primary-database :

DB_NAME=PRIMARY_DB
DB_UNIQUE_NAME=STANDBY_DB
DB_BLOCK_SIZE=8192
And startup the database in nomount mode :

SQL> startup nomount pfile=’?/dbs/initSTANDBY_DB.ora’

Create the standby database using rman  :

rman target sys/<Password>@PRIMARY_DB auxiliary sys/<Password>@STANDBY_DB
rman
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
  parameter_value_convert 'PRIMARY_DB','STANDBY_DB'
  set db_create_file_dest='+ORA_DATA_DG_DG1'
  set db_create_online_log_dest_1='+ORA_REDO_DG_DG1'
  set db_create_online_log_dest_2='+ORA_FRA_DG_DG1'
  set db_recovery_file_dest='+ORA_FRA_DG_DG1'
  set db_unique_name='STANDBY_DB'
  set db_file_name_convert='+ORA_DATA_DG1','+ORA_DATA_DG_DG1'
  set log_file_name_convert='+ORA_REDO_DG1','+ORA_REDO_DG_DG1'
  set control_files='+ORA_DATA_DG_DG1','+ORA_FRA_DG_DG1'
  set log_archive_max_processes='5'
  set fal_client='STANDBY_DB'
  set fal_server='PRIMARY_DB'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(PRIMARY_DB,STANDBY_DB)'
  set log_archive_dest_1='LOCATION=+ORA_FRA_DG_DG1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY_DB'
  set log_archive_dest_2='service=PRIMARY_DB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PRIMARY_DB'
;
}
Example used to create rcat_stby :
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
  parameter_value_convert 'RCAT','RCAT_STBY'
  set db_create_file_dest='+ORA_DATA_DG_DG1'
  set db_recovery_file_dest='+ORA_FRA_DG_DG1'
  set db_unique_name='RCAT_STBY'
  set db_file_name_convert='+ORA_DATA_DG1','+ORA_DATA_DG_DG1'
  set log_file_name_convert='+ORA_REDO_DG1','+ORA_REDO_DG_DG1','+ORA_FRA_DG1','+ORA_FRA_DG_DG1'
  set log_archive_max_processes='5'
  set fal_client='RCAT_STBY'
  set fal_server='RCAT'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(RCAT,RCAT_STBY)'
  set log_archive_dest_1='LOCATION=+ORA_FRA_DG_DG1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RCAT_STBY'
  set log_archive_dest_2='service=RCAT ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=RCAT'
  set control_files='+ORA_DATA_DG_DG1'
  set db_create_online_log_dest_1='+ORA_REDO_DG_DG1'
  set db_create_online_log_dest_2='+ORA_FRA_DG_DG1' 
;
}

When the above is finished the standby database should be created. Last step is to start up the apply process :

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

Configuration and usage of dgmgrl

When first starting dgmgrl and creating the configuration you will get the following error :

oracle [ /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs ]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/<Password>
Connected.
DGMGRL> show configuration
Error:
ORA-16525: the Data Guard broker is not yet available
Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration 'PRIMARY_DB_DR' as
> primary database is PRIMARY_DB
> connect identifier is PRIMARY_DB;
Error:
ORA-16525: the Data Guard broker is not yet available
ORA-06512: at "SYS.DBMS_DRS", line 157
ORA-06512: at line 1
DGMGRL> exit
This is because dgmgrl should be activated on both primary and standby database :

oracle [ /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs ]$ sqlplus / as sysdba
SQL> show parameter broker
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 .3/dbhome_1/dbs/dr1PRIMARY_DB.da
                                                 t
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 .3/dbhome_1/dbs/dr2PRIMARY_DB.da
                                                 t
dg_broker_start                      boolean     FALSE
SQL> alter system set dg_broker_start=true;
System altered.
SQL>

Next you will be able to create the initial configuration :
oracle [ /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs ]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/<Password>
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration 'PRIMARY_DB_DR'
> as
> primary database is 'PRIMARY_DB'
> connect identifier is 'PRIMARY_DB';
Configuration "PRIMARY_DB_DR" created with primary database "PRIMARY_DB"
DGMGRL> show configuration
Configuration - PRIMARY_DB_DR
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY_DB - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> add database 'STANDBY_DB'
> as
> connect identifier is 'STANDBY_DB';
Database "STANDBY_DB" added
DGMGRL> show configuration
Configuration - PRIMARY_DB_DR
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY_DB - Primary database
    STANDBY_DB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration - PRIMARY_DB_DR
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY_DB - Primary database
    STANDBY_DB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database PRIMARY_DB
Object "primary_db" was not found
DGMGRL> show database 'PRIMARY_DB'
Database - PRIMARY_DB
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PRIMARY_DB
Database Status:
SUCCESS
DGMGRL> show database 'STANDBY_DB';
Database - STANDBY_DB
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STANDBY_DB
Database Status:
SUCCESS
Next you should be able to perform a switchover :

DGMGRL> switchover to 'PRIMARY_DB';
Performing switchover NOW, please wait...
New primary database "PRIMARY_DB" is opening...
Operation requires shutdown of instance "STANDBY_DB" on database "STANDBY_DB"
Shutting down instance "STANDBY_DB"...
ORACLE instance shut down.
Operation requires startup of instance "STANDBY_DB" on database "STANDBY_DB"
Starting instance "STANDBY_DB"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PRIMARY_DB"
DGMGRL> show configuration
Configuration - PRIMARY_DB_DR
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY_DB      - Primary database
    FLEETTST_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> exit


Known issues


DGMGRL> show configuration
Configuration - PRIMARY_DB_DR
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY_DB    - Primary database
    STANDBY_DB    - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>


On the standby database issue the following :
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL>


Related articles
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)
Gepost door op
Labels: , ,

Geen opmerkingen:

Een reactie posten

Abonneren op: Reacties posten (Atom)