OracleDB‎ > ‎

Dataguard

Oracle Dataguard (replication) setup for a single node primary database to a single node standby database.

Please note that setting up Oracle Dataguard is possible only on the Enterprise Edition.

Architecture:

Primary Database Name: primary
Primary SID: primary
Primary hostname: primarynode
Primary ORACLE_BASE: /opt/oracle

Standby Database Name: stndby
Standby SID: stndby
Standby hostname: stndbynode
Standby ORACLE_BASE: /opt/oracle

Create the primary node (you can just use dbca), migrate data if needed, configure the listener using netca (default setting is fine), then modify the following init.ora (fix paths and everything else that is different from this example).

init.ora of the primary node:

primary.__db_cache_size=167772160 primary.__java_pool_size=4194304 primary.__large_pool_size=4194304 primary.__shared_pool_size=100663296 primary.__streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/opt/oracle/admin/primary/adump' *.background_dump_dest='/opt/oracle/admin/primary/bdump' *.compatible='10.2.0.1.0' *.control_files='/opt/oracle/oradata/primary/control01.ctl','/opt/oracle/oradata/primary/control02.ctl','/opt/oracle/oradata/primary/control03.ctl' *.core_dump_dest='/opt/oracle/admin/primary/cdump' *.db_block_size=8192 *.db_domain= *.db_file_multiblock_read_count=16 *.db_name='primary' *.db_recovery_file_dest='/opt/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.db_unique_name='primary' *.dg_broker_start=TRUE *.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)' *.fal_client='primary' *.fal_server='stndby' *.job_queue_processes=10 *.log_archive_config='DG_CONFIG=(primary,stndby)' *.log_archive_dest_1='LOCATION=/opt/oracle/oradata/primary/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' *.log_archive_dest_2='SERVICE=stndby LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stndby' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' primary.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=2 *.log_archive_min_succeed_dest=1 primary.log_archive_trace=0 *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=285212672 primary.standby_archive_dest='/opt/oracle/oradata/primary/arch' *.standby_file_management='AUTO' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/primary/udump'

Start the primary database (we are making sure it is in archivelog mode as well).

$ set ORACLE_SID=primary $ sqlplus '/as sysdba' SQL> create spfile from pfile='<path to the init.ora file>/init.ora'; SQL> startup mount SQL> alter database archivelog; SQL> alter database open;

Run the following on the primary server to add redo logs for the standby (Assuming the redo logs of the primary db is 50M each).

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M;

After running the above command, do a shutdown immediate, and copy the datafiles, archive and redo logs (in this setup all this is located in $ORACLE_BASE/oradata/primary/ just exclude the control files as we need to create standby control files and copy those over [see next step]) to the standbynode:$ORACLE_BASE/oradata/stndby/.

On the primary database, we create a control file for the standby database and open the db.

SQL>STARTUP MOUNT; SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndby01.ctl'; SQL>ALTER DATABASE OPEN;

Copy '/tmp/stndby01.ctl' to standbynode:$ORACLE_BASE/oradata/stndby/control01.ctl, standbynode:$ORACLE_BASE/oradata/stndby/control02.ctl, and standbynode:$ORACLE_BASE/oradata/stndby/control03.ctl

Setup the following tnsnames.ora on both nodes.

$ORACLE_HOME/network/admin/tnsnames.ora:

STNDBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = STNDBY)
   )
 )


STANDBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = STNDBY)
   )
 )

PRIM =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521)))

PRIMARY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = PRIMARY)
   )
 )

EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
   )
   (CONNECT_DATA =
     (SID = PLSExtProc)
     (PRESENTATION = RO)
   )
 )

Copy primarynode:$ORACLE_HOME/dbs/orapwprimary to standbynode:$ORACLE_HOME/dbs/orapwstandby

Create the following init.ora on the standbynode.

init.ora of the standby node:

stndby.__db_cache_size=184549376 stndby.__java_pool_size=4194304 stndby.__large_pool_size=4194304 stndby.__shared_pool_size=83886080 stndby.__streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/opt/oracle/admin/stndby/adump' *.background_dump_dest='/opt/oracle/admin/stndby/bdump' *.compatible='10.2.0.1.0' *.control_files='/opt/oracle/oradata/stndby/control01.ctl','/opt/oracle/oradata/stndby/control02.ctl','/opt/oracle/oradata/stndby/control03.ctl' *.core_dump_dest='/opt/oracle/admin/stndby/cdump' *.db_block_size=8192 *.db_domain= *.db_file_multiblock_read_count=16 *.db_file_name_convert='primary','stndby' *.db_name='primary' *.db_recovery_file_dest='/opt/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.db_unique_name='stndby' *.dg_broker_start=TRUE *.dispatchers='(PROTOCOL=TCP) (SERVICE=stndbyXDB)' *.fal_client='stndby' *.fal_server='primary' *.job_queue_processes=10 *.log_archive_config='DG_CONFIG=(stndby,primary)' *.log_archive_dest_1='LOCATION=/opt/oracle/oradata/stndby/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stndby' *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' primary.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=2 primary.log_archive_trace=0 *.log_file_name_convert='primary','stndby' *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=285212672 *.standby_archive_dest='/opt/oracle/oradata/stndby/arch' *.standby_file_management='AUTO' *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/stndby/udump'

Startup the standby node by running the following:

$ export ORACLE_SID=stndby $ sqlplus '/as sysdba' SQL>create spfile from pfile='<path to the pfile.ora>/pfile.ora'; SQL>STARTUP MOUNT; SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

You are done with the setup!

Verifying log shipment

Identify the existing files on standbynode

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Switch a log on the primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Re-Run the same SQL to make sure that the logs are received and applied to the standby server. Verify that these logs were applied :

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Testing real-time apply

On Primary Database : create a table 'test' and insert a record.

SQL> create table test (a number); SQL> insert into test values (1); SQL> commit;

Do not make a log switch because I set up the LGWR ASYNC option so that The redo should be transferred and applied to the standby server in real time.

On the STANDBY DB server:

SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN READ ONLY; SQL> SELECT * FROM test;

You should see the commited transaction.

Place the standby back in managed recover mode

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This will take the standby directly from read only mode and place it in managed recovery mode.

Comments