Primary Database Preparation
Step 1:- Setup Archive log mode
Primary database must be in Archive Log Mode. This is mandatory.
Below 2 commands help us identify weather the db is in archivelog mode or no.
SQL> archive log list;
or
SQL> select log_mode from v$database;
To put the database in archive log mode do the following:
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
To confirm the archive log status;
archive log list;
or
select name, log_mode from v$database;
Step 2:- Enable Force Logging
Force Logging must be enabled at primary side.
To check the force logging status at primary side:
select name, force_logging from v$database;
to enable force logging:
alter database force logging;
Step 3:- Standby Redo Logs
SRL at primary side are optional. These will be used when we perform switchover.
SRL files size must be same as OLR files. If we have 3 ORL files at least 4 SRL must be configured.
To check the count and size of Online Redo Log Files:
select a.group#, a.status, a.bytes/1024/1024 SizeMB, b.member
from v$log a, v$logfile b
where a.group# = b.group#
order by group#;
Here we can see 3 Online Redo Log files with each 10 Mb size. So we need to create atleast 4 standby redo log files with 10 mb each. The advantage of creating these 4 files at primary side is when we use RMAN duplicate command this will create same standby redo logs at standby side also.
alter database add standby logfile
group 11 ('C:\APP\LENOVO\ORADATA\ORCL\standby_redo01.log')
size 10m;
alter database add standby logfile
group 12 ('C:\APP\LENOVO\ORADATA\ORCL\standby_redo02.log')
size 10m;
alter database add standby logfile
group 13 ('C:\APP\LENOVO\ORADATA\ORCL\standby_redo03.log')
size 10m;
alter database add standby logfile
group 14 ('C:\APP\LENOVO\ORADATA\ORCL\standby_redo04.log')
size 10m;
To check the status for above standby redo log files:
select * from v$standby_log;
To choose important 3 columns from the above dynamic view:
select group#, bytes/1024/1024 "Mb", status from v$standby_log;
Step 4:- Setup FRA (Fast / Flash Recovery Area)
To configure FRA we need to set below 2 dynamic parameters.
show parameter db_recovery_file_dest;
NAME TYPE VALUE
-------------------------- ----------- ---------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer
To setup above 2 parameters we need to first set the size then location.
alter system set db_recovery_file_dest_size=10g;
alter system set db_recovery_file_dest=C:\app\Lenovo\flash_recovery_area;
show parameter db_recovery_file_dest;NAME TYPE VALUE
-------------------------- ----------- ---------------------------------
db_recovery_file_dest string C:\app\Lenovo\flash_recovery_area
db_recovery_file_dest_size big integer 10G
Note:- While creating initStandby.ora file for standby we need to set these 2 parameters for standby fra.
Step 5:- Some important prameters
db_name
This is db name (8 characters), must be same in all instances. Max length 8 characters.
db_unique_name
For all the above instances with same db_name, we can use this parameter to set different unique names. This must be different on primary and standby. Max length 30 characters. With this parameter we can create location specific alias to each database.
alter system set db_unique_name='hyd' scope=spfile;
|
Parameter
|
Primary
|
Physical Standby
|
|
Instance Name
|
Prod
|
Std
|
|
DB_NAME
|
Prod
|
Prod
|
|
DB_UNIQUE_NAME
|
Hyd
|
Pune
|
|
Net Service Name
|
ToHyd
|
ToPune
|
log_archive_config
You can enable or disable sending/receiving redo logs to/from databases.
Default are SEND, RECEIVE, NODG_CONFIG
alter system set log_archive_config= 'DG_CONFIG=(pune,hyd)' scope=both;
log_archive_dest_n
Here n can be from 1 to 31. We can configure 31 destinations for archive redo data. With this we also need to configure Location or Service attributes.
Location and Service
To point this parameter to normal location:
alter system set log_archive_dest_1='LOCATION=/u01/prod/arch';
To point this parameter to Fra:
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
To configure Service attribute:
alter system set log_archive_dest_2='SERVICE=topune db_unique_name=pune';
valid_for
valid_for (redo_log_type, database_role)
redo_log_type options :- online_logfile, standby_logfile, all_logfiles
database_role options :- primary_role, standby_role, all_roles
Sync and Async
This is for redo transport mode. This is used in max protection and max availability mode.
alter system set log_archive_dest_2='SERVICE=topune LGWR SYNC db_unique_name=pune';
Async is default:-
alter system set log_archive_dest_2='SERVICE=topune LGWR ASYNC db_unique_name=pune';
Affirm and NoAffirm
Here default is Affirm.
alter system set log_archive_dest_2='SERVICE=topune SYNC AFFIRM DB_UNIQUE_NAME=pune';
select affirm from v$archive_dest where dest_id=2;
Delay
This attribute is used to set a delay between the primary and standby databases.
alter system set log_archive_dest_2='SERVICE=topune delay=10
db_unique_name=pune';
select delay_mins, destination from v$archive_dest where dest_id=2;
This attribute will be ignored when we use real time apply. Also we can NODELAY option in the managed recovery command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
log_archive_dest_state_n
Here n is from 1 to 31. This is the state of the redo transport for respective destination. The default value is ENABLE.
alter system set log_archive_dest_state_2 = 'defer';
show parameter log_archive_dest_state_2;
While configuring failover the value for this parameter will be ALTERNATE.
Parameters to set on Standby database
FAL_SERVER
fal_server=tohyd
to_hyd is the net service name of the primary database.
STANDBY_FILE_MANAGEMENT
standby_file_management=auto (manual)
alter system set standby_file_management='AUTO';
If this parameters value is AUTO, it will create or drop datafiles at standby side automatically.
DB_FILE_NAME_CONVERT
alter system set db_file_name_convert= "'/u01/prod/hyd', '/u01/prod/pune'" scope=spfile;
No comments:
Post a Comment