DBA和后台开发人员偶尔会遇到要重建数据库的情况，大多数情况下，可以使用DBCA配置助手，以交互式方式，在图形界面中完成。但是在有些特殊的情况下，Oracle数据库服务器上没有安装图形界面，临时安装图形界面有比较困难或费时。这时该怎么办？
遇到这种情况，有经验的DBA可以通过create database 命令创建，这种方式步骤比较繁琐，需要理解大量的创建参数，需要对Oracle数据库有比较深入的了解，有没有其它办法可以在命令行模式下重建数据库？
Oracle 的DBCA提供了-silent（沉默）运行模式，使用这种模式创建数据库，不需要数据库服务器支持图形界面，步骤也相对简单一些，只需要编辑一个响应文件，然后运行DBCA命令。
1 环境准备
使用DBCA创建数据库前，需要做一下准备，获取响应文件的模板，搜集一下响应文件需要的信息。
DBCA命令位于数据库ORACLE_HOME目录下bin目录中，一般这个目录会在shell变量的PATH路径中，可以直接运行这个命令，不需要切换到ORACLE_HOME目录下bin目录运行。
响应文件模板可以从网上找一个下载下来，也可以使用Oracle安装包中的响应文件模板。Oracle数据库安装完成后默认也安装了dbca的响应文件模板，这个模板在下面的目录中。
[oracle@iZ2ze0t8khaprrpfvmevjiZ dbca]$ pwd/opt/oracle/product/21c/dbhome_1/assistants/dbca
这个目录中有一个响应文件的样本，可以根据自己的需要修改。要想启动dbca命令，虽然是命令行模式，也需要设置操作系统环境变量DISPLAY，可以设置成下面的值
[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ exportDISPLAY=.0:0
运行DBCA另一个要注意的是服务器etc目录下hosts文件中主机名不能解析为本地环回地址，否则，命令运行会报错，这个好像与java有关。
2 编辑响应文件
DBCA的响应文件中大部分参数都是不是强制的，可以采用默认设置如下图所示：
[oracle@iZ2ze0t8khaprrpfvmevjiZ dbca]$ grep"# Mandatory : NO" dbca.rsp -A2# Mandatory : NO#-----------------------------------------------------------------------------characterSet=--# Mandatory : NO#-----------------------------------------------------------------------------listeners=--# Mandatory : NO#-----------------------------------------------------------------------------variablesFile=--# Mandatory : NO#-----------------------------------------------------------------------------variables=--# Mandatory : NO#-----------------------------------------------------------------------------initParams=--# Mandatory : NO#-----------------------------------------------------------------------------memoryPercentage=--# Mandatory : NO#-----------------------------------------------------------------------------databaseType=--# Mandatory : NO#-----------------------------------------------------------------------------automaticMemoryManagement=--# Mandatory : NO#-----------------------------------------------------------------------------totalMemory=[oracle@iZ2ze0t8khaprrpfvmevjiZ dbca]$ grep"# Mandatory : YES" dbca.rsp -A2# Mandatory : YES, if the value of registerWithDirService is TRUE#-----------------------------------------------------------------------------dirServiceUserName=--# Mandatory : YES, if the value of registerWithDirService is TRUE#-----------------------------------------------------------------------------dirServicePassword=--# Mandatory : YES, if the value of registerWithDirService is TRUE#-----------------------------------------------------------------------------walletPassword=[oracle@iZ2ze0t8khaprrpfvmevjiZ dbca]$ grep"# Mandatory : NO" dbca.rsp -A2# Mandatory : NO#-----------------------------------------------------------------------------characterSet=--# Mandatory : NO#-----------------------------------------------------------------------------listeners=--# Mandatory : NO#-----------------------------------------------------------------------------variablesFile=--# Mandatory : NO#-----------------------------------------------------------------------------variables=--# Mandatory : NO#-----------------------------------------------------------------------------initParams=--# Mandatory : NO#-----------------------------------------------------------------------------memoryPercentage=--# Mandatory : NO#-----------------------------------------------------------------------------databaseType=--# Mandatory : NO#-----------------------------------------------------------------------------automaticMemoryManagement=--# Mandatory : NO#-----------------------------------------------------------------------------totalMemory=
在选择某些项后会有一些强制的选项
[oracle@iZ2ze0t8khaprrpfvmevjiZ dbca]$ grep"# Mandatory : YES" dbca.rsp -A2# Mandatory : YES, if the value of registerWithDirService is TRUE#-----------------------------------------------------------------------------dirServiceUserName=--# Mandatory : YES, if the value of registerWithDirService is TRUE#-----------------------------------------------------------------------------dirServicePassword=--# Mandatory : YES, if the value of registerWithDirService is TRUE#-----------------------------------------------------------------------------walletPassword=
在创建数据库，大部分情况下要支持中文，不能采用默认的字符集，数据文件的存放目录通常也不会采用默认的设置，本次创建数据库用的响应文件是下面这个
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v21.0.0gdbName=orcl sid=orcl sysPassword=sys123 systemPassword=sys123 emExpressPort=5500templateName=/opt/oracle/product/21c/dbhome_1/assistants/dbca/templates/General_Purpose.dbccharacterSet=AL32UTF8
可以看到这个响应文件很简单，这个响应文件中，第一行是强制的，不能更改，templateName也是必须的，如果没有，创建数据库的过程中会报错退出。
3 创建数据库
有了响应文件，就可以创建数据库了
[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ dbca-silent-createDatabase-responseFile ./dbca.rsp [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. Prepare for db operation 10% complete Copying database files 40% complete Creating and starting Oracle instance 42% complete 46% complete 52% complete 56% complete 60% complete Completing Database Creation 66% complete Executing Post Configuration Actions 100% complete Database creation complete. For details check the logfiles at: /opt/oracle/cfgtoollogs/dbca/ORCL.
数据库创建成功了，创建过程中的日志存储在/opt/oracle/cfgtoollogs/dbca/ORCL目录中。
4 创建数据库后的工作
4.1 登录数据库查看可插拔数据库信息
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ WRITE YES
可以看到，dbca没有创建可插拔数据库，这个和响应文件的默认值有关
# Name : numberOfPDBs # Datatype : Number # Description : Specify the number of pdb to be created # Valid values:0 to 4094# Default value :0--numberOfPDBs=#-----------------------------------------------------------------------------# Name : pdbName # Datatype : String # Description : Specify the pdbname/pdbanme prefix if one or more pdb need to be created # Valid values: Check Oracle21c Administrator's Guide# Default value : None--pdbName=
从上面响应文件的部分内容可以看出，默认的pdb数量是0.也就是不创建可插拔数据库。
4.2 创建可插拔数据库
创建可插拔数据库之前，需要创建可插拔数据库数据文件的目录，
SQL>! mkdir /opt/oracle/oradata/ORCL/pdb1 SQL>! ls -ld /opt/oracle/oradata/ORCL/pdb1 drwxr-xr-x 2 oracle oinstall 6 Aug 1909:09/opt/oracle/oradata/ORCL/pdb1
使用下面命令创建可插拔数据库，这个命令应在根容器数据库中运行
SQL>create pluggable database pdb1 admin user pdb1_admin identified by oracle roles=(connect) file_name_convert=('/opt/oracle/oradata/ORCL/pdbseed','/opt/oracle/oradata/ORCL/pdb1'); Pluggable database created.
创建完毕后查看启动创建的pdb
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ WRITE YES 3 PDB1 MOUNTED SQL>alter pluggable database PDB1 open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ WRITE YES 3 PDB1 READ WRITE NO
切换当前容易的会话为pdb1,创建表空间和用户,给用户授权
SQL>alter session set container=PDB1; Session altered. --查看一下pdb1的数据文件SQL>select file_name from dba_data_files; FILE_NAME --------------------------------------------------------------------------------/opt/oracle/oradata/ORCL/pdb1/undotbs01.dbf/opt/oracle/oradata/ORCL/pdb1/sysaux01.dbf/opt/oracle/oradata/ORCL/pdb1/system01.dbf--创建一个表空间SQL>create tablespace tbs_test datafile '/opt/oracle/oradata/ORCL/pdb1/test01.dbf' size 100M; Tablespace created. --创建用户,默认表空间设置为刚在创建的表空间 SQL>create user test default tablespace tbs_test identified by"test123"; User created. --给用户授予资源和连接权限SQL> grant connect,resource to test; Grant succeeded.
4.3 登录可插拔数据库
查看监听信息
[oracle@iZ2ze0t8khaprrpfvmevjiZ orcl]$ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 19-AUG-202209:24:31 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=iZ2ze0t8khaprrpfvmevjiZ)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date 17-AUG-202213:40:13 Uptime 1 days 19 hr. 44 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/homes/OraDBHome21cEE/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/iZ2ze0t8khaprrpfvmevjiZ/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=iZ2ze0t8khaprrpfvmevjiZ)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "e68ecedbc9564717e053f40b14acf0d9" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully
Oracle已经创建了pdb1 的服务,用这个服务可以登录到pdb1可插拔数据库.
[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ sqlplus test/test123@iZ2ze0t8khaprrpfvmevjiZ/pdb1
