oracle – 第13页 – 但行好事莫问前程

使用RMAN对CDB的root执行完全恢复

Posted on 2017年12月11日 by YongJing(敬勇)

如果数据损坏或用户错误只影响CDB的root容器，那么可能只会考虑恢复root容器。然而，Oracle强烈建议你在恢复root容器后恢复所有的PDB来阻止root与PDB中的元数据不一致的情况。在这种情况下，更好的方法是对整个CDB执行恢复操作。

使用RMAN对root执行完全恢复的操作如下:
1.启动RMAN并以有sysdba或sysbackup权限的公共用户连接到root容器。

[oracle@jytest1 ~]$ rman target/ catalog rco/xxxxxx@jypdb 

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Dec 11 15:55:18 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: JY (DBID=979425723, not open)
connected to recovery catalog database

2.将整个CDB启动到mount状态

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  8807168 bytes
Variable Size            1895828736 bytes
Database Buffers         4529848320 bytes
Redo Buffers                7966720 bytes
Database mounted.

SQL> select name from v$pdbs;

NAME
--------------------------------------------------------------------------------
PDB$SEED
JYPDB

3.可选操作，使用configure命令来配置缺省的设备类型与自动通道。

4.执行以下命令来还原与恢复root容器

RMAN> restore database root;

Starting restore at 11-DEC-17
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1521 instance=jy1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/JY/DATAFILE/system.317.962209603
channel ORA_DISK_1: restoring datafile 00003 to +DATA/JY/DATAFILE/sysaux.298.962209605
channel ORA_DISK_1: restoring datafile 00004 to +DATA/JY/DATAFILE/undotbs1.277.962209605
channel ORA_DISK_1: restoring datafile 00007 to +DATA/JY/DATAFILE/users.301.962209605
channel ORA_DISK_1: restoring datafile 00009 to +DATA/JY/DATAFILE/undotbs2.312.962209605
channel ORA_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_20171208_0fslkbg2_1_1
channel ORA_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_20171208_0fslkbg2_1_1 tag=TAG20171208T165528
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 11-DEC-17

RMAN> recover database root;

Starting recover at 11-DEC-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +TEST/arch/1_14_961976319.dbf
archived log for thread 1 with sequence 15 is already on disk as file +TEST/arch/1_15_961976319.dbf
archived log for thread 1 with sequence 16 is already on disk as file +TEST/arch/1_16_961976319.dbf
archived log for thread 1 with sequence 17 is already on disk as file +TEST/arch/1_17_961976319.dbf
archived log for thread 1 with sequence 18 is already on disk as file +TEST/arch/1_18_961976319.dbf
archived log for thread 1 with sequence 19 is already on disk as file +TEST/arch/1_19_961976319.dbf
archived log for thread 1 with sequence 20 is already on disk as file +TEST/arch/1_20_961976319.dbf
archived log for thread 1 with sequence 21 is already on disk as file +TEST/arch/1_21_961976319.dbf
archived log for thread 1 with sequence 22 is already on disk as file +TEST/arch/1_22_961976319.dbf
archived log for thread 1 with sequence 23 is already on disk as file +TEST/arch/1_23_961976319.dbf
archived log for thread 1 with sequence 24 is already on disk as file +TEST/arch/1_24_961976319.dbf
archived log for thread 1 with sequence 25 is already on disk as file +TEST/arch/1_25_961976319.dbf
archived log for thread 1 with sequence 26 is already on disk as file +TEST/arch/1_26_961976319.dbf
archived log for thread 1 with sequence 27 is already on disk as file +TEST/arch/1_27_961976319.dbf
archived log for thread 2 with sequence 12 is already on disk as file +TEST/arch/2_12_961976319.dbf
archived log for thread 2 with sequence 13 is already on disk as file +TEST/arch/2_13_961976319.dbf
archived log for thread 2 with sequence 14 is already on disk as file +TEST/arch/2_14_961976319.dbf
archived log for thread 2 with sequence 15 is already on disk as file +TEST/arch/2_15_961976319.dbf
archived log for thread 2 with sequence 16 is already on disk as file +TEST/arch/2_16_961976319.dbf
archived log for thread 2 with sequence 17 is already on disk as file +TEST/arch/2_17_961976319.dbf
archived log for thread 2 with sequence 18 is already on disk as file +TEST/arch/2_18_961976319.dbf
archived log for thread 2 with sequence 19 is already on disk as file +TEST/arch/2_19_961976319.dbf
archived log for thread 2 with sequence 20 is already on disk as file +TEST/arch/2_20_961976319.dbf
archived log file name=+TEST/arch/1_14_961976319.dbf thread=1 sequence=14
archived log file name=+TEST/arch/2_12_961976319.dbf thread=2 sequence=12
archived log file name=+TEST/arch/1_15_961976319.dbf thread=1 sequence=15
archived log file name=+TEST/arch/2_13_961976319.dbf thread=2 sequence=13
archived log file name=+TEST/arch/1_16_961976319.dbf thread=1 sequence=16
archived log file name=+TEST/arch/1_17_961976319.dbf thread=1 sequence=17
archived log file name=+TEST/arch/2_14_961976319.dbf thread=2 sequence=14
archived log file name=+TEST/arch/1_18_961976319.dbf thread=1 sequence=18
archived log file name=+TEST/arch/1_19_961976319.dbf thread=1 sequence=19
archived log file name=+TEST/arch/1_20_961976319.dbf thread=1 sequence=20
archived log file name=+TEST/arch/2_15_961976319.dbf thread=2 sequence=15
archived log file name=+TEST/arch/1_21_961976319.dbf thread=1 sequence=21
archived log file name=+TEST/arch/1_22_961976319.dbf thread=1 sequence=22
archived log file name=+TEST/arch/2_16_961976319.dbf thread=2 sequence=16
archived log file name=+TEST/arch/1_23_961976319.dbf thread=1 sequence=23
archived log file name=+TEST/arch/1_24_961976319.dbf thread=1 sequence=24
archived log file name=+TEST/arch/2_17_961976319.dbf thread=2 sequence=17
archived log file name=+TEST/arch/1_25_961976319.dbf thread=1 sequence=25
archived log file name=+TEST/arch/2_18_961976319.dbf thread=2 sequence=18
archived log file name=+TEST/arch/1_26_961976319.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:06:07
Finished recover at 11-DEC-17
starting full resync of recovery catalog
full resync complete

5.检查输出结果查看是否介质恢复成功。如果介质恢复成功继续下面的操作

6.强烈建议的操作，恢复所有PDB，包括CDB seed

RMAN> restore pluggable database 'PDB$SEED',jypdb;

Starting restore at 11-DEC-17
using channel ORA_DISK_1

skipping datafile 5; already restored to file +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
skipping datafile 6; already restored to file +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
skipping datafile 8; already restored to file +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
channel ORA_DISK_1: restoring datafile 00011 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
channel ORA_DISK_1: restoring datafile 00012 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
channel ORA_DISK_1: restoring datafile 00013 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
channel ORA_DISK_1: restoring datafile 00014 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
channel ORA_DISK_1: restoring datafile 00015 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
channel ORA_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_20171208_0gslkbie_1_1
channel ORA_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_20171208_0gslkbie_1_1 tag=TAG20171208T165528
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 11-DEC-17

RMAN> recover pluggable database 'PDB$SEED',jypdb;

Starting recover at 11-DEC-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +TEST/arch/1_14_961976319.dbf
archived log for thread 1 with sequence 15 is already on disk as file +TEST/arch/1_15_961976319.dbf
archived log for thread 1 with sequence 16 is already on disk as file +TEST/arch/1_16_961976319.dbf
archived log for thread 1 with sequence 17 is already on disk as file +TEST/arch/1_17_961976319.dbf
archived log for thread 1 with sequence 18 is already on disk as file +TEST/arch/1_18_961976319.dbf
archived log for thread 1 with sequence 19 is already on disk as file +TEST/arch/1_19_961976319.dbf
archived log for thread 1 with sequence 20 is already on disk as file +TEST/arch/1_20_961976319.dbf
archived log for thread 1 with sequence 21 is already on disk as file +TEST/arch/1_21_961976319.dbf
archived log for thread 1 with sequence 22 is already on disk as file +TEST/arch/1_22_961976319.dbf
archived log for thread 1 with sequence 23 is already on disk as file +TEST/arch/1_23_961976319.dbf
archived log for thread 1 with sequence 24 is already on disk as file +TEST/arch/1_24_961976319.dbf
archived log for thread 1 with sequence 25 is already on disk as file +TEST/arch/1_25_961976319.dbf
archived log for thread 1 with sequence 26 is already on disk as file +TEST/arch/1_26_961976319.dbf
archived log for thread 1 with sequence 27 is already on disk as file +TEST/arch/1_27_961976319.dbf
archived log for thread 2 with sequence 12 is already on disk as file +TEST/arch/2_12_961976319.dbf
archived log for thread 2 with sequence 13 is already on disk as file +TEST/arch/2_13_961976319.dbf
archived log for thread 2 with sequence 14 is already on disk as file +TEST/arch/2_14_961976319.dbf
archived log for thread 2 with sequence 15 is already on disk as file +TEST/arch/2_15_961976319.dbf
archived log for thread 2 with sequence 16 is already on disk as file +TEST/arch/2_16_961976319.dbf
archived log for thread 2 with sequence 17 is already on disk as file +TEST/arch/2_17_961976319.dbf
archived log for thread 2 with sequence 18 is already on disk as file +TEST/arch/2_18_961976319.dbf
archived log for thread 2 with sequence 19 is already on disk as file +TEST/arch/2_19_961976319.dbf
archived log for thread 2 with sequence 20 is already on disk as file +TEST/arch/2_20_961976319.dbf
archived log file name=+TEST/arch/1_14_961976319.dbf thread=1 sequence=14
archived log file name=+TEST/arch/2_12_961976319.dbf thread=2 sequence=12
archived log file name=+TEST/arch/1_15_961976319.dbf thread=1 sequence=15
archived log file name=+TEST/arch/2_13_961976319.dbf thread=2 sequence=13
archived log file name=+TEST/arch/1_16_961976319.dbf thread=1 sequence=16
archived log file name=+TEST/arch/1_17_961976319.dbf thread=1 sequence=17
archived log file name=+TEST/arch/2_14_961976319.dbf thread=2 sequence=14
archived log file name=+TEST/arch/1_18_961976319.dbf thread=1 sequence=18
archived log file name=+TEST/arch/1_19_961976319.dbf thread=1 sequence=19
archived log file name=+TEST/arch/1_20_961976319.dbf thread=1 sequence=20
archived log file name=+TEST/arch/2_15_961976319.dbf thread=2 sequence=15
archived log file name=+TEST/arch/1_21_961976319.dbf thread=1 sequence=21
archived log file name=+TEST/arch/1_22_961976319.dbf thread=1 sequence=22
archived log file name=+TEST/arch/2_16_961976319.dbf thread=2 sequence=16
archived log file name=+TEST/arch/1_23_961976319.dbf thread=1 sequence=23
archived log file name=+TEST/arch/1_24_961976319.dbf thread=1 sequence=24
archived log file name=+TEST/arch/2_17_961976319.dbf thread=2 sequence=17
archived log file name=+TEST/arch/1_25_961976319.dbf thread=1 sequence=25
archived log file name=+TEST/arch/2_18_961976319.dbf thread=2 sequence=18
archived log file name=+TEST/arch/1_26_961976319.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:02:52
Finished recover at 11-DEC-17
starting full resync of recovery catalog
full resync complete

检查输出结果查看是否介质恢复成功。如果介质恢复成功继续下面的操作

7.open CDB与所有的PDB

RMAN> alter database open;

Statement processed

RMAN> alter pluggable database all open;

Statement processed
starting full resync of recovery catalog
full resync complete

Oracle sharding database

Posted on 2017年10月17日 by YongJing(敬勇)

Sharding架构是数据库层面的一种分片技术，可以使分过区的数据分布在各不相同的独立数据库里。Sharding是Oracle Database 12c Release 2的新特性，它能为适合于 Sharding技术的OLTP应用提供线性扩展和完全错误隔离的能力，q 我们可以将 Sharding简单地理解为Oracle 表分区技术的扩展，下面将详细描述安装操作。

1.Oracle Sharding 安装条件
12.2企业版
non-cdb
使用文件系统而非ASM （12.2 Beta要求，正式发行后，可能会改）
主机hosts文件写上本机和各个shard node的IP解析
机器必须全新，不能残留之前有安装过oracle的信息。

2.设置环境变量，创建相关目录与设置内核参数
shardcat和gds都安装在一个主机上，同一个oracle用户，不同ORACLE_HOME。

[oracle12c@sdb1 ~]$ cat .bash_profile
# .bash_profile
 
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
 
# User specific environment and startup programs
 
PATH=$PATH:$HOME/bin
 
export PATH
 
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
 
ORACLE_BASE=/u01/ora12c/app/oracle; export ORACLE_BASE
DB_HOME=$ORACLE_BASE/product/12.2.0/db_1; export DB_HOME
GSM_HOME=$ORACLE_BASE/product/12.2.0/gsm; export GSM_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=shardcat; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch; export PATH
 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL

[root@shard1 ~]# groupadd -g 1009 dba
[root@shard1 ~]# groupadd -g 1010 oper
[root@shard1 ~]# groupadd -g 1011 oinstall
[root@shard1 ~]# useradd -u 1001 -g oinstall -G dba,oper oracle
[root@shard1 ~]# 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@shard1 ~]# mkdir -p /u01/app/oraInventory
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oraInventory
[root@shard1 ~]# chmod -R 775 /u01/app/oraInventory
[root@shard1 ~]# mkdir -p /u01/app/oracle
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle
[root@shard1 ~]# chmod -R 775 /u01/app/oracle
[root@shard1 ~]# mkdir -p /u01/app/oracle/product/12.2.0/db
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/db
[root@shard1 ~]# chmod -R 775 /u01/app/oracle/product/12.2.0/db
[root@shard1 ~]# mkdir /u01/tmp
[root@shard1 ~]# chmod a+wr /u01/tmp
[root@shard1 ~]# mkdir -p /u01/app/oracle/product/12.2.0/gsm
[root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/gsm
[root@shard1 ~]# chmod -R 775 /u01/app/oracle/product/12.2.0/gsm
[root@shard1 ~]# su - oracle

[oracle@shard1 ~]$ vi .bash_profile
export PATH
# .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/oracle
DB_HOME=/u01/app/oracle/product/12.2.0/db
GSM_HOME=/u01/app/oracle/product/12.2.0/gsm
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=jytest2
export ORACLE_UNQNAME=jytest
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

[oracle@shard1 ~]$ alias gsm_env='. /home/oracle/gsm_env'
[oracle@shard1 ~]$ alias db_env='. /home/oracle/db_env' 
[oracle@shard1 ~]$ vi gsm_env
ORACLE_HOME=$GSM_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

[oracle@shard1 ~]$ vi db_env
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

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

[root@shard1 ~]# vi /etc/sysctl.conf
# System default settings live in /usr/lib/sysctl.d/00-system.conf.
# To override those settings, enter new settings here, or in an /etc/sysctl.d/.conf file
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

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@shard1 ~]# 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文件中添加以下参数

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

3.安装Oracle软件

[root@shard1 soft]# unzip linuxx64_12201_database.zip

[root@shard1 soft]# chown -R oracle:oinstall database

[root@shard1 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@shard1 Desktop]# su - oracle
Last login: Thu Oct 12 12:01:58 CST 2017 on pts/1
[oracle@shard1 ~]$ export DISPLAY=:1
[oracle@shard1 ~]$ cd /soft/database
[oracle@shard1 database]$ ls -lrt
total 24
-rwxr-xr-x.  1 oracle oinstall  500 Feb  7  2013 welcome.html
drwxr-xr-x.  4 oracle oinstall 4096 Jan 26  2017 install
-rwxr-xr-x.  1 oracle oinstall 8771 Jan 26  2017 runInstaller
drwxr-xr-x.  2 oracle oinstall   34 Jan 26  2017 rpm
drwxrwxr-x.  2 oracle oinstall   28 Jan 26  2017 sshsetup
drwxrwxr-x.  2 oracle oinstall   58 Jan 26  2017 response
drwxr-xr-x. 14 oracle oinstall 4096 Jan 26  2017 stage
[oracle@shard1 database]$ ./runInstaller

[root@shard1 soft]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@shard1 soft]# /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]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
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_shard1_2017-10-12_13-06-02-537061115.log
Finished installing Oracle Trace File Analyzer (TFA)

其它两个shard主机shard2,shard3安装Oracle软件的操作不再描述同上。

shard2上的oracle环境变量设置如下,其中ORACLE_SID与
ORACLE_UNQNAME设置为sh1，在后面向shard catalog数据库注册shard时会读取到

[oracle@shard2 ~]$ cat .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/oracle
DB_HOME=/u01/app/oracle/product/12.2.0/db
GSM_HOME=/u01/app/oracle/product/12.2.0/gsm
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=sh1
export ORACLE_UNQNAME=sh1
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

shard3上的oracle环境变量设置如下,其中ORACLE_SID与
ORACLE_UNQNAME设置为sh1，在后面向shard catalog数据库注册shard时会读取到

[oracle@shard3 ~]$ 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/oracle
DB_HOME=/u01/app/oracle/product/12.2.0/db
GSM_HOME=/u01/app/oracle/product/12.2.0/gsm
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=sh2
export ORACLE_UNQNAME=sh2
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

4.安装Shard Director Software

[root@shard1 soft]# unzip linuxx64_12201_gsm.zip

[root@shard1 Desktop]# xhost + 
access control disabled, clients can connect from any host
[root@shard1 Desktop]# su - oracle
Last login: Thu Oct 12 18:05:56 CST 2017 on pts/0
[oracle@shard1 ~]$ export DISPLAY=:1
[oracle@shard1 ~]$ cd /soft/gsm
[oracle@shard1 gsm]$ ls -lrt
total 24
-rwxrwxr-x.  1 oracle oinstall  500 Feb  7  2013 welcome.html
-rwxr-xr-x.  1 oracle oinstall 8772 Jan 26  2017 runInstaller
drwxr-xr-x.  4 oracle oinstall 4096 Jan 26  2017 install
drwxrwxr-x.  2 oracle oinstall   28 Jan 26  2017 response
drwxr-xr-x. 14 oracle oinstall 4096 Jan 26  2017 stage
[oracle@shard1 gsm]$ ./runInstaller


[root@shard1 soft]# /u01/app/oracle/product/12.2.0/gsm/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/gsm

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.

5.创建shard catalog数据库
运行dbca开始建立数据库实例，这个实例是放分片数据的元数据的。我们把这个实例名叫shardcat。

[root@shard1 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@shard1 Desktop]# su - oracle
Last login: Thu Oct 12 18:35:35 CST 2017 on pts/1
[oracle@shard1 ~]$ export DISPLAY=:1
[oracle@shard1 ~]$ dbca

[oracle@shard1 arch]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-OCT-2017 18:58:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shard1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                12-OCT-2017 18:26:22
Uptime                    0 days 0 hr. 31 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "shardcat" has 1 instance(s).
  Instance "shardcat", status READY, has 1 handler(s) for this service...
Service "shardcatXDB" has 1 instance(s).
  Instance "shardcat", status READY, has 1 handler(s) for this service...
The command completed successfully

6.设置Oracle Sharding Management and Routing Tier
登录shardcat主机，登录shardcat数据库：–建立tablespace set需要使用omf，所以需要指定db_create_file_dest参数。

[oracle@shard1 ~]$ echo $ORACLE_SID
shardcat
[oracle@shard1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 12 19:01:30 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;

System altered.

SQL> alter system set open_links=16 scope=spfile;

System altered.

SQL> alter system set open_links_per_instance=16 scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8622776 bytes
Variable Size             620760392 bytes
Database Buffers         1509949440 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.
SQL> alter user gsmcatuser account unlock;

User altered.

SQL> alter user gsmcatuser identified by oracle;

User altered.

SQL> create user mygdsadmin identified by oracle;

User created.

SQL> grant connect, create session, gsmadmin_role to mygdsadmin;

Grant succeeded.

SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;

Grant succeeded.

（update 2016-11-10 注：在12.2. beta 2后可以不做这步）
SQL> alter system set events 'immediate trace name GWM_TRACE level 7'; 

System altered.

（update 2016-11-10  注：在12.2. beta 2后可以不做这步）
SQL> alter system set event='10798 trace name context forever, level 7' scope=spfile;

System altered.

SQL> execute dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/prvtrsch.plb 

PL/SQL procedure successfully completed.


Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Session altered.


Session altered.


Package created.

No errors.

Grant succeeded.


Session altered.


Session altered.


Package body created.

No errors.

Session altered.


Session altered.


Procedure created.

No errors.

Function created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Procedure created.

No errors.

Audit policy altered.


1 row updated.


Commit complete.


Session altered.


PL/SQL procedure successfully completed.

SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracle'); 

PL/SQL procedure successfully completed.

SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8622776 bytes
Variable Size             620760392 bytes
Database Buffers         1509949440 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.

在所有的shard节点分别执行Agent注册
登录shard2主机：

[oracle@shard2 ~]$ schagent -start

Scheduler agent started using port 65121
[oracle@shard2 ~]$ schagent status
Agent running with PID 12078

Agent_version:12.2.0.1.2
Running_time:00:00:10
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/12.2.0/db
ORACLE_BASE:/u01/app/oracle
Port:65121
Host:shard2

[oracle@shard2 ~]$ echo oracle|schagent -registerdatabase shard1 8080
Agent Registration Password ?  
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!

[oracle@shard2 ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@shard2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

登录shard3主机：

[oracle@shard3 ~]$ source .bash_profile
[oracle@shard3 ~]$ schagent -start

Scheduler agent started using port 35374
[oracle@shard3 ~]$ schagent -status
Agent running with PID 13019

Agent_version:12.2.0.1.2
Running_time:00:00:07
Total_jobs_run:0
Running_jobs:0
Platform:Linux
ORACLE_HOME:/u01/app/oracle/product/12.2.0/db
ORACLE_BASE:/u01/app/oracle
Port:35374
Host:shard3

[oracle@shard3 ~]$ echo oracle|schagent -registerdatabase shard1 8080
Agent Registration Password ?  
Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent
Agent Registration Successful!
[oracle@shard3 ~]$  mkdir -p /u01/app/oracle/oradata
[oracle@shard3 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

7.Deploying and Managing a System-Managed SDB
我们开始部署，以最简单的System-Managed SDB为例。
另外，admin guide中介绍的是4台主机做shard node，其中每2台互为dataguard主备。我们这边为了节约空间和资源，不搞dataguard了，只建立primary库。因此只要2台主机做shard node。先设置gsm的环境变量
进入到GDSCTL命令行，创建shard catalog。


[oracle@shard1 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@shard1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsm
[oracle@shard1 ~]$ export PATH=/u01/app/oracle/product/12.2.0/gsm/bin:$PATH:$HOME/bin
[oracle@shard1 ~]$ gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Thu Oct 12 19:24:36 CST 2017

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

Welcome to GDSCTL, type "help" for information.

Warning: current GSM name is not set automatically because gsm.ora contains zero or several GSM entries. Use "set  gsm" command to set GSM for the session.
Current GSM is set to GSMORA
GDSCTL>

GDSCTL>create shardcatalog -database shard1:1521:shardcat -chunks 12 -user mygdsadmin/oracle -sdb shardcat -region region1 -agent_port 8080 -agent_password oracle
Catalog is created

创建和启动shard director.
参数含义：
-gsm: 指定shard director名称
-listener: 指定shard director的监听端口，注意不能与数据库的listener端口冲突
-catalog: 指定catalog database 信息，catalog数据库的主机名:监听器port: catalog 数据库db_name


GDSCTL>add gsm -gsm sharddirector1 -listener 1571 -pwd oracle -catalog shard1:1521:shardcat -region region1
GSM successfully added

GDSCTL>start gsm -gsm sharddirector1
GSM is started successfully

添加操作系统认证.
GDSCTL>add credential -credential oracle_cred -osaccount oracle -ospassword oracle
The operation completed successfully

开始布署SharedDatabase。本例将布署System-ManagedSDB。
部署system-managed SDB

1.连接到shard director/GSM服务器(shard1)

[oracle@shard1 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@shard1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsm
[oracle@shard1 ~]$ export PATH=/u01/app/oracle/product/12.2.0/gsm/bin:$PATH:$HOME/bin
[oracle@shard1 ~]$ gdsctl
GDSCTL: Version 12.2.0.1.0 - Production on Thu Oct 12 19:35:21 CST 2017

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

Welcome to GDSCTL, type "help" for information.

Current GSM is set to SHARDDIRECTOR1

设置当前session为sharddirector1 shard director

GDSCTL>set gsm -gsm sharddirector1 
GDSCTL>connect mygdsadmin/oracle
Catalog connection is established

添加shardgroup， shardgroup是一组shard的集合，shardgroup名称为primary_shardgroup，-deploy_as primary表示这个group中的shard都是主库。

GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1
The operation completed successfully

将每个shard地址添加到catalog的valid node checking for registration (VNCR)列表，并且创建shard
GDSCTL>add invitednode shard2
GDSCTL>create shard -shardgroup primary_shardgroup -destination shard2 -credential oracle_cred
The operation completed successfully
DB Unique Name: sh1
GDSCTL>add invitednode shard3
GDSCTL>create shard -shardgroup primary_shardgroup -destination shard3 -credential oracle_cred
The operation completed successfully
DB Unique Name: sh2

检查配置

GDSCTL>config

Regions
------------------------
region1                       

GSMs
------------------------
sharddirector1                

Sharded Database
------------------------
shardcat                      

Databases
------------------------
sh1                           
sh2                           

Shard Groups
------------------------
primary_shardgroup            

Shard spaces
------------------------
shardspaceora                 

Services
------------------------

GDSCTL pending requests
------------------------
Command                       Object                        Status                        
-------                       ------                        ------                        

Global properties
------------------------
Name: oradbcloud
Master GSM: sharddirector1
DDL sequence #: 0


GDSCTL>config shardspace
Shard space                   Chunks                        
-----------                   ------                        
shardspaceora                 12                            

GDSCTL>config shardgroup
Shard Group         Chunks Region              Shard space         
-----------         ------ ------              -----------         
primary_shardgroup  12     region1             shardspaceora       

GDSCTL>config vncr
Name                          Group ID                      
----                          --------                      
shard2                                                      
shard3                                                      
10.138.130.180                                              

GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh1                 primary_shardgroup  U         none        region1   -            
sh2                 primary_shardgroup  U         none        region1   -

部署deploy
Shard数据库部署过程采用静默安装方式。

GDSCTL>deploy

此时，就开始部署shard了。在shard node上的agent会自动的调用netca和dbca，创建listener和database，2个shard node的操作是并行进行的。（如果是有datauard，那么是先建立一对主备，再建立另一对主备。）你可以在分别是两个shard node上ps -ef|grep ora_ 看到已经有sh1和sh2的实例了。我们可以执行在shard2与shard3上执行ps -ef | grep dbca与lsnrctl status,ps -ef | grep pmon来检查。

[root@shard2 ~]# ps -ef | grep dbca
oracle   20437 20429 99 19:49 pts/0    00:00:19 /u01/app/oracle/product/12.2.0/db/jdk/jre/bin/java -Doracle.installer.not_bootstrap=true -DCV_HOME=/u01/app/oracle/product/12.2.0/db -DORACLE_HOME=/u01/app/oracle/product/12.2.0/db -XX:-OmitStackTraceInFastThrow -XX:CompileCommand=quiet -XX:CompileCommand=exclude,javax/swing/text/GlyphView,getBreakSpot -DSET_LAF= -Dsun.java2d.font.DisableAlgorithmicStyles=true -Dice.pilots.html4.ignoreNonGenericFonts=true -DDISPLAY= -DJDBC_PROTOCOL=thin -mx512m -classpath /u01/app/oracle/product/12.2.0/db/assistants/dbca/jlib/dbca.jar:/u01/app/oracle/product/12.2.0/db/assistants/dbca/jlib/dbcaext.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/assistantsCommon.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/rconfig.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/asstcommonext.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraInstaller.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraPrereq.jar:/u01/app/oracle/product/12.2.0/db/inventory/prereqs/oui/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraCheckPoint.jar:/u01/app/oracle/product/12.2.0/db/jlib/cvu.jar:/u01/app/oracle/product/12.2.0/db/install/jlib/installcommons_1.0.0b.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/ssh.jar:/u01/app/oracle/product/12.2.0/db/jlib/ewt3.jar:/u01/app/oracle/product/12.2.0/db/jlib/ewtcompat-3_3_15.jar:/u01/app/oracle/product/12.2.0/db/jlib/share.jar:/u01/app/oracle/product/12.2.0/db/jlib/help4.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/db/jlib/oracle_ice.jar:/u01/app/oracle/product/12.2.0/db/jlib/kodiak.jar:/u01/app/oracle/product/12.2.0/db/lib/xmlparserv2.jar:/u01/app/oracle/product/12.2.0/db/jlib/orai18n.jar:/u01/app/oracle/product/12.2.0/db/jlib/ldapjclnt12.jar:/u01/app/oracle/product/12.2.0/db/jlib/netcfg.jar:/u01/app/oracle/product/12.2.0/db/jlib/ojmisc.jar:/u01/app/oracle/product/12.2.0/db/jlib/oraclepki.jar:/u01/app/oracle/product/12.2.0/db/jlib/opm.jar:/u01/app/oracle/product/12.2.0/db/jdbc/lib/ojdbc8.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvm.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvmhas.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvmasm.jar:/u01/app/oracle/product/12.2.0/db/dv/jlib/dvca.jar:/u01/app/oracle/product/12.2.0/db/jlib/gns.jar:/u01/app/oracle/product/12.2.0/db/jlib/commons-compress-1.8.jar oracle.assistants.dbca.driver.DBConfigurator -silent -responseFile /u01/app/oracle/product/12.2.0/db/shard_sh1_dbca.rsp -createDatabase -gdbName sh1 -sid sh1 -initparams db_unique_name=sh1,db_name=sh1,db_domain= -templateName /u01/app/oracle/product/12.2.0/db/shard_sh1_template.dbt -customscripts /u01/app/oracle/product/12.2.0/db/shard_sh1_postCR.sql -listeners LISTENER_sh1
root     20881 12186  0 19:49 pts/1    00:00:00 grep --color=auto dbca


[oracle@shard2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-OCT-2017 19:56:01

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_sh1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                12-OCT-2017 19:48:45
Uptime                    0 days 0 hr. 7 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shard2/listener_sh1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "sh1" has 1 instance(s).
  Instance "sh1", status RESTRICTED, has 1 handler(s) for this service...
Service "sh1_DGMGRL" has 1 instance(s).
  Instance "sh1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@shard2 ~]$ ps -ef | grep pmon
oracle   22086     1  0 19:54 ?        00:00:00 ora_pmon_sh1
oracle   22982  5293  0 19:56 pts/0    00:00:00 grep --color=auto pmon

GDSCTL>deploy
deploy: examining configuration...
deploy: deploying primary shard 'sh1' ...
deploy: network listener configuration successful at destination 'shard2'
deploy: starting DBCA at destination 'shard2' to create primary shard 'sh1' ...
deploy: deploying primary shard 'sh2' ...
deploy: network listener configuration successful at destination 'shard3'
deploy: starting DBCA at destination 'shard3' to create primary shard 'sh2' ...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: waiting for 2 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'shard2' for shard 'sh1'
deploy: waiting for 1 DBCA primary creation job(s) to complete...
deploy: DBCA primary creation job succeeded at destination 'shard3' for shard 'sh2'
deploy: requesting Data Guard configuration on shards via GSM
deploy: shards configured successfully
The operation completed successfully

我们可以检查一下shard的情况了：

GDSCTL>config shard
Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh1                 primary_shardgroup  Ok        Deployed    region1   ONLINE       
sh2                 primary_shardgroup  Ok        Deployed    region1   ONLINE       

GDSCTL>databases
Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     shardcat%1
Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Registered instances:
     shardcat%11

GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shard2:1521/sh1:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    

GDSCTL>config shard -shard sh2
Name: sh2
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shard3:1521/sh2:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------

创建service

GDSCTL>add service -service oltp_rw_srvc -role primary
The operation completed successfully
GDSCTL>start service -service oltp_rw_srvc
The operation completed successfully
GDSCTL>status service
Service "oltp_rw_srvc.shardcat.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
   Instance "shardcat%1", name: "sh1", db: "sh1", region: "region1", status: ready.
   Instance "shardcat%11", name: "sh2", db: "sh2", region: "region1", status: ready.

(其实这个service，用于adg的主备切换后，这个service漂移到备库上)

创建用户和对象
1. 在catalog数据库中创建业务用户

[oracle@shard2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 13:24:45 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session enable shard ddl;

Session altered.

SQL> create user app_schema identified by oracle;

User created.

SQL> grant all privileges to app_schema;

Grant succeeded.

SQL> grant gsmadmin_role to app_schema;

Grant succeeded.

SQL> grant select_catalog_role to app_schema;

Grant succeeded.

SQL> grant connect, resource to app_schema;

Grant succeeded.

SQL>  grant dba to app_schema;

Grant succeeded.

SQL> grant execute on dbms_crypto to app_schema;

Grant succeeded.

2. 利用应用用户登录,创建表空间集合

SQL>  conn app_schema/oracle
Connected.
SQL>  alter session enable shard ddl;

Session altered.

SQL> create tablespace set tsp_set_1 using template (datafile size 100m autoextend on next 10m maxsize unlimited extent management local segment space management auto);

Tablespace created.

3. 为duplicated tables创建表空间，这个测试中duplicated table是Products table.

SQL> create tablespace products_tsp datafile size 100m autoextend on next 10m maxsize unlimited extent management local uniform size 1m;

Tablespace created.

4. 创建shard表

SQL> create sharded table customers
  2    (
  3      custid      varchar2(60) not null,
  4      firstname   varchar2(60),
  5      lastname    varchar2(60),
  6      class       varchar2(10),
  7      geo         varchar2(8),
  8      custprofile varchar2(4000),
  9      passwd      raw(60),
 10      constraint pk_customers primary key (custid),
 11      constraint json_customers check (custprofile is json)
 12    ) tablespace set tsp_set_1
 13  partition by consistent hash (custid) partitions auto;

Table created.

SQL> create sharded table orders
  2  (
  3    orderid     integer not null,
  4    custid      varchar2(60) not null,
  5    orderdate   timestamp not null,
  6    sumtotal    number(19,4),
  7    status      char(4),
  8    constraint  pk_orders primary key (custid, orderid),
  9    constraint  fk_orders_parent foreign key (custid) 
 10    references customers on delete cascade
 11  ) partition by reference (fk_orders_parent);  

Table created.

5.为orders表的orderid列创建序列

SQL> create sequence orders_seq; 

Sequence created.

6. 创建SHARDED TABLE LineItems

SQL> create sharded table lineitems
  2  (
  3    orderid     integer not null,
  4    custid      varchar2(60) not null,
  5    productid   integer not null,
  6    price       number(19,4),
  7    qty         number,
  8    constraint  pk_items primary key (custid, orderid, productid),
  9    constraint  fk_items_parent foreign key (custid, orderid)
 10    references orders on delete cascade
 11  ) partition by reference (fk_items_parent);

Table created.

7. 创建duplicated tables.

SQL> create duplicated table products
  2  (
  3    productid  integer generated by default as identity primary key,
  4    name       varchar2(128),
  5    descruri   varchar2(128),
  6    lastprice  number(19,4)
  7  ) tablespace products_tsp;  

Table created.

8. 创建function，目的是为了后面的DEMO：

SQL> create or replace function passwcreate(passw in raw)
  2  return raw
  3  is
  4  salt raw(8);
  5  begin
  6  salt := dbms_crypto.randombytes(8);
  7  return utl_raw.concat(salt, dbms_crypto.hash(utl_raw.concat(salt,
  8  passw), dbms_crypto.hash_sh256));
  9  end;
 10  /

Function created.

SQL> create or replace function passwcheck(passw in raw, phash in raw)
  2  return integer is
  3  begin
  4  return utl_raw.compare(
  5  dbms_crypto.hash(utl_raw.concat(utl_raw.substr(phash, 1, 8),
  6  passw), dbms_crypto.hash_sh256),
  7  utl_raw.substr(phash, 9));
  8  end;
  9  /

Function created.

GDSCTL>connect mygdsadmin/oracle
Catalog connection is established

GDSCTL>show ddl
id DDL Text Failed shards
— ——– ————-
7 grant execute on dbms_crypto to app_s…
8 create tablespace set tsp_set_1 using…
9 create tablespace products_tsp datafi…
10 create sharded table customers ( …
11 create sharded table orders ( orde…
12 create sequence orders_seq
13 create sharded table lineitems ( o…
14 CREATE MATERIALIZED VIEW “APP_SCHEMA”…
15 create or replace function passwcreat…
16 create or replace function passwcheck…

10. 检查每个shard是否有DDL错误

GDSCTL>config shard -shard sh1
Name: sh1
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shard2:1521/sh1:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---   没有DDL错误
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    
oltp_rw_srvc                                                    Yes       Enabled   

GDSCTL>config shard -shard sh2
Name: sh2
Shard Group: primary_shardgroup
Status: Ok
State: Deployed
Region: region1
Connection string: shard3:1521/sh2:dedicated
SCAN address: 
ONS remote port: 0
Disk Threshold, ms: 20
CPU Threshold, %: 75
Version: 12.2.0.0
Failed DDL: 
DDL Error: ---   没有DDL错误
Failed DDL id: 
Availability: ONLINE
Rack: 


Supported services
------------------------
Name                                                            Preferred Status    
----                                                            --------- ------    
oltp_rw_srvc                                                    Yes       Enabled

验证环境-表空间/chunks
1. 在gsm(shard1)节点，检查chunks信息
前面创建shardcatalog时指定chunks为12，因此后续创建shard table分配12个chunks

GDSCTL>config chunks
Chunks
------------------------
Database                      From      To        
--------                      ----      --        
sh1                           1         6         
sh2                           7         12   


SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by  tablespace_name;

TABLESPACE_NAME                                                      MB
------------------------------------------------------------ ----------
PRODUCTS_TSP                                                        100
SYSAUX                                                              520
SYSTEM                                                              810
TSP_SET_1                                                           100
UNDOTBS1                                                             70
USERS                                                                 5

6 rows selected.


SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;

no rows selected


SQL> col TABLE_NAME for a20
SQL> col PARTITION_NAME for a20
SQL> col TABLESPACE_NAME for a20
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like '%SET%';

TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME
-------------------- -------------------- --------------------
CUSTOMERS            CUSTOMERS_P1         TSP_SET_1
ORDERS               CUSTOMERS_P1         TSP_SET_1
LINEITEMS            CUSTOMERS_P1         TSP_SET_1


SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files;

TABLESPACE_NAME              MB
-------------------- ----------
SYSTEM                      810
SYSAUX                      520
UNDOTBS1                     70
USERS                         5
TSP_SET_1                   100
PRODUCTS_TSP                100

6 rows selected.

SQL> select a.name Shard, count( b.chunk_number) Number_of_Chunks from gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b where a.database_num=b.database_num group by a.name;

SHARD                                                        NUMBER_OF_CHUNKS
------------------------------------------------------------ ----------------
sh1                                                                         6
sh2                                                                         6

2. 在shard2节点检查表空间和chunks信息
–表空间

[oracle@shard2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:25:30 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;

TABLESPACE_NAME                                                      MB
------------------------------------------------------------ ----------
C001TSP_SET_1                                                       100
C002TSP_SET_1                                                       100
C003TSP_SET_1                                                       100
C004TSP_SET_1                                                       100
C005TSP_SET_1                                                       100
C006TSP_SET_1                                                       100
PRODUCTS_TSP                                                        100
SYSAUX                                                              520
SYSTEM                                                              810
TSP_SET_1                                                           100
UNDOTBS1                                                             70
USERS                                                                 5

12 rows selected.

创建了6个表空间，分别是C001TSP_SET_1 ~ 表空间C006TSP_SET_1，因为设置chunks=12，每个shard有6个chunks。每个表空间有一个datafile，大小是100M，这个是在创建tablespace set时设置的datafile 100M。

–检查chunks

SQL> set linesize 140
SQL> column table_name format a20
SQL> column tablespace_name format a20
SQL> column partition_name format a20
SQL> show parameter db_unique_name
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 sh1
SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;

TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEITEMS            CUSTOMERS_P1         C001TSP_SET_1
CUSTOMERS            CUSTOMERS_P1         C001TSP_SET_1
ORDERS               CUSTOMERS_P1         C001TSP_SET_1
CUSTOMERS            CUSTOMERS_P2         C002TSP_SET_1
ORDERS               CUSTOMERS_P2         C002TSP_SET_1
LINEITEMS            CUSTOMERS_P2         C002TSP_SET_1
CUSTOMERS            CUSTOMERS_P3         C003TSP_SET_1
LINEITEMS            CUSTOMERS_P3         C003TSP_SET_1
ORDERS               CUSTOMERS_P3         C003TSP_SET_1
LINEITEMS            CUSTOMERS_P4         C004TSP_SET_1
CUSTOMERS            CUSTOMERS_P4         C004TSP_SET_1
ORDERS               CUSTOMERS_P4         C004TSP_SET_1
CUSTOMERS            CUSTOMERS_P5         C005TSP_SET_1
ORDERS               CUSTOMERS_P5         C005TSP_SET_1
LINEITEMS            CUSTOMERS_P5         C005TSP_SET_1
CUSTOMERS            CUSTOMERS_P6         C006TSP_SET_1
ORDERS               CUSTOMERS_P6         C006TSP_SET_1
LINEITEMS            CUSTOMERS_P6         C006TSP_SET_1

18 rows selected.

4. 在catalog数据库检查chunks信息

SQL> set echo off
SQL> select a.name Shard, count( b.chunk_number) Number_of_Chunks from gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b where a.database_num=b.database_num group by a.name;

SHARD                                                        NUMBER_OF_CHUNKS
------------------------------------------------------------ ----------------
sh1                                                                         6
sh2                                                                         6

5. 验证环境-tables
–catalog数据库

SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS
MLOG$_PRODUCTS
RUPD$_PRODUCTS

6 rows selected.

–shard节点shard2和shard3

[oracle@shard2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:36:12 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS


[oracle@shard3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:36:06 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn app_schema/oracle
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
ORDERS
LINEITEMS
PRODUCTS

–插入数据

SQL>  INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
  2      Class, Geo, Passwd) VALUES ('james.parker@x.bogus', 'James', 'Parker',
  3      NULL, 'Gold', 'east', hextoraw('8d1c00e'));

1 row created.

SQL> commit;

Commit complete.


SQL> set termout on
SQL> set linesize 120
SQL> set echo on
SQL> column firstname format a20
SQL> column lastname format a20
SQL> explain plan for SELECT FirstName,LastName, geo, class FROM Customers;

Explained.

SQL> select plan_table_output from table(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2953441084

--------------------------------------------------------------
| Id  | Operation        | Name | Cost (%CPU)| Inst   |IN-OUT|
--------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     0   (0)|        |      |
|   1 |  SHARD ITERATOR  |      |            |        |      |
|   2 |   REMOTE         |      |            | ORA_S~ | R->S |
--------------------------------------------------------------

Remote SQL Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------

   2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
       "A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS" FROM
       "CUSTOMERS" "A1" /* coord_sql_id=9j0dws979r7rr */  (accessing
       'ORA_SHARD_POOL@ORA_MULTI_TARGET' )


18 rows selected.

sharding在12.2中有太多的限制与坑，期待以后的改进。

Oracle 12c 使用SQL*Plus来创建与移动应用程序Seeds

Posted on 2017年9月12日 by YongJing(敬勇)

可以使用多种方法来创建应用程序seeds，包括使用CDB seed，克隆现有的PDB或Non-CDB，与附加一个卸载的PDB。也可以从应用程序容器中删除应用程序seeds。

为了在应用程序容器中创建新的应用程序seed，可以执行带有as seed子句的create pluggable database语句。可以使用应用程序种子为应用程序提供一个应用程序容器。通常在应用程序seed创建之前应用程序容器的应用程序被安装在应用程序root中。在应用程序seed创建之后它将与应用程序root进行同步，因此应用程序被安装在应用程序seed中。当创建应用程序seed之后，使用应用程序seed创建的任何PDB都安装应用程序。当应用程序root中的应用程序被升级或打补丁后，应用程序seed必须使用应用程序root进行同步来应用这些改变。

通过执行带有as seed子句的create pluggable database语句来创建应用程序seed。

一个应用程序容器可以有零或一个应用程序seed。当使用as seed子句创建应程序seed时，不用指定它的名字。应用程序seed名字总是以application_container_name$SEED命名，其中application_container_name是应用程序seed的应用程序容器的名字。例如，在salesact应用程序容器中的应用程序seed它的名字必须是salesact$SEED。

当创建一个新的应用程序seed时，必须为在create pluggable database语句中为应用程序容器指定管理员。这个语句将在应用程序容器中创建一个本地用户的管理员，并且被授予pdb_dba角色。

创建应用程序seed的所需要满足的条件:
.CDB必须存在

.CDB必须处于读写模式

.应用程序seed所属的应用程序容器必须处于读写模式

.当前用户必须是一个公共用户，应用程序seed所属的应用程序root是当前容器

.当前用户有create pluggable database系统权限

.在应用程序容器中对于包含应用程序的应用程序seed，应用程序必须安装在应用程序root

创建应用程序seed
可以通过执行带有as seed子句的create pluggable database语句来创建应用程序。应用程序容器中的应用程序seed类似于CDB中的seed。一个应用程序seed能用来快速与简单的创建满足应用程序容器要求的应用程序PDB。创建应用程序seed的操作如下:
1.在SQL*Plus中，确保当前容器是应用程序root。

2.执行带有as seed子句的create pluggable database语句来创建应用程序seed。根据需要还可以指定其它子句。在创建完应用程序seed后，它处于mounted模式，状态为new。可以通过查询v$pdbs视图的open_mode列来检查应用程序seed的打开模式。可以通过查询cdb_pdbs或者dba_pdbs视图的status列来查看应用程序seed的状态。还会为应用程序seed创建缺省的服务名。服务名与应用程序seed同名并且可以被用来访问应用程序seed。

3.以读写模式来打开新的应用程序seed

4.为了将新应用程序seed集成到应用程序容器中必须以读写模式来打开新的应用程序seed。如果试图以只读模式来打开新的应用程序seed将会返回错误信息。在应用程序seed以读写模式打开后，它的状态将为normal。

5.执行一个或多个以下操作:
5.a 如果使用CDB seed中创建应用程序seed，那么将容器切换到应用程序seed，并且执行带有sync子句的alter pluggable database语句来同步应用程序seed。同步使用应用程序root来实例化应用程序seed中的一个或多个应用程序root的应用程序。

5.b 如果使用应用程序root中创建应用程序seed，那么将容器切换到应用程序seed，然后执行pdb_to_apppdb.sql脚本来将应用程序root转换为应用程序PDB。

当通过克隆一个应用程序PDB来创建应用程序seed时这些操作不需要执行。

6.关闭应用程序seed，然后以只读模式来打开它。

7.备份应用程序seed。

使用CDB seed来创建应用程序seed
这个例子假设满足以下条件:
.应用程序seed将被创建在名为salesact的应用程序容器中。

.对应用程序seed不使用存储限制，因此不指定storage子句。

.应用程序seed不创建缺省表空间。

.不指定path_prefix子句。

.不指定file_name_convert与create_file_dest子句。可以对CDB启用OMF或设置pdb_file_name_convert初始化参数。与CDB相关的文件将会基于OMF配置或参数设置被复制到新目录中。

.在目标目录中没有与新temp文件同名的文件存在，将会创建新的temp文件，因此不用指定tempfile reuse子句。

.不需要预先定义Oracle角色被授予给pdb_dba角色。

执行的语句如下:
先切换到应用程序容器salesact中

SQL> alter session set container=salesact;

Session altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE

执行语句来使用CDB seed来在应用程序容器salesact中创建应用程序seed，并打开应用程序seed。

SQL> create pluggable database as seed admin user actseedadm identified by "xxzx7817600";

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESACT$SEED                                                                                                                    MOUNTED

SQL> alter pluggable database salesact$seed open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESACT$SEED                                                                                                                    READ WRITE

切换容器到应用程序seed(salesact$SEED)中，使用应用程序root中的所有应用程序来同步应用程序seed。

SQL> alter session set container=salesact$seed;

Session altered.

SQL>  select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT$SEED                                                                                                                    READ WRITE

SQL> alter pluggable database application all sync;

Pluggable database altered.

关闭应用程序seed(salesact$seed)，然后以只读模式来打开应用程序seed。

SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT$SEED                                                                                                                    MOUNTED

SQL> alter pluggable database  open read only;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT$SEED                                                                                                                    READ ONLY

因为应用程序容器名为salesact，所以应用程序seed名为salesact$seed。在创建应用程序seed时一起创建了一个本地管理用户并且被授予了pdb_dba公共角色。如果这个用户在创建应用程序seed时没有授予管理权限，那么使用sys与system公共用户来管理应用程序seed。当创建应用程序seed时，将使用应用程序root来同步应用程序seed。因此应用程序seed将包含安装在应用程序root中的应用程序与应用程序公共对象它们是这些应用程序的一部分。当使用应用程序seed来创建新的应用程序PDB时，应用程序PDB也会包含这些安装的应用程序与应用程序公共对象。

使用应用程序PDB创建应用程序seed
这个例子假设满足以下条件:
.在应用程序容器salesact中创建了应用程序seed。

.将在应用程序PBD(salesapppdb)所在的应用程序容器中创建应用程序seed。

.对应用程序seed不使用存储限制，因此不指定storage子句。

.应用程序seed不包含缺省表空间。

.不指定path_prefix子句。

.不指定file_name_convert与create_file_dest子句。可以启用OMF或设置pdb_file_name_convert初始化参数。与应用程序root相关的文件会基于OMF的配置或初始化参数设置被复制到新目录中。

.在目标目录中没有与新temp文件同名的文件存在，因此不需要使用tempfile reuse子句。

切换容器到应用程序容器(salesact)，并执行以下命令来创建应用程序seed。

SQL> create pluggable database as seed from salesapppdb;

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESAPPPDB                                                                                                                      READ WRITE
SALESACT$SEED                                                                                                                    MOUNTED

然后打开应用程序seed，再关闭应用程序seed，最后再以只读方式打开应用程序seed。

SQL> alter pluggable database salesact$seed open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESAPPPDB                                                                                                                      READ WRITE
SALESACT$SEED                                                                                                                    READ WRITE

SQL> alter pluggable database salesact$seed close immediate;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESAPPPDB                                                                                                                      READ WRITE
SALESACT$SEED                                                                                                                    MOUNTED

SQL> alter pluggable database salesact$seed open read only;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESAPPPDB                                                                                                                      READ WRITE
SALESACT$SEED                                                                                                                    READ ONLY

因为应用程序容器名为salesact，所以应用程序seed名字默认为salesact$seed。应用程序seed是使用应用程序pdb而创建的，因此应用程序seed就包含了安装在应用程序root中的应用程序与应用程序公共对象，它们是这些应用程序的一部分。当使用应用程序seed来创建新的应用程序pdb时，应用程序pdb也会包含安装的应用程序与应用程序公共对象。

使用应用程序root来创建应用程序seed
这个例子假设满足以下条件:
.应用程序seed将创建在应用程序容器(salesact)中。应用程序seed使用对应用程序容器的root进行克隆进行创建。

.对应用程序seed不使用存储限制，因此不使用storage子句。

.应用程序seed不包含缺省表空间。

.不使用path_prefix子句。

.不使用file_name_convert与create_file_dest子句。可以启用OMF或设置pdb_file_name_convert初始化参数。基于OMF配置或初始化参数的设置与应用程序root相关的文件会被复制到新目录中。

.在目标目录中没有与新temp文件同名的文件存在，因此不需要使用tempfile reuse子句。

切换到应用程序容器(salesact)中，执行下面的命令来创建应用程序seed。

SQL> create pluggable database as seed from salesact;

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESACT$SEED                                                                                                                    MOUNTED
SALESAPPPDB                                                                                                                      READ WRITE

打开应用程序seed，切换容器到应用程序seed，然后执行pdb_to_apppdb.sql脚本将应用程序root转换为应用程序pdb。

SQL> alter pluggable database salesact$seed open;

Warning: PDB altered with errors.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESACT$SEED                                                                                                                    READ WRITE
SALESAPPPDB                                                                                                                      READ WRITE

SQL> alter session set container=salesact$seed;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql

...
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> 
SQL> WHENEVER SQLERROR CONTINUE;



SQL> alter pluggable database close immediate instances=all;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT$SEED                                                                                                                    MOUNTED

1 row selected.

SQL> alter pluggable database open read only instances=all;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT$SEED                                                                                                                    READ ONLY

1 row selected.

因为应用程序容器名为salesact，所以应用程序seed名为salesact$seed。应用程序seed是使用应用程序root进行创建的。因此应用程序seed包含安装在应用程序root中的应用程序与应用程序公共对象，它们是这些应用程序的一部分。当使用应用程序seed来创建新应用程序PDB时，应用程序pdb也会包含安装的应用程序与应用程序公共对象。

从应用程序容器中拔出应用程序seed
拔出应用程序seed就是断开应用程序seed与应用程序容器的关联。当不再需要应用程序seed时可以将其删除。拔出应用程序seed类似于拔出PDB。为了拔出应用程序seed，连接到它的应用程序root并使用alter pluggable database语句来指定生成xml文件或.pdb文件。当指定xml文件后，在卸载完成后生成的xml文件会包含描述应用程序seed的元数据。

SQL> alter pluggable database salesact$seed close immediate instances=all;

Pluggable database altered.



SQL>  select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESACT$SEED                                                                                                                    MOUNTED
SALESAPPPDB                                                                                                                      READ WRITE

3 rows selected.



SQL> alter pluggable database salesact$seed unplug into '/tts/plug/salesact_seed.xml';

Pluggable database altered.

删除应用程序seed
当不再需要应用程序seed时，可以执行drop pluggable database语句来删除。当删除应用程序seed时，CDB的控制文件会被修改来删除与被删除应用程序seed的所有相关信息，但是归档重做日志与备份不会被删除，但可以使用RMAN来删除它们。

SQL> drop pluggable database salesapppdb including datafiles;

Pluggable database dropped.

创建应用程序PDB
可以在应用程序root容器中执行create pluggable database语句来创建应用程序PDB。创建应用程序pdb与在CDB root中创建PDB都是使用相同的SQL语句。当在应用程序root中执行create pluggable database语句时新创建的PDB为应用程序PDB。SQL语句必须在应用程序root中执行并且对应用程序root中所定义的应用程序数据库有显式依赖性。创建应用程序PDB的操作如下:

1.在SQL*Plus中，确保当前容器为应用程序root

2.执行create pluggable database语句。在应用程序PDB完成创建之后，它处于mounted模式并且状态为NEW。可以通过查询v$pdbs视图的open_mode列来查看应用程序pdb的打开模式，可以通过查询cdb_pdbs或dba_pdbs视图的status列来查看应用程序pdb的状态。对于新创建的应用程序PDB也会创建一个缺省的服务名，服务名与应用程序PDB同名并且可以被用来访问应用程序PDB。

3.以读写模式来打开应用程序PDB

4.为了将新的应用程序PDB集成到应用程序容器必须以读写模式来打开新的应用程序PDB。如果试图以只读模式来打开应用程序PDB将会返回错误信息。在应用程序PDB以读写模式打开后它的状态将变为NORMAL。

5.切换容器为应用程序PDB

6.执行alter pluggable database … sync来同步应用程序PDB。同步使用应用程序PDB来实例化应用程序PDB中的一个或多个应用程序root中的应用程序。

7.关闭应用程序PDB，然后以只读模式打开。

8.备份应用程序PDB。

SQL> alter session set container=salesact;

Session altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE

1 row selected.



SQL> create pluggable database salesapppdb admin user salesapppdbadm identified by "xxzx7817600";

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESAPPPDB                                                                                                                      MOUNTED

2 rows selected.

SQL> alter pluggable database salesapppdb open read write instances=all;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESACT                                                                                                                         READ WRITE
SALESAPPPDB                                                                                                                      READ WRITE

2 rows selected.

SQL> alter session set container=salesapppdb;

Session altered.


SQL> alter pluggable database application all sync; 

Pluggable database altered.

SQL> alter pluggable database salesapppdb close immediate instances=all;

Pluggable database altered.

SQL>  select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESAPPPDB                                                                                                                      MOUNTED

1 row selected.

SQL> alter pluggable database salesapppdb open read only instances=all;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
SALESAPPPDB                                                                                                                      READ ONLY

1 row selected.

Oracle 12c使用SQL*Plus来创建与删除应用程序容器

Posted on 2017年9月12日 by YongJing(敬勇)

可以使用不同方式来创建应用程序容器，比如使用CDB seed，克隆现有的PDB或Non-CDB，插入一个被拔出的PDB，也可以从CDB中删除应用程序容器。

创建应用程序容器
可以使用create pluggable database语句来在CDB中创建应用程序容器。当执行create pluggable database语句时指定as application container子句可以创建一个新的应用程序容器。应用程序容器由应用程序root与用来存储一个或多个应用程序数据的一组应用程序PDB组成。应用程序PDB可以被附加到应用程序root中，并且可以选择快速地创建应用程序seed与简单地创建新的应用程序PDB。应用程序PDB与应用程序root可以共享应用程序公共对象。

有三种类型的应用程序公共对象:
.元数据链接应用程序公共对象存储特定对象的元数据，比如表。因此容器可以共享有相同结构没有相同数据的应用程序公共对象。

.数据链接应用程序公共对象只在应用程序root中定义一次并且在应用程序PDB的上下文中共享只读对象。

.扩展数据链接应用程序公共对象在应用程序root中存储共享数据但也允许应用程序PDB来将数据添加到对象中。添加的数据是本地数据它对于每个应用程序PDB来说是唯一的。

通过执行creatae pluggable database as application container子句可以创建应用程序容器。也可以使用以下技术来创建应用程序容器:
.使用CDB seed
.克隆现有的PDB或Non-CDB
.迁移PDB
.插入被拔出的PDB

当当前容器是CDB的root并且在执行create pluggable database语句时指定了as application container子句时，将创建应用程序容器。

迁移现有的应用程序为应用程序容器
可以通过使用现有的PDB来创建应用程序容器。当迁移现有应用程序为一个应用程序容器时必须完成额外的任务。要附加的PDB必须包含应用程序数据库对象，包含它们的数据，并且对于应用程序所访问的相关数据库对象必须执行dbms_pdb中的过程。当应用程序公共用户，角色或profiles存放在应用程序root中，对与应用程序相关的数据库对象必须执行dbms_pdb包。在应用程序迁移为应用程序容器后，可以在应用程序容器中创建应用程序PDB，并且可以使用现有的PDB来创建应用程序PDB。

创建应用程序容器的准备
在创建应用程序容器之前必须完达到以下条件:
.CDB必须存在

.CDB必须处于读写模式

.当前容器如果是CDB的root，那么当前用户必须是一个公共用户

.当前用户必须有create pluggable database系统权限

.对于每个应用程序容器必须要决定一个唯一的应用程序容器名字。每个应用程序容器名字必须对于单个CDB中的所有容器来说是唯一的，并且每个应用程序容器名字必须在特定监听所监听的所有CDB范围内是唯一的。应用程序容器名字用来区分CDB中的应用程序容器。应用程序容器名字遵守与服务名一样的规则，它是大小写敏感的。

.如果在使用物理备库的Data Guard配置中创建应用程序容器，那么在创建应用程序容器之前必须完成额外的任务。

.如果使用PDB将现有的应用程序迁移成应用程序容器，那么它必须能够通过克隆PDB来创建应用程序root，迁移PDB到应用程序root中，或者将PDB插入到应用程序root中。

创建应用程序容器
可以执行create pluggable database … as application container语句来创建应用程序容器。创建应用程序容器的操作如下:
1.在SQL*Plus中，确保当前容器为CDB的root。

2.执行create pluggable database … as application container语句。如果需要还可以指定其它子句。在创建应用程序容器后，它处于mounted模式，并且它的状态为NEW。可以查询v$pdbs视图的open_mode列来查看应用程序容器的打开模式。可以通过查询cdb_pdbs或dba_pdbs视图来查看应用程序状态。并且会对应用程序容器创建一个新的缺省服务。这个服务与应用程序容器有相同的名字，并且可以被用来访问应用程序容器。

3.以读写模式来打开新的应用程序容器。为了将新应用程序容器集成到CDB中必须以读写模式来打开新的应用程序容器。如果你试图以只读模式来打开应用程序容器将会返回错误住处。在应用程序容器以读写模式打开后，它的状态将会变为NORMAL。

4.备份应用程序容器

使用CDB Seed来创建应用程序容器
这个例子假设满足以下条件:
.对应用程序容器没有使用存储限制。因此不指定storage子句。

.应用程序容器不创建缺省表空间

.不指定path_prefix子句

.不指定file_name_convert与create_file_dest子句。可以启用OMF或设置pdb_file_name_convert参数。与CDB Seed相关的文件基于OMF配置或参数设置将会被复制到新目录中。

.在目标目录中如果没有与新temp文件同名的文件存储，将会创建新temp文件，因此不指定tempfile reuse子句。

.没有预先定义的Oracle角色需要被授予给PDB_DBA角色。

SQL> create pluggable database salesact as application container admin user salesadm identified by "xxzx";

Pluggable database created.


SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
JYPDB                                                                                                                            READ WRITE
ORCLPDB                                                                                                                          READ WRITE
SALESACT                                                                                                                         MOUNTED


SQL> alter pluggable database salesact open read write;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
JYPDB                                                                                                                            READ WRITE
ORCLPDB                                                                                                                          MOUNTED
SALESACT                                                                                                                         READ WRITE

通过克隆本地PDB来创建应用程序容器
这个例子假设满足以下条件:
.不指定path_prefix子句

.指定file_name_convert子句来指定复制文件所存放的目录。在这个例子中，文件从
+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/与+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/TEMPFILE/目录中复制到+test/jy/hract目录中。不指定create_file_dest子句，并且也不使用OMF或pdb_file_name_conver参数。

.对于应用程序root使用存储限制。因此需要指定storage子句。这里指定属于应用程序root的所有表空间大小不能超过6G。这种存储限制不会应用到附加到应用程序root中的应用程序PDB。

.在目标目录中没有与新temp文件同名的文件存在，所以将会创建新的temp文件。因此不用指定tempfile reuse子句。

SQL> create pluggable database hract as application container from jypdb
  2  file_name_convert = ('+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/system.297.954436417', '+test/jy/hract/system01.dbf',
  3                       '+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/sysaux.276.954436419','+test/jy/hract/sysaux01.dbf',
  4                       '+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/undotbs1.296.954436417','+test/jy/hract/undotsb1.dbf',
  5                       '+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/undo_2.274.954436487','+test/jy/hract/undo_2.dbf',
  6                       '+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/TEMPFILE/temp.275.954436435','+test/jy/hract/temp01.dbf'
  7                       )
  8  storage (maxsize 6g);

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
HRACT                                                                                                                            MOUNTED
JYPDB                                                                                                                            READ WRITE
SALESACT                                                                                                                         READ WRITE

通过插入被拔出的PDB来创建应用程序容器
这个例子假设满足以下条件:
.新应用程序容器不是基于已经被用来创建PDB或应用程序容器的同一个被拔出的PDB而进行创建。因此不用指定as clone子句。

.不指定path_prefix子句

.xml元数据文件没有精确地描述文件的当前目录，因此需要指定source_file_name_convert或source_file_directory子句。在这个例子中XML文件指示文件在+DATA/JY/DATAFILE/，但实际文件是存储在+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/与+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/TEMPFILE/目录中，指定source_file_name_convert子句。

.文件存储在正确目录中，因此不指定nocopy子句。

.对应用程序容器使用存储限制，因此指定storage子句。指定应用程序容器的所有表空间大小不超过4G。

.在目标目录中有与新temp文件同名的文件存在，为了创建新的temp文件。因此指定tempfile reuse子句。

SQL> create pluggable database payrollact as application container using '/tts/plug/jypdb.xml'
  2  source_file_name_convert = ('+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/', '+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/DATAFILE/',
  3                              '+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/TEMPFILE/','+DATA/JY/58E7574E7926075EE053AC828A0AA4C9/TEMPFILE/'
  4                              )
  5  nocopy
  6  storage (maxsize 4g)
  7  tempfile reuse;

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
PAYROLLACT                                                                                                                       MOUNTED

SQL> alter pluggable database payrollact open read write;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
PAYROLLACT                                                                                                                       READ WRITE

从CDB中拔出应用程序容器
为了拔出应用程序容器必须满足以下条件:
.当前用户必须有sysdba或sysoper管理权限

.应用程序容器必须至少打开过一次

.应用程序容器不能包含任何应用程序PDB

.应用程序容器不能包含任何应用程序Seed

SQL> alter pluggable database payrollact unplug into '/tts/plug/payrollact.xml';

Pluggable database altered.

删除应用程序容器
可以使用drop pluggable database语句来删除应用程序容器。当想要将一个应用程序容器从一个CDB中移动到另一个CDB中或不再需要这个应用程序容器时可以将其删除。

删除应用程序容器与删除PDB是很类似的。当删除应用程序容器时，CDB的控制文件会被修改来删除与被删除应用程序容器相关的信息。与应用程序容器相关的归档重做日志与备份不会被删除，但可以使用RMAN来删除它们。

当删除应用程序容器时，可以使用下面的子句来保留或删除数据文件:
. keep datafiles，缺省值，保留数据文件，即使指定了keep datafiles子句应用程序容器的temp文件仍然会被删除，因为temp文件不再需要。

.including datafiles，删除数据文件。如果应用程序容器是使用snapshot copy子句创建的，那么当删除应用程序容器时必须指定including datafiles子句。

要删除应用程序容器必须满足以下条件:
.应用程序容器必须处于mounted状态或者unplugged状态。

.当前用户必须有sysdba或sysoper管理权限。

.应用程序容器不能包含任何应用程序PDB。

.应用程序容器不能包含任何应用程序Seed。

当删除应用程序容器时保留数据文件

drop pluggable database payrollact keep datafiles;

当删除应用程序容器时并且删除数据文件

drop pluggable database payrollact including  datafiles;

Oracle 12c 使用Non-CDB来创建PDB

Posted on 2017年9月12日 by YongJing(敬勇)

可以移动Non-CDB来变为一个PDB。可以使用以下方式来完成这个任务:
.通过克隆Non-CDB来创建PDB。从Oracle 12.1.0.2开始，可以通过克隆Non-CDB来创建PDB。这种方法是创建PDB最简单的一种方法，但它需要从Non-CDB中把文件复制到新目录中。

.使用dbms_pdb包来生成XML元数据文件，XML元数据文件用来描述Non-CDB的数据文件可以用来将其附加到CDB中。这种方法比克隆Non-CDB要执行更多的步骤，但它能让你不用移动Non-CDB的文件来创建PDB。为了使用这种技术，Non-CDB必须是Oracle 12C的Non-CDB。如果当前的Non-CDB使用Oracle 12C之前的版本，那么你必须将Non-CDB升级到Oracle 12C。

.使用Oracle Data Pump导出/导入
可以使用Data Pump从Non-CDB中导出数据然后导入到PDB中。当你执行导入时，在用户名后要指定PDB的连接标识符。例如，如果PDB的连接标识符为hrpdb，那么在执行导入进执行以下命令:
imdp username@hrpdb ….如果Non-CDB的版本为11.2.0.3或之后的版本，那么可以使用完全传输导出/导入来移动数据。当将版本为11.2.0.3或之后的11g版本的Non-CDB迁移到Oracle 12C，那么在导出时需要将version参数设置为12.0.0.0.0或更高版本。如果Non-CDB的版本为11.2.0.3之前的版本，那么可以使用传输表空间来移动数据或者执行完全数据库导出\导入。

.使用goldengate复制
可以使用goldengate从Non-CDB中复制数据到PDB中。

对Non-CDB执行dbms_pdb包
可以使用dbms_pdb包来为Non-CDB生成XML元数据文件来将其附加到CDB中。使用dbms_pdb包移动Non-CDB为PDB的操作如下:
1.如果CDB不存在先创建CDB

2.确保Non-CDB处于事务一致状态并将它置于只读状态

[oracle@jytest1 ~]$ export ORACLE_SID=orcl1
[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 00:38:31 2017

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  8628936 bytes
Variable Size            2583692600 bytes
Database Buffers         1694498816 bytes
Redo Buffers                8146944 bytes
Database mounted.
SQL> alter database open read only;

Database altered.


[oracle@jytest2 ~]$ export ORACLE_SID=orcl2
[oracle@jytest2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 7 00:38:31 2017

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  8628936 bytes
Variable Size            2583692600 bytes
Database Buffers         1694498816 bytes
Redo Buffers                8146944 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

3.连接到Non-CDB，并执行dbms_pdb.describe过程来创建描述Non-CDB的XML元数据文件。执行该过程的用户必须有sysdba权限。

SQL> exec dbms_pdb.describe(pdb_descr_file=>'/cdb_pdb/orcl.xml');

PL/SQL procedure successfully completed.


[oracle@jytest2 cdb_pdb]$ ls -lrt
total 8
-rw-r--r-- 1 oracle asmadmin 6963 Sep  7 00:18 orcl.xml

4.执行dbms_pdb.check_plug_compatibility过程来判断是否Non-CDB与目标CDB兼容。当执行这个过程时设置以下参数:
-pdb_descr_file:设置XML元数据文件的完整路径。
-pdb_name:指定新PDB名字，如果这个参数被忽略，那么将使用XML元数据文件中的PDB名字。

SQL> set serveroutput on
  1  declare
  2  compatible constant varchar2(3) :=
  3  case dbms_pdb.check_plug_compatibility(
  4  pdb_descr_file => '/cdb_pdb/orcl.xml',
  5  pdb_name => 'orclpdb')
  6  when true then 'yes'
  7  else 'no'
  8  end;
  9  begin
 10  dbms_output.put_line(compatible);
 11  end;
 12  /
yes

PL/SQL procedure successfully completed.

如果输出为yes，那么Non-CDB是兼容的，并且可以继续下一步操作。如果输出为no，那么Non-CDB与目标CDB不兼容，并且可以检查pdb_plug_in_violations视图来检查为什么不兼容。所有的违反条目在继续操作之前必须被修复。例如，任何版本或补丁不匹配可以通过执行升级或打补丁来解决。在修复这个违反条目后，再次执行dbms_pdb.check_plug_compatibility来确保Non-CDB与目标CDB兼容。

5.关闭Non-CDB

[grid@jytest1 ~]$ srvctl stop database -db orcl

6.插入Non-CDB

SQL> create pluggable database orclpdb using '/cdb_pdb/orcl.xml'
  2  copy
  3  file_name_convert = ('+DATA/orcl/datafile/', '+data/jy/orclpdb/','+DATA/orcl/tempfile/','+data/jy/orclpdb/');

Pluggable database created.


SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
JYPDB                                                                                                                            READ WRITE
ORCLPDB                                                                                                                          MOUNTED

ASMCMD [+data/jy/orclpdb] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   SEP 07 00:00:00  N    usertbs01.dbf => +DATA/jy/4D913A1436E25EE2E053AB828A0AF7B8/DATAFILE/USERTBS.278.954031319
DATAFILE  UNPROT  COARSE   SEP 07 00:00:00  N    users01.dbf => +DATA/jy/4D913A1436E25EE2E053AB828A0AF7B8/DATAFILE/USERS.302.954031321
DATAFILE  UNPROT  COARSE   SEP 07 00:00:00  N    undotbs01.dbf => +DATA/jy/4D913A1436E25EE2E053AB828A0AF7B8/DATAFILE/UNDOTBS1.273.954031321
TEMPFILE  UNPROT  COARSE   SEP 07 00:00:00  N    temp01.dbf => +DATA/jy/4D913A1436E25EE2E053AB828A0AF7B8/TEMPFILE/TEMPTS1.315.954031465
DATAFILE  UNPROT  COARSE   SEP 07 00:00:00  N    system01.dbf => +DATA/jy/4D913A1436E25EE2E053AB828A0AF7B8/DATAFILE/SYSTEM.303.954031321
DATAFILE  UNPROT  COARSE   SEP 07 00:00:00  N    sysaux01.dbf => +DATA/jy/4D913A1436E25EE2E053AB828A0AF7B8/DATAFILE/SYSAUX.295.954031321

如果这里没有任何错误，那么现在不打开新PDB。

7.执行$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql脚本，这个脚本必须在第一次打开新PDB之前执行。如果PDB不是由Non-CDB创建而来，那么就不需要执行这个脚本。为了执行noncdb_to_pdb.sql脚本，完成以下操作:
a.访问PDB，当前用户必须有sysdba权限，并且权限必须是公共或本地授予给PDB。最好使用as sysdba进行连接。

b.执行noncdb_to_pdb.sql脚本:

SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

8.以读写模式打开新PDB

SQL> alter pluggable database orclpdb open read write;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
JYPDB                                                                                                                            READ WRITE
ORCLPDB                                                                                                                          READ WRITE

9.备份PDB
到此使用Non-CDB来创建PDB的操作就完成了。

Oracle 12c Relocate PDB

Posted on 2017年8月29日 by YongJing(敬勇)

在oracle 12.2中可以对pdb执行relocate失踪，下面的例子将介绍对远程CDB中将名为jypdb的PDB进行迁移操作。假设满足以下条件:
.当前用户在被迁移PDB所在的CDB的root容器中有create pluggable database系统权限

.目标CDB连接PDB当前CDB的dblink名为jycdb_link。dblink的创建语句如下:

create public database link jycdb_link connect to c##yyl
identified by "yyl" using 'jy';

PDB的当前CDB中的公共用户c##yyl有sysoper管理权限与create pluggable database系统权限

.不指定path_prefix子句

.不指定file_name_convert与create_file_dest子句。当启用OMF或设置pdb_file_name_convert参数时，基于OMF的配置或参数的设置文件会被移动到新目录。

.对PDB不指定存储限制。因此不指定storage子句

.在目标目录中不存在相同名字的temp文件，那么新的temp文件会被创建。因此不指定tempfile reuse子句。

.连接会被自动从源PDB迁移到迁移后的PDB。因此指定availability max子句。

执行以下语句来将PDB(jypdb)从远程CDB迁移到当前CDB中:
1.在远程CDB中创建公共用户c##yyl

SQL> create user c##yyl identified by "yyl" container=all;

User created.

SQL> grant sysoper,connect,resource,create pluggable database to c##yyl container=all;

Grant succeeded.

2.检查远程CDB是否使用本地undo与归档

SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A30
SQL>
SQL> SELECT property_name, property_value
  2  FROM   database_properties
  3  WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE


SQL> SELECT log_mode FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

因为远程CDB使用本地undo与归档，因此不需要将远程数据库设置为只读模式

3.在本地CDB(jy)中创建指定远和CBD(jy)的dblink。连接串中要包含(SERVER=DEDICATED)条目录，否则会收到 “ORA-01031: insufficient privileges”错误

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

Database link created.


SQL> select * from dual@jycdb;

D
-
X

4.检查本地CDB是否使用了本地undo与归档

SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A30
SQL>
SQL> SELECT property_name, property_value
  2  FROM   database_properties
  3  WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL> SELECT log_mode FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

5.在本地CDB执行下面的语句来克隆可刷新的PDB

SQL> create pluggable database jypdb from jypdb@jycdb relocate availability max;

Pluggable database created.
SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
JYPDB                                                                                                                            READ WRITE

以read write方式来打开PDB以完成迁移操作

SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> alter session set container=jypdb;

Session altered.

SQL> set long 200
SQL> set linesize 200
SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
JYPDB                                                                                                                            READ WRITE

删除公共dblink

SQL> drop public database link jycdb;

Database link dropped.

检查远程PDB可以看到已经被删除了

SQL> alter session set container=jypdb;

Session altered.

SQL> select name,open_mode from v$pdbs;

no rows selected

Oracle 12c Refreshable Clone

Posted on 2017年8月24日2017年8月24日 by YongJing(敬勇)

这个例子将介绍通过克隆一个远程PDB(jypdb)来创建一个目标PDB(ycpdb)。这种克隆是对源PDB的一种可以刷新的副本，它意味着对源PDB所执行的任何改变都可以通过刷新来对目标PDB进行更新。这里假设满足以下条件:
.连接到远程PDB(jypdb)的dblink为jypdb_link
.不使用path_prefix子句
.不使用file_name_convert与create_file_dest子句，如果启用了OMF，或者设置了pdb_file_name_convert参数。那么基于OMF或参数设置
文件将会被复制到新指定的目录中
.对PBD不使用存储限制。因此不使用storage子句
.这里没有与新temp文件同名的文件存在，因此新的temp文件会创建到目标目录中。因此不用指定tempfile reuse子句。
.刷新克隆将会每隔10分钟自动刷新。记住，为了创建一个可刷新的PDB，源PDB必须启用archivelog模式与本地undo模式。

1.在目标数据库CDB(jy)中创建指向源PBD(jypdb)的dblink

SQL> create public database link  jypdb_link
  2    connect to system identified by "cs"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.13.17)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jypdb)
 10      )
 11    )';
Database link created

2.检查源pdb是否启用arachivelog与本地undo

SQL> col PROPERTY_NAME for a25;
SQL> col PROPERTY_VALUE for a25;
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_VALUE
------------------------- -------------------------
LOCAL_UNDO_ENABLED        TRUE

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/arch
Oldest online log sequence     251
Next log sequence to archive   253
Current log sequence           253

3.在目标数据库CDB(jy)执行下面的语句来克隆可刷新的PDB

SQL> create pluggable database ycpdb from jypdb@jypdb_link refresh mode every 10 minutes;

Pluggable database created.

4.在源数据库中对表jy.jy_test与jy.test插入数据与删除数据

SQL> select * from jy.jy_test;

   USER_ID
----------
         2
         1

SQL> select * from jy.test;

   USER_ID
----------
         2
         1


SQL> insert into jy.jy_test values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from jy.test where rownum<2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> host date
Fri Aug 25 01:58:40 CST 2017

5.十分钟后我们在目标数据库ycpdb中来查询数据是否被刷新到目标数据库ycpdb中
5.1先将pdb(ycpdb)以read only模式打开

SQL> select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
YCPDB
MOUNTED


SQL> alter  pluggable database ycpdb open read only;

Pluggable database altered.

5.2再来查看数据是否被刷新,从下面的结果可以看到数据已经被刷新了。

SQL> select * from jy.jy_test;

   USER_ID
----------
         2
         3
         1

SQL> select * from jy.test;

   USER_ID
----------
         1

6.为了与源pdb(jypdb)进行同步我们需要将目标pdb设置为close状态

SQL> alter  pluggable database ycpdb close immediate;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;


NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
YCPDB
MOUNTED

我们还可以手动刷新目标pdb

SQL> alter  pluggable database ycpdb close immediate;

Pluggable database altered.

SQL> alter pluggable database refresh;

Pluggable database altered.

Oracle 12c 手动创建CDB

Posted on 2017年8月8日 by YongJing(敬勇)

使用create database创建CDB的具体操作如下:
1.指定实例标识(SID)
ORACLE_SID环境变量被用来区分不同的实例。
1.决定实例的唯一标识SID
2.打开命令窗口
3.设置ORACLE_SID环境变量

在Unix/Linux下设置ORACLE_SID环境变量如下:
export ORACLE_SID=mynewdb
或
setenv ORACLE_SID=mynewdb

在Windows下设置ORACLE_SID环境变量如下:
set ORACLE_SID=mynewdb

2.确保所需的环境变量被设置
依赖于平台，在启动SQL*Plus之后，可能需要设置相关的环境变量，或者验证相关的设置。例如，在大多数平台中,ORACLE_SID与ORACLE_HOME必须设置。另外，建议PATH环境变量包含ORACLE_HOME/bin目录。在Unix/Linux平
台中，必须手动设置这些环境变量。在Windows平台中，OUI会自动设置ORACLE_HOME与ORACLE_SID。如果在安装期间不创建数据库，OUI不会设置ORACLE_SID，并且在之后创建数据库时必须要设置ORACLE_SID环境变量。

3.选择数据库管理员审核方法
为了创建数据库，用户必须被审核并且被授予相关的系统权限。审核方法有以下两种:
.使用密码文件
.使用操作系统审核

4.创建初始化参数文件
当Oracle实例启动时，它将读取初始化参数文件。这个参数文件可以是文本文件可以使用文本编辑器进行编辑，或者是二进制文件，可以由数据库进行动态修改。二进制参数文件也叫服务器参数文件。对于这一步操作，可以先创建一个文本参数文件，之后通过文本参数文件来创建服务器参数文件。

5.创建实例只限于Windows平台
对于Windows平台，在连接实例之前，必须手动创建实例。ORADIM命令就是用来创建新实例，其语法如下:oradim -NEW -SID sid -STARTMODE MANUAL -PFILE file.注意在创建新实例时，不要将-STARTMODE参数指定为AUTO，因为这会造成新实例启动与mount数据库，而这时数据库是不存在的。

6.连接实例
启动SQL*Plus并且使用有sysdba权限的用户连接到数据库实例。
.使用密码文件进行审核，输入以下命令并输入sys用户的密码
$sqlplus /nolog
SQL>connect sys as sysdba

.使用操作系统审核，输入以下命令
$sqlplus /nolog
SQL>conn / as sysdba

7.创建服务器参数文件
服务器参数文件能通过alter system命令来修改参数，并且这种修改会永久生效。可以通过文本参数文件来创建服务器参数文件。

8.启动实例
启动实例但不mount数据库执行以下命令
startup nomount

9.使用create database语句来创建CDB
当使用create database语句来创建CDB时，必须在操作CDB之前完成额外的操作。这些操作包含对数据字典表创建视图，安装标准的PL/SQL包。执行catcdb.sql脚本。

使用create database语句来创建语句需要注意
9.1 将enable_pluggable_database参数设置为true。在CDB中，db_name参数指定root的名称。将SID设置为root名称是常见的做法。这个名称最多有30个字符。

9.2使用create database语句来创建新的CDB。
9.2.1 不使用OMF来创建CDB

9.2.2 使用OMF来创建CDB

不使用OMF来创建CDB
下面的例子将介绍如何不使用OMF功能来创建CDB
1.设置SID

[root@jytest3 ~]# su - oracle
Last login: Fri Aug  4 15:07:33 CST 2017
[oracle@jytest3 ~]$ cd $ORACLE_HOME/dbs

[oracle@jytest3 dbs]$ export ORACLE_SID=test

2.创建密码文件

[oracle@jytest3 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwtest password=abcdefg format=12.2 entries=20

3.创建初始化参数

[oracle@jytest3 dbs]$ vi inittest.ora
db_name='test'
memory_target=4G
memory_max_target=4G
control_files='+data/test/controlfile/testcdb/control01.ctl','+data/test/controlfile/testcdb/control02.ctl'
enable_pluggable_database=true

4.启动实例但不mount

[oracle@jytest3 dbs]$ export ORACLE_SID=test
[oracle@jytest3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 20:59:37 2017

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

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/inittest.ora' nomount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  8628936 bytes
Variable Size            2315257144 bytes
Database Buffers         1962934272 bytes
Redo Buffers                8146944 bytes

5.执行create database语句来创建CDB
下面的语句将创建一个名为test的CDB数据库。这个名字与参数文件中的db_name同名。并且满足以下条件:
.已经设置control_files参数
.创建了+data/test/datafile/testcdb目录
.创建了+data/test/datafile/pdbseed目录
.创建了+data/test/onlinelog/testcdb目录

为了创建包含root与CDB seed的CDB库在create database语句中包含了enable pluggable database子句。在这个例子还包含了seed file_name_convert子句来指定CDB seed文件的文件名与目录。

SQL> create database test
  2  user sys identified by xxzx_7817600
  3  user system identified by xxzx_7817600
  4  logfile group 1 ('+data/test/onlinelog/testcdb/redo01.log')
  5  size 100m blocksize 512,
  6  group 2 ('+data/test/onlinelog/testcdb/redo02.log')
  7  size 100m blocksize 512,
  8  group 3 ('+data/test/onlinelog/testcdb/redo03.log')
  9  size 100m blocksize 512
 10  maxloghistory 1
 11  maxlogfiles 16
 12  maxlogmembers 3
 13  maxdatafiles 1024
 14  character set al32utf8
 15  national character set al16utf16
 16  extent management local
 17  datafile '+data/test/datafile/testcdb/system01.dbf'
 18  size 700m reuse autoextend on next 10240k maxsize unlimited
 19  sysaux datafile '+data/test/datafile/testcdb/sysaux01.dbf'
 20  size 550m reuse autoextend on next 10240k maxsize unlimited
 21  default tablespace deftbs
 22  datafile '+data/test/datafile/testcdb/deftbs01.dbf'
 23  size 500m reuse autoextend on maxsize unlimited
 24  default temporary tablespace tempts1
 25  tempfile '+data/test/datafile/testcdb/temp01.dbf'
 26  size 20m reuse autoextend on next 640k maxsize unlimited
 27  undo tablespace undotbs1
 28  datafile '+data/test/datafile/testcdb/undotbs01.dbf'
 29  size 200m reuse autoextend on next 5120k maxsize unlimited
 30  enable pluggable database
 31  seed file_name_convert = ('+data/test/datafile/testcdb/','+data/test/datafile/pdbseed/')
 32  local undo on;

Database created.

6.执行脚本$ORACLE_HOME/rdbms/admin/catcdb.sql

SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql
SQL>
SQL> Rem The script relies on the caller to have connected to the DB
SQL>
SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to
SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL>
SQL> Rem $ORACLE_HOME
SQL> column oracle_home new_value oracle_home noprint
SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;




SQL>
SQL> Rem OS-dependent slash
SQL> column slash new_value slash noprint
SQL> select sys_context('userenv', 'platform_slash') as slash from dual;




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin
SQL> column rdbms_admin new_value rdbms_admin noprint
SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;
old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual
new   1: select '/u01/app/oracle/product/12.2.0/db'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint
SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;
old   1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual
new   1: select '/u01/app/oracle/product/12.2.0/db/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual




SQL>
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter value for 1:
Enter value for 2:
Can't locate Term/ReadKey.pm in @INC (you may need to install the Term::ReadKey module) (@INC contains: /u01/app/oracle/product/12.2.0/db/rdbms/admin /usr/lib/perl5/site_perl/5.22.0/x86_64-linux /usr/lib/perl5/site_perl/5.22.0 /usr/lib/perl5/5.22.0/x86_64-linux /usr/lib/perl5/5.22.0 .) at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catcdb.pl line 30.
BEGIN failed--compilation aborted at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catcdb.pl line 30.

对于这种错误参考了杨建荣的解决方法，抛出的错误提示找不到ReadKey.pm，Linux，Unix其实都是自带Perl的，但这里需要的文件在$ORACLE_HOME下的Perl目录，只需要把这个目录引用到PATH变量中就可以了，比如：
export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$ORACLE_HOME/jdk/bin

[oracle@jytest3 dbs]$ export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$ORACLE_HOME/jdk/bin
[oracle@jytest3 dbs]$ export ORACLE_SID=test
[oracle@jytest3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 22:12:56 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql
SQL>
SQL> Rem The script relies on the caller to have connected to the DB
SQL>
SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to
SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL>
SQL> Rem $ORACLE_HOME
SQL> column oracle_home new_value oracle_home noprint
SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;




SQL>
SQL> Rem OS-dependent slash
SQL> column slash new_value slash noprint
SQL> select sys_context('userenv', 'platform_slash') as slash from dual;




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin
SQL> column rdbms_admin new_value rdbms_admin noprint
SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;
old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual
new   1: select '/u01/app/oracle/product/12.2.0/db'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint
SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;
old   1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual
new   1: select '/u01/app/oracle/product/12.2.0/db/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual




SQL>
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter value for 1:
Enter value for 2:
Can't locate util.pm in @INC (you may need to install the util module) (@INC contains: /u01/app/oracle/product/12.2.0/db/rdbms/admin /u01/app/oracle/product/12.2.0/db/perl/lib/site_perl/5.22.0/x86_64-linux-thread-multi /u01/app/oracle/product/12.2.0/db/perl/lib/site_perl/5.22.0 /u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0/x86_64-linux-thread-multi /u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0 .) at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catcdb.pl line 35.
BEGIN failed--compilation aborted at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catcdb.pl line 35.

这个问题把util改为Util


[oracle@jytest3 ~]$  find $ORACLE_HOME -name util.pm | wc -l
0

[oracle@jytest3 ~]$ find $ORACLE_HOME -name Util.pm | wc -l
5
[oracle@jytest3 ~]$ find $ORACLE_HOME -name Util.pm
/u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash/Util.pm
/u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0/x86_64-linux-thread-multi/List/Util.pm
/u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0/x86_64-linux-thread-multi/Scalar/Util.pm
/u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0/x86_64-linux-thread-multi/Sub/Util.pm
/u01/app/oracle/product/12.2.0/db/perl/lib/site_perl/5.22.0/HTTP/Headers/Util.pm

这个过程中到底该选哪个目录下的Util.pm呢，如果多点耐心仔细看看里面的内容还是能够找到一些头绪的，最后选择的是：

/u01/app/oracle/product/12.2.0/db/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash/Util.pm

需要手工修改catcdb.pl脚本
那么问题来了，这个catcdb.pl脚本是不是要改动呢。修改文件catcdb.pl，把下面的util修改为Util

use Term::ReadKey; # to not echo password
use Getopt::Long;
use Cwd;
use File::Spec;
use Data::Dumper;
use Utilqw(trim, splitToArray);
use catcon qw(catconSqlplus);

再来一轮测试，结果发现还是会有报错，这种尝试会让你开始怀疑自己的选择到底是不是正确的方向。如果还是没有找到，说明在当前的环境变量中没有匹配到相关的内容，我们需要直接切换到目录Hash下，然后运行脚本才可以，这个时候输出才算有了改观，提示你输入密码。

[oracle@jytest3 Hash]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 22:25:23 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catcdb.sql /main/7 2016/06/23 11:38:38 akruglik Exp $
SQL> Rem
SQL> Rem catcdb.sql
SQL> Rem
SQL> Rem Copyright (c) 2013, 2016, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         catcdb.sql - 
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         invoke catcdb.pl
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         
SQL> Rem
SQL> Rem    PARAMETERS:
SQL> Rem         - log directory
SQL> Rem         - base for log file name
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    akruglik    06/21/16 - Bug 22752041: pass --logDirectory and
SQL> Rem                           --logFilename to catcdb.pl
SQL> Rem    akruglik    11/10/15 - use catcdb.pl to collect passowrds and pass them
SQL> Rem                           on to catcdb_int.sql using env vars
SQL> Rem    aketkar     04/30/14 - remove SQL file metadata
SQL> Rem    cxie        08/16/13 - remove SQL_PHASE
SQL> Rem    cxie        07/10/13 - 17033183: add shipped_file metadata
SQL> Rem    cxie        03/19/13 - create CDB with all options installed
SQL> Rem    cxie        03/19/13 - Created
SQL> Rem
SQL>
SQL> set echo on
SQL>
SQL> Rem The script relies on the caller to have connected to the DB
SQL>
SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to
SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL>
SQL> Rem $ORACLE_HOME
SQL> column oracle_home new_value oracle_home noprint
SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;




SQL>
SQL> Rem OS-dependent slash
SQL> column slash new_value slash noprint
SQL> select sys_context('userenv', 'platform_slash') as slash from dual;




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin
SQL> column rdbms_admin new_value rdbms_admin noprint
SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;
old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual
new   1: select '/u01/app/oracle/product/12.2.0/db'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint
SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;
old   1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual
new   1: select '/u01/app/oracle/product/12.2.0/db/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual




SQL>
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter new password for SYS: xxzx_7817600
Enter new password for SYSTEM: xxzx_7817600
Enter temporary tablespace name: tempts1
No options to container mapping specified, no options will be installed in any containers
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catalog_catcon_27898.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catalog*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catalog_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catproc_catcon_3352.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catproc*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catproc_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catoctk_catcon_9051.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catoctk*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catoctk_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/owminst_catcon_9233.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/owminst*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/owminst_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11572.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11761.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_*.lst] files for spool files, if any
validate_script_path: sqlplus script /u01/app/oracle/product/12.2.0/db/sqlplus/admin/help/hlpbld does not exist or is unreadable
catconExec: empty Path returned by validate_script_path for
    SrcDir = /u01/app/oracle/product/12.2.0/db/sqlplus/admin/help, FileName = hlpbld
catcon.pl: Unexpected error encountered in catconExec; exiting
exec_DB_script: /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11761_exec_DB_script.done did not need to be deleted before running a script
exec_DB_script: opened Reader and Writer
exec_DB_script: connected
exec_DB_script: executed set echo on

exec_DB_script: executed @@/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_kill_sess_11761_ALL.sql

exec_DB_script: sent
host sqlplus -v > /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11761_exec_DB_script.done to Writer
exec_DB_script: sent -exit- to Writer
exec_DB_script: closed Writer
exec_DB_script: marker was undefined; read and ignore output, if any
exec_DB_script: finished reading and ignoring output
exec_DB_script: waiting for child process to exit
exec_DB_script: child process exited
sureunlink: unlink(/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11761_exec_DB_script.done) succeeded after 1 attempt(s)
sureunlink: verify that the file really no longer exists
sureunlink: confirmed that /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11761_exec_DB_script.done no longer exists after 1 attempts
exec_DB_script: deleted /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_11761_exec_DB_script.done after running a script
exec_DB_script: closed Reader
exec_DB_script: waitpid returned
kill_sqlplus_sessions: output produced in exec_DB_script [

    SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 00:30:52 2017

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

    SQL> Connected.
    SQL> SQL> SQL>
    SQL> ALTER SYSTEM KILL SESSION '78,1729' force timeout 0 -- process 11802
      2  /

    System altered.

    SQL>
    SQL> SQL>
    SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  ] end of output produced in exec_DB_script
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catclust_catcon_11824.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catclust*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catclust_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catfinal_catcon_12430.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catfinal*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catfinal_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catbundleapply_catcon_12604.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catbundleapply*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catbundleapply_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/utlrp_catcon_12789.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/utlrp*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully

使用OMF来创建CDB
下面的例子将介绍如何使用OMF功能来创建CDB
1.设置SID

[root@jytest3 ~]# su - oracle
Last login: Fri Aug  4 15:07:33 CST 2017
[oracle@jytest3 ~]$ cd $ORACLE_HOME/dbs

[oracle@jytest3 dbs]$ export ORACLE_SID=cs

2.创建密码文件

[oracle@jytest3 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwcs password=xxzx_7817600 format=12.2 entries=20

3.创建初始化参数

[oracle@jytest3 dbs]$ vi inittest.ora
db_name='cs'
memory_target=4G
memory_max_target=4G
control_files='+data/cs/controlfile/control01.ctl','+data/cs/controlfile/control02.ctl'
enable_pluggable_database=true
db_create_file_dest=+data

4.启动实例但不mount

[oracle@jytest3 dbs]$ export ORACLE_SID=cs
[oracle@jytest3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 20:59:37 2017

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

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/initcs.ora' nomount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  8628936 bytes
Variable Size            2315257144 bytes
Database Buffers         1962934272 bytes
Redo Buffers                8146944 bytes

5.执行create database语句来创建CDB
下面的语句将创建一个名为cs的CDB数据库。这个名字与参数文件中的db_name同名。为了创建包含root与CDB seed的CDB库在create database语句中包含了enable pluggable database子句。在这个例子还包含了
seed tablespace datafiles子句来指定CDB seed文件的文件名与目录。

SQL> create database cs
  2  user sys identified by xxzx_7817600
  3  user system identified by xxzx_7817600
  4  extent management local
  5  default tablespace users
  6  default temporary tablespace temp
  7  undo tablespace undotbs1
  8  enable pluggable database
  9  seed
 10  system datafiles size 125m autoextend on next 10m maxsize unlimited
 11  sysaux datafiles size 100m;

Database created.

6.执行脚本$ORACLE_HOME/rdbms/admin/catcdb.sql

SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql
SQL>
SQL> Rem The script relies on the caller to have connected to the DB
SQL>
SQL> Rem This script invokes catcdb.pl that does all the work, so we just need to
SQL> Rem construct strings for $ORACLE_HOME/rdbms/admin and
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL>
SQL> Rem $ORACLE_HOME
SQL> column oracle_home new_value oracle_home noprint
SQL> select sys_context('userenv', 'oracle_home') as oracle_home from dual;




SQL>
SQL> Rem OS-dependent slash
SQL> column slash new_value slash noprint
SQL> select sys_context('userenv', 'platform_slash') as slash from dual;




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin
SQL> column rdbms_admin new_value rdbms_admin noprint
SQL> select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual;
old   1: select '&&oracle_home'||'&&slash'||'rdbms'||'&&slash'||'admin' as rdbms_admin from dual
new   1: select '/u01/app/oracle/product/12.2.0/db'||'/'||'rdbms'||'/'||'admin' as rdbms_admin from dual




SQL>
SQL> Rem $ORACLE_HOME/rdbms/admin/catcdb.pl
SQL> column rdbms_admin_catcdb new_value rdbms_admin_catcdb noprint
SQL> select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual;
old   1: select '&&rdbms_admin'||'&&slash'||'catcdb.pl' as rdbms_admin_catcdb from dual
new   1: select '/u01/app/oracle/product/12.2.0/db/rdbms/admin'||'/'||'catcdb.pl' as rdbms_admin_catcdb from dual




SQL>
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter value for 1: /u01/app/oracle/product/12.2.0/db/rdbms/log
Enter value for 2: cs.log
Enter new password for SYS: xxzx_7817600
Enter new password for SYSTEM: xxzx_7817600
Enter temporary tablespace name: temp
No options to container mapping specified, no options will be installed in any containers
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catalog_catcon_17898.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catalog*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catalog_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catproc_catcon_25615.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catproc*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catproc_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catoctk_catcon_32295.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catoctk*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catoctk_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/owminst_catcon_32474.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/owminst*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/owminst_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2305.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2530.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_*.lst] files for spool files, if any
validate_script_path: sqlplus script /u01/app/oracle/product/12.2.0/db/sqlplus/admin/help/hlpbld does not exist or is unreadable
catconExec: empty Path returned by validate_script_path for
    SrcDir = /u01/app/oracle/product/12.2.0/db/sqlplus/admin/help, FileName = hlpbld
catcon.pl: Unexpected error encountered in catconExec; exiting
exec_DB_script: /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2530_exec_DB_script.done did not need to be deleted before running a script
exec_DB_script: opened Reader and Writer
exec_DB_script: connected
exec_DB_script: executed set echo on

exec_DB_script: executed @@/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_kill_sess_2530_ALL.sql

exec_DB_script: sent
host sqlplus -v > /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2530_exec_DB_script.done to Writer
exec_DB_script: sent -exit- to Writer
exec_DB_script: closed Writer
exec_DB_script: marker was undefined; read and ignore output, if any
exec_DB_script: finished reading and ignoring output
exec_DB_script: waiting for child process to exit
exec_DB_script: child process exited
sureunlink: unlink(/u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2530_exec_DB_script.done) succeeded after 1 attempt(s)
sureunlink: verify that the file really no longer exists
sureunlink: confirmed that /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2530_exec_DB_script.done no longer exists after 1 attempts
exec_DB_script: deleted /u01/app/oracle/product/12.2.0/db/rdbms/log/pupbld_catcon_2530_exec_DB_script.done after running a script
exec_DB_script: closed Reader
exec_DB_script: waitpid returned
kill_sqlplus_sessions: output produced in exec_DB_script [

    SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 04:04:00 2017

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

    SQL> Connected.
    SQL> SQL> SQL>
    SQL> ALTER SYSTEM KILL SESSION '144,61245' force timeout 0 -- process 2602
      2  /

    System altered.

    SQL>
    SQL> SQL>
    SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  ] end of output produced in exec_DB_script
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catclust_catcon_2620.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catclust*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catclust_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catfinal_catcon_3402.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catfinal*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catfinal_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/catbundleapply_catcon_3568.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catbundleapply*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/catbundleapply_*.lst] files for spool files, if any
catcon.pl: completed successfully
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/rdbms/log/utlrp_catcon_3726.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/utlrp*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/rdbms/log/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully

Oracle分布式系统中的全局数据库名

Posted on 2017年8月3日2017年8月3日 by YongJing(敬勇)

在分布式系统中，每个数据库都有一个唯一的全局数据库名。全局数据库名能唯一标识系统中的一个数据库。在分布式系统中一个主要的任务就是创建与管理全局数据库名。

1.全局数据库名的组成
一个全局数据库名由两部分组成:一个数据库名与一个域名。在数据库创建时数据库名与域名是由以下参数来决定的。
数据库名:db_name 小于等于30个字符，例如sales
域名: db_domain 必须遵守标准互联网规则。域名中的级别必须通过点号进行分隔并且域名的顺序是从叶子到根，从左到右。

db_domain参数只是在数据库创建时被使用的一个很重要的参数，它与db_name参数一起来决定全局数据库名。全局数据库名是被存储在数据字典中。必须通过alter database语句来改变全局数据库名，不能通过修改参数文件中的db_domain参数来进行修改。

决定是否强制执行全局数据库名
在本地数据库中创建数据链路指定名称时会根据是否强制执行全局数据库名来生成链路名。如果本地数据库强制执行全局数据库名，那么你必须像使用远程数据库全局数据库名作为数据链路名。例如，如果连接到本地数据库hq，并且想创建一个连接到远程数据库的mfg的数据链路，并且本地数据库强制执行全局数据库名，那么必须使用mfg全局数据库名作为链路名。

在创建数据链路时也可以使用服务名作为数据库链路名的一部分。例如，如果使用服务名sn1和sn2来连接数据库hq.example.com，并且全局数据库名被强制执行时，那么可以对数据库hq创建以下链路名:
hq.example.com@sn1
hq.example.com@sn2

为了判断数据库是否启用了全局数据库名，可以检查数据库初始化参数文件或查询v$parameter视图。例如，为了查看对数据库mfg是否强制执行全局数据库名，可以执行以下语句来进行查询：

SQL> col name format a12
SQL> col value format a6
SQL> select name, value from v$parameter  where name = 'global_names'
  2  /

NAME         VALUE
------------ ------
global_names FALSE

查看全局数据库名
可以查询视图global_name来查看全局数据库名

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
JYPDB

修改全局数据库名中的域名
可以使用alter database语句来改变全局数据库名中的域名。在数据库创建之后，修改db_domain参数不会影响全局数据库名或数据库链路名。下面的例子显示重命名全局数据库名的语句，database是数据库名，domain是网络域名:

alter database rename global_name to database.domain;

修改全局数据库名的操作如下:
1.判断当前的全局数据库名:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
JY

2.执行alter database语句来修改全局数据库名

SQL> alter database rename global_name to jy.jydba.net;

Database altered.

3.查询视图global_name来检查新的全局数据库

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
JY.JYDBA.NET

4.修改全局数据库名中的域名
使用alter database语句来修改全避数据库名中的域名。在数据库创建之后，修改初始化参数db_domain不会影响全局数据库名或对数据库链路名的解析。下面的例子显示了修改全局数据库名的语法，database是数据库名，domain是网络域名：

alter database rename global_name to database.domain;

使用下面的过程来修改全局数据库名中的域名:
1.判断当前全局数据库名。

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
JY.JYDBA.NET

2.使用alter database语句来修改全局数据库名

SQL> alter database rename global_name to jy.changde.net;
Database altered

3.查询global_name表来检查新的全局数据库名

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
JY.CHANGDE.NET

下面将介绍修改全局数据库名的场景
在这种情况下，可以修改本地数据库的全局数据库名中的域名部分。也可以使用全局名来创建数据链路来测试数据库是如何解析链路名的。

1.连接到jy.jydba.net并查询global_name数据字典视图来判断当前的数据为全局名:

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
JY.JYDBA.NET

2.查询v$parameter视图来判断db_domain参数的当前值

SQL> select name, value from v$parameter where name = 'db_domain';
NAME                            VALUE
------------------------------- ----------------
db_domain

3.创建一个连接到数据库sjjh的数据链路,保指定全局名的一部分：

SQL> create database link dblink_test using 'sjjh';
Database link created

数据库会通过将本地数据库的全局数据库名中的域名部分增加到数据链路的全局名中

4.查询user_db_links来判断数据链路的域名

SQL> select db_link from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
DBLINK_TEST.JYDBA.NET

查询结果显示，本地数据库的全局名中的域名jydba.net被用来作为数据链路的域名

5.因为要将数据库jy移到changde.net这个域中，所以执行以下操作

SQL> alter database rename global_name to jy.changde.net;
Database altered

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
JY.CHANGDE.NET

6.查询v$parameter发现db_domain参数并没有修改

SQL> select name, value from v$parameter where name = 'db_domain';
NAME                            VALUE
------------------------------- ----------------
db_domain

这也就说明了db_domain参数是独立于alter database rename global_name语句的。alter database语句决定了全局数据库名中的域名。

7.可以创建另一个数据链路来连接数据库sjjh，并查询user_db_links来查看数据链路中的域名

SQL> create database link dblink_cs using 'sjjh';
Database link created

SQL> select db_link from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
DBLINK_CS.CHANGDE.NET
DBLINK_TEST.JYDBA.NET

可以看到新创建的数据链路的域名，是使用当前本地数据库全局数据库名中的域名。

Oracle 12.2 创建分离Jobs

Posted on 2017年7月18日2017年7月29日 by YongJing(敬勇)

分离job必须指向一个程序对象，程序对象的分离属性设置为true。下面的盒子是在Linux和系统上创建一个夜间job来对数据库执行备份。

1.创建一个脚本来调用RMAN备份脚本，脚本名为$ORACLE_HOME/scripts/backup.sh

[oracle@sjjh scripts]$ vi backup.sh
export ORACLE_HOME=/home/app/oracle/product/11.2.0
export ORACLE_SID=sjjh
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/backup.rman
trace /home/app/oracle/backup/backup.out &
exit 0

2.创建rman备份脚本,脚本名为$ORACLE_HOME/scripts/backup.rman

[oracle@sjjh scripts]$ vi backup.rman
run{
# Perform full database backup
backup full format "/home/app/oracle/backup/%d_FULL_%U" (database) ;
# Open database after backup
alter database open;
# Call notification routine to indicate job completed successfully
sql " BEGIN DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.backup_job'', 0,
null); END; ";
}

3.创建job并使用分离程序对象

[oracle@sjjh scripts]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 11:13:44 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> BEGIN
  2   DBMS_SCHEDULER.CREATE_PROGRAM(
  3    program_name => 'sys.backup_program',
  4    program_type => 'executable',
  5    program_action => '/home/app/oracle/product/11.2.0/scripts/coldbackup.sh',
  6    enabled => TRUE);
  7
  8   DBMS_SCHEDULER.SET_ATTRIBUTE('sys.backup_program', 'detached', TRUE);
  9   DBMS_SCHEDULER.CREATE_JOB(
 10    job_name => 'sys.backup_job',
 11    program_name => 'sys.backup_program',
 12    repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0');
 13
 14   DBMS_SCHEDULER.ENABLE('sys.backup_job');
 15  END;
 16  /

PL/SQL procedure successfully completed.