Oracle Database 12C 学习之多租户(连载四)
使用克隆现存PDB的方式创建新的PDB:这里有两种情况,一种为使用本地PDB,另外一种为使用远程PDB。二者并无太大差异。只是第二种需要使用DBLINK而已。
克隆本地方式:
SYS@ora12g show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12C_PDB1 READ WRITE YES 4 ORACDB_PDB2 READ WRITE NO SYS@ora12g alter pluggable database ORA12C_PDB1 close; Pluggable database altered. SYS@ora12g alter pluggable database ORA12C_PDB1 open read only; Pluggable database altered. SYS@ora12g show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12C_PDB1 READ ONLY NO 4 ORACDB_PDB2 READ WRITE NO
--我们这里克隆ORA12C_PDB1来创建新的PDB,需要先将其置于read only模式。
SYS@ora12c alter session set container=ORA12C_PDB1; Session altered. SYS@ora12c select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/oracle/oradata/ora12c/ora12c_pdb1/system01.dbf /u01/oracle/oradata/ora12c/ora12c_pdb1/sysaux01.dbf /u01/oracle/oradata/ora12c/ora12c_pdb1/SAMPLE_SCHEMA_users01.dbf /u01/oracle/oradata/ora12c/ora12c_pdb1/example01.dbf SYS@ora12c create pluggable database ORA12C_PDB2 from ORA12c_PDB1 file_name_convert = (/u01/oracle/oradata/ora12c/ora12c_pdb1, /u01/oracle/oradata/ora12c/ora12c_pdb2) storage (MAXSIZE 4G MAX_SHARED_TEMP_SIZE 100M); 2 3 4 5 create pluggable database ORA12C_PDB2 ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database
--不能在PDB中复制PDB。
SYS@ora12c alter session set container=CDB$ROOT; Session altered. SYS@ora12c create pluggable database ORA12C_PDB2 from ORA12C_PDB1 file_name_convert = (/u01/oracle/oradata/ora12c/ora12c_pdb1, /u01/oracle/oradata/ora12c/ora12c_pdb2) storage (MAXSIZE 4G MAX_SHARED_TEMP_SIZE 100M); 2 3 4 5 Pluggable database created.
--目标PDB的数据文件存储目录也可以不用事先创建,oracle会自动创建。
--可以在创建PDB的同时指定该PDB的空间使用限额。
SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12C_PDB1 READ ONLY NO 4 ORACDB_PDB2 READ WRITE NO 5 ORA12C_PDB2 MOUNTED
使用远程PDB创建:
SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12C_PDB1 READ ONLY NO 4 ORACDB_PDB2 READ WRITE NO 5 ORA12C_PDB2 MOUNTED SYS@ora12c conn sys/oracle@ORACDB_PDB2 as sysdba Connected. SYS@ORACDB_PDB2 shutdown immediate; Pluggable Database closed. SYS@ORACDB_PDB2 startup; Pluggable Database opened. SYS@ORACDB_PDB2 show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 ORACDB_PDB2 READ WRITE NO SYS@ORACDB_PDB2 create user remote_pdb identified by remote; User created. SYS@ORACDB_PDB2 grant create pluggable database to remote_pdb; Grant succeeded.
--在源PDB中创建拥有create PDB权限的用户。
SYS@ORACDB_PDB2 conn / as sysdba Connected. SYS@ora12g create database link dbl_pdb connect to remote_pdb identified by remote using ORACDB_PDB2; Database link created.
--我们这里以ORACDB_PDB2作为远程数据库,也就是创建PDB的源PDB。
--利用前面创建的用户创建db link。
SYS@ora12c alter pluggable database ORACDB_PDB2 open read only force; Pluggable database altered. SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12C_PDB1 READ ONLY NO 4 ORACDB_PDB2 READ ONLY NO 5 ORA12C_PDB2 MOUNTED
--同样将其置于read only状态。
SYS@ora12c create pluggable database ORACDB_PDB_NEW from ORACDB_PDB2@dbl_pdb file_name_convert = (/u01/oracle/oradata/ora12c/cdb/pdb2, /u01/oracle/oradata/ora12c/cdb/pdb2_new); 2 3 4 Pluggable database created. SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORA12G_PDB1 READ ONLY NO 4 ORACDB_PDB2 READ ONLY NO 5 ORA12C_PDB2 MOUNTED 6 ORACDB_PDB_NEW MOUNTED
克隆非CDB的数据库来创建PDB。
这里有三种方法:
1,使用DBMS_PDB包生成源数据库的元数据,然后再利用create pluggable database语句创建;
2,使用数据泵(可传输表空间);
3,使用OGG。
使用数据泵方式,请参考官方文档Oracle® Database Utilities 12c Release 1 (12.1)
使用OGG方式,请参阅OGG相关文档。
我们这里测试下第一种方式。
1,先创建一个新的非CDB数据库。我们这里用DBCA创建。这步我就不多写了。各位一路next下去就好。记得别勾选create as a container database即可。
另外需要注意的是,数据库版本必须得是12c或者更高版本。
2,将该数据库以read only模式打开
[oracle@ora12 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 18 09:56:43 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@noncdb shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@noncdb startup mount; ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 599788272 bytes Database Buffers 230686720 bytes Redo Buffers 5455872 bytes Database mounted. SYS@noncdb alter database open read only; Database altered.
--这里,数据库需要开归档才能以read only模式打开,至于原因嘛,恩,各位小伙伴应该都能想的出来吧
3,利用DBMS_PDB包生成该数据库的pdb描述文件并关闭数据库。
SYS@noncdb begin dbms_pdb.describe(pdb_descr_file = /home/oracle/noncdb.xml); PL/SQL procedure successfully completed. SYS@noncdb SYS@noncdb shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
--这里生成的pdb描述文件为xml格式的,各位可以进去看一下它的内容。
--该文件中包含了数据库的版本信息,非默认值的初始化参数信息,表空间及数据文件信息,其他可选组件的版本信息,service信息,以及AWR中loadprofile的内容。
4,登陆CDB,检测要插入的pdb是否存在兼容性问题
SYS@ora12c set serveroutput on declare test_via boolean; begin test_via := dbms_pdb.check_plug_compatibility(pdb_descr_file = /home/oracle/noncdb.xml); if test_via then dbms_output.put_line(Yes); dbms_output.put_line(No); end if; PL/SQL procedure successfully completed.
--输出结果为yes,表示没有兼容性问题。
--如果为no,则需要去检查pdb_plug_in_violations视图。
5,创建PDB
SYS@ora12c create pluggable database PDB_NEW using /home/oracle/noncdb.xml file_name_convert = (/u01/oracle/oradata/noncdb, /u01/oracle/oradata/cdb/pdb_new); Pluggable database created. SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_NEW MOUNTED
6,执行脚本
SYS@ora12c conn sys/oracle@ora12:1521/PDB_NEW as sysdba; Connected. SYS@ora12:1521/PDB_NEW @?/rdbms/admin/noncdb_to_pdb.sql;
--该脚本的用处是:更新非CDB的数据库中的数据字典表,将其调整为PDB。
执行完成之后,该PDB就可以使用了。
需要注意的是:
在执行该脚本的时候,建议同时查看alert 日志,因为我的虚拟机只有2G内存,然后新创建的这个数据库noncdb我将其内存设置为了800M。所以在将该数据库创建为PDB时,oracle需要调整其参数设置。alert 日志中就看到如下类似的内容:
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 2097152 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query: select total_size,awr_flush_emergency_count from v$ash_info;
以及:
Default pga_aggregate_limit value is too high for the amount of physical memory in the system pga_aggregate_limit is 2048 MB limit based on physical memory and SGA usage is 1285 MB
因此,在虚拟机上创建新的PDB时,需要考虑内存以及其他比如说磁盘容量等方面的限制。
从CDB中拔出和插入PDB:
3 file_name_convert = (/u01/oracle/oradata/ora12c/pdbseed,/u01/oracle/oradata/ora12c/cdb/pdb1); Pluggable database created. SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED SYS@ora12c alter pluggable database PDB1 open; Pluggable database altered. SYS@ora12c show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SYS@ora12c alter pluggable database PDB1 close; Pluggable database altered.
--需要先关闭可插拔数据库,然后再unplug。
SYS@ora12c alter pluggable database PDB1 unplug into /home/oracle/pdb1.xml; Pluggable database altered.
--需要注意的是,这里只是unplug了,实际上该PDB还存在。如果想重新插入该PDB,则需要先删除。
SYS@ora12c drop pluggable database PDB1; Pluggable database dropped.
插入PDB的时候,首先需要做兼容性检查。也就是上篇文章中提到的DBMS_PDB.check_plug_compatibility函数。
如果没有问题,再插入。
插入语句跟创建新的PDB一样
SYS@ora12c create pluggable database PDB1 using /home/oracle/pdb1.xml file_name_convert = (/u01/oracle/oradata/pdb1, /u01/oracle/oradata/cdb/pdb_new);
使用DBCA创建PDB的方式,这里不再多说
本文来自云栖社区合作伙伴“DBGEEK”
Oracle 12c之后CDB上创建公共用户无法访问PDB 上v$session表内容 Oracle 12c CDB模式下,创建公共用户,登录到CDB,无法查看PDB下一些表的信息,如v$session,登录到具体PDB才能看到,这篇文章将介绍如何给公共用户授权解决这个问题。
3月19日直播【从12c到20c,Oracle多租户之10046跟踪PDB关库案例原理解析】 本次分享将针对DevOps代码质量审核、监控、开发环境测试、生产环境测试这四个方面存在的问题进行剖析并给出解决之道,希望能助您全面转型DevOps。
相关文章
- 【Oracle】oracle的LAG和LEAD分析函数
- PL/SQL Developer的安装以及与64位Oracle Database进行连接
- phalcon 连接多个数据库 phalcon multi-database
- Database 'xxxx' is being recovered. Waiting until recovery is finished.
- Oracle Database 11g Express Editon介绍及安装
- android.database.sqlite.SQLiteException: near "FROM"
- MySql: show databases/tables use database desc table
- 如何简化 Oracle Linux 上 Oracle Database 的安装
- 【Oracle 集群】Linux下Oracle RAC集群搭建之Oracle DataBase安装(八)
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之缓存融合技术和主要后台进程(四)
- 什么是SAP HANA Database Procedure(数据库过程)
- 【例题5-9 UVA - 1592】Database
- SAP HANA Database Explorer 里的 SQL 语句如何排错 trouble shoot
- Database之SQL:SQL在线编程、工作中常用SQL代码实践之查询-SQL问题分析解决思路、高级案例SQL语法拆解(单技巧各自用法详细分类/多技巧组合用法)、经典组合案例实战之详细攻略
- Database之SQL:SQL语句操作三类(数据定义语句DDL/数据操作语句DML/数据控制语句DCL/其他基本语句、流程控制语句、批处理语句)概念及其代码实现案例之详细攻略
- 【翻译自mos文章】在Oracle单机数据库中定义database service
- ORACLE database console无法登陆
- Go组件学习——database/sql数据库连接池你用对了吗
- oracle-database预安装包离线下载 —— 筑梦之路
- SQL Server Database 维护计划创建一个完整的备份策略
- Oracle的报错:ORA-00845MEMORY_TARGET not supported on this system和ORA-01102: cannot mount database in EX
- How to Setup Archive Log Destination Directory in Oracle Database
- How to gathering Database Statistics in Oracle
- MySQL删除数据库(DROP DATABASE语句)