首页 > 数据库 >正文

【RMAN】SPFILE的恢复方式有哪几种?

【RMAN】SPFILE的恢复方式有哪几种?





真题1、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

如果数据库已经关闭,但是存在SPFILERMAN备份,那么可以使用如下的方式进行恢复:

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  如果没有关于SPFILERMAN备份,那么可以从告警日志中获取数据库启动所需要的参数。

& 说明:

有关SPFILE的恢复可以参考我的BLOGhttp://blog.itpub.net/26736162/viewspace-2152507/

 

 





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_archive_format       = "%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"








About Me

.............................................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友646634621,注明添加缘由

● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成

● 最新修改时间:2018-04-01 06:00 ~ 2018-04-31 24:00

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书:http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用网络班:http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

【RMAN】SPFILE的恢复方式有哪几种?【RMAN】SPFILE的恢复方式有哪几种?【RMAN】SPFILE的恢复方式有哪几种?【RMAN】SPFILE的恢复方式有哪几种?

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面试宝典》读者群       小麦苗的微店

.............................................................................................................................................


【RMAN】SPFILE的恢复方式有哪几种?
【RMAN】SPFILE的恢复方式有哪几种? 【RMAN】SPFILE的恢复方式有哪几种?