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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24890594/viewspace-694044/