数据泵迁移11g用户数据至19c.txt

明靓

4784

4页

36次

2020-05-21

10墨值下载

--源端数据库版本 11203

迁移用户：wiki

--目标端数据库版本 19000

1、查看源库信息

SQL> select username,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users

where username='WIKI';

USERNAME DEFAULT_TABLESPACE

TEMPORARY_TABLESPACE

---------- ------------------------------------------------------------

------------------------------------------------------------

WIKI WIKITBS TEMP

TABLESPACE_NAME USED_G

MAX_G FREE_G

------------------------------------------------------------ ----------

---------- ----------

WIKITBS 6.09

20 13.91

SQL> select count(*) from dba_indexes where owner='WIKI';

COUNT(*)

----------

SQL> select count(*) from dba_tables where owner='WIKI';

COUNT(*)

----------

select 'select count(*) from wiki.'||table_name||';' from dba_tables where

owner='WIKI';

SQL> select count(*) from wiki.DJANGO_SESSION;

COUNT(*)

----------

4375

SQL> select count(*) from wiki.PASTE_PASTE;

COUNT(*)

----------

101777

2、源端导出 wiki 用户数据

mkdir /picclife/dump_dir

create or replace directory dump_tmp_dir as '/picclife/dump_dir';

nohup expdp \'/ as sysdba\' directory=dump_tmp_dir dumpfile=user_wiki.dmp

logfile=user_wiki.log schemas=wiki cluster=N &

3、目标端创建一个新的 PDB

CREATE PLUGGABLE DATABASE WIKI ADMIN USER pdbadmin IDENTIFIED BY oracle123

ROLES=(CONNECT);

alter pluggable database WIKI open;

4、目标端创建用户表空间：

create tablespace WIKITBS datafile

'/picclife/app/oracle/oradata/ORA19CTEST/9B7038721628B639E0530100007FF3AB/datafi

le/WIKI.dbf' size 30G;

alter database tempfile 5 resize 1G;

5、目标端数据导入

root:

mkdir /picclife/dumpdir

chown oracle:oinstall /picclife/dumpdir

create or replace directory dump_tmp_dir as '/picclife/dumpdir';

传包：

scp oracle@10.135.10.22:/picclife/dump_dir/user_wiki.dmp /picclife/dumpdir/

##进入 pdb

alter session set container=wiki;

##创建导入目录

create or replace directory dump_tmp_dir as '/picclife/dumpdir';

mkdir /picclife/dump_dir

##创建执行导入命令用户

SQL> create user impdp identified by impdp account unlock;

User created.

SQL> grant dba to impdp;

Grant succeeded.

##登录测试

sqlplus impdp/impdp@10.135.102.96:1521/wiki

nohup impdp impdp/impdp@wiki dumpfile=user_wiki.dmp logfile=user_wiki.log

DIRECTORY=dump_tmp_dir remap_schema=wiki:wiki remap_tablespace=WIKITBS:WIKITBS &

导入过程报错：

（1）

Import: Release 19.0.0.0.0 - Production on Mon Jan 6 10:57:58 2020

Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -

Production

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name DUMP_TMP_DIR is invalid

---这是由于创建 dump 目录未进入 pdb 进行创建

（2）

Processing object type SCHEMA_EXPORT/USER

ORA-39083: Object type USER:"WIKI" failed to create with error:

of 4

10墨值下载

关注

评论