Logical backup of the database

Logical backup and Recovery of database (table)

Introduced
A logical backup is the process of exporting the structure and data of a data object to a file using the tools export.
Logical recovery refers to the process of importing a data object into a database using a backup file when the database object is damaged by mistake.
Physical backups can be performed in the open state of the database or after the database is closed, but logical backups and restores can only be performed in open state.

Export
The export is divided into: Export table, export the scheme, export the database three kinds of ways.
The export is done using the EXP command, which has the following common options:
UserID: Used to specify the user name, password, and connection string to perform the export operation the Network Service command. Specify who will perform the export operation
Tables: Specifies which table to export the table to which the export operation is performed
Owner: Specifies which scenario to export the scenario for export operation
Full=y: Used to specify which database is exported from the database that performs the export operation
Inctype: Used to specify the increment type for an incremental type export operation that performs an export operation
Rows: Used to specify whether the export operation is to export data in the table to export data in the table
File: Used to specify the export file name                                                              exported to a file

  Export Table
1. Export your own table
Exp userid=scott/ [email protected] tables= (EMP, Dept) File=d:\e1.dmp
2. Export tables for other scenarios
If a user wants to export a table for another scenario, the DBA or exp_full_database permission is required, such as system to export Scott's table
E:\ Oracle\ora92\bin>exp userid=system/ [Email protected] Tables= (scott.emp) file=d:\e2.emp
Special Note: When importing and exporting, go to the bin directory of the Oracle directory.   I want to set up the environment variables.
3. Export the structure of the table
Exp userid=scott/[email  Protected] tables= (EMP) file=d:\e3.dmp? Rows=n
4. Using the direct Export Method
Exp Userid=scott/[ Email protected] tables= (EMP) file=d:\e4.dmp? Direct=y
This is faster than the default normal way, and you can consider using this method when the amount of data is large.
This requires the database character set to be exactly the same as the client character set, otherwise it will error ...

Export scenarios
Export scenarios refer to exporting a scenario or all objects in multiple scenarios (tables, indexes, constraints ...) using the Export tool. ) and data. Co-exist in the file.
1. Export your own solution
Exp userid=scott/[email protected] Owner=scott file=d:\scott.dmp
2. Export other programs
If users want to export other scenarios, they need DBA authority or exp_full_database permissions, such as system users can export any scheme
Exp userid=system/[email protected] owner= (System,scott) file=d:\system.dmp

Export Database
Exporting a database refers to exporting the objects and data in all databases using export, requiring that user to have DBA authority or exp_full_database permissions
Incremental backup (the benefit is that the second backup is much faster after the first backup)
Exp userid=system/[email protected] full=y inctype=complete file=d:\all.dmp

Import
Introduced
Import is to import objects and data from files into the database using the tool import
, but the file to be used by the import must be a file exported by export.
Similar to export, import is also divided into import table, import scheme, import database three ways.
The options commonly used by IMP are
UserID: Used to specify the user name, password, connection string to perform the import operation
Tables: Used to specify the table to perform the import operation
Formuser: Used to specify the source user
Touser: Used to specify the target user
File: To specify the import file name
Full=y: Used to specify execution to import the entire file
Inctype: Used to specify the increment type to perform the import operation
Rows: Specify whether to import table rows (data)
Ignore: If the table exists, only the data is imported
Import Table
1. Import your own tables
Imp userid=scott/[email protected]tables= (EMP) File=d:\xx.dmp This doesn't have to write Fromuser touser default is their own
2. Importing tables to other users
Requires the user to have DBA authority, or Imp_full_database
Imp userid=system/[email protected]tables= (EMP) file=d:\xx.dmp Touser=scott This is not written fromuser the default is their own
3. Structure of the Import table
Import only the structure of a table without importing data
Imp userid=scott/[email protected]tables= (EMP) file=d:\xx.dmp? Rows=n
4. Import data
If an object (such as a table) already exists, you can import only the data for the table
Imp userid=scott/[email protected]tables= (EMP) file=d:\xx.dmp? Ignore=y
Import Scenarios
Import scenarios refer to importing objects and data from files into one or more scenarios using the Import tool. If you are importing other scenarios, ask the user to have DBA authority, or Imp_full_database
1. Import your own scenarios
Imp Userid=scott/tiger file=d:\xxx.dmp
2. Import other Scenarios
Require the user to have DBA authority
Imp Userid=system/manager file=d:\xxx.dmp Fromuser=system Touser=scott
Import Database
By default, when you import a database, all object structures and data are imported, as in the following cases:
Imp Userid=system/manager full=y file=d:\xxx.dmp

View the specific command mode
$exp help=y
$imp help=y
First, export tool exp
1 It is the next executable file of the operating system
Storage directory/oracle_home/bin
The EXP export tool compresses the data backup in the database into a binary system file. Can be migrated between different OS

It has three modes:               
A.      User mode: Export all objects of the user and the data in the object;               
b.     Table mode: Export all tables for a user or a specified table;                
c.     Entire database: Exports all objects in the database.

Two, import tool imp   It is the next executable file of the operating system  
Directory/oracle_home/bin 
Imp Import Tool imports binary system files formed by exp into the database.        
It has three modes:                 
a.     User mode: Export all objects of the user and the data in the object;               
b.     Table mode: Export all tables for the user or the specified table;              
c.      Entire database: Exports all objects in the database.                      
only users with Imp_full_database and DBA authority can do the entire database import  

Import and Export (IMP/EXP) all data under a user table Note: You must be logged on with an Oracle user
1. Export all objects of a user exp testa/[email protected] owner= Testa file=/home/oracle/testa.dmp
If you have multiple users, you can separate them with commas such as

Exp testa/[email protected]Owner= (TESTA,TESTB) file=/home/oracle/testa.dmp
2. Import data for a user imp testa/[email protected]Fromuser =testa touser =testa file=/home/oracle/testa.dmp Note:
Note: If a table already exists at the time of import and then it is an error, the table will not be imported.
Add Ignore=y to the back.
To ensure that the name of the imported user's tablespace is the same as the exported tablespace name
Before importing, it is best to ensure that there are no duplicate objects and data in the user, or the import will not succeed.
3. Export and import a table under a user
Exp test/[email protected]File=/home/oracle/desktop/t_area.dmp Log=t_area.log Tables=t_area
Export:release 11.2.0.1.0-production on Mon 7 15:03:47 Copyright (c)
1982, and/or, Oracle, and its affiliates. All rights reserved.
Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit
Production with the partitioning, OLAP, Data Mining and Real application
Testing options Export done in ZHS16GBK character set and Al16utf16 NCHAR
Character Set server uses Al32utf8 character set (possible charset conversion)
About to export specified tables via conventional Path .... . Exporting table
T_area rows exported exp-00091:exporting questionable statistics. Export
Terminated successfully with warnings.
This prompt appears because the character set of the terminal is not the same as the character set of the database.
The workaround is to set the character set of the terminal to the same as the database, so that after
Tips for databases change from English to Chinese

Resolution steps: Oracle Identity login Sqlplus/as SYSDBA
Sql>?select? Property_value?from?database_properties?where?property_name?like? ' %set% ';?
Property_value?
--------------------------------------------------------------------------------? Al32utf8? Al16utf16?
Then exit Sqlplus and go back to the Linux terminal?
Echo $NLS _lang?
American_america. ZHS16GBK?
? export? nls_lang= "Simplified? Chinese_china. Al32utf8 "?
? exp?test/[email protected]? File=t_area.dmp?log=t_area.log?tables=t_area?
Export:? release?11.2.0.1.0?-? Production?on, Monday? May? 7?15:04:44?2012? Copyright? (c) 1982,?2009,? Oracle?and/or?its?affiliates.?? All?rights?reserved.?
Connect to:? Oracle? Database?11g? Enterprise? Edition? release?11.2.0.1.0?-? 64bit? Production? With?the? Partitioning, huh? Olap? Data? Mining?and? Real? Application? Testing?options? already exported? Al32utf8? Character set and? Al16utf16? NCHAR? The specified table is about to be exported by the regular path ...?
.?.? Exporting Table?????????????????????????? T_area exported the???????? 31, okay?
Successful termination of export, no warning appears.
?
4. Import a table under a user?
? imp?test/[email protected]?? File=/home/oracle/desktop/t_area.dmp?tables=t_area?commit=y?ignore=y?
Note Before importing, it is best to ensure that the user does not have related tables and data?
5, how to use SYS user to perform exp operation? Method 1?
Exp?\ ' sys/ab123489?as?sysdba\ '? File=/home/oracle/s.dmp?full=y?compress=y? Method 2?
Exp?\ "sys/[email protected]? as?sysdba\ "? file=/home/oracle/u.dmp?full=y?compress=y?
Method 3?
? exp. "' /@ instance name? as?sysdba ' "? Enter?array?fetch?buffer?size:?4096?>
Export?file:?expdat.dmp?>?
(1) E (ntire?database),? (2) U (sers),? or? (3) T (ables):? (2) U?>??? 1? Export?grants? (yes/no):? yes?>? Export?table?data? (yes/no):? yes?>? Compress?extents? (yes/no):? yes?>?
?
8. Solve the problem of using oracle11g to export (exp/imp) empty table?
There is a new feature in 11G, when the table has no data, do not allocate segment to save space, so there is no export data?? What are the common methods now?
?????
Imp-00008:?unrecognized?statement?in?the?export?file:?????
Imp-00008:?unrecognized?statement?in?the?export?file:?
Imp-00008:?unrecognized?statement?in?the?export?file: Error when importing data with IMP?
Workaround?
In ORACLE?11G, exp cannot export empty tables by default. It is troublesome to use traditional exp,imp for heterogeneous platform database migration. However, you can use EXPDP, IMPDP to migrate.???
Migrating a 64-bit windows?2003 ORACLE11GR2 database to 64-bit Linux? RedHat? Enterprise?5, you can use EXPDP, IMPDP to migrate data.???
such as: Linux? For a server, Linux for B server, database user for test, a server data migration to B server???
In a server operation? 1, create a directory?
Sql>?create?directory?expdp_dir?as? ' /home/oracle/expdp_dir ';?? One server is created once? 2? Give the exported user permission?
sql>?grant?read,write?on?directory?expdp_dir?to?test;//a single server once created

3, create the directory in the Linux directory/home/oracle/expdp_dir mkdir-p/home/oracle/expdp_dir//A server just create once 4, in the Command Window export: EXPDP test/test DIRECT Ory=expdp_dir dumpfile=test.dmp Logfile=testexpdp.log//Every time to operate in the B server operation: 5, sql> Create directory Impdp_dir as '/home /oracle/impdp_dir ';//a server is created once sql> grant Read,write on directory Impdp_dir to test;//a server once created 6, need to have/hom in the system E/oracle/impdp_dir directory, in the Impdp_dir directory must have read and Write permissions (chmod 777 Impdp_dir) 7, with FTP to upload a server exported data to the/home/oracle/impdp_dir directory of B server The server name of B server is configured in a server, import data in a server 8, import in command window: IMPDP test/[email protected]_database directory=impdp_dir dumpfile= Test.dmp Logfile=testimpdp.log (note here case, if TEST.DMP is uppercase in Linux, it must be changed to uppercase.) Linux is case sensitive)

Issues that may occur when importing IMP
1 database object already exists
In general, you should completely delete the table, sequence, Function procedure, trigger from the target data before importing the data.
The database object already exists, and the import fails by the default imp parameter.
If the parameter ignore=y is used, the data contents inside the exp file are imported
If the table has constraints for unique keywords, the condition will not be imported
If the table does not have a constraint on a unique keyword, it causes the record to repeat

2 database objects have a primary foreign KEY constraint
Data imports fail when the Han Master foreign key constraint is not met.
Workaround: Import the primary table first, and then import the saved tables.
Disable the primary foreign KEY constraint for the target import object, after importing the data, enable them

3 Insufficient permissions
If you want to import a user's data into a B user, a needs to have imp_full_database
Permissions.

4 Storage allocation fails when importing large tables (greater than 80m)
The default exp, Compress=y, is to compress all the data on a block of data.
On import, if there is no contiguous large block of data, the import fails.
When exporting large tables of more than 80m, remember that compress=n does not cause this error.

5IMP and exp use different character sets
If the character set is different, the import will fail. You can change the UNIX environment variable or NT registration table
Nls_lang related information.
Import it and change it back.

6IMP and EXP versions are not compatible
IMP can successfully import the lower version of the exp-generated file and cannot import the high-version exp-generated file