Skip to main content
Delphix

Managing Oracle dSource Delphix Database User Password Changes

 

Applicable Delphix Versions

Major Release

All Sub Releases

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.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

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

Issue

A password change has been implemented in the Delphix dSource and a new password has been put in place 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 set 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 with the change only currently existing at a dSource level.

dSourcePassChange_SnapsFailure1.jpg

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.

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 X number of failed login attempts.

dSourcePassChange_PWChangeFails.jpg

Eliminating this error requires an unlock of the account on the Oracle side 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 is yet to be 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 now account unlocked quickly alter the password held by Delphix via the Delphix UI.

dSourcePassChange_MakePWChange.jpg

Make sure that the tick icon on the bottom right of the screen shot is also clicked to put the change in place in Delphix after the verify button has been used to test the connection using the new password.

A new snapshot is captured with the new password "delphix123" in place for the Delphix DB User "delphix"

dSourcePassChange_SnapSuccess.jpg

 

What are the implications of the password change for provisioning VDB's?

VDB's provisioned from snapshots where the new password is in place should experience no issues. VDB's 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.

Where a provision is performed using a Snapshot 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=vdbpass1
[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

o The old password is required in order for a log in to the VDB created from the old Snapshot.

[oracle@oelvbc1n1 ~]$ export ORACLE_SID=vdbpass1
[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 a VDB  provisioned from an older snapshot where the old password is in place needs some additional configuration via a configure clone hook that is used 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".

dSourcePassChange_ProvVDB1.jpg

dSourcePassChange_ProvVDB2.jpg

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
dSourcePassChange_ProvVDB3.jpg

The provision is kicked off and succeeds.

dSourcePassChange_ProvVDB4.jpg

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

[oracle@oelvbc1n1 ~]$ export ORACLE_SID=vdbpass2
[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 warning around the new VDB and its login details.


dSourcePassChange_ProvVDBFault.jpg

This needs to be corrected by setting the new password within the Delphix UI for the VDB.

dSourcePassChange_VDBSuccess.jpg

The fault then needs to be marked as resolved.

dSourcePassChange_ProvVDBFaultResolved.jpg

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 VDB's from snapshots captured via SYS where an oracle password file creation would be required.

Linking Delphix dSources

https://docs.delphix.com/docs/delphix-administration/oracle-environments-and-data-sources/managing-oracle-oracle-rac-and-oracle-pdb-data-sources/linking-an-oracle-data-source

Delphix Source and Target requirements for Oracle Databases

https://docs.delphix.com/docs/delphix-administration/oracle-environments-and-data-sources/oracle-support-and-requirements/requirements-for-oracle-source-hosts-and-databases