Prod configuration
If you use spfile, set scope=both at the end of all alter commands. Other case, dont forget to alter parameters in init.ora file
1-)Be sure logging is enabled at database level:ALTER DATABASE FORCE LOGGING;
2-)Change db_unique_name parameter (in init.ora or spfile) if necessary. Default is ORACLE_SID. You may leave it as it is. Change requires restart to apply.
* alter system set STANDBY_FILE_MANAGEMENT=AUTO;
* alter system set log_archive_dest_state_1=ENABLE;
* alter system set log_archive_dest_state_2=DEFER;
* alter system set log_archive_dest_1='LOCATION={archive_path_prod} MANDATORY REOPEN=60 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME={db_unique_name_prod}';
TNS_stby is the TNS name configured for standby database. I strongly suggest configuring a new TNS listener on a different port reserverd for dataguard configuration. We will set it later at standby database.
* alter system set log_archive_dest_2='SERVICE={TNS_stby} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={db_unique_name_stby}';
* alter system set FAL_SERVER={TNS_stby};
* alter system set FAL_CLIENT={TNS_prod}; -- TNS_prod: tns name for primary database.
3-)Be sure that remote_login_passwordfile is set to EXCLUSIVE (show parameter remote_login_passwordfile)
4-)On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
6-)Add number of redologgroups+1 standby logfiles to the prod database. These redo logs wont be used unless a swithover is performed.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('{redo_path}/stbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('{redo_path}/stbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('{redo_path}/stbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('{redo_path}/stbyredo04.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('{redo_path}/stbyredo05.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('{redo_path}/stbyredo06.log') SIZE 52428800;
if there is RAC configuration then add logfiles for thread 2:
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 26 ('{redo_path}/stbyredo07.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 27 ('{redo_path}/stbyredo08.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 28 ('{redo_path}/stbyredo09.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 29 ('{redo_path}/stbyredo10.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 30 ('{redo_path}/stbyredo11.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 31 ('{redo_path}/stbyredo12.log') SIZE 52428800;
Standby configuration:
If you use spfile, set scope=both at the end of all alter commands. Other case, dont forget to alter parameters in init.ora file
1-) change db_unique_name parameter (in init.ora or spfile) if necessary. It can not be the same with production. I assume it is {db_unique_name_stby}
* alter system set STANDBY_FILE_MANAGEMENT=AUTO;
* alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
* alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
* alter system set LOG_ARCHIVE_DEST_1='LOCATION={archive_path_stby} VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME={db_unique_name_stby}';
TNS_prod is the TNS name configured for prod database. I strongly suggest configuring a new TNS listener on a different port reserverd for dataguard configuration.
* alter system set LOG_ARCHIVE_DEST_2='SERVICE={TNS_prod} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={db_unique_name_prod}';
* alter system set FAL_SERVER={TNS_prod};
* alter system set FAL_CLIENT={TNS_stby};
2-)Be sure that remote_login_passwordfile is set to EXCLUSIVE (show parameter remote_login_passwordfile)
3-)Configure listener at prod site so be sure that "tnsping TNS_prod" is working.
4-)Configure listener at standby site so be sure that "tnsping TNS_stby" is working.
5-)On platforms other than Windows, create a password file, and set the password for the SYS user to the same password used by the SYS user on the primary database. The password for the SYS user on every database in a Data Guard configuration must be identical for redo transmission to succeed. See Oracle Database Administrator's Guide.
6-)Add number of redologgroups+1 standby logfiles to the standby database.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('{redo_path}/stbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('{redo_path}/stbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('{redo_path}/stbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('{redo_path}/stbyredo04.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('{redo_path}/stbyredo05.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('{redo_path}/stbyredo06.log') SIZE 52428800;
if there is RAC configuration then add logfiles for thread 2:
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 20 ('{redo_path}/stbyredo07.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 21 ('{redo_path}/stbyredo08.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 22 ('{redo_path}/stbyredo09.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 23 ('{redo_path}/stbyredo10.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 24 ('{redo_path}/stbyredo11.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 25 ('{redo_path}/stbyredo12.log') SIZE 52428800;
Finally, activating dataguard configuration (prod):
alter systen set log_archive_dest_state_2=ENABLE; -- If you use spfile, set scope=both, Other case, dont forget to update init.ora file
Tuning Network Configuration:
Network Tuning - Oracle Net Session Data Unit (SDU) Size
Prod (tnsnames.ora):
...
( DESCRIPTION =
(SDU=32767)
( ADDRESS = (PROTOCOL = TCP) (Host = ...) (Port = ...) )
( CONNECT_DATA = (SID = ...) )
)
Standby (listener.ora):
SID_LIST_LISTENER_... =
( SID_LIST=
(SID_DESC =
(SDU=32767)
(ORACLE_HOME = ...)
(SID_NAME =...)
)
)
Network Tuning: Setting TCP Socket Buffer Sizes:
BDP= {available bandwidth}*{Network Round Trip Time-RTT}
Socket Buffer Size = 3*BDP
Example for 34Mbit bandwith with 2ms RTT:
= 3* ((34000000*0.02)/8)
=255000
Prod(tnsnames.ora):
... =
( DESCRIPTION =
(SDU=32767)
(SEND_BUF_SIZE=255000)
(RECV_BUF_SIZE=255000)
( ADDRESS = (PROTOCOL = TCP) (Host = ...) (Port = ...) )
( CONNECT_DATA = (SID = ...) )
)
standby(listener.ora):
LISTENER_...=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= ...))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =...)(PORT = 1521) (SEND_BUF_SIZE=255000) (RECV_BUF_SIZE=255000))
)
)
)
Starting and stopping Managed Recovery (Standby):
start
STARTUP MOUNT;
alter database recover managed standby database using current logfile disconnect from session; -- Applies not only completed redo logs, but also redo log currently being copied
or
alter database recover managed standby database disconnect from session; -- Applies completed redo logs
stop
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;
Monitoring:
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
2 comments:
güzel bir best practise olmuş...
teşekkürler
Post a Comment