Oracle 12c 如何在 PDB 中添加 SCOTT 模式（数据泵方式）
2019-05-24 21:18 askscuti 阅读(858) 评论(0) 编辑 收藏 举报Oracle 12c 建库后，没有 scott 模式，本篇使用数据泵方式，在12c版本之前数据库中 expdp 导出 scott 模式，并连接 12c 的 pdb 进行 impdp 导入。
目录
1. 在11g版本中数据泵导出
1.1 创建 directory 目录
1.2 数据泵导出
1.3 确认scott用户及权限信息
2. 在12c版本中数据泵导入
2.1 配置TNSNAMES.ora
2.2 测试连接PDB
2.3 执行scott用户及权限信息
2.4 创建 directory 目录
2.5 数据泵导入
2.6 验证数据
1. 在11g版本中数据泵导出
1.1 创建 directory 目录
SQL> select * from dba_directories; SQL> create directory backup_dir as '/u01/app/oracle/backup'; Directory created. SQL> grant read,write on directory backup_dir to system; Grant succeeded.
1.2 数据泵导出
[oracle@henry ~]$ expdp system/oracle directory=backup_dir dumpfile=expdp_scott.dmp logfile=expdp_scott.log schemas=scott Export: Release 11.2.0.4.0 - Production on Fri May 24 19:53:16 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=backup_dir dumpfile=expdp_scott.dmp logfile=expdp_scott.log schemas=scott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 384 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."A" 5.460 KB 6 rows . . exported "SCOTT"."ABC" 5 KB 1 rows . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."TESTEMP" 8.648 KB 19 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows . . exported "SCOTT"."TBA" 0 KB 0 rows . . exported "SCOTT"."TBS_C" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/backup/expdp_scott.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:54:28
1.3 确认scott用户及权限信息
用户（模式）导出，要事先知道该用户（模式）在原来数据库中所涉及的表空间、系统权限、对象权限、角色权限等。我们可以通过调用 dbms.metadata 包来获得相关资源。
SQL> select dbms_metadata.get_ddl('USER','SCOTT') from dual; CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:F2071AA98BBC32C96BBADA4D194880F30491BAD413C402C5887BF4439B7C;F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','SCOTT') from dual; ERROR: ORA-31608: specified object of type OBJECT_GRANT not found ORA-06512: at "SYS.DBMS_METADATA", line 5088 ORA-06512: at "SYS.DBMS_METADATA", line 7737 ORA-06512: at line 1 SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT','SCOTT') from dual; GRANT "CONNECT" TO "SCOTT" GRANT "RESOURCE" TO "SCOTT" GRANT "DBA" TO "SCOTT" SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SCOTT') from dual; GRANT UNLIMITED TABLESPACE TO "SCOTT" SQL> select dbms_metadata.get_ddl('TABLESPACE','SCOTT') from dual; ERROR: ORA-31603: object "SCOTT" of type TABLESPACE not found in schema "SYS" ORA-06512: at "SYS.DBMS_METADATA", line 5088 ORA-06512: at "SYS.DBMS_METADATA", line 7589 ORA-06512: at line 1
相关报错说明该用户在原来数据库中对应的元数据不存在，直接忽略。将上面的获取到的语句进行整理备用。最后，将导出的文件，拷贝至12c所在的服务器（实验环境的具体路径和11g所在的服务器是一致的）。
2. 在12c版本中数据泵导入
2.1 配置TNSNAMES.ora
在12c的PDB中，普通用户无法像非CDB那样直接通过 conn 互切用户（比如在一个PDB中，普通henry用户无法直接切换到其他普通用户），默认在PDB中 sys 用户也无法直接切换到 henry 用户，例如：
SQL> alter session set container=pdb1; Session altered. SQL> select username from dba_users where username='HENRY'; USERNAME ------------------------------ HENRY SQL> show user; USER is "SYS" SQL> show con_name; CON_NAME ------------------------------ PDB1 SQL> conn henry/henry ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
必须配置 tnsnames.ora 连接字符串别名，并通过 sqlplus 命令进行连接。
[oracle@henry ~]$ cd $ORACLE_HOME/network/admin/ [oracle@henry admin]$ vim tnsnames.ora
在tnsnames.ora文件中添加连接字符串
PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = henry)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB1) ) )
2.2 测试连接PDB
查看当前CDB中的PDB
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
当前CDB中只有一个用户PDB：PDB1
因为已经添加过连接字符串，现在尝试连接PDB1
[oracle@henry ~]$ sqlplus henry/henry@pdb1 SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 21:06:36 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Fri May 24 2019 21:04:59 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show user; USER is "HENRY" SQL> show con_name CON_NAME ------------------------------ PDB1
测试连接成功。
2.3 执行scott用户及权限信息
该语句信息在1.3小节通过 dbms.metadata 包得到。
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:F2071AA98BBC32C96BBADA4D194880F30491BAD413C402C5887BF4439B7C;F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; GRANT "CONNECT" TO "SCOTT"; GRANT "RESOURCE" TO "SCOTT"; GRANT "DBA" TO "SCOTT"; GRANT UNLIMITED TABLESPACE TO "SCOTT"
2.4 创建 directory 目录
[oracle@henry ~]$ mkdir /u01/app/oracle/backup
12c数据库所在的服务器具体目录路径和11g是一样的（具体路径根据实际情况），backup目录下放着11g数据泵导出来的 scott 模式的dmp文件。
[oracle@henry ~]$ sqlplus sys/oracle@pdb1 as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 20:06:25 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show con_name; CON_NAME ------------------------------ PDB1 SQL> select * from dba_directories; SQL> create directory backup_dir as '/u01/app/oracle/backup'; Directory created. SQL> grant read,write on directory backup_dir to system; Grant succeeded.
2.5 数据泵导入
[oracle@henry ~]$ impdp system/oracle@pdb1 directory=backup_dir dumpfile=expdp_scott.dmp logfile=impdp_scott.log schemas=scott Import: Release 12.2.0.1.0 - Production on Fri May 24 20:12:35 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@pdb1 directory=backup_dir dumpfile=expdp_scott.dmp logfile=impdp_scott.log schemas=scott Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SCOTT" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."A" 5.460 KB 6 rows . . imported "SCOTT"."ABC" 5 KB 1 rows . . imported "SCOTT"."DEPT" 5.929 KB 4 rows . . imported "SCOTT"."EMP" 8.562 KB 14 rows . . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT"."TESTEMP" 8.648 KB 19 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows . . imported "SCOTT"."TBA" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Fri May 24 20:13:28 2019 elapsed 0 00:00:44
2.6 验证数据
[oracle@henry ~]$ sqlplus scott/tiger@pdb1 SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 21:16:54 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Fri May 24 2019 21:16:43 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show user USER is "SCOTT" SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> select count(*) from dept; COUNT(*) ---------- 4
