1 数据库报错，发现磁盘空间不足

Fri Jul 09 15:22:50 2021

Errors in file /oracle/test/testtrace/diag/rdbms/testadg/test001/trace/test001_pr00_406649.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 #120 added to control file as 'UNNAMED00120'.

Originally created as:

'+DATA/test/datafile/tbs_test.13330.1077463317'

Recovery was unable to create the file as:

'+data'

MRP0: Background Media Recovery terminated with error 1274

Errors in file /oracle/test/testtrace/diag/rdbms/testadg/test001/trace/test001_pr00_406649.trc:

ORA-01274: cannot add datafile '+DATA/test/datafile/tbs_test.13330.1077463317' - file could not be created

2 查看ADG的状态，发现有报错

set linesize 200 pagesize 999

col MESSAGE for a90

col TIME for a25

select facility,severity,error_code,to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') TIME,message from v$dataguard_status;

Log Transport Services Control 0 2021/07/09 15:12:26 ARC3: Completed archiving thread 2 sequence 74564 (0-0)

Log Apply Services Warning 0 2021/07/09 15:12:37 Media Recovery Waiting for thread 2 sequence 74565 (in transit)

Log Apply Services Error 1274 2021/07/09 15:22:50 MRP0: Background Media Recovery terminated with error 1274

Log Apply Services Informational 0 2021/07/09 15:22:50 Managed Standby Recovery not using Real Time Apply

Log Apply Services Control 0 2021/07/09 15:23:15 MRP0: Background Media Recovery process shutdown

Log Transport Services Control 0 2021/07/09 15:33:38 ARC2: Beginning to archive thread 2 sequence 74565 (14688539160-14689334993)

Log Transport Services Control 0 2021/07/09 15:33:38 ARC2: Completed archiving thread 2 sequence 74565 (0-0)

3 查看ADG的延迟信息，发现延迟了7天

set linesize 300

col name for a20

set pagesize 999

col time for 999999999999999

SELECT * FROM V$STANDBY_EVENT_HISTOGRAM order by 5 ;

NAME TIME UNIT COUNT LAST_TIME_UPDATED

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

apply lag 22 hours 3595 07/10/2021 13:22:57

apply lag 23 hours 3595 07/10/2021 14:22:57

apply lag 1 days 3593 07/10/2021 15:22:57

apply lag 2 days 86705 07/11/2021 15:23:12

apply lag 3 days 86396 07/12/2021 15:23:24

apply lag 4 days 86467 07/13/2021 15:23:38

apply lag 5 days 86360 07/14/2021 15:23:50

a pply lag 7 days 81283 07/15/2021 15:25:09

4 查看v$datafile视图，发现有创建到本地的数据文件

SQL> select name,STATUS from v$datafile where STATUS !='ONLINE';

NAME STATUS

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

+DATA/testadg/datafile/system.297.1018423267 SYSTEM

+DATA/testadg/datafile/tbs_test.266.1077462783 RECOVER

+DATA/testadg/datafile/tbs_test.265.1077462839 RECOVER

+DATA/testadg/datafile/tbs_test.264.1077462891 RECOVER

+DATA/testadg/datafile/tbs_test.263.1077462969 RECOVER

+DATA/testadg/datafile/tbs_test.262.1077463025 RECOVER

+DATA/testadg/datafile/tbs_test.261.1077463071 RECOVER

+DATA/testadg/datafile/tbs_test.260.1077463139 RECOVER

+DATA/testadg/datafile/tbs_test.258.1077463195 RECOVER

+DATA/testadg/datafile/tbs_test.376.1077463243 RECOVER

+DATA/testadg/datafile/tbs_test.377.1077463297 RECOVER

/oracle/test/11204/dbs/UNNAMED00120 RECOVER

12 rows selected.

查看是否由此文件，发现没有

[oratest@adg01 ~]$ ls -ltr /oracle/test/11204/dbs/UNNAMED00120

ls: cannot access /oracle/test/11204/dbs/UNNAMED00120: No such file or directory

5 查看asm磁盘使用情况

[oracle@adg01 ~]$ asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 1024000 1009061 0 1009061 0 N ARCH/

MOUNTED EXTERN N 512 4096 1048576 3584000 17642 0 17642 0 N DATA/

MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCR/

MOUNTED EXTERN N 512 4096 1048576 1024000 971319 0 971319 0 N RECO/

添加磁盘后：

[oracle@adg01 ~]$ asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 1024000 1005212 0 1005212 0 N ARCH/

MOUNTED EXTERN N 512 4096 1048576 5632000 2065618 0 2065618 0 N DATA/

MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCR/

MOUNTED EXTERN N 512 4096 1048576 1024000 971319 0 971319 0 N RECO/

6 根据报错信息，查看Oracle官方文档（ MRP0: Background Media Recovery terminated with error 1119 (Doc ID 1397416.1)），使用如下方法解决

SQL> show parameter standby_file_management

NAME TYPE VALUE

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

standby_file_management string AUTO

alter system set standby_file_management=manual scope=both sid='*';

SQL> alter system set standby_file_management=manual scope=both sid='*';

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

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

standby_file_management string MANUAL

SQL> alter database create datafile '/oracle/test/11204/dbs/UNNAMED00120' as '+DATA';

alter database create datafile '/oracle/test/11204/dbs/UNNAMED00120' as '+DATA'

*

ERROR at line 1:

ORA-01136: specified size of file 120 (12800 blocks) is less than original size of 3932160 blocks

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

ORA-01110: data file 120: '/oracle/test/11204/dbs/UNNAMED00120'

SQL> alter database create datafile '/oracle/test/11204/dbs/UNNAMED00120' as '+DATA' size 30G;

Database altered.

SQL> alter system set standby_file_management=AUTO scope=both sid='*';

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

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

standby_file_management string AUTO

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

Database altered.

7 查看延迟，发现延迟下降很快，证明ADG恢复正常，如下

set linesize 300

col name for a20

set pagesize 999

col time for 999999999999999

SELECT * FROM V$STANDBY_EVENT_HISTOGRAM order by 5 ;

NAME TIME UNIT COUNT LAST_TIME_UPDATED

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

apply lag 2 days 86705 07/11/2021 15:23:12

apply lag 3 days 86396 07/12/2021 15:23:24

apply lag 7 days 653 07/15/2021 15:31:31

apply lag 6 days 87785 07/15/2021 15:56:20

apply lag 5 days 86518 07/15/2021 15:58:59

apply lag 4 days 87217 07/15/2021 16:11:36

apply lag 1 minutes 0

8 以下为参考的oracle官方文档的内容

MRP0: Background Media Recovery terminated with error 1119 (Doc ID 1397416.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

In this example, dbname=orcl

New datafile has been added to primary

Space has exhausted in standby file system or asm diskgroup

Fri Jan 13 11:25:54 2012

Successfully added datafile 37 to media recovery

Datafile #37: '+ASMDB/orcl/datafile/rsby_mis.345.772456805'

File #38 added to control file as 'UNNAMED00038'.

Originally created as:

'+ASMDB/gcprod/datafile/rsby_mis.304.772456135'

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

Errors with log +FRA/orcl/archivelog/2012_01_13/thread_2_seq_70338.720.772456111

MRP0: Background Media Recovery terminated with error 1119

Fri Jan 13 11:25:59 2012

Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_mrp0_528412.trc:

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

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

ORA-15041: diskgroup space exhausted

Some recovered datafiles maybe left media fuzzy

Media recovery may continue but open resetlogs may fail

Fri Jan 13 11:26:03 2012

SUCCESS: diskgroup ASMIND was dismounted

Fri Jan 13 11:26:03 2012

Errors in file /oracle/app/oracle/admin/orcl/bdump/orcl_mrp0_528412.trc:

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

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

ORA-15041: diskgroup space exhausted

Fri Jan 13 11:26:03 2012

MRP0: Background Media Recovery process shutdown (orcl)

Fri Jan 13 11:44:30 2012

CHANGES

CAUSE

ORA-15041: diskgroup space exhausted

SOLUTION

when Data Guard setup is managed via sqlplus

Ensure Disk / file system space issue is addressed and then follow this on the standby

sql>alter system set standby_file_management='manual' scope=both sid='*';

sql>alter database create datafile
'/oracle/app/oracle/product/10.2.0.1/db_1/dbs/UNNAMED00038' as new;

Note:- It assumes that database files are using Oracle Managed File (OMF),
else keyword "new" has to be replaced by actual file name

sql>alter system set standby_file_management='auto' scope=both sid='*';

sql>alter database recover managed standby database disconnect from session;

when Data Guard setup is managed via Data Guard Broker or OEM

Ensure Disk / file system space issue is addressed and then follow this on the standby

dgmgrl /
edit database 'standby db unique name here' set property StandbyFileManagement='MANUAL';
exit

sql>alter database create datafile
'/oracle/app/oracle/product/10.2.0.1/db_1/dbs/UNNAMED00038' as new;

Note:- It assumes that database files are using Oracle Managed File (OMF),
else keyword "new"has to be replaced by actual file name

dgmgrl /
edit database 'standby db unique name here' set property StandbyFileManagement='AUTO';
edit database 'standby db unique name here' set state='ONLINE';
exit

Note:- 11gR1 onwards, use "APPLY-ON" instead of "ONLINE"

ITPub博客

MRP0:backgroud Media recovery terminated with error 1274及ORA-15041

APPLIES TO:

SYMPTOMS

CHANGES

CAUSE

SOLUTION

最新文章