Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "backup and recovery of Oracle database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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 faults: disk damage, head collision, instantaneous strong magnetic field interference

-- wrong actions of users

-- the complete crash of the server

-- computer virus

Unpredictable factors: natural disasters, power failure, theft

2. The faults in oracle are 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: a 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), or 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. RMAN- creates 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 a recovery catalog database

Create tablespace cc datafile'/ opt/oracle/oradata/cc.ora'

Size 20M autoextend on next 5m maxsize unlimited

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

Create user ydw identified by oracle default tablespace cc temporary tablespace temp

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.

After completing the registration of the target database in the recovery directory, you can now use RMAN's recovery directory 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) Manual channel configuration

Use ALLOCATE CHANNEL to manually assign channels in the RUN block.

For example: specify three disk channels, which are ch2, ch3, and ch4.

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 slices in the backup set are stored in a proprietary RMAN format and need to be rebuilt using the RESTORE command, 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 rebuilt 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

Check that the current data mode is non-archive mode

Modify the mode to archive mode

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 you also include archived log files, add the plus archivelog keyword, which means to delete the archivelog file after the backup is complete.

Define specific paths and formats through FORMAT by automatically assigning channels

First, set up a backup directory:

Set directory permissions and back up

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

The database cannot be started at this time because system01.dbf has been deleted

Connect to RMAN using sys

Restore synchronously.

Open the database

The system01.dbf tablespace 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 rcvcat rman/rman@localname

Run {

Allocate channel c1 type disk

Allocate channel c2 type disk

Allocate channel c3 type disk

Backup fileaperset 3 format'/ u01Accord oradata Universe backupchar% 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 minutes,hours,day of month,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

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.

SCN:scn is an incremental number that is automatically maintained by dbms when the Oracle database is updated. The current SCN number can be obtained by querying the CURRENT_SCN in the data dictionary V$DATABASE.

Note:

The flashback database is based on the flashback recovery area (Flash Recovery Area) feature. If you want to use the flashback database technology, you need to enable the flashback recovery area.

Flashback deletion is based on the Recycle Bin (Recycle Bin) feature. If you want to use flashback deletion technology, you also need to enable the Recycle Bin

Flashback queries, flashback version queries, flashback transaction queries and flashback tables are mainly based on rollback information in undo tablespaces.

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.

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:

Enable archive mode (flashback database can only be in archive mode)

Database does not set flashback database

Establish a flashback zone

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

You can deactivate the flashback recovery area by setting the db_recovery_file_dest parameter to null, but you cannot cancel the flashback recovery area if you have enabled the flashback database, so you must disable the flashback database before you can cancel the flashback recovery area.

The steps are as follows:

Shut down the database

Shutdown immediate

Load the database

Startup mount

Turn off database flashback

Alter database flashback off

Open the database

Alter database open

Modify flashback parameters

Alter system set_recovery_file_dest=''

4. 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 especially useful for databases to recover from logical errors and is the best choice for recovering databases in the event of most logical damage.

 acts like a rewind button for a database.

 can be used when the user has corrupted the logical data.

The basic syntax of 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 a state before a specified point in time

Restrictions on flashback database operations:

Media failures such as corrupted or missing  data files cannot be recovered using a flashback database. The flashback database can only be based on currently running data files.

After the  flashback database feature is started, if database control files are rebuilt or backup is used to restore control files, the flashback database cannot be used.

 cannot use a flashback database for data file shrink operations.

 cannot use the flashback database to restore the database to the SCN that preceded the earliest SCN available in the flashback log, because the flashback log file is deleted under certain conditions and is not always saved in the flashback recovery area.

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 1915426

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 test2, 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.

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

 starts the row movement feature of the manipulated table in the following ways.

Alter table table name enable row movement

The basic syntax for flashback table operations 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 the table to a specified point in time

ENABLE | DISABLE TRIGGER: whether triggers on the table are activated or disabled during the recovery of data in the table (disabled by default)

Note: SYS users or users logged in as AS SYSDBA cannot perform flashback table operations

Case: using SCN flashback table

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

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.

The syntax is as follows:

PURGE [TABLE 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: empties the user's Recycle Bin and reclaims disk space for all objects

DBA_RECYCLEBIN: clear the Recycle Bin for the entire database system. Only users with SYSDBA permission can use it.

TABLESPACE: clear the specified table space in the Recycle Bin and reclaim disk space

USER: clears objects for specific users in the specified tablespace in the Recycle Bin and reclaims disk space

5) flashback delete operation

FLASHBACK TABLE [schema.] table TO BEFORE DROP [RENAME TO table]

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

Case: flashback deletion example

Create a new table, insert data and submit

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

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 flashback query

As of scn: flashback query based on scn

Case: flashback query instance

This is the end of "backup and recovery of Oracle Database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.