一、Oracle 控制檔案
為二進制檔案，初始化大小由CREATE DATABASE指定,可以使用RMAN備份
記錄了目前資料庫的結構資訊,同時也包含資料檔案及日志檔案的資訊以及相關的狀态,歸檔資訊等等
在參數檔案中描述其位置，個數等等。通常采用分散放開，多路複用的原則。在mount階段被讀取，open階段一直被使用
維護資料庫一緻性(資料庫啟動時會比較控制檔案與聯機日志檔案中的ckpt,即起始scn号，如相等則正常啟動，否則需要媒體恢複)
一個控制檔案隻能屬于一個資料庫
控制檔案的任意修改将寫入到初始化參數中指定的所有控制檔案中，讀取時則僅讀取第一個控制檔案
控制檔案隻能連接配接一個資料庫，控制檔案的大小一般不要超過MB,最多為個，最少一個，互為鏡像
控制檔案中包含的内容
資料庫的名字、ID、建立的時間戳
表空間的名字
聯機日志檔案、資料檔案的位置、個數、名字
聯機日志的Sequence号碼
檢查點的資訊
撤銷段的開始或結束
歸檔資訊
備份資訊
二、檢視控制檔案的相關資訊
1.使用相關視圖來檢視
V$CONTROLFILE --列出執行個體中所有控制檔案的名字及狀态資訊
V$PARAMETER --列出所有參數的位置及狀态資訊
V$CONTROLFILE_RECORD_SECTION --列出控制檔案中記錄的部分資訊
SHOW PARAMETER CONTROL_FILES --列出控制檔案的名字、狀态、位置等
2.使用STRINGS指令來檢視控制檔案中的具體内容
3.備份控制檔案到平面檔案(然後檢視控制檔案中的具體内容)
SQL> alter database backup controlfile to trace as '/u01/app/oracle/ctl.txt';
Database altered.
4.轉儲控制檔案内容(檢視控制檔案中的具體内容)
alter session set events 'immediate trace name CONTROLF level 12'; level表示級别
或 --level1 塊頭的内容 --level2 資料檔案内容 --levle 10 |12 所有内容
oradebug setmypid
oradebug dump controlf 12
SQL> alter system set events 'immediate trace name controlf level 10';
System altered.
SQL> show parameter user_dump;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/orcl/udu
mp
三、控制檔案的管理
規劃原則：多路複用,建議存放到不同的磁盤或同一磁盤不同的分區
個數與位置及狀态管理:
檢視控制檔案的狀态是否與參數定義中的相吻合,當資料庫發生結構修改時，将修改内容同時寫入控制檔案
備份管理
恢複管理
建立控制檔案語句
spfile或pfile都可以實作對控制檔案的個數及位置管理
spfile步驟
修改spfile參數中的control_files -- alter system ... scope = spfile | both |memory
一緻性關閉資料庫
增加或減少控制檔案(cp or mv)
啟動資料庫使用spfile
驗證結果
pfile步驟
修改pfile參數(vi或vim) 修改*.control_files＝......這一段
啟動資料庫使用pfile
--示範spfile修改控制檔案
SQL> show parameter control_file
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
--将控制檔案減少到一個
SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl' scope =spfile;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 88082196 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Database mounted.
--再次檢視參數檔案,已顯示為一個
ontrol01.ctl
--增加控制檔案(在nomount狀态下即可修改)
SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl',
2 '/u01/app/oracle/oradata/orcl/control02.ctl',
3 '/u01/app/oracle/oradata/orcl/control03.ctl'
4 scope = spfile;
--啟動時可以看到在執行個體階段出現了版本号不一緻的問題
SQL> startup force
ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version
1051 inconsistent with file '/u01/app/oracle/oradata/orcl/control02.ctl'
version 1049
--處理辦法,用版本号高的控制檔案覆寫版本号低的控制檔案
SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control02.ctl;
SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control03.ctl;
SQL> alter database mount;
SQL> alter database open;
Database altered.
對于控制檔案丢失的情況下,通過檢視參數檔案中設定,使用作業系統指令逐個檢視這些檔案是否存在
SQL> host ls /u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control01.ctl
SQL> host ls /u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
檢視控制檔案所在的目錄可用空間及控制檔案的大小,建議不要超過100MB
SQL> host ls /u01/app/oracle/oradata/orcl/c*.ctl -lh
-rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control01.ctl
-rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control02.ctl
-rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control03.ctl
SQL> ! df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 6.4G 3.9G 2.2G 64% /
/dev/sdd1 6.8G 3.7G 2.8G 58% /u01
/dev/sdc2 1.2G 34M 1.1G 3% /home
/dev/sdc1 760M 17M 704M 3% /tmp
/dev/sda1 456M 18M 415M 5% /boot
tmpfs 450M 0 450M 0% /dev/shm
none 450M 104K 450M 1% /var/lib/xenstored
控制檔案的備份
熱備:
alter database backup controlfile to '<dir>'; --熱備份控制檔案
alter database backup controlfile to trace as '<dir>' ;--得到建立控制檔案的腳本
RMAN:
backup current controlfile;
backup database include current controlfile;
-- 或者設定RMAN 為自動備份
RMAN > configure controlfile autobackup on;
--示範備份
--用于歸檔模式下的恢複，直接覆寫到控制檔案
SQL> alter database backup controlfile to '/u01/app/oracle/control.bak';
--用于重建控制檔案
SQL> alter database backup controlfile to trace as'/u01/app/oracle/recreate_controlfile.txt';
--使用RMAN備份
RMAN> connect target /;
connected to target database: ORCL (DBID=1242732291)
RMAN> backup current controlfile; --handle為備份檔案的路徑
Starting backup at 23-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-10
channel ORA_DISK_1: finished piece 1 at 23-MAY-10
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/
2010_05_23/o1_mf_ncnnf_TAG20100523T131841_5zkgon2l_.bkp tag=TAG20100523T131841 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 23-MAY-10
RMAN> backup database include current controlfile;
channel ORA_DISK_1: sid=141 devtype=DISK
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tbs1_1.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/orcl/tbs1_2.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
2010_05_23/o1_mf_nnndf_TAG20100523T132647_5zkh4sk2_.bkp tag=TAG20100523T132647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
including current SPFILE in backupset
2010_05_23/o1_mf_ncsnf_TAG20100523T132647_5zkhh5st_.bkp tag=TAG20100523T132647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:14
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/10g/dbs/snapcf_orcl.f'; # default
RMAN> configure controlfile autobackup on; --将控制檔案自動備份功能置為on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
控制檔案的恢複管理
控制檔案版本不一緻的問題
用較新版本的控制檔案覆寫舊版本的控制檔案
直接修改參數control_file
丢失問題
歸檔模式下
當歸檔日志全的時候,先做全備,然後使用備份的控制檔案恢複即可
當歸檔日志不全的時候,先做全備,然後建立新的控制檔案即可
非歸檔模式下
先做全備,然後建立新的控制檔案即可
建立控制檔案語句
資料庫處于mount及open狀态
執行alter database backup controlfile to trace as '<dir>';得到建立語句
注意[no]archievelog [no]resetlogs 兩個參數的差別
版本不一緻示範
SQL> startup
--控制檔案部分丢失的示範，原本有兩個控制檔案，丢失一個
--處理辦法：
1.将存在的控制檔案複制到目的路徑并更改控制檔案名字為正确的控制檔案名稱
2.修改控控檔案參數将丢失的控制檔案去掉(一般不建議使用)
SQL> alter system set control_files = ' /u01/app/oracle/oradata/orcl/c ontrol01.ctl',
2 '/u01/app/oracle/oradata/orcl/control02.ctl' scope = spfile;
Variable Size 75499284 bytes
Database Buffers 171966464 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@robinson ~]$ tail -n 100 /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
..........................
Tue Jun 8 19:03:42 2010
starting up 1 shared server(s) ...
MMON started with pid=11, OS id=4557
CJQ0 started with pid=10, OS id=4555
Tue Jun 8 19:03:44 2010
ALTER DATABASE MOUNT
ORA-00202: control file: '/u01/app/oracle/10g/dbs/ /u01/app/oracle/oradata/orcl/c ontrol01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Jun 8 19:03:47 2010
ORA-205 signalled during: ALTER DATABASE MOUNT...
--從警告日志中得知，檔案名為c ontrol01.ctl的檔案不存在，故将其改為正确的檔案名
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl',
Variable Size 83887892 bytes
Database Buffers 163577856 bytes
Database opened.
非歸檔模式下，當所有的控制檔案都丢失，隻能重建控制檔案來解決
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
[oracle@robinson ~]$tail -n 100 /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
Thu Jul 15 12:13:15 2010
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
--重建控制檔案主要有三個需要考慮的是
--搞清各個日志檔案的大小及位置
--搞清各個資料檔案的位置
--設定正确的字元集
SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NOARCHIVELOG NORESTLOGS
2 MAXLOGFILES 16
3 MAXLOGMEMBER 3
4 MAXDATAFILES 20
5 MAXINSTANCES 8
6 MAXLOGHISTORY 3
7 LOGFILE
8 GROUP 1(
9 'u01/app/oracle/oradata/orcl/redo1.log',
10 '/u01/app/oracle/oradata/orcl/redo01.log'
11 ) SIZE 50M,
12 GROUP 2(
13 '/u01/app/oracle/oradata/orcl/redo2.log',
14 '/u01/app/oracle/oradata/orcl/redo02.log'
15 ) SIZE 50M,
16 GROUP 3(
17 '/u01/app/oracle/oradata/orcl/redo3.log',
18 '/u01/app/oracle/oradata/orcl/redo03.log'
19 ) SIZE 100M
20 DATAFILE
21 '/u01/app/oracle/oradata/orcl/tbs1_2.dbf',
22 '/u01/app/oracle/oradata/orcl/tbs1_1.dbf',
23 '/u01/app/oracle/oradata/orcl/example01.dbf',
24 '/u01/app/oracle/oradata/orcl/users01.dbf',
25 '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
26 '/u01/app/oracle/oradata/orcl/system01.dbf',
27 '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
28 CHARACTER SET WE8ISO8859P1;
CREATE CONTROLFILE REUSE DATABASE "orcl" NOARCHIVELOG NORESTLOGS
*
ERROR at line 1:
ORA-01967: invalid option for CREATE CONTROLFILE
SQL> save /u01/app/oracle/oradata/rectl.sql;
Created file /u01/app/oracle/oradata/rectl.sql
SQL> ho vim /u01/app/oracle/oradata/rectl.sql
SQL> @/u01/app/oracle/oradata/rectl.sql
Control file created.
--下面給出正确執行後的語句内容
SQL> host cat /u01/app/oracle/oradata/rectl.sql
CREATE CONTROLFILE REUSE DATABASE "orcl" NOARCHIVELOG NORESETLOGS
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 20
MAXINSTANCES 8
MAXLOGHISTORY 3
LOGFILE
GROUP 1(
'/u01/app/oracle/oradata/orcl/redo01.log'
) SIZE 50M,
GROUP 2(
'/u01/app/oracle/oradata/orcl/redo2.log',
'/u01/app/oracle/oradata/orcl/redo02.log'
GROUP 3(
'/u01/app/oracle/oradata/orcl/redo3.log',
'/u01/app/oracle/oradata/orcl/redo03.log'
) SIZE 100M
DATAFILE
'/u01/app/oracle/oradata/orcl/tbs1_2.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf'
CHARACTER SET WE8ISO8859P1
/
--将資料庫切換到open 狀态
SQL> select * from dual;
D
-
X
--檢視已經新産生了控制檔案
SQL> host ls $ORACLE_BASE/oradata/orcl/control* -l --可以看到新增了控制檔案control01.ctl和control02.ctl
-rw-r----- 1 oracle oinstall 6012928 Jul 15 12:58 /u01/app/oracle/oradata/orcl/control01.ctl
-rw------- 1 oracle oinstall 7389184 Jul 15 12:04 /u01/app/oracle/oradata/orcl/control01.ctl.bak
-rw-r----- 1 oracle oinstall 6012928 Jul 15 12:58 /u01/app/oracle/oradata/orcl/control02.ctl
歸檔模式下控制檔案全部丢失的處理
--首先将資料庫切換到歸檔模式
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open ;
--檢視歸檔的狀态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
--備份控制檔案
SQL> alter database backup controlfile to '/u01/app/oracle/oradata/orcl/rectl.bak';
SQL> create table tb_temp(id int,col1 varchar2(20));
Table created.
SQL> insert into tb_temp select 1,'Robinson' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; --切換日志
SQL> archive log list; --日志切換後sequence由變成
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
--模拟控制檔案全部丢失
SQL> show parameter control
oradata/orcl/control02.ctl
--檢視實體控制檔案是否存在
SQL> ho ls /u01/app/oracle/oradata/orcl/contr*
ls: /u01/app/oracle/oradata/orcl/contr*: No such file or directory
--建議先對資料庫作備份再将備份的控制檔案複制到參數檔案中指定的位置
SQL> ho cp /u01/app/oracle/oradata/orcl/rectl.bak/u01/app/oracle/oradata/orcl/control01.ctl
SQL> ho cp /u01/app/oracle/oradata/orcl/rectl.bak/u01/app/oracle/oradata/orcl/control02.ctl
alter database open
*
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-01109: database not open
SQL> alter database open resetlogs;
SQL> select * from tb_temp; --表成功被恢複
ID COL1
---------- --------------------
1 Robinson
SQL> drop table tb_temp purge;
Table dropped.
SQL> archive log list; --日志的sequence号被置為
Next log sequence to archive 1
Current log sequence 1
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)