Oracle Data Guard Configuration ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that
create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard
maintains these standby databases as transitionally consistent copies of the production database. Then, if the production database becomes unavailable because of a
planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can
be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
With Data Guard Configuration, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby
systems.
1.In the standby db have only the Oracle software not the database.
2.In the Primary server side database must be open and database should be in archivelog mode.
3.Both machine IP Address must be set on primary and standby server.
On the primary side ping standby machine.
# ping 192.168.1.105
On the standby side ping primary machine.
# ping 192.168.1.104
Note:- requirement both operating system must be same in version also.
On standby if database exist then remove files from below directories.
/u01/app/oracle/flash_recovery_area/
/u01/app/oracle/oradata/
/u01/app/oracle/admin/ (remove all adump,pfile,cdump,udump,dbdump)
$ORACLE_HOME/dbs/
$ORACLE_HOME/dbs/network/admin/ (remove all file except the sample folder.)
Primary database must be in Archivelog mode.
> create pfile='$ORACLE_HOME/dbs/pfile/initprimary.ora' from spfile;
> shu immediate
(after shutdown primary instance open the initprimary.ora parameter file of primary side .)
Modify primary machine parameters.
db_unique_name='PRIMARY'
FAL_Client='to_primary'
FAL_Server='to_standby'
Log_archive_config='DG_CONFIG=(primary,standby)'
Log_archive_dest_1='Location=c:\oracle\backup
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=primary'
Log_archive_dest_2='Service=to_standby
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=standby'
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=DEFER
Service_names='primary'
Standby_File_Management='AUTO'
and save it
>host
]$ vi $ORACLE_HOME/dbs/initprimary.ora
shift+g( to goto the last line and paste parameter change the parameter in path)
log_archive_dest_1 ="location=use_db_recovery_file_dest"
esc shift:wq
]$ exit
> show parameter log_archive
> alter system set log_archive_dest_state_2=enable;
> alter system set log_archive_dest_1="location=use_db_recovery_file_dest";
> show parameter log_archive
> host
]$ export ORACLE_SID=primary
]$ sqlplus
user name:sys/sys as sysdba
connected to an idle instance.
> startup pfile=$ORACLE_HOME/dbs/initprimary.ora
Check the path of the datafiles
> select name from v$datafile;
> shu immediate
Create the same directory structure on the standby machine.
ADMIN->ORCL->PFILE,BDUMP,CDUMP,UDUMP,ADUMP,DBDUMP
FLASH_RECOVERY_AREA->ORCL
ORADATA->ORCL
Primary side
> host
]$ scp $ORACLE_BASE/oradata/orcl/*.dbf 192.168.1.105:$ORACLE_BASE/oradata/orcl/
are you sure: yes
password :oracle (of the standby machine where you want to copy the datafile.)
Now make the password file on the both machine primary and standby.
On primary
]$ orapwd file=$ORACLE_HOME/dbs/orapwprimary password=sys
On standby
]$ orapwd file=$ORACLE_HOME/dbs/orapwstandby password=sys
On primary
]$ exit
> alter database add standby logfile '$ORACLE_HOME/dbs/standbyredo01.log' size 150m;
note: redo is generate for the standby database.
> host
]$ scp $ORACLE_HOME/dbs/standbyredo01.log 192.168.1.105:$ORACLE_BASE/oradata/orcl/
password: oracle (standby machine password)
]$ exit
> shu immediate
> startup mount pfile=$ORACLE_HOME/dbs/initprimary.ora
> alter database create standby controlfile as '$ORACLE_HOME/dbs/standbycontrol01.ctl';
> host
]$ scp $ORACLE_HOME/dbs/standbycontrol01.ctl 192.168.1.105: $ORACLE_BASE/oradata/orcl/control01.ctl
password:oracle
]$ scp $ORACLE_HOME/dbs/standbycontrol01.ctl 192.168.1.105: $ORACLE_BASE/oradata/orcl/control02.ctl
pasword:oracle
]$ scp $ORACLE_HOME/dbs/standbycontrol01.ctl 192.168.1.105: $ORACLE_BASE/oradata/orcl/control03.ctl
pasword:oracle
]$ exit
> create spfile from pfile='$ORACLE_HOME/dbs/initprimary.ora';
> shu immediate
> startup
Now rename the pfile name from this path
/u01/app/oracle/admin/orcl/pfile/init.ora (we have to set)
> host
]$ scp $ORACLE_BASE/admin/orcl/init.ora 192.168.1.105:$ORACLE_BASE/admin/orcl/pfile/
On standby
]$ vi $ORACLE_BASE/admin/orcl/pfile/init.ora
db_unique_name='STANDBY'
FAL_Client='to_standby'
FAL_Server='to_primary'
Log_archive_config='DG_CONFIG=(primary,standby)'
Log_archive_dest_1='Location=c:\oracle\backup
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=standby'
Log_archive_dest_2='Service=to_primary
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=primary'
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names='STANDBY'
Standby_File_Management='AUTO'
Now set the SID of standby machine and open to the mount stage.
]$ export ORACLE_SID=standby
]$ sqlplus
sys/sys as sysdba
connected to the idle instance.
> startup pfile='$ORACLE_BASE/admin/orcl/pfile/init.ora' mount
Create the listener on the standby machine and primary machine
>host
]$ netmgr
global database name :standby
sid :standby
]$ lsnrctl stop and start
on primary side
____________________
]$ netmgr
global database name :primary
sid :primary
]$ lsnrctl stop and start
Set the netca service on primary machine and standby machine
On primary side
]$ netca
naming method configuration
use local naming
local net service name configuration
service:standby
host :192.168.1.105
yes perform a test
change login
system
sys
net service name:to_standby
On standby machine
]$ netca
naming method configuration
use local naming
local net service name configuration
service:primary
host :192.168.1.104
yes perform a test
change login
system
sys
net service name:to_primayOn standby machine
]$ sqlplus
> sys/sys@primary as sysdba
> select name from v$database;
> select database_role from v$database;
On primary side
]$ sqlplus
> sys/sys@standby as sysdba
> select database_role from v$database;
> exit
]$ export ORACLE_SID=primary
]$ sqlplus
sys/sys as sysdba
> startup
On standby side
]$ export ORACLE_SID=standby
]$ sqlplus
sys/sys as sysdba
connected to an idle instance.
> create spfile from pfile='$ORACLE_BASE/admin/orcl/pfile/init.ora';
>startup mount
> alter database force logging;
On primary side
>alter system switch logfile;
>/
> select error from v$archive_dest;
> select count(*) from v$archive_log;
> alter database force logging;
Standby database now to come in recovery mode
> alter database recover managed standby database disconnect from session;
To cancel the MRP (Managed recovery process) on standby side to
open the database in read only mode and at this time standby db will not apply any
archived log.
> recover managed standby database cancel;
For creating Logical standby you can click on LOGICAL STANDBY DATABASE
How to Convert Physical Standby To Snapshot Standby?
Or you can go to oracle-base site.
Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.
Pingback: LOGICAL STANDBY DATABASE