Shulou(Shulou.com)06/01 Report--

-

1. Query the current log group 21:43:00 sys@TESTDB11 > select * from v$log

1 1 36 52428800 512 1 NO CURRENT 1349824

2. Query the log file 21:42:44 sys@TESTDB11 > select * from v$logfile

GROUP# STATUS TYPE MEMBER IS_

3 ONLINE + DATA/testdb11/redo03.log NO

2 ONLINE + DATA/testdb11/redo02.log NO

1 ONLINE + DATA/testdb11/redo01.log NO

3. Query archive log files 21:42:28 sys@TESTDB11 > select name from v$archived_log

/ home/oracle/archivelog_bak/TestDB111_31_846843855.dbf

/ home/oracle/archivelog_bak/TestDB111_32_846843855.dbf

/ home/oracle/archivelog_bak/TestDB111_33_846843855.dbf

/ home/oracle/archivelog_bak/TestDB111_34_846843855.dbf

/ home/oracle/archivelog_bak/TestDB111_35_846843855.dbf

/ home/oracle/archivelog_bak/TestDB111_36_846843855.dbf

DML operation mining scn and time point

Execute NEW's current log group in turn in sqlplus separated by commas, and ADDFILE archives the last file.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'+ DATA/testdb11/redo01.log',-

OPTIONS = > DBMS_LOGMNR.NEW)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ home/oracle/archivelog_bak/TestDB111_36_846843855.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS = >-

DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

EXECUTE DBMS_LOGMNR.END_LOGMNR

Alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Col username for a10

Col sql_redo for a50

Select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='t1' order by scn

1363373 2014-05-20 20:15:41

Two kinds of flashback

Flashback table scott.t1 to scn 1363373

Flashback table scott.t1 to timestmp to_timestmp ('2014-05-20 20-15-15-41-5-5-20-5-20-5-20-15-15-15-41-5-5-20-5-20-5-20-15-15-15-41-5-5-20-5-20-5-20-15-15-15-41-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-15-15-15-15-15-15-15-15-15-15-15-15-15-15-15-15-15-15-51-5-20-20-20-05-20-20-20-20-20-20-20-20-20-20-20-20-20-20-20-20-20-

Example: DML operation flashback table

SQL > create table T1 as select * from scott.dept

Table created.

SQL > select * from T1

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL > delete T1

4 rows deleted.

SQL > insert into T1 select * from scott.dept where deptno=10

1 row created.

SQL > select * from T1

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

SQL > commit

Commit complete.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME

-

1 1 86 52428800 2 YES INACTIVE 862829 2014-07-22 16:00:01

2 1 87 52428800 2 YES INACTIVE 862850 2014-07-22 16:00:03

3 1 88 52428800 2 NO CURRENT 862976 2014-07-22 16:02:18

First

Open database supplementary log

Alter database add supplemental log data

Current log group

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ prod_log/prod/redo13.log',-

OPTIONS = > DBMS_LOGMNR.NEW)

More than one can be written in the last archive, in reverse order

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_87_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_86_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_85_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS = >-

DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

EXECUTE DBMS_LOGMNR.END_LOGMNR

Alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Col username for a10

Col sql_redo for a50

Select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='T1' order by scn

USERNAME SCN TIMESTAMP SQL_REDO

SYS 863211 2014-07-22 16:10:20 insert into "SYS". "T1" ("DEPTNO", "DNAME", "LOC") val

Ues ('40 days, weeks, days, days,

SYS 863228 2014-07-22 16:10:51 delete from "SYS". "T1" where "DEPTNO" = '10' and "

DNAME "= 'ACCOUNTING' and" LOC "=' NEW YORK' and R

OWID = 'AAAM4GAABAAAO2iAAA'

SYS 863228 2014-07-22 16:10:51 delete from "SYS". "T1" where "DEPTNO" = '20' and "

DNAME "= 'RESEARCH' and" LOC "=' DALLAS' and ROWID

= 'AAAM4GAABAAAO2iAAB'

Enable row migration

Alter table t1 enable row movement

Based on scn

Flashback table t1 to scn 863227

Based on point in time

Flashback table T1 to timestamp to_timestamp ('2014-07-22 16 purl 10 purl 50 hundred hundred and twenty-five hh34:mi:ss')

Flashback query

Select * from T1 as of timestamp to_timestamp ('2014-07-22 16-15-16-15-10-15-5-5-5-5-12-12-12-16-12-16-12-14-12-16-14-12-16-14-14-12-16-14-14-12-12-16-15-14-12-12-16-15-14-12-12-12-12-16-15-14-12-16-15-14-12-16-15-14-12-16-15-14-14-12-16-15-14-12-12-12-12-16-15-14-14-15-12-16-15-14-14-12-12-14-15-14-12-12-16-15-14-12-12-12-16-15-14-12-

Sys users cannot use flashback, created with flashback queries

Create table T2 as select * from T1 as of timestamp to_timestamp ('2014-07-22 16 purl 10 purl 50 lemyyyuki Mmmerdd hh34:mi:ss')

Note: possible error message

SQL > select * from T1 as of timestamp to_timestamp ('2014-07-22 16 purl 10 purl 20 hundred and eighty-seven years Mmmerdd hh34:mi:ss')

Select * from T1 as of timestamp to_timestamp ('2014-07-22 16-15-10-12-12-16-12-12-12-16-12-12-12-12-12-16-12-12-12-16-15-12-12-12-16-15-12-16-15-12-16-15-12-16-15-12-12-12-16-15-12-12-12-16-15-12-16-15-12-16-15-12-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-12-12-12-16-15-12-16-15-12-12-15-12-16-15-12-16-15-12-16-12-12-16-12-16-15-

*

ERROR at line 1:

ORA-01466: unable to read data-table definition has changed

The time point is wrong. You should find delete for a few seconds before deletion.

SQL > flashback table T1 to timestamp to_timestamp ('2014-07-22 16-14-10-15-13-16-15-10-12-12-12-12-12-12-12-12-16-12-12-16-15-12-12-12-16-15-12-16-15-12-12-16-15-12-16-15-12-12-12-16-15-12-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-16-15-12-15-12-16-15-12-16-15-12-16-15-12-16-12-16-15-

Flashback table T1 to timestamp to_timestamp ('2014-07-22 16 purse 10 purl 30 charmmayymermerdd hh34:mi:ss')

*

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

DDL operation flashback database * * Note: it is best to do a flashback database operation on the slave database, and then logically import it into the main database

SQL > create table T2 as select * from dept

Table created.

SQL > select * from T2

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL > drop table T2 purge

Table dropped.

Set parameters and store data dictionary

Mkdir / home/oracle/logmnr

SQL > show parameter utl

NAME TYPE VALUE

-

Create_stored_outlines string

Utl_file_dir string

SQL > alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile

System altered.

SQL > startup force

ORACLE instance started.

Total System Global Area 570425344 bytes

Fixed Size 2022480 bytes

Variable Size 209716144 bytes

Database Buffers 352321536 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.

Create a data dictionary file dict.ora

Execute dbms_logmnr_d.build ('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file)

Add log analysis

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_110_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.NEW)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_109_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_108_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (-

LOGFILENAME = >'/ arch/1_107_853529715.dbf',-

OPTIONS = > DBMS_LOGMNR.ADDFILE)

Execute dbms_logmnr.end_logmnr

Executive analysis

Execute dbms_logmnr.start_logmnr (dictfilename= >'/ home/oracle/logmnr/dict.ora',options= > dbms_logmnr.ddl_dict_tracking)

View analysis results

Alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Col username for a10

Col sql_redo for a50

Select username,scn,timestamp,sql_redo from v$logmnr_contents where username='SCOTT' and lower (sql_redo) like'% table%'

SQL > select username,scn,timestamp,sql_redo from v$logmnr_contents where username='SCOTT' and lower (sql_redo) like'% table%'

USERNAME SCN TIMESTAMP SQL_REDO

SCOTT 898096 2014-07-22 17:54:04 drop table T1 purge

SCOTT 898346 2014-07-22 17:55:27 create table T2 as select * from dept

SCOTT 899047 2014-07-22 17:56:24 drop table T2 purge

Flashback database to scn 898096

Flashback database to timestamp to_timestamp ('2014-07-22 17-15-55-15-55-22-17-15-22-17-15-22-17-15-55-15-17-15-22-17-15-22-17-15-55-15-17-15-22-17-15-55-15-17-15-22-17-15-55-17-15-17-15-17-15-17-15-17-15-17-15-17-17-15-17-15-17-17-15-17-15-17-17-15-17-17-15-17-17-15-17-17-15-17-17-15-17-17-17-15-17-17-15-17-17-15-17-17-15-17-17-17-15-17-17-17-

Close the library to mount flashback

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 570425344 bytes

Fixed Size 2022480 bytes

Variable Size 218104752 bytes

Database Buffers 343932928 bytes

Redo Buffers 6365184 bytes

Database mounted.

SQL > flashback database to timestamp to_timestamp ('2014-07-22 17 purl 55 purl 45 mm Murray d hh34:mi:ss')

Flashback complete.

Read-only

SQL > alter database open read only

Database altered.

SQL > select * from scott.t2

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL > shutdown immediate

SQL > startup mount

ORACLE instance started.

Total System Global Area 570425344 bytes

Fixed Size 2022480 bytes

Variable Size 218104752 bytes

Database Buffers 343932928 bytes

Redo Buffers 6365184 bytes

Database mounted.

SQL > alter database open resetlogs

Database altered.

SQL > select * from scott.t2

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.