控制文件的备份与恢复

控制文件对数据库的重要性是不言而喻的，根据我近期学习，总结了一下控制文件的备份和恢复

1.单个控制文件丢失或损坏

  我们知道数据库的控制文件都不止一个（一般为3个），这些控制文件互相为镜像，所以只需要将其他没损坏的控   制   文件重命名为损坏的控制文件即可。

  我现在有三个控制文件

  -rw-r----- 1 oracle oinstall   7258112 Mar 13 15:18 control01.ctl
  -rw-r----- 1 oracle oinstall   7258112 Mar 13 15:18 control02.ctl
  -rw-r----- 1 oracle oinstall   7258112 Mar 13 15:18 control03.ctl

   现在删除一个控制文件control01.ctl

  SQL> startup
  ORACLE instance started.
  Total System Global Area  285212672 bytes
  Fixed Size                  1218992 bytes
  Variable Size             104859216 bytes
  Database Buffers          176160768 bytes
  Redo Buffers                2973696 bytes
  ORA-00205: error in identifying control file, check alert log for more info

  将control02.ctl复制为control01.ctl

  [oracle@localhost orcl]$ cp  control02.ctl  control01.ctl

  成功启动数据库

  SQL> alter database  mount;
  Database altered.

  SQL> alter database open；
  Database altered.

  注：单个控制文件损坏也可以使用下面所讲的全部控制文件损坏时的恢复方法，只是这里直接重命名其他控制文件       的方法 比较快

2.全部控制文件损坏

  我们先说一下控制文件的几种备份恢复情况

  2.1使用RMAN备份与恢复控制文件

     控制文件备份：

     a.通过CONTROLFILE AUTOBACKUP ON;参数来实现控制文件的自动备份（默认存放位置为快速恢复区）

     b.RMAN> backup  current  controlfile;

     c.RMAN> backup database include current controlfile;

     控制文件恢复：

     RMAN> startup  nomount;

     RMAN> restore controlfile   from  autobackup;（前提是启动的自动备份，也可以restore            controlfile   from ‘/opt/app/bak/control.ctl';)

     RMAN> alter database  mount;

     RMAN> recover  database;

     RMAN> alter  database  open resetlogs;(建议在resetlogs后备份数据库）

  2.2使用sql*plus备份控制文件到指定文件

     备份：

     SQL>alter  database  backup  controlfile  to '/home/oracle/bak/control_bk.ctl';

     恢复：

     首先将备份的控制文件拷贝至原控制文件的路径下，控制文件的名字和个数都要与原来的相同

     其次SQL>startup  mount;

         SQL>recover database  using backup  controlfile;

         SQL>alter database  open  resetlogs;

  3.3使用sql*plus备份控制文件到trace

     SQL> alter database backup controlfile to trace;   

     在/opt/app/oracle/admin/orcl/udump下

     [oracle@localhost udump]$ vi orcl_ora_8280.trc

     只需要执行标记”Set #1. NORESETLOGS case”部分。
      注：
     一般情况下trace文件里都会有2部分相关内容
     恢复时先执行”Set #1. NORESETLOGS case”部分

    

     --     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/opt/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 (
    '/opt/app/oracle/oradata/orcl/redo02.log',
    '/home/oracle/dpump/redo2.log'
  ) SIZE 50M,
  GROUP 3 (
    '/opt/app/oracle/oradata/orcl/redo03.log',
    '/home/oracle/dpump/redo3.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/opt/app/oracle/oradata/orcl/system01.dbf',
  '/opt/app/oracle/oradata/orcl/undotbs01.dbf',
  '/opt/app/oracle/oradata/orcl/sysaux01.dbf',
  '/opt/app/oracle/oradata/orcl/users01.dbf',
  '/opt/app/oracle/oradata/orcl/example01.dbf',
  '/home/oracle/dpump/part2.dbf',
  '/home/oracle/dpump/part3.dbf',
  '/home/oracle/dpump/part4.dbf',
  '/home/oracle/dpump/user02.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_13/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/opt/app/oracle/oradata/block_trace.log' REUSE;
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00006'
  TO '/home/oracle/dpump/part1.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "PART1_TBS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/app/oracle/oradata/orcl/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE NEWTEMP ADD TEMPFILE '/home/oracle/dpump/newtemp.dbf'
     SIZE 10485760  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

   执行完后再去查看发现控制文件全回来了，数据库正常打开