Oracle 控制檔案(CONTROLFILE)

us_yunleiwang發表於2013-12-05

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

-- 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.

   

    4.轉儲控制檔案內容(檢視控制檔案中的具體內容)

        alter session set events 'immediate trace name CONTROLF level 12'; level表示級別

                             --level1  塊頭的內容 --level2 資料檔案內容 --levle 10 |12 所有內容

        oradebug setmypid

        oradebug dump controlf 12

   

        SQL> alter system set events 'immediate trace name controlf level 10';

 

        System altered.

 

        SQL> show parameter user_dump;

 

        NAME                                 TYPE        VALUE

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

        user_dump_dest                       string      /u01/app/oracle/admin/orcl/udu

                                                         mp

       

        --檢視路徑中最新的檔案

        [root@robinson ~]# vim /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc

       

        --以下僅列出部分資訊

        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

        With the Partitioning, OLAP and Data Mining options

        ORACLE_HOME = /u01/app/oracle/10g

        System name:    Linux

        Node name:      robinson.com

        Release:        2.6.18-164.el5xen

        Version:        #1 SMP Tue Aug 18 16:06:30 EDT 2009

        Machine:        i686

        Instance name: orcl

        Redo thread mounted by this instance: 1

        Oracle process number: 19

        Unix process pid: 5110, image: oracle@robinson.com (TNS V1-V3)

 

        *** 2010-05-20 18:52:30.104

        *** SERVICE NAME:(SYS$USERS) 2010-05-20 18:52:30.084

        *** SESSION ID:(159.10) 2010-05-20 18:52:30.084

        DUMP OF CONTROL FILES, Seq # 985 = 0x3d9

         V10 STYLE FILE HEADER:

                Compatibility Vsn = 169869568=0xa200100

                Db ID=1242732291=0x4a129703, Db Name='ORCL'

                Activation ID=0=0x0

                Control Seq=985=0x3d9, File size=430=0x1ae

                File Number=0, Blksiz=16384, File Type=1 CONTROL

         Logical block number 1 (header block)

        Dump of memory from 0xB7EF7E00 to 0xB7EFBE00

           

        --使用oradebug setmypid

        SQL> oradebug setmypid  --標記當前程式

        Statement processed.

        --獲得當前的spid

        SQL> select spid from v$process where addr =

          2      (select paddr from v$session where sid =

          3          (select sid from v$mystat where rownum = 1));

 

        SPID

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

        5110

       

        SQL> oradebug dump controlf 3;--3表示包含標頭檔案和資料檔案內容

        Statement processed.

       

        --檢視user_dump_dest  路徑

        SQL> show parameter user_d  

 

        NAME                                 TYPE        VALUE

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

        user_dump_dest                       string      /u01/app/oracle/admin/orcl/udu

                                                         mp

       

        --找到當前spidtrc檔案                                            

        SQL>  ! ls /u01/app/oracle/admin/orcl/udump/*5110.*              --  */

        /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc 

        SQL>  ! cat /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc

       

        --下面列出的為部分內容

        ***************************************************************************

        RMAN CONFIGURATION RECORDS

        ***************************************************************************

         (size = 1108, compat size = 1108, section max = 50, section in-use = 0,

          last-recid= 0, old-recno = 0, last-recno = 0)

         (extent = 1, blkno = 90, numrecs = 50)

         ***************************************************************************

        FLASHBACK LOGFILE RECORDS

        ***************************************************************************

         (size = 84, compat size = 84, section max = 2048, section in-use = 0,

          last-recid= 0, old-recno = 0, last-recno = 0)

         (extent = 1, blkno = 139, numrecs = 2048)

                 

        ***************************************************************************

        THREAD INSTANCE MAPPING RECORDS

        ***************************************************************************

         (size = 80, compat size = 80, section max = 8, section in-use = 8,

          last-recid= 0, old-recno = 0, last-recno = 0)

         (extent = 1, blkno = 156, numrecs = 8)

        orcl recno=1

        UNNAMED_INSTANCE_2 recno=2

        UNNAMED_INSTANCE_3 recno=3

        UNNAMED_INSTANCE_4 recno=4

        UNNAMED_INSTANCE_5 recno=5

        UNNAMED_INSTANCE_6 recno=6

        UNNAMED_INSTANCE_7 recno=7

        UNNAMED_INSTANCE_8 recno=8

          

        ***************************************************************************

        MTTR RECORDS

        ***************************************************************************

         (size = 100, compat size = 100, section max = 8, section in-use = 1,

          last-recid= 0, old-recno = 0, last-recno = 0)

         (extent = 1, blkno = 157, numrecs = 8)

        MTTR record for thread 1

        MTTR statistics status: 3

        Init time: Avg: 47090974 us, Times measured: 4

        File open time: Avg: 11225 us, Times measured: 21

        Log block read time: Avg: 20 us, Times measured: 65536

        Data block read/claim time: Avg: 170 us, Times measured: 1000

        Data block write time: Avg: 390 us

        1000 change vector apply time: Avg: 112435 us, Times measured: 37

        Ratio Information:

        # of log blocks measured: 207891

        # of data blocks measured: 12536

        # of change vectors measured: 564444

                 

        ***************************************************************************

        STANDBY DATABASE MAP RECORDS

        ***************************************************************************

         (size = 400, compat size = 400, section max = 10, section in-use = 10,

          last-recid= 0, old-recno = 0, last-recno = 0)

         (extent = 1, blkno = 160, numrecs = 10)

                 

        ***************************************************************************

        RESTORE POINT RECORDS

        ***************************************************************************

         (size = 212, compat size = 212, section max = 2048, section in-use = 0,

          last-recid= 0, old-recno = 0, last-recno = 0)

         (extent = 1, blkno = 161, numrecs = 2048)

   

三、控制檔案的管理 

    規劃原則:多路複用,建議存放到不同的磁碟或同一磁碟不同的分割槽

    個數與位置及狀態管理:

        檢視控制檔案的狀態是否與引數定義中的相吻合,當資料庫發生結構修改時,將修改內容同時寫入控制檔案

    備份管理

    恢復管理

    新建控制檔案語句

   

    spfilepfile都可以實現對控制檔案的個數及位置管理

    spfile步驟

        修改spfile引數中的control_files   -- alter system ... scope = spfile | both |memory

        一致性關閉資料庫

        增加或減少控制檔案(cp or mv)

        啟動資料庫使用spfile

        驗證結果

       

    pfile步驟

        一致性關閉資料庫

        修改pfile引數(vivim)   修改*.control_files......這一段

        增加或減少控制檔案(cp or mv)

        啟動資料庫使用pfile

        驗證結果

       

    --演示spfile修改控制檔案

        SQL> show parameter control_file

 

        NAME                                 TYPE        VALUE

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

        control_file_record_keep_time        integer     7

        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> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl' scope =spfile;

 

        System altered.

 

        SQL> shutdown immediate;

        Database closed.

        Database dismounted.

        ORACLE instance shut down.

        SQL> startup mount

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              88082196 bytes

        Database Buffers          159383552 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

       

        --再次檢視引數檔案,已顯示為一個

        SQL> show parameter control_file

 

        NAME                                 TYPE        VALUE

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

        control_file_record_keep_time        integer     7

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

                                                         ontrol01.ctl

       

        --增加控制檔案(nomount狀態下即可修改)

        SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl',

          2  '/u01/app/oracle/oradata/orcl/control02.ctl',

          3  '/u01/app/oracle/oradata/orcl/control03.ctl'

          4  scope = spfile;

 

        System altered.

       

        --啟動時可以看到在例項階段出現了版本號不一致的問題

        SQL> startup force

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              88082196 bytes

        Database Buffers          159383552 bytes

        Redo Buffers                2973696 bytes

        ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version

        1051 inconsistent with file '/u01/app/oracle/oradata/orcl/control02.ctl'

        version 1049

       

        --處理辦法,用版本號高的控制檔案覆蓋版本號低的控制檔案

        SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control02.ctl;

 

        SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control03.ctl;

 

        SQL> alter database mount;

 

        Database altered.

 

        SQL> alter database open;

 

        Database altered.  

       

    對於控制檔案丟失的情況下,透過檢視引數檔案中設定,使用作業系統命令逐個檢視這些檔案是否存在

   

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

        /u01/app/oracle/oradata/orcl/control01.ctl

 

        SQL> host ls /u01/app/oracle/oradata/orcl/control02.ctl

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

   

    檢視控制檔案所在的目錄可用空間及控制檔案的大小,建議不要超過100MB

        SQL> host ls /u01/app/oracle/oradata/orcl/c*.ctl -lh

        -rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control01.ctl

        -rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control02.ctl

        -rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control03.ctl

 

        SQL> ! df -h

        Filesystem            Size  Used Avail Use% Mounted on

        /dev/sda2             6.4G  3.9G  2.2G  64% /

        /dev/sdd1             6.8G  3.7G  2.8G  58% /u01

        /dev/sdc2             1.2G   34M  1.1G   3% /home

        /dev/sdc1             760M   17M  704M   3% /tmp

        /dev/sda1             456M   18M  415M   5% /boot

        tmpfs                 450M     0  450M   0% /dev/shm

        none                  450M  104K  450M   1% /var/lib/xenstored

       

    控制檔案的備份

        熱備:

            alter database backup controlfile to '

';  --熱備份控制檔案

            alter database backup controlfile to trace as '

' ;--得到建立控制檔案的指令碼

        RMAN:

            backup current controlfile;

            backup database include current controlfile;

            -- 或者設定RMAN 為自動備份

            RMAN > configure controlfile autobackup on;

           

        --演示備份

        --用於歸檔模式下的恢復,直接覆蓋到控制檔案

        SQL> alter database backup controlfile to '/u01/app/oracle/control.bak';

 

        Database altered.

       

        --用於重建控制檔案

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

 

        Database altered.

      

        --使用RMAN備份

        RMAN> connect target /;

 

        connected to target database: ORCL (DBID=1242732291)

 

        RMAN> backup current controlfile;   --handle為備份檔案的路徑

 

        Starting backup at 23-MAY-10

        using target database control file instead of recovery catalog

        allocated channel: ORA_DISK_1

        channel ORA_DISK_1: sid=148 devtype=DISK

        channel ORA_DISK_1: starting full datafile backupset

        channel ORA_DISK_1: specifying datafile(s) in backupset

        including current control file in backupset

        channel ORA_DISK_1: starting piece 1 at 23-MAY-10

        channel ORA_DISK_1: finished piece 1 at 23-MAY-10

        piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/

              2010_05_23/o1_mf_ncnnf_TAG20100523T131841_5zkgon2l_.bkp tag=TAG20100523T131841 comment=NONE

        channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05

        Finished backup at 23-MAY-10

       

        RMAN> backup database include current controlfile;

 

        Starting backup at 23-MAY-10

        using target database control file instead of recovery catalog

        allocated channel: ORA_DISK_1

        channel ORA_DISK_1: sid=141 devtype=DISK

        channel ORA_DISK_1: starting full datafile backupset

        channel ORA_DISK_1: specifying datafile(s) in backupset

        input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

        input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

        input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

        input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tbs1_1.dbf

        input datafile fno=00007 name=/u01/app/oracle/oradata/orcl/tbs1_2.dbf

        input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

        input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

        channel ORA_DISK_1: starting piece 1 at 23-MAY-10

        channel ORA_DISK_1: finished piece 1 at 23-MAY-10

        piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/

              2010_05_23/o1_mf_nnndf_TAG20100523T132647_5zkh4sk2_.bkp tag=TAG20100523T132647 comment=NONE

        channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25

        channel ORA_DISK_1: starting full datafile backupset

        channel ORA_DISK_1: specifying datafile(s) in backupset

        including current control file in backupset

        including current SPFILE in backupset

        channel ORA_DISK_1: starting piece 1 at 23-MAY-10

        channel ORA_DISK_1: finished piece 1 at 23-MAY-10

        piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/

              2010_05_23/o1_mf_ncsnf_TAG20100523T132647_5zkhh5st_.bkp tag=TAG20100523T132647 comment=NONE

        channel ORA_DISK_1: backup set complete, elapsed time: 00:00:14

        Finished backup at 23-MAY-10

       

        RMAN> show all; 

 

        RMAN configuration parameters are:

        CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

        CONFIGURE BACKUP OPTIMIZATION OFF; # default

        CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

        CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

        CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

        CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

        CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

        CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

        CONFIGURE MAXSETSIZE TO UNLIMITED; # default

        CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

        CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

        CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

        CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/10g/dbs/snapcf_orcl.f'; # default

 

        RMAN> configure controlfile autobackup on;   --將控制檔案自動備份功能置為on;

 

        new RMAN configuration parameters:

        CONFIGURE CONTROLFILE AUTOBACKUP ON;

        new RMAN configuration parameters are successfully stored

       

    控制檔案的恢復管理

        控制檔案版本不一致的問題

            用較新版本的控制檔案覆蓋舊版本的控制檔案

            直接修改引數control_file

        丟失問題

            歸檔模式下

                當歸檔日誌全的時候,先做全備,然後使用備份的控制檔案恢復即可

                當歸檔日誌不全的時候,先做全備,然後建立新的控制檔案即可

            非歸檔模式下

                先做全備,然後建立新的控制檔案即可

        新建控制檔案語句

            資料庫處於mountopen狀態

            執行alter database backup controlfile to trace as '

';得到建立語句

            注意[no]archievelog [no]resetlogs 兩個引數的區別

           

    版本不一致演示 

        SQL> startup

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              88082196 bytes

        Database Buffers          159383552 bytes

        Redo Buffers                2973696 bytes

        ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version

        1051 inconsistent with file '/u01/app/oracle/oradata/orcl/control02.ctl'

        version 1049

       

        --處理辦法,用版本號高的控制檔案覆蓋版本號低的控制檔案

        SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control02.ctl;

 

        SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control03.ctl;

 

        SQL> alter database mount;

 

        Database altered.

 

        SQL> alter database open;

 

        Database altered.  

       

        --控制檔案部分丟失的演示,原本有兩個控制檔案,丟失一個

        --處理辦法:

            1.將存在的控制檔案複製到目的路徑並更改控制檔名字為正確的控制檔名稱

            2.修改控控檔案引數將丟失的控制檔案去掉(一般不建議使用)

        SQL> alter system set control_files = ' /u01/app/oracle/oradata/orcl/c ontrol01.ctl',

          2  '/u01/app/oracle/oradata/orcl/control02.ctl' scope = spfile;

 

        System altered.

 

        SQL> shutdown immediate;

        Database closed.

        Database dismounted.

        ORACLE instance shut down.

        SQL> startup

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              75499284 bytes

        Database Buffers          171966464 bytes

        Redo Buffers                2973696 bytes

        ORA-00205: error in identifying control file, check alert log for more info

               

        [oracle@robinson ~]$ tail -n 100 /u01/app/oracle/admin/orcl/bdump/alert_orcl.log

        ..........................     

        Tue Jun  8 19:03:42 2010

        starting up 1 shared server(s) ...

        MMON started with pid=11, OS id=4557

        CJQ0 started with pid=10, OS id=4555

        Tue Jun  8 19:03:44 2010

        ALTER DATABASE   MOUNT

        Tue Jun  8 19:03:44 2010

        ORA-00202: control file: '/u01/app/oracle/10g/dbs/ /u01/app/oracle/oradata/orcl/c ontrol01.ctl'

        ORA-27037: unable to obtain file status

        Linux Error: 2: No such file or directory

        Additional information: 3

        Tue Jun  8 19:03:47 2010

        ORA-205 signalled during: ALTER DATABASE   MOUNT...

 

        --從警告日誌中得知,檔名為c ontrol01.ctl的檔案不存在,故將其改為正確的檔名

                                                         

        SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl',

          2  '/u01/app/oracle/oradata/orcl/control02.ctl' scope = spfile;

 

        System altered.

 

        SQL> startup force

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              83887892 bytes

        Database Buffers          163577856 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

        Database opened.

 

    非歸檔模式下,當所有的控制檔案都丟失,只能重建控制檔案來解決

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              79693588 bytes

        Database Buffers          167772160 bytes

        Redo Buffers                2973696 bytes

        ORA-00205: error in identifying control file, check alert log for more info

       

        [oracle@robinson ~]$tail -n 100 /u01/app/oracle/admin/orcl/bdump/alert_orcl.log

        ALTER DATABASE   MOUNT

        Thu Jul 15 12:13:15 2010

        ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'

        ORA-27037: unable to obtain file status

        Linux Error: 2: No such file or directory

 

        --重建控制檔案主要有三個需要考慮的是

        --搞清各個日誌檔案的大小及位置

        --搞清各個資料檔案的位置

        --設定正確的字符集     

 

        SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NOARCHIVELOG NORESTLOGS

          2  MAXLOGFILES 16

          3  MAXLOGMEMBER 3

          4  MAXDATAFILES 20

          5  MAXINSTANCES 8

          6  MAXLOGHISTORY 3

          7  LOGFILE

          8    GROUP 1(

          9      'u01/app/oracle/oradata/orcl/redo1.log',

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

         11       ) SIZE 50M,

         12    GROUP 2(

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

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

         15       ) SIZE 50M,

         16    GROUP 3(

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

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

         19       ) SIZE 100M 

         20  DATAFILE

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

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

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

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

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

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

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

         28  CHARACTER SET WE8ISO8859P1;

        CREATE CONTROLFILE REUSE DATABASE "orcl" NOARCHIVELOG NORESTLOGS

                                                              *

        ERROR at line 1:

        ORA-01967: invalid option for CREATE CONTROLFILE

 

        SQL> save /u01/app/oracle/oradata/rectl.sql;

        Created file /u01/app/oracle/oradata/rectl.sql

        SQL> ho vim /u01/app/oracle/oradata/rectl.sql

 

        SQL> @/u01/app/oracle/oradata/rectl.sql

 

        Control file created.

 

        --下面給出正確執行後的語句內容

        SQL> host cat /u01/app/oracle/oradata/rectl.sql

        CREATE CONTROLFILE REUSE DATABASE "orcl" NOARCHIVELOG NORESETLOGS

        MAXLOGFILES 16

        MAXLOGMEMBERS 3

        MAXDATAFILES 20

        MAXINSTANCES 8

        MAXLOGHISTORY 3

        LOGFILE

          GROUP 1(

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

             ) SIZE 50M,

          GROUP 2(

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

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

             ) SIZE 50M,

          GROUP 3(

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

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

             ) SIZE 100M

        DATAFILE

          '/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/system01.dbf',

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

        CHARACTER SET WE8ISO8859P1

        /

       

        --將資料庫切換到open 狀態

        SQL> alter database open;

 

        Database altered.

 

        SQL> select * from dual;

 

        D

        -

        X

       

        --檢視已經新產生了控制檔案

        SQL> host ls $ORACLE_BASE/oradata/orcl/control* -l  --可以看到新增了控制檔案control01.ctlcontrol02.ctl

        -rw-r----- 1 oracle oinstall 6012928 Jul 15 12:58 /u01/app/oracle/oradata/orcl/control01.ctl

        -rw------- 1 oracle oinstall 7389184 Jul 15 12:04 /u01/app/oracle/oradata/orcl/control01.ctl.bak

        -rw-r----- 1 oracle oinstall 6012928 Jul 15 12:58 /u01/app/oracle/oradata/orcl/control02.ctl

 

    歸檔模式下控制檔案全部丟失的處理

        --首先將資料庫切換到歸檔模式

        SQL> shutdown immediate;

        Database closed.

        Database dismounted.

        ORACLE instance shut down.

        SQL> startup mount;

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              79693588 bytes

        Database Buffers          167772160 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

        SQL> alter database archivelog;

        Database altered.

 

        SQL> alter database open  ;

        Database altered.

 

        --檢視歸檔的狀態

        SQL> archive log list;

        Database log mode              Archive Mode

        Automatic archival             Enabled

        Archive destination            USE_DB_RECOVERY_FILE_DEST

        Oldest online log sequence     1

        Next log sequence to archive   3

        Current log sequence           3

 

        --備份控制檔案

        SQL> alter database backup controlfile to '/u01/app/oracle/oradata/orcl/rectl.bak';

 

        Database altered.

 

        SQL> create table tb_temp(id int,col1 varchar2(20));

 

        Table created.

 

        SQL> insert into tb_temp select 1,'Robinson' from dual;

 

        1 row created.

 

        SQL> commit;

 

        Commit complete.

 

        SQL> alter system switch logfile;   --切換日誌

 

        System altered.

 

        SQL> archive log list; --日誌切換後sequence由變成

        Database log mode              Archive Mode

        Automatic archival             Enabled

        Archive destination            USE_DB_RECOVERY_FILE_DEST

        Oldest online log sequence     2

        Next log sequence to archive   4

        Current log sequence           4

 

        --模擬控制檔案全部丟失

        SQL> startup

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              79693588 bytes

        Database Buffers          167772160 bytes

        Redo Buffers                2973696 bytes

        ORA-00205: error in identifying control file, check alert log for more info

 

        SQL> show parameter control

 

        NAME                                 TYPE        VALUE

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

        control_file_record_keep_time        integer     7

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

                                                         ontrol01.ctl, /u01/app/oracle/

                                                         oradata/orcl/control02.ctl

 

        --檢視物理控制檔案是否存在                                             

        SQL> ho ls /u01/app/oracle/oradata/orcl/contr*

        ls: /u01/app/oracle/oradata/orcl/contr*: No such file or directory

 

        --建議先對資料庫作備份再將備份的控制檔案複製到引數檔案中指定的位置

 

        SQL> ho cp /u01/app/oracle/oradata/orcl/rectl.bak /u01/app/oracle/oradata/orcl/control01.ctl

 

        SQL> ho cp /u01/app/oracle/oradata/orcl/rectl.bak /u01/app/oracle/oradata/orcl/control02.ctl

 

        SQL> alter database mount;

 

        Database altered.

 

        SQL> alter database open;

        alter database open

        *

        ERROR at line 1:

        ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

        SQL> shutdown immediate;

        ORA-01109: database not open

        Database dismounted.

        ORACLE instance shut down.

       

        SQL> startup mount;

        ORACLE instance started.

 

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              79693588 bytes

        Database Buffers          167772160 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

 

        SQL> alter database open resetlogs;

 

        Database altered.

 

        SQL> select * from dual;

 

        D

        -

        X

 

        SQL> select * from tb_temp;  --表成功被恢復

 

                ID COL1

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

                 1 Robinson

 

        SQL> drop table tb_temp purge;

 

        Table dropped.

 

        SQL> archive log list;   --日誌的sequence號被置為

        Database log mode              Archive Mode

        Automatic archival             Enabled

        Archive destination            USE_DB_RECOVERY_FILE_DEST

        Oldest online log sequence     1

        Next log sequence to archive   1

        Current log sequence           1

   

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23490154/viewspace-1062366/,如需轉載,請註明出處,否則將追究法律責任。

相關文章