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

原创

客居天涯 2012-02-28 14:38:59 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

第六章：控制文件(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;

    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.

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

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.