控制文件：
控制文件是一个二进制文件，是数据库的一部分，这个控制文件是用于记录数据库的状态和物理结构。
每个数据库必须要至少一个控制文件，但是建议超过一个控制文件，最多能定义八个控制文件（多个控制文件时镜像的关系），每个控制文件的备份应该放在不同的磁盘上。控制文件的位置是由参数文件定义的。数据库在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:{=suggested | filename |AUTO |CANCEL} /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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29532781/viewspace-1130089/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
