Skip to Main Content
  • Questions

  • can i cache a table on active physical standby dataguard database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, YueLin.

Asked: January 18, 2017 - 3:02 am UTC

Last updated: January 20, 2017 - 12:09 pm UTC

Version: 11.2.0.3.11

Viewed 1000+ times

You Asked

can i cache a table on active physical standby dataguard database；or，if not，can it be a new feature in the future

and Connor said...

What do mean by cache ?

In 12.2, you can have the in-memory option in a standby

Before that in 12.1, you could consider

- full database caching

https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#NEWFT502

- big table caching

https://docs.oracle.com/database/121/VLDBG/GUID-A553169D-C6CD-443E-88C3-B746D5E32923.htm#VLDBG14145

and in versions before that:

- defining a KEEP pool on the standby

https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA552

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

YueLin, January 18, 2017 - 9:58 am UTC

Thanks Tom.
I mean execute "alter table t cache".But when I execute it on my standby database ，i got an error like this:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

In my view,this may not have an impact on mrp,it can be a usefull feature if could cache a table in memory on standby database.Because,if the cache a big table in memory on my production database will have an impact on my business,so i want cache it on my standby database.
Connor McDonald
January 19, 2017 - 1:16 am UTC

Thats why I suggested the KEEP pool. You can set the table to be in the KEEP pool in Production, but not have a KEEP pool defined. You would only set the KEEP pool size to non-zero on the standby.

That's the basic principle here - the settings for the table must be the same for both Prod/Standby as you've discovered - so its about what you can manipulate outside that (ie, instance level parameters) to achieve what you're after.

YueLin, January 19, 2017 - 3:36 am UTC

Thanks a lot!
That's wonderful,i made a test,it works well.

Would you please tell me that which Oracle official Documentation said that a table can use default pool to cache table data if the table‘s storage in keep pool but the parameter db_keep_cache_size is 0 bytes？I should use it to convince my boss.

Connor McDonald
January 20, 2017 - 12:09 pm UTC

It all comes from the same set of buffers

SQL> select name, buffers from v$buffer_pool;

NAME                                        BUFFERS
---------------------------------------- ----------
DEFAULT                                      216370

SQL> alter system set db_keep_cache_size = 64m;

System altered.

SQL> select name, buffers from v$buffer_pool;

NAME                                        BUFFERS
---------------------------------------- ----------
KEEP                                           7868
DEFAULT                                      208502

SQL> alter system set db_keep_cache_size = 0;

System altered.

SQL> create table KEEP_TAB ( x int ) storage ( buffer_pool keep );

Table created.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.