Data Guard Interview Questions
What are the advantages in using
Oracle Data Guard?
High
Availability.
Data
Protection.
Off-loading
Backup operation to standby database.
Automatic
Gap detection and Resolution in standby database.
Automatic
Role Transition using Data Guard Broker.
What are the different services
available in Oracle Data Guard?
Following
are the different Services available in Oracle Data Guard of Oracle database.
Redo
Transport Services.
Log Apply
Services.
Role
-Transitions.
What are the different Protection
modes available in Oracle Data Guard?
Below are
the protection modes available in DG
Maximum
Protection
Maximum
Availability
Maximum
Performance => This is the default protection mode. It provides the
highest level of data protection that is possible without affecting the
performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by
those transactions has been written to the online log.
How to check what protection mode
of primary database in your Oracle Data Guard?
SELECT PROTECTION_MODE FROM V$DATABASE;
How to change protection mode in
Oracle Data Guard setup?
ALTER
DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION | PERFORMANCE |
AVAILABILITY];
What are the advantages of using a
Physical standby database in Oracle Data Guard?
High
Availability.
Load
balancing (Backup and Reporting).
Data
Protection.
Disaster Recover.
What is the usage of
DB_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
DB_FILE_NAME_CONVERT This
parameter is used when you are using different directory
structure in standby database compare to primary database data files
location & also when we duplicate database this parameter can be used to generate files in a different location.
What are the services required on
the primary and standby data-base?
The
services required on the primary database are:
Log Writer Process (LGWR): Collects redo information and
updates the online redo logs. It can also create local archived redo logs and
transmit online redo to standby databases.
Archiver Process (ARCn): One or more archiver processes
make copies of online redo logs either locally or remotely for standby
databases.
Fetch Archive Log (FAL) Server: Services requests for archive
redo logs from FAL clients running on multiple standby databases. Multiple FAL
servers can be run on a primary database, one for each FAL request.
Log network server (LNS): LNS is used on the primary to
initiate a connection with the standby database.
The
services required on the standby database are:
Fetch Archive Log (FAL) Client: Pulls archived redo log files
from the primary site. Initiates transfer of archived redo logs when it detects
a gap sequence.
Remote File Server (RFS): Receives archived and/or
standby redo logs from the primary database.
Archiver (ARCn) Processes: Archives the standby redo logs
applied by the managed recovery process (MRP).
Managed Recovery Process (MRP): applies archive redo log
information to the standby database.
It controls
the automated transfer of redo data from the production database to one or more
archival destinations.
What is RTS (Redo Transport
Services) in Data-guard?
Transmit
redo data from the primary system to the standby systems in the configuration.
Manage the
process of resolving any gaps in the archived redo log files due to a network
failure.
Automatically
detect missing or corrupted archived redo log files on a standby system and
automatically retrieve replacement archived redo log files from the primary
database or another standby database.
Control the
automated transfer of redo data from a database destination to one or more
destinations. Redo transport services also manage the process of resolving any
gaps in the archived redo log files due to a network failure.
How to delay the application of
logs to a physical standby?
A standby
database automatically applies redo logs when they arrive from the primary
database. But in some cases, we want to create a time lag between the archiving
of a redo log at the primary site, and the application of the log at the
standby site.
Modify the
Log_Archive_Dest_n initialization parameter on the primary database to set
a delay for the standby database.
Example: For
60min Delay:
ALTER SYSTEM
SET LOG_ARCHIVE_DEST_2=’SERVICE=stdby_srvc DELAY=60′;
The DELAY
attribute is expressed in minutes.
The archived
redo logs are still automatically copied from the primary site to the standby
site, but the logs are not immediately applied to the standby database. The
logs are applied when the specified time interval expires.
Oracle Data Guard Interview Questions
How many standby databases we can
create (in 10g/11g)?
What are differences between physical, logical, snapshot standby and
ADG (or) what are different types of standby databases?
Physical standby – in MOUNT STATE, MRP proves
will apply the archives
Active Dataguard – in READ ONLY state, MRP will
apply the archives
Logical standby – in READ ONLY state, LSP will run
Snapshot standby databases – Physical standby
database can be converted to snapshot standby database, which will be in READ
WRITE mode, can do any kind of testing, then we can convert back snapshot
standby database to physical standby database and start MRP which will apply
all pending archives.
What are the parameters we’ve to set
in primary/standby for Data Guard?
DB_UNIQUE_NAME
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_MAX_PROCESSES
DB_CREATE_FILE_DEST
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOGARCHIVE_DEST_STATE_n
FAL_SERVER
FAL_CLIENT
STANDBY_FILE_MANAGEMENT
What is the use of fal_server &
fal_client, is it mandatory to set these?
FAL_SERVER
specifies
the FAL (fetch archive log) server for a standby database. The value is an
Oracle Net service name, which is assumed to be configured properly on the
standby database system to point to the desired FAL server.
FAL_CLIENT
specifies
the FAL (fetch archive log) client name that is used by the FAL service,
configured through the
FAL_SERVER
initialization parameter, to refer to the FAL client.
The value is
an Oracle Net service name, which is assumed to be configured properly on the
FAL server system to point to the FAL client (standby database).
How to find out backlog of standby?
select
round((sysdate - a.NEXT_TIME)*24*60) as "Backlog",m.SEQUENCE#-1
"Seq Applied",m.process, m.status
from
v$archived_log a, (select process,SEQUENCE#, status from v$managed_standby
where process like '%MRP%')m where a.SEQUENCE#=(m.SEQUENCE#-1);
If you didn't have access to the
standby database and you wanted to find out what error has occurred in a data
guard configuration, what view would you check in the primary database to check
the error message?
You can
check the v$dataguard_status view.
select
message from v$dataguard_status;
How can u recover standby which far
behind from primary (or) without archive logs how can we make standby sync?
By using
RMAN incremental backup.
What is snapshot standby (or) How can we give a physical standby to
user in READ WRITE mode and let him do updates and revert to standby?
Till Oralce 10g, create guaranteed restore point, open in read write, let him do
updates, flashback to restore point, start MRP.
From Oracle 11g, convert physical standby to
snapshot standby, let him do updates, convert to physical standby, start MRP.
What
is active data guard? Does it need additional licensing?
Active
dataguard means, the standby database is open with read only mode, when redo
logs are getting applied in real time.
Below are
the benefit of using active dataguard.
Reporting
queries can be offloaded to standby database.
Physical
block corruptions are repaired automatically either at primary or physical
standby database.
RMAN backups
can be initiated from standby , instead of primary which will reduce CPU load
from primary.
What is active dataguard duplicate?
Starting
from 11g we can duplicate database by two way 1) Active DB duplicate 2)
Backup-based duplicate.
Active DB
duplicate copies the live TARGET DB over the network to the AUXILLARY
destination and then create the duplicate database. In an active
duplication process, target database online image copies and archived
redo log files were copied through the auxiliary instance service name. So,
there is no need of target db backup.
Q 1
What is data guard in
simple language?
A 1
Your primary database is running, and you want
to reduce downtime because of unplanned outages. You create a replica of this
primary database (termed as standby database).
You regularly ship redo generated in the primary database to standby database and apply it there. So that is our ‘Data Guard’ standby database and it is in a continuous state of recovery, validating and applying redo to remain in sync with the primary database.
You regularly ship redo generated in the primary database to standby database and apply it there. So that is our ‘Data Guard’ standby database and it is in a continuous state of recovery, validating and applying redo to remain in sync with the primary database.
Q 2
Your standby database
was out of reach because of network issue. How will you synchronize it with
primary database again?
A 2
Data Guard automatically resynchronizes the
standby following network or standby outages using redo data that has been
archived at the primary.
Q 3
What is Redo Transport
Services (RTS)?
A 3
This process takes care of the transmission of
redo from a primary database to the standby database.
Below is how Redo Transport Services (RTS)
works:
1) Log Network Server (LNS) reads redo
information from the redo buffer in SGA of PRIMARY Database
2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database
3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database
4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database
2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database
3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database
4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database
** For multi-standby configuration, the
primary database has a separate LNS process for each standby database.
** Two redo transport methods are supported with the LNS process: synchronous (SYNC) or asynchronous (ASYNC).
** Two redo transport methods are supported with the LNS process: synchronous (SYNC) or asynchronous (ASYNC).
Q 4
What is the difference
between SYNC and ASYNC redo transport method?
A 4
Synchronous transport
(SYNC)
Also known as a “zero data loss” redo
transport method.
Below is how it works:
1) Log Network Server (LNS) reads redo
information from the redo buffer in SGA of PRIMARY Database
2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database
3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database
4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database
5) Remote File Server (RFS) transmits an acknowledgement back to the LNS process on the primary database
6) Log Network Server (LNS) notifies the LGWR that transmission is complete on the primary database.
7) Log Writer (LGWR) acknowledges the commit to the user.
2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database
3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database
4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database
5) Remote File Server (RFS) transmits an acknowledgement back to the LNS process on the primary database
6) Log Network Server (LNS) notifies the LGWR that transmission is complete on the primary database.
7) Log Writer (LGWR) acknowledges the commit to the user.
Asynchronous transport
(ASYNC)
Unlike SYNC, Asynchronous transport (ASYNC)
eliminates the requirement that the LGWR wait for acknowledgement from the LNS.
This removes the performance impact on the
primary database irrespective of the distance between primary and standby locations.
primary database irrespective of the distance between primary and standby locations.
So if the LNS is unable to keep pace and the
log buffer is recycled before the redo can be transmitted to the standby, the
LNS automatically transitions to reading and sending from the Online Redo logs.
Once the LNS is caught up, it automatically transitions back to reading &
sending directly from the log buffer.
Below is how it works:
1) Log Network Server (LNS) reads redo
information from the redo buffer in SGA of PRIMARY Database
2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database
3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database
4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database
2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database
3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database
4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database
so step 5, 6 & 7 as discussed above for
SYNC are not applicable here.
The only drawback of ASYNC is the increased potential
for data loss. Say a failure destroyed the primary database before any
transport lag was reduced to zero, this means any committed transactions that
were a part of the transport lag will be lost. So it is highly advisable to
have enough network bandwidth to handle peak redo
generation rates when using ASYNC method.
generation rates when using ASYNC method.
Q 5
How Synchronous
transport (SYNC) can impact the primary database performance?
A 5
SYNC guarantees protection for every
transaction that the database acknowledges as having been committed but at the
same time LGWR must wait for confirmation that data is protected at the standby
before it can proceed with the next transaction. It can impact primary database
performance and it depends on factors like
> the amount of redo information to be
written
> available network bandwidth
> round-trip network latency (RTT)
> standby I/O performance writing to the SRL.
> distance betweeen primary and standby databases as network RTT increases with distance.
> available network bandwidth
> round-trip network latency (RTT)
> standby I/O performance writing to the SRL.
> distance betweeen primary and standby databases as network RTT increases with distance.
Q 6
What is Data Guard’s
Automatic Gap Resolution?
A 6
Your database is using ASYNC transport method
and the instance load is at the peak. The LNS is unable to keep pace and the
log buffer is recycled before the redo can be transmitted to the standby, the
LNS automatically transitions to reading and sending from the Online Redo logs.
Once the LNS is caught up, it automatically transitions back to reading &
sending directly from the log buffer.
Now in some cases there can be two or more log
switches before the LNS has completed sending the redo information from online
redo log files and in meantime if any such required online redo log files were
archived then those redo information will be transmitted via Data Guard’s gap
resolution process “Automatic Gap Resolution”.
OR
In some other case when your network or the
standby database is down and your primary system is one busy system, so before
the connection between the primary and standby is restored, a large log file
gap will be formed.
Automatic Gap Resolution will take care of such scenarios by following below action plan:
Automatic Gap Resolution will take care of such scenarios by following below action plan:
1) ARCH process on the primary database
continuously ping the standby database during the outage to determine its
status.
2) As soon as the standby is restored, the ARCH ping process queries the standby control file (via its RFS process) to determine the last complete log file that the standby received from the primary database.
3) Data Guard determines which log files are required to resynchronize the standby database and immediately begins transmitting them using additional ARCH processes.
4) LNS process at primary database will also attempt and succeed in making a connection to the standby database and will begin transmitting current redo. So first all the ARCH files are applied and then current redo log.
2) As soon as the standby is restored, the ARCH ping process queries the standby control file (via its RFS process) to determine the last complete log file that the standby received from the primary database.
3) Data Guard determines which log files are required to resynchronize the standby database and immediately begins transmitting them using additional ARCH processes.
4) LNS process at primary database will also attempt and succeed in making a connection to the standby database and will begin transmitting current redo. So first all the ARCH files are applied and then current redo log.
The Data Guard architecture enables gaps to be
resolved quickly using multiple background ARCH processes
Q 7
What is the difference between Physical standby and
Logical standby database?
A 7
Data Guard Apply process in standby database
can apply redo information directly and, in that case, it will be called
physical standby.
OR It can apply SQL and, in that case, it will be called Logical standby.
OR It can apply SQL and, in that case, it will be called Logical standby.
Physical Standby:
In this case standby database is an exact,
block-by-block, physical replica of the primary database.
The change vectors received by RFS process are directly applied to the standby database by using media recovery.so here the apply process read data blocks, assemble redo changes from mappings, and then apply redo changes to data blocks directly.
Physical Standby is the best choice for disaster recovery (DR) based upon their simplicity, transparency, high performance, and good data protection.
The change vectors received by RFS process are directly applied to the standby database by using media recovery.so here the apply process read data blocks, assemble redo changes from mappings, and then apply redo changes to data blocks directly.
Physical Standby is the best choice for disaster recovery (DR) based upon their simplicity, transparency, high performance, and good data protection.
Logical Standby:
In this case standby database uses SQL Apply
method to “mine” the redo by converting it to logical change records, and then
building SQL transactions and applying SQL to the standby database.
As this process of replaying the workload is
more complex than the Physical Standby’s process, so it requires more memory,
CPU, and I/O.
One good advantage here is that a logical
standby database can be opened read-write while SQL Apply is active which means
you can update (create/insert/delete etc) local tables and schemas in the
logical standby database.
Q 8
How is Data Guard
Apply process works if primary and secondary database involves Oracle RAC?
A 8
If Primary database is RAC but standby is
Non-RAC:
Each primary Oracle RAC instance ships its own
thread of redo that is merged by the Data Guard apply process at the standby
and applied in SCN order to the standby database.
If both Primary and standby databases are RAC:
If both Primary and standby databases are RAC:
If the standby is also an Oracle RAC database,
only one instance (the apply instance) will merge and apply changes to the
standby database. If the apply instance fail for any reason, the apply process
will automatically failover to a surviving instance in the Oracle RAC standby
database when using the Data Guard broker.
Q 9
What is Active Data
Guard Option (Oracle Database 11g Enterprise Edition)?
A 9
For physical standby database, prior to 11g,
the database would have to be in the mount state when media recovery was active
which means you were not able to query the standby database during media
recovery stage as there was no read-consistent view.
Active Data Guard 11g features solves the read
consistency problem by use of a “query” SCN. The media recovery process on the
standby database will advance the query SCN after all the changes in a
transaction have been applied . The query SCN will appear to user as the CURRENT_SCN
column in the V$DATABASE view on the standby database. So Read-only users will
only be able to see data up to the query SCN, and hence guaranteeing the same
read consistency as the primary database.
This enables a physical standby database to be
open as read-only while media recovery is active, making it useful for doing
read-only workloads.
Also, if you need read-write access to the
standby database, you can use SQL Apply method of dataguard.
Q 10
What are the important database parameters related to
Data Guard corruption prevention?
A 10
On the primary
database:
a) DB_ULTRA_SAFE
Values can be DATA_AND_INDEX or DATA_ONLY.
Setting DB_ULTRA_SAFE at the primary will also automatically set DB_
LOST_WRITE_PROTECT=TYPICAL on the primary database.
In Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.
In Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.
On the standby
database:
a) DB_BLOCK_CHECKSUM=FULL
DB_BLOCK_CHECKSUM detects redo and data block
corruptions and detect corruptions on the primary database and protect the
standby database. This parameter requires minimal CPU resources.
b) DB_LOST_WRITE_PROTECT=TYPICAL
A lost write can occur when an I/O subsystem acknowledges the completion of a write, while in fact the write did not occur in persistent storage.
This will create a stale version of the data block. When the DB_LOST_WRITE_PROTECT initialization parameter is set, the database records buffer cache block reads in the redo log, and this information is used to detect lost writes.
A lost write can occur when an I/O subsystem acknowledges the completion of a write, while in fact the write did not occur in persistent storage.
This will create a stale version of the data block. When the DB_LOST_WRITE_PROTECT initialization parameter is set, the database records buffer cache block reads in the redo log, and this information is used to detect lost writes.
You set DB_LOST_WRITE_PROTECT to TYPICAL in
both primary and standby databases.
Q 11
What are different
Data Guard protection modes?
A 11
Data Guard protection modes implement rules
that controls how the configuration will respond to failures, enabling you to
achieve specific objectives for data protection, availability, and performance.
a) Maximum Performance
– emphasis is on primary database performance
over data protection.
– requires ASYNC (the default method) redo transport so that the LGWR process never waits for acknowledgment from the standby database.
– network connection between primary and standby OR the availability of the standby database DO NOT IMPACT the primary database performance
– requires ASYNC (the default method) redo transport so that the LGWR process never waits for acknowledgment from the standby database.
– network connection between primary and standby OR the availability of the standby database DO NOT IMPACT the primary database performance
b) Maximum
Availability
– first emphasis is on availability and second
priority is zero data loss protection.
– requires SYNC redo transport so primary database performance may be impacted in waiting for acknowledgment from the standby (it doesn’t mean indefinite wait in case standby database fails, maximum wait will be equal to parameter NET_TIMEOUT seconds).
– requires SYNC redo transport so primary database performance may be impacted in waiting for acknowledgment from the standby (it doesn’t mean indefinite wait in case standby database fails, maximum wait will be equal to parameter NET_TIMEOUT seconds).
c) Maximum Protection
– utmost priority is on data protection.
– also requires SYNC redo transport.
– unlike ‘Maximum Availability’ it does not consider the NET_TIMEOUT parameter, which means If the primary does not receive acknowledgment from a SYNC standby database, it will stall primary and eventually abort it, preventing any unprotected commits from occurring.
– highly recommended to use a minimum of two SYNC standby databases at different locations if using ‘Maximum Protection’ to have high availability of primary database.
– also requires SYNC redo transport.
– unlike ‘Maximum Availability’ it does not consider the NET_TIMEOUT parameter, which means If the primary does not receive acknowledgment from a SYNC standby database, it will stall primary and eventually abort it, preventing any unprotected commits from occurring.
– highly recommended to use a minimum of two SYNC standby databases at different locations if using ‘Maximum Protection’ to have high availability of primary database.
Q 12
What is Switchover event?
What is Switchover event?
A 12
Switchover is useful for minimizing downtime
during planned maintenance. It is a planned event in which Data Guard reverses
the roles of the primary and a standby database.
The primary database runs unaffected while we
are making the required changes on our standby database (e.g. patchset
upgrades, full Oracle version upgrades, etc).
Once changes are complete, production is switched over to the standby site running at the new release.
Once changes are complete, production is switched over to the standby site running at the new release.
This means regardless of how much time is
required to perform planned maintenance, the only production database downtime
is the time required to execute a switchover, which can be less than 60 seconds
Below operations happens when switchover command is executed:
Below operations happens when switchover command is executed:
1. primary database is notified that a
switchover is about to occur.
2. all users are disconnected from the primary.
3. a special redo record is generated that signals the End Of Redo (EOR).
4. primary database is converted into a standby database.
5. the final EOR record is applied to standby database, this guarantees that no data has been lost, and it converts the standby to the primary role.
2. all users are disconnected from the primary.
3. a special redo record is generated that signals the End Of Redo (EOR).
4. primary database is converted into a standby database.
5. the final EOR record is applied to standby database, this guarantees that no data has been lost, and it converts the standby to the primary role.
Q 13
What is Failover event?
A 13
The Failover process is like switchover event
except that the primary database never has the chance to write an EOR record as
this is an unplanned event.
Whether or not a failover results in data loss
depends upon the Data Guard protection mode:
a) Maximum Protection >>
No Data Loss
b) Maximum Availability >>
No Data Loss (except when there was a previous failure (e.g. a network failure)
that had INTERRUPTED REDO TRANSPORT and allowed the primary database to move
ahead of standby)
c) Maximum Performance (ASYNC) >>
may lose any committed transactions that were not transmitted to the standby
database before the primary database failed.
Failover event can be of two types:
1) Manual
Administrator have complete control of
primary-standby role transitions. It can lengthen the outage by the amount of
time required for the administrator to be notified and manual execution of
command.
2) Automatic
It uses Data Guard’s Fast-Start Failover
feature which automatically detects the failure, evaluates the status of the
Data Guard configuration, and, if appropriate, executes the failover to a
previously chosen standby database.
Q 14
Which tools can be used for Data Guard Management?
A 14
1) SQL*Plus – traditional method, can prove
most tedious to use
2) Data Guard broker – automates and centralizes the creation, maintenance, and monitoring of a Data Guard configuration. Simplifies and automates many administrative tasks. It has its own command line (DGMGRL) and syntax.
3) Enterprise Manager – requires that the Data Guard broker be enabled. a GUI to the Data Guard broker, replacing the DGMGRL command line and interfacing directly with the broker’s monitor processes.
2) Data Guard broker – automates and centralizes the creation, maintenance, and monitoring of a Data Guard configuration. Simplifies and automates many administrative tasks. It has its own command line (DGMGRL) and syntax.
3) Enterprise Manager – requires that the Data Guard broker be enabled. a GUI to the Data Guard broker, replacing the DGMGRL command line and interfacing directly with the broker’s monitor processes.
Q 15
What is Data Guard 11g
snapshot standby?
A 15
With 11g, you can thoroughly test your changes
on a true replica of your production system and database using actual
production workload.
Data Guard 11g physical standby can now be converted to a snapshot standby, independent of the primary database, that is open read-write and able to be used for preproduction testing. It uses Flashback Database and sets a guaranteed restore point (GRP) at the SCN before the standby was open read-write.
Data Guard 11g physical standby can now be converted to a snapshot standby, independent of the primary database, that is open read-write and able to be used for preproduction testing. It uses Flashback Database and sets a guaranteed restore point (GRP) at the SCN before the standby was open read-write.
NOTE: Primary database redo continues to be
shipped to a snapshot standby, and while not applied, it is archived for later
use.
You can convert this snapshot database back
into a synchronized physical standby database when testing is complete. Redo
Apply process at standby will take care that all
primary database redo archived while a snapshot standby is applied until it is caught up with the primary database.
primary database redo archived while a snapshot standby is applied until it is caught up with the primary database.
Q 16
What is the difference between Recovery Point
Objective(RPO) and Recovery Time Objective (RTO)?
A 16
A) Recovery Point
Objective(RPO)
RPO concerns with data. It is the amount of
data you are willing to lose when the failure occurs in your database system.
Usually people define data loss in terms of time, so possible values can be 5
seconds of data loss, 2 hours of data loss etc.
Remember that each standby database has its
own set of attributes and parameters. It means you can mix zero data loss
standby databases with minimal data loss standby
databases in the same Data Guard configuration
databases in the same Data Guard configuration
If you have decided that you want to implement
zero data loss strategy, then you should really focus on Networks and Data Loss
B) Recovery Time Objective (RTO)
B) Recovery Time Objective (RTO)
RTO is defined as how fast you can get back up
and running (whereas RPO is concerned with data loss)
So with your RPO strategy you lost say only
about 6 seconds of data as you committed to your client but with RTO you need
to formulate how fast clients can connect back to the database system after the
data loss has occurred.
Q 17
What is Standby Redo
Log (SRL) files?
A 17
The SRL files are where the Remote File Server
(RFS) process at your standby database writes the incoming redo so that it is
persistent on disk for recovery. SRL files are important for better redo
transport performance and data protection.
SRL are MUST in Maximum Availability or
Maximum Protection mode and OPTIONAL (but recommended) in Maximum Performance
mode.
If there are no Standby Redo Log (SRL) files,
then at each log switch in the primary database, the RFS process on the standby
database that is serving an asynchronous standby destination has to create an
archive log of the right size. While the RFS is busy doing creating the archive
log file, the LNS process at the primary database has to wait, getting further
and further behind the LGWR (in case of Maximum Performance mode). That is why
it recommended to have Standby Redo Log (SRL) files in Maximum Performance mode
also.
We generally configure them on our primary
database as well in preparation for a role transition b/w primary-standby.
Also, do not multiplex SRLs. Since Data Guard
will immediately request a new copy of the archive log if an SRL file fails,
there is no real need to have more than one copy of each.
Q 18
What is Fast Start Fail Over (FSFO)?
A 18
Main criticism of Oracle standby databases has
always been that too much manual interaction is required in case of disaster
situation. FSFO helps in filling up this requirement. FSFO quickly and
reliably fails over the target standby database to the primary database role,
without requiring you to perform any manual steps to invoke the failover.
Please keep in mind that you need to have Broker configuration done to be able
to use FSFO feature.
Q 19
What is the concept of
OBSERVER in Fast Start Fail Over ( FSFO)?
A 19
In normal scenario, If you have to perform a
switch over activity in your standby setup you keep some kind of
monitor/observation on your setup so that you are aware that when your primary
database is not available and you need to switch over to standby database.
Oracle helped in getting this manual observation activity by providing an
OBSERVER process which constantly monitors the availability of the Primary
database. Now, if we run OBSERVER on the primary or secondary database server
itself then their is risk that the OBSERVER itself will get down when that
server is down because of disaster. That is why observer is a separate OCI
client-side component that runs on a different computer from the primary and
standby databases.
So Once the observer is started, no
further user interaction is required. If both the observer and designated
standby database lose connectivity with the primary database for longer than
the number of seconds specified by the FastStartFailoverThreshold configuration
property, the observer will initiate a fast-start failover to the standby
database.
Q 20
What are the high
level steps for configuring Fast Start Fail Over (FSFO)?
A20
To configure FSFO in your Standby setup, broad
level steps will be:
STEP 1: Determine Which of the Available
Standby Databases is the Best Target for the Failover. Means you want to choose
physical over logical etc.
STEP 2: Specify the Target Standby Database with the FastStartFailoverTarget Configuration Property. This may not be required if you have only one standby database in configuration.
STEP 3: Determine the Protection Mode You Want . You will have to choose from either maximum performance or maximum availability. This is more of business decision and you will have to take consensus from all stakeholders on which mode will prove right for you.
STEP 4: Set the FastStartFailoverThreshold Configuration Property. This parameter tells how long (in seconds) OBSERVER process should wait before starting failover
STEP 5: Set Other Properties Related to Fast-Start Failover (Optional). There are some other parameters like FastStartFailoverAutoReinstate, ObserverOverride etc which can also be applicable to meet your specific requirements.
STEP 6: Enable Additional Fast-Start Failover Conditions (Optional). This step can give you some more options to define when you primary database is unusable example: stuck archiver, corrupt control file etc.
STEP 7: Enable FSFO Using DGMGRL or Cloud Control. This is the main step in which you will enable to FSFO.
STEP 8: Start the Observer. You can use Cloud Control or DGMGRL to start the observer process.
STEP 9: Verify the Fast-Start Failover Environment. DGMGRL command “SHOW FAST_START FAILOVER” can show you easily the status of FSFO.
STEP 2: Specify the Target Standby Database with the FastStartFailoverTarget Configuration Property. This may not be required if you have only one standby database in configuration.
STEP 3: Determine the Protection Mode You Want . You will have to choose from either maximum performance or maximum availability. This is more of business decision and you will have to take consensus from all stakeholders on which mode will prove right for you.
STEP 4: Set the FastStartFailoverThreshold Configuration Property. This parameter tells how long (in seconds) OBSERVER process should wait before starting failover
STEP 5: Set Other Properties Related to Fast-Start Failover (Optional). There are some other parameters like FastStartFailoverAutoReinstate, ObserverOverride etc which can also be applicable to meet your specific requirements.
STEP 6: Enable Additional Fast-Start Failover Conditions (Optional). This step can give you some more options to define when you primary database is unusable example: stuck archiver, corrupt control file etc.
STEP 7: Enable FSFO Using DGMGRL or Cloud Control. This is the main step in which you will enable to FSFO.
STEP 8: Start the Observer. You can use Cloud Control or DGMGRL to start the observer process.
STEP 9: Verify the Fast-Start Failover Environment. DGMGRL command “SHOW FAST_START FAILOVER” can show you easily the status of FSFO.
Oracle Data Guard Interview Questions and Answers
Q1. What is a Dataguard?
Ans: Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.
Q2.What are the uses of Oracle Data Guard?
Ans :
a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
b) 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.
c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.
Q3. What is Redo Transport Services?
Ans: It control the automated transfer of redo data from the production database to one or more archival destinations.
Redo transport services perform the following tasks:
a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.
Q4. What is apply services?
Ans: Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.
Q5. What is difference between physical and logical standby databases?
Ans: The main difference between physical and logical standby databases is the manner in
which apply services apply the archived redo data:
a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of
an Oracle database.
b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the
generated SQL statements on the logical standby database.
Ans: Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.
Q2.What are the uses of Oracle Data Guard?
a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
b) 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.
c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.
Q3. What is Redo Transport Services?
Ans: It control the automated transfer of redo data from the production database to one or more archival destinations.
Redo transport services perform the following tasks:
a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.
Q4. What is apply services?
Ans: Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.
Q5. What is difference between physical and logical standby databases?
Ans: The main difference between physical and logical standby databases is the manner in
which apply services apply the archived redo data:
a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of
an Oracle database.
b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the
generated SQL statements on the logical standby database.
Q6. What is Data Guard
Broker?
Ans: Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:
a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.
Q7. What are the Data guard Protection modes and summarize each?
Maximum availability :
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.
Maximum performance :
This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.
Maximum protection :
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.
Here are some additional Oracle Data Guard Interview Questions for newer versions of Oracle:
Ans: Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:
a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.
Q7. What are the Data guard Protection modes and summarize each?
Maximum availability :
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.
Maximum performance :
This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.
Maximum protection :
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.
Here are some additional Oracle Data Guard Interview Questions for newer versions of Oracle:
Q9. In Oracle 11g, what command in RMAN can you use to create
the standby database while the target database is active?
Ans: Oracle 11g has made it
extremely simple to set up a standby database environment because Recovery Manager (RMAN) now supports the ability
to clone the existing primary database directly to the intended standby
database site over the network via the DUPLICATE DATABASE command set while the target database is active. RMAN
automatically generates a conversion script in memory on the primary site and
uses that script to manage the cloning operation on the standby site with
virtually no DBA intervention required. You can execute this in a
run block in RMAN:
duplicate
target database for standby do recover from active database;
Q10. What additional standby database mode does Oracle 11g
offer?
Ans: Oracle 11g has introduced the Oracle Snapshot Standby
Database. In Snapshot Standby Database a physical standby database
can easily open in read-write mode and again you can convert it back to the
physical standby database. This is suitable for test and development
environments and maintains protection by continuing to receive data from the
production database and archiving it for later use.
Q11. In Oracle 11g how can speed up backups on the standby
database?
Ans: In Oracle 11g, block change tracking is now supported in the standby database.
Q12. With the availability of Active
Data Guard, what role does SQL Apply (logical standby) continue to play?
Ans: Use SQL Apply for the following requirements: (a) when you
require read-write access to a synchronized standby database but do not modify
primary data, (b) when you wish to add local tables to the standby database
that can also be updated, or (c) when you wish to create additional indexes to
optimize read performance. The ability to handle local writes makes SQL
Apply better suited to packaged reporting applications that often require write
access to local tables that exist only at the target database. SQL Apply also
provides rolling upgrade capability for patchsets and major database
releases. This rolling upgrade functionality can also be used by physical
standby databases beginning with Oracle 11g using Transient Logical Standby.
Q13. Why would I use Active Data Guard and not simply use
SQL Apply (logical standby) that is included with Data Guard 11g?
Ans: If read-only access satisfies the requirement – Active
Data Guard is a closer fit for the requirement, and therefore is much easier to
implement than any other approach. Active Data Guard supports all
datatypes and is very simple to implement. An Active Data Guard replica can
also easily support additional uses – offloading backups from the primary
database, serve as an open read-write test system during off-peak hours
(Snapshot Standby), and provide an exact copy of the production database for
disaster recovery – fully utilizing standby servers, storage and software while
in standby role.
Q14. Why do I need the Oracle 11g Active Data Guard
Option?
Ans: Previous capabilities did not allow Redo Apply to be
active while a physical standby database was open read-only and did not enable
RMAN block change tracking on the standby database. This resulted in (a)
read-only access to data that was frozen as of the time that the standby
database was opened read-only, (b) failover and switchover operations that could
take longer to complete due to the backlog of redo data that would need to be
applied, and (c) incremental backups that could take up to 20x longer to
complete – even on a database with a moderate rate of change. Previous
capabilities are still included with Oracle Data Guard 11g, no additional
license is required to use previous capabilities.
Q15. If you wanted to upgrade your current 10g physical
standby data guard configuration to 11g, can you upgrade the standby to
11g first then upgrade the primary ?
Ans: Yes, in Oracle 11g, you can temporarily convert the
physical standby database to a logical standby database to perform a rolling
upgrade. When you issue the convert command you need to keep the
identity:
alter
database recover logical standby keep identity;
Q16. If you have a low-bandwidth WAN network, what can you
do to improve the Oracle 11g data guard configuration in a GAP detected
situation?
Ans: Oracle 11g introduces the capability to compress redo log
data as it transports over the network to the standby database. It can be
enabled using the compression parameter. Compression becomes enabled only
when a gap exists, and the standby database needs to catch up to the primary
database.
alter
system set log_archive_dest_1=’SERVICE=DBA11GDR COMPRESSION=ENABLE’;
Q17. In an Oracle 11g Logical Standby Data Guard
configuration, how can you tell the dbms_scheduler to only run jobs in primary
database?
Ans: Oracle 11g, logical standby now provides support for
DBMS_SCHEDULER. It can run jobs in both primary and logical standby
database. You can use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to
set the database role. You can specify that the jobs can run only when
operating in that database role.
Q18. How can you control when an archive log can be deleted
in the standby database in oracle 11g ?
Ans: In Oracle 11g, you can control it by using the
log_auto_delete initialization parameter. The log_auto_delete parameter
must be coupled with the log_auto_del_retention_target parameter to specify the
number of minutes an archivelog is maintained until it is purged. Default is 24
hours. For archivelog retention to be effective, the log_auto_delete
parameter must be set to true.
Q19. Can Oracle Data Guard be used with Standard Edition of
Oracle ?
Ans: Yes and No. The automated features of Data
Guard are not available in the standard edition of Oracle. You can
still however, perform log shipping manually and write scripts to manually
perform the steps. If you are on unix platform, you can write
shell scripts that identify the logs and then use the scp or sftp command to
ship it to the standby server. Then on the standby server, identify which
logs have not been applied and apply/recover them maually and remove them once
applied.
Q20. What is the difference between Active Dataguard, and the
Logical Standby implementation of 10g dataguard?
Ans: Active dataguard is mostly about the physical standby.
Use
physical standby for testing without compromising protection of the production
system. You can open the physical standby read/write – do some destructive
things in it (drop tables, change data, whatever – run a test – perhaps with
real application testing). While this is happening, redo is still streaming
from production, if production fails – you are covered. Use physical standby
for reporting while in managed recovery mode. Since physical standby supports
all of the datatypes – and logical standby does not (11g added broader support,
but not 100%) – there are times when logical standby isn’t sufficient. It also
permits fast incremental backups when offloading backups to a physical standby
database.
Q4) What is the default
protection mode of the software?
The default mode in the software Data Guard is Maximum performance.
The default mode in the software Data Guard is Maximum performance.
Q5) What are advantages offered
by Maximum Performance protection mode?
The main advantage of this default mode is that the performance of the primary data is not affected while the highest possible level of protection for the data is ensured. The transactions begin to commit as soon as the redone data produced by the transactions get written on to the online log.
The main advantage of this default mode is that the performance of the primary data is not affected while the highest possible level of protection for the data is ensured. The transactions begin to commit as soon as the redone data produced by the transactions get written on to the online log.
Q6) What are the benefits of
maximum protection mode?
This protection mode comes to the rescue when the primary database fails. The primary database shuts down and stops processing the transaction when this protection mode is enabled. So, with this protection mode, you can be assured that there will be no data loss.
This protection mode comes to the rescue when the primary database fails. The primary database shuts down and stops processing the transaction when this protection mode is enabled. So, with this protection mode, you can be assured that there will be no data loss.
Q7) How is the maximum protection
mode enabled?
The redone data must get written in two places before committing the transaction- the online log and a standby database. This way the primary database can shut down, but the data remains intact.
The redone data must get written in two places before committing the transaction- the online log and a standby database. This way the primary database can shut down, but the data remains intact.
Q8) What are the advantages of
maximum availability protection mode?
The biggest advantage with one is that the availability of the primary database is uncompromised, but the maximum possible level of protection is offered to the data. The transactions with this protection mode only commit when all the redone data needed to recover the transactions get written to a standby database and the online log.
The biggest advantage with one is that the availability of the primary database is uncompromised, but the maximum possible level of protection is offered to the data. The transactions with this protection mode only commit when all the redone data needed to recover the transactions get written to a standby database and the online log.
Q9) How is the protection mode
changed?
The steps for changing the protection mode are as follows:
The steps for changing the protection mode are as follows:
1.
Go to the alter database option.
2. Set the standby database.
3. Select from the three available options of performance, availability and protection
2. Set the standby database.
3. Select from the three available options of performance, availability and protection
Q21) What are the disadvantages
that are associated with the Logical Standby systems?
The only major disadvantage that this one has is in the matters of types of tables and a few of the data functions. On the overall aspects, it is held as having an advantage on the physical database.
The only major disadvantage that this one has is in the matters of types of tables and a few of the data functions. On the overall aspects, it is held as having an advantage on the physical database.
Comments
Post a Comment