Oracle数据库adg数据没同步,adg备库磁盘空间不足，主库添加数据文件导致同步异常...

80 阅读 0 评论 53 点赞

我是靠谱客的博主落后期待，最近开发中收集的这篇文章主要介绍Oracle数据库adg数据没同步,adg备库磁盘空间不足，主库添加数据文件导致同步异常...，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

下午收到告警，某同城adg同步异常，数据库比较重要，需要紧急处理。

一、分析

1、查看同步状态

该环境是主备均为两节点的rac adg结构，登录备库1节点，查询同步进程状态。

SQL> set line 999

SQL> select inst_id,thread#,process,pid,status,client_process,client_pid,sequence#,block#,active_agents,known_agents from gv$managed_standby;

INST_ID THREAD# PROCESS PID STATUS CLIENT_PROCESS CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

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

1 2 ARCH 75208 CLOSING ARCH 75208 18277 622592 0 0

1 2 ARCH 75210 CLOSING ARCH 75210 18273 18432 0 0

1 0 ARCH 75212 CONNECTED ARCH 75212 0 0 0 0

1 2 ARCH 75214 CLOSING ARCH 75214 18279 276480 0 0

1 2 ARCH 75216 CLOSING ARCH 75216 18274 26624 0 0

1 2 ARCH 75218 CLOSING ARCH 75218 18272 202752 0 0

1 2 ARCH 75220 CLOSING ARCH 75220 18270 24576 0 0

1 2 ARCH 75222 CLOSING ARCH 75222 18252 626688 0 0

1 2 RFS 81844 IDLE LGWR 43790 18280 367617 0 0

1 0 RFS 101341 IDLE ARCH 27516 0 0 0 0

1 0 RFS 75268 IDLE UNKNOWN 27512 0 0 0 0

1 0 RFS 102717 IDLE ARCH 2645 0 0 0 0

2 1 ARCH 65246 CLOSING ARCH 65246 18592 722944 0 0

2 1 ARCH 65248 CLOSING ARCH 65248 18593 163840 0 0

2 0 ARCH 65250 CONNECTED ARCH 65250 0 0 0 0

2 1 ARCH 65252 CLOSING ARCH 65252 18594 610304 0 0

2 1 ARCH 65254 CLOSING ARCH 65254 18595 253952 0 0

2 1 ARCH 65256 CLOSING ARCH 65256 18589 32768 0 0

2 1 ARCH 65258 CLOSING ARCH 65258 18590 198656 0 0

2 1 ARCH 65260 CLOSING ARCH 65260 18591 67584 0 0

2 1 RFS 81245 IDLE LGWR 30715 18596 452360 0 0

2 0 RFS 65360 IDLE UNKNOWN 49378 0 0 0 0

22 rows selected.

发现同步进程MRP0已经停止。

2、查看告警日志

Wed Jan 27 12:59:12 2021

Media Recovery Waiting for thread 2 sequence 18279 (in transit)

Recovery of Online Redo Log: Thread 2 Group 14 Seq 18279 Reading mem 0

Mem# 0: +DATA/dr_xxxxdb/onlinelog/group_14.287.1046720255

Wed Jan 27 12:59:12 2021

Archived Log entry 5392 added for thread 2 sequence 18278 ID 0x61a69b1c dest 1:

Media Recovery Waiting for thread 1 sequence 18595 (in transit)

Recovery of Online Redo Log: Thread 1 Group 13 Seq 18595 Reading mem 0

Mem# 0: +DATA/dr_xxxxdb/onlinelog/group_13.288.1046720253

Wed Jan 27 14:54:22 2021

Errors in file /home/db/oracle/diag/rdbms/dr_xxxxdb/ibpldb1/trace/ibpldb1_pr00_75552.trc:

ORA-01119: error in creating database file '+DATA'

ORA-17502: ksfdcre:4 Failed to create file +DATA

ORA-15041: diskgroup "DATA" space exhausted

File #18 added to control file as 'UNNAMED00018'.

Originally created as:

'+DATA/ibpldb/datafile/xxxxx_data.295.1062946453'

Recovery was unable to create the file as a new OMF file.

MRP0: Background Media Recovery terminated with error 1274

Errors in file /home/db/oracle/diag/rdbms/dr_xxxxdb/ibpldb1/trace/ibpldb1_pr00_75552.trc:

ORA-01274: cannot add datafile '+DATA/ibpldb/datafile/xxxxx_data.295.1062946453' - file could not be created

Wed Jan 27 14:54:22 2021

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 5816190494

Wed Jan 27 14:54:25 2021

Reconfiguration started (old inc 6, new inc 8)

List of instances:

1 2 (myinst: 1)

Global Resource Directory frozen

Communication channels reestablished

Master broadcasted resource hash value bitmaps

Non-local Process blocks cleaned out

Wed Jan 27 14:54:25 2021

LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Wed Jan 27 14:54:25 2021

LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Set master node info

Submitted all remote-enqueue requests

Dwn-cvts replayed, VALBLKs dubious

All grantable enqueues granted

Submitted all GCS remote-cache requests

Fix write in gcs resources

Reconfiguration complete

Wed Jan 27 14:54:27 2021

MRP0: Background Media Recovery process shutdown (ibpldb1)

Wed Jan 27 14:59:11 2021

RFS[4]: Selected log 12 for thread 2 sequence 18280 dbid 1638254198 branch 947517108

从alert里面可以看出，在Jan 27 14:54:22 2021添加数据文件的时候，由于DATA磁盘组不足，创建数据文件报错，导致MRP0进程shutdown。

3、查看磁盘组及数据文件状态

SQL> set lines 300

SQL> col name for a20

SQL> col free for a20

SQL> select name,group_number,state ,TOTAL_MB/1024,FREE_MB/1024,trunc(free_mb/total_mb*100,2)||'%' free from v$asm_diskgroup ;

NAME GROUP_NUMBER STATE TOTAL_MB/1024 FREE_MB/1024 FREE

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

ARCH 1 CONNECTED 50 29.390625 58.78%

CRS 2 MOUNTED 9.29882813 8.39453125 90.27%

DATA 3 CONNECTED 402 2.546875 .63%

FRA 4 MOUNTED 51 50.8789063 99.76%

可以看到DATA磁盘组剩余空间不足3G了。

SQL> alter session set nls_DATe_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select file#,CREATION_TIME,NAME,bytes/1024/1024/1024,AUX_NAME from v$datafile ;

FILE# CREATION_TIME NAME BYTES/1024/1024/1024 AUX_NAME

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

1 2017-05-18 21:12:52 +DATA/dr_xxxxdb/datafile/system.261.1046719933 10 NONE

2 2017-05-18 21:12:58 +DATA/dr_xxxxdb/datafile/sysaux.260.1046719939 10 NONE

3 2017-05-18 21:13:11 +DATA/dr_xxxxdb/datafile/undotbs1.267.1046719699 20 NONE

4 2017-05-18 21:13:28 +DATA/dr_xxxxdb/datafile/undotbs2.266.1046719699 20 NONE

5 2017-05-18 21:13:28 +DATA/dr_xxxxdb/datafile/users.291.1046720051 .004882813 NONE

6 2017-06-23 16:20:21 +DATA/dr_xxxxdb/datafile/xxxxx_data.272.1046719379 30 NONE

7 2017-06-23 16:20:34 +DATA/dr_xxxxdb/datafile/xxxxx_index.265.1046719707 20 NONE

8 2017-06-23 16:21:02 +DATA/dr_xxxxdb/datafile/xxxxx_data.271.1046719379 30 NONE

9 2017-06-23 16:21:15 +DATA/dr_xxxxdb/datafile/xxxxx_index.264.1046719843 20 NONE

10 2017-06-23 16:22:38 +DATA/dr_xxxxdb/datafile/xxxxx_data.270.1046719379 30 NONE

11 2017-06-23 16:22:51 +DATA/dr_xxxxdb/datafile/xxxxx_index.263.1046719897 20 NONE

12 2017-06-23 16:23:30 +DATA/dr_xxxxdb/datafile/xxxxx_data.269.1046719379 30 NONE

13 2017-06-23 16:23:45 +DATA/dr_xxxxdb/datafile/xxxxx_index.262.1046719933 20 NONE

14 2018-06-22 11:34:28 +DATA/dr_xxxxdb/datafile/xxxxx_index.258.1046720035 2 NONE

15 2018-06-22 11:35:10 +DATA/dr_xxxxdb/datafile/xxxxx_data.268.1046719675 30 NONE

16 2020-04-30 11:46:37 +DATA/dr_xxxxdb/datafile/xxxxx_data.259.1046720035 10 NONE

17 2020-08-24 12:44:20 +DATA/dr_xxxxdb/datafile/xxxxx_data.293.1049287461 10 UNKNOWN

18 2021-01-27 14:54:22 /home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018 0 UNKNOWN

18 rows selected.

查看数据文件状态可以看出，2021-01-27 14:54:22的时候，备库同步数据文件不成功，数据文件创建在本地了。

处理之前先注释掉crontab中的定时删除归档脚本，避免归档被删除。

三、处理过程

1、尝试重新create datafile

SQL> alter database create datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.293.1049287461' as new size 1g;

alter database create datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.293.1049287461' as new size 1g

ERROR at line 1:

ORA-00933: SQL command not properly ended

SQL> alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as new ;

alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as new

ERROR at line 1:

ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic.

SQL> ALTER SYSTEM SET standby_file_management=manual scope=both;

System altered.

SQL> alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as new ;

alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as new

ERROR at line 1:

ORA-01119: error in creating database file '+DATA'

ORA-17502: ksfdcre:4 Failed to create file +DATA

ORA-15041: diskgroup "DATA" space exhausted

可以看出create 新的数据文件不成功，因为创建的数据文件大于剩余磁盘组的大小。

SQL> alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as '+DATA' size 1g;

alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as '+DATA' size 1g

ERROR at line 1:

ORA-01136: specified size of file 18 (131072 blocks) is less than original size of 1310720 blocks

ORA-01111: name for data file 18 is unknown - rename to correct file

ORA-01110: data file 18: '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018'

SQL> alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as '+DATA' size 1.5G;

alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as '+DATA' size 1.5G

ERROR at line 1:

ORA-02017: integer value required

SQL> alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as '+DATA' size 1200M;

alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as '+DATA' size 1200M

ERROR at line 1:

ORA-01136: specified size of file 18 (153600 blocks) is less than original size of 1310720 blocks

ORA-01111: name for data file 18 is unknown - rename to correct file

ORA-01110: data file 18: '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018'

SQL> select 1310720*8/1024 from dual;

1310720*8/1024

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

10240

可以看出，该数据文件的大小是10G。

2、查看是否有有条件扩容

SQL> select GROUP_NUMBER,DISK_NUMBER,name,state,HEADER_STATUS,PATH ,TOTAL_MB/1024,MOUNT_STATUS from v$asm_disk ;

GROUP_NUMBER DISK_NUMBER NAME STATE HEADER_STATUS PATH TOTAL_MB/1024 MOUNT_STATUS

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

3 1 DATA_0001 NORMAL MEMBER /dev/sdf 100 CACHED

3 2 DATA_0002 NORMAL MEMBER /dev/sdh 101 CACHED

2 1 CRS_0001 NORMAL MEMBER /dev/sdc 3.09960938 CACHED

1 0 ARCH_0000 NORMAL MEMBER /dev/sdg 50 CACHED

3 0 DATA_0000 NORMAL MEMBER /dev/sde 201 CACHED

2 2 CRS_0002 NORMAL MEMBER /dev/sdd 3.19921875 CACHED

2 0 CRS_0000 NORMAL MEMBER /dev/sdb 3 CACHED

4 0 FRA_0000 NORMAL MEMBER /dev/sdi 51 CACHED

8 rows selected.

16:28:33 SQL> !lkbls

/bin/bash: lkbls: command not found

16:28:41 SQL> !lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

sr0 11:0 1 1024M 0 rom

sda 8:0 0 200G 0 disk

|-sda1 8:1 0 512M 0 part /boot/efi

|-sda2 8:2 0 512M 0 part /boot

`-sda3 8:3 0 199G 0 part

|-rootvg-lvroot (dm-0) 253:0 0 10G 0 lvm /

|-rootvg-lvswap (dm-1) 253:1 0 16G 0 lvm [SWAP]

|-rootvg-lvhome (dm-2) 253:2 0 10G 0 lvm /home

|-rootvg-lvtmp (dm-3) 253:3 0 10G 0 lvm /tmp

|-rootvg-lvusr (dm-4) 253:4 0 10G 0 lvm /usr

|-rootvg-lvvar (dm-5) 253:5 0 10G 0 lvm /var

|-rootvg-lvopt (dm-6) 253:6 0 10G 0 lvm /opt

|-rootvg-lvoracle (dm-7) 253:7 0 30G 0 lvm /home/db/oracle

|-rootvg-lvgrid (dm-8) 253:8 0 30G 0 lvm /home/db/grid

|-rootvg-lvopenv (dm-9) 253:9 0 10G 0 lvm /usr/openv

|-rootvg-lvemagent (dm-10) 253:10 0 10G 0 lvm /emagent

`-rootvg-lvuniagent (dm-11) 253:11 0 5G 0 lvm /home/app/uniagent

sdb 8:16 0 3G 0 disk

sdd 8:48 0 3.2G 0 disk

sde 8:64 0 201G 0 disk

sdg 8:96 0 50G 0 disk

sdc 8:32 0 3.1G 0 disk

sdh 8:112 0 101G 0 disk

sdf 8:80 0 100G 0 disk

sdi 8:128 0 51G 0 disk

发现已经没有多余的磁盘可以给asm扩容了，紧急反馈给客户，客户联系存储工程师划磁盘。

3、尝试释放已有磁盘空间

边等待存储划分磁盘，边思考有没有别的思路来处理。首先想到的是可不可以通过resize的方法，释放已有的磁盘空间。先查询一下

SQL> select 'alter database datafile '''||file_name||''' resize ' ||

2 ceil( (nvl(hwm,1)*8000)/1024/1024 ) || 'm;' cmd

3 from dba_data_files a,

4 ( select file_id, max(block_id+blocks-1) hwm

5 from dba_extents

6 group by file_id ) b

7 where a.file_id = b.file_id(+)

8 and ceil( blocks*8000/1024/1024) -

9 ceil( (nvl(hwm,1)*8000)/1024/1024 ) > 0

10 /

CMD

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

alter database datafile '+DATA/dr_xxxxdb/datafile/system.261.1046719933' resize 763m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.272.1046719379' resize 27348m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_index.263.1046719897' resize 8200m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_index.262.1046719933' resize 11094m;

alter database datafile '+DATA/dr_xxxxdb/datafile/sysaux.260.1046719939' resize 4723m;

alter database datafile '+DATA/dr_xxxxdb/datafile/undotbs2.266.1046719699' resize 100m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.271.1046719379' resize 22204m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.293.1049287461' resize 3411m;

alter database datafile '+DATA/dr_xxxxdb/datafile/undotbs1.267.1046719699' resize 83m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_index.265.1046719707' resize 7036m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_index.264.1046719843' resize 274m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.259.1046720035' resize 5055m;

alter database datafile '+DATA/dr_xxxxdb/datafile/users.291.1046720051' resize 1m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_data.268.1046719675' resize 1m;

alter database datafile '+DATA/dr_xxxxdb/datafile/xxxxx_index.258.1046720035' resize 1m;

15 rows selected.

SQL> col aux_name for a10

SQL> select file#,CREATION_TIME,NAME,bytes/1024/1024/1024,AUX_NAME from v$datafile ;

FILE# CREATION_TIM NAME BYTES/1024/1024/1024 AUX_NAME

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

1 18-MAY-17 +DATA/dr_xxxxdb/datafile/system.261.1046719933 10 NONE

2 18-MAY-17 +DATA/dr_xxxxdb/datafile/sysaux.260.1046719939 10 NONE

3 18-MAY-17 +DATA/dr_xxxxdb/datafile/undotbs1.267.1046719699 20 NONE

4 18-MAY-17 +DATA/dr_xxxxdb/datafile/undotbs2.266.1046719699 20 NONE

5 18-MAY-17 +DATA/dr_xxxxdb/datafile/users.291.1046720051 .004882813 NONE

6 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_data.272.1046719379 30 NONE

7 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_index.265.1046719707 20 NONE

8 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_data.271.1046719379 30 NONE

9 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_index.264.1046719843 20 NONE

10 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_data.270.1046719379 30 NONE

11 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_index.263.1046719897 20 NONE

12 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_data.269.1046719379 30 NONE

13 23-JUN-17 +DATA/dr_xxxxdb/datafile/xxxxx_index.262.1046719933 20 NONE

14 22-JUN-18 +DATA/dr_xxxxdb/datafile/xxxxx_index.258.1046720035 2 NONE

15 22-JUN-18 +DATA/dr_xxxxdb/datafile/xxxxx_data.268.1046719675 30 NONE

16 30-APR-20 +DATA/dr_xxxxdb/datafile/xxxxx_data.259.1046720035 10 NONE

17 24-AUG-20 +DATA/dr_xxxxdb/datafile/xxxxx_data.293.1049287461 10 UNKNOWN

18 27-JAN-21 /home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018 0 UNKNOWN

18 rows selected.

发现有部分大的数据文件，实际使用并不多，可以resize。给客户反馈，申请resize释放空间。奈何客户不同意。因为该方法我想的是重启备库节点到mount下执行resize操作。后续再测试环境测试该方法验证一下。

4、考虑将数据文件create到已有的空闲的asm磁盘组中

SQL> select name,group_number,state ,TOTAL_MB/1024,FREE_MB/1024,trunc(free_mb/total_mb*100,2)||'%' free from v$asm_diskgroup ;

NAME GROUP_NUMBER STATE TOTAL_MB/1024 FREE_MB/1024 FREE

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

ARCH 1 CONNECTED 50 29.390625 58.78%

CRS 2 MOUNTED 9.29882813 8.39453125 90.27%

DATA 3 CONNECTED 402 2.546875 .63%

FRA 4 MOUNTED 51 50.8789063 99.76%

因为FRA磁盘组有比较大的剩余，所以考虑先create datafile到FRA磁盘组中，恢复adg同步，等DATA磁盘组扩容后将FRA磁盘组中的数据文件在移到DATA磁盘组中。该方法客户也没有同意，后续再测试环境完整的测试该方法。

5、ASM磁盘组扩容

等到存储工程师也划分好磁盘了，开始扩容工作。

[root@dr-xxxxx01 ~]# lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

sr0 11:0 1 1024M 0 rom

sda 8:0 0 200G 0 disk

|-sda1 8:1 0 512M 0 part /boot/efi

|-sda2 8:2 0 512M 0 part /boot

`-sda3 8:3 0 199G 0 part

|-rootvg-lvroot (dm-0) 253:0 0 10G 0 lvm /

|-rootvg-lvswap (dm-1) 253:1 0 16G 0 lvm [SWAP]

|-rootvg-lvhome (dm-2) 253:2 0 10G 0 lvm /home

|-rootvg-lvtmp (dm-3) 253:3 0 10G 0 lvm /tmp

|-rootvg-lvusr (dm-4) 253:4 0 10G 0 lvm /usr

|-rootvg-lvvar (dm-5) 253:5 0 10G 0 lvm /var

|-rootvg-lvopt (dm-6) 253:6 0 10G 0 lvm /opt

|-rootvg-lvoracle (dm-7) 253:7 0 30G 0 lvm /home/db/oracle

|-rootvg-lvgrid (dm-8) 253:8 0 30G 0 lvm /home/db/grid

|-rootvg-lvopenv (dm-9) 253:9 0 10G 0 lvm /usr/openv

|-rootvg-lvemagent (dm-10) 253:10 0 10G 0 lvm /emagent

`-rootvg-lvuniagent (dm-11) 253:11 0 5G 0 lvm /home/app/uniagent

sdb 8:16 0 3G 0 disk

sdd 8:48 0 3.2G 0 disk

sde 8:64 0 201G 0 disk

sdg 8:96 0 50G 0 disk

sdc 8:32 0 3.1G 0 disk

sdh 8:112 0 101G 0 disk

sdf 8:80 0 100G 0 disk

sdi 8:128 0 51G 0 disk

sdj 8:144 0 202G 0 disk

划分了202G的新磁盘，接下来就是扩容工作了。先做udev绑定

KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id --whitelisted --device=/dev/%k", RESULT=="36000c2998e8dae734b3af6d632c39659", SYMLINK+="oracleasm/asm-data04", OWNER="grid", GROUP="asmadmin", MODE="0660"

使udev生效

udevadm control --reload-rules

udevadm trigger --sysname-match=sdj

进入grid用户，开始扩容

su - grid

sqlplus / as sysasm

SQL> select GROUP_NUMBER,DISK_NUMBER,name,state,HEADER_STATUS,PATH ,TOTAL_MB/1024,FREE_MB/1024,MOUNT_STATUS from v$asm_disk ;

GROUP_NUMBER DISK_NUMBER NAME STATE HEADER_STATU PATH TOTAL_MB/1024 FREE_MB/1024 MOUNT_S

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

0 0 NORMAL CANDIDATE /dev/sdj 0 0 CLOSED

3 1 DATA_0001 NORMAL MEMBER /dev/sdf 100 .63671875 CACHED

3 2 DATA_0002 NORMAL MEMBER /dev/sdh 101 .63671875 CACHED

2 1 CRS_0001 NORMAL MEMBER /dev/sdc 3.09960938 2.79785156 CACHED

1 0 ARCH_0000 NORMAL MEMBER /dev/sdg 50 28.65625 CACHED

3 0 DATA_0000 NORMAL MEMBER /dev/sde 201 1.2734375 CACHED

2 2 CRS_0002 NORMAL MEMBER /dev/sdd 3.19921875 2.89453125 CACHED

2 0 CRS_0000 NORMAL MEMBER /dev/sdb 3 2.70214844 CACHED

4 0 FRA_0000 NORMAL MEMBER /dev/sdi 51 50.8789063 CACHED

9 rows selected.

SQL> set lines 300

SQL> col name for a20

SQL> col free for a20

SQL> select name,group_number,state ,TOTAL_MB/1024,FREE_MB/1024,trunc(free_mb/total_mb*100,2)||'%' free from v$asm_diskgroup ;

NAME GROUP_NUMBER STATE TOTAL_MB/1024 FREE_MB/1024 FREE

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

ARCH 1 MOUNTED 50 28.65625 57.31%

CRS 2 MOUNTED 9.29882813 8.39453125 90.27%

DATA 3 MOUNTED 402 2.546875 .63%

FRA 4 MOUNTED 51 50.8789063 99.76%

SQL> alter diskgroup data add disk '/dev/sdj';

Diskgroup altered.

扩容后，重新创建数据文件

SQL> select file#,CREATION_TIME,NAME,bytes/1024/1024/1024,AUX_NAME from v$datafile ;

FILE# CREATION_TIM NAME BYTES/1024/1024/1024 AUX_NAME

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