Oracle_ Advanced Features (10) Backup recovery

Backup and Recovery
The Oracle database has three standard backup methods, namely export/import (EXP/IMP), hot and cold backup.
Export/import is a logical backup, and cold and hot backups are physical backups.
First, export/import (Export/import)
With export, data can be extracted from the database, and the extracted data can be sent back to the Oracle database using import.
The process of importing data is the inverse process of data export,
Import the data files into the database and export the database data to a data file, respectively.

1. Exporting and importing data (import)
Oracle supports three different types of output:
1.1 Table mode (t) to export and import data for the specified table.
To export a table:
Exp system/123 tables=scott.dept File=e:\dept.dmp
Exp System/[email protected] tables=scott.dept,scott.emp file=e:\empdept.dmp
Log=e:\explog_empdept.log

Import Table:
drop table scott.emp;
drop table scott.dept;
--drop table scott.dept cascade constraints;
Imp System/[email protected] fromuser= (Scott) touser= (Scott) tables= (dept,emp)
File=e:\empdept.dmp Log=e:\implog_empdept.log

1.2 User mode (U-mode), export and import all objects and data of the specified user.
Exp System/[email protected] owner= (Scott) file=e:\scott.dmp Log=e:\scott_exp.log buffer=655000 compress=y

--Common
Connect system/[email protected];
--Delete User--
Drop users find cascade;
--Delete Table space--
Drop tablespace ts_find including contents;
--Create tablespace and data file--
Create tablespace ts_find01 datafile ' E:\app\oradata\orcl\ts_find01. DBF ' size 100M reuse autoextend on next 10M;
--Create the user and authorize--
The creation user find identified by find default tablespace ts_find01;
Grant Resource,connect to find;
Grant Select any sequence to find;
Grant Create any table,alter any table,drop any table to find;
Grant Select any table,insert any table,update any table,delete any table to find;
Grant Create any trigger,alter any trigger,drop any trigger to find;
Grant Create any procedure,alter any procedure,drop any procedure,execute any procedure to find;
Grant Create any view,drop any view to find;
Grant create any synonym to find;

Imp System/[email protected] fromuser= (Scott) touser= (find) buffer=655000
Ignore=y commit=y file=e:\scott.dmp Log=e:\scott_imp.log

1.3 Whole-Library mode (full mode) to export all objects in the database.
Exp System/[email protected] file=e:\full.dmp log=e:\full_exp.log full=y buffer=655000 compress=y

-Generally not
Imp system/[email protected] full=y buffer=655000 ignore=y commit=y file=e:\full.dmp log=e:\full_imp.log

2. Incremental Export/Import
Incremental export is a common method of data backup that can only be implemented for the entire database and must be exported as system.
The exported file name defaults to Export.dmp, and if you do not want your output file to be named Export.dmp, you must indicate the name of the file you want to use on the command line.
There are three types of incremental exports:
2.1 "Full" incremental export (complete)
That is to back up the complete database information,
Like what:
Exp System/[email protected] Inctype=complete file=e:\complete.dmp Log=e:\complete_exp.log
2.2 "incremental" incremental export
Back up the data that was changed after the last backup,
Like what:
Exp System/manager inctype=incremental file=e:\incremental.dmp log=e:\incremental_exp.log
2.3 "Cumulative" incremental export
The cumulative export method is to export information that has changed in the database since the last "full" export.
Like what:
Exp System/manager inctype=cumulative file=e:\cumulative.dmp log=e:\cumulative_exp.log
The database administrator can schedule a backup schedule that is reasonably and efficiently done in three different ways of exporting data.
For example, the task of the database can be arranged as follows:
Monday: Full Backup (A)
Tuesday: Incremental Export (B)
Wednesday: Incremental Export (C)
Thursday: Incremental Export (D)
Friday: Cumulative Export (E)
Saturday: Incremental Export (F)
Sunday: Incremental Export (G)
If, in Sunday, the database was accidentally compromised, the database administrator could follow the steps to reply to the database:
The first step: Create database with the command to regenerate the data structure;
Step Two: Create an additional rollback that is large enough.
Step three: Full incremental import:
Imp System/manager inctype=restore full=y file=e:\complete.dmp
Fourth Step: Cumulative incremental import:
Imp System/manager inctype=restore full=y file=e:\cumulative.dmp
Fifth step: The most recent incremental import:
Imp System/manager inctype=restore full=y file=e:\incremental.dmp

Second, cold backup
A cold backup occurs when the database has been shut down properly and provides us with a complete database when it shuts down normally.
Cold backup is a statement that copies critical files to a different location.
Cold backup is the fastest and safest way to back up Oracle information.
The advantages of cold backup are:
1. is a very fast backup method (just copy the file)
2. Easy to archive (simple copy)
3. Easy to recover to a certain point in time (just copy the file back)
4. Can be combined with the archiving method to do the "best state" recovery of the database.
5. Low maintenance, high safety.
However, cold backup also has the following disadvantages:
1. When used alone, it can only be provided to "a point in time" recovery.
2. In the process of re-implementing the backup, the database must be backed up and no other work can be done. In other words, during a cold backup, the database must be off state.
3. If you have limited disk space, you can only copy to other external storage devices such as tapes, which can be very slow.
4. Cannot be recovered by table or by user.
Files that must be copied in a cold backup include:
1. All data files
2. All control files
3. All online redo log files
4.init.ora file (optional)
It is important to note that a cold backup must be done in the case of a database shutdown, and that performing a database file system backup is not valid when the database is in an open state.
Here is a complete example of a cold backup.
(1) Closing the database
Sqlplus/nolog
Sql>connect/as SYSDBA
--sql>shutdown Normal;
Sql>shutdown immediate;
(2) use copy command to back up all time files, redo log files, control files, initialize parameter files
Sql>cp
(3) Restart Oracle database
Sql>startup

Startup Nomount--load parameter file
ALTER DATABASE Mount
Recover DataFile ' E:\app\oradata\orcl\FIND. DBF ';
ALTER DATABASE Open

Third, hot backup
A hot backup is a method of backing up a database using the Archivelog mode mode in the case of a database run.
Hot backup requires the database to operate in Archivelog mode and requires a large amount of file space.
1. Check the database schema:
Sqlplus/nolog
Conn/as SYSDBA
Archive log list (to see if the database is in archive mode)

If it is non-archived, modify the database archiving mode.
Shutdown immediate; --Close the database immediately
startup Mount; --Start the database to Mount state
ALTER DATABASE noarchivelog;--set non-archive mode
ALTER DATABASE archivelog;--SET archive mode
ALTER DATABASE open;--Open the databases
Alter system set LOG_ARCHIVE_START=TRUE Scope=spfile; --Set up the database as an automatic archive
Alter tablespace Ts_find begin backup; --Turn on backup
Host copy F:\app\oracle\oradata\orcl\FIND. DBF E:\FIND. DBF--Copy files (including data files and control files)
Alter tablespace Ts_find end backup; --End Backup

--BACKUP Log
SELECT * from V$backup;

Four, RMAN
1. Start the Log archive mode:
sql> ALTER DATABASE archivelog;
2. Manually create the operating system directory: E:\rman\
3. Backup statement
Run {
Configure Retention Policy to recovery window of 2 days;
Configure CONTR Olfile autobackup on;
Configure Controlfile autobackup format for device type disk to ' e:/rman/%f ';
Allocate channel CH1 device type disk format ' e:/rman/data_%t_%u ';
Backup Database Skip inaccessible Filesperset
Plus archivelog filesperset
Delete all input;
Release Channel CH1;
}
Allocate channel for maintenance device type disk;
Crosscheck backupset;
Delete noprompt obsolete;

Description:
by Configure retention policy to recovery window of 7 days, set the number of days for the backup to be retained, and if it is a "full backup every day", the backup is retained for 2 day.
Pass Crosscheck backupset; Check that the backup is out of date.
Delete noprompt obsolete by deleting the expired backup.

4. Building tables, inserting data
CREATE TABLE Backup_sales
(
PRODUCT_ID Number (10),
Sales_date date,
Sales_cost number (10,2),
Status Varchar2 (20)
);
INSERT into backup_sales values (1,sysdate-10,18.23, ' inactive ');
Commit

5. Enable Rman to do a full library backup
Run the BACKUP statement for step 3rd
As seen from the log file:
Control File and spfile:e:\rman\c-1275904369-20110429-00
Data file: E:\RMAN\DATA_20110429_1DMB01T6_1_1
Log files: E:\RMAN\DATA_20110429_1CMB01T2_1_1 and E:\RMAN\DATA_20110429_1EMB022O_1_1
The backup check is complete.
6. Re-insert Data
INSERT into backup_sales values (2,sysdate-5,18, ' inactive ');
Commit

--Toggle log (manually triggering checkpoints)
Connect Sys/[email protected] as SYSDBA;
alter system switch logfile;

--Continue inserting data
INSERT into backup_sales values (3,sysdate-3,88.23, ' inactive ');
Commit
INSERT into backup_sales values (4,sysdate-1,8.23, ' inactive ');

--Full library recovery
1. Connect Rman
Rman Target/
2. Start the database to the load state
rman> shutdown immediate;
rman> startup Mount;
3. Perform repair and restore operations
rman> Restore Database;
rman> Recover database;
4. Open the database (recovery completes exiting Rman)
rman> ALTER DATABASE open;
Rman> exit;
5. Check
Sql> select * from Backup_sales;

Three data is complete, the database is restored when the log file is enabled to restore the data.
Incremental backups are now redundant, and checkpoints do not need to be manually triggered by the user.
Oracle Archives The log file once before the backup, and the log file is archived once after the backup, which is why two log files were generated at backup time.

Oracle_ Advanced Features (10) Backup recovery