Interview Q and A for Oracle Data Guard Part - 2

26.  What additional standby database mode does Oracle 11g offer?

Oracle 11g has introduced the Oracle Snapshot Standby Database. In Snapshot Standby Database a physical standby database can easily open in read-write mode and again you can convert it back to the physical standby database. This is suitable for test and development environments and also maintains protection by continuing to receive data from the production database and archiving it for later use.

27.  In Oracle 11g how can speed up backups on the standby database?

In Oracle 11g, block change tracking is now supported in the standby database.

28. With the availability of Active Data Guard, what role does SQL Apply (logical standby) continue to play?

Use SQL Apply for the following requirements: (a) when you require read-write access to a synchronized standby database but do not modify primary data, (b) when you wish to add local tables to the standby database that can also be updated, or (c) when you wish to create additional indexes to optimize read performance. The ability to handle local writes makes SQL Apply better suited to packaged reporting applications that often require write access to local tables that exist only at the target database. SQL Apply also provides rolling upgrade capability for patchsets and major database releases. This rolling upgrade functionality can also be used by physical standby databases beginning with Oracle 11g using Transient Logical Standby.

30. What is Data Guard Broker?
Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:
a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

31. What is apply services?
Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.

32. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?
Active dataguard is mostly about the physical standby.
Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.

33. Is it possible to open a 10g Physical Standby read-write, perform application testing on it, and reinstantiate it as Physical Standby using flashback technologies?

Yes, you need to use version 10g R2.

34. Which is the correct High Availability configuration for tnsnames.ora when using Data Guard?

There are different approaches to provide application high availability on a Data Guard Configuration.

The best I think it is to use the DB_ROLE_CHANGE system event to trigger a service reconfiguration that is managed with DBMS_SERVICE.MODIFY_SERVICE procedure.

This way the services used by the clients will point always to the database that is holding the primary role.

35. How I can avoid redo shipping when the standby database is not available?

First we need to check on the primary database the actual values for the standby archive log destination

SQL> show parameters log_archive_dest_state_2

NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

SQL> show parameters log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=dgfdb LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=dgfdb

Once we are sure which is the destination to defer we can execute on the primary database the defer command

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.

SQL> show parameters log_archive_dest_state_2

NAME TYPE VALUE

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

log_archive_dest_state_2 string DEFER

36. Which are 10g Data Guard Data Type restrictions?

Data Type and other restrictions do exist for Logical Standby Databases.

Logical standby databases do not support the following data types:

BFILE

Collections (including VARRAYS and nested tables)

Encrypted columns

Multimedia data types (including Spatial, Image, and Context)

ROWID, UROWID

User-defined types

XMLType

Unsupported Storage Type

Logical standby databases do not support the segment compression storage type.

Unsupported PL/SQL Supplied Packages

Packages that modify system metadata are not supported by SQL Apply:

DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH,

DBMS_REDEFINITION, DBMS_SCHEDULER, and DBMS_AQ.

Partial support for DBMS_JOB has been provided.

Job execution is suspended on a logical standby

Jobs submitted on the primary database are replicated in the standby database.

In the event of a switchover or failover, jobs scheduled on the original primary database willautomatically begin running on the new primary database.

37. Does Data Guard require configuration platforms to have identical O/S version?

The Data Guard product recommends that the configuration platforms use the same operating system version, but this is not a requirement. The important factor is the copy of the Oracle software you use and its bit level. You must use the same software media for both the standby and primary environments. Here are some examples:

Not supported:

primary - 9.2.0.5 32-bit on Solaris 2.7

standby - 9.2.0.5 64-bit on Solaris 2.8

==> Oracle 32-bit vs Oracle 64-bit are separate ports for every platform

primary - 9.2.0.4 64-bit on HP-UX

standby - 9.2.0.4 64-bit on Tru64

==> The OS are completely different ports and thus so is Oracle Supported:

primary - 9.2.0.5 32-bit on Solaris 2.8

standby - 9.2.0.5 32-bit on Solaris 2.9

primary - 9.2.0.4 64-bit on HP-UX 11.0

standby - 9.2.0.4 64-bit on HP-UX 11.11

==> In both of these cases, the same port of Oracle is certified for the different OS versions from the same vender and at the same bit level.

38. Does Data Guard require configuration platforms to have identical O/S patch release?

Different operating system patch releases can be used on different platforms.

39. Does Data Guard require platforms to have identical hardware architecture?

The Data Guard product only operates on homogenous CPU hardware configurations. By this we mean the CPU type needs to be the same for all environments. For example, both standby and primary servers should be all Sparc 64bit orall Itanium level processors. The use of heterogeneous disk hardware configurations if fully supported. So the standby can have 2x650Mhz processors and 1gig of memory while the primary has 8x2Ghz processors and 8Gig of memory. Hardware configuration should be as close as possible however to help avoid performance impact.

40. Does Data Guard require platforms to have identical Oracle versions?

It is recommended that identical Oracle major versions be used.

41. Can Data Guard physical or logical standby be used for "rolling upgrades"?

Data Guard does not support rolling upgrades in Oracle9i but will in a patch release of Oracle10g. Data Guard physical  standby can possibly be used for operating system upgrades but neither logical or physical can be used for application upgrades.

42. What is the maximum data transfer size when using synchronous network I/O?

Synchronous network I/O operations use a maximum transfer size of 1Mb (2,048 512-byte blocks); however, this maximum size is seldom achieved. Furthermore, there are no user-defined controls when using synchronous network I/O. Internal network I/O algorithms utilize non-deterministic non-blocking I/O operations for best achievable performance characteristics.

43. What is the maximum data transfer size when using asynchronous network I/O?

Asynchronous network I/O operations use a maximum transfer size of 10Mb (20,480 512-byte blocks). The maximum transfer size can be user-defined on a per-destination basis, so that the potential for data loss can be minimized.

44. What is the impact of using additional standby databases?

When using synchronous network I/O, there is an incremental "cost" associated with each additional standby database. When using asynchronous network I/O, the incremental cost is extremely minuscule.

45. When using asynchronous network I/O, is data buffered?

Asynchronous network I/O is achieved by buffering the primary database redo data on the primary-side.

46. If network connection is broken when using asynchronous network I/O, how is standby database re-synchronized?

The standby database is automatically re-synchronized with the primary database, as soon as network-based archival operations resume.

47. Is it possible to change synchronous mode and asynchronous mode dynamically?

Yes, these attributes can be specified on a per-destination basis, and can be changed dynamically. The destination attribute change takes place on the next log switch operation.

48. Is it possible to transfer redo streams to non-standby instances?

The Data Guard physical standby product supports cross-instance archival in Oracle9i Release 1. The product also supports the concept of a non-standby archivelog repository.

49. Is it possible to combine non-standby transfer with hardware mirror solution for online redo logs?

The Data Guard physical standby product does not provide integrated hardware mirroring support in Oracle9i. This includes both host-based and stand-alone hardware support.

50. Is it possible to configure multiple standby databases for a single primary database?

The Data Guard physical standby product supports up to nine standby databases per primary database, depending on other archive log destination requirements and considerations.

51. When using asynchronous network I/O, what background processes are participating at the primary and standby sites?

When using synchronous network I/O, the LGWR process itself performs the network I/O operations. When using asynchronous network I/O, the LGWR network server process (LNS) performs the network I/O operations. On the standby database, the Remote File Server (RFS) process consumes the network I/O from the primary database. There is one RFS per network connection.

52.. What methods are available to improve standby database redo apply performance?

There are several methods available to improve standby redo performance. These include, but are not limited to, the following:

- Use parallel managed recovery

- Increase standby database cache buffer size

- Use more datafiles on separate disk devices

- Use more standby redo logs on separate disk devices

- Upgrade CPU or use SMP technology

- Use smaller online redo logfiles for RAC and/or switch logfiles on the primary database more frequently to provide more linear recovery characteristics and to reduce failover latency

53. When using parallel recovery, what operation is actually performed in parallel?

The managed recovery process merges the various archivelog threads and invokes several redo apply processes to apply the redo change vectors to various datafiles.

54. When does recovery process on standby database apply transferred archivelogs?

The standby managed recovery process applies each archivelog as it becomes available on the standby database. Archivelogs are not applied until they are completely received on the standby database, even when using the LGWR  process to synchronously archive primary database redo.

55. What init.ora parameters need to be synchronized between the primary and standby databases?

Oracle recommends that to simply database switchover operations, each database should have a separate init.ora for  each role (primary and standby).

56. How does the primary database detect standby database problems or failure?

The Data Guard physical standby product is able to detect and report all standby database and network failures. However, you are responsible for querying the appropriate fixed views to identify the cause of the problem.

57. Is there any case where data can be lost while using no data loss mode?

There are no known issues with standby no-data-loss mode, assuming network connectivity is available for resynchronization of primary database failure. This includes crash and instance recovery of primary database. This also includes re-transmission of archivelogs missed during standby database downtime. However, standby no-data-loss does require the use of standby redo logs.

58. What is the best method available to synchronize primary and standby databases (hardware mirror or Oracle9i)?

The best method really depends on specific application requirements. However, it should be noted that hardware mirroring does not protect the primary database against physical corruption.

59. How long does it take to perform a switchover or switchback operation?

There are several considerations that affect the duration of the database switchover operation. These include, but are not limited to, the following:

- Active sessions must be terminated prior to starting switchover operation

- Each database switching roles must be shutdown and restarted

- Instance restart can be slightly longer due to standby database network connectivity requirements

- Pending redo apply on standby database may delay switchover to primary role

60. Is it possible to switchover or switchback when using Real Application Clusters?

Yes. There are no known restrictions.

61. Will switchover or switchback fail if other sessions are active?

Yes, switchover fails if other sessions are active. In 9.2.0.1 you can issue the switchover command to include the "with session shutdown" clause which will disconnect the sessions for you automatically.

62. Are there any methods available to speed up instance shutdown and startup?

Instance startup and shutdown is not influenced by physical standby database considerations.

63. What events require a primary to standby database failover operation?

Failover can be averted by instance and/or crash recovery of primary database (re-synchronizes standby in no data loss mode). If instance and/or crash recovery of the primary database, then database failover is required. Failover should not be used as a means of "testing" the standby database, as this operation destroys the standby  database.

64. When database failover has occurred, is there any way to decrease the time required to re-instantiate the new   standby database?

New standby databases MUST be re-instantiated from a backup of the new primary database, even in no data loss mode. Existing (surviving) standby databases do NOT need to be re-instantiated when using standby redo logs - this is the recommended failover model. Existing standby databases not using standby redo logs MUST be re-instantiated  from a backup of the new primary database.

65. Is there a way to recovery up to the last transaction during a failover operation?

Yes, there are several methods available including, but not limited to, the following:

-Using Log Apply Services FINISH recovery operation

- Using H/W mirrors of online redo logs

- Using O/S tools when database is in "hot backup" mode.

66. Does database switchover work with "system parameter files"?

Oracle recommends using two separate init.ora files for each database: one for the primary role and one for the  standby role. This facilitates easy database switchover operations. However, this is difficult when using system  parameter files. Therefore, Oracle recommends using standby client-side parameter files in which the corresponding  system parameter file is identified.

67.  What is Real-Time Apply ?

Real-Time Apply is a new feature in 10g that enables the log apply services to apply redo data (physical standby  database) or SQL (logical standby database) as it is received from Primary database 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 are applied to the standby database before failover or switchover begins.

68.   How to enable Real-Time Apply?

For Physical standby, use the following SQL statement:

SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

For Logical standby, use the following SQL statement:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

69.  How to tell if Real-Time Apply is being used ?

Use the following query from the primary database:

SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

DEST_ID RECOVERY_MODE

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

2 MANAGED REAL TIME APPLY

In the above query, the RECOVERY_MODE column will indicate if Real-Time Apply is being used.

70.  What are the Benefits of using Real-Time Apply ?

Following are the benefits of Real Time Apply....

Quicker Switchover and Failover Operations.

Instantly up-to-date Results after you open a Physical Standby Database READ ONLY

Up to Date Reporting on a Logical Standby Database and Physical Standby Database using Active Data Guard (New Feature in Oracle 11g).

The Ability to leverage larger Logfiles. Having larger Logfiles with Real Time Apply is desirable because the   Apply service will stay in a Logfile longer and the overhead of the Switch will have less Impact on the Real time Apply Processing.