ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

I was in the process of creating the Swingbench Order Entry Schema with scale factor 100 when I noticed that the schema creation failed.

On looking at the alert log, I found the following message:
Tue Jul 10 21:47:51 2012
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Tue Jul 10 21:47:53 2012
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_37158936.trc  (incident=2745):
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb/incident/incdir_2745/testdb_ora_37158936_i2745.trc
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_37158936.trc  (incident=2746):
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)

Fix:
The oewizard had suggested that a schema of scale factor 100 needs around 60 GB of temp file space.
My host machine(database server) did have 16GB of memory, so I didn't make any changes to it.

So I just added the tempfiles as follows:
SQL> alter tablespace temp add tempfile '/orcldata/testdb/temp02.dbf' size 31G;

Tablespace altered.

SQL> alter tablespace temp add tempfile '/orcldata/testdb/temp03.dbf' size 31G;

Tablespace altered.

SQL> alter tablespace temp add tempfile '/orcldata/testdb/temp04.dbf' size 31G;

Tablespace altered.

SQL> select FILE_NAME, (bytes)/1024/1024 from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
(BYTES)/1024/1024
-----------------
/orcldata/testdb/temp01.dbf
               20

/orcldata/testdb/temp02.dbf
            31744

/orcldata/testdb/temp03.dbf
            31744

/orcldata/testdb/temp04.dbf
            31744

This fixed the issue.

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>

DBUA hangs with ORA-19815 , ORA-19809 ,and ORA-19804

I was upgrading from Oracle 11.1.0.6.0 to Oracle 11.2.0.1.0 using dbua when I noticed that around 84% at "Upgrading Oracle Java Packages" dbua was hung.

On looking at alter log showed the following message:

bash-3.2$ tail -f ./app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log Tue Apr 03 22:21:38 2012 Errors in file /u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc1_30212224.trc: ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available. ************************************************************************ You have following choices to free up space from recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,    then consider changing RMAN ARCHIVELOG DELETION POLICY. 2. Back up files to tertiary device such as tape using RMAN    BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter to    reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating    system command was used to delete files, then use RMAN CROSSCHECK and    DELETE EXPIRED commands. ************************************************************************ Errors in file /u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc1_30212224.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 36977152 bytes disk space from 2147483648 limit ARC1: Error 19809 Creating archive log file to '/oraarc/TESTDB/archivelog/2012_04_03/o1_mf_1_60_%u_.arc'

Fix:

DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area.

After setting the ORACLE_HOME to 11gR2 and ORACLE_SID=testdb I found that the current size of "db_recovery_file_dest_size" was 2G. So I increased it to 10G.

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 3 22:26:52 2012 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter db_recovery_file_dest_size; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size           big integer 2G SQL> SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G'; System altered. SQL>

I noticed in the alter log the "Archiver process freed from errors. No longer stopped" message.

bash-3.2$ tail -f ./app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=MEMORY; Tue Apr 03 22:29:06 2012 db_recovery_file_dest_size of 10240 MB is 19.99% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Tue Apr 03 22:29:37 2012 Archived Log entry 64 added for thread 1 sequence 60 ID 0xffffffff98badd37 dest 1: Archiver process freed from errors. No longer stopped Tue Apr 03 22:29:37 2012 Thread 1 advanced to log sequence 63 (LGWR switch)   Current log# 3 seq# 63 mem# 0: /oradb/testdb/redo03.log Archived Log entry 65 added for thread 1 sequence 61 ID 0xffffffff98badd37 dest 1: Tue Apr 03 22:29:38 2012 Archived Log entry 66 added for thread 1 sequence 62 ID 0xffffffff98badd37 dest 1: Tue Apr 03 22:29:57 2012 SERVER COMPONENT id=ORDIM: status=VALID, version=11.2.0.1.0, timestamp=2012-04-03 22:29:57 Tue Apr 03 22:29:57 2012 Thread 1 advanced to log sequence 64 (LGWR switch)   Current log# 1 seq# 64 mem# 0: /oradb/testdb/redo01.log Tue Apr 03 22:29:57 2012 Archived Log entry 67 added for thread 1 sequence 63 ID 0xf

After that dbua proceeded file, and completed with the upgrade from Oracle 11.1.0.6.0 to Oracle 11.2.0.1.0

Increasing the jfs2 file system size on AIX beyond 64GB

In my earlier post I talk about increasin the size of a file system on AIX

http://shettymayur.blogspot.com/2011/01/how-to-increase-file-system-size-on-aix.html

In this post I created a 100GB SAN volume, which I mapped to my AIX 7.1 host.

Using smitty I created a 64GB jfs2 file system on the volume.

/dev/fslv02       64.00     52.61   18%     5459     1% /bigfs isvp14_ora> lslv fslv02 LOGICAL VOLUME:     fslv02                 VOLUME GROUP:   bigfs LV IDENTIFIER:      00f62a6b00004c0000000136568e8fcf.2 PERMISSION:     read/write VG STATE:           active/complete        LV STATE:       opened/syncd TYPE:               jfs2                   WRITE VERIFY:   off MAX LPs:            512                    PP SIZE:        128 megabyte(s) COPIES:             1                      SCHED POLICY:   parallel LPs:                512                    PPs:            512 STALE PPs:          0                      BB POLICY:      relocatable INTER-POLICY:       minimum                RELOCATABLE:    yes INTRA-POLICY:       middle                 UPPER BOUND:    32 MOUNT POINT:        /bigfs                 LABEL:          /bigfs MIRROR WRITE CONSISTENCY: on/ACTIVE EACH LP COPY ON A SEPARATE PV ?: yes Serialize IO ?:     NO isvp14_ora>

The volume that was mapped to the host was 100GB, but when I tried to increase the file system size beyond 64GB I get the below message.

isvp14_ora> chfs -a size=90G /bigfs 0516-787 extendlv: Maximum allocation for logical volume fslv02 is 512.

We see that it's complaining that the maximum number LP is 512. LV can go up to 512 LPs, each LP is 128 MB , so  512 x 128MB= 64 GB

Below we below see the command to increase the LPs to 1024, this will give us a max file system of 128 GB

isvp14_ora> chlv -x'1024' fslv02 isvp14_ora> lslv fslv02 LOGICAL VOLUME:     fslv02                 VOLUME GROUP:   bigfs LV IDENTIFIER:      00f62a6b00004c0000000136568e8fcf.2 PERMISSION:     read/write VG STATE:           active/complete        LV STATE:       opened/syncd TYPE:               jfs2                   WRITE VERIFY:   off MAX LPs:            1024                   PP SIZE:        128 megabyte(s) COPIES:             1                      SCHED POLICY:   parallel LPs:                512                    PPs:            512 STALE PPs:          0                      BB POLICY:      relocatable INTER-POLICY:       minimum                RELOCATABLE:    yes INTRA-POLICY:       middle                 UPPER BOUND:    32 MOUNT POINT:        /bigfs                 LABEL:          /bigfs MIRROR WRITE CONSISTENCY: on/ACTIVE EACH LP COPY ON A SEPARATE PV ?: yes Serialize IO ?:     NO isvp14_ora> chfs -a size=90G /bigfs Filesystem size changed to 188743680 isvp14_ora> df -g /dev/fslv02       90.00     78.60   13%     5459     1% /bigfs

Installing Sqoop on Red Hat 4.1.2-50

As I wanted to install sqoop on my Red Hat box, I went to https://ccp.cloudera.com/display/CDHDOC/Sqoop+Installation where I found some install steps. For Red Hat Linux it said to do a "yum install", but that failed on my box.

[root@isvx7 ~]# sudo yum install sqoop Loaded plugins: rhnplugin, security There was an error communicating with RHN. RHN Satellite or RHN Classic support will be disabled. Error communicating with server. The message was: Error Message:         Please run rhn_register as root on this client Error Class Code: 9 Error Class Info: Invalid System Credentials. Explanation:      An error has occurred while processing your request. If this problem      persists please enter a bug report at bugzilla.redhat.com.      If you choose to submit the bug report, please be sure to include      details of what you were trying to do when this error occurred and      details on how to reproduce this problem. Setting up Install Process No package sqoop available. Nothing to do

I then ended up installing the mapr packages from http://package.mapr.com/releases/v1.2.2/redhat/

As per the http://www.mapr.com/doc/display/MapR/Local+Packages+-+Red+Hat you required the following packages

Role	Package	Dependencies
Sqoop	mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm	mapr-core-1.2.2.12455.GA-1.x86_64.rpm mapr-hbase-internal-1.2.2.12455.GA-1.x86_64.rpm mapr-sqoop-internal-1.2.2.12455.GA-1.x86_64.rpm

[root@isvx7 bigdata]# wget http://package.mapr.com/releases/v1.2.2/redhat/mapr-s            qoop-internal-1.2.2.12455.GA.v1.3.0-1.x86_64.rpm --2012-03-06 15:52:34--  http://package.mapr.com/releases/v1.2.2/redhat/mapr-sqo            op-internal-1.2.2.12455.GA.v1.3.0-1.x86_64.rpm Resolving package.mapr.com... 205.251.215.104, 205.251.215.186, 205.251.215.219,             ... Connecting to package.mapr.com|205.251.215.104|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 3425149 (3.3M) [application/x-redhat-package-manager] Saving to: `mapr-sqoop-internal-1.2.2.12455.GA.v1.3.0-1.x86_64.rpm' 100%[======================================>] 3,425,149    347K/s   in 8.4s 2012-03-06 15:52:42 (399 KB/s) - `mapr-sqoop-internal-1.2.2.12455.GA.v1.3.0-1.x8            6_64.rpm' saved [3425149/3425149]

[root@isvx7 bigdata]# wget http://package.mapr.com/releases/v1.2.2/redhat/mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm --2012-03-06 15:48:30--  http://package.mapr.com/releases/v1.2.2/redhat/mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm Resolving package.mapr.com... 216.137.43.10, 216.137.43.125, 216.137.43.136, ... Connecting to package.mapr.com|216.137.43.10|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 1973 (1.9K) [application/x-redhat-package-manager] Saving to: `mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm' 100%[======================================>] 1,973       --.-K/s   in 0s 2012-03-06 15:48:30 (125 MB/s) - `mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm' saved   [1973/1973]

[root@isvx7 bigdata]# wget http://package.mapr.com/releases/v1.2.2/redhat/mapr-c  ore-1.2.2.12455.GA-1.x86_64.rpm --2012-03-06 15:48:49--  http://package.mapr.com/releases/v1.2.2/redhat/mapr-cor  e-1.2.2.12455.GA-1.x86_64.rpm Resolving package.mapr.com... 216.137.43.136, 216.137.43.174, 216.137.43.203, ..  . Connecting to package.mapr.com|216.137.43.136|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 112941895 (108M) [application/x-redhat-package-manager] Saving to: `mapr-core-1.2.2.12455.GA-1.x86_64.rpm' 100%[======================================>] 112,941,895  678K/s   in 3m 36s 2012-03-06 15:52:25 (511 KB/s) - `mapr-core-1.2.2.12455.GA-1.x86_64.rpm' saved [  112941895/112941895]

[root@isvx7 bigdata]# ls mapr-core-1.2.2.12455.GA-1.x86_64.rpm mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm mapr-sqoop-internal-1.2.2.12455.GA.v1.3.0-1.x86_64.rpm

[root@isvx7 bigdata]# rpm -i mapr-core-1.2.2.12455.GA-1.x86_64.rpm [root@isvx7 bigdata]# rpm -i mapr-sqoop-internal-1.2.2.12455.GA.v1.3.0-1.x86_64.            rpm [root@isvx7 bigdata]# rpm -i mapr-sqoop-1.2.2.12455.GA-1.x86_64.rpm [root@isvx7 bigdata]# which sqoop /usr/bin/sqoop

At this point only the sqoop rpm's were added, the hbase rpm had not yet been added to the linux box that is why we get a warning message when invoking sqoop as shown below.

[root@isvx7 bigdata]# sqoop -h Warning: /opt/mapr/hbase/hbase-0.90.4 does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. No such sqoop tool: -h. See 'sqoop help'.

We now get the hbase rpm and then install the hbase rpm on the linux host.

[root@isvx7 bigdata]# wget http://package.mapr.com/releases/v1.2.2/redhat/mapr-hbase-internal-1.2.2.12455.GA.v0.90.4-1.x86_64.rpm --2012-03-06 16:47:39--  http://package.mapr.com/releases/v1.2.2/redhat/mapr-hbase-internal-1.2.2.12455.GA.v0.90.4-1.x86_64.rpm Resolving package.mapr.com... 205.251.215.160, 205.251.215.180, 205.251.215.222, ... Connecting to package.mapr.com|205.251.215.160|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 29024196 (28M) [application/x-redhat-package-manager] Saving to: `mapr-hbase-internal-1.2.2.12455.GA.v0.90.4-1.x86_64.rpm' 100%[========================================>] 29,024,196  1.56M/s   in 38s 2012-03-06 16:48:17 (755 KB/s) - `mapr-hbase-internal-1.2.2.12455.GA.v0.90.4-1.x86_64.rpm' saved [29024196/29024196] [root@isvx7 bigdata]# [root@isvx7 bigdata]# rpm -i mapr-hbase-internal-1.2.2.12455.GA.v0.90.4-1.x86_64.rpm

No warning messages this time around.

[root@isvx7 bigdata]# sqoop help usage: sqoop COMMAND [ARGS] Available commands:   codegen            Generate code to interact with database records   create-hive-table  Import a table definition into Hive   eval               Evaluate a SQL statement and display the results   export             Export an HDFS directory to a database table   help               List available commands   import             Import a table from a database to HDFS   import-all-tables  Import tables from a database to HDFS   job                Work with saved jobs   list-databases     List available databases on a server   list-tables        List available tables in a database   merge              Merge results of incremental imports   metastore          Run a standalone Sqoop metastore   version            Display version information See 'sqoop help COMMAND' for information on a specific command. [root@isvx7 bigdata]# sqoop version Sqoop 1.3.0 git commit id Compiled by sathya@satdev1 on Tue Aug 23 18:03:37 PDT 2011 [root@isvx7 bigdata]#

Storage options for Oracle RAC and Oracle Clusterware with AIX

Every now and again this question come up, "What are the supported storage options for Oracle RAC and Oracle Clusterware with AIX?"

This table below from the Oracle docs pretty much answers that question in a nutshell.