Below are some of the Oracle Standby Database Interview Questions. Datagaurd Interview Questions.
Q1) Why do you think using Data Guard by Oracle is helpful to our work?
The benefits of using the software are many in any environment dealing with data. It ensures that the data stays protected and easily available. You get to take the load off from backups to standby operations database. The problems like gaps in the standby database are automatically detected and resolved. The guard broker allows automated role transition.
Q2) Name the three top services offered by the Data Guard software?
The three top services offered by the software are:
1. Redoing the transport service
2. Applying of log service
3. Role transitional services
Q3) Name the three protection modes available in the software.
The three protection modes that are available include maximum availability, protection and performance.
Q4) What is the default protection mode of the software?
The default mode in the software Data Guard is Maximum performance.
Q5) What are advantages offered by Maximum Performance mode?
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.
Q6) What are the benefits of maximum protection mode?
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.
Q7) What are the advantages of maximum availability mode?
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.
Q8) How is the protection mode changed?
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
Q9) What is the number of standby databases that can be created?
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.
Q10) How would you create a physical Standby in this software?
The following easy steps can create a physical standby in the software:
- Enabling of the forced logging
- Creating of a password file
- Configuration of a standby redone log
- The archiving is enabled
- Setting up of the parameters for initialization of the primary database
- Configure the needful settings that support database of both the nodes.
Q11) Mention the chief advantages of using Physical database standby in the software.
There is fourfold advantage to using the physical database standby.
1. The higher availability quotient
2. The high capability of balancing the load including both reporting as well as backup.
3. Protecting the data
4. Recovery of data is case of disasters
Q12) What services are needed on the primary database?
Four basic services are required on the primary database. They are as follows:
1. The Log Writer: Updates the online redone logs by collecting the information. It creates localized archived logs and causes the transmission of the redo ones to the standby bases.
2. The Archive Process: Copying of the online redone logs for the standby bases in a remote or localized manner.
3. The Fetch Archive Log: Servicing of the request for archived logs from the Fetch Archive Log customers that run on more than one standby bases. More than one server can run on the primary database where for each Fetch Archive Log request, there is one server.
4. Log Network: It gets used on the primary database to establish a connection with that of standby.
Q13) Outline the services needed on the standby database.
There are four services required on the standby:
1. Fetch Archive Log: The archived redo files get pulled from primary sites. The transfer of the archived redo files is initiated on the detection of a sequence gap.
2. Remote File: The Primary database is used to receive the archived or standby redone logs. You can get both the archived and the standby logs together as well.
3. Archive Process: The standby redone logs that get applied by managed recovery are archived by this service.
4. Managed Recovery: The archived redo log details are applied to the standby databank.
Q14) How would you delay an application of the logs to the physical standby mode?
The standby gets naturally applied to the redo logs at the time of their arrival from primary database. There are times when a time lag is needed to be created between archiving a redo log at the primary point and applying the log at standby.
You need to modify the parameter for initialization on the primary site to lead to a delay on the standby site. For instance, if you want a delay of one hour, you can set log for delay at sixty minutes. It is worth mentioning in this regard that the delay is always given in minutes in the format of, for instance, 60′.
With this action, the redo logs are still copied from the primary to standby site but it does not immediately applied. They get applied after the expiry of the specific time interval that has been set.
Q15) What is the utility of the Redo Transport Service?
It is used for controlling the automatic transmission of redo data of the production database in one or multiple archives. The following tasks are successfully performed by it:
1. Transmission of redo data from primary to standby in the configuring.
2. The management of the process of handling gaps in archive log files initiated as a result of the failure of network.
3. Automatic detection of missing or corrupted archived log files in a standby database. It also automatically retrieves the replacement archived redone log from either the primary or standby system.
Q16) Differentiate between logical standby database and physical database.
1. Physical standby differs from logical standby:
- Physical standby schema matches exactly the source database.
- Archived redo logs and FTP’ed directly to the standby database which is always running in “recover” mode.
- Upon arrival, the archived redo logs are applied directly to the standby database.
2. Logical standby is different from physical standby:
- Logical standby database does not have to match the schema structure of the source database.
- Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.
- Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.
- Logical standby database can have additional materialized views and indexes added for faster performance.
Q17) What do you understand by DG Broker ?
The DG Broker helps in the management of standby and primary bases by the utilization of command-line, broker or graphical interfaces. These are integrated in the Enterprise Manager software by Oracle.
Q18) What are the tasks that you can perform using DG Broker?
It can be used to provide five benefits which have been listed below:
- Creating and enabling configurations for the software. This includes factors like the apply and the transport redo services.
- Management of the complete configuration from any kind of system in configuration.
- Monitoring and managing of the configurations that have the standby or the primary data.
- Simplification of fail over and switch over by letting their invocation by using either one key click or by the use of a single command in the interface available in the software.
- On the unavailability of the primary data, the fast and start fail over is enabled to fail over naturally. When this fail over gets enabled, the broker of the software determines the necessity of a fail over. If it is deemed necessary, then it is initiated to the standby system naturally. There is no need of intervention from database application.
Q19) Can this software get used with the standard edition released by the makers?
There are certain automated features that are not available in the standard edition. You can ship logs and write the scripts manually to get the steps done. On certain platforms, you get to write the scripts and then use the commands to ship it to standby system. The recovery is then carried out manually and it is removed after the application is done.
Q20) What do you understand by apply services in this software?
The apply services refer to the application of the redo data on the standby system to keep the transactional sync with the primary system. This redo data can get applied from either archived redo files or from the standby redo files directly as they get filled. The application from the from the standby redo files is only possible if the real-time facility gets enabled. It does not require the redo log to get archived at the standby system first. One of the main features of this service is that it also permits read-only accessibility to the data.
Q21) 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);
Q22) If you didn’t have access to the standby database and you want 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;
Q23) How can you recover standby which far behind from primary (or) without archive logs how can we make standby sync?
By using RMAN incremental backup.
Q24) 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 back to standby?
Till Oralce 10g, create guaranteed restore point, open in read write, let user do DMLs on snapshot standby, flashback to restore point, start MRP. From Oracle 11g, convert physical standby to snapshot standby, let user do DMLs on snapshot standby, convert to physical standby, start MRP.
Q25) What are the uses of standby redo log files?
A standby redo log resides on the standby database site. The standby redolog file is similar to an online redo log, except that a standby redo log is used to store redo data that has been received from a another/primary database.
Q26) What is the use of standby redolog?
The Advantage of having Standby Redo Logs is that every records written into the Online RedoLogs of the Primary Database is transferred to the Standby database and written into the Standby Redo Logs at the same time.
therefore, you it minimizes the probability of Data Loss on the Standby Database.
For real time apply, it is mandatory to have redolog.
Without Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed,
this Archived Redo Log is applied to the Standby Database by the MRP process . An incomplete ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.
If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log ,when there record entered in online redolog and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.
Q27) What is the difference between switchover and failover?
A switchover means just switching roles between the primary database and standby db.
nswitchover, the primary database chnaged to a standby role, and the standby database changed to the primary role. This is typically done for planned maintenance of the primary db server.
A failover is when the primary database fails and one of the standby databases is transitioned to take over the primary role. Failover is performed only in the event of a catastrophic failure of the primary database, and there is no possibility of recovering the primary database in a timely manner. Failover may or may not result in data loss depending on the protection mode in effect at the time of the failover.
Q28) What is the process to apply a psu patch in dataguard setup?
- Make sure lag between primary and standby is zero.
- Cancel the recovery (MRP) on standby.
- Shutdown standby db and listener.
- Apply patch to binary using opatch apply command.
- Once patch applied to binary, startup the listener and standby in mount stage.
- Now shutdown primary db and listener.
- Apply patch to binary using opatch apply command.
- Once patch applied to binary, startup the listener and db & perform post patch steps.
- Start the MRP recovery process on standby.
Q29) What are fal_client and fal_server parameters?
FAL Means – Fetch Archive log. FAL_CLIENT and FAL_SERVER parameters are used on standby database for archive gap resolution.
FAL_SERVER and FAL_CLIENT parameters are required on standby database only .
FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested i.e the tns alias of the primary db.
FAL_CLIENT: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points from FAL_SERVER TO standby db. i.e the tns alias of the standby db.
fal_server = ‘primdb’
fal_client = ‘stdbydb’
Q30) What is active dataguard. Does it needs 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.
NOTE – To use active dataguard, you need additional license from oracle.
Q31) The support DBA , added a tempfile on primary database, but the tempfile is not reflecting on standby database despite, the standby_file_management is set to AUTO.
Adding tempfiles to TEMP tablespaces in primary database, will not automatically create on standby database. Because no redo is generated, while adding tempfile. So DBA have to add the temp file manually.
Q32) What are the different types of redo transport services in dataguard?
SYNC(SYNCHRONOUS): This mode is used for maximum protection and maximum availability protection mode. The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment. A transaction cannot commit until all redo generated by that transaction has been successfully sent to every standby destinations.
ASYNC(ASYNCHRONOUS): This mode is used for maximum performance mode. A transaction can commit without waiting for the redo generated by that transaction to be successfully sent to any redo transport destination that uses the asynchronous redo transport mode.
Q33) What is snapshot standby database?
Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database i. e we can convert the physical standby database to snapshot standby for testing purpose. On that, we can do all types of testing (BOTH READ/WRITE) or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert
the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.