AIX ORACLE IMP异常扩表空间一例

科技小先锋 2017-11-15

Oracle SQL open volume database 表空间 Group


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) bwhere 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 
 
-------------------------------------------------------------------------------------
PPs2了,扩成功了!
 
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) bwhere a.Tablespace_Name = b.Tablespace_Name
结果如下:

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

 

本文转自zylhsy 51CTO博客,原文链接:http://blog.51cto.com/yunlongzheng/412214,如需转载请自行联系原作者
登录 后评论
下一篇
云栖号资讯小编
5244人浏览
2020-07-13
相关推荐
ORACLE冷备份与恢复
895人浏览
2015-12-11 11:54:00
Aix5~6小机运维
846人浏览
2015-04-08 20:26:00
ORA-12500内存耗尽一例
1003人浏览
2017-11-12 16:45:00
Oracle故障排查思路
2183人浏览
2018-09-11 15:16:19
0
0
0
968