Oracle DBA课程系列笔记（6_1）

第六章：控制文件(1)
 
   1、控制文件的功能和特点
       1）定义数据库当前物理状态
       2）维护数据的一致性
       3）二进制文件
       4）在mount阶段被读取
       5）记录备份恢复信息
      
 ----------查看database控制文件位置：
 23:36:07 SQL> show parameter control                                                                                                    

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/test/c
                                                 ontrol01.ctl
23:36:57 SQL> select name from v$controlfile;                                                                                           

NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl
                                                
      
    2、控制文件的内容
        
05:45:31 SQL> alter database backup controlfile to trace;

05:45:31 SQL> show parameter dump
 
 user_dump_dest                       string      /u01/app/oracle/admin/lx01/udump
                                                
        
         CREATE CONTROLFILE REUSE DATABASE "LX01" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/disk2/lx01/oradata/redo01a.log',
    '/disk1/lx01/oradata/redo01b.log'
  ) SIZE 10M,
  GROUP 2 (
    '/disk2/lx01/oradata/redo02a.log',
    '/disk1/lx01/oradata/redo02b.log'
  ) SIZE 10M,
  GROUP 3 (
    '/disk2/lx01/oradata/redo03a.log',
    '/disk1/lx01/oradata/redo03b.log'
  ) SIZE 10M,
  GROUP 4 (
    '/disk2/lx01/oradata/redo04a.log',
    '/disk1/lx01/oradata/redo04b.log'
  ) SIZE 10M,
  GROUP 5 (
    '/disk1/lx01/oradata/redo05b.log',
    '/disk2/lx01/oradata/redo05a.log'
  ) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/lx01/system01.dbf',
  '/u01/app/oracle/oradata/lx01/rtbs01.dbf',
  '/u01/app/oracle/oradata/lx01/sysaux01.dbf',
  '/u01/app/oracle/oradata/lx01/user01.dbf',
  '/u01/app/oracle/oradata/lx01/small_undo01.dbf',
  '/u01/app/oracle/oradata/lx01/test01.dbf',
  '/u01/app/oracle/oradata/lx01/users02.dbf',
  '/u01/app/oracle/oradata/lx01/user03.dbf',
  '/u01/app/oracle/oradata/lx01/undo01.dbf',
  '/u01/app/oracle/oradata/lx01/indexes01.dbf'
CHARACTER SET ZHS16GBK
;

------------控制文件转储
 05:55:48 SQL> alter session set events 'immediate trace name controlf level 12';
     
  3、控制文件多元化
     spfile：
    
     1）查看控制文件信息和spfile 信息
    
     
      05:55:48 SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx02/control01.ctl
   
   
    05:56:45 SQL> show parameter contro

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/lx02/c
                                                 ontrol01.ctl
05:56:49 SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfilelx02.ora
05:57:00 SQL>

    2）修改control_files 参数
   
05:57:55 SQL> alter system set control_files=
05:58:01   2    '/u01/app/oracle/oradata/lx02/control01.ctl','/disk1/lx02/oradata/control02.ctl' scope=spfile;

System altered.

    3）关闭instance ，拷贝controlfile
    05:58:29 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:59:02 SQL> !
[oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/control01.ctl /disk1/lx02/oradata/control02.ctl

    4）startup 启动实例
   
    05:59:44 SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx02/control01.ctl
/disk1/lx02/oradata/control02.ctl

    pfile：
   
    1）关闭实例，修改init 文件
   
06:01:16 SQL> startup pfile=$ORACLE_HOME/dbs/initlx02.ora
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
06:01:43 SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
06:02:15 SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx02/control01.ctl

06:02:21 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
  [oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ vi initlx02.ora

control_files = $ORACLE_BASE/oradata/lx02/control01.ctl,/disk1/lx02/oradata/control02.ctl,/disk2/lx02/oradata/control03.ctl

   2）拷贝controlfile
   [oracle@oracle dbs]$ cp $ORACLE_BASE/oradata/lx02/control01.ctl /disk1/lx02/oradata/control02.ctl
[oracle@oracle dbs]$ cp $ORACLE_BASE/oradata/lx02/control01.ctl  /disk2/lx02/oradata/control03.ctl

    3）startup
    06:07:59 SQL> startup pfile=$ORACLE_HOME/dbs/initlx02.ora
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
06:08:21 SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
06:08:30 SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx02/control01.ctl
/disk1/lx02/oradata/control02.ctl
/disk2/lx02/oradata/control03.ctl

06:08:39 SQL> create spfile from pfile;

File created.

    4）控制文件查看：v$controlfile 、show parameter controlfile、 v$parameter
              
    5） 控制文件备份
   
        1)trace :用于控制文件重建 （生成的trace 文件在udump）
         06:09:32 SQL> alter database backup controlfile to trace;

Database altered.           
   
       2）binary 文件 ：控制文件的备份
       06:12:03 SQL> alter database backup controlfile to '/disk1/lx01/oradata/control.bak';

Database altered.

本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/791741