Datafile Needs Recovery

When there is a disk read/write problem, such as adapter issues connecting SAN luns to server or other server IO problems, Oracle data files may fail to get checkpoint. In this case, Oracle may place the datafile offline and write errors to the alert log:

Errors in file /u01/app/oracle/admin/dw/bdump/dw_ckpt_1081360.trc:
ORA-01171: datafile 7 going offline due to error advancing checkpoint
ORA-01110: data file 7 : '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
ORA-01115: IO error reading block from file 7 (block # 1)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: -1
Additional information: 16384

Database view DBA_DATA_FILES will confirm that the datafile needs recovery:

SQL> select * from dba_data_files
where online_status != 'ONLINE';

FILE_NAME ONLINE_STATUS
/u06/app/oracle/oradata/dw/dw_idx01.dbf RECOVER
/u02/app/oracle/oradata/dw/system01.dbf SYSTEM

The fix to the problem is to run two "alter database ..." commands to bring the data file back online, after the disk IO issue has been firmly resolved. Here is the alert log when the two commands ran:

Fri May 7 23:25:30 2010
alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
Fri May 7 23:25:30 2010
Media Recovery Start
parallel recovery started with 7 processes
Fri May 7 23:25:31 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 70541 Reading mem 0
Mem# 0: /u03/app/oracle/oradata/dw/redo02a.log
Mem# 1: /u03/app/oracle/oradata/dw/redo02b.log
Fri May 7 23:25:34 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 70542 Reading mem 0
Mem# 0: /u04/app/oracle/oradata/dw/redo03a.log
Mem# 1: /u04/app/oracle/oradata/dw/redo03b.log
Fri May 7 23:25:36 2010
Media Recovery Complete (dw)
Completed: alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'

Fri May 7 23:26:07 2010
alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online
Fri May 7 23:26:07 2010
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/app/oracle/product/10.2.0/db_1/dbs/c-1219972149-20100507-02'
Completed: alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online

Install Oracle Fusion Middleware 11gR1

Oracle Fusion Middleware expands Oracle Applications Server 10g to include more components that you may never need.

I studies the way of installing Oracle Dsicoverer 11.1.1 for Oracle E-business Realease 12 (1074326.1). Here are some key steps to make the installation work.

1. Indentify the installation files from download.

As of today 5/14/10, the location is
http://www.oracle.com/technology/software/products/middleware/htdocs/fmw_11_download.html

This page also has a link to downlaod WebLogic
http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html

File names in three areas:

- Repository Creation Utility (RCU, 11.1.1.2.1)
ofm_rcu_linux_11.1.1.2.1_disk1_1of1.zip

- Weblogic server (10.3.2)
wls1032_generic.jar

- Portal, Forms, Reports and Discoverer (11.1.1.2.0)
ofm_pfrd_linux_11.1.1.2.0_64_disk1_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk2_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk3_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk4_1of1.zip

2. Need a database for repository

The supported database versions by Oracle Fusion Middleware 11gR1 are

Oracle 10.2.0.4+ (if it's a 10g)
Oracle 11.1.0.7+ (if it's an 11gR1)
Oracle 11.2.0.1+ (if it's an 11gR2)

3. Create repository schemas

Run RCU to install schemas for Oracle Portal and Oracle Discoverer. RCU only works on Linux platform. If the database is on other platforms, run RCU remotely connecting to the database.

4. Install WebLogic server

Run the .jar file to create new Middleware Home directory. Java must be available (by the PATH env variable) for the installtion, and its version must be high enough. Here is the way to check Java version:

$ ./java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)

5. Install Oracle Portal, Forms, Reports and Discoverer

Following guid has screenshots on installation:

Installation Guide for Oracle Portal, Forms, Reports and Discoverer
11g Release 1 (11.1.1) E10421-02

init parameter PROCESSES

Initialization parameter PROCESSES defines the max number of user connections that can access the database at a same time. The default value for the parameter is 300.

If the max number exceeds the parameter value, new connection will not be able to connect to the database. Grid Control will report the connection problem and the database will give error in trace file:

ORA-00020: maximum number of processes (300) exceeded

In that case, the issues can be confirmed by number of OS sessions and by database view v$process:

$ echo $ORACLE_SID
$ ps -ef | grep $ORACLE_SID | wc -l
299

SQL> select count(*) from v$process;

COUNT(*)
----------
297

To increase the value, run an alter statement. Then a database bounce is necessary.

SQL> alter system set processes=500 scope=pfile;

I read that below three parameters are related by a formula (in 10G, but might not be always). If database reports ORA-00018 error, you may also consider modifying parameter PROCESSES (or find why the number of sessions jumped).

ORA-00018: maximum number of sessions exceeded

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

Oracle Client Patchset

When Oracle releases a database (RDBMS) version, it releases a corresponding client. For example, for 11.2.0.1.0 database server, aix.ppc64_11gR2_client.zip and aix.ppc32_11gR2_client.zip are client files. If you want to upgrade your client (i.e. from 11.2.0.1 to 11.2.0.3), you have to use the server patchset. Document 438049.1 says "For database and client upgrade use the same patch set. There is no separate patch set for client".

Document 207303.1 provides a matrix summarizing client and server combinations that are supported Oracle.

To install Oracle client utilities, choose "Custom" during the installation. Document 437377.1 shows how to install SQL*Loader on the 10G client:

The steps to install SQL*Loader in client in Custom Installation :

1) Start the Oracle Universal Installer (OUI) from the client source (CD)

2) Choose 'Installation Type'
==> Select the "Custom" and then continue with 'Next'

3) Choose the ORACLE_HOME, continue with 'Next'

4) In the 'Available Product Components' Screen
==> Select the "Oracle Database Utilities" and then continue with 'Next'
Note: Only components with a check mark are installed.

5) At the "Summary" screen Click "Install"

Finish the installation. This will Install SQL Loader.

You will be able to find the "sqlldr.exe" (or "sqlldr") in the directory %ORACLE_HOME%\bin in Windows and $ORACLE_HOME\bin in UNIX

Get AWR, ADDR, and ASH reports from SQL scripts

One way to identify the causes of poor performance in the database is to review AWR, ADDR, and ASH reports. They should be for a period of 60 or more minutes, covering the poor performance period.

. To generate the AWR report, execute the following script and select two snapshots:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

>> Choose the TXT format or the HTML format.

. To generate the ADDM report, execute:

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt

>> Choose the TXT format or the HTML format.

. To generate the ASH report, execute:

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt

>> Choose the TXT format or the HTML format.