oracle| Backup | strategy | recovery | data | database
SummaryThe recovery of lost database files depends to a large extent on the backup strategy used. Based on the flexibility of recovery, this paper discusses the backup and recovery strategy of Oracle8 database, and gives the project development example of the script file which automates the backup process under Windows 2000 environment.
Key WordsOracle Database Backup Recovery
Introduction
With the increasing importance of information system in various business activities, the maintenance of the availability of system data has become a very important topic at present. In order to maintain the health information system effectively, some measures must be taken to prevent the RDBMS (relational database management system) from being affected by media, operating system, software and other events causing serious damage to the database files, and then to affect the normal operation of the information system. This requires the failure of such systems in the case of 芄 suffering from ⒂ line у urgent sex flag raven refers to the basis; refers to cooking у neon naan raise sunfaith Qiu 蟪 tam first "Nian ye take mei seek fu Siegesbeckiae Bai yu furui the mu-read shell proud of the 腛 database backup and recovery strategy is discussed.
basic rules for database backup and recovery
1. Multi-work online redo log files
Each database instance has its own online redo log group, where Oracle first saves all changes to the database in the Redo log buffer, and then the logger process (LGWR) uses the data from the system's shared zone SGA (System Global Area), the redo log buffer write online redo log file, when a disk crashes or an instance fails, the database can be protected by a related online redo log, minimizing the loss, but Oracle creates only one set of redo log files in the default mode (each group has only one project file), In order to reduce the risk of losing these important redo log files, it is necessary to mirror the copy.
When the Oracle-level multi-worker online redo log file, that is, add multiple files to each group to mirror the data so that I/O failure or write loss only damages a copy, thus ensuring that the LGWR daemon can write information to at least one member, and the database can still continue to run. You should also ensure that the members of the log group should not be on the same physical device, as this will weaken the effect of multiple log files.
2. Mirror Copy control file
The control file describes a common database structure that stores a large amount of database state information, including the physical structure and the name, location, and status of the online redo log file at that time. The control file is read by an Oracle instance when the database is started, remains open, and the file content is updated as the operation progresses until the instance is closed. The information that needs to be recovered is synchronized during its opening. Includes checkpoint information, so if the control file is damaged or lost, Oracle will not be able to continue working, so multiple copies of the control file should be kept in the system, and the copies should be placed on different disk devices installed under different disk controllers.
Because Oracle does not provide full support for control file Control_files, you should copy the control files to the defined new location before you restart the database by using the operating system or hardware mirroring on the control file, or you will have an error when the database starts up before you restart it after modifying the initialization file's parameters.
3. Activate the archive process
When the database is running in Noarchivelog mode, you can only make a consistent backup of the database after the database is completely closed, and the archive of online redo logs is disabled, so that when the Oracle instance fails, the database can only be repaired to the point of the most recent full database backup. The instance cannot be recovered at the expiration point. In Archivelog mode, the database can be backed up not only by a consistent backup, but also online when the database is open. With the backup of the database and the online and archived redo log files, users can recover all committed transactions and allow the database to be restored to a specified time, SCN, or log series number, increasing the flexibility of recovery and reducing data loss in the case of failure. Therefore, the database should run in Archivelog mode.
In Archivelog mode, to prevent file corruption and media failure, you should archive the log to a different disk, which specifies multiple target implementations for the archive redo log in the initialization file.
4, database implementation of large changes in backup
Because the schema structure information of the database is saved in the control file, you should back up the control files and the corresponding data files immediately when you make a large change to the database (including changing the table structure, adding, deleting log files, or data files, etc.).
5, use the Resetlogs option to open the database after the backup
After you open the database with the Resetlogs option, you should make a backup of the entire database offline or online, or you will not be able to restore the changes after the reset log.
When the database is opened with the Resetlogs option, Oracle discards duplicate information that is not applied in the recovery and ensures that it will never be used again, and also initializes information about the online logs and redo threads in the control file, and clears the contents of the online logs. Therefore, the serial number of the Resetlogs archive log will not match the required value of the Resetlogs Oracle control file (the checkpoint in the backup file is older than the checkpoint in the control file), that is, the previous archive log file cannot be applied in the recovery. This causes the backup before the resetlogs operation to be useless in the new form.
6. Avoid back up online redo log files
Because the paper puts forward a multiple-work online redo log file and the database runs in Archivelog mode, the arch process can archive the online redo log so that it does not have to be backed up. Rebuilding it with a backup online redo log file may cause confusion in the log file sequence number, thereby destroying the database and getting counterproductive results.
7. Reset the online log
The online log should be reset after an incomplete recovery or recovery with a backup control file.
To ensure consistency of the database, it is necessary to ensure that all data files are restored to the same point in time after the recovery, but incomplete recovery may result in a data file with a different checkpoint than other files, causing the database to be compromised. Similarly, the number of SCN and counters saved in the backup control file may be different from the values in the current log file, which also undermines the consistency of the database, so you should reset the online logs after both of these actions are completed.
8. Logical Backup of database
All of the backups described above are physical backups, a backup of the actual physical database files from one place to another, and a logical backup of the database using an Oracle-provided export utility. Oracle also provides the appropriate import utility to rebuild the information saved in a logical backup.
A logical backup copies only the data in the database and does not record the backup process of the data location. It uses SQL statements to export data from the database to an external file that is stored in the appropriate location, and detects damage to the block, so it can be used as a complement to the physical backup.
Backup Strategy
Considering that most of today's information systems are 24x7 every week, an online backup is available, or an offline backup can be made at a certain time.
By applying the above rules, you can derive the following typical backup strategies:
① Mirror copy redo log file;
② Mirror Copy control file;
③ activates the archiving process, which operates the database in Archivelog mode;
④ Partial Online backup of the database daily (full hot backup of the database every day will fearlessly increase the burden of the database and unnecessary, while also increasing the flexibility of database recovery);
⑤ A logical backup of the database every other week or weeks.
Actual Project Application
When the file in the database reaches a certain number, the DBA may not be able to remember the name or location of the backed-up file, so automating the backup process can be achieved by making the backup processes more automated, while not omitting the files that should be backed up, and using automated batch files and script files. This paper introduces the application of the automatic backup and recovery strategy in the actual project development, and proves the correctness and feasibility of the strategy through practical application.
(1) The following script implements the multiple work of online redo logs:
Connect account name/password
The storage location of the ALTER DATABASE Ktgis add LogFile member log file to the group online log number;
Shutdown immediate
Startup pfile= initialization file storage location exclusive mount; Mount database and do not open
ALTER DATABASE Archivelog; Activating the archive process
ALTER DATABASE open;
Exit
(2) The following VB code automatically establishes the batch file for online backup and the corresponding script file.
Set adotmp = Objconnect.execute ("Select Tablespace_name from Sys.dba_data_files") ' gets the table space name in the database
Dim Lnum1 as Long
Dim lnum2 as Long
Lnum = FreeFile
Open Automatic backup batch file path for Binary as Lnum
LNUM1 = FreeFile
Open "Onlinebegin.sql" for Binary as lnum2 onlinebegin.sql script file file name for setting table space into hot backup mode
lnum2 = FreeFile
Open "Onlineend.sql" for Binary as lnum2 onlinebegin.sql script file file name for end table Space Hot backup mode
strtmp = "Connect account name/password" & Chr (+) & Chr (10)
Put Lnum1, strtmp.
Strtmp = "Shutdown Immediate" & Chr (+) & Chr (10)
Put Lnum1, strtmp.
strtmp = "Startup pfile= the location of the initialization file exclusive mount;" & Chr (+) & Chr (10)
Put Lnum1, strtmp.
STRTMP = "ALTER DATABASE archivelog;" & Chr (+) & Chr (10)
Put Lnum1, strtmp.
STRTMP = "ALTER DATABASE open;" & Chr (+) & Chr (10)
Put Lnum1, strtmp.
strtmp = "Connect account name/password" & Chr (+) & Chr (10)
Put Lnum2, strtmp.
strtmp = Oracle Service Manager Path & "@" & "Onlinebegin.sql" & Chr (d) & CHR (10) Executing script files in Service Manager onlinebegin.sql
Put Lnum, strtmp.
Do as not adotmp.eof
Set ADOTMP1 = Objconnect.execute ("Select file_name from Sys.dba_data_files where Tablespace_name= '" & Adotmp.fields ( 0) & "" "To get the current table space corresponding to all the data file name, through the loop can get all the table space corresponding data file name, if only back up the specified table space, you can specify the table space name to get its corresponding physical data file
strtmp = "Alter Tablespace" & Adotmp.fields (0) & "Begin Backup;" & Chr (+) & Chr (10) Place tablespace in hot backup mode
Put Lnum1, strtmp.
strtmp = Oracle Ocopy.exe tool full path "& Adotmp1.fields (0) &" "& Backup File Storage path & CHR (10) & CHR
Put Lnum, strtmp.
strtmp = "Alter Tablespace" & Adotmp.fields (0) & "End Backup;" & Chr (+) & Chr (10) tablespace restore Normal mode
Put Lnum2, strtmp.
Adotmp.movenext
Loop
strtmp = Oracle Service Manager Path & "@" & "Onlineend.sql" & Chr (d) & CHR (10) Executing script files in Service Manager onlineend.sql
Put Lnum, strtmp.
strtmp = "Exit" & Chr & Chr (10) Exit Service Manager
Put Lnum1, strtmp.
STRTMP = "ALTER system switch LogFile & CHR" & Chr (10) force log conversion to enable Oracle to create an archive log file
Put Lnum2, strtmp.
strtmp = "Exit" & Chr (+) & Chr (10)
Put Lnum2, strtmp.
Close
Set adotmp = Nothing
Set ADOTMP1 = Nothing
Run the resulting automatic batch file to automate the online backup of the database.
(3) After recovering the data file from the backup, execute the following script to restore the database.
Connect account name/password
Shutdown abort
Startup Mount pfile= the location where the initialization files are stored; Mount Database
Set AutoRecovery on; Turn on automatic recovery
Recover database;
ALTER DATABASE open; Open Database
Concluding remarks
To improve the reliability of recovery after database disaster is being paid more and more attention, this paper discusses the backup and recovery of Oracle database based on the experience of individual in actual project development and the flexibility of recovery, and puts forward a typical backup strategy, Users can apply flexibly based on their actual situation and database structure.
