Connecting To 12CR2 RAC Pluggable Database With ORA-1033

操作系统为Oracle Linux 7.1 数据库为Oracle 12.2.0.1,今天在登录pdb时使用sysdba权限登录正常,使用非sysdba权限登录出现ora-01033错误,错误信息如下:

[root@jytest1 ~]# su - oracle
Last login: Tue May 16 18:32:29 CST 2017
[oracle@jytest1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 18:39:42 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.
SQL> conn jy/jy@jypdb
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Warning: You are no longer connected to ORACLE.

在MOS上有篇文档” Connecting To A 12c RAC Pluggable Database Intermittently Fails With ORA-1033 (Doc ID 1998112.1)”描述相关问题,原因有两个,一是pdb所使用的服务名与pdb数据库名相同,二是PDB没有在所有RAC实例上open,说使用pdb数据库名作为服务名对于RAC来说不是一个最佳方案,因为当实例使用SCAN来注册pdb名时并且节点监听到pdb被mounted。这可能造成连接被发送到pdb被mounted的实例上,当以非sysdba权限登录时就会出现ora-0133错误。

pdb的服务名确实是使用pdb名作为其服务名

[grid@jytest1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-MAY-2017 18:42:17

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-MAY-2017 11:14:02
Uptime                    14 days 7 hr. 28 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.175)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "4b2c6373ae2547cce053ab828a0a7ca3" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "4e0ba8d9d278217be053ab828a0a1330" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jycs" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycspdb" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jypdb" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

pdb数据库在所有实例上都open了

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

给pdb增加服务名jypdb_srv

[grid@jytest1 ~]$ su - oracle
Password: 
Last login: Tue May 16 18:39:02 CST 2017 on pts/0
[oracle@jytest1 ~]$ srvctl add service  -db jy -pdb jypdb -s jypdb_srv  -preferred "jy1" -available "jy2"
[oracle@jytest1 ~]$ srvctl start service -db jy -s jypdb_srv

[grid@jytest1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-MAY-2017 18:56:55

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-MAY-2017 11:14:02
Uptime                    14 days 7 hr. 42 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.175)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "4b2c6373ae2547cce053ab828a0a7ca3" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy1", status READY, has 2 handler(s) for this service...
Service "4e0ba8d9d278217be053ab828a0a1330" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jycs" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycspdb" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jypdb" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy1", status READY, has 2 handler(s) for this service...
Service "jypdb_srv" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy1", status READY, has 2 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

使用新服务名再次以非sysdba权限登录成功
SQL> conn jy/jy@jypdb_srv
Connected.
SQL>

Oracle 12c Grid Infrastructure for a Standalone Server with a New Database on Oracle Linux 7

Oracle 12CR2发布了,学习的季节也到了,学习的第一步就是得安装数据库,这篇文章介绍如何在Oracle Linux 7.1上安装Oracle 12c Grid Infrastructure for a Standalone Server with a New Database

检查Linux内核与软件包
1.检查linux版本

[root@jytest ~]# cat /etc/oracle-release
Oracle Linux Server release 7.1
[root@jytest ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)

2.检查内核版本

[root@jytest ~]# uname -r
3.8.13-55.1.6.el7uek.x86_64

3.检查需要的软件包是否安装

rpm -q package_name

也可以指定系统结构信息,例如:

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep package_name

也可以将查询多个软件包的语句组合在一起,并查看正确的软件版本.

[root@jytest ~]# rpm -q binutils compat-libstdc++ gcc glibc libaio libgcc libstdc++ make sysstat unixodbc
binutils-2.23.52.0.1-30.el7.x86_64
package compat-libstdc++ is not installed
gcc-4.8.3-9.el7.x86_64
glibc-2.17-78.0.1.el7.x86_64
libaio-0.3.109-12.el7.x86_64
libgcc-4.8.3-9.el7.x86_64
libstdc++-4.8.3-9.el7.x86_64
make-3.82-21.el7.x86_64
sysstat-10.1.5-7.el7.x86_64
package unixodbc is not installed

禁用Transparent HugePages
对于Red Hat Enterprise Linux kernels:

#cat /sys/kernel/mm/redhat_transparent_hugepage/enabled

其它kernels:

#cat /sys/kernel/mm/transparent_hugepage/enabled

下面是输出示例,显示Transparent HugePages被使用[always]标记被启用
[always] never
如果Transparent HugePages从内核中被删除,那么/sys/kernel/mm/transparent_hugepage或/sys/kernel/mm/redhat_transparent_hugepage文件会不存在。

为了禁用transparent hugepage执行以下操作:
1.向内核心/etc/grub.conf文件中增加以下记录

transparent_hugepage=never

2.重启操作系统

#reboot

创建用户组

[root@jytest /]# groupadd asmadmin
[root@jytest /]# groupadd asmdba
[root@jytest /]# groupadd asmoper
[root@jytest /]# groupadd dba
[root@jytest /]# groupadd oper
[root@jytest /]# groupadd oinstall
[root@jytest /]# groupadd backupdba
[root@jytest /]# groupadd dgdba
[root@jytest /]# groupadd kmdba
[root@jytest /]# groupadd racdba

创建用户

[root@jytest /]# useradd  -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba,oper 
oracle
[root@jytest /]# useradd  -g oinstall -G asmadmin,asmdba,asmoper,dba grid

[root@jytest /]# passwd oracle
Changing password for user oracle.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@jytest /]# passwd grid
Changing password for user grid.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.

以root用户创建“Oracle inventory 目录”

[root@jytest /]# mkdir -p /u01/app/oraInventory
[root@jytest /]# chown -R grid:oinstall /u01/app/oraInventory
[root@jytest /]# chmod -R 775 /u01/app/oraInventory

以root用户创建“Grid Infrastructure BASE 目录”

[root@jytest /]# mkdir -p /u01/app/grid
[root@jytest /]# chown -R grid:oinstall /u01/app/grid
[root@jytest /]# chmod -R 775 /u01/app/grid

以root用户创建“Grid Infrastructure Home 目录”

[root@jytest /]# mkdir -p /u01/app/product/12.2.0/crs
[root@jytest /]# chown -R grid:oinstall /u01/app/product/12.2.0/crs
[root@jytest /]# chmod -R 775 /u01/app/product/12.2.0/crs

以root用户创建“Oracle Base 目录”

[root@jytest /]# mkdir -p /u01/app/oracle
[root@jytest /]# chown -R oracle:oinstall /u01/app/oracle
[root@jytest /]# chmod -R 775 /u01/app/oracle

以root用户创建“Oracle RDBMS Home 目录”

[root@jytest /]# mkdir -p /u01/app/oracle/product/12.2.0/db
[root@jytest /]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/db
[root@jytest /]# chmod -R 775 /u01/app/oracle/product/12.2.0/db

创建一个tmp目录

[root@jytest /]# mkdir /u01/tmp
[root@jytest /]# chmod a+wr /u01/tmp

设置环境变量

[root@jytest /]# su - grid
[grid@jytest ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/product/12.2.0/crs
export ORACLE_SID=+ASM
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022


TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db
export ORACLE_SID=jy
export ORACLE_UNQNAME=jy
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022

修改内核参数编辑/etc/sysctl.conf文件

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576


[root@jytest3 ~]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

修改oarcle参数的shell限制
在/etc/security/limits.conf中添加以下参数:

grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
grid soft stack 10240
grid hard stack 32768
grid soft memlock 3145728
grid hard memlock 3145728

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock 3145728
oracle hard memlock 3145728

修改shell的默认参数文件,在/etc/profile文件中添加以下内容:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi


if [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

对C shell(csh or tcsh)在/etc/csh.login文件中增加以下代码

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

if ( $USER == "grid" ) then
limit maxproc 16384
limit descriptors 65536
endif

配置创建ASM磁盘组的asm磁盘

[root@jytest3 ~]# fdisk -l

Disk /dev/sdb: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdc: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sda: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000209aa

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     1026047      512000   83  Linux
/dev/sda2         1026048   104857599    51915776   8e  Linux LVM

Disk /dev/mapper/ol-root: 47.7 GB, 47747956736 bytes, 93257728 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/ol-swap: 5368 MB, 5368709120 bytes, 10485760 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

查看磁盘uuid

[root@jytest3 ~]# /usr/lib/udev/scsi_id -g -u /dev/sdb
36000c295f8b38933c37ef4a42f446599
[root@jytest3 ~]# /usr/lib/udev/scsi_id -g -u /dev/sdc
36000c29739826dc8ef28ef9a3589d3a0

udev绑定

[root@jytest3 ~]# vi /etc/udev/rules.d/99-my-asmdevices.rules


KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c295f8b38933c37ef4a42f446599", RUN 
+="/bin/sh -c 'mknod /dev/asmdisk01 b  $major $minor; chown grid:oinstall /dev/asmdisk01; chmod 0660 /dev/asmdisk01'"

KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c29739826dc8ef28ef9a3589d3a0", RUN 
+="/bin/sh -c 'mknod /dev/asmdisk02 b  $major $minor; chown grid:oinstall /dev/asmdisk02; chmod 0660 /dev/asmdisk02'"

~

[root@jytest3 ~]# /sbin/udevadm trigger --type=devices --action=change
[root@jytest3 ~]# ls -lrt /dev/asm*
brw-rw----. 1 grid oinstall 8, 32 Mar  8 22:24 /dev/asmdisk02
brw-rw----. 1 grid oinstall 8, 16 Mar  8 22:24 /dev/asmdisk01

解压GI安装压缩包:

[grid@jytest3 soft]cd /u01/app/grid/product/12.2.0/crs/
[grid@jytest3 soft]# unzip -q /soft/linuxx64_12201_grid_home.zip


配置vnc
检查系统是否已经安装vncserver软件包
[root@jytest3 system]# rpm -qa|grep tigervnc
tigervnc-server-minimal-1.2.80-0.30.20130314svn5065.el7.x86_64
tigervnc-license-1.2.80-0.30.20130314svn5065.el7.noarch

没有安装执行以下命令进行安装

[root@jytest3 /]# cd soft
[root@jytest3 soft]# yum -y install tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64.rpm
Loaded plugins: langpacks
Examining tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64.rpm: tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64
Marking tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package tigervnc-server.x86_64 0:1.2.80-0.30.20130314svn5065.el7 will be installed
--> Finished Dependency Resolution
http://public-yum.oracle.com/repo/OracleLinux/OL7/UEKR3/x86_64/repodata/repomd.xml: [Errno 14] curl#6 - "Could not resolve host: public-yum.oracle.com; Unknown error"
Trying other mirror.
http://public-yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/repodata/repomd.xml: [Errno 14] curl#6 - "Could not resolve host: public-yum.oracle.com; Unknown error"
Trying other mirror.

Dependencies Resolved

=======================================================================================================================================================================
 Package                     Arch               Version                                      Repository                                                           Size
=======================================================================================================================================================================
Installing:
 tigervnc-server             x86_64             1.2.80-0.30.20130314svn5065.el7              /tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64             488 k

Transaction Summary
=======================================================================================================================================================================
Install  1 Package

Total size: 488 k
Installed size: 488 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
  Installing : tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64                                                                                              1/1 
  Verifying  : tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64                                                                                              1/1 

Installed:
  tigervnc-server.x86_64 0:1.2.80-0.30.20130314svn5065.el7                                                                                                             

Complete!

之前的版本,如果安装vnc一般都需要使用vncserver命令来设置口令,然后配置/etc/sysconfig/vncservers文件。
在Oracle Linux7中,虽然然还存在这个文件,不过其内容只有如下一行:

root@jytest3 soft]#  cat /etc/sysconfig/vncservers
# THIS FILE HAS BEEN REPLACED BY /lib/systemd/system/vncserver@.service

复制文件

[root@jytest3 soft]# cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver_root@:2.service

编辑vncserver_root@:2.service文件将文件中的用root替换

[root@jytest3 soft]# cd /etc/systemd/system/
[root@jytest3 system]# vi vncserver_root@:2.service
# The vncserver service unit file
#
# Quick HowTo:
# 1. Copy this file to /etc/systemd/system/vncserver@:.service
# 2. Edit  and vncserver parameters appropriately
#   ("runuser -l  -c /usr/bin/vncserver %i -arg1 -arg2")
# 3. Run `systemctl daemon-reload`
# 4. Run `systemctl enable vncserver@:.service`
#
# DO NOT RUN THIS SERVICE if your local area network is
# untrusted!  For a secure way of using VNC, you should
# limit connections to the local host and then tunnel from
# the machine you want to view VNC on (host A) to the machine
# whose VNC output you want to view (host B)
#
# [user@hostA ~]$ ssh -v -C -L 590N:localhost:590M hostB
#
# this will open a connection on port 590N of your hostA to hostB's port 590M
# (in fact, it ssh-connects to hostB and then connects to localhost (on hostB).
# See the ssh man page for details on port forwarding)
#
# You can then point a VNC client on hostA at vncdisplay N of localhost and with
# the help of ssh, you end up seeing what hostB makes available on port 590M
#
# Use "-nolisten tcp" to prevent X connections to your VNC server via TCP.
#
# Use "-localhost" to prevent remote VNC clients connecting except when
# doing so through a secure tunnel.  See the "-via" option in the
# `man vncviewer' manual page.


[Unit]
Description=Remote desktop service (VNC)
After=syslog.target network.target

[Service]
Type=simple
# Clean any existing files in /tmp/.X11-unix environment
ExecStartPre=/bin/sh -c '/usr/bin/vncserver -kill %i > /dev/null 2>&1 || :'
ExecStart=/sbin/runuser -l root -c "/usr/bin/vncserver %i"
PIDFile=/root/.vnc/%H%i.pid
ExecStop=/bin/sh -c '/usr/bin/vncserver -kill %i > /dev/null 2>&1 || :'

[Install]
"vncserver_root@:2.service" 45L, 1739C written

启用vncserver配置

[root@jytest3 system]# systemctl enable vncserver_root@:2.service
ln -s '/etc/systemd/system/vncserver_root@:2.service' '/etc/systemd/system/multi-user.target.wants/vncserver_root@:2.service'

启动vncserver服务

[root@jytest3 system]# vncserver

You will require a password to access your desktops.

Password:
Verify:
xauth:  file /root/.Xauthority does not exist

New 'jytest3:1 (root)' desktop is jytest3:1

Creating default startup script /root/.vnc/xstartup
Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/jytest3:1.log

关闭防火墙

[root@jytest3 system]# systemctl stop firewalld
[root@jytest3 system]# systemctl disable firewalld
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
rm '/etc/systemd/system/basic.target.wants/firewalld.service'


用vnc登录服务器安装软件

[root@jytest3 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@jytest3 Desktop]# su - grid
Last login: Tue Mar  7 17:50:57 CST 2017 on pts/0
[grid@jytest3 ~]$ cd /soft/grid


[grid@jytest3 crs]$ ./gridSetup.sh 
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

Can't connect to X11 window server using '10.138.135.167:1.0' as the value of the DISPLAY variable.

出现在错误,并且错误信息提示需要设置环境变量DISPLAY,下面设置环境变量DISPLAY

[grid@jytest3 grid]$ exit
logout
[root@jytest3 Desktop]# xdpyinfo
name of display:    :1
version number:    11.0
vendor string:    The X.Org Foundation
vendor release number:    11500000
X.Org version: 1.15.0
maximum request size:  16777212 bytes
motion buffer size:  256
bitmap unit, bit order, padding:    32, LSBFirst, 32
image byte order:    LSBFirst
number of supported pixmap formats:    6
...省略...只需要记录name of display:的值
[root@jytest3 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@jytest3 Desktop]# su - grid
Last login: Tue Mar  7 17:50:40 CST 2017 on pts/0
[grid@jytest3 ~]$ export DISPLAY=:1(这时设置为name of display的值:1)
[grid@jytest3 ~]$ xdpyinfo
name of display:    :1
version number:    11.0
vendor string:    The X.Org Foundation
vendor release number:    11500000
X.Org version: 1.15.0
maximum request size:  16777212 bytes
motion buffer size:  256
bitmap unit, bit order, padding:    32, LSBFirst, 32
image byte order:    LSBFirst
number of supported pixmap formats:    6
...省略...
[grid@jytest3 ~]$cd /u01/app/grid/product/12.2.0/crs/
[grid@jytest3 crs]$ ./gridSetup.sh

选择for a Standalone Server (Oracle Restart)


现在可以让安装程序来以root用户自动执行安装所需要执行的脚本,指定root用户的密码


检查磁盘组data是否创建成功

[grid@jytest3 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512             512   4096  4194304     40960    40760                0           20380              0             N  DATA/

安装数据库软件

[root@jytest3 soft]# unzip linuxx64_12201_database.zip -d 

[root@jytest3 Desktop]# xhost +
access control disabled, clients can connect from any host

[root@jytest3 Desktop]# xdpyinfo
name of display:    :1
version number:    11.0
vendor string:    The X.Org Foundation
vendor release number:    11500000
X.Org version: 1.15.0
maximum request size:  16777212 bytes
motion buffer size:  256
bitmap unit, bit order, padding:    32, LSBFirst, 32
image byte order:    LSBFirst
number of supported pixmap formats:    6
...省略...只需要记录name of display:的值

[root@jytest3 Desktop]# su - oracle
Last login: Thu Mar  9 04:17:35 CST 2017 on pts/2
[oracle@jytest3 ~]$ export DISPLAY=:1
[oracle@jytest3 ~]$ cd /soft/database
[oracle@jytest3 database]$ ./runInstaller


执行脚本

[root@jytest3 /]# ./u01/app/oracle/product/12.2.0/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/12.2.0/db

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : 
yes
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/app/oracle/product/12.2.0/db/install/root_jytest3_2017-03-09_15-33-03-156667468.log
Finished installing Oracle Trace File Analyzer (TFA)

创建数据库

[oracle@jytest3 database]$ dbca









在创建的过程中出现了以下错误

[Thread-301] [ 2017-03-09 22:15:26.774 CST ] [PostDBCreationStep.executeImpl:1156]  Exception while Starting with HA Database Resource PRCR-1079 : Failed to start resource ora.jy.db
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest3/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest3' failed
ORA-01017: invalid username/password; logon denied

数据库jy确实没能通过oracle restart自动启动

[grid@jytest3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jytest3                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest3                  STABLE
ora.asm
               ONLINE  ONLINE       jytest3                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      jytest3                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.evmd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.jy.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
--------------------------------------------------------------------------------

如是手动启动数据库

[root@jytest3 ~]# su - oracle
Last login: Thu Mar  9 22:08:36 CST 2017 on pts/4
[oracle@jytest3 ~]$ echo $ORACLE_SID
jy
[oracle@jytest3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 9 22:17:21 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8795904 bytes
Variable Size            1358956800 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.

[grid@jytest3 trace]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jytest3                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest3                  STABLE
ora.asm
               ONLINE  ONLINE       jytest3                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      jytest3                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.evmd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest3                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
--------------------------------------------------------------------------------

到此安装完成!

Oracle 12cr2 数据库之间跨网络传输表,分区或子分区

为了跨网络传输表,可以在执行导入操作时使用network_link参数,这样导入操作将会使用数据库链路而不用先导出dump文件。其操作步骤如下:
1.选择一组表,分区或子分区。
如果是要传输分区,那么在传输表操作中可以指定一个表的分区,并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出,那么在导入后每个分区将变成非分区表。

2.在源数据库中,将要被传输的表,分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图,为了查询表空间的所有文件可以查询dba_data_files视图。

3.传输表,分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样,那么可以使用以下
任何一种方法来转换数据文件。
–使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件,它们会自动将数据文件转换为目标平台的字节编码。

–使用rman的convert命令来将数据文件转换为目标平台的字节编码。

4.在目标数据库上执行导入操作

5.可选操作,将源数据库中的表空间设置为读写模式

下面的例子将介绍如何使用跨网传输表,分区或子分区的方法来将一个数据库中的hr.emp_test与oe.orders_test表传输到另一个数
据库中。其中源平台与目标平台的字节编码相同。

1.先在源数据库中创建表hr.emp_test与oe.orders_test

SQL> create tablespace emp_test datafile '+DATADG/jyrac/datafile/emp_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created

SQL> create tablespace orders_test datafile '+DATADG/jyrac/datafile/orders_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created



SQL> create table hr.emp_test tablespace emp_test as select * from hr.employees;
Table created

SQL> create table oe.orders_test tablespace orders_test as select * from oe.orders;
Table created

2.在目标数据库中创建数据库链路连接到源数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.

3.将源数据库中表hr.emp_test与oe.orders_test所在的表空间设置为只读状态

SQL> alter tablespace emp_test read only;
Tablespace altered

SQL> alter tablespace orders_test read only;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
EMP_TEST                       READ ONLY
ORDERS_TEST                    READ ONLY
11 rows selected

4.将表空间tem_test与orders_test的所有数据文件复制到目标数据库中
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'emp_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'emp_test_01.dbf');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'orders_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'orders_test_01.dbf');
PL/SQL procedure successfully completed


ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt  
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    orders_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.303.945987633
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    emp_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.302.945987591
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.303.945987633
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.302.945987591
DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  Y    FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399

5.在目标数据库中执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf' tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump

Import: Release 12.2.0.1.0 - Production on Tue Jun 6 22:24:24 2017

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
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jun 6 22:24:57 2017 elapsed 0 00:00:30

6.可选操作,将源数据库中的表空间emp_test与orders_test设置为读写模式

SQL> alter tablespace emp_test read write;
Tablespace altered

SQL> alter tablespace orders_test read write;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
EMP_TEST                       ONLINE
ORDERS_TEST                    ONLINE
11 rows selected

Oracle 12r2 数据库之间传输表,分区或子分区

在Oracle 12cr2中,可以使用传输表功能来从一个数据库中复制一组表,分区或子分区到另一个数据库中。传输表操作将会指定表,分区或子分区的元数据移到另一个数据库中。传输表操作会自动识别所指定表所在的表空间。为了移动数据,需要将这些表所在表空间的所有数据文件复制到目标数据库。Data Pump导入会自动释放由表,分区或子分区所占有的数据块,这些数据块不是传输表操作的一部分。

可以使用以下方法来传输表,分区或子分区:
.使用导出dump文件
在执行导出时,指定tables参数并且设置transportable参数为always。在执行导入时,不需要指定transportable参数。Data Pump导入会自动识别传输表操作。

.跨网络
在执行导入时,指定tables参数并且设置transportable参数为always,并且指定network_link参数来指定数据链路

传输表操作的限制
.不能将相同方案中相同表名的表传输到目标数据库中。然而可以使用remap_table导入参数来将表中的数据导入到不同的表中。另外,在传输操作执行之前,可以重命名被传输表或目标表。

.对于加密有以下限制:
–不能传输加密表空间中的表
–不能包含加密列的表

.不能在使用不同的time zone文件版本的不同平台之间传输使用timestamp with timezone的表

使用导出dump文件方式来传输表,分区,或子分区
在数据库之间使用志出dump文件来传输表需要执行以下步骤。
1.选择一组表,分区或子分区。
如果是要传输分区,那么在传输表操作中可以指定一个表的分区,并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出,那么在导入后每个分区将变成非分区表。

2.在源数据库中,将要被传输的表,分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图,为了查询表空间的所有文件可以查询dba_data_files视图。

3.执行Data Pump导出

4.传输导出的dump文件,将导出的dump文件复制到目标数据库并且让其可以访问。

5.传输表,分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样,那么可以使用以下任何一种方法来转换数据文件。
–使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件,它们会自动将数据文件转换为目标平台的字节编码。

–使用rman的convert命令来将数据文件转换为目标平台的字节编码。

6.可选操作,将源数据库中的表空间设置为读写模式

7.在目标数据库上执行导入操作

下面的例子将分区表sh.sales_test表中的部分分区(sales_test_q1_2000,sales_test_q2_2000)传输到目标数据库中。源平台与目标平台字节编码一样,都是linux 64位操作系统

1.先创建分区表sales_test

SQL> create tablespace sales_test datafile '+DATADG/jyrac/datafile/sales_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created

-- Create table
create table SH.SALES_TEST
(
  prod_id       /* NUMBER not null*/,
  cust_id       /*NUMBER not null*/,
  time_id       /*DATE not null*/,
  channel_id    /*NUMBER not null*/,
  promo_id      /*NUMBER not null*/,
  quantity_sold /*NUMBER(10,2) not null*/,
  amount_sold   /*NUMBER(10,2) not null*/
)
partition by range (TIME_ID)
(
  partition SALES_TEST_1995 values less than (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_1996 values less than (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_H1_1997 values less than (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_H2_1997 values less than (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q1_1998 values less than (TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_1998 values less than (TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_1998 values less than (TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_1998 values less than (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_1999 values less than (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_1999 values less than (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_1999 values less than (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_1999 values less than (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2000 values less than (TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_2000 values less than (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_2000 values less than (TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_2000 values less than (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2001 values less than (TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_2001 values less than (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_2001 values less than (TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_2001 values less than (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2002 values less than (TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q2_2002 values less than (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q3_2002 values less than (TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q4_2002 values less than (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q1_2003 values less than (TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q2_2003 values less than (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q3_2003 values less than (TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q4_2003 values less than (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 5
    initrans 1
    maxtrans 255
) tablespace sales_test as select * from sh.sales;
-- Add comments to the table
comment on table SH.SALES_TEST
  is 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys';
-- Add comments to the columns
comment on column SH.SALES_TEST.prod_id
  is 'FK to the products dimension table';
comment on column SH.SALES_TEST.cust_id
  is 'FK to the customers dimension table';
comment on column SH.SALES_TEST.time_id
  is 'FK to the times dimension table';
comment on column SH.SALES_TEST.channel_id
  is 'FK to the channels dimension table';
comment on column SH.SALES_TEST.promo_id
  is 'promotion identifier, without FK constraint (intentionally) to show outer join optimization';
comment on column SH.SALES_TEST.quantity_sold
  is 'product quantity sold with the transaction';
comment on column SH.SALES_TEST.amount_sold
  is 'invoiced amount to the customer';
-- Create/Recreate indexes
create bitmap index SH.SALES_TEST_CHANNEL_BIX on SH.SALES_TEST (CHANNEL_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_CUST_BIX on SH.SALES_TEST (CUST_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_PROD_BIX on SH.SALES_TEST (PROD_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_PROMO_BIX on SH.SALES_TEST (PROMO_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_TIME_BIX on SH.SALES_TEST (TIME_ID)
  nologging  local;
-- Create/Recreate primary, unique and foreign key constraints
alter table SH.SALES_TEST
  add constraint SALES_TEST_CHANNEL_FK foreign key (CHANNEL_ID)
  references SH.CHANNELS (CHANNEL_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_CUSTOMER_FK foreign key (CUST_ID)
  references SH.CUSTOMERS (CUST_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_PRODUCT_FK foreign key (PROD_ID)
  references SH.PRODUCTS (PROD_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_PROMO_FK foreign key (PROMO_ID)
  references SH.PROMOTIONS (PROMO_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_TIME_FK foreign key (TIME_ID)
  references SH.TIMES (TIME_ID)
  novalidate;

2.登录到源数据库,将表sh.sales_test所在的表空间设置为只读状态

SQL> alter tablespace sales_test read only;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     READ ONLY
9 rows selected

3.导出dump文件

SQL> create or replace directory tts_dump as '/tts';
Directory created
SQL> grant execute,read,write on directory tts_dump to public;
Grant succeeded


[root@jyrac1 ~]# su - oracle
[oracle@jyrac1 ~]$ expdp system/xxzx7817600 dumpfile=sales_test.dmp directory=tts_dump tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 transportable=always logfile=sales_test.log

Export: Release 11.2.0.4.0 - Production on Tue Jun 6 11:21:02 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=sales_test.dmp directory=tts_dump tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 transportable=always logfile=sales_test.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tts/sales_test.dmp
******************************************************************************
Datafiles required for transportable tablespace SALES_TEST:
  +DATADG/jyrac/datafile/sales_test_01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 6 11:21:22 2017 elapsed 0 00:00:17

4.将导出的dump文件传输到目标数据库

[oracle@jytest1 tts]$ scp oracle@10.138.130.151:/tts/sales_test.* /tts/
The authenticity of host '10.138.130.151 (10.138.130.151)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.151' (RSA) to the list of known hosts.
oracle@10.138.130.151's password:
sales_test.dmp                                                                                                                                                                                            100%  264KB 264.0KB/s   00:00
sales_test.log                                                                                                                                                                                            100% 1542     1.5KB/s   00:00
[oracle@jytest1 tts]$ ls -lrt
total 268
-rw-r----- 1 oracle oinstall 270336 Jun  6 18:49 sales_test.dmp
-rw-r--r-- 1 oracle oinstall   1542 Jun  6 18:49 sales_test.log

5.将sales_test表空间的数据文件传输到目标数据库
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.


SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'sales_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'sales_test_01.dbf');
PL/SQL procedure successfully completed

ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 06 18:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 06 18:00:00  Y    FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399

6.可选操作,将源数据库中的表空间sales_test设置为读写模式

SQL> alter tablespace sales_test read write;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
9 rows selected

7.在目标数据库上执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 dumpfile=sales_test.dmp directory=tts_dump transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/sales_test_01.dbf' tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 logfile=imp_sales_test.log

Import: Release 12.2.0.1.0 - Production on Tue Jun 6 19:23:09 2017

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_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 dumpfile=sales_test.dmp directory=tts_dump transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/sales_test_01.dbf tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 logfile=imp_sales_test.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 13 error(s) at Tue Jun 6 19:25:06 2017 elapsed 0 00:01:46

SQL> select owner,table_name,tablespace_name from dba_tables where owner='SH';
OWNER                                                                            TABLE_NAME                                                                       TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------
SH                                                                               SALES_TEST_SALES_TEST_Q1_2000                                                    SALES_TEST
SH                                                                               SALES_TEST_SALES_TEST_Q2_2000                                                    SALES_TEST
SH                                                                               SALES_TRANSACTIONS_EXT
SH                                                                               COSTS
SH                                                                               SALES
SH                                                                               CAL_MONTH_SALES_MV                                                               EXAMPLE
SH                                                                               FWEEK_PSCAT_SALES_MV                                                             EXAMPLE
SH                                                                               DIMENSION_EXCEPTIONS                                                             EXAMPLE
SH                                                                               SUPPLEMENTARY_DEMOGRAPHICS                                                       EXAMPLE
SH                                                                               COUNTRIES                                                                        EXAMPLE
SH                                                                               CUSTOMERS                                                                        EXAMPLE
SH                                                                               PROMOTIONS                                                                       EXAMPLE
SH                                                                               PRODUCTS                                                                         EXAMPLE
SH                                                                               TIMES                                                                            EXAMPLE
SH                                                                               CHANNELS                                                                         EXAMPLE

可以看到分区sales_test_q1_2000与sale_test_q2_2000导入后分别成为了一张非分区表

Oracle 12c full transportable export & import

传输数据库full transportable export/import
可以使用full transportable export/import功能将整个数据库从一个数据库实例复制到另一个数据库实例。可以使用Data Pump来生成一个导出dump文件,如果需要将这个dump文件传输到目标数据库,然后导入dump文件。另外也可以使用Data Pump跨网络来复制数据库。

数据库中要被传输的表空间可以是字典管理或本地管理表空间。源数据库中的表空间的块大小不必与目标数据库中标准块大小相同。

这种传输数据库的方法要求直到完成导出dump文件之前所要传输的用户创建的表空间必须设置为只读状态。如果不能满足这个条件那么可以使用备份功能来完成传输表空间。

full transportable export/import的限制
full transportable export/import有以下限制:
.对于不同字节编码的平台不能传输加密表空间,对于相同字节编码的平台为了传输加密表空间,在执行导出dump文件时需要设置encryption_pwd_prompt导出参数设置为yes,或者使用encryption_password导出参数。在导入dump文件时,使用与导出时相同的参数设置。
.当跨网络传输数据库时,如果在管理表空间(比如system或sysaux表空间)中存在包含long或long raw列的表,那么是不支持传输的。
.full transportable export/import可以使用传统的Data Pump导出/导入来导出与导入存储在管理表空间中用户创建的数据库对象,比如直接路径或外部表。管理表空间不是用户创建而是由数据库提供,比如sytem与sysaux表空间。
.full transportable export/import不能传输同时存储在管理表空间(比如system与sysaux)与用户创建表空间中的数据库对象。例如,一个分区表可能会同时存储在管理表空间与用户表空间中。如果有这样的对象,那么在传输之前应该重新定义这些对象,因此它们将整个存储在管理表空间或者用户表空间中。如果对象不能重定义,那么可以使用传统的Data Pump导出/导入。.当跨网络传输数据库时,当存储在管理表空间(比如system与sysaux)中的表它的审计跟踪住处本身存储在用户表空间中就不能启用审计。

使用导出dump文件来传输数据库
使用导出dump文件方式来传输数据库必须执行以下步骤:
1.在源数据库上,将每个用户表空间设置为只读状态。在执行导出操作时要确保设置参数transportable=always与full=y。如果源数据库的版本是11.2.0.3或11G之后的版本,那么还必须设置version=12或更高版本号。导出的dump文件包含了存储在用户表空间中对象的元数据与存储在管理表空间(比如system与sysaux)中用户创建对象的元数据与实际数据。

2.将导出的dump文件传输到目标数据库

3.将所有用户表空间的所有数据文件传输到目标数据库,如果源平台与目标平台不同,那么需要检查字节编码,可以通过查询v$transportable_platform视图进行查看。如果源平台与目标平台的字节编码不一样,那么使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会将源数据文件自动转换为目标平台的字节编码方式。
.使用rman的convert命令将源数据文件转换为目标平台的字节编码方式

4.可选操作,将源数据库中的将被传输的表空间设置为读写状态

5.在目标数据库中导入数据,当导入完成后,用户表空间将会设置为读写状态。

下面的例子将把jyrac数据库(11.2.0.4)传输到jypdb数据库(12.2的PDB),源数据库jyrac中用户表空间为test,users,example,源平台与目标平台的字节编码相同。具体操作如下:
1.将表空间test设置为只读状态

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.



SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
TEST                           READ ONLY

8 rows selected.

2.使用Data Pump导出工具执行full transportable export操作

SQL> create or replace directory tts_dump as '/tts';
Directory created
SQL> grant execute,read,write on directory tts_dump to public;
Grant succeeded

SQL> host expdp tts/tts@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log

Export: Release 11.2.0.4.0 - Production on Fri May 26 17:41:33 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "TTS"."SYS_EXPORT_FULL_01":  tts/********@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47.43 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX
ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088402C00006$$".
ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088405C00002$$".
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.976 KB      38 rows
. . exported "SYS"."AUD$"                                473.3 KB    2931 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.367 KB      10 rows
. . exported "WMSYS"."WM$ENV_VARS"                       5.921 KB       3 rows
......
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "TTS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TTS.SYS_EXPORT_FULL_01 is:
  /tts/exp_test.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  +DATADG/jyrac/datafile/example.260.930413057
Datafiles required for transportable tablespace TEST:
  +DATADG/jyrac/datafile/test01.dbf
Datafiles required for transportable tablespace USERS:
  +DATADG/jyrac/datafile/users.263.930413057
Job "TTS"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Fri May 26 17:47:08 2017 elapsed 0 00:05:31

在执行导出时必须指定transportable=always,它用来判断是否使用传输选项。full参数用来指定将导出整个数据库。dumpfile参数指定dump文件名。directory参数指定目录,它可以指向操作系统或ASM磁盘组。在执行导出前必须先创建目录,并授予读写权限。在non-CDB中,会自动创建目录对象DATA_PUMP_DIR,并且会自动授予DBA角色可以对其执行读写访问。因此sys与system用户就可以对目录执行读写操作。然而在PDB中不会自动创建目录DATA_PUMP_DIR。因此在导入PDB时,需要先创建目录。logfile参数用来指定导出操作日志文件。为了对数据库版本为11.2.0.3或以后的11G版本执行full transportable导出,必须使用version参数,并且必须指定为12或更高版本。

full transportable导入操作只有在Oracle 12c中支持,因此目标数据库必须为12c

3.将导出的dump文件传输到目标平台的所选定的目录中,该目录可以被目标数据库所访问在目标数据库中创建目录tts_dump(存储dump文件),tts_datafile(存储数据文件)

SQL> create or replace directory tts_dump as '/tts';

Directory created.

SQL> grant execute,read,write on directory tts_dump to public;

Grant succeeded.


SQL> create or replace directory tts_datafile as '+test/jycs/datafile';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中执行以下命令来传输dump文件

[oracle@jytest1 tts]$ scp -r oracle@10.138.130.152:/tts/exp_test.dmp /tts/
The authenticity of host '10.138.130.152 (10.138.130.152)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.152' (RSA) to the list of known hosts.
oracle@10.138.130.152's password:
exp_test.dmp                                                                                                                                                                                              100%   59MB  29.5MB/s   00:02
[oracle@jytest1 tts]$

4.从源平台将所有用户表空间传的相关数据文件输到目标平台的tts_datafile文件,通过dbms_file_transfer.put_file过程来实现。
创建源数据库连接目标数据库的数据链路

SQL> create database link jycs_link
  2  connect to system identified by "xxzx7817600"
  3  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.175)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = jycs)))';
Database link created

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATADG/jyrac/datafile/users.263.930413057                                       USERS
+DATADG/jyrac/datafile/undotbs1.262.930413057                                    UNDOTBS1
+DATADG/jyrac/datafile/sysaux.258.930413055                                      SYSAUX
+DATADG/jyrac/datafile/system.259.930413057                                      SYSTEM
+DATADG/jyrac/datafile/example.260.930413057                                     EXAMPLE
+DATADG/jyrac/datafile/undotbs2.261.930413057                                    UNDOTBS2
+DATADG/jyrac/datafile/test01.dbf                                                TEST
7 rows selected

需要传输的数据文件为test01.dbf,example.260.930413057与users.263.930413057

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

在目标数据库的ASM磁盘组可以看到相关的数据文件

ASMCMD [+test/jycs/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    users01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.281.945022371
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    test01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.279.945022099
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    example01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.280.945022161
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.281.945022371
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.280.945022161
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.279.945022099
DATAFILE  MIRROR  COARSE   MAY 26 06:00:00  Y    SYSAUX.260.942323941
DATAFILE  MIRROR  COARSE   MAY 20 22:00:00  Y    UNDOTBS1.259.942323977
DATAFILE  MIRROR  COARSE   MAY 11 12:00:00  Y    SYSTEM.269.942323889
DATAFILE  MIRROR  COARSE   MAY 11 00:00:00  Y    UNDOTBS2.266.942324411
DATAFILE  MIRROR  COARSE   MAY 02 11:00:00  Y    USERS.258.942323981

5.可选操作,将源数据库中的所有用户表空间设置为读写模式

SQL> alter tablespace users read write;

Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

5.在目标数据库上执行数据库导入

[oracle@jytest1 admin]$ impdp jy/jy@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri May 26 20:18:03 2017

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 "JY"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "JY"."SYS_IMPORT_TRANSPORTABLE_01":  jy/********@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf logfile=import.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"TEMP" already exists

Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
ORA-31685: Object type USER:"SYS" failed due to insufficient privileges. Failing sql is:
 ALTER USER "SYS" IDENTIFIED BY VALUES 'S:0C82FC9FD1570D45359355071D58A402378ABB404B83306BEA34DD19216F;D50A6384B1C2A4CF' TEMPORARY TABLESPACE "TEMP"
.....

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP"                 5.976 KB      38 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYS"."AMGT$DP$AUD$"                        473.3 KB    2931 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.367 KB      10 rows
. . imported "WMSYS"."E$ENV_VARS"                        5.921 KB       3 rows
. . imported "WMSYS"."E$EVENTS_INFO"                      5.75 KB      12 rows
. . imported "WMSYS"."E$HINT_TABLE"                       9.25 KB      72 rows
. . imported "WMSYS"."E$NEXTVER_TABLE"                   6.265 KB       1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE"         5.875 KB       1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE"                14.51 KB       1 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE"            6.851 KB       8 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows
. . imported "SYS"."NET$_ACL"                                0 KB       0 rows
. . imported "SYS"."WALLET$_ACL"                             0 KB       0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES"           0 KB       0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE"                   0 KB       0 rows
. . imported "WMSYS"."E$CONS_COLUMNS"                        0 KB       0 rows
. . imported "WMSYS"."E$INSTEADOF_TRIGS_TABLE"               0 KB       0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO"                       0 KB       0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES"                     0 KB       0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE"           0 KB       0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE"          0 KB       0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE"                0 KB       0 rows
. . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE"            0 KB       0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE"            0 KB       0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE"                   0 KB       0 rows
. . imported "WMSYS"."E$RIC_TABLE"                           0 KB       0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE"                  0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS"               0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_INFO"                         0 KB       0 rows
. . imported "WMSYS"."E$VERSION_TABLE"                       0 KB       0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE"                     0 KB       0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE"          0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
ORA-31693: Table data object "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-22303: type "SYS"."JDM_STR_VALS" not found
ORA-21700: object does not exist or is marked for delete

. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.859 KB       2 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP"               0 KB       0 rows
. . imported "WMSYS"."E$EXP_MAP"                             0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
ORA-39082: Object type PACKAGE BODY:"SYS"."WWV_DBMS_SQL" created with compilation warnings
......

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_EXPR" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_FORM" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_STANDARD" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_XLIFF" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_META_CLEANUP" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_ACC_LOAD" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRMMENU_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_OLB_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UPDATE_APX_APP" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UTILITIES" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_RPT_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CALENDAR2" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CHART2" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_REPORT3" created with compilation warnings

ORA-39082: Object type TRIGGER:"APEX_030200"."WWV_BIU_FLOW_SESSIONS" created with compilation warnings

Job "JY"."SYS_IMPORT_TRANSPORTABLE_01" completed with 536 error(s) at Fri May 26 20:45:45 2017 elapsed 0 00:27:38

检查表空间及其状态

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015          SYSTEM
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015          SYSAUX
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015        UNDOTBS1
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063          UNDO_2
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf                   USERS
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf                  TESTTB
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf                 EXAMPLE
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf                    TEST
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs2.278.945029905        UNDOTBS2
9 rows selected

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TESTTB                         ONLINE
TEMP2                          ONLINE
TEMP3                          ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
UNDOTBS2                       ONLINE
12 rows selected

对于要传输整个数据库来说,使用full transportable export /import这种方法要比传输表空间方便很多。

Proudly powered by WordPress | Indrajeet by Sus Hill.