2.oracle 12c 创建-访问-关闭-删除PDB

时间：2022-03-13 22:44

1.创建PDB SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ora12c/system01.dbf /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf /u01/app/oracle/oradata/ora12c/sysaux01.dbf /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf /u01/app/oracle/oradata/ora12c/undotbs01.dbf /u01/app/oracle/oradata/ora12c/users01.dbf /u01/app/oracle/oradata/ora12c/EMP/system01.dbf /u01/app/oracle/oradata/ora12c/EMP/sysaux01.dbf /u01/app/oracle/oradata/ora12c/EMP/EMP_users01.dbf 9 rows selected. SQL> alter system set db_create_file_dest=‘/u01/app/oracle/oradata/ora12c/dsg‘; System altered. SQL> create pluggable database dsg admin user hxy identified by hxy roles=(connect); Pluggable database created. 要先设置db_create_file_dest，否则创建PDB的时候报下面的错误： SQL> create pluggable database dsg admin user hxy identified by hxy roles=(connect); create pluggable database dsg admin user hxy identified by hxy roles=(connect) * ERROR at line 1: ORA-65016: FILE_NAME_CONVERT must be specified 查看 SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 EMP READ WRITE 4 DSG MOUNTED 启动DSG SQL> alter pluggable database dsg open; Pluggable database altered. SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 EMP READ WRITE 4 DSG READ WRITE 2.（1）通过tnsname访问PDB数据库例如：查看当前数据库的PDB SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 EMP READ WRITE 查看tnsnames.ora EMP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = EMP) ) ) 访问： [oracle@localhost ~]$ sqlplus sys/oracle@emp as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sat Jan 18 14:26:04 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name con_id CON_NAME ------------------------------ EMP CON_ID ------------------------------ 3 （2）使用EZCONNECT方式连接到数据库 SQL> conn sys/oracle@//localhost/dsg as sysdba Connected. SQL> show con_name con_id CON_NAME ------------------------------ DSG CON_ID ------------------------------ 4 查看 SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/ora12c/undotbs01.dbf /u01/app/oracle/oradata/ora12c/dsg/ORA12C/F039E431F9CE7170E0430100007F6A0D/datafile/o1_mf_system_9fn895qv_.dbf /u01/app/oracle/oradata/ora12c/dsg/ORA12C/F039E431F9CE7170E0430100007F6A0D/datafile/o1_mf_sysaux_9fn89mpk_.dbf 有两个单独的数据文件system、sysaux和共享的undo表空间共享的全局数据文件 SQL> select name from v$tempfile; NAME ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/ora12c/dsg/ORA12C/F039E431F9CE7170E0430100007F6A0D/datafile/o1_mf_temp_9fn8bxy4_.dbf 有独立的临时文件 SQL> select name from v$controlfile; NAME ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/ora12c/control01.ctl /u01/app/oracle/fast_recovery_area/ora12c/control02.ctl 控制文件是共享的 （3）在DBA等高级权限的用户下，通过alter命令进行会话级别的容器切换，访问不同容器下的对象 SQL> conn / as sysdba Connected. SQL> alter session set container=dsg; Session altered. SQL> show con_id con_name CON_ID ------------------------------ 4 CON_NAME ------------------------------ DSG SQL> alter session set container=emp; Session altered. SQL> show con_id con_name CON_ID ------------------------------ 3 CON_NAME ------------------------------ EMP 五，关闭PDB （1）切换到PDB SQL> alter session set container=dsg; Session altered. SQL> show con_id con_name CON_ID ------------------------------ 4 CON_NAME ------------------------------ DSG SQL> shutdown immediate Pluggable Database closed. （2）在具有sys权限的用户执行 SQL> alter pluggable database emp close; Pluggable database altered. 六，删除PDB SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 EMP MOUNTED 4 DSG READ WRITE SQL> drop pluggable database dsg including datafiles; drop pluggable database dsg including datafiles * ERROR at line 1: ORA-65025: Pluggable database DSG is not closed on all instances. 必须让PDB处于关闭状态才能删除PDB SQL> alter pluggable database dsg close; Pluggable database altered. SQL> drop pluggable database dsg including datafiles; Pluggable database dropped. 查看产生的alert日志： gxlsystem.com,布布扣把相应的数据文件全部删除在OS中查看：