Oracle 12c CDB PDB 安装/配置/管理

Oracle安装参考：https://www.cnblogs.com/zhichaoma/p/9288739.html

对于CDB，启动和关闭与之前传统的方式一样，具体语法如下：

    STARTUP[NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]

    SHUTDOWN[IMMEDIATE | ABORT]

手动创建CDB

1. 修改~/.bash_profile文件，修改ORACLE_SID名称

export ORACLE_SID=orcl

 2. 创建所需要目录

$ mkdir -p /data/app/oracle/oradata/orcl
$ mkdir -p /data/app/oracle/oradata/pdbseed
$ mkdir -p /data/app/oracle/admin/orcl/adump
$ mkdir -p /data/app/oracle/fast_recovery_area

 3.创建初始化配置文件

$ vim $ORACLE_HOME/dbs/initcdb.ora

db_name='orcl'
memory_target=1G
processes = 150
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_file_dest='/data/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/data/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/data/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=orcl)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/data/app/oracle/oradata/orcl/ora_control01.ctl, /data/app/oracle/oradata/orcl/ora_control02.ctl)
compatible ='12.2.0'
enable_pluggable_database=true

4.开始创建库

$ sqlplus / as sysdba
SQL> shutdown abort
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initcdb.ora';
SQL> startup nomount

SQL> CREATE DATABASE orcl
USER SYS IDENTIFIED BY pass
USER SYSTEM IDENTIFIED BY pass
LOGFILE GROUP 1 ('/data/app/oracle/oradata/orcl/redo01a.log','/data/app/oracle/oradata/orcl/redo01b.log') 
   SIZE 100M BLOCKSIZE 512,
  GROUP 2 ('/data/app/oracle/oradata/orcl/redo02a.log','/data/app/oracle/oradata/orcl/redo02b.log') 
   SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/data/app/oracle/oradata/orcl/system01.dbf' SIZE 700M
SYSAUX DATAFILE '/data/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 550M
DEFAULT TABLESPACE deftbs
 DATAFILE '/data/app/oracle/oradata/orcl/deftbs01.dbf' SIZE 500M
DEFAULT TEMPORARY TABLESPACE tempts1
 TEMPFILE '/data/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M 
UNDO TABLESPACE undotbs1
 DATAFILE '/data/app/oracle/oradata/orcl/undotbs01.dbf' SIZE 200M 
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/data/app/oracle/oradata/orcl/', 
'/data/app/oracle/oradata/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE '/data/app/oracle/oradata/pdbseed/usertbs01.dbf' SIZE 200M;

填充数据字典，如果是12cR2版本，则直接执行catcdb.sql代替以下脚本

使用sysdba权限执行以下脚本

SQL> @?/rdbms/admin/catalog.sql --数据库字典，动态视图创建等
SQL> @?/rdbms/admin/catproc.sql --PL/SQL存过过程相关代码
SQL> @?/rdbms/admin/utlrp.sql  --编译

 使用SYSTEM用户执行以下脚本

SQL> @?/sqlplus/admin/pupbld.sql #SQL*Plus相关

12CR2

注：这个过程可能比较漫长

SQL> @?/rdbms/admin/catcdb.sql

会提示输入以下信息

Enter value for 1: /data/app/oracle/product/12.2.0/db_1/rdbms/admin
Enter value for 2: /data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl

如果出现以下报错

1）错误-1

Can't locate util.pm in @INC (@INC contains: /data/app/oracle/product/12.2.0/db_1/rdbms/admin /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at 
/data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 35.
BEGIN failed--compilation aborted at /data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 35.

 解决方法：

$ find $ORACLE_HOME -name util.pm | wc -l
0
$ find $ORACLE_HOME -name Util.pm | wc -l
5
$ find $ORACLE_HOME -name Util.pm
/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash/Util.pm
/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/List/Util.pm
/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Scalar/Util.pm
/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Sub/Util.pm
/data/app/oracle/product/12.2.0/db_1/perl/lib/site_perl/5.22.0/HTTP/Headers/Util.pm

手工修改catcdb.pl脚本

use Util qw(trim, splitToArray);     //util改为Util

再来一轮测试，结果发现还是会有报错，这种尝试会让你开始怀疑自己的选择到底是不是正确的方向。

如果还是没有找到，说明在当前的环境变量中没有匹配到相关的内容，我们需要直接切换到目录Hash下，然后运行脚本才可以，这个时候又出现一个错误

2）错误-2

Can'tlocate Term/ReadKey.pm in @INC (@INC contains: /data/app/oracle/product/12.2.0/db_1/rdbms/admin 
/usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl 
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at 
/data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 30.
BEGINfailed--compilation aborted at 
/data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 30.

 声明环境变量

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$ORACLE_HOME/jdk/bin:$PATH

验证结果

SQL> SELECT name,
   DECODE (cdb,
     'YES', 'Multitenant Option enabled',
     'Regular 12c Database: ')
   "Multitenant Option",
   open_mode,
   con_id
 FROM v$database;

创建PDB

1.查看当前容器

$ sqlplus / as sysdba
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

 2. 创建一个新的PDB

$ mkdir -p /data/app/oracle/oradata/pdb1

SQL> create pluggable database pdb1 admin 
user pdb1 identified by pdb1 
file_name_convert=('/data/app/oracle/oradata/pdbseed/',
'/data/app/oracle/oradata/pdb1/');

3. 查看所有PDB

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  MOUNTED

4. 删除一个pdb

SQL> alter pluggable database pdb2 close;
SQL> DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

5. 启动和关闭一个创建好的PDB

1）启动pdb和关闭pdb

    a) 启动pdb

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO

    b)关闭pdb

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  MOUNTED

 2）通过sqlplus使用传统的startup和shutdown命令来启动和关闭PDB

    a) 启动pdb

SQL> alter session set container=pdb1;

Session altered.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 PDB1 			  MOUNTED
SQL> startup
Pluggable Database opened.
SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 PDB1 			  READ WRITE NO

    b) 关闭pdb

SQL> shutdown immediate;
Pluggable Database closed.
SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 PDB1 			  MOUNTED

6. 配置监听

$ vim $ORACLE_HOME/network/admin/tnsnames.ora    //如果此文件不存在，手动创建

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))

pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

7. 测试与登陆

$ tnsping pdb1				//测试登陆
$ sqlplus pdb1/pdb1@pdb1	        //登陆

 数据泵导入导出PDB数据

以pdb1数据库为例

1. 启动pdb1数据库

$ sqlplus / as sysdba
SQL> alter session set container=pdb1;
SQL> startup

 2. 创建导入数据目录和数据库映射关系

$ mkdir -p /home/oracle/dump_dir
SQL> alter session set container=pdb1;
SQL> create directory dump_dir as '/home/oracle/dump_dir';

 3. 创建一个dba权限的数据泵导入导出用户

SQL> grant dba to dp identified by dp;
SQL> grant read,write on directory dump_dir to dp;

4. 创建表空间

SQL> CREATE TABLESPACE "PDB1" DATAFILE
'/data/app/oracle/oradata/pdb1/pdb101.dbf' SIZE 128M AUTOEXTEND
ON NEXT 128M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

5. 导出数据

$ expdp dp/dp@pdb1 directory="dump_dir" dumpfile="aa.dmp" logfile=aa.log

6. 导入数据

$ impdp dp/dp@pdb1 directory="dump_dir" dumpfile="aa.dmp" logfile=bb.log