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)

透過向引數檔案中增加記憶體引數解決了這個故障。