Categories
dataguard

Active Dataguard : read only with apply

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.

Categories
dataguard dba

Changing the log apply delay (DelayMins)

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet.


DGMGRL> show database sDB01 delaymins
DelayMins = '5'

DGMGRL> edit DATABASE sDB01 set property delaymins=2;
Property "delaymins" updated

ARC3: Archive log thread 1 sequence 3199 available in 5 minute(s)
Tue Dec 03 15:34:59 2013
ARC0: Archive log thread 1 sequence 3200 available in 2 minute(s)
Tue Dec 03 15:35:15 2013

SQL> select sysdate, SEQUENCE# from v$managed_standby where process='MRP0'

SYSDATE SEQUENCE#
------------------- ----------
2013-12-03_15:38:00 3199

The old logs are not affected. Let’s wait until the latest Delay=5 got applied.


Tue Dec 03 15:40:02 2013
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3199_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3200_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3201_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3202_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3203_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3204_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3205_827686279.arc

All files which had a delay=2 were “pending” apply. Now we got the apply=2 behavior

Same if you increase the value


DGMGRL> edit DATABASE sDB01 set property delaymins=30;
Property "delaymins" updated

SQL> select sysdate, SEQUENCE# from v$managed_standby where process='MRP0';

SYSDATE SEQUENCE#
------------------- ----------
2013-12-03_15:49:04 3224

ARC3: Archive log thread 1 sequence 3224 available in 2 minute(s)
Tue Dec 03 15:47:22 2013

Here again, the old logs are not affected, we need to wait until the last delay=2 got applied to get a delay=30 behavior.

While you cannot change the delay, there is still a way to workaround the problem.

If you want to immediately increase log to 30 minutes, turn off applying for half an hour.


DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.
-- coffee break
DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.

If you want to decrease log from 30 to 2 minutes right now and immediately apply the old logs which have reached this threshold, use sqlplus

ARC1: Archive log thread 1 sequence 3253 available in 30 minute(s)
Tue Dec 03 16:01:26 2013
ARC3: Archive log thread 1 sequence 3254 available in 2 minute(s)
Tue Dec 03 16:01:37 2013

DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.

SQL> recover automatic standby database until time '2013-12-03_16:01:30';
Media recovery complete.

DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.

I wrote on delay standby failover here : here

Categories
dataguard

how to rename datafiles on standby

If you rename a file with alter database on primary, it will not occur on standby. If you want to rename it on standby to, you need to do it manually

  1. cancel the recovery (set dg_broker_start to false and restart db in mount status if you use dataguard)
  2. set the standby file management to manual alter system set standby_file_management=manual
  3. move the file with OS commands as you did on the primary
  4. rename the file with alter database rename file ‘old’ to ‘new’
  5. reenable automatic standby file management and dg broker and restart the recovery (start db in mount status if you use dataguard)
Categories
dataguard dba

Fast start failover

There are loads of things to do to prepare you for a fast start failover.

First you must have a working set of primary / standby database
Then you must have both databases in flashback mode.
Then verify your dataguard configuration with OEM.

Ok, once you are so far, you will need to review your dataguard property.

lsc01 my primary, lsc05 my standby

edit database lsc01 set LogXptMode='SYNC';
edit database lsc01 set FastStartFailoverTarget= 'lsc05'
edit database lsc05 set LogXptMode='SYNC';
edit database lsc05 set FastStartFailoverTarget= 'lsc01'
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

then you need to start the observer in the background :

nohup dgmgrl -silent sys/*** "start observer" &

note: it does not work if you connect with /. You will get DGM-16979 if you use / or if you use different passwords in standby and primary.

so far so good, let’s enable fast_start failover in dgmgrl

ENABLE FAST_START FAILOVER

Before you switch, check the listener.ora is correctly configured :

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=LSC01_DGMGRL.example.com)
(SID_NAME=LSC01)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=LSC05_DGMGRL.example.com)
(SID_NAME=LSC05)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = precision.example.com)(PORT = 1521))
)

The GLOBAL_DBNAME is mandatory to enable a painless switchover. Otherwise the standby startup will fail with ORA-12514.

UPDATE: alternatively in 11gR2 you can set the dataguard property StaticConnectIdentifier to use SID instead of service name :

edit database lsc05 set property StaticConnectIdentifier=
'(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=precision.example.com)(PORT=1521))
(CONNECT_DATA=(SID=LSC05)))';
edit database lsc01 set property StaticConnectIdentifier=
'(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=precision.example.com)(PORT=1521))
(CONNECT_DATA=(SID=LSC01)))';

in this case you will not need global_dbname in listener.ora. See note 308943.1

Ok, let’s see how fast I can switch

$ time dgmgrl -silent sys/*** "switchover to lsc05"
Performing switchover NOW, please wait...
New primary database "lsc05" is opening...
Operation requires shutdown of instance "LSC01" on database "lsc01"
Shutting down instance "LSC01"...
ORACLE instance shut down.
Operation requires startup of instance "LSC01" on database "lsc01"
Starting instance "LSC01"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "lsc05"

real 1m10.685s
user 0m0.052s
sys 0m0.109s

About one minute. most of the time was spent restarting the original primary as a standby, the primary was already available for queries after about 20 seconds.