Oracle12c创建可插入式数据库pdb和连接pdb并创建用户心得

最新推荐文章于 2024-05-16 15:33:38 发布

顺随心动

最新推荐文章于 2024-05-16 15:33:38 发布

阅读量4.5k

点赞数 4

分类专栏： oracle笔记文章标签： oracle

本文链接：https://blog.csdn.net/qq_30794175/article/details/124365178

版权

oracle笔记专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文详细介绍了在Oracle12c中如何创建、启动和管理可插拔数据库（PDB），包括创建PDB、设置容器、分配权限，以及解决登录问题。此外，还讲解了如何通过tnsnames.ora配置连接，并创建及授权新用户，最后展示了查询不同PDB中的用户方法。

摘要由CSDN通过智能技术生成

Oracle12c创建可插入式数据库pdb和连接pdb并创建用户心得

创建可插接式数据库

启动oracle数据库

#使用sqlplu / as sysdba登录数据库主容器
[oracle@localhost ~]$ sqlplus / as sysdba;

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 23 01:05:14 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

#启动数据库主容器实例
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size		    		8794848 bytes
Variable Size		 	 1090522400 bytes
Database Buffers	 	 1040187392 bytes
Redo Buffers		    	7979008 bytes
Database mounted.
Database opened.
#查看数据库pdb(可插拔数据库)
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED

默认安装oracle的时候，会设置为一个PDBORCL，如果不使用安装时的pdborcl这个，你可以用一下语句创建一个可插入数据库

create pluggable database reportdb admin user report identified by pwd_report
 roles=(connect)
 file_name_convert=('/oracle/oradata/orcl/pdbseed','/oracle/oradata/orcl/reportpdb');

其中reportdb是我创建的可插接式数据库，report是创建的用户，pwd_report是密码。
file_name_convert换成相应目录,注意/oracle/oradata/orcl为具体数据库实例orcl的所在文件夹

创建完成后，启动可插接式数据库，

SQL> alter session set container=reportdb;
SQL> alter pluggable database reportdb open;
SQL> grant dba to report;

接下来关键来了，在/oracle/product/12.2.0/db_1/network/admin目录下找到tnsnames.ora，没有可以下samples目录下复制一个示例tnsnames.ora到该目录下

cd /oracle/product/12.2.0/db_1/network/admin
vim tnsnames.ora

添加一下内容

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


REPORTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = REPORTDB)
    )
  )

REPORTDB就是你创建的可插入数据库,SERVICE_NAME的内容就是服务名

保存好后，只需重启监听程序可以，如果无效，重启oracle服务

[oracle@db12c ~]$ lsnrctl reload

[oracle@db12c ~]$ lsnrctl stop

[oracle@db12c ~]$ lsnrctl start

如果连接数据库（pdb可插拔数据库）出现Oracle 12c 报ORA-01017: invalid username/password; login denied，有可能是pdb未启动，需要按照以下步骤排查并启动

#使用sqlplu / as sysdba登录数据库主容器
[oracle@localhost ~]$ sqlplus / as sysdba;

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 23 01:05:14 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
#关闭数据库主容器实例
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

#启动数据库主容器实例
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size		    		8794848 bytes
Variable Size		 	 1090522400 bytes
Database Buffers	 	 1040187392 bytes
Redo Buffers		    	7979008 bytes
Database mounted.
Database opened.
#查看数据库pdb(可插拔数据库)
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 REPORTDB                       MOUNTED

SQL> alter session set container=REPORTDB;

Session altered.
#启动pdb，(或使用： alter pluggable database REPORTDB open;)
SQL> startup
Pluggable Database opened.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 REPORTDB                       READ WRITE NO

SQL> 
————————————————
OPEN MODE的状态为READ WRITE 代表成功

使用sqlpuls或者navicat验证REPORTDB的report账号是否能登录
- 使用sqlplus指定pdb用户登录
  语法：sqlplus username/password@IP:port/pdb_name
  若密码中含有“@”，“$”中等特殊字符，则将用密码需加上双引号:sqlplus ‘username/“password”’
- 使用connect指定pdb用户登录
- navicat

创建新的PDB用户并授予基本权限的操作过程

进入具体的pdb(可插拔数据库)

[oracle@db12c ~]$ sqlplus / as sysdba;

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 23 15:21:53 2022

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 pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ODSPDB                         MOUNTED
         4 REPORTDB                       READ WRITE NO
#进入REPORTDB
SQL> alter session set container=REPORTDB;

Session altered.

#查看当前模式
SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
REPORTDB

创建test用户

#创建用户：test 密码：pwd_123456
SQL> CREATE USER test IDENTIFIED BY pwd_123456;

User created.

#授予dba权限
SQL> grant dba to test;

Grant succeeded.

用户登录

SQL> conn test/123456;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

不能使用connect直接登录在pdb中创建的普通用户，普通用户登录需指定pdb

- 使用sqlplus指定pdb用户登录
  语法：sqlplus username/password@IP:port/pdb_name
  若密码中含有“@”，“$”中等特殊字符，则将用密码需加上双引号:sqlplus ‘username/“password”’

1. [oracle@localhost ~]$ sqlplus test/pwd_123456@192.168.1.157:1521/REPORTPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 23 01:29:24 2022

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 user;
USER is "TEST"

- 使用connect指定pdb用户登录

#使用sysdba用户登录数据库
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 4 13:38:39 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
#使用test用户登录REPORTPDB可插拔数据库
SQL> conn test/pwd_123456@REPORTPDB
Connected.

Oracle12c查询不同pdb有哪些新建用户

描述
先查询系统有哪些pdb，然后根据pdb的con_id查询特定pdb有哪些新建用户
操作

SQL> show pdbs;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBSKY                         READ WRITE NO
         4 PDBCLOUD                       READ WRITE NO
         5 REPORTPDB                      READ WRITE NO
SQL> select a.name,b.username, b.password,b.created
    from v$pdbs a
    left join cdb_users b on a.con_id = b.con_id
    order by 1;
 
NAME                 USERNAME             PASSWORD             CREATED
-------------------- -------------------- -------------------- --------------
PDBCLOUD             PDBCLOUD                                  03-6月 -19
PDBCLOUD             CLOUD                                     04-6月 -19
PDBSKY               PDBSKY                                    03-6月 -19
PDBSKY               PSKY                                      04-6月 -19
REPORTPDB            TEST                                      04-6月 -19