Shulou(Shulou.com)06/03 Report--

Blogger QQ:819594300

Blog address: http://zpf666.blog.51cto.com/

Friends who have any questions can contact the blogger, the blogger will help you answer, thank you for your support!

Database backup and recovery must be mastered by database administrators. No system is protected from physical damage to the hard disk, misoperation by careless users, or potential disasters that could threaten stored data. In order to restore the database data to the maximum and ensure the safe operation of the database, the most reasonable backup method should be chosen to prevent the loss of user data caused by various faults. This paper mainly introduces three backup and recovery technologies, namely RMAN technology, data pump technology and flashback technology.

I. Overview of backup and recovery

1. Reasons why backup is needed

In real work, there are many situations that may cause data loss, and the main factors causing data loss are as follows.

Media failure: disk damage, head collision, instantaneous strong magnetic field interference

User's erroneous actions

A complete crash of the server

Computer virus

Unpredictable factors: natural disasters, power failure, theft

The above are some possible causes of failure, according to which the faults in oracle can be divided into the following four types.

1) statement failure

A logic failure that occurs during the execution of a sql statement during a statement failure. For example, invalid data that violates constraints is inserted into a table, or data cannot be inserted because there is no space in the tablespace. At this time, the application personnel or DBA will deal with it accordingly according to the situation.

2) user process failure

When the user program fails to access the oracle database, the user process failure occurs due to abnormal disconnection or termination of the process. If the network is down and the client computer is restarted, these failures will cause the connection between the user process and the server to be terminated unexpectedly.

User process failure will only prevent the current user from operating the database normally, but will not affect other user processes. When a user process fails, the process monitor (PMON) automatically performs process recovery. PMON is the background process of oracle, which is used to detect server processes that have lost connection to the user process. PMON handles the failure by rolling back the transaction and frees up resources currently consumed by the process.

3) instance failure

An instance failure occurs when the database instance of oracle cannot continue to run due to hardware or software problems. Hardware problems include an unexpected power outage, while software problems may be a server operating system crash.

When restarting the database, if an instance failure is found, oracle automatically completes the instance recovery. Instance recovery restores the database to a state 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, part of a file, or disk cannot be read or written. For example, damage to the hard disk head can lead to complete corruption of the database file. To repair database file corruption caused by media failure, you need to use media recovery.

Definition and classification of backup and recovery

1. Definition and classification of backup

Backup is the process of copying the database to the dump device. Where the dump device refers to the tape or disk used to place a copy of the database. The categories from different angles are as follows:

1) classify from a physical and logical point of view

From a physical and logical point of view, backup can be divided into physical backup and logical backup.

① physical backup:

Backup of physical files (such as data files, control files, and log files) of the database operating system. Physical backup can be divided into offline backup (cold backup) and online backup (hot backup). The former is carried out when the database is closed, and the latter backs up the database that is running as an archive log. You can use oracle's recovery Manager (RMAN) or operating system commands to make a physical backup of the database.

② logical backup:

Backup of database logical components, 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.

2) Classification from the point of view of database backup strategy

① full backup:

Make a full backup of the data each time. In the event of a disaster of data loss, a full backup can achieve 100% data recovery without relying on other 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, and the disadvantage is that the recovery needs to rely on the previous backup records, so there is a greater risk of problems.

③ differential backup:

Back up files that have been modified since the last full backup. It takes a short time to recover the database from a differential backup, so only two pieces of data are needed (the last full backup and the last differential backup). The disadvantage is that each backup takes a long time.

2. Definition and classification of recovery

Recovery is to re-establish a complete database by using the backed-up data or control files after a failure. There are two types of recovery.

1) instance recovery: when the oracle instance fails, oracle automatically restores the instance.

2) Media recovery: the recovery made when the media storing the database fails. Media recovery can be divided into complete recovery and incomplete recovery.

Full recovery: restore the database to the state when the database fails. This recovery is done by mounting the database backup and using all the redo logs.

Incomplete recovery: restore the database to the state at some point before the database failed. This recovery is done by loading the database backup and applying part of the redo log. After an incomplete recovery, the online redo log must be reset with resetlogs selection when starting the database.

Third, use RMAN tools

RMAN (recovery manager) is an important tool for oracle to back up and restore database files, archive logs, and control files. It can also be used to perform a full or incomplete database recovery. RMAN has three different user interfaces: command line, GUI (backup manager integrated in OEM), and api (used to integrate 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 and recovery

4) reduce the amount of spare parts required

5) easy to use for backup and recovery

RMAN has a rich feature set. With the release of the new version of oracle, RMAN features have been enhanced to enable RMAN to back up and restore databases in almost any case. Many of these new features are developed for problems and difficulties encountered in daily work.

1. RMAN component

1) target database (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, and spfile.

2) server session (server session)

RMAN starts the oracle server process on the database and establishes a session with the target database. The actual operation of backup, restore and recovery by the server process on the target database.

3) RMAN repository (RMAN database)

The control information used in the use of RMAN is metadata about backup, archive logs, and RMAN activities.

4) recovery catalog (recovery directory)

The recovery catalog is a schema object established on the RMAN recovery catalog database and is used to hold RMAN database data.

The recovery directory is an optional component. RMAN records the database data in the control file of the target database, but this is not secure enough, because once the control file of the target database is corrupted, it means that all RMAN backups fail. Therefore, it is recommended to set up a recovery directory in a separate data and save a copy of the database data.

For

For most medium-sized enterprise environments, RMAN backup information is stored in the recovery catalog database rather than in the control file of the target database to take advantage of the full functionality of the RMAN tool, in addition, many of the advanced features of RMAN are only supported in the environment where the recovery catalog is created.

5) MML (Media Management Library)

MML (media management layer) is a third-party tool or software used to manage tape reading, writing and file tracking. If you want to backup to tape directly through RMAN, you must configure media management, media management tools and RMAN to complete backup and recovery.

6) Flash recovery area

The flash recovery area is the default disk location where the oracle database holds all recovery-related files. These related files include archived logs, RMAN backups, automatic backup of control files, reused control files and redo log copies, and flashback log files.

7) Secondary database

When in normal use, RMAN is used with the target database and, if a recovery catalog database is created, with the recovery catalog database. In some cases, you want to create a secondary database, which 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 disaster in the product database, it can be switched to the standby database without losing any data and with the shortest downtime.

2. Create a recovery directory

The recovery directory exists in the catalog database of RMAN, which is similar to the standard database directory and mainly contains the following parts of information:

1) backup and recovery information, which can be multiple target databases

2) RMAN script, which can be stored for reuse

3) Information about data files and log files

4) Information about the tablespaces and data files of the target database

Create a recovery directory

1) create the recovery catalog database (the tablespace benet used to create the recovery catalog in the oracl database)

2) create a RMAN user and authorize it in the recovery catalog database

3) create a recovery directory in the recovery catalog database

3. Register the target database to the recovery directory

Log in to the target database, and if you want to register the target database, you can do the following.

At this point, the registration of the target database in the recovery directory is completed, and you can use the recovery directory of RMAN to back up and restore the target database.

4. Channel allocation

When using RMAN for backup and recovery operations, channels must be allocated. One channel is a connection between RMAN and the target database. The channel specifies a certain type of device for backup and recovery. The channel devices that RMAN can use include disk and tape.

1) automatic channel configuration

Use the CONFIGURE command to configure automatic channels.

2) you can also configure the channel manually

3) display channel configuration parameters

5. Related concepts and operations of backup and recovery.

1) backup set and backup film

Backup set: a backup set is a collection of the first backup, which contains all the backup slices of this backup, saved in an oracle proprietary format, and is a collection of logical data.

Backup slice: 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 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 you use the BACKUP command for backups, you need to specify the storage path and file name format of the backup files. Its path and format can be uniformly set using FORMAT parameters. The FORMAT format consists of two parts, the storage path and the file name.

If you do not specify the storage path and file name format using FORMAT, the backup sets generated by BACKUP are stored in the flash recovery area by default, and RMAN automatically uses% U to ensure that the file names are not duplicated.

The FORMAT command format is as follows:

FORMAT 'format string'

The file name part of the format string can use replacement variables, such as FORMAT'/ backup/rmanback/%U'

Common replacement variables are as follows:

% c: number of copies of backup slices

% d: database name

% D: day ordinal of the month (DD)

% M: month of the year (MM)

% F: a unique name based on DBID in the form C-|-YYYYMMDD-QQ. Where | the DBID,YYYYMMDD of the database is the date, and QQ is a sequence of 1-256.

% n: database name, fill to the right up to 8 characters

% u: an 8-character name based on the number of backup sets and creation time information.

% p: the backup slice number of the backup set, starting from 1 to the number of files created

% U: a unique file name generated by the system, which means the same as% u_%p_%c for backup slices.

% s: number of the backup set

% t: backup set timestamp

% T: format of year, month and day (YYYYMMDD)

3) backup and restore command

The ① BACKUP command is used to back up database files. Multiple files, tablespaces, and entire databases can be backed up to disk or tape in the form of backup sets. Using the BACKUP command outside the RUN command automatically uses the automatic channel; using the BACKUP command within the RUN command, if a manual channel is defined, the manual channel definition takes precedence.

Databases backed up by ② using RMAN can only be restored using the restore commands provided by RMAN. Backup information for the target database is stored in the recovery directory of RMAN. Based on the information stored in the recovery directory, RMAN automatically restores the database synchronization to a data-consistent state.

RMAN uses two commands to restore the database, RESTORE and RECOVER. The RESOTRE command restores the backup data to the specified directory, and the RECOVER command performs a synchronous restore of the database.

RESOTRE command: because the backup slice in the backup set is stored in a proprietary RMAN format, it needs to be rebuilt using the RESTORE command, and the result of the reconstruction is to automatically generate the physical file structure of the target database.

RECOVER command: responsible for using the archive log files for the reconstructed data files to complete the synchronous recovery of the database. "when executing the RECOVER command, RMAN needs to read the archive log, and if there are no archive logs, or if the database is running in non-archive mode, the recovery process will report an error."

Case: backup and restore in archived mode

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 open.

1) back up and restore the entire database

Backup and restore the entire database, automatically assign channels, FORMAT default use% U, backup set is stored in the database flash recovery zone, backup files include data files, control files, redo log files and parameter files.

Check the flash recovery area and find that the backup is successful:

If the archive log file is also included, add the plus archivelog keyword, which means to delete the archivelog (archive log) file after the backup is complete. But the fact is that we don't usually delete archive log files, so we generally don't add delete input.

One of the following methods is to create our own directory to store backup sets instead of using the flash recovery area:

Define specific paths and formats through FORMAT by automatically assigning channels

First, set up a backup directory:

Then set directory permissions and back up

Finally, check to see if there are backup files in the backup directory.

Of course, you can also manually assign channels and back up the database.

Restore the entire database (the database is to be executed in mount)

Delete system01.dbf

Open the database instance again

Connect to RMAN using sys

Open the database

You can see that the system01.dbf tablespace has been restored

2) backup and restore tablespaces

Back up the tablespace users:

Back up multiple tablespaces:

Back up tablespaces in the RUN command

If only a specific tablespace data file is lost, you can choose to restore only this tablespace instead of the entire database, and tablespace recovery can be done without shutting down the database, only by offline the tablespace that needs to be restored.

Now simulate deleting tablespaces

Restore tablespace

Check that the 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 or a file name during backup and restore)

View data files

Delete the data file user01.dbf

When restoring a data file or tablespace, the data file and tablespace must be in the offline state. The data file is lost, but the database instance can still be used.

The status of data file 4 is recover

Restore data files

Put the data file datafile 4 in the online state

Check the status of the data file. It has changed to online status.

Or use the run command to restore, as follows:

Delete user01.dbf first

Start recovery:

Check that the file has been restored:

Supplementary 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 rcvcatrman/rman@localname

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 the execution file

Generate 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 scheduling

You can use the Task scheduling Wizard in windows, or use 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

# means to back up the database at 23:00 on Sunday

0 12pr 18 * backup_archive.sh

# means backup at 12:00 or 18:00 every day

Each line of the Crontab file consists of six fields (minutes,hours,day ofmonth,month,day of week,command) separated by spaces or Tab.

Second, use data pump technology to realize logical backup.

A logical backup is the process of creating a logical copy of a database object and storing it in a binary dump file. In essence, logical backup and recovery is actually the import and export of data to the database.

Export:

An export is a logical backup of a database, essentially reading a database set and writing that dataset to a file (usually with a dmp extension), regardless of the physical location.

Import:

The essence of import and logical recovery of database is to read the exported binary dump file and restore it to the database.

1. Import / export using data pump technology

Data pump (data pump) is a technology of high-speed transmission between databases or before the database and the operating system. The data pump tool runs on the server, and the database administrator needs to specify a database directory to hold the dumped data.

The steps for exporting the data pump based on the command line are as follows

1) create a directory for data pump backup, and assign owners and groups to oracle users

2) connect to oracle database

3) create an operation directory

4) Grant the user scott permission to operate the mulu directory

5) query whether the directory has been established

6) create a test user zpf and authorize it

7) Export the table under the scott user

Description: use expdp command to export data (can export according to table, export according to user mode, export according to tablespace and full library export), use impdp command to import data (can import according to table, import according to user mode, import according to tablespace and import of full library).

8) Delete emp table

9) Import emp table

10) check that the emp table has been restored

11) Import the dept and emp tables under exported scott users into zpf users

12) use zpf user to log in to view the table

Or

13) Export scott user mode

14) Import scott user mode

15) Import all objects in scott into zpf

16) check the zpf user's table and find that all the scott's tables appear here in the user zpf.

17) Export tablespace

Export benet tablespaces (create a benet tablespace in advance)

Create a table bdqn in the tablespace benet

18) Import tablespaces

Delete tablespaces before import, and related tables will also be deleted

Before importing a tablespace, you need to create an benet tablespace

Start importing tablespaces

Check that the table has been restored.

19) Export the entire database

20) Import the entire database

III. Use flashback technology

1. Overview of flashback technology

In order to enable the database to recover quickly from any logical misoperation, oracle withdrew the flashback technology. By using this technology, the data changes at row level and transaction level can be recovered, the time of data recovery is reduced, and the operation is simple. Data recovery can be realized through sql statements, which greatly improves the efficiency of database recovery. Flashback technology is a major progress in the history of database recovery technology, which fundamentally changes data recovery.

Flashback technology includes the following:

1) flashback query (flashback query): query the data information in the table at a certain point in time or in a SCN (system change number, system change number) value table.

2) flashback version query (flashback version query): query the changes of data in the table in a certain time period or a certain SCN segment in the past.

3) flashback transaction query (flashback transaction query): view changes made to data by a transaction or all transactions over a period of time.

4) flashback database (flashback database): restore the database to its state at a point in time or an SCN value in the past.

5) flashback deletion (flashback drop): restore deleted tables and associated objects to their pre-deletion state.

6) flashback table (flashback table): restore the table to a point in time or an SCN value in the past.

Note: SCN:scn is when the Oracle database is updated, there is dbms automatic maintenance and cumulative increment of a number, you can query the data dictionary v$database in the current_scn to get the current SCN number.

2. Flashback recovery area

1) the meaning of flashback recovery area

Oracle recommends that you specify a flashback recovery area (flash recovery area) as the default location for backup and recovery related files, so that oracle can achieve automatic disk-based backup and recovery (automatic disk-based backup and recovery). So, what is the flashback recovery area? In a nutshell, the flashback recovery area is a piece of storage space used to store recovery-related files, allowing users to store all recovery-related files centrally. The following set of files can be placed in the flashback recovery area.

Control file

Archived log file

Flashback log

Control automatic backup of files and SPFILE

RMAN backup set

Data file copy

2) advantages of flashback recovery area

Flashback recovery area provides a centralized storage area, which greatly reduces administrative overhead, which is its main advantage. In addition, it can be used in combination with RMAN for fast recovery.

3) set flashback recovery area

If you use a database created by dbca, you can set the location and size of the flashback recovery area during installation.

4) the flashback recovery area is mainly set and managed by the following three initialization parameters.

Db_recovery_file_dest: specify the location of the flashback recovery area

Db_recovery_file_dest_size: specifies the amount of free space in the flashback recovery area

Db_flashback_retention_target: this parameter is used to control how long data is retained in the flashback log, or the earliest point in time to which you want the flashback database to recover. The unit is MIN, and the default is 1440min, which is one day. Of course, the time that can be returned actually depends on the size of the flashback recovery area, because the flashback log is saved in it, so this parameter should be modified in conjunction 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 be emptied only after the db_recovery_file_dest has been emptied. (that is, the location of the flashback recovery area must be deleted before deleting the space size of the flashback recovery area)

5) set flashback database

After setting up the flashable recovery area, further configuration is required to enable the flashback database feature. The database must already be in archive mode before setting up the flashback database.

The steps to set up the flashback database are as follows:

1) enable archive mode (flashback database can only be in archive mode)

2) the database does not set a flashback database

3) set up a flashback zone with a capacity of 3G (the rest are written in a fixed way, so the capacity can be changed)

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

Enable flashback log

Query whether the flashback recovery area is enabled successfully

Query whether flashback database is enabled successfully

6) cancel the flashback recovery area

7) check to see if the flashback recovery area is cancelled

3. Flashback error operation technology

1) flashback the database

Flashback to a database allows data to be rolled back quickly to a previous point in time or to a SCN, which is particularly useful for the database to recover from logical errors and is the best choice for recovering the database in the event of most logical damage.

Acts like a rewind button for a database.

It can be used if the user has corrupted the logical data.

Case: using SCN flashback database

1) query the current SCN of the database

2) change the current state of the database, simulate the creation of table bxjg, and insert a piece of data.

3) perform flashback database recovery to restore the database to the state before the table was created, that is, the SCN is 1033421

4) Open the database with the resetlogs option

5) verify the status of the database (the bxjg table should not exist)

6) the earliest scn number that can be flashback can be obtained by querying the data dictionary view v$flashback_database_log, and the value of flashback cannot be less than this value at the earliest. Similarly, if you flashback through a point in time, the flashback time cannot be less than the earliest time obtained by the query.

Set the display time format:

Query the current earliest flashback SCN and time in the database

7) flashback the database at the specified time

First query the current time of the data system

Change the current state of the database, simulate the creation of table test, and insert a piece of data

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

Open the database using resetlogs

Verify that the status test table of the database exists

2) flashback table

Flashback table is to restore the table to a certain point in time or a specified SCN without restoring data files, which provides an online, fast and convenient recovery method for DBA, which can recover incorrect operations such as modification, deletion, insertion and so on.

The process of recovering data in a table by using flashback table technology is actually a process of DML operation on the table. Oracle automatically maintains indexes, triggers, constraints, and so on, 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 flashback object permissions for the tables being manipulated.

Users have permissions on select, insert, delete, and alter objects of the table they operate.

Starting the row movement feature of the manipulated table can be done in the following ways.

Alter table table name enable row movement

Case: using SCN flashback table

1) Log in using scott user

2) change the current state of the database, simulate the creation of table it, and insert 3 pieces of data

3) query the current SCN number. If the current user does not have the permission to query v$database, log in as sys user and grant the current user the right to access the data dictionary.

4) update the data (change the name of id=1 to batianhu, delete the data of id=3) and submit

5) start the row movement feature of the it table

6) restore the it table to 2016-12-14 20:08:34

7) query

8) restore table it to a state with a scn of 1036263

3) flashback deletion

Flashback deletion restores tables deleted using the drop table statement, which is a recovery mechanism for tables that are accidentally deleted.

The flashback deletion function is mainly realized 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 is not immediately recycled. Instead, they are renamed and saved in a logical container called the Recycle Bin. The table can not really be deleted until the user decides to delete them permanently or the table space in which the table is stored is insufficient. In order to use the flashback delete technology, the Recycle Bin of the database must be opened.

1) start the Recycle Bin, set the parameter recyclebin to ON, and the Recycle Bin is started by default.

2) check the Recycle Bin. When performing a drop table operation, the table and its associated objects are named and saved in the Recycle Bin. The deleted table and its associated object information can be obtained by querying the user_recyclebin,dba_recyclebin view.

Note: sys users are not supported, and objects under system tablespaces cannot be obtained from the Recycle Bin, so when logging in with sys or system users, the query is empty.

3) if the PURGE phrase is used when deleting the table, the table and its associated objects are released directly, the space is reclaimed, and the related information is not entered into the Recycle Bin.

Create a table and delete it using the purge phrase

Check to see if it's in the recycle bin.

4) empty the Recycle Bin

Because the information of the deleted table and its associated objects is saved in the Recycle Bin, its storage space is not released, so it is necessary to regularly empty the Recycle Bin or clear the objects that are not used in the Recycle Bin (tables, indexes, tablespaces). Free up the disk space it occupies.

5) flashback delete operation

FLASHBACK TABLE [schema.] table TO BEFOREDROP [RENAME TO table]

Note: only locally managed tables in non-system tablespaces can use flashback delete operations.

Case: flashback deletion example

1) create a new table, insert data and submit

2) delete the table

3) flashback table

Or rename it after flashing the table

4) flashback query technology

Flashback query: allows you to view old data based on a point in time or SCN. In addition to being able to view old data, you can undo incorrect changes by retrieving old data when needed.

Case: flashback query instance

1) use scott user login to query the emp table based on as of timestamp flashback

2) update the salary of employee number 7844, update it to 2000, and submit things

The first transaction commits and the second thing opens automatically.

3) update the salary of employee number 7844 again, update twice, and submit things.

The second thing commits and the third transaction starts automatically.

4) update the salary of employee number 7844 again to 3500, and submit things

The third transaction commits, and the fourth transaction starts automatically.

5) inquire about the current salary of employee 7844

6) inquire about the salary of employee 7844 in the previous hour.

7) query the salary of employee 7844 when the first transaction is submitted and the second transaction is not yet submitted

(2016-12-1421 / 46 / 26)

8) if necessary, you can restore the data to the state it was at some point in the past

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.