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

Flashback of Oracle

1 functions of flashback:

1) use undo data to backtrack or undo the submitted data

2) flashback log enables database to recover to a certain point in the past and can be used as a supplement to endless recovery.

2 flashback classification

1) flashback drop

2) flashback query (new flashback database archive)

3) flashback table

4) flashback version query

5) flashback transaction

6) flashback database

2.1Flashback drop also known as flashback deletion

1) understand the Recycle Bin (recyclebin)

From an administrative point of view, each user is assigned a recycle bin, but the recycle bin does not actually open up space (just a logical container), and when drop table (non-purge), the data in the segment used by the original table is not really deleted. It actually renames the table segment name as the Recycle Bin. When the table space of the segment is insufficient and needs to be expanded, the information in the Recycle Bin will be automatically cleared (test site).

Give an example to understand.

SQL > show parameter recyclebin

/ / when initializing the parameter recyclebin to on, each user has his own Recycle Bin

/ / if the parameter is set to off to cancel the user's Recycle Bin, then drop table is equivalent to purge.

SQL > create tablespace test1 datafile'/ u01qqApplicate oracleUniplicateoradataUniqhyykUnique test01.dbf' size 1m

SQL > create table scott.t1 (id int) tablespace test1

SQL > insert into scott.t1 values (1)

SQL > insert into scott.t1 values (1)

SQL > commit

SQL > select segment_name from dba_segments where tablespace_name='TEST1'

/ / see there is a segment under the test tablespace

SQL > select sum (bytes) from dba_free_space where tablespace_name='TEST1'

/ / see how much free space there is in this section.

Continue to insert data to fill up the tablespace

SQL > insert into scott.t1 values (1)

SQL > insert into scott.t1 select * from scott.t1

/

/

Check how many rows there are in the data

SQL > select count (*) from scott.t1

SQL > select sum (bytes) from dba_free_space where tablespace_name='TEST1'

/ / shows that there is no free space

Delete table T1

SQL > drop table scott.t1

Check that the segment name whose tablespace is TEST1 has been modified

SQL > select segment_name from dba_segments where tablespace_name='TEST1'

SQL > select sum (bytes) from dba_free_space where tablespace_name='TEST1'

Look, the free space in the TEST table space is back, which means that if the test table space is not enough, this part of the free space can be reused. In fact, even if you set the table space autoextend feature, Oracle will first use recyclebin, and then consider autoextend if there is not enough space.

SQL > create table scott.emp2 tablespace test1 as select * from scott.emp; / / squeeze test tablespace

SQL > select sum (bytes) from dba_free_space where tablespace_name='TEST1'

SQL > select segment_name from dba_segments where tablespace_name='TEST1'

/ / the data in the T1 table has been washed away and cannot be recovered using flashback deletion (test site).

2) about flashback and cleanup of objects in the Recycle Bin

Flashback and cleanup are in different order

Flashback uses LIFO (last in, first out)

Clear using FIFO (first-in, first-out)

Suppose there are two T1 tables in the Recycle Bin, look at the following two statements:

SQL > flashback table T1 to before drop; / / flashes the latest T1 table (test site).

SQL > purge table T1; / / the oldest T1 table (test site) is cleared.

If you want to avoid confusion, you can directly point out the table name in the Recycle Bin

SQL > flashback table "BIN$qrJLbL74ZgvgQKjA8Agb/A==$0" to befroe drop

SQL > purge table "BIN$qrJLbL74ZgvgQKjA8Agb/A==$0"

SQL > purge recyclebin; / / empty the Recycle Bin

3) how to restore the table prepared for flashback under the same schema already has an object with the same name. Flashback drop needs to be renamed.

SQL > drop table emp1

SQL > create table emp1 as select * from emp

SQL > select * from tab

SQL > show recycle

SQL > flashback table emp1 to before drop rename to test_old

/ / renaming is used in the flashback drop statement

4) there is no recycle bin for objects in system tablespaces, so when system tablespaces are used by default under sys, drop table will delete objects directly (test points).

5) if a table has indexes and constraints on it and flashes back to the table after drop, are the indexes and constraints still there?

Create table t (id int,name char (10))

Alter table t add constraint pk_t primary key (id)

Insert into t values (1)

Insert into t values (2)

Commit

SQL > select * from t

-take a look at constraints and indexes

SCOTT@hyyk > col segment_name for A20

SCOTT@hyyk > select segment_name,segment_type from user_segments

SCOTT@hyyk > select constraint_name from user_constraints

SQL > drop table t

-the table is drop to the Recycle Bin, and then take a look at the constraints and indexes.

SQL > select segment_name,segment_type from user_segments

SQL > select constraint_name from user_constraints; / / constraints exist, but garbled (except for foreign key constraints)

SQL > flashback table t to before drop

-take a look at constraints and indexes

SQL > select segment_name,segment_type from user_segments

/ / the index is back, valid (test site), but garbled

SQL > select constraint_name from user_constraints; / / constraints are also there, valid (test site), but garbled

-rename indexes and constraints respectively

SQL > alter index "BIN$YIkba0zjgoDgUAEKCAFgVQ==$0" rename to pk_t

SQL > alter table t rename constraint "BIN$YIkba0zigoDgUAEKCAFgVQ==$0" to pk_t

-take a look at constraints and indexes

2.2 Flashback query flashback query: (for DML misoperation)

1) main points:

Using the undo block that has been submitted in undo tablespace (not overwritten), you can query the records in the table back to a point in time in the past. Set the retention time of the previous mirror by setting the undo_retention parameter.

Syntax of the query:

Select... As of scn | timestamp

2) experiment

Sys users:

Create table scott.student (sno int,sname char (10), sage int)

Insert into scott.student values (1) Tommy 21)

Insert into scott.student values (2 recordings Kitesong 22)

Insert into scott.student values (3 recordings Bobbing 23)

Insert into scott.student values (4) mix mix 24)

Commit

/

View data

SQL > select * from scott.student

Take time 1 and scn1 for later flashback queries

Select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') from dual; / / time 1

TO_CHAR (SYSDATE,'YY

-

2017-12-17 20:28:49

Select current_scn from vault database; / / fetch scn1

CURRENT_SCN

-

1431343

Delete 1 row of data in the table and submit

Delete scott.student where sno=1

Commit

Verify that the deletion was successful

Select * from scott.student

Here we are taking time 2 and scn2

Select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') from dual; / / fetch time 2

TO_CHAR (SYSDATE,'YY

-

2017-12-17 20:30:41

Select current_scn from vault database; / / fetch scn 2

CURRENT_SCN

-

1431479

We are updating the data in the table

Update scott.student set sage=50

Commit

Select * from scott.student

We use scott users for flashback queries:

Flashback queries the data of the student table at time 1. Although the database has been deleted and updated after time 1, the status of time 1 can be read from the undo without overwriting.

Select * from student as of timestamp to_date ('2017-12-17 20-20-30-15-41-12-12-17-20-30-30-30-31-12-17-20-30-15-41-12-12-17-20-30-15-41-12-12-17-20-30-30-15-41-12-12-17-12-17-20-30-30-15-12-17-20-30-30-30-15-12-17-12-17-12-17-12-17-12-17-12-17-12-17-12-17-12-17-12-17-12-17 20-20-30-15-12-17-12-17 20 mm Murray dd)

Flashback to query the status of scn2

Select * from student as of scn 1431343

If some data is lost due to operating things after scn2, you can use the following methods to recover the data

1. Create a student2 table by flashing the queried data

Create table student2 as select * from scott.student as of scn 1431343

two。 Delete the old student

Drop table student

3. Rename the student2 table to student

Rename student2 to student

Select * from student

* Test site: you can query the database at a certain point in time, but you can never DML the database at a point in time.

2.3 flashback table

1) main points

A flashback table usually rolls back the status of the table to a previous time or SCN. In fact, it can flash both forward and backward. Automatically restore related properties, including indexes, triggers, etc.

Only if row movement is enabled for the table.

Why use row movement: flashback table here refers to flashback from undo. Imagine that if the original storage location of this table is occupied by another objects, it cannot return to its original location, so use enable row movement.

Syntax:

Flashback table to timestamp | scn

2) experiment

Delete student

Commit

Alter table student enable row movement

Flashback table student to scn XXXXX

Test venue:

1) the sys table cannot be flashed back.

2) it must be enabled to move.

3) by default, the relevant trigger is turned off during the flashback table.

4) the flashback table has index that is maintained

5) Flashback table operation is not allowed in materialized view.

2.4Flashback data Archiving (FLASHBACK ARCHIVE) 11g what's New (TOTAL RECALL)

1. Concept

Indefinitely store table row data, through the background process FBDA, capture the necessary data and save it in the archive, and then use the regular flashback query command (as of) to query the required data, but flashback can go back many years. The flashback file can be seen as an extension of flashback query time.

two。 There are several main points

First of all, there must be one (or more) tablespaces for archived tables, and quotas can be added, and it is technically feasible to use the same tablespace as the original table, but Oracle recommends that they be better stored separately from regular data.

The first step is to create an archive name (scheme), and as an object of a database, you can specify the default attribute, which means that all tables to be archived are established under the default archive name.

3) you can create multiple flash regression file names (schemes) as needed, depending on how many different retention times you need. You can adjust the retention time, and once the retention period is exceeded, the background process FDBA will automatically delete the table history, or you can delete it manually during the retention period.

4) about two permissions:

Flashback archive administer / / Grant the user to create, modify or delete the flash regression file name (scheme)

Flashback archive / / Grant the user to archive the table.

5) alter table table name flashback archive archive name is required to enable flashback archiving of tables.

6) there is also a prerequisite for the flash return file enable: the table space should be managed by ASSM and undo_management=on, otherwise the table times ORA_55614 error should be modified.

7) View of flashback archiving:

DBA_FLASHBACK_ARCHIVE / / describe the archive of the configuration

DBA_FLASHBACK_ARCHIVE_TS / / lists the tablespaces used

DBA_FLASHBACK_ARCHIVE_TABLES / / View archived tables

3. Experiment

For example, 1:DBA creates a tablespace for flashback archiving, and then creates and authorizes the flashback file as an object.

Sys:

Create tablespace fda datafile'/ u01 size size 5m

Create flashback archive fla1 tablespace fda quota 2m retention 1 year

Grant flashback archive on fla1 to scott

Users define their table as a flash return file

Scott:

Alter table emp1 flashback archive fla1

You can do some DML operations before using flashback query (as of) validation.

If you try to do some DDL operations on 11gR1, you will get an error (improved on 11gR2, PPT-II-291):

Scott:

Alter table emp1 drop column comm

Truncate table emp1

ORA-55610: the DDL statement for the history tracking table is invalid. (modify column delete column, empty record can but not delete)

Drop table emp1; (not even on 11gR2)

The following is a demonstration of canceling archive protection

Sys:

Check what archive names are available.

SQL > select owner_name,flashback_archive_name from dba_flashback_archive

SQL > select * from dba_flashback_archive_ts; / / View archived information on the use of tablespaces

SQL > select * from dba_flashback_archive_tables; / / View the relationship between the table emp1 and the archive name.

Sys:

SQL > alter table scott.emp1 no flashback archive; / / remove table scott.emp1 from the flashback archive

Example 2: through an examination question, let's experiment on what is default filing:

Title:

Note the output of the following query

SQL > SELECT flashback_archive_name, status FROM dba_flashback_archive

FLASHBACK_ARCHIEVE_NAME STATUS

FLA1 (no default needs to be specified)

You executed the following command to enable Flashback Data Archive on the

EXCHANGB_PATE table:

ALTER TABLE exchange_rate FLASHBACK ARCHIEVE; on .

What is the outcome of this command?

A.The table uses the default Flashback Data Archive.

B.The Flashback Data Archive Is created In the SYSAUX tablespace.

C.The Flashback Data Archive is created in the same tablespace where the tables are stored.

D.The command generates an error because no flashback Data Archive name is specified and there is no default Flashback Data Achieve.

Answer: d

SQL > conn / as sysdba.

SQL > alter flashback archive fla1 set default

SQL > select FLASHBACK_ARCHIVE_NAME,STATUS from dba_flashback_archive

SQL > alter table scott.emp1 flashback archive; / / No need to specify an archive name, emp1 is bound to FLA1 (DEFAULT).

SQL > select * from dba_flashback_archive_tables

Delete flash return file (scheme)

SQL > drop flashback archive fla1

Test site: there are two prerequisites for setting up flashback data archiving:

1) enable automatic undo managent

2) tablespace wants ASSM.

2.5 flashback version query (PPT-II-260)

1) main points

The flashback query can only get the data at a certain point in the past, but it can not reflect the details of the data changes in the data table over a period of time. The 10g flashback version query can query each row change (different version) of the data table during the time period.

Grammar: select... From... Versions between

Among them, the pseudo column can be selected after the select to get the start and end time of the transaction, SCN number, ID number, and so on.

2) for example:

Scott:

Create table T3 (id int, name char (10))

Insert into T3 values (1)

Insert into T3 values (2meme mike')

Insert into T3 values (3 recordings brain')

Insert into T3 values (4)

Commit

Update t3 set name='nelson' where id=4

Commit

Delete t3 where id=2

Commit

Update t3 set id=id+100

Commit

Look at the time changes experienced by the T3 table.

SQL > select versions_startscn, versions_endscn, versions_xid, versions_operation,id,name from scott.t3 versions between scn minvalue and maxvalue

SQL > select versions_xid, versions_operation,id, name from T3 versions between scn minvalue and maxvalue

Test venue:

Flashback version queries cannot be used for external tables, temporary tables, or V$ views. The reason is that none of these objects generate undo data. The undo of the temporary table is based on session.

2) the content in the flashback version does not include uncommitted DML statements.

2.6Flashback transaction query (PPT-II-270)

1) main points:

Flashback transaction queries can provide undo queries. From the flashback_transaction_query view, query the transaction that causes the data change, and the SQL statement that undoes the transaction is to query the operation and undo_sql columns. Can be used in conjunction with flashback version queries.

Sys:

SQL > desc flashback_transaction_query

SQL > select undo_sql from flashback_transaction_query where xid=hextoraw ('transaction number')

Follow the example above:

SQL > select undo_sql from flashback_transaction_query where xid=hextoraw ('02000E0024040000')

/ / execute the above statement, and the original operation (update T3 set id=id+100;) is undone

SQL > update "SYS". "T3" set "ID" ='4' where ROWID = 'AAANByAABAAAO/yAAD'

SQL > update "SYS". "T3" set "ID" ='3' where ROWID = 'AAANByAABAAAO/yAAC'

SQL > update "SYS". "T3" set "ID" ='1' where ROWID = 'AAANByAABAAAO/yAAA'

SQL > commit

Grant execute on dbms_flashback to scott

Flashback transaction query test site

Enable Supplemental Logging (supplementary log) and database version 10.0 compatibilitySQL > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

3) SELECT ANY TRANSACTION permission is required to query flashback_transaction_query view.

2.7 flashback to the database (flash log first)

1) concept:

Flashback to the database is equivalent to an incomplete recovery, which rolls back the database as a whole to a certain point in time by flashback logs.

The flashback database is aimed at logic errors. If the database is physically damaged or the media is lost, the flashback database will be powerless. The flashback database cannot completely replace the traditional incomplete recovery (test site).

To use the flashback database, you need to turn on the flashback log, which is stored in the flashback recovery area.

Once the flashback database is enabled, the images of some blocks are copied from db buffer to a new storage area of SGA, the flashback buffer, and then the contents of this flashback recovery area are flushed to disk and flashback logs by the background process (Recover Write RVWR). All this does not change the routine role of LGWR. Unlike the redo log, RVWR is not a log of changes in the database, but a record of a complete block image. (PPT-311)

* Test site: unlike redo logs, flashback logs cannot be multiplexed and archived. They are created and managed automatically.

2) the flashback log is placed in the flashback recovery area

Flashback recovery area (flash recovery area) is a very important concept, which not only stores flashback logs, but also many files related to recovery, for example, it can store three automatically managed files related to RMAN, 1. Archive log, 2. Control files to automatically back up 3.RMAN backup slices. Oracle can also automatically clean up some obsolete (obsolete) files when there is not enough flash recovery area space. (PPT-II-49-55)

SQL > show parameter recovery_file

SQL > show parameter flash

* examination site: 1. Db_recovery_file_dest_size must be set before setting db_recovery_file_dest. two。 The retention period for the flashback log, which defaults to 1440, in minutes. (set the size first, then the path)

3) basic steps to configure flashback database:

3.1) Database flashback should be in mount state

SQL > STARTUP MOUNT EXCLUSIVE

Alter database archivelog; must start archiving first, because flashback depends on media recovery

Alter database flashback on

3.2) configured for archiving

SQL > ALTER DATABASE ARCHIVELOG

The flashback database must be equipped with archiving mode, because only snapshots are recorded in the flashback log, which can cause the database to fall back to a certain SCN point, while the SCN of the fallback snapshot is only a little earlier than the SCN you specified, and then rolls forward a short segment using the archive log or the current log, and stops when the specified san is reached. Then resetlogs opens the database before this SCN.

3.3) specify flashback recovery area

Set the parameter db_recovery_file_dest='u01/flash_recovery_area'

3.4) configure flashback retention time

Set the parameter db_flashback_retention_target=1440

Note that the unit is minutes, and the default 1440 is equivalent to 24 hours.

3.5) enable flashback to the database

SQL > alter database flashback on

/ / as a result, a .flb flashback log file is created in the / u01/app/oracle/flash_recovery_area/HYYK/flashback directory.

SQL > select flashback_on from vault database; / / View flashback database on or off

Just check whether the RVWR process and flashback log are available, if the alter database flashback off; flashback log is automatically cleared (test site).

3.6) Open the database

Alter database open

4) example: restore deleted users.

First, go to mount, configure the relevant parameters, enable the flashback database log, then open the database, and then:

4.1) take the current SCN

SQL > select current_scn from v$database

(method 2, SQL > create restore point abc [gurantee flashback database]

/ / the "gurantee flashback database" option ensures that the flashback log exists all the time since the ABC point (SCN) and is not affected by retention)

CURRENT_SCN

-

1472195

4.2) Delete scott users

SQL > drop user scott cascade

4.3) prepare to flash the database under mount

SQL > shutdown immediate

SQL > startup mount exclusive / / mount to attach exclusive (exclusive), which means that other sysdba cannot open the database, which is a test site.

SQL > flashback database to scn 1472195

(method 2, SQL > flashback database to restore point abc;)

4. 4) Open read-only mode to confirm that scott has been flashed

SQL > alter database open read only

SQL > select * from scott.emp

4.5) after confirmation, reopen the database in resetlogs mode (belongs to incomplete recovery)

Startup force

Alter database open resetlogs

/ / once the resetlogs is turned on, if you want to flash again, you can only flash back the older scn (see PPT-315page).

Reminder: to flash the database under mount

Flashback database to timestamp to_date ('2012-03-02 1915 11 11 mm hh34:mi:ss')

Flashback database to scn 1264788

After flashback, open the database, and for the first time, it is best to use read-only mode to see if it has been restored to the point in time you want, and if not, you can flash again (either forward or back flash is OK, because read-only scn will not grow).

5) which operations are suitable or not suitable for flashback database

Suitable for:

Drop table xxx purge

Drop user xxx cascade

Wrong truncate table when a user action affects the entire database

Not suitable:

Control files, data files, modified data cannot be flashed back to the database

Backup control file or trace file is used

Operation of drop tablespace

The table after segment reorganization and the data file after contraction.

6) flashback the database using SavePoint

SQL > create restore point gold; / / SavePoint gold is quite a tag that records the current SCN

...

SQL > startup mount

SQL > flashback database to restore point gold

7) limit the amount of flashback data generated

By default, if the flashback database is enabled, the flashback data for all tablespaces is logged, and the properties of individual tablespaces can be turned off using the following command, so that flashback data for that tablespace is not generated.

SQL > alter tablespace flashback off; / / can be set under open

SQL > alter tablespace flashback on; / / can be set under mount

SQL > select * from v$tablespace / / there is a field FLA to display the status

The tablespace of flashback off should be offline before flashback to the database (do not participate in flashback), and the database cannot be opened until all data files are scn inconsistent.

Before going online, the data files of offline should be partially restored and incomplete restored.

8) there are two views about the flashback database:

V$flashback_database_log shows the earliest time you can fall back to, depending on the number of Flashback Database Log retained.

V$flashback_database_stat records the amount of database activity per unit of time on a single line (about 1 hour).

SQL > select * from v$flashback_database_log

Test site: OLDEST_FLASHBACK_SCN says it allows you to flash back to the earliest scn point.

SQL > select * from v$flashback_database_stat

V$flashback_database_log information is more helpful to flash database than it is.

* Test points: flashback database requires archived log mode and uses alter database open resetlogs to create a new avatar of database logs.

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.