 |
Step By Step process of
Configuring Oracle 10gR2 (10.2.0.1)
Dataguard on 32 bit RedHat Enterprise Linux 3 (RHEL3) / CentOS 3
By Bhavin Hingu
bhavin@oracledba.org
|
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
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 init parameter
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
Standby DB init parameter
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 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’
Create
the Standby Controlfile:
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.
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
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
Realtime Apply:
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
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.
=========================================================================================
Click
HERE for Step By Step
Process of Configuring RAC Standby Dataguard for RAC Primary ON Redhat
Linux.