Test 2 ORACLE Flashback recovery data

Flashback recovering data
Preparatory work:

1. Create a table space
Create tablespace zxq datafile ' d:\zxq.dbf ' size 2m;
2. Create a user
Create user Zxq identified by zxq default Tablespace zxq;
3. Create a test table
CREATE TABLE Zxq.zxq as SELECT * from Scott.emp;


Modify database does not flash back
ALTER DATABASE Flashback off
Current SCN
Select Current_scn from V$database;
1426285

Delete a table
drop table Zxq.zxq;

Flash Back to deleted table
Flashback table Zxq.zxq to before drop;
Ok
Querying data
Select COUNT (*) from ZXQ.ZXQ
COUNT (*)
-------------------------------
14
Delete a table
drop table Zxq.zxq;

Shut down the database in the Open
Shutdown immediate;
startup;
Querying data tables
Select COUNT (*) from ZXQ.ZXQ as of SCN 1426285
Table does not exist error

Recovering a deleted table Zxq
Flashback table Zxq.zxq to before drop;
Select COUNT (*) from ZXQ.ZXQ
COUNT (*)
-------------------------------
14

Table Data Deletion
Delete from Zxq.zxq;
Querying data tables
Select COUNT (*) from ZXQ.ZXQ;
COUNT (*)
-------------------------------
0

Data for deleted tables is queried via SCN (data from undo)
Select COUNT (*) from ZXQ.ZXQ as of SCN 1426285
COUNT (*)
-------------------------------
14

Try to recover
Flashback table Zxq.zxq to SCN 1426285;
Error, table does not support move

Move the row
ALTER TABLE ZXQ.ZXQ enable row movement
Revert to the specified SCN again
Flashback table Zxq.zxq to SCN 1426285;
Ok
Querying the recovered data
Select COUNT (*) from ZXQ.ZXQ;
COUNT (*)
-------------------------------
14

Conclusion
Table-level recovery and ALTER DATABASE flashback OFF/OF no relationship

Test 2 Recovery Database

Delete a table
drop table Zxq.zxq;

Recovering a database to a specified SCN
Flashback database Zxq.zxq to SCN 1426285;
Error, database is not open flashback

Shutdown and boot to mount state
Shutdown immediate;
startup Mount;
Open Database Flash back support
ALTER DATABASE flashback on;
Recovering a database to a specified SCN
Flashback database Zxq.zxq to SCN 1426285;
Reported errors
The log is not complete.

Recovery failed.
conclusion, there is no log and can not be restored.



Open Database
ALTER DATABASE open;


Inserting data
Insert into ZXQ.ZXQ (empno) values (1);
Insert into ZXQ.ZXQ (empno) values (2);

Querying the current SCN
Select Current_scn from V$database;
1429091
Delete data
Delete from Zxq.zxq;
16 Rows are deleted

Attempt to recover data
Flashback database to SCN 1429091;
Error, the open state of the database cannot be flashed back.
Shut down the database in the Open
Shutdown immediate;
startup Mount;
Recovering data again
Flashback database to SCN 1429091;
Ok
Completed:flashback database to SCN 1429091
Database Open
ALTER DATABASE open;
Error, you need to open the database in Resetlogs mode.

Resetlogs Way to open data
ALTER DATABASE open resetlogs;
To view recovered data
Select COUNT (*) from ZXQ.ZXQ;
COUNT (*)
-------------------------------
16

Recovery is complete.

The role of Resetlogs
Prevent stale data from entering the database (to ensure database consistency), which is why the database is opened with Resetlogs, be sure to do a full backup of the database immediately.
In the control file, the data file Header,redo Log header Stores "Resetlogs data", which can be checked for consistency when open resetlogs is executed.
When to use Resetlogs?
1. Incomplete recovery
2. Restore with backup control files
3. Newly created control files to restore

Attention:
With the Resetlogs option, the current log sequence number (log sequence numbers) is reset to 1 and all log information is discarded. The Resetlogs option is required when the following conditions are used:
In the incomplete recovery (media recovery);
Use a backup control file.
After you open a database with Resetlogs, be sure to make a full database backup.

Test 2 ORACLE Flashback recovery data