Oracle Database Export and Import and execution

Sqlplus lnadc/lnadc@ZXIN_10.130.83.48

Export :( 1) exp mobileadc/mobileadc@ZXIN_10.130.83.48
(2) Set Buffering
(3) set the file name (full path)
Import: (1) Open PL/SQL, Click Tools-Export user objects, select all objects in the list, enter the Save path, and click Export;
Export the SQL statement of the entire object to view the tablespace and user name involved in this database.
(2) create enough tablespace with the same name on the local machine and specify the corresponding user name (the password can be changed)
(3) imp mobileadc/mobileadc@ZXIN_10.130.83.48

Export some tables:
(1) exp mobileadc/mobileadc@ZXIN_10.130.83.48 tables = mobileadc. Table name file = table name. dmp
Import some tables:
(1) imp mobileadc/mobileadc@ZXIN_10.130.83.48 tables = mobileadc. Table name file = table name. dmp ignore = y
Note: Ignore = y is generally used when tables exist.
****************
The following are two documents:ArticleFor reference
======================
The following is a detailed command:

Oracle databases have two backup methods. The first type is physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), this method requires a large number of external storage devices, such as tape libraries. The second type of backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-back mode, which is not only simple, in addition, external storage devices are not required.

Database logical backup method

There are three logical backup modes for Oracle databases: Table backup, user backup, and full backup.

Table mode

Back up the specified object (table) in a user mode ). Business databases usually adopt this backup method.

To back up a local file, run the following command:

Exp icdmain/ICD rows = y indexes = n compress = n buffer = 65536
Feedback= 100000 volsize = 0
File = exp_icdmain_csd_yyyymmdd.dmp
Log = exp_icdmain_csd_yyyymmdd.log
Tables = icdmain. commoninformation, icdmain. serviceinfo, icdmain. dealinfo

If you back up the data directly to a tape device, run the following command:
Exp icdmain/ICD rows = y indexes = n compress = n buffer = 65536
Feedback= 100000 volsize = 0
File =/dev/rmt0
Log = exp_icdmain_csd_yyyymmdd.log
Tables = icdmain. commoninformation, icdmain. serviceinfo, icdmain. dealinfo

Note: If the disk space permits, back up the data to the local server and copy the data to the tape. For speed considerations, do not back up data directly to tape devices.

User Mode

Back up all objects in a user mode. Business databases usually adopt this backup method.
To back up a local file, run the following command:
Exp icdmain/ICD owner = icdmain rows = y indexes = n compress = n buffer = 65536
Feedback= 100000 volsize = 0
File = exp_icdmain_yyyymmdd.dmp
Log = exp_icdmain_yyyymmdd.log
If you back up the data directly to a tape device, run the following command:
Exp icdmain/ICD owner = icdmain rows = y indexes = n compress = n buffer = 65536
Feedback= 100000 volsize = 0
File =/dev/rmt0
Log = exp_icdmain_yyyymmdd.log
Note: If the disk has space, we recommend that you back up the disk and then copy it to the tape. If the database data volume is small, backup can be performed in this way.

Full Mode

Back up the complete database. Business databases do not adopt this backup method. The backup command is:
Exp icdmain/ICD rows = y indexes = n compress = n buffer = 65536
Feedback= 100000 volsize = 0 full = y
File = exp_fulldb_yyyymmdd.dmp (the tape device is/dev/rmt0)
Log = exp_fulldb_yyyymmdd.log
For database backup, Incremental backup is recommended, that is, only the data changed since the last backup is backed up. Incremental Backup command:
Exp icdmain/ICD rows = y indexes = n compress = n buffer = 65536
Feedback = 100000 volsize = 0 full = y inctype = Incremental
File = exp_fulldb_yyyymmdd.dmp (the tape device is/dev/rmt0)
Log = exp_fulldb_yyyymmdd.log
Note: Incremental Backup must meet the following requirements:
1. It is only valid for full database backup and requires the full = y parameter for the first time. The inctype = Incremental parameter will be required later.
2. You must have an exp_full_database system role.
3. Database Backup can be used if the traffic volume is small.
4. If the disk has space, we recommend that you back up it to the disk and back up it to the tape.

Business Database Backup methods and cycles

Run the catexp. SQL file under the Sys user before backing up with exp (if this file has been run before, do not execute this script ).
The backup command cannot be executed on the client unless otherwise specified.
For the BACKUP command, refer to the BACKUP command in Table mode.
Back up disk files to tape
If you back up a file to a local disk, You need to dump it to the tape device.
1. To view the tape devices configured on the host, run the following command:
Lsdev-CC tape
The result is as follows:
Rmt0 available 30-58-00-4mm SCSI tape drive
Rmt1 defined 30-58-00-4mm SCSI tape drive
The device indicating available is an available tape device.
2. To view the tape storage content, run the following command:
Tar-tvf/dev/rmt0
The result is as follows:
-RW-r -- 300 400 8089600 Jan 11 14:33:57 2001 exp_icdmain_20010111.dmp
If the following content is displayed, it indicates that the backup data stored on the tape is backed up directly from the database to the tape, rather than from the local disk to the backup file of the tape, therefore, the operating system cannot be identified.
Tar: 0511-193 An error occurred while reading from the media.
There is an input or output error.
Or
Tar: 0511-169 A directory checksum error on media;-267331077 not equal
2
5626.
3. For new tapes or tapes without retaining existing data, run the following command:
Tar-CVF/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: A. This command will overwrite the existing data on the tape unconditionally.
B. The file name cannot contain path information, such as/backup/exp_icdmain_yyyymmdd.dmp.
4. Use the following command to retain existing data on tapes:
Tar-RVF/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Note: This command appends the file exp_icdmain_yyyymmdd.dmp to the end of the tape and does not overwrite the existing data.
Note: If the backup is directly backed up from the database to the tape, you cannot append or copy any other files to the tape. Otherwise, the backup data becomes invalid.
5. to copy the backup file dumped to the tape to the local hard disk, run the following command:
A. Copy all files on the tape to the current directory of the local hard disk.
Tar-xvf/dev/rmt0
B. Copy the specified file on the tape to the current directory of the local hard disk.
Tar-xvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp
Backup schedule
Because the backup has a great impact on system I/O, we recommend that you back up the data after.

Recovery of the Oracle version of the Business Database

The recovery plan should be determined based on the backup plan. Because the business database adopts a combination of table backup and user backup, the recovery of the business database should adopt a combination of table recovery and user recovery based on the actual situation.

Recovery Plan

The logical recovery of a database is divided into three modes: Table recovery, user recovery, and full recovery.

Table mode

This method restores data backed up in Table mode.
A. Restore all the backup data
To restore data from a local file, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
File = exp_icdmain_cs
D_yyyymmdd.dmp
Log = imp_icdmain_csd_yyyymmdd.log
To recover from a tape device, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file =/dev/rmt0
Log = imp_icdmain_csd_yyyymmdd.log
B. Restore the specified table in the backup data
To restore data from a local file, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
File = exp_icdmain_cs
D_yyyymmdd.dmp
Log = imp_icdmain_csd_yyyymmdd.log
Tables = commoninformation, serviceinfo
To recover from a tape device, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
File =/dev/rmt0
Log = imp_icdmain_csd_yyyymmdd.log
Tables = commoninformation, serviceinfo

User Mode

This method will be restored Based on the Data backed up in user mode.
A. Restore all the backup data
To restore data from a local file, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
File = exp_icdmain_yy
Yymmdd. dmp
Log = imp_icdmain_yyyymmdd.log
To recover from a tape device, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file =/dev/rmt0
Log = imp_icdmain_yyyymmdd.log
B. Restore the specified table in the backup data
To restore data from a local file, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0
File = exp_icdmain_yy
Yymmdd. dmp
Log = imp_icdmain_yyyymmdd.log
Tables = commoninformation, serviceinfo
To recover from a tape device, run the following command:
IMP icdmain/ICD fromuser = icdmain touser = icdmain rows = y indexes = N
Commit = y buffer = 65536 feedback = 100000 ignore = n volsize = 0 file =/dev/rmt0
Log = imp_icdmain_yyyymmdd.log
Tables = commoninformation, serviceinfo

Full Mode

If the backup mode is full, use the following restoration methods:
To restore data from a local file, run the following command:
IMP system/manager rows = y indexes = ncommit = y buffer = 65536
Feedback = 100000 ignore = y volsize = 0 full = y
File = exp_icdmain_yyyymmdd.dmp
Log = imp_icdmain_yyyymmdd.log
To recover from a tape device, run the following command:
IMP system/manager rows = y indexes = ncommit = y buffer = 65536
Feedback = 100000 ignore = y volsize = 0 full = y
File =/dev/rmt0
Log = imp_icdmain_yyyymmdd.log

Parameter description

1. Ignore Parameters
When Oracle recovers a table during data recovery, the table already exists, and you need to determine how to operate according to the ignore parameter settings.
If ignore = Y, Oracle directly inserts data into the table without executing the create table statement. If the inserted record violates the constraints, such as the primary key constraint, the error record is not inserted, however, valid records are added to the table.
If ignore = N, Oracle does not execute the create table statement and does not insert data into the table. Instead, it ignores the error of the table and continues to restore the next table.
2. Indexes Parameters
During data restoration, if indexes = N, the indexes on the table will not be restored, but the unique index corresponding to the primary key will be restored unconditionally to ensure data integrity.

Character Set Conversion

For a single-byte character set (such as us7ascii), the database is automatically converted to the character set of the session (the nls_lang parameter) during restoration. For a multi-byte character set (such as zhs16cgb231280, the character set should be the same as possible (to avoid conversion). To convert, the character set of the target database should be the superset of the output database character set.

Recovery Method

The business database adopts the table recovery solution. Run the catexp. SQL file under the Sys user before restoring it with IMP (if the file has been run before, do not execute this script), and then run the following command:
IMP icdmain/ICD file = File Name Log = Log File Name rows = y
Commit = y buffer = y ignore = y tables = table name
NOTE: For the table name to be restored, refer to the backup table name.
. Data is accumulated on the basis of the original table.
. No special instructions. You cannot execute recovery commands on the client.

The following example shows how to export all the tables of a database user to another database user.
Exp userid = system/manager owner = username1 file = expfile. dmp
IMP userid = system/manager fromuser = username1 touser = username2 ignore = y file = expfile. dmp

The following example shows how to export all the tables of a database user to another database user.

Exp userid = system/manager owner = username1 file = expfile. dmp
IMP userid = system/manager fromuser = username1 touser = username2 ignore = y file = expfile. dmp

Oracle databases have two backup methods. The first type is physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), this method requires a large number of external storage devices, such as tape libraries. The second type of backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-back mode, which is not only simple, in addition, external storage devices are not required.

==========================================================
Below is the name parameter function:

Oracle exp/IMP Parameters
16:17:41/personal classification: elementary

Exp export options
Keyword content

Userid: username/password of the account that runs the Export command

Buffeer is used to obtain the buffer size of data rows.

File Export the name of the dump file

Whether the compress export should compress segments with fragments into a range, which will affect the storage clause

When exporting grants, do you want to export the authorization on the database object?

Does indexes need to export table indexes?

Whether the rows should export rows. If it is 'n', only the DDL of the database object is generated in the export file.

Whether consstraints exports the old conventions of the table

If full is set to 'y', an entire database export operation will be executed.

The owner needs to export a series of database accounts, and then execute the user export operations for these accounts

Tables to be exported. Execute the table export operations for these tables.

The length (in bytes) of the recordlength dump export file log, which is usually used by default, unless the export file is transferred in two different operating systems

The type of the export operation that inctype is executing (valid values include complete (default value), cumulative and ivcremental)

A record in the incremental export type indicates whether logs are stored in the table exported from the data dictionary.

The name of the parameter file to be exported. This file will contain entries for all parameters

Analyze indicates whether to write the statistics of the migrated object to the export dump file.

Consistent indicates whether to retain consistent read replication of all exported objects

Name of the file to be written in the log export log

MLS indicates whether to export the MLS label

Mls_label specifies the MLS label format

IMP import options
Keyword content

Userid: username/password of the account that runs the import command

Buffeer is used to obtain the buffer size of data rows.

Name of the File Import dump file

Show specifies whether the file content is displayed, not executed.

Egnore indicates whether errors are ignored when the 'create' command is executed. If the object being imported already exists, use this parameter.

If full is set to 'y', an entire database import operation will be executed.

Fromuser has parameters only when full = n. It is a series of database accounts whose objects should be read from the export dump file

Touser: A series of database accounts. The objects deducted from the exported dump file will be imported to these accounts.

When importing grants, do you want to import the authorization on the database object?

Does indexes need to import table indexes?

Whether the rows should import rows. If it is 'n', execute the DDL of the database object in the import file.

Tables

The length (in bytes) of the recordlength dump import file log, which is usually used by default, unless the import file is transferred in two different operating systems

The type of the import operation that inctype is executing (valid values include complete (default value), cumulative and ivcremental)

Oracle exp/IMP Parameters

The name of the parameter file to be passed to the import. This file will contain entries for all parameters

Analyze
Indicates whether to write the statistics of the migrated object into the import dump file.

Name of the file to be written into the log import log

Indicates whether to import the MLS label.

Indexfile does not execute the Import action. Only the text for index creation is generated.