重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: duplicate搭建备库时触发诡异的ORA-01031报错 [打印本页]

作者: mahan 时间: 2025-9-7 18:06
标题: duplicate搭建备库时触发诡异的ORA-01031报错
在使用 RMAN duplicate命令搭建 Oracle 备库过程中，触发ORA-01031: insufficient privileges报错的原因及解决过程。问题表面上看是权限不足，实则与静态监听配置细节有关。通过完整的排查与验证，最终找到问题根源，解决了这一诡异报错。适合 DBA 学习和参考，避免类似踩坑。

1，故障现象
执行的命令及报错的现象如下：

ounter(lineounter(lineounter(lineounter(lineounter(line

RMAN>  duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 21-MAY-25

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=33 instance=htzadg1 device type=DISK

报错的前后的日志：

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 21-MAY-25

sql statement: alter system set  control_files = ''+DATA/htzadg/controlfile/current.311.1201734559'', ''+DATA/htzadg/controlfile/current.312.1201734559'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/21/2025 23:09:34

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

可以看到这里提示关闭数据库实例，后续提示ORA-01031的错误，我晕，还果真有这个错误的信息。

2，故障分析
2.1 探寻什么操作触发ORA-01031错误
遇到报错时，首先想到的就是到时是什么操作触发的这个报错，但是通过上面日志暂时看不出来是什么操作触发的报错，此时开DEBUG，感觉麻烦，于是从原来的duplicate正常的日志中拿一份记录来看看便可知当时的操作，正常操作如下：

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

ql statement: alter system set  control_files = ''+DATA/htzadg/controlfile/current.311.1201734559'', ''+DATA/htzadg/controlfile/current.312.1201734559'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1653518336 bytes

Fixed Size                   2227032 bytes

Variable Size             1040188584 bytes

Database Buffers          603979776 bytes

Redo Buffers                7122944 bytes

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

通过上面操作即可快速得知在启动数据库时触发了ORA-01031的错误。

2.2 手动模拟操作
既然重启数据库报错，于是便想到手动重启一下数据库，看看是否会触发对应的报错，操作如下：

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size                2227032 bytes

Variable Size          1040188584 bytes

Database Buffers       603979776 bytes

Redo Buffers             7122944 bytes

Database mounted.

跟预期一样，手动重启数据库实例不会报错，否则在duplicate之前启动数据库到nomount阶段就应该报错。那么现在基本上就可以定位，数据库通过oracle用户启动完全没有完全，此时问题的方式大概清晰了，通过监听远程启动数据库实例报错。

2.3 分析监听配置
2.3.1 查看tnsnames.ora配置
ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

htzADG =

  (DESCRIPTION =

(ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))

)

(CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = htzadg)

      (UR=A)

)

  )

这里看到UR=A配置时，我就感觉兄弟是真很久没有研究过ADG搭建每一步到底应该怎么配置了，于是继续往下面看

2.3.2 监听的配置
ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

[grid@node3 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 21-MAY-2025 23:27:52

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node3-vip)(PORT=1521)))

STATUS of the LISTENER

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

Alias                   LISTENER

Version                TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date             21-MAY-2025 19:12:31

Uptime                   0 days 4 hr. 15 min. 20 sec

Trace Level             off

Security                ON: Local OS Authentication

SNMP                   OFF

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File       /u01/app/grid/diag/tnslsnr/node3/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.41.111)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.41.168)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "htzadg" has 2 instance(s).

  Instance "htzadg1", status UNKNOWN, has 1 handler(s) for this service...

  Instance "htzadg1", status READY, has 1 handler(s) for this service...

The command completed successfully

这里看到监听还配置了静态监听，从监听状态来看，完全看不出有问题。

于是继续查看监听配置文件：

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

[grid@node3 admin]$ cat  listener.ora

# listener.ora Network Configuration File: u01/app/11.2.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

SID_LIST_LISTENER =

  (SID_LIST =

(SID_DESC =

   (GLOBAL_DBNAME = htzadg)

   (ORACLE_HOME = u01/app/11.2.0/grid)

   (SID_NAME = htzadg1)

)

  )

LISTENER =

  (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))

  )

我晕，尴尬了ORACLE_HOME配置为Grid用户的ORACLE_HOME，而非Oracle用户的ORACLE_HOME，那大概问题也就定位到了，原来真没有想到是由于这个原因导致的ORA-01031的错误。其实为什么自己原来没有遇到过呢，其实还是因为原来在实施类似这些操作时，我都是通过先梳理标准，在跟过脚本的方式来自动配置，这样可以防止类似的问题的产生，但是感觉太过于标准化后，也会让我们错失很多故障分析的机会和乐趣了。

3，解决方案
将ORACLE_HOME的值更改为Oracle用户的ORACLE_HOME即可，记住需要重启监听才会生效。

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line

[grid@node3 admin]$ vi listener.ora

# listener.ora Network Configuration File: u01/app/11.2.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

SID_LIST_LISTENER =

  (SID_LIST =

(SID_DESC =

   (GLOBAL_DBNAME = htzadg)

   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

   (SID_NAME = htzadg1)

)

  )

LISTENER =

  (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))

  )

4，建议
上面是一个典型的在ADG环境，由于部分配置错误导致ADG部分功能错误。如果是通过手动备份与还原的方式来搭建ADG，那么这个错误在正常同步过程中就无法体现出来，很有可能就会带病一直运行，也会让大家错认为当前系统无问题和隐患，那么在后续采用dgmgrl来做ADG主从切换时，此故障就会完全暴露出来，同时也会导致ADG环境主从切换失败。

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)