oracle Control Files 杂谈-2

在上一篇文件里面，我列举了2种控制文件的方法，其中有一个备份文本文件的实现，里面都是脚本。今天我就想介绍一下，脚本里面的内容。文件里面主要有二部分。

**********************省去N行**************************************
--     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 "PROD" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
	
**********************省去N行**************************************
--     Set #2. RESETLOGS 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.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- 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
**********************省去N行**************************************

第一步部分(Set #1. NORESETLOGS case):这部分脚本是不用resetlog的，一般在你只是丢了控制文件的时候用。

第二步部分(Set #2. RESETLOGS case)：主要是不完全恢复时候用到的。

从我个人的经验来看，现在都是这2种方式现在都不怎么常用了，都是用rman来管理备份和恢复的。但是从这些脚本内容来看，能说明一个oracle恢复的思路。

下面我就给大家演示一个只有数据文件备份，在数据库运行中数据文件&控制文件&在线日志文件都误删，通过上面第二个脚本(Set #2. RESETLOGS case)如何恢复一个数据库的。先看环境：

Last login: Sun Jun  1 07:42:27 2014 from 10.10.27.1
[[email protected] ~]# su - oracle
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 2 20:41:28 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.1.0/db_1/dbs/arch
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15
SQL> 
SQL> conn sec/sec
Connected.
SQL> 
SQL> 
SQL> create table my_insert (id number,area_code varchar2(40)) tablespace users; 

Table created.

SQL> insert into my_insert values(1,'sicheng1');

1 row created.

SQL> insert into my_insert values(2,'sicheng2');

1 row created.

SQL> insert into my_insert values(3,'sicheng3');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile; 

System altered.

SQL> conn / as sysdba                                                             
Connected.
SQL> alter database backup controlfile to trace as '/home/oracle/controlfile.txt';

防止数据文件有变动。先备份下弄出一个控制文件备份。

在这之前，我已经把所有的归档日志删除了，在上面运行完切换日志的动作后，在归档日志的目录下面，就应该有一个归档日志。

[[email protected] arch]$ cd /u01/app/oracle/product/11.1.0/db_1/dbs/arch
[[email protected] arch]$ ll
total 0
[[email protected] arch]$ 
[[email protected] arch]$ 
[[email protected] arch]$ ll
total 984
-rw-r----- 1 oracle oinstall 1001472 Jun  2 20:46 1_15_834821443.dbf
[[email protected] arch]$

我这边用最原始的冷备份方式，备份数据文件

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

先关闭数据库，然后才开始冷备份

[[email protected] u01]$ mkdir backup
[[email protected] u01]$ 
[[email protected] u01]$ 
[[email protected] u01]$ cd /u01/app/oracle/oradata/PROD/
[[email protected] PROD]$ ll\
> 
total 1949228
-rw-r----- 1 oracle oinstall   9748480 Jun  2 20:47 control01.ctl
-rw-r----- 1 oracle oinstall   9748480 Jun  2 20:47 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jun  2 20:47 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jun  2 20:47 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jun  2 20:41 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jun  2 20:46 redo03.log
-rw-r----- 1 oracle oinstall 534781952 Jun  2 20:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jun  2 20:47 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jun  1 07:44 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 Jun  2 20:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 Jun  2 20:47 users01.dbf
[[email protected] PROD]$ 
[[email protected] PROD]$ 
[[email protected] PROD]$ 
[[email protected] PROD]$ cp *.dbf /u01/backup/
[[email protected] PROD]$ 
[[email protected] PROD]$ cd /u01/backup/
[[email protected] backup]$ ll
total 1776372
-rw-r----- 1 oracle oinstall 362422272 Jun  2 20:49 example01.dbf
-rw-r----- 1 oracle oinstall 534781952 Jun  2 20:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jun  2 20:50 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jun  2 20:50 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 Jun  2 20:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 Jun  2 20:50 users01.dbf
[[email protected] backup]$

这样我就把数据文件全部备份了下来。备份完毕后，开启数据库，做2条数据

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> 
SQL> conn sec/sec
Connected.
SQL> insert into my_insert values(4,'sicheng4');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile; 

System altered.

SQL> 
SQL> insert into my_insert values(5,'sicheng5');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

这次呢，我insert了2条id=4和5的数据，大家可以猜下，如果这个时候删除刚才提到的数据文件&控制文件&在线日志文件这3类所有的文件，两条记录谁还能恢复回来呢。删除之前，先看看归档目录

[[email protected] backup]$ cd /u01/app/oracle/product/11.1.0/db_1/dbs/arch
[[email protected] arch]$ ll
total 1272
-rw-r----- 1 oracle oinstall 1001472 Jun  2 20:46 1_15_834821443.dbf
-rw-r----- 1 oracle oinstall  289280 Jun  2 20:54 1_16_834821443.dbf
[[email protected] arch]$

因为在insert id=4的记录后，切换了在线日志，所有这个时候多了一个归档日志，换句话说，id=4的记录，在归档日志里面已经存在了。

好了，现在开始删除所有的文件了啊

[email protected] arch]$ cd /u01/app/oracle/oradata/PROD/
[[email protected] PROD]$ 
[[email protected] PROD]$ ll
total 1949228
-rw-r----- 1 oracle oinstall   9748480 Jun  2 20:56 control01.ctl
-rw-r----- 1 oracle oinstall   9748480 Jun  2 20:56 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jun  2 20:54 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jun  2 20:54 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jun  2 20:56 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jun  2 20:52 redo03.log
-rw-r----- 1 oracle oinstall 534781952 Jun  2 20:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jun  2 20:54 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jun  2 20:52 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 Jun  2 20:54 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 Jun  2 20:54 users01.dbf
[[email protected] PROD]$ 
[[email protected] PROD]$ rm *.*
[[email protected] PROD]$ ll
total 0
[[email protected] PROD]$

文件没了，数据库就停了，开始准备恢复吧

SQL>       
SQL> shutdown abort    
ORACLE instance shut down.
SQL> 
SQL> 
SQL>

无论怎么恢复，要有个起点吧，这个时候，数据文件我们就用刚才备份的。

[[email protected] PROD]$ cd /u01/backup/
[[email protected] backup]$ ll
total 1776372
-rw-r----- 1 oracle oinstall 362422272 Jun  2 20:49 example01.dbf
-rw-r----- 1 oracle oinstall 534781952 Jun  2 20:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jun  2 20:50 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jun  2 20:50 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 Jun  2 20:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 Jun  2 20:50 users01.dbf
[[email protected] backup]$ 
[[email protected] backup]$ cp *.dbf /u01/app/oracle/oradata/PROD
[[email protected] backup]$ 
[[email protected] backup]$ 
[[email protected] backup]$ cd /u01/app/oracle/oradata/PROD
[[email protected] PROD]$ ll
total 1776372
-rw-r----- 1 oracle oinstall 362422272 Jun  2 21:00 example01.dbf
-rw-r----- 1 oracle oinstall 534781952 Jun  2 21:00 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jun  2 21:00 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jun  2 21:00 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 Jun  2 21:01 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 Jun  2 21:01 users01.dbf
[[email protected] PROD]$

把数据文件复制回去，这个时候我们还少控制文件盒在线日志文件。这个只能恢复控制文件了，参考Set #2. RESETLOGS case，先造个控制文件好了

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/PROD/system01.dbf',
 14    '/u01/app/oracle/oradata/PROD/sysaux01.dbf',
 15    '/u01/app/oracle/oradata/PROD/undotbs01.dbf',
 16    '/u01/app/oracle/oradata/PROD/users01.dbf',
 17    '/u01/app/oracle/oradata/PROD/example01.dbf'
 18  CHARACTER SET WE8MSWIN1252
 19  ;

Control file created.

[[email protected] PROD]$

看下系统路径，控制文件回来了。

[[email protected] PROD]$ 
[[email protected] PROD]$ 
[[email protected] PROD]$ ll
total 1796084
-rw-r----- 1 oracle oinstall  10076160 Jun  2 21:02 control01.ctl
-rw-r----- 1 oracle oinstall  10076160 Jun  2 21:02 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jun  2 21:00 example01.dbf
-rw-r----- 1 oracle oinstall 534781952 Jun  2 21:00 sysaux01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jun  2 21:00 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jun  2 21:00 temp01.dbf
-rw-r----- 1 oracle oinstall  52436992 Jun  2 21:01 undotbs01.dbf
-rw-r----- 1 oracle oinstall  57679872 Jun  2 21:01 users01.dbf
[[email protected] PROD]$

继续看Set #2. RESETLOGS case里面的步骤，要recover database using backup controlfile 了

SQL> recover database using backup controlfile until cancel --在这里 我故意加上了until cancel 就是想自己决定恢复到哪里
ORA-00279: change 1212972 generated at 06/02/2014 20:47:53 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_16_834821443.dbf --这里提示需要这个日志文件(归档|在线)，这个我一看就知道在归档文件目录下，之间回车
ORA-00280: change 1212972 for thread 1 is in sequence #16


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1213269 generated at 06/02/2014 20:54:00 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_17_834821443.dbf  --数据库继续要1_17***的这个，我真的没有啊。。。
ORA-00280: change 1213269 for thread 1 is in sequence #17
ORA-00278: log file
'/u01/app/oracle/product/11.1.0/db_1/dbs/arch/1_16_834821443.dbf' no longer --好像是上面没有1_17就恢复到1_16
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL  --没有就输入cancel 到此为止了
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf'
  2       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> 
SQL> conn sec/sec
Connected.
SQL> 
SQL> select * from my_insert;

        ID AREA_CODE
---------- ----------------------------------------
         1 sicheng1
         2 sicheng2
         3 sicheng3
         4 sicheng4

SQL>

数据库起来了，为什么没有id=5的数据呢，大家知道为什么吧！因为insert id=5以后，虽然commit了，但是还在online redo log里面呢，我把online redo log一删除，自然就哪里也找不到了啊。

通过上面的恢复方式，我们可以清楚的了解数据库恢复步骤，其实rman也是这样恢复的，不过给我们省去了很多需要操作的地方。

其实大家仔细看看这个文件，还能发现很多好玩的东西，比如关于rman备份的记录，比如如果你数据库里面有read only 表空间，里面的东西都会随之相应变化的啊。

oracle Control Files 杂谈-2

继续阅读

ArcSDE 9.3.1对应Oracle的版本

MooseFS 3.0部署笔记(2)【原创】

Oracle目录结构与系统用户

路由器配置文件的备份与恢复

centos7.6 静默安装Oracle11.2.0.4（亲测成功）

windows 2008 R2下的 oracle 11204 遇到了oracle.exe 吃掉大部分windows 内存的问题

oracle11g 创建ASM实例

Oracle开发部门推荐的 AIX上的Oracle11.2.0.4 应该打的补丁-非psu

oracle expdp作业外表报错ORA-20011&KUP-11024&ORA-29913

ORACLE rac 11.2.0.4 for rhel7.8 upgrade to 19.11.1 报错ORA-29516处理

Elasticsearch经验之-GitHub故障处理近期代码检索中断（2013-2-4）

控制文件坏块处理方法

win7 64位 plsql 连不上 oracle11g 解决方案

oracle 在exp|imp|运行sql 中文乱码问题

阿里云centos6静默安装oracle11G

HP DL380服务器RAID信息丢失数应该如何进行数据恢复