-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/