Tuesday, September 14, 2010

We are greatful, Mr. Export/Import!

We had a production issue yesterday that caused an outage. Export backups came to the rescue and saved the day!

Per organizational security policy, Oracle database passwords of all database accounts, including those used in database links must be changed at regular interval. Database links have been established over secure network between our database and our partner’s database. DB Links are in place for over 10 years, and serving the business purpose of one-directional data/information dissemination. We have DB accounts in our database that our partners use in database links to access our database.

Per organizational security policy, yesterday was the password-change day. DBAs changed the passwords of DB link accounts. When DBAs tried to contact our partner so that they could re-create the database links with the new passwords, there was no one on the other side to re-create the DB link. DBAs tried home and cell, but no avail. A five-minute of planned maintenance window turned into a thirty-minute of production outage, and still no response from the other side. That’s when a decision was made to revert the passwords back to original passwords. But DBAs realized that they didn’t have, and they don’t maintain DB Link accounts passwords. Production outage had reached a 45-minute mark. DBAs tried jogging their memory, referred to old notes, etc., but couldn’t trace the original passwords.
This is when an idea came up! Export backup! This was an Oracle10g database. Nightly export backups were performed. Export dump file stores “CREATE USER username IDENTIFIED BY VALUES ’hash string'” statements. Hash strings represent hashed Oracle passwords. DBAs quickly located last night’s export dump file, grabbed the applicable CREATE USER statements, and changed passwords to their original values with “ALTER USER username IDENTIFIED BY VALUES 'hash string'”. DBAs had to work around password reuse settings in database profiles to restore the passwords, but that wasn’t a problem. DB Links were back in operation! Export saved the day! We are greatful, Mr. Export/Import!

P.S. As of Oracle 11g R2, Export/Import has been desupported in favor of more versatile Data Pump. Export and Import executables are provided with Oracle11g so data from older versions of Oracle can be imported or exported to. Oracle Data Pump import do provide option to retrieve hashed passwords.

No comments:

Post a Comment