Oracle 12c 搭建学习
Vm workstaton10 安装linux 6.4 安装oracle12c
Oracle 12c只支持64位系统
1 环境检查
[root@rac1 ~]# grep MemTotal /proc/meminfo
MemTotal: 1914684 kB
[root@rac1 ~]# grep SwapTotal /proc/meminfo
SwapTotal: 4095992 kB
[root@rac1 ~]# df -k /tmp
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 40980816 2691472 36207604 7% /
[root@rac1 ~]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 40980816 2691472 36207604 7% /
tmpfs 2097152 372 2096780 1% /dev/shm
/dev/sda1 297485 37162 244963 14% /boot
/dev/sr0 49064 49064 0 100% /media/CDROM
/dev/sr1 3632776 3632776 0 100% /media/RHEL_6.4 x86_64 Disc 1
[root@rac1 ~]# grep "model name" /proc/cpuinfo
model name : Pentium(R) Dual-Core CPU E5500 @ 2.80GHz
model name : Pentium(R) Dual-Core CPU E5500 @ 2.80GHz
[root@localhost ~]# uname -a
Linux localhost.localdomain 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost ~]# uname -r
2.6.32-431.el6.x86_64
[root@local12cdg ~]# cat /proc/version
Linux version 2.6.32-431.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Fri Nov 22 03:15:09 UTC 2013
[oracle@local12cdg app]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 75G 8.8G 63G 13% /
tmpfs 935M 0 935M 0% /dev/shm
/dev/sda1 291M 39M 238M 14% /boot
[oracle@local12cdg app]$ free -g
total used free shared buffers cached
Mem: 1 1 0 0 0 0
-/+ buffers/cache: 0 1
Swap: 3 0 3
2 检查相应的包
[root@rac1 yum_pkg]# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH}) ' binutils compat-libcap1 compat-libstdc gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXext libXtst libX11 libXau libxcb libXi make sysstat
binutils-2.20.51.0.2-5.36.el6 (x86_64)
package compat-libcap1 is not installed
package compat-libstdc is not installed
gcc-4.4.7-3.el6 (x86_64)
gcc-c++-4.4.7-3.el6 (x86_64)
glibc-2.12-1.107.el6 (x86_64)
glibc-2.12-1.107.el6 (i686)
glibc-devel-2.12-1.107.el6 (x86_64)
glibc-devel-2.12-1.107.el6 (i686)
package ksh is not installed
libgcc-4.4.7-3.el6 (x86_64)
libstdc++-4.4.7-3.el6 (x86_64)
libstdc++-devel-4.4.7-3.el6 (i686)
libstdc++-devel-4.4.7-3.el6 (x86_64)
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libXext-1.3.1-2.el6 (x86_64)
libXtst-1.2.1-2.el6 (x86_64)
libX11-1.5.0-4.el6 (x86_64)
libXau-1.0.6-4.el6 (x86_64)
libxcb-1.8.1-1.el6 (x86_64)
libXi-1.6.1-3.el6 (x86_64)
make-3.81-20.el6 (x86_64)
sysstat-9.0.4-20.el6 (x86_64)
[root@rac1 yum_pkg]# rpm -Uvh compat-db* --aid --nodeps --force
http://docs.oracle.com/database/121/LTDQI/toc.htm#CEGHFFGG
|
Packages for Oracle Linux 6 and Red Hat Enterprise Linux 6 |
The following packages (or later versions) must be installed: binutils-2.20.51.0.2-5.11.el6 (x86_64)compat-libcap1-1.10-1 (x86_64)compat-libstdc++-33-3.2.3-69.el6 (x86_64)compat-libstdc++-33-3.2.3-69.el6 (i686)gcc-4.4.4-13.el6 (x86_64)gcc-c++-4.4.4-13.el6 (x86_64)glibc-2.12-1.7.el6 (i686)glibc-2.12-1.7.el6 (x86_64)glibc-devel-2.12-1.7.el6 (x86_64)glibc-devel-2.12-1.7.el6 (i686)kshlibgcc-4.4.4-13.el6 (i686)libgcc-4.4.4-13.el6 (x86_64)libstdc++-4.4.4-13.el6 (x86_64)libstdc++-4.4.4-13.el6 (i686)libstdc++-devel-4.4.4-13.el6 (x86_64)libstdc++-devel-4.4.4-13.el6 (i686)libaio-0.3.107-10.el6 (x86_64)libaio-0.3.107-10.el6 (i686)libaio-devel-0.3.107-10.el6 (x86_64)libaio-devel-0.3.107-10.el6 (i686)libXext-1.1 (x86_64)libXext-1.1 (i686)libXtst-1.0.99.2 (x86_64)libXtst-1.0.99.2 (i686)libX11-1.3 (x86_64)libX11-1.3 (i686)libXau-1.0.5 (x86_64)libXau-1.0.5 (i686)libxcb-1.5 (x86_64)libxcb-1.5 (i686)libXi-1.3 (x86_64)libXi-1.3 (i686)make-3.81-19.el6sysstat-9.0.4-11.el6 (x86_64) |
[root@localhost ~]# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH}) ' binutils compat-libcap1 compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXext libXtst libX11 libXau libxcb libXi make sysstat
[root@localhost oracleware]# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH}) ' binutils compat-libcap1 compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel libXext libXtst libX11 libXau libxcb libXi make sysstat
binutils-2.20.51.0.2-5.36.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
package compat-libstdc++ is not installed
gcc-4.4.7-11.el6 (x86_64)
gcc-c++-4.4.7-11.el6 (x86_64)
glibc-2.12-1.132.el6 (x86_64)
glibc-2.12-1.7.el6_0.3 (i686)
glibc-devel-2.12-1.132.el6 (x86_64)
glibc-devel-2.12-1.132.el6_5.5 (i686)
package ksh is not installed
libgcc-4.4.7-11.el6 (x86_64)
libstdc++-4.4.7-11.el6 (x86_64)
libstdc++-4.4.4-13.el6 (i686)
libstdc++-devel-4.4.7-11.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6 (i686)
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (i686)
libaio-devel-0.3.107-10.el6 (x86_64)
libXext-1.3.1-2.el6 (x86_64)
libXext-1.1-3.el6 (i686)
libXtst-1.2.1-2.el6 (x86_64)
libXtst-1.0.99.2-3.el6 (i686)
libX11-1.5.0-4.el6 (x86_64)
libX11-1.3-2.el6 (i686)
libXau-1.0.6-4.el6 (x86_64)
libXau-1.0.5-1.el6 (i686)
libxcb-1.8.1-1.el6 (x86_64)
libxcb-1.8.1-1.el6 (i686)
libXi-1.6.1-3.el6 (x86_64)
libXi-1.6.1-3.el6 (i686)
make-3.81-20.el6 (x86_64)
sysstat-9.0.4-22.el6 (x86_64)
[root@localhost oracleware]#
[root@localhost oracleware]# rpm -qa|grep compat-libstdc++-33-3.2.3-69.el6
compat-libstdc++-33-3.2.3-69.el6.i686
compat-libstdc++-33-3.2.3-69.el6.x86_64
package gcc-c++ is not installed
Installed:
gcc-c++.x86_64 0:4.4.7-11.el6
Dependency Updated:
cpp.x86_64 0:4.4.7-11.el6 gcc.x86_64 0:4.4.7-11.el6 libgcc.i686 0:4.4.7-11.el6 libgcc.x86_64 0:4.4.7-11.el6
libgomp.x86_64 0:4.4.7-11.el6 libstdc++.i686 0:4.4.7-11.el6 libstdc++.x86_64 0:4.4.7-11.el6 libstdc++-devel.i686 0:4.4.7-11.el6
libstdc++-devel.x86_64 0:4.4.7-11.el6
Complete!
[root@local12cdg yum.repos.d]# yum install gcc-c++
3 创建oracle用户
[root@localhost ~]# /usr/sbin/groupadd oinstall
[root@localhost ~]# /usr/sbin/groupadd dba
[root@localhost ~]# id oracle
id: oracle: No such user
[root@localhost ~]# /usr/sbin/useradd -g oinstall -G dba oracle
[root@localhost ~]# passwd oracle
Changing password for user oracle.
New password: hongquanoracle
Retype new password:
passwd: all authentication tokens updated successfully.
[root@localhost ~]# id oracle
uid=502(oracle) gid=502(oinstall) groups=502(oinstall),503(dba)
4 禁用 selinux
[root@localhost ~]# /usr/sbin/sestatus
SELinux status: enabled
SELinuxfs mount: /selinux
Current mode: enforcing
Mode from config file: enforcing
Policy version: 24
Policy from config file: targeted
[root@localhost ~]# vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
[root@localhost ~]# setenforce 0
需要重启？ 重启生效
[root@localhost ~]# /usr/sbin/sestatus
SELinux status: disabled
5 Configuring Kernel Parameters and Resource Limits
|
Parameter |
Command |
|
semmsl, semmns, semopm, and semmni |
# /sbin/sysctl -a | grep sem This command displays the value of the semaphore parameters in the order listed. |
|
shmall, shmmax, and shmmni |
# /sbin/sysctl -a | grep shm |
|
file-max |
# /sbin/sysctl -a | grep file-max |
|
ip_local_port_range |
# /sbin/sysctl -a | grep ip_local_port_range |
|
rmem_default |
# /sbin/sysctl -a | grep rmem_default |
|
rmem_max |
# /sbin/sysctl -a | grep rmem_max |
|
wmem_default |
# /sbin/sysctl -a | grep wmem_default |
|
wmem_max |
# /sbin/sysctl -a | grep wmem_max |
Verify that the kernel parameters shown in the following table are set to values greater than or equal to the minimum value shown.
|
fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 536870912 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 = 1048586 |
[root@localhost ~]# vi /etc/sysctl.conf
[root@localhost ~]# /sbin/sysctl -p
Enter the command /sbin/sysctl -a to confirm that the values are set correctly.
[root@rac1 yum_pkg]# vi /etc/security/limits.conf
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 10240
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ ulimit -Sn
1024
[oracle@localhost ~]$ ulimit -Hn
65536
[root@rac1 yum_pkg]# vi /home/oracle/.bash_profile
export PATH
export TMP=/tmp;
export TMPDIR=$TMP;
DISPLAY=localhost:0.0
export DISPLAY
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_HOME
ORACLE_SID=rac1
export ORACLE_SID
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG
PATH=$ORACLE_HOME/bin:/bin:/sbin:/usr/ccs/bin:/usr/local/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/local/lib
export LD_LIBRARY_PATH
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
if [ $USER = "oracle" ]||[ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
stty erase ^h
[root@localhost ~]# source .bash_profile
[root@local12cdg app]# vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
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 = 1048586
6 Creating Required Directories
[root@localhost ~]# mkdir -p /u01/app/
[root@localhost ~]# chown -R oracle:oinstall /u01/app/
[root@localhost ~]# chmod -R 775 /u01/app/
7 安装
[oracle@localhost oracleware]$ unzip linuxamd64_12c_database_2of2.zip
[oracle@localhost /]$ cd /u01/oracleware/database/
[oracle@localhost database]$ ll
total 32
drwxr-xr-x. 4 oracle oinstall 4096 Apr 15 02:28 install
drwxrwxr-x. 2 oracle oinstall 4096 May 24 2013 response
drwxr-xr-x. 2 oracle oinstall 4096 May 24 2013 rpm
-rwxr-xr-x. 1 oracle oinstall 7808 May 24 2013 runInstaller
drwxrwxr-x. 2 oracle oinstall 4096 May 24 2013 sshsetup
drwxr-xr-x. 14 oracle oinstall 4096 May 24 2013 stage
-r-xr-xr-x. 1 oracle oinstall 500 Jun 9 2013 welcome.html
安装xterm
[root@localhost yum.repos.d]# yum install xterm
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
[root@localhost yum.repos.d]# xterm -help
[root@localhost ~]# whereis xterm
xterm: /usr/bin/xterm /usr/share/man/man1/xterm.1.gz
/usr/bin/xterm -ls -display $DISPLAY
[oracle@localhost database]$ su - root
Password:
[root@localhost ~]# DISPLAY=:0.0
[root@localhost ~]# export DISPLAY
[root@localhost ~]# echo $DISPLAY
:0.0
[root@localhost ~]# xhost +
access control disabled, clients can connect from any host
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ DISPLAY=:0.0
[oracle@localhost ~]$ export DISPLAY
[oracle@localhost ~]$ echo $DISPLAY
:0.0
[oracle@localhost ~]$ cd /u01/oracleware/database/
[oracle@localhost database]$ ./runInstaller
./runInstaller: line 21: dirname: command not found
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 57706 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3999 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-04-15_08-16-10PM. Please wait ...[oracle@lo
[root@localhost database]# more runInstaller
#!/bin/sh
CMDDIR=`dirname $0`
设置oracle用户的环境变量
[oracle@localhost ~]$ echo $PATH
[oracle@localhost ~]$ env
-bash: env: No such file or directory
[oracle@localhost ~]$ PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/ccs/bin
[oracle@localhost ~]$ export PATH
[oracle@localhost ~]$ echo $PATH
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/ccs/bin
[oracle@localhost ~]$ id oracle
uid=502(oracle) gid=502(oinstall) groups=502(oinstall),503(dba)
[oracle@localhost ~]$
[oracle@localhost ~]$ ls /usr/bin/dirname -l
-rwxr-xr-x. 1 root root 26264 Nov 22 2013 /usr/bin/dirname
ORACLE_TERM=xterm
export ORACLE_TERM
[root@localhost ~]# cd /u01/app/ora
oracle/ oraInventory/
[root@localhost ~]# cd /u01/app/oraInventory/
[root@localhost 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@localhost oraInventory]# cd /u01/app/oracle/product/12.1.0/dbhome_1/
[root@localhost dbhome_1]# ./root.sh
Performing root user operation for Oracle 12c
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.1.0/dbhome_1
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.
---20150705
netca
[oracle@localhost bin]$ netca
-bash: netca: command not found
[oracle@localhost bin]$ ls netca
Netca
[oracle@localhost database]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/bin/
[oracle@localhost bin]$ ls netca
netca
[oracle@localhost bin]$ ls netmgr
netmgr
[oracle@localhost bin]$ netca
-bash: netca: command not found
[root@localhost dbhome_1]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.188 oracle12c
[root@localhost dbhome_1]# vi /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=oracle12c
GATEWAY=192.168.2.2
修改一下参数
[oracle@oracle12c bin]$ more /home/oracle/.bash_profile
PATH=$ORACLE_HOME/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/ccs/bin
PATH=/u01/app/oracle/product/12.1.0/dbhome_1/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/ccs/bin
export PATH
开机启动
[oracle@oracle12c admin]$ more /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.
touch /var/lock/subsys/local
su - oracle -c "/u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl start"
su - oracle -c "/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbstart start"
su - mysql /data/mysqldata/scripts/mysql_db_startup.sh > /home/mysql/mysql_db_startup.log 2>&1
[oracle@oracle12c bin]$ cat /etc/oratab
local12c:/u01/app/oracle/product/12.1.0/dbhome_1:Y
sqlplus /nolog
[oracle@oracle12c bin]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Thu Apr 16 01:15:10 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn sys/987064 as sysdba
Connected to an idle instance.
SQL> show pdbs;
ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SP2-1545: This feature requires Database availability.
SQL> startup;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2288872 bytes
Variable Size 1040188184 bytes
Database Buffers 553648128 bytes
Redo Buffers 7286784 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
SQL> show user;
USER is "SYS"
SQL> show con_name; ----------查看当前容器
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID
---------- ----------
NAME
------------------------------------------------------------
OPEN_MODE
--------------------
2 4119682648
PDB$SEED
READ ONLY
3 2324297228
PDBORCL
READ WRITE
CON_ID DBID
---------- ----------
NAME
------------------------------------------------------------
OPEN_MODE
--------------------
SQL> show pdbs;----------查看pdb
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
SQL> alter session set container=PDBORCL;
Session altered.
SQL> create user oracle12c identified by 'oracle12c';
create user oracle12c identified by 'oracle12c'
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
SQL> create user oracle12c identified by "oracle12c";
User created.
-------查看services
SQL> SELECT NAME, CON_ID FROM V$ACTIVE_SERVICES ORDER BY 1;
NAME CON_ID
-------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
local12cXDB 1
orcl.localdomain 1
pdborcl.localdomain 3
yhq_pdb.localdomain 4
--建立连接
--进入cdb
sqlplus /nolog
conn sys/sys as dba
--//
sqlplus sys/sys@localhost:1521/orcl as sysdba
--进入pdb（pdb可以通过alter session container进入也可以直接通过tns方式（如下）登录）
sqlplus sys/sys@localhost:1521/pdborcl as sysdba
--显示所有的plaggable db
show pdbs;
--查看当前连接名称
show con_name;
--新建pdb
--1直接创建
CREATE PLUGGABLE DATABASE yhq_pdb
ADMIN USER yhq_pdb IDENTIFIED BY yhq_pdb
ROLES=(CONNECT)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE yhq_pdb DATAFILE '/u01/app/oracle/oradata/yhq_db/yhq_pdb001.dbf' SIZE 25M AUTOEXTEND ON
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/yhq_db/yhq_pdb/');
SQL> CREATE PLUGGABLE DATABASE yhq_pdb
2 ADMIN USER yhq_pdb IDENTIFIED BY yhq_pdb
3 ROLES=(CONNECT)
4 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
5 DEFAULT TABLESPACE yhq_pdb DATAFILE '/u01/app/oracle/oradata/yhq_db/yhq_pdb001.dbf' SIZE 25M AUTOEXTEND ON
6 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/yhq_db/yhq_pdb/');
Pluggable database created.
SQL> alter pluggable database yhq_pdb open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 YHQ_PDB READ WRITE NO
ALTER PLUGGABLE DATABASE YHQ_PDB OPEN;
ALTER PLUGGABLE DATABASE YHQ_PDB CLOSE;
SQL> ALTER PLUGGABLE DATABASE PDBORCL CLOSE;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 YHQ_PDB MOUNTED
--2以现有pdb为模板创建
--现有pdb必须在read only模式下才能被创建，否则报错
--以pdb1为模板创建pdb2
drop pluggable database pdb2 including datafiles;
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;
create pluggable database pdb2
from pdb1
file_name_convert =('/opt/oracle/oradata/ZARADB/E98BEB85B1A80C56E0439A02A8C05841/datafile/','/opt/oracle/oradata/ZARADB/datafile/pdb2');
--打开关闭pdb
alter pluggable database pdb2 close;
alter pluggable database all close;
alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 open;
alter pluggable database all open;
---删除pdb
--与删除表空间类似，可以级联删除数据文件
drop pluggable database pdb2;
drop pluggable database pdb2 including datafiles;
--unplug
--unplug后pdb只能mount不能open
alter pluggable database pdb2 unplug into '/tmp/pdb2.xml';
--unplug后删除pdb，再使用xml文件加回pdb
drop pluggable database pdb2;
create pluggable database pdb2 using '/tmp/pdb2.xml' nocopy;
--启动关闭PDB
--pdb的管理可以在cdb中进行也可以在pdb中进行,
--如果是cdb中进行,需要PLUGGABLE关键字（如下alter）,
--如果是pdb中直接和普通数据库一样(startup/shutdown immediate)
--打开单个pdb
alter pluggable database pdborcl open;
--打开所有pdb
alter pluggable database all open;
--关闭所有pdb
alter pluggable database all close immediate;
--进入pdb
alter session set container=pdborcl;
--//
conn sys/sys@localhost:1521/pdborcl as sysdba;
--进入cdb
alter session set container=cdb$root;
--//
conn / as sysdba;
--创建用户
--创建用户默认的是container=all,在cdb中只能创建全局用户(c##开头),
--会在cdb和所有的pdb中创建该用户(但是pdb中的全局用户需要另外授权才能够在pdb中访问)。
--在pdb中只能创建的用户为本地用户
--创建全局用户
create user c##xff identified by xifenfei;
--创建本地用户
alter session set container=pdborcl;
create user xxx identified by 123;
--用户授权
--用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的pdb且存在该用户都进行授权
grant connect to c##xff;
grant resource to c##xff container=all
Cdb中
SQL> conn /as sysdba
Connected.
SQL> create user c##t2 identified by t2;
User created.
SQL> grant connect to c##t2;
Grant succeeded.
SQL> grant resource to c##t2;
Grant succeeded.
SQL> conn c##t2 /t2
Connected.
SQL>
Pdb中
SQL> alter session set container=PDBORCL;
Session altered.
SQL> create user test1 identified by test1;
User created.
SQL> grant connect to test1;
Grant succeeded.
SQL> grant resource to test1;
Grant succeeded.
SQL> conn test1/test1
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle12c admin]$ sqlplus TEST1/test1@192.168.2.188:1521/pdborcl.localdomain
SQL*Plus: Release 12.1.0.1.0 Production on Thu Apr 16 18:52:26 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show user;
USER is "TEST1"
SQL> conn oracle12c/oracle12c@192.168.2.188:1521/pdborcl.localdomain
Connected.
SQL> show user;
USER is "ORACLE12C"
--修改参数
--在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义
alter system set open_cursors=500 container=all;
show parameter open_cursors;
alter session set container=pdborcl;
show parameter open_cursors;
--//
alter system set open_cursors=100;
show parameter open_cursors;
conn / as sysdba
show parameter open_cursors;
Pdb自启动
SQL> conn / as sysdba
Connected.
SQL> CREATE TRIGGER open_all_pdbs
2 AFTER STARTUP
3 ON DATABASE
4 BEGIN
5 EXECUTE IMMEDIATE 'alter pluggable database all open';
6 END open_all_pdbs;
7 /
Trigger created.
Cdb与pdb的区别
12c以前是一对一，一对多（rac)
进入oracle12c以后，实例与数据库的关系可以是一对多
CDB组件（Components of a CDB）
一个CDB数据库容器包含了下面一些组件：
ROOT组件
ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User,元数据的一个例子是ORACLE提供的PL/SQL包的源代码，Common User 是指在每个容器中都存在的用户。
SEED组件
Seed又叫PDB$SEED,这个是你创建PDBS数据库的模板，你不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed. 这个感念，个人感觉非常类似SQL SERVER中的model数据库。
PDBS
CDB中可以有一个或多个PDBS，PDBS向后兼容，可以像以前在数据库中那样操作PDBS，这里指大多数常规操作。
这些组件中的每一个都可以被称为一个容器。因此，ROOT(根)是一个容器，Seed(种子)是一个容器，每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
NAME Multitenant Option OPEN_MODE CON_ID
------------------ ---------------------------------------------------- ---------------------------------------- ----------
ORCL Multitenant Option enabled READ WRITE 0
YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)
SQL> show con_name
CON_NAME
------------------------------
PDBORCL
SQL> select sys_context('userenv', 'con_name') "Container DB" from dual;
查看cdb中有多少个pluggable database
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;