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 tool export, which refers to a logical recovery
Importing data objects into a database using a backup file when the database object is damaged by mistake
The process.
Physical backups can be performed in the open state of the database or after the database is closed, but logical backup and recovery
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, 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
Exporting tables
1. Export your own tables

exp UserID=Scott/Tiger@myoral Tables=file= d:\e1.dmp

2. Export tables for other scenarios
If a user wants to export a table for another scenario, it requires DBA authority or exp_full_database permissions, such as the system
You can export Scott's table.

E:\oracle\ora92\bin>exp userid=system/manager@myoral Tables=  file=d:\e2.emp

Special note: When importing and exporting, go to the bin directory of the Oracle directory.
3. Structure of the exported table

exp UserID=Scott/Tiger@accp Tables=file= d:\e3.dmp Rows=n

4. Using the direct export method

exp UserID=Scott/Tiger@accp Tables=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.
At this time the character set of the database needs 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/Tiger@myorcl owner=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, such as System
The user can export any scheme

exp UserID=system/Manager@myorcl owner=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
are exp_full_database permissions
Incremental backup (the benefit is that the second backup is much faster after the first backup)

exp UserID=system/manager@myorclFull=y inctype= file =d:\all. DMP

Import

Introduced
Import is to import the objects and data in the file into the database using the tool import, but import the files to be used
The exported file must be the 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/Tiger@myorcl Tables=file=d:\xx.dmp

2. Importing tables to other users
Requires the user to have DBA authority, or Imp_full_database

IMP userid=system/Tiger@myorcl Tables=file=d:\xx.dmp Touser=Scott

3. Structure of the Import table
Import only the structure of a table without importing data

IMP userid=Scott/Tiger@myorcl Tables=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/Tiger@myorcl Tables=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 want to
Import other scenarios that require the user to have DBA authority, or Imp_full_database
1. Import your own scenarios

IMP userid=Scott/file=d:\xxx.dmp

2. Import other Scenarios
Require the user to have DBA authority

IMP userid=system/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/Full=file=d:\xxx.dmp

Logical backup and Recovery of database (table)