oracle 關於--控制檔案
控制檔案：
控制檔案是一個二進位制檔案，是資料庫的一部分，這個控制檔案是用於記錄資料庫的狀態和物理結構。
每個資料庫必須要至少一個控制檔案，但是建議超過一個控制檔案，最多能定義八個控制檔案（多個控制檔案時映象的關係），每個控制檔案的備份應該放在不同的磁碟上。控制檔案的位置是由引數檔案定義的。資料庫在mount之後就會一直使用控制檔案。控制檔案只能連線一個資料庫。
控制檔案包含如下資訊：
資料庫名字和標識
資料庫建立的時間戳
表空間名字
資料檔案的名字和位置
redo log的名字和位置
最新日誌的序列號
checkpoint 資訊
回滾段的開始和結束
最近的 RMAN備份
聯機重做日誌的歸檔資訊
檢視控制檔案資訊
|
SQL> show parameter control
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 20 control_files string +DATA/fengzi/controlfile/curre nt.269.842186993, +DATA/fengzi /controlfile/current.270.84218 6999
SQL> select name,value from v$parameter where name='control_files';
NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- control_files +DATA/fengzi/controlfile/current.269.842186993, +DATA/fengzi/controlfile/current .270.842186999
SQL> SQL> select name,status from v$controlfile;
NAME -------------------------------------------------------------------------------- STATUS ------- +DATA/fengzi/controlfile/current.269.842186993
+DATA/fengzi/controlfile/current.270.842186999
SQL>
|
檢視控制檔案內容
|
檔案系統的話： 可以直接檢視內容 [oracle@dongyang dbs]$ strings $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl
使用備份方式 （只顯示一部分的內容） SQL> alter database backup controlfile to trace as '/u01/app/setup.ctl';
Database altered.
SQL> [oracle@dongyang dbs]$ cd /u01/app [oracle@dongyang app]$ ls as.sql oracle setup.ctl [oracle@dongyang app]$ cat setup.ctl STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "FENGZI" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA/fengzi/onlinelog/group_1.272.842187017' ) SIZE 50M, GROUP 2 ( '+DATA/fengzi/onlinelog/group_2.274.842187053' ) SIZE 50M, GROUP 3 ( '+DATA/fengzi/onlinelog/group_3.276.842187083' ) SIZE 50M -- STANDBY LOGFILE
DATAFILE '+DATA/fengzi/datafile/system.277.842187103', '+DATA/fengzi/datafile/undotbs1.278.842187181', '+DATA/fengzi/datafile/sysaux.279.842187235', '+DATA/fengzi/datafile/users.281.842187289' CHARACTER SET ZHS16GBK ; 查詢檢視 SQL> select type,record_size from v$controlfile_record_section;
TYPE RECORD_SIZE ---------------------------- ----------- DATABASE 316 CKPT PROGRESS 8180 REDO THREAD 256 REDO LOG 72 DATAFILE 428 FILENAME 524 TABLESPACE 68 TEMPORARY FILENAME 56 RMAN CONFIGURATION 1108 LOG HISTORY 56 OFFLINE RANGE 200
TYPE RECORD_SIZE ---------------------------- ----------- ARCHIVED LOG 584 BACKUP SET 40 BACKUP PIECE 736 BACKUP DATAFILE 116 BACKUP REDOLOG 76 DATAFILE COPY 660 BACKUP CORRUPTION 44 COPY CORRUPTION 40 DELETED OBJECT 20 PROXY COPY 852 BACKUP SPFILE 36
TYPE RECORD_SIZE ---------------------------- ----------- DATABASE INCARNATION 56 FLASHBACK LOG 84 RECOVERY DESTINATION 180 INSTANCE SPACE RESERVATION 28 REMOVABLE RECOVERY FILES 32 RMAN STATUS 116 THREAD INSTANCE NAME MAPPING 80 MTTR 100 DATAFILE HISTORY 568 STANDBY DATABASE MATRIX 400 GUARANTEED RESTORE POINT 212
TYPE RECORD_SIZE ---------------------------- ----------- RESTORE POINT 212
34 rows selected.
SQL>
使用轉儲方式 SQL> alter session set events 'immediate trace name controlf level 8';
System altered.
SQL> SQL> show parameter user_d
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string /u01/app/oracle/admin/fengzi/u dump SQL> [oracle@dongyang app]$ ll /u01/app/oracle/admin/fengzi/udump/ -t total 1548 -rw-r----- 1 oracle oinstall 132221 Mar 24 17:06 fengzi_ora_6281.trc -rw-r----- 1 oracle oinstall 2021 Mar 24 13:39 fengzi_ora_5892.trc -rw-r----- 1 oracle oinstall 686 Mar 24 13:39 fengzi_ora_5880.trc -rw-r----- 1 oracle oinstall 629 Mar 24 13:39 fengzi_ora_5853.trc -rw-r----- 1 oracle oinstall 752 Mar 24 13:22 fengzi_ora_5764.trc -rw-r----- 1 oracle oinstall 686 Mar 24 13:22 fengzi_ora_5752.trc -rw-r----- 1 oracle oinstall 629 Mar 24 13:22 fengzi_ora_5725.trc -rw-r----- 1 oracle oinstall 816 Mar 24 13:20 fengzi_ora_5662.trc -rw-r----- 1 oracle oinstall 551 Mar 24 09:50 fengzi_ora_4400.trc -rw-r----- 1 oracle oinstall 2020 Mar 24 09:03 fengzi_ora_4122.trc -rw-r----- 1 oracle oinstall 1372 Mar 24 09:03 fengzi_ora_4111.trc -rw-r----- 1 oracle oinstall 657 Mar 24 09:03 fengzi_ora_4074.trc -rw-r----- 1 oracle oinstall 551 Mar 21 15:39 fengzi_ora_7452.trc -rw-r----- 1 oracle oinstall 551 Mar 21 15:38 fengzi_ora_7443.trc -rw-r----- 1 oracle oinstall 2030 Mar 21 11:26 fengzi_ora_4271.trc -rw-r----- 1 oracle oinstall 686 Mar 21 11:26 fengzi_ora_4256.trc -rw-r----- 1 oracle oinstall 629 Mar 21 11:26 fengzi_ora_4219.trc -rw-r----- 1 oracle oinstall 721 Mar 21 11:10 fengzi_ora_3537.trc -rw-r----- 1 oracle oinstall 752 Mar 21 10:58 fengzi_ora_8130.trc -rw-r----- 1 oracle oinstall 686 Mar 21 10:58 fengzi_ora_8116.trc -rw-r----- 1 oracle oinstall 629 Mar 21 10:58 fengzi_ora_8080.trc -rw-r----- 1 oracle oinstall 902 Mar 21 10:33 fengzi_ora_7828.trc -rw-r----- 1 oracle oinstall 2023 Mar 21 09:02 fengzi_ora_4129.trc
[oracle@dongyang app]$ vi /u01/app/oracle/admin/fengzi/udump/ fengzi_ora_6281.trc
/u01/app/oracle/admin/fengzi/udump/fengzi_ora_6281.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: dongyang Release: 2.6.32-200.13.1.el5uek Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011 Machine: x86_64 Instance name: fengzi Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 6281, image: oracle@dongyang (TNS V1-V3)
*** 2014-03-24 17:06:14.500 *** SERVICE NAME:(SYS$USERS) 2014-03-24 17:06:14.499 *** SESSION ID:(324.5) 2014-03-24 17:06:14.499 DUMP OF CONTROL FILES, Seq # 705 = 0x2c1 V10 STYLE FILE HEADER: Compatibility Vsn = 169869568=0xa200100 --控制檔案的版本號 Db ID=1573521836=0x5dca09ac, Db Name='FENGZI' ---資料庫的庫名與dbid Activation ID=0=0x0---活動ID Control Seq=705=0x2c1, File size=430=0x1ae---控制檔案序列號 與控制檔案大小 File Number=0, Blksiz=16384, File Type=1 CONTROL—檔案號，塊大小，檔案型別等等 Logical block number 1 (header block) *************************************************************************** DATABASE ENTRY *************************************************************************** (size = 316, compat size = 316, section max = 1, section in-use = 1, last-recid= 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 1, numrecs = 1) 03/14/2014 12:49:48 DB Name "FENGZI" Database flags = 0x00404001 0x00001000 Controlfile Creation Timestamp 03/14/2014 12:50:02 Incmplt recovery scn: 0x0000.00000000 Resetlogs scn: 0x0000.00000001 Resetlogs Timestamp 03/14/2014 12:49:48 -----時間戳的一些資訊 Prior resetlogs scn: 0x0000.00000000 Prior resetlogs Timestamp 01/01/1988 00:00:00 Redo Version: compatible=0xa200100 #Data files = 4, #Online files = 4 Database checkpoint: Thread=1 scn: 0x0000.000b2593 -----資料庫啟動的SCN Threads: #Enabled=1, #Open=1, Head=1, Tail=1 enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Max log members = 3, Max data members = 1 Arch list: Head=2, Tail=2, Force scn: 0x0000.000a00cbscn: 0x0000.000afce0 Activation ID: 1573474988 Controlfile Checkpointed at scn: 0x0000.000b25dd 03/26/2014 09:32:31 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
*************************************************************************** CHECKPOINT PROGRESS RECORDS *************************************************************************** (size = 8180, compat size = 8180, section max = 11, section in-use = 0, last-recid= 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 2, numrecs = 11) THREAD #1 - status:0x2 flags:0x0 dirty:92 low cache rba:(0x23.3feb.0) on disk rba:(0x23.4233.0) on disk scn: 0x0000.000b2691 03/26/2014 09:32:37 resetlogs scn: 0x0000.00000001 03/14/2014 12:49:48 heartbeat: 843241904 mount id: 1574539612 THREAD #2 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 THREAD #3 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 THREAD #4 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 THREAD #5 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 THREAD #6 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 THREAD #7 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 THREAD #8 - status:0x0 flags:0x0 dirty:0 low cache rba:(0x0.0.0) on disk rba:(0x0.0.0) on disk scn: 0x0000.00000000 01/01/1988 00:00:00 resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00 heartbeat: 0 mount id: 0 *************************************************************************** LOG FILE RECORDS *************************************************************************** (size = 72, compat size = 72, section max = 16, section in-use = 3, last-recid= 3, old-recno = 0, last-recno = 0) (extent = 1, blkno = 10, numrecs = 16) LOG FILE #1: (name #1) +DATA/fengzi/onlinelog/group_1.271.842187003 (name #2) +DATA/fengzi/onlinelog/group_1.272.842187017 Thread 1 redo log links: forward: 2 backward: 0 siz: 0x19000 seq: 0x00000022 hws: 0xb bsz: 512 nab: 0x456e flg: 0x1 dup: 2 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a00cb Low scn: 0x0000.000a8115 03/24/2014 09:03:31 --再觸發DBWR的時候，在LOW SCN到NEXT SCN之間所有redo記錄的 Next scn: 0x0000.000afce0 03/24/2014 13:39:46 的資料就被DBWR寫入到資料檔案中 LOG FILE #2: (name #3) +DATA/fengzi/onlinelog/group_2.273.842187037 (name #4) +DATA/fengzi/onlinelog/group_2.274.842187053 Thread 1 redo log links: forward: 3 backward: 1 siz: 0x19000 seq: 0x00000023 hws: 0x8 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a8115 Low scn: 0x0000.000afce0 03/24/2014 13:39:46 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 LOG FILE #3: (name #5) +DATA/fengzi/onlinelog/group_3.275.842187067 (name #6) +DATA/fengzi/onlinelog/group_3.276.842187083 Thread 1 redo log links: forward: 0 backward: 2 siz: 0x19000 seq: 0x00000021 hws: 0xa bsz: 512 nab: 0x462b flg: 0x1 dup: 2 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00099cf8 Low scn: 0x0000.000a00cb 03/21/2014 11:26:40 Next scn: 0x0000.000a8115 03/24/2014 09:03:31
*************************************************************************** DATA FILE RECORDS *************************************************************************** (size = 428, compat size = 428, section max = 100, section in-use = 4, last-recid= 43, old-recno = 0, last-recno = 0) (extent = 1, blkno = 11, numrecs = 100) DATA FILE #1: (name #7) +DATA/fengzi/datafile/system.277.842187103 creation size=38400 block size=8192 status=0xe head=7 tail=7 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:70 scn: 0x0000.000b2593 03/26/2014 09:32:23 -- Stop scn: 0xffff.ffffffff 03/26/2014 09:31:45 Creation Checkpointed at scn: 0x0000.0000001a 03/14/2014 12:52:49 thread:1 rba:(0x1.3.10) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.00000000 prev_range: 0 Online Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 ……. 還有很多很多的資料庫資訊
還有一種方法：使用oradebug SQL> oradebug setmypid Statement processed. SQL> SQL> oradebug dump controlf 10; Statement processed. SQL>select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID ------------ 4674
SQL> SQL> show parameter user_d
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string /u01/app/oracle/admin/fengzi/u dump SQL> [oracle@dongyang udump]$ pwd /u01/app/oracle/admin/fengzi/udump [oracle@dongyang udump]$ ls *4674* fengzi_ora_4674.trc [oracle@dongyang udump]$ vi fengzi_ora_4674.trc 可以直接根據查詢到的SPID 直接找到檔案 |
控制檔案的管理
控制檔案的大小最好不要超過100m
對於新增控制檔案
|
可以參考我的文件 http://blog.itpub.net/29532781/viewspace-1108114/
|
對於控制檔案的備份
|
只適用歸檔模式 SQL> alter database backup controlfile to '/u01/app/oracle/backcontrol2014.bak';
可以直接複製出建立控制檔案的指令碼 SQL> alter database backup controlfile to trace as '/u01/app/oracle/backcontrolctl.txt';
還可以使用RMAN 備份 [oracle@dongyang udump]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 26 10:57:11 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: FENGZI (DBID=1573521836)
RMAN> backup current controlfile; （備份當前使用的controlfile）
Starting backup at 26-MAR-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=316 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 26-MAR-14 channel ORA_DISK_1: finished piece 1 at 26-MAR-14 piece handle=+DATA/fengzi/backupset/2014_03_26/ncnnf0_tag20140326t105759_0.301.843217083 tag=TAG20140326T105759 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09 Finished backup at 26-MAR-14
Starting Control File and SPFILE Autobackup at 26-MAR-14 piece handle=+DATA/fengzi/autobackup/2014_03_26/s_843217088.302.843217089 comment=NONE Finished Control File and SPFILE Autobackup at 26-MAR-14
|
對於ORA-00214錯誤
這個錯誤就是控制檔案的版本號不一致
資料庫在啟動到mount狀態時就會讀取控制檔案，判斷是否存在，是否一致。
解決：
|
使用拷貝的方法（一定要使用版本號高的） 示例： ORA-00214 control file ‘/u01/app/oracle/oradata/orcl/control01.ctl’ version 999 Inconsistent with file ‘/u01/app/oracle/oradata/orcl/control02.ctl’ version 888 首先要檢視你使用幾個控制檔案 SQL> show parameter control_files 拷貝高版本號到低版本號的檔案 SQL>ho cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl 如果能啟動到mount狀態說明成功解決 SQL> alter database mount; SQL> alter database open;
或者直接修改引數檔案，啟動時只使用高版本號的controlfile（不推薦使用，控制檔案最好多個）
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl' scope=spfile; 因為此引數只能修改到spfile 所以需要重啟資料庫生效 SQL>shutdown immediate; SQL>startup
|
對於控制檔案丟失 ORA-00205
首先一定要確認資料庫是否處於歸檔模式
非歸檔模式
在非歸檔模式下，如果定義的控制檔案全部丟失就需要重建控制檔案
|
處於非歸檔模式（需要重建控制檔案且聯機重做日誌不能丟失） 可以根據自己的告警日誌檢視相關內容 [oracle@dongyang bdump]$ vi /u01/app/oracle/admin/fengzi/bdump alert_fengzi.log CREATE DATABASE "fengzi" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE SIZE 300M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER SET ZHS16GBK NATIONAL CHARACTER SET UTF8 LOGFILE GROUP 1 SIZE 51200K, GROUP 2 SIZE 51200K, GROUP 3 SIZE 51200K USER SYS IDENTIFIED BY *USER SYSTEM IDENTIFIED BY
根據情況檢視資料庫datafile、redolog存放位置 檔案系統 [oracle@dongyang ~]$ ls $ORACLE_BASE/oradata/$ORACLE_SID Control02.ctl control03.ctl redo01.log redo02.log redo03/log system01.dbf Undotbs01.dbf example01.dbf sysaux01.dbf temp01.dbf users01.dbf
ASM [oracle@dongyang ~]$ echo $ORACLE_SID +ASM [oracle@dongyang ~]$ asmcmd ASMCMD>cd data/fengzi/datafile ASMCMD> ls SYSAUX.279.842187235 SYSTEM.277.842187103 UNDOTBS1.278.842187181 USERS.281.842187289 ASMCMD> ASMCMD> pwd +data/fengzi/ONLINELOG ASMCMD> ls -s Block_Size Blocks Bytes Space Name 512 102401 52429312 120586240 group_1.272.842187017 512 102401 52429312 120586240 group_2.274.842187053 512 102401 52429312 120586240 group_3.276.842187083
建立控制檔案 SQL> create controlfile reuse database fengzi noarchivelog noresetlogs （這裡一定要noresetlogs） 2 maxlogfiles 16 3 maxinstances 8 4 maxlogmembers 3 5 maxloghistory 1 6 datafile 7 ‘+data /fengzi/datafile/ SYSAUX.279.842187235’, 8 ‘+data /fengzi/datafile/ SYSTEM.277.842187103’, 9 ‘+data /fengzi/datafile/ UNDOTBS1.278.842187181’, 10 ‘+data /fengzi/datafile/ USERS.281.842187289’ 11 logfile 12 group 1 ‘+data/fengzi/ONLINELOG/ group_1.272.842187017’ size 50m, 13 group 2 ‘+data/fengzi/ONLINELOG/ group_2.274.842187053’ size 50m, 14 group 3 ’ +data/fengzi/ONLINELOG/ group_3.276.842187083’ size 50m 15 character set zhs16gbk 16/
Ok 建立完成，需要做一次資料庫恢復 SQL> recover database;
恢復完成資料庫就可以開啟了 SQL>alter database open；
|
歸檔模式
在歸檔模式下，是可以使用備份進行恢復的。也可以建立控制檔案。
如果是RMAN自動備份了控制檔案那麼恢復就簡單了
SQL> shutdown abort
[oracle@dongyang ~]$ rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;
|
以前的備份語句 SQL> alter database backup controlfile to '/u01/app/oracle/backcontrol2014.ctl';
需要檢視alert日誌 vi $ORACLE_BASE/admin/$ORACLE_SID/bdump/ alert_fengzi.log
檢視定義的控制檔案（根據查詢的內容恢復） SQL> show parameter control_files
檔案系統 SQL> show parameter control_files
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/fengzi/control01.ctl, /u01/app/oracle/oradata/fengzi/control02.ctl, /u01/app/oracle/oradata/fengzi/control03.ctl、 確認檔案還存不存在 SQL> ho ls /u01/app/oracle/oradata/fengzi/control01.ctl, SQL> ho ls /u01/app/oracle/oradata/fengzi/control02.ctl, SQL> ho ls /u01/app/oracle/oradata/fengzi/control03.ctl, 如果全部丟失
使用舊的備份恢復controlfile SQL> ho cp /u01/app/oracle/backcontrol2014.ctl /u01/app/oracle/oradata/$ORACLE_SID/control01.ctl SQL> ho cp /u01/app/oracle/backcontrol2014.ctl /u01/app/oracle/oradata/$ORACLE_SID/control02.ctl SQL> ho cp /u01/app/oracle/backcontrol2014.ctl /u01/app/oracle/oradata/$ORACLE_SID/control03.ctl
SQL>alter database mount; SQL>alter database open； Ora-01589：must use RESETLOGS or NORESETLOGS…. SQL> alter database open resetlogs； ORA-01152： file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: ‘/u01/app/oracle/oradata/fengzi/system01.dbf’ SQL> alter database recover database using backup controlfile； ORA-00279: change 1275156 …….. ORA-00289:suggestion: ORA-00280: change 1275156 for …… SQL> shutdown immediate; SQL> startup Ora-01589：must use RESETLOGS or NORESETLOGS…. QL> alter database open resetlogs； ORA-01113:file 1 needs media recovery ORA-01110: data file 1: ‘/u01/app/oracle/oradata/fengzi/system01.dbf’ SQL> recover database using backup controlfile;
Specify
log:{ /u01/app/oracle/oradata/fengzi/control01.ctl --一個一個的輸入你的控制檔案目錄直到找到為止 Log applied. Media recovery complete. SQL> alter database open resetlogs；
SQL> archive log list; archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 0 Next log sequence to archive 1 Current log sequence 1
ASM SQL> show parameter control_files
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/fengzi/controlfile/curre nt.269.842186993, +DATA/fengzi /controlfile/current.270.84218 6999
SQL> shutdown abort [oracle@dongyang ~]$ rman target / RMAN>alter database nomount; RMAN> restore controlfile from '/u01/app/oracle/backcontrol2014.ctl'; RMAN> alter database mount; RMAN> recover database; RMAN> alter database open resetlogs;
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1130089/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 控制檔案Oracle
- ORACLE 控制檔案（Control Files）概述Oracle
- Oracle 控制檔案損壞解決方案Oracle
- 關於Docx動態控制word模板檔案的資料
- Oracle 11g 重新建立控制檔案Oracle
- oracle11g修改控制檔案路徑Oracle
- oracle 控制檔案及引數檔案何時自動備份Oracle
- 【RMAN】Oracle中如何備份控制檔案？備份控制檔案的方式有哪幾種？Oracle
- 與控制檔案有關的恢復
- 關於檔案的open方法
- oracle快速拿到重建控制檔案語句的方法二Oracle
- 清除Oracle控制檔案中的歸檔資訊v$archived_logOracleHive
- 與控制檔案有關的恢復(二)
- 關於Java使用MinIO檔案伺服器操作檔案Java伺服器
- oracle ORA-01189 ORA-01110（控制檔案建立報錯）Oracle
- mysql關於ibdata檔案的理解MySql
- 2.6.1 關於初始化檔案
- 關於版本控制
- sqlserver關於filestream檔案流、filetable檔案表的總結SQLServer
- 關於C++的標頭檔案C++
- 關於node實現檔案上傳
- [20201104]關於稀疏檔案(sparse files).txt
- C#關於讀寫INI檔案C#
- 關於ubuntu修改hosts檔案的方法Ubuntu
- Android 關於 so 檔案的總結Android
- 關於Play框架的靜態檔案框架
- 2.6.4 指定控制檔案
- 關於檔案系統在建立目錄檔案和普通檔案時的區別
- ORACLE 概要檔案管理Oracle
- Oracle 密碼檔案Oracle密碼
- 4.1 關於 Oracle RestartOracleREST
- 2.5.10.2 關於資料庫時區檔案資料庫
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- Oracle 資料檔案回收Oracle
- Oracle：ASM & 密碼檔案OracleASM密碼
- 關閉（隱藏）控制檯上顯示的檔案路徑
- mysql關於db.opt檔案的總結MySql
- C語言關於標頭檔案的使用C語言