暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle DataGuard异常处理【ORA-01119 ORA-17502 ORA-15041】→【ORA-01111 ORA-01110 ORA-01157】

Oracle DataGuard异常处理【ORA-01119 ORA-17502 ORA-15041】→【ORA-01111 ORA-01110 ORA-01157】

原创 Gemin_DBA 2024-06-19
272

收到用户反馈DG上查不到最近一天的数据，怀疑同步有问题

一、检查备库同步情况 两个节点都未看到MRP0进程


SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS 	   STATUS		       THREAD#	SEQUENCE#
------------------ ------------------------ ---------- ----------
ARCH		   CLOSING			     6	   174201
DGRD		   ALLOCATED			 0		0
DGRD		   ALLOCATED			 0		0
ARCH		   CLOSING			     5	   284619
ARCH		   CLOSING			     5	   280446
ARCH		   CLOSING			     6	   152822
RFS			   IDLE 			     0		0
RFS			   IDLE 			     0		0
RFS			   IDLE 			     0		0
RFS			   IDLE 			     6		0

二、查看alert日志发现如下报错 备库ASM 空间满

2023-02-28T16:47:29.089825+08:00
Errors in file /opt/oracle/diag/rdbms/node/NODE1/trace/NODE1_pr00_169293.trc:
ORA-01119: error in creating database file '+DATADG'
ORA-17502: ksfdcre:4 Failed to create file +DATADG
ORA-15041: diskgroup "DATADG" space exhausted
File #1071 added to control file as 'UNNAMED01071'.
Originally created as:
'+DATADG/NODE/DATAFILE/flxeaptbs2.3305.1129999631'
Recovery was unable to create the file as a new OMF file.
PR00 (PID:169293): MRP0: Background Media Recovery terminated with error 1274
2023-02-28T16:47:29.121223+08:00
Errors in file /opt/oracle/diag/rdbms/node/NODE1/trace/NODE1_pr00_169293.trc:
ORA-01274: cannot add data file that was originally created as '+DATADG/NODE/DATAFILE/flxeaptbs2.3305.1129999631'
2023-02-28T16:47:29.123758+08:00
.... (PID:9453): Managed Standby Recovery not using Real Time Apply

三、处理空间问题

查看发现过期备份未删除 已经累计29T了…

grid@NODE1:/home/grid>$ ./asmdu.sh +DATADG/NODE

		    +DATADG/NODE subdirectories size

                   Subdir         Used MB       Mirror MB
                   ------         -------       ---------
               BACKUPSET/        30461584        60923168
             CONTROLFILE/             520            1560
                DATAFILE/         1549554         3099108
               ONLINELOG/           86856          173712
           PARAMETERFILE/               4               8
                PASSWORD/               0               0
                TEMPFILE/          646354         1292708

                   ------         -------       ---------
                    Total        32744872        6549026

利用原本的删除脚本删除报错

sh clean.sh
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 03/01/2023 14:02:41
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

这也找到为什么会把空间撑爆的原因了…这个DB我们接手不久,都是前任DBA留的坑啊…

修改删除策略

run{
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
allocate channel c3 device type disk ;
allocate channel c4 device type disk ;
allocate channel c5 device type disk ;
allocate channel c6 device type disk ;
allocate channel c7 device type disk ;
allocate channel c8 device type disk ;
allocate channel c9 device type disk ;
allocate channel c10 device type disk ;
allocate channel for maintenance type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete device type disk;
delete noprompt archivelog until time 'sysdate-7' device type disk;
delete noprompt expired backup;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
}

再次执行脚本
sh.clean.sh
''''''
backup piece handle=+REDODG/NODE/AUTOBACKUP/2023_02_20/s_1129314781.5314.1129314783 RECID=47584 STAMP=1129314782
Deleted 4442 objects
''''''

删除成功

空间清理完成


grid@NODE1:/home/grid>$ ./asmdu.sh +DATADG/NODE

		    +DATADG/NODE subdirectories size

                   Subdir         Used MB       Mirror MB
                   ------         -------       ---------
               BACKUPSET/          701954         1403908
             CONTROLFILE/             520            1560
                DATAFILE/         1549554         3099108
               ONLINELOG/           86856          173712
           PARAMETERFILE/               4               8
                PASSWORD/               0               0
                TEMPFILE/          646354         1292708

                   ------         -------       ---------
                    Total         2985242         5971004

删除后仅剩不到700M…

四、尝试重新开启MRP0进程

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.
执行成功 再次检查
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS 	   STATUS		       THREAD#	SEQUENCE#
------------------ ------------------------ ---------- ----------
ARCH		   CLOSING			     6	   174201
DGRD		   ALLOCATED			 0		0
DGRD		   ALLOCATED			 0		0
ARCH		   CLOSING			     5	   284619
ARCH		   CLOSING			     5	   280446
ARCH		   CLOSING			     6	   152822
RFS			   IDLE 			     0		0
RFS			   IDLE 			     0		0
RFS			   IDLE 			     0		0
RFS			   IDLE 			     6		0
发现MRP进程仍然没有启动

再次查看alert日志发现如下报错

Errors in file /opt/oracle/diag/rdbms/node/NODE1/trace/NODE1_mrp0_138218.trc:
ORA-01111: name for data file 1071 is unknown - rename to correct file
ORA-01110: data file 1071: '/opt/oracle/products/19.3.0/dbs/UNNAMED01071'
ORA-01157: cannot identify/lock data file 1071 - see DBWR trace file
ORA-01111: name for data file 1071 is unknown - rename to correct file
ORA-01110: data file 1071: '/opt/oracle/products/19.3.0/dbs/UNNAMED01071'
2023-03-01T14:29:23.252555+08:00
Background Media Recovery process shutdown (NODE1)

备库ASM空间不足时会把数据文件传入到dbs目录下

五、查MOS得到解决方案

这个问题MOS上有标准接近步骤，我们正常按照步骤处理就行

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database (Doc ID 1416554.1)
#### 执行方案 ```sql SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SID='*';

Database altered.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

db_create_file_dest string +DATADG
– 当前环境已经配置了OMF
SQL> alter database create datafile ‘/opt/oracle/products/19.3.0/dbs/UNNAMED01071’ as ‘+DATADG’ size 31G;

Database altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID=’*’;

Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.


#### 再次检查MRP进程
```sql
SQL> select process,status,thread#,sequence# from v$managed_standby;

PROCESS 	   STATUS		       THREAD#	SEQUENCE#
------------------ ------------------------ ---------- ----------
ARCH		   CLOSING			     6	   174201
DGRD		   ALLOCATED			 0		0
DGRD		   ALLOCATED			 0		0
ARCH		   CLOSING			     5	   284619
ARCH		   CLOSING			     5	   280446
ARCH		   CLOSING			     6	   152822
RFS			   IDLE 			     0		0
RFS			   IDLE 			     0		0
RFS			   IDLE 			     0		0
RFS			   IDLE 			     6		0
MRP0		   APPLYING_LOG 		 6	   174558

已回复正常同步，日志已应用

墨力计划数据库oracle数据库
最后修改时间：2025-02-12 13:59:22
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论