• In ansible, what type of activites you did?
• What is a role in ansible?
• How you can print the output in ansible task?
• How you handle errors in ansible?
• Explain the postgres architecture
• How connection gets established in postgres
• Different Components
• Where the data is stored in postgres
• What is vacuuming and vaccuming full and their difference.
• What MVCC? What is the advantage and disadvantage of this?
• What is visibility Map?
• What happens in the backend when we do vacuuming?
• Difference between awk and sed?
• What is the use of xarg?
• What is tee?
• How to list all the files inside a directory and sub-directories recursiverly .
• Which mongo db version you have worked
• What are some parameters in mongod.conf parameter
• What is bindip in mongod.conf
• Different protection modes.
• Different types of standby databases
• What is snapshot standby database and how it works
• If the standby setup in max protection mode and if the standby goes down, what will happen.
• How to fix if my archive is missing in standby
• Explain different migration process?
• How you can do cross platform migration?
• How you did migration using transportable tablespace?
• What is endian format?
• Different methods of cache fusion?
• Which processes are responsible for cache fusion?
• Where voting disk path is stored, ?
• How can I read GPNP profile?
• What is OLR, and which data is stored there?
• Why not voting disk path is not present in olr?
• In RAC, My one node is down, and getting error like vip already in use? What might be the issue?
• If a node , not coming up, how you troubleshoot it?
• What RAC scenarios you have faced in your environment?
• What proactive tasks you have done in your environment to improve the performance?

NOTE – You can share your interview experience with us, which will be helpful for our fellow DBAs.

The post Real DBA Interview questions -4 appeared first on DBACLASS.

ROUND 1: ( Technical Round)

• Background processes in oracle database
•  Explain about dbwr and lgwr
•  What is shared poool. What it does
•  What is the role of PGA
•  What it contains?
•  What is the persistent area in PGA
•  Explain how update statement works.
•  Lets say, one user is updating and other one is selecting, How it will get the data.
•  What is touchcount and its related question?
•  What redo buffer contains.
•  How recovery happens?
•  What type of issues you face in oracle database
•  How can we control db writer process.  What should the be the value of db writer process.
•  Different status of buffer in buffer cache.
•  Different status of redo logfile
•  Can i drop a online redolog from oracle?
•  What core dba issues you face
•  How transaction recovery happens
•  How update statement work flow happens
•  RAC STARTUP SEQUENCE.
•  DIFFERENT BACKGROUND PROCESSES in RAC.
•  Role of ocr, vd.
•  How many. Number of voting disk for 8 node RAC?
•  Which file while starting cluster and what happens next?
•  Why OLR  is required?
•  What is gpnp profile
•  Role of LMS, LMON, LMD, LCK,
•  What is dynamic remastering
•  What happens during instance reconfiguration
•  Which process responsible for instance reconfig
•  What is GCS,GES and GRD and which processes are responsible for this.
•  What is past image
•  Instance recovery in RAC
•  Which proess does node eviction, Which node gets evicted
•  Different protection modes in dataguard
•  Which process gets the data from primary to standby
•  Can we convert protection mode
•  AFFIRM and NOAFFIRM
•  What type of issues come in standby dataguard
•  What are some parameters in dataguard
•  What is fal_server
•  What is log_archive_dest_1 and log_archiv_dest_2
•  What is db_name and db_unique_name
•  New features of oracle 19c dataguard
•  What is far sync
•  What is db_flashback_retention_target
•  Different between dataguard and active dataguard
•  Difference between force logging and supplemental logging
•  Difference between classic and integrated
•  What is the parallel integrated apply
•  What is the coordinated integrated apply
•  What is handle collision
•  What is the common issues in goldengate and how you handle it
•  What is discard file
•  Where LCRs are stored
•  Lets say process touched limit and you are unable to login with sysdba ,what you will do
•  What is huge pages and why we need to enable hugepages.
•  If database is running slow, what are the things we need to check.
•  Why dbwr called lazy writer?
• What is the voting disk timeout value?

ROUND 2:( Technical Round)

• How select statement processing happens?
• How insert statement processing happens?
• Explain cache fusion
• Explain cluster startup sequence
• How asm gets started
• Explain flex asm
• How to enable flex asm
• What is asm proxy?
• How you can recover undo tablespace corruption.
• While installing grid, what happens when you do root.sh script.
• In flex asm , how database connects with asm
• If you lost your OLR , How will you troubleshoot?
• If you cluster node gets rebooted, which logs you will observer usually.
• Explain how you apply patch manually in RAC
• Lets say, you applied patch on node 2, and ran rootcrs.sh -post , and now the command is not coming out and error this patch mismatch, When you checked the oracle inventory , you found that patches are same . How you will troubleshoot it
• Explain steps for node addition
• Explain cache fusion , Which processes are responsing for cache fusion.
• Explain write write scenarios in cache fusion
• What is dynamic resource remastering? Is it good or bad?
• If I remove the entry for spfile in gpnp profile, then will the crs start?
• How you check the private interconnect issues.
• How to do db upgrade.
• What happens during db upgrade
• How many phases are there in db upgrade
• If I don’t do the timezone upgrade, will the database work.
• What is far sync?
• Which process send the redologs to standby
• Which process received the data in standby
• If block corruption happens in standby , how you willl recover it
• What is multi instance redo apply . And how I can enable multi instance redo apply in dataguard.
• What is sql quarantine .

ROUND 3( Managerial Round):

Mostly questions about

• The candidate education history and work experience history.
• Some high level question on database migration.
• High level question on RAC node eviction.
• Asked some question about some real time RAC issues that you faced in the past.
• Asked about the certifications.
• Asked whether any knowledge on oracle cloud or any other technologies
• Asked whether any knowledge on exadata.
• Asked how good you are at weblogic troubleshooting
• Some discussion happened on salary and work related discussion

Current update

Candidate didn’t hear back from the Recruiter. Most probably candidate was not selected.

The post Real DBA interview questions -3 appeared first on DBACLASS.

Below are the questions asked to a candidate with 10 years of experience in Oracle DBA and postgres . There were 2 rounds.

Round 1 :

•  What are your day to day activities. And what is your role in the Team?
• Explain cluster startup sequence.
• Suppose users are connected to node1 and Os team, want to do emergency maintenance and they need to reboot. What wil happens to the transactions on node1 , and can we move them to node 2.
• What are the advantages of partitioning?
• Can we convert non-partitioned table to partitioned table online( in production)?
• What are the cloning methods. And what is the difference between duplicate method and restore method?
• How can we open the standby database as read write for testing and Once testing is done need to revert to previous position. But without using snapshot command?
• Explain how you did grid upgrade.
• Which advanced security features you have used ?
• What is audit purging?
• Lets says a query was running fine for last few days , but today it is not performing well. How you will troubleshoot it?
• What is oracle in memory ?
• Explain what type of patches you applied on grid.
• As per resume ,You have worked on ansible . So how good you are with ansible and what type of scripts you have wrote.
• How can you troubleshoot a shell script?

2nd round:

• Explain ASM architecture , means when ever user runs a query, how it gets the data from ASM.
• Why ASM performance is better
• Why VIP is used , Bcoz before 10g vip was not used. What benefits vip provide
• What is Current block and CR block in RAC
• What wait events are there in RAC??
• What is gc buffer busy wait
• What is gc 2 way current wait, gc 3 way current wait.
• What is node eviction. In which cases it happens.
• How you do load balancing using services in your project. Explain
• What activities you did using goldengate
• How you did upgrade/migration using goldengate.
• What is goldengate instantiation.
• What is bounded recovery in goldengate
• What is the difference between CSN in goldengate vs SCN in database?
• Which type of os tool/commands you have used for os monitoring/performance. And how you read it.
• How you troubleshoot I/O issues in oracle
• What type of sql performane tools you use.
• How you migrate a plan from one db to another db
• What is the dbms package for baseline and sql tuning?
• Lets say, despite importing the baseline, query is not picking the plan, What can the be reason?
• What is index fast full scan and index range scan?
• What is the purpose of standby redolog.
• Any automation tools you have used?
• Did you worked on jenkins or gitlab?
• What activities you did in postgres.
• How you implement backup in postgres.
• Is your postgres cluster active active or active passive?
• Can we create a active active postgres cluster?
• Do you remember any postgres extensions?
• What type of scripts you have written on ansible?
• Tell me some modules of ansible.
• How can we encrypt the server credentials in ansible.

If you wish to share your interview experiences, then please drop a mail to support@dbaclass.com. We will happy to publish it with your permission.

The post Real Interview questions for experienced DBAs – 1 appeared first on DBACLASS.

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 standby redolog on standby database.

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 whenever a record is inserted into oracle redolog , the RFS process will write into the Standby Redo Log  of standby redolog and it gets applied by MRP , which ensures real time sync. 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.

   Oracle recommends to keep standby redologs on both primary and standby database.(Though SLR is not mandatory for primary, it will make the switchover quick without any additional DBA activity. i.e If you don’t standby redologs on primary, whenever we do switchover, we need to create the SLR .

2. How many standby logs need to be created? What should be the size?

It is recommended to create one additional standby log group per thread . And its size should be same as that of primary redo log.

Suppose, we have 4 number of redo log groups of 200M on primary , Then we need to create 5 standby redo logs of 200M.

Why extra:

During heavy dml activity on primary, this extra standby log will help as a buffer in coping with the sync up.

Please note, Oracle recommends not to multiplex the standby redologs on , As it puts unnecessary i/o overhead during log shipping.

3. Explain different protection modes in dataguard?

There are three modes .

1. MAXIMUM PROTECTION
This mode provides maximum protection. It guarantees zero data loss. In this mode the redo/transaction data must be written to both primary redo log and standby redo log. For any reason(mostly N/W issue) if it is unable to write to standby, Then primary database will get shutdown. i.e if a user commits transaction, then that commit will not be be completed, unless primary receives a confirmation that write is completed on standby.

2. MAXIMUM AVAILABILITY:
It provides the highest level of data protection that is possible without affecting the availability of the primary database.Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

3. MAXIMUM PERFORMANCE:(default one)
This is the default protection mode. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.

4. 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.

5. Support my dataguard setups is having one primary and standby and it is in maximum protection mode. Can i convert the standby to snapshot standby database?

No we cannot covert the standby database to snapshot in maximum protection, when this is the only standby db.  Because  in maximum protection mode , transaction data must be written to standby redo , before proceeding further.

6. 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 changed 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.

7. 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  the standby database oracle home binary using opatch apply command.
•  Once patch applied to binary , startup the listener and standby in mount stage or OPEN(if active dataguard).
•  Now shutdown primary db and listener.
•  Apply patch to primary database home binary using opatch apply command.
•  Once patch applied to binary , startup open  the primary database and listener 
•  Start the MRP recovery process on standby .
•  Run post patching script catbundle.sql(if 11g)  or datapatch -verbose command( if 12c) on primary database 

For exact steps  – How to apply patch in standby database

8. What is fal_client and fal_server parameter:

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. This parameter has been deprecated.

fal_server = ‘primdb’
fal_client = ‘stdbydb’

9. What are different types of dataguard and their difference.

There are three types of dataguard setups. PHYSICAL and LOGICA and SNAPSHOT.

PHYSICAL STANDBY:

A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is
maintained as an exact copy through a process called REDO APPLY, in which redo data received from a
primary database is continuously applied to a physical standby database using the database recovery mechanisms. So it will be always in sync with primary.

This Standby database can be opened in read only mode( knows as ACTIVE DATA GUARD), for reporting purpose. Most of the corporations use physical standby for dataguard
configuration.

Physical standby database is suitable for high availability and disaster recovery

LOGICAL STANDBY:

The logical standby database is kept synchronized with the primary database through SQL APPLY, which transforms the data in the redo received  from the primary database into SQL statements and then executes the SQL statements on the standby database. So it contains same logical information as that of production , but physical structure of data can be different.

Logical standby is suitable for reporting purpose, Where we can do read,write operations. ( But we should not modify the standby objects, that exists on primary). This also helps in  near zero down time database upgrade.

SNAPSHOT STANDBY:

10. What is standby_file_management parameter oracle.

Standby_file_management parameter can be have two values. MANUAL or AUTO(Recommended).

AUTO – > Datafiles created on primary will be create automatically on standby . 

MANUAL – > Datafiles created on primary , will not be replicated to standby. The datafile need to be created manually on Standby

11.  With standby_file_management set to AUTO, If I rename a datafile in primary, will the changes be propagated to standby . 

No, for renaming  a data file, the rename command need to be run on standby database as well .

12. 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

13. Lets say, few of archive logs are missing from primary db server, before it is shipped to standby. In this case, How you will resolve the gap and keep the standby in sync with that of primary??

Recover the standby database by taking an incremental backup of primary db (using scn of standby db) . Refer the below link for complete article.

How to recover standby database when archive logs are missing in primary

14.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.

15. What are the different types of redo transport services in dataguard.

1 . ARCH transportation mode:

In this mode, logs are sent by ARCH process. LNS process is not active here. Also standby redologs are not mandatory. i.e Real time APPLY will not happen here. In this mode.  Archive logs will be copied to standby server and will be applied there.

2. SYNC and ASYNC transportation mode:( real time apply) 

In below cases, standby redologs will be required.  Redo is read and sent from redo log buffer to standby by LNS process.

If redo logs buffer is flushed to online redolog , before redo being sent to standby, then LNS will read the online redo log files and sent to standby.

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.

16. Which parameter defines the attributes of redo log transport service.

log_archive_dest_2(n) – > This parameter defines the service name of the standby database and whether this is SYNC or ASYNC mode and the attributes like AFFIRM/NOAFFIRM.

Apart From that , there are additional attributes like ,

17. What are the different types of Apply services in dataguard.

REDO APPLY:

With redo apply, apply services waits for a standby redo log file to be archived before applying the redo to standby. 

REAL TIME APPLY:

If the real-time apply feature is enabled, then apply services can apply redo data ( either from redo log buffer or redo log file)as it is received, without waiting for the current standby redo log file to be archived.

18. What is fast start failover(FSFO)?

Fast-Start Failover is a feature that allows the Oracle Data Guard broker to failover a failed primary database automatically to a predetermined standby database.

Observer process which runs on different server than that of primary and standby , continuously checks the availability of primary database. If both observer and standby database  both are unable to connect with primary database for a pre defined time, Then failover will be initiated automatically. The FastStartFailoverThreshold paramter defines the time limit for failover.

And post failover, If observer is able to establish the connection with old primary, then it will reinstate the database using flashback.

Prerequisites for FSFO:

• Flashback should be enabled on both primary and standby database.
• Observer need to present on a server different from primary and standby .

NOTE – We can run observer on either primary or standby . But it is always recommended to run observer on a different server.

Because , Lets say, you are running observer on primary and if primary server goes does, then observer cannot take any action , it is also not available.

19. Let’s say, Fast start failover is configured in the dataguard setup. And the DBA shutdown the primary database using Shutdown immediate, Then will failover happen?

No. failover will not initiate if shutdown immediate or shutdown normal issued on primary by user action.

But if shutdown abort is issued, then automatic failover will be initiated.

20. What will be the impact on standby setup , when observer is down?

If the observer is down, then there will be no impact on the dataguard setup. But FSFO i.e automatic failover will not happen if primary becomes inaccessible.

21. What is a far sync instance in dataguard?

T Far sync instance is an proxy/dummy instance, which receives redos from primary in sync mode and ships them to  one or multiple standby databases (upto 30 members) in async mode.

It has only standby control file. It doesn’t have any user data file. And the instance will be always in mount state. This feature need s active data guard license. 

Far sync instance is usually useful, when primary and standby databases are far away.Far sync instances stay close to primary database and manages the near real time apply data.

22. What are the new features of oracle 19c /20c/21c dataguard?

Automatic flashback of standby db( in. 19c) – If you flashback the primary database, then standby database will also be flashed back automatically.

Restore Point Replication( in. 19c) – If we create a restore on primary , then same restore point will be created on the standby database.

Active Dataguard DML Redirection(ADR) – If you run a DML statement on standby , then it will be redirected to primary and the requested data will be send to standby again.

Database Buffer cache status maintained(18c)–  Database buffer cache status will be maintained after role transition  on standby also. Users on the standby will be able to continue exactly where they left off after a role change (switchover of failover) with the same performance .

23. What is dataguard broker and how it is useful?

Dgbroker is used for easy management and administration of one or multiple standby databases.  It helps in simple switchover, failover, FSFO, role transitions etc.

DMON process run when dataguard broker is enabled.

dgmgrl utility is used for administration of dgbroker.

Whenever we issue any command from dgmgrl prompt, dmon process will process the request on primary and coordinate the same all standby databases. And it will update the configurations if required.

24. How the apply process in ORACLE RAC dataguard works?

Prior to 12.2, The MRP ( recovery process) can be  started only on one node . However from 12.2 onwards, MRP can started on multiple or all nodes.

Below is the command: 

25. Why we need to enable force logging on primary database, before setting up standby.

If somone does nologging operations on primary, then these data will be missed on standby, which will make the standby inconsistent with primary. So to ensure all the transaction on primary should generate redolog , force logging is enabled.

26. Which additional parameters we add in primary for configuring standby database.

log_archive_config: –

db_unique_name

standby_file_management=auto

log_archive_dest_2  – tns details of standby database, to which logs will be shipped

log_archive_dest_state_2

db_file_name_convert – for mapping the database files of primary and standby db ( when directory structures are different on primary and standby)

log_file_name_convert – For mapping redologs of primary and standby

fal_server – > This defines from where the standby database should request the missing archive logs. i.e on standby database its values will be the tns service_name of primary .

27. Will the db_name and db_unique_name will same for primary and standby databases?

DB_NAME will be same for primary and its standby databases. But everyone will have a different db_unique_name.

28. Except snapshot standby method, is there any way we can open the standby database in read write  mode for testing and revert it back once testing is done.

Flashback method can be used.

29. For security reason, you are not allowed to use sys user for redo transport service. Is there any alternative way or we need use sys user only.

If we want to use an user other than SYS, then we can create a user with sysoper privilege and set the parameter REDO_TRANSPORT_USER to that username.

30. Explain the standby database /dataguard related background process.

MRP – > Managed recovery process, which is responsible for read and redo and apply , through multiple parallel process like Pr(n) process.

RFS – > Remote file service.

LNS – > Log network service ( From 12c LNS is replaced with NSS( FOR SYNC) and NSA( For ASYNC).

DMON – > DG BROKER monitor

FSFP – > Fast start failover process.

31. How oracle detects gaps and resolves it?

Two methods are there.

Automatic gap resolution:

This is done by log transport service. When there is a mismatch between currently transfered redo with that of last received log in standby, then RFS will request the missing log sequences from primary via arch-rfs hearbeat ping.

No special setting is required.

Fetch archive log( FAL Method):

FAL_SERVER – specifies the tns service database from where the missing archive logs need to be fetched.

When a archive is shipped to standby , it gets registered in the standby controlfile. When log apply service detects a gap , it sends a request to fal server to resend the missing logs.

32.  We did failover, and the standby became the new primary. Now we need to make the old primary as new standby. But the database is very high , So rebuilding it will take time. Is there any way to fix it?

There is a way, But only if flashback is enabled. If flashback is enabled , then we can get the current scn from the new primary database and flashback the old primary(new standby) upto that scn.

33.  Can we convert the physical standby database to logical standby database.

Yes it is possible to convert physical to logical standby.

34.  Can we enable tracing in dataguard . 

Yes we can set LOG_ARCHIVE_TRACE parameter to trace redo transport and apply services on primary and standby.

possible values are 0(default means no tracing) , 1,2,4,8,16,32,64 …. 8192

35.  Some one configured the dataguard setup, without enabling force_logging on primary. And in primary  few nologging operations happened . So getting error like data block was loading using NOLOGGINE option. How can We fix it?

In that case, we can get FIRST_NONLOGGED_SCN value from v$datafile on standby database.  Then we can take an incremental backup using the SCN  from Primary. and apply the same on standby.

36.  Explain the physical standby  architecture . How standby gets synced with primary?

37. What is AFFIRM/NOAFFIRM in dataguard?

38. What are the common issues you face in dataguard environments?

1. Replication issues due to network issues
2. Archive  log Missing errors
3. If a datafile is renamed on primary, then also error will come in standby.
4. Issue may occur if someone mistakenly changes the dataguard related parameters like log_archive_config, log_archive_dest parameters.

39. What is FastSync?

Fast Sync provides an easy way of improving performance in synchronous zero data loss configurations. Fast Sync allows a standby to acknowledge the primary database as soon as it receives redo in memory, without waiting for disk I/O to a standby redo log file (SYNC NOAFFIRM). This reduces the impact of synchronous transport on primary database performance by shortening the total round-trip time between primary and standby.

40. What is Multi instance Redo Apply? How can we enable this?

Prior to 12.2 , If your dataguard is RAC, then MRP process can be run only on one node. However From 12.2 Onward, We can enable MIRA( Multi instance Redo apply), i.e MRP can be run on multiple instances or all the nodes.

Below are the syntax:

-- Start MRP from all the instances available:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL;

-- Start MRP on 2 Instances:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

41. If there is any block corruption on standby database, How can we recover that?

--- First check on standby which blocks got corrupted.

SQL> Select * from v$database_block_corruption ;

----Cancel the Recovery:

alter database Recover managed standby database Cancel;

--Restore the datafile, 

 SQL> Restore datafile 9 FORCE  from service 'PRIM_DB' ;

-- Start MRP:

SQL> alter database Recover managed standby database using current logfile disconnect from session;

42. Can i generate awr report in a standby database? If yes how?

43. Can i run sql tuning advisor in standby database?

Yes we can do that , using the concept of db_link.

in dbms_sqltune module, there is a parameter database_link_to which we can se to a database_link(db_link with connect SYS$UMF , pointing to primary). and then we can run the task on standby.

Reference     – https://fatdba.com/2022/01/15/part-1-running-sql-tuning-advisor-for-a-slow-sql-in-a-read-only-standby-database/#:~:text=You%20can%20issue%20SQL%20Tuning,write%20the%20SQL%20tuning%20data. 

44. We are having dataguard setup in maximum protection mode , and we created one datafile in a new diskgroup on primary, But that diskgroup was not present in standby? What will be the outcome?

the primary will shutdown .

45. Can i set sync,, AFFIRM with ARCH in log_archive_dest_n paramter?

46.Which process on standby receive the changes from primary?

47. Can i run expdp command on standby?

Yes we can run expdp on standby database by createing  db_link and using  network_link parameter in expdp.

How to run expdp on physical standby database

48. Can I take full backup on standby and incremental backup on primary?

Yes we can do that.

49. Is there any prerequisite for converting physical standby to snapshot standby database?

You need to set db_recovery_file_dest and db_recovery_file_dest_size parameter on standby and then enable flashback.

50. Some one mistakenly truncated a table on primary database. We already have a dataguard setup. Is there any way we can recover that table? 

Below are the steps.

51. If observor is unable to connect with primary , but it can connect with standby, then what will happen?

If the observor is unable to connect to primary, then it will check the status of primary, through standby.

The post Oracle dataguard – Interview Questions appeared first on DBACLASS.