PREAMBLE :-
This Document gives basic information about the steps carried out to setup an Standby Database. The primary database (production DB) is a dual node RAC database. The Physical standby database is a Single Node/Single instance. The DB version on both the Primary and Target is 10.2.0.3.
The Environment:-
Production
(2 Linux Servers) With DB version 10.2.0.3.0 Real Application Cluster.
Standby Server(1 Linux Server) with the same version of DB as the Primary.
The Physical layout of the mounts/DB files/Log file etc in the standby are exact replica of the the primary instance.
1) Setup the primary Database.
a) Enabled the Force logging in the Primary Database using the below command as sysdba
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
b) Created the password file on both the RAC nodes in Primary as the Redolog files from both the servers will be transferred to the Standby.
/d02/oraprod/product/10.2.0/dbs
cd $ORACLE_HOME/dbs
orapwd file=orapwPROD1 password=PASSWORD force=y
(Repeat the same in the other Node)
c) The Standby Log is not created now as it is only during the testing of the failover.
d) Setup the Primary database Initialization parameters need for Standby.(These parameters should be added in both the nodes)
Note: the Parameter Remote_login_passwordfile should be set to exclusive.
*.workarea_size_policy='AUTO'# Required 11i setting
#Archiving
.log_archive_config='send'
*.log_archive_dest_1='location=/oracle04/oraprod/arch01'
*.log_archive_dest_2='location=/oracle03/oraprod/arch02'
.log_archive_dest_3='SERVICE=PRDN_STANDBY LGWR ASYNC REOPEN=100'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='prod_%s_%t_%r.arc'
*.log_archive_max_processes=8
*.log_archive_start=TRUE
*.standby_archive_dest='/d01/oraprod/prodarc'
e) Once the new parameters needed for Standby are added bounce the instance for the parameters to take effect.
2) Standby Database steps
a) Create control file in the primary database which will be used by the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
'/data01/home/oraprod/PRODSTBY.ctl';
Database altered.
3) Backup the primary Database and copy the data files to the target server to the required mounts.
In our case i had shutdown the database and took a Tar backup in the local server and copied to the Standby server. We should copy all the archive files to the standby server which will be used for recovery.
4) Copy the initialization parameter file from the primary to the standby node and make necessary changes.
[oraprod@standby dbs]$ more initPROD.ora
PROD.__db_cache_size=2097152000
PROD.__java_pool_size=33554432
PROD.__large_pool_size=16777216
PROD.__shared_pool_size=2097152000
PROD.__streams_pool_size=33554432
*._b_tree_bitmap_plans=FALSE# Required 11i setting
*._fast_full_scan_enabled=FALSE
*._immediate_commit_propagation=TRUE
*._like_with_bind_as_equality=TRUE
*._lm_global_posts=TRUE
*._sort_elimination_cost_ratio=5
*._sqlexec_progression_cost=2147483647
*._system_trig_enabled=true
*._trace_files_public=TRUE
*.aq_tm_processes=1
*.background_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD_standby/bdump'
#*.cluster_database_instances=2
#*.cluster_database=TRUE
*.compatible='10.2.0'
*.control_files='/oracle03/oraprod/data01/PRODSTBY.ctl','/oracle03/oraprod/data01/PRODSTBY1.ctl'
*.core_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD1_PROD_standby/cdump'
*.cursor_sharing='EXACT'# Required 11i settting
*.db_block_checking='FALSE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_file_multiblock_read_count=8# Required 11i setting
*.db_files=1024# Max. no. of database files
*.db_name='PROD'
*.dml_locks=10000
*.instance_name='PROD'
*.instance_number=1
*.job_queue_processes=2
*.log_buffer=10485760
*.log_checkpoint_interval=100000
*.log_checkpoint_timeout=1200# Checkpoint at least every 20 mins.
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20480'# trace file size
*.nls_comp='binary'# Required 11i setting
*.nls_date_format='DD-MON-RR'
*.nls_language='american'
*.nls_length_semantics='BYTE'# Required 11i setting
*.nls_numeric_characters='.,'
*.nls_sort='binary'# Required 11i setting
*.nls_territory='america'
*.olap_page_pool_size=4194304
*.open_cursors=600# Consumes process memory, unless using MTS.
*.optimizer_secure_view_merging=false
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=1G
*.plsql_code_type='INTERPRETED'# Default 11i setting
*.plsql_native_library_dir='/d02/oraprod/product/10.2.0/plsql/nativelib'
*.plsql_native_library_subdir_count=149
*.plsql_optimize_level=2# Required 11i setting
*.processes=800# Max. no. of users x 2
*.service_names='PROD'
*.session_cached_cursors=500
*.sessions=400# 2 X processes
*.sga_target=4G
*.shared_pool_reserved_size=200M
*.shared_pool_size=2000M
*.thread=1
*.timed_statistics=true
*.undo_management='AUTO'# Required 11i setting
*.undo_tablespace='APPS_UNDOTS1'# Required 11i setting
*.user_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD_standby/udump'
*.utl_file_dir='/usr/tmp','/usr/tmp','/d02/oraprod/product/10.2.0/appsutil/outbound/PROD1_standby','/usr/tmp','/d02/pr
od/oracle/prodappl/xxdn/11.5.0/data','/d02/prod/oracle/prodappl/xxdn/11.5.0/data/inbound','/d02/prod/oracle/prodappl/xxdn/11.
5.0/data/outbound','/usr/tmp'
*.workarea_size_policy='AUTO'# Required 11i setting
#Archiving
.log_archive_config='send'
*.log_archive_dest_1='location=/oracle04/oraprod/prodarc'
*.log_archive_dest_2='SERVICE=PROD_PROD LGWR ASYNC REOPEN=100'
#.log_archive_dest_3='SERVICE=PRDN_STANDBY LGWR ASYNC REOPEN=100'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='defer'
#*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='prod_%s_%t_%r.arc'
*.log_archive_max_processes=8
*.log_archive_start=TRUE
*.standby_archive_dest='/oracle04/oraprod/prodarc'
*.standby_file_management='AUTO'
5) Create all the required dump directories and archive log destinations mentioned in the parameter file.
6) Copy and place the standby control file created in the Primary database to the Standby server.
7) Configure the listener ,tnsnames in the standby DB to reflect the entries of the both the RAC instances.
Tnsnames.Ora entry:
PROD_PROD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =prod1)(PORT = 1581))
(ADDRESS = (PROTOCOL = TCP)(HOST =prod2)(PORT = 1581))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 200)
(DELAY = 5)
)
)
)
Also Add the entry of the Standby Instance in the tnsnames of both the RAC nodes and test is by issuing tnsping
8) Setup the Standby instance and bring up the instance .
a) Setup the initialization parameters to point to the standby database, and bringup the instance with mount option and place it in the standby mode.
SQL> Startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
9) The Standby database will automatically apply all the archives and the current logs will applied. Check the Alert logs and confirm that the standby is in Sync.
This Document gives basic information about the steps carried out to setup an Standby Database. The primary database (production DB) is a dual node RAC database. The Physical standby database is a Single Node/Single instance. The DB version on both the Primary and Target is 10.2.0.3.
The Environment:-
Production
(2 Linux Servers) With DB version 10.2.0.3.0 Real Application Cluster.
Standby Server(1 Linux Server) with the same version of DB as the Primary.
The Physical layout of the mounts/DB files/Log file etc in the standby are exact replica of the the primary instance.
1) Setup the primary Database.
a) Enabled the Force logging in the Primary Database using the below command as sysdba
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
b) Created the password file on both the RAC nodes in Primary as the Redolog files from both the servers will be transferred to the Standby.
/d02/oraprod/product/10.2.0/dbs
cd $ORACLE_HOME/dbs
orapwd file=orapwPROD1 password=PASSWORD force=y
(Repeat the same in the other Node)
c) The Standby Log is not created now as it is only during the testing of the failover.
d) Setup the Primary database Initialization parameters need for Standby.(These parameters should be added in both the nodes)
Note: the Parameter Remote_login_passwordfile should be set to exclusive.
*.workarea_size_policy='AUTO'# Required 11i setting
#Archiving
.log_archive_config='send'
*.log_archive_dest_1='location=/oracle04/oraprod/arch01'
*.log_archive_dest_2='location=/oracle03/oraprod/arch02'
.log_archive_dest_3='SERVICE=PRDN_STANDBY LGWR ASYNC REOPEN=100'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='prod_%s_%t_%r.arc'
*.log_archive_max_processes=8
*.log_archive_start=TRUE
*.standby_archive_dest='/d01/oraprod/prodarc'
e) Once the new parameters needed for Standby are added bounce the instance for the parameters to take effect.
2) Standby Database steps
a) Create control file in the primary database which will be used by the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
'/data01/home/oraprod/PRODSTBY.ctl';
Database altered.
3) Backup the primary Database and copy the data files to the target server to the required mounts.
In our case i had shutdown the database and took a Tar backup in the local server and copied to the Standby server. We should copy all the archive files to the standby server which will be used for recovery.
4) Copy the initialization parameter file from the primary to the standby node and make necessary changes.
[oraprod@standby dbs]$ more initPROD.ora
PROD.__db_cache_size=2097152000
PROD.__java_pool_size=33554432
PROD.__large_pool_size=16777216
PROD.__shared_pool_size=2097152000
PROD.__streams_pool_size=33554432
*._b_tree_bitmap_plans=FALSE# Required 11i setting
*._fast_full_scan_enabled=FALSE
*._immediate_commit_propagation=TRUE
*._like_with_bind_as_equality=TRUE
*._lm_global_posts=TRUE
*._sort_elimination_cost_ratio=5
*._sqlexec_progression_cost=2147483647
*._system_trig_enabled=true
*._trace_files_public=TRUE
*.aq_tm_processes=1
*.background_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD_standby/bdump'
#*.cluster_database_instances=2
#*.cluster_database=TRUE
*.compatible='10.2.0'
*.control_files='/oracle03/oraprod/data01/PRODSTBY.ctl','/oracle03/oraprod/data01/PRODSTBY1.ctl'
*.core_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD1_PROD_standby/cdump'
*.cursor_sharing='EXACT'# Required 11i settting
*.db_block_checking='FALSE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_file_multiblock_read_count=8# Required 11i setting
*.db_files=1024# Max. no. of database files
*.db_name='PROD'
*.dml_locks=10000
*.instance_name='PROD'
*.instance_number=1
*.job_queue_processes=2
*.log_buffer=10485760
*.log_checkpoint_interval=100000
*.log_checkpoint_timeout=1200# Checkpoint at least every 20 mins.
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20480'# trace file size
*.nls_comp='binary'# Required 11i setting
*.nls_date_format='DD-MON-RR'
*.nls_language='american'
*.nls_length_semantics='BYTE'# Required 11i setting
*.nls_numeric_characters='.,'
*.nls_sort='binary'# Required 11i setting
*.nls_territory='america'
*.olap_page_pool_size=4194304
*.open_cursors=600# Consumes process memory, unless using MTS.
*.optimizer_secure_view_merging=false
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=1G
*.plsql_code_type='INTERPRETED'# Default 11i setting
*.plsql_native_library_dir='/d02/oraprod/product/10.2.0/plsql/nativelib'
*.plsql_native_library_subdir_count=149
*.plsql_optimize_level=2# Required 11i setting
*.processes=800# Max. no. of users x 2
*.service_names='PROD'
*.session_cached_cursors=500
*.sessions=400# 2 X processes
*.sga_target=4G
*.shared_pool_reserved_size=200M
*.shared_pool_size=2000M
*.thread=1
*.timed_statistics=true
*.undo_management='AUTO'# Required 11i setting
*.undo_tablespace='APPS_UNDOTS1'# Required 11i setting
*.user_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD_standby/udump'
*.utl_file_dir='/usr/tmp','/usr/tmp','/d02/oraprod/product/10.2.0/appsutil/outbound/PROD1_standby','/usr/tmp','/d02/pr
od/oracle/prodappl/xxdn/11.5.0/data','/d02/prod/oracle/prodappl/xxdn/11.5.0/data/inbound','/d02/prod/oracle/prodappl/xxdn/11.
5.0/data/outbound','/usr/tmp'
*.workarea_size_policy='AUTO'# Required 11i setting
#Archiving
.log_archive_config='send'
*.log_archive_dest_1='location=/oracle04/oraprod/prodarc'
*.log_archive_dest_2='SERVICE=PROD_PROD LGWR ASYNC REOPEN=100'
#.log_archive_dest_3='SERVICE=PRDN_STANDBY LGWR ASYNC REOPEN=100'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='defer'
#*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='prod_%s_%t_%r.arc'
*.log_archive_max_processes=8
*.log_archive_start=TRUE
*.standby_archive_dest='/oracle04/oraprod/prodarc'
*.standby_file_management='AUTO'
5) Create all the required dump directories and archive log destinations mentioned in the parameter file.
6) Copy and place the standby control file created in the Primary database to the Standby server.
7) Configure the listener ,tnsnames in the standby DB to reflect the entries of the both the RAC instances.
Tnsnames.Ora entry:
PROD_PROD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =prod1)(PORT = 1581))
(ADDRESS = (PROTOCOL = TCP)(HOST =prod2)(PORT = 1581))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 200)
(DELAY = 5)
)
)
)
Also Add the entry of the Standby Instance in the tnsnames of both the RAC nodes and test is by issuing tnsping
8) Setup the Standby instance and bring up the instance .
a) Setup the initialization parameters to point to the standby database, and bringup the instance with mount option and place it in the standby mode.
SQL> Startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
9) The Standby database will automatically apply all the archives and the current logs will applied. Check the Alert logs and confirm that the standby is in Sync.
No comments:
Post a Comment