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

Once the flashback database is enabled

1. Enable archive mode

Sql > shutdown immediate

Sql > startup mount

Sql > alter database archivelog

two。 Establish a flashback zone

Sql > alter system set db_recovey_file_dest='/opt/oracle/flash_recovery_area' scope=both

Sql > alter system set db_recovery_file_dest_size=3g scope=both

3. Sets the data retention period of the flashback database to one day, in min

Sql > alter system set db_flashback_retention_target=1440

4. Enable flashback log

Sql > alter database flashback on

Sql > alter database open

Second, use scn to flashback the database

1. Query the current scn of the database system (write down this scn)

Sql > select current_scn from v$database

two。 Change the current state of the database, simulate the creation of the table test1, and insert a piece of data

Create table test1 (id number,name char (20))

Insert into test1 values (1 recording data')

Commit

3. Perform a flashback database recovery to restore the database to the state it was before the table was created

SQL > select OLDEST_FLASHBACK_SCN from vault FLASHBACKDATABASE log; (view scn)

Shutdown immediate

Startup mount

The scn that flashback database to scn found earlier

4. Finally, use the resetlogs option to open the database

Alter database open resetlogs

5. Verify the state of the database (the test1 table should not exist)

SQL > select from test1

Select from test1

*

ERROR at line 1:

ORA-00942: table or view does not exist

6. Flashback the database at the specified time

1) set the display date format

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

Select sysdate from dual; (View system time)

2) change the current state of the database, simulate the creation of table test2, and insert a record

Sql > set time on

Sql > create table test2 (id number,name char (20))

Sql > insert into test2 values (1)

Sql > commit

Sql > shutdown immediate

Sql > startup mount

Sql > flashback database to timestamp (to_timestamp ('.')); That is, the time of looking up the system before)

Sal > alter database open resetlogs

Sql > select * from test2

ERROR at line 1:

ORA-00942: table or view does not exist (shows that test2 table does not exist)

Description: restrictions on flashback database operations

1) Media failures such as corrupted or missing data files cannot be recovered using a flashback database. The flashback database can only be based on currently running data files.

2) after the flashback database function is started, if the database control file is rebuilt or the backup is used to restore the control file, the flashback database cannot be used.

3) cannot use flashback database for data file shrinking operation

4) you cannot use the flashback database to restore the database to the flashback log to get the earliest SCNM before SCN, because the flashback log file is deleted under certain conditions rather than always saved in the flashback recovery area

3. Flashback table

A flashback table can restore the table to a specific point in time or to a specified SCN

A flashback table is actually a process of DML a table.

The database remains online

In order to use the database flashback table feature, the following conditions must be met:

The user has FLASHBACK ANY TABLE system permissions or FLASHBACK object permissions for the table being manipulated

The user has permissions on the SELECT,INSERT,DELETE,ALTER object of the table being manipulated

Starting the ROW MOVEMENT feature of the manipulated table can be done in the following ways

ALTER TABLE table ENABLE ROW MOVEMENT

Be careful

SYS users or users logged in as AS SYSDBA cannot perform flashback table operations

1. Log in using the scott user

Sql > alter user scott account unlock

Sql > alter user scott identified by 123456

Sql > grant connect,resource to scott

Sql > conn scott/123456

Set time on

Sql > create table test3 (id number,name char (20))

Sql > insert into test3 values (1)

Commit

Sql > insert into test3 values (2)

Commit

Sql > insert into test3 values (3 recordings Wang')

Commit

Connect to the database as sys, grant the scott user permission to select any dictionary, and finally view the current scn as scott

Conn / as sysdba

Grant select any dictionary to scott

SQL > set time on

Sql > conn scott/123456

Sql > select current_scn from vault database; write down this scn

Sql > update test3 set name='liu' where id=1

Sql > commit

Sql > select from test3

Sql > delete from test3 where id=3

Sql > commit

Sql > select from test3

two。 Start the row movement feature of the test3 table

Sql > alter table test3 enable row movement

Sql > flashback table test3 to timestamp to_timestamp (. )

IV. Flashback deletion

Flashback deletion restores tables deleted using DROP TABLE statements, which is a recovery mechanism for tables that are accidentally deleted

The flashback deletion function is mainly realized through the "Recycle Bin" (Recycle Bin) technology in the database.

In order to use flashback delete technology, the Recycle Bin of the database must be opened.

Start the Recycle Bin

View the Recycle Bin

The basic syntax for flashback deletion is

FLASHBACK TABLE [schema.] table TO BEFORE DROP [RENAME TO table]

Be careful

SYS users are not supported, and objects under the SYSTEM tablespace are not available from the Recycle Bin. Therefore, when a user logs in using SYS or SYSTEM, the query is empty.

1. Start the Recycle Bin and set the parameter recyclebin to on. By default, the Recycle Bin has been started.

SQL > conn / as sysdba

Sql > show parameter recyclebin

Sql > alter system set recyclebin=on deferred

two。 View the Recycle Bin. Sys users are not supported, and objects in system tablespaces cannot be obtained from the Recycle Bin, so switch to scott user testing.

Sql > conn scott/123456

Create table test4 (id number,name char (20))

Sql > insert into test4 values (3 recordings Wang')

Sql > commit

Sql > drop table test4

Sql > select object_name,original_name,type from user_recyclebin

Flashback table and rename to new_test4

Sql > flashback table test4 to before drop rename to new_test4

Five flashback queries

Sql > conn scott/123456

Sql > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Sql > set time on

Sql > select empno,sal from scott.emp where empno=7844

Sql > update scott.emp set sal=2000 where empno=7844

Sql > commit

Sql > update scott.emp set sal=2500 where empno=7844

Sql > update scott.emp set sal=3000 where empno=7844

Sql > commit

Query the salary value of 7844 employees in the previous hour.

Sql > select empno,sal from scott.emp as of timestamp sysdate-1/24 where empno=7844

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.