BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]Oracle12cR2_手工创建CDB

2017-09-10
   

[原创]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;

测试:

链接和测试

Conn

Version

*关于windows链接vm,可以通过端口跳转进行处理,详细解释请自行baidu或者google之。

portforward

~~~ 完工 LinHong 2017/09/10 ~~~~


Similar Posts

Comments