首页 > 代码库 > 2.oracle 12c 创建-访问-关闭-删除PDB

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

2024-07-17 06:30:58   218人阅读

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 =http://www.mamicode.com/
      (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日志：
把相应的数据文件全部删除
在OS中查看：


联系
我们