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.
