Sunday, March 30, 2014

ORA-29339

I discovered a new error today. I was using Data Pump to copy a database from Sun SPARC to X86 and from Oracle 11.1.0.6.0 to 11.2.0.4.0 for testing purposes. I extracted the tablespace creation SQL from the old database like this. (This is only some of it.):
 
SQL> set lines 32000
SQL> set pages 50
SQL> exec dbms_metadata.set_transform_param(-
> DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_metadata.get_ddl(-
> 'TABLESPACE','PINDAR_LARGE_32KBLOCKS')
  2  from dual
  3  /
 
DBMS_METADATA.GET_DDL('TABLESPACE','PINDAR_LARGE_32KBLOCKS')
--------------------------------------------------------------------------------
 
  CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE
  '/cisdpt/nbadpt1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,
  '/cisdpt/nbadpt1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,
  '/cisdpt/nbadpt1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000
  LOGGING ONLINE PERMANENT BLOCKSIZE 32768
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;
 
SQL>
 
I did a global replace to change part of the file name to bring it in line with the file system naming on the X86 server. Then I ran the SQL to create the tablespace in the new database but it failed:
 
SQL>   CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE
  2    '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,
  3    '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,
  4    '/database/GBNBAPF1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000
  5    LOGGING ONLINE PERMANENT BLOCKSIZE 32768
  6    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;
  CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE
*
ERROR at line 1:
ORA-29339: tablespace block size 32768 does not match configured block sizes
 
SQL>
 
The db_block_size was 8192 in both the old and new databases but the old one had the following extra line in its parameter file:
 
db_32k_cache_size=12m
 
This is to set up a cache for tablespaces with a 32K block size. I added this line to the parameter file for the new database and bounced it. Then I was able to create the tablespace there successfully:
 
SQL> CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE
  2  '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,
  3  '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,
  4  '/database/GBNBAPF1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000
  5  LOGGING ONLINE PERMANENT BLOCKSIZE 32768
  6  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;
 
Tablespace created.
 
SQL>

No comments: