Oracle DBA – A lifelong learning experience

Preventing standby databases opening in Active DataGuard mode + chopt !!!

Posted by John Hallas on August 6, 2014

It is long been a concern of mine that it is relatively easy to open a standby database  inadvertently and then be potentially liable to a licensing cost as usage is shown in the DBA_FEATURE_USAGE_STATISTICS view.

In fact we logged an SR on the subject based on experiences on failing over using DataGuard Broker

On dgmgrl switchover the Oracle restart database ‘start option’ property is not modified to ‘mount’ database even though dgmgrl modifies the Oracle restart database role property to PHYSICAL_STANDBY. Nor does a dgmgrl switchover modify the Oracle restart database ‘start option’ property to ‘open’ database even though dgmgrl modifies the Oracle restart database role property to PRIMARY. The potential is there to accidentally open a standby database and invoke Active dataguard on any reboot/restart of services which is what we do not want.

After a fair bit of discussion we got this rather unhelpful response

Starting in 11, broker no longer changes the state of the database, i.e., from mount to open.  It is up to the user what state they want their database in and to setup start_option appropriately.  
In the course of role changes or other operations that involve database restarts, the broker will restart the database based on the role of the database, i.e., open for PRIMARY database and mounted for PHYSICAL STANDBY.  
If the user wants the new standby to only be in the mounted mode for “srvctl start database …”, they must manually change the start_options for the databases appropriately after the role change – they will have to change it to open for the new primary as well.
In other words, what you are seeing is as designed!
We recommend that you leave the start option to OPEN for both databases, so that regardless of who the primary is, you can be sure it will be opened after a role change. This of course means the standby will be opened, which is okay as long as the user has paid for the Active DG option.
If the user has not paid for the Active DG option, you should modify the start option for both databases prior to the role change, so that after the role change the new primary will open and the new standby will be mounted.
The broker does not modify the startup options during a role change in the Clusterware Repository. It only modifies the role of the database.
Set the start options to OPEN for both databases and you won’t run into this issue.

They did provide a script but we ended up using a startup trigger something like this

CREATE OR REPLACE TRIGGER SYS.DB_ROLE
AFTER STARTUP ON DATABASE
DECLARE
CTL varchar(10);
BEGIN
SELECT CONTROLFILE_TYPE INTO CTL FROM V$DATABASE;
IF CTL = 'STANDBY' THEN
execute immediate 'alter system set dg_broker_start=FALSE scope=memory';
END IF ;
END DB_ROLE;

However that is not the purpose of this post. It is more about coming across an undocumented parameter from 11Gr2 which enforces that a standby cannot be opened (and activate the need for an ADG license) providing the MRP process is running. I think that came from a  post by Fritz Hoogland on the Oracle-L list. This is exactly what we wanted and a colleague Hamid Ansari prepared a summary and test of the parameter

In Oracle11g, there is a new option called Oracle Active Data Guard. This is almost the same as what was described before with one major difference. The Archive logs are shipped and applied to the standby database. This would make the standby database up to date with the primary database. However you need additional licenses to use this feature which are expensive.

To start the standby database in Real Query mode; we perform the following steps;

SHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER DATABASE OPEN READ ONLY;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The above statements and commands are fully described in oracle documentation and papers.  There is the oracle hidden parameter “_Query_on_physical”. When the physical standby database is configured, this parameter is not set and the normal behaviour is expected. However when the parameter is applied to the standby database, we see the following changes;

 

SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
MOUNTED
 
SQL> alter system set "_query_on_physical"=true scope=spfile;
System altered.
 
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
 
SQL> startup

         Database mounted.

Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

At this stage we can access all objects from the standby database without any issues. This is Active Data Guard in action. Now if we disable this parameter, we see the following;

SQL> alter system set “_query_on_physical”=false scope=spfile;

System altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE E instance shut down

SQL> startup

ORACLE instance started.

Total System Global Area  797523968 bytes

Fixed Size                  2293440 bytes

Variable Size             314573120 bytes

Database Buffers          478150656 bytes

Redo Buffers                2506752 bytes

Database mounted.

ORA-16669: instance cannot be opened because the Active Data Guard option is disabled

Here the request to open the database is ignored and it’s simply mounted as normal. We see the following lines in the alert log file.

Sat Jul 26 09:37:32 2014
Data Guard: Active Data Guard is not enabled, database open not allowed
ORA-16669 signalled during: ALTER DATABASE OPEN...
Sat Jul 26 09:37:35 2014
ALTER SYSTEM SET log_archive_format='archive_%t_%s_%r.log' SCOPE=SPFILE SID='PROD';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
Sat Jul 26 09:37:35 2014
Attempt to start background Managed Standby Recovery process (PROD)

The standby database is mounted as a physical standby database and it starts applying the log files as it receives them from the primary. If you attempt to open the database, you also get the same error;

 SQL> ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE OPEN READ ONLY
*
ERROR at line 1:
ORA-16669: instance cannot be opened because the Active Data Guard option is disabled

Implementing this parameter will stop any attempts in enabling this active Data Guard feature. I have implemented and tested this parameter on a 12.1.0.1 and 11.2.0.1 standby database and observed the same behaviour.

 

This looks absolutely ideal and is exactly what we want. What I am unclear about is why it is a hidden parameter and as we all know you should never implement one of them without Oracle support signing the change control in blood.

This is a similar discussion to the one over enabling  12c in-memory database when not licensed to do so . Researching on Metalink sorry MoS all I can find is a note about Bug 8740993 which refers to a ORA-1410 on standby after dropping, truncating a table in primary. A comment right at the end states

 In version 11.2 set the static hidden parameter “_query_on_physical=FALSE” in the standby init.ora and open the standby read only. The previously failing standby queries should then return
the correct results. Note that setting “_query_on_physical=FALSE” disables the ADG option and startup is not allowed if MRP is running.
If startup is attempted while MRP is running, ORA-16669 is produced (instance cannot be opened because the Active Data Guard option is disabled).

After writing all of the above I did a bit more research and came across a blog by Uwe Hesse which mentions a MoS 1436313.1  but I cannot see that so perhaps it has been removed now. So once again I wonder about the reasons which we have to battle various combinations of chopt and suchlike to ensure that we do not appear to be using any form of licensed software when we know full well that we have not used it and have no intentions to

 

PS That reminds me that you can unset options with chopt using all the options on one line instead of having to run the command four times (in my example).

 

So we find out what options are enabled by looping through the oratab for unique Oracle Homes

#

# See what options are set at the binary level
#
# As oracle
user=`whoami`

[[ $user != 'oracle' ]] && exit

# loop around the oratab cd into any oracle homes a check the options using ar
#
my_host=`hostname`
for i in `grep -v ^\# /etc/oratab|grep -v ^$|grep -iv agent10g|grep -iv grid|grep -iv 'crs'|grep -iv agent12c|grep -iv 11gclient|grep -v AppsServer|awk -F':' '{ print $2 }' |sort -u`
do

echo " $my_host $i "

2>/dev/null    cd $i/rdbms/lib

#If the grep output is 0, the option is disabled.
#If the grep output is 1, the option is enabled

2>/dev/null    /usr/bin/ar  -t libknlopt.a | grep -c   dmwdm.o    >/dev/null  && echo " $my_host $i Data Mining    is on"
2>/dev/null    /usr/bin/ar  -t libknlopt.a | grep -c   kzvidv.o   >/dev/null  && echo " $my_host $i Database Vault is on"
2>/dev/null    /usr/bin/ar  -t libknlopt.a | grep -c   kzlilbac.o >/dev/null  && echo " $my_host $i Label security is on"
2>/dev/null    /usr/bin/ar  -t libknlopt.a | grep -c   kecwr.o    >/dev/null  && echo " $my_host $i RAT            is on"
done

Then run this command for each O_H

cd $ORACLE_HOME/rdbms/lib

time make -f ins_rdbms.mk dm_off rat_off lbac_off dv_off ioracle

 

The performance difference between HPUX and Linux is massive – from similar spec servers the first on HP then on Linux

Timings  per relink – HPUX:
cd $ORACLE_HOME/rdbms/lib
time make -f ins_rdbms.mk dm_off rat_off lbac_off dv_off ioracle
real    17:42.8
user     2:23.1
sys        49.2

Timings per relink – Linux:
cd $ORACLE_HOME/rdbms/lib
time make -f ins_rdbms.mk dm_off rat_off lbac_off dv_off ioracle
real    0m23.89s
user    0m6.87s
sys     0m2.05s

It can be seen that the savings on Linux are small – about one minute in total – but saving nearly 18 minutes per relink, on HP-UX, means up to 54 minutes saved, per Oracle Home.

Anyway that is enough from me, two completely different subjects linked into the same blog – only related by the inadequacies of the Oracle License Management system

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 217 other followers

%d bloggers like this: