A common frustration with standby is that your database is doing nothing else than applying logs. One may want to run some reports on it.
Usually, the database is MOUNTED and not OPEN. This means, apart from selecting from DUAL and performance views like v$$managed_standby or v$session, there is little you can do.
Possibly, you can cancel the recovery and open in read only mode.
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
or
Dataguard edit database db01_sb set state='APPLY-OFF'
Now we can open the database
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ ONLY
Now we can query the database
SQL> select count(*) from dba_objects;
COUNT(*)
----------
22783
but not write
SQL> create table t(x number);
create table t(x number)
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
if you need to write, there is more than one way to do it. Either logical standby, or snapshot standby, or create an additional database and create database links and synonyms. My preferred option would be golden gate. But this is beyond the scope of this post.
A good option is to open it without stopping the apply process…
DGMGRL> edit database db01_sb set state='APPLY-ON';
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
This good but is bounded to the Active Dataguard licensing option (EE).
There are also a few differences
One is that you cannot compile view on the fly.
Primary
SQL> create or replace force view bar as select * from foo;
Warning: View created with compilation errors.
SQL> create table foo(x number);
Table created.
SQL> select status from user_objects where object_name='BAR';
STATUS
---------------
INVALID
The view is invalid, but a select would compile. But not on standby read only
Standby
SQL> select * from bar;
select * from bar
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of BAR
ORA-16000: database or pluggable database open for read-only access
Primary
SQL> select * from bar;
no rows selected
SQL> select * from bar;
no rows selected
A more worrying issuing is security. On your main system, you have failed login attempts
Primary
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 3;
Profile altered.
SQL> create user u identified by p;
User created.
SQL> conn u/a@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/b@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/c@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/d@db01_prim
ERROR:
ORA-28000: the account is locked
SQL> conn / as sysdba
Connected.
SQL> alter user u account unlock;
User altered.
On the standby, since it is read only, the last tentatives are not recorded.
SQL> conn u/e@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/f@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/g@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn u/h@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
which allows you unlimited login attempts
As well, audit records are not generated.
Primary
SQL> audit session;
Standby
SQL> conn u/xxx@db01_sb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> SELECT USERNAME,ACTION_NAME,TIMESTAMP,RETURNCODE FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC;
no rows selected
No audit record from ORA-01017
Primary
SQL> conn u/xxx@db01_prim
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> SELECT USERNAME,ACTION_NAME,TIMESTAMP,RETURNCODE FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC
USER ACTION TIMESTAMP RETURNCODE
---- ------ ------------------- ----------
U LOGON 2019-06-14_15:39:05 1017
On primary, audit records are saved as expected. There are many other things/tools that won’t work the same way. Because it is read-only. Use with care if you are entitled too.
