[原创]Oracle12cR2_手工创建CDB
Oracle12cR2创建CDB有很多方法：
常用有 DBCA创建CDB 和 手工创建CDB 两种方法，以下是手工创建CDB的步骤。
环境
OS环境：
[oracle@databasevm ~]$ uname -a
Linux databasevm.localdomain 4.1.12-61.1.28.el7uek.x86_64 #2 SMP Thu Feb 23 19:55:12 PST 2017 x86_64 x86_64 x86_64 GNU/Linux
[oracle@databasevm ~]$
[oracle@databasevm ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[oracle@databasevm ~]$ grep MemTotal /proc/meminfo
MemTotal: 4045056 kB
[oracle@databasevm ~]$ grep SwapTotal /proc/meminfo
SwapTotal: 0 kB
[oracle@databasevm ~]$ df -hT
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 2.0G 0 2.0G 0% /dev
tmpfs tmpfs 2.0G 96K 2.0G 1% /dev/shm
tmpfs tmpfs 2.0G 8.9M 2.0G 1% /run
tmpfs tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/sda1 xfs 50G 12G 39G 23% /
/dev/sdb xfs 50G 11G 40G 22% /opt/oracle
tmpfs tmpfs 396M 20K 396M 1% /run/user/1000
tmpfs tmpfs 396M 0 396M 0% /run/user/0
[oracle@databasevm ~]$
数据库环境：
安装Linux/DB12cR2软件，请参考其他文档
手工创建CDB
参考文档：
Home / Database / Oracle Database 12c Release 2 / Administration / 1.7.2 Creating a Database Password File with ORAPWD
Home / Database / Oracle Database 12c Release 2 / Administration / 2 Creating and Configuring an Oracle Database
Home / Database / Oracle Database 12c Release 2 / Administration / 37.4 Using the CREATE DATABASE Statement to Create a CDB
step 0. 环境变量设置
通过设置.bashrc或者.bash_profile追加环境变量 ORACLE_BASE ORACLE_HOME ORACLE_SID PATH等
[oracle@databasevm ~]$ cat .bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
# export ORACLE_UNQNAME=ORCLCDB
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/12.2.0.1/db_1
# export ORACLE_SID=ORCLCDB
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch/:/usr/sbin:/usr/local/bin:/usr/bin
export LD_LIBRARY_PATH=/opt/oracle/product/12.2.0.1/db_1/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:/opt/oracle/product/12.2.0.1/db_1/rdbms/jlib
#export SQL_OR_SQLPLUS='sql -oci'
export SQL_OR_SQLPLUS=sqlplus
export NVM_DIR="$HOME/.nvm"
[ -s "$NVM_DIR/nvm.sh" ] && \. "$NVM_DIR/nvm.sh" # This loads nvm
alias sqlplus='rlwrap sqlplus'
[oracle@databasevm ~]$
step 1. 密码文件
创建密码文件12cR2比较严格了，需要注意一下。
[oracle@databasevm ~]$ orapwd
Usage: orapwd file=<fname> force=<y/n> asm=<y/n>
dbuniquename=<dbname> format=<12/12.2>
delete=<y/n> input_file=<input-fname>
sys=<y/password/external(<sys-external-name>)>
sysbackup=<y/password/external(<sysbackup-external-name>)>
sysdg=<y/password/external(<sysdg-external-name>)>
syskm=<y/password/external(<syskm-external-name>)>
Usage: orapwd describe file=<fname>
where
file - name of password file (required),
password - password for SYS will be prompted
if not specified at command line.
Ignored, if input_file is specified,
force - whether to overwrite existing file (optional),
asm - indicates that the password to be stored in
Automatic Storage Management (ASM) disk group
is an ASM password. (optional),
dbuniquename - unique database name used to identify database
password files residing in ASM diskgroup only.
Ignored when asm option is specified (optional),
format - use format=12 for new 12c features like SYSBACKUP, SYSDG and
SYSKM support, longer identifiers, SHA2 Verifiers etc.
use format=12.2 for 12.2 features like enforcing user
profile (password limits and password complexity) and
account status for administrative users.
If not specified, format=12.2 is default (optional),
delete - drops a password file. Must specify 'asm',
'dbuniquename' or 'file'. If 'file' is specified,
the file must be located on an ASM diskgroup (optional),
input_file - name of input password file, from where old user
entries will be migrated (optional),
sys - specifies if SYS user is password or externally authenticated.
For external SYS, also specifies external name.
SYS={y/password} specifies if SYS user password needs
to be changed when used with input_file,
sysbackup - creates SYSBACKUP entry (optional).
Specifies if SYSBACKUP user is password or externally
authenticated. For external SYSBACKUP, also specifies
external name. Ignored, if input_file is specified,
sysdg - creates SYSDG entry (optional).
Specifies if SYSDG user is password or externally authenticated.
For external SYSDG, also specifies external name.
Ignored, if input_file is specified,
syskm - creates SYSKM entry (optional).
Specifies if SYSKM user is password or externally authenticated.
For external SYSKM, also specifies external name.
Ignored, if input_file is specified,
describe - describes the properties of specified password file
(required).
There must be no spaces around the equal-to (=) character.
[oracle@databasevm ~]$ orapwd file='$ORACLE_HOME/dbs/orapwPRODCDB' force=y format=12.2
Enter password for SYS:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.
[oracle@databasevm ~]$
OPW-00029错误：
oracle12cR2密码要求都比较严格：
Password must contain at least 8 characters
Password must not contain double quotes
Password must contain at least 1 letter
Password must contain at least 1 digit
Password must contain at least 1 special character
Password must not contain the username
Password must not contain username reversed
两种解决办法解决OPW-00029错误
-
方法1：用12的格式生成密码文件
[oracle@databasevm ~]$ orapwd file='$ORACLE_HOME/dbs/orapwPRODCDB' password=oracle force=y format=12.2 OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters. [oracle@databasevm ~]$ orapwd file='$ORACLE_HOME/dbs/orapwPRODCDB' password=oracle force=y format=12 [oracle@databasevm ~]$ -
方法2：12的格式生成密码文件之后再升级到12.2
[oracle@databasevm ~]$ orapwd file='$ORACLE_HOME/dbs/orapwPRODCDB.001' password=oracle force=y format=12 [oracle@databasevm ~]$ orapwd file='$ORACLE_HOME/dbs/orapwPRODCDB' input_file='$ORACLE_HOME/dbs/orapwPRODCDB.001' [oracle@databasevm ~]$
step 2. 参数文件spfile
pfile 要转成 spfile
[oracle@databasevm ~]$ cd $ORACLE_HOME/dbs
[oracle@databasevm dbs]$ ls
hc_ORCLCDB.dat init.ora initORCLCDB.ora lkORCLCDB orapwORCLCDB spfileORCLCDB.ora
[oracle@databasevm dbs]$ cat init.ora | grep -v '^#' | grep -v '^$'
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
[oracle@databasevm dbs]$
[oracle@databasevm dbs]$ cat init.ora | grep -v '^$' | grep -v '^#' > initPRODCDB.ora
[oracle@databasevm dbs]$ vi initPRODCDB.ora
[oracle@databasevm dbs]$
[oracle@databasevm dbs]$ cat initPRODCDB.ora
db_name='PRODCDB'
memory_target=800m
processes = 150
audit_file_dest='/opt/oracle/admin/prodcdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/opt/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest=/opt/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/opt/oracle/oradata/PRODCDB/ora_control01.ctl', '/opt/oracle/oradata/PRODCDB/ora_control02.ctl')
compatible ='12.2.0'
[oracle@databasevm dbs]$
另外需要追加：enable_pluggable_database=true 不然在create database报错： ORA-65093: multitenant container database not set up properly
[oracle@databasevm dbs]$ mkdir /opt/oracle/admin/prodcdb/adump -p
[oracle@databasevm dbs]$ mkdir /opt/oracle/oradata/PRODCDB/
[oracle@databasevm dbs]$ mkdir /opt/oracle/fast_recovery_area
[oracle@databasevm dbs]$ export ORACLE_SID=PRODCDB;sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 20 13:05:02 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 557846464 bytes
Database Buffers 264241152 bytes
Redo Buffers 8146944 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/12.2.0.1/d
b_1/dbs/spfilePRODCDB.ora
SQL>
step 3. Create database语句
构造create database手工创建语句
[oracle@databasevm dbs]$ vi createdb.sql
[oracle@databasevm dbs]$ cat createdb.sql
CREATE DATABASE PRODCDB
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/opt/oracle/oradata/PRODCDB/redo01a.log','/opt/oracle/oradata/PRODCDB/redo01b.log')
SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/opt/oracle/oradata/PRODCDB/redo02a.log','/opt/oracle/oradata/PRODCDB/redo02b.log')
SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/opt/oracle/oradata/PRODCDB/redo03a.log','/opt/oracle/oradata/PRODCDB/redo03b.log')
SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/opt/oracle/oradata/PRODCDB/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/opt/oracle/oradata/PRODCDB/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
DATAFILE '/opt/oracle/oradata/PRODCDB/deftbs01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/opt/oracle/oradata/PRODCDB/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/opt/oracle/oradata/PRODCDB/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/opt/oracle/oradata/PRODCDB/',
'/opt/oracle/oradata/pdbseed/')
LOCAL UNDO ON;
[oracle@databasevm dbs]$
[oracle@databasevm dbs]$ ls -ld /opt/oracle/oradata/PRODCDB/
drwxrwxr-x. 2 oracle oracle 6 Sep 20 13:04 /opt/oracle/oradata/PRODCDB/
[oracle@databasevm dbs]$ mkdir /opt/oracle/oradata/pdbseed/
[oracle@databasevm dbs]$
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 566235032 bytes
Database Buffers 255852544 bytes
Redo Buffers 7974912 bytes
SQL>
SQL> show parameter enable_pluggable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database boolean TRUE
SQL>
SQL> @createdb
Database created.
SQL>
step 4. catcdb.sql SQL script的执行
报错：
Can't locate util.pm in @INC (you may need to install the util module) (@INC contains: /opt/oracle/product/12.2.0.1/db_1/rdbms/admin /opt/oracle/product/12.2.0.1/db_1/rdbms/admin /opt/oracle/product/12.2.0.1/db_1/rdbms/admin /opt/oracle/product/12.2.0.1/db_1/perl/lib/site_perl/5.22.0/x86_64-linux-thread-multi /opt/oracle/product/12.2.0.1/db_1/perl/lib/site_perl/5.22.0 /opt/oracle/product/12.2.0.1/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi /opt/oracle/product/12.2.0.1/db_1/perl/lib/5.22.0 .) at /opt/oracle/product/12.2.0.1/db_1/rdbms/admin/catcdb.pl line 35.
BEGIN failed--compilation aborted at /opt/oracle/product/12.2.0.1/db_1/rdbms/admin/catcdb.pl line 35.
参考：
Bug 17033183 – $OH/rdbms/admin/catcdb.sql is missing from 12c release (Doc ID 17033183.8)
catcdb.sql and the util.pm issue in Oracle Database 12.2
更改名字的方法测试了下，好像不行
Can’t locate util.pm 的解决过程如下：
[oracle@databasevm ~]$ export PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB
[oracle@databasevm ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
[oracle@databasevm ~]$ cd $ORACLE_HOME/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash
[oracle@databasevm Hash]$
[oracle@databasevm Hash]$ pwd
/opt/oracle/product/12.2.0.1/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash
[oracle@databasevm Hash]$ ls -l
total 24
drwxr-xr-x. 2 oracle oracle 25 Apr 2 14:13 Util
-rwxr-x---. 1 oracle oracle 23392 May 13 2016 Util.pm
[oracle@databasevm Hash]$ ln -s Util.pm util.pm
[oracle@databasevm Hash]$ export ORACLE_SID=PRODCDB;sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 20 16:14:15 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> @?/rdbms/admin/catcdb.sql
SQL>
SQL> Rem The script relies on the caller to have connected to the DB
~省略~
SQL>
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter value for 1: /home/oracle ----> 执行上面脚本的执行日志目录
Enter value for 2: /home/oracle/prodcdb.log ----> 执行上面脚本的执行日志文件名
Enter new password for SYS: oracle ----> sys密码
Enter new password for SYSTEM: oracle ----> system密码
Enter temporary tablespace name: temp ----> 临时表空间名
过程比较久，请耐心等待，通过另外一个窗口，可以查看/home/oracle目录下的log日志文件是否随时更新中…
~省略~
[/home/oracle/utlrp_catcon_16940.lst]
catcon: See [/home/oracle/utlrp*.log] files for output generated by scripts
catcon: See [/home/oracle/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
SQL>
SQL> select name,cdb,con_id,open_mode from v$database;
NAME CDB CON_ID OPEN_MODE
--------- --- ---------- --------------------
PRODCDB YES 0 READ WRITE
SQL>
step 5. 配置监听
编辑listener.ora文件
[oracle@databasevm admin]$ pwd
/opt/oracle/product/12.2.0.1/db_1/network/admin
[oracle@databasevm admin]$
[oracle@databasevm admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCLCDB)
(SID_NAME = ORCLCDB)
(ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = PRODCDB)
(SID_NAME = PRODCDB)
(ORACLE_HOME = /opt/oracle/product/12.2.0.1/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
)
DEDICATED_THROUGH_BROKER_LISTENER=ON
DEFAULT_SERVICE_LISTENER = (ORCLCDB)
[oracle@databasevm admin]$
重启监听
lsnrctl stop;
lsnrctl start;
测试：
链接和测试
*关于windows链接vm，可以通过端口跳转进行处理，详细解释请自行baidu或者google之。
~~~ 完工 LinHong 2017/09/10 ~~~~