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

[oracle@db12c ~] $mkdir utl_file_dir

[oracle@db12c ~] $sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Sep 2015: 55:20 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@ORA12C > alter system set utl_file_dir='/home/oracle/utl_file_dir' scope=spfile

System altered.

SYS@ORA12C > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA12C > startup

ORACLE instance started.

Total System Global Area 729808896 bytes

Fixed Size 2928680 bytes

Variable Size 553652184 bytes

Database Buffers 167772160 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SYS@ORA12C > @? / rdbms/admin/dbmslms.sql

Session altered.

Package created.

No errors.

Grant succeeded.

Session altered.

_ user'@'_connect_identifier > @? / rdbms/admin/dbmslm.sql

Session altered.

Package created.

Grant succeeded.

Synonym created.

Session altered.

_ user'@'_connect_identifier > @? / rdbms/admin/dbmslmd.sql

Session altered.

Package created.

Synonym created.

Session altered.

SYS@ORA12C > alter database add supplemental log data;-- Open the supplementary log

Database altered.

SYS@ORA12C > select SUPPLEMENTAL_LOG_DATA_MIN from v$database

SUPPLEME

-

YES

SYS@ORA12C > exec dbms_logmnr_d.build (dictionary_filename= > 'LGM_v1201dict.ora',dictionary_location= >' / home/oracle/utl_file_dir');-- create a data dictionary

PL/SQL procedure successfully completed.

Simulated data recovery: (first, the scott user table T is given to truncate, and then the hr users do a lot of operations, so they need to recover all the data)

COTT@ORA12C > create table t as select * from dba_objects

Table created.

SCOTT@ORA12C > select sysdate from dual

SYSDATE

-

2015-09-20 16:32:58

1 row selected.

SCOTT@ORA12C > conn hr/hr

Connected.

HR@ORA12C > create table hr (id int,name varchar2)

Create table hr (id int,name varchar2)

HR@ORA12C > create table hr (id int,name varchar2)

Table created.

HR@ORA12C > insert into hr values (1GSGE')

1 row created.

HR@ORA12C > insert into hr values (2gsdfdge')

1 row created.

HR@ORA12C > insert into hr values (3gsdfddfdge')

1 row created.

HR@ORA12C > insert into hr values (5gsdfddfdgdfde')

1 row created.

HR@ORA12C > insert into hr values (4 gsdfddfdfdfdgdfde')

1 row created.

HR@ORA12C > commit

Commit complete.

HR@ORA12C > select * from hr

ID NAME

1 1gsge

2 1gsdfdge

3 1gsdfddfdge

5 1gsdfddfdgdfde

4 1gsdfddfdfdfdgdfde

5 rows selected.

HR@ORA12C > conn / as sysdba

Connected.

SYS@ORA12C > conn scott/scott

Connected.

SCOTT@ORA12C > truncate table t

Table truncated.

SCOTT@ORA12C > conn / as sysdba

Connected.

SYS@ORA12C > alter system switch logfile

System altered.

SYS@ORA12C > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

The current log serial number is 5, so what I did before is 4.

Add Log

SYS@ORA12C > exec dbms_logmnr.add_logfile (logfilename= >'/ tmp/o1_mf_1_4_bzww1nfk_.arc',options= > dbms_logmnr.new)

PL/SQL procedure successfully completed.

Start digging:

SYS@ORA12C > exec dbms_logmnr.start_logmnr (dictfilename= >'/ tmp/LGM_v1201dict.ora',options= > dbms_logmnr.ddl_dict_tracking)

PL/SQL procedure successfully completed.

SYS@ORA12C > select distinct username from vault logmnrcontents; valid only for the current session

USERNAME

-

HR

SCOTT

UNKNOWN

SYS

SYS@ORA12C > create table logmnr as select * from vault logmnrcontents;-- create a formal table to facilitate query.

Table created.

Create index logmnr_idx on logmnr (username,table_name,operation);-indexing

SELECT username, sql_redo, TIMESTAMP

FROM logmnr a

WHERE a.timestamp >

To_timestamp ('2015-09-20 1616-32-15-58,' yyyy-mm-dd hh34:mi:ss')

AND a.username NOT IN ('SYS',' SYSMAN')

In this way, the scott user T table can be restored using flashback or rman, and then the HR user data can be restored through sql_redo:

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.