ORACLE体系结构--控制文件管理

于 2016-12-07 16:46:00 发布
阅读量96 收藏
点赞数
文章标签： 数据库 python 开发工具
原文链接：https://my.oschina.net/liubaizi/blog/801573
版权
本文介绍Oracle数据库中控制文件的作用及管理方法，包括控制文件的基本概念、如何增加或减少控制文件、处理版本不一致等问题，并提供了在不同场景下恢复控制文件的具体步骤。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

控制文件管理

controlfile
1.控制文件的作用
记录数据库的结构与行为
数据库的名字，日志名字，存放路径....

数据库在做恢复和实例恢复的时候会使用我们的控制文件
2.控制文件在项目中规划

最多可以创建8个控制文件，至少要有一个控制文件，实际工作中一般规划的是2-3个，而且要分开存放
控制文件里的信息互为镜像，只写当前的控制文件，其他控制文件采用同步的方式备份

100M以内 --超过100M我们可以重建控制文件来修改大小

>>>select * from v$controlfile //查看控制文件的信息
>>>desc v$CONTROLFIL_RECORD_SECTION //查看控制文件的信息
>>>show parameter control_files //查看当前系统中有多少个控制文件



3.控制文件的管理
a.控制文件增加
数据库关闭状态：
1.startup nomount
2.修改参数control_files

SQL> alter system set control_files='/opt/u01/oracle/oradata/maxdb/control01.ctl' ,'/opt/u01/oralce/fast_recovery_area/maxdb/control02.ctl', '/opt/u01/oracle/oradata/maxdb/control02.ctl' scope=spfile;



3.将存在的控制文件复制给新建的
4.alter database mount;
5.alter database open;

数据库如果是开启状态，则将数据库关闭，再进行上面的操作
1.修改参数control_files

SQL> alter system set control_files='/opt/u01/oracle/oradata/maxdb/control01.ctl' ,'/opt/u01/oracle/fast_recovery_area/maxdb/control02.ctl', '/opt/u01/oracle/oradata/maxdb/control02.ctl' scope=spfile;

2.shutdown immediate
3.将存在的控制文件复制给新建的控制文件
4.startup
b.控制文件减少
数据库打开状态：
法一：
1.shutdown immediate;
   shutdown abort;
2.startup nomount;
3. SQL> alter system set control_files='/opt/u01/oracle/oradata/maxdb/control01.ctl' ,'/opt/u01/oracle/fast_recovery_area/maxdb/control02.ctl';
4.alter database mount;
alter database open;
法二：
1. SQL> alter system set control_files='/opt/u01/oracle/oradata/maxdb/control01.ctl' ,'/opt/u01/oracle/fast_recovery_area/maxdb/control02.ctl';
2.shutdown immediate
shutdown abort
3.startup

c.控制文件版本不一致的处理
把高版本的拷贝给低版本

d.控制文件部分丢失的处理
已有的复制给丢失的，或者直接删除

e.控制文件有备份全丢失的处理
>>alter database backup controlfile to '/tmp/con.ctl' ;/备份当前的二进制的控制文件
 

SQL> alter database backup controlfile to trace as '/tmp/con.sql'; //将控制文件备份为脚本
vim /tmp/con.sql //脚本内容如下

-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="maxdb"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     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 "MAXDB" NORESETLOGS  ARCHIVELOG //主要是这段，不重置日志文件
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 4 '/opt/u01/oracle/oradata/maxdb/redo04.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 5 '/opt/u01/oracle/oradata/maxdb/redo05.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 6 '/opt/u01/oracle/oradata/maxdb/redo06.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/opt/u01/oracle/oradata/maxdb/system01.dbf',
  '/opt/u01/oracle/oradata/maxdb/sysaux01.dbf',
  '/opt/u01/oracle/oradata/maxdb/undotbs02.dbf',
  '/opt/u01/oracle/oradata/maxdb/users01.dbf',
  '/opt/u01/oracle/oradata/maxdb/example01.dbf',
  '/opt/u01/oracle/oradata/maxdb/carla01.dbf',
  '/opt/u01/oracle/oradata/maxdb/carla02.dbf'
CHARACTER SET AL32UTF8
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/u01/oracle/oradata/maxdb/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     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

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MAXDB" RESETLOGS  ARCHIVELOG   //重置日志文件
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 4 '/opt/u01/oracle/oradata/maxdb/redo04.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 5 '/opt/u01/oracle/oradata/maxdb/redo05.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 6 '/opt/u01/oracle/oradata/maxdb/redo06.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/opt/u01/oracle/oradata/maxdb/system01.dbf',
  '/opt/u01/oracle/oradata/maxdb/sysaux01.dbf',
  '/opt/u01/oracle/oradata/maxdb/undotbs02.dbf',
  '/opt/u01/oracle/oradata/maxdb/users01.dbf',
  '/opt/u01/oracle/oradata/maxdb/example01.dbf',
  '/opt/u01/oracle/oradata/maxdb/carla01.dbf',
  '/opt/u01/oracle/oradata/maxdb/carla02.dbf'
CHARACTER SET AL32UTF8
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/u01/oracle/fast_recovery_area/MAXDB/archivelog/2016_12_07/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/u01/oracle/oradata/maxdb/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.




1.shut abort;
2.@ /tmp/con.sql //如果无法执行，则拷贝上面的内容来创建脚本

f.控制文件没有备份全丢失的处理
create controlfile reuse database updba noresetlogs noarchivelog

手动创建控制文件

logfile                       //日志文件
group 1 '/u01/oracle/oradata/maxdb/redo01.log' size 50m,
group 1 '/u01/oracle/oradata/maxdb/redo03.log' size 50m,
group 1 '/u01/oracle/oradata/maxdb/redo03.log' size 50m
datafile               //数据文件
'/opt/u01/oracle/oradata/maxdb/system01.dbf',
'/opt/u01/oracle/oradata/maxdb/sysaux01.dbf',
'/opt/u01/oracle/oradata/maxdb/undotbs01.dbf',
'/opt/u01/oracle/oradata/maxdb/users01.dbf'，
‘/opt/u01/oracle/oradata/maxdb/example01.dbf‘
chatacter set al32utf8;

为避免控制文件丢失，我们可以做成多工，分开存放

转载于:https://my.oschina.net/liubaizi/blog/801573

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值