SPFILE

-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

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

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;

SQL> shutdown immediate;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27693611/viewspace-743187/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27693611/viewspace-743187/