2009/01/21

Some Notes about Password File and OS Authentication

1. In order to let remote user login database as sysdba, password file needs to be
created and REMOTE_LOGIN_PASSWORDFILE must be set to a value that is not NONE.

2. In 9i, optional values for REMOTE_LOGIN_PASSWORDFILE are NONE, SHARED and EXCLUSIVE. In 10g, EXCLUSIVE still works for backwards compatibility, but now it behaves just like SHARED. This is the quote from the documentation:

9i
# NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.
# SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.
# EXCLUSIVE
The password file can be used by only one database and the password file can contain names other than SYS.

10g
# NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.
# SHARED
One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.

3. "GRANT sysdba TO user" will add an entry (username and password) into the password file, so that remote user can log into database as sysdba. Remote users in the password file can be found in V$pwfile_users. Oracle checks the password
in password file to verify remote user's identity. Everytime user's password is set,
new password will be added into password file and substitute the old password.

4. ANYONE in the dba group (whatever group you used upon install) is permited to
connect locally "as sysdba".

5. OS authentication allows Oracle to pass control of user authentication to the operating system. Non-priviliged OS authentication connections take the following form.

sqlplus /
sqlplus /@service

Use the following command to enable OS authentication for a user.
CREATE USER ops$username IDENTIFIED EXTERNALLY;
*ops$ can be check by "SHOW PARAMETER os_authent_prefix"

Refer to "Problem to connect as SYSDBA" on asktom.oracle.com for detail explanation.

2009/01/20

Changing Hostname, Domain Name and ias_admin password for Oracle Application Server

Application Server: 10.1.2.0.2
OS: Windows Server 2003 R2

Recently, I took over an Oracle application server machine which was cloned from a VM
machine. To set it up, I need to change the hostname, domain name and ias_admin
password since I have no credentials for administration.

Refer to "Oracle Application Server Admininstrator's Guide 10g Release 2"->Chapter 8
Changing Network Configurations->Changing the Hostname, Domain Name, or IP Address
for how to change hostname and domain name.

After that, application server was running and I can access to application server
control page. Since I don't have password for ias_admin, I can't log into this page.
I need to update the password using command line.

Refer to the following steps for updaing ias_admin password using command line:
1. Log in as the user who installed the Oracle Application Server instance
2. Stop the Application Server Control.
On UNIX systems, enter the following command:
ORACLE_HOME/bin/emctl stop iasconsole
On Windows systems, use the Services control panel to stop the Application Server Control
service.
3. Locate and open the following file in a text editor:
ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
4. Locate the line that defines the credentials property for the ias_admin user.
The section is located in tag name->users->user->credentials
5. Replace the existing encrypted password with the new password.
Be sure to prefix the password with an exclamation point (!). For example:
!mynewpassword123
See Also:
"The ias_admin User and Restrictions on its Password" in the Oracle Application
Server 10g Installation Guide
6. Start the Application Server Control.
After the restart, the Application Server Control will use your new Administrator
(ias_admin) password, which will be stored in encrypted format within the
jazn-data.xml file.

2009/01/05

Keep an Eye on CONTROL_FILE_RECORD_KEEP_TIME

Oracle Database: 10.2.0.2

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable
record in the control file can be used. In the event a new record needs to be added to a reusable section and the oldest record not aged enough, the record sectione expands.

You don't have to worry about this parameter if you use recovery catalog. Otherwise,
it is suggested that the DBA set this parameter to a value no less than the database
backup retention period.

This is the problem I met due to inappropriate settings.
The parameter is set to default (7 days), and the retention for backup is set to 8
days. During backup procedure, old archivelog records can not be found in controlfile
because the space is reused, but the archivelog files are still on disk. Everytime
the job is supposed to delete the files older than 8 days, it didn't because the
controlfile have no records for those fils. So, after a couple of days, the space for
archivelog is filled up.