Oracle database backup Restore (EXPDP/IMPDP) records

Recently the company switched the original database server. Not before the whole piece, but also a bunch of degrees Niang. After a constant groping, finally succeeded. Now it is hard to record this hardship, to facilitate their later access to the same time, to facilitate the students with similar needs reference.

We are switching total: ERP, LOS, WMS three systems.

These three systems are all in the same instance as a result of the previous construction. Differentiated by different tablespace and user.

This switch idea:

1) Back up and restore as an instance. Command Exp/imp (command no longer narrated)

Advantages: Easy, can be exported as a whole restore, switch between the intranet, remote backup (such as: 192.168.1.1 Backup to 192.168.1.2);

Result: failed; Prompt table space, user, instance, index already exists, cannot continue to restore;

2) Back up and restore in tablespace mode. Command Exp/imp

In the previous way, the same instance is disassembled into three tablespace as a backup, restore

Result: failure; Cause: Multiple users in the same tablespace, imported as tablespaces, and some of the reasons why some users are not established (this is purely personal speculation, the opportunity to verify again)

3) Backup and restore by user. Command EXPDP/IMPDP (Oracle 10G later version supported)

Advantage: This way backup speed is fast. (This is a local backup compared to the first, and then the backup file is copied to the new server restore.) We are switching between the intranet, this way is more appropriate.

Result: Success!

Error message:

A) null table index creation failed (ignored)

b) View creation with DbLink failed (Restore by: Restore the stored procedure, index, and view->dblink). The Dblink was not created when the view was created, so it failed. As there are fewer views related to Dblink, restore them from the old library as per the restore log

Ideas as above, the specific process is as follows:

Migration process

1. Establish an example

2. Set up Backup folder: Create directory Backup as ' folder address ';

View Settings Folder select * from Dba_directories;

3, the new server to establish the same form of source server space (note the size of the form space, form spatial data files can be multiple, a maximum data of 32G)

View the instance inside the form space: select Tablespace_name from Dba_tablespaces;

There are two ways of creating a form space:

The first one is built in EM.

The second is built with DBA authority in Sqlplus

command to create tablespace files

Create Tablespace Erpsys (Erpsys for you to create a form name)

Logging

DataFile ' d:\oracle data\erp\erpsys.dbf ' (database file storage address)

Size 32m (32M is the initial size of your form file)

Autoextend on

Next 32m maxsize 30000m (32M data file after full auto-expansion size 30000M file maximum)

Extent Management Local

Adding data files to Tablespace

ALTER tablespace erpsys Add datafile (Erpsys for the form you want to add the file to)
' D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04. DBF ' (database file storage address)

SIZE32M (32M for your form file initial size)
Autoextend on

next32m MAXSIZE 30000M; (Automatically expands size 30000M file maximum after 32M data file is full)

4. Establish user

1) Creating User: Create Userlosuser identified by sys2012;

2) Allocated space: Alter user losuserdefault tablespace Los;

3) Grant DBA Authority: GRANTCONNECT,RESOURCE,DBA to Losuser;

5. Export

C:\users\administrator>expdpfxwmsuser/[email protected] Schemas=wmsuser DUMPFILE=WMSU

Ser.dmp Directory=backuplogfile=wmsuser.log

6. Import

IMPDP System/[email protected] directory=backupdumpfile=wmsuser.dmp schemas=wmsuser Logfile=wmsuser.log

Oracle database backup Restore (EXPDP/IMPDP) records