Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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;
1 | RECOVER DATAFILE 6 BLOCK 3; |
If more than one Block in more than one Datafile will be repaired;
1 | RECOVER DATAFILE 2 BLOCK 43,79 DATAFILE 6 BLOCK 183; |
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;
1 | RECOVER BLOCK … EXCLUDE STANDBY |
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.
1 2 3 4 5 6 | SQL> select status from gv$instance; STATUS ------------ MOUNTED MOUNTED |
Step2: Stop Recovery.
1 2 3 | SQL> alter database recover managed standby database cancel; Database altered. |
Step3: Enable Active Data Guard.
1 2 3 | SQL> alter database open read only; Database altered. |
Step4: Check Database Role.
1 2 3 4 5 | SQL> select OPEN_MODE, DATABASE_ROLE from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY PHYSICAL STANDBY |
Step5: Start Recovery.
1 2 3 | SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
Step6: Check Database Role After Recovery.
1 2 3 4 5 | SQL> select OPEN_MODE, DATABASE_ROLE from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY WITH APPLY PHYSICAL STANDBY |
Step7: Try to login from third party clients (TOAD, SQL Developer).
Step8: Check database status.
1 2 3 4 5 6 | SQL> select status from gv$instance; STATUS ------------ OPEN MOUNTED |
Step9: Perform he same operations in the other Node and check the database role.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open read only; Database altered. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> select OPEN_MODE, DATABASE_ROLE from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
How To Disable Active Data Guard?
Step1: Shutdown Instances on the standby side and open them in Mount mode.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. |
Step2: Enable Real Time Apply. I recommend you read the article named “What is Real Time Apply in Dataguard”
1 2 3 | SQL> alter database recover managed standby database using current logfile disconnect; Database altered. |
Step3: Check the instance status and the role of Databases.
1 2 3 4 5 6 7 8 9 10 11 12 | SQL> select status from gv$instance; STATUS ------------ MOUNTED MOUNTED SQL> select OPEN_MODE, DATABASE_ROLE from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PHYSICAL STANDBY |
Step4: On the primary side, Log Switch operation is performed separately from both Node.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 77 1 40 2 SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 77 1 42 2 |
Step5: Check whether Redo-Transport is coming to standby side.
1 2 3 4 5 6 | SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 77 1 42 2 |
Step6: By checking the LAG status, check whether the redos transported are applied or not.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> column name format a22 SQL> column value format a15 SQL> column value format a30 SQL> column datum_time format a20 SQL> column time_computed format a20 SQL> set linesize 9000 SQL> SQL> select * from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED DATUM_TIME ---------------------- ------------------------------ ------------------------------ -------------------- -------------------- transport lag +00 00:00:00 day(2) to second(0) interval 01/03/2017 21:57:43 01/03/2017 21:57:42 apply lag +00 00:00:00 day(2) to second(0) interval 01/03/2017 21:57:43 01/03/2017 21:57:42 apply finish time +00 00:00:00.000 day(2) to second(3) interval 01/03/2017 21:57:43 estimated startup time 33 second 01/03/2017 21:57:43 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Physical-1] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; INSERT INTO my_temp_table * ERROR at line 1: 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.
1 2 3 | [Primary] SQL> create user tempundo identified by "1" account unlock; User created. |
Step2: Grant permission to the user so that the user can connect to the database.
1 2 3 | [Primary] SQL> grant create session to tempundo; Grant succeeded. |
Step3: Grant permission to the user so that the user can create a temporary table.
1 2 3 | [Primary] SQL> grant create table to tempundo; Grant succeeded. |
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
1 2 3 4 5 6 | [Physical] SQL> create table employees_yedek as select * from hr.employees; create table employees_yedek as select * from hr.employees * 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.
1 2 3 4 5 6 7 | [Primary] SQL> grant select on v_$tempundostat to tempundo; Grant succeeded. [Primary] SQL> grant select on v_$transaction to tempundo; Grant succeeded. |
Step5: Create a Temporary table by switching to TEMPUNDO user.
1 2 3 4 | [Primary] SQL> conn tempundo/1 Connected. [Primary] SQL> show user USER is "TEMPUNDO" |
1 2 3 | [Primary] SQL> create global temporary table my_temp_table (id number, description varchar2(20)) on commit delete rows; Table created. |
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.
1 2 3 | [Primary] SQL> SET AUTOTRACE ON STATISTICS; SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report |
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.
1 2 3 4 | [Primary] SQL> conn / as sysdba Connected. [Primary] SQL> show user USER is "SYS" |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [Primary] SQL> create role plustrace; Role created. [Primary] SQL> grant select on v_$sesstat to plustrace; Grant succeeded. [Primary] SQL> grant select on v_$statname to plustrace; Grant succeeded. [Primary] SQL> grant select on v_$session to plustrace; Grant succeeded. [Primary] SQL> grant select on v_$mystat to plustrace; Grant succeeded. [Primary] SQL> grant plustrace to dba with admin option; Grant succeeded. [Primary] SQL> grant plustrace to tempundo; Grant succeeded. |
Switch to TEMPUNDO user and open trace.
1 2 3 4 5 6 | [Primary] SQL> conn tempundo/1; Connected. [Primary] SQL> show user USER is "TEMPUNDO" [Primary] SQL> set autotrace on statistics; |
Step7: Insert into temp table to see statistics.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Primary] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; 1000000 rows created. Statistics ---------------------------------------------------------- 22 recursive calls 15687 db block gets 2359 consistent gets 343 physical reads 3070280 redo size 861 bytes sent via SQL*Net to client 986 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed |
Step8: Let’s see how much Undo is produced for this Redo produced.
1 2 3 4 5 6 7 8 9 10 | [Primary] SQL> SELECT t.used_ublk, 2 t.used_urec 3 FROM v$transaction t, 4 v$session s 5 WHERE s.saddr = t.ses_addr 6 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); USED_UBLK USED_UREC ---------- ---------- 653 12476 |
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.
1 2 3 4 5 6 7 8 9 10 11 | [Primary] SQL> conn / as sysdba Connected. [Primary] SQL> alter system set temp_undo_enabled=TRUE scope=both; System altered. [Primary] SQL> show parameter temp_undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean TRUE |
Step10: Login with the TEMPUNDO user and open trace.
1 2 3 4 5 6 | [Primary] SQL> conn tempundo/1 Connected. [Primary] SQL> show user USER is "TEMPUNDO" [Primary] SQL> set autotrace on statistics |
Step11: Insert into temp table to see produced redo.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Primary] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; 1000000 rows created. Statistics ---------------------------------------------------------- 23 recursive calls 15365 db block gets 2357 consistent gets 16 physical reads 528 redo size 857 bytes sent via SQL*Net to client 986 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed |
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:
1 2 3 4 5 | [Physical] SQL> show parameter temp_undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE |
Step13: Check whether the “user and the table” created on the primary side are on the Standby side.
1 2 3 4 5 6 7 8 9 10 11 | [Physical] SQL> select username from dba_users where username='TEMPUNDO'; USERNAME -------------------------------------------------------------------------------- TEMPUNDO [Physical] SQL> select object_name from dba_objects where owner='TEMPUNDO'; OBJECT_NAME -------------------------------------------------------------------------------- MY_TEMP_TABLE |
Step14: Open the trace by connecting with the TEMPUNDO user.
1 2 3 4 5 6 | [Physical] SQL> conn tempundo/1 Connected. [Physical] SQL> show user USER is "TEMPUNDO" [Physical] SQL> set autotrace on statistics |
Step15: Bulk insert in the same way.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [Physical] SQL> INSERT INTO my_temp_table 2 WITH data AS ( 3 SELECT 1 AS id 4 FROM dual 5 CONNECT BY level < 10000 6 ) 7 SELECT rownum, TO_CHAR(rownum) 8 FROM data a, data b 9 WHERE rownum <= 1000000; 1000000 rows created. Statistics ---------------------------------------------------------- 23 recursive calls 15397 db block gets 2341 consistent gets 152 physical reads 0 redo size 857 bytes sent via SQL*Net to client 987 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1000000 rows processed |
Since REDO is not produced, data can be inserted into this TEMPORARY table.
