oracle控制文件多路复用及重建

前端之家收集整理的这篇文章主要介绍了oracle控制文件多路复用及重建前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

管理控制文件
在mount状态第一次被加载,open之后时刻在变
如果丢失,需要还原和恢复
生产库推荐控制文件至少2个
记录数据库的物理信息,属性,行为的二进制文件
数据库正常工作至少需要一个控制文件,最多可以同时指定8个控制文件
控制文件的位置和数量由初始化参数决定(control_files)
所有的控制文件都是镜像关系

查看控制文件
nomount:
show parameter control_files
mount:
select name from v$controlfile;

控制文件种所包含的记录片段有哪些?
select TYPE,RECORD_SIZE,RECORDS_TOTAL,RECORDS_USED from v$controlfile_record_section;

ORA-00205: error in identifying control file,check alert log for more info

show parameter background_dump_dest

控制文件多路复用(多元化): mount时使用多个控制文件
alter system set control_files=
'/u01/app/oracle/oradata/madrid/control01.ctl',
'/u01/app/oracle/oradata/madrid/control02.ctl'
scope=spfile;

shut immediate
cp /u01/app/oracle/oradata/madrid/control02.ctl /u01/app/oracle/oradata/madrid/control01.ctl
startup

移动控制文件
alter system set control_files=
'/u01/app/oracle/oradata/madrid/control01.ctl',
'/u02/app/oracle/oradata/madrid/control02.ctl'
scope=spfile;

shut immediate
mv /u01/app/oracle/oradata/madrid/control02.ctl /u02/app/oracle/oradata/madrid/control02.ctl
startup

控制文件改名:
alter system set control_files=
'/u01/app/oracle/oradata/madrid/ctl01',
'/u02/app/oracle/oradata/madrid/ctl02'
scope=spfile;

shut immediate
mv /u01/app/oracle/oradata/madrid/control01.ctl /u01/app/oracle/oradata/madrid/ctl01
mv /u01/app/oracle/oradata/madrid/control02.ctl /u01/app/oracle/oradata/madrid/ctl02
startup

减少控制文件:
alter system set control_files=
'/u01/app/oracle/oradata/madrid/ctl01'
scope=spfile;
shut immediate
startup

重建控制文件
[root@oracle0 ~]# lsof | grep /u01/app/oracle/oradata/madrid/control01.ctl

cd /proc/5132/fd/

256 -> /u01/app/oracle/oradata/madrid/control01.ctl (deleted)
cp 256 /home/oracle/control01.ctl
cp /home/oracle/control01.ctl /u01/app/oracle/oradata/madrid/control01.ctl
cp /home/oracle/control01.ctl /u01/app/oracle/oradata/madrid/control02.ctl
chown oracle.oinstall /u01/app/oracle/oradata/madrid/*.ctl

为什么控制文件可以被重建?
因为控制文件种最核心的信息就是所有数据文件头的信息!只要数据文件存在,就可以被重建控制文件

重建控制文件的时候datafile的指针不能少(少就丢数据)
create controlfile reuse database madrid noresetlogs noarchivelog
datafile
'/u01/app/oracle/oradata/madrid/system01.dbf',
'/u01/app/oracle/oradata/madrid/sysaux01.dbf',
'/u01/app/oracle/oradata/madrid/undo01.dbf'
logfile
'/u01/app/oracle/oradata/madrid/redo01.log',
'/u01/app/oracle/oradata/madrid/redo02.log',
'/u01/app/oracle/oradata/madrid/redo03.log';

sql> recover database;
sql> alter database open;
sql> alter tablespace temp add tempfile '/u01/app/oracle/oradata/madrid/temp01.dbf' reuse;


另外实践:

(5)重建控制文件(联机日志没有丢)##############注意先后顺序#############select name from v$controlfile;路径trace #### shutdown abort ###########shutdowm immediate;rm rf /u01/app/oracle/oradata/db01/control01.ctl;rm -rf /home/oracle/control02.ctlstartup(可能报错 执行shutdown abort)select status from v$instance;重建控制文件的时候datafile的指针不能少(少就丢数据)create controlfile reuse database db01 noresetlogs noarchivelogdatafile'/u01/app/oracle/oradata/db01/system01.dbf','/u01/app/oracle/oradata/db01/sysaux01.dbf','/u01/app/oracle/oradata/db01/undo01.dbf'logfile'/u01/app/oracle/oradata/db01/redo01.log','/u01/app/oracle/oradata/db01/redo02.log','/u01/app/oracle/oradata/db01/redo03.log';select status from v$instance;alter database open;select status from v$instance;alter tablespace temp add tempfile '/u01/app/oracle/oradata/db01/temp01.dbf' reuse;(6)控制文件和联机日志都丢失情况下##############注意先后顺序#############模拟实验:select name from v$controlfile;shutdown immediate; (先关机在删除控制文件和日志文件)rm -rf /u01/app/oracle/oradata/db01/control01.ctlrm -rf /home/oracle/control02.ctlrm -rf /u01/app/oracle/oradata/db01/redo01.logrm -rf /u01/app/oracle/oradata/db01/redo02.logrm -rf /u01/app/oracle/oradata/db01/redo03.logselect name from v$controlfile;select status from v$instance;shutdown immediate;create controlfile reuse database db01 resetlogs noarchivelogdatafile'/u01/app/oracle/oradata/db01/system01.dbf','/u01/app/oracle/oradata/db01/undo01.dbf'logfilegroup 1 '/u01/app/oracle/oradata/db01/redo01.log' size 50m,group 2 '/u01/app/oracle/oradata/db01/redo02.log' size 50m,group 3 '/u01/app/oracle/oradata/db01/redo03.log' size 50m;alter database open resetlogs;select status from v$instance;alter tablespace temp add tempfile '/u01/app/oracle/oradata/db01/temp01.dbf' size 50m;

猜你在找的Oracle相关文章