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

1. An overview of data backup and recovery A, the necessity of backup and recovery due to various software and hardware failures of the computer system, user misoperation and some malicious destruction are difficult to avoid, so this affects the correctness of the data, and even causes data loss, the serious consequences of server crash. So frequent backup can effectively prevent data loss and restore data from the wrong state to the correct state. B, backup solution Oracle backup is divided into physical backup and logical backup. Physical backup is divided into cold backup and hot backup. (hot backup is divided into user managed backup and Oracle managed backup)

C, cold backup (offline backup) specific steps offline backup is a full backup after shutting down the database is the fastest and safest way. An offline backup is a backup of all files in the database, including parameter files, all control files, all data files, and all online redo log files.

The specific steps for offline backup are three steps:

1. Log in to the database as sys user or system, and close the database by immediate.

SQL > conn sys/02000059 as sysdba is connected. The SQL > shutdown immediate database has been closed. The database has been uninstalled. The ORACLE routine has been closed.

2. copy the files that need to be backed up to the specified location. (copy all files that need to be backed up)

3. After the copy is completed, if you continue to let users use the database, you need to start the database in open mode.

The SQL > startup openORACLE routine has been started. Total System Global Area 3373858816 bytesFixed Size 2180424 bytesVariable Size 2013268664 bytesDatabase Buffers 1342177280 bytesRedo Buffers 16232448 bytes database loaded. The database is already open. D. Advantages and disadvantages of offline backup:

1. It is a very fast backup method (only need to copy files).

2. Easy to file (just a simple copy).

3. It is easy to recover to a certain point in time (just copy the file back).

4. It can be combined with the archiving method to restore the "latest state" of the database.

5. Low maintenance and high safety. Deficiency:

1. When used alone, it can only provide recovery to "at a certain point in time".

2. In the whole process of implementing backup, the database must be backed up and not as other work. That is, the database must be closed during an offline backup.

3. If the disk space is limited and can only be copied to other external storage devices, the speed will be very slow.

4. Cannot restore by table or by user. Database recovery database recovery is to restore the data backed up in the database to the original database. Database recovery technology is divided into complete recovery and incomplete recovery. Secure recovery refers to restoring the database to the database state when the database fails; incomplete recovery refers to restoring the database to the database state at a certain time before the database failure. Offline recovery offline recovery is the use of backup files to restore the database to the state it was when it was backed up. G. Specific operation of offline recovery. The specific steps of offline recovery are divided into three steps:

1. Log in to the database as sys user or system, and close the database by immediate.

2. Copy all backup files to the original location.

3. After the copy is completed, if you continue to let users use the database, you need to start the database in open mode. 2. the import and export of data a, the way of graphically importing and exporting data

1. Use PLSQL Developer tool to export data:

The first is the file format exported to .dump, which is binary, cross-platform, contains permissions, is also very efficient, and is the most widely used.

The second is exported to a .sql file, which can be viewed by a text editor, with good versatility, but not as efficient as the first, which is suitable for import and export with a small amount of data.

2. Use PLSQL Developer tool to export data:

The first is to import files in .dump format:

The second is to import files in .sql format:

It should be noted that imported .sql format files can have no data, but must have the structure of the table, otherwise the import will fail. B, command line data export data export command is executed as follows: exp [username / password] Parameter 1 Parameter 2 example 1: export the database orc completely. Exp system/oracle@orcl file=c:\ dump\ oracle_bak.dmp full=y example 2: export all objects of the scott user in the database. Exp scott/tiger1@orcl file=c:\ dump\ scott_bak.dmp owner=scott example 3: import data in the scott user table emp,dept export exp scott/tiger1@orcl file=c:\ dump\ table_bak.dmp tables= (emp,dept) c, command line import data command is executed as follows: IMP [username/password] parameter 1 parameter 2. Import the backup file into the database: imp scott/tiger1@orcl file=c:\ dump\ scott_bak.dmp ignore=y note that the export operation performed by the scott user in this example must also be imported by the scott user at the time of import. 3. Using trigger to realize synchronous backup of data a, using trigger to realize synchronous backup diagram of data.

B, example: using trigger to realize synchronous backup of data

Because only one Oracle database is installed in the system, the choice is to back up in the same database: (that is, table An and table A backup are in the same database)

1. Create a backup table of the employee table:

CREATE TABLE emp_bakASSELECT * FROM emp

2. Create triggers to realize synchronous backup of data. (for example, when the employee is deleted, the backup table is deleted synchronously)

CREATE OR REPLACE TRIGGER syno_bak_tirggerAFTER DELETEON empFOR EACH ROWBEGIN DELETE FROM emp_bak WHERE empno =: old.empno;END

3. Testing

DELETE FROM emp WHERE empno = 7951 * select * FROM emp;SELECT * FROM emp_bak

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.