Mani's Blog

September 30, 2011

Oracle: ORA-22924: snapshot too old on BLOB columns

Filed under: Oracle — mani @ 9:00 pm

Issue:

We have a tool that migrated data between databases.   It reads partition by partition and moves them.  Due to nature of the application only current month or recent data will be busy with DML.   Only allowed DMLs are Insert or Delete.  The table contains BLOB columns.  When it is reading migration current month partition, the application started throwing errors “: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old “

UNDO_RETENTION and UNDO space all looked good.

Reason and solution:

LOB segments don’t use ROLLBACK or UNDO tablespace (except that are stored inline).

If there are high number of concurrent updates as well as reads of LOB column then increase the value for PCTVERSION on LOB Column.

PCTVERSION is percentage of LOB storage space kept for old versions of LOB data to maintain the read consistency for reads started before an update.  The default value is 10 percent.

RETENTION also can be used alternatively, but I used PCTVERSION to fix the issue.

I have increased the value from 10 to 50.

ALTER TABLE INTERNAL_MSG_DATA MODIFY LOB (RECIPIENT_IDS) ( PCTVERSION 50 );

ALTER TABLE INTERNAL_MSG_DATA MODIFY LOB (BODY) ( PCTVERSION 50 );

September 26, 2011

Incorrect table definition; there can be only one auto column and it must be defined as a key

Filed under: MySQL — mani @ 6:20 pm

I was dropping primary key which I don’t want.  I got the following error.

alter table invitation_migration drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Error can be skipped by:  alter table <table_name> drop primary key, ADD PRIMARY KEY (‘diff_column_from_table’);

September 6, 2011

MySQL: Defragmenting a Table

Filed under: MySQL — mani @ 11:28 pm

We have many sharded databases in MySQL.  The nature of the application updates, deletes records.  Realized that we may have lot of fragmentation on tables and wanted to remove it.  This is has been done on Master Slave environment.  We were able to reclaim good amount of storage from bigger fragmented tables.  It is performed by NULL alter table operation.  BE AWARE that this process would rebuild the objects, so cannot have DML running on the table during defragmentation.

First do it on Slave, then failover the service to slave  and then do it on Master.

— Set timeout

set wait_timeout = 28800;
set interactive_timeout = 28800;

select @@interactive_timeout;
select @@wait_timeout;
— stop the slave

stop slave;
show slave status\G

set sql_log_bin = 0;

ALTER TABLE _topic ENGINE=INNODB ;
Query OK, 882094 rows affected (11 min 48.53 sec)
Records: 882094  Duplicates: 0  Warnings: 0

Once you are done.  Start slave to catchup.

Failover the service to slave and do the same on Master.

We are able to reclaim 50GB storage per cluster(including master and slave).   Anyway it depends on environment and its characteristics.

September 2, 2011

MySQL: ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Filed under: MySQL — mani @ 10:26 pm

The requirement is the script needed super privileges to create tables,triggers and few other objects on specific database.   I ran into this error.

(root@localhost) [(none)]> grant super on inv0.* to inv0@’%’;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Super is one of the privileges should be granted as global not specific to the database.  Grant is like this.

(root@localhost) [(none)]> grant super on *.* to inv0@’%’;
Query OK, 0 rows affected (0.00 sec)

September 1, 2011

Oracle: RMAN-06059: expected archived log not found

Filed under: Oracle — mani @ 11:08 pm

I scheduled a full backup that includes archive logs that are not backed up yet.   I ran into error

RMAN> backup device type disk tag '%s' archivelog all not backed up;
Starting backup at 01-SEP-11
current log archived
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/01/2011 22:46:30
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file %s.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

It is okay, I don’t want to take archive logs that are missing now.

Solution: connect to RMAN and run “change archivelog all crosscheck;”  this fixed the issue.

RMAN> change archivelog all crosscheck;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1041 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1060 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1057 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1087 device type=DISK
validation succeeded for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_115_717451798.dbf RECID=115 STAMP=725994293
validation succeeded for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_116_717451798.dbf RECID=116 STAMP=725994356
validation succeeded for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_117_717451798.dbf RECID=117 STAMP=725994358
validation succeeded for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_1399_717451798.dbf RECID=118 STAMP=760657462
validation succeeded for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_1400_717451798.dbf RECID=119 STAMP=760742780
validation succeeded for archived log
archived log file name=/local/data/oracle/nfs/XNB3CORE/archivelog/2011_09_01/o1_mf_1_1401_7602bmxc_.arc RECID=120 STAMP=760747092
validation succeeded for archived log
archived log file name=/local/data/oracle/nfs/XNB3CORE/archivelog/2011_09_01/o1_mf_1_1402_7602dlgm_.arc RECID=121 STAMP=760747154
Crosschecked 7 objects

validation failed for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_1_717451798.dbf RECID=1 STAMP=717457431
validation failed for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_2_717451798.dbf RECID=2 STAMP=718149657
validation failed for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_3_717451798.dbf RECID=3 STAMP=718477282
validation failed for archived log
archived log file name=/local/opt/oracle/product/11.1.0/db_1/dbs/arch1_4_717451798.dbf RECID=4 STAMP=718763012
………

Crosschecked 114 objects

RMAN>

Oracle: ORA-15028: ASM file ‘%S’ not dropped; currently being accessed

Filed under: Oracle — mani @ 5:33 pm

We had a situation on TEST DB, the datafile is corrupted.  Decided to drop the datafile since we are not using it.    Dropped using ALTER TABLESPACE,  I don’t see the file in v$datafile.  The physical file still exist, get following error.

ASMCMD> rm -f xn_msg.dbf
ORA-15032: not all alterations performed
ORA-15028: ASM file ‘+XN_DATA2/XNB1RAC/DATAFILE/xn_msg.dbf’ not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

Solution:

I will have to restart the instance then I was able to remove the file.  I wouldn’t prefer to restart the instance, since it is staging area, no much impact.

ASMCMD> rm -f xn_msg.dbf

Create a free website or blog at WordPress.com.