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

2016-12-08 10:28:02来源:oschina作者:柳白子人点击

第七城市

控制文件管理

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.startupc.控制文件版本不一致的处理 把高版本的拷贝给低版本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" NORESETLOGSARCHIVELOG //主要是这段,不重置日志文件
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 30408704REUSE AUTOEXTEND ON NEXT 655360MAXSIZE 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" RESETLOGSARCHIVELOG //重置日志文件
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 30408704REUSE AUTOEXTEND ON NEXT 655360MAXSIZE 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;

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

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台