ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 控制文件(CONTROLFILE)- <1>

Oracle 控制文件(CONTROLFILE)- <1>

原创 Linux操作系统 作者:beatony 时间:2011-07-29 10:49:04 0 删除 编辑

Oracle 控制文件(CONTROLFILE)

分类: Oracle 体系结构 2001人阅读 评论(0) 收藏 举报

--=============================

-- Oracle 控制文件(CONTROLFILE)

--=============================

 

一、Oracle 控制文件

        为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

        记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等

        在参数文件中描述其位置,个数等等。通常采用分散放开,多路复用的原则。在mount阶段被读取,open阶段一直被使用

        维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号,如相等则正常启动,否则需要介质恢复)

        一个控制文件只能属于一个数据库

        控制文件的任意修改将写入到初始化参数中指定的所有控制文件中,读取时则仅读取第一个控制文件

        控制文件只能连接一个数据库,控制文件的大小一般不要超过MB,最多为个,最少一个,互为镜像

               

        控制文件中包含的内容

            数据库的名字、ID、创建的时间戳

            表空间的名字

            联机日志文件、数据文件的位置、个数、名字

            联机日志的Sequence号码

            检查点的信息

            撤销段的开始或结束

            归档信息

            备份信息

   

二、查看控制文件的相关信息

    1.使用相关视图来查看

        V$CONTROLFILE                   --列出实例中所有控制文件的名字及状态信息

        V$PARAMETER                     --列出所有参数的位置及状态信息

        V$CONTROLFILE_RECORD_SECTION    --列出控制文件中记录的部分信息

        SHOW PARAMETER CONTROL_FILES    --列出控制文件的名字、状态、位置等

 

        SQL> select * from v$controlfile;

        STATUS  NAME                                               IS_ BLOCK_SIZE FILE_SIZE_BLKS

        ------- -------------------------------------------------- --- ---------- --------------

                /u01/app/oracle/oradata/orcl/control01.ctl         NO       16384            430

                /u01/app/oracle/oradata/orcl/control02.ctl         NO       16384            430

                /u01/app/oracle/oradata/orcl/control03.ctl         NO       16384            430

       

        SQL> select name,type,value from  v$parameter where name like '%control%';

 

        NAME                                 TYPE VALUE

        ------------------------------ ---------- ------------------------------------------------------------

        control_files                           2 /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/

                                                  oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/con

                                                  trol03.ctl

        control_file_record_keep_time           3 7

       

        SQL> select * from v$controlfile_record_section;

        TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

        ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------

        DATABASE                             316             1            1           0          0          0

        CKPT PROGRESS                       8180            11            0           0          0          0

        REDO THREAD                          256             8            1           0          0          0

        REDO LOG                              72            16            9           0          0         20

        DATAFILE                             428           100            8           0          0         28

        FILENAME                             524          2298           21           0          0          0

        TABLESPACE                            68           100            7           0          0          7

        TEMPORARY FILENAME                    56           100            1           0          0          1

        RMAN CONFIGURATION                  1108            50            0           0          0          0

        LOG HISTORY                           56           292           35           1         35         35

        OFFLINE RANGE                        200           163            0           0          0          0

 

        TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

        ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------

        ARCHIVED LOG                         584            28           20           1         20         20

        BACKUP SET                            40           409            0           0          0          0

        BACKUP PIECE                         736           200            0           0          0          0

        BACKUP DATAFILE                      116           282            0           0          0          0

        BACKUP REDOLOG                        76           215            0           0          0          0

        DATAFILE COPY                        660           223            1           1          1          1

        BACKUP CORRUPTION                     44           371            0           0          0          0

        COPY CORRUPTION                       40           409            0           0          0          0

        DELETED OBJECT                        20           818            3           1          3          3

        PROXY COPY                           852           249            0           0          0          0

        BACKUP SPFILE                         36           454            0           0          0          0

 

        TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

        ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------

        DATABASE INCARNATION                  56           292            2           1          2          2

        FLASHBACK LOG                         84          2048            0           0          0          0

        RECOVERY DESTINATION                 180             1            1           0          0          0

        INSTANCE SPACE RESERVATION            28          1055            1           0          0          0

        REMOVABLE RECOVERY FILES              32          1000            0           0          0          0

        RMAN STATUS                          116           141            0           0          0          0

        THREAD INSTANCE NAME MAPPING          80             8            8           0          0          0

        MTTR                                 100             8            1           0          0          0

        DATAFILE HISTORY                     568            57            0           0          0          0

        STANDBY DATABASE MATRIX              400            10           10           0          0          0

        GUARANTEED RESTORE POINT             212          2048            0           0          0          0

 

        TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

        ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------

        RESTORE POINT                        212          2083            0           0          0          0

 

        SQL> show parameter control_files;

 

        NAME                                 TYPE        VALUE

        ------------------------------------ ----------- ------------------------------

        control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                         ontrol01.ctl, /u01/app/oracle/

                                                         oradata/orcl/control02.ctl, /u

                                                         01/app/oracle/oradata/orcl/con

                                                         trol03.ctl

        SQL> select controlfile_sequence# from v$database;

 

        CONTROLFILE_SEQUENCE#

        ---------------------

                          985      

                     

    2.使用STRINGS命令来查看控制文件中的具体内容

        SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl | more

        }|{z

        JORCL

        L-+RG

        +ORCL

        +ORCL

        orcl

        orcl

        -+-='

        -+-='

        /u01/app/oracle/oradata/orcl/redo03.log

        /u01/app/oracle/oradata/orcl/redo3.log

        /u01/app/oracle/oradata/orcl/redo02.log

        /u01/app/oracle/oradata/orcl/redo2.log

        /u01/app/oracle/oradata/orcl/redo01.log

        /u01/app/oracle/oradata/orcl/tbs1_2.dbf

        /u01/app/oracle/oradata/orcl/tbs1_1.dbf

        /u01/app/oracle/oradata/orcl/example01.dbf

        /u01/app/oracle/oradata/orcl/users01.dbf

        /u01/app/oracle/oradata/orcl/sysaux01.dbf

        /u01/app/oracle/oradata/orcl/undotbs01.dbf

        /u01/app/oracle/oradata/orcl/system01.dbf

        u01/app/oracle/oradata/orcl/undotbs02.dbf

        /u01/app/oracle/oradata/orcl/redo03.log

        /u01/app/oracle/oradata/orcl/redo3.log

        /u01/app/oracle/oradata/orcl/redo02.log

        /u01/app/oracle/oradata/orcl/redo2.log

        /u01/app/oracle/oradata/orcl/redo01.log

        /u01/app/oracle/oradata/orcl/tbs1_2.dbf

        /u01/app/oracle/oradata/orcl/tbs1_1.dbf

        /u01/app/oracle/oradata/orcl/example01.dbf

        /u01/app/oracle/oradata/orcl/users01.dbf

        /u01/app/oracle/oradata/orcl/sysaux01.dbf

        /u01/app/oracle/oradata/orcl/undotbs01.dbf

        /u01/app/oracle/oradata/orcl/system01.dbf

        u01/app/oracle/oradata/orcl/undotbs02.dbf

        SYSTEM

        UNDOTBS1

        SYSAUX

        USERS

        EXAMPLE

        TBS1

        TEMP

       

    3.备份控制文件到平面文件(然后查看控制文件中的具体内容)

       

        SQL> alter database backup controlfile  to trace as '/u01/app/oracle/ctl.txt';

 

        Database altered.

       

        --或者使用

        SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl > /u01/app/oracle/ctl.txt

       

        SQL> host cat /u01/app/oracle/ctl.txt;  

        -- The following are current System-scope REDO Log Archival related

        -- parameters and can be included in the database initialization file.

        --

        -- LOG_ARCHIVE_DEST=''

        -- LOG_ARCHIVE_DUPLEX_DEST=''

        --

        -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

        --

        -- DB_UNIQUE_NAME="orcl"

        --

        -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

        -- LOG_ARCHIVE_MAX_PROCESSES=2

        -- STANDBY_FILE_MANAGEMENT=MANUAL

        -- STANDBY_ARCHIVE_DEST=?/dbs/arch

        -- FAL_CLIENT=''

        -- FAL_SERVER=''

        --

        -- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'

        -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'

        -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

        -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'

        -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

        -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

        -- LOG_ARCHIVE_DEST_STATE_10=ENABLE

        --

        -- Below are two sets of SQL statements, each of which creates a new

        -- control file and uses it to open the database. The first set opens

        -- the database with the NORESETLOGS option and should be used only if

        -- the current versions of all online logs are available. The second

        -- set opens the database with the RESETLOGS option and should be used

        -- if online logs are unavailable.

        -- The appropriate set of statements can be copied from the trace into

        -- a script. file, edited as necessary, and executed when there is a

        -- need to re-create the control file.

        --

        --     Set #1. NORESETLOGS case

        --

        -- The following commands will create a new control file and use it

        -- to open the database.

        -- Data used by Recovery Manager will be lost.

        -- Additional logs may be required for media recovery of offline

        -- Use this only if the current versions of all online logs are

        -- available.

 

        -- After mounting the created controlfile, the following SQL

        -- statement will place the database in the appropriate

        -- protection mode:

        --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

 

        STARTUP NOMOUNT

        CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

            MAXLOGFILES 16

            MAXLOGMEMBERS 3

            MAXDATAFILES 100

            MAXINSTANCES 8

            MAXLOGHISTORY 292

        LOGFILE

          GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,

          GROUP 2 (

            '/u01/app/oracle/oradata/orcl/redo02.log',

            '/u01/app/oracle/oradata/orcl/redo2.log'

          ) SIZE 50M,

          GROUP 3 (

            '/u01/app/oracle/oradata/orcl/redo03.log',

            '/u01/app/oracle/oradata/orcl/redo3.log'

          ) SIZE 100M,

          GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log '  SIZE 50M,

          GROUP 7 (

            '/u01/app/oracle/oradata/orcl/redo07.log ',

            '/u01/app/oracle/oradata/orcl/redo7.log '

          ) SIZE 50M,

          GROUP 8 (

            '/u01/app/oracle/oradata/orcl/redo08.log',

            '/u01/app/oracle/oradata/orcl/redo8.log'

          ) SIZE 50M

        -- STANDBY LOGFILE

 

        DATAFILE

          '/u01/app/oracle/oradata/orcl/system01.dbf',

          '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

          '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

          '/u01/app/oracle/oradata/orcl/users01.dbf',

          '/u01/app/oracle/oradata/orcl/example01.dbf',

          '/u01/app/oracle/oradata/orcl/tbs1_1.dbf',

          '/u01/app/oracle/oradata/orcl/tbs1_2.dbf'

        CHARACTER SET WE8ISO8859P1

        ;

 

        -- Commands to re-create incarnation table

        -- Below log names MUST be changed to existing filenames on

        -- disk. Any one log file from each branch can be used to

        -- re-create incarnation records.

        -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area

        --                                   /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';

        -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area

        --                                   /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';

        -- Recovery is required if any of the datafiles are restored backups,

        -- or if the last shutdown was not normal or immediate.

        RECOVER DATABASE

 

        -- Database can now be opened normally.

        ALTER DATABASE OPEN;

 

        -- Commands to add tempfiles to temporary tablespaces.

        -- Online tempfiles have complete space information.

        -- Other tempfiles may require adjustment.

        ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

             SIZE 26214400  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

        -- End of tempfile additions.

        --

        --     Set #2. RESETLOGS case

        --

        -- The following commands will create a new control file and use it

        -- to open the database.

        -- Data used by Recovery Manager will be lost.

        -- The contents of online logs will be lost and all backups will

        -- be invalidated. Use this only if online logs are damaged.

 

        -- After mounting the created controlfile, the following SQL

        -- statement will place the database in the appropriate

        -- protection mode:

        --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

 

        STARTUP NOMOUNT

        CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG

            MAXLOGFILES 16

            MAXLOGMEMBERS 3

            MAXDATAFILES 100

            MAXINSTANCES 8

            MAXLOGHISTORY 292

        LOGFILE

          GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,

          GROUP 2 (

            '/u01/app/oracle/oradata/orcl/redo02.log',

            '/u01/app/oracle/oradata/orcl/redo2.log'

          ) SIZE 50M,

          GROUP 3 (

            '/u01/app/oracle/oradata/orcl/redo03.log',

            '/u01/app/oracle/oradata/orcl/redo3.log'

          ) SIZE 100M,

          GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log '  SIZE 50M,

          GROUP 7 (

            '/u01/app/oracle/oradata/orcl/redo07.log ',

            '/u01/app/oracle/oradata/orcl/redo7.log '

          ) SIZE 50M,

          GROUP 8 (

            '/u01/app/oracle/oradata/orcl/redo08.log',

            '/u01/app/oracle/oradata/orcl/redo8.log'

          ) SIZE 50M

        -- STANDBY LOGFILE

 

        DATAFILE

          '/u01/app/oracle/oradata/orcl/system01.dbf',

          '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

          '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

          '/u01/app/oracle/oradata/orcl/users01.dbf',

          '/u01/app/oracle/oradata/orcl/example01.dbf',

          '/u01/app/oracle/oradata/orcl/tbs1_1.dbf',

          '/u01/app/oracle/oradata/orcl/tbs1_2.dbf'

        CHARACTER SET WE8ISO8859P1

        ;

 

        -- Commands to re-create incarnation table

        -- Below log names MUST be changed to existing filenames on

        -- disk. Any one log file from each branch can be used to

        -- re-create incarnation records.

        -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area

        --                                   /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';

        -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area

        --                                    /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';

        -- Recovery is required if any of the datafiles are restored backups,

        -- or if the last shutdown was not normal or immediate.

        RECOVER DATABASE USING BACKUP CONTROLFILE

 

        -- Database can now be opened zeroing the online logs.

        ALTER DATABASE OPEN RESETLOGS;

 

        -- Commands to add tempfiles to temporary tablespaces.

        -- Online tempfiles have complete space information.

        -- Other tempfiles may require adjustment.

        ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

             SIZE 26214400  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

        -- End of tempfile additions.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-703441/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-05-18

  • 博文量
    192
  • 访问量
    455750