Oracle 12c 手动创建CDB

使用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分布式系统中的全局数据库名

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

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

分离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.


Oracle 12.2 使用联机重定义对表进行多处改变

下面的例子将演示如何使用联机重定义操作来对表进行多处改变,原始表jy.original的创建语句如下:

SQL> create table jy.original(
  2  col1 number primary key,
  3  col2 varchar2(10),
  4  col3 clob,
  5  col4 date)
  6  organization index;

Table created.

表jy.original将按以下规则进行重定义:
.表启用高级行压缩方法进行压缩
.LOB列将被改变为SecureFiles LOB存储
.表的存储表空间将由test改变为example,并且表的块大小由8KB改变为 16KB。
.表将基于col1列进行分区
.将增加列col5
.列col2将被删除
.列col3与col4会被重命名,并且它们的位置会发生改变
.列col3的数据类型将从date改变为timestamp
.表将由索引组织表改变为堆表
.表的碎片将会被整理

为了演示碎片整理,使用下面的语句来向表加载数据:

SQL> declare
  2  v_clob clob;
  3  begin
  4   for i in 0..999 loop
  5    v_clob := null;
  6    for j in 1..1000 loop
  7     v_clob := v_clob||to_char(i,'0000');
  8    end loop;
  9    insert into jy.original values(i,to_char(i),v_clob,sysdate+i);
 10    commit;
 11   end loop;
 12   commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

执行下面的语句来使用表被碎片化

SQL> delete from jy.original where (col1/3) <> trunc(col1/3);

666 rows deleted.

SQL> commit;

Commit complete.
SQL> set serveroutput on;
SQL> declare
  2      l_fs1_bytes number;
  3      l_fs2_bytes number;
  4      l_fs3_bytes number;
  5      l_fs4_bytes number;
  6      l_fs1_blocks number;
  7      l_fs2_blocks number;
  8      l_fs3_blocks number;
  9      l_fs4_blocks number;
 10      l_full_bytes number;
 11      l_full_blocks number;
 12      l_unformatted_bytes number;
 13      l_unformatted_blocks number;
 14  begin
 15      dbms_space.space_usage(
 16           segment_owner      => 'JY',
 17           segment_name       => 'ORIGINAL',
 18           segment_type       => 'TABLE',
 19           fs1_bytes          => l_fs1_bytes,
 20           fs1_blocks         => l_fs1_blocks,
 21           fs2_bytes          => l_fs2_bytes,
 22           fs2_blocks         => l_fs2_blocks,
 23           fs3_bytes          => l_fs3_bytes,
 24           fs3_blocks         => l_fs3_blocks,
 25           fs4_bytes          => l_fs4_bytes,
 26           fs4_blocks         => l_fs4_blocks,
 27           full_bytes         => l_full_bytes,
 28           full_blocks        => l_full_blocks,
 29           unformatted_blocks => l_unformatted_blocks,
 30           unformatted_bytes  => l_unformatted_bytes
 31            );
 32
 33        dbms_output.put_line('0-25% free = '||l_fs1_blocks||' and bytes = '||l_fs1_bytes);
 34        dbms_output.put_line('25-50% free = '||l_fs2_blocks||' and bytes = '||l_fs2_bytes);
 35        dbms_output.put_line('50-75% free = '||l_fs3_blocks||' and bytes = '||l_fs3_bytes);
 36        dbms_output.put_line('75-100% free = '||l_fs4_blocks||' and bytes = '||l_fs4_bytes);
 37        dbms_output.put_line(' full blocks = '||l_full_blocks||' and bytes = '||l_full_bytes);
 38  end;
 39  /
0-25% free = 0 and bytes = 0
25-50% free = 3 and bytes = 24576
50-75% free = 0 and bytes = 0
75-100% free = 0 and bytes = 0
full blocks = 10 and bytes = 81920

PL/SQL procedure successfully completed.

1.用要执行联机重定义操作的用户登录数据库

SQL> conn jy/jy@jypdb
Connected.

2.验证原始表是否可以执行联机重定义

SQL> begin
  2  dbms_redefinition.can_redef_table(
  3  uname => 'jy',
  4  tname => 'original',
  5  options_flag => dbms_redefinition.cons_use_pk);
  6  end;
  7  /

PL/SQL procedure successfully completed.

3.创建中间表jy.int_original

SQL> create table jy.int_original(
  2  col1 number,
  3  col3 timestamp,
  4  col4 clob,
  5  col5 varchar2(3))
  6  lob(col4) store as securefile (nocache filesystem_like_logging)
  7  partition by range (col1) (
  8  partition par1 values less than (333),
  9  partition par2 values less than (666),
 10  partition par3 values less than (maxvalue))
 11  tablespace example
 12  row store compress advanced;

Table created.

4.开始联机重定义操作

SQL> begin
  2  dbms_redefinition.start_redef_table(
  3  uname => 'jy',
  4  orig_table => 'original',
  5  int_table => 'int_original',
  6  col_mapping => 'col1 col1, to_timestamp(col4) col3, col3 col4',
  7  options_flag => dbms_redefinition.cons_use_pk);
  8  end;
  9  /

PL/SQL procedure successfully completed.

5.复制依赖对象

SQL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents(
  5  uname => 'jy',
  6  orig_table => 'original',
  7  int_table => 'int_original',
  8  copy_indexes => dbms_redefinition.cons_orig_params,
  9  copy_triggers => true,
 10  copy_constraints => true,
 11  copy_privileges => true,
 12  ignore_errors => true,
 13  num_errors => num_errors);
 14  end;
 15  /

PL/SQL procedure successfully completed.

6.可选操作同步中间表

SQL> begin
  2  dbms_redefinition.sync_interim_table(
  3  uname => 'jy',
  4  orig_table => 'original',
  5  int_table => 'int_original');
  6  end;
  7  /

PL/SQL procedure successfully completed.

7.完成联机重定义操作

SQL> begin
  2  dbms_redefinition.finish_redef_table(
  3  uname => 'jy',
  4  orig_table => 'original',
  5  int_table => 'int_original');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Oracle 12.2 联机重定义使用VPD策略的表并修改表的列名

下面的例子将演示联机重定义使用VPD策略的表,并修改表中的一个列名,原始表jy.orders的创建语句如下:

SQL> create table jy.orders(
  2  order_id number(12) primary key,
  3  order_date timestamp with local time zone constraint order_date_nn not null,
  4  order_mode varchar2(8),
  5  customer_id number(6) constraint order_customer_id_nn not null,
  6  order_status number(2),
  7  order_total number(8,2),
  8  sales_rep_id number(6),
  9  promotion_id number(6),
 10  constraint order_mode_lov
 11  check (order_mode in ('direct','online')),
 12  constraint order_total_min
 13  check (order_total >= 0));

Table created.

创建下面的jy.auth_orders函数来创建VPD策略

SQL> create or replace function jy.auth_orders(
  2  schema_var in varchar2,
  3  table_var in varchar2
  4  )
  5  return varchar2
  6  as
  7  return_val varchar2 (400);
  8  unm varchar2(30);
  9  begin
 10  select user into unm from dual;
 11  if (unm = 'jy') then
 12  return_val := null;
 13  else
 14  return_val := 'sales_rep_id = 159';
 15  end if;
 16  return return_val;
 17  end auth_orders;
 18  /

Function created.

下面执行dbms_rls.add_policy过程来使用jy.auth_orders函数来给原始表jy.orders指定VPD策略:

SQL> begin
  2  dbms_rls.add_policy(
  3  object_schema => 'jy',
  4  object_name => 'orders',
  5  policy_name => 'orders_policy',
  6  function_schema => 'jy',
  7  policy_function => 'auth_orders');
  8  end;
  9  /

PL/SQL procedure successfully completed.

在这个例子中,被重定义后表中的sales_rep_id列被修改为sale_pid。当在执行重定义过程如可修改一个或多个列或列的数据类型,那么在start_refef_table过程中对于copy_vpd_opt参数必须指定dbms_redefinition.cons_vpd_manual。

1.用要执行联机重定义操作的用户登录数据库

SQL> conn jy/jy@jypdb
Connected.

2.验证原始表是否可以执行联机重定义

SQL> begin
  2  dbms_redefinition.can_redef_table(
  3  uname => 'jy',
  4  tname => 'orders',
  5  options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  6  end;
  7  /

PL/SQL procedure successfully completed.

3.创建中间表

SQL> create table jy.int_orders(
  2  order_id number(12),
  3  order_date timestamp with local time zone,
  4  order_mode varchar2(8),
  5  customer_id number(6),
  6  order_status number(2),
  7  order_total number(8,2),
  8  sales_pid number(6),
  9  promotion_id number(6));

Table created.

注意,在中间表中sales_rep_id列被修改为sales_pid。

4.开始联机重定义操作

SQL> begin
  2  dbms_redefinition.start_redef_table (
  3  uname => 'jy',
  4  orig_table => 'orders',
  5  int_table => 'int_orders',
  6  col_mapping => 'order_id order_id, order_date order_date, order_mode
  7  order_mode, customer_id customer_id, order_status
  8  order_status, order_total order_total, sales_rep_id
  9  sales_pid, promotion_id promotion_id',
 10  options_flag => dbms_redefinition.cons_use_pk,
 11  orderby_cols => null,
 12  part_name => null,
 13  copy_vpd_opt => dbms_redefinition.cons_vpd_manual);
 14  end;
 15  /

PL/SQL procedure successfully completed.

因为原始表与中间表存在不同的列名,那么copy_vpd_opt参数必须设置为dbms_redefinition.cons_vpd_manual。

5.对中间表创建VPD策略
5.1创建一个名为jy.auth_orders_sales_pid的函数来创建VPD策略,这里使用sales_pid列来代替sales_rep_id列。

SQL> create or replace function jy.auth_orders_sales_pid(
  2  schema_var in varchar2,
  3  table_var in varchar2
  4  )
  5  return varchar2
  6  as
  7  return_val varchar2 (400);
  8  unm varchar2(30);
  9  begin
 10  select user into unm from dual;
 11  if (unm = 'jy') then
 12  return_val := null;
 13  else
 14  return_val := 'sales_pid = 159';
 15  end if;
 16  return return_val;
 17  end auth_orders_sales_pid;
 18  /

Function created.

5.2执行dbms_rls.add_policy过程来使用jy.auth_orders_sales_pid函数来为中间表增加VPD策略

SQL> begin
  2  dbms_rls.add_policy (
  3  object_schema => 'jy',
  4  object_name => 'int_orders',
  5  policy_name => 'orders_policy',
  6  function_schema => 'jy',
  7  policy_function => 'auth_orders_sales_pid');
  8  end;
  9  /

PL/SQL procedure successfully completed.

6.复制依赖对象

SQL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents(
  5  uname => 'jy',
  6  orig_table => 'orders',
  7  int_table => 'int_orders',
  8  copy_indexes => dbms_redefinition.cons_orig_params,
  9  copy_triggers => true,
 10  copy_constraints => true,
 11  copy_privileges => true,
 12  ignore_errors => true,
 13  num_errors => num_errors);
 14  end;
 15  /

PL/SQL procedure successfully completed.

注意在这里ignore_errors参数被设置为true。原因是因为原始表对于列sales_rep_id存在索引与约束,并且在中间表中列被修改为sales_pid。

7.查询dba_redefinition_errors视图来检查是否存在错误

SQL> set long 8000
SQL> set pages 8000
SQL> column object_name heading 'object name' format a20
SQL> column base_table_name heading 'base table name' format a10
SQL> column ddl_txt heading 'ddl that caused error' format a40
SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;

no rows selected

8.可选操作同中间表

SQL> begin
  2  dbms_redefinition.sync_interim_table(
  3  uname => 'jy',
  4  orig_table => 'orders',
  5  int_table => 'int_orders');
  6  end;
  7  /

PL/SQL procedure successfully completed.

9.完成联机重定义操作

SQL> begin
  2  dbms_redefinition.finish_redef_table(
  3  uname => 'jy',
  4  orig_table => 'orders',
  5  int_table => 'int_orders');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> desc jy.orders
Name         Type                              Nullable Default Comments
------------ --------------------------------- -------- ------- --------
ORDER_ID     NUMBER(12)
ORDER_DATE   TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE   VARCHAR2(8)                       Y
CUSTOMER_ID  NUMBER(6)
ORDER_STATUS NUMBER(2)                         Y
ORDER_TOTAL  NUMBER(8,2)                       Y
SALES_PID    NUMBER(6)                         Y
PROMOTION_ID NUMBER(6)                         Y

10.等待任何查询中间表的语句执行完成后将其删除

SQL> drop table jy.int_orders;
Table dropped

到此重定义操作就完成了。