Computer major publishes papers.

If you are a computer major, you can suggest doing research on the Internet, which is popular, informative and useful.

Others, such as databases, have more references than algorithms and data structures.

Related model essay:

Backup and recovery of database.

Based on the architecture of Oracle, this paper discusses the methods and strategies of Oracle database backup from principle to practice. Including import and export, RMAN, operating system backup, etc.

Abstract: Starting from the architecture of ORACLE, this paper probes into the backup methods and strategies of database Oracle from theory and practice, including IMP/EXP, PMAN and OS.

Keywords: Oracle, backup, recovery, RMAN

Keywords: Oracle Bone Inscriptions; Backup; Restore; RMAN

abstract

There are many backup strategies for large-scale software operating systems, such as RAID technology and cluster technology. Many times, the backup of these systems can solve the problem of database backup. However, this kind of backup is very expensive. At the same time, hardware backup sometimes can't meet the actual needs. If a user accidentally deletes a table and wants to restore it, database backup becomes very important.

Introduction: In some large-scale software operating systems, there are many backup strategies, such as RAID technology and cluster technology. In most cases, these system backup strategies can complete database backup. However, the cost is quite high. At the same time, hardware backup is sometimes far from the actual demand. When a table is accidentally deleted and needs to be restored, database backup becomes very important.

How Oracle works

Oracle database has two operation modes: one is ARCHIVELOG, which aims to restore the database to the maximum extent when the database fails and ensure that the submitted data is not lost; Second, NOARCHIVELOG can only restore the database to the nearest recovery point (cold backup or logical backup). According to the high availability of the database and the lost workload that users can bear, archiving is strongly required for databases with high real-time requirements; Non-archival methods are only used for those databases that are developed and debugged.

How to change the operation mode of the database, when creating the database, as a part of creating the database, determines the initial archiving mode of the database. Generally speaking, it is NOARCHIVELOG mode. When creating a database, according to our needs, the database that needs to run in archive mode is changed to ARCHIVELOG mode. The operation is as follows.

1. Closing the database, backing up the existing data and changing the operation mode of the database are all important changes to the database, so it is necessary to back up the database to protect possible problems.

2. Modify the initial test parameters to enable automatic archiving.

Modify (add) the initialization file init[SID]. Ora parameter:

Log_archive_start=true # Start automatic archiving.

Log _ archive _ format = arc% t% s.arc # archive file format

Log _ archive _ dest =/archdir/arch # archive path

In 8i, there can be up to five archive paths, which can be archived to other servers, such as standby database servers.

3. Start the instance to the Mount state, that is, load the database without opening it.

$ & gtsvrmgrl

SVRMGRL & gt connection inside

SVRMGRL & gt starts the installation

SVRMGRL & gt change the database archive log; //Make the database run in archive mode.

SVRMGRL & gt open the database;

Oracle's backup scheme

According to the backup methods, it can be divided into logical backup, cold backup (offline backup) and hot backup (online backup), in which cold backup and hot backup can be collectively referred to as physical backup. According to backup tools, it can be divided into EXP/IMP backup, operating system backup, RMAN backup and third-party tool backup, such as VERITAS. The following are several backup tools and operations provided by Oracle itself.

1. export/import backup (logical backup)

EXP/IMP belongs to the category of logical backup, which means only backing up the data in the database without recording the physical location of the data. Export makes a binary backup of the database, which can only be read by its sister program imp(imp). The specific use method is as follows. (Because the parameters of EXP and IMP are basically the same, we only take EXP as an example. )

Command format and parameters of EXP

Format: Keyword = Value or Keyword = (Value 1, Value 2, ..., valuen)

Routine: expscott/tiger grants = ytables = (EMP, dept, mgr)

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

USERID Username/Password Export the whole file completely (n)

The size of the buffer data buffer owner user name list.

File output file (EXPDAT. DMP) table name list

The length range (y) of the recordlengthio record imported by COMPRESS.

Grant export permission (Y) INCTYPE Incremental export type

Index Export Index (y) Record Tracking Incremental Export (y)

Row Export Data Row (Y) PARFILE Parameter File Name

Constraint derivation Constraint (Y) Consistency Crosstab Consistency

Log file statistical analysis object (estimation) is output from the log screen.

The direct path (n) triggers the export trigger (y)

Feedback shows the progress of each X-ray (0). The maximum size of each dump file is the file size.

Query the clauses of the selected subset of export tables.

Note: You can check the usage of exp through the exp -help command; Imp -help command to see how to use imp.

2. Operating system backup (cold backup and hot backup)

There are two types of operating system backup, cold backup and hot backup. Operating system backup is essentially different from the above logical backup, which will copy the entire data file.

Offline backup

The database must be completely shut down before the file-level backup can begin. The shutdown operation must be performed with shutdown with normal, immediate and transactional options.

Back up every file used in the database, including all data files, all control files, all online redo log files and INIT. ORA file (recommended).

The general steps of cold backup are:

1) Shut down the instance for normal backup;

2) Back up the whole database to a directory.

3) Start the database

Namely:

SVRMGRL & gt connection inside

SVRMGRL & gt shut down immediately.

SVRMGRL & gt！ Cp< file & gt& lt backup directory & gt

or

SVRMGRL & gt！ tar CVF/db bak/full bk . tar/u 0 1/Oracle/oradata/dbname

SVRMGRL & gt startup

hot standby

A hot backup is an operating system backup when the database is open. Hot backup can only be used for databases in ARCHIVELOG mode. Hot backup does not need to back up the online log, but it must be in an archived state, which may be needed when the instance is restored. The current online log must be well protected or in a mirror state. At present, online logs do great damage to the database, and the database can only be restored if the data is lost. For temporary tablespaces, temporary information is stored. In hot backup, you can also consider not backing up. If the temporary file fails, you can delete the data file and tablespace and rebuild the temporary tablespace.

The contents of hot backup are the same as those of cold backup, and the general operation steps are as follows:

1) The backed-up tablespace uses Alter Tablespace ... to start the backup, so that the tablespace enters the hot backup mode.

2) Copy the data files that make up the tablespace with operating system commands similar to cold backup.

3) Use alter tablespace ... to end the backup command, so that the tablespace is out of hot backup mode.

4) Use the command Change Database ... Backup Control File to back up the control file.

Namely:

Inside the SVRMGRL & gt connection;

SVRMGRL & gtalter tablespace user starts backup;

SVRMGRL & gt！ CP/u 0 1/oradata/dbname/user 0 1 . ora/db bak/user 0 1 . ora

SVRMGRL & gt change tablespace client backup;

SVRMGRL & gt change the database backup control file to & ltfielname & gt；;

or

SVRMGRL & gt changes the database backup control file to trace.

Note: Because users are still operating the database during hot backup, it is best to keep each tablespace in the shortest backup state, which requires backing up one tablespace. Do not copy data files while keeping tablespaces together in a backup state.

3.RMAN

Recovery Manager(RMAN) is an Oracle application tool, which enables DBAs to perform backup and recovery tasks on databases conveniently, and provides DBAs with centralized control over backup and recovery operations of enterprise databases. RMAN can only be used in ORACLE8 or later. It can back up an entire database or database components, including tablespaces, data files, control files and archive files. RMAN can access and perform backup and recovery as needed.

RMAN supports six different types of backups, two of which are commonly used:

Full database full backup, including all data blocks.

Incremental incremental backup refers to backing up only those data blocks that have changed since the last backup at the same level or lower. This requires a 0-level increment as the basis of the increment, and its backup includes all data blocks used by the database (but not a full database backup). RMAN*** can support 7-level increments.

Backup, restore and restore are three basic commands of RMAN, which are used to backup, restore and restore the database respectively. The restore command is used to restore data files, control files or archived redo logs from backup sets or image copies. The recovery command is used for media recovery to apply redo log files.

The backup information of RMAN is generally stored in the recovery directory, which is also a database, but this database is used to store backup information, and a recovery directory can store backup information of multiple databases. RMAN can also run without a recovery directory, in which case the backup information is saved in the control file. This situation is even more dangerous, because once the control file is destroyed, all the database backup information will be lost and the recovery will fail. In addition, many RMAN commands will not be supported if the directory is not restored. Therefore, it is recommended to create a recovery directory for important databases.

Creating a recovery directory usually includes the following steps. (The sample database is db)

1) creates a separate tablespace for the directory.

SQL> creates a tablespace TSR MAN Datafile'/dbbak/RMAN/rsrman.dbf' with a size of 50M.

2) Create RMAN user

SQL> creates the user rman identified by the RMAN default tablespace rsrman temporary tablespace temp;

3) Grant RMAN permission

SQL> grants connect, resource, recovery_catalog_owner to rman.

4) open RMAN

$rman

5) Connect the recovery directory database

RMAN & gt； Connection directory rman/rman@db

6) Create a recovery directory

RMAN & gt； Create directory tablespace tsrman

Before backing up a database, it must be registered in the recovery directory. This process is as follows (assuming that the target database connection string is db 100).

1) connects to the recovery catalog database.

$rman rman/rman@db

2) Connect to the target database of RMAN (the database to be backed up).

RMAN & gt； Connection target sys/change _ on _ install @ db100

3) Register the database

RMAN & gt； Register the database;

After registering the database, you can back up the database. There are complete database backup, tablespace backup, control file backup and archive log backup. The operation is as follows.

1) Full database backup

Requirements: ARCHIVELOG mode, complete database backup in DB OPEN.

RMAN & gt； Run {

Distribution channel c 1 type = disk;

Back up the database;

Release channel c1;

}

2) Tablespace backup

Requirements: Archive Log Mode

RMAN & gt； Run {

Distribution channel c 1 type = disk;

Backup tablespace "ts_users" filesperset 3 format' aatst _% t% s.% p';

Release channel c1;

}

3) control file backup

RMAN & gt； Run {

Distribution channel c 1 type = disk;

Back up the current control file tag = weekly _ sat _ backup.

Release channel c1;

}

When the database is fully backed up, the control file is automatically included. You can also include control files in the backup of tablespaces or data files.

RMAN & gt； Run {

Distribution channel c 1 type = disk;

Backup tablespace "ts_users"

Filesperset 3 format "aatst _% t% s.% p";

Comprise a current control file;

Release channel c1;

}

4) Archive log backup

Get the log serial number to be backed up by querying the data dictionary table v $ archived _ log, and then execute the following command:

RMAN & gt； Run {

Distribution channel c 1 type = disk;

Backup archive log low log sequence 3 high log sequence 10 thread1;

Release channel c1;

}

Oracle's backup strategy

The correct backup strategy can not only ensure the 24*7 high-performance operation of the database server, but also ensure the rapidity and reliability of backup and recovery. We will discuss the multi-level incremental backup of RMAN as an example of a backup strategy. Multi-level backup is used to reduce the time required for daily backup and ensure the good recovery of the system. There is a trade-off between recovery time and backup time. For example, as long as the database is fully backed up and then only backed up and archived, the database can be restored to the latest state, but such a recovery time will be unbearable. Multi-level backup is to solve this problem, combined with the characteristics of some applications, the following backup strategies can be adopted:

Make a full backup of the database once a month (including all data and read-only tablespaces);

Make a zero-level backup every Monday (except read-only tablespaces);

Make a first-level backup every Wednesday;

Make a second backup every day.

Make a hot backup of the recovery directory once a day.

Any database changes need to resynchronize the catalog directory and back it up again (such as adding data files) or back it up again (such as changing the tablespace to read-only).

After each backup, you can back up the archive log or back up the archive log regularly. If possible, you can back it up directly to tape.

Recovery of Oracle

The following operating protocol recovery directories are stored in db 1 18, and the target database is db 100.

1. Database recovery

1) Start SQL*PLUS, use the correct init.ora file, and start the target database instance with the NOMOUNT option.

2) Start RMAN and connect to the recovery directory, as shown below:

$ TERM directory rman/rman@db 1 18

Recovery Manager: Version 9. 2. 0. 1.0- Production

Copyright (c) 1995, 2002, Oracle Bone Inscriptions company. property in copyright

Connect to the recovery catalog database

RMAN & gt；

3) Connect to the target database

RMAN & gt； Connect target inside /oracle@db 100

Connect to the target database: db (dbid =1142471523)

4) After connecting to the target database, execute the restore command to perform a full database recovery.

RMAN & gt； Run {

2> distribution channel c 1 disk;

3> restore the database;

4> release channel c1;

}

2. Table space and data file recovery

To perform tablespace or datafile recovery, the database must be running in ARCHIVELOG mode, and users need to have archived redo log files required for media recovery. Before attempting to restore a tablespace, you must ensure that the tablespace is offline. RMAN commands are as follows:

Run {

Distribution channel c 1 disk;

SQL“alter tablespace users _ data offline immediate”；

Restore tablespace users _ data

Restore tablespace users _ data

SQL“alter tablespace users _ data online”；

}

The above script does the following:

1) RMAN allocates a channel for the recovery operation from c 1.

2) Take the tablespace users_data offline.

3) RMAN restores the data files belonging to the tablespace from the backup set.

4) Provide the required archived log files through media recovery, and recover the data files belonging to tablespace users_data.

5) RMAN brings the tablespace online.

During script execution, if the data file cannot be restored to the specified location, please use the set newname command before executing the restore command.

3. Control file recovery

1) Start SQL*PLUS, use the correct init.ora file, and start the target database instance with the NOMOUNT option.

2) Start RMAN and connect to the recovery directory, as shown below:

$ TERM directory rman/rman@ora8i.oracle

Recovery Manager: Version 9. 2. 0. 1.0- Production

Copyright (c) 1995, 2002, Oracle Bone Inscriptions company. property in copyright

Connect to the recovery catalog database

RMAN & gt；

3) Connect to the target database

RMAN & gt； Connect the target internal/oracle@demo.oracle.

Connect to the target database: db (dbid =1142471523)

4) After connecting to the target database, execute the restore command to restore the control file.

RMAN & gt； Run {

2> distribution channel c 1 disk;

3> Restore the control file;

4 & gt}

summary

Ensuring the security of Oracle database is an important part of system security, and a perfect database backup and recovery scheme must be designed. The combination of various tools provided by Oracle can make database backup and recovery simple. In the actual backup and recovery of Oracle database, there will be many unreasonable and complicated situations. According to different situations, it is necessary to restore the database with the principle of making the data have the maximum recoverability and the shortest recovery time, which requires a lot of practice and experience accumulation.

refer to

[1] Oracle8i backup and recovery guide Oracle documentation

[2] Oracle8i Recovery Manager User Guide and Reference Oracle Documentation.

[3] Oracle9i: A Beginner's Guide (USA) Michael Abby Michael Keryan abramson 2002.3 Mechanical Industry Press.

[4] Oracle8i Backup and Recovery Manual (USA) Ramaville Prianande Adekoli Wang Leyan et al. Translated 200 1.9 Machinery Industry Press.

For reference only, please learn by yourself.

I hope it helps you.