Oracle 12c 搭建学习

Oracle 12c 搭建学习
1 环境检查
2 检查相应的包
3 创建oracle用户
4 禁用 selinux
5 Configuring Kernel Parameters and Resource Limits
6 Creating Required Directories
7 安装
netca
开机启动
sqlplus /nolog
--建立连接
--新建pdb
--启动关闭PDB
--创建用户
Pdb自启动
Cdb与pdb的区别

Vm workstaton10 安装linux 6.4 安装oracle12c

Oracle 12c只支持64位系统

[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

[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

umask 022

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

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

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;

2 检查相应的包

3 创建oracle用户

4 禁用 selinux

5 Configuring Kernel Parameters and Resource Limits

6 Creating Required Directories

7 安装

netca

开机启动

sqlplus /nolog

--建立连接

--新建pdb

--启动关闭PDB

--创建用户

Cdb中

Pdb中

Pdb自启动

Cdb与pdb的区别

相关推荐