[20161101]环境变量ORA_RMAN_SGA_TARGET-优快云博客

本文介绍如何通过环境变量ORA_RMAN_SGA_TARGET来调整Oracle RMAN环境中SGA的大小，并演示了在没有spfile的情况下启动数据库到nomount状态的方法。此外，还提供了恢复spfile的具体步骤。

[20161101]环境变量ORA_RMAN_SGA_TARGET.txt

--如果数据库恢复，需要启动数据库到nomount，如果没有spfile文件，在sqlplus是无法启动的。
--在rman环境下，可以启动到nomount，解决鸡与蛋的问题，有一个环境变量ORA_RMAN_SGA_TARGET控制sga大小。

--测试看看：

1.环境：
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--关闭数据库，改名spfile文件，如果存在init文件也改名。

$ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ mv spfilebook.ora spfilebook.ora_20161101

2.启动看看:
--可以设置参数可以改变sga内存大小.
export ORA_RMAN_SGA_TARGET=512

SYS@book> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initbook.ora'

--在sqlplus无法启动到nomount.

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initbook.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     534462464 bytes
Fixed Size                     2254952 bytes
Variable Size                272631704 bytes
Database Buffers             251658240 bytes
Redo Buffers                   7917568 bytes

SYS@book> show sga
Total System Global Area 534462464 bytes
Fixed Size                  2254952 bytes
Variable Size             272631704 bytes
Database Buffers          251658240 bytes
Redo Buffers                7917568 bytes

SYS@book> show parameter sga_target
NAME TYPE VALUE
----------- ------------- ---------
sga_target big integer 512M

--可以发现SGA使用512M。与设置相符。

2.如果不设置呢？

$ export ORA_RMAN_SGA_TARGET=
$ rlrman
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 1 08:10:54 2016
Copyright (c) 1982, 2011, 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 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initbook.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes
Fixed Size                     2260088 bytes
Variable Size                301990792 bytes
Database Buffers             754974720 bytes
Redo Buffers                   9711616 bytes

--可以发现SGA_TARGET使用1024M.我一直想设置有什么用，难道有可能出现太小或者太多无法启动的情况吗？

3. 测试恢复spfile文件。一般spfile这个时候也有问题，必须先恢复pfile，再转成spfile格式。

RMAN> restore spfile to pfile '/tmp/b.oar' from autobackup;
Starting restore at 2016-11-01 08:12:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1345 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/01/2016 08:12:57
RMAN-06495: must explicitly specify DBID with SET DBID command

--//原来这样需要指定dbid，第一次遇到这种情况。

RMAN> set dbid 1337401710
executing command: SET DBID

RMAN> restore spfile to pfile '/tmp/b.oar' from autobackup;
Starting restore at 2016-11-01 08:19:29
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161101
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161031
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161030
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161029
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161028
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161027
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20161026
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/01/2016 08:19:31
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

--奇怪不知道为什么不行。实际上我喜欢直接指定路径，这样就不需要设置dbid了。

$ rlrman
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 1 08:21:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)

RMAN> restore spfile to pfile '/tmp/b.oar' from '/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_01/o1_mf_s_926756227_d1hqr3pw_.bkp';
Starting restore at 2016-11-01 08:21:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1345 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_01/o1_mf_s_926756227_d1hqr3pw_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2016-11-01 08:21:54

$ head /tmp/b.oar
book.__db_cache_size=394264576
book.__java_pool_size=12582912
book.__large_pool_size=37748736
book.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
book.__pga_aggregate_target=209715200
book.__sga_target=637534208
book.__shared_io_pool_size=0
book.__shared_pool_size=213909504
book.__streams_pool_size=0
*._cursor_bind_capture_area_size=2001