|
|
Step
By Step process of Configuring Oracle 10gR2 (10.2.0.1) Dataguard on 32 bit RedHat Enterprise Linux 3 (RHEL3) / CentOS 3 |
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 (RHEL3) / 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
Technical
Architecture of DataGuard
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
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
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
Enabling
Archiving on primary DB:
Ensure that the primary is in archive log mode
SQL>shutdown immediate
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
tnsnames/listener.ora
configuration:
# 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)
)
)
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))
)
)
Standby Redo Logs (SLRs)
Creation:
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
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 data files, online redologs and the standby logs if created and scp to the standby server in the corresponding directory. I
used the same directory structure as that with primary.
The only difference was the name of the directory. For e.g.,
On primary database, I have a path of /u01/app/oracle/oradata/PRIMARY/datafile whereas
This is the reason, I have used the db_file_name_convert parameter in the
primary init.ora file with the value of
Create the Standby
Controlfile:
On Primary Database:
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndby01.ctl';
SQL>ALTER DATABASE OPEN;
Copy 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.
Starting and Verifying the standby DB:
SQL>create spfile from pfile;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT;
Verify the Standby :
Identify the existing files on the
standby
SELECT SEQUENCE#,
FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Switch a log on the primary database:
ALTER SYSTEM SWITCH
LOGFILE;
Re-Run the same SQL to make sure that the logs are received and applied to
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.
INSERT INTO test VALUES (sysdate);
COMMIT;
Do not make a log switch because I set up the LGWR ASYNC option so that
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 committed transaction.
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.
Click HERE for Step By Step Process of Configuring
RAC Standby Dataguard for RAC Primary ON Redhat Linux.