Flash back Database concept: 10 Gb new feature. After the flashbackdatabase function is enabled, the database regularly writes the image before the changed data block to the log file of the flash back log. When the database is flashed back, these data blocks can be directly copied back to meet the database recovery needs. REDOLOG can be used to help restore data to a more accurate time
Flash back database concept: 10 Gb new feature. After the flashback database function is enabled, the database regularly writes the image before the changed data block to the log file of the flash back log, during database flashback, these data blocks can be directly copied back to meet the database recovery needs. Redo log can be used to help restore data to a more accurate time
Concept of flashback database:
10 Gb new feature. After the flashback database function is enabled, the database regularly writes the pre-image of the changed data block to the log file of the flash log,
During database flashback, these data blocks can be directly copied back to meet the database recovery needs.
The redo log can be used to help restore data to a more precise time point and shorten the recovery time.
The log file of the flash back database is written by the recovery writer RVWR process.
The Flash log file is automatically created and maintained by the RVWR process in the Flash recovery area.
The flash back database feature is disabled by default and needs to be enabled in the MOUNT status.
During flashback, unrelated tablespaces can be temporarily OFFLINE to speed up recovery. Only the tablespace corresponding to system undo and the data to be flashed can be online.
Data Files cannot be physically damaged. Only physical recovery is required.
Data File shrink
Use the backup control file-or recreate the control file because the flash log information is recorded in the control file
Tablespace deletion --
To return to a time point that is earlier than the minimum SCN in the Flash log --
Such as deleting a user, You can first flash back to the pre-deletion, open in read only mode, and then export EXPDP to the deleted user.
The database is completely restored, read write is enabled, and then the user is imported.
Summary:
1. Restart the database to the MOUNT status-startup mount;
2. Open archive mode-alter database archivelog; if you do not archive, an error will be reported when you enable the flashback database: ORA-38707: Media recovery is not enabled.
3. set the flashback region -- you need to create a directory in the OS and specify the size-not specified or the specified space is insufficient report: ORA-38709: Recovery Area is not enabled. ORA-38708: not enough space for first flashback database log file
Alter system set db_recovery_file_dest_size = 2g;
Alter system set db_recovery_file_dest = '/backup/flashback_area ';
4. Open the DATABASE, query the archive mode-archive log list, and query the status of the V $ DATABASE. FLASHBACK_ON field,
In the following experiment, the flash back database function is enabled and a flash back database operation is performed to restore data.
1. view the database archiving status and enable the flash back database function 22:08:49 SQL> conn/as sysdba
Connected.
22:08:54 SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination/u01/app/oracle/archbys001/
Oldest online log sequence 46
Next log sequence to archive 48
Current log sequence 48
If the archive is not enabled, the following error is reported:SYS @ bys3> alter database noarchivelog;
Database altered.
SYS @ bys3> alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on flashback database logging.
ORA-38707: Media recovery is not enabled.
Check the flash back status. The default value is NO. It is not enabled.
22:08:56 SQL> select flashback_on from v $ database;
FLASHBACK_ON
------------------
NO
22:09:18 SQL> host
Filesystem Size Used Avail Use % Mounted on
/Dev/sda2 19G 9.2G 8.9G 51%/
/Dev/sda1 99 M 21 M 74 M 22%/boot
Tmpfs 3.0G 484 M 2.6G 16%/dev/shm
/Dev/sda5 4.6G 1.4G 3.1G 31%/backup
[Oracle @ oel-01 ~] $ Cd/backup
[Oracle @ oel-01 backup] $ mkdir flashback_area
[Oracle @ oel-01 backup] $ ls
Flashback_area
Full
Lost + found
[Oracle @ oel-01 backup] $ exit
Exit
Manually specify the size and directory of the flash back area. You can use the default value.
22:10:53 SQL> alter system set db_recovery_file_dest_size = 1g;
System altered.
At 22:11:47 SQL> alter system set db_recovery_file_dest = '/backup/flashback_area ';
System altered.
If no flash back area is specified, the following error is returned:SYS @ bys3> alter database archivelog;
Database altered.
SYS @ bys3> alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on flashback database logging.
ORA-38709: Recovery Area is not enabled.
Define a time limit and set the maximum time limit for the database to flash back, in minutes. 2880 is two days. You can use the default value.
22:11:58 SQL> alter system set db_flashback_retention_target = 2880;
System altered.
22:14:12 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
22:14:36 SQL> startup mount;
ORACLE instance started.
Total System Global Area 845348864 bytes
Fixed Size 1339796 bytes
Variable Size 583011948 bytes
Database Buffers 255852544 bytes
Redo Buffers 5144576 bytes
Database mounted.
22:20:43 SQL> show parameter db_recovery
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string/backup/flashback_area
Db_recovery_file_dest_size big integer 1G
Enable the flash back database feature
22:21:18 SQL> alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on flashback database logging.
ORA-38708: not enough space for first flashback database log file
22:21:32 SQL> alter system set db_recovery_file_dest_size = 2g;
System altered.
22:26:22 SQL> alter database flashback on;
Database altered.
22:26:27 SQL> alter database open;
Database altered.
22:26:57 SQL> select flashback_on from v $ database;
FLASHBACK_ON
------------------
YES
2. Perform DDL and DML operations in the database, and delete the created table 22:27:35 SQL> conn bys/bys
Connected.
22:34:41 SQL> create table test7 as select * from test6;
Table created.
22:37:43 SQL> select * from test7;
ABC
---------
1
2
22:37:53 SQL> select current_scn from v $ database;
CURRENT_SCN
-----------
1373570
22:38:02 SQL & gt; drop table test7;
Table dropped.
22:38:19 SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-----------------------------------------------------------------------------
TEST7 BIN $ 39Q/PNkxLungQFXeqUARIQ = $0 TABLE 2013-06-23: 22: 38: 18
22:38:24 SQL & gt; purge recyclebin;
Recyclebin purged.
22:39:52 SQL> show recyclebin;
22:39:54 SQL> select oldest_flashback_scn from v $ flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
1373161
22:40:28 SQL> ALTER SESSION SET nls_date_format = 'dd-MON-YYYY HH24: MI: ss ';
Session altered.
Query the time corresponding to the earliest SCN --- here is the time for enabling the flash back database feature above.
You can also query the oldest SCN from the OLDEST_FLASHBACK_SCN column.
22:40:35 SQL> SELECT oldest_flashback_time FROM v $ flashback_database_log;
OLDEST_FLASHBACK_TIM
--------------------
23-JUN-2013 22:26:27
3. Restart the database to the MOUNT state and perform the flash back database operation 22:42:19 SQL> shutdown immediate;
ORA-01031: insufficient privileges
22:43:00 SQL> conn/as sysdba
Connected.
22:43:05 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
22:43:16 SQL> startup mount;
ORACLE instance started.
Total System Global Area 845348864 bytes
Fixed Size 1339796 bytes
Variable Size 583011948 bytes
Database Buffers 255852544 bytes
Redo Buffers 5144576 bytes
Database mounted.
22:49:14 SQL> ALTER SESSION SET nls_date_format = 'dd-MON-YYYY HH24: MI: ss ';
Session altered.
At 22:49:20 SQL> select sysdate from dual;
SYSDATE
--------------------
23-JUN-2013 22:49:25
22:49:25 SQL> SELECT oldest_flashback_time FROM v $ flashback_database_log;
OLDEST_FLASHBACK_TIM
--------------------
23-JUN-2013 22:26:27
22:50:38 SQL> select oldest_flashback_scn from v $ flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
1373161
22:50:48 SQL> flashback database to scn 1373570;
Flashback complete.
The following statement tests the available timestamp flash back. I did not use it.
SQL> flashback database to timestamp to_timestamp ('2017-06-26 14:05:10 ', 'yyyy-MM-dd hh24: mi: ss ');
After resetlogs is enabled, you cannot roll back to the time point before resetlogs.
At 22:51:53 SQL> alter database open resetlogs;
Database altered.
22:53:18 SQL> show user
USER is "SYS"
4. Use the BYS user to log in and verify that the data before the table deletion operation is normal. 22:54:12 SQL> conn bys/bys
Connected.
22:55:13 SQL> select * from test7;
ABC
---------
1
2
Supplement: you are not sure whether the flash back time or SCN is sufficient for data recovery. You can open the database in read only mode.
That is, flashback database to scn 1373570; or flashback database to timestamp to_timestamp ('2017-06-26 14:05:10 ', 'yyyy-MM-dd hh24: mi: ss ');
Then alter database open read only; then query the data to see if the required data has been restored.
If the data is not restored, you can continue to use statements such as flashback database to SCN/TIMESTAMP to restore the data.
After you have recovered all required data, you can use the alter database open resetlogs statement to open the database.
After resetlogs opens the database, it cannot use flashback to return to the time point before resetlogs.
Add a recovery experiment for a forced checkpoint. It can be used to refresh the data dictionary during PSU.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220432 bytes
Variable Size 125829296 bytes
Database Buffers 406847488 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> create restore point p1 guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
SQL> create user bys identified by bys;
User created.
SQL> grant dba to bys;
Grant succeeded.
SQL> Flashback database to restore point p1;
Flashback database to restore point p1
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> conn bys/bys
Connected.
SQL> conn/as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220432 bytes
Variable Size 125829296 bytes
Database Buffers 406847488 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>Flashback database to restore point p1;
Flashback complete.
SQL>Alter database open;
Alter database open
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>Alter database open resetlogs;
Database altered.
SQL>Select username from dba_users;
USERNAME
------------------------------
OLTP_USER
OE
HR
SST
SH
TSMSYS
DIP
DBSNMP
OUTLN
SYS
SYSTEM
11 rows selected.
SQL>
