At one of our customers Oracle Active Data Guard is being utilized for resource intensive BI queries during normal business hours.
The setup is based on 11.2.0.3 EE on OEL 6.4 and everything is running just smooth.
One issue has been identified – namely implicit invalidation of database objects, e.g. VIEWS and PL/SQL procedures.
The invalidation occurrs when underlying objects are being modified, e.g. a table column.
Let’s illustrate this through the following test case.
On the PRIMARY database we establish a table called TESTTAB1 in the schema BGDBA:
As a DBA on the PRIMARY database create the BGADBA user and grant appropriate privileges:
create user bgdba identified by bgdba default tablespace users temporary tablespace temp;
alter user bgdba quota unlimited on users;
grant create session, create table, create view to bgdba;
Now connect to the PRIMARY database as BGDBA and create the TESTTAB1:
SQL> conn bgdba/bgdba
SQL> create table testtab1 as select * from all_objects;
Table created.
SQL> select count(*) from testtab1;
COUNT(*)
----------
63467
Now let’s create a VIEW on top of the TESTTAB1 table, as BGDBA on the PRIMARY database:
SQL> create view testtab1_v as select * from testtab1;
SQL> select object_name,status from all_objects
2 where OBJECT_TYPE='VIEW'
3* and object_name like 'TESTTAB1%';
OBJECT_NAME STATUS
------------------------------ -------
TESTTAB1_V VALID
Everything is fine on the PRIMARY – let’s check on the STANDBY-database as well, first logon as SYSDBA to the STANDBY:
SQL> conn / as sysdba
Connected.
SQL> select log_mode,open_mode , database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY
We see that OPEN_MODE is “READ ONLY WITH APPLY”, so let’s connect as BGDBA, which now should be possible:
SQL> conn bgdba/bgdba
Connected.
SQL> select object_name,status from all_objects
2 where OBJECT_TYPE='VIEW'
3 and object_name like 'TESTTAB1%';
SQL>
OBJECT_NAME STATUS
------------------------------ -------
TESTTAB1_V VALID
The BGDBA VIEW is now also present on the STANDBY-database.
Now let’s go back to the PRIMARY – as BGDBA try to invalidate the view, e.g.
SQL> alter table testtab1 modify(edition_name varchar2(40));
Table altered.
SQL> select object_name,status from all_objects
2 where OBJECT_TYPE='VIEW'
3 and object_name like 'TESTTAB1%';
OBJECT_NAME STATUS
------------------------------ -------
TESTTAB1_V INVALID
As we see from the above the VIEW is now “invalid”.
ON STANDBY in ACTIVE DATAGUARD mode as BGDBA:
SQL> select count(*) from testtab1_v;
select count(*) from testtab1_v
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of BGDBA.TESTTAB1_V
ORA-16000: database open for read-only access
SQL> select object_name,status from all_objects
where OBJECT_TYPE='VIEW'
and object_name like 'TESTTAB1%';
OBJECT_NAME STATUS
------------------------------ -------
TESTTAB1_V INVALID
So because the STANDBY-database is open in READ/ONLY mode, then the implicit recompilation of the view cannot complete and we hit the ORA-04045 error.
Solution: Fix/recompile the object on the PRIMARY database and then reissue the SQL query on the STANDBY database.
On the PRIMARY database as BGDBA:
SQL> alter view testtab1_v compile;
View altered.
NOTICE: This could in this case also have been accomplished simply by selecting from the VIEW, thereby force a recompilation.
SQL> select object_name,status from all_objects
2 where OBJECT_TYPE='VIEW'
3 and object_name like 'TESTTAB1%';
OBJECT_NAME STATUS
------------------------------ -------
TESTTAB1_V VALID
Now we can issue the query against the VIEW on the STANDBY-database:
SQL> select count(*) from testtab1_v;
COUNT(*)
----------
63467
Lesson learned:
When querying against an Active Data Guard physical STANDBY-database, you must handle all changes, e.g. object compilation, on the PRIMARY database and let this be propagated to the STANDBY database automatically through REDO APPLY.
Thanks to Kim Berg Hansen for providing input to this blog.