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資訊丢失數應該如何進行資料恢複