Oracle SPFILE的恢复方式有哪几种？
SPFILE的恢复方式有哪几种？
答案：在数据库还没有关闭的情况下，可以使用如下的SQL从内存中恢复数据库的参数：
create spfile from memory;
create spfile='/home/oracle/spfileOCPLHR1.ora' from memory;
create pfile from memory;
create spfile from pfile;
除非必要，应该将以下划线开头的隐含参数删掉：
grep -v '^(_|#).' initlhrdb.ora > initlhrdb.ora2
如果数据库已经关闭，但是存在SPFILE的RMAN备份，那么可以使用如下的方式进行恢复：
l 如果知道数据库的DBID，那么可以按照如下步骤进行恢复：
startup nomount
set dbid 9815108;
restore spfile from autobackup;--已连接到了catalog恢复目录数据库
shutdown immediate
set dbid 9815108;
startup;
l 如果不知道数据库的DBID，那么可以按照如下步骤进行恢复：
① startup nomount;
② restore spfile from '备份文件';
③ shutdown immediate;
④ startup;
l 如果没有关于SPFILE的RMAN备份，那么可以从告警日志中获取数据库启动所需要的参数。
4.5.2 spfile 丢失
4.5.2.1 数据库没有挂掉
create spfile from memory;
create spfile='/home/oracle/spfileOCPLHR1.ora' from memory;
create pfile from memory;
create spfile from pfile;
除非必要，应该将以下划线开头的隐含参数删掉：
grep -v '^(_|#).' initlhrdb.ora > initlhrdb.ora2
4.5.2.2 数据库已经挂掉
一、 从rman的自动备份中找回
如果知道数据库的DBID，那么：
startup nomount
set dbid 9815108;
restore spfile from autobackup;--已连接到了catalog恢复目录数据库
shutdown immediate
set dbid 9815108;
startup;
如果不知道dbid可以这样做：
① startup nomount;
② restore spfile from '备份文件';
③ shutdown immediate;
④ startup;
restore spfile from autobackup; 可以成功的前提是连接到了catalog恢复目录数据库。
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:29:42 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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/11.2.0/dbhome_1/dbs/initOCPLHR1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 130025192 bytes
Database Buffers 20971520 bytes
Redo Buffers 5439488 bytes
RMAN> restore spfile from autobackup;
Starting restore at 2018-03-25 20:30:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/25/2018 20:30:08
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> SET DBID 2909198110
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 2018-03-25 20:30:24
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180325
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180324
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180323
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180322
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180321
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180320
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180319
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/25/2018 20:30:26
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
RMAN> exit
Recovery Manager complete.
[oracle@OCPLHR dbs]$
[oracle@OCPLHR dbs]$ rman target / catalog rc_admin/rc_admin@ocplhr2
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:31:23 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-01017: invalid username/password; logon denied
[oracle@OCPLHR dbs]$
[oracle@OCPLHR dbs]$
[oracle@OCPLHR dbs]$ rman target / catalog rc_admin/lhr@ocplhr2
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:31:32 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
connected to recovery catalog database
RMAN>
RMAN> restore spfile from autobackup;
Starting restore at 2018-03-25 20:31:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180325
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180324
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180323
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180322
channel ORA_DISK_1: AUTOBACKUP found: /bak/cf_c-2909198110-20180322-0e.ctl
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /bak/cf_c-2909198110-20180322-0e.ctl
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2018-03-25 20:31:41
RMAN> exit
Recovery Manager complete.
[oracle@OCPLHR dbs]$ ll
total 9764
-rw-rw---- 1 oracle oinstall 1544 Mar 25 20:29 hc_OCPLHR1.dat
-rw-rw---- 1 oracle oinstall 1544 Mar 22 20:02 hc_OCPLHR2.dat
-rw-r----- 1 oracle oinstall 24 Mar 25 20:26 lkDUMMY
-rw-r----- 1 oracle oinstall 24 Jan 17 20:08 lkOCPLHR1
-rw-r----- 1 oracle oinstall 24 Jan 17 20:16 lkOCPLHR2
-rw-r----- 1 oracle oinstall 1536 Mar 25 20:09 orapwOCPLHR1
-rw-r----- 1 oracle oinstall 1536 Jan 17 20:25 orapwOCPLHR2
-rw-r----- 1 oracle oinstall 9945088 Mar 22 21:24 snapcf_OCPLHR1.f
-rw-r----- 1 oracle oinstall 3584 Mar 25 20:31 spfileOCPLHR1.ora
-rw-r----- 1 oracle oinstall 2560 Mar 25 19:54 spfileOCPLHR2.ora
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:32:26 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
RMAN> shutdown abort
using target database control file instead of recovery catalog
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
[oracle@OCPLHR dbs]$ echo $ORACLE_SID
OCPLHR1
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:32:47 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup
Oracle instance started
database mounted
database opened
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 297797960 bytes
Database Buffers 213909504 bytes
Redo Buffers 7999488 bytes
RMAN>
如果不知道dbid可以这样做：
⑤ startup nomount;
⑥ restore spfile from '备份文件';
⑦ shutdown immediate;
⑧ startup;
（一）startup nomount
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:19:00 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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/11.2.0/dbhome_1/dbs/initOCPLHR1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2226456 bytes
Variable Size 130025192 bytes
Database Buffers 20971520 bytes
Redo Buffers 5439488 bytes
RMAN>
[oracle@OCPLHR oracle]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:22:43 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
[oracle@OCPLHR ~]$ ps -ef|grep pmon
oracle 13806 1 0 18:07 ? 00:00:00 ora_pmon_OCPLHR2
oracle 15930 1 0 20:19 ? 00:00:00 ora_pmon_OCPLHR1
oracle 15969 15657 0 20:19 pts/0 00:00:00 grep pmon
（二）restore spfile from '备份文件'
[oracle@OCPLHR oracle]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:22:43 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
RMAN>
RMAN>
RMAN> restore spfile from '/bak/OCPLHR1/spfile_OCPLHR1x_1ssuevao_1_1_20180322.bak';
Starting restore at 2018-03-25 20:23:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /bak/OCPLHR1/spfile_OCPLHR1x_1ssuevao_1_1_20180322.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2018-03-25 20:23:57
RMAN> exit
Recovery Manager complete.
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:27:03 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMY (not mounted)
RMAN> shutdown abort
using target database control file instead of recovery catalog
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
[oracle@OCPLHR dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 25 20:27:12 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2229944 bytes
Variable Size 293603656 bytes
Database Buffers 218103808 bytes
Redo Buffers 7999488 bytes
RMAN>
二、 手动创建
手动创建，创建的时候参数的值可以从告警日志中找见。
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: OCPLHR
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine: x86_64
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCPLHR1.ora
System parameters with non-default values:
processes = 150
sga_max_size = 500M
control_files = "/u01/app/oracle/oradata/OCPLHR1/control01.ctl"
control_files = "/u01/app/oracle/fast_recovery_area/OCPLHR1/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archiveformat = "%t%s_%r.dbf"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4122M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=OCPLHR1XDB)"
audit_file_dest = "/u01/app/oracle/admin/OCPLHR1/adump"
audit_trail = "DB"
db_name = "OCPLHR1"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
