Chinaunix首页 | 论坛 | 博客
  • 博客访问: 654547
  • 博文数量: 244
  • 博客积分: 9445
  • 博客等级: 中将
  • 技术积分: 2572
  • 用 户 组: 普通用户
  • 注册时间: 2006-04-11 16:39
个人简介

简单点,再简单点

文章分类

全部博文(244)

文章存档

2020年(2)

2019年(1)

2018年(2)

2014年(2)

2013年(4)

2012年(13)

2011年(24)

2010年(12)

2009年(34)

2008年(61)

2007年(51)

2006年(38)

我的朋友

分类: Oracle

2006-09-16 12:33:28

五. 是否使用了spfile
判断是否使用了SPFILE,可以使用以下方法:
1.查询v$parameter动态视图,如果以下查询返回空值,那么你在使用pfile.
SQL> SELECT name,value FROM v$parameter WHERE name='spfile';
2.或者你可以使用SHOW命令来显示参数设置,如果以下结果value列返回空值,那么说明你在使用pfile:
SQL> SHOW PARAMETER spfile
3.查询v$spparameter视图
如果以下查询返回0值,表示你在使用pfile,否则表明你使用的是spfile:
SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
或者使用以下查询,如果true值返回非0值,那么说明我们使用的是spfile.
SQL> select isspecified, count(*) from v$spparameter group
2 by isspecified;
ISSPECIFIED COUNT(*)
------------ ----------
FALSE 226
TRUE 33
更为直接的:
SQL> select decode(count(*), 1, 'spfile', 'pfile' ) USED
2 from v$spparameter
3 where rownum=1 and isspecified='TRUE'
4 /
USED
------------
spfile

六. SPFILE的备份与恢复
下面我们来看一下这个过程:
a. 设置控制文件自动备份:
[oracle@jumper oracle]$ rman target /
 Recovery Manager: Release 9.2.0.3.0 - Production
 Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
 connected to target database: HSJF (DBID=1052178311)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
 using target database controlfile instead of recovery catalog
 old RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP OFF;
 new RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 new RMAN configuration parameters are successfully stored
RMAN> exit
这个设置可以在数据库中通过如下方式查询得到:
[oracle@jumper bdump]$ sqlplus "/ as sysdba"
SQL> select * from v$rman_configuration;
CONF# NAME VALUE
---------- ------------------------- ----------
1 CONTROLFILE AUTOBACKUP ON
b. 记录数据库变化
SQL> create tablespace eygle datafile '/data1/oracle/oradata/eygle01.dbf' size 5M;
如果新创建一个表空间,这时候检查alert.log文件,你可以在其中发现这样的备份信息:
Sat Jan 17 00:55:57 2004Starting control autobackupControl autobackup written to DISK devicehandle '/opt/oracle/product/9.2.0/dbs/c-1052178311-20040117-00'Completed: create tablespace eygledatafile '/data1/oracle/oradata/eygle01.dbf’
如果使用rman进行备份,在提示中你可以看到如下信息:
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> run
2> {
3> allocate channel ch1 type disk format='e:\oracle\orabak\penny%t.arc';
4> backup archivelog all delete all input;
5> release channel ch1;
6> }
allocated channel: ch1
channel ch1: sid=13 devtype=DISK
Starting backup at 02-DEC-03
current log archived
channel ch1: starting archive log backupset
channel ch1: specifying archive log(s) in backup set
input archive log thread=1 sequence=63 recid=168 stamp=511712617
input archive log thread=1 sequence=64 recid=169 stamp=511712620
input archive log thread=1 sequence=65 recid=170 stamp=511712626
input archive log thread=1 sequence=66 recid=171 stamp=511712690
channel ch1: starting piece 1 at 02-DEC-03
channel ch1: finished piece 1 at 02-DEC-03
piece handle=E:\ORACLE\ORABAK\PENNY511712693.ARC comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:03
channel ch1: deleting archive log(s)
archive log filename=E:\ORACLE\ORADATA\PENNY\ARCHIVE\1_63.DBF recid=168 stamp=511712617
archive log filename=E:\ORACLE\ORADATA\PENNY\ARCHIVE\1_64.DBF recid=169 stamp=511712620
archive log filename=E:\ORACLE\ORADATA\PENNY\ARCHIVE\1_65.DBF recid=170 stamp=511712626
archive log filename=E:\ORACLE\ORADATA\PENNY\ARCHIVE\1_66.DBF recid=171 stamp=511712690
Finished backup at 02-DEC-03
Starting Control File and SPFILE Autobackup at 02-DEC-03
piece handle=E:\ORACLE\ORA92\DATABASE\C-3627775766-20031202-01 comment=NONE
Finished Control File and SPFILE Autobackup at 02-DEC-03
released channel: ch1
我们简单看一下自动备份的控制文件及spfile文件的格式及命名规则:
c-IIIIIIIIII-YYYYMMDD-QQ
c ------------------------控制文件
IIIIIIIIII---------DBID
YYYYMMDD------------时间戳
QQ----------------------序号00-FF,16进制表示
c. 使用自动备份恢复spfile文件
[oracle@jumper bdump]$ rman target /
Recovery Manager: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: HSJF (DBID=1052178311)
RMAN> restore spfile to '/tmp/spfileeygle.ora' from autobackup;
Starting restore at 17-JAN-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20040117
channel ORA_DISK_1: autobackup found: c-1052178311-20040117-01
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 17-JAN-04
RMAN> exit
Recovery Manager complete.
[oracle@jumper bdump]$ ls -l /tmp/spfileeygle.ora
-rw-r----- 1 oracle dba 3584 1月 17 09:34 /tmp/spfileeygle.ora
你同样可以通过这种方法恢复控制文件,示例如下:
[oracle@jumper bdump]$ rman target /
RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;
Starting restore at 17-JAN-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20040117
channel ORA_DISK_1: autobackup found: c-1052178311-20040117-02
channel ORA_DISK_1: controlfile restore from autobackup complete
Finished restore at 17-JAN-04
RMAN> exit

Recovery Manager complete.
[oracle@jumper bdump]$ ls -l /tmp/control*
-rw-r----- 1 oracle dba 1892352 1月 17 09:44 /tmp/control01.ctl
Oracle9i自动备份控制文件的功能给我们带来了极大的收益,通过自动备份,在数据库出现紧急状况的时候,你可能可以从这个自动备份中获得更
为有效及时的控制文件.
缺省的,这个自动备份功能是关闭的,你可以用我们上面提到的方法打开该功能.
阅读(1944) | 评论(0) | 转发(0) |
0

上一篇:SPFILE使用(2)转

下一篇:SPFILE的使用(4)转

给主人留下些什么吧!~~