TutorialDBA - Support | Training | Consultant

Answer: Resolving Archive Gap Sequence on Physical Standby Databases Oracle9i has introduced many new features designed to detect and resolve gap sequences. One of the main improvements is the new Fetch Archive Log service using fal_server and fal_client parameters. In spite of these automated process, there are certain scenarios when the gap sequence cannot be avoided, and the DBA needs to intervene to resume the managed recovery on the physical standby database. Resolving Archive Gap Sequence on Physical Standby Databases Usually, a stalled managed recovery process is the indication of gap sequence. If the recovery process on the physical standby site is stopped, query the v$archive_gap view to find the gap sequence. The query find_gap.sql from code depot will assist in finding out if there is any archive log gap in the database. Select THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# From V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# -------- ----...

Answer: When you are using Dataguard, there are several scenarios when physical standby can go out of sync with the primary database. Before doing anything to correct the problem, we need to verify that why standby is not in sync with the primary. In this particular article, we are covering the scenario where a log is missing from the standby but apart from the missing log, all logs are available. Verify from v$archived_log that there is a gap in the sequence number. All the logs up to that gap should have APPLIED=YES and all the sequence# after the missing log sequence# are APPLIED=NO. This means that due to the missing log, MRP is not applying the logs on standby but the logs are still being transmitted to the standby and are available. SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG; So for example, if the missing log sequence# is 400, then the above query should show that up to sequence#399, all have APPLIED=YES and starting from 401, all are APPLIED=NO. There are ...

Question:  I have a Data Guard set-up and I seem to have some missing archived redo logs, with gaps in the log sequence.  I tried querying v$archive_gap, but I don't understand how to formally resolve gaps in my redo logs for Data Guard. Answer:  Archive redo log gaps are simply a range of archived redo logs that were created at a time when the standby database was not available to receive them.  These archive redo log gaps occur most often during network outages where the standby database fails to receive the redo logs that are transported from the primary database. When the network connectivity is restored, Data Guard's automatic transmission of redo data from the primary to the standby database should resume. Every 60 seconds the primary database does a "heartbeat" poll all of its Data Guard standby databases to check for gaps ion the transported archived redo logs. We start detecting gaps in the redo logs by querying the v$archive_gap view: SQL> sel...

Answer: A common DBA task is to perform regular clones and database refreshes of the production database for the purpose of setting up training or test or development environments. If we are having a physical standby Data Guard environment, then we can easily offload the potentially I/O and CPU intensive  backup process required for creating these clone or duplicate databases to the standby site. Here are a few examples of using the physical Standby database in a Data Guard environment to create a clone of the primary production database. In the first example we use RMAN to perform the backup and restore and in the second example we are using OS commands to just copy files ONLINE from Standby host to the target host. Note that in 11g, we can take the backup of the control file from the Standby database. In 10g, we have to take the backup of the controlfile from the primary database. Metalink states that: From 11G onwards controlfile backups are interchangible. This ...

Terminology Primary Database: Also called production database. Standby Database: It is a consistent copy of the primary DB. You can have upto 9 standby DBs for a primary. A standby database can be either a physical standby database or a logical standby database. Physical Standby: It is a physically identical copy to primary DB (in short block-on-block) on disk. Changes from primary are synched with standby using Redo-Apply method. Logical Standby: This standby DB have the same data(logical information) as in primary but the physical structure can be different. The DB is kept current using SQL Apply method. Switchover: During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role (in short roles are reversed). This is typically part of planned maintenance of the primary system. Failover: Failover happens only in the event of a disaster or failure of the primary database, and the failover results in a transition of ...

Answer: In short, the failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database. What to do in case of failover: (Important note: Nijam is the primary server and Benz is the standby server) 1. [Nijam] If the primary database is accessible and running, then it must provided  to send redo buffer to the standby database. SQL> alter system flush redo to standby_db_name; SQL>alter system archive log current; If you don’t receive an error, you can continue with step 5th. In this case, the system can be opened by zero data loss. If you receive an error, We continue with step 2 to open the system at least data loss. 2. [Benz] We must run the following query to learn last applied archive log sequence number. sql> select unique thread# as thread, max(sequence#) over (partition by thread#) as last from v$archived_log; 3. [Nijam’dan Ben...

Answer: In this article i will explained about how to configure oracle dataguard without using Rman utility and also explained what type of errors you will be faced at the time of configuring oracle dataguard and also i gave some real time interview questions and answer related to oracle dataguard   Before Dataguard configure you need to perform some postrequest oracle version and os version should be same. Ping the both server from primary to standby and standby to primary ( ping 192.168.1.100) check fairewal;l status if enaled or not using service iptables status check the port number whether available or not using netstat -plunts |grep port_number Primary database : nijam                       standby database : benz       server : 192.168.1.100               ...