REDO log sizing

Online redo log sizing is very important. If it is too small, the LGWR, ARCH and DBWR background processes will be busy by the frequent log switches and increase CPU usage. It it is too big, the database risks losing data during an instance crash.

In Oracle 10G, Oracle advisory utility makes recommendation on optimal redo log size. To use this feature, an initialization parameter FAST_START_MTTR_TARGET parameter must be set to a nonzero value, which specifies a target (bounded) time (in seconds) to complete the cache recovery phase of recovering. Using this parameter, Oracle database can now self-tune checkpointing to achieve good recovery times with low impact on normal throughput. DBAs no longer have to set any checkpoint-related parameters and should disable (set to 0) the following parameters:

- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINT_INTERVAL
- FAST_START_IO_TARGET

View v$instance_recovery holds the MTTR (mean time to recovery ) information with following columns:

optimal_logfile_size -- the suggested size in megabytes
target_mttr -- MTTR target in effect in seconds
estimated_mttr -- the estimated MTTR if a crash happens right now

The data reported in this view may change frequently, based on the DML load on the database. DBAs can use the suggested size when the database is relatively stable to build the redo log properly.

Grid Control also gives the suggested size. The navigation is
Adminstration --> Storage --> Redo Log Group --> Actions dropdown: Sizing advice --> Go.

Oracle Database Backup and Recovery Advanced User's Guide (10gR2, B14191-01) has details on Oracle recovery process and on FAST_START_MTTR_TARGET sizing.
Note 276103.1 has more on Advisors.

EBS Configurator version

The Configurator version in 11i can be obtained using the following URL:
http://hostname:portnum/configurator/oracle.apps.cz.servlet.UiServlet?test=version

the values "hostname:portnum" can be found in "BOM:Configurator URL of UI Manager" profile option.

Reference: Note 419300.1

For EBS R12, the URL is

http://hostname:portnum/OA_HTML/configurator/UiServlet?test=version

List of Scheduled Concurrent Requests

One of concurrent programs stopped to run. I am not sure if it is not scheduled to run any more or it is just cancelled temporarily.

Below code (Note 170107.1) helps me to get the answer. There is another Note (602162.1) on the same subject.

select b.concurrent_program_name, a.*
from apps.fnd_concurrent_requests a , apps.fnd_concurrent_programs b
where a.concurrent_program_id = b.concurrent_program_id
and status_code in ('Q','I')
and requested_start_date > SYSDATE
and hold_flag = 'N'
order by b.concurrent_program_name;
-- 'I' indicates a status of Normal

Change EBS 11i password

In Oracle E-business Suite, the commands for changing APPS passwors and for changing SYSADMIN password are different. It is very important to use the right syntax when making the change.

Make sure you can log onto the database as APPS and SYSTEM by using Sql*plus on the apps server BEFORE you run below lines.

- To change APPS password
$FND_TOP/bin/FNDCPASS apps/old_apps_pwd 0 Y system/system_pwd SYSTEM APPLSYS new_apps_pwd

- To change SYSADMIN password
$FND_TOP/bin/FNDCPASS apps/current_apps_pwd 0 Y system/system_pwd USER SYSADMIN new_sysadmin_pwd

- to change schema owner password
FNDCPASS apps/current_apps_pwd 0 Y system/system_pwd ORACLE OE new_oe_pwd

UPDATE on R12:

Below statement by APPS will change SYSADMIN password:
SQL> select fnd_web_sec.change_password('sysadmin','newPwd4U') from dual;

Below line to check if a user's password is right or not:
SQL> select fnd_web_sec.validate_login('ebs_userID', 'userPWD') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('EBS_USERID','USERPWD')
--------------------------------------------------------------------------------
Y

Enable remote SYSDBA login

UNIX OS user who is in the same Group as the user who installed Oracle on the server can login as SYS from the server locally. The key to log onto Oracle as SYS from a remote client is to set init.ora parameter REMOTE_LOGIN_PASSWORDFILE not equal to NONE (the default?).

The steps to make it work:

Step 1: Set the init parameter, and then bounce the database:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

EXCLUSIVE forces the password file to be tied exclusively to a single instance. In 10gR2 release, the value EXCLUSIVE is supported for backward compatibility and now has the same behavior as the value SHARED (Reference B14237-04).

Step 2: Create a passwor file:
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxxx

ORAPWD has an option "entries=", which is not mandatory. I do not know what is the default number. In most cases, just do not use this option.

Step 3: Verify below view returns at least one row (username SYS):
SQL> select * from v$pwfile_users;

If yes, then everything works and the new passwordfile is in use by the username on the list. Now, SYSDBA can logon to the database from a remote client.

Additional notes:
1. If "grant sysdba to user_A;" is run by SYS, the user_A will show up in view v$pwfile_users. And user_A can login as SYSDBA useing its own password (not the one in passwordfile).
2. Without Step 1, Step 2 really does not do anything.
3. The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and should not be used, it is for 'normal' users that use OS authentication (Note 50507.1).
4. If REMOTE_OS_AUTHENT is set to FALSE, OS_AUTHENT_PREFIX does not take any effect.