Oracle Backup and Recovery

Faults in Oracle are divided into the following 4 types
.
1) Statement failure
A logical failure occurred during the execution of the SQL statement when the statement failed. For example, inserting invalid data into a table that violates a constraint, or if there is no space in the table space, the data cannot be inserted. At this point, the application person or DBA will handle the situation accordingly and can.
2) User Process failure
When a user program fails to access the Oracle database, a user process failure occurs because of an abnormally disconnected or terminated process, which causes the user process to terminate unexpectedly with the server if the network is not reachable and the client computer restarts.
A user process failure only causes the current user to not operate the database properly, but does not affect other user processes. Process Monitoring Program (Pmon) automatically performs process recovery when a user process fails. Pmon is a background process for Oracle that detects server processes that have lost connectivity to user processes. Pmon will handle the failure by rolling back the transaction, and will also release the resources currently occupied by the process.
3) Instance failure
An instance failure occurs when an Oracle DB instance cannot continue to run due to a hardware failure or a software problem. Hardware issues include accidental power outages, and software problems could be a server operating system crash.
When the database is restarted, Oracle automatically completes the instance recovery if an instance failure is found. Instance recovery restores the database to a state that is consistent with the transaction before the failure, and Oracle automatically rolls back uncommitted data.
4) Media failure
A media failure is a failure that occurs when a database file, a portion of a file, or a disk is unreadable or cannot be written. For example, damage to the hard disk head can result in a complete corruption of the database file. To fix database file corruption due to media failure, you need to use media recovery.
.
Definition and classification of backup and recovery
1. Definition and classification of backup
A backup is the process of copying a database to a dump device. Where the dump device refers to a tape or disk that is used to place a copy of the database. The following are categorized from different angles:

1) Classification from physical and logical angles
From a physical and logical point of view, backups can be divided into physical and logical backups.
① Physical Backup:
A backup of physical files (such as data files, control files, and log files) of the database operating system. Physical backups can also be broken down into offline backups (cold backups) and online backups (hot backups), which are performed when the database is shut down, which backs up the database that is running in the archive log mode. You can use Oracle's recovery Manager (RMAN) or operating system commands to make a physical backup of the database.
② logical Backup: A backup of a database logical component, such as a database object, such as tables and stored procedures. There are many means of logical backup, such as traditional exp, data pump EXPDP, database flashback technology and third-party tools, which can make logical backup of database.
.
2) Classification from the database backup policy perspective
① Full backup:
Make a full backup of the data every time. In the event of a data loss disaster, a full backup can achieve 100% data recovery without relying on additional information, with the shortest recovery time and the most convenient operation.
② Incremental Backup:
Only those files that were modified after the last full or incremental backup are backed up. The advantage is that the amount of backup data is small, the time required is short, the drawback is that the recovery needs to rely on the previous backup records, the risk of a larger problem.
③ Differential Backup:
Back up files that have been modified since the last full backup. Recovering a database from a differential backup takes a short time, so only two copies (the last full and last differential) are needed, with the disadvantage that it takes longer to take each backup.
.
2. Definition and classification of recovery
Recovery is the failure to re-establish a complete database using the backed up data or control files. The recovery is divided into the following two types.
1) Instance Recovery: Oracle automates recovery When an Oracle instance fails.
2) Media recovery: When the media that holds the database fails, the recovery occurs. Media recovery is also divided into full recovery and incomplete recovery.
Full recovery: The state when the database is restored to a database failure. This recovery is done by loading the database backup and using the full redo log.
Incomplete recovery: Restores the database to the state of a time before the database fails. This recovery is done by loading the database backup and applying a partial redo log. After an incomplete recovery, you must reset the online redo log with resetlogs selection when you start the database.

.

Using the Rman tool
RMAN (Recovery Manager) is an important tool for Oracle to back up and recover database files, archive logs, and control files. can also be used to perform a full or incomplete database recovery. Rman has 3 different user interfaces: command line, GUI (integrated in the OEM's backup manager), API approach (for integration into third party backup software).
.
It has the following advantages:
1) Support Online hot backup
2) Support multi-level incremental backup
3) Support parallel backup, recovery
4) Reduce the amount of backup required
5) Easy to use for backup and recovery
Rman has a rich set of features. With the release of Oracle's new version, the Rman feature has also been enhanced, enabling Rman to back up and recover databases in almost any situation. Many of these new features have been developed for the problems and difficulties encountered in daily work.

Rman components

1) Target Database
The target database is the database that needs to be backed up and restored by Rman. Rman can back up data files, control files, archive log files, SPFile.
.
2) Server session (server sessions)
Rman initiates the Oracle server process on the database and establishes a session with the target database. The actual operations that are backed up, restored, and restored by the server process on the target database.
.
3) Rman repository (Rman repository)
The control information used by Rman is metadata about backup, archive logs, and rman activity.
4) Recovery Catalog (recover catalog)
The recovery directory is a schema object that is built on the rman Recovery catalog database to hold the Rman repository data.
The recovery directory is an optional component. Rman records repository data in the control file of the target database, but it is not secure enough, as the control file corruption of the target database means that all Rman backups are invalidated. It is recommended that you set up a recovery directory in a separate data store to save one database data.
For
For most medium environments, the Rman backup information is stored in the recovery catalog database, not in the control file of the target database, so that the full functionality of the Rman tool can be played, and many of the advanced features of Rman are supported only in the context of creating a recovery catalog.
5) MML (Media Management Library)
MML (Media management layer) is a third-party tool or software that manages the tracking and management of tape read-write and file, and if you want to back up to tape directly from Rman, you must configure the media management layer, the tools of the media management layer, and Rman to complete the backup and recovery together.
.
6) Fast Flash recovery area
The Flash recovery area is the default disk location used by the Oracle database to hold all recovery-related files. These related files include archived logs, Rman backups, automatic control file backups, multiplexed control files, and redo log copies and flashback log files.
.
7) Secondary Database
When used normally, Rman is used with the target database, and if a recovery catalog database is created, it is also used with the recovery catalog database. In some cases, you want to create a secondary database that uses Rman to create a new database from a backup of the target database. The secondary database can be created as a standby database for use. In the event of a disaster in the product database, you can switch to the standby database without losing any data and minimizing downtime.

.
Create a Recovery Catalog

The recovery directory exists in the Rman catalog database, similar to the standard database directory, and consists mainly of the following sections:
1) Backup and restore information, can be multiple target database
2) Rman script, can be stored for re-use
3) Information about data files and log files
4) Information about the table space and data files for the target database
.

Create a Recovery Catalog
1) Create a Recovery catalog database
Create tablespace cc datafile '/opt/oracle/oradata/cc.ora '
Size 20M autoextend on next 5m MaxSize Unlimited

.
2) Create an Rman user in the recovery catalog database and authorize
Create user YDW identified by Oracle default Tablespace cc
Temporary Tablespace Temp

.
Grant Connect,resource to Ydw
Grant Recovery_catalog_owner to Ydw

.
3) Create a recovery directory in the recovery catalog database

.

.
3. Register the target database to the recovery directory
To log in to the target database, if you want to register the target database, you can follow these steps.

.
This completes the registration of the target database in the recovery directory, where you can use the recovery directory of Rman to back up and restore the target database.

.
Channel Assignment
When using Rman for backup and recovery operations, the channel assignment must be made, a channel is a connection between Rman and the target database, the channel specifies a type of device for backup and recovery, and the channel devices that Rman can use include both disk and tape.
1) Automatic channel configuration
Use the Configure command to configure an automatic channel.

.

.
2) Manual channel Configuration
Manually allocate channels using the allocate channel in the run block.
For example: Specify 3 disk channels, CH1, CH2, and CH3, respectively.

.
3) display channel configuration parameters

.
5. Concepts and operations related to backup and recovery
1) Backup set and backup slice
Backup set: The backup set is the collection of the first backup, which contains all the backup slices for this backup, saved in Oracle's proprietary format, and is a collection of logical data.
Backup slices: A backup set consists of several backup slices, each of which is a separate output file, and the size of a backup slice is limited, and its size cannot be greater than the maximum file length supported by the file system.
2) Configure the format of the backup set file
When backing up with the backup command, you need to specify the storage path and file name format of the backup file. Its path and format can be set uniformly using the format parameter. The format is composed of two parts, the storage path and the file name.
If you do not specify a storage path and file name format by using format, the backup set produced by backup is stored in the Flash recovery area by default, and Rman automatically uses%u to ensure that the file name is not duplicated.
The format command is formatted as follows:
Format ' formatted string '
Where the file name portion of the format string can use substitution variables, for example: Format '/backup/rmanback/%u '
The common substitution variables are as follows:
%c: Copy number of backup slices
%d: Database name
%d: The day ordinal (DD) in the month
%M: Month (MM) in the year
%F: A unique name based on dbid, which is in the form of c-| | | | | | -yyyymmdd-qq. where | | | | | | For the database DBID,YYYYMMDD is the date, QQ is a 1-256 sequence.
%n: Database name, fill to the right up to a maximum of 8 characters
%u: A 8-character name that is based on the number of backup sets and the creation time information.
%p: The backup piece number of the backup set, starting from 1 to the number of files created
%u: A unique file name generated by the system that, for backup slices, is equivalent to%u%p%c
%s: The number of the backup set
%t: Backup set timestamp
%T: Month and day format (YYYYMMDD)
.
3) Backup and Restore command
The ①backup command is used to back up database files, and you can back up multiple files, tablespaces, and entire databases to disk or tape as a backup set. Using the backup command outside of the Run command automatically uses the automatic channel, and using the backup command within the Run command, if a manual channel is defined, prioritizes the use of manually defined channels.
② databases that use Rman Backup can only be recovered using the recovery commands provided by Rman. The backup information for the target database is stored in the Rman recovery directory. Rman automatically restores database synchronization to a consistent state of data based on information stored in the recovery catalog.
Rman recovers a database with two commands, restore and recover. The resotre command restores the backup data to the specified directory, and the recover command restores the database synchronously.
? Resotre command: Because the backup slices in the backup set are stored in a proprietary Rman format and need to be rebuilt with the restore command, the result of the rebuild is to automatically generate the physical file structure of the target database.
? Recover command: Responsible for the archive log file for the reconstruction of the data file, to complete the synchronization of the database recovery. When executing the recover command, Rman needs to read the archive log, and if there is no archived log, or if the database is running in non-archive mode, the recovery process will error.

.
Case: Backup and recovery in archive mode
You want to use Rman for online backup operations. The database must be in archive mode. The recovery directory must be open, the target database process must be started, and the database is already loaded or opened.
1) Back up and restore the entire database
View current data mode as non-archive mode

.

.
Modify mode to archive mode

.

.
Backup and restore the entire database, automatic allocation of channels, format by default%u, backup set stored in the database Flash recovery area, backup files including data files, control files, redo log files and parameter files.

.
Check the Flash recovery area and find that the backup was successful.

.
If you also want to include archived log files, add the plus archivelog keyword, and delete input means that after the backup is complete, delete the Archivelog file.

.
Make specific paths and formats through format by automatically assigning channels
First create a backup directory:

.

.
Set directory permissions and back up

.

.

.
You can also manually assign channels to back up the database

.
Restore entire database (database to execute in Mount state)

.
Delete system01.dbf

.
Open the DB instance again

.
The database failed to start because SYSTEM01.DBF was deleted

Using SYS to connect Rman

.
Rebuild the database physical file.

.
Synchronous recovery.

.
Open Database

.

.
SYSTEM01.DBF table space has been restored.

.
2) Backup and restore table space
Table Space Users Backup:

.
Backing up multiple table spaces

.
To back up the tablespace in the Run command

.
If you have only lost data files for a particular tablespace, then you can choose to restore only that tablespace instead of restoring the entire database, and table space recovery can be done without shutting down the database, simply offline the tablespace that needs to be recovered

Simulating the deletion of table spaces

.
Recover table Space

.
View Tablespace file has been restored

.

3) Backup and recovery of data files
View data files

.
Backup data file users01.dbf (you can specify a file number when backing up and restoring, or you can specify a filename)

.
View data files

.
Delete a data file user01.dbf

.
When the data file or tablespace is restored, the data file and table space must be in the offline state, the data file is lost, but the database instance can also be used,

.
The status of data file 4 at this time is recover

.
Recovering data files

.

.
The data file DataFile 2 is in the online state

.
View the status of the data file, has become online status

.
or use the Run command to restore the command as follows:
Delete user01.dbf First

.
Start recovery:

.
View file has been restored.

.
Supplemental Automatic Backup:
1) Generate script files, such as BACKUP.RCV
Assume that the contents of the file are as follows:
$>cat BACKUP.RCV
Connect target Sys/password rcvcat rman/[email protected];
run{
Allocate channel C1 type disk;
Allocate channel C2 type disk;
Allocate channel C3 type disk;
Backup fileaperset 3 format '/u01/oradata/backup/arch%u%s%p
Archivelog all delete input;
Release channel C1;
Release channel C2;
Release channel C3;
}
2) Generate execution file
Build Backup_archive.bat on Windows, including:
Rman Cmdfile = BACKUP.RCV
Generate backup_archive.sh under UNIX, including:
/oracle/ramn/rman Cmdfile = BACKUP.RCV
3) Join the Dispatch
You can use the Task Scheduler Wizard in Windows, or the AT command.
In Unix, write a file on the target machine to start the automatic backup process. Assuming the file name is Oracle, the file will be placed in the/var/spool/cron/crontabs directory
$>cat Oracle
0 23 0 backup_archive.sh
#表示星期天23点对数据库备份
0 12,18 * backup_archive.sh
#表示每天12点, 18-point backup
Each line of the crontab file consists of six fields (Minutes,hours,day of month,month,day of Week,command) separated by a space or tab

.
Second, the use of data pump technology to achieve logical backup
A logical backup is the process of creating a logical copy of a database object and depositing it into a binary dump file. In essence, logical backup and recovery is actually the import and export of database implementation data.

.
Export:
Exporting a logical backup of a database is essentially reading a database set and writing the data set to a file (usually a DMP extension), where the export of these records is independent of the physical location.
Import:
The import and logical recovery of the database is essentially to read the exported binary dump file and restore it to the database.
.
1. Import/export using Data pump technology
Data pump is a technology that transmits at high speed between databases or before the database and operating system. The data pump tool runs on the server, and the database administrator needs to specify the database directory to hold the dumped data.
Command line-based data pump export steps are as follows
1) Connect to Oracle Database

.
2) Create an action directory

.
3) Grant user permission to manipulate the Dump_dir directory

.
4) Check whether the directory is established.

.
5) Create test user yyy and authorize

.
6) Export the table under the Scott user
Import data using the EXPDP command (you can export by table, export by user mode, export by table space and all-in-a-way), import data using the IMPDP command (you can import by table, import by user mode, import by table space, and full-Library import).

.
7) Remove the EMP table

.
8) Import the EMP table

.
9) Check that the EMP table has been restored

.

.

10) Import the Dept and EMP tables under the exported Scott user under the YYY user

Viewing tables with YYY User login

.

.
11) Export Scott User mode

.
12) Import Scott user mode

.
13) Import all the objects from Scott into yyy

.
View the YYY User's table and find a lot more.

.
14) Export Table space
Export YYY table Space (Create a yyy table space in advance)

.
Create a table in Tablespace yyy CCC

.

.

Export Table Space

.
15) Import Table space
Delete table space before import, related tables will also be deleted

.
Before you import a tablespace, you need to create a yyy table space

.
Import Table Space

.
The view table has been restored back.
Sql> Conn scott/12345
Sql> select * from SCOTT.CCC;
.
16) Export the entire database

.
17) Import the entire database

.

Third, the use of flash back technology
1. Flash Back Technology Overview
To enable the database to recover quickly from any logical misoperation, Oracle exits the flash back technology. This technology can restore the data of row-level and transaction-level, reduce the time of data recovery, and the operation is simple, the data can be recovered by SQL statement, and the efficiency of database recovery is greatly improved. Flashback technology is a major advance in the history of database recovery technology, fundamentally changing data recovery.
Flash back technology includes the following:
1) Flashback queries (flashback query): Queries the data information in the table in the past at a certain point in time or at the value of a SCN (System change number).
2) Flash back version query (Flashback version queries): Queries for changes in the data in a table in the past or in a SCN segment.
3) Flashback transaction query (Flashback transaction query): View the changes that a transaction or all transactions have made to the data over time.
4) Flashback Database (Flashback): Restores the database to a previous point in time or an SCN value.
5) Flashback Delete (Flashback drop): Restores the deleted tables and associated objects to their pre-deleted state.
6) The Flashback table (Flashback table): Restores the table to a previous point in time or to the state of an SCN value.
.

.
SCN:SCN is a number that accumulates incrementally when an Oracle database is updated, with DBMS auto-maintenance, and the current SCN number can be obtained by querying the CURRENT_SCN in the data dictionary v$database.
.
Attention:
The flashback database is implemented based on the flash Recovery zone (Flash Recovery area) feature, and if you want to use Flashback database technology, you also need to enable the Flashback recovery area
Flashback removal is implemented based on the Recycle Bin (Recycle bin) feature, and if you want to use the flashback removal technology, you also need to enable the Recycle Bin
The flashback query, the Flashback version query, the Flashback transaction query, and the flashback table are mainly based on the rollback information in the Undo table space.
.
2. Flash Back Recovery area
1) What is the meaning of the Flashback recovery area
Oracle recommends specifying a flash recovery zone (Flash recovery area) as the default location for backup and recovery related files, enabling Oracle to automate disk-based backup and recovery (automatic disk-based backup and recovery). So, what is the Flashback recovery area? Simply put, the flashback recovery area is a piece of storage space to store recovery-related files, allowing users to centrally store all recovery-related files. The following centralized files can be placed in the Flashback recovery area.
Control files
Archived log files
Flash back Log
Automatic backup of control files and SPFile
Rman Backup Set
Data file copy
.
2) Advantages of Flashback Recovery Area
The Flashback recovery area provides a centralized storage area, which greatly reduces management overhead, which is the main advantage, and can be quickly recovered with the use of Rman.
.
3) Set the Flashback recovery area
If you create a database using DBCA, you can set the location and size of the flashback recovery area when you install it.

.
The Flashback recovery area is set up and managed mainly by the following 3 initialization parameters.
Db_recovery_file_dest: Specify the location of the Flashback recovery area
Db_recovery_file_dest_size: Specify the amount of free space in the Flashback recovery area
Db_flashback_retention_target: This parameter is used to control the time of data retention in the flashback log, or the earliest point in time that you want the database to revert back to. The unit is min, the default is 1440min, that is, one day. Of course, the amount of time that can actually be returned depends on the size of the flashback recovery area, because it holds the flashback log required for fallback, so this parameter is modified with Db_recovery_file_dest_size.
If you want to undo the flashback recovery area, clear the value of the initialization parameter db_recovery_file_dest.
.
Note: Db_recovery_file_dest_size can only be emptied after db_recovery_file_dest is emptied.
5) Setting up the Flash back database
After setting the flash back recovery area, further configuration is required to enable the functionality of the flashback database. The database must already be in archive mode, and then set the flashback database.
The steps to set up the flashback database are as follows:
Enable archive mode (the flashback database can only be in archive mode)

.
Database is not set to flash back to database

.
Set up a flashback zone

.
Set the data retention period for the flashback database to one day in min

.
Enable Flashback Log

.
Query whether the Flashback recovery area was successfully enabled

.
Whether the query successfully enabled the Flashback database

.
6) Cancel the Flashback recovery area
Set the Db_recovery_file_dest parameter to NULL to deactivate the flashback recovery area, but if you have enabled the flashback database, you cannot cancel the Flashback recovery area, so you must disable the Flashback database before you can cancel the Flash back recovery area.
The steps are as follows:
Close the database
Shutdown Immediate

.
Loading the database
Startup Mount

.
Turn off database Flash back
Alter database Flashback off;

.
Open Database
Alter Database Open

.
modifying flashback parameters
Alter system set_recovery_file_dest= ';

.
Three, flash back error operation technology
1. Flash back to the database
The flashback database can quickly roll back data to a previous point in time or to a SCN, which is particularly useful for recovering a database from logical errors, and is the best choice for recovering a database when most logical damage occurs.
function like a rewind button for a database.
? can be used in cases where the user has caused logical data corruption.

.
The basic syntax for the flashback database is
FLASHBACK database [Database] to
[scn| TIMESTAMP expression]|
[Before scn| Timestampexpression]
Parameter description
To SCN: Restores the database to the state of the specified SCN
To TIMESTAMP: Restore the database to a specified point in time
To before SCN: Restores the database to the previous SCN state of the specified SCN
To before TIMESTAMP: Restore the database to the state before the specified point in time
.
Limitations of Flashback Database operations:
Media failures such as corrupted or missing data files cannot be recovered using the flashback database. The flashback database can only be based on data files that are currently running correctly.
After the Flashback database feature starts, you cannot use the Flashback database if a database control file rebuild occurs or a backup recovery control file is used.
You cannot use the Flashback database for data file shrink operations.
You cannot use the Flashback database to restore the database to the SCN that precedes the earliest SCN in the flashback log, because the flashback log file is deleted under certain conditions, not always in the flashback recovery area.

.
Case: Using the SCN to flash back to the database
1) Querying the current SCN of the database

.
2) Change the current state of the database, simulate creating a table BXJG, and insert a piece of data.

.
3) Flashback database recovery to restore the database to the state before the table was created, that is, the SCN is 1915426

.
4) Open the database with the Resetlogs option

.
5) Verify the status of the database (the BXJG table should not exist)

.
6) You can query the data dictionary view V$flashback_database_log to get the earliest SCN number that can be flashed back, and the flashback value cannot be less than the first. Likewise, if you flash back through a point in time, the flashback time cannot be less than the earliest time the query was given.
To set the display time format:

.
Querying the current oldest flashback SCN and time in the database

.
7) Flash back to the database at the specified time
The current time of the data system is queried first

.
Changes the current state of the database, simulates creating a table test2, and inserts a piece of data

.

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

.

.
Open a database using Resetlogs

.
Verify the status of the database Test2 table exists.

.
2. Flash Back Table
The flashback table is to restore the table to a certain point in the past or the specified SCN without recovering the data file, providing the DBA with an online, fast-reading, convenient way to recover, which can restore the error of modifying, deleting, inserting and so on.
The process of recovering data in a table using the Flashback table technique is actually the process of DML operations on the table. Oracle automatically maintains indexes, triggers, constraints, and so on that are associated with tables.
In order to use the Database Flashback table feature, the following conditions must be met
The user has flashback any table system permissions, or has flashback object permissions on the action table.
User has the SELECT, INSERT, delete, Alter object permissions on the action table
Starting the row movement attribute of the manipulated table can be done in the following ways.
Alter table name enable row movement;

.
The basic syntax for the flash back table operation is
FLASHBACK TABLE [schema.] Table to
scn| TIMESTAMP expression
[enable| DISABLE TRIGGERS]
.
Parameter description
SCN: Restore the table to the specified SCN state
TIMESTAMP: Restores a table to a specified point in time
enable| DISABLE TRIGGER: The triggers on the table are activated or disabled during the recovery of data in the table (default is disabled)
.
Note: The SYS user or the user logged in as SYSDBA cannot perform a flash back table operation

.
Case: Using the SCN flash back table
1) Log in with Scott User

.
2) Change the current state of the database, simulate the creation of the table Test3, and insert 3 data

.
Sql> commit;

.

3) Query The current SCN number, if the current user does not have permission to query v$database, with the SYS user logon, grant the current user access to the data dictionary permissions.

.
4) Update the data (change the name of the id=1 to Batianhu, delete the id=3 data), and submit.

.

.
5) Start the row Movement feature of the TEST3 table

.
6) Restore the Test3 table to the 2016-12-26 15:17:50,..... 7) Query

.

.
8) Restore the table Test3 to the SCN 1923436 state

.
3. Flash back Delete
Flashback Delete can recover a table deleted using the DROP TABLE statement, which is a recovery mechanism for accidentally deleted tables.
The implementation of the Flashback deletion feature is achieved primarily through the "Recycle Bin" technology in the Oracle database. In an Oracle database, when a drop table operation is performed, the space for tables and their associated objects is not immediately reclaimed, but instead they are renamed and saved in a logical container called the Recycle Bin until the user decides to permanently delete them or if the table space storage for the table is insufficient. The table can actually be deleted. In order to use the flashback removal technique, the Recycle Bin of the database must be turned on.

.
1) Start the Recycle Bin, set the parameter RecycleBin to on, and the Recycle Bin is started by default.

.
2) Review the Recycle Bin. When a drop table operation is performed, the table and its associated objects are named and saved in the Recycle Bin, and the deleted table and its associated object information can be obtained by querying the User_recyclebin,dba_recyclebin view.

.
Select Object_name,original_name,type from User_recyclebin;
.
Note: SYS users are not supported, and objects under the system table space cannot be retrieved from the Recycle Bin, so the query is empty when logging in with SYS or the system user.
.
3) If you use the purge phrase when you delete a table, the table and its associated objects are released directly, the space is reclaimed, and the information is not entered in the Recycle Bin.
Create a table and use the purge phrase to delete

.
View in Recycle Bin No

.
Select object_name, Original_name, type from User_reclebin;
.
4) emptying the Recycle Bin
Because the information for the deleted table and its associated objects is saved in the Recycle Bin, its storage space is not released, so you need to periodically empty the Recycle Bin, or clear the unused objects (tables, indexes, tablespaces) in the Recycle Bin, freeing up the disk space they occupy.
.
The syntax is as follows:
PURGE [table name | Index index]
[RecycleBin | Dba_recyclebin] | [Tablespace tablespace [user User]]
Parameter description:
Table: Clears the specified table from the Recycle Bin
Index: Clears the specified index from the Recycle Bin and reclaims its disk space
RecycleBin: Empty the user Recycle Bin and reclaim disk space for all objects
Dba_recyclebin: Empty the Recycle Bin for the entire database system, only users with SYSDBA permissions can use the
Tablespace: Clears the specified amount of space in the Recycle Bin and reclaims disk space
User: Clears objects for specific users in the specified tablespace in the Recycle Bin and reclaims disk space

.
5) Flash Back delete operation
FLASHBACK TABLE [schema.] Table to before DROP [RENAME to table]
.
Note: Only locally managed tables in non-system tablespace can use the flashback delete operation.
.
Case: Flash Back Delete Example
Create a new table, insert data and submit

.
Delete a table

.
Flash back Table

.
Or rename the table after the flash back

.
4, Flash back query technology
Flashback query: Allows viewing of old data based on point-in-time or SCN. In addition to viewing old data, you can undo the wrong changes by retrieving old data when needed.
The basic syntax is as follows:
Select column_name[,...]
From table_name
[As of SCN | Timestamp expression]
Where condition
.
Parameter description:
As for timestamp: time-based flash back query
As of SCN: SCN-based flashback query
.
Case: Flash Back Query instance
1) Use Scott User login, the EMP table based on the timestamp of the Flash back query

.
2) Update salary for employee number 7844, update to 2000, and submit things

.
The first transaction commits, and the second thing is automatically opened.
.
3) Update the salary of employee number 7844, update two times, submit things again.

.
The second thing commits, automatically opens the third transaction
4) Update the payroll for Employee number 7844 again, update to 3500, and submit things

.
Execute Commit Commit
.
The third transaction commits, and the fourth transaction is automatically opened
.
5) Check the current salary of employee No. No. 7844

.
6) Check the salary for the first one hours of employee No. No. 7844

.
7) Query the first transaction commit, the second thing has not been submitted when the salary of employee number No. 7844

.
8) If necessary, you can restore the data to the state of a previous time

Oracle Backup and Recovery