首页 > 其他分享 >创建PDB的两种操作

创建PDB的两种操作

时间:2023-06-19 11:04:21浏览次数:43  
标签:TEST1 opt BISALCDB 两种 创建 oradata oracle PDB


Oracle 19c的安装写了一些文章,

《非Oracle Linux下Oracle 19c CDB数据库安装

Oracle 19c的examples静默安装

Oracle Cloud创建19c数据库

非Oracle Linux下安装Oracle 19c

Oracle 19c之RPM安装

之前介绍的PDB都是通过配置文件在数据库初始化的时候就装上了,如果要在一个Oracle 19c已有的CDB上创建PDB,主要有两种方式。

方式1,直接创建PDB

直接从PDB$SEED这个种子PDB,创建新的PDB,

SQL> create pluggable database test1 admin user bisal identified by bisal
  2  storage (maxsize 2G) 
  3  default tablespace users
  4  path_prefix='/opt/oracle/oradata/BISALCDB/TEST1/'
  5  file_name_convert=('/opt/oracle/oradata/BISALCDB/pdbseed','/opt/oracle/oradata/BISALCDB/TEST1');

trace显示执行过程,

create pluggable database test1 admin user bisal identified by *
storage (maxsize 2G)
default tablespace users
path_prefix='/opt/oracle/oradata/BISALCDB/TEST1/'
file_name_convert=('/opt/oracle/oradata/BISALCDB/pdbseed','/opt/oracle/oradata/BISALCDB/TEST1')
2021-10-31T15:41:46.183962+08:00
PDB$SEED(2): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2021-10-31T15:42:23.580231+08:00
TEST1(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database TEST1 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
2021-10-31T15:42:28.108404+08:00
TEST1(4):Autotune of undo retention is turned on. 
2021-10-31T15:42:29.304164+08:00
TEST1(4):Undo initialization recovery: err:0 start: 1926300190 end: 1926300215 diff: 25 ms (0.0 seconds)
TEST1(4):[51756] Successfully onlined Undo Tablespace 2.
TEST1(4):Undo initialization online undo segments: err:0 start: 1926300215 end: 1926300477 diff: 262 ms (0.3 seconds)
TEST1(4):Undo initialization finished serial:0 start:1926300190 end:1926300520 diff:330 ms (0.3 seconds)
TEST1(4):Database Characterset for TEST1 is AL32UTF8
TEST1(4):JIT: pid 51756 requesting stop
2021-10-31T15:42:30.389724+08:00
TEST1(4):Buffer Cache flush started: 4
TEST1(4):Buffer Cache flush finished: 4
Completed: create pluggable database test1 admin user bisal identified by *
storage (maxsize 2G)
default tablespace users
path_prefix='/opt/oracle/oradata/BISALCDB/TEST1/'
file_name_convert=('/opt/oracle/oradata/BISALCDB/pdbseed','/opt/oracle/oradata/BISALCDB/TEST1')

TEST1的初始状态是MOUNTED,

SQL> show pdbs;
 CON_ID   CON_NAME   OPEN MODE    RESTRICTED
-------- ---------- ------------ ------------
      2   PDB$SEED   READ ONLY     NO
      3   BISALPDB1  MOUNTED
      4   TEST1      MOUNTED
      5   BISALPDB2  READ WRITE    NO

打开这个PDB,就可以用了,

SQL> alter pluggable database test1 open;
Pluggable database altered.


SQL> show pdbs;
 CON_ID   CON_NAME   OPEN MODE    RESTRICTED
-------- ---------- ------------ ------------
      2   PDB$SEED   READ ONLY     NO
      3   BISALPDB1  MOUNTED
      4   TEST1      READ WRITE    NO
      5   BISALPDB2  READ WRITE    NO

方式2,克隆创建PDB

如果非CDB,复制一个库,我们能选择逻辑导出导入、克隆数据库、dblink等,但是在CDB,直接支持克隆PDB,某些场景下,更实用,更简单,

创建PDB的两种操作_linux

Oracle支持克隆本地PDB、克隆远程CDB的PDB,支持克隆非CDB等多种形式,我们尝试下克隆一个本地PDB,

创建PDB的两种操作_数据库_02

Oracle 12.1的克隆数据库,要求源库是Read Only,这个不太方便,影响正常业务,12.2开始引入了Hot Clone技术,支持在线复制,不影响源库使用的情况下,实现克隆数据库,

创建PDB的两种操作_linux_03

官方对Hot Clone的介绍,

When the CDB is in ARCHIVELOG mode and local undo mode, the source PDB can be open in read/write mode and operational during the cloning process. This technique is known as hot cloning.

为了验证Hot,我们在源库中,执行如下SQL,模拟实时事务,

create table test(id number, dt timestamp);


begin
  for i in 1 .. 500 loop
    insert into test values(i, systimestamp);
  commit;
  dbms_lock.sleep(2);
  end loop;
end;
/

我们选择BISALPDB1作为复制的源库,Read Write状态下克隆,

SQL> show pdbs;
 CON_ID     CON_NAME    OPEN MODE    RESTRICTED
---------- ----------- ------------ ------------
     2      PDB$SEED    READ ONLY        NO
     3      BISALPDB1   READ WRITE       NO
     5      BISALPDB2   READ WRITE       N

找到BISALPDB1的文件信息,

SQL> select con_id, name from v$datafile where con_id=3;
  CON_ID    NAME
---------- -------------------------------------------------------
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/system01.dbf
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/sysaux01.dbf
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/undotbs01.dbf
     3      /opt/oracle/oradata/BISALCDB/BISALPDB1/users01.dbf

创建目标库的数据文件路径,

mkdir -p /opt/oracle/oradata/BISALCDB/TEST1

执行复制操作,

SQL> alter system set db_create_file_dest='/opt/oracle/oradata/BISALCDB/TEST1';
System altered.


SQL> create pluggable database test1 from bisalpdb1;
Pluggable database created.

从trace日志,能看到Oracle做了Incomplete Recovery,

create pluggable database test1 from bisalpdb1
2021-10-31T18:37:36.221609+08:00
BISALPDB1(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2021-10-31T18:38:08.719482+08:00
TEST1(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database TEST1 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
2021-10-31T18:38:09.796408+08:00
TEST1(4):Media Recovery Start
2021-10-31T18:38:09.827728+08:00
TEST1(4):Serial Media Recovery started
TEST1(4):max_pdb is 5
2021-10-31T18:38:10.401253+08:00
TEST1(4):Recovery of Online Redo Log: Thread 1 Group 3 Seq 79 Reading mem 0
TEST1(4):  Mem# 0: /oradata/REDO/redo03.dbf
2021-10-31T18:38:10.881738+08:00
TEST1(4):Incomplete Recovery applied until change 7357265 time 10/31/2021 18:38:05
2021-10-31T18:38:10.898436+08:00
TEST1(4):Media Recovery Complete (BISALCDB)
TEST1(4):Autotune of undo retention is turned on. 
2021-10-31T18:38:13.065567+08:00
TEST1(4):Undo initialization recovery: err:0 start: 1936843789 end: 1936843976 diff: 187 ms (0.2 seconds)
TEST1(4):[48600] Successfully onlined Undo Tablespace 2.
TEST1(4):Undo initialization online undo segments: err:0 start: 1936843976 end: 1936843988 diff: 12 ms (0.0 seconds)
TEST1(4):Undo initialization finished serial:0 start:1936843789 end:1936844007 diff:218 ms (0.2 seconds)
TEST1(4):Database Characterset for TEST1 is AL32UTF8
TEST1(4):JIT: pid 48600 requesting stop
TEST1(4):Buffer Cache flush started: 4
TEST1(4):Buffer Cache flush finished: 4
Completed: create pluggable database test1 from bisalpdb1

打开这个PDB,能看到源库已经创建的对象了,

SQL> alter pluggable database test1 open;
Pluggable database altered.


SQL> alter session set current_schema=test;
Session altered.


SQL> select count(*) from test;
  COUNT(*)
----------
  12


标签:TEST1,opt,BISALCDB,两种,创建,oradata,oracle,PDB
From: https://blog.51cto.com/u_13950417/6511671

相关文章

  • std::thread 一:创建线程的三种方式
    前言:#include<thread>thread.join()//阻塞thread.detach()//非阻塞thread.joinable()//bool,判断线程是否支持join或者detach 正文:创建线程有三种方式,分别是:使用函数来创建线程、使用自定义的类来创建线程、使用lambda函数来创建线程 一、使用函数来......
  • Java多线程-Lesson01-线程的创建
    线程创建的三种方式继承Thread类步骤:继承Thread类重写run()方法调用start()开启线程重写run()方法:@Overridepublicvoidrun(){for(inti=0;i<200;i++){System.out.println("run():"+i);}} run()方法里面就是我们多......
  • 关于lvm磁盘管理中lv-logical-volume逻辑卷的创建-新建
    在lvm-(LogicalVolumeManager)-逻辑卷管理的简写),在磁盘管理体系中,有一个lv的概念lv即是logicalvolume逻辑卷的缩写,今天笔者主要讲解一下关于lv的创建过程 在创建之前,需要有几个知识点需要明白一下1、lv是从vg中进行创建的,一个操作系统中,是可以有多个vg的2、因此在创建......
  • Python编程和数据科学中的人工智能:如何创建复杂的智能系统并提高模型性能
    目录1.引言2.技术原理及概念3.实现步骤与流程4.应用示例与代码实现讲解标题:《Python编程和数据科学中的人工智能:如何创建复杂的智能系统并提高模型性能》1.引言人工智能(AI)是一个广泛的领域,涵盖了许多不同的技术和应用。在Python编程和数据科学中,人工智能是一个非常重要......
  • 机器学习中的神经网络:如何创建强大的模型并解决复杂的问题
    目录文章标题:《17."机器学习中的神经网络:如何创建强大的模型并解决复杂的问题"》文章背景介绍:随着人工智能技术的快速发展,神经网络已经成为机器学习领域中最受欢迎的模型之一。神经网络是一种基于深度学习的技术,能够通过多层神经元来学习和提取特征,从而实现复杂的分类、回归和......
  • C++创建对象的4种方式对比
    三种方式分别为等号,圆括号,大括号,最后一种是等号和大括号一起使用等号和圆括号是以前就存在的,大括号是新的语法,其具有2个优点:防止变窄转换:大括号不支持变窄转换,等号和圆括号为了向下兼容支持变窄转换免疫C++最令人头疼的解析:C++规定任何可以被解析为一个声明的东西必......
  • Oracel 创建表空间、创建用户并设置密码及制定表空间、分配权限、创建角色、角色授权
    select*fromdba_data_files;select*fromdba_users;--删除用户--DROPUSERSYNC_PLUS_1cascade;--删除表空间--droptablespaceSYNC_PLUS_ORCLPDB1includingcontentsanddatafiles;/*createtablespacesync_plus_1datafile'F:\DB_FILES\ORAC......
  • java 创建聚合项目的2种方式及 部署Nacos
    创建聚合项目的2种方式byIDEA20231.创建module,erp-parent2.创建子module,设置每个module的pom.xml属性pom及父模块;3.配置erp-parent模块里的pom.xml1.创建module,erp-parent2.创建子module--mavenarchet部署nacosype(archetype:quickstart(java工程),webapp(web工程),设置每......
  • java创建List时候 初始化赋值
    List<String>list=newArrayList<>(){//这个大括号就相当于我们new接口{//这个大括号就是构造代码块会在构造函数前调用System.out.println("我是构造代码块");this.add("one");//this可以省略这里加上只是为了让......
  • 编译原理实验二 使用lex创建词法分析器
    实验二直接上代码吧,其实我也不怎么知道哪里是致命的,课上听老师的课后,将代码写上,但是运行在环境里面还是不行,把注释去掉就可以了,不知道会不会是注释的问题%{#include<stdio.h>#include"define.h"intlineno=1; TokenTypeid2keyword(constchar*token);%}newline ......