Immediately following the previous article, the Oracle database schema has been created, and my requirement is to migrate the databases on the old server to the new database .
This will use the IMPDP (import) operation.
To implement IMPDP (import) work on a new database,
First you need to do EXPDP (export) operation from the old database, the command is as follows:
EXPDP user name/password schemas=iirspublish dumpfile= export the database name. DMP directory= Directory name logfile= export log. Log
Note: schemas, it's easy to understand it with user users, and you can have a set of non-interfering objects under each mode (user).
If you want to access other schema objects, you need to specify the schema name, which is actually specifying username.
Here are some of the steps I've practiced in my actual work,
Preparation: First connect to the Linux server with Xshell and switch to Oracle account,
[Email protected] ~]# su-oracle
Last Login:fri Jul 13:49:22 CST on PTS/1 ( successful switchover )
[Email protected] ~]$ sqlplus sys/as sysdba
Switch to
Sql>
Then execute the following command in turn:
1. SYS User Login Create directory
Sql>create directory dir_iirsuserbaknew as '/u01/app/oracle/oradata '; (Create a directory and assign a storage path)
2. Create TABLE space under SYS user
Sql>create tablespace iirsuser datafile '/u01/app/oracle/oradata/iirsuser01 ' size 100m autoextend on next 100m, '/u01/ App/oracle/oradata/iirsuser02 ' size 100m autoextend on next 100m Extent management local uniform size 200k; (Create table space, set file autogrow Space size)
3. User created under SYS user
Sql>create user Iirsuser identified by "Iirsuser" Default tablespace iirsuser temporary tablespace temp; (Create a user name, set a password, and indicate the default tablespace and temporary table space)
Sql>grant Dba,connect,resource to Iirsuser; (Grant DBA, Foundation, development rights to the user)
4, sys user under
Sql>grant read,write on the directory dir_iirsuserbaknew to Iirsuser; (Grant users Read and write permissions to the directory)
5, DOS under IMPDP restore
[[Email protected] ~] $IMPDP iirsuser/"Iirsuser" @orcl directory=dir_iirsuserbaknew dumpfile= Expiirspublish20170814.dmp Remap_tablespace=iirspublish:iirsuser Remap_schema=iirspublish:iirsuser logfile= Impdbiirspublish20170814.log transform=oid:n
The 5th step requires the exit (exit) SQL command line to execute under the Oracle account.
What you need to explain is:
IMPDP username/password @sid directory= directory dumpfile= The instance name EXPDP the old database. DMP remap_tablespace= Old Database space name: New database space name remap_schema= old database user name: New database user name logfile=impdb log name transform= This parameter can be removed and applied to the applicable object's metadata transformation
It should be emphasized that:
Question 1: If you forget the directory you built during the operation, you can use the command (SELECT * from dba_directories;) to query;
Issue 2: If you want to delete a newly restored DB instance, you need to do the following
1), first delete the user
-----drop USER C#iirsuser CASCADE;
2), second Delete table space including tablespace content and data files
-----Drop tablespace iirsuser including contents and datafiles cascade constraints;
3), finally delete the directory
-----Drop directory Dir_iirsuserbaknew;
EXPDP (export) and IMPDP (import) of Oracle databases under Linux servers