ANBOB™

现在我们只说spfile丢失的恢复方法,spfile 是一个二进制文件不可以手工修改，实例启动时查询顺序为spfile>pfile>init，

1，如果有pfile 当然可以用pfile 启动,然后create spfile from pfile;

2,如果没有pfile 也可以去alter日志copy一段内容，新建个pfile用上面的方法建spfile；

3，如果以前用rman 做过备份且备了spfile，就可以用rman 来恢复一个

下面实验第3条用rman

rman 分catalog和非catalog备份，非catalog存放在控制文件中，catalog是存放在catalog数据库中，catalog database可以和target database 在一台机器上不过不建议。

CONFIGURE CONTROLFILE AUTOBACKUP ON; 默认为off

实验步骤是用rman nocatalog先做个数据库全备，然后在os删掉spfile，用rman 恢复spfile

–绿字为注释

[oracle@orazhang dbs]$ rman nocatalog   nocatalog可带可不带为rman默认

Recovery Manager: Release 10.2.0.1.0 – Production on 星期二 2月 22 11:56:37 2011  红色部分为rman版本信息

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

RMAN> list backup    无显示为当前没有备份集
2> ;

RMAN> backup format ‘/rmanbackup/%d_%s_%p_%T.bak’ database; –全备 路径可带可不带

Starting backup at 21-2月 -11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_6cgckxds_.dbf
input datafile fno=00007 name=+ASMDATA/asmts01.dbf
input datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_6cgckx95_.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_6cgckx9p_.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_6cgckxc7_.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs1_6okmoslq_.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/datafile/tt.dbf
input datafile fno=00009 name=/u01/app/oracle/oradata/ORCL/datafile/tt1.dbf
channel ORA_DISK_1: starting piece 1 at 21-2月 -11
channel ORA_DISK_1: finished piece 1 at 21-2月 -11
piece handle=/rmanbackup/ORCL_189_1_20110221.bak tag=TAG20110221T195210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs4_6cgcmnfg_.dbf
channel ORA_DISK_1: starting piece 1 at 21-2月 -11
channel ORA_DISK_1: finished piece 1 at 21-2月 -11
piece handle=/rmanbackup/ORCL_190_1_20110221.bak tag=TAG20110221T195210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-2月 -11

Starting Control File and SPFILE Autobackup at 21-2月 -11
piece handle=/home/oracle/c-1246063822-20110221-01.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 21-2月 -11

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
133     Full    3.64G      DISK        00:05:16     21-2月 -11
BP Key: 133   Status: AVAILABLE  Compressed: NO  Tag: TAG20110221T195210
Piece Name: /rmanbackup/ORCL_189_1_20110221.bak
List of Datafiles in backup set 133
File LV Type Ckp SCN    Ckp Time   Name
—- — —- ———- ———- —-
1       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_6cgckx95_.dbf
2       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf
3       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_6cgckx9p_.dbf
4       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_6cgckxds_.dbf
5       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_6cgckxc7_.dbf
6       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/tt.dbf
7       Full 43500349   21-2月 -11 +ASMDATA/asmts01.dbf
9       Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/tt1.dbf
10      Full 43500349   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs1_6okmoslq_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
134     Full    76.00K     DISK        00:00:02     21-2月 -11
BP Key: 134   Status: AVAILABLE  Compressed: NO  Tag: TAG20110221T195210
Piece Name: /rmanbackup/ORCL_190_1_20110221.bak
List of Datafiles in backup set 134
File LV Type Ckp SCN    Ckp Time   Name
—- — —- ———- ———- —-
8       Full 43500496   21-2月 -11 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs4_6cgcmnfg_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
135     Full    6.83M      DISK        00:00:01     21-2月 -11
BP Key: 135   Status: AVAILABLE  Compressed: NO  Tag: TAG20110221T195739
Piece Name: /home/oracle/c-1246063822-20110221-01.ctl –这路径是我在rman config中配置的
Control File Included: Ckp SCN: 43500507     Ckp time: 21-2月 -11
SPFILE Included: Modification time: 21-2月 -11

[oracle@orazhang dbs]$ mv  spfileorcl.ora spfileorcl.ora_bak
[oracle@orazhang dbs]$ ls
ab_+ASM.dat     init+ASM.ora  lkORCL       run_grant.sql
alert_orcl.log  initdw.ora    orapw+ASM    snapcf_orcl.f
hc_+ASM.dat     init.ora      orapworcl    spfile+ASM.ora
hc_orcl.dat     lk+ASM        reorg21.sql  spfileorcl.ora_bak
[oracle@orazhang dbs]$ ora

SQL*Plus: Release 10.2.0.1.0 – Production on 星期二 2月 22 10:19:53 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> shudown immediate
SP2-0734: unknown command beginning “shudown im…” – rest of line ignored.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora’
SQL>

–-不要在sqlplus里startup ,去rman 里 startup 到nomount

oracle@orazhang dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on 星期二 2月 22 10:24:22 2011

Copyright (c) 1982, 2005, Oracle.  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/10.2.0/db_1/dbs/initorcl.ora’

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

Total System Global Area     159383552 bytes

Fixed Size                     1218244 bytes
Variable Size                 58722620 bytes
Database Buffers              92274688 bytes
Redo Buffers                   7168000 bytes

RMAN> restore spfile from autobackup;

Starting restore at 22-2月 -11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/22/2011 10:43:06
RMAN-06495: must explicitly specify DBID with SET DBID command

—提示如果要用 autobackup,就要指定dbid, 要先在 rman 中set dbid=xxxx,dbid在rman备份时会显示，也可以在数据库状态打开时，用

SQL> select dbid from  v$database;

DBID
———-
1246063822

如果知道dbid就可以

RMAN> startup nomount

Oracle instance started

Total System Global Area     562036736 bytes

Fixed Size                     1220556 bytes
Variable Size                176160820 bytes
Database Buffers             377487360 bytes
Redo Buffers                   7168000 bytes

RMAN> set dbid=1246063822

executing command: SET DBID

RMAN> restore spfile from autobackup;

现在是假如我dbid不知道怎么办？直接指定备份的控制文件

RMAN> restore spfile from ‘/home/oracle/c-1246063822-20110221-01.ctl’;

Starting restore at 22-2月 -11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=39 devtype=DISK

channel ORA_DISK_1: autobackup found: /home/oracle/c-1246063822-20110221-01.ctl
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 22-2月 -11

RMAN> shutdown immediate

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area     562036736 bytes

Fixed Size                     1220556 bytes
Variable Size                176160820 bytes
Database Buffers             377487360 bytes
Redo Buffers                   7168000 bytes

好，spfile恢复成功了

打赏

微信扫一扫，打赏作者吧～