Managing Oracle dSource Delphix Database User Password Changes (KBA1562)

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

How to Manage Oracle dSource Delphix Database User Password Changes

If you are using a Delphix version prior to 6.0.7.0. and a password change has been implemented in the Delphix dSource and a new password has been put in place for the Delphix DB User continue reading below.

Issue

A password change has been implemented in the Delphix dSource and a new password has been created for the Delphix DB User.
A new virtual database (VDB) is being provisioned from a Snapshot where the old password is in place.
The new password must be implemented in the VDB as it is going to be used going forward.

While this article focuses on the the Delphix DB User and its implications in the Delphix space, this same technique for altering passwords can also be used for other Oracle Database Users.

Background

The current password for the dSource db112stb DB user delphix is set to delphix. Existing Snapshots were captured when this password was in place.

The dSource password for the dSource DB user delphix is altered to delphix123.

[oracle@oelvbc1n2 ~]$ echo $ORACLE_SID
db112stb
[oracle@oelvbc1n2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 10 12:03:40 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter user delphix identified by delphix123; 

User altered.

What are the implications of this password change on the dSource?

Capturing Snapshots may fail

Attempts to capture another Snapshot where the new password is in place will fail as the dSource has a new password for the Delphix DB User, and this has not yet been aligned on the Delphix side. The change currently exists only at the dSource level.

Overcoming this requires the Delphix DB User delphix password be changed in the Delphix UI to align the password held for the dSource inside Delphix with the password now held by the Oracle dSource database itself.

Screen Shot 2022-12-26 at 12.50.48 PM.png

Adjust the Delphix DB User password in the Delphix UI to align the password held by Delphix with that now set in the Oracle dSource.

This may fail depending on how long the new password is in place and whether or not Oracle has a policy/profile associated with database user password management in place that locks accounts after some number of failed login attempts.

Eliminating this error requires unlocking the account on the Oracle side followed by a quick password change in Delphix. The reason behind the locking of the account is due to Delphix Environment Monitoring and/or LogSync processes attempting logins using the password set prior to the change that has not yet been adjusted on the Delphix side.

[oracle@oelvbc1n2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 10 12:03:40 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter user delphix identified by delphix123 account unlock;

User altered.

With the password change in place and the account now unlocked, quickly alter the password held by Delphix via the Delphix UI.

A new snapshot can now be captured with the new password delphix123 in place for the Delphix DB User delphix.

What are the implications of the password change for provisioning VDBs?

VDBs provisioned from snapshots where the new password is in place should experience no issues. VDBs provisioned from snapshots where the old password was in place when the Snapshots were captured require additional changes be made to them in order for the new password to be used.

When a provision is performed using a Snapshot where the original password delphix is in place for the Delphix DB User delphix, attempts to log in using the new password delphix123 will fail.

Attempts to use the new password fail as the dSource database upon which the VDB was based had the password delphix in place in its dictionary at the time the snapshot used to create the VDB was taken of the dSource.

[oracle@oelvbc1n1 ~]$ export ORACLE_SID=vdb1
[oracle@oelvbc1n1 ~]$ sqlplus delphix/delphix123

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 10 12:30:13 2017

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

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

The old password is required in order to log into the VDB created from the old Snapshot.

[oracle@oelvbc1n1 ~]$ export ORACLE_SID=vdb1
[oracle@oelvbc1n1 ~]$ sqlplus delphix/delphix

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 10 12:30:06 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Provisioning the VDB from an older snapshot and implementing the new password.

Should the new password be required for accessing a VDB provisioned from an older snapshot where the old password is in place, some additional configuration is needed. You can use a Configure Clone hook to alter the Delphix DB User password to its new value.

Provisioning the VDB with the configure clone hook script in place.

A provision is performed using the snapshot that has the old password delphix in place for the Delphix DB User delphix.

A Configure Clone hook is added to the VDB provision process that logs into the VDB and alters the password to match the new password delphix123 set in the dSource for the Delphix DB user delphix.

The scripts contents are the following (in its simplest form):

#
# Delphix DB User Password change hook 
#
sqlplus -s '/ as sysdba' << eof
alter user delphix identified by delphix123 account unlock
/
eof

The provision is kicked off and succeeds.

Logins to the new VDB using sqlplus can be performed using the new password.

[oracle@oelvbc1n1 ~]$ export ORACLE_SID=vdb1
[oracle@oelvbc1n1 ~]$ sqlplus delphix/delphix123

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 10 12:49:34 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

The old password fails.

[oracle@oelvbc1n1 ~]$ sqlplus delphix/delphix

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 10 12:50:29 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Delphix may throw a fault and/or warning regarding the new VDB and its login details.

This is corrected by setting the new password within the Delphix UI for the VDB.

Screen Shot 2022-12-26 at 1.16.54 PM.png

The fault then needs to be marked as resolved.

Additional Information

This process only examines the management of passwords for the Delphix Database User created and managed using the processes discussed in the following Delphix Documentation and through establishing the user via the createDelphixDBUser.sh script. It does not examine the use of the SYS user for linking where the dSource was a mounted Standby Database and provisioning VDBs from snapshots captured via SYS where an oracle password file would be required.

KBA