下午收到告警，某同城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 ---------- ------------ ---------------------------------------------------------------------- -------------------- ---------- 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 FILE# CREATION_TIM NAME BYTES/1024/1024/1024 AUX_NAME ---------- ------------ ---------------------------------------------------------------------- -------------------- ---------- 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. SQL> alter database create datafile '/home/db/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00018' as new; Database altered.
6、启动同步
SQL> alter database recover managed standby database disconnect from session using current logfile; Database altered. 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 18280 643072 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 MRP0 104318 APPLYING_LOG N/A N/A 18280 295185 9 9 1 2 RFS 81844 IDLE LGWR 43790 18281 43309 0 0 1 0 RFS 101341 IDLE ARCH 27516 0 0 0 0 INST_ID THREAD# PROCESS PID STATUS CLIENT_PROCESS CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- ---------- ---------- ------ ------------- ---------------- ------------- --------- ---------- ------------- ------------ 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 18596 722944 0 0 2 1 ARCH 65258 CLOSING ARCH 65258 18597 139264 0 0 2 1 ARCH 65260 CLOSING ARCH 65260 18591 67584 0 0 2 1 RFS 81245 IDLE LGWR 30715 18598 48677 0 0 INST_ID THREAD# PROCESS PID STATUS CLIENT_PROCESS CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS ---------- ---------- ---------- ------ ------------- ---------------- ------------- --------- ---------- ------------- ------------ 2 0 RFS 65360 IDLE UNKNOWN 49378 0 0 0 0 23 rows selected.
完善工作： a、改回参数
SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH; System altered. SQL> show parameter manage NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ control_management_pack_access string DIAGNOSTIC+TUNING resource_manager_cpu_allocation integer 8 resource_manager_plan string standby_file_management string AUTO undo_management string AUTO SQL>
b、打开定时删除归档 取消crontab的注释。 至此处理完毕。
四、总结
遇到这种情况，总结一下处理方法 1、扩容，扩容后再create new。 2、使用resize的方法释放已有空间，在create new。 3、先create new到剩余空间充足的磁盘组，后续在通过rman backup as copy的方法移到DATA磁盘组。
