CENTOS7/RHEL7.4安装oracle12c数据库,配置PDB监听

# vi /etc/pam.d/login
session required /lib/security/pam_limits.so
session required pam_limits.so

创建oralce安装目录以及数据存放目录.

# mkdir -p /data/app/oracle

# chown -R oracle:oinstall /data/app/oracle

# chmod -R 775 /data/app/oracle

由于oracle 12c使用了CDB-PDB架构，类似于docker，在container-db内可以加载多个pluggable-db，因此安装后需要额外配置才能使用。

以下方法是使用静态注册方法，不建议使用。

一、修改listener.ora , tnsnames.ora

[oracle@centos7 admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/oracle/app/product/12.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12g)
      (ORACLE_HOME = /data/oracle/app/product/12.2/db_1)
      (SID_NAME = orcl12g)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12gpdb)
      (ORACLE_HOME = /data/oracle/app/product/12.2/db_1)
      (SID_NAME = orcl12g)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos7)(PORT = 1521))
  )

ADR_BASE_LISTENER = /data/oracle/app

###listener.ora###
 LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos7)(PORT = 1521))
  )

#sid list列举cdb和所有pdb的数据库名，所有sid与oracle环境变量保持一致#

SID_LIST_LISTENER =(
 SID_LIST = 
  (SID_DESC = (GLOBAL_DBNAME = orcl12g) #cdb db_name 
    (SID_NAME = orcl12g)      # SID 信息与pdb信息一致 ) 
  (SID_DESC = (GLOBAL_DBNAME = orcl12gpdb) #pdb db_name 
    (SID_NAME = orcl12g)      # SID 信息与pdb信息一致                 
 )
)
###listener.ora###


##tnsnames.ora###

#cdb
orcl =
(DESCRIPTION = 
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl) #cdb的db_name
 )
)
#pdb
pdborcl =
(DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = pdborcl) #pdb的db_name
 )
)
##tnsnames.ora###

2.12C 不推荐静态注册，建议动态注册

.动态注册方法信息如下：

To resolve this, make sure that you do the following:
1. Backup then edit the listener.ora file to REMOVE the "static" SID_DESC sections for these PDBs
2. Make sure the Database knows where to register by explicitly setting the LOCAL_LISTENER to 
  any of the end points (addresses) that this listener is listening on.
 Within the PDB issue the following statement:

alter session set container=PDB1;

alter system set listener_networks='(( NAME=listener)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.com)(PORT=1521)))))' scope=spfile;

3. Restart the Listener and db.

Always use Dynamic Services for connections. Do not use SID which is very old and obsolete with respect to how connections should be established or serviced.

This would not apply to certain components such as DG or RMAN which sometimes requires connections via a "Static Service" due to the Database not being in an OPEN (and therefore not "ready") status. See the following: Understanding Static Service Registration

oracle自动动态注册监听方法：

[oracle@centos7 admin]$ sqlplus / as sysdba

SQL>
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centos7)(PORT=1521)))';

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (DESCRIPTION=(ADDRESS=(PROTOCO
                                                 L=TCP)(HOST=centos7)(PORT=1521
                                                 )))
remote_listener                      string

添加PDB注册信息。

SQL> alter session set container=orcl12gpdb;

SQL> alter system set listener_networks='(( NAME=listener)(LOCAL_LISTENER=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=centos7)(PORT=1521)))))' scope=spfile;

清除掉静态注册的信息，内容如下：

[oracle@centos7 admin]$ cat /data/oracle/app/product/12.2/db_1/network/admin/listener.ora

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = centos7)(PORT = 1521))
  )

ADR_BASE_LISTENER = /data/oracle/app

[oracle@centos7 admin]$

-》 静态注册方法信息如下：
•Use of external procedure calls
•Use of Oracle Heterogeneous Services
•Use of Oracle Data Guard
•Remote database startup from a tool other than Oracle Enterprise Manager Cloud Control
•Connections to Oracle databases earlier than Oracle8i release 2 (8.1)

1、配置监听

首先要明确，所有的PDB都使用1个监听，配置多个实际上启动时也只有第1个有意义。

LISTENER=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))
  )


接下来使用SID_LIST_LISTENER来进行静态注册服务。
SID_LIST_LISTENER=
(SID_LIST=
  (SID_DESC =
  (GLOBAL_DBNAME = ora12c) #该服务是我配置的cdb信息
  (SID_NAME = ora12c)
  )

  (SID_DESC =
  (GLOBAL_DBNAME = pdborcl)#该服务是我配置的pdb信息
  (SID_NAME = ora12c)
  )
)

ADR_BASE_LISTENER= /opt/oracle

2、tnsnames.ora配置
观察发现，在tnsnames中配置pdb跟CDB，即原来11g的配置完全一样。这里SERVICE_NAME = pdborcl使用得是PDB的名字，可以在v$pdbs中查看。

ORA12C=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora12c)
    )
  )

pdborcl=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =ora12c_A)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdborcl)
    )
  )


3、为了保险，检查下sqlnet.ora
NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
这一句是为了保证优先使用TNSNAMES解析。