• Blog
  • Get Help Now!
  • Customer Portal

BlueGecko Denmark

  • Why you need us
    • Why act now
    • Why are we unique
  • What we do
    • Products Supported 24/7
      • MySQL
      • Postgres
      • SQL Server
      • Open Source
      • OS & Storage
  • How we do it
    • Consulting
    • System Monitoring
    • Security
  • Support
    • Blog
      • Oracle
      • MySQL
      • News & Events
      • Oracle Applications
      • Amazon Web Services
      • Jeremiah Wilton’s Oradeblog
      • OurSQL Community Podcast
      • Remote DBA
    • Team viewer
    • Whitepapers
  • About Us
    • Philosophy
    • The Blue Gecko Teams
    • Employment Opportunities
    • Partners
    • News
    • Press
  • Contact Us

September 16, 2013 by lbvanting

Challenges with object invalidation and ORA-04045 in Active Data Guard STANDBY-database

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.

Filed Under: Oracle

Categories

  • Amazon Web Services
  • Configuration Management
  • downtime
  • Drizzle
  • ebs
  • Education
  • elastic block store
  • elastic compute cloud
  • hosting
  • hot backup
  • ignorance
  • Infrastructure
  • IOUG
  • Jeremiah Wilton's Oradeblog
  • misconception
  • misconceptions
  • Monocle
  • MySQL
  • MySQL Council
  • News & Events
  • Oracle
  • Oracle Applications
  • Oracle Data Guard
  • Oracle Database
  • Oracle Database Appliance
  • Our People
  • OurSQL Community Podcast
  • outage
  • parallel
  • performance
  • podcast
  • proof
  • Remote DBA
  • replication
  • S3
  • Security
  • SQL Server
  • System monitoring
  • Uncategorized

Contact Blue Gecko

  • This field is for validation purposes and should be left unchanged.

Get Our Newsletter

  • This field is for validation purposes and should be left unchanged.

The Fine Print

  • Privacy Policy
  • Terms of Use
  • When is remote DBA the right solution
  • What are remote DBA services
  • Get Help

Blog Categories

  • 24/7 support
  • Blue Gecko 360° Services
  • Products Supported 24/7

Virtual runs

Contact Blue Gecko

Blue Gecko A/S
Slotsgade 21
DK – 4200 Slagelse
Denmark
Phone: (DK) +45 70 60 51 20

Networks

  • Email
  • Twitter

© Blue Gecko Group