Concepts and usage of Oracle database backup strategy

This forum article mainly introduces the Oracle database backup strategy related concepts and the use of attention, detailed content please refer to the following

First, understand the importance of backup

It can be said that from the day the computer system was born, there is the concept of backup, the computer with its powerful speed to deal with the ability to replace a lot of man-made work, but, often many times, it is so fragile, the motherboard on the chip, motherboard circuit, memory, power, and so any one of the normal work, Will cause the computer system to not work properly. Of course, these corruptions can be repaired without causing application and data corruption. However, if your computer's hard disk is damaged, it will cause data loss, and you must restore the data with backup.

In fact, in our real world, there are many backup strategies, such as RAID technology, dual-machine hot standby, cluster technology is not the development of computer systems backup and high availability? There are many times, the system backup can indeed solve the problem of database backup, such as disk media damage, often from the mirror to do a simple recovery, or a simple switch machine on it.

However, the above mentioned system backup strategy is to consider the problem of backup and recovery from the hardware point of view, this is a cost. The backup strategy we can choose is based on the cost of losing the data and the cost of ensuring that the data is not lost. In some cases, the hardware backup sometimes can not meet the actual needs, if you mistakenly deleted a table, but you want to restore the time, the database backup becomes important. Oracle itself provides a powerful backup and recovery strategy, where we discuss only Oracle backup strategies, and the following backups refer to Oracle database backups, and the recovery will be placed in the next lecture.

Backup is the process of copying a database to a dump device. Where a dump device refers to a tape or disk that is used to place a copy of a database.

What kind of recovery depends on what kind of backup is available. As a DBA, it is the responsibility to maintain the recoverability of the database from the following three aspects:

• Minimize the number of database failures to maintain maximum availability of the database;

• When the database is inevitably invalidated, the recovery time should be minimized so that the efficiency of recovery can be highest;

• When a database fails, make sure that as little data is lost or not lost, so that the data is most recoverable.

The most important task of disaster recovery is to design a sufficient frequency hard disk backup process. The backup process should meet the recoverability of the system requirements. For example, if a database can have a long shutdown time, you can make a cold backup every week and archive the redo logs, and perhaps we can only consider hot backups for 24*7 systems. It would be ideal if you could back up every day, but consider the reality. Enterprises are looking for ways to reduce maintenance costs, the real solution can be adopted. As long as you plan carefully and try to reach the bottom line of database usability, it is possible to spend a small amount of money on successful backup and recovery.

Ii. Understanding Oracle's operating mode

There are two ways to run an Oracle database: One is the Archiving method (Archivelog), and the purpose of archiving is to restore the database to the maximum when the database fails, to ensure that no data has been submitted, and not to file the way (Noarchivelog), Only the database can be recovered to the nearest recycle point (cold or logical). Depending on the high availability of the database and the amount of work that the user can afford to lose, it is strongly required for the production database to be archived; databases that are being developed and debugged can be used in a way that is not archived.

How to change the way the database runs, when you create a database, as part of creating a database, determines how the database is initially archived. In general, it is Noarchivelog way. When the database is created, we need to change the database that needs to be run in the way of archiving to Archivelog.

1, the change does not file the way for the filing way

A. Close the database, back up the existing data, change the operation of the database is important to the database changes, so to do a backup of the database, to protect the problems that may arise.

B. Modifying the initial parameters so that they can be archived automatically

Modify (Add) initialization file Init[sid].ora parameters:

Log_archive_start=true #启动自动归档

Log_archive_format=arc%t%s.arc #归档文件格式

Log_archive_dest=/arch12/arch #归档路径

In 8i, you can have up to five archive paths and can be archived to other servers, such as standby databases (standby database) servers

C. Start instance to mount state, that is, load the database without opening the database:

$ "SVRMGRL

SVRMGRL "Connect Internal

SVRMGRL "Startup Mount"

D. Issuing a Change Order

SVRMGRL "ALTER DATABASE archivelog;

SVRMGRL "ALTER DATABASE open;

2, change the status of the archive is not archived state

This is the same as the previous step, but some operations are not the same, mainly in the above B operation, now to delete or annotate the parameter, in D operation, the command is

SVRMGRL "ALTER DATABASE noarchivelog;

Note that you must do a full cold backup of the database to prevent accidental occurrences when converting from an archive to a non-archived way.

III. Classification of Oracle Backups

The simple way of backup, can be divided into logical backup, cold backup (offline backup), hot backup (online backup), in fact, cold backup and hot backup can also be called physical backup

According to the Backup tool, it can be divided into Exp/imp backup, OS copy, RMAN, third party tools, such as Veritas

Here are a few ways to illustrate the various types of backup

1, Exp/imp logical backup

Import/Export is the oldest surviving two command-line tool for Oracle, in fact, I never think Exp/imp is a good backup way, the correct argument is that Exp/imp can only be a good dump tool, especially in small database dumps, table space migration, table extraction, There is no small credit for detecting logical and physical conflicts. Of course, we can also use it as a logical backup after the physical backups of small databases, which is also a good suggestion.

For increasingly large databases, especially TB-level databases and the emergence of more and more data warehouses, Exp/imp is becoming more and more powerless, at this time, the database backup to the Rman and Third-party tools. Below we still briefly introduce the use of exp/imp.

I, the use of methods

EXP Parameter_name=value

Or Exp parameter_name= (Value1,value2 .....) ）

You can see all the help as soon as you enter the parameters help=y

Such as:

C:\ "Set nls_lang=simplified CHINESE_CHINA.ZHS16GBK

C:\ "Exp-help

Export:release 8.1.6.0.0-production on Thursday April 10 19:09:21 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

By entering the EXP command and user name/password, you can

Commands after user/password:

Example: EXP Scott/tiger

Alternatively, you can control the export by entering the EXP command with various parameters

Operation mode. To specify a parameter, you can use the keyword:

Format: EXP keyword=value or keyword= (value1,value2, ..., Valuen)

Example: EXP scott/tiger grants=y tables= (emp,dept,mgr)

or tables= (T1:P1,T1:P2), if T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)

--------------------------------------------------------------------------

USERID username/password Full export entire file (N)

Buffer Data buffer size owner owner user Name list

File output file (expdat.dmp) table List of tables

COMPRESS Import a range (Y) RecordLength IO record length

Grants Export Permission (Y) Inctype Incremental Export type

INDEXES Export Index (Y) record tracking incremental export (Y)

Rows Export data row (Y) parfile parameter file name

CONSTRAINTS Export Restriction (Y) consistent cross-table consistency

Log screen output logs file STATISTICS Analysis Object (estimate)

Direct directly path (N) triggers export trigger (Y)

FEEDBACK shows the progress of each x row (0)

FILESIZE the maximum size of each dump file

QUERY Select a clause that exports a subset of the table

The following keywords are only available for use in a table space that can be transferred

Transport_tablespace export of removable tablespace metadata (N)

tablespaces List of table spaces to be transferred

Successfully terminates the export without warning.

C:\ "

Help has explained in detail the meaning and use of the parameters, and a few simple examples, note that, starting from 8i, has begun to support the data subset of methods, that is, you can specify their where conditions, you can export one or more rows of data from a table.

Note the above set nls_lang=simplified CHINESE_CHINA.ZHS16GBK, by setting the environment variable, you can let exp help appear in Chinese if set Nls_lang=american_ America. Character set, then your help is in English.

Incremental and cumulative exports must be valid throughout the library, and, in most cases, incremental and cumulative exports are not as effective as they might seem. Oracle starts at 9i and no longer supports incremental export and cumulative exports.

II, table space transmission

Table Space Transfer is a new method of 8i to rapidly move data between databases, it is useful to attach a format data file from one database to another, rather than exporting the data to a DMP file, which can be useful in some cases, because moving data in a table space is as fast as copying a file.

There are some rules about the transport table space, namely:

• The source and target databases must be running on the same platform.

• The source database and the target database must use the same character set.

• The source and target databases must have the same size blocks of data (9i is no longer needed)

• The target database cannot have a table space with the same name as the migrated table space

· SYS object cannot be migrated

• A self-contained set of objects must be transferred

• Some objects, such as materialized views, function based indexes, etc. cannot be transmitted

You can use the following methods to detect whether a table space or a set of table spaces meets the transport criteria:

exec sys.dbms_tts.transport_set_check (' Tablespace_name ', true);

SELECT * from Sys.transport_set_violation;

If there is no row selection, the table space contains only the table data and is self-contained. For some self-contained table spaces, such as datasheet space and Index table space, you can transfer together.

The following is a brief use step, and you can refer to the Oracle online Help if you want to refer to the detailed use method.

A. Set table space to read-only (assuming tablespace names are App_Data and App_index)

Alter Tablespace App_Data Read only;

Alter Tablespace App_index Read only;

B. Issue exp Command

SQL host exp userid= "" "Sys/password as Sysdba" ""

Transport_tablespace=y tablespace= (App_Data, App_index)

The above need to note is

• In order to execute Exp,userid in SQL you must use three quotes, and in Unix you must also be careful to avoid the use of "/"

• After 816 and later, you must use SYSDBA to operate

• This command must be placed on one line in SQL (this is because the display problem is placed in two rows)

C. Copy data files to another location, i.e. target database

can be CP (Unix) or copy (Windows) or transfer files via FTP (be sure to be in bin mode)

D. Setting the local table space to read and write

E. Attaching the data file to the target database

Imp file=expdat.dmp userid= "" "Sys/password as Sysdba" ""

Transport_tablespace=y

"Datafile= (C:\temp\app_data,c:\temp\app_index)"

F. Setting the Target database table space for read-write

Alter Tablespace App_Data read write;

Alter Tablespace App_index Read write;

III, export/import and character set

Understanding Oracle's multi-language settings, Oracle Multi-Language settings are designed to support worldwide language and character sets, and are generally effective for language hints, currency forms, sorting methods, and the display of data in char,varchar2,clob,long fields. The main two features of Oracle's multinational language settings are national language settings and character set settings, and national language settings determine the type of language that the interface or hint uses, and the character set determines the encoding rules for the database to hold data about the character set (such as text). As a small example above, the difference in the Nls_lang of the environment variable, which causes the EXP help to change, is the function of the multinational language setting (Nls_lang contains the national language settings and character set settings, where the national language settings, rather than the character set, function).

Oracle Character set set, divided into database character set and client character set environment settings. At the database end, the character set is set when the database is created and stored in the Database props$ table, and for products above 8i, the "Alter DB character Set character set" can be used to modify the character sets of the database, but only from subsets to superset. Do not modify the character set through the update props$, if it is an unsupported conversion, you may lose all data related to the character set, which is a supported conversion, or it may cause the database to work abnormally. The character set is divided into Single-byte character sets and multibyte character sets, and Us7ascii is a typical single-byte character set, which is LENGTH=LENGTHB in this character set, and ZHS16GBK is the commonly used double-byte character set, Lengthb=2*length here.

In the client's character set environment is relatively simple, mainly environmental variables or registry key Nls_lang, note that Nls_lang priority is: parameter file à registry à environment variable àalter session. The composition of the Nls_lang is the national language setting. Character set ", such as Nls_lang=simplified CHINESE_CHINA.ZHS16GBK. The client's character set is best the same as the database side (the national language settings can be different, such as the ZHS16GBK character set, the client can be Nls_lang =simplified CHINESE_CHINA.ZHS16GBK or Ameircan_ AMERICA.ZHS16GBK, does not affect the normal display of database characters), if the character set is not the same, and the conversion of the character set is not compatible, then the client's data display and export/import of the character set-related data will be garbled.

With a little bit of finesse, you can make export/import transform data on databases in different character sets. This requires a 2-in-file editing tool, such as Uedit32. Open the exported DMP file in edit mode, get 2, 3 bytes of content, such as 00 01, first convert it to 10, 1, use function nls_charset_name to get the character set:

SQL Select Nls_charset_name (1) from dual;

Nls_charset_name (1)

-------------------

Us7ascii

You can know that the character set of the DMP file is Us7ascii, and if you need to replace the character set of the DMP file with ZHS16GBK, you need to obtain the number of the character set with nls_charset_id:

SQL Select nls_charset_id (' ZHS16GBK ') from dual;

nls_charset_id (' ZHS16GBK ')

--------------------------

852

Convert 852 to 16, 354, 2, 3 bytes 00 01 to 03 54, complete the conversion of the DMP file character set from Us7ascii to ZHS16GBK, and then import the DMP file into the ZHS16GBK character set database. (Note that the conversion between decimal numbers and hexadecimal, to understand the truth)

Iv, cross-version use of Exp/imp

Exp/imp many times, you can use it across versions, such as exporting import data between version 7 and version 8, but you must select the correct version, and the rule is:

• Always use the version of IMP to match the version of the database, and if you want to import to 816, use the 816 Import tool.

• Always use the version of exp to match the lower version of the two databases, such as between 815 and 816, then use the EXP tool 815.

2, OS Backup

There are two types of operating system backups, cold standby (cold backup) and hot backup, and operating system backups are essentially different from the logical backups above. A logical backup extracts the data content of the database without backing up the physical data block. The operating system backup is a copy of the entire data file.

I, cold backup

The database must be completely shut down before the file-level backup starts. The close operation must be performed with a shutdown with the normal, immediate, transaction options.

Each file that is used by the database is backed up, and these files include:

☆ All data files

☆ All Control Documents

☆ All online Redo LOG files

☆init.ora file (optional)

General steps for cold backup are:

A. Normal shutdown of the instance to be backed up (instance);

B. Back up the entire database to a directory

C. Start the database

Such as

SVRMGRL "Connect Internal

SVRMGRL "Shutdown immediate

SVRMGRL "! Cp

Or

SVRMGRL "! Tar cvf/dev/rmt/0/u01/oradata/prod

SVRMGRL "Startup

Note: If you use a script to make a cold backup of the database, you must logically check the command to shut down the database, and if the command to shut down the database does not perform properly and the database does not shut down properly, then all cold backups will be invalidated.

II, hot Backup

Hot backups are OS-level data backups that are available when the database is open and valid for the user. Hot backups can only be used in Archivelog-mode databases. Before a data file is backed up, the corresponding tablespace must be used by using alter tablespace ... BEGIN Backup to be placed in a backup manner. The data files that make up the table space can then be copied using an operating system command similar to a cold backup. After the data file is copied using the operating system command, alter TABLESPACE should be used ... The end Backup command causes the tablespace to be detached from the hot backup method.

Hot backup is not necessary to back up the online logs, but it must be an archive state, and you may need to use an archive log when the instance is recovered. The current online log must be protected or in a mirror state, the current online log corruption, damage to the database is huge, only the loss of data for the recovery of the database work.

For temporary tablespace, the temporary information is stored, in hot backup You can also consider not to backup, if the temporary file failure, you can delete the data file and table space, rebuild a temporary table space.

The advantages of hot backup are obvious

----A. Can be backed up at the table space or data file level for short backup time.

----B. The database is still available for backup.

----C. Can reach a second level recovery (revert to a point in time).

----d. Almost all database entities can be recovered.

----E. Recovery is quick and, in most cases, restored when the database is still working.

The general steps for hot backup of the operating system are:

① Connection Database

SVRMGRL "Connect internal;

② the table space (such as user) that needs to be backed up as a backup method

SVRMGRL "Alter tablespace User begin backup;

③ Copy data file

SVRMGRL "! Cp/u01/oradata/prod/user01.ora/backup/prod/user01.ora

Or

$CP Cp/u01/oradata/prod/user01.ora/backup/prod/user01.ora

④ after the data file copy completes, the table space drags the body to back up the way

SVRMGRL "Alter tablespace User end backup;

⑤ Repeat for all table spaces that need to be backed up 2,3,4

⑥ uses the following command to back up the control file alter DATABSE ... BACKUP Controlfile

such as backing up binary files

ALTER DATABASE backup Controlfile to ' new Fielname ';

Back up as a text file

ALTER DATABASE backup Controlfile to trace;

Because of the hot backup, the user is still operating the database, so it is best that each table space in the backup state of the shortest time, so that a table space to a table space backup, do not make the table space in the backup state while copying the data files.