`

create controlfile resetlogs or noresetlogs

 
阅读更多

概述

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时的信息
用noresetlogs重建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重建conf后转储控制文件
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)

 

参考至：http://blog.itpub.net/28859270/viewspace-777284/

如有错误，欢迎指正

邮箱：czmcj@163.com

分享到：
| Oracle Incarnation 详解与Rman跨resetlog ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    RMAN测试演练即讲解

    （6） alter database open resetlogs 6、 模拟redolog file恢复（其实下面操作在sqlplus里面，不在rman执行） （1） shutdown immediate; （2） startup mount; （3） recover database until cancel; （4） alter ...

    file control

    share the file,hope can help the people who need it.

    Oracle8i_9i数据库基础

    §8.4.1 CREATE DIMENSION语法 200 §8.4.2 创建维的例子 201 第九章 安全管理 203 §9.1 CREATE USER 命令 203 §9.2 建立用户 204 §9.2.1 外部验证（Authenticated ）用户 204 §9.2.2 全局(Globally)验证用户-...

    Oracle9i灾难恢复详细步骤 pdf

    2. 有 datafile、archivelog、controlfile、spfile 的有效备份和全备后的Archivelog，不完全恢复整个数据库。 3. Controlfile 中包括所有的backupset 信息（RMAN 信息），参考下文的备份脚本。 4. 在 controlfile 的...

    最全的oracle常用命令大全.txt

    create table 表名 （列说明列表）； 为基表增加新列命令如下： ALTER TABLE 表名 ADD （列说明列表） 例：为test表增加一列Age，用来存放年龄 sql>alter table test add （Age number(3)）； 修改基表列定义...

    Oracle归档日志删除

    Ora我们都都知道在controlfile中记录着每一个archivelog的相关信息，当然们在OS下把这些物理文件delete掉后，在我们的 controlfile中仍然记录着这些archivelog的信息，在oracle的OEM管理器中有可视化的日志展现出，...

    blackflagking#Notes-Databases#Oracle备份恢复之recover database的四条语句区别

    与recover database using backup controlfile until cancel效果一样Oracle会以当前controlfile

    第十一章实验作业.docx

    Alter database backup controlfile to ‘c:\control.bkp’; （2）以脚本文件形式备份控制文件 Alter database backup controlfile to trace; （3）查看脚本文件的存放位置 Show parameter user_dump_dest; （4）...

    oracle - answer

    ALTER DATABASE BACKUP CONTROLFILE TO ‘D:\ORACLE\CONTROL.BKP’; （7） ALTER DATABASE ADD LOGFILE GROUP 4 (‘D:\ORACLE\ORADATA\ORCL\redo04a.log’,’D:\ORACLE\ORADATA\ORCL\redo04b.log’)SIZE 5M; （8）...

    Oracle数据库.pdf

    使用数据字典v$controlfile，查看当前数据库的控制文件的名称与路 径，具体如下： SQL> COLUMN name FORMAT A50; SQL> SELECT name FROM v$controlfile; 通过SHOW PARAMETER语句可以查看块的默认大小信息 SQL> SHOW...

    cadence v16.0 安装方法

    这两天一直在试着安装 cadence v16.0 。可是按现在网上说 试过了，怎么还是装不好啊？最后找到了一个新的 crack 。

    12_ORCLE数据库管理_示例脚本

    select * from v$controlfile --列出所有参数的位置及状态信息 select * from v$parameter --列出控制文件中记录的部分信息 select * from v$controlfile_record_section -----------------------------------...

    Oracle 11g For Dummies.pdf

    Or did you suddenly become a DBA by default? If database administration with Oracle is part of your job, you’ll be glad to have Oracle 11g For Dummies in your cubicle. This nuts-and-bolts guide ...

    RMAN恢复实践

    RMAN恢复实践:详细脚本，包括controlfile,全库等等

    RAC+RMAN单实例的恢复

    1）拷贝源数据库的备份（分清是全备或是增量备份）到目标库。... 4）恢复备份中的controlfile到目标库，并使库至mount状态。 5）恢复数据，更改数据文件，临时文件，日志文件的路径。 6）恢复数据库。

    oracle实验报告

    controlfile reuse maxinstances 1 maxdatafiles 100 maxlogfiles 5 maxlogmembers 5 maxloghistory 2 logfile 'E:\app\oracle\oradata\mydb\log01.log' size 10m, 'E:\app\oracle\oradata\mydb\log02.log...

    oracle归档日志步骤+RMAN步骤

    oracle归档日志步骤+RMAN步骤 . set ORACLE_SID=EKP sqlplus /nolog ...configure controlfile autobackup on; 启动控制文件自动备份 configure retention policy to redundancy 4; 备份策略，4个版本

    oracle 安装教程

    方便好用，解决安装问题，十分好用。SELECT status,name FROM v$controlfile;

    在Oracle关闭情况下如何修改spfile的参数

    发现问题 我使用的Oracle11g，当我敲下如下一段命令后，就让我傻眼了。。 alter system set sga_max_size=960M scope=spfile; shutdown immediate startup 此时的startup报错了，错误为： ...ORA-00844: Parameter ...

    第三方API开发说明电子账户版.rar_新中新一卡通_第三方API开发说明电子账户版

    （3）当程序调用模糊查询操作时，如果查询成功，查询成功的文件放在RecvTemp目录下，如果是下载控制文件，下载成功的文件放到ControlFile目录下，控制文件的名称是：Control.bin;如果下载相片文件，下载成功的文件放...

Magicbox
Global site tag (gtag.js) - Google Analytics