二.control file（控制文件)：指定数据文件，临时文件，重做日志文件及其他控制信息
1. 控制文件概述
·每个oracle数据库必须至少有一个控制文件，用于记载数据库物理结构
·每个oracle数据库应该至少有两个控制文件相互镜像，并存储在不同的disk，防止disk失效
·控制文件记载的信息包括：
数据库名称（可在参数文件获得）；
数据文件名称和位置；重做日志名称和位置；表空间名称；
当前日志序列号；检查点信息；日志历史信息；
2. 查询数据库控制文件
SQL> select name,block_size（16k）,file_size_blks from v$controlfile;
查看所有控制文件及大小
SQL> select value from v$parameter where name='control_files';查看所有控制文件
SQL> host ls -l /u01/app/oracle/oradata/ora10/control*
-rw-r----- 1 oracle oinstall 7061504 Mar 31 13:40 /u01/app/oracle/oradata/ora10/control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 31 13:40 /u01/app/oracle/oradata/ora10/control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 31 13:40 /u01/app/oracle/oradata/ora10/control03.ctl
3. 修改数据库控制文件
·新增/移动一个数据库控制文件
SQL> select name from v$controlfile;
NAME
-----------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10/control01.ctl
/u01/app/oracle/oradata/ora10/control02.ctl
/u01/app/oracle/oradata/ora10/control03.ctl
SQL>create pfile from spfile
SQL>shutdown immediate
$cp control03.ctl control04.ctl
$mv control03.ctl ../controlbak.ctl
$vi $ORRACLE_HOME/dbs/init$ORACLE_SID.ora
SQL> startup pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora';
SQL> select name from v$controlfile;
NAME
-----------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10/control01.ctl
/u01/app/oracle/oradata/ora10/control02.ctl
/u01/app/oracle/oradata/ora10/control04.ctl
SQL>create spfile from pfile;
·所有控制文件被破坏的恢复
SQL> alter database backup controlfile to trace; 每增加一个文件，必须输此命令，文件才完整，所有文件记录将保存在跟踪文件中
vi /u01/app/oracle/admin/ora10/udump/ora10_ora_11956.trc
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora10/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/ora10/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/ora10/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora10/system01.dbf',
'/u01/app/oracle/oradata/ora10/undotbs01.dbf',
'/u01/app/oracle/oradata/ora10/sysaux01.dbf',
'/u01/app/oracle/oradata/ora10/users01.dbf',
'/u01/app/oracle/oradata/ora10/example01.dbf'
CHARACTER SET ZHS16GBK
SQL>startup 启动失败
SQL>startup nomount
SQL> alter system set
control_files='/u01/app/oracle/oradata/ora10/netcontrolfile01', '/u01/app/oracle/oradata/ora10/netcontrolfile02' scope=spfile;
SQL> shutdown immediate 重启生效
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
SQL> select value from v$parameter where name='control_files'; 查看新设定
VALUE
----------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10/netcontrolfile01, /u01/app/oracle/oradata/ora10/netcontrolfile02
SQL> create controlfile database ora10 noresetlogs （可从跟踪文件中获得，前提alter database backup......)
2 logfile
3 group 1 '/u01/app/oracle/oradata/ora10/redo01.log',
4 group 2 '/u01/app/oracle/oradata/ora10/redo02.log',
5 group 3 '/u01/app/oracle/oradata/ora10/redo03.log'
6 datafile
7 '/u01/app/oracle/oradata/ora10/sysaux01.dbf',
8 '/u01/app/oracle/oradata/ora10/system01.dbf',
9 '/u01/app/oracle/oradata/ora10/undotbs01.dbf',
10 '/u01/app/oracle/oradata/ora10/users01.dbf',
11 '/u01/app/oracle/oradata/ora10/example01.dbf'
12 character set zhs16gbk;
Control file created.
SQL> alter database open;
Database altered.
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora10/temp.dbf' size 24125440 reuse autoextend off； （以避免排序报错 reuse指重用原OS文件）
Tablespace altered.
SQL> host ls -l /u01/app/oracle/oradata/ora10/ne* (控制文件已生成）
-rw-r----- 1 oracle oinstall 6078464 Mar 31 14:50 /u01/app/oracle/oradata/ora10/netcontrolfile02
-rw-r----- 1 oracle oinstall 6078464 Mar 31 14:50 /u01/app/oracle/oradata/ora10/netcontrolfile01 转载请保留固定链接： https://linuxeye.com/database/663.html |