Posts Tagged Data Guard

Account locking in an Active Data Guard environment

During the Data Guard round table of the excellent UKOUG Tech18 conference I got aware of this topic that I’d like to share with the Oracle community:

What is the locking behavior for user accounts in an environment where users may connect to the primary as well as to the standby database?

User gets locked on the primary

SQL> alter profile default limit failed_login_attempts 2;

Profile altered.

SQL> create user adam identified by adam;

User created.
SQL> grant create session to adam;

Grant succeeded.

SQL> connect adam/wrong@prima
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@prima
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect adam/wrong@prima
ERROR:
ORA-28000: The account is locked.

I changed the default profile so the account lock happens faster. The change of the default profile reaches the standby via redo apply. The same goes for account locks that happened on the primary like above.

Standby inherits the locked accounts from primary

SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.

This inherited lock cannot be unlocked on the standby:

SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> alter user adam account unlock;
alter user adam account unlock
*
ERROR at line 1:
ORA-28015: account unlock attempted on read-only database but a conflicting
account lockout on the primary exists

The account can only be unlocked on the primary and that implictly unlocks it on the standby too:

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> alter user adam account unlock;

User altered.
SQL> connect adam/adam@prima
Connected.
SQL> connect adam/adam@physt
Connected.

Account gets locked on the standby only

SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.

This lock at the standby is kept there in memory only and doesn’t impact the primary:

SQL> connect adam/adam@prima
Connected.

It can be unlocked on the standby:

SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> alter user adam account unlock;

User altered.

SQL> connect adam/adam@physt
Connected.

Standby locks are kept in memory

After a restart, the lock is gone:

SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.


SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  629143384 bytes
Fixed Size		    8660824 bytes
Variable Size		  180355072 bytes
Database Buffers	  436207616 bytes
Redo Buffers		    3919872 bytes
Database mounted.
Database opened.
SQL> connect adam/adam@physt
Connected.

Role change involving a restart of the standby

That will also lose the previous locks that have been done at the standby only:

SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect adam/wrong@physt
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect adam/wrong@physt
ERROR:
ORA-28000: The account is locked.


SQL> exit
[oracle@uhesse ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Fri Dec 7 08:28:59 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "prima"
Connected as SYSDBA.
DGMGRL> switchover to physt;
Performing switchover NOW, please wait...
Operation requires a connection to database "physt"
Connecting ...
Connected to "physt"
Connected as SYSDBA.
New primary database "physt" is opening...
Operation requires start up of instance "prima" on database "prima"
Starting instance "prima"...
Connected to an idle instance.
ORACLE instance started.
Database mounted.
Database opened.
Connected to "prima"
Switchover succeeded, new primary is "physt"
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxPerformance
  Members:
  physt - Primary database
    prima - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

DGMGRL> exit
[oracle@uhesse ~]$ sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Dec 7 08:31:16 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

SQL> connect adam/adam@prima
Connected.
SQL> connect adam/adam@physt
Connected.

I did the tests on 18c but I don’t think this is a new feature. I just didn’t have that topic on the radar before. Which is one reason why we go to conferences, right? 🙂

,

1 Comment

orapwd gives OPW-00029 Password complexity failed in #Oracle 12.2

When I tried to create a password file for a 12.2 database, it initially failed with my favorite (simple) password:

[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=oracle

OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.

Two options to resolve this: Either provide a password that passes the complexity check, like:

[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=Very_Complex§1

Or create the password file in 12.1 format (default being 12.2 format)

[oracle@uhesse dbs]$ orapwd describe file=orapwprima
Password file Description : format=12.2 
[oracle@uhesse dbs]$ rm orapwprima
[oracle@uhesse dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprima password=oracle format=12
[oracle@uhesse dbs]$ orapwd describe file=orapwprima
Password file Description : format=12 

The only drawback of the 12.1 format seems to be the lack of support for granting administrative privileges to external users and enabling SSL and Kerberos authentication for administrative users, according to the documentation. Which means for me I will keep my passwords simple 🙂

, ,

2 Comments

Fast-Start Failover for Maximum Protection in #Oracle 12c

Fast-Start Failover is supported with Maximum Protection in 12cR2. Also Multiple Observers can now monitor the same Data Guard Configuration simultaneously. I will show both in this article. Starting with a (Multitenant) Primary in Maximum Protection mode with two Standby Databases. It is still not recommended to have the highest protection mode configured with only one standby. So this is my starting point:

DGMGRL> show configuration;

Configuration - myconf

Protection Mode: MaxProtection
Members:
cdb1 - Primary database
cdb1sb - Physical standby database
cdb1sb2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 57 seconds ago)

All three databases have flashback turned on. I want to have a setup like this in the end:

FSFO with Max Protection and 2 Observers

FSFO with Max Protection and 2 Observers

This is how it’s been configured:

DGMGRL> edit database cdb1 set property faststartfailovertarget='cdb1sb,cdb1sb2';
Property "faststartfailovertarget" updated
DGMGRL> edit database cdb1sb set property faststartfailovertarget='cdb1,cdb1sb2';
Property "faststartfailovertarget" updated
DGMGRL> edit database cdb1sb2 set property faststartfailovertarget='cdb1,cdb1sb';
Property "faststartfailovertarget" updated
DGMGRL> enable fast_start failover;
Enabled.

On host uhesse4:

[oracle@uhesse4 ~]$ dgmgrl sys/oracle@cdb1
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jan 13 17:20:52 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDBA.
DGMGRL> start observer number_one;
[W000 01/13 17:21:04.85] FSFO target standby is cdb1sb
[W000 01/13 17:21:07.05] Observer trace level is set to USER
[W000 01/13 17:21:07.05] Try to connect to the primary.
[W000 01/13 17:21:07.05] Try to connect to the primary cdb1.
[W000 01/13 17:21:07.05] The standby cdb1sb is ready to be a FSFO target
[W000 01/13 17:21:09.06] Connection to the primary restored!
[W000 01/13 17:21:13.07] Disconnecting from database cdb1.

On host uhesse3:

[oracle@uhesse3 ~]$ dgmgrl sys/oracle@cdb1
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jan 13 17:22:16 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDBA.
DGMGRL> start observer number_two;
[W000 01/13 17:22:32.68] FSFO target standby is cdb1sb
[W000 01/13 17:22:34.85] Observer trace level is set to USER
[W000 01/13 17:22:34.85] Try to connect to the primary.
[W000 01/13 17:22:34.85] Try to connect to the primary cdb1.
[W000 01/13 17:22:34.85] The standby cdb1sb is ready to be a FSFO target
[W000 01/13 17:22:36.86] Connection to the primary restored!
[W000 01/13 17:22:40.86] Disconnecting from database cdb1.

This is now the state of the configuration:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1    - Primary database
    cdb1sb  - (*) Physical standby database 
    cdb1sb2 - Physical standby database 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 33 seconds ago)

DGMGRL> show fast_start failover;

Fast-Start Failover: ENABLED

  Threshold:          15 seconds
  Target:             cdb1sb
  Candidate Targets:  cdb1sb,cdb1sb2
  Observers:      (*) number_two
                      number_one
  Lag Limit:          30 seconds (not in use)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

That protects against the failure of any two components in the configuration with automatic failover and zero data loss! For example the first standby may fail and then the primary. We failover to the second standby that becomes the new fast-start failover target:

[oracle@uhesse2 ~]$ ps -ef | grep smon
oracle   15087     1  0 17:40 ?        00:00:00 ora_smon_cdb1sb
oracle   15338  9765  0 17:49 pts/2    00:00:00 grep --color=auto smon
[oracle@uhesse2 ~]$ kill -9 15087

Above crashed the first standby. This is what the Observers report:

[W000 01/13 17:49:34.24] Failed to ping the standby.
[W000 01/13 17:49:37.25] Failed to ping the standby.
[W000 01/13 17:49:40.25] Failed to ping the standby.
[W000 01/13 17:49:43.25] Failed to ping the standby.
[W000 01/13 17:49:46.26] Failed to ping the standby.
[W000 01/13 17:49:46.26] Standby database has changed to cdb1sb2.
[W000 01/13 17:49:47.26] Try to connect to the primary.
[W000 01/13 17:49:47.26] Try to connect to the primary cdb1.
[W000 01/13 17:49:48.34] The standby cdb1sb2 is ready to be a FSFO target
[W000 01/13 17:49:53.35] Connection to the primary restored!
[W000 01/13 17:49:57.35] Disconnecting from database cdb1.

This is the state of the configuration now:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1    - Primary database
    Error: ORA-16778: redo transport error for one or more members

    cdb1sb2 - (*) Physical standby database 
    cdb1sb  - Physical standby database 
      Error: ORA-1034: ORACLE not available

Fast-Start Failover: ENABLED

Configuration Status:
ERROR   (status updated 14 seconds ago)

Notice that the Fast-Start Failover indicator (*) now points to cdb1sb2. Now the primary fails:

[oracle@uhesse1 ~]$ ps -ef | grep smon
oracle   21334     1  0 17:41 ?        00:00:00 ora_smon_cdb1
oracle   22077  5043  0 17:52 pts/0    00:00:00 grep --color=auto smon
[oracle@uhesse1 ~]$ kill -9 21334

This is what the Observers report:

[W000 01/13 17:52:54.04] Primary database cannot be reached.
[W000 01/13 17:52:54.04] Fast-Start Failover threshold has not exceeded. Retry for the next 15 seconds
[W000 01/13 17:52:55.05] Try to connect to the primary.
[W000 01/13 17:52:57.13] Primary database cannot be reached.
[W000 01/13 17:52:58.13] Try to connect to the primary.
[W000 01/13 17:53:06.38] Primary database cannot be reached.
[W000 01/13 17:53:06.38] Fast-Start Failover threshold has not exceeded. Retry for the next 3 seconds
[W000 01/13 17:53:07.39] Try to connect to the primary.
[W000 01/13 17:53:09.46] Primary database cannot be reached.
[W000 01/13 17:53:09.46] Fast-Start Failover threshold has expired.
[W000 01/13 17:53:09.46] Try to connect to the standby.
[W000 01/13 17:53:09.46] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 01/13 17:53:09.46] Check if the standby is ready for failover.
[S019 01/13 17:53:09.47] Fast-Start Failover started...

17:53:09.47  Friday, January 13, 2017
Initiating Fast-Start Failover to database "cdb1sb2"...
[S019 01/13 17:53:09.47] Initiating Fast-start Failover.
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdb1sb2"
17:53:23.68  Friday, January 13, 2017

After having restarted the two crashed databases, they become automatically reinstated and the configuration then looks like this:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1sb2 - Primary database
    cdb1    - (*) Physical standby database 
    cdb1sb  - Physical standby database 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

DGMGRL> show fast_start failover;

Fast-Start Failover: ENABLED

  Threshold:          15 seconds
  Target:             cdb1
  Candidate Targets:  cdb1,cdb1sb
  Observers:      (*) number_two
                      number_one
  Lag Limit:          30 seconds (not in use)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

Switching back to make cdb1 primary – this is of course optional:

DGMGRL> switchover to cdb1;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb1"
Connecting ...
Connected to "cdb1"
Connected as SYSDBA.
New primary database "cdb1" is opening...
Operation requires start up of instance "cdb1sb2" on database "cdb1sb2"
Starting instance "cdb1sb2"...
ORACLE instance started.
Database mounted.
Connected to "cdb1sb2"
Switchover succeeded, new primary is "cdb1"
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxProtection
  Members:
  cdb1    - Primary database
    cdb1sb  - (*) Physical standby database 
    cdb1sb2 - Physical standby database 

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

I think this enhancement is really a big deal!

,

6 Comments

%d bloggers like this: