oracle重建spfile,oracle spfile 丢失，简单恢复案例！

最新推荐文章于 2023-05-02 21:17:22 发布

进击的大虎

最新推荐文章于 2023-05-02 21:17:22 发布

阅读量1.1k

点赞数

文章标签： oracle重建spfile

如果系统的spfile丢失，在有备份的情况下，可以直接进行恢复的，在生产环境中，rman的设置和备份集的设置可能和实验环境不一样，不过还是大同小异，我们先来看一下测试环境的基本情况：

oracle@linux-fz9d:~> rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 30 11:32:53 2013

connected to target database: ORCL (DBID=1348370955)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1/db_1/dbs/snapcf_ora12c130.f'; # default

RMAN> list backup summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

40 B 0 A DISK 30-OCT-13 1 1 NO JERRY_LEV0

41 B 0 A DISK 30-OCT-13 1 1 NO JERRY_LEV0

43 B 0 A DISK 30-OCT-13 1 1 NO JERRY_LEV0

44 B 0 A DISK 30-OCT-13 1 1 NO JERRY_LEV0

46 B A A DISK 30-OCT-13 1 1 NO ARC_BAK

48 B A A DISK 30-OCT-13 1 1 NO ARC_BAK

50 B F A DISK 30-OCT-13 1 1 NO BAK_CTLFILE

51 B F A DISK 30-OCT-13 1 1 NO SPFILE

oracle@linux-fz9d:~>rmantarget/

RecoveryManager:Release12.1.0.1.0-ProductiononWedOct3011:32:532013

connectedtotargetdatabase:ORCL(DBID=1348370955)

RMAN>showall;

usingtargetdatabasecontrolfileinsteadofrecoverycatalog

RMANconfigurationparametersfordatabasewithdb_unique_nameORCLare:

CONFIGURERETENTIONPOLICYTOREDUNDANCY1;# default

CONFIGUREBACKUPOPTIMIZATIONOFF;# default

CONFIGUREDEFAULTDEVICETYPETODISK;# default

CONFIGURECONTROLFILEAUTOBACKUPOFF;# default

CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F';# default

CONFIGUREDEVICETYPEDISKPARALLELISM4BACKUPTYPETOBACKUPSET;

CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;# default

CONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;# default

CONFIGUREMAXSETSIZETOUNLIMITED;# default

CONFIGUREENCRYPTIONFORDATABASEOFF;# default

CONFIGUREENCRYPTIONALGORITHM'AES128';# default

CONFIGURECOMPRESSIONALGORITHM'BASIC'ASOFRELEASE'DEFAULT'OPTIMIZEFORLOADTRUE;# default

CONFIGURERMANOUTPUTTOKEEPFOR7DAYS;# default

CONFIGUREARCHIVELOGDELETIONPOLICYTONONE;# default

CONFIGURESNAPSHOTCONTROLFILENAMETO'/opt/oracle/product/12.1/db_1/dbs/snapcf_ora12c130.f';# default

RMAN>listbackupsummary;

ListofBackups

===============

KeyTYLVSDeviceTypeCompletionTime#Pieces #Copies Compressed Tag

-----------------------------------------------------------------

40B0ADISK30-OCT-1311NOJERRY_LEV0

41B0ADISK30-OCT-1311NOJERRY_LEV0

43B0ADISK30-OCT-1311NOJERRY_LEV0

44B0ADISK30-OCT-1311NOJERRY_LEV0

46BAADISK30-OCT-1311NOARC_BAK

48BAADISK30-OCT-1311NOARC_BAK

50BFADISK30-OCT-1311NOBAK_CTLFILE

51BFADISK30-OCT-1311NOSPFILE

首先要知道数据库的dbid，可以从v$database视图中查出来，在进入rman的时候，也有提示dbid

SQL> select dbid from v$database;

DBID

----------

1348370955

SQL>selectdbidfromv$database;

DBID

----------

1348370955

然后我们将数据库启动到nomount状态，设置dbid

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 584568832 bytes

Fixed Size 2290992 bytes

Variable Size 465570512 bytes

Database Buffers 113246208 bytes

Redo Buffers 3461120 bytes

SQL>shutdownimmediate

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

SQL>startupnomount

ORACLEinstancestarted.

TotalSystemGlobalArea584568832bytes

FixedSize2290992bytes

VariableSize465570512bytes

DatabaseBuffers113246208bytes

RedoBuffers3461120bytes

oracle@linux-fz9d:~> rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 30 11:38:16 2013

connected to target database: ORCL (not mounted)

RMAN> set dbid=1348370955

executing command: SET DBID

oracle@linux-fz9d:~>rmantarget/

RecoveryManager:Release12.1.0.1.0-ProductiononWedOct3011:38:162013

connectedtotargetdatabase:ORCL(notmounted)

RMAN>setdbid=1348370955

executingcommand:SETDBID

然后直接restore spfile

RMAN> restore spfile from autobackup;

Starting restore at 30-OCT-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/30/2013 11:40:42

RMAN-06564: must use the TO clause when the instance is started with SPFILE

RMAN>restorespfilefromautobackup;

Startingrestoreat30-OCT-13

usingchannelORA_DISK_1

RMAN-00571:===========================================================

RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============

RMAN-00571:===========================================================

RMAN-03002:failureofrestorecommandat10/30/201311:40:42

RMAN-06564:mustusetheTOclausewhentheinstanceisstartedwithSPFILE

这里会报一个06564的错误，原因是因为数据库已经用spfile挂载到nomount状态，如果spfile丢失，这里应该是用pfile挂载的，就不会报这个错误了，我这里就直接按照提示修改一下就可以了

RMAN> restore spfile to '/opt/oracle/spfile.ora' from autobackup;

Starting restore at 30-OCT-13

using channel ORA_DISK_1

recovery area destination: /opt/oracle/fast_recovery_area

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131030

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131029

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131028

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131027

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131026

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131025

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131024

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 10/30/2013 11:43:12

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>restorespfileto'/opt/oracle/spfile.ora'fromautobackup;

Startingrestoreat30-OCT-13

usingchannelORA_DISK_1

recoveryareadestination:/opt/oracle/fast_recovery_area

databasename(ordatabaseuniquename)usedforsearch:ORCL

channelORA_DISK_1:noAUTOBACKUPSfoundintherecoveryarea

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131030

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131029

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131028

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131027

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131026

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131025

channelORA_DISK_1:lookingforAUTOBACKUPonday:20131024

channelORA_DISK_1:noAUTOBACKUPin7daysfound

RMAN-00571:===========================================================

RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============

RMAN-00571:===========================================================

RMAN-03002:failureofrestorecommandat10/30/201311:43:12

RMAN-06172:noAUTOBACKUPfoundorspecifiedhandleisnotavalidcopyorpiece

这里又报06172错误，提示没有找到7天内的自动备份，而我使用的备份集手动备份的时候，没有设置controlfile和spfile自动备份，可以看到如下参数是off的：CONTROLFILE AUTOBACKUP OFF; 如果启用这个参数，在重新备份的情况下(备份一下datafile或者表空间)，使用上面的命令就不会有问题了:

触发spfile自动备份的条件：

1. 当CONFIGURE CONTROLFILE AUTOBACKUP OFF;时，仅备份数据文件1(system01.dbf)时会自动备份控制文件；

2. 当CONFIGURE CONTROLFILE AUTOBACKUP ON;时，备份任意文件(数据文件/归档日志)都会自动备份控制文件。

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1/db_1/dbs/snapcf_ora12c130.f'; # default

RMAN>showall;

RMANconfigurationparametersfordatabasewithdb_unique_nameORCLare:

CONFIGURERETENTIONPOLICYTOREDUNDANCY1;# default

CONFIGUREBACKUPOPTIMIZATIONOFF;# default

CONFIGUREDEFAULTDEVICETYPETODISK;# default

CONFIGURECONTROLFILEAUTOBACKUPOFF;# default

CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F';# default

CONFIGUREDEVICETYPEDISKPARALLELISM4BACKUPTYPETOBACKUPSET;

CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;# default

CONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;# default

CONFIGUREMAXSETSIZETOUNLIMITED;# default

CONFIGUREENCRYPTIONFORDATABASEOFF;# default

CONFIGUREENCRYPTIONALGORITHM'AES128';# default

CONFIGURECOMPRESSIONALGORITHM'BASIC'ASOFRELEASE'DEFAULT'OPTIMIZEFORLOADTRUE;# default

CONFIGURERMANOUTPUTTOKEEPFOR7DAYS;# default

CONFIGUREARCHIVELOGDELETIONPOLICYTONONE;# default

CONFIGURESNAPSHOTCONTROLFILENAMETO'/opt/oracle/product/12.1/db_1/dbs/snapcf_ora12c130.f';# default

这里我们就先不启用这个参数，看一下系统有没有spfile的备份

RMAN> list backup of spfile;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of list command at 10/30/2013 11:43:55

ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

RMAN> list backup of spfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

51 Full 80.00K DISK 00:00:00 30-OCT-13

BP Key: 51 Status: AVAILABLE Compressed: NO Tag: SPFILE

Piece Name: /opt/oracle/fast_recovery_area/ORCL/backup/Jerry_spfile_1eonmrg2_1_1_20131030

SPFILE Included: Modification time: 30-OCT-13

SPFILE db_unique_name: ORCL

RMAN>listbackupofspfile;

RMAN-00571:===========================================================

RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============

RMAN-00571:===========================================================

RMAN-03002:failureoflistcommandat10/30/201311:43:55

ORA-01507:databasenotmounted

RMAN>alterdatabasemount;

Statementprocessed

releasedchannel:ORA_DISK_1

RMAN>listbackupofspfile;

ListofBackupSets

===================

BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime

-------------------------------------------------------------

51Full80.00KDISK00:00:0030-OCT-13

BPKey:51Status:AVAILABLECompressed:NOTag:SPFILE

PieceName:/opt/oracle/fast_recovery_area/ORCL/backup/Jerry_spfile_1eonmrg2_1_1_20131030

SPFILEIncluded:Modificationtime:30-OCT-13

SPFILEdb_unique_name:ORCL

在系统找到一个spfile的备份，这里我们直接恢复一下

RMAN> restore spfile to '/opt/oracle/spfile.ora' from '/opt/oracle/fast_recovery_area/ORCL/backup/Jerry_spfile_1eonmrg2_1_1_20131030';

Starting restore at 30-OCT-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=22 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=23 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=24 device type=DISK

channel ORA_DISK_2: skipped, AUTOBACKUP already found

channel ORA_DISK_3: skipped, AUTOBACKUP already found

channel ORA_DISK_4: skipped, AUTOBACKUP already found

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /opt/oracle/fast_recovery_area/ORCL/backup/Jerry_spfile_1eonmrg2_1_1_20131030

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 30-OCT-13

RMAN>restorespfileto'/opt/oracle/spfile.ora'from'/opt/oracle/fast_recovery_area/ORCL/backup/Jerry_spfile_1eonmrg2_1_1_20131030';

Startingrestoreat30-OCT-13

allocatedchannel:ORA_DISK_1

channelORA_DISK_1:SID=21devicetype=DISK

allocatedchannel:ORA_DISK_2

channelORA_DISK_2:SID=22devicetype=DISK

allocatedchannel:ORA_DISK_3

channelORA_DISK_3:SID=23devicetype=DISK

allocatedchannel:ORA_DISK_4

channelORA_DISK_4:SID=24devicetype=DISK

channelORA_DISK_2:skipped,AUTOBACKUPalreadyfound

channelORA_DISK_3:skipped,AUTOBACKUPalreadyfound

channelORA_DISK_4:skipped,AUTOBACKUPalreadyfound

channelORA_DISK_1:restoringspfilefromAUTOBACKUP/opt/oracle/fast_recovery_area/ORCL/backup/Jerry_spfile_1eonmrg2_1_1_20131030

channelORA_DISK_1:SPFILErestorefromAUTOBACKUPcomplete

Finishedrestoreat30-OCT-13

ok了，简单的一个spfile就已经恢复了，一般在生产环境中，配置成CONFIGURE CONTROLFILE AUTOBACKUP ON; # default 这样在备份的时候，控制文件和spfile就可以自动备份，恢复的时候方便。如果是off的话，只有备份system表空间才会触发spfile的自动备份。

最后编辑：2013-10-31作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL

进击的大虎

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle重建spfile,oracle spfile 丢失，简单恢复案例！

如果系统的spfile丢失，在有备份的情况下，可以直接进行恢复的，在生产环境中，rman的设置和备份集的设置可能和实验环境不一样，不过还是大同小异，我们先来看一下测试环境的基本情况：C#oracle@linux-fz9d:~> rman target /Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 30 11:32:...
复制链接

扫一扫