JFS2 options for Oracle database

JFS2 Filesystem is pretty much the filesystem on choice for an Oracle database running on AIX. The question that always comes up is, what filesystems options should I use when using it for an Oracle database.

Here is what Oracle recommend:

Oracle database file	JFS2 Filesystem options
Control Files	cio
Data Files	cio
Log Files	cio, agblksize=512
ORACLE_HOME	cio option not supported

How to create a file system for the Oracle logs with agblksize=512
# mkfs -s <fs_size> -o agblksize=512 /ora_log

# mount /ora_log

How do you update an existing jfs2 filesystem to have the cio option:
chfs -a options=cio /ora_data   

With Oracle 11g we can enable dio and cio on jfs2 at file level. This is done by setting the FILESYSTEMIO_OPTIONS parameter in the server parameter file to setall or directIO. This enables Concurrent Input-Output on JFS2.

SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE; System altered. SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ filesystemio_options                 string    SETALL

setall enables both direct/IO and asynchronous I/O wherever possible.

Oracle Database logs files should be on a separate JFS2 file system for optimal performance.

Here are some good links talk about jfs2 and Oracle:
http://docs.oracle.com/cd/E11882_01/server.112/e10839/appa_aix.htm
http://haveyoutriedtoswitchitonandoff.blogspot.com/2009/08/tuning-jfs2-for-oracle.html

Oracle Flashback does not work for tables on the system tablespace

I was playing around with Oracle Flashback, to see when what happens when we have flashback enabled and we drop a table and on how it can be retrieved.

I connected to the database as user sys

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Tue May 8 10:33:43 2012 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user; USER is "SYS" SQL>

I then did the following to start using Flashback. I first put the database in archivelog mode, and then set up
the DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST_SIZE, and DB_RECOVERY_FILE_DEST

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 6948155392 bytes Fixed Size                  2138944 bytes Variable Size            3758099648 bytes Database Buffers         3154116608 bytes Redo Buffers               33800192 bytes Database mounted. SQL> SQL>  alter database archivelog; Database altered.

The flashback retention was set for 2880 minutes(2 days), with a recovery destination size of 4 G, on filesystem /bigfs.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880; System altered. SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=4G; System altered. SQL> alter system set DB_RECOVERY_FILE_DEST='/bigfs'; System altered. SQL> alter database flashback off; Database altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL>

After the database was opened I created a table called beatles_table, and entered some data into it.

SQL> create table beatles_table (   2  id number(2),   3  name varchar2(30)   4  ); SQL> insert into beatles_table values (1, 'John'); 1 row created. SQL> insert into beatles_table values (2, 'Paul'); 1 row created. SQL> insert into beatles_table values (3, 'George'); 1 row created. SQL> insert into beatles_table values (4, 'Ringo'); 1 row created. SQL>

As we see the owner of the table is SYS and the table is on the system tablespace

SQL> select owner, table_name, tablespace_name   2  from all_tables where   3  table_name='BEATLES_TABLE'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ TABLESPACE_NAME ------------------------------ SYS                            BEATLES_TABLE SYSTEM SQL> SQL> drop table BEATLES_TABLE; Table dropped. SQL> SQL> flashback table "BEATLES_TABLE" to before drop; flashback table "BEATLES_TABLE" to before drop * ERROR at line 1: ORA-38305: object not in RECYCLE BIN SQL> select * from RECYCLEBIN; no rows selected SQL> SQL> select * from DBA_RECYCLEBIN where ORIGINAL_NAME='BEATLES_TABLE'; no rows selected SQL> SQL> select * from BEATLES_TABLE; select * from BEATLES_TABLE               * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> create table beatles_table (   2  id number(2),   3  name varchar2(30)   4  ) tablespace users; Table created. SQL> SQL> select owner, table_name, tablespace_name   2  from all_tables where   3  table_name='BEATLES_TABLE'; OWNER                          TABLE_NAME ------------------------------ ------------------------------ TABLESPACE_NAME ------------------------------ SYS                            BEATLES_TABLE USERS SQL> insert into beatles_table values (1, 'John'); 1 row created. SQL> insert into beatles_table values (2, 'Paul'); 1 row created. SQL> insert into beatles_table values (3, 'George'); 1 row created. SQL> insert into beatles_table values (4, 'Ringo'); 1 row created. SQL> SQL> drop table BEATLES_TABLE; Table dropped. SQL> select * from RECYCLEBIN; OBJECT_NAME                    ORIGINAL_NAME                    OPERATION ------------------------------ -------------------------------- --------- TYPE                      TS_NAME                        CREATETIME ------------------------- ------------------------------ ------------------- DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN ------------------- ---------- -------------------------------- --- ---    RELATED BASE_OBJECT PURGE_OBJECT      SPACE ---------- ----------- ------------ ---------- BIN$v4sirVl4AHrgQwkLUw0Aeg==$0 BEATLES_TABLE                    DROP TABLE                     USERS                          2012-05-08:10:57:50 2012-05-08:10:58:35    2786532                                  YES YES      72633       72633        72633          8 SQL> select * from BEATLES_TABLE; select * from BEATLES_TABLE               * ERROR at line 1: ORA-00942: table or view does not exist SQL> flashback table "BEATLES_TABLE" to before drop; Flashback complete. SQL> select * from BEATLES_TABLE;         ID NAME ---------- ------------------------------          1 John          2 Paul          3 George          4 Ringo SQL> SQL> select * from RECYCLEBIN; no rows selected SQL>