This document explains the step by step process of Configuring 10g R2 Single Instance Dataguard for Single Instance Primary
on RedHat Enterprise Linux 32 bit / CentOS 3.6.
Click HERE for Step By Step Process of Configuring RAC Standby Dataguard for RAC Primary ON Redhat Linux.
Task List:
10g R2 Dataguard Technical Architecture
on RedHat Enterprise Linux 32 bit / CentOS 3.6.
Click HERE for Step By Step Process of Configuring RAC Standby Dataguard for RAC Primary ON Redhat Linux.
Task List:
10g R2 Dataguard Technical Architecture
Primary DB init parameter
Standby DB init parameter
Enable Archiving On Primary DB
tnsnames.ora/listener.ora configuration
Creating Standby Redo logs (SLRs)
Backup the Primary DB.
Creating the standby controlfile
Startig and verifying Standby DB
Testing Realtime Apply
Standby DB init parameter
Enable Archiving On Primary DB
tnsnames.ora/listener.ora configuration
Creating Standby Redo logs (SLRs)
Backup the Primary DB.
Creating the standby controlfile
Startig and verifying Standby DB
Testing Realtime Apply
Primary Database Name: primary
Service Name: primary
Primary Node:
SID: primary
Network name (hostname): node1-prv
ORACLE_BASE: /u01/app/oracle
Standby Database Name: stndby
Service Name: stndby
Standby Node:
SID: stndby
Network name (hostname): node2-prv
ORACLE_BASE: /u01/app/oracle
Service Name: primary
Primary Node:
SID: primary
Network name (hostname): node1-prv
ORACLE_BASE: /u01/app/oracle
Standby Database Name: stndby
Service Name: stndby
Standby Node:
SID: stndby
Network name (hostname): node2-prv
ORACLE_BASE: /u01/app/oracle
primary.__db_cache_size=67108864
primary.__java_pool_size=4194304 primary.__large_pool_size=4194304 primary.__shared_pool_size=88080384 primary.__streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/u01/app/oracle/admin/primary/adump' *.background_dump_dest='/u01/app/oracle/admin/primary/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/PRIMARY/controlfile/o1_mf_26lg83r9_.ctl','/u01/app/oracle/flash_recovery_area/PRIMARY/controlfile/o1_mf_26lg844c_.ctl'
*.core_dump_dest='/u01/app/oracle/admin/primary/cdump' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='primary'
*.db_recovery_file_dest='/u01/app/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=/u01/app/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=167772160 primary.standby_archive_dest='/u01/app/oracle/oradata/PRIMARY/arch '
*.standby_file_management='AUTO'
*.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/primary/udump' *.local_listener=prim
primary.__java_pool_size=4194304 primary.__large_pool_size=4194304 primary.__shared_pool_size=88080384 primary.__streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/u01/app/oracle/admin/primary/adump' *.background_dump_dest='/u01/app/oracle/admin/primary/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/PRIMARY/controlfile/o1_mf_26lg83r9_.ctl','/u01/app/oracle/flash_recovery_area/PRIMARY/controlfile/o1_mf_26lg844c_.ctl'
*.core_dump_dest='/u01/app/oracle/admin/primary/cdump' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='primary'
*.db_recovery_file_dest='/u01/app/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=/u01/app/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=167772160 primary.standby_archive_dest='/u01/app/oracle/oradata/PRIMARY/arch '
*.standby_file_management='AUTO'
*.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/primary/udump' *.local_listener=prim
stndby.__db_cache_size=75497472 stndby.__java_pool_size=4194304 stndby.__large_pool_size=4194304 stndby.__shared_pool_size=79691776 stndby.__streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/u01/app/oracle/admin/stndby/adump' *.background_dump_dest='/u01/app/oracle/admin/stndby/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/STNDBY/controlfile/stndby01.ctl','/u01/app/oracle/flash_recovery_area/STNDBY/controlfile/stndby02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/stndby/cdump' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='primary' *.db_recovery_file_dest='/u01/app/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=/u01/app/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'
*.log_archive_max_processes=2 *.log_archive_trace=0 *.db_file_name_convert= 'PRIMARY', 'STNDBY'
*.log_file_name_convert='PRIMARY', 'STNDBY'
*.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=167772160 *.standby_archive_dest='/u01/app/oracle/oradata/STNDBY/arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/stndby/udump'
*.local_listener=stnd
*.core_dump_dest='/u01/app/oracle/admin/stndby/cdump' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='primary' *.db_recovery_file_dest='/u01/app/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=/u01/app/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'
*.log_archive_max_processes=2 *.log_archive_trace=0 *.db_file_name_convert= 'PRIMARY', 'STNDBY'
*.log_file_name_convert='PRIMARY', 'STNDBY'
*.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=167772160 *.standby_archive_dest='/u01/app/oracle/oradata/STNDBY/arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/stndby/udump'
*.local_listener=stnd
Ensure that the primary is in archive log mode
SQL>shutdown immediate
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db10g/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
STNDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node2-prv)(PORT = 10521)) ) (CONNECT_DATA = (SERVICE_NAME = STNDBY) ) )
PRIM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1-prv)(PORT = 10521)))
PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node1-prv)(PORT = 10521)) ) (CONNECT_DATA = (SERVICE_NAME = PRIMARY) ) )
EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
STNDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node2-prv)(PORT = 10521)) ) (CONNECT_DATA = (SERVICE_NAME = STNDBY) ) )
PRIM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1-prv)(PORT = 10521)))
PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node1-prv)(PORT = 10521)) ) (CONNECT_DATA = (SERVICE_NAME = PRIMARY) ) )
EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
Copy the same file to the standby server and adjust it based on the listener.ora file. Also update the listener.ora file so that it listen the SIDs mentioned in the tnsnames.ora file.
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db10g/network/admin/listener.ora # Generated by Oracle configuration tools.
SID_LIST_LISTENER_STBY = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db10g) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = stndby) (GLOBAL_DBNAME = stndby_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db10g) ) )
LISTENER_STBY = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2-prv)(PORT = 10521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
SID_LIST_LISTENER_STBY = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db10g) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = stndby) (GLOBAL_DBNAME = stndby_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db10g) ) )
LISTENER_STBY = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2-prv)(PORT = 10521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
In case of OMF:
Get the max group# of online redo logs on PRIMARY database
SELECT max (group#) from v$logfile;
Create the standby redo logs on the primary database with the same size of that of online redo logs. If the above query retuns the value of 3 and each logfile is 50M in size (from the below query) then, create atleast 4standby redo logs of the size of 50M per thread.
SELECT byte from v$log;
Create the SRL's :
Get the max group# of online redo logs on PRIMARY database
SELECT max (group#) from v$logfile;
Create the standby redo logs on the primary database with the same size of that of online redo logs. If the above query retuns the value of 3 and each logfile is 50M in size (from the below query) then, create atleast 4standby redo logs of the size of 50M per thread.
SELECT byte from v$log;
Create the SRL's :
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M / ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M / ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M / ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M /
Backup the primary DB:
Take a cold/Hot/RMAN backup of the primary database. I used the cold backup in this case.
SQL>SHUTDOWN IMMEDIATE
Backup the datafiles, online redologs and the standby logs if created and scp to the standby server in the corrosponding directory. I used the same directory structure as that with primary. The only differece was the name of the direcory. For e.g,
On primary database, I have a path of /u01/app/oracle/oradata/PRIMARY/datafile whereas On standby server, I have a path of
/u01/app/oracle/oradata/STNDBY/datafile,
This is the reason, I have used the db_file_name_convert parameter in the primary init.ora file with the value of
db_file_name_convert=’PRIMARY’,’STNDBY’
and in the standby init.ora file with the value of
db_file_name_convert=’STNDBY’, ‘PRIMARY’
SQL>SHUTDOWN IMMEDIATE
Backup the datafiles, online redologs and the standby logs if created and scp to the standby server in the corrosponding directory. I used the same directory structure as that with primary. The only differece was the name of the direcory. For e.g,
On primary database, I have a path of /u01/app/oracle/oradata/PRIMARY/datafile whereas On standby server, I have a path of
/u01/app/oracle/oradata/STNDBY/datafile,
This is the reason, I have used the db_file_name_convert parameter in the primary init.ora file with the value of
db_file_name_convert=’PRIMARY’,’STNDBY’
and in the standby init.ora file with the value of
db_file_name_convert=’STNDBY’, ‘PRIMARY’
On Primary Database:
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndby01.ctl';
SQL>ALTER DATABASE OPEN;
scp the stndby01.ctl file to the standby site. I have multiplexed it in the initstndby.ora file. So I SCPed the same file to both the locations mentioned in the initstndby.ora file. Also, SCPed the $ORACLE_HOME/dbs/orapwprimary file of the primary to the same location on the standby with the name of orapwstndby.
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndby01.ctl';
SQL>ALTER DATABASE OPEN;
scp the stndby01.ctl file to the standby site. I have multiplexed it in the initstndby.ora file. So I SCPed the same file to both the locations mentioned in the initstndby.ora file. Also, SCPed the $ORACLE_HOME/dbs/orapwprimary file of the primary to the same location on the standby with the name of orapwstndby.
SQL>create spfile from pfile;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Verify the Standby :
- Identify the existing files on the standby
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#;
On Primary Database : create a table 'test' and insert a record. 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#;
INSERT INTO test VALUES (sysdate);
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:
SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
ALTER DATABASER RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
SELECT * FROM test;
You should see the commited transaction.
Place the standby back in managed recover mode
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.
=================================================================
No comments:
Post a Comment