AIX ORACLE IMP异常扩表空间一例

 

AIX ORACLE IMP异常扩表空间一例

 

一、起因

IMP数据倒入时发生ORA-01659错误：

IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 128 in tablespace USERS

 

二、分析

1、查询USERS表空间使用状况
Select a.Tablespace_Name, a.Total || 'M' Total_Space, (a.Total - b.Free) || 'M' Used_Space, To_Char((a.Total - b.Free) / a.Total * 100, '99.99') || '%' Pct_Free
From
( Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
( Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name

结果如下：

	TABLESPACE_NAME	TOTAL_SPACE	USED_SPACE	PCT_FREE
39	SUZHOU	512M	511M	99．99%

显然SUZHOU表空间已经使用完了

 

三、处理
1、首先想到RESIZE表空间

SQL> alter database datafile /dev/rsuzhou_disk resize 1024m;

alter database datafile /dev/rsuzhou_disk resize 1024m

ERROR at line 1:

ORA-02236: invalid file name

-------------------------------------------------------------------------------------

怎么不行，不认文件名/dev/rsuzhou_disk（缺乏’’引号）

 

SQL> alter database SUZHOU resize 1024m;

alter database SUZHOU resize 1024m

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

-------------------------------------------------------------------------------------

怎么不行，缺乏关键字datafile

 

SQL> alter database datafile SUZHOU resize 1024m;

alter database datafile SUZHOU resize 1024m

ERROR at line 1:

ORA-02236: invalid file name

-------------------------------------------------------------------------------------

怎么不行，不认文件名SUZHOU

 

SQL> alter database datafile '/dev/rsuzhou_disk' resize 1024m;

alter database datafile '/dev/rsuzhou_disk' resize 1024m

ERROR at line 1:

ORA-01237: cannot extend datafile 74

ORA-01110: data file 74: '/dev/rsuzhou_disk'

ORA-27042: not enough space on raw partition to fullfill request

Additional information: 3

-------------------------------------------------------------------------------------

怎么不行，空间不够

 

SQL> alter database datafile '/dev/rsuzhou_disk' resize 768m;

alter database datafile '/dev/rsuzhou_disk' resize 768m

ERROR at line 1:

ORA-01237: cannot extend datafile 74

ORA-01110: data file 74: '/dev/rsuzhou_disk'

ORA-27042: not enough space on raw partition to fullfill request

Additional information: 3

-------------------------------------------------------------------------------------

怎么不行，空间不够

 

2 、空间不够就要扩空间

$ lsvg

rootvg

oradatavg

$ lsvg oradatavg

VOLUME GROUP:        oradatavg                VG IDENTIFIER: 000037750000d60000000112749c9dbb

VG STATE:            active                   PP SIZE:        512 megabyte(s)

VG PERMISSION:       read/write               TOTAL PPs:      1170 (599040 megabytes)

MAX LVs:             256                      FREE PPs:       626 (320512 megabytes)

LVs:                 128                      USED PPs:       544 (278528 megabytes)

OPEN LVs:            87                       QUORUM:         2

TOTAL PVs:           2                        VG DESCRIPTORS: 3

STALE PVs:           0                        STALE PPs:      0

ACTIVE PVs:          2                        AUTO ON:        no

Concurrent:          Enhanced-Capable         Auto-Concurrent: Disabled

VG Mode:             Concurrent                              

Node ID:             -                        Active Nodes:   

MAX PPs per VG:      32512                                    

MAX PPs per PV:      1016                     MAX PVs:        32

LTG size (Dynamic): 256 kilobyte(s)           AUTO SYNC:      no

HOT SPARE:           no                       BB POLICY:      relocatable

$ lsvg -l oradatavg

oradatavg:

LV NAME              TYPE       LPs   PPs   PVs LV STATE      MOUNT POINT

ocr_disk                        1     1     1    open/syncd    N/A

vote_disk                       1     1     1    open/syncd    N/A

system_disk          raw        6     6     1    open/syncd    N/A

undotbs01_disk       raw        8     8     1    open/syncd    N/A

undotbs02_disk       raw        8     8     1    open/syncd    N/A

log11_disk           raw        1     1     1    open/syncd    N/A

log12_disk           raw        1     1     1    open/syncd    N/A

log21_disk           raw        1     1     1    open/syncd    N/A

log22_disk           raw        1     1     1    open/syncd    N/A

control01_disk       raw        1     1     1    open/syncd    N/A

control02_disk       raw        1     1     1    open/syncd    N/A

control03_disk       raw        1     1     1    open/syncd    N/A

spfile_disk          raw        1     1     1    closed/syncd N/A

data_disk                       4     4     1    closed/syncd N/A

index_disk           raw        4     4     1    closed/syncd N/A

temp_disk            raw        8     8     1    open/syncd    N/A

suzhou_data_dis      raw        1     1     1    open/syncd    N/A

suzhou_index_di      raw        1     1     1    open/syncd    N/A

suzhou_disk          raw        1     1     1    open/syncd    N/A

 

$ lslv suzhou_disk

LOGICAL VOLUME:      suzhou_disk            VOLUME GROUP:   oradatavg

LV IDENTIFIER:       000037750000d60000000112749c9dbb.128 PERMISSION:     read/write

VG STATE:            active/complete        LV STATE:       opened/syncd

TYPE:                raw                    WRITE VERIFY:   off

MAX LPs:             512                    PP SIZE:        512 megabyte(s)

COPIES:              1                      SCHED POLICY:   parallel

LPs:                 1                      PPs:            1

STALE PPs:           0                      BB POLICY:      relocatable

INTER-POLICY:        minimum                RELOCATABLE:    yes

INTRA-POLICY:        middle                 UPPER BOUND:    32

MOUNT POINT:         N/A                    LABEL:          None

MIRROR WRITE CONSISTENCY: on/ACTIVE                              

EACH LP COPY ON A SEPARATE PV ?: yes                                    

Serialize IO ?:      NO              

                     

$ extendlv suzhou_disk 1

ksh: extendlv: 0403-006 执行许可权被拒绝。

-------------------------------------------------------------------------------------

为什么呢，需要DBA权限

 

p550a:/#extendlv suzhou_disk 1

p550a:/#ls -l /dev/rsuzhou_disk 

crwxrwxrwx    1 oracle   dba          80,128 10月29 08时36 /dev/rsuzhou_disk

p550a:/#lslv pt_users_disk                                      

LOGICAL VOLUME:      pt_users_disk          VOLUME GROUP:   oradatavg

LV IDENTIFIER:       000037750000d60000000112749c9dbb.97 PERMISSION:     read/write

VG STATE:            active/complete        LV STATE:       closed/syncd

TYPE:                                       WRITE VERIFY:   off

MAX LPs:             512                    PP SIZE:        512 megabyte(s)

COPIES:              1                      SCHED POLICY:   parallel

LPs:                 2                      PPs:            2

STALE PPs:           0                      BB POLICY:      relocatable

INTER-POLICY:        minimum                RELOCATABLE:    no

INTRA-POLICY:        middle                 UPPER BOUND:    32

MOUNT POINT:         N/A                    LABEL:         

MIRROR WRITE CONSISTENCY: on/ACTIVE                              

EACH LP COPY ON A SEPARATE PV ?: yes                                    

Serialize IO ?:      NO 

 

-------------------------------------------------------------------------------------

看 PPs 为 2 了，扩成功了！

 

3 、空间不够就要扩空间

SQL> alter database datafile 74 resize 1000m;

Database altered.

 

4 、查询 USERS 表空间使用状况
Select a.Tablespace_Name, a.Total || 'M' Total_Space, (a.Total - b.Free) || 'M' Used_Space, To_Char((a.Total - b.Free) / a.Total * 100, '99.99') || '%' Pct_Free
From
( Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
( Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name

结果如下：

	TABLESPACE_NAME	TOTAL_SPACE	USED_SPACE	PCT_FREE
39	SUZHOU	1000M	768M	76.80%