Oracle 11g R2 Rman, data pump, flash back backup and recovery

Oracle 11g R2 Rman Backup
Definition and classification of backup and recovery

Definition and classification of backup:

Backup is the process of copying a database to a dump device. Among them, the dump device refers to the tape or disk used to place a copy of the database. A copy of the database stored in the dump device is often referred to as a backup or dump of the original database. A backup is a copy of data that is categorized as follows from different perspectives:

From a physical and logical point of view:

From physical and logical, backups can be divided into physical and logical backups.

Physical Backup: A backup of the physical files (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 closed, and the database is backed up in archived logs. 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. Logical backup means many, such as the traditional exp, data pump (EXPDP), database flashback technology and other third-party tools, can be a logical backup of the database.

From the backup perspective of the database:

From a database backup perspective, backups can be divided into full and incremental backups and differential backups

Full backup: Every time a full backup is made to a database, a full backup can achieve 100% data recovery without relying on additional information when a data loss disaster occurs, 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: Backs up files that have been modified since the last full backup. Recovering data from a differential backup is short, so you only need two copies of the data---the last full backup and the last differential backup, the disadvantage is that each backup takes a long time.

Definition and classification of recovery
Recovery is to use the backed up data files or control files to rebuild a complete database after a failure occurs. There are two types of recovery:

Instance Recovery: Oracle automates recovery When an Oracle instance fails

Media recovery: Recovery when the media that holds the database fails. 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 applying 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 the Resetlogs option when you start the database.

There are three ways in which Rman stores the storage type:

1. Default type of backup set

2. Compressing the backup set

3. Mirror Copy

Each file that makes up each backup set is called a backup slice

Rman backups can be stored in two locations:

1. Control files for the target database

2. Restore the Directory

Advantages of Rman Backup:

1.RMAN only valid data is backed up

2.RMAN Enables incremental backups

Using the Rman tool
Rman is an important tool for Oracle to back up and recover data files, archive logs and control files, and to perform full or incomplete database recovery. Rman has 3 different user interfaces, command line, GUI (integrated in the OEM backup manager), API mode (for integration into third-party backup software), which has the following characteristics:

Support Online Hot backup

Multi-level incremental backup support

Supports parallel backup, recovery

Reduce the amount of backup required

Easy backup and recovery use

Components of Rman
Target Database

The target database is the database that needs to be backed up and restored by Rman, and Rman can back up data files, control files, archive logs, SPFile;

Service session (server session)

Rman starts the Oracle server process on the database and establishes a session with the target database, with the actual operations of the server process on the target database for backup, restore, and recovery.

Rman Repository (Rman repository)

The control information used during Rman usage is metadata about backup, archive logs, and Rman activity

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 the 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 create a recovery directory in a separate database and save a copy of the repository data.

MML (Media Management Library)

MML is a third-party tool or software that manages the tracking and management of tape read-write and file. If you want to back up to tape directly from Rman, you must configure the media management layer, with tools from the media management layer and Rman working together to complete backup and recovery.

Quick Flash Recovery Zone

The Flash recovery area is the default disk location used by the Oracle database to hold all recovery-related files. These related files include archive log, Rman Backup, control file automatic backup, reuse control file and redo log copy and flashback log file.

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. A secondary database uses Rman to create a new database from a backup of the target database. The secondary database can be created to be used as a standby database. In the event of a database disaster, 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:

Backup recovery information, which can be multiple target databases

Rman scripts, which can be stored for re-use

About backup information for data files and log files

About table space and data file information for the target database

The Rman repository can be stored in the target database control file or in the recovery directory;

Create recovery directory: used to store the Rman repository.

Sql>create tablespace yy datafile '/opt/oracle/oradata/yy.dbf ' size 20m autoextendon next 5m MaxSize Unlimited;

Create Rman users in the recovery catalog database and authorize

Sql>create user Lilei identified by Oracle default Tablespace yy quota unlimited onyy;

Sql>grant Connect,resource,recovery_catalog_owner to Lilei;

Connect to the Rman recovery catalog database

Create a Recovery Catalog

Prompt to connect to the target database, connect to the recovery catalog database

Register the database ORCL to the recovery directory, prompting the Resync to start the full recovery directory, complete the resync. At this point, you can use the recovery directory of Rman to perform backup and restore operations on the target database.

Allocation of channels

When using Rman for backup and recovery, channel allocation must be made, one channel being a connection between Rman and the target database, a channel specifying a type of device for backup and recovery, and a channel device that Rman can use to include both disk and tape.

Channel assignment can be done automatically or manually

Automatic channel Configuration

Rman>configure Device type disk paralielism 5;

Rman>configure default device type to disk;

Manual channel Configuration

Rman>run

2>{

3>allocate channel CH1 device type disk;

4>allocate channel CH2 device type disk;

5>allocate channel CH3 device type disk;

6>}

You can use the show all command to display parameters that have been configured with default values, including channel parameters

rman>showall;

Concepts and operations for backup and recovery
Backup sets and backup slices

Back up set (backup set)

A backup set is a collection of backups that contain all of the backup slices for this backup, which are stored in Oracle's proprietary format and are a collection of logical data.

Backup slices:

A backup set consists of several backup slices. Each backup slice is a separate output file, the size of a backup slice is limited, and his size cannot be greater than the maximum file length supported by the file system.

To configure the format of a backup set file:

When you use the backup command to back up, you need to explicitly format the storage path and file name of the backup file. Its path and format can be set uniformly using the format parameter. Format is composed of two parts: the storage path and the file name format;

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 determine 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, such as format '/backup/yy/%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: In the month of the year (MM)

%F: A unique name based on DBID, which is in the form of C-DBID-YYYYMMDD-QQ. where dbid for the database of DBID,YYYYMMDD for the log, 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 generated based on the number of backup sets and the creation time information.

%p: The backup piece number in 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, means the equivalent of%u%p%c.

%s: The number of the backup set

%t: Backup set timestamp

%T: Month and day format (YYYYMMDD)

Backup and Restore commands

The backup command is used to back up database files, you can back up multiple files, tablespace, entire database backup sets to disk or tape, and use the backup command outside of the Run command to automatically use automatic channels. Using the backup command within the Run command, if a manual channel is defined, the use of manually defined channels is preferred.

Databases backed up with Rman 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 synchronizes the database to a consistent state of data based on the storage information in the recovery directory.

Rman recovers a database with two commands, restore and recover. The RESTORE command restores the backup data to the specified directory, and the recover command synchronizes the database for recovery.

Restore command: Because the backup slices in the backup set are stored in a proprietary Rman format, they need to be rebuilt using the Restore command. The result of the rebuild is that the physical file structure of the target database is automatically generated.

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. If there is no archived log, or if the database is running in non-archive mode, the recovery process will error.

Example of backup and recovery in archive mode

To use Rman for online backup, the database must be in archive mode, the recovery directory must be open, the target database process must be started, the database is loaded or opened.

To see if the database is in archive mode:

Close the database and start the database to Mount state

Use the ALTER DATABASE Archivelog command to set the data to archive mode and verify that the archive mode of the database has changed

Back up and restore the entire database

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

Rman>backup database;

Back up to the flash recovery area,

If you want to include the archive log file when backing up the entire database, add the plus archivelog keyword, and delete input means that after the backup is complete, delete the Archivelog file

Rman>backup database plus archivelog Delete input;

Create Directory/opt/oracle/oradata

Automatically assign channels, format specific paths and formats with format

Rman>backup database FORMAT '/opt/oracle/oradata/backup/rmanbak/%u ';

Path to the backup file Store

To back up a database using manual channel allocation:

Rman>run

2>{

3>allocate channel CH1 device type disk;

4>backup database format '/opt/oracle/oradata/backup/rmanbak/%u ';

5>release channel CH1;

6>}

Backup results

How to recover a database

Full-Library backup recovery, database to execute in Mount state, delete system01.dbf, use Rman recovery

The database failed to start because SYSTEM01.DBF was deleted

Current state of the database, connecting Rman

Recovering a Database

Open Database

The database is already in open state

SYSTEM01.DBF table space has been restored.

Backing up and recovering table spaces
Backup table Space

Backing up the Users table space

Rman>backup tablespace users;

Backing up multiple table spaces

Rman>backup filesperset=3 tablespace Users,system,sysaux;

To back up the tablespace in the Run command

Rman>run

2>{

3>allocate channel CH1 device type disk;

4>backup format '/opt/oracle/oradata/backup/rmanbak/%u ' (tablespaceusers,system,sysaux);

5>release channel CH1;

6>}

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

Recovering a deleted table space

USERS01.DBF has been restored.

Backing up data files 2

Backup sysaux01.dbf

View data files in the current system


Delete a data file sysaux01.dbf

When restoring data files or tablespaces, the data file and tablespace must be in the offline state, the data file is lost, but the DB instance can also be used

The status of the data file sysaux01.dbf is now recover

Recovering data Files sysaux01.dbf


The data file DataFile 2 is in the online state

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

Use data pump technology for logical backups
Logical Backup Overview

A logical backup is the process of creating a logical copy of a database object and depositing it into a binary dump file. Essentially, logical backup and recovery is actually the import and export of database fact data.

Export:

The export is a logical backup of the database, essentially reading a database record and writing the recordset to a file (usually a DMP extension), where the export of these records is independent of the physical location

Import:

Import is the logical recovery of the database, essentially reading the exported binary dump file and restoring it to the database.

Import/export using Data pump technology

Data PUMP is a technology that transfers data between databases or between databases and the operating system at high speeds. The data pump tool runs on the server, and the database administrator needs to specify the database directory to hold the dumped data.

Connecting to an Oracle database

Create an Action directory

Grant user permission to manipulate the Dump_dir directory

Export data using the EXPDP command (you can export by table, export by user mode, export by table space and full-scale), import data using the IMPDP command (you can import by table, import by user mode, export by table space, and full-Library import).

Import and Export databases using command-line-based data pump technology.

Sql>col Directory_name for A20

Sql>col Director_path for A60

Sql>col OWNER for A8

Create a test user Tom and authorize

Export EMP and Dept tables under the Scott user

[Email protected]~]$ expdp scott/oracle directory=dump_dir dumpfile=scotttab.dmp tables=emp,dept

Connect to the Scott user to remove the EMP table under the Scott user

Import an EMP table

[Email protected]~]$ impdp scott/oracle directory=dump_dir dumpfile=scotttab.dmp tables=emp

The EMP table has been imported successfully.

Import the Dept table and EMP table under the exported Scott user to Tom User

[Email protected]~]$ impdp system/oracle11g Directory=dump_dir dumpfile=scotttab.dmptables=scott.emp,scott.dept Remap_schema=scott:tom

View the results of the import, using the Tom user connection

Export Table Space

Create a table AA on the XX table space and insert a record for the table

Export Table Space

Delete data files while table space xx is deleted

The AA table is gone.

Import Table Space

Before you import a tablespace, you need to create a table space of XX

Import Table Space xx

Verify that the AA table recovers back.
Export Full Library

[Email protected]]$ expdp system/oracle11g directory=dump_dir dumpfile=full.dmp full=y


Import Full Library

[Email protected]]$ impdp system/oracle11g directory=dump_dir dumpfile=full.dmp full=y

Using Flash back Technology
To enable the database to recover quickly from any logic error, Oracle has introduced flash back technology. With this technique, the data changes of row-level and thing-level can be recovered, the time of data recovery is reduced, and the operation is simple. The recovery of data can be realized by SQL statement, which greatly improves the efficiency of database recovery. Flashback technology is a major advance in the history of database recovery technology, fundamentally changing data recovery.

Flash back technology includes the following:

Flashback query: (FLASHBACK query): Data information in a table when querying a past point in time or an SCN value

Flashback version query: Queries for data changes in a table in the past time period or in a SCN segment FLASHBACK.

Flashback to a thing query (FLASHBACK Transaction query): View a thing or all the changes that have been made to the data over time.

Flashback Database (FLASHBACK): The state at which a database was restored to a previous point in time or an SCN value

Flashback Delete (FLASHBACK drop): Restores the deleted tables and their associated objects to their pre-deleted state.

Flashback tables (FLASHBACK table): Restores the table to a previous point in time or to the state of an SCN value.

The SCN is a number that accumulates incrementally as the DBMS is automatically maintained after an Oracle database is updated. The current SCN number can be obtained by querying the CURRENT_SCN in the data dictionary v$database.

What the flashback recovery zone means
Oracle recommends specifying a flashback recovery area (Flashrecovery AERA) as the default location for backup-to-recovery, so Oracle can automate disk-based backup and recovery. The Flashback recovery area is a storage space used to store recovery-related files, allowing users to centrally store all recovery-related files. The following files can be stored in the Flashback recovery area.

Control files

Archive log files

Flash back Log

Automatic backup of control files and SPFile

Rman Backup Set

Data file copy

The Flashback recovery area is set up and managed primarily 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 free space for 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 time to rollback is also dependent on the size of the flashback recovery area, because it holds the flashback log required for fallback, so this parameter should be modified with db_recovery_file_dest_size.

If you want to undo the flashback recovery area, empty the value of the initialization parameter db_recovery_file_dest.

Db_recovery_file_dest_size can only be emptied after db_recovery_file_dest is emptied.

Setting up the Flash back database
Set the flashback recovery area, to start the Flashback database function, further configuration is required, the data must be in archive mode, set the Flash back database

The database is already in archive mode:

Database is not enabled for Flash back database

Setting up the 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

Flash back to the database

The flashback database makes it possible to quickly roll back data to a previous point in time or to a SCN, which is particularly useful for recovering a database from a logical error. It is also the best choice for recovering a database when logical corruption occurs.

Use SCN to flash back to database

View database System current SCN

Changes the current state of the database, simulates creating a table TEST10, and inserts a record

Perform a flashback database recovery to restore the database to the state before the table was created, that is, the SCN is 1238544

Open the database with the Resetlogs option

Verify the status of the database and the TEST10 table does not exist.

Querying the current oldest flashback SCN and time in the database

Flash back to the database at a specified time

Querying the current time and current SCN in the database

Change the current state of the database, simulate creating table test11, and insert 1 records

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 test11 table exists.

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 function starts, you cannot use the flashback database if a data control file rebuild occurs or a backup recovery control file is used

Cannot use flashback database for data file shrink operations

You cannot use the Flashback database to restore the database to the SCN prior to the earliest SCN available in the Flashback log, because the flashback log file is deleted under certain conditions, not always in the Flashback recovery area

Flash back Table
The flashback table, which restores the table to a certain point in time or the specified SCN without recovering the data file, provides the DBA with an online, quick, and convenient way to recover from the error of modifying, deleting, inserting, and so on.

利用闪回表技术恢复表中的数据的过程，实际上是对表进行DML操作的过程。oracle自动维护与表相关联的索引、触发器、约束等。

In order to use the database Flashback feature, the following conditions must be met

The user has Falshbackany table system permissions, or Flashback object permissions with the action table

User has 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:

sql> ALTER table name ENABLE rowmovement;

Flash back table Syntax format:

FLASHBACK TABLE [schema].table to SCN | TIMESTAMP expression [enable| DISABLE TRIGGERS]

Parameter description:

SCN: The state when the table is restored to the specified SCN

TIMESTAMP: Restore a table to a specified amount of point in time

enable| Disabletriggers: In the process of recovering data in a table, the trigger on the table is disabled or activated (by default it is a reference)

To illustrate:

Log in with a Scott user

Create a table, insert a record, commit a thing

Queries the current SCN number and, if the current user does not have permission to query V$database, logs in with the SYS user, granting the current user access to the data dictionary.

Update records, and submit things

View records in a table

Delete a id=3 record

Start the row movement feature of the test01 table

Restore the test01 table to the state of 2015-11-22 05:44:48

Flash back Delete
Flashback removal can recover a table deleted using the DROP TABLE statement, which is a table recovery mechanism for accidental deletion. The implementation of the flashback removal 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 of the table and its associated objects are not immediately retracted, but instead they are renamed and placed in a logical container called the Recycle Bin until the user decides to permanently delete them or if the table space or storage space for the table is insufficient. The table is actually deleted, in order to use the flash-back deletion technology of the database, you must turn on the Recycle Bin of the database

Start the Recycle Bin to set the parameter RecycleBin to on, by default the Recycle Bin is started

If you do not have a startup you can use:

View the Recycle Bin. When a drop table is executed, the table and associated objects are named and saved in the Recycle Bin, and the deleted table and its associated objects can be obtained by querying the User_recyclebin dba_recyclebin view.

Viewing a deleted table by User_recyclebin

SYS users and system users are not supported, and after the two user tables are deleted, they cannot be retrieved from the Recycle Bin, and the query is "empty"

If a purge phrase is used when deleting a table, the table and its associated objects are directly freed, the space is recycled, and the information is not entered in the Recycle Bin

Emptying the Recycle Bin, because its associated object information is saved in the Recycle Bin at the deleted table level, its storage space is not released, so the Recycle Bin needs to be emptied periodically. or clear the Unused object (table, index, tablespace) in the Recycle Bin to free the disk space it occupies

The syntax is as follows:

PURGE [table name | Index index]

[RecycleBin | Dba_recyclebin] | [Tablespacetablespace [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

Flashback delete operation, basic syntax for flashback deletion

FLASHBACK TABLE [schema.] Table to Beforedrop [RENAME to table]

Flash back Delete

Example of flashback removal:



Note: Only locally managed tables in non-system table spaces can use the flashback delete operation.

Flash back Query
Allows data to be viewed based on point-in-time timestamp or SCN, in addition to viewing old data, and retrieving old data to undo erroneous changes when needed.

Using the Scott user login, a flashback query based on the timestamp of the EMP table

Sql> Show user;

USER is "SCOTT"

Update salary for employee number 7900, update to 2000, and submit things

Update the salary for employee number 7900, update two times, submit things.

Update salary for employee number 7900, update to 3500, and submit things

Check the post-update salary for employee number No. 7900.

What is the salary for the first one hours of the No. 7900 employees?

Query the first thing submitted, the second thing has not been submitted when 7900 employees wages

Query the second thing submitted, the third thing has not been submitted when the employee's salary of No. No. 7900

If you want, you can restore the data to a state at a certain point in the past.

If you are interested in this, please scan the QR code below for free for more details

Oracle 11g R2 Rman, data pump, flash back backup and recovery