oracle-Flash Back Technology 2

Flash back version query, with additional logs

Flashback transaction Query

http://blog.csdn.net/laoshangxyc/article/details/12405459 the backup and recovery of this blog can be consulted

###################################

http://blog.itpub.net/18922393/viewspace-697625/

Additional logs (supplemental log) can instruct the database to add additional information to the log stream in the log.
To support log-based tools such as logical standby, streams, GoldenGate, Logminer.
Can be set on the database and the table.

#################################

1. Database-level settings, divided into two categories:
1.1 Minimum additional log (minimal supplemental logging):
The data option enables minimal additional logging. Enable minimal logging to ensure Logminer
(or any other Logminer-based product) can support row links, cluster tables, indexed organization tables, and so on.
The syntax is as follows:
ALTER DATABASE {ADD|DROP} supplemental log data;

1.2 Identity key log (identification key logging):
The data (all,primary key,unique,foreign key) columns option enables the minimum log and column log.
In the case where the source library log synchronizes other databases for the source of change, such as a logical standby database,
The affected data rows must be identified with column data (instead of ROWID), and this additional log must be enabled.
The syntax is as follows:
ALTER DATABASE {Add|drop} supplemental log {data (all,primary key,unique,foreign key) columns};

1.3 By default, Oracle does not enable any of these additional logs. The minimum full-time log is turned on by default when attaching logs with all,primary,unique or foreign (that is, the check result is implicit).
After removing any additional logs that cause implicit to minimize additional logs, the minimized additional log becomes No.

###############################

2, table-level additional log settings, divided into two categories
2.1, you can set up named log groups by using the following statement:

Grammar:
ALTER TABLE TABLE_NAME
Add Supplemental Log Group group_a (column_a [No Log],column_b,...) [Always];

The NO log option specifies which columns are excluded from the log. In a named log group, there is at least one fixed-length column with no "no log".
For example, if you use the No Log option for long, you can record the contents of other columns when you change the long column (the long column itself cannot exist in the log).

Always option, when updated, all columns in the log group are recorded in the log. This is called the "unconditional" log group, sometimes called "Always log Group".
If you do not specify this option, all columns appear in the log only if any of the columns in the log group are modified.
This is called a "conditional" log group.

Description: The same column can exist in multiple log groups, but only once in the log;
When the same column exists in the unconditional and conditional log groups, the column is "unconditionally" logged.

2.2, you can set all columns or primary key/foreign key/Unique key combination log groups with the following statement:

Grammar:
ALTER TABLE TABLE_NAME
Add Supplemental log data (All,primary key,unique,foreign key) columns;

Oracle will generate an unconditional or conditional log group. For an unconditional log group, all columns in the log group will be recorded in the log;
For a conditional log group, all columns in the log group are recorded only if the columns in the log group change.

If you specify the "All" column, the log will contain all columns of the maximum size fixed length. This log is an unconditional log group created by the system.
If you specify the "PRIMARY key" column, all columns that make up the primary key are recorded in the log whenever there is an update. This log is an unconditional log group created by the system. Oracle uses the following order to determine which columns are attached to the record:
* Columns that make up the primary key (primary key valid, or rely and non-disabled or initially deferred state)
* A unique index with a minimum of at least one non-empty column
* Record all scalar columns
If you specify a unique column, if any columns that make up a unique key or bitmap index are modified, the other columns that make up the unique key or bitmap index are recorded in the log. This log is a system-created conditional log group.
If you specify the "FOREIGN key" column, if any of the columns that make up the foreign key are modified, the other columns that make up the foreign key are recorded in the log. This log is a system-created conditional log group.


2.3 Table Level Test
drop table test;
CREATE TABLE Test (x int,y int);

--Add additional logs
ALTER TABLE test
Add Supplemental log data (All,primary key,unique,foreign key) columns;

SELECT * from Dba_log_groups;
OWNER log_group_name table_name Log_group_type always GENERATED
SYS sys_c0037555 TEST All COLUMN LOGGING always GENERATED NAME
SYS sys_c0037556 TEST PRIMARY KEY LOGGING always GENERATED NAME
SYS sys_c0037557 TEST UNIQUE KEY LOGGING CONDITIONAL GENERATED NAME
SYS sys_c0037558 TEST FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME

SELECT * from Dba_log_group_columns;
No row selected

--Delete additional logs
ALTER TABLE test
Drop Supplemental log data (All,primary key,unique,foreign key) columns;

SELECT * from Dba_log_groups;
No row selected

SELECT * from Dba_log_group_columns;
No row selected

--Add Name additional log
ALTER TABLE test
Add Supplemental Log Group group_a (x, y);

SELECT * from Dba_log_groups;
OWNER log_group_name table_name Log_group_type always GENERATED
SYS group_a TEST user LOG GROUP CONDITIONAL user NAME

SELECT * from Dba_log_group_columns;
OWNER log_group_name table_name column_name POSITION Logging_property
SYS group_a TEST X 1 LOG
SYS group_a TEST Y 2 LOG

--Delete named append log
ALTER TABLE test
Drop Supplemental log group group_a;

###############################

Statement Summary

ALTER DATABASE add supplemental log data;
ALTER DATABASE Add supplemental log data (all) columns;
ALTER DATABASE Add supplemental log data (primary key) columns;
ALTER DATABASE Add supplemental log data (foreign key) columns;
ALTER DATABASE Add supplemental log data (unique) columns;
ALTER DATABASE Add supplemental log data for procedural replication;

When discarded, there is a sequence in which the log data is discarded and the other is not in order.
ALTER DATABASE drop supplemental log data (all) columns;
ALTER DATABASE drop supplemental log data (primary key) columns;
ALTER DATABASE drop supplemental log data (foreign key) columns;
ALTER DATABASE drop supplemental log data (unique) columns;
ALTER DATABASE drop supplemental log data for procedural replication;
ALTER DATABASE drop supplemental log data;

Whether the query switch is open
Select Supplemental_log_data_min Min1,supplemental_log_data_all all1,
SUPPLEMENTAL_LOG_DATA_PK PK,SUPPLEMENTAL_LOG_DATA_FK Fk,
SUPPLEMENTAL_LOG_DATA_UI UI,SUPPLEMENTAL_LOG_DATA_PL Pl
From V$database;

###################################

Log output

Tail-f/u01/app/oracle/diag/rdbms/kyc/kyc/trace/alert_kk.log

Fri APR 28 18:05:54 2017
ALTER DATABASE add supplemental log data
Suplog:previous Supplemental logging attributes at SCN = 996039
Suplog:minimal = off, primary key = Off
Suplog:unique = off, foreign key = off, all column = Off
Suplog:procedural replication = OFF
Suplog:new Supplemental logging attributes at SCN = 996039
Suplog:minimal = ON, primary key = OFF
Suplog:unique = off, foreign key = off, all column = Off
Suplog:procedural replication = OFF
Completed:alter database Add supplemental log data
ALTER DATABASE Add supplemental log data (primary key) columns
Suplog:previous Supplemental logging attributes at SCN = 996049
Suplog:minimal = ON, primary key = OFF
Suplog:unique = off, foreign key = off, all column = Off
Suplog:procedural replication = OFF
Suplog:new Supplemental logging attributes at SCN = 996049
Suplog:minimal = ON, primary key = On
Suplog:unique = off, foreign key = off, all column = Off
Suplog:procedural replication = OFF
Completed:alter database Add supplemental log data (primary key) columns

######################################

Fri APR 28 15:13:50 2017
ALTER DATABASE MOUNT
Successful mount of Redo thread 1, with Mount ID 119196142
Database mounted in Exclusive Mode
Lost Write protection disabled
Completed:alter DATABASE MOUNT
Fri APR 28 15:14:09 2017
ALTER DATABASE Archivelog
Completed:alter Database Archivelog
Fri APR 28 15:16:59 2017
ALTER DATABASE Flashback on
Starting background Process RVWR
Fri APR 28 15:16:59 2017
RVWR started with pid=22, OS id=3403
Allocated 8388608 bytes in shared pool for flashback generation buffer
Flashback Database Enabled at SCN 991029
Completed:alter Database Flashback on

#################################

http://blog.csdn.net/laoshangxyc/article/details/12405459

Flashback version Query Flashback
Returns all versions within a specified time interval or SCN interval using Flashback version queries, and a COMMIT command creates a version. The
syntax is as follows:
SELECT ..... From TableName VERSIONS {between {SCN | TIMESTAMP} start and end}
--start,end can be either time or SCN

-As you can see, a commit is a version, the current version of the VERSIONS_ENDSCN and Versions_endtime values are empty, and the old version has values.
Flashback Transaction Query Flashback transaction Query
Flashback Transaction query is actually the data dictionary flashback_transaction_query of the query. You can return a previous version of the data based on the Undo_sql column value of Flashback_transaction_query.


-Before you use the Flashback transaction query, you must enable additional logging for the redo log stream, which is the same as the data used by log miner, except that the interface is different.

Select Supplemental_log_data_min min1,supplemental_log_data_all all1,
supplemental_log_data_pk Pk, SUPPLEMENTAL_LOG_DATA_FK Fk,
supplemental_log_data_ui ui,supplemental_log_data_pl Pl
from V$database;

--OPEN log
ALTER DATABASE add supplemental log data;
ALTER DATABASE Add supplemental log data (primary key) columns;

--Change the value
Select * from E1;
Update E1 set Name= ' UUU ' where name= ' ni ';
Commit;
Update E1 set name= ' ooo ' where name= ' UUU ';
Commit;

--Query the transaction ID, below all the version query pseudo-columns

Flashback Version Query pseudo-column description
SCN or timestamp starting with version Versions_start{scn|time}
Versions_end{scn|time} version end SCN or timestamp, if there is a value indicating that the old version was changed after this line, or null, the row version is the current version or the row is deleted (that is, the versions_operation value is D).
VERSIONS_XID transaction ID for creating row versions
Versions_operation actions performed on a row (i= insert, d= Delete, u= update)

Select Versions_startscn,versions_starttime,
Versions_endscn,versions_endtime,
Versions_xid,versions_operation
From E1 versions between timestamp
To_timestamp (' 2017-04-28 18:09:58 ', ' yyyy-mm-dd HH24:MI:SS ')
and
To_timestamp (' 2017-04-28 18:14:58 ', ' yyyy-mm-dd HH24:MI:SS ');

--Fuzzy query
SELECT * FROM Flashback_transaction_query
where logon_user!= ' SYS ' and table_name not like '%$ ';
--Query the undo SQL
Select Start_scn,commit_scn,logon_user,operation,table_name,undo_sql
From Flashback_transaction_query
where Xid=hextoraw (' 09000f0098030000 ');
--Back to previous version

################################

oracle-Flash Back Technology 2