003、控制文件
控制文件：
1、描述了数据库的结构和行为
2、位置在参数据文件的control_files参数中描述
3、最少1个，最多8个
4、所有的控制文件都是镜像关系
5、控制文件预先分配空间，可以自动增长，也可以建立新的更大的控制文件
6、mount时读控制文件
7、open时一直在使用控制文件
SQL> select name from v$controlfile; --记录的是当前实例正在使用的控制文件
NAME
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
SQL> select value from v$spparameter where name='control_files'; --二进制参数文件中，control_files的值记录的控制文件的路径
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
增加控制文件个数
1、修改参数的值：
SQL> alter system set control_files=
2 '+DATA/ora10g/controlfile/current.256.1043492187',
3 '/u01/scripts/control02.ctl'
4 scope=spfile;
System altered.
SQL> select name from v$controlfile; --记录的是当前实例正在使用的控制文件
NAME
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
SQL> select value from v$spparameter where name='control_files'; --二进制参数文件中，control_files的值记录的控制文件的路径
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
/u01/scripts/control02.ctl2、重启数据库，发现无法启动，报错ora-00205：
SQL> startup;
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 167775296 bytes
Database Buffers 423624704 bytes
Redo Buffers 6287360 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@rac1 ~]$ oerr ora 00205
00205, 00000, "error in identifying control file, check alert log for more info"
// *Cause: The system could not find a control file of the specified name and
// size.
// *Action: Check that ALL control files are online and that they are the same
// files that the system created at cold start time.查看alert日志：
……
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/scripts/control02.ctl'
……复制控制文件：
--若数据文件是存储在文件系统，或者Oracle版本为11g以上，则可以直接用操作系统命令复制现有的控制文件：
-- cp +DATA/ora10g/controlfile/current.256.1043492187 /u01/scripts/control02.ctl
--但是oracle 10g asm存储的文件，不支持直接用操作系统命令复制，需要通过RMAN恢复的方式复制控制文件，如下：
RMAN> restore controlfile to '/u01/scripts/control02.ctl' from '+DATA/ora10g/controlfile/current.256.1043492187';
Starting restore at 16-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 instance=ora10g1 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 16-JUN-213、修改数据库状态为mount：
SQL> alter database mount;
Database altered.
SQL> select name from v$controlfile; --记录的是当前实例正在使用的控制文件
NAME
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
/u01/scripts/control02.ctl
SQL> select value from v$spparameter where name='control_files'; --二进制参数文件中，control_files的值记录的控制文件的路径
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
/u01/scripts/control02.ctl减少控制文件的个数
1、修改参数文件中，控制文件的个数
SQL> alter system set control_files=
2 '+DATA/ora10g/controlfile/current.256.1043492187'
3 scope=spfile;
System altered.
SQL> select value from v$spparameter where name='control_files';
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.10434921872、重启生效
SQL> shutdown immediate;
SQL> startup mount;
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
SQL> select value from v$spparameter where name='control_files';
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187查看控制文件的版本号
SQL> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
662只要数据库是在OPEN的状态，控制文件的版本号就在增长，增长机制：
数据库发生变化，会先将变化写进控制文件，再写进控制文件的镜像文件，每写一次控制文件，版本号就增加1.
验证控制文件最多8个
1、修改控制文件个数为9个
SQL> alter system set control_files=
2 '+DATA/ora10g/controlfile/current.256.1043492187',
3 '/u01/scripts/control02.ctl',
4 '/u01/scripts/control03.ctl',
5 '/u01/scripts/control04.ctl',
6 '/u01/scripts/control05.ctl',
7 '/u01/scripts/control06.ctl',
8 '/u01/scripts/control07.ctl',
9 '/u01/scripts/control08.ctl',
10 '/u01/scripts/control09.ctl'
11 scope=spfile;
System altered.
--查看参数文件记录的个数
SQL> select value from v$spparameter where name='control_files';
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
/u01/scripts/control02.ctl
/u01/scripts/control03.ctl
/u01/scripts/control04.ctl
/u01/scripts/control05.ctl
/u01/scripts/control06.ctl
/u01/scripts/control07.ctl
/u01/scripts/control08.ctl
/u01/scripts/control09.ctl
9 rows selected.
RMAN> shutdown immediate2、复制控制文件
--若数据文件是存储在文件系统，或者Oracle版本为11g以上，则可以直接用操作系统命令复制现有的控制文件：
-- cp +DATA/ora10g/controlfile/current.256.1043492187 /u01/scripts/control02.ctl
--但是oracle 10g asm存储的文件，不支持直接用操作系统命令复制，需要通过RMAN恢复的方式复制控制文件，如下：
RMAN> startup nomount
RMAN> restore controlfile to '/u01/scripts/control02.ctl' from '+DATA/ora10g/controlfile/current.256.1043492187';
Starting restore at 16-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 instance=ora10g1 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 16-JUN-21
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control03.ctl
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control04.ctl
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control05.ctl
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control06.ctl
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control07.ctl
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control08.ctl
[oracle@rac1 ~]$ cp /u01/scripts/control02.ctl /u01/scripts/control09.ctl3、修改数据库状态为mount，并查看报错：
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel查看alert日志：
……
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g1_ckpt_1980.trc:
ORA-00208: number of control file names exceeds limit of 8
……
Shutting down instance (abort)
License high water mark = 4
Wed Jun 16 21:48:54 CST 2021
Instance terminated by CKPT, pid = 1980
Wed Jun 16 21:48:58 CST 2021
Instance terminated by USER, pid = 12271说明控制文件个数最多为8个。
4、修改参数，将control_files参数记录的个数修改为少于8个
SQL> startup nomount
SQL> select value from v$spparameter where name='control_files';
VALUE
--------------------------------------------------------------------------------
……
9 rows selected.
--查询，可以直接修改control_files参数
--或者修改文本的参数文件，并转化成spfile修改文本的参数文件：
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g1.ora
SQL> create pfile='/u01/scripts/initora10g1.ora' from spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/spfileora10g1.ora';
File created.
[oracle@rac1 ~]$ vi /u01/scripts/initora10g1.ora
……
*.control_files='+DATA/ora10g/controlfile/current.256.1043492187'
……
SQL> create spfile='+data/ora10g/spfileora10g.ora' from pfile='/u01/scripts/initora10g1.ora';
File created.
[oracle@rac1 bdump]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ rm spfileora10g1.ora5、重新启动数据库：
SQL> shutdown immediate
SQL> startup
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ora10g/spfileora10g.ora
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187
SQL> select value from v$spparameter where name='control_files';
VALUE
--------------------------------------------------------------------------------
+DATA/ora10g/controlfile/current.256.1043492187创建控制文件
1、产生创建控制文件的脚本到udump目录：
SQL> alter database backup controlfile to trace;
Database altered.
--文件名为sid_ora_pid.trc
--查看当前会话的进程号：
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
------------
20503
SQL> show parameter dump;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/admin/ora10g/bdump
core_dump_dest string /u01/app/oracle/admin/ora10g/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/admin/ora10g/udump2、产生创建控制文件的脚本到指定目录：
--SQL> alter database backup controlfile to trace as '/u01/scripts/createcontrolfile.txt' reuse; --reuse代表覆盖已存在的文件
SQL> alter database backup controlfile to trace as '/u01/scripts/createcontrolfile.txt';
Database altered.3、查看脚本内容：
[oracle@rac1 ~]$ vi /u01/scripts/createcontrolfile.txt
……
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 192 --这几个max..就是预先分配空间
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/ora10g/onlinelog/group_1.257.1043492187' SIZE 50M,
GROUP 2 '+DATA/ora10g/onlinelog/group_2.258.1043492189' SIZE 50M,
GROUP 3 '+DATA/ora10g/onlinelog/group_3.265.1043492623' SIZE 50M,
GROUP 4 '+DATA/ora10g/onlinelog/group_4.266.1043492625' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'+DATA/ora10g/datafile/system.259.1043492191',
'+DATA/ora10g/datafile/undotbs1.260.1043492199',
'+DATA/ora10g/datafile/sysaux.261.1043492199',
'+DATA/ora10g/datafile/undotbs2.263.1043492205',
'+DATA/ora10g/datafile/users.264.1043492207',
'+DATA/ora10g/goldengate/goldengate01.dbf'
CHARACTER SET ZHS16GBK
;
……
--不要忘记添加临时文件等内容附件列表
浙公网安备 33010602011771号