Question 1. What Is Data Guard?
Data Guard provides a comprehensive set of offerings that create, hold, control, and display one or extra standby databases to permit production Oracle databases to live on screw ups and information corruptions. Data Guard continues those standby databases as copies of the manufacturing database. Data Guard can be used with conventional backup, healing, and cluster techniques to offer a high degree of data protection and statistics availability.
Question 2. What Is Dg Broker?
DG Broker “it's miles the control and monitoring tool”.
Oracle dataguard broker is a dispensed control framework that automates and centralizes the creation , maintenance and tracking of DG configuration.
All management operations may be accomplished both via OEM, which makes use of the broking (or) broking unique command-line tool interface “DGMGRL”.
Oracle 10g Interview Questions
Question 3. What Is The Difference Between Data Guard And Standby?
Data Guard : Dataguard is mechanism/tool to hold standby database. The dataguard is set up between number one and standby example . Data Guard is only to be had on Enterprise Edition.
Standby Database : Physical standby database affords a bodily equal copy of the number one database, with on disk database systems which might be same to the primary database on a block-for-block basis. Standby capability is to be had on Standard Edition.
Question four. What Are The Differences Between Physical/logical Standby Databases? How Would You Decide Which One Is Best Suited For Your Environment?
Physical standby DB: As the name, it is physically (datafiles, schema, other bodily identity) identical reproduction of the primary database. It synchronized with the number one database with Apply Redo to the standby DB.
Logical Standby DB: As the name logical facts is similar to the production database, it may be physical structure may be one-of-a-kind.
It synchronized with primary database even though SQL Apply, Redo obtained from the number one database into SQL statements and then executing these SQL statements on the standby DB.
We can open “bodily stand with the aid of DB to “read simplest” and make it available to the applications customers (Only select is authorized in the course of this period). We can not observe redo logs acquired from number one database at this time.
We do not see such issues with logical standby database. We can open the database in normal mode and make it to be had to the users. At the identical time, we will observe archived logs obtained from primary database.
For OLTP massive transaction database it's miles better to pick logical standby database.
Oracle 10g Tutorial
Question five. Explain Active Data Guard?
11g Active Data Guard
Oracle Active Data Guard allows examine-only access to a physical standby database for queries, sorting, reporting, web-based totally access, and many others., even as continuously making use of modifications obtained from the manufacturing database.
Oracle Active Data Guard also enables the usage of rapid incremental backups while offloading backups to a standby database, and may offer extra advantages of excessive availability and disaster protection towards deliberate or unplanned outages at the production website online.
Oracle 9i Interview Questions
Question 6. What Are Differences Between Physical, Logical, Snapshot Standby And Adg (or) What Are Different Types Of Standby Databases?
Physical standby – in mount nation, MRP will observe documents
ADG – in READ ONLY nation, MRP will follow archives
Logical standby – in READ ONLY state, LSP will run
Snapshot standby databases – Physical standby database can be transformed to photo standby database, with a view to be in READ WRITE mode, can do any form of testing, then we can convert back photograph standby database to physical standby database and start MRP as a way to apply all pending files.
Question 7. How To Find Out Backlog Of Standby?
choose round((sysdate – a.NEXT_TIME)*24*60) as “Backlog”,m.SEQUENCE#-1 “Seq Applied”,m.Method, m.Popularity
from v$archived_log a, (choose manner,SEQUENCE#, status from v$managed_standby in which system like ‘%MRP%’)m where a.SEQUENCE#=(m.SEQUENCE#-1);
Oracle 9i Tutorial Oracle 8i Interview Questions
Question 8. 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;
Question 9. How Can U Recover Standby Which Far Behind From Primary (or) Without Archive Logs How Can We Make Standby Sync?
Answer :By the use of RMAN incremental backup.
Informatica Interview Questions
Question 10. 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 assured restore point, open in read write, let him do updates, flashback to restore point, begin MRP.
From Oracle 11g, convert physical standby to snapshot standby, permit him do updates, convert to bodily standby, start MRP.
Oracle 8i Tutorial
Question eleven. What Are New Features In 11g Data Guard?
Here is some records defend category and there enhancement
Data Protection Advanced Compression Lost-write safety Fast-Start Failover
Increase ROI Active Data Guard Snapshot Standby
High Availability Faster Redo Apply Faster failover & switchover Automatic Failover the use of ASYNC
Manageability Mixed Windows/Linux
PL/SQL Interview Questions
Question 12. What Are The Uses Of Standby Redo Log Files?
A standby redo log is needed for the maximum safety and most availability modes and the LGWR ASYNC transport mode is usually recommended for all databases. Data Guard can recover and practice more redo data from a standby redo log than from archived redo log documents by myself.
If the actual-time apply feature is enabled, log practice offerings can observe redo information as it's miles acquired, with out awaiting the modern standby redo log report to be archived.
This outcomes in quicker switchover and failover instances due to the fact the standby redo log documents have been carried out already to the standby database by the point the failover or switchover starts.
Oracle 10g Interview Questions
Question 13. What Is Dg_config ?
Specify the DG_CONFIG characteristic to identify the DB_UNIQUE_NAME for the primary database and every standby database inside the Data Guard configuration.
The default value of this parameter permits the primary database to send redo data to faraway destinations and enables standby databases to acquire redo statistics.
Question 14. What Is Rta (actual Time Apply) Mode Mrp?
real-time practice where before log delivery the LGWR system writes to a standbylog file concurrently along with the online redolog report.
This standby logfile is written to standby log record on standby server. There isn't any lack of any committed transaction whatsoever in Real-Time Apply scenario.
•In Real Time Apply, as soon as a transaction is devoted on the Primary, the committed changes may be to be had at the Standby in Real Time even with out switching the log at the Primary
MRP – Managed recuperation system – For Data Guard, the historical past system that applies archived redo log to the standby database.
Question 15. What Is The Difference Between Sync/async, Lgwr/arch, And Affirm/noaffirm ?
Specifies that community I/O is to be finished synchronously (SYNC) or asynchronously (ASYNC) while archival is finished the use of the log author process (LGWR).
Specifies whether or not redo delivery offerings use archiver procedures (ARCn) or the log creator procedure (LGWR) to accumulate transaction redo facts and transmit it to standby locations. If neither the ARCH or LGWR attributes are designated, the default is ARCH.
Controls whether or not redo shipping services use synchronous or asynchronous I/O to write redo data to disk
AFFIRM—specifies that every one disk I/O to archived redo log files and standby redo log files is accomplished synchronously and completes efficiently earlier than the log author method keeps.
NOAFFIRM—specifies that every one disk I/O to archived redo log files and standby redo log documents is carried out asynchronously; the log writer procedure at the primary database does not wait till the disk I/O completes before continuing.
Oracle DBA Interview Questions
Question sixteen. What Is Staticconnectidentifier Property Used For?
Answer :11gr2 new database assets, StaticConnectIdentifier, which permits the user to specify a static connect identifier that the DGMGRL customer will use to begin database instances.
Oracle DBA Tutorial
Question 17. What Is Failover/switchover (or) What Is The Difference Between Failover And Switchover?
Switchover – This is performed whilst both number one and standby databases are available. It is pre-planned.
Failover – This is carried out whilst the number one database is NO longer available (ie in a Disaster). It isn't pre-deliberate.
Oracle 11g Interview Questions
Question 18. What Are The Background Processes Involved In Data Guard?
Oracle 9i Interview Questions
Question 19. What Are Different Types Of Modes In Data Guard And Which Is Default?
Maximum overall performance:
This is the default protection mode.
It affords the best stage of facts protection that is viable without affecting the overall performance of a primary database.
This is done by permitting transactions to commit as quickly as all redo facts generated by means of those transactions has been written to the net log.
This safety mode guarantees that no data loss will arise if the number one database fails.
To offer this level of protection, the redo information had to get better a transaction have to be written to each the web redo log and to at the least one standby database earlier than the transaction commits.
To make certain that facts loss can't arise, the number one database will close down, in preference to hold processing transactions.
This protection mode offers the highest stage of statistics safety this is feasible with out compromising the supply of a number one database.
Transactions do no longer commit until all redo information needed to recover those transactions has been written to the online redo log and to as a minimum one standby database.
Oracle 11g Tutorial
Question 20. How Many Standby Databases We Can Create (in 10g/11g)?
Till Oracle 10g, nine standby databases are supported.
From Oracle 11g R2, we will create 30 standby databases..
SQL Interview Questions
Question 21. What Are The Parameters We’ve To Set In Primary/standby For Data Guard ?
Question 22. What Is The Use Of Fal_server & Fal_client, Is It Mandatory To Set These ?
specifies the FAL (fetch archive log) server for a standby database. The price is an Oracle Net carrier name, which is believed to be configured well at the standby database device to point to the desired FAL server.
specifies the FAL (fetch archive log) customer call that is utilized by the FAL service, configured through the
FAL_SERVER initialization parameter, to refer to the FAL consumer.
The fee is an Oracle Net carrier call, which is thought to be configured nicely at the FAL server gadget to point to the FAL client (standby database).
Question 23. What Is A Snapshot Standby Database?
11g Snapshot Standby Database
Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been made out of a physical standby database.
We can convert a bodily standby database to a snapshot standby database, perform a little form of trying out on a database which is a study write replica of the cutting-edge primary or manufacturing database and then finally revert it to it’s earlier country as a physical standby database.
While the picture standby database is open in examine-write mode, redo is being received from the number one database, but isn't always implemented.
After converting it again to a bodily standby database, it's far resynchronized with the primary by way of making use of the accumalated redo facts which changed into in advance shipped from the primary database however not carried out.
Using a photo standby, we're able to do actual time application trying out the use of close to real time production facts. Very regularly we are required to do manufacturing clones for the cause of testing. But the usage of photograph standby databases we will meet the equal requirement sparing the attempt,time,sources and disk area.
Oracle apps Interview Questions
Question 24. Snapshot Standby Database (updatable Snapshot For Testing)?
A photo standby database is a completely updatable standby database that is created by changing a bodily standby database right into a snapshot standby database.
Like a bodily or logical standby database, a picture standby database receives and files redo records from a primary database. Unlike a bodily or logical standby database, a photograph standby database does no longer practice the redo records that it receives. The redo information received by way of a image standby database is not applied until the photograph standby is transformed again into a physical standby database, after first discarding any local updates made to the image standby database.
Oracle 8i Interview Questions
Question 25. Data Guard Architecture?
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or extra standby databases. The databases in a Data Guard configuration are linked by means of Oracle Net and may be dispersed geographically. There are no regulations on in which the databases are located, furnished they can communicate with each different.
The Oracle 9i Data Guard structure contains the subsequent items:
Primary Database – A production database that is used to create standby databases. The archive logs from the number one database are transfered and implemented to standby databases. Each standby can simplest be related to a single number one database, however a unmarried number one database can be related to a couple of standby databases.
Standby Database – A reproduction of the number one database.
Log Transport Services – Control the automated transfer of archive redo log documents from the primary database to one or extra standby locations.
Network Configuration – The number one database is attached to at least one or extra standby databases the usage of Oracle Net.
Log Apply Services – Apply the archived redo logs to the standby database. The Managed Recovery Process (MRP) virtually does the paintings of keeping and applying the archived redo logs.
Role Management Services – Control the converting of database roles from primary to standby. The offerings include switchover, switchback and failover.
Data Guard Broker – Controls the creation and tracking of Data Guard. It comes with a GUI and command line interface.
A Data Guard configuration carries one manufacturing database, also known as the number one database, that capabilities within the number one role. This is the database that is accessed by way of most of your applications.
A standby database is a transactionally consistent replica of the number one database. Using a backup reproduction of the number one database, you can create up to 9 standby databases and contain them in a Data Guard configuration. Once created, Data Guard routinely maintains every standby database by using transmitting redo records from the number one database and then applying the redo to the standby database.
The forms of standby databases are as follows:
Physical standby database:
Provides a bodily same copy of the primary database, with on disk database structures which are equal to the number one database on a block-for-block foundation. The database schema, which includes indexes, are the identical. A bodily standby database is saved synchronized with the number one database, thru Redo Apply, which recovers the redo facts acquired from the primary database and applies the redo to the physical standby database.
Logical standby database:
Contains the identical logical statistics as the production database, despite the fact that the physical company and structure of the facts may be extraordinary. The logical standby database is kept synchronized with the number one database through SQL Apply, which transforms the records in the redo acquired from the number one database into SQL statements after which executes the SQL statements on the standby database.
Question 26. What Are The Services Required On The Primary And Standby Database ?
The services required on the primary database are:
Log Writer Process (LGWR) – Collects redo facts and updates the net redo logs. It can also create local archived redo logs and transmit on-line redo to standby databases.
Archiver Process (ARCn) – One or extra archiver tactics make copies of on-line redo logs either locally or remotely for standby databases.
Fetch Archive Log (FAL) Server – Services requests for archive redo logs from FAL clients strolling on a couple of standby databases. Multiple FAL servers may be run on a primary database, one for every FAL request. .
The offerings required at the standby database are:
Fetch Archive Log (FAL) Client – Pulls archived redo log documents from the primary web site. Initiates switch of archived redo logs when it detects a gap series.
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 means of the managed recovery process (MRP).
Managed Recovery Process (MRP) – Applies archive redo log statistics to the standby database
Sybase Interview Questions
Question 27. What Is Rts (redo Transport Services) In Data Guard?
It controls the automated transfer of redo information from the production database to one or extra archival locations.
The redo delivery services perform the following responsibilities:
Transmit redo statistics from the number one gadget to the standby structures in the configuration.
Manage the manner of resolving any gaps inside the archived redo log files because of a network failure.
Automatically discover missing or corrupted archived redo log files on a standby gadget and robotically retrieve replacement archived redo log files from the primary database or any other standby database.
Informatica Interview Questions
Question 28. What Are The Protection Modes In Dataguard?
Data Guard Protection Modes
This section describes the Data Guard protection modes.
In those descriptions, a synchronized standby database is meant to be one which meets the minimum requirements of the configured information protection mode and that doesn't have a redo gap. Redo gaps are discussed in Section 6.Three.3.
This protectionmode presents the highest level of data protection this is feasible with out compromising the supply of a primary database. Transactions do now not dedicate until all redo facts needed to recover those transactions has been written to the web redo log and to at the least one synchronized standby database. If the number one database can't write its redo circulation to at the least one synchronized standby database, it operates as if it have been in maximum performance mode to preserve number one database availability until it is again able to write its redo circulation to a synchronized standby database.
This mode ensures that no data loss will arise if the primary database fails, however best if a second fault does no longer save you a whole set of redo data from being sent from the number one database to at the least one standby database.
This protectionmode affords the best stage of information protection this is viable without affecting the performance of a number one database. This is completed via allowing transactions to devote as soon as all redo information generated by using those transactions has been written to the net log. Redo records is likewise written to one or extra standby databases, however this is executed asynchronously with recognize to transaction commitment, so primary database performance is unaffected via delays in writing redo facts to the standby database(s).
This safety mode gives barely much less information safety than most availability mode and has minimum impact on primary database performance.
This is the default protection mode.
This safety mode ensures that zero information loss occurs if a number one database fails. To provide this level of protection, the redo records had to get better a transaction have to be written to each the net redo log and to at the least one synchronized standby database earlier than the transaction commits. To make sure that data loss can not occur, the number one database will shut down, in preference to retain processing transactions, if it can't write its redo circulation to at the least one synchronized standby database.
Because this records safety mode prioritizes records safety over primary database availability, Oracle recommends that not less than two standby databases be used to guard a number one database that runs in maximum protection mode to prevent a unmarried standby database failure from causing the primary database to close down.
Question 29. How To Delay The Application Of Logs To A Physical Standby?
A standby database routinely applies redo logs whilst they arrive from the number one database. But in some cases, we want to create a time lag between the archiving of a redo log at the number one website, and the application of the log at the standby website online.
Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a postpone for the standby database.
Example: For 60min Delay:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=stdby_srvc DELAY=60′;
The DELAY characteristic is expressed in mins.
The archived redo logs are nonetheless robotically copied from the number one website to the standby site, however the logs are not right now carried out to the standby database. The logs are carried out when the desired time interval expires.
Oracle RAC/ASM Interview Questions
Question 30. Steps To Create Physical Standby Database?
Take a complete hot backup of Primary database
Create standby manage record
Transfer complete backup, init.Ora, standby manipulate file to standby node.
Modify init.Ora report on standby node.
Recover Standby database(Alternatively, RMAN DUPLICATE DATABASE FOR STANDBY DO RECOVERY may be extensively utilized)
Setup FAL_CLIENT and FAL_SERVER parameters on each facets
Put Standby database in Managed Recover mode
Question 31. What Are The Data Guard Parameters In Oracle?
Set Primary Database Initialization Parameters
On the number one database, you outline initialization parameters that manipulate redo shipping services whilst the database is inside the primary position. There are extra parameters you want to add that manipulate the receipt of the redo information and log observe offerings while the primary database is transitioned to the standby function.
‘SERVICE=boston LGWR ASYNC
Primary Database: Standby Role Initialization Parameters
Prepare an Initialization Parameter File for the Standby Database
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) utilized by the primary database; a text initialization parameter report can be copied to the standby place and modified. For instance:
CREATE PFILE=’/tmp/initboston.Ora’ FROM SPFILE;
Modifying Initialization Parameters for a Physical Standby Database.
LOG_ARCHIVE_DEST_2= ‘SERVICE=chicago LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago’
Question 32. What Are The Types Of Oracle Data Guard?
Oracle Data Guard labeled in to 2 sorts based on manner of creation and method used for Redo Apply.
They are as follows:
Physical standby (Redo Apply generation)
Logical standby (SQL Apply Technology)
RMAN (Recovery Manager) Interview Questions
Question 33. What Are The Advantages In Using Oracle Data Guard?
Following are the extraordinary advantages in the usage of Oracle Data Guard feature on your surroundings:
Off loading Backup operation to standby database.
Automatic Gap detection and Resolution in standby database.
Automatic Role Transition the usage of Data Guard Broker.
PL/SQL Interview Questions
Question 34. What Are The Different Services Available In Oracle Data Guard?
Following are the unique Services to be had in Oracle Data Guard of Oracle database.
Redo Transport Services.
Log Apply Services.
Question 35. What Are The Different Protection Modes Available In Oracle Data Guard?
Following are the special safety modes to be had in Data Guard of Oracle database you can use someone based on your application requirement.
Question 36. How To Check What Protection Mode Of Primary Database In Your Oracle Data Guard?
By the use of following query you may check safety mode of primary database on your Oracle Data Guard setup. SELECT PROTECTION_MODE FROM V$DATABASE;
For Example: SQL> select protection_mode from v$database;
Oracle DBA Interview Questions
Question 37. How To Change Protection Mode In Oracle Data Guard Setup?
By the use of following query your can trade the safety mode on your primary database after setting up required fee in corresponding LOG_ARCHIVE_DEST_n parameter in primary database for corresponding standby database. ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTIONmodify database set standby database to MAXIMUM PROTECTION;
Question 38. What Are The Advantages Of Using Physical Standby Database In Oracle Data Guard?
Advantages of the usage of Physical standby database in Oracle Data Guard are as follows:
Load balancing (Backup and Reporting).
Question 39. What Is Physical Standby Database In Oracle Data Guard?
Oracle Standby database are divided into bodily standby database or logical standby database based totally on standby database creation and redo log practice technique. Physical standby database are created as actual reproduction i.E block by block replica of primary database. In physical standby database transactions show up in primary database are synchronized in standby database with the aid of using Redo Apply technique through continuously making use of redo statistics on standby database acquired from primary database.
Physical standby database can offload the backup interest and reporting activity from Primary database. Physical standby database may be opened for read-best transactions however redo follow won’t manifest all through that time. But from 11g onwards the usage of Active Data Guard choice (more purchase) you may simultaneously open the bodily standby database for examine-best get admission to and follow redo logs acquired from primary database.
Question 40. What Is Logical Standby Database In Oracle Data Guard?
Oracle Standby database are divided into bodily standby database or logical standby database based on standby database creation and redo log follow method. Logical standby database may be created similar to Physical standby database and later you may alter the shape of logical standby database. Logical standby database uses SQL Apply method to synchronize logical standby database with number one database.
This SQL practice era converts the acquired redo logs to SQL statements and constantly practice the ones SQL statements on logical standby database to make standby database steady with number one database. Main advantage of Logical standby database examine to bodily standby database is you could use Logical standby database for reporting motive all through SQL apply i.E Logical standby database need to be open throughout SQL follow.
Even even though Logical standby database are opened for read/write mode, tables which are in synchronize with primary database are to be had for examine-best operations like reporting, pick out queries and adding index on those tables and creating materialized perspectives on the ones tables. Though Logical standby database has advantage on Physical standby database it has some restriction on records-kinds, styles of DDL, kinds of DML and styles of tables.
Oracle 11g Interview Questions
Question 41. What Are The Advantages Of Logical Standby Database In Oracle Data Guard?
Better usage of aid
Question forty two. What Is The Usage Of Db_file_name_convert Parameter In Oracle Data Guard Setup?
DB_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. DB_FILE_NAME_CONVERT parameter are used to replace the region of facts documents in standby database. These parameter are used whilst you are the use of distinct listing structure in standby database compare to number one database facts files location.
SQL Interview Questions
Question 43. What Is The Usage Of Log_file_name_convert Parameter In Oracle Data Guard Setup?
LOG_FILE_NAME_CONVERT parameter is utilized in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to replace the region of redo log files in standby database. These parameter are used when you are using distinctive directory shape in standby database examine to number one database redo log record area.
Step for Physical Standby
These are the stairs to comply with:
Enable compelled logging
Create a password report
Configure a standby redo log
Set up the primary database initialization parameters
Configure the listener and tnsnames to assist the database on each nodes
col name layout a20
col thread# layout 999
col sequence# layout 999
col first_change# format 999999
col next_change# layout 999999
SELECT thread#, sequence# AS “SEQ#”, name, first_change# AS “FIRSTSCN”,
next_change# AS “NEXTSCN”,archived, deleted,completion_time AS “TIME”
Question 44. Tell Me About Parameter Which Is Used For Standby Database?
The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the number one database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to deliver redo log stream to the standby database. In this case, the standby database provider is dg2.
Next, STANDBY_FILE_MANAGEMENT is about to AUTO so that after Oracle files are brought or dropped from the primary database, these adjustments are made to the standby databases routinely. The STANDBY_FILE_MANAGEMENT is best relevant to the physical standby databases.
Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is usually recommended whilst the use of Oracle Managed Files (OMF) on the number one database. Next, the primary database should be strolling in ARCHIVELOG mode.