ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 （恢复-关于控制文件(2)）

oracle实验记录 （恢复-关于控制文件(2)）

原创 Linux操作系统 作者：fufuh2o 时间：2009-08-24 15:41:59 0 删除 编辑

接1 实验create controlfile

 

SQL> create tablespace test datafile 'd:\test.dbf' reuse;

表空间已创建。


SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
TEST                           D:\TEST.DBF
EXAMPLE                        E:\ORACLE\PRODUCT\10.2.0\ORADA
                               TA\ORCL\EXAMPLE01.DBF

USERS                          E:\ORACLE\PRODUCT\10.2.0\ORADA
                               TA\ORCL\USERS01.DBF

SYSAUX                         E:\ORACLE\PRODUCT\10.2.0\ORADA
                               TA\ORCL\SYSAUX01.DBF

UNDOTBS1                       E:\ORACLE\PRODUCT\10.2.0\ORADA

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
                               TA\ORCL\UNDOTBS01.DBF

SYSTEM                         E:\ORACLE\PRODUCT\10.2.0\ORADA
                               TA\ORCL\SYSTEM01.DBF


已选择6行。


SQL> alter database backup controlfile to trace;

数据库已更改。
SQL> col name format a40
SQL> select thread#,sequence#,name,first_change# from v$archived_log;

   THREAD#  SEQUENCE# NAME                                     FIRST_CHANGE#
---------- ---------- ---------------------------------------- -------------
         1          1 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        581428
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_
                      1_5946V0DN_.ARC

         1          1 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        534907
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_
                      1_5942NJ1D_.ARC

         1          2 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        567754
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_
                      2_5942NCVR_.ARC

   THREAD#  SEQUENCE# NAME                                     FIRST_CHANGE#
---------- ---------- ---------------------------------------- -------------

         1          3 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        573647
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_
                      3_5942NGV9_.ARC

         1          2 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        583431
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_
                      2_5949GZST_.ARC

         1          1 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        581428
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_

   THREAD#  SEQUENCE# NAME                                     FIRST_CHANGE#
---------- ---------- ---------------------------------------- -------------
                      1_5949H0JK_.ARC

         1          1 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_        586170
                      AREA\ORCL\ARCHIVELOG\2009_08_24\O1_MF_1_
                      1_594BYJL0_.ARC


已选择7行。
SQL> select thread#,group#,sequence#,group#,status from v$log;

   THREAD#     GROUP#  SEQUENCE#     GROUP# STATUS
---------- ---------- ---------- ---------- ----------------
         1          1          1          1 CURRENT
         1          2          0          2 UNUSED
         1          3          0          3 UNUSED
shudown immediate

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             209718148 bytes
Database Buffers          394264576 bytes
Redo Buffers                7135232 bytes
SQL>

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
 14    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
 15    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
 16    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
 17    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF',
 18    'D:\TEST.DBF'
 19  CHARACTER SET ZHS16GBK;

控制文件已创建。


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

   THREAD#     GROUP#  SEQUENCE#     GROUP# STATUS
---------- ---------- ---------- ---------- --------------------------------
         1          1          1          1 CURRENT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~信息来自online redo log
         1          3          0          3 UNUSED
         1          2          0          2 UNUSED

 

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            590900
            590900
            590900
            590900
            590900
            590900

已选择6行。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            590900
            590900~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~controlfile scn,datafile scn一样可以直接open
            590900
            590900
            590900
            590900

已选择6行。

SQL>
SQL> alter database open;

数据库已更改。

~~~~~~~~~~~~~~~
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             213912452 bytes
Database Buffers          390070272 bytes
Redo Buffers                7135232 bytes

SQL> alter session set events '10046 trace name context forever ,level 12' ;

会话已更改。

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG~~~~~~~~~~~~~~指定resetlogs
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
 14    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
 15    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
 16    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
 17    'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF',
 18    'D:\TEST.DBF'
 19  CHARACTER SET ZHS16GBK
 20  ;

控制文件已创建。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            591328
            591328
            591328
            591328
            591328
            591328

已选择6行。

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            591328
            591328
            591328
            591328
            591328
            591328

已选择6行。

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

   THREAD#     GROUP#  SEQUENCE#     GROUP# STATUS
---------- ---------- ---------- ---------- ----------------
         1          1          0          1 UNUSED~~~~~~~~~~~~~~~~~~~~~~~~~~~~SEQUENCE 都为0 需要resetlogs OPEN 这样实际的 redolog sequence才为0
         1          3          0          3 CURRENT
         1          2          0          2 UNUSED
SQL> select thread#,sequence#,name,first_change# from v$archived_log;

未选定行

 

SQL> alter system dump logfile 'E:\oracle\product\10.2.0\oradata\orcl\redo01.log';

系统已更改。

DUMP OF REDO FROM FILE 'E:\oracle\product\10.2.0\oradata\orcl\redo01.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
 Compatibility Vsn = 169869568=0xa200100
 Db ID=1223510333=0x48ed493d, Db Name='ORCL'
 Activation ID=1223549532=0x48ede25c
 Control Seq=626=0x272, File size=102400=0x19000~~~~~~~~~~~~~~~~~~~Control Seq=626=0x272(Control file sequence number)
 File Number=1, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000001, SCN 0x00000008faf3-0x000000090435"       SEQ为1

 

 

 


SQL> alter session set events 'immediate trace name controlf level 8';

会话已更改。

Control Seq=646=0x286,~~~~~~~~~~~~~~~~~~~~~~~
***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  (name #3) E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1~~~~~~~~~~~~~~~~~~~~~~SEQ为0
 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
LOG FILE #2:
  (name #2) E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
 Thread 1 redo log links: forward: 3 backward: 1
 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
LOG FILE #3:
  (name #1) E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb 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
 

通过dump可以发现 redo 与control中记录的  seq: 不一样

SQL> alter database open resetlogs;

数据库已更改。
SQL> select thread#,group#,sequence#,group#,status from v$log;

   THREAD#     GROUP#  SEQUENCE#     GROUP# STATUS
---------- ---------- ---------- ---------- ----------------
         1          1          0          1 UNUSED
         1          2          1          2 CURRENT
         1          3          0          3 UNUSED

SQL> alter system dump logfile 'E:\oracle\product\10.2.0\oradata\orcl\redo01.log';~~~~~~~~~~~~~~~还处于unused状态
alter system dump logfile 'E:\oracle\product\10.2.0\oradata\orcl\redo01.log'
*
第 1 行出现错误:
ORA-00339: 归档日志未包含任何重做
ORA-00334: 归档日志: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

SQL> alter system dump logfile 'E:\oracle\product\10.2.0\oradata\orcl\redo02.log';

系统已更改。
SQL> alter session set events 'immediate trace name controlf level 8';

会话已更改。


DUMP OF REDO FROM FILE 'E:\oracle\product\10.2.0\oradata\orcl\redo02.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
 Compatibility Vsn = 169869568=0xa200100
 Db ID=1223510333=0x48ed493d, Db Name='ORCL'
 Activation ID=1223572562=0x48ee3c52
 Control Seq=658=0x292, File size=102400=0x19000~~~~~~~~~~~~~~Control Seq=658=0x292(Control file sequence number)
 File Number=2, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000001, SCN 0x0000000905e1-0xffffffffffff"
 thread: 1 nab: 0xffffffff seq: 0x00000001 hws: 0x3 eot: 1 dis: 0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~seq 1
 resetlogs count: 0x297844e7 scn: 0x0000.000905e1 (591329)
 resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

 

 

trace  dump  controflile
Compatibility Vsn = 169869568=0xa200100
 Db ID=1223510333=0x48ed493d, Db Name='ORCL'
 Activation ID=0=0x0
 Control Seq=673=0x2a1, File size=450=0x1c2
 File Number=0, Blksiz=16384, File Type=1 CONTROL
                                                                ~~~~~~~~~~~~~~~~~Control Seq=658=0x292(Control file sequence number)

LOG FILE #2:
   (name #2) E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000001 hws: 0x3 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1~~~~~~~~~~~~~~~~~~seq 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.000905e1 08/24/2009 15:16:55
 Next scn: 0xffff.ffffffff 08/24/2009 15:16:55
LOG FILE #3:


SQL> alter system switch logfile
  2  ;

系统已更改。

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

   THREAD#     GROUP#  SEQUENCE#     GROUP# STATUS
---------- ---------- ---------- ---------- ----------------
         1          1          2          1 CURRENT
         1          2          1          2 ACTIVE
         1          3          0          3 UNUSED

SQL> alter system checkpoint;

系统已更改。

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

   THREAD#     GROUP#  SEQUENCE#     GROUP# STATUS
---------- ---------- ---------- ---------- ----------------
         1          1          2          1 CURRENT
         1          2          1          2 INACTIVE
         1          3          0          3 UNUSED

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

0
0
上一篇： oracle实验记录 （恢复-关于控制文件(1)）
下一篇： oracle实验记录（恢复-checkpoint cnt）
请登录后发表评论 登录
全部评论
fufuh2o

注册时间：2009-06-26

  • 博文量
    182
  • 访问量
    471652

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593