Saturday , December 9 2023

Oracle Active Data Guard

What is Oracle Active Data Guard?

Oracle Active Data Guard is a Physical Standby Option available with Enterprise Edition. Thanks to Active Data Guard, we do not leave Data Guard idle. It contains many features like below. It licensed. Active Data Guard provides automatic block repair feature. With this feature, a DML operation on the Primary side can be seen instantly on the Standby side.

  • Real-Time query,
  • We can get Fast Incremental Backup on physical standby database,
  • Can fix automatic block corruption,
  • Far SYNC feature we can use

If a block is found to be corrupt as a result of a query run in the primary database, the corrupted block is automatically repaired by taking it from the Physical Standby Database, which is the Active Data Guard.

If a block is found to be corrupt as a result of a query run in Physical Standby, the corrupted block is taken from the Primary Database and automatically repaired.

If the Physical Standby database is not Active Data Guard then this is done manually.

If a single block will be repaired;

If more than one Block in more than one Datafile will be repaired;

When these commands are executed, Oracle searches the block that is corrupt from the following;

  • Physical Standby Database
  • Flashback Logs
  • Full veya Level 0 incremental Backup

If you do not want to search for uncorrupted block from Standby Database, use the following command;

How To Enable Active Data Guard?

We can switch the physical standby database in mount mode to the active data guard as follows.

Step1: Check standby database recovery mode.

Step2: Stop Recovery.

Step3: Enable Active Data Guard.

Step4: Check Database Role.

Step5: Start Recovery.

Step6: Check Database Role After Recovery.

Step7: Try to login from third party clients (TOAD, SQL Developer).

Step8: Check database status.

Step9: Perform he same operations in the other Node and check the database role.

How To Disable Active Data Guard?

Step1: Shutdown Instances on the standby side and open them in Mount mode.

Step2: Enable Real Time Apply. I recommend you read the article named “What is Real Time Apply in Dataguard”

Step3: Check the instance status and the role of Databases.

Step4: On the primary side, Log Switch operation is performed separately from both Node.

Step5: Check whether Redo-Transport is coming to standby side.

Step6: By checking the LAG status, check whether the redos transported are applied or not.

Create Global Temporary Tables On Active Data Guard

Active Data Guard is a physical standby database that can only be used read-only. Inserting data to the Temprorary table is not allowed, as it does not allow any operations to produce Redo on it.

Temporary Tablespace is used during insert data into the Temproray table. A Redo is not produced for this process, but since Undo Tablespace is used for Undo, Oracle internally produces Redo. For this reason, inserting data is not allowed to Global Temprorary table in versions before 12c. When you try to insert data to Global Temprorary table, you receive the following error.

ORA-16000: database open for read-only access

temp_undo_enabled in oracle 12c

In 12c, this situation is solved with the TEMP_UNDO_ENABLED parameter. For transactions using Temporary Tablespace, Temporary Undo Tablespace is used. For this reason, no Redo is produced and data can be inserted into Temporary Tables.

This parameter can be used in both Primary and Physical Standby. When used on the primary side, Redo production decreases 100 times , while on the Active Data Guard side, no Redo is produced.

In Active Data Guard, this parameter is enabled by default.

temp_undo_enabled Example

Step1: Create a user in the primary database.

Step2: Grant permission to the user so that the user can connect to the database.

Step3: Grant permission to the user so that the user can create a temporary table.

Note: The reason why CREATE TABLE is granted is that it cannot create any other table other than temporary table because it is already a Read-only database. If the user wants to create another table using this authorization, he/she gets the error as follows.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access

Step4: Grant permission to user so that the user can query for objects.

Step5: Create a Temporary table by switching to TEMPUNDO user.

Note: The reason I wrote ON COMMIT DELETE ROWS while creating the table is that I want the data in the table to be deleted at the end of the session.

Step6: Open autotrace to see statistics.

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

Make some adjustments to fix the error.

Switch to TEMPUNDO user and open trace.

Step7: Insert into temp table to see statistics.

Step8: Let’s see how much Undo is produced for this Redo produced.

USED_UBLK: Number of Undo Blocks Used
USED_UREC: Undo Records Used

Step9: Set the TEMP_UNDO_ENABLED parameter to true and let’s see the REDO produced as a result of the same test.

Step10: Login with the TEMPUNDO user and open trace.

Step11: Insert into temp table to see produced redo.

As can be seen, while TEMP_UNDO_ENABLED parameter is FALSE, 3070280 bytes REDO is produced as a result of the same bulk insert, and 528 bytes REDO is produced when the parameter is set to TRUE.

Step12: The actual effect of the parameter is seen in the Active Data Guard Physical Standby Database.

Query the current value of the parameter:

Step13: Check whether the “user and the table” created on the primary side are on the Standby side.

Step14: Open the trace by connecting with the TEMPUNDO user.

Step15: Bulk insert in the same way.

Since REDO is not produced, data can be inserted into this TEMPORARY table.



Leave a Reply

Your email address will not be published. Required fields are marked *