Friday, May 18, 2012

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

Tuesday, May 8, 2012

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>