rman连接辅助数据库ORA-04031的解决方法

在给oracle 10g搭建dg使用rman复制数据库时，在连接辅助实例时出错，错误信息如下所示:

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/system@aux_test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 10:50:22 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-00600: internal error code, arguments: [15435], [SYS], [X$STANDARD], [], [], [], [], []
ORA-04031: unable to allocate 1040 bytes of shared memory ("shared pool","X$STANDARD","PL/SQL DIANA","PAR.C:parapt:Page")
RMAN-04015: error setting target database character set to ZHS16GBK

从上面的错误信息可以看到ORA-04031是因为不能给共享内存分配1040 bytes大小的内存。如是直接用rman连接辅助实例也出现错误，错误信息如下所示:

[oracle@jingyong1 ~]$ rman target sys/system@aux_test

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 10:51:28 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-00600: internal error code, arguments: [15435], [SYS], [X$STANDARD], [], [], [], [], []
ORA-04031: unable to allocate 1040 bytes of shared memory ("shared pool","X$STANDARD","PL/SQL DIANA","PAR.C:parapt:Page")
RMAN-04015: error setting target database character set to ZHS16GBK

在MOS上找到了ORA-4031 During Startup Nomount using RMAN without parameter file [ID 1176443.1]内容如下:

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 08-May-2013***
Symptoms
RMAN startup nomount failed with ORA-4031

Customer was testing RMAN backup/restore in Exadata.
Customer firstly backup the database to tape and then remove all the datafiles, spfile, controlfiles for testing.
Then during the recover, customer connected RMAN with nocatalog and try to "startup nomount", then ORA-4031 occured.



==================== Log ========================
oracle@hkfop011db01:/home/oracle
$ export ORACLE_SID=TEST
oracle@test011db01:/home/oracle
$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 8 20:45:10 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/oracle/product/11.2.0/db_1/dbs/initTEST.ora‘

starting Oracle instance without parameter file for retrieval of spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/08/2010 20:45:19
RMAN-04014: startup failed: ORA-04031: unable to allocate 111264 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KEWS sesstat values")
Cause
RMAN has failed to start a dummy instance without pfile.
Default values used for the dummy instance are not enough to start the instance up.

This is reported in Bug 9680987 - RMAN CANNOT START DATABASE WITHOUT PARAMETER FILE

Solution
There are two possible solutions:
1- Create temporary init.ora file (/oracle/product/11.2.0/db_1/dbs/initTEST.ora) with the following parameters:

    db_name=
    large_pool_size=100m
    shared_pool_size=250m
    db_cache_size=10m

2- Set environment variable  ORA_RMAN_SGA_TARGET before executing rman. For example:

    $ export ORA_RMAN_SGA_TARGET=350

这篇文章给出了两种解决方法，一种是增加内存参数，一种是设置环境变量，适用版本是11.2.0.1及其以后版本，我这里是oracle 10.2.0.5,因为第二种方法简单，所以尝试使用设置环境变量的方法:

[oracle@jingyong1 ~]$ export ORA_RMAN_SGA_TARGET=350
[oracle@jingyong1 ~]$ rman target sys/system@aux_test

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 10:51:46 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select ks.inst_id,ksuxsins,k...","sql area","ub1[]: qkexrXformVal")

我这里的版本是10.2.0.5，使用第二种方法不行。只能采取第一种增加内存参数的方法。

[oracle@jingyong1 dbs]$ vi inittest.ora

db_name=test
db_unique_name=_test
control_files= /u01/app/oracle/auxiliary/control01.ctl
db_file_name_convert=(‘ /u01/app/oracle/oradata/test/‘,‘ /u01/app/oracle/auxiliary‘)
log_file_name_convert=(‘ /u01/app/oracle/oradata/test/‘,‘ /u01/app/oracle/auxiliary‘)
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes

[oracle@jingyong1 dbs]$ export ORACLE_SID=test
[oracle@jingyong1 dbs]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 11:00:47 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: test (not mounted)

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/system@aux_test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 11:01:00 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: TEST (not mounted)

通过向参数文件中增加内存参数解决了这个故障。