ITPub博客

首页 > Linux操作系统 > Linux操作系统 > create_controlfile_resetlogs_or_noresetlogs

create_controlfile_resetlogs_or_noresetlogs

原创 Linux操作系统 作者:tthero00boo 时间:2013-11-22 00:28:59 0 删除 编辑

CREATE CONTROLFILE { RESETLOGS | NORESETLOGS }

/* Syntax: */

CREATE CONTROLFILE
  [ REUSE ]
  [ SET ]
  DATABASE database
  [ logfile_clause ]
  { RESETLOGS | NORESETLOGS }
  [ DATAFILE file_specification
             [, file_specification ]... ]
  [ { MAXLOGFILES integer
    | MAXLOGMEMBERS integer
    | MAXLOGHISTORY integer
    | MAXDATAFILES integer
    | MAXINSTANCES integer
    | { ARCHIVELOG | NOARCHIVELOG }
    | FORCE LOGGING
    }
      [ MAXLOGFILES integer
      | MAXLOGMEMBERS integer
      | MAXLOGHISTORY integer
      | MAXDATAFILES integer
      | MAXINSTANCES integer
      | { ARCHIVELOG | NOARCHIVELOG }
      | FORCE LOGGING
      ]...
  ]
  [ character_set_clause ] ;

RESETLOGS 
 Specify RESETLOGS if you want Oracle Database to ignore the contents of the files listed in the LOGFILE clause.
 These files do not have to exist. Each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter.
 The database assigns all online redo log file groups to thread 1 and enables this thread for public use by any
 instance. After using this clause, you must open the database using the RESETLOGS clause of the ALTER DATABASE
 statement.

NORESETLOGS 
 Specify NORESETLOGS if you want Oracle Database to use all files in the LOGFILE clause as they were when the
 database was last open. These files must exist and must be the current online redo log files rather than restored
 backups. The database reassigns the redo log file groups to the threads to which they were previously assigned
 and reenables the threads as they were previously enabled.

/* RESETLOGS 用于logfile丢失,或恢复到以前某一时刻
  1. 不会校验logfile_clause子句中文件是否存在,但它会为你重建,所以必须指定log大小
  2. alter database open 必须 resetlogs(sequence#重置为1)
   NORESETLOGS 用于logfile全部完好的恢复
*/

只要数据库结构,datafile位置没有变化,alter database backup controlfile to trace as '/home/oracle/cf_bak.trc';
备份的trace文本都不会有变化

那么重建conf时的信息是怎样获得的? resetlogs/noresetlogs时,生成的conf有什么不同?

用noresetlogs/resetlogs重建conf后,分别转储控制文件

***************************************************************************
DATABASE ENTRY
***************************************************************************
...
 Database checkpoint: Thread=1 scn: 0x0000.00773f06  
 Threads: #Enabled=1, #Open=1, Head=1, Tail=1

/* resetlogs :
 Database checkpoint: Thread=0 scn: 0x0000.00000000
 Threads: #Enabled=1, #Open=0, Head=0, Tail=0
*/

scn: 0x0000.00773f06
[oracle@localhost ~]$ printf "%#d\n" 0x00773f06
7814918

这个scn是 当前redo log的start scn ,是从redo logfile的文件头中获得的, datafile上不会有这个信息,

SQL> select group#,sequence#,status,first_change#,next_change# from v$log;

    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1          7 INACTIVE               7814869      7814872
         2          8 INACTIVE               7814872      7814918
         3          9 CURRENT                7814918   2.8147E+14


***************************************************************************
REDO THREAD RECORDS
***************************************************************************

Checkpointed at scn:  0x0000.00773f06 11/21/2013 12:10:05
 thread:1 rba:(0x9.2.0)

/* resetlogs :
Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
*/

这里记录了current redolog ,start_scn的rba地址
当open db时,
可从alert.log中观察实例恢复的具体步骤
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 35 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 9, block 2, scn 7814918
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: /opt/oracle/oradata/myorcl11/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 9, block 73, scn 7834953
 0 data blocks read, 0 data blocks written, 35 redo k-bytes read
 ...

***************************************************************************
LOG FILE RECORDS
***************************************************************************

LOG FILE #2:
  name #2: /opt/oracle/oradata/myorcl11/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000008 hws: 0x2 bsz: 512 nab: 0x94 flg: 0x0 dup: 1
 Archive links: fwrd: 3 back: 1 Prev scn: 0x0000.00773ed5
 Low scn: 0x0000.00773ed8 11/21/2013 12:10:04
 Next scn: 0x0000.00773f06 11/21/2013 12:10:05
LOG FILE #3:
  name #1: /opt/oracle/oradata/myorcl11/redo03.log
 Thread 1 redo log links: forward: 4 backward: 2
 siz: 0x19000 seq: 0x00000009 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
 Archive links: fwrd: 0 back: 2 Prev scn: 0x0000.00773ed8
 Low scn: 0x0000.00773f06 11/21/2013 12:10:05
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

/*  resetlogs :
LOG FILE #3:
  name #4: /opt/oracle/oradata/myorcl11/redo03.log
 Thread 1 redo log links: forward: 4 backward: 2
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
*/

noresetlogs 读取logfile_clause指定的文件信息,都读进来之后Next scn: 0xffff.ffffffff
找到当前redo的Low scn -> scn:  0x0000.00773f06
写到DATABASE ENTRY ,REDO THREAD RECORDS ,LOG FILE RECORDS ,DATA FILE RECORDS

***************************************************************************
DATA FILE RECORDS
***************************************************************************
/* 控制文件中记录的datafile区 */

DATA FILE #1:
  name #19: /opt/oracle/oradata/myorcl11/system01.dbf
creation size=0 block size=8192 status=0x12 head=19 tail=19 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:570 scn: 0x0000.00773f06 01/01/1988 00:00:00
 Stop scn: 0xffff.ffffffff 11/21/2013 13:00:15


/*  resetlogs :
DATA FILE #1:
  name #19: /opt/oracle/oradata/myorcl11/system01.dbf
creation size=0 block size=8192 status=0x12 head=19 tail=19 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:570 scn: 0x0000.00773ecd 11/21/2013 12:10:01
 Stop scn: 0xffff.ffffffff 11/21/2013 12:57:24
*/

noresetlogs 把current redo的low scn当做他认为数据文件应该达到的full_checkpoint_scn

resetlogs 是把df文件头的Checkpointed at scn:  0x0000.00773ecd 当做full_checkpoint_scn

他们的Stop scn: 0xffff.ffffffff,所以势必需要做crash recovery

df文件头的Checkpoint_scn可通过
alter session set events 'immediate trace name file_hdrs level 10';

dump出的trace文件的信息包含两个部分,一部分来自控制文件,另一部分来自数据文件:

/* V10 STYLE FILE HEADER: 这之后的是从数据文件中dump的 ,之前是conf的内容 */

DATA FILE #4:
  name #16: /opt/oracle/oradata/myorcl11/users01.dbf
creation size=0 block size=8192 status=0x12 head=16 tail=16 dup=1
 tablespace 4, index=4 krfil=4 prev_file=1024
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:568 scn: 0x0000.00773f06 01/01/1988 00:00:00
 Stop scn: 0xffff.ffffffff 11/21/2013 13:00:15
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 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
 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
 aux_file is NOT DEFINED
 Plugged readony: NO
 Plugin scnscn: 0x0000.00000000
 Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Online move state: 0
 V10 STYLE FILE HEADER:
 Compatibility Vsn = 186646528=0xb200000
 Db ID=2432893466=0x9103021a, Db Name='MYORCL11'
 Activation ID=0=0x0
 Control Seq=18824=0x4988, File size=640=0x280
 File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00004743 08/13/2009 23:01:06
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x3198774e scn: 0x0000.00773ccf
 prev reset logs count:0x31985dd9 scn: 0x0000.007644c3
 recovered at 11/21/2013 12:05:47
 status:0x4 root dba:0x00000000 chkpt cnt: 568 ctl cnt:567
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00773ecd 11/21/2013 12:10:01
 thread:1 rba:(0x6.2.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
Backup 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
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp  01/01/1988 00:00:00
Platform Information:  Creation Platform ID: 10
Current Platform ID: 10 Last Platform ID: 10
 

从数据文件的rba,可以看出dbwr写到了sequence#=6的redo内容
Checkpointed at scn:  0x0000.00773ecd 11/21/2013 12:10:01
 thread:1 rba:(0x6.2.10)

以上列出了[no]resetlogs conf的不同,现在开始在noresetlogs的情况下recovery

SQL> select dbid,checkpoint_change# from v$database;

      DBID CHECKPOINT_CHANGE#
---------- ------------------
2432893466            7814918

select file#,checkpoint_change#,last_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE# CPT
---------- ------------------ ------------ -------------------
         1            7814918
         2            7814918
         3            7814918
         4            7814918
         5            7814918
         6            7814918
         7            7814918
         8            7814918
      1024            7814918
      1025            7814918
      1026            7814918
      1027            7814918
      1028            7814918

13 rows selected.

SQL>
     FILE# CHECKPOINT_CHANGE# CPT
---------- ------------------ -------------------
         1            7814861 2013-11-21 12:10:01
         2            7814861 2013-11-21 12:10:01
         3            7814861 2013-11-21 12:10:01
         4            7814861 2013-11-21 12:10:01
         5            7814861 2013-11-21 12:10:01
         6            7814861 2013-11-21 12:10:01
         7            7814861 2013-11-21 12:10:01
         8            7814861 2013-11-21 12:10:01
      1024            7814861 2013-11-21 12:10:01
      1025            7814861 2013-11-21 12:10:01
      1026            7814861 2013-11-21 12:10:01
      1027            7814861 2013-11-21 12:10:01
      1028            7814861 2013-11-21 12:10:01

13 rows selected.

 


SQL> recover database;
Media recovery complete.

 

 

查看alert.log

Thu Nov 21 14:17:16 2013
ALTER DATABASE RECOVER  database 
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 6 Seq 6 Reading mem 0
  Mem# 0: /opt/oracle/oradata/myorcl11/redo06.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
  Mem# 0: /opt/oracle/oradata/myorcl11/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
  Mem# 0: /opt/oracle/oradata/myorcl11/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: /opt/oracle/oradata/myorcl11/redo03.log
Media Recovery Complete (myorcl11)
Completed: ALTER DATABASE RECOVER  database 

由于数据文件scn比conf的scn(从redo拿来的)小
所以要做介质恢复

Group 6 Seq 6,从sequence#=6开始应用logfile,一直应用到当前redo


SQL> select dbid,checkpoint_change# from v$database;

      DBID CHECKPOINT_CHANGE#
---------- ------------------
2432893466            7814918

select file#,checkpoint_change#,last_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile;
select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') cpt from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE# CPT
---------- ------------------ ------------ -------------------
         1            7834953      7834953 2013-11-21 12:10:29
         2            7834953      7834953 2013-11-21 12:10:29
         3            7834953      7834953 2013-11-21 12:10:29
         4            7834953      7834953 2013-11-21 12:10:29
         5            7834953      7834953 2013-11-21 12:10:29
         6            7834953      7834953 2013-11-21 12:10:29
         7            7834953      7834953 2013-11-21 12:10:29
         8            7834953      7834953 2013-11-21 12:10:29
      1024            7834953      7834953 2013-11-21 12:10:29
      1025            7834953      7834953 2013-11-21 12:10:29
      1026            7834953      7834953 2013-11-21 12:10:29
      1027            7834953      7834953 2013-11-21 12:10:29
      1028            7834953      7834953 2013-11-21 12:10:29


13 rows selected.

SQL>
     FILE# CHECKPOINT_CHANGE# CPT
---------- ------------------ -------------------
         1            7834953 2013-11-21 12:10:29
         2            7834953 2013-11-21 12:10:29
         3            7834953 2013-11-21 12:10:29
         4            7834953 2013-11-21 12:10:29
         5            7834953 2013-11-21 12:10:29
         6            7834953 2013-11-21 12:10:29
         7            7834953 2013-11-21 12:10:29
         8            7834953 2013-11-21 12:10:29
      1024            7834953 2013-11-21 12:10:29
      1025            7834953 2013-11-21 12:10:29
      1026            7834953 2013-11-21 12:10:29
      1027            7834953 2013-11-21 12:10:29
      1028            7834953 2013-11-21 12:10:29

13 rows selected.

7834953--应用到当前redo的最新检查点

SQL> alter system archive log all
  2  ;

System altered.

SQL> alter database open;

Database altered.

--crash recovery

Thu Nov 21 14:21:29 2013
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 35 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 9, block 2, scn 7814918
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: /opt/oracle/oradata/myorcl11/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 9, block 73, scn 7834953
 0 data blocks read, 0 data blocks written, 35 redo k-bytes read
Thu Nov 21 14:21:30 2013

....

*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************

SQL> select group#,sequence#,status,first_change#,next_change# from v$log;

    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
         1          7 INACTIVE               7814869      7814872
         2          8 INACTIVE               7814872      7814918
         3          9 INACTIVE               7814918      7834955
         4         10 CURRENT                7834955   2.8147E+14
         5          5 INACTIVE               7814858      7814861
         6          6 INACTIVE               7814861      7814869

 

/* add临时表空间文件 */
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/myorcl11/temp01.dbf'
  2       SIZE 30408704  REUSE AUTOEXTEND OFF;

Tablespace altered.

如果使用 alter database backup controlfile to '/home/oracle/conf_bak.bin';
那么restore二进制备份的conf,需要recover database using backup controlfile;
并且要用resetlogs打开

 

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

上一篇: Spfile Pfile
下一篇: Flashback
请登录后发表评论 登录
全部评论

注册时间:2013-06-30

  • 博文量
    31
  • 访问量
    142117