oracle 多租户 mysql_Oracle 12c 多租户常用管理命令

--进入cdb

sqlplus /nolog

conn sys/sys as dba

--查看数据库基本信息

select name,cdb from v$database;

--查看容器的相关信息

select con_id,name from v$containers;

--查看数据文件的路径

select con_id,file_name from cdb_data_files order by 1;

--显示当前连接容器信息

show con_id con_name user

--显示所有pdb

show pdbs

--start和stop根容器

--启动数据

--CDB

sqlplus / as sysdba

startup

--PDB(所有PDB)

alter pluggable database all open;

--启动pdb1库

alter pluggable database pdb1 open;

--关闭数据库

--CDB

shutdown immediate

--pdb1

alter pluggable database pdb1 close;

alter pluggable database pdb1 close immediate;

--关闭所有pdb

alter pluggable database all close immediate;

--创建公用用户(sys和system在可插拔中就是自动创建的公用用户)

sqlplus / as sysdba

create user c#dba identified by alex;

--创建公用角色

sqlplus / as sysdba

create role c##dbaprivs container=all;

grant dba to c##dbaprivs container=all;

--公用角色给公用用户

grant c##dbaprivs to c#dba container=all;

--切换容器

alter session set container=ehrpdb;

--切换回根容器

alter session set container=cdb$root;

--2创建可插拔数据库

--2.1克隆种子容器

create pluggable database cpcdb

admin user cpcadm identified by  cpcadm

file_name_convert=('/oracle/app/oracle/oradata/edw/pdbseed','/oradata/edw/cpcdb/datafile/');

--2.2直接创建数据库

CREATE PLUGGABLE DATABASE hrdb

ADMIN USER hrdba IDENTIFIED BY hrdba

STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 2G)

DEFAULT TABLESPACE hrdb DATAFILE '/oradata/EDW/hrdb/datafile/hrdb001.dbf' SIZE 512M AUTOEXTEND on next 8M maxsize 10G;

CREATE PLUGGABLE DATABASE cpcdb

ADMIN USER cpcdba IDENTIFIED BY cpcdba

STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 2G)

DEFAULT TABLESPACE tbscpc DATAFILE '/oradata/EDW/cpcdb/datafile/tbscpc001.dbf' SIZE 512M AUTOEXTEND on next 8M maxsize 10G;

--2.3创建数据案例

create pluggable database cpcdb

admin user cpcadm identified by  cpcadm

file_name_convert=('/oracle/app/oracle/oradata/edw/pdbseed','/oradata/edw/cpcdb/datafile/');

create pluggable database ncdb

admin user ncadm identified by  ncadm

file_name_convert=('/oradata/edw/pdbseed','/oradata/edw/ncdb/datafile/');

CREATE PLUGGABLE DATABASE ncdb

ADMIN USER ncdba IDENTIFIED BY ncdba

STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 2G);

--访问ncdb的插拔数据库

sqlplus sys/ncdba@oraerp:1521/ncdb as sysdba

--关闭PDB数据库

alter pluggable database ncdb close immediate;

alter pluggable database hrdb close immediate;

alter pluggable database cpcdb close immediate;

alter pluggable database edwpdb close immediate;

--删除PDB数据库

DROP PLUGGABLE DATABASE ncdb INCLUDING DATAFILES;

DROP PLUGGABLE DATABASE hrdb INCLUDING DATAFILES;

DROP PLUGGABLE DATABASE cpcdb INCLUDING DATAFILES;

DROP PLUGGABLE DATABASE edwpdb INCLUDING DATAFILES;

--创建pdb案例

--1、cpcdb

create pluggable database cpcdb

admin user cpcadm identified by  cpcadm

file_name_convert=('/oradata/edw/pdbseed/','/oradata/edw/cpcdb/');

--2、ncdb

create pluggable database ncdb

admin user ncadm identified by  ncadm

file_name_convert=('/oradata/edw/pdbseed/','/oradata/edw/ncdb/');

--3、hrdb

create pluggable database hrdb

admin user hradm identified by hradm

file_name_convert=('/oradata/edw/pdbseed/','/oradata/edw/hrdb/');

alter pluggable database all open;

--登录cpcdb

$sqlplus sys/Oracleedw13@192.168.2.218:1531/cpcdb as sysdba

$sqlplus sys/OraEdw!23@192.168.1.105:1533/ncdb as sysdba --含有！需要单独输入密码