Control File 恢复

最新推荐文章于 2024-03-07 22:10:51 发布

原创最新推荐文章于 2024-03-07 22:10:51 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ControlFile 恢复 #ORACLE

数据迁移同时被 3 个专栏收录

16 篇文章

订阅专栏

读书笔记

6 篇文章

订阅专栏

Oracle Backup & Recovery

4 篇文章

订阅专栏

本文介绍了Oracle数据库中控制文件的几种恢复方法，包括在丢失所有控制文件时的重建过程、多路复用控制文件的恢复操作以及利用RMAN工具进行控制文件的恢复。文章详细展示了SQL命令和步骤。

场景一：重建control File
•You’ve experienced a failure and lost all of your control files, and you belatedly realizethat you don’t have a good binary backup of the control file.
•You want to change a database setting that can be modified only by re-creating the con-trol file.
•You are relocating a large number of datafiles, and you find it easier to re-create the　control file with the new names and locations (instead of manually renaming the　datafiles)

解决：重建Control FIles

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/pur
                                                 ple/purple1/trace
技巧：NAMING A TRACE FILE
Trace文件命名规则 <SID><Oracle process id><tracefile_identifier>.trc，我们可以通过设置tracefile_identifier来识别Trace文件。
SQL> alter session set tracefile_identifier=mytrace 

Session altered.

SQL> select distinct tracefile from v$process p,v$session s ,v$mystat m  where p.addr=s.paddr   and s.sid=m.sid;--查看当前TraceFile

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/purple/purple1/trace/purple1_ora_14669_MYTRACE.trc


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PURPLE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/purple/onlinelog/group_1.257.805043621'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/purple/onlinelog/group_2.258.805043623'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/purple/datafile/system.264.805043525',
  '+DATA/purple/datafile/sysaux.262.805043527',
  '+DATA/purple/datafile/undotbs1.256.805043527',
  '+DATA/purple/datafile/users.260.805043527',
  '+DATA/purple/datafile/undotbs2.263.805043817'
CHARACTER SET ZHS16GBK
;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PURPLE" NORESETLOGS ARCHIVELOG 

    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/purple/onlinelog/group_1.257.805043621'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/purple/onlinelog/group_2.258.805043623'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/purple/onlinelog/group_3.271.805043939'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 '+DATA/purple/onlinelog/group_4.272.805043941'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/purple/datafile/system.264.805043525',
  '+DATA/purple/datafile/sysaux.262.805043527',
  '+DATA/purple/datafile/undotbs1.256.805043527',
  '+DATA/purple/datafile/users.260.805043527',
  '+DATA/purple/datafile/undotbs2.263.805043817'
CHARACTER SET ZHS16GBK
;

场景二：多路复用控制文件的恢复

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size            1090521080 bytes
Database Buffers          553648128 bytes
Redo Buffers                7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info

检查alter.log
ORA-00210: ???????????
ORA-00202: ????: ''+DATA/purple/controlfile/current03''
ORA-17503: ksfdopn: 2 ?????? +DATA/purple/controlfile/current03
ORA-15173: entry 'current03' does not exist in directory 'controlfile'
SQL> alter system set control_files='+DATA/purple/controlfile/current01','+DATA/purple/controlfile/current02' scope=spfile sid='*'; --修改control Files
System altered.
SQL> startup
SQL> shutdown immediate 
ASMCMD> cp current02 current03 --拷贝好的控制文件 
copying +data/purple/controlfile/current02 -> +data/purple/controlfile/current03
SQL>startup nomount;
SQL> alter system set control_files='+DATA/purple/controlfile/current01','+DATA/purple/controlfile/current02','+DATA/purple/controlfile/current03' scope=spfile id='*'；

场景三：使用RMAN 恢复 Control Files

RMAN>  restore controlfile to '+DATA/purple/controlfile/current04' from '+DATA/purple/controlfile/current01';

Starting restore at 22-JAN-2013 11:12:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=194 instance=purple1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 22-JAN-2013 11:12:33