Shulou(Shulou.com)05/31 Report--

This article introduces how Oracle Flashback opens and operates. The content is very detailed. Interested friends can refer to it for reference. I hope it can help you.

Oracle Flashback Start and Operation

1. Record current time or SCN

Record time or SCN before database changes

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual;

SQL> select current_scn from v$database;

2. Initiate flashback.

Turning on flashback requires that the database be under archive model, so you need to turn on archive first. and in the mount state.

First check if flashback is enabled:

SQL> select flashback_on from V$database;

FLASHBACK_ON

------------------

NO

If flashback is turned on, there is no need to do the following.

SQL> alter system set db_recovery_file_dest_size=30G scope=both;

SQL> alter system set db_recovery_file_dest='/u01/flashback' scope=both;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database flashback on;

SQL> alter database open;

Confirm again:

SQL> select flashback_on from V$database;

FLASHBACK_ON

------------------

YES

3. flashback operation

3.1 flashback database

SQL >flashback database to time to_date(xxx);

SQL >flashback database to time TO_TIMESTAMP (xxx);

SQL >flashback database to scn xxx

SQL >flashback database to sequence xxx thread 1

SQL>flashback database to timestamp(sysdate-1/24)

3.2 flashback tables

Start with row movement.

SQL>alter table emp enable row movement;

SQL >FLASHBACK TABLE tablename TO TIMESTAMP (JUL-07-2013, 02:33:00)

SQL >FLASHBACK TABLE employee TO SCN 133456;

SQL >FLASHBACK TABLE t1 TO TIMESTAMP '2013-03-03 12:05:00' ENABLE TRIGGERS;

Flashback table can be divided into two cases, one is flashback to the specified time or SCN, one is drop flashback

Flash back to specified time or SCN:

SQL>flashback table emp to timestampto_timestamp

('2013-09-12 08:00:00','yyyy-mm-ddhh34:mi:ss');

SQL>flashback table emp to scn 11064241;

DROP Flashback:

If you drop a table by mistake, you can flashback as follows

SQL>flashback table s_emp1 to before drop;

You can flash back in the following way

SQL>flashback table flashbacktable to before drop rename to a;

SQL>flashback table "Recycle bin entity name"to before drop;

--The index of the table will also come back after flashback, but the index name is still the name shown in recyclbin, so use alterindex" bin$xxx" rename to xxxx command to modify the index name.

SQL>alter index "BIN$s6TKiw4uafDgRAAVF3jtoA==$0"

rename to PK_PC_STIM_INJ_ENHANCE_MON

3.3 Flashback query

delete After all the data in the database has been deleted, still commit, use the following method to restore the data

Query the database for data at a specified point in time.

SQL>select* from emp as of timestamp to_timestamp('2013-07-05 08:00:00','yyyy-mm-ddhh34:mi:ss');

·Delete a table and add data to the original table

SQL>insertinto emp select * from emp

as of timestamp to_timestamp

('2013-07-05 08:00:00','yyyy-mm-ddhh34:mi:ss');

3.4 Delete flashbackdrop

Flashbacks were dropped from the base table,

·Display recycling bin information

SQL>show recyclebin

--select* from user_recyclebin

·Completely delete base tables

SQL>drop table s_emp1 purge ;

---Completely delete a table with PURGE, such deleted tables cannot be flashback.

·Clear recycling bin

SQL>purge recyclebin;

·Search database options Products:

SQL>select * from V$option;

· Truncate does not retain storage space after deletion, while Delete retains storage space. If you want to delete these storage spaces to free up space, you can execute the following command:

SQL>alterttable emp deallocate unused;

·If you want to keep a portion, you can use:

SQL>alterttable emp deallocate unused 200k;

4. A few notes on data flashbacks

* System tablespace data must not flash back

:: The retention time of entities in the database is not guaranteed and depends on the health of the system, which may be a few seconds or months.

* When base table flashback occurs, constraints established on the table are not guaranteed

About Oracle flashback open and how the operation is shared here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.