Oracle参数文件的备份与恢复

        Oracle实例启动需要读取参数文件，实例启动进程会在$ORACLE_HOME/dbs目录(linux/unix系统)下按照spfile<sid>.ora、spfile.ora、init<sid>.ora的顺序搜寻参数文件，如果这三个文件都存在，则找到spfile文件就不往下找其他的参数文件。

        spfile<sid>.ora和spfile.ora是服务器参数文件，以二进制的格式保存，如果是以服务器参数文件启动的实例，可以用"alter system  …… "命令修改实例的参数。可以使用命令"show parameter spfile"查看实例是否以服务器参数文件启动。

       "alter system  ……  scope=spfile"将修改的结果保存到spfile<sid>.ora文件，但是内存中的值不做修改，此命令一般适合修改静态参数，如果修改完后想要参数生效，必须重启实例。

       "alter system  ……  scope=both"将修改的结果保存到spfile<sid>.ora文件的同时修改内存中的值，使得修改的参数及时生效。

       init<sid>.ora是客户端参数文件也叫pfile，以文本方式保存，可以按照需要编辑器中的内容。如果实例以客户端参数文件启动，启动完后将和客户端参数文件再无关系，使用"alter system  …… "无法修改客户端和服务端参数文件的内容，比如：

SYS@orcl>startup pfile='?/dbs/initorcl.ora'                             ---启动实例指定$ORACLE_HOME/dbs目录下的pfile

ORACLE instancestarted.

Total System GlobalArea  536870912 bytes

FixedSize                    2085360 bytes

VariableSize                  159387152 bytes

DatabaseBuffers          369098752 bytes

RedoBuffers                    6299648 bytes

Database mounted.

Database opened.

 

SYS@orcl>show parameter spfile;                                                     ---值为空表示使用pfile启动的实例

NAME                                     TYPE        VALUE

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

spfile                                     string

SYS@orcl>

 

SYS@orcl>showparameter pga

NAME                                     TYPE        VALUE

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

pga_aggregate_target                     big integer 109M

 

SYS@orcl>alter system set pga_aggregate_target=120m;                             ---可以修改动态参数

System altered.

 

SYS@orcl>showparameter pga;

NAME                                     TYPE        VALUE

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

pga_aggregate_target                     big integer 120M

 

SYS@orcl>alter system set pga_aggregate_target=130mscope=both;                            ---修改的参数无法保存到spfile

alter system setpga_aggregate_target=130m scope=both

*

ERROR at line 1:

ORA-32001: write toSPFILE requested but no SPFILE specified at startup

 

以下关于参数文件的备份和恢复均在Oracle 10g 10.2.0.4单实例环境下实现，rman的备份和恢复均利用快速恢复区flash recovery area，没有使用rman recovery catalog。

一、服务器参数文件的备份

1、利用操作系统命令备份spfile

cp$ORACLE_HOME/dbs/spfile<sid>.ora /home/oracle

 

2、利用rman备份spfile参数文件

2.1手工备份

RMAN> backup spfile;                                            ---手工备份spfile

Starting backup at25-NOV-12

using channelORA_DISK_1

channel ORA_DISK_1:starting full datafile backupset

channel ORA_DISK_1:specifying datafile(s) in backupset

including currentSPFILE in backupset

channel ORA_DISK_1:starting piece 1 at 25-NOV-12

channel ORA_DISK_1:finished piece 1 at 25-NOV-12

piecehandle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_25/o1_mf_nnsnf_TAG20121125T153635_8c3ln41t_.bkptag=TAG20121125T153635 comment=NONE

channel ORA_DISK_1:backup set complete, elapsed time: 00:00:01

Finished backup at25-NOV-12

 

2.2自动备份

如果rman开启了控制文件自动备份，参数文件和控制文件会在备份任何文件的时候自动备份，比如：

RMAN> show controlfile autobackup;                                 ---显示控制文件自动备份，参数文件也会同时备份，rman命令'configurecontrolfile autobackup on'设置控制文件自动备份

 

RMAN configurationparameters are:

CONFIGURECONTROLFILE AUTOBACKUP ON;

 

RMAN> backup tablespace users;                                            ---备份users表空间

Starting backup at25-NOV-12

using channelORA_DISK_1

channel ORA_DISK_1:starting full datafile backupset

channel ORA_DISK_1:specifying datafile(s) in backupset

input datafilefno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1:starting piece 1 at 25-NOV-12

channel ORA_DISK_1:finished piece 1 at 25-NOV-12

piecehandle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_25/o1_mf_nnndf_TAG20121125T150315_8c3jom9g_.bkptag=TAG20121125T150315 comment=NONE

channel ORA_DISK_1:backup set complete, elapsed time: 00:00:01

Finished backup at25-NOV-12

 

Starting Control File and SPFILE Autobackup at 25-NOV-12                      ---显示控制文件和spfile也自动备份

piecehandle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_25/o1_mf_s_800290996_8c3jonff_.bkpcomment=NONE

Finished ControlFile and SPFILE Autobackup at 25-NOV-12

 

 二、服务器参数文件的恢复

1、启动实例时spfile文件损坏

1.1如果之前有用操作系统命令备份过spfile，可以再操作系统命令把备份复制到$ORACLE_HOME/dbs目录，比如：

cp/home/spfile<sid>.ora $ORACLE_HOME/dbs/spfile<sid>.ora

 

1.2利用rman恢复spfile

1.2.1恢复时指定恢复文件

[oracle@oraedudbs]$ rman target /

 

Recovery Manager:Release 10.2.0.4.0 - Production on Sun Nov 25 15:23:40 2012

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

 connected to targetdatabase (not started)

 

RMAN> startup nomount;                               ---启动实例到nomount状态，rman使用内置参数文件启动实例

 startup failed:ORA-01078: failure in processing system parameters

LRM-00109: could notopen parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'

 

starting Oracleinstance without parameter file for retrival of spfile

Oracle instancestarted

 

Total System GlobalArea     159383552 bytes

 Fixed Size                     2082400 bytes

Variable Size                 67111328 bytes

DatabaseBuffers              83886080 bytes

Redo Buffers                   6303744 bytes

 

RMAN> restore spfile from'/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_25/o1_mf_nnsnf_TAG20121125T153635_8c3ln41t_.bkp';     ---指定'backup spfile'命令备份的备份集

 

Starting restore at25-NOV-12

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channel ORA_DISK_1:sid=36 devtype=DISK

 

channel ORA_DISK_1:autobackup found:/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_11_25/o1_mf_nnsnf_TAG20121125T153635_8c3ln41t_.bkp

channel ORA_DISK_1:SPFILE restore from autobackup complete

Finished restore at25-NOV-12

 

RMAN> restore spfile from'/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_25/o1_mf_s_800292997_8c3ln567_.bkp';                                                         ---指定rman自动备份的备份集

 

Starting restore at25-NOV-12

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channel ORA_DISK_1:sid=36 devtype=DISK

 

channel ORA_DISK_1:autobackup found:/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_25/o1_mf_s_800292997_8c3ln567_.bkp

channel ORA_DISK_1:SPFILE restore from autobackup complete

Finished restore at25-NOV-12

 

1.2.2利用rman的自动备份恢复spfile

如果知道数据库的db_name和快速恢复区的路径可以执行下列命令还原

RMAN>restore spfile from autobackup db_name=orcldb_recovery_file_dest='/u01/app/oracle/flash_recovery_area';

 

Starting restore at25-NOV-12

using channelORA_DISK_1

 

recovery areadestination: /u01/app/oracle/flash_recovery_area

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

channel ORA_DISK_1:autobackup found in the recovery area

channel ORA_DISK_1:autobackup found:/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_25/o1_mf_s_800292997_8c3ln567_.bkp

channel ORA_DISK_1:SPFILE restore from autobackup complete

Finished restore at25-NOV-12

 

2.实例运行时spfile文件损坏

如果实例运行时发现spfile文件丢失或者损坏，不要关闭实例，可以直接在rman中利用自动备份恢复到其他目录，比如：

SYS@orcl>altersystem set pga_aggregate_target=120m scope=both;

alter system setpga_aggregate_target=120m scope=both

*

ERROR at line 1:

ORA-01565: error inidentifying file

'/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora'

ORA-27037: unable toobtain file status

Linux-x86_64 Error:2: No such file or directory

Additionalinformation: 3

 

RMAN> restore spfile to '/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora' from autobackup; 

---从自动备份中恢复spfile到指定的目录

 

Starting restore at25-NOV-12

using channelORA_DISK_1

 

recovery areadestination: /u01/app/oracle/flash_recovery_area

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

channel ORA_DISK_1:autobackup found in the recovery area

channel ORA_DISK_1:autobackup found:/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2012_11_25/o1_mf_s_800292997_8c3ln567_.bkp

channel ORA_DISK_1:SPFILE restore from autobackup complete

Finished restore at25-NOV-12

 

如果数据库是11g，还可以在sqlplus中执行create pfile/spfile from memory生成spfile和pfile。

 

三、没有备份的情况下恢复参数文件

如果极端情况下没有备份spfile和pfile，或者所有的spfile和pfile备份丢失损坏，可以用下面两种方法恢复：

1.数据库是用dbca命令创建

如果数据库使用dbca命令创建，在创建过程中dbca会在$ORACLE_BASE/admin/<sid>/pfile/目录下生成一个'init.ora.创建时间'的pfile，可以在启动实例时指定此pfile,比如：

SYS@orcl>startuppfile='/u01/app/oracle/admin/orcl/pfile/init.ora.7292012204845';

ORACLE instancestarted.

 

Total System GlobalArea  343932928 bytes

FixedSize                    2083816 bytes

VariableSize                  104858648 bytes

DatabaseBuffers          230686720 bytes

RedoBuffers                    6303744 bytes

Database mounted.

Database opened.

 

然后从pfile中创建spfile。

SYS@orcl>createspfile from pfile='/u01/app/oracle/admin/orcl/pfile/init.ora.7292012204845';

File created.

 

2.利用警告日志

可以查看警告日志，警告日志位于$ORACLE_BASE/admin/<sid>/bdump/alert_<sid>.log，警告日志中有

Starting up ORACLERDBMS Version: 10.2.0.4.0.

System parameterswith non-default values:

  processes                = 150

  sga_max_size             = 536870912

  __shared_pool_size       = 150994944

  __large_pool_size        = 4194304

  __java_pool_size         = 4194304

  __streams_pool_size      = 0

  nls_length_semantics     = BYTE

  resource_manager_plan    =

  sga_target               = 536870912

  control_files            =/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/oradata/orcl/control02.ctl,/u01/app/oracle/oradata/orcl/control03.ctl

db_block_size            = 8192

  __db_cache_size          = 369098752

  compatible               = 10.2.0.1.0

  log_archive_format       = %t_%s_%r.dbf

  db_file_multiblock_read_count= 16

  db_recovery_file_dest    = /u01/app/oracle/flash_recovery_area

  db_recovery_file_dest_size= 2147483648

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  undo_retention           = 900

  remote_login_passwordfile= EXCLUSIVE

  db_domain                =

  dispatchers              = (PROTOCOL=TCP)(SERVICE=orclXDB)

  job_queue_processes      = 10

  background_dump_dest     = /u01/app/oracle/admin/orcl/bdump

  user_dump_dest           = /u01/app/oracle/admin/orcl/udump

  core_dump_dest           = /u01/app/oracle/admin/orcl/cdump

  audit_file_dest          = /u01/app/oracle/admin/orcl/adump

  db_name                  = orcl

  open_cursors             = 300

  pga_aggregate_target     = 114294784

  aq_tm_processes          = 0

其中非默认的参数都记录在内，可以使用里面的内容重建pfile。

 

3、极端情况下没有备份、没有警告日志

在这种情况下只有手工重建pfile

control_files

db_block_size

db_name

Compatible

这几个参数是启动实例和mount数据库必须的，可以在pfile中设置这几个参数启动实例，但是非常不推荐使用这种途径恢复参数文件，所以一定要注意备份、备份、再备份。