Maintaining Control File

1.控制文件概念：
  控制文件是一个小型的二进制(binary)文件，可以记录数据库的物理结构。包括：
   *数据库名称
   *数据文件和联机重做日志文件的名称和位置
   *当前日志的序号
   *检查点信息
 一般oracle数据库创建时都会创建至少两个或两个以上的控制文件。

2.控制文件的标准:
  1).控制文件的文件名：
      由CONTROL_FILES参数来指定控制文件名。
      若没有指定该参数，则生成默认文件名，默认文件名在各个操作系统中各不相同。
  2).控制文件的复用
     一般不同的控制文件都存放在不同的磁盘，当某个磁盘损坏时可以通过在其他磁盘上的控制文件进行复用，不需要任何的介质恢复。

     * 找到CONTROL_FILES参数列出所有文件名，将相应内容写入所有控制文件
     * CONTROL_FILES中列出的第一个文件是Oracle数据库运行期间唯一可以读取的文件
     * 数据库运行期间，任何控制文件变为不可用，则实例不能继续运行

  3).备份控制文件
      进行以下数据库物理结构改变之后，需要备份控制文件
     * 添加、取消或重命名数据文件
     * 添加或撤销表空间，或更改表空间读写状态
     * 添加或取消重做日志文件
 
  4).控制文件大小管理
      MAXDATAFILES、MAXLOGFILES、MAXLOGMEMBERS、MAXLOGHISTORY、MAXINSTANCES参数控制

3.1.使用SPFILE时对控制文件进行多元备份:
  1).改变SPFILE:

1  SQL>alter system set          control_files='$HOME/ORA/u01/ctrl01.ctl',
2          '$HOME/ORA/u02/ctrl02.ctl' scope=spfile;

  2).关闭数据库：

1 SQL>shutdown immediate;

  3).创建控制文件副本：   

1 $cp $HOME/ORA/u01/ctrl01.ctl $HOME/ORA/u02/ctrl02.ctl

  4).启动数据库

1 SQL>startup

 

3.2.使用PFILE时对控制文件进行多元备份：
  1).关闭数据库   

1 SQL>shutdown immediate;

  2).创建控制文件副本

1 $cp $HOME/ORA/u01/ctrl01.ctl $HOME/ORA/u02/ctrl02.ctl

  3).向PFILE添加控制文件名   

1 control_files='$HOME/ORA/u01/ctrl01.ctl','$HOME/ORA/u02/ctrl02.ctl' 

  4).启动数据库     

1 SQL>startup pfile='/oraclesoft/oracle/dbs/initbbk.ora';

  5).创建SPFILE   

1 SQL>create spfile form pfile;

 

4.1.控制文件转移：
   1).shutdown immediate;
   2).把control file 拷贝到新目录；
   3).修改pfile中control_files的路径；
   4).启动数据库并用pfile,再用pfile创建spfile。

4.2.当控制文件进行好多元备份以后，当丢失某个控制文件的时候只需要把复制一个存在的控制文件改名会丢失的控制文件名。

5.获取控制文件信息
  v$controlfile
  v$parameter
  v$controlfile_record_section

6.1 创建新的控制文件(方法一,用于参考)：
  截取trace文件内容：

1 SQL> alter database backup controlfile to trace;
2 
3   数据库已更改。

 

1 SQL> @gettrcname
2 
3 TRACE_FILE_NAME
4 --------------------------------------------------------------------------------
5 /oraclesoft/admin/bbk/udump/bbk_ora_15992.trc

 

  说明：gettrcname是一个用于获取跟踪文件的脚本。
  内容：

 1 SELECT    d.VALUE
 2        || '/'
 3        || LOWER (RTRIM (i.INSTANCE, CHR (0)))
 4        || '_ora_'
 5        || p.spid
 6        || '.trc' trace_file_name
 7   FROM (SELECT p.spid
 8           FROM v$mystat m, v$session s, v$process p
 9          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10        (SELECT t.INSTANCE
11           FROM v$thread t, v$parameter v
12          WHERE v.NAME = 'thread'
13            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14        (SELECT VALUE
15           FROM v$parameter
16          WHERE NAME = 'user_dump_dest') d
17 /

 

  编辑创建新的控制文件脚本：createctlf.sql
  接下来是运行这个脚本创建新的控制文件：

 1 STARTUP NOMOUNT
 2 CREATE CONTROLFILE REUSE DATABASE "BBK" NORESETLOGS  ARCHIVELOG
 3     MAXLOGFILES 64
 4     MAXLOGMEMBERS 3
 5     MAXDATAFILES 100
 6     MAXINSTANCES 1
 7     MAXLOGHISTORY 292
 8 LOGFILE
 9   GROUP 1 (
10     '/oracledata/data/bbk/redo01a.rdo',
11     '/oracledata/data/bbk/redo01b.rdo'
12   ) SIZE 50M,
13   GROUP 2 (
14     '/oracledata/data/bbk/redo02a.rdo',
15     '/oracledata/data/bbk/redo02b.rdo'
16   ) SIZE 50M,
17   GROUP 3 (
18     '/oraclebak/bbk/redo03a.rdo',
19     '/oracledata/data/bbk/redo03b.rdo'
20   ) SIZE 50M,
21   GROUP 4 (    '/oraclebak/bbk/redo04a.rdo',
22     '/oracledata/data/bbk/redo04b.rdo'
23   ) SIZE 50M
24 -- STANDBY LOGFILE
25 DATAFILE
26   '/oracledata/data/bbk/system01.dbf',
27   '/oracledata/data/bbk/undotbs01.dbf',
28   '/oracledata/data/bbk/sysaux01.dbf',
29   '/oracledata/data/bbk/bbk01.dbf',
30   '/oraclebak/bbk/bbk02.dbf',
31   '/oracledata/data/bbk/assm01.dbf',
32   '/oracledata/data/bbk/mssm01.dbf',
33   '/oraclebak/bbk/u22.dbf',
34   '/oraclebak/bbk/bbk01.dbf',
35   '/oracledata/data/bbk/undotbs02.dbf',
36   '/oraclebak/bbk/system02.dbf',
37   '/oraclebak/bbk/bbk03.dbf',
38   '/oraclebak/bbk/wilson01.dbf',
39   '/oracledata/data/bbk/xifenfei.dbf',
40   '/oracledata/data/bbk/bbkzzz01.dbf'
41 CHARACTER SET UTF8
42 RECOVER DATABASE
43 ALTER SYSTEM ARCHIVE LOG ALL;
44 ALTER DATABASE OPEN;
45 ALTER TABLESPACE TEMP ADD TEMPFILE '/oracledata/data/bbk/temps01.dbf'
46      SIZE 209715200  REUSE AUTOEXTEND OFF;
47 ALTER TABLESPACE ESEAL ADD TEMPFILE '/oracledata/data/bbk/temps02.dbf'
48      SIZE 209715200  REUSE AUTOEXTEND OFF;

 

 1 [oracle@oracle10gserver ~]$ ./sql.sh 
 2 
 3 SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 2月 9 19:45:14 2012
 4 
 5 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 6 
 7 SQL> conn /as sysdba
 8 已连接到空闲例程。
 9 SQL> @createctlf
10 ORACLE 例程已经启动。
11 
12 Total System Global Area  268435456 bytes
13 Fixed Size      1218892 bytes
14 Variable Size    176162484 bytes
15 Database Buffers    88080384 bytes
16 Redo Buffers      2973696 bytes
17 
18 控制文件已创建。
19 
20 完成介质恢复。
21 
22 系统已更改。
23 
24 
25 数据库已更改。
26 
27 
28 表空间已更改。
29 
30 
31 表空间已更改。
32 
33 
34 SQL> select status from v$instance;
35 
36 STATUS
37 ------------------------
38 OPEN

 

6.2  创建控制文件2(方法二，用于参考)
 
    1).创建初始化控制文件
 
    CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
                     /u02/oracle/prod/control02.ctl,
                     /u03/oracle/prod/control03.ctl)
 
    注：若已经存在与指定名同名的文件，则在CREATE DATABASE语句中指定CONTROLFILE REUSE子句。而且新控制文件与原先控制文件的SIZE必须相同。
 
    2).创建额外副本、重命名和重定位控制文件
 
    ① 关闭数据库
    ② 在操作系统中复制、修改原控制文件
    ③ 修改CONTROL_FILES，添加或修改新的控制文件名
    ④ 重新启动数据库
 
    3).创建新的控制文件
 
    以下情况可能会需要重新创建新的控制文件：
 
    * 数据库所有控制文件都收到永久性损坏，且无任何备份
    * 希望修改数据库参数的永久性设置，例如：SID、MAXDATAFILES、MAXLOGFILES、MAXLOGMEMBERS、MAXLOGHISTORY、MAXINSTANCES等参数
 
    4).创建语句示例
 
    CREATE CONTROLFILE
    SET DATABASE prod
    LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
                     '/u01/oracle/prod/redo01_02.log'),
            GROUP 2 ('/u01/oracle/prod/redo02_01.log',
                     '/u01/oracle/prod/redo02_02.log'),
            GROUP 3 ('/u01/oracle/prod/redo03_01.log',
                     '/u01/oracle/prod/redo03_02.log')
    NORESETLOGS
    DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
             '/u01/oracle/prod/rbs01.dbf' SIZE 5M,
             '/u01/oracle/prod/users01.dbf' SIZE 5M,
             '/u01/oracle/prod/temp01.dbf' SIZE 5M
    MAXLOGFILES 50
    MAXLOGMEMBERS 3
    MAXDATAFILES 200
    MAXINSTANCES 6
    ARCHIVELOG;
 
    注：CREATE CONTROLFILE 语句可能会损坏数据文件和联机重做日志文件，省略文件名会引起文件中数据的丢失，或失去访问完整数据库的能力，所以在使用这条语句时需要特别小心。
 
    5).创建步骤(严格遵守)
 
    ① 制作包括数据库所有数据文件和联机重做日志文件的列表，可以使用以下SQL列出清单
        selectmemberfrom v$logfile; --联机重做日志文件
        select name from v$datafile; -- 数据文件
        select value from v$parameter where name = 'control_files' ; -- 控制文件
    ② 关闭数据库(IMMEDIATE、ABORT)
    ③ 备份数据库的所有数据文件和联机重做日志文件
    ④ 启动一个新的实例，但不要装载和打开数据库(NOMOUNT)
    ⑤ 使用CREATE CONTROLFILE语句创建一个新的控制文件
        注：若重命名数据库，或联机重做日志文件丢失，可使用RESETLOGS子句
    ⑥ 在离线存储设备上存储新的控制文件备份
    ⑦ 编辑CONTROL_FILES初始化参数，若重命名数据库，则编辑DB_NAME参数
    ⑧ 进行数据库恢复(如果需要的话 )
        注：若指定RESETLOGS，则需要加入USING BACKUP CONTROL FILE来恢复数据库
    ⑨ 打开数据库(ALTER DATABASE OPEN;)
    
    
   
6.3. Creating New Control Files

6.3.1 When to Create New Control Files:

  It is necessary for you to create new control files in the following situations:

    1).All control files for the database have been permanently damaged and you do not have a control file backup.

    2).You want to change the database name.

    For example, you would change a database name if it conflicted with another database name in a distributed environment.
   
    3).The compatibility level is set to a value that is earlier than 10.2.0, and you must make a change to an area of database configuration that relates to any of the following parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.
   
6.3.2 The Create Controlfile Statement
  You can create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the prod database (a database that formerly used a different database name):
  
   

 1 CREATE CONTROLFILE
 2    SET DATABASE prod
 3    LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', 
 4                     '/u01/oracle/prod/redo01_02.log'),
 5            GROUP 2 ('/u01/oracle/prod/redo02_01.log', 
 6                     '/u01/oracle/prod/redo02_02.log'),
 7            GROUP 3 ('/u01/oracle/prod/redo03_01.log', 
 8                     '/u01/oracle/prod/redo03_02.log') 
 9    RESETLOGS
10    DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
11             '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
12             '/u01/oracle/prod/users01.dbs' SIZE 5M,
13             '/u01/oracle/prod/temp01.dbs' SIZE 5M
14    MAXLOGFILES 50
15    MAXLOGMEMBERS 3
16    MAXLOGHISTORY 400
17    MAXDATAFILES 200
18    MAXINSTANCES 6
19    ARCHIVELOG;

 

  
6.3.3 Steps for Creating New Control Files
  Complete the following steps to create a new control file.
 
    1.Make a list of all datafiles and redo log files of the database.

      If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.

      SELECT MEMBER FROM V$LOGFILE;
      SELECT NAME FROM V$DATAFILE;
      SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';

      If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.

    2.Shut down the database.

      If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.

    3.Back up all datafiles and redo log files of the database.

    4.Start up a new instance, but do not mount or open the database:

      STARTUP NOMOUNT

    5.Create a new control file for the database using the CREATE CONTROLFILE statement.

      When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

    6.Store a backup of the new control file on an offline storage device. See "Backing Up Control Files" for instructions for creating a backup.

    7.Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the new name.

    8.Recover the database if necessary. If you are not recovering the database, skip to step 9.

      If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.

      If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.

    9.Open the database using one of the following methods:

      If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.

      ALTER DATABASE OPEN;

      If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

      ALTER DATABASE OPEN RESETLOGS;

The database is now open and available for use.  

 

转载于:https://www.cnblogs.com/Richardzhu/articles/2805153.html