2013年4月27日 星期六
10:33
1、控制文件的功能和特点
1) 【定义数据库当前物理状态】
2) 【维护数据的一致性】 如果控制文件中的检查点与数据文件中的一致，则说明数据一致，可以启动到open状态
3) 【很小的二进制文件】
4)【控制文件包括静态部分、动态部分 当使用rman备份数据库的时候, 控制文件会记录catalog信息, 动态部分会增大 】
查看数据一致性的方法：
SQL> select file#,checkpoint_change# from v$datafile;【从控制文件读取】 以控制文件为主
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 172373
2 172373
3 172373
4 172373
SQL> select file#,checkpoint_change# from v$datafile_header;【从数据文件读取】
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 172373
2 172373
3 172373
4 172373
3)二进制文件
4)在mount阶段被读取
5)记录备份恢复信息
----------查看database控制文件位置：
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/test/control01.ctl
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl
SYS@ prod>select * from v$rollname; 查看undo 段
USN NAME
---------- --------------------------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
2、控制文件的内容
SQL> alter database backup controlfile to trace;【将控制文件备份成trace文件】
建议：只要对数据库的物理结构进行修改，都应该进行控制文件的备份或者转储
V$DATABASE 查看数据库信息
V$LOGFILE,V$LOG 查看日志信息
V$DATAFILE
V$CONTROLFILE 查看控制文件
SQL> show parameter dump
user_dump_dest string /u01/app/oracle/admin/orcl/udump
重建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/disk2/orcl/oradata/redo01a.log',
'/disk1/orcl/oradata/redo01b.log'
) SIZE 10M,
GROUP 2 (
'/disk2/orcl/oradata/redo02a.log',
'/disk1/orcl/oradata/redo02b.log'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/user01.dbf',
'/u01/app/oracle/oradata/orcl/undo01.dbf',
CHARACTER SET ZHS16GBK;
------------利用ORACLE EVENT 转储 控制文件
SQL> alter session set events 'immediate trace name controlf level 12';
3、控制文件多元化（复制多个控制文件并存放到不同的磁盘 注意 控制文件3个足够不宜太多 如果太多的话oracle 实时同步控制文件 需要做物理i/o 消耗系统性能）
spfile：查看控制文件信息并修改control_file参数，将新增的控制文件路径写上；关闭实例，复制控制文件到相应路径；重新启动实例。
pfile：关闭实例，修改参数文件init；拷贝控制文件；重新启动实例。
修改spfile文件，增加控制文件
1)查看控制文件信息和spfile 信息
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/control01.ctl
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora
2)修改control_files 参数
SQL> alter system set control_files= '/u01/app/oracle/oradata/orcl/control01.ctl','/disk1/orcl/oradata/control02.ctl' scope=spfile;
System altered.
3)关闭instance ，拷贝controlfile
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@oracle ~]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /disk1/orcl/oradata/control02.ctl
4)startup 启动实例
SQL> startup
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/disk1/orcl/oradata/control02.ctl
修改pfile文件增加控制文件：
1)关闭实例，修改init 文件
SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ vi initorcl.ora
修改如下
control_files = /disk1/orcl/oradata/control01.ctl,/disk1/orcl/oradata/control02.ctl,/disk2/orcl/oradata/control03.ctl
2)拷贝controlfile
[oracle@oracle dbs]$ cp $ORACLE_BASE/oradata/orcl/control01.ctl /disk1/orcl/oradata/control02.ctl
[oracle@oracle dbs]$ cp $ORACLE_BASE/oradata/orcl/control01.ctl /disk2/orcl/oradata/control03.ctl
3)startup
SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora;
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/disk1/orcl/oradata/control02.ctl
/disk2/orcl/oradata/control03.ctl
SQL> create spfile from pfile;
File created.
4、控制文件查看：v$controlfile 、show parameter controlfile、 v$parameter
5、控制文件备份
1)trace :用于控制文件重建 （生成的trace 文件在udump)
SQL> alter database backup controlfile to trace;
Database altered.
2)binary 文件 ：控制文件的备份
SQL> alter database backup controlfile to '/disk1/orcl/oradata/control.bak';
Database altered.
6、控制文件恢复
单个文件丢失：
[oracle@oracle dbs]$ rm /disk2/orcl/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:14:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
通过告警日志获得信息：
ALTER DATABASE MOUNT
Mon Aug 1 06:14:57 2011
ORA-00202: control file: '/disk2/orcl/oradata/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> !
[oracle@oracle dbs]$ cp /disk1/orcl/oradata/control02.ctl /disk2/orcl/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:15:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:15:37 SQL> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/disk1/orcl/oradata/control02.ctl
/disk2/orcl/oradata/control03.ctl
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
案例
所有的文件丢失：
【先改变数据库的物理文件，然后备份到trace和二进制文件，然后又对数据进行修改，然后删除所有的控制文件】
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle dbs]$ rm /u01/app/oracle/oradata/orcl/control01.ctl
[oracle@oracle dbs]$ rm /disk1/orcl/oradata/control02.ctl
[oracle@oracle dbs]$ rm /disk2/orcl/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:17:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
告警日志：
ALTER DATABASE MOUNT
Mon Aug 1 06:17:54 2011
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Aug 1 06:17:54 2011
利用trace 文件重建
在nomount 状态 一定要正常关库 .
SQL>CREATE CONTROLFILE REUSE DATABASE "orclgps" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01a.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02a.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/rtbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/user01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/indx01.dbf',
'/u01/app/oracle/oradata/orcl/OLTP01.DBF'
CHARACTER SET ZHS16GBK
Control file created.
NORESETLOGS 不会重置日志文件可以做完全恢复
Set 如果修改数据库名字修要添加 SET DATABASE ‘DATABASENAME’ RESETLOGS 注意修改初始化参数文件 db_name
SQL> alter database open resetlogs;
Database altered.
非正常关库，重建控制文件
SQL> insert into scott.test values (10);
1 row created.
SQL> insert into scott.test values (11);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; 产生增量检查点
System altered.
SQL> shutdown abort ; database 异常关闭
ORACLE instance shut down.
[oracle@work dbs]$ rm /disk1/oradata/test/control02.ctl
[oracle@work dbs]$ rm /disk2/oradata/test/control03.ctl
[oracle@work dbs]$ rm /u01/app/oracle/oradata/test/control01.ctl
[oracle@work dbs]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 27 00:44:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes
Variable Size 88082628 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> @/home/oracle/control.sql ;
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 20
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/test/redo01a.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/test/redo02a.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/test/redo03a.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/test/system01.dbf',
'/u01/app/oracle/oradata/test/rtbs01.dbf',
'/u01/app/oracle/oradata/test/sysaux01.dbf',
'/u01/app/oracle/oradata/test/users01.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
325588
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
325588
325588
325588
325588
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
325588
325588
325588
325588
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf'
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 57
Current log sequence 59
SQL> recover database until cancel; 或者
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 325588 generated at 08/27/2011 00:43:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_59_759630389.dbf
ORA-00280: change 325588 for thread 1 is in sequence #59
00:45:43 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf'
ORA-01112: media recovery not started
--------通过当前redo 日志进行database recover
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 58 10485760 1 NO INACTIVE 325585 27-AUG-11
3 1 57 10485760 1 NO INACTIVE 325583 27-AUG-11
2 1 59 10485760 1 NO CURRENT 325588 27-AUG-11
SQL> select group# ,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
2 /u01/app/oracle/oradata/test/redo02a.log
1 /u01/app/oracle/oradata/test/redo01a.log
3 /u01/app/oracle/oradata/test/redo03a.log
SQL> recover database until cancel;
ORA-00279: change 325588 generated at 08/27/2011 00:43:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_59_759630389.dbf
ORA-00280: change 325588 for thread 1 is in sequence #59
00:46:23 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/test/redo02a.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
9
10
11
10 rows selected.
TRACE文件、BAK文件、dump文件的区别
1、trace文件里面是控制文件的重建脚本，当数据库的物理结构发生变化时就要trace一下，否则会丢失，恢复时要使用最新的。
2、bak文件是controlfile的副本，里面包含检查点等所有的信息，用他备份会出现数据不一致的情况，数据库不能打开到open状态。
3、dump文件是用来查看controlfile里面的内容的，不能用来恢复控制文件。
转载于:https://www.cnblogs.com/iyoume2008/p/7524641.html
相关资源：Oracle Database 9i10g11g编程艺术：深入数据库体系结构（第2版）--详细书签版