This tutorial provides a quick review of what Data Guard is, and then goes on to show how you can extract value from Data Guard in every-day usage: how you can use it to enhance performance, and to reduce (yes, reduce!) your need for Oracle licences.
Opening the standby database read-only. This has in fact been possible since the automation of redo transport and redo apply when it was first introduced in release 8i.
Redo is received on the standby database but is not applied. So the redo is written to the RFS, slave to standby logs, archive to standby logs into archive logs but there's no apply done. So recoverability is not compromised. There's still no danger being lots of data. You're still receiving.
The thing is open read-only. What does that mean? It means you can run queries against it. You can offload all your heavyweight queries from the primary to the standby.
When I say all of them, even queries that require vast massive temporary space, those horrendous queries, those doing hash joins between multi gigabyte tables, they'll be generating many gigabytes of temporary data. You can write temporary data.
Provided that you're using proper temporary tablespaces which you're certain you should be, you can run any query no matter how big, no matter how much temporary data it needs.
You can also write audit data and this is important because with version 11, there's quite a lot of auditing enabled by default. By default, that auditing goes to the database, writing database tables. And you can't write to a database table in a read-only database. That we can configure for automatic switching and direct the audit records to the operating system or the trail instead.
The queries you run, why is this such a good facility? First, you're taking stress off the primary. I'm sure that we're all DBAs here and we all know how difficult it is to tune your database or a concurrent workload that includes both high volume DML and includes heavyweight queries. Basically, you can't tune that way at all.
Offloading the queries to the physical standby will reduce a lot of stress from the primary. So your DML should run significantly better and you can even do a certain amount of tuning on the standby to tune for optimal query performance. The most obvious tuning one would do, would be the memory parameters. No one could for example give the primary a massive PGA aggregate target and the much smaller SGA target or use indeed automatic memory management, perhaps cell tuning.
One can adjust many parameters to do with tuning the optimizer to drive the optimizer to a certain execution plan, to exploit the environments. All that can be done on an open read-only database.
How does one do it? It really couldn't be simpler.
If we look at my physical standby here, this thing database role, physical standby - and if we select the open mode from v$ database, we find this in mount mode.
How do we actually open at read-only? First off, we have to stop the recovery process because remember redo is received but no applied. You have to cancel the recovery. So "alter database recover managed standby database cancel."
That will give us a read consistent version of the database.
The command is intelligent enough to know that there might be incomplete transactions. Long-run transactions could've been partially received. We will end up with a read consistent version.
To cancel it, all we do is alter database open read-only.
It is that simple.