Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will learn how to create a Flashback Database with the help of commands.
1. We check the existing data of the table on which Logical Corruption will be performed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [Primary-1] SQL> select * from hr.locations; LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE CO ----------- ---------------------------------------- ------------ ------------------------------ ------------------------- -- 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San Francisco California US 1600 2007 Zagora St 50090 South Brunswick New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA 2000 40-5-12 Laogianggen 190518 Beijing CN 2100 1298 Vileparle (E) 490231 Bombay Maharashtra IN 2200 12-98 Victoria Street 2901 Sydney New South Wales AU LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE CO ----------- ---------------------------------------- ------------ ------------------------------ ------------------------- -- 2400 8204 Arthur St London UK 2500 Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford UK 2600 9702 Chester Road 09629850293 Stretford Manchester UK 2700 Schwanthalerstr. 7031 80925 Munich Bavaria DE 2800 Rua Frei Caneca 1360 01307-002 Sao Paulo Sao Paulo BR 2900 20 Rue des Corps-Saints 1730 Geneva Geneve CH 3000 Murtenstrasse 921 3095 Bern BE CH 3100 Pieter Breughelstraat 837 3029SK Utrecht Utrecht NL 3200 Mariano Escobedo 9991 11932 Mexico City Distrito Federal, MX 20 rows selected. |
2. We create a restore point before the transaction is performed.
1 2 3 | 11:24:37 [Primary-1] SQL> create restore point delete_locations; Restore point created. |
3. We are learning SCN.
1 2 3 4 5 | 11:25:07 [Primary-1] SQL> select current_scn from v$database; CURRENT_SCN ----------- 20608714 |
4. We perform deletion.
1 2 3 4 5 6 7 | 11:25:58 [Primary-1] SQL> delete hr.locations where location_id like '3%'; 3 rows deleted. 11:26:11 [Primary-1] SQL> commit; Commit complete. |
5. We close the database.
1 | 11:27:30 [Primary-1] SQL> ! srvctl stop database -d primary |
6. The database opens in MOUNT mode.
1 2 3 4 5 6 7 | 11:28:03 [Primary-1] SQL> ! srvctl start database -d primary -o mount [Primary-1] SQL> select status from gv$instance; STATUS ------------ MOUNTED MOUNTED |
7. Flashback Database is created.
1 2 3 | [Primary-1] SQL> flashback database to restore point delete_locations; Flashback complete. |
8. By opening the database read only, it is checked whether the deleted data has been retrieved or not.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | [Primary-1] SQL> alter database open read only; Database altered. [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> select * from hr.locations; LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE CO ----------- ---------------------------------------- ------------ ------------------------------ ------------------------- -- 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San Francisco California US 1600 2007 Zagora St 50090 South Brunswick New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA 2000 40-5-12 Laogianggen 190518 Beijing CN 2100 1298 Vileparle (E) 490231 Bombay Maharashtra IN 2200 12-98 Victoria Street 2901 Sydney New South Wales AU LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE CO ----------- ---------------------------------------- ------------ ------------------------------ ------------------------- -- 2400 8204 Arthur St London UK 2500 Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford UK 2600 9702 Chester Road 09629850293 Stretford Manchester UK 2700 Schwanthalerstr. 7031 80925 Munich Bavaria DE 2800 Rua Frei Caneca 1360 01307-002 Sao Paulo Sao Paulo BR 2900 20 Rue des Corps-Saints 1730 Geneva Geneve CH 3000 Murtenstrasse 921 3095 Bern BE CH 3100 Pieter Breughelstraat 837 3029SK Utrecht Utrecht NL 3200 Mariano Escobedo 9991 11932 Mexico City Distrito Federal, MX 20 rows selected. |
9. The database is closed and opened with RESETLOGS.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [Primary-1] SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. [Primary-1] SQL> startup mount; ORACLE instance started. Total System Global Area 5428244480 bytes Fixed Size 2262856 bytes Variable Size 1124075704 bytes Database Buffers 4294967296 bytes Redo Buffers 6938624 bytes Database mounted. [Primary-1] SQL> alter database open resetlogs; Database altered. |
10. Other instances are also opened.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [Primary-2] SQL> startup mount; ORACLE instance started. Total System Global Area 5428244480 bytes Fixed Size 2262856 bytes Variable Size 1124075704 bytes Database Buffers 4294967296 bytes Redo Buffers 6938624 bytes Database mounted. [Primary-2] SQL> alter database open; Database altered. [Primary-2] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
11. Other Flashback commands.
1 2 3 | [Primary-1] SQL> flashback database to scn ...; [Primary-1] SQL> flashback database to timestamp to_timestamp('23-05-2018 10:00:00','DD-MM-YYYY HH24:MI:SS'); [Primary-1] SQL> flashback database to timestamp (SYSDATE -1/24); |
