模拟控制文件丢失的数据库恢复

更多精彩内容尽在www.leonarding.com一数据库版本SYS@LEO1showuserUSERisSYSSYS@LEO1select*fromv$version;BANNER--------------------------------------------

更多精彩内容尽在

一 数据库版本

SYS@LEO1>showuser

USER is"SYS"

SYS@LEO1>select* from v$version;

BANNER

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

Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux:Version 11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 - Production

1.模拟控制文件丢失后的数据库恢复(完全恢复)。

今天的主题是备份与恢复，目的就是保护数据的安全性，众所周知Oracle之所以在市场上占据了50%的份额，与它提供了强大的数据保护措施是分不开的，下面我们就来简捷的介绍一下。

1）物理备份

冷备：这是最原始的一种备份方法，又是最简单可行的，就和copy一份文件一样，直接把库shutdown拷贝一份即可，操作简单，恢复快。当在一个没有专业人员的场合下，告诉他们这么操作是简单可行的，不是不可能只是你没遇到，一切皆有可能哦！

热备：Oracle专业备份工具RMAN，这是在8i就有的东东，很强大，可以在很多维度层面进行备份恢复，利用RMAN可以在联机的情况下进行在线备份与恢复。

2）逻辑备份

Exp/Imp：表级 用户级 数据库级进行逻辑备份，逻辑是对于业务层面而言的，例如我只想备份 person employment address表的内容用这种方法将会非常简单，它的亮点更在于备份出来的文件非常好迁移，兼容不同版本

Expdp/Impdp：这是上面2个工具的高级版，可压缩 速度更快 传输表空间的最佳利器，但只能用在服务器端

3）实例恢复

实例是什么，就是内存区+后台进程，那么实例恢复也就是恢复内存数据，例如 突然死机 掉电 强制关库等，在你startup 启动时候后台会自动进行实例恢复。SMON进程负责执行

4）介质恢复

就是恢复硬盘数据，例如 文件被误删除 坏块等，需要手工恢复

在介绍了几种备份恢复方法后，我们进入topic，如何进行控制文件丢失恢复，先看一下数据库各种状态

5）SYS@LEO1>archivelog list 数据库处于非归档状态

Database logmode No Archive Mode

Automaticarchival Disabled

Archivedestination /u02/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online logsequence 71

Current logsequence 73

我们要先做一个RMAN全备，首先启动归档功能

[oracle@leonarding1oracle]$ pwd

/u02/app/oracle

[oracle@leonarding1oracle]$ mkdir archdata 创建一个归档日志目录

在ORACLE10g和11g版本，ORACLE默认的日志归档路径为闪回恢复区，但我们也可以修改为自己指定的目录路径

SYS@LEO1>altersystem set log_archive_dest_1='location=/u02/app/oracle/archdata' scope=both;

System altered.

SYS@LEO1>setlinesize 300 pagesize 999

设置的归档日志保存路径已经生效

SYS@LEO1>selectdest_name,destination,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';

DEST_NAME DESTINATION STATUS ERROR

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

LOG_ARCHIVE_DEST_1 /u02/app/oracle/archdata VALID

启动到mount状态，启动归档模式

SYS@LEO1>shutdownimmediate 关库

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SYS@LEO1>startupmount mount状态

ORACLE instancestarted.

Total SystemGlobal Area 471830528 bytes

Fixed Size 2214456 bytes

Variable Size 150996424 bytes

DatabaseBuffers 310378496 bytes

Redo Buffers 8241152 bytes

Database mounted.

SYS@LEO1>alterdatabase archivelog; 启动归档模式

Database altered.

SYS@LEO1>alterdatabase open; 打开数据库

Database altered.

注：凡是alter database操作都是对控制文件进行修改

凡是alter system 操作都是对参数文件进行修改

SYS@LEO1>altersystem switch logfile; 手工切换日志（不会触发检查点，自动切换会）

System altered.

SYS@LEO1>selectsequence#,name,archived,applied from v$archived_log; 查看已经归档的日志信息

SEQUENCE# NAME ARC APPLIED

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

73 /u02/app/oracle/archdata/1_73_813654649.dbf YES NO

操作系统层面查看，没有问题也生成了

[oracle@leonarding1archdata]$ ll

total 5624

-rw-r----- 1oracle asmadmin 5757952 Apr 25 21:28 1_73_813654649.dbf

SYS@LEO1>archivelog list

Database logmode Archive Mode 归档模式

Automaticarchival Enabled 自动归档启动

Archivedestination /u02/app/oracle/archdata 归档日志目录

Oldest online logsequence 72 旧在线日志序号

Next log sequenceto archive 74 下一个归档日志序号

Current logsequence 74 当前日志序号

下面我们就要进行RMAN全库备份了，在此之前还需要设置一下RMAN的环境变量

6）登陆RMAN

[oracle@leonarding1archdata]$ rman target sys/oracle

Recovery Manager:Release 11.2.0.1.0 - Production on Fri Apr 26 06:05:24 2013

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

connectedto target database: LEO1 (DBID=1692458681) 只有连接到目标库才能显示环境变量，这些元数据是存放在控制文件中的

显示当前RMAN的环境变量

RMAN> show all;

using targetdatabase control file instead of recovery catalog

RMAN configurationparameters for database with db_unique_name LEO1 are:

CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUPOPTIMIZATION OFF; # default

CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default

CONFIGURECONTROLFILE AUTOBACKUP OFF; # default

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

CONFIGURE DEVICETYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOGBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGUREMAXSETSIZE TO UNLIMITED; # default

CONFIGUREENCRYPTION FOR DATABASE OFF; # default

CONFIGUREENCRYPTION ALGORITHM 'AES128'; # default

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

CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default

创建RMAN默认备份介质保存目录/u02/app/oracle/backup

[oracle@leonarding1oracle]$ mkdir backup

RMAN> configure channel device type disk format'/u02/app/oracle/backup/DB_%U';

new RMANconfiguration parameters:

CONFIGURE CHANNELDEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';

new RMANconfiguration parameters are successfully stored 新man配置参数生效

配置控制文件自动备份并保存到/u02/app/oracle/backup/control目录

[oracle@leonarding1backup]$ mkdir control

[oracle@leonarding1control]$ pwd

/u02/app/oracle/backup/control

RMAN> configure controlfile autobackup on; 启动控制文件自动备份

new RMANconfiguration parameters:

CONFIGURECONTROLFILE AUTOBACKUP ON;

new RMANconfiguration parameters are successfully stored

RMAN> configure controlfile autobackup format for device type diskto '/u02/app/oracle/backup/control/cf_%F';

new RMANconfiguration parameters: 配置控制文件自动备份目录和格式

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';

new RMANconfiguration parameters are successfully stored

调整备份介质保留期为7天

RMAN> configureretention policy to recovery window of 7 days;

new RMANconfiguration parameters:

CONFIGURERETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMANconfiguration parameters are successfully stored

显示配置后RMAN环境变量

RMAN> show all;

RMAN configurationparameters for database with db_unique_name LEO1 are:

CONFIGURERETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUPOPTIMIZATION OFF; # default

CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default

CONFIGURECONTROLFILE AUTOBACKUP ON;

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';

CONFIGURE DEVICETYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGUREARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURECHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';

CONFIGUREMAXSETSIZE TO UNLIMITED; # default

CONFIGUREENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTIONALGORITHM 'AES128'; # default

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

CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default

带颜色的就是我们刚刚修改过的变量

7）启动RMAN的压缩备份功能对数据库进行全备Oracle10g只压缩RMAN元数据11g真正压缩了数据

backup as compressed backupset full database format 命令行中直接指定压缩选项即可

'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn'include current controlfile

plus

archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input; 全部备份完之后，删除备份过的旧归档日志

如果我们想使用默认通道默认配置备份一次数据库，同时删除备份过的归档日志，那么命令为

RMAN> backup ascompressed backupset full database include current controlfile plus archivelogdelete all input;

Starting backup at26-APR-13 备份时间

current logarchived

allocated channel:ORA_DISK_1 默认通道磁盘

channelORA_DISK_1: SID=140 device type=DISK

channelORA_DISK_1: starting compressed archived log backup set 先压缩备份的归档日志

channel ORA_DISK_1:specifying archived log(s) in backup set 备份了73 74归档日志

input archived logthread=1 sequence=73 RECID=1 STAMP=813706084

input archived logthread=1 sequence=74 RECID=2 STAMP=813739820

channelORA_DISK_1: starting piece 1 at 26-APR-13 启动备份片

channelORA_DISK_1: finished piece 1 at 26-APR-13 完成备份片

piece handle= /u02/app/oracle/backup/DB_01o81bpd_1_1 tag=TAG20130426T065020 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒

channelORA_DISK_1: deleting archived log(s) 删除已备份的归档日志73 74

archived log filename=/u02/app/oracle/archdata/1_73_813654649.dbf RECID=1 STAMP=813706084

archived log filename=/u02/app/oracle/archdata/1_74_813654649.dbf RECID=2 STAMP=813739820

Finished backup at26-APR-13

Starting backup at26-APR-13

using channelORA_DISK_1

channelORA_DISK_1: starting compressed full datafile backup set 再压缩备份数据文件

channelORA_DISK_1: specifying datafile(s) in backup set 指定备份如下数据文件

input datafilefile number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafilefile number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafilefile number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafilefile number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafilefile number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf

channelORA_DISK_1: starting piece 1 at 26-APR-13 启动备份片

channelORA_DISK_1: finished piece 1 at 26-APR-13 完成备份片

piece handle= /u02/app/oracle/backup/DB_02o81bpf_1_1 tag=TAG20130426T065022 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:01:45备份完成用时1分45秒

channelORA_DISK_1: starting compressed full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

includingcurrent control file in backupset 这个是备份数据文件的同时包含备份控制文件

channelORA_DISK_1: starting piece 1 at 26-APR-13

channel ORA_DISK_1:finished piece 1 at 26-APR-13

piece handle= /u02/app/oracle/backup/DB_03o81bso_1_1 tag=TAG20130426T065022 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒

Finished backup at26-APR-13

Starting backup at26-APR-13

current logarchived

using channelORA_DISK_1

channelORA_DISK_1: starting compressed archived log backup set

channelORA_DISK_1: specifying archived log(s) in backup set

input archived logthread=1 sequence=75 RECID=3 STAMP=813739930 备份75归档日志

channel ORA_DISK_1:starting piece 1 at 26-APR-13

channelORA_DISK_1: finished piece 1 at 26-APR-13

piece handle= /u02/app/oracle/backup/DB_04o81bsq_1_1 tag=TAG20130426T065210 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 备份完成用时1秒

channelORA_DISK_1: deleting archived log(s) 删除已备份的归档日志75

archived log filename=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=3 STAMP=813739930

Finished backup at26-APR-13

Starting ControlFile and SPFILE Autobackup at 26-APR-13 启动控制文件和参数文件自动备份

piece handle= /u02/app/oracle/backup/control/cf_c-1692458681-20130426-00 comment=NONE

Finished ControlFile and SPFILE Autobackup at 26-APR-13 完成自动备份

在操作系统上都可以找到对应的备份集并且已经删除了备份过的旧归档日志

[oracle@leonarding1backup]$ pwd

/u02/app/oracle/backup

[oracle@leonarding1backup]$ ll

total 249468

drwxr-xr-x 2oracle oinstall 4096 Apr 26 06:52control

-rw-r-----1 oracle asmadmin 2790912 Apr 26 06:50DB_01o81bpd_1_1

-rw-r-----1 oracle asmadmin 251551744 Apr 26 06:52 DB_02o81bpf_1_1

-rw-r-----1 oracle asmadmin 1097728 Apr 26 06:52DB_03o81bso_1_1

-rw-r-----1 oracle asmadmin 7168 Apr 26 06:52DB_04o81bsq_1_1

[oracle@leonarding1 backup]$ cd control/

[oracle@leonarding1control]$ ll

total 9600

-rw-r-----1 oracle asmadmin 9830400 Apr 26 06:52 cf_c-1692458681-20130426-00

[oracle@leonarding1archdata]$ pwd

/u02/app/oracle/archdata

[oracle@leonarding1archdata]$ ll 归档日志全没有了

total 0

新的归档日志是从76号开始，75号之前都已经备份并删除

SYS@LEO1>archivelog list

Database logmode Archive Mode

Automaticarchival Enabled

Archivedestination /u02/app/oracle/archdata

Oldest online logsequence 74

Next log sequenceto archive 76

Current logsequence 76

到此我们的备份准备已经完成，稍微休息一下：）坐车上班班

8）SYS@LEO1>selectstatus from v$instance; 检查数据库状态

STATUS

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

OPEN

LEO1@LEO1>showparameter control_files 我们检查一下控制文件个数

NAME TYPE VALUE

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

control_files string /u02/app/oracle/oradata/LEO1/control01.ctl,

/u02/app/oracle/oradata/LEO1/control02.ctl

[oracle@leonarding1LEO1]$ ll 操作系统上也是2个没有问题

total 2618136

-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:01control01.ctl

-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:01control02.ctl

一般控制文件丢失大多数都是被误删除了，用rm 命令删除control01.ctl文件

[oracle@leonarding1trace]$ pwd

/u02/app/oracle/diag/rdbms/leo1/LEO1/trace

[oracle@leonarding1 trace]$ tail -10falert_LEO1.log 实时监控告警日志看看有什么变化

Fri Apr 2606:50:20 2013

Thread 1 advancedto log sequence 75 (LGWR switch)

Current log# 3 seq# 75 mem# 0:/u02/app/oracle/oradata/LEO1/redo03.log

Archived Log entry2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:

Fri Apr 2606:52:10 2013

ALTER SYSTEMARCHIVE LOG

Fri Apr 2606:52:10 2013

Thread 1 advancedto log sequence 76 (LGWR switch)

Current log# 1 seq# 76 mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log

Archived Log entry3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:

[oracle@leonarding1LEO1]$ rm control01.ctl 模拟control01文件丢失的场景

LEO1@LEO1>createtablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10mautoextend off;

create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off

l 我们创建一个表空间，此时突然报错

ERROR at line 1:

ORA-00210:cannot open the specified control file 不能打开指定的控制文件

ORA-00202:control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' 控制文件丢失

ORA-27041:unable to open file 无法打开这个文件

Linux-x86_64Error: 2: No such file or directory 找不到这个文件，好恐怖bless，赶紧看看alert日志

Additionalinformation: 3

Alert_LEO1.log日志内容

create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off

ORA-210 signalledduring: create tablespace test datafile'/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off...

Fri Apr 2609:14:15 2013

Errors in file/u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_m000_7975.trc:

ORA-00210: cannotopen the specified control file

ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unableto open file

Linux-x86_64Error: 2: No such file or directory

Additionalinformation: 3

是不是和上面报的错误信息一样啊，由于是我们自己搞的鬼，所以我们明白是怎么回事，如果在生产库上就要首先查看日志信息进行分析啦，好了现在我们开始修复吧->start on