Oracle data import and Export ____oracle

Import Export of data Description:

Objects targeted: Import and export of data the role involved is primarily the engineering implementation personnel.

The problem to be solved is to import the required data from one database into another. 1 Tools mode 1.1 tool description

1. The use of Plsql developer tools is mainly to facilitate the operation of the project personnel database, such as import, export database objects.

2. The use of Toad tools facilitates project operators to manipulate databases and to import and export data more efficiently.

3. Using both of these tools, the implementation personnel do not need to operate the database at the command line, convenient and error-prone. 1.1.1 Preparation work

1. Tool software Plsql Developer, TOAD.

2. Record Data source Oracle database information (IP, port, global database name), recorded as source_db;

3. Scheme (Source_schm), tablespace (default table space SOURCE _space, Index Table Space index _space);

4. Account for data export (recorded as EXPUSR);

5. Oracle Database Information (IP, port, global database name) recording data target, recorded as dest_db;

6. Scheme (Dest_schm), tablespace (default table Space Dest_space, Index Table Space desindex _space);

7. Information for data import account (recorded as IMPUSR).

8. Record the database objects that need to be exported from the data source, recorded as db_objects;

9. If you need to filter the export data, you should record the filter conditions, recorded as Data_filter.

1.2 Database objects 1.2.1 Export database objects

1. Start the Pl/sql Developer, the user expusr login to the source Oracle database source_db;

2. Select Export User Objects from Pl/sql developer Menu tools;

3. Export Database objects

A, in the user's Drop-down box, select the <SOURCE_SCHM> that you want to export data to.

B, select Properties Single File,include Owner, include Storage.

C, select the database object that needs to be exported;

D, click the Export button to export the database object, which will generate a file with SQL extension (recorded as Export_file). 1.1.1. Import Database Objects

1. Modify SQL Script

If the scheme of the source data is inconsistent with the scheme of the target database, or if the table space of the source database is inconsistent with the table space of the target database, you need to modify the exported export_file.

CREATE table <source_schm>. Action_info modified to

CREATE table <dest_schm>. Action_info

Tablespace <source _space> (source default table space)--Change to destination default tablespace (<DEST_SPACE>)

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64K

Minextents 1

Maxextents Unlimited

);

Create INDEX Ak_cdr_d_detail on Cdr_d_detail (User_number)

Tablespace <index _space> (source index tablespace)--à to Destination Index table space (<desindex _space>)

Pctfree 10

Initrans 2

Maxtrans 255

Storage

(

Initial 64K

Minextents 1

Maxextents Unlimited

);

Using index

Tablespace <index _space> (source index tablespace)--à to Destination Index table space (<desindex _space>)

Pctfree 10

Initrans 2

Maxtrans 255

Storage

(

Initial 64K

Minextents 1

Maxextents Unlimited

);

2. Start Pl/sql Developer, IMPUSR login to target Oracle database dest_db;

3. From the Pl/sql developer menu, select Command Window under New.

Select the editor option to copy the contents of the Export_file file to the editor option box

Press F8 to import the database objects into the dest_schm.

1.3 Data Import Export

Use the Toad tool for data import and export. 1.3.1 Export Data

1. Start Toad to expusr login to the source database source_db;

2 Select Database->export->data Pump Export Wizard option in the menu

2. Select tables in the Export option

3. Select the Scheme:source_schm of the source database in the Drop-down box and select the data table you want to export.

4. If the data need to filter, then fill in the filter condition Data_filter.

5. Content dropdown box Select the data only option.

6. In directory where you select the data you want to export, Output file name (. dmp) specifies a file name for the exported data as Dump_file.

7. Click Finish to export data.

1.3.2 Import data

8. dest_db The Oracle database with user <IMPUSR> login target;

9. Select the database->import-> Data Pump Import Wizard option in the menu

Select entire DumpFile in the import option.

Source is the <DEST_SCHM> of the import target database for the <source_schm>,target of the originating database.

The Content dropdown box option selects data only.

Directory: Specifies the path to the import data file, and the Input file name dump_file The data file that you want to import.

13. Click Finish to import data.

1.4 Import Export Records

source_db	Source database Information
Source_schm	The user to export the source data to
SOURCE _space	Source Default table Space
INDEX _space	Source Index Table Space
Expusr	Data export Account
dest_db	Target Database Information
Dest_schm	Users of Target data import
Dest_space	Destination default table Space
Desindex _space	Target Index Table space
Impusr	Data import Account
Export_file	Exported SQL scripts
Dump_file	Exported data files
Db_objects	Exported Database objects
Data_filter	Filter conditions for exporting data

2 command-line mode 2.1 database object Import

1, open the Data object script, check the script is correct. Whether the table space matches and whether the index is built in the correct index space.

2, in the Command line window input data Object script: @ file path + filename, for example:

@e:/biao/tables.sql after importing the data object script, check to see if it is correct. Includes tables, indexes, stored procedures, views, and so on. Checks whether the number of objects matches.

2.2 Data export

To use EXPDP, you first create your directory in the database, such as setting up Dumptestdir under D disk, and giving the appropriate user Read,write permissions. Be aware of landing with a privileged user;

Sql>create directory Dumpdir as ' D:\dumptestdir ';

Sql>grant read,write on directory Dumpdir to Hunan;

1 the method of data export

To export table data from a database to a directory:

>EXPDP hunan/hunan@192.168.1.22 Tables (action_info) dumpfile=dumptest.dmp Directory=dumpdir Logfile=dumptest.log Connect=data_only 2.3 data import

1 Use IMPDP to create a directory in the database first such as under D disk to establish dumptestdir, and to the corresponding user read,write permissions. and copy the files to be imported in this directory, pay attention to the user with permission to log in;

2 Import the table data into the database (first of all, import the table structure into the database by the above method):

IMPDP kuang/kzp4715712@192.168.1.44 dumpfile= "Dumptestsj.dmp" Directory=dumpdir Remap_schema=hunan:kuang TABLESPACE=CDMADATA:CDMA content=data_only

some commonly used parameter description:

DumpFile: Exported file name (*)

DIRECTORY: Export file location (located on server side) (*)

Content: Export the contents contained in the file (default is: All, optional data_only/metadata_only)

FILESIZE: Specifies the export file size (in bytes).

Job_name: The name used by this export process to facilitate tracking queries (optional)

LOGFILE: Log file name (default: Export.log)

Include: Contains the specified type when exporting

(Example: Include=table_data,

Include=table: "Like ' tab% '"

Include=table: "Not like ' tab% '" ...)

EXCLUDE: Data types excluded when exporting (example: exclude=table:emp)

Full: Use when the entire library is exported (with all exp, the default is N)

Schema: Exporting all data under a schema

Tables: exporting by Table (here's the same method as exp)

Tablespace: Specifies a table space export.

Query: When exporting by table, qualify the export scope with conditional statements (query in EXP

3 Oracle Backup Recovery

3 3.1 based on Rman backup strategy

1

2 3.1.1 Rman introduction

Rman (Recovery Manager) is an important tool for DBAs to back up, restore, and restore Oracle databases, which can be used to back up and restore database files, archive logs, and control files, or to perform full or incomplete database recovery. Has the following characteristics:

1 Rman operation is relatively simple, a lot of work is done automatically

2 The RMAN tool can back up Oracle databases in a variety of ways, providing a lot of flexibility for backup and recovery methods

3) perform an incremental backup. RMAN has the ability to perform incremental backups and full backups. Incremental backups only back up content that has been modified since the last backup, which allows you to perform a full backup only one day a week, and an incremental backup for the other days, which improves the performance of the backup

4 provide a list of directory information. You can use the Rman list and the table command to query information stored in the catalog directory about backups, which provide an effective way to display information

5) Rman automatically determines the consistency of the block when making an online backup, and does not need to have the data file in a backup state

6) Rman can provide some critical information about the report

7 Easy to check whether the backup saved on the media available 3.2 backup strategy 3.2.1 Backup Marked

Keep the target database secure, minimize the number of database failures, so that the database to maintain the maximum availability;

When the database is unavoidable, the recovery time is minimized, so the recovery efficiency is the highest.

When the database fails, make sure that as little data is lost or not lost at all, so that the data is most recoverable.

3.2.2 Backup strategy preparation

1 How to back up:

With Rman's incremental backup (differential backup), multilevel backups are used to reduce the time required for recovery and reduce the time required for daily backups, while ensuring that the system is well restored.

2 where the backed-up data is stored:

The backed-up data should be saved offsite so that the security of the backup is guaranteed.

3) Backup time schedule:

Because of the significant impact on system I/O during backup, it is recommended that you do backup work after work.

4) Rman Backup considerations:

any database changes need to resynchronize the catalog directory or back up 3.2.3 A specific backup strategy

1) Configure the target database:

Determining the database as archive mode

Set flashback enabled as on

To set the SPFile parameter when the database is started

2) Configuring the Rman Database

3 Differential Incremental backup: Full standby Once a month, Sunday One 0 standby, Monday, two to do two-level backup, Wednesday to do one level, Thursday, five, six to do two-level backup.

Rman> Backup Database (fully prepared)

Rman> Backup incremental Level 0 database; (Sunday)

Rman> Backup incremental level 2 database; (Monday)

Rman> Backup incremental level 2 database; (Tuesday)

Rman> Backup incremental level 1 database; (Wednesday)

Rman> Backup incremental level 2 database; (Thursday)

Rman> Backup incremental level 2 database; (Friday)

Rman> Backup incremental level 2 database; (Saturday)

Note: The difference between full backup and level 0 backup for all data in the entire database can be incrementally backed up at level 0, but not all available.

A level 0 backup is a full backup of all data for the entire database

A Level 1 backup is a backup of only the data that has been modified since the level 0 backup

A level 2 backup only backs up data that has been modified since the level 1 backup

Note: Specific backup recovery steps See 1.3 3.3 backup steps

Backup target database and Rman database preparation:

(i) Target database

A) Modify the database archive status to view the status of the archive:

sql> archive log list;

Set to archive mode if the database is not in archive mode

(b) Enable Flashback DATABASE event logging so that you can recover quickly when inadvertently drop tables. To view the flashback enabled status:

Sql> select Dbid,name,flashback_on,current_scn from V$database;

If flashback on is not on, the following settings are made:

sql> shutdown Immediate

Sql> Startup Mount

Sql> ALTER DATABASE flashback on;

Sql> select Dbid,name,flashback_on,current_scn from V$database;

sql> ALTER DATABASE open;

C Use the SPFile parameter when the database is started, so that the spfile will be backed up with the control file.

To see if the SPFile parameter file is used:

Sql> Show Parameter SPFile

Value is null, the database is started with the Pfile file, and if there is a value, the SPFile is started, and if there is no value, the following setting is made:

Create SPFile parameter file

Sql> Create SPFile from Pfile

sql> shutdown Immediate

Sql> Startup

Sql> Show parameter SPFile;

(ii) Configuring the Rman Database

Create an Rman catalog, the following steps illustrate the process of establishing an Rman catalog in a database.

A. Create a separate table space for the directory

Sql>create tablespace rmantablespace datafile ' d:/rmantablespace.ora ' size 500m;

B. Creating an Rman user

Sql>create user RMAN identified by rman default tablespace rmantablespace temporary tablespace temp;

C. Granting of permission to Rman

Sql>grant Connect, resource, Recovery_catalog_owner to Rman;

D. Open Rman

$>rman

E. Connecting to the database

Rman>connect Catalog Rman/rman

F. Creating a recovery directory

Rman>create catalog Tablespace rmantablespace;

Register the target database, restore the directory after the successful creation, you can register the target database, the target database is the database that needs to be backed up, a recovery directory can register multiple target databases, the command to register the target database is:

$>rman Target Sys/kzp4715712@kuang

Rman>connect Catalog Rman/rman

Rman>register database;

Database registration is complete, you can use Rman for backup.

D View and modify the Rman backup configuration

Rman>show all;

View Rman Default backup parameters, changing according to actual requirements

Rman>configure Controlfile autobackup on;

Configuration parameter specifies that both the control file and the parameter file are backed up at the same time as any backup (default off)

(iii) Backup target database

A log switch to the target database to generate the latest archive for backup

Rman>sql ' alter system archive log current ';

b) Full-Library backup:

Rman>backup database;

Back up all data files on the target database, control file and parameter files, and archive files

C To view the backup information for the target database:

Rman>list Backup of database;

d) Incremental Backups:

Rman> Backup incremental level 0 database; (Sunday)

Rman> Backup incremental level 2 database; (Monday)

Rman> Backup incremental level 2 database; (Tuesday)

Rman> Backup incremental level 1 database; (Wednesday)

Rman> Backup incremental level 2 database; (Thursday)

Rman> Backup incremental level 2 database; (Friday)

Rman> Backup incremental level 2 database; (Saturday) 3.4 Recovery steps

(i) Full-Library recovery

A) connecting to the target server

>rman

Rman> Connect Target SYS/KZP4715712@ORCL

b) connecting the Rman server

Rman>connect Catalog Rman/rman

c) Assume the target server is corrupted

d) Start the target server

Rman>startup Nomount;

Rman>restore spfile from autobackup; repair parameter file

Rman>restore controlfile from autobackup; repair control file

Rman>restore database; repairing data files

Rman>alter database mount; boot to Mount state

Rman>recover database; to fix

Rman>alter db Open resetlogs log File Open database

(e) Final reconstruction of temporary table space

(ii) Time-based recovery

Time-based recovery restores a database to a point in the past, often used to recover lost data before it can be recovered to the deletion time after a misuse of the data is deleted.

Rman> run{

SQL ' alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

Set until time ' 2009-04-08:11:00:00 ';

Restore database;

Recover database;

ALTER DATABASE open resetlogs;

4 based on Emp/imp backup policy (oracle10g not supported)

Oracle Export/import (Export/import) database backup method: Export can be used to extract data from the database, the use of Import can be extracted from the data sent back to the Oracle database. 4.1 Simple Export data (export) and imported data (import)

Oracle supports three different types of output:

(a) Table mode (t), which exports the data from the specified table.

(ii) User mode (U), which will specify the user's all objects and data export.

(iii) whole-Library (full) to export all objects in the database.

The process of data import is the inverse process of data export, which imports the data file into the database and exports the database data to the data file respectively. 4.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. When making this export, the system does not require any questions to be answered. The export file name defaults to Export.dmp, and if you do not want your output file to be named Export.dmp, you must indicate the file name you want to use on the command line.

An incremental export consists of three types:

(i) "full" incremental export (Complete)

That is, back up three databases, such as:

Exp system/kzp4715712 Inctype=complete File=040731.dmp

(ii) "Incremental" incremental export

Back up data that was changed after the last backup, such as:

EXP system/kzp4715712 inctype=incremental file=040731.dmp

(iii) "Cumulative" incremental export

Cumulative export is the export of information that has changed in the database since the last "full" export. Like what:

Exp System/manager inctype=cumulative File=040731.dmp

The database administrator can schedule a backup schedule that is reasonably and efficiently accomplished with three different ways of exporting data.

For example, a database backup task can be arranged as follows: (the letter in parentheses indicates the backup file name)

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 the database is accidentally compromised in Sunday, the database administrator can follow the steps to reply to the database:

Step one: Use the command create database to regenerate the databases structure;

Step Two: Create an additional rollback that is large enough.

Step three: Full incremental import a:

Imp System/manager inctype=restore full=y file=a

Step Fourth: Cumulative incremental import E:

Imp System/manager inctype=restore full=y file=e

Fifth Step: Recent incremental import F:

Imp System/manager inctype=restore full=y file=f