Oracle备库空间不足导致主库添加数据文件失败处理方法

Oracle 11.2.0.4 RAC+ DG架构，主库表空间添加了数据文件，备库空间不足，re-create数据文件失败。

alert日志：

RC0: Encountered disk I/O error 19502
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/home/arch/emrdb/1_157891_851889862.arch' (error 19502) (emrdb)
Fri Mar 05 19:58:19 2021
RFS[4]: Selected log 13 for thread 1 sequence 157892 dbid -1015616954 branch 851889862
ARC0: I/O error 19502 archiving log 14 to '/home/arch/emrdb/1_157891_851889862.arch'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance emrdb - Archival Error
ORA-16038: log 14 sequence# 157891 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 14 thread 1: '/home/oradata/emrdb/stdredo1_02.log'
Fri Mar 05 19:58:20 2021
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance emrdb - Archival Error
ORA-16014: log 14 sequence# 157891 not archived, no available destinations
ORA-00312: online log 14 thread 1: '/home/oradata/emrdb/stdredo1_02.log'
Fri Mar 05 20:03:32 2021
ARC3: Encountered disk I/O error 19502
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '/home/arch/emrdb/1_157891_851889862.arch' (error 19502) (emrdb)
ARC3: I/O error 19502 archiving log 14 to '/home/arch/emrdb/1_157891_851889862.arch'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance emrdb - Archival Error
ORA-16038: log 14 sequence# 157891 cannot be archived

查看操作系统空间

$ df -Th
Filesystem           Type     Size  Used Avail Use% Mounted on
/dev/mapper/vg_cxyyemrdg-lv_root
                     ext4      50G   12G   36G  25% /
tmpfs                tmpfs    7.8G  300K  7.8G   1% /dev/shm
/dev/sda2            ext4     477M   41M  411M   9% /boot
/dev/sda1            vfat     200M  264K  200M   1% /boot/efi
/dev/mapper/vg_cxyyemrdg-lv_home
                     ext4     3.7T  3.7T   103M  100% /home
/media/rhel6.9.iso   iso9660  3.7G  3.7G     0 100% /mnt

剩余空间不足，对/home进行了扩容。

$ df -Th
Filesystem           Type     Size  Used Avail Use% Mounted on
/dev/mapper/vg_cxyyemrdg-lv_root
                     ext4      50G   12G   36G  25% /
tmpfs                tmpfs    7.8G  300K  7.8G   1% /dev/shm
/dev/sda2            ext4     477M   41M  411M   9% /boot
/dev/sda1            vfat     200M  264K  200M   1% /boot/efi
/dev/mapper/vg_cxyyemrdg-lv_home
                     ext4    5T  3.7T   1.3T  100% /home
/media/rhel6.9.iso   iso9660  3.7G  3.7G     0 100% /mnt

备库里查看数据文件情况

SQL> col name for a45         
SQL> set linesize 200
SQL> select file#,name from v$datafile order by 1;

    FILE# NAME
---------- ---------------------------------------------
         1 /home/oradata/emrdb/system01.dbf
         2 /home/oradata/emrdb/sysaux01.dbf
         3 /home/oradata/emrdb/undotbs01.dbf
         ...
          170 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAM
           ED00170

数据文件170没创建成功

主库查看数据文件170名称路径

SQL> col name for a45
SQL> set linesize 200
SQL> select file#,name from v$datafile where file#=170;

     FILE# NAME
---------- ---------------------------------------------
       170 +DATA/emrdb/datafile/ts_emr139.dbf

主库备份170号数据文件：

RMAN> backup datafile 170 format '/home/oracle/ts_emr139.dbf';

Starting backup at 09-MAR-2021 12:47:41
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00170 name=+DATA/emrdb/datafile/ts_emr139.dbf
channel ORA_DISK_1: starting piece 1 at 09-MAR-2021 12:47:41
channel ORA_DISK_1: finished piece 1 at 09-MAR-2021 12:47:44
piece handle=/home/oracle/ts_emr139.dbf tag=TAG20210309T124741 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 09-MAR-2021 12:47:44

备库恢复170号数据文件

RMAN> run{
2> set newname for datafile 170 to '/home/oradata/emrdb/ts_emr139.dbf';
3> restore datafile 170;
4> switch datafile 170;
5> recover datafile 170;
6> }

executing command: SET NEWNAME

Starting restore at 09-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1514 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00170 to /home/oradata/emrdb/ts_emr139.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/ts_emr139.dbf
channel ORA_DISK_1: piece handle=/home/oracle/ts_emr139.dbf tag=TAG20210309T124741
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 09-MAR-21

datafile 170 switched to datafile copy
input datafile copy RECID=13 STAMP=1066740948 file name=/home/oradata/emrdb/ts_emr139.dbf

Starting recover at 09-MAR-21
using channel ORA_DISK_1

查看备库数据文件

SQL> col name for a45         
SQL> set linesize 200
SQL> select file#,name from v$datafile order by 1;

    FILE# NAME
---------- ---------------------------------------------
         1 /home/oradata/emrdb/system01.dbf
         2 /home/oradata/emrdb/sysaux01.dbf
         3 /home/oradata/emrdb/undotbs01.dbf
         ...
          170 /home/oradata/emrdb/ts_emr139.dbf

170 rows selected.

恢复完成后，启动备库MRP

SQL> alter database recover managed standby database using current logfile disconnect from session;

确认alert日志等无报错