12. Logical backup and Recovery of Oracle Database (table)

First, Introduction
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.

Second, backup (export)
Export is divided into export table, export scheme, export 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, connection string to perform the export operation
Tables: Used to specify the table to perform the export operation
Owner: Specifies the scenario to perform the export operation
Full=y: Used to specify the database to perform the export operation
Inctype: Used to specify the increment type to perform the export operation
Rows: Used to specify whether the export operation is to export data from the table
File: Used to specify the export file name

Note: Special instructions--when importing and exporting, go to the bin directory of the Oracle directory.

1), Export table
1. Export your own tables
Exp userid=scott/[email protected] tables= (EMP) FILE=D:\EMP.DMP--Export a single table
Exp userid=scott/[email protected] tables= (emp,dept) file=d:\emp.dmp--Export multiple tables
eg
C:\USERS\JIQINLIN>CD D:\dev\oracle\product\10.2.0\db_1\bin
C:\users\jiqinlin>d:
D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=scott/[email protected] tables= (EMP) file=d:\emp.dmp

2. Export tables for other scenarios
If a user wants to export a table for another scenario, they need DBA authority or exp_full_database permissions, such as system to export Scott's table
D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=system/[email protected] tables= (scott.emp) file=d:\ Emp.emp
D:\dev\oracle\product\10.2.0\db_1\bin>exp userid=system/[email protected] tables= (scott.emp, scott.dept) file=d:\emp.emp

3. Structure of the exported table
Exp userid=scott/[email protected] tables= (EMP) file=d:\emp.dmp rows=n

4. Using the direct export method
Exp userid=scott/[email protected]tables= (EMP) file=d:\emp.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.
At this time the character set of the database needs to be exactly the same as the client character set, otherwise it will error ...

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

3), export the 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

Third, restore (import)
Import is to import the objects and data in the file into the database using the tool import, but the file to be used by the import must be the exported file. 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

1) Import Table
1. Import your own tables
imp userid=scott/ [email protected]  tables= ( EMP) FILE=D:\XX.DMP
2. Importing a table to another user
requires that the user has DBA authority, or imp_full_database
Imp userid=system/[email protected]  tables= (EMP) file=d:\ Xx.dmp Touser=scott
3. Import the structure of a 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

2) Import Scenario
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/[email protected] file=d:\xxx.dmp
2. Import other Scenarios
Require the user to have DBA authority
Imp userid=system/[email protected] file=d:\xxx.dmp Fromuser=system Touser=scott

3) Import database (equivalent to database migration)
By default, when you import a database, all object structures and data are imported, as in the following cases:
Imp userid=system/[email protected] full=y file=d:\xxx.dmp

12. Logical backup and Recovery of Oracle Database (table)