Adding a datafile to a tablespace on ASM

I  had created a database using the ASM diskgroup +DATA for the Oracle datafiles. Next, I wanted to add another datafile to the USERS tablespace.

The commands below show how I went about it.

SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/testasm/datafile/system.256.755000669 +DATA/testasm/datafile/sysaux.257.755000671 +DATA/testasm/datafile/undotbs1.258.755000671 +DATA/testasm/datafile/users.259.755000671 SQL> alter tablespace users add datafile '+DATA/testasm/datafile/users.260.755000671' size 4M; alter tablespace users add datafile '+DATA/testasm/datafile/users.260.755000671' size 4M * ERROR at line 1: ORA-01276: Cannot add file +DATA/testasm/datafile/users.260.755000671.  File has an Oracle Managed Files file name. SQL> alter tablespace users add datafile '+DATA' size 100M; Tablespace altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/testasm/datafile/system.256.755000669 +DATA/testasm/datafile/sysaux.257.755000671 +DATA/testasm/datafile/undotbs1.258.755000671 +DATA/testasm/datafile/users.259.755000671 +DATA/testasm/datafile/users.266.755002021 SQL>

Enterprise Manager: Hostname should not contain underscore _ in it.

While using dbca to create a database with Enterprise manager configured. The configuration of the Enterprise Manager failed with the following error message:

"Invalid Hostname isvp14_ora. Hostname should not contain an underscore _ in it."

 Well, RFC 952 and RFC 1123 explain the rules for choosing a hostname.

Anyroad, here is the workaround:
On the shell prompt set the ORACLE_HOSTNAME variable to your machines IP address, and then re-configure.

bash-3.2$ export ORACLE_HOSTNAME=<ip_address>

I/O stat for Veritas VxVM volumes

I had created a Veritas disk group(dg3) which was made up of 3 volumes (SSD, SAS, and Nearline SAS drives).

I was running the Oracle TPC-C workload, and wanted to measure the iostat information for the diskgroup. Running iostat -x was not particularly useful as it gave me an output as follows:

[root@isvx7 smarttier]# iostat -x 3 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util .................. .................... .................... VxVM5000 0.00 0.00 0.00 0.00 0.00 0.00 5.77 0.00 0.35 0.35 0.00 VxVM23000 0.00 0.00 0.20 1830.51 3.13 14261.30 7.79 0.36 0.20 0.14 26.46 VxVM23001 0.00 0.00 192.38 1488.29 5490.32 34298.45 23.67 0.45 0.27 0.15 25.38 VxVM23002 0.00 0.00 1.91 117.70 67.23 7716.99 65.08 0.07 0.56 0.24 2.90 VxVM6000 0.00 0.00 9.70 845.52 9146.15 11965.72 24.69 0.18 0.21 0.17 14.28 ..................... ..................... .....................

I then found that using vmstat is much more useful, and here is what I got when I ran the vmstat command:

[root@isvx7 report]# vxstat -i 3 -g dg3 OPERATIONS BLOCKS AVG TIME(ms) TYP NAME READ WRITE READ WRITE READ WRITE Tue 14 Jun 2011 11:19:19 AM MST vol vol_nlsas 0 0 0 0 0.00 0.00 vol vol_sas 4 888 2056 108967 0.75 0.71 vol vol_ssd 49 29066 784 308527 1.55 0.17 Tue 14 Jun 2011 11:19:22 AM MST vol vol_nlsas 0 0 0 0 0.00 0.00 vol vol_sas 0 2 0 64 0.00 0.00 vol vol_ssd 107 28129 1712 286129 0.50 0.17 Tue 14 Jun 2011 11:19:25 AM MST vol vol_nlsas 0 0 0 0 0.00 0.00 vol vol_sas 4 881 2056 108455 1.25 0.66 vol vol_ssd 169 26647 2704 291859 0.50 0.18

ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace USERS

While running the Hammerora workload against the database that I had created. I noticed that though the workload was running, the "Transaction Counter" was still showing 0.

I looked into the alert log of the database instance, and notice lots of ORA-1654 being generated.

ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS

To fix the issue I first found the datafile names and there sizes for the USERS tablespace.

SQL> select file_name,bytes/1024/1024 "Megs" from dba_data_files where tablespace_name=   2  'USERS';  SQL> select file_name,bytes/1024/1024 "Megs" from dba_data_files where tablespace_name='USERS'; FILE_NAME --------------------------------------------------------------------------------       Megs ---------- /oracle_mvfs/testorcl/users01.dbf           32735 /oracle_mvfs/testorcl/users02.dbf           30720 /oracle_mvfs/testorcl/users03.dbf           30720 .......... .......... ..........

Next, I increased the size of the datafiles as follows.

SQL> alter database datafile '/oracle_mvfs/testorcl/users20.dbf' resize 32735M; Database altered. SQL> alter database datafile '/oracle_mvfs/testorcl/users19.dbf' resize 32735M; Database altered.

Voila, we were back on track.

ORA-24550: signal received: [si_signo=11] [si_errno=0] [si_code=1] [si_int=-1066996032]

While trying to run the tpc-c workload using Hammerora tool I got the following ORA-24550 error message and the client GUI crashed.

The exact error message was:

ORA-24550: signal received: [si_signo=11] [si_errno=0] [si_code=1] [si_int=-1066996032] [si_ptr=0x2b8bc066eec0] [si_addr=0xef000a72bd] kpedbg_dmp_stack()+314<-kpeDbgCrash()+166<-kpeDbgSignalHandler()+158<-skgesig_sigactionHandler()+217<-__restore_rt()<-DisplayText()+621

After trying out a few things, I tried to connect to the database(testorcl) using the hammer ora users(tpcc) account and got the error message ORA-12541: TNS:no listener

[oracle@isvx7 ~]$ sqlplus tpcc/xxxx@testorcl SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 9 11:23:08 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. ERROR: ORA-12541: TNS:no listener

I started the listener using lsnrctl, and then started the Hammerora client againg to generate the TPC-C workload, this time things worked fine.

vxlicinst ERROR V-21-1-19 Duplicate License key detected

I was working on Symantec SmartTier and multi-volumed file systems when I got the message that my license has expired and that I need a new one.

[root@isvx7 ~]# fsppadm query /oracle_mvfs UX:vxfs fsppadm: ERROR: V-3-26550: The command failed with message - à< UX:vxfs fsppadm: ERROR: V-3-26550: The command failed with message - /sbin/vxlictest -n "VERITAS File System" -sup -f "Quality of Storage Service" UX:vxfs fsppadm: ERROR: V-3-26540: Valid license is not found

I got a new license, and tried to add it when I got the message "Duplicate License key detected"

[root@isvx7 ~]# cd /sbin [root@isvx7 sbin]# ls vxlic* vxlicinst  vxlicrep  vxlictest [root@isvx7 sbin]# /sbin/vxlicinst Symantec License Manager vxlicinst utility version 3.02.51.010 Copyright (C) 1996-2010 Symantec Corporation. All rights reserved. Enter your license key : MMMM-NNNN-OOOO-PPPP-QQQQ-RRRR-SSSS-T12 vxlicinst ERROR V-21-1-19 Duplicate License key detected [root@isvx7 sbin]#

The workaround for this is move all the files under /etc/vx/license/lic to some other directory, and then try to apply the license again.

[root@isvx7 sbin]# cd /etc/vx/licenses/lic [root@isvx7 lic]# ls AAAA-BBB-CCCC-DDDD-EEEE-FFFF-GGGG-HHHH-III.vxlic JJJJ-KKKK-LLLL-MMMM-NNNN-OOOO-PPPP-QQQQ-RRR.vxlic SSSS-TTTT-UUUU-VVVV-WWWW-XXXX-YYYY-ZZZZ-AAA.vxlic BBBB-CCCC-DDDD-EEEE-FFFF-GGGG-HHHH-III.vxlic [root@isvx7 lic]# mv *.* /root/vxlicenses [root@isvx7 ~]# /sbin/vxlicinst Symantec License Manager vxlicinst utility version 3.02.51.010 Copyright (C) 1996-2010 Symantec Corporation. All rights reserved. Enter your license key : MMMM-NNNN-OOOO-PPPP-QQQQ-RRRR-SSSS-T12 Number of days left for Demo = 364 License key successfully installed for VERITAS Storage Foundation Enterprise HA License key successfully installed for VERITAS Volume Manager License key successfully installed for VERITAS Cluster Server License key successfully installed for VERITAS File System [root@isvx7 ~]#

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

I wanted to turn archiving for my database. To turn on archive log mode the database must be mounted, but not open. If the database is already open, first shutdown the database using "shutdown" or "shutdown immediate". If you use "shutdown abort" to shutdown the database, then while running the SQL> alter database archivelog; you will get the ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

The easiest way to get around this would be to start the database again using startup, and the shutting it again using "shutdown" or "shutdown immediate". This will do the trick.

-bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 8 12:06:23 2011 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> archive log list; Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence     132361 Current log sequence           132363 SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size                  2213976 bytes Variable Size            1.7985E+10 bytes Database Buffers         8858370048 bytes Redo Buffers              145174528 bytes Database mounted. SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-00265: instance recovery required, cannot set ARCHIVELOG mode SQL> shutdown; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size                  2213976 bytes Variable Size            1.7985E+10 bytes Database Buffers         8858370048 bytes Redo Buffers              145174528 bytes Database mounted. SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-00265: instance recovery required, cannot set ARCHIVELOG mode SQL> shutdown; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size                  2213976 bytes Variable Size            1.7985E+10 bytes Database Buffers         8858370048 bytes Redo Buffers              145174528 bytes Database mounted. Database opened. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size                  2213976 bytes Variable Size            1.7985E+10 bytes Database Buffers         8858370048 bytes Redo Buffers              145174528 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> SQL>  archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence     132362 Next log sequence to archive   132364 Current log sequence           132364 SQL>