oracle 控制文件的备份恢复

添加控制文件
alter system set control_files='/u01/oracle/app/oradata/pahc/control01.ctl','/u01/oracle/app/oradata/pahc/control02.ctl' scope=spfile;
shutdown immediate;
cp control01.ctl control02.ctl
startup

有备份控制文件恢复
删除控制文件
[oracle@localhost pahc]$ mv control01.ctl control01.ctl.bak
[oracle@localhost pahc]$ mv control02.ctl control02.ctl.bak
SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oracle/app/oradata/pahc/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

启动数据库到nomount 状态
SQL> startup nomount
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes

从备份中还原备份的控制文件
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 5 10:03:13 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PAHC (not mounted)

RMAN> set DBID=1813560051

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 05-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/oracle/app/fast_recovery_area
database name (or database unique name) used for search: HCDBDG
channel ORA_DISK_1: AUTOBACKUP /u01/oracle/app/fast_recovery_area/HCDBDG/autobackup/2016_04_05/o1_mf_s_908358691_cj66k483_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160405
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/oracle/app/fast_recovery_area/HCDBDG/autobackup/2016_04_05/o1_mf_s_908358691_cj66k483_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oracle/app/oradata/pahc/control01.ctl
output file name=/u01/oracle/app/oradata/pahc/control02.ctl
Finished restore at 05-APR-16

启动到mount状态查看控制文件和数据文件头的checkpoint_change#
v$database.checkpoint_change#<v$datafile_header.checkpoint_change#
则数据库需要恢复控制文件
SQL> alter database mount;

Database altered.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
    1417055

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
    1417055
    1417055
    1417055
    1417055
    1417055

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
    1418465
    1418465
    1418465
    1418465
    1418465

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 1417562 generated at 04/05/2016 09:47:45 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_41_907601094.dbf
ORA-00280: change 1417562 for thread 1 is in sequence #41

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/oracle/app/arch/1_41_907601094.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/u01/oracle/app/arch/1_41_907601094.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
查看当前的日子组
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
  1 INACTIVE
  4 INACTIVE
  3 INACTIVE
  2 CURRENT

SQL>

SQL> recover database using backup controlfile;
ORA-00279: change 1417562 generated at 04/05/2016 09:47:45 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_41_907601094.dbf
ORA-00280: change 1417562 for thread 1 is in sequence #41

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/app/oradata/pahc/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database noresetlogs;
alter database noresetlogs
                         *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>

有之前的控制文件，备份后数据库物理结构发生变化
创建新的表空间
create  tablespace test_controlfile datafile '/u01/oracle/app/oradata/pahc/test_controlfile.dbf' size 10M;

SQL> recover database using backup controlfile;
ORA-00279: change 1418578 generated at 04/05/2016 10:12:06 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_1_908359926.dbf
ORA-00280: change 1418578 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/app/oradata/pahc/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/u01/oracle/app/oradata/pahc/test_controlfile.dbf'

ORA-01112: media recovery not started

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
  1
/u01/oracle/app/oradata/pahc/system01.dbf

  2
/u01/oracle/app/oradata/pahc/sysaux01.dbf

  3
/u01/oracle/app/oradata/pahc/undotbs01.dbf

     FILE#
----------
NAME
--------------------------------------------------------------------------------
  4
/u01/oracle/app/oradata/pahc/users01.dbf

  5
/u01/oracle/app/oradata/pahc/test_arch.dbf

  6
/u01/oracle/app/oracle/product/11.2/dbhome_1/dbs/UNNAMED00006

6 rows selected.
修改数据文件名称
SQL> alter database rename file '/u01/oracle/app/oracle/product/11.2/dbhome_1/dbs/UNNAMED00006' to '/u01/oracle/app/oradata/pahc/test_controlfile.dbf';

Database altered.

SQL> recover database using backup controlfile;
ORA-00279: change 1418980 generated at 04/05/2016 10:22:30 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_1_908359926.dbf
ORA-00280: change 1418980 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/app/oradata/pahc/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
  1
/u01/oracle/app/oradata/pahc/system01.dbf

  2
/u01/oracle/app/oradata/pahc/sysaux01.dbf

  3
/u01/oracle/app/oradata/pahc/undotbs01.dbf

     FILE#
----------
NAME
--------------------------------------------------------------------------------
  4
/u01/oracle/app/oradata/pahc/users01.dbf

  5
/u01/oracle/app/oradata/pahc/test_arch.dbf

  6
/u01/oracle/app/oradata/pahc/test_controlfile.dbf

6 rows selected.

没有rman备份，但是有数据库层面备份

alter database backup controlfile to trace as '/home/oracle/control.trc';
SQL> alter database backup controlfile to trace as '/home/oracle/control.trc';

Database altered.

SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oracle/app/oradata/pahc/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes
根据数据库层面的备份，创建新的控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "PAHC" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/oracle/app/oradata/pahc/redo01.log',
    '/u01/oracle/app/oradata/pahc/redo01_2.log',
    '/u01/oracle/app/oradata/pahc/redo01_3.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/oracle/app/oradata/pahc/redo02.log',
    '/u01/oracle/app/oradata/pahc/redo02_2.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/oracle/app/oradata/pahc/redo03.log',
    '/u01/oracle/app/oradata/pahc/redo03_2.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 4 (
    '/u01/oracle/app/oradata/pahc/redo04.log',
    '/u01/oracle/app/oradata/pahc/redo04_1.log'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oracle/app/oradata/pahc/system01.dbf',
  '/u01/oracle/app/oradata/pahc/sysaux01.dbf',
  '/u01/oracle/app/oradata/pahc/undotbs01.dbf',
  '/u01/oracle/app/oradata/pahc/users01.dbf',
  '/u01/oracle/app/oradata/pahc/test_arch.dbf',
  '/u01/oracle/app/oradata/pahc/test_controlfile.dbf'
CHARACTER SET AL32UTF8
; 
Control file created.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

SQL> recover database using backup controlfile;
ORA-00279: change 1420188 generated at 04/05/2016 10:39:35 needed for thread 1
ORA-00289: suggestion : /u01/oracle/app/arch/1_1_908361013.dbf
ORA-00280: change 1420188 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/app/oradata/pahc/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29497510/viewspace-2075368/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29497510/viewspace-2075368/