最近在群里，看到很多群友都是控制文件出问题了，所以今天我想说一下Control Files的特性。 Control Files 的特性如下 Control Files是声明在数据库属性文件(SPFILE|PFILE)里面的,每次数据库启动的时候，都会读取第一个控制文件的，但是如果 Control Files有
最近在群里，看到很多群友都是控制文件出问题了，所以今天我想说一下Control Files的特性。
Control Files的特性如下
Control Files是声明在数据库属性文件(SPFILE|PFILE)里面的,每次数据库启动的时候，都会读取第一个控制文件的，但是如果 Control Files有改变的话，会同时写入所有声明的控制文件。如果所有声明的控制文件有一个坏了，那么数据库也mount不了。如果你物理上面删除一个 Control Files文件，数据库可能不会马上报错,只有在数据库需要写入 Control Files文件的时候，才会报错。
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat May 31 10:07:29 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/PROD/c
ontrol01.ctl, /u01/app/oracle/
oradata/PROD/control02.ctl
SQL> 这里面有2个控制文件，一般都是3个放在不同硬盘上面。
我们大家都知道，一般数据库之间各个文件之间的SCN号不一致的时候，要恢复的，那么怎么看呢。
这张图就说明了，控制文件的SCN号与数据文件SCN不同时候，数据库的反应。去哪里看相应的SCN号呢？
SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE
SQL> select FILE# ,CHECKPOINT_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1210981
2 1210981
3 1210981
4 1210981
5 1210981
SQL>
SQL> desc v$datafile_header;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
STATUS VARCHAR2(7)
ERROR VARCHAR2(18)
FORMAT NUMBER
RECOVER VARCHAR2(3)
FUZZY VARCHAR2(3)
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TABLESPACE_NAME VARCHAR2(30)
TS# NUMBER
RFILE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
CHECKPOINT_COUNT NUMBER
BYTES NUMBER
BLOCKS NUMBER
NAME VARCHAR2(513)
SPACE_HEADER VARCHAR2(40)
LAST_DEALLOC_CHANGE# VARCHAR2(16)
UNDO_OPT_CURRENT_CHANGE# VARCHAR2(40)
SQL> select FILE# ,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1210981
2 1210981
3 1210981
4 1210981
5 1210981
SQL>
v$datafile:从控制文件里面查询的SCN
v$datafile_header:从数据文件里面查询的SCN
看来控制文件是很重要的啊，那么怎么备份控制文件呢。
SQL> alter database backup controlfile to '/home/oracle/controlfile.bkp'; #备份出一个二进制的文件，可以直接使用 Database altered. SQL> SQL> alter database backup controlfile to trace as '/home/oracle/controlfile.txt'; #备份出一个文本文件里面有SQL脚本 Database altered.备份的方式当然还有rman命令啊
[root@localhost ~]# su - oracle [oracle@localhost ~]$ ls controlfile.bkp controlfile.txt database my.txt sql.html [oracle@localhost ~]$ [oracle@localhost ~]$ ll total 9576 -rw-r----- 1 oracle oinstall 9748480 May 31 10:19 controlfile.bkp --刚才二进制方式的备份文件 -rw-r--r-- 1 oracle oinstall 5816 May 31 10:20 controlfile.txt --备份的带文本文件，以后说明怎么用 drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database -rw-r--r-- 1 oracle oinstall 10134 Jan 18 20:58 my.txt -rw-r--r-- 1 oracle oinstall 14731 Jan 18 21:00 sql.html [oracle@localhost ~]$ [oracle@localhost ~]$ cd /u01/app/oracle/oradata/PROD/ [oracle@localhost PROD]$ ll total 1949180 -rw-r----- 1 oracle oinstall 9748480 May 31 10:22 control01.ctl --这个是数据库里面正在使用的控制文件，大小和<span style="font-family: Arial, Helvetica, sans-serif;">二进制方式的备份文件一样</span> -rw-r----- 1 oracle oinstall 9748480 May 31 10:22 control02.ctl -rw-r----- 1 oracle oinstall 362422272 May 31 10:06 example01.dbf -rw-r----- 1 oracle oinstall 52429312 May 31 10:06 redo01.log -rw-r----- 1 oracle oinstall 52429312 May 31 10:06 redo02.log -rw-r----- 1 oracle oinstall 52429312 May 31 10:21 redo03.log -rw-r----- 1 oracle oinstall 534781952 May 31 10:17 sysaux01.dbf -rw-r----- 1 oracle oinstall 807411712 May 31 10:12 system01.dbf -rw-r----- 1 oracle oinstall 20979712 Feb 6 09:31 temp01.dbf -rw-r----- 1 oracle oinstall 52436992 May 31 10:17 undotbs01.dbf -rw-r----- 1 oracle oinstall 57679872 May 31 10:06 users01.dbf [oracle@localhost PROD]$ [oracle@localhost PROD]$
那么什么时候我们要备份控制文件呢?就一个原则，控制文件变化了，我们需要备份了，一般下面命令出现的时候，建议大家备份
看来以后控制文件要好好的看着啊。
How to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AMTograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)
How to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AMToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl
MySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AMToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'
MySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AMThe string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.
MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AMThe best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.
MySQL BLOB : are there any limits?May 08, 2025 am 12:22 AMMySQLBLOBshavelimits:TINYBLOB(255bytes),BLOB(65,535bytes),MEDIUMBLOB(16,777,215bytes),andLONGBLOB(4,294,967,295bytes).TouseBLOBseffectively:1)ConsiderperformanceimpactsandstorelargeBLOBsexternally;2)Managebackupsandreplicationcarefully;3)Usepathsinst
MySQL : What are the best tools to automate users creation?May 08, 2025 am 12:22 AMThe best tools and technologies for automating the creation of users in MySQL include: 1. MySQLWorkbench, suitable for small to medium-sized environments, easy to use but high resource consumption; 2. Ansible, suitable for multi-server environments, simple but steep learning curve; 3. Custom Python scripts, flexible but need to ensure script security; 4. Puppet and Chef, suitable for large-scale environments, complex but scalable. Scale, learning curve and integration needs should be considered when choosing.
MySQL: Can I search inside a blob?May 08, 2025 am 12:20 AMYes,youcansearchinsideaBLOBinMySQLusingspecifictechniques.1)ConverttheBLOBtoaUTF-8stringwithCONVERTfunctionandsearchusingLIKE.2)ForcompressedBLOBs,useUNCOMPRESSbeforeconversion.3)Considerperformanceimpactsanddataencoding.4)Forcomplexdata,externalproc
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Atom editor mac version download
The most popular open source editor
SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.
PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
SublimeText3 Chinese version
Chinese version, very easy to use
SublimeText3 Linux new version
SublimeText3 Linux latest version
