网站举报中心官网,深圳最大的广告公司,企业门户账号是什么,关键字优化技巧目录 1.在11gnon-cdb数据库中创建测试用户
2.在19cCDB容器数据库中新建pdb2
3.执行命令导出
4.执行命令导入 Exporting from a Non-CDB and Importing into a PDB 我們要記住一点#xff1a;如果是全库导出导入的话#xff0c;目标数据库没有的表空间我们要事先创建#…
目录 1.在11gnon-cdb数据库中创建测试用户
2.在19cCDB容器数据库中新建pdb2
3.执行命令导出
4.执行命令导入 Exporting from a Non-CDB and Importing into a PDB 我們要記住一点如果是全库导出导入的话目标数据库没有的表空间我们要事先创建不然就会导入失败。
全库数据传输我们最好不要用数据泵进行传输因为会遇到各种各样的问题如果只是导出表空间导出一张表是可以的。
1.共享池太小会导致导入失败可以通过告警日志获得信息。
2、 如果在 profile 中设置了资源限制也会导致导入失败比如 SESSIONS_PER_USER 设置的太小。
3、 如果源数据库初始时用的是 OMF 管理那么目标数据库也应该是 OMF 管理 如果不是则要创建和源表空间路径一样的路径。如果是要启用OMF管理要设置DB_CREATE_FILE_DEST参数OMF默认数据文件大小是100mAUTOEXTEND 为UNLIMITED。 4、 在导入过程中会尝试创建跟源数据库表空间名字一样的 undo、temp、users 等表空间如果已经存在则忽略。
5、 如果源数据库有创建非标准块的表空间则要提前分配非标准块的数据缓冲区 否则在建立表空间时会报错。
6、 如果是全庫導出導入會導出所有的對象目标数据库最好是新建的空的数据库。
1.在11gnon-cdb数据库创建测试用户
SQL SELECT file_name, tablespace_name FROM dba_data_files;------这里可以看出11g数据库比pdb多了两个表空间SQL set linesize 200
SQL /FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/cai/users01.dbf USERS
/u01/app/oracle/oradata/cai/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/cai/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/cai/system01.dbf SYSTEM
/u01/app/oracle/oradata/cai/example01.dbf EXAMPLESQL grant dba,resource,connect to CC;Grant succeeded.SQL select * from dba_sys_privs where granteeCC;GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CC UNLIMITED TABLESPACE NOSQL create directory dump_dir as /home/oracle/scripts;
Directory created.SQL grant read,write,execute on directory dump_dir to system;Grant succeeded. 2.在19cCDB容器数据库新建pdb2
SQL show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 MOUNTED
SQL create pluggable database pdb2 admin user admin1 identified by oracle2 FILE_NAME_CONVERT(pdbseed,pdb2);Pluggable database created.SQL show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 MOUNTED4 PDB2 MOUNTED
SQL alter session set containerpdb2;Session altered.SQL startup
Pluggable Database opened.
SQL show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------4 PDB2 READ WRITE NO
SQL select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;TABLESPACE_NAME BLOCK_SIZE STATUS LOGGING EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM 8192 ONLINE LOGGING LOCAL MANUAL
SYSAUX 8192 ONLINE LOGGING LOCAL AUTO
UNDOTBS1 8192 ONLINE LOGGING LOCAL MANUAL
TEMP 8192 ONLINE NOLOGGING LOCAL MANUALSQL select tablespace_name,file_name from dba_temp_files;TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/u01/app/oracle/oradata/CDB/pdb2/temp012024-01-10_16-24-28-422-PM.dbfSQL create directory dump_dir as /home/oracle/scripts;Directory created.SQL grant read,write,execute on directory dump_dir to system;Grant succeeded.SQL show parameter createNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL alter system set db_create_file_dest/u01/app/oracle/oradata/CDB/pdb2;System altered.SQL create tablespace users; Tablespace created.
SQL create tablespace example;Tablespace created.
3.执行命令导出
[oracle11g scripts]$ export NLS_LANGAMERICAN_AMERICA.AL32UTF8
[oracle11g scripts]$ expdp system/oracle fully dumpfilejie.dmp directorydump_dir
[oracle11g scripts]$ scp jie.dmp 19c:/home/oracle/scripts
[oracle11g scripts]$ ll -ls
total 209916116 -rw-r--r-- 1 oracle oinstall 116052 Jan 12 14:12 export.log
209800 -rw-r----- 1 oracle oinstall 214835200 Jan 12 14:12 jie.dmp4.执行命令导入
[oracle19c ~]$ export NLS_LANGAMERICAN_AMERICA.AL32UTF8
[oracle19c ~]$ impdp system/oraclepdb2 fully dumpfilejie.dmp directorydump_dir
导入执行成功后查看pdb2是否有CC用户如下显示导入成功
SQL show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------4 PDB2 READ WRITE NO
SQL
SQL
SQL
SQL
SQL SELECT file_name, tablespace_name FROM dba_data_files;FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf SYSTEM
/u01/app/oracle/oradata/CDB/pdb2/CDB/0EA407C05EF40 USERS
9A6E06302F4A8C0589B/datafile/o1_mf_users_lszzp7rj_
.dbf/u01/app/oracle/oradata/CDB/pdb2/CDB/0EA407C05EF40 EXAMPLE
9A6E06302F4A8C0589B/datafile/o1_mf_example_lt00g0o
4_.dbfSQL select username from all_users;USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
SYSBACKUP
SYSDG
SYSKM
SYSRAC
OUTLN
DBSFWUSER
XS$NULL
GSMADMIN_INTERNALUSERNAME
--------------------------------------------------------------------------------------------------------------------------------
GSMUSER
DIP
REMOTE_SCHEDULER_AGENT
DBSNMP
ORACLE_OCM
SYS$UMF
APPQOSSYS
GSMCATUSER
GGSYS
XDB
ANONYMOUSUSERNAME
--------------------------------------------------------------------------------------------------------------------------------
WMSYS
OJVMSYS
CTXSYS
ORDSYS
ORDDATA
ORDPLUGINS
SI_INFORMTN_SCHEMA
MDSYS
OLAPSYS
MDDATA
DVSYSUSERNAME
--------------------------------------------------------------------------------------------------------------------------------
LBACSYS
DVF
ADMIN1
APEX_PUBLIC_USER
SYSMAN
MGMT_VIEW
FLOWS_FILES
APEX_030200
OWBSYS
OWBSYS_AUDIT
SPATIAL_WFS_ADMIN_USRUSERNAME
--------------------------------------------------------------------------------------------------------------------------------
SPATIAL_CSW_ADMIN_USR
SCOTT
HR
OE
IX
SH
PM
BI
CC53 rows selected.
