-1. SPFILE 所在目录

l Unix: $ORACLE_HOME/dbs;

l NT: $ORACLE_HOME\database

-2. 管理 SPFILE

a. 从PFILE(initsid.ora)中创建SPFILE

需要SYSDBA或者SYSOPER的权限；新创建的SPFILE在下一次启动数据库时生效

SQL>CONNECT SYS/password AS SYSDBA

SQL>CREATE SPFILE[='SPFILE-NAME'] FROM PFILE[='PFILE-NAME']

SQL> create spfile from pfile;

SQL>CREATE SPFILE='c:\ORACLE\ora92\database\spfileORACLE9i.ora' FROM PFILE= 'c:\ORACLE\ora92\database\initORACLE9i.ora';

b. 从SPFILEsid.ORA中创建PFILE文件, sysdba角色

SQL>CREATE PFILE='c:\ORACLE\ora92\database\initORACLE9i.ora' FROM SPFILE='c:\ORACLE\ora92\database\spfileORACLE9i.ora';

-3. 管理 PFILE-11g , sysdba角色

SQL>CREATE PFILE FROM MEMORY ;

-4. SPFILE视图

a. 从V$parameter查询实例使用的SPFILE:

SQL> select name,value from v$parameter where name='spfile';

NAME VALUE

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

spfile /opt/ora10g/product/10.2.0/db_1/dbs/spfiletsid.ora

Oracle 9i 增加了v$parameter2视图；V$parameter2与v$parameter 具有同样的作用；

b. 用show命令得到实例使用的SPFILE:

SQL> show parameter spfile;

NAME TYPE VALUE

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

spfile string /opt/ora10g/product/10.2.0/db_

1/dbs/spfiletsid.ora

-5. SPFILE参数的修改

ALTER SYSTEM SET [=] [comment ''] [deferred]

[SCOPE = {memory | spfile | both} ]

[sid = { '' | '*' } ];

l 是参数的完整名称，而是要设置的具体值，如Audit_trail=TRUE

l comment是注释字串

l deferred表示参数延迟生效

l MEMORY [Default] : 在当前实例内存有效

l SPFILE : 只写到spfile

l BOTH : memory + spfile

a. 修改（实例和SPFILE都生效）

SQL> alter system set db_cache_size=24M scope=both;

b. 修改参数只写到SPFILE中

SQL> ALTER SYSTEM SET control_files =
'$HOME/ORADATA/u01/ctrl01.ctl',
'$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;

c. RAC修改参数只是sales实例有效

SQL> ALTER SYSTEM SET timed_statistics=TRUE SID='sales';

d. RAC修改参数使两个实例都有效

SQL> ALTER SYSTEM SET timed_statistics=FALSE SID='*';

-6. SPFILE参数修改注意

l V$parameter的ISINSTANCE_MODIFIABLE=TRUE => BOTH修改
表示会话级可修改(设置)

l V$parameter的ISINSTANCE_MODIFIABLE=FALSE=>SPFILE修改
10g版本修改后需要重启动的参数

[oracle@Red-server pfile]$ echo $ORACLE_BASE

/opt/ora10g

[oracle@Red-server pfile]$ ls

init.ora.85201217279 默认数据库安装完的备份

[oracle@Red-server pfile]$ pwd

/opt/ora10g/admin/test/pfile

一、导出SPFILE文件

SQL> conn / as sysdba

Connected.

SQL> create pfile='/database/backup.ora' from spfile;

File created.

二、恢复

l SQL> shutdown immediate

l 修改pfile

l SQL> startup pfile='/database/backup.ora'

l SQL> create spfile from pfile='/database/init****.ora';

l SQL> startup

l SQL> show parameter spfile

三、RMAN

[oracle@Red-server /]$ rman target sys/oracle nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 10 17:20:22 2012

connected to target database: TEST (DBID=2091360412)

using target database control file instead of recovery catalog

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN>backup spfile;

Starting backup at 10-SEP-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=153 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 10-SEP-12

channel ORA_DISK_1: finished piece 1 at 10-SEP-12

piece handle=/database/TEST/backupset/2012_09_10/o1_mf_nnsnf_TAG20120910T173208_84vdwrgx_.bkp tag=TAG20120910T173208 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-SEP-12

Starting Control File and SPFILE Autobackup at 10-SEP-12

piece handle=/database/TEST/autobackup/2012_09_10/o1_mf_s_793647129_84vdwt0c_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-SEP-12

RMAN>list backup of spfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

1 Full 80.00K DISK 00:00:00 10-SEP-12

BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20120910T173208

Piece Name: /database/TEST/backupset/2012_09_10/o1_mf_nnsnf_TAG20120910T 173208_84vdwrgx_.bkp

SPFILE Included: Modification time: 10-SEP-12

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

2 Full 6.80M DISK 00:00:01 10-SEP-12

BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120910T173209

Piece Name: /database/TEST/autobackup/2012_09_10/o1_mf_s_793647129_84vdw t0c_.bkp

Control File Included: Ckp SCN: 415793 Ckp time: 10-SEP-12

SPFILE Included: Modification time: 10-SEP-12

RMAN> startup;

RMAN> set dbid=2091360412

RMAN> restore spfile from autobackup;

RMAN> restore spfile from '/database/TEST/backupset/2012_09_10/*_.bkp’;

SQL> shutdown immediate;

ITPub博客

SPFILE