DATA GUARD

 DATA GUARD

 

What are different types of modes in Data Guard and which is default?

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.

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.

 

What is the pre-requisite for creating standby DB?

Primary DB must be in force logging mode.

What is the pre-requisite for switchover to standby DB?

No session connected to standby or primary DB.

select switchover_status from v$database;

‘To Primary’ or ‘To Standby’ and not ‘Session Active’

 

Insufficient disk space or UNNAMED or MISSING in the standby database

There are many reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) Improper parameter settings related to file management.

alter database create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536' as new;

 

How many standby databases we can create (in 10g/11g)?

Till Oracle 10g, 9 standby databases are supported.

From Oracle 11g R2, we can create 30 standby databases.

 

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

 

What are differences between physical, logical, snapshot standby and ADG (or) what are different types of standby databases?

 

Physical standby – in mount state, MRP will apply archives

ADG – in READ ONLY state, MRP will apply archives

Logical standby – in READ/WRITE 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.

 

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 back 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 are new features in 11g Data Guard?

Here is some data guard category and there enhancement

1) Data Protection

Advanced Compression

Lost-write protection

Fast-Start Failover

2) Increase ROI

Active Data Guard

Snapshot Standby

3) High Availability

Faster Redo Apply

Faster failover & switchover

Automatic Failover using ASYNC

4) Manageability

Mixed Windows/Linux

 

What are the uses of standby redo log files?

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

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

This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.

 

What is dg_config ?

Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration.

The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data.

 

 What is RTA (real time apply) mode MRP?

Real-time apply where before log shipping the LGWR process writes to a standbylog file simultaneously along with the online redolog file.

This standby logfile is written to standby log file on standby server. There is no loss of any committed transaction whatsoever in Real-Time Apply scenario.

In Real Time Apply, once a transaction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log at the Primary

MRP - Managed recovery process - For Data Guard, the background process that applies archived redo log to the standby database.

 

What is StaticConnectIdentifier property used for?

11gr2 new database property, StaticConnectIdentifier, which allows the user to specify a static connect identifier that the DGMGRL client will use to start database instances.

 

What is the difference between normal MRP (managed apply) and RTA MRP (real time apply)?

The difference between Redo Apply & Real-Time Apply

------------------------------------------------------

Normally, by default, Archiver processes will be responsible for Redo Transport from Primary to Standby.

Once a log switch happens on the Primary, the online redo log is archived in the Local Archive destination as pointed to by Log_archive_dest_1

by an Archiver process.

Another Archiver process will then transmit the redo to the remote standby destination as indicated by Log_archive_dest_2.

Data Guard Remote File Server (RFS) Process on the Standby then writes redo data from the Standby redo log file to archive redo log file.

Log apply services then makes use of Managed Recovery Process (MRP) process to apply the redo to the standby database.

This method of propagating redo from the primary to standby is called Redo Apply and it happens only on log switch at the Primary.

When using Redo Apply mode, the status of MRP in v$managed_standby view will show as WAIT_FOR_LOG.

Real Time Apply, in contrast, uses either LGWR or Archiver on the Primary to write redo data to Standby Redo log on the Standby and Log Apply Services can apply the redo data in real-time without the need of the current standby redo log being archived. Once a transaction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log.

When using Real Time Apply mode, the status of MRP in v$managed_standby view will show as APPLYING_LOG.

 

What are the background processes involved in Data Guard?

MRP, LSP, 

What is failover/switchover (or) what is the difference between failover & switchover?

Switchover – This is done when both primary and standby databases are available. It is pre-planned.

Failover – This is done when the primary database is NO longer available (ie in a Disaster). It is not pre-planned.

Why extra standby redo log group?

Determine the appropriate number of standby redo log file groups.Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database....

(maximum number of logfiles for each thread + 1) * maximum number of threads

Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database."

I think it says that if you have groups #1 and #2 on primary and #1, #2 on standby, and if LGWR on primary just finished #1, switched to #2, and now it needs to switch to #1 again because #2 just became full, the standby must catch up, otherwise the primary LGWR cannot reuse #1 because the standby is still archiving the standby's #1. Now, if you have the extra #3 on standby, the standby in this case can start to use #3 while its #1 is being archived. That way, the primary can reuse the primary's #1 without delay.

 

Why Primary DB stop shipping archive log or redo to standby server?

->Check sys password change on the primary db, verify the password file timestamp on primary and standby server.

->DG Broker might have changed the init parameter.

->Check listener or TNS entry.

->Check Network

 

Snapshot Standby

in Oracle Database 11g, where the physical standby database can be temporarily converted into an up dateable one called Snapshot Standby Database. In that mode, you can run your app—which may modify a lot of tables—and gauge its impact. Once the impact is assessed, you can convert the database into a standby undergoing the normal recovery. This is accomplished by creating a restore point in the database, using the Flashback database feature to flashback to that point and undo all the changes

First, start recovery on the standby, if not going on already:

 

SQL> alter database recover managed standby database disconnect;

SQL> alter database recover managed standby database cancel;

At this point, you may create the snapshot standby database. Remember, it enables Flashback logging, so if you haven't configured the flash recovery area, you will get a message like this:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/12/2008

00:23:14'.

ORA-38786: Flash recovery area is not enabled.

To avoid that, you should have already created flash recovery area. If you didn't, don't worry, you can create it now:

SQL> alter system set db_recovery_file_dest_size = 2G;

SQL> alter system set db_recovery_file_dest= '/db_recov';

Now that the formalities are completed, you can convert this standby database to snapshot standby using this simple command:

SQL> alter database convert to snapshot standby;

Now recycle the database:

SQL> shutdown immediate

SQL> startup

Now the database is open for read/write operations:

SQL> select open_mode, database_role  from v$database;

OPEN_MODE  DATABASE_ROLE

---------- ----------------

READ WRITE SNAPSHOT STANDBY

 

You can do changes in this database now. This is a perfect place to replay the captured workload using Database Replay. You can then perform the system changes in this database and replay several times to see the impact of the changes. As this is a copy of the production database, the replay will be an accurate representation of the workload.

 

After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database. Just follow the steps shown below:

     

SQL> shutdown immediate

SQL> alter database convert to physical standby;

 Now shutdown, mount the database and start managed recovery.

SQL> shutdown

SQL> startup mount

Start the managed recovery process:

SQL> alter database recover managed standby database disconnect;

 

Now the standby database is back in managed recovery mode. Needless to say, when the database was in snapshot standby mode, the archived logs from primary were not applied to it. They will be applied now and it may take some time before it completely catches up.

 

Active Data Guard

With Oracle Database 11g, that situation changes: You can open the physical standby database in read-only mode and restart the recovery process. This means you can continue to be in sync with primary but can use the standby for reporting

->Stop Managed Recovery process on standby.

->Open standby database as read-only.

->Restart managed recover process on the standby db.

alter database recover managed standby database using logfile disconnect;

Now the standby db is updated but it is simultaneously open for read-only.

 

How can we clone database with rman backup?

DUPLICATE DATABASE TO DB11G  FROM ACTIVE DATABASE   SPFILE  NOFILENAMECHECK;

 

Setting Archive Tracing for Data Guard

alter system set log_archive_trace=15

By default the log_archive_trace parameter is set to zero, meaning archive log tracing is disabled.

Level 8192: Tracks redo apply activity (media recovery or physical standby)

 

 

Data Guard Broker

alter system set dg_broker_start=TRUE/FALSE

alter system set  dg_broker_config_file1='file_name1’

alter system set  dg_broker_config_file2='file_name2’

Create Configuration

DGMGRL>CREATE CONFIGURATION 'DG_BBIDEV' AS PRIMARY DATABASE IS 'BBIDEV' CONNECT IDENTIFIER IS 'BBIDEV';

Verify configuration

DGMGRL> show configuration;

Verify database;

DGMGRL> show database 'BBIDEV';

Add standby database to the configuration

DGMGRL> ADD DATABASE 'BBIDEV_SB' AS CONNECT IDENTIFIER IS BBIDEV_SB MAINTAINED AS PHYSICAL;

Enable or Disable the broker

DGMGRL> ENABLE/DISABLE CONFIGURATION;

Verify Configuration

DGMGRL>show database 'BBIDEV' StatusReport;

DGMGRL>show database 'BBIDEV' LogxpStatus;

DGMGRL>show database 'BBIDEV' InconsistentProperties;

DGMGRL>Edit database  'BBIDEV' SET PROPERTY 'LogXpMode'='SYNC';

Performing a Switchover Operation

DGMGRL>Switchover to 'STDBY_BBIDEV';

Remove the broker configuration

DGMGRL> REMOVE CONFIGURATION;