impdp时碰到的表空间与默认blocksize不同的问题。

以前习惯用rman duplicate方式迁移，没有注意到block size的差异，这次在创建新DB后，计划用数据泵迁移数据时，碰到了block size不匹配的问题。

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=expbak dumpfile=expdp.dmp logfile=impdp.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
**ORA-29339: tablespace block size 16384 does not match configured block sizes**

Failing sql is:
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/xxxx/undotbs01.dbf' SIZE 209715200 AUTOEXTEND ON NEXT 10485760 MAXSIZE 65535M,'/xxxx/undTOEXTEND ON NEXT 10485760 MAXSIZE 65535M BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-29339: tablespace block size 16384 does not match configured block sizes
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE '/xxxx/users01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 65535M LOGGING ONLINE PERMANENT BLOCKSIZE 163OALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


Wed Jan 10 17:44:34 2024
statement in resumable session 'SYSTEM.SYS_IMPORT_FULL_01.1' was suspended due to
    ORA-01658: unable to create INITIAL extent for segment in tablespace SN_DATA

查看源库和目标库相关参数：

SQL> show parameter db_block_size;

源库为：

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     16384


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192


SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                        2617245696
large pool                                                          335544320
java pool                                                           268435456
streams pool                                                        134217728
DEFAULT buffer cache                                               2.2280E+10
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
ASM Buffer Cache                                                            0

14 rows selected.

测试了一下，使用block size 16k的方式创建表空间的话可以成功，但在网上查询说表空间和默认block size不同的话，影响性能，还必须调整对应的buffer cache：

Wed Jan 10 18:04:22 2024
create tablespace xxx datafile '/xxx/xxx01.dbf' size 50m AUTOEXTEND ON next 10m MAXSIZE UNLIMITED BLOCKSIZE 16k
Completed: create tablespace xxx datafile '/xxx/xxx01.dbf' size 50m AUTOEXTEND ON next 10m MAXSIZE UNLIMITED BLOCKSIZE 16k

决定卸载oracle重装，在选择使用目标时，不要选择gerneral purpose，而选择自定义方式创建，后面可以修改默认block size大小。

这里记录一下遇到的问题。