Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to back up and restore Oracle database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to back up and restore Oracle database.

Backup and recovery are common operations in the use of Oracle database. Backups in Oracle fall into two main categories: logical backup and physical backup. Physical backup is divided into two categories: cold backup and hot backup. This section briefly describes how to use various backup methods to backup and restore Oracle databases.

1 logical backup / restore (export / import)

Logical backup refers to the use of exp commands for backup. Using this command for backup is simple and easy, and does not affect the normal database operation. Therefore, it is often used as a means of daily backup. The exp command can add multiple parameter options to implement different export policies. You can use exp -? Command to view. Among them, the common parameters include: owner, table and inctype.

1. Required parameters

For an export command, you can use only the required parameters, as shown in example 1.

[example 1] demonstrates the use of the export command.

C:\ Documents and Settings\ Administrator > exp system/abc123 file=d:/b.dmp

[code description] system/abc123 is the user name and password used to log in to the database; file=d:/b.dmp specifies the full path of the file where the data export is stored. It is important to note that this command does not specify which database instance to log in to, so the database instance specified by the system environment variable ORACLE_SID will be used.

Note: another special case is that ORACLE_SID is not found in the list of environment variables, so you can search for the ORACLE_SID key in the registry. Oracle also refers to the value of the key in the registry to set the environment.

2.owner parameter

The owner parameter, which can specify a list of user names. When exporting, only the objects owned by the user in the user name list are exported, as shown in example 2.

[example 2] demonstrates the use of the owner parameter.

C:\ Documents and Settings\ Administrator > exp system/abc123 owner= (test, oracle) file=d:/b.dmp

[code description] owner= (test, oracle) specifies that the exp command only exports objects owned by test and oracle users. If a user does not exist, for example, in this case, the user oracle does not exist, then a warning is given, but the export of the object for the user test is not affected.

3.tables parameter

When using the exp command, you can also specify the tables parameter. This parameter is used to specify which data tables are exported. Example 3 demonstrates the use of this parameter.

[example 3] demonstrates the use of the tables parameter.

Exp system/abc123 tables= (people, employees) file=d:/b.dmp

[code description] tables= (people, employees) specifies the list of data tables to be exported. When the exp command is executed, only the people and employees tables of the user system will be exported. If the table to be exported does not exist, a warning message is given. However, it does not affect the export of other tables.

4. About inctype parameter

In versions prior to Oracle 9i, you could use the inctype parameter to specify the type of increment to be exported. However, this parameter is no longer used in Oracle 9i and later versions. Example 4 demonstrates the error message for using this parameter in Oracle 10g.

[example 4] the parameter inctype in Oracle 10g has been abolished.

C:\ Documents and Settings\ Administrator > exp system/abc123 inctype=complete file=d:/b.dmp

[code description] use the inctype parameter in the exp command, and Oracle will throw an error message

For logical backups, the corresponding restore command is imp. If the database is corrupted, you can restore it with the following command:

C:\ Documents and Settings\ Administrator > imp system/abc123 file=d:/b.dmp

The imp command can also specify parameters. For example, use the owner parameter to import only specific user objects, and the tables parameter to import only specific tables, as shown in the following code:

C:\ Documents and Settings\ Administrator > imp system/abc123 file=d:/b.dmp tables= (people, employees)

This command will import only tables named people and employees.

2 physical backup / restore

Physical backup refers to backing up the files of the database directly. There are two types of physical backups: cold backup and hot backup.

1. Cold backup / restore

Cold backup refers to backing up all database files when the database is closed. These files include: all data files, all control files, all online REDO LOG files, and Init.ora files (optional).

[example 5] demonstrates the cold backup of the database.

(1) first log in to the database as an administrator and shut down the database

C:\ Documents and Settings\ Administrator > sqlplus / as sysdba

SQL > shutdown normal

Database closed.

Database dismounted.

ORACLE instance shut down.

(2) after shutting down the database, you can back up its physical files. These physical files are at {ORACLE_HOME}\ product\ 10.1.0\ oradata\ test by default, where test is the database name. Therefore, first go back to the Windows command line and use the copy command, or add the host keyword to the SQL command line and use the host command directly:

SQL > host copy D:\ oracle\ product\ 10.1.0\ oradata\ test f:\ backup\

D:\ oracle\ product\ 10.1.0\ oradata\ test\ CONTROL01.CTL

D:\ oracle\ product\ 10.1.0\ oradata\ test\ CONTROL02.CTL

D:\ oracle\ product\ 10.1.0\ oradata\ test\ CONTROL03.CTL

At this time, the cold backup of the whole database has been realized. The recovery of cold backup is very convenient, as long as the database is closed, the backup files can be copied to the original location.

two。 Hot backup / restore

The hot backup of the database refers to the backup of the database in the startup state. To make a hot backup of a database, we should first ensure that the database is running in archive mode, then back up the data files of the tablespace, and finally back up the control files.

[example 6] demonstrates database hot backup.

(1) before switching the log mode, the running database must be shut down normally. Therefore, you should first log in to the database as an administrator and shut down the database.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Restart the database instance, but do not open the database.

SQL > startup mount

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

Database mounted.

(3) use the alter command to switch the database to archive mode.

SQL > alter database archivelog

Database altered.

(4) Open the database in order to operate the database.

SQL > alter database open

Database altered.

(5) use the archive log list command to confirm that the current database is in archive mode.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 538

Next log sequence to archive 540

Current log sequence 540

Archive Mode indicates that the current database is in archive mode. The Oracle database has an online redo log that records the user's database operations, such as inserting, deleting, or updating data. Typically, each Oracle database contains at least two online redo log groups. A log switch occurs when an online redo log group is full. The other online log consists of the currently used log, which continues to record user actions. When the current online log group is full, it will switch to the first online log group and overwrite the data in it.

If the database is in non-archive mode, the online log discards existing information when it is switched. In archive mode, when a log switch occurs, the switched log is archived first and the information is copied to other directories. In this way, the online log information will not be lost.

(6) set the tablespace users in the database to backup mode, as follows:

SQL > alter tablespace users begin backup

Tablespace altered.

(7) copy the actual data files in the table space to the backup directory, as follows:

SQL > host copy D:\ oracle\ product\ 10.1.0\ oradata\ test\ users01.dbf d:\ back

1 file has been copied.

Turn off the backup mode of the tablespace as follows:

SQL > alter tablespace users end backup

Tablespace altered.

(8) back up the data files of other tablespaces in the database in the same way.

(9) backup control files, the code is as follows:

SQL > alter database backup controlfile to'F:\ backup\ TEST_BACKUP' reuse

Database altered.

(10) create the backup control file with the following code:

SQL > alter database backup controlfile to trace

Database altered.

A trace file for the new control file is generated in the {ORACLE_HOME}\ admin\ {INSTANCE_NAME}\ udump directory. In this case, the path is D:\ oracle\ product\ 10.1.0\ admin\ test\ udump. Get the latest trace file in the directory, which records the creation script for the database control file. The following code is a file snippet:

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 454

LOGFILE

GROUP 1'D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ TEST\ REDO01.LOG' SIZE 10m

GROUP 2'D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ TEST\ REDO02.LOG' SIZE 10m

GROUP 3'D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ TEST\ REDO03.LOG' SIZE 10m

-- STANDBY LOGFI

Copy the file and copy the file to the backup directory. At this point, the hot backup process is over.

After the backup is successful, in the event of a database failure, the backup file can be used for recovery. For example, if the data file D:\ oracle\ product\ 10.1.0\ oradata\ test\ users01.dbf is corrupted, an error is thrown when starting the database.

SQL > startup mount

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

Database mounted.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 4-see DBWR trace file

ORA-01110: data file 4:'D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ TEST\ USERS01. DBF'

Database startup failed because the data file USERS01.DBF could not be found. At this point, you need to copy the previously backed up data files to the original directory.

SQL > host copy f:\ backup\ users01.dbf D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\

TEST\ USERS01.DBF

1 file has been copied.

At this point, restart the database.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4:'D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ TEST\ USERS01. DBF'

When Oracle starts, it always checks whether the identity in the control file (Checkpoint CNT and Checkpoint SCN) is the same as that in the data file. If not, the data file needs to be restored to synchronize the identity in the control file with the identity in the data file. The restore command is as follows:

SQL > recover datafile'D:\ ORACLE\ PRODUCT\ 10.1.0\ ORADATA\ TEST\ USERS01. DBF'

Of course, you can also use the following statement instead.

SQL > recover datafile 4

At this point, Oracle may ask the user to specify the archive log. Archive logs are stored by default under {ORACLE_HOME}\ flash_recovery_area\ {DATABASE_NAME}\ ARCHIVELOG\. Select the most recent archive log for the recover command or use the archive log recommended by Oracle, as shown in the figure.

Select Archive Log

"after the archive log is selected, Oracle uses the archive log for recovery." After the restore is successful, you can successfully open the database.

SQL > alter database open

Database altered.

The significance of backing up the control file is that when the control file is lost, the hot backup control file can be restored. The significance of backing up the tracking file is to recreate the control file using the tracking file when the backup control file cannot be used properly.

3 back up the database with PL/SQL Developer

In addition to using various commands to back up the database, you can also use PL/SQL Developer for backup. Using this tool for backup is easy to learn and not easy to make mistakes. The modes of backing up with PL/SQL Developer include for the entire database, for a user, for specific tables, and so on. This section takes backing up a user object as an example to describe how to use PL/SQL Developer for backup.

[example 7] demonstrates using PL/SQL Developer to back up all the objects of the user System.

(1) Open PL/SQL Developer and log in to the database TEST using the System user.

(2) Select [My objects] from the drop-down menu in the left window to ensure that all operations are directed at the current user's object.

(3) Select the [Export User Objects] menu item under the [Tools] menu in the menu bar, and the export window will pop up, as shown in the figure.

Use PL/SQL Developer to back up all the objects of the user

(4) Select all objects in the list and specify the path to the exported file for [Output File]. The exported file is actually a SQL script file. It records the creation scripts for all objects for the current user. Once the database fails, you can rebuild the database and create the corresponding user (System user is the default user of the database), then use this user to log in to the database and execute the SQL script to recreate the database object.

(5) in addition to backing up database objects, you also need to back up the data in the data table. To back up the data in the table, you need to use the [Export Tables] function under the [Tools] menu.

At this point, I believe you have a deeper understanding of "how to back up and restore Oracle database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.