Tuesday, October 30, 2007

10g Certification IZ0-040

I obtained my 10g Certification last week. Pending since long, it completes a major requirement for that real 10g DBA. Since i am already 9i Certified Professional, upgrading to 10g wasn't such a big deal.

I found passing the 4 exams of the initial Oracle 9i OCP quite a big thing, especially dealing with the amount of books and experience needed to pass the exam. Its like passing one of your engineering semester exams as part of your Bachelor's degree. However 10g certification is going to be easy for any existing 9i certified professional. I am posting my experience with the exam here.

  1. 60 questions. Atleast 44 answers have to be correct.
  2. Questions will be independent of 10g R.1 and 10g R.2 releases. You can still pass the exam if you are just experienced in 10g R.1 and not 10g R.2 release.
  3. Only new features will be tested here (including some 9i advanced new features). If one wants to pass this exam easily, one needs to study thoroughly the 10 new features guide found here.

Thursday, October 11, 2007

Oracle listener password

Ever been in a situation where your database listener has been shutdown by an end user ? Yes , thats right, the default database listener configuration in 9i comes *WITHOUT* a password and any client or end user who obtains the listener name and a TNS entry(listener port and host name) , can shutdown the database server's listener sitting from that client machine !!! . All the places that i worked so far had their database server listeners' unsecured when i first checked them.

Quarterly database and apps CPU patches are recommended to fix security gaps and loopholes, but database listener security is often overlooked and easiest thing to break.

Problem



Database listeners are not always password protected. They are vulnerable to security attacks from any host in the network with a TNS information of the target database and an oracle client software. The TNS connection information is already widely distributed to users for e.g to run queries using TOAD or SQL*Navigator etc., so any user can easily *stop* database listeners inadvertently thereby comprising new database connections.

Below is a simulation of how these listeners can be stopped from a client machine

1) Obtain target database TNS connection descriptor
2) Put the connection descriptor in a client machine’s tnsnames.ora
3) Login to the client machine and issue ‘lsnrctl stop db listener name’


This stop command from the client machine will stop the database listeners on the server

Solution



Password protection is the solution for this problem. Passwords can be either encrypted or unencrypted. For maximum security the database listeners should be password protected with encryption.

Implementation

Setting Encrypted Password

1) Login to each of the database instance server host as oracle owner
2) lsnrctl
3) LSNRCTL> set current_listener listener_name3) LSNRCTL> change_password
Old password : Hit Enter KeyNew password : Type the new passwordReenter new password: Retype the new password4) LSNRCTL>set password
Password: Enter the newly created password5) LSNRCTL> save_config listener name
This will create a new passwords parameter in the $TNS_ADMIN/listener.ora and it is encrypted in the file. The old file will be backed up with a listener.bak extention.

Resetting the password if its forgotten

1) Login to the database instance server as oracle owner
2) kill -9 listener pid3) Open the listener.ora and delete the passwords_listener_name line
4) lsnrctl
5) LSNRCTL> set current_listener listener name
6) LSNRCTL> start
7) LSNRCTL> change_password
Old password: Hit Enter key
New password: Type a new passwordReenter new password: Retype the new password
8) LSNRCTL> set password
Password: Enter the newly typed password again
9) LSNRCTL> save_config listener name

Changing an existing password

1) Login to the database instance server as oracle owner
2) kill -9 listener pid
3) lsnrctl
4) LSNRCTL> set current_listener listener name
5) LSNRCTL> start
6) LSNRCTL> change_password
Old password: Enter old passwordNew password: Type a new password
Reenter new password: Retype the new password
7) LSNRCTL> set password
Password: Enter the new password again
8) LSNRCTL> save_config listener name

Listener administration with a password

start

Starting the listener does not require a password. A listener can be started in the conventional method without setting any password

stop

Stopping the listener always requires a password.

1) lsnrctl
2) LSNRCTL>set password
Password: Enter the password3) stop listener name

status

A password is also required for finding the status of a listener

1) lsnrctl
2) LSNRCTL>set password
Password: Enter the password
3) status listener name
Testing

1) Obtain the database TNS descriptor information
2) Put the connection descriptor in a client machine’s tnsnames.ora
3) Login to the client machine and issue ‘lsnrctl stop
The stop command will fail with the following message.

TNS-01169: The listener has not recognized the password

Uninstall (Status Quo)

1) Login to the database instance server as oracle owner
2) kill -9 listener pid
3) Open the listener.ora and delete the passwords_listener_name line
4) lsnrctl
5) LSNRCTL> set current_listener listener name
6) LSNRCTL> start

Caveats

1) A client can still stop/status a listener after setting the password explicitly. Hence its always recommended to choose a strong password with encryption.

Tuesday, October 2, 2007

How to Change APPS Password without running ADCONFIG

Any other ORACLE database user password can be changed simply by the 'alter user username identified by xxxxx' command. However the APPS user password change procedure in an Oracle Applications 11i instance has got a few more additional steps. In this post i will outline all the steps required without running the adconfig utility as well as the verification process after changing the password.
 
APPS password change procedure WITHOUT running adconfig
  1. Login to any of the admin tiers of the 11i instance
  2. FNDCPASS apps/old_pwd 0 Y system/sys_pwd SYSTEM APPLSYS new_pwd
  3. Edit $ORACLE_HOME/reports60/server/CGIcmd.dat and update with the new password
  4. Login to all of the middle tiers of the 11i instance (or just one of them if its a shared appl tier configuration)
  5. Edit the following files and update them with the new password
  6. $FND_TOP/bin/appsweb.cfg
  7. $FND_TOP/resource/wfmail.cfg
  8. $ORACLE_HOME/reports60/server/CGIcmd.dat
  9. $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app
  10. Recreate any database links that are owned by the apps user
  11. Update any 3rd party tools, interfaces or scripts that have the password hardcoded

APPS password change procedure WITH adconfig

  1. Login to any of the admin tiers of the 11i instance
  2. FNDCPASS apps/old_pwd 0 Y system/sys_pwd SYSTEM APPLSYS new_pwd
  3. Backup any customizations in the configuration files
  4. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME and run adautocfg.sh
  5. Restore the customizations to the configuration files
  6. Login to all of the middle tiers of the 11i instance (or just one of them if its a shared appl tier configuration)
  7. Backup any customizations in the configuration files
  8. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME and run adautocfg.sh
  9. Restore the customizations to the configuration files
  10. Recreate any database links that are owned by the apps user
  11. Update any 3rd party tools, interfaces or scripts that have the password hardcoded

Verification

  1. Login to the database as apps user
  2. Complete the AOL/J diagnostic test. This webpage asks for the new apps password and tries to complete a jdbc connection. http://11i_web_URL:port/OA_HTML/jsp/fnd/aoljtest.jsp

Conclusion

There may be lot of customizations in the configuration files that are generated by the adautocfg.sh. Saving and restoring them each and every time adautocfg.sh is run is the key to a successful apps password change procedure when adconfig is run (adautocfg.sh). If one is not sure about all the customizations in the configuration files, one can choose the first procedure above WITHOUT running adconfig.

Related Posts Plugin for WordPress, Blogger...