Centos7静默安装Oracle12c(12.2.0.1.0) & DBCA创建CDB&PDB

谁践踏了优雅 2022-12-23 00:55 325阅读 0赞

1.安装数据库软件

安装OS依赖包

Oracle 12.2.0.1.0的系统依赖包官方说明可以参考：  
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ladbi/supported-red-hat-enterprise-linux-7-distributions-for-x86-64.html\#GUID-2E11B561-6587-4789-A583-2E33D705E498

yum -y install binutils compat-libcap1 compat-libstdc++-33\*.i686 compat-libstdc++-33 gcc gcc-c++ glibc\*.i686 glibc glibc-devel\*.i686 glibc-devel ksh libaio\*.i686 libaio libaio-devel\*.i686 libaio-devel libgcc\*.i686 libgcc libstdc++\*.i686 libstdc++ libstdc++-devel\*.i686 libstdc++-devel libXi\*.i686 libXi libXtst\*.i686 libXtst make sysstat libxcb\*.i686 libxcb libX11\*.i686 libX11 libXau\*.i686 libXau libXrender\*.i686 libXrender libXrender-devel\*.i686 libXrender-devel net-tools nfs-utils smartmontools bc

rpm -qa binutils compat-libcap1 compat-libstdc++-33\*.i686 compat-libstdc++-33 gcc gcc-c++ glibc\*.i686 glibc glibc-devel\*.i686 glibc-devel ksh libaio\*.i686 libaio libaio-devel\*.i686 libaio-devel libgcc\*.i686 libgcc libstdc++\*.i686 libstdc++ libstdc++-devel\*.i686 libstdc++-devel libXi\*.i686 libXi libXtst\*.i686 libXtst make sysstat bc libxcb\*.i686 libxcb libX11\*.i686 libX11 libXau\*.i686 libXau libXrender\*.i686 libXrender libXrender-devel\*.i686 libXrender-devel net-tools nfs-utils smartmontools |wc -l

OS基础环境配置和准备操作（防火墙&Selinux、swap、内核优化、用户限制），可以参考：[https://blog.csdn.net/sunny05296/article/details/56840775][https_blog.csdn.net_sunny05296_article_details_56840775]

创建用户和组  
groupadd oinstall  
groupadd dba  
useradd -g oinstall -G dba -m oracle  
passwd oracle

创建Oracle数据库的安装目录(for静默安装)  
mkdir -p /opt/oracle  
mkdir -p /opt/oraInventory  
mkdir -p /opt/database  
mkdir -p /opt/oracle/product/12.2.0.1.0/dbhome\_1  
mkdir -p /opt/oracle/oradata  
mkdir -p /opt/oracle/flash\_recovery\_area

chown -R oracle:oinstall /opt/oracle  
chown -R oracle:oinstall /opt/oracle/oradata  
chown -R oracle:oinstall /opt/oraInventory  
chown -R oracle:oinstall /opt/database

chmod -R 775 /opt/oracle

Oracle用户的环境变量配置：  
\#oracle数据库安装目录  
export ORACLE\_BASE=/opt/oracle  
\#oracle数据库路径  
export ORACLE\_HOME=$ORACLE\_BASE/product/12.2.0.1.0/dbhome\_1  
\#oracle启动数据库实例名  
export ORACLE\_SID=orcl  
\#xterm窗口模式安装  
export ORACLE\_TERM=xterm  
\#添加系统环境变量  
export PATH=$ORACLE\_HOME/bin:/usr/sbin:$PATH  
\#添加系统环境变量  
export LD\_LIBRARY\_PATH=$ORACLE\_HOME/lib:/lib:/usr/lib  
\#防止安装过程出现乱码  
\#export LANG=en\_US.gbk  
export LANG=en\_US.UTF-8  
\#设置Oracle客户端字符集，必须与Oracle安装时设置的字符集保持一致，  
\#export NLS\_LANG=AMERICAN\_AMERICA.ZHS16GBK  
export NLS\_LANG=AMERICAN\_AMERICA.UTF8

解压  
su - oracle  
cd /opt  
unzip linuxx64\_12201\_database.zip  -d /opt/database

编辑静默安装响应文件  
su oracle  
cp -R /opt/database/database/response/  /home/oracle/  
vim /home/oracle/response/db\_install.rsp  \#Oracle 12.2.0.1.0和低版本的配置有些区别

oracle.install.option=INSTALL\_DB\_SWONLY  
\#ORACLE\_HOSTNAME=localhost \#没有该参数  
UNIX\_GROUP\_NAME=oinstall  
INVENTORY\_LOCATION=/opt/oraInventory  
\#SELECTED\_LANGUAGES=en,zh\_CN  \#没有该参数    
ORACLE\_HOME=/opt/oracle/product/12.2.0.1.0/dbhome\_1  
ORACLE\_BASE=/opt/oracle  
oracle.install.db.InstallEdition=EE  
oracle.install.db.OSDBA\_GROUP=dba       \#和低版本项目，参数名 DBA\_GROUP => OSDBA\_GROUP 多了一个OS，其他后面几个也是一样的  
oracle.install.db.OSOPER\_GROUP=dba  
DECLINE\_SECURITY\_UPDATES=true  
\#12c除了上面11g的参数外，还要额外设置下面参数，否则会报错  
oracle.install.db.OSBACKUPDBA\_GROUP=dba  
oracle.install.db.OSDGDBA\_GROUP=dba  
oracle.install.db.OSKMDBA\_GROUP=dba  
oracle.install.db.OSRACDBA\_GROUP=dba   \#12.2.0.1.0比12.1多出来的参数

静默安装  
cd /opt/database/database  
./runInstaller -silent -responseFile /home/oracle/response/db\_install.rsp -ignorePrereq

安装成功会提示root身份执行root.sh，按照执行即可

2.创建监听器

以静默方式配置监听  
重新使用oracle用户登录  
su - oracle  
cd /home/oracle/response/  
netca /silent /responseFile /home/oracle/response/netca.rsp   
注意此处，必须使用 /silent /responseFile 格式，而不是-silent -responseFile，因为是静默安装

查看监听状态  
lsnrctl  status

3.创建数据库

本文下面只介绍创建CDB+PDB的过程，如果不需要CDB，只需要创建FS的非CDB数据库，则可以直接参考：[https://blog.csdn.net/sunny05296/article/details/81589464?utm\_source=blogxgwz5][https_blog.csdn.net_sunny05296_article_details_81589464_utm_source_blogxgwz5]

dbca建库

建库时刻根据自己的实际情况选择对应的方式建库，一般有下面几种：  
1.创建存储为磁盘组的CDB单实例数据库  
2.创建rac类型的CDB数据库  
3.创建FS存储方式的单实例（不含PDB）数据库  
4.创建FS存储方式的单实例（含一个PDB）数据库  
5.静默方式创建一个非CDB数据库

由于本次我是想要创建CDB&PDB数据库，所以，这里和以往创建非CDB数据库的操作有些区别

接下来通过dbca创建容器数据库(CDB)

注意：  
1.当数据库版本小于 12.2.0.1 时，一般放在 example 表空间中。从 Oracle 12.2 开始不再提供该示例表空间了，即使在创建语句中加上“ sampleSchema true ”也依然没有 example 表空间，而会把相关数据放到 SYSAUX 表空间中。

2.在《Oracle数据库许可信息用户手册》中，详细说明了不同情况下PDB的个数限制：  
对于所有产品，如果您未获得Oracle Multitenant（多租户）许可，则容器数据库体系结构可在单租户模式下使用，即使用一个用户创建的PDB，一个用户创建的应用程序根，以及一个用户创建的代理PDB。  
EE：额外费用选项; 如果您获得Oracle Multitenant许可，那么您最多可以创建252个PDB。  
EE-ES：额外费用选项; 如果您获得Oracle Multitenant许可，那么您最多可以创建4096个PDB。  
DBCS EE-HP，DBCS EE-EP和ExaCS：包含选项; 您最多可以创建4096个PDB。

3. 关于多租户。多租户最重要的2个概念是容器数据库（CDB,multitenant container database）和可热插拔数据库（pluggable databases，PDB）。  
  CDB相当于一个大的容器，这个大的容易在物理上是一个整体，在这个大的容器中还有一些小的容器（PDB），一个CDB含有3种类型的容器：  
  root容器：包含元数据信息。  
  seed容器：它就是一个模板，新的PDB可以基于这个模板进行创建。  
  PDB：     也是最重要的，它包含用户数据，我们的创建的表就放在这里。而root和seed主要出于管理的目的。

我接下来创建FS存储方式的CDB数据库（默认包含2个PDB）数据库：注意红色部分尽量用小写，否则可能导致后面我遇到的问题

dbca -silent -createDatabase -templateName General\_Purpose.dbc -responseFile NO\_VALUE \\  
\-gdbname ORCL  \-sid ORCL \\  
\-createAsContainerDatabase TRUE \\  
\-numberOfPDBs 3 \\  
\-pdbName ORCLPDB \\  
\-pdbAdminPassword 1q2w3e \\  
\-sysPassword 1q2w3e -systemPassword 1q2w3e \\  
\-datafileDestination '/opt/oracle/oradata' \\  
\-recoveryAreaDestination '/opt/oracle/flash\_recovery\_area' \\  
\-redoLogFileSize 200 \\  
\-storageType FS \\  
\-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \\  
\-sampleSchema true \\  
\-totalMemory 1024 \\  
\-databaseType OLTP  \\  
\-emConfiguration NONE

说明：  
\-templateName : $ORACLE\_HOME/assistants/dbca/templates/General\_Purpose.dbc  
\-responseFile：如果配置了responseFile，则指定，如果不指定配置文件，也可以通过参数设置进行创建  
\-createAsContainerDatabase TRUE：指定创建容器数据库CDB  
\-numberOfPDBs 3：指定包含pdb的个数  
\-pdbName ORCLPDB：如果numberOfPDBs=1，则指定的就是pdbname，如果创建的个数大于1，则这里指定的就是pdbname的前缀，会自动根据个数进行区分  
\-redoLogFileSize 200 ：指定redoLogFile文件大小200M  
\-storageType FS：FS | ASM。存储系统类型，我用的是文件系统。如果用ASM，则填 ASM  
\-characterset：数据库字符集，默认是US7ASCII，国内存储中文一般设置为 AL32UTF8   
\-nationalCharacterSet ：UTF8 | AL16UTF16，国家语言字符集  
\-databaseType：MULTIPURPOSE | DATA\_WAREHOUSING | OLTP。used for memory distribution when memoryPercentage specified。  
\-totalMemory: total memory in MB to allocate to Oracle  
\-emConfiguration : CENTRAL|DBEXPRESS|BOTH|NONE，Enterprise Manager Configuration Type，默认为NONE

\[FATAL\] \[DBT-12507\] DBCA does not allow sample schemas to be configured in container database with more than one PDBs.  
   ACTION: Choose only one PDB to be created in the container database.

针对该错误信息，Oracle官方的解释：  
https://docs.oracle.com/en/database/oracle/oracle-database/19/errmg/DBT-00001.html\#GUID-05C1A5C2-217B-4489-AFA7-EBF3CCB2578A

DBT-12507: DBCA does not allow sample schemas to be configured in container database with more than one PDBs.  
Cause: n/a

Action: Choose only one PDB to be created in the container database. \*FQN: oracle.assistants.dbca.resource.DBCAErrorCode.UNABLE\_TO\_CREATE\_SAMPLE\_SCHEMA\_FOR\_MULTIPLE\_PDB\_ERR

大概意思是说container database中只能创建1个PDB，详细原因没有搞明白。  
那就先创建CDB（只包含1个PDB）：

注意：gdbname & sid 指定时， ORCL 尽量用小写的 orcl，避免文件系统区分大小写带来的问题。我后面就碰到问题了。

dbca -silent -createDatabase -templateName General\_Purpose.dbc -responseFile NO\_VALUE \\  
\-gdbname ORCL  \-sid ORCL \\  
\-createAsContainerDatabase TRUE \\  
\-numberOfPDBs 1 \\  
\-pdbName ORCLPDB \\  
\-pdbAdminPassword 1q2w3e \\  
\-sysPassword 1q2w3e -systemPassword 1q2w3e \\  
\-datafileDestination '/opt/oracle/oradata' \\  
\-recoveryAreaDestination '/opt/oracle/flash\_recovery\_area' \\  
\-redoLogFileSize 200 \\  
\-storageType FS \\  
\-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \\  
\-sampleSchema true \\  
\-totalMemory 2048 \\  
\-databaseType OLTP  \\  
\-emConfiguration NONE

创建成功  
......  
75% complete  
Executing Post Configuration Actions  
100% complete  
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCL/ORCL.log" for further details.  
\[oracle@localhost response\]$

建库后进行实例进程检查：

ps -ef | grep ora\_ | grep -v grep

查看监听状态  
lsnrctl  status

\[oracle@localhost response\]$ lsnrctl  status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2020 18:23:52

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))  
STATUS of the LISTENER  
\------------------------  
Alias                     LISTENER  
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production  
Start Date                21-NOV-2020 18:21:47  
Uptime                    0 days 0 hr. 2 min. 4 sec  
Trace Level               off  
Security                  ON: Local OS Authentication  
SNMP                      OFF  
Listener Parameter File   /opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/listener.ora  
Listener Log File         /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml  
Listening Endpoints Summary...  
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))  
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))  
Services Summary...  
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 "b49c3521c8e513cbe055000000000001" has 1 instance(s).  
  Instance "ORCL", status READY, has 1 handler(s) for this service...  
Service "orclpdb" has 1 instance(s).  
  Instance "ORCL", status READY, has 1 handler(s) for this service...  
The command completed successfully  
\[oracle@localhost response\]$

查看监听端口  
\[oracle@localhost response\]$   
\[oracle@localhost response\]$ netstat -an |grep 1521  
tcp6       0      0 :::1521                 :::\*                    LISTEN

登录查看实例状态：  
sqlplus / as sysdba  
select status from v$instance;

SQL> select status from v$instance;  
select status from v$instance  
\*  
ERROR at line 1:  
ORA-01034: ORACLE not available  
Process ID: 0  
Session ID: 0 Serial number: 0

数据库没有启动

SQL> startup  
ORA-01078: failure in processing system parameters  
LRM-00109: could not open parameter file '/opt/oracle/product/12.2.0.1.0/dbhome\_1/dbs/initorcl.ora'  
SQL>

\[oracle@localhost pfile\]$ ls -l $ORACLE\_HOME/network/admin/  
total 16  
\-rw-r--r-- 1 oracle oinstall  337 Nov 21 18:41 listener.ora  
drwxr-xr-x 2 oracle oinstall   64 Nov 21 18:42 samples  
\-rw-r--r-- 1 oracle oinstall 1441 Aug 28  2015 shrept.lst  
\-rw-r--r-- 1 oracle oinstall  195 Nov 21 18:41 sqlnet2011216PM4157.bak  
\-rw-r--r-- 1 oracle oinstall  195 Nov 21 18:41 sqlnet.ora  
\[oracle@localhost pfile\]$

发现没有 tnsnames.ora，只有一个listener.ora

关于这个错误：LRM-00109: could not open parameter file '/opt/oracle/product/12.2.0.1.0/dbhome\_1/dbs/initorcl.ora'  
我在网上搜了一下，说只需要把 /$ORACLE\_BASE/admin/$ORACLE\_SID/pfile/init.ora.10212020181036 文件拷贝到 /$ORACLE\_HOME/dbs 下面，  
然后重命名为 initorcl.ora 即可，然后重启数据库，就能解决了。

$ ls -l $ORACLE\_BASE/admin/$ORACLE\_SID/pfile/init.ora.\*  
ls: cannot access /opt/oracle/admin/orcl/pfile/init.ora.\*: No such file or directory

$ ls -l /$ORACLE\_HOME/dbs  
total 20  
\-rw-rw---- 1 oracle oinstall 1544 Nov 21 18:13 hc\_ORCL.dat  
\-rw-r--r-- 1 oracle oinstall 3079 May 15  2015 init.ora  
\-rw-r----- 1 oracle oinstall   24 Nov 21 18:06 lkORCL  
\-rw-r----- 1 oracle oinstall 3584 Nov 21 18:10 orapwORCL  
\-rw-r----- 1 oracle oinstall 3584 Nov 21 19:00 spfileORCL.ora

发现我配置的环境变量 ORACLE\_SID 是小写的 'orcl'，而我dbca建库的时候指定的SID为大写的'ORCL'  
修改环境变量ORACLE\_SID把小写改成大写：orcl 改为 ORCL。

执行拷贝和重命名：

cp /$ORACLE\_BASE/admin/$ORACLE\_SID/pfile/init.ora.10212020181036  /$ORACLE\_HOME/dbs

mv init.ora.10212020181036 initORCL.ora

说明：建库配置的时候尽量使用小写的sid，虽然Oracle数据库不区分大小写默认大写处理，但文件系统目录是区分大小写的，容易导致不一致的问题。

重启数据库和监听器  
sqlplus  / as sysdba  
shutdown immediate;

lsnrctl  stop  
lsnrctl  start  
sqlplus  / as sysdba  
startup

OK，问题解决，成功启动了

数据库启动成功后，再查看一下 $ORACLE\_HOME/network/admin/ ，发现 tnsnames.ora 没有自动生成，后面测试远程连接时无法连接，后面遇到问题，我还是手动创建配置了该问题。

详细原因，后面再研究。备注：首次安装的时候，没有自动生成这个文件，我第二套环境再次安装的时候发现是会自动生成的。

$ ls -l $ORACLE\_HOME/network/admin/  
total 20  
\-rw-r--r-- 1 oracle oinstall  337 Nov 21 19:41 listener.ora  
drwxr-xr-x 2 oracle oinstall   64 Nov 21 18:42 samples  
\-rw-r--r-- 1 oracle oinstall 1441 Aug 28  2015 shrept.lst  
\-rw-r--r-- 1 oracle oinstall  195 Nov 21 18:41 sqlnet2011216PM4157.bak  
\-rw-r--r-- 1 oracle oinstall  195 Nov 21 19:41 sqlnet2011217PM4123.bak  
\-rw-r--r-- 1 oracle oinstall  195 Nov 21 19:41 sqlnet.ora  
\[oracle@localhost dbs\]$

$ ls -l /$ORACLE\_HOME/dbs/initORCL.ora

sqlplus  / as sysdba

select status from v$instance;  
SQL> select status from v$instance;

STATUS  
\------------------------------------  
OPEN

col CON\_NAME for a20  
col NAME for a20  
col OPEN\_MODE for a20

show pdbs;  
show con\_name;  
select name,cdb,open\_mode,con\_id from v$database;

SQL> show pdbs;

CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 ORCLPDB                        MOUNTED  
SQL> show con\_name;

CON\_NAME  
\------------------------------  
CDB$ROOT  
SQL> select name,cdb,open\_mode,con\_id from v$database;

NAME                 CDB       OPEN\_MODE                CON\_ID  
\-------------------- --------- -------------------- ----------  
ORCL                 YES       READ WRITE                    0

查看CDB的数据文件存储路径  
SQL> select file\_name from dba\_data\_files;  
FILE\_NAME  
\--------------------------------------------------------------------------------  
/opt/oracle/oradata/ORCL/system01.dbf  
/opt/oracle/oradata/ORCL/sysaux01.dbf  
/opt/oracle/oradata/ORCL/undotbs01.dbf  
/opt/oracle/oradata/ORCL/users01.dbf

查看一下目录结构：  
\[oracle@localhost ORCL\]$ ls -l /opt/oracle/oradata/ORCL/  
total 2005916  
\-rw-r----- 1 oracle oinstall  18726912 Nov 21 20:17 control01.ctl  
drwxr-x--- 2 oracle oinstall       104 Nov 21 18:13 ORCLPDB  
drwxr-x--- 2 oracle oinstall       111 Nov 21 18:08 pdbseed  
\-rw-r----- 1 oracle oinstall 209715712 Nov 21 20:09 redo01.log  
\-rw-r----- 1 oracle oinstall 209715712 Nov 21 20:17 redo02.log  
\-rw-r----- 1 oracle oinstall 209715712 Nov 21 20:09 redo03.log  
\-rw-r----- 1 oracle oinstall 492838912 Nov 21 20:14 sysaux01.dbf  
\-rw-r----- 1 oracle oinstall 838868992 Nov 21 20:14 system01.dbf  
\-rw-r----- 1 oracle oinstall  34611200 Nov 21 18:09 temp01.dbf  
\-rw-r----- 1 oracle oinstall  68165632 Nov 21 20:14 undotbs01.dbf  
\-rw-r----- 1 oracle oinstall   5251072 Nov 21 20:09 users01.dbf  
\[oracle@localhost ORCL\]$

切换到ORCLPDB：  
SQL> alter session set container=ORCLPDB;

Session altered.

SQL> alter pluggable database ORCLPDB open;

Pluggable database altered.

SQL> show pdbs;

CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         3 ORCLPDB                        READ WRITE NO  
SQL>   
SQL> select file\_name from dba\_data\_files;

FILE\_NAME  
\--------------------------------------------------------------------------------  
/opt/oracle/oradata/ORCL/ORCLPDB/system01.dbf  
/opt/oracle/oradata/ORCL/ORCLPDB/sysaux01.dbf  
/opt/oracle/oradata/ORCL/ORCLPDB/undotbs01.dbf  
/opt/oracle/oradata/ORCL/ORCLPDB/users01.dbf

为了测试，我需要再创建1个pdb2

基于种子容器(seed)创建pdb2：

切换到CDB操作：

alter session set container=CDB$ROOT;

create pluggable database pdb2 admin user pdb2 identified by "1q2w3e"  
storage (maxsize 10G)  
default tablespace pdb  
datafile '/opt/oracle/oradata/ORCL/pdb2/pdb01.dbf' size 100M autoextend on  
path\_prefix = '/opt/oracle/oradata/ORCL/pdb2/'  
file\_name\_convert = ('/opt/oracle/oradata/ORCL/pdbseed','/opt/oracle/oradata/ORCL/pdb2/');

SQL> show pdbs;

CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 ORCLPDB                        READ WRITE NO  
         4 PDB2                           MOUNTED  
SQL>

SQL> alter session set container=PDB2;

Session altered.

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL>  
SQL> show pdbs;

CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         4 PDB2                           READ WRITE NO  
SQL>

接下来需要创用户、表空间和授权远程访问等操作。参考图形化安装的创建用户、表空间、授权等操作即可:  
https://blog.csdn.net/sunny05296/article/details/56840775

1.CDB中创建表空间、创建用户、授权（我这里就不单独创建表空间了，只简单创建用户和授权）

alter session set container=CDB$ROOT;

create user C\#\#TEST identified by "1q2w3e";

grant dba to C\#\#TEST;

2.ORCLPDB中创建表空间、创建用户、授权（我这里就不单独创建表空间了，只简单创建用户和授权）

alter session set container=ORCLPDB;

create user TEST identified by "1q2w3e";

grant dba to TEST;

3.PDB2中创建表空间、创建用户、授权（我这里就不单独创建表空间了，只简单创建用户和授权）

alter session set container=PDB2;

create user TEST identified by "1q2w3e";

grant dba to TEST;

远程连接测试，连接失败，本地连接测试，也无法连接：  
SQL> conn test/1q2w3e@orcl  
ERROR:  
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.  
SQL>

应该是TNS配置有问题。

tnsping 进行本次测试

tnsping  orcl  
tnsping  ORCLPDB  
tnsping  pdb2  
都测试不通

\[oracle@localhost ~\]$ tnsping  orcl  
    
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2020 21:03:29

Used parameter files:  
/opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name  
\[oracle@localhost ~\]$ tnsping  ORCL

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2020 21:03:37

Used parameter files:  
/opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name  
\[oracle@localhost ~\]$

看来还得创建修改配置：

touch /opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/tnsnames.ora  
vim  /opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/tnsnames.ora

修改后的内容如下：

\# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db\_1/network/admin/tnsnames.ora  
\# Generated by Oracle configuration tools.

ORCL =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = ORCL.localdomain)  
    )  
  )

ORCLPDB =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = ORCLPDB.localdomain)  
    )  
  )

PDB2 =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = PDB2.localdomain)  
    )  
  )

vim /opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/listener.ora  
增加 SID\_LIST\_LISTENER 配置，修改后的内容如下：

\[oracle@localhost admin\]$ cat listener.ora   
\# listener.ora Network Configuration File: /opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/listener.ora  
\# Generated by Oracle configuration tools.

LISTENER =  
  (DESCRIPTION\_LIST =  
    (DESCRIPTION =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
    )  
  )

SID\_LIST\_LISTENER =  
(SID\_LIST =  
 (SID\_DESC =  
 (GLOBAL\_DBNAME = ORCL) \#cdb db\_name  
 (SID\_NAME = ORCL)  
 )  
 (SID\_DESC =  
 (GLOBAL\_DBNAME = ORCLPDB) \#pdb db\_name  
 (SID\_NAME = ORCL)  
 )  
 (SID\_DESC =  
 (GLOBAL\_DBNAME = PDB2) \#pdb db\_name  
 (SID\_NAME = PDB2)  
 )  
)

重启监听后，再tnsping测试，全部OK：

\[oracle@localhost admin\]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2020 21:29:36

Used parameter files:  
/opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias  
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT\_DATA = (SERVER = DEDICATED) (SERVICE\_NAME = ORCL.localdomain)))  
OK (0 msec)  
\[oracle@localhost admin\]$   
\[oracle@localhost admin\]$ tnsping orclpdb

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2020 21:29:42

Used parameter files:  
/opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias  
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT\_DATA = (SERVER = DEDICATED) (SERVICE\_NAME = ORCLPDB.localdomain)))  
OK (0 msec)  
\[oracle@localhost admin\]$   
\[oracle@localhost admin\]$ tnsping pdb2

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2020 21:29:46

Used parameter files:  
/opt/oracle/product/12.2.0.1.0/dbhome\_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias  
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT\_DATA = (SERVER = DEDICATED) (SERVICE\_NAME = PDB2.localdomain)))  
OK (10 msec)  
\[oracle@localhost admin\]$

PDB2连接测试OK：  
IP:1521  
ServieNAme: PDB2  
TEST/1q2w3e

ORCLPDB连接测试OK:  
IP:1521  
ServieNAme: ORCLPDB  
TEST/1q2w3e

CDB连接测试OK：  
IP:1521  
ServieNAme: ORCL  
C\#\#TEST/1q2w3e

[https_blog.csdn.net_sunny05296_article_details_56840775]: https://blog.csdn.net/sunny05296/article/details/56840775
[https_blog.csdn.net_sunny05296_article_details_81589464_utm_source_blogxgwz5]: https://blog.csdn.net/sunny05296/article/details/81589464?utm_source=blogxgwz5