ITPub博客

首页 > Linux操作系统 > Linux操作系统 > flashback database 闪回数据库(使用logmnr 查找合适闪回scn点)

flashback database 闪回数据库(使用logmnr 查找合适闪回scn点)

原创 Linux操作系统 作者:xpj0515 时间:2011-04-28 21:16:47 0 删除 编辑

SYS@standby1/2011-04-27 17:49:46>show parameter flashback

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_flashback_retention_target        integer    1440
SYS@standby1/2011-04-27 21:26:59>alter database flashback on;

Database altered.

Elapsed: 00:00:01.59
SYS@standby1/2011-04-27 21:27:16>show parameter db_recovery

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
db_recovery_file_dest                string     /boot/u03/recovery_area
db_recovery_file_dest_size           big intege 8G
                                     r
SYS@standby1/2011-04-27 21:27:28>


SYS@standby1/2011-04-27 17:21:05>create table table_tab as select rownum id,dbms_random.string('U',3) as t1 from dual connect by level<=10;

Table created.

Elapsed: 00:00:00.26

SYS@standby1/2011-04-27 17:21:29>col id for 9999
SYS@standby1/2011-04-27 17:21:41>col t1 for a5
SYS@standby1/2011-04-27 17:21:47>select * from table_tab;


SYS@standby1/27-APR-11>select current_scn from v$database;

CURRENT_SCN
-----------
    1554512

1 row selected.

Elapsed: 00:00:00.00
SYS@standby1/27-APR-11>@log_file.sql

FILE                                                         TYPE       GROUP  SEQ   SIZE MEM ARC STATUS
------------------------------------------------------------ ---------- ----- ---- ------ --- --- ----------
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh23t_.log     ONLINE         1    7    512   3 YES INACTIVE
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh2c1_.log     ONLINE         1    7    512   3 YES INACTIVE
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh3nm_.log     ONLINE         1    7    512   3 YES INACTIVE
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh5wx_.log     ONLINE         2    8    512   3 YES INACTIVE
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh6b7_.log     ONLINE         2    8    512   3 YES INACTIVE
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh8mn_.log     ONLINE         2    8    512   3 YES INACTIVE
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_3_6sxsc7m7_.log     ONLINE         3    9    512   3 NO  CURRENT
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_3_6sxsc80d_.log     ONLINE         3    9    512   3 NO  CURRENT
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_3_6sxscb5g_.log     ONLINE         3    9    512   3 NO  CURRENT

9 rows selected.

Elapsed: 00:00:00.02
SYS@standby1/27-APR-11>select * from table_tab;

   ID T1
----- -----
    1 OVQ
    2 YHZ
    3 TDA
    4 ZKQ
    5 QXQ
    6 PAQ
    7 AOS
    8 SFV
    9 KHN
   10 MZQ

10 rows selected.

Elapsed: 00:00:00.00
SYS@standby1/27-APR-11>select current_scn from v$database;

CURRENT_SCN
-----------
    1554545

1 row selected.

Elapsed: 00:00:00.00
SYS@standby1/27-APR-11>alter system switch logfile;

System altered.

Elapsed: 00:00:00.01
SYS@standby1/27-APR-11>select current_scn from v$database;

CURRENT_SCN
-----------
    1554553

1 row selected.

Elapsed: 00:00:00.01
SYS@standby1/27-APR-11>delete table_tab where id>7;

3 rows deleted.

Elapsed: 00:00:00.00
SYS@standby1/27-APR-11>delete table_tab where id<4;

3 rows deleted.

Elapsed: 00:00:00.00

SYS@standby1/27-APR-11>commit;

Commit complete.

Elapsed: 00:00:00.00
SYS@standby1/27-APR-11>select * from table_tab;

   ID T1
----- -----
    4 ZKQ
    5 QXQ
    6 PAQ
    7 AOS

4 rows selected.

Elapsed: 00:00:00.00

SYS@standby1/27-APR-11>@archived_log.sql


   8 /boot/u02/oradata/ORCL_2/archivelog/1_8_    1536716    1537001     .24    1 NO    ARCH NO  A   NO
     449973005.dbf

   8 /boot/u03/oradata/ORCL_2/archivelog/1_8_    1536716    1537001     .24    1 NO    ARCH NO  A   NO
     449973005.dbf

   9 /boot/u02/oradata/ORCL_2/archivelog/1_9_    1537001    1554552    3.33    1 NO    ARCH NO  A   NO
     449973005.dbf

   9 /boot/u03/oradata/ORCL_2/archivelog/1_9_    1537001    1554552    3.33    1 NO    ARCH NO  A   NO
     449973005.dbf

  10 /boot/u02/oradata/ORCL_2/archivelog/1_10    1554552    1554712     .14    1 NO    ARCH NO  A   NO
     _449973005.dbf

  10 /boot/u03/oradata/ORCL_2/archivelog/1_10    1554552    1554712     .14    1 NO    ARCH NO  A   NO
     _449973005.dbf

SYS@standby1/27-APR-11>@log_file.sql

FILE                                                         TYPE       GROUP  SEQ   SIZE MEM ARC ST
------------------------------------------------------------ ---------- ----- ---- ------ --- --- ----------
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh23t_.log     ONLINE         1   10    512   3 YES ACTIVE
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh2c1_.log     ONLINE         1   10    512   3 YES ACTIVE
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_1_6sxqh3nm_.log     ONLINE         1   10    512   3 YES ACTIVE
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh5wx_.log     ONLINE         2   11    512   3 NO  CURRENT
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh6b7_.log     ONLINE         2   11    512   3 NO  CURRENT
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_2_6sxqh8mn_.log     ONLINE         2   11    512   3 NO  CURRENT
/boot/u02/oradata/ORCL_2/onlinelog/o1_mf_3_6sxsc7m7_.log     ONLINE         3    9    512   3 YES ACTIVE
/boot/u03/oradata/ORCL_2/onlinelog/o1_mf_3_6sxsc80d_.log     ONLINE         3    9    512   3 YES ACTIVE
/boot/u04/oradata/ORCL_2/onlinelog/o1_mf_3_6sxscb5g_.log     ONLINE         3    9    512   3 YES ACTIVE

9 rows selected.

Elapsed: 00:00:00.01


SYS@standby1/27-APR-11>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@standby1/27-APR-11>startup mount
ORACLE instance started.

Total System Global Area  376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             301992628 bytes
Database Buffers           67108864 bytes
Redo Buffers                6197248 bytes
Database mounted.
SYS@standby1/27-APR-11>flashback database to scn 1554712;

Flashback complete.

Elapsed: 00:00:01.13
SYS@standby1/27-APR-11>alter database open read only;

Database altered.

Elapsed: 00:00:00.74
SYS@standby1/27-APR-11>select * from table_tab;

   ID T1
----- -----
    4 ZKQ
    5 QXQ
    6 PAQ
    7 AOS

4 rows selected.

Elapsed: 00:00:00.00

SYS@standby1/27-APR-11>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@standby1/27-APR-11>startup mount;
ORACLE instance started.

Total System Global Area  376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             301992628 bytes
Database Buffers           67108864 bytes
Redo Buffers                6197248 bytes
Database mounted.
SYS@standby1/27-APR-11>recover database;
Media recovery complete.
SYS@standby1/27-APR-11>alter database open;

Database altered.

Elapsed: 00:00:02.10



SYS@standby1/27-APR-11>show parameter utl_file

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
utl_file_dir                         string
SYS@standby1/27-APR-11>alter system set utl_file_dir='/boot/u01/app/oracle/utlfiledir' scope=spfile;

System altered.

Elapsed: 00:00:00.01
SYS@standby1/27-APR-11>startup force;
ORACLE instance started.

Total System Global Area  376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             301992628 bytes
Database Buffers           67108864 bytes
Redo Buffers                6197248 bytes
Database mounted.
Database opened.
SYS@standby1/27-APR-11>

SYS@standby1/27-APR-11>show parameter utl_file

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
utl_file_dir                         string     /boot/u01/app/oracle/utlfiledi
                                                r
SYS@standby1/27-APR-11>exec dbms_logmnr_d.build('logmnr1.ora','/boot/u01/app/oracle/utlfiledir');

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.12
SYS@standby1/27-APR-11>exec dbms_logmnr.add_logfile('/boot/u02/oradata/ORCL_2/archivelog/1_10_449973005.dbf',dbms_logmnr.new);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SYS@standby1/27-APR-11>exec dbms_logmnr.start_logmnr(dictfilename=>'/boot/u01/app/oracle/utlfiledir/logmnr1.ora',startscn=>1554552,endscn=>1554712);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.15


set linesize 220
col log_id for 9999
col filename for a40
col low_time for a20
col high_time for a20
col low_scn for 9999999999
col next_scn for 9999999999
col  blocksize for 9999
col filesize/1024 for 99999
select log_id,filename,low_time,high_time,low_scn,
next_scn,blocksize,filesize/1024 from v$logmnr_logs
  3  /

LOG_ID FILENAME                                 LOW_TIME             HIGH_TIME                LOW_SCN    NEXT_SCN  SIZE FILESIZE/1024
------ ---------------------------------------- -------------------- -------------------- ----------- ----------- ----- -------------
    10 /boot/u02/oradata/ORCL_2/archivelog/1_10 27-APR-11            27-APR-11                1554552     1554712   512          144
       _449973005.dbf


1 row selected.

Elapsed: 00:00:00.01

set linesize 240
col username for a8
col seg_name for a10
col sql_undo for a30
col sql_redo for a30
col session# for 99999
col sequence# for 99999
col scn for 999999999
col commit_scn for 999999999
col timestamp for a20
select username,seg_name,sql_redo,sql_undo,
session#,scn,commit_scn
 from v$logmnr_contents
where table_name=upper('&tablename')
  5  /
Enter value for tablename: table_tab

USERNAME SEG_NAME   SQL_REDO                       SQL_UNDO                       SESSION#        SCN COMMIT_SCN
-------- ---------- ------------------------------ ------------------------------ -------- ---------- ----------
UNKNOWN  TABLE_TAB  delete from "SYS"."TABLE_TAB"  insert into "SYS"."TABLE_TAB"(      125    1554629
                    where "ID" = '8' and "T1" = 'S "ID","T1") values ('8','SFV');
                    FV' and ROWID = 'AAAOpGAABAAAU
                    ehAAH';

UNKNOWN  TABLE_TAB  delete from "SYS"."TABLE_TAB"  insert into "SYS"."TABLE_TAB"(      125    1554629
                    where "ID" = '9' and "T1" = 'K "ID","T1") values ('9','KHN');
                    HN' and ROWID = 'AAAOpGAABAAAU
                    ehAAI';

UNKNOWN  TABLE_TAB  delete from "SYS"."TABLE_TAB"  insert into "SYS"."TABLE_TAB"(      125    1554629
                    where "ID" = '10' and "T1" = ' "ID","T1") values ('10','MZQ')
                    MZQ' and ROWID = 'AAAOpGAABAAA ;
                    UehAAJ';

UNKNOWN  TABLE_TAB  delete from "SYS"."TABLE_TAB"  insert into "SYS"."TABLE_TAB"(      125    1554631
                    where "ID" = '1' and "T1" = 'O "ID","T1") values ('1','OVQ');
                    VQ' and ROWID = 'AAAOpGAABAAAU
                    ehAAA';

UNKNOWN  TABLE_TAB  delete from "SYS"."TABLE_TAB"  insert into "SYS"."TABLE_TAB"(      125    1554631
                    where "ID" = '2' and "T1" = 'Y "ID","T1") values ('2','YHZ');
                    HZ' and ROWID = 'AAAOpGAABAAAU
                    ehAAB';

UNKNOWN  TABLE_TAB  delete from "SYS"."TABLE_TAB"  insert into "SYS"."TABLE_TAB"(      125    1554631
                    where "ID" = '3' and "T1" = 'T "ID","T1") values ('3','TDA');
                    DA' and ROWID = 'AAAOpGAABAAAU
                    ehAAC';


6 rows selected.

Elapsed: 00:00:00.09


SYS@standby1/27-APR-11>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@standby1/27-APR-11>startup mount
ORACLE instance started.

Total System Global Area  376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             310381236 bytes
Database Buffers           58720256 bytes
Redo Buffers                6197248 bytes
Database mounted.





SYS@standby1/27-APR-11>flashback database to scn 1554628;

Flashback complete.

Elapsed: 00:00:03.99
SYS@standby1/27-APR-11>alter database open read only;

Database altered.

Elapsed: 00:00:01.58
SYS@standby1/27-APR-11>select * from table_tab;

   ID T1
----- -----
    1 OVQ
    2 YHZ
    3 TDA
    4 ZKQ
    5 QXQ
    6 PAQ
    7 AOS
    8 SFV
    9 KHN
   10 MZQ

10 rows selected.

Elapsed: 00:00:00.01

SYS@standby1/27-APR-11>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@standby1/27-APR-11>startup
ORACLE instance started.

Total System Global Area  376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             310381236 bytes
Database Buffers           58720256 bytes
Redo Buffers                6197248 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SYS@standby1/27-APR-11>alter database open resetlogs;

Database altered.

Elapsed: 00:00:23.06
SYS@standby1/27-APR-11>select * from table_tab;

   ID T1
----- -----
    1 OVQ
    2 YHZ
    3 TDA
    4 ZKQ
    5 QXQ
    6 PAQ
    7 AOS
    8 SFV
    9 KHN
   10 MZQ

10 rows selected.

Elapsed: 00:00:00.00
SYS@standby1/27-APR-11>select current_scn from v$database;

CURRENT_SCN
-----------
    1645607

1 row selected.

Elapsed: 00:00:00.00
















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

请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    55236