Oracle Database Backup & Recovery (Reprint forum User Articles)
When we use a database, always hope that the content of the database is reliable, correct, but due to the fault of the computer system (including machine fault, media failure, misoperation, etc.), the database may sometimes be destroyed, how to make it as soon as possible Recovery data becomes a top priority. If you make a backup of the database, you will be easy to recover data at this time. It can be seen that how important the backup of the database is, and the author takes Oracle7 as an example to tell the database backup and recovery. The Oracle database has three standard backup methods that are export / import (export / import), cold backup, hot backup. Export backup is a logical backup, a cold backup and a hot backup is a physical backup.
First, export / import (export / import)
Using EXPORT to extract data from the database, you can send the extracted data back into the Oracle database using Import.
1. Simple export data (Export) and import data (import)
Oracle supports three types of output:
(1) Table mode (T method) export the data of the specified table.
(2) User mode (U mode), export all objects and data of the specified user.
(3) The full warehouse (FULL mode), export all objects in the database.
The process of data derivation is the inverse process of the data import (export), and their data flows are different.
2. Incremental Export / Import
Incremental export is a commonly used data backup method, which can only be implemented throughout the database and must be used as system. When performing such export, the system does not request an answer any questions. Export file name defaults to export.dmp, if you do not want your own output file to be named Export.dmp, you must point out the file name you want to use in the command line.
Incremental export includes three types:
(1) "Complete" incremental export (Complete)
That is, back up the entire database, such as:
$ Exp system / manager incy = complete file = 990702.dmp
(2) "incremental" incremental export
Back up the data changed after the last backup. such as:
$ Exp system / manager incy = incremental file = 990702.dmp
(3) "Cumulative" incremental export (cumulative)
The cumulative export method is just the information that changes in the database after the last "complete" export. such as:
$ Exp system / manager incy = cumulative file = 990702.dmp
Database administrators can schedule a backup schedule that is completely efficient and efficiently completed by three different ways of data.
For example, the backup task of the database can be arranged as follows:
Monday: Export (a)
Tuesday: Increment Export (B)
Wednesday: Increment Export (C)
Thursday: Incremental Export (D)
Friday: Accumulated Export (E)
Saturday: Increment Export (F) Sunday: Increment Export (G)
If on Sunday, the database is accidentally destroyed, and the database administrator can restore the database as follows:
The first step: regenerate the database structure with the command Create Database;
Step 2: Create a sufficiently big additional reflection.
Step 3: Full incremental import A:
$ IMP System./Manager Inctype = Rectore Full = Y File = a
Step 4: Accumulated incremental import E:
$ IMP System / Manager Inctype = Rector Full = Y File = E
Step 5: Recent incremental import f:
$ IMP System / Manager InctYpe = Restore Full = Y File = f
Second, cold backup
The cold backup occurs in the case where the database has been properly closed, and it will be given to us a complete database when properly turned off. Cold backup is a statement that copies a key file to an additional location. For backup Oracle information, cold backup is the fastest and safest way. The advantage of the cold backup is:
1. Is a very fast backup method (only copy file)
2. Easy to archive (simple copy)
3. Easy to recover to a point in time (just copy the file back)
4. Can be combined with the archive method to make the database "Latest Status" recovery.
5. Low level maintenance, high security.
But the cold backup also has the following deficiencies:
1. You can only provide recovery to "a certain point in time" alone.
2. In implementing the full process of the backup, the database must be a backup without making it. That is, during the cold backup process, the database must be closed.
3. If the disk space is limited, only the other external storage devices such as tape can be copied, and the speed will be very slow.
4. Can't be restored in form or by user.
If possible (mainly efficient), the information should be backed up to the disk, then start the database (user can work) and copy the backed up to the tape (copy, the database can also work). Documents that must be copied in the cold backup include:
1. All data files
2. All control files
3. All online redo log files
4. INIT.ORA file (optional).
It is worth noting that the cold backup must be performed in the case where the database is closed. When the database is open, the execution database file system backup is invalid.
Here is a complete example of making cold backup:
(1) Turn off the database $ sqldba lmode = y
Sqldba> Connect Internal;
Sqldba> shutdown normal;
(2) Back up all of the time files, redo log files, control files, and initialize parameter files
SQLDBA>! CP
(3) Restart Oracle Database
$ SQLDBA LMODE = Y
Sqldba> Connect Internal;
Sqldba> Startup;
Third, hot spare
Thermal backup is a method of backing up data using ArchiveLog MODE mode in the case of a database operation. So, if you have a cold backup yesterday and today's hot backup documents, you can use this information to restore more information when there is a problem. The hot backup requires the database to operate in the ArchiveLog mode and require a lot of file space. Once the database is running in ArchiveLog, you can make a backup. The command file of the hot spare is consisting of three parts: 1. Data file A tablespace backup backup.
(1) Set the table space to back up status
(2) Data file of backup table space
(3) Recovery table space is normal
2. Backup archive log file.
(1) Temporary stop archiving process
(2) Log under the file in the Archive Redo Log Target Directory
(3) Restart the Archive process
(4) Backup archived redo log file
3. Use the alter database backup controlfile command to back up the copy file
The advantage of hot backup is:
1. The backup time is short in the table space or data file level backup.
2. The database can still be used when backed up.
3. Can reach second level recovery (restored to a point in time).
4. Almost all database entities can be recovered.
5. Recovery is fast, and in most cases, it is restored when the database is still working.
The lack of hot spare is:
1. Can't go wrong, otherwise the consequences are serious.
2. If the hot backup is unsuccessful, the result is not available for time point recovery.
3. Because it is difficult to maintain, it is necessary to carefully carefully, and "endlessly end" is allowed. "
Oracle Database Backup
First, the meaning: When we use a database, the content of the database will always be reliable, correct, but due to the fault of the computer system (hardware failure, network fault, process failure and system failure) affects the operation of the database system, affecting the database The correctness of data, even damages the database, making it lost all or part of the data in the database. Therefore, after the above fault occurs, it is desirable to re-establish a complete database, which is called database recovery. The recovery subsystem is an important part of the database management system. Recovery processing changes with the structure of the fault type that occurs. Second, the backup method Oracle database has three standard backups. Export / Import (Export / Import), cold backup, thermal spare. Export backup is a logical backup, including reading a series of database logs and writes in a file, and the reads of these logs are independent of their location. Cold backup and thermal backups are physical backups (also known as low-level backups), which involves files that make up the database, but does not consider logical content. Below we will introduce these three backup methods, and its characteristics: (1), export / import (export / import) export / import / import is used to remove data from Oracle to the database. 1. EXPORT backs up the database into a binary system file called "Export Dump File" and regenerates the database write into the file. It has three modes: a. User mode: Export all objects of the user and the data in the object; b. Table mode: Export all the tables or specified tables; c. Overall database: Export all objects in the database. It has three types: a. Complete export: Back up the entire database; b. Accounting (COMULATIVE ERPORT): Back up data changed on the previous accumulated backup; increamental export: Back up the data changed after the last backup. Export's commands can be performed interactively, and the specific meaning of each parameter is shown in the appendix. When conducting a backup, we must consider a problem, that is, the consistency of the export, exporting a table during the export process, thus starting at a feature, but different tables The time is different, so when the export begins to read a table, the data in the table is the exported data, so that most tables are in the export process, when most tables are connected by internal keywords and external keywords, these related terms The change will lead to inconsistencies of the export. To avoid this, we can arrange the export process to do when it is not operated. In addition, when the RESTRICTEP CONSISTENT option is in Oracle, you can guarantee that the data consistency is exported. 2, import import: The imported process is the exported reverse process, this command reads the exported dump binary file and runs files, restores object users, and data. The method of implementing the import command is related to the Export solution. 1. If the full-time scheme implemented in Export, all data objects, including tablespaces, data files, users when IMPORT, but consider the physical layout in the database, pre-create table space It is useful to users. 2. If Export uses the Incremental / Cumulative mode, you need to set a table space, user, and data files in advance. (2): Cold backup cold backups occur in situations where the database has been properly closed, and will provide us a complete database when properly closed.
Each file used by the database is backed up, including: ☆ All data files ☆ All control files ☆ All online redo log files ☆ Init.ora file (optional) is worth noting that the cold backup must be a database shutdown Under completion, when the database is open, the execution database file system backup is invalid. Cooling backup The general step is: 1: Instance to back up, properly closed; 2: Back up the entire database to a directory 3: Start Database 1: Sqldba> Connect Internal Sqldba> ShutDown Normal 2: SqlDBA>! CP
It provides all copies related to the database.% Dbshut% cp /oracle/oradata/oracle7/system.dbf /oracle/oradata/roracle7/RBS.DBF / ORACLE/Ordata/Oracle7/temp.dbf / oracle / oracle / ORADATA / ORACLE7 /Tools.dbf /oracle/oradata/racle7/Users.dbf /orecle/app/racle/Product/7.3.2/dbs/application.dbf /oracle/app/racle/Product/7.3.2/dbs/apptmpsp.dbf / Oracle/app/oracle/product/7.3.2/dbs/apprlbksp.dbf /oracle/oradata/oracle7/redooracle7/control1.ctata/undata/control1.ctl /oracle/roadata/oracle7/control2.ctl / oracle / OraData/oracle7/control3.cta
6.3, once a day partial online with archiving backups #su -. Oracle% svrmgrl% svrmgrl> connect internal% svrmgrl> alter tablespace billsp begin backup% svrmgrl> alter tablespace billtmpsp begin backup% svrmgrl> alter tablespace billrlbksp begin backup% svrmgrl> alter tablespace system begin backup% svrmgrl> alter tablespace rbs begin backup% svrmgrl> alter tablespace temp begin backup% svrmgrl> alter tablespace tools begin backup% svrmgrl> alter tablespace users begin backup% svrmgrl> host cp all datafile to the backup storage. # file Name in the first step in.% SVRMGRL> ALTER TABLESPACE BILLSP END Backup% SVRMGRL> ALTER TABLESPACE BILLTMPSP END Backup% SVRMGRL> ALTER TABLESPA ce billrlbksp end backup% svrmgrl> alter tablespace system end backup% svrmgrl> alter tablespace rbs end backup% svrmgrl> alter tablespace temp end backup% svrmgrl> alter tablespace tools end backup% svrmgrl> alter tablespace users end backup make a sql file, as described above Written. The Billing System Can Recover To The Point of Failure When The Failure Occur.
7. Recovery of backup files: 7.1 Recovery file: The following is the recovery of the "/ etc / passwd" file (, put the correct tape (, run the following command to restore "/ etc / passwd" file # rsh www MT -F / DEV / RMT / 0 REW (find the nearest tape, bring forward to the nearest "/ etc / passwd", such as the "/ etc / passwd" file on Wednesday, run the following command) #RSH WWW MT -F / DEV / RMT / 0N FSF 2 # UFSRESTORE IF WWW: / DEV / RMT / 0N UFSRESTORE> LS.: etc / ufsrestore> CD etc UFSRESTORE> LS./etc: passwd ufsrestore> add passwd warnign: ./ etc: file exists ufsrestore> verbose verbose mode on ufsrestore> extract Extract requested files you have not read any volumes yet Unless you know which volume your file (s) are on you should start witch the last volume and work to wards the first Specify next volume #:.. 1 Extract file ./etc/passwd add links set directory mode, oER, AND TIMES. SET OWNER / MODE for '.'? (yn) n Directories Already EXIT, SET MODE Anyway? (YN) Y Ufsrestore> QUIT hot backup can be The database is opened in the case, generally starting backup by changing the status of the table space using the alter command, the backup is completed, otherwise the REDO log will be wrong, causing the database to be affected next time the database is started. The table space is fully covered. The hot spare requirements are: 1. The hot backup work must require the database in the ArchiveLog mode, with the ALTER DATABASE archivelog | noarchiveLog command to change the mode of the backup in the SQLDBA state. 2. The hot backup can only be carried out without using the database without using or low usage. 3. The hot backup requires a lot of file space. In general, Oracle writes the online redo log file in a loop, when filling the first redo log file, until the last one, the last one is filled, the background process LGWR covers the first, In the ArchiveLog mode, the background process Arch gives it a copy before each Redo log file is overwritten, generally, the Redo log file of these documents is written into the disk or tape. If the disk space is enough, it is recommended to use a disk, which greatly reduces the time required to complete the backup.
Before making a hot backup, to config.ora file log_archive_start set to true will log_archive_dest predetermined position archive redo log files: log_archive_dest = / wwwdg / oracle / arch / arch log_archive_start = true once the database is running in archivelog mode, you You can make a backup. The command file of the hot spare is made of three parts: 1. Data file a table space a spatial backup: a. Set table space is a backup state. B. Data file of the backup table space. C. The recovery table space is normal. 2, backup archiving should be log files: a. Temporary stop archive process B. Log under the file c in the Archive Redo Log target directory c. Restart the Archive process d. Backup archived Redo Log file 3, with Alter Database Backup ControlFile Command to back up the copy file before the hot backup runs the log_archive_dest directory may be full, then the directory is to be backed up, then delete the file, release space, this process has the following steps: 1, temporarily stop Archive process. 2. Record the files in the log_archive_dest directory. 3, restart the Archive process. 4, back up the Archive Redo log file. 5, delete files in the directory. sqldba lmode = y << EOF arch1archive log stop; exitEOF arch1FILES = 'ls /wwwdg/oracle/arch/arch*.dbf';sqldba lmode = y << EOF arch2connect internalarchive log start; exitEOF arch2tar -rvf / dev / rmt / 0 FILESRM -F $ FILES logic backup combined with physical backups. In the previous introduction, the method of the three backups. When the database is backed up, the characteristics of the three backups of the data should be flexible, and we first compare the characteristics of the three methods.
Method Type Recovery Features EXPORT Export Logic You can restore any database objects to the state hutdown when it is exported? The nearest Copyes of DataFile is copied to the $ Oracle_Home / DBS directory. When too many archive log files; automatically recover data to the point of failure; svrmgrl> alter database open:??;% Svrmgrl svrmgrl> connect internal ... svrmgrl> startup mount pfile = filename exclusive svrmgrl> alter database recover Notes??? After doing some backup, you can delete the log *****. Arc before Current Log Sequence. #Su - Oracle% SVRMGRL SVRMGRL> Connect Internal; SVRMGRL> Archive log list; # This is marked with current log sequence. Assume 360. SVRMGRL> EXIT $ CD /BILLDG/Oracle/app/oracle/Product/7.3.2/dbs/arch $ rm ......... # will log360.arc, log359.arc, log358.arc .............delete. Cold backup physical type can restore the database to its closed state hot backup physical type to restore the database to any point in time. When the database is time, the processing business is hit, the cold backup export is OK. If you only export one or two tables, it is enough. Generally speaking, the backup ultimate execution always includes logic and physics, exporting confirmation database correctness in logic and physically backup ensuring physical. In addition, some storage media is required when making backups. For these media, it should be managed to be managed. According to the backup content, the media is unified, so as to make the media when backup and recovery, resulting in the original backup loss. 2: Media should be stored in different locations with computer equipment, and the backup is damaged with computer equipment when there is an unexpected machine room.
Appendix: TAR Option [Arguments] FileName ... Option: C: Create a new TARFILE T by the command line: List the content in Tarfile x: Open TARFILE; F: Use the subsequent filename or / dev / RMT / 0 | 1 V: List the recovered file name; P: Restore files EXAMPLE: #TAR CVF / DEV / RMT / 0 / Oracle Backup / ROOT #TAR TVF / DEV / RMT / 0 column All content on the tape #TAR XVF / DEV / RMT / 0 / Oracle restores the contents of the / oracle directory on the tape to the current directory
Export the user name / password for the account of the account that runs the export command. Buffeer is used to take the size of the buffer of the data line. The name of the file exporting the dump file corresponds to whether the fragmentation of the fragmentation should be compressed, which will affect the Storage clause, whether the GRANTS exports should export the authorization indexes on the database object whether I want to export the index ROWS on the table should be exported. Export line. If it is 'n', only the DDLCONSSTRAINTS that generates only the database object in the export file exports the old agreement condition Full, such as 'Y', will execute a series of database accounts to export the entire database to export Owner. Then execute these accounts of the User export operates a series of tables to be exported; the Table Export Operation of these tables is executed, the RECordLength dump exported file log, which usually uses the default, unless The type of exported operations in two different operating systems inDype (Valid values have "complete", cumulative, and "ivcremental") RECORD a tag in the incremental export type, indicate whether Store logs to the log exported table in the log export of the log exported to the name of the exported parameter file. This file will contain the entry item Analyze of all parameters analysis indicate whether to write the statistics of the object to the export dump file in the export dump file. Indicates whether all read unwinds of all export objects are kept. Log Export Log The name MLS of the file to be written indicates whether the MLS label MLS_Label_ specifies the format of the MLS label.
Import the user name / password of the Select Item UserId run the entry command. Buffeer is used to take the size of the buffer of the data line. The name of the file import dump file show is specified whether the file content is displayed, not executed. EGORE indicates whether the error encountered is ignored when executing the 'create' command. This parameter should be used if the object being imported already exists. If Full is set to 'Y', the entire database introduction operation fromUser has parameters in the case of full = n. It is a series of database accounts that should be read from the export dump file. Touser A series of database accounts, exporting objects that deduct dump files will import these accounts. GRANTS imports whether or not to import the authorized indexes on the database object to import the index ROWS on the table should be transferred. As it is 'n', execute the DDLfull of the database object in the import file, such as 'Y', then execute a series of table RecordLength Tables to import a series of table RecordLength dumps to import file logs (with Bytes are in units, usually use default, unless the imported file in the two different operating systems is to pass the imported operation of INCTYPE (the valid value has "Complete", cumulative "and" Ivcremental ") Parfile to pass the name of the imported parameter file. This file will contain the entry INALYZE of all parameters Analyze indicate whether to write the statistics of the object to the import dump file in the import dump file name, the name of the file to be written MLS MLS numerals indicate whether to import hot backup command file: ORACLE_SID = oracle7ORACLE_HOME = / wwwdg / oraclesqldba lmode = y << EOF arch1connect internalalter tablespace SYSTEM begin backup; tar -cvf / dev / rmt / 0 / wwwdg / oracle / dbms! /sys01.dbfalter TableSpace System End Backup;
ALTER TABLESPACE BWT Begin Backup;! TAR-CVF / DEV / RMT / 0 / Wwwd1.dbfalter TableSpace BWT End Backup;
Archive log stopexiteof arch1files = 'ls /wwwwg/oracle/arch/arch*.dbf';
SQLDBA LMODE = Y << EOF Arch2connect InternalarChive Log Start; Exiteof Arch2
Tar -RVF / DEV / RMT / 0 $ filesrm -f $ files
SQLDBA LMODE = Y << EOFARCH3ALTER DATABASE BACKUP ControlFile To '/wwwdg/oracle/dbms/bwtcontrfile.backup' ;Exiteof Arch3
Tar -RVF / DEV / RMT / 0 / Wwwdg/oracle/bwt/bwtcontrofile.backup
