本文记录了在Oracle Linux 7中以静默方式(Silent)安装和配置Oracle Database 12c的具体方法和实施步骤。
如果想通过图形界面的方式进行安装的话,请参考本站的另一篇文章《Oracle Linux 7.3 下安装 Oracle Database 12c R2》。
打开Oracle VM VirtualBox Manager,打开File->Preferences...->Network->Host-only Networks-> 双击VirtualBox Host-Only Ethernet Adapter。在弹出窗口中,取消选择Enable Server,以禁止为Host-Only网卡自动分配地址。
在Virtualbox中创建一个虚拟机实例,配置如下:
在创建好的虚拟机实例中安装Oracle Linux 7.4,选择默认的最小化安装方式。
重启之后,以root用户登陆到系统中,开始配置网络。
在/etc/sysconfig/network-scripts中有三个文件:
先配置ifcfg-enp0s3,只需要改动一处:ONBOOT=yes,确保开机时自动激活该网卡。NAT方式的网卡都会以DHCP自动分配IP,所以不用配置IP地址。
再配置ifcfg-enp0s8:
BOOTPROTO=static #static为静态IP地址
ONBOOT=yes #开机自动启用网络连接
IPADDR0=192.168.56.112 #设置静态IP地址
PREFIXO0=24 #设置子网掩码
注意这里没有配置网关和DNS,如果你想配置的话,加入如下内容:
GATEWAY0=192.168.21.1 #设置网关
DNS1=8.8.8.8 #设置主DNS
DNS2=144.144.144.144 #设置备DNS
重启网络服务,并查看IP分配情况:
service network restart
ip addr
可以看到,NAT的网卡1动态分配了10.0.2.15这个地址,而Host-only的网卡2获取了静态地址192.168.56.112。
从主机用Putty直接访问192.168.56.112,就可以不再使用VirtualBox的那个终端了。
接下来设置主机名称为12col7-112.example.com。这里不再配置DNS服务器(bind),而简单的配置一个静态的/etc/hosts文件,确保主机名永远都能被解析到。最后重启系统。
[root@localhost ~]# hostname 12col7-112.example.com
[root@localhost ~]# cat /etc/hostname
12col7-112.example.com
[root@localhost ~]# cat /etc/hosts
127.0.0.1 localhost
::1 localhost
192.168.56.112 12col7-112 12col7-112.example.com
[root@localhost ~]# reboot
请参考关闭防火墙。
请参考停用 SELinux。
打开虚拟机实例的窗口,在菜单中选择Devices->Optical Drives->Choose Disk Image...打开刚才的系统安装光盘镜像。
登录到虚拟机实例中,挂载光盘到/media/cdrom:
[root@12col7-112 ~]# mkdir /media/cdrom
[root@12col7-112 ~]# mount -r /dev/cdrom /media/cdrom
备份原来的Yum源,配置新的本地Yum源:
[root@12col7-112 ~]# mkdir /etc/yum.repos.d/orig.repo.d
[root@12col7-112 ~]# mv /etc/yum.repos.d/public-yum-ol7.repo /etc/yum.repos.d/orig.repo.d/
[root@12col7-112 ~]# cat <<EOF>/etc/yum.repos.d/local.repo
> [Local]
> name=OL7.4
> baseurl=file:///media/cdrom
> gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY
> enabled=1
> EOF
[root@12col7-112 ~]#
测试本地Yum源:
[root@12col7-112 ~]# yum repolist
Loaded plugins: ulninfo
Local | 3.6 kB 00:00
(1/2): Local/group_gz | 136 kB 00:00
(2/2): Local/primary_db | 4.7 MB 00:00
repo id repo name status
Local OL7.4 4,970
repolist: 4,970
[root@12col7-112 ~]#
oracle-database-server-12cR2-preinstall包是Oracle Linux独有的软件包,它为安装Oracle数据库提供了方便(包括安装数据库依赖包,创建Oracle用户和组,修改内核参数等等)。
[root@12col7-112 ~]# yum install -y oracle-database-server-12cR2-preinstall
执行这步的目的是为了使用虚拟机与主机之间的共享目录功能,以方便安装文件的复制。当然,这个步骤并不是必须的。因为在网络配置好之后,还可以使用sftp通过主机向虚拟机中复制安装文件。
下面这些包是编译和安装 Guest Additions 时需要的:
[root@12col7-112 ~]# yum install lsof bzip2 gcc kernel-uek-devel
然后卸载系统安装镜像文件:
[root@12col7-112 ~]# umount /media/cdrom
然后打开虚拟机实例的窗口,在菜单中选择Devices->Optical Drives->Remove disk from virtual drive彻底弹出系统安装镜像。
接着,继续在菜单中选择Devices->Insert Guest Additions CD Image...把Guest Additions的镜像转到CD驱动器中。
登录到虚拟机实例,挂载Guest Additions光盘镜像并开始安装:
[root@12col7-112 ~]# mount -r /dev/cdrom /media/cdrom
[root@12col7-112 ~]# sh /media/cdrom/VBoxLinuxAdditions.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 5.1.26 Guest Additions for Linux...........
VirtualBox Guest Additions installer
Copying additional installer modules ...
Installing additional modules ...
vboxadd.sh: Starting the VirtualBox Guest Additions.
Could not find the X.Org or XFree86 Window System, skipping.
[root@12col7-112 ~]#
执行下面的命令,修改/etc/pam.d/login,启用 limits:
sed -i -e '/session required pam_selinux.so open/i\
session required \/lib64\/security\/pam_limits.so\
session required pam_limits.so' /etc/pam.d/login
修改后的文件如下:
[root@12col7-112 ~]# cat /etc/pam.d/login
#%PAM-1.0
auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so
auth substack system-auth
auth include postlogin
account required pam_nologin.so
account include system-auth
password include system-auth
# pam_selinux.so close should be the first session rule
session required pam_selinux.so close
session required pam_loginuid.so
session optional pam_console.so
# pam_selinux.so open should only be followed by sessions to be executed in the user context
session required /lib64/security/pam_limits.so
session required pam_limits.so
session required pam_selinux.so open
session required pam_namespace.so
session optional pam_keyinit.so force revoke
session include system-auth
session include postlogin
-session optional pam_ck_connector.so
[root@12col7-112 ~]#
修改oracle用户的密码为welcome。
[root@12col7-112 ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@12col7-112 ~]#
创建安装目录,并设置权限:
[root@12col7-112 ~]# mkdir -p /u01/app
[root@12col7-112 ~]# chown oracle:oinstall /u01/app
[root@12col7-112 ~]# usermod -G oinstall,dba,vboxsf oracle
[root@12col7-112 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),996(vboxsf)
[root@12col7-112 ~]#
重启系统,并以oracle用户登陆。
先从共享目录中把数据库的安装文件拷贝到oracle用户的主目录中,并解压缩。
[oracle@12col7-112 ~]$ mkdir install
[oracle@12col7-112 ~]$ cp /media/sf_VM/iso/linuxx64_12201_database.zip install/
[oracle@12col7-112 ~]$ cd install/
[oracle@12col7-112 install]$ unzip linuxx64_12201_database.zip
为保险起见,这时可以打开虚拟机实例的窗口,选择Machine->Take Snapshot建立一个快照。这样万一后面的步骤出了问题进行不下去的话,总可以回退到这个快照重新开始。
事实上,Oracle数据库安装包里面已经提供了各种响应文件的参考例子,这里只需要把这些文件拷贝出来,根据自己的需要修改即可。
[oracle@12col7-112 ~]$ mkdir -p silent-install/response
[oracle@12col7-112 ~]$ cp install/database/response/db_install.rsp silent-install/response/
需要修改的地方汇总如下:
oracle.install.option=INSTALL_DB_SWONLY # 只安装数据库软件,之后我们会手动创库
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE # 安装Enterprise Edition
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
DECLINE_SECURITY_UPDATES=true
去除了空白行和注释,完整的db_install.rsp文件内容如下:
[oracle@12col7-112 ~]$ cat silent-install/response/db_install.rsp | grep -v ^$ |grep -v ^#
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.rac.configurationType=
oracle.install.db.CLUSTER_NODES=
oracle.install.db.isRACOneInstall=
oracle.install.db.racOneServiceName=
oracle.install.db.rac.serverpoolName=
oracle.install.db.rac.serverpoolCardinality=
oracle.install.db.config.starterdb.type=
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.ConfigureAsContainerDB=
oracle.install.db.config.PDBName=
oracle.install.db.config.starterdb.characterSet=
oracle.install.db.config.starterdb.memoryOption=
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.password.PDBADMIN=
oracle.install.db.config.starterdb.managementOption=
oracle.install.db.config.starterdb.omsHost=
oracle.install.db.config.starterdb.omsPort=
oracle.install.db.config.starterdb.emAdminUser=
oracle.install.db.config.starterdb.emAdminPassword=
oracle.install.db.config.starterdb.enableRecovery=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
COLLECTOR_SUPPORTHUB_URL=
[oracle@12col7-112 ~]$
启动OUI安装器,指定响应文件,开始静默安装,大概需要3分钟左右,很快不是么?
<INSTALL_DIR>/runInstaller -ignoreSysPrereqs -waitforcompletion -silent -responseFile <FULL PATH>
需要注意的是:
[oracle@12col7-112 ~]$ /home/oracle/install/database/runInstaller -ignoreSysPrereqs -waitforcompletion -silent -responseFile /home/oracle/silent-install/response/db_install.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 39345 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3967 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-09-21_11-47-25AM. Please wait ...You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2017-09-21_11-47-25AM.log
The installation of Oracle Database 12c was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2017-09-21_11-47-25AM.log' for more details.
As a root user, execute the following script(s):
1. /u01/app/oraInventory/orainstRoot.sh
2. /u01/app/oracle/product/12.2.0/dbhome_1/root.sh
Successfully Setup Software.
按照上面的说明,切换到root用户,执行这两个脚本:
[oracle@12col7-112 ~]$ su -
Password:
Last login: Thu Sep 21 10:41:14 CST 2017 from 192.168.56.1 on pts/0
[root@12col7-112 ~]# /u01/app/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@12col7-112 ~]# /u01/app/oracle/product/12.2.0/dbhome_1/root.sh
Check /u01/app/oracle/product/12.2.0/dbhome_1/install/root_12col7-112.example.com_2017-09-21_11-56-20-903254406.log for the output of root script
[root@12col7-112 ~]#
先编写一个Oracle环境变量配置文件db_profile.sh:
[oracle@12col7-112 ~]$ cat db_profile.sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
在当前的Shell中,执行该文件,导入Oracle环境变量,确保dbca命令能正确找到:
[oracle@12col7-112 ~]$ . db_profile.sh
[oracle@12col7-112 ~]$ env|grep ORACLE
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
[oracle@12col7-112 ~]$ which dbca
/u01/app/oracle/product/12.2.0/dbhome_1/bin/dbca
开始安装。下面的命令会创建一个pdb,其中包含一个数据库实例orcl,所有管理员的密码都设置为welcome。
dbca -silent \
-createDatabase \
-templateName General_Purpose.dbc \
-gdbName orcl.example.com \
-sid orcl \
-createAsContainerDatabase true \
-numberOfPdbs 1 \
-pdbName pdb \
-pdbadminUsername pdba \
-pdbadminPassword welcome \
-SysPassword welcome \
-SystemPassword welcome \
-emConfiguration NONE \
-recoveryAreaDestination $ORACLE_BASE/recovery_area \
-characterSet "AL32UTF8" \
-nationalCharacterSet "UTF8" \
-enableArchive true \
-redoLogFileSize 100
实际执行结果如下:
[oracle@12col7-112 ~]$ dbca -silent \
> -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbName orcl.example.com \
> -sid orcl \
> -createAsContainerDatabase true \
> -numberOfPdbs 1 \
> -pdbName pdb \
> -pdbadminUsername pdba \
> -pdbadminPassword welcome \
> -SysPassword welcome \
> -SystemPassword welcome \
> -emConfiguration NONE \
> -recoveryAreaDestination $ORACLE_BASE/recovery_area \
> -recoveryAreaSize 3200 \
> -characterSet "AL32UTF8" \
> -nationalCharacterSet "UTF-8" \
> -enableArchive true \
> -redoLogFileSize 100
[WARNING] [DBT-06801] Specified Fast Recovery Area size (3,200 MB) is less than the recommended value.
CAUSE: Fast Recovery Area size should at least be three times the database size (2,430 MB).
ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
[FATAL] [DBT-11152] National character set specified (UTF-8) is invalid.
[oracle@12col7-112 ~]$ dbca -silent \
> -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbName orcl.example.com \
> -sid orcl \
> -createAsContainerDatabase true \
> -numberOfPdbs 1 \
> -pdbName pdb \
> -pdbadminUsername pdba \
> -pdbadminPassword welcome \
> -SysPassword welcome \
> -SystemPassword welcome \
> -emConfiguration NONE \
> -recoveryAreaDestination $ORACLE_BASE/recovery_area \
> -characterSet "AL32UTF8" \
> -nationalCharacterSet "UTF8" \
> -enableArchive true \
> -redoLogFileSize 100
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
49% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.
[oracle@12col7-112 ~]$
登陆数据库,查看一下数据库实例的状态:
[oracle@12col7-112 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 21 13:35:34 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> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/orcl/pdb/system01.dbf
/u01/app/oracle/oradata/orcl/pdb/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdb/undotbs01.dbf
/u01/app/oracle/oradata/orcl/pdb/users01.dbf
11 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/recovery_area/orcl/control02.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
同样,安装包里面提供了配置监听的响应文件。只需要在这个文件的基础上修改就行。
[oracle@12col7-112 ~]$ cp install/database/response/netca.rsp silent-install/response/
去除空白行和注释之后的响应文件内容如下:
[oracle@12col7-112 ~]$ cat silent-install/response/netca.rsp | grep -v ^$ | grep -v ^#
[GENERAL]
RESPONSEFILE_VERSION="12.2"
CREATE_TYPE="CUSTOM"
SHOW_GUI=false
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}
[oracle@12col7-112 ~]$
通过响应文件,鸟悄地默默开始配置监听器:
[oracle@12col7-112 ~]$ netca -silent -responsefile /home/oracle/silent-install/response/netca.rsp
Parsing command line arguments:
Parameter "silent" = true
Parameter "responsefile" = /home/oracle/silent-install/response/netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
Running Listener Control:
/u01/app/oracle/product/12.2.0/dbhome_1/bin/lsnrctl start LISTENER
Listener Control complete.
Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0
[oracle@12col7-112 ~]$
确认监听器状态:
[oracle@12col7-112 ~]$ lsnrctl
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-SEP-2017 15:00:49
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=12col7-112)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 21-SEP-2017 14:59:54
Uptime 0 days 0 hr. 0 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/12col7-112/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=12col7-112)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "59acf70353b0390ae0537038a8c0a3af.example.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl.example.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.example.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb.example.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
请参考下面的文档进行:
最后设置好的数据库可以用EM来访问。
重启系统,之后继续登陆EM,看看数据库是否正常启动。
在配置之前需要先了解在Oracle 12c 中引入的多租用户环境(Multitenant Environment),也就是让一个CDB(数据库容器)存放多个PDB(可插拔数据库),像下面这样:
可以看出,在一个CDB数据库容器中包含了以下几个容器:
在使用sqlplus / as sysdba登陆时,默认登陆到Root容器:
[oracle@12col7-112 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 22 10:27:51 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> show con_name;
CON_NAME
------------------------------
CDB$RO
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
在理解了以上概念之后,就明白要创建表空间,首先要切换到PDB中。
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> alter session set container=PDB;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB
SQL>
然后在这个PDB中,创建一个名称为TEST01_TBS,大小为4GB的表空间:
SQL> create tablespace test01_tbs datafile '/u01/app/oracle/oradata/orcl/pdb/test01_tbs.dbf' size 4g;
Tablespace created.
SQL>
如果这一步出错了,想删除表空间的话:
drop tablespace TEST01_TBS including contents and datafiles;
查看表空间:
SQL> set linesize 200
SQL> col file_name for a50
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/orcl/pdb/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/pdb/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/pdb/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/pdb/users01.dbf USERS
/u01/app/oracle/oradata/orcl/pdb/test01_tbs.dbf TEST01_TBS
SQL>
先切换到PDB中,创建测试用户test01,指定默认表空间为test_tbs,密码为test01,并解锁用户账号。
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> create user test01 identified by test01 default tablespace TEST01_TBS account unlock;
User created.
SQL> col username for a6
SQL> col default_tablespace for a15
SQL> col temporary_tablespace for a15
SQL> select username,default_tablespace,temporary_tablespace,account_status from dba_users where username like '%TEST01%';
USERNA DEFAULT_TABLESP TEMPORARY_TABLE ACCOUNT_STATUS
------ --------------- --------------- --------------------------------
TEST01 TEST01_TBS TEMP OPEN
SQL>
为test01用户赋予权限,这样test01用户才能访问和操作数据库:
SQL> grant connect, resource to test01;
Grant succeeded.
SQL>
查看CBD中可以用的服务,会发现通过pdb.example.com这个service_name是可以远程连接到PDB的。
SQL> col name for a15
SQL> col name for a30
SQL> select name,pdb from v$services;
NAME PDB
------------------------------ --------------------
orclXDB CDB$ROOT
orcl.example.com CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
pdb.example.com PDB
SQL>
此外,查看$ORACLE_HOME/network/admin/sqlnet.ora的内容,可以看到Oracle默认启用了两种命名方式:
[oracle@12col7-112 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@12col7-112 ~]$
那么,接下来就使用这两种方式分别链接到数据库。
这种方法无需任何配置,直接可以使用,需要注意的是格式。
如果已经进入到了sqlplus中,使用下面的格式:
CONNECT username/password@[//]host[:port][/service_name]
否则,使用下面的格式:
sqlplus username/password@[//]host[:port][/service_name]
所以,在数据库本机上,以下任何一种形式都可以让test01用户访问到PDB中:
sqlplus test01/test01@12col7-112:1521/pdb.example.com
sqlplus test01/test01@localhost:1521/pdb.example.com
sqlplus test01/test01@12col7-112/pdb.example.com
sqlplus test01/test01@localhost/pdb.example.com
实际效果如下:
[oracle@12col7-112 admin]$ sqlplus test01/test01@12col7-112:1521/pdb.example.com
SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 22 14:21:40 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Sep 22 2017 14:13:32 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
为了能通过TNSNAMES访问到数据库,需要在$ORACLE_HOME/network/admin/tnsnames.ora
文件中加入如下内容:
pdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12col7-112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb.example.com)
)
)
之后,就可以通过下面的方式直接访问到PDB了:
[oracle@12col7-112 ~]$ sqlplus test01/test01@pdb
SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 22 14:30:11 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Sep 22 2017 14:21:40 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>