jiang5495
- 浏览: 92990 次
- 性别:
- 来自: 湖南
-
文章分类
最新评论
-
凡尘不了De：
大哥能否发一份源码，不甚感激，研究一下组卷的算法。。邮箱 lw ...
一个有点像样的考试系统 -
15959912925：
找到原因没？现在是14jar报这个错误，很无语，网上找了一堆也 ...
连接oracle数据库的一个报错 -
Mr_lee_2012：
来过。马克。
Java继承,深度理解。不上机运行你能正确写出运行结果吗？ -
konnin：
打个能否给我一份源代码啊，我邮箱是pkonnin@gmail. ...
一个有点像样的考试系统 -
fy616508150：
楼主辛苦了``
Java继承,深度理解。不上机运行你能正确写出运行结果吗？
一、Oracle 控制文件
为二进制文件，初始化大小由CREATE DATABASE指定,可以使用RMAN备份
记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等
在参数文件中描述其位置，个数等等。通常采用分散放开，多路复用的原则。在mount阶段被读取，open阶段一直被使用
维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)
一个控制文件只能属于一个数据库
控制文件的任意修改将写入到初始化参数中指定的所有控制文件中，读取时则仅读取第一个控制文件
控制文件只能连接一个数据库，控制文件的大小一般不要超过MB,最多为个，最少一个，互为镜像
控制文件中包含的内容
数据库的名字、ID、创建的时间戳
表空间的名字
联机日志文件、数据文件的位置、个数、名字
联机日志的Sequence号码
检查点的信息
撤销段的开始或结束
归档信息
备份信息
二、查看控制文件的相关信息
1.使用相关视图来查看
V$CONTROLFILE --列出实例中所有控制文件的名字及状态信息
V$PARAMETER --列出所有参数的位置及状态信息
V$CONTROLFILE_RECORD_SECTION --列出控制文件中记录的部分信息
SHOW PARAMETER CONTROL_FILES --列出控制文件的名字、状态、位置等
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl NO 16384 430
/u01/app/oracle/oradata/orcl/control02.ctl NO 16384 430
/u01/app/oracle/oradata/orcl/control03.ctl NO 16384 430
SQL> select name,type,value from v$parameter where name like '%control%';
NAME TYPE VALUE
------------------------------ ---------- ------------------------------------------------------------
control_files 2 /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/con
trol03.ctl
control_file_record_keep_time 3 7
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
REDO THREAD 256 8 1 0 0 0
REDO LOG 72 16 9 0 0 20
DATAFILE 428 100 8 0 0 28
FILENAME 524 2298 21 0 0 0
TABLESPACE 68 100 7 0 0 7
TEMPORARY FILENAME 56 100 1 0 0 1
RMAN CONFIGURATION 1108 50 0 0 0 0
LOG HISTORY 56 292 35 1 35 35
OFFLINE RANGE 200 163 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 28 20 1 20 20
BACKUP SET 40 409 0 0 0 0
BACKUP PIECE 736 200 0 0 0 0
BACKUP DATAFILE 116 282 0 0 0 0
BACKUP REDOLOG 76 215 0 0 0 0
DATAFILE COPY 660 223 1 1 1 1
BACKUP CORRUPTION 44 371 0 0 0 0
COPY CORRUPTION 40 409 0 0 0 0
DELETED OBJECT 20 818 3 1 3 3
PROXY COPY 852 249 0 0 0 0
BACKUP SPFILE 36 454 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE INCARNATION 56 292 2 1 2 2
FLASHBACK LOG 84 2048 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
RMAN STATUS 116 141 0 0 0 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
MTTR 100 8 1 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0
STANDBY DATABASE MATRIX 400 10 10 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
RESTORE POINT 212 2083 0 0 0 0
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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> select controlfile_sequence# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
985
2.使用STRINGS命令来查看控制文件中的具体内容
SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl | more
}|{z
JORCL
L-+RG
+ORCL
+ORCL
orcl
orcl
-+-='
-+-='
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo3.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo2.log
/u01/app/oracle/oradata/orcl/redo01.log
/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/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
u01/app/oracle/oradata/orcl/undotbs02.dbf
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo3.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo2.log
/u01/app/oracle/oradata/orcl/redo01.log
/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/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
u01/app/oracle/oradata/orcl/undotbs02.dbf
SYSTEM
UNDOTBS1
SYSAUX
USERS
EXAMPLE
TBS1
TEMP
3.备份控制文件到平面文件(然后查看控制文件中的具体内容)
SQL> alter database backup controlfile to trace as '/u01/app/oracle/ctl.txt';
Database altered.
--或者使用
SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl > /u01/app/oracle/ctl.txt
SQL> host cat /u01/app/oracle/ctl.txt;
-- 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="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=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 "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/orcl/redo02.log',
'/u01/app/oracle/oradata/orcl/redo2.log'
) SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/orcl/redo03.log',
'/u01/app/oracle/oradata/orcl/redo3.log'
) SIZE 100M,
GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,
GROUP 7 (
'/u01/app/oracle/oradata/orcl/redo07.log ',
'/u01/app/oracle/oradata/orcl/redo7.log '
) SIZE 50M,
GROUP 8 (
'/u01/app/oracle/oradata/orcl/redo08.log',
'/u01/app/oracle/oradata/orcl/redo8.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_2.dbf'
CHARACTER SET WE8ISO8859P1
;
-- 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 '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/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
-- 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 '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 26214400 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 "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/orcl/redo02.log',
'/u01/app/oracle/oradata/orcl/redo2.log'
) SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/orcl/redo03.log',
'/u01/app/oracle/oradata/orcl/redo3.log'
) SIZE 100M,
GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,
GROUP 7 (
'/u01/app/oracle/oradata/orcl/redo07.log ',
'/u01/app/oracle/oradata/orcl/redo7.log '
) SIZE 50M,
GROUP 8 (
'/u01/app/oracle/oradata/orcl/redo08.log',
'/u01/app/oracle/oradata/orcl/redo8.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_2.dbf'
CHARACTER SET WE8ISO8859P1
;
-- 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 '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/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 '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
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
--查看路径中最新的文件
[root@robinson ~]# vim /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc
--以下仅列出部分信息
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/10g
System name: Linux
Node name: robinson.com
Release: 2.6.18-164.el5xen
Version: #1 SMP Tue Aug 18 16:06:30 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 5110, image: oracle@robinson.com (TNS V1-V3)
*** 2010-05-20 18:52:30.104
*** SERVICE NAME:(SYS$USERS) 2010-05-20 18:52:30.084
*** SESSION ID:(159.10) 2010-05-20 18:52:30.084
DUMP OF CONTROL FILES, Seq # 985 = 0x3d9
V10 STYLE FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=1242732291=0x4a129703, Db Name='ORCL'
Activation ID=0=0x0
Control Seq=985=0x3d9, File size=430=0x1ae
File Number=0, Blksiz=16384, File Type=1 CONTROL
Logical block number 1 (header block)
Dump of memory from 0xB7EF7E00 to 0xB7EFBE00
--使用oradebug setmypid
SQL> oradebug setmypid --标记当前进程
Statement processed.
--获得当前的spid
SQL> select spid from v$process where addr =
2 (select paddr from v$session where sid =
3 (select sid from v$mystat where rownum = 1));
SPID
------------
5110
SQL> oradebug dump controlf 3;--3表示包含头文件和数据文件内容
Statement processed.
--查看user_dump_dest 路径
SQL> show parameter user_d
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/orcl/udu
mp
--找到当前spid的trc文件
SQL> ! ls /u01/app/oracle/admin/orcl/udump/*5110.* -- */
/u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc
SQL> ! cat /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc
--下面列出的为部分内容
***************************************************************************
RMAN CONFIGURATION RECORDS
***************************************************************************
(size = 1108, compat size = 1108, section max = 50, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 90, numrecs = 50)
***************************************************************************
FLASHBACK LOGFILE RECORDS
***************************************************************************
(size = 84, compat size = 84, section max = 2048, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 139, numrecs = 2048)
***************************************************************************
THREAD INSTANCE MAPPING RECORDS
***************************************************************************
(size = 80, compat size = 80, section max = 8, section in-use = 8,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 156, numrecs =
orcl recno=1
UNNAMED_INSTANCE_2 recno=2
UNNAMED_INSTANCE_3 recno=3
UNNAMED_INSTANCE_4 recno=4
UNNAMED_INSTANCE_5 recno=5
UNNAMED_INSTANCE_6 recno=6
UNNAMED_INSTANCE_7 recno=7
UNNAMED_INSTANCE_8 recno=8
***************************************************************************
MTTR RECORDS
***************************************************************************
(size = 100, compat size = 100, section max = 8, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 157, numrecs =
MTTR record for thread 1
MTTR statistics status: 3
Init time: Avg: 47090974 us, Times measured: 4
File open time: Avg: 11225 us, Times measured: 21
Log block read time: Avg: 20 us, Times measured: 65536
Data block read/claim time: Avg: 170 us, Times measured: 1000
Data block write time: Avg: 390 us
1000 change vector apply time: Avg: 112435 us, Times measured: 37
Ratio Information:
# of log blocks measured: 207891
# of data blocks measured: 12536
# of change vectors measured: 564444
***************************************************************************
STANDBY DATABASE MAP RECORDS
***************************************************************************
(size = 400, compat size = 400, section max = 10, section in-use = 10,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 160, numrecs = 10)
***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
(size = 212, compat size = 212, section max = 2048, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 161, numrecs = 2048)
三、控制文件的管理
规划原则：多路复用,建议存放到不同的磁盘或同一磁盘不同的分区
个数与位置及状态管理:
查看控制文件的状态是否与参数定义中的相吻合,当数据库发生结构修改时，将修改内容同时写入控制文件
备份管理
恢复管理
新建控制文件语句
spfile或pfile都可以实现对控制文件的个数及位置管理
spfile步骤
修改spfile参数中的control_files -- alter system ... scope = spfile | both |memory
一致性关闭数据库
增加或减少控制文件(cp or mv)
启动数据库使用spfile
验证结果
pfile步骤
一致性关闭数据库
修改pfile参数(vi或vim) 修改*.control_files＝......这一段
增加或减少控制文件(cp or mv)
启动数据库使用pfile
验证结果
--演示spfile修改控制文件
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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;
System altered.
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.
--再次查看参数文件,已显示为一个
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
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;
System altered.
--启动时可以看到在实例阶段出现了版本号不一致的问题
SQL> startup force
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
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;
Database altered.
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';
Database altered.
--用于重建控制文件
SQL> alter database backup controlfile to trace as '/u01/app/oracle/recreate_controlfile.txt';
Database altered.
--使用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;
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=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
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
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_nnndf_TAG20100523T132647_5zkh4sk2_.bkp tag=TAG20100523T132647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE 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_ncsnf_TAG20100523T132647_5zkhh5st_.bkp tag=TAG20100523T132647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:14
Finished backup at 23-MAY-10
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
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
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;
Database altered.
SQL> alter database open;
Database altered.
--控制文件部分丢失的演示，原本有两个控制文件，丢失一个
--处理办法：
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;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 75499284 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 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
Tue Jun 8 19:03:44 2010
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',
2 '/u01/app/oracle/oradata/orcl/control02.ctl' scope = spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 83887892 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
非归档模式下，当所有的控制文件都丢失，只能重建控制文件来解决
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 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
ALTER DATABASE MOUNT
Thu Jul 15 12:13:15 2010
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
--重建控制文件主要有三个需要考虑的是
--搞清各个日志文件的大小及位置
--搞清各个数据文件的位置
--设置正确的字符集
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'
) SIZE 50M,
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> alter database open;
Database altered.
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> 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 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open ;
Database altered.
--查看归档的状态
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';
Database altered.
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; --切换日志
System altered.
SQL> archive log list; --日志切换后sequence由变成
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
--模拟控制文件全部丢失
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
--查看物理控制文件是否存在
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
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from dual;
D
-
X
SQL> select * from tb_temp; --表成功被恢复
ID COL1
---------- --------------------
1 Robinson
SQL> drop table tb_temp purge;
Table dropped.
SQL> archive log list; --日志的sequence号被置为
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 1
Current log sequence 1
为二进制文件，初始化大小由CREATE DATABASE指定,可以使用RMAN备份
记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等
在参数文件中描述其位置，个数等等。通常采用分散放开，多路复用的原则。在mount阶段被读取，open阶段一直被使用
维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)
一个控制文件只能属于一个数据库
控制文件的任意修改将写入到初始化参数中指定的所有控制文件中，读取时则仅读取第一个控制文件
控制文件只能连接一个数据库，控制文件的大小一般不要超过MB,最多为个，最少一个，互为镜像
控制文件中包含的内容
数据库的名字、ID、创建的时间戳
表空间的名字
联机日志文件、数据文件的位置、个数、名字
联机日志的Sequence号码
检查点的信息
撤销段的开始或结束
归档信息
备份信息
二、查看控制文件的相关信息
1.使用相关视图来查看
V$CONTROLFILE --列出实例中所有控制文件的名字及状态信息
V$PARAMETER --列出所有参数的位置及状态信息
V$CONTROLFILE_RECORD_SECTION --列出控制文件中记录的部分信息
SHOW PARAMETER CONTROL_FILES --列出控制文件的名字、状态、位置等
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl NO 16384 430
/u01/app/oracle/oradata/orcl/control02.ctl NO 16384 430
/u01/app/oracle/oradata/orcl/control03.ctl NO 16384 430
SQL> select name,type,value from v$parameter where name like '%control%';
NAME TYPE VALUE
------------------------------ ---------- ------------------------------------------------------------
control_files 2 /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/con
trol03.ctl
control_file_record_keep_time 3 7
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
REDO THREAD 256 8 1 0 0 0
REDO LOG 72 16 9 0 0 20
DATAFILE 428 100 8 0 0 28
FILENAME 524 2298 21 0 0 0
TABLESPACE 68 100 7 0 0 7
TEMPORARY FILENAME 56 100 1 0 0 1
RMAN CONFIGURATION 1108 50 0 0 0 0
LOG HISTORY 56 292 35 1 35 35
OFFLINE RANGE 200 163 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG 584 28 20 1 20 20
BACKUP SET 40 409 0 0 0 0
BACKUP PIECE 736 200 0 0 0 0
BACKUP DATAFILE 116 282 0 0 0 0
BACKUP REDOLOG 76 215 0 0 0 0
DATAFILE COPY 660 223 1 1 1 1
BACKUP CORRUPTION 44 371 0 0 0 0
COPY CORRUPTION 40 409 0 0 0 0
DELETED OBJECT 20 818 3 1 3 3
PROXY COPY 852 249 0 0 0 0
BACKUP SPFILE 36 454 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE INCARNATION 56 292 2 1 2 2
FLASHBACK LOG 84 2048 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
RMAN STATUS 116 141 0 0 0 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
MTTR 100 8 1 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0
STANDBY DATABASE MATRIX 400 10 10 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
RESTORE POINT 212 2083 0 0 0 0
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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> select controlfile_sequence# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
985
2.使用STRINGS命令来查看控制文件中的具体内容
SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl | more
}|{z
JORCL
L-+RG
+ORCL
+ORCL
orcl
orcl
-+-='
-+-='
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo3.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo2.log
/u01/app/oracle/oradata/orcl/redo01.log
/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/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
u01/app/oracle/oradata/orcl/undotbs02.dbf
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo3.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo2.log
/u01/app/oracle/oradata/orcl/redo01.log
/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/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
u01/app/oracle/oradata/orcl/undotbs02.dbf
SYSTEM
UNDOTBS1
SYSAUX
USERS
EXAMPLE
TBS1
TEMP
3.备份控制文件到平面文件(然后查看控制文件中的具体内容)
SQL> alter database backup controlfile to trace as '/u01/app/oracle/ctl.txt';
Database altered.
--或者使用
SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl > /u01/app/oracle/ctl.txt
SQL> host cat /u01/app/oracle/ctl.txt;
-- 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="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=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 "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/orcl/redo02.log',
'/u01/app/oracle/oradata/orcl/redo2.log'
) SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/orcl/redo03.log',
'/u01/app/oracle/oradata/orcl/redo3.log'
) SIZE 100M,
GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,
GROUP 7 (
'/u01/app/oracle/oradata/orcl/redo07.log ',
'/u01/app/oracle/oradata/orcl/redo7.log '
) SIZE 50M,
GROUP 8 (
'/u01/app/oracle/oradata/orcl/redo08.log',
'/u01/app/oracle/oradata/orcl/redo8.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_2.dbf'
CHARACTER SET WE8ISO8859P1
;
-- 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 '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/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
-- 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 '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 26214400 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 "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/orcl/redo02.log',
'/u01/app/oracle/oradata/orcl/redo2.log'
) SIZE 50M,
GROUP 3 (
'/u01/app/oracle/oradata/orcl/redo03.log',
'/u01/app/oracle/oradata/orcl/redo3.log'
) SIZE 100M,
GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,
GROUP 7 (
'/u01/app/oracle/oradata/orcl/redo07.log ',
'/u01/app/oracle/oradata/orcl/redo7.log '
) SIZE 50M,
GROUP 8 (
'/u01/app/oracle/oradata/orcl/redo08.log',
'/u01/app/oracle/oradata/orcl/redo8.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',
'/u01/app/oracle/oradata/orcl/tbs1_2.dbf'
CHARACTER SET WE8ISO8859P1
;
-- 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 '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area
-- /ORCL/archivelog/2010_05_20/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 '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
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
--查看路径中最新的文件
[root@robinson ~]# vim /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc
--以下仅列出部分信息
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/10g
System name: Linux
Node name: robinson.com
Release: 2.6.18-164.el5xen
Version: #1 SMP Tue Aug 18 16:06:30 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 5110, image: oracle@robinson.com (TNS V1-V3)
*** 2010-05-20 18:52:30.104
*** SERVICE NAME:(SYS$USERS) 2010-05-20 18:52:30.084
*** SESSION ID:(159.10) 2010-05-20 18:52:30.084
DUMP OF CONTROL FILES, Seq # 985 = 0x3d9
V10 STYLE FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=1242732291=0x4a129703, Db Name='ORCL'
Activation ID=0=0x0
Control Seq=985=0x3d9, File size=430=0x1ae
File Number=0, Blksiz=16384, File Type=1 CONTROL
Logical block number 1 (header block)
Dump of memory from 0xB7EF7E00 to 0xB7EFBE00
--使用oradebug setmypid
SQL> oradebug setmypid --标记当前进程
Statement processed.
--获得当前的spid
SQL> select spid from v$process where addr =
2 (select paddr from v$session where sid =
3 (select sid from v$mystat where rownum = 1));
SPID
------------
5110
SQL> oradebug dump controlf 3;--3表示包含头文件和数据文件内容
Statement processed.
--查看user_dump_dest 路径
SQL> show parameter user_d
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/orcl/udu
mp
--找到当前spid的trc文件
SQL> ! ls /u01/app/oracle/admin/orcl/udump/*5110.* -- */
/u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc
SQL> ! cat /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc
--下面列出的为部分内容
***************************************************************************
RMAN CONFIGURATION RECORDS
***************************************************************************
(size = 1108, compat size = 1108, section max = 50, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 90, numrecs = 50)
***************************************************************************
FLASHBACK LOGFILE RECORDS
***************************************************************************
(size = 84, compat size = 84, section max = 2048, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 139, numrecs = 2048)
***************************************************************************
THREAD INSTANCE MAPPING RECORDS
***************************************************************************
(size = 80, compat size = 80, section max = 8, section in-use = 8,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 156, numrecs =
orcl recno=1
UNNAMED_INSTANCE_2 recno=2
UNNAMED_INSTANCE_3 recno=3
UNNAMED_INSTANCE_4 recno=4
UNNAMED_INSTANCE_5 recno=5
UNNAMED_INSTANCE_6 recno=6
UNNAMED_INSTANCE_7 recno=7
UNNAMED_INSTANCE_8 recno=8
***************************************************************************
MTTR RECORDS
***************************************************************************
(size = 100, compat size = 100, section max = 8, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 157, numrecs =
MTTR record for thread 1
MTTR statistics status: 3
Init time: Avg: 47090974 us, Times measured: 4
File open time: Avg: 11225 us, Times measured: 21
Log block read time: Avg: 20 us, Times measured: 65536
Data block read/claim time: Avg: 170 us, Times measured: 1000
Data block write time: Avg: 390 us
1000 change vector apply time: Avg: 112435 us, Times measured: 37
Ratio Information:
# of log blocks measured: 207891
# of data blocks measured: 12536
# of change vectors measured: 564444
***************************************************************************
STANDBY DATABASE MAP RECORDS
***************************************************************************
(size = 400, compat size = 400, section max = 10, section in-use = 10,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 160, numrecs = 10)
***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
(size = 212, compat size = 212, section max = 2048, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 161, numrecs = 2048)
三、控制文件的管理
规划原则：多路复用,建议存放到不同的磁盘或同一磁盘不同的分区
个数与位置及状态管理:
查看控制文件的状态是否与参数定义中的相吻合,当数据库发生结构修改时，将修改内容同时写入控制文件
备份管理
恢复管理
新建控制文件语句
spfile或pfile都可以实现对控制文件的个数及位置管理
spfile步骤
修改spfile参数中的control_files -- alter system ... scope = spfile | both |memory
一致性关闭数据库
增加或减少控制文件(cp or mv)
启动数据库使用spfile
验证结果
pfile步骤
一致性关闭数据库
修改pfile参数(vi或vim) 修改*.control_files＝......这一段
增加或减少控制文件(cp or mv)
启动数据库使用pfile
验证结果
--演示spfile修改控制文件
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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;
System altered.
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.
--再次查看参数文件,已显示为一个
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
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;
System altered.
--启动时可以看到在实例阶段出现了版本号不一致的问题
SQL> startup force
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
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;
Database altered.
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';
Database altered.
--用于重建控制文件
SQL> alter database backup controlfile to trace as '/u01/app/oracle/recreate_controlfile.txt';
Database altered.
--使用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;
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=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
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
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_nnndf_TAG20100523T132647_5zkh4sk2_.bkp tag=TAG20100523T132647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE 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_ncsnf_TAG20100523T132647_5zkhh5st_.bkp tag=TAG20100523T132647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:14
Finished backup at 23-MAY-10
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
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
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;
Database altered.
SQL> alter database open;
Database altered.
--控制文件部分丢失的演示，原本有两个控制文件，丢失一个
--处理办法：
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;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 75499284 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 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
Tue Jun 8 19:03:44 2010
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',
2 '/u01/app/oracle/oradata/orcl/control02.ctl' scope = spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 83887892 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
非归档模式下，当所有的控制文件都丢失，只能重建控制文件来解决
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 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
ALTER DATABASE MOUNT
Thu Jul 15 12:13:15 2010
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
--重建控制文件主要有三个需要考虑的是
--搞清各个日志文件的大小及位置
--搞清各个数据文件的位置
--设置正确的字符集
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'
) SIZE 50M,
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> alter database open;
Database altered.
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> 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 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open ;
Database altered.
--查看归档的状态
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';
Database altered.
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; --切换日志
System altered.
SQL> archive log list; --日志切换后sequence由变成
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
--模拟控制文件全部丢失
SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
--查看物理控制文件是否存在
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
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 79693588 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from dual;
D
-
X
SQL> select * from tb_temp; --表成功被恢复
ID COL1
---------- --------------------
1 Robinson
SQL> drop table tb_temp purge;
Table dropped.
SQL> archive log list; --日志的sequence号被置为
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 1
Current log sequence 1
分享到：
发表评论
-
Oracle中查询用户目前拥有哪些权限
2012-07-19 11:03 1140SQL> select * from user_sy ... -
sdf
2012-01-04 16:11 0sdfsdfsd -
dafasdfasfd
2011-11-29 19:53 0http://www.codeproject.com/KB/j ... -
sadfasdfa
2011-11-20 15:57 0dfassdafsadfasdfasdf -
fffffffffffffffffffffffffffffffffff
2011-11-16 11:44 0这个过程中需要读入一个文件：r_pa18_pscp.prm 这 ... -
Mysql与Oracle之间的转换
2011-07-29 15:02 1405今天由于项目需要，需 ... -
normal、sysdba、sysoper登陆oracle的区别理解(转载）
2011-06-25 18:12 1405sys和system用户的区别 【system】用户只能用no ... -
oracle 方案的理解(转载）
2011-06-25 18:02 869我们先来看一下他们的 ...
相关推荐
### FAT32 文件系统详解 #### 一、FAT32 文件系统概述 FAT32（File Allocation Table 32）是一种文件系统格式，它主要用于容量较大的软盘和硬盘分区，特别是在Windows操作系统中非常常见。FAT32是FAT文件系统的一...
文件系统详解 文件系统是操作系统的核心组成部分，它负责管理和控制计算机系统的软件资源，这些资源通常以文件的形式存储在磁盘或其他外部存储介质上。通过对软件资源的高效管理，文件系统在操作系统中扮演着至关...
#### 四、NTFS文件详解 除了属性之外，NTFS文件系统还包括了一些特殊的文件，这些文件对于维护整个文件系统的正常运行至关重要。 ##### 1. 文件:$MFT (0) 主文件表($MFT)是NTFS的核心组件之一，它记录了文件系统...
### ADI Blackfin 处理器 Vdsp LDF 文件格式详解 #### 一、概述 在ADI (Analog Devices Inc.) 的Blackfin系列处理器中，`.ldf`（Linker Description File）是一种非常重要的文件类型，它主要用于描述链接器如何...
SVN版本控制工具使用详解 SVN（Subversion）是一种自由开源的版本控制系统，可以将代码恢复到早期版本，或者检查数据修改的历史，这些数据可以是源代码，也可以是其他类型的文件。其功能包括根据文件修改的次数自动...
"DXF文件格式详解(全).zip"是一个压缩包，包含了对DXF格式的全面解析，对于需要理解或处理DXF文件的用户来说，是一个宝贵的资源。 DXF文件格式的核心在于其结构，它分为多个部分，包括标题块、表格、实体和结束标记...
标题中的"MANIFEST.MF 文件内容详解(转)"是指一篇关于理解Java应用程序的MANIFEST.MF文件的文章。MANIFEST.MF文件是Java档案（JAR）文件格式的一部分，它包含了有关JAR文件元数据的重要信息。这篇博文可能是从其他...
Spring 配置文件详解 Spring 配置文件是 Spring 框架中最重要的配置文件之一，它负责定义和配置应用程序的Bean对象，以及它们之间的依赖关系。Spring 配置文件通常以XML文件的形式存在，文件名通常为...
VERICUT控制文件设置详解 VERICUT控制文件设置是VERICUT软件中的一项重要功能，它允许用户根据不同的机床系统控制器自定义控制文件，以适应不同的机床系统需求。VERICUT提供了多种标准控制文件，例如GenericControl...
### 汽车电子A2L文件协议详解 #### 一、引言 A2L文件格式是由ASAM（Automotive Simulation and Measurement Systems Association）组织制定的一种标准文件格式，主要用于汽车电子系统的标定数据交换。它是一种广泛...
### Creo3.0-config.pro配置文件详解 #### 概述 `config.pro`配置文件是PTC Creo Parametric中的一个核心配置文件，它控制着Creo Parametric的多种功能和行为。通过编辑`config.pro`文件，用户可以根据自身需求...
事实上微控制器是无法从硬件上去定位main函数的入口地址，因为使用C语言作为开发语言后，变量/函数的地址便由编译器在编译时自行分配，因此main函数的入口地址在编译后便不一定是一个绝对地址。MCU上电后又是如何...
域控文件服务器搭建与使用详解是一种基于域控的文件服务器解决方案，旨在满足企业中文件共享、访问控制和安全管理的需求。本文将详细介绍域控文件服务器的搭建和使用，涵盖文件服务器的设置、权限管理、共享文件的...
《自动控制原理习题详解》是由王建辉主编的一部专为学习自动控制原理的学生和教师编写的参考资料。这本书深入浅出地解析了...使用超星阅览器打开PDG文件，可以方便地在线阅读和学习，是学习自动控制原理的得力助手。
Apache配置文件是管理和控制Apache HTTP服务器的关键文件，它包含了服务器的全局环境设定、主服务器参数以及虚拟主机的配置。这份中文详解将深入解析其主要内容。 首先，`ServerType`指令决定了Apache服务器的运行...
DBF文件格式是一种常见的数据库文件格式，主要用于存储结构化数据，尤其在早期的数据库系统和桌面应用程序中广泛使用。DBF文件由头文件和实体信息两部分组成。 1. 文件头部分： 文件头是DBF文件的起始部分，包含...
**EDS文件详解** EDS（Ethernet Device Description）文件是描述以太网/IP设备特征的文件，它包含设备的制造商信息、产品型号、通信参数、数据结构等。这些信息对于配置和诊断以太网/IP网络至关重要。 1. 设备描述...
附带的文档如"CMD文件配置详解.doc"和"CMD文件的原理.pdf"将更深入地探讨CMD文件的各个细节，包括具体指令的用法、最佳实践以及常见问题的解决。通过学习这些资料，开发者将能够更好地理解和利用CMD文件来提升TI DSP...