目录
创建和卸载pdb
从seed创建一个新pdb
复制system,undo,sysaux三个表空间,默认在新创建的pdb中会存在sys和system两个通用用户。
SQL> alter session set container=cdb$root;---首先切换到root容器下
Session altered.
SQL> select name from v$datafile;--查看所有的数据文件,数据库默认是在CDB下面以pdb的名字创建一个文件
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/system01.dbf
/u01/app/oracle/oradata/CDB/sysaux01.dbf
/u01/app/oracle/oradata/CDB/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/CDB/users01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf
11 rows selected.
SQL> create pluggable database pdb2 admin user admin1 identified by oracle
2 FILE_NAME_CONVERT=('pdbseed','pdb2');--创建一个pdb,数据库会默认在CDB下面去找到pdbseed这个路径创建创建一个以pdb2命名的数据库
Pluggable database created.
SQL> show pdbs----新创建的pdb是mounted状态,新创建的pdb会自动注册一个服务,可以查看监听的状态 lsnrctl status
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL> alter pluggable database pdb2 open;---新创建一个pdb后需要以读写的方式打开,使数据字典加载到数据库
Pluggable database altered.
select con_id,pdb_name,dbid,status from cdb_pdbs;---查看所有pdb的信息
CON_ID PDB_NAME DBID STATUS
---------- --------------- ---------- ----------
3 PDB1 2920121943 NORMAL
2 PDB$SEED 1091113511 NORMAL
4 PDB2 3746068775 NORMAL
SQL> select * from v$tablespace;---查看所有容器的表空间信息,因为这个视图的来源是内存和控制文件,所以pdb在mounted状态下是可以看到的
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
1 SYSAUX YES NO YES 1
0 SYSTEM YES NO YES 1
2 UNDOTBS1 YES NO YES 1
4 USERS YES NO YES 1
3 TEMP NO NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 2
2 UNDOTBS1 YES NO YES 2
3 TEMP NO NO YES 2
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
5 USERS YES NO YES 3
0 SYSTEM YES NO YES 4
1 SYSAUX YES NO YES 4
2 UNDOTBS1 YES NO YES 4
3 TEMP NO NO YES 4
18 rows selected.
SQL> select status,con_id,tablespace_name from cdb_tablespaces;------只会显示已经打开的数据库的表空间信息
STATUS CON_ID TABLESPACE_NAME
--------- ---------- ------------------------------
ONLINE 1 SYSTEM
ONLINE 1 SYSAUX
ONLINE 1 UNDOTBS1
ONLINE 1 TEMP
ONLINE 1 USERS
ONLINE 3 SYSTEM
ONLINE 3 SYSAUX
ONLINE 3 UNDOTBS1
ONLINE 3 TEMP
ONLINE 3 USERS
ONLINE 4 SYSTEM
STATUS CON_ID TABLESPACE_NAME
--------- ---------- ------------------------------
ONLINE 4 SYSAUX
ONLINE 4 UNDOTBS1
ONLINE 4 TEMP
14 rows selected.
-----以下查看新建pdb的表空间和临时表空间情况,可以看出是继承了pdbseed的临时表空间
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> create pluggable database pdb2 admin user admin1 identified by oracle
2 FILE_NAME_CONVERT=('pdbseed','pdb2');
Pluggable database created.
SQL> alter session set container=pdb2;
SQL> startup;
Pluggable Database opened.
SQL> select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS LOGGING EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM 8192 ONLINE LOGGING LOCAL MANUAL
SYSAUX 8192 ONLINE LOGGING LOCAL AUTO
UNDOTBS1 8192 ONLINE LOGGING LOCAL MANUAL
TEMP 8192 ONLINE NOLOGGING LOCAL MANUAL
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/u01/app/oracle/oradata/CDB/pdb2/temp012024-01-10_16-24-28-422-PM.dbf
以下可以看出新建pdb的temp文件和pdbseed的temp文件是一样的
[root@19c ~]# su - oracle
Last login: Wed Jan 10 17:39:21 CST 2024 on pts/1
[oracle@19c ~]$ cd /u01/app/oracle/oradata
[oracle@19c oradata]$ ll -ls
total 0
0 drwxr-x---. 5 oracle oinstall 239 Jan 10 17:54 CDB
[oracle@19c oradata]$ cd CDB
[oracle@19c CDB]$ ll -ls
total 2439084
18288 -rw-r-----. 1 oracle oinstall 18726912 Jan 10 18:02 control01.ctl
18288 -rw-r-----. 1 oracle oinstall 18726912 Jan 10 18:02 control02.ctl
0 drwxr-x---. 2 oracle oinstall 104 Jan 10 16:35 pdb1
0 drwxr-x---. 2 oracle oinstall 111 Jan 10 17:54 pdb2
0 drwxr-x---. 2 oracle oinstall 111 Jan 10 16:24 pdbseed
204804 -rw-r-----. 1 oracle oinstall 209715712 Jan 10 18:01 redo01.log
204804 -rw-r-----. 1 oracle oinstall 209715712 Jan 10 16:34 redo02.log
204804 -rw-r-----. 1 oracle oinstall 209715712 Jan 10 16:34 redo03.log
532488 -rw-r-----. 1 oracle oinstall 545267712 Jan 10 18:00 sysaux01.dbf
911368 -rw-r-----. 1 oracle oinstall 933240832 Jan 10 17:59 system01.dbf
1184 -rw-r-----. 1 oracle oinstall 33562624 Jan 10 16:34 temp01.dbf
337928 -rw-r-----. 1 oracle oinstall 346038272 Jan 10 18:00 undotbs01.dbf
5128 -rw-r-----. 1 oracle oinstall 5251072 Jan 10 16:34 users01.dbf
[oracle@19c CDB]$ cd pdb2
[oracle@19c pdb2]$ ll -ls
total 716880
337928 -rw-r-----. 1 oracle oinstall 346038272 Jan 10 18:00 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Jan 10 18:00 system01.dbf
56 -rw-r-----. 1 oracle oinstall 37756928 Jan 10 17:55 temp012024-01-10_16-24-28-422-PM.dbf
102408 -rw-r-----. 1 oracle oinstall 104865792 Jan 10 18:00 undotbs01.dbf
[oracle@19c pdb2]$ cd -
/u01/app/oracle/oradata/CDB
[oracle@19c CDB]$ cd pdbseed
[oracle@19c pdbseed]$ ll -ls
total 716880
337928 -rw-r-----. 1 oracle oinstall 346038272 Jan 10 16:34 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Jan 10 16:34 system01.dbf
56 -rw-r-----. 1 oracle oinstall 37756928 Jan 10 16:27 temp012024-01-10_16-24-28-422-PM.dbf
102408 -rw-r-----. 1 oracle oinstall 104865792 Jan 10 16:34 undotbs01.dbf
连接到pdb
[oracle@19c ~]$ sqlplus sys/oracle@localhost:1521/pdb2 as sysdba----连接到pdb2
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 25 15:58:55 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
卸载pdb
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE NO
SQL> alter pluggable database pdb2 close;-----先关闭pdb
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> drop pluggable database pdb2 including datafiles;-----同时卸载pdb中所有的数据文件
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
从一个pdb中克隆出另一个pdb
首先要记住,创建pdb都是在CDB$ROOT容器下创建的
1.包含原pdb的所有表空间
2.通用用户:system,sys
3.所有的目录
4.会自动创建一个service
第一步:首先查看 被克隆pdb的文件路径,我们是要克隆pdb2
SQL> alter session set container=cdb$root;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/system01.dbf
/u01/app/oracle/oradata/CDB/sysaux01.dbf
/u01/app/oracle/oradata/CDB/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/CDB/users01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf
第二步:为新创建的pdb3创建一个目录
[oracle@oracle19c ~]$ mkdir -p /u01/app/oracle/oradata/CDB/pdb2
[oracle@oracle19c ~]$ cd /u01/app/oracle/oradata/CDB
total 2418484
18288 -rw-r----- 1 oracle oinstall 18726912 Dec 25 17:17 control01.ctl
18288 -rw-r----- 1 oracle oinstall 18726912 Dec 25 17:17 control02.ctl
0 drwxr-x--- 2 oracle oinstall 104 Nov 27 12:50 pdb1
0 drwxr-x--- 2 oracle oinstall 6 Dec 25 16:36 pdb2
0 drwxr-x--- 2 oracle oinstall 111 Nov 27 12:28 pdbseed
204804 -rw-r----- 1 oracle oinstall 209715712 Dec 25 17:16 redo01.log
204804 -rw-r----- 1 oracle oinstall 209715712 Dec 25 14:54 redo02.log
204804 -rw-r----- 1 oracle oinstall 209715712 Dec 25 14:54 redo03.log
563208 -rw-r----- 1 oracle oinstall 576724992 Dec 25 17:16 sysaux01.dbf
911368 -rw-r----- 1 oracle oinstall 933240832 Dec 25 17:14 system01.dbf
1064 -rw-r----- 1 oracle oinstall 33562624 Nov 27 12:47 temp01.dbf
286728 -rw-r----- 1 oracle oinstall 293609472 Dec 25 17:14 undotbs01.dbf
5128 -rw-r----- 1 oracle oinstall 5251072 Dec 25 14:54 users01.dbf
第三步:执行创建命令
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> create pluggable database pdb2 from pdb1
2 CREATE_FILE_DEST='/u01/app/oracle/oradata/CDB/pdb2';
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 PDB2 MOUNTED
克隆Non-CDB 插入到CDB当中
注意:pdb不能变成non-cdb
1.包含原pdb的所有表空间
2.通用用户:system,sys
3.所有的目录
4.会自动创建一个service
1.将Non-CDB设置为只读状态
[oracle@oracle19c CDB]$ ps -ef|grep ora_ 可以通过这个查看操作系统进程的方式去查看数据库的名字
2.在CDB中创建一个连接Non-CDB 的dblink
SQL> CREATE DATABASE LINK link_orcl CONNECT TO system identified by oracle using 'orcl';-----这个要首先在tns里面配一个别名连到orcl
另外這裏説一下創建dblink的語法:
1.首先在要創建DBlink的數據庫給用戶賦予創建dblink的權限
grant create public database link, create database link to myAccount;---給myAccount用戶賦予創建dblink的權限
2.然後創建dblink
CREATE PUBLIC DATABASE LINK <DBlink名称> CONNECT TO <用户名> IDENTIFIED BY <密码> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <目标数据库IP>)(PORT = <目标数据库端口>))) (CONNECT_DATA = (SERVICE_NAME = <目标数据库实例名称>)))';
3.如:A库需要访问B库的表数据,需要在A库建立连接到B库的dblink,在A账户下执行(推荐):
create database link LIS_LINK01 connect to bUser identified by bPasword using '10.22.xx.xx:1521/orcl';
bUser:B库的数据库账户
bPassword:B库的数据密码
10.22.xx.xx:B库的ip地址
3.使用命令克隆Non-CDB
SQL> CREATE pluggable database orclpdb from non$link_orcl
2 CREATE _FILE_DEST='/u01/app/oracle/oradata/CDB/orclpdb;-------这个目录也是要用上面的方式事先创建
在这里我们执行名命令的时候会报错权限不够,那是因为system用户默认没有创建pluggable database 的权限。
解决办法:重新启动Non-CDB数据库,将创建 pluggable database 的权限赋予system用户,再将数据库设置为只读状态。
4.执行一个脚本,将克隆过来的Non-CDB变成pdb
SQL> alter session set container=orclpdb;------切换到新创建的pdb
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;----------执行脚本
5.打开新创建的pdb
使用DBCA克隆一个远程的pdb
1.要求被克隆的pdb是open,read write,被克隆后也会自动open
2.要求必须在远程cdb上创建通用用户,同时授予create session,create pluggable database的权限
3.要求两个CDB必须是归档模式
SQL> create user c##remote_clone_user identified by remote_clone_user;
SQL> grant create session,create pluggable database to c##remote_clone_user;
以下命令成功执行远程克隆pdb,路径自动放在CDB目录下创建的子目录
dbca静默安装要在操作系统下指定实例名:
export ORACLE_SID=CDB1 然后再执行命令
dbca - silent \-------采用dbca静默安装的方式
-createPluggableDdatabase \----创建一个插拔式数据库
-pdbNames sales \------指定本地pdb的名字叫sales
-sourceDB CDB1 \------当前db的实例名
-createFromRemotePDB \
-RemotePDBName sales \-----远程被克隆pdb的名字sales
-RemoteDBConnString pg-xc3:1521/sales \-----远程被克隆的pdb的端口号和主机名
-RemoteDBSYSDBUserName sys \-----远程管理员
-RemoteDBSYSDBUserPassword oracle \-----远程管理员密码
-dbLinkUsername c##remote_clone_user \---dbac静默方式克隆远程的pdb会自动创建需要的dblink,克隆完后会自动把dblink删除
-dbLinkUserPassword remote_clone_user \
从一个CDB中拔出pdb插入到同一个CDB
要求被拔出的pdb处于mounted状态
如果我要将拔出来的pdb1插入到其他的CDB,那我们需要检查一个数据库字符集的兼容性 ,需要执行一个包。以下的cuugpdb1 就是pdb1,我没有改名字。如果是YES,就说明字符集是兼容的。如果不兼容可以查看报错信息
select message from PDB_PLUG_IN_VIOLATIONS;----查看报错信息
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
5 PDB2 MOUNTED
SQL> alter pluggable database pdb2 close;
alter pluggable database pdb2 close
*
ERROR at line 1:
ORA-65020: pluggable database PDB2 already closed
SQL> alter pluggable database pdb2 unplug into'/u01/app/oracle/testtest/test1.xml';------拔出pdb1将pdb1中的元数据放入指定目录中
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
5 PDB2 MOUNTED
SQL> drop pluggable database pdb2 keep datafiles;
Pluggable database dropped.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> create pluggable database pdb2 using '/u01/app/oracle/testtest/test1.xml'nocopy;-----nocopy参数表示在创建过程中不复制任何数据文件
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
从一个CDB中拔出pdb插入到另一个CDB
如果插入到另一个CDB就需要拔出原pdb的所有数据文件和元数据。
SQL> alter pluggable database pdb2 close;----首先关闭pdb
Pluggable database altered.
SQL> alter pluggable database pdb2 unplug into '/u01/app/oracle/testtest/test1.pdb';------拔出所有的数据文件和元数据
Pluggable database altered.
SQL> drop pluggable database pdb2 including datafiles;
Pluggable database dropped.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> create pluggable database pdb2 as clone using '/u01/app/oracle/testtest/test1.pdb'
2 file_name_convert=('/u01/app/oracle/testtest/','u01/app/oracle/oradata/CDB/pdb2');
Pluggable database created.
管理pdb
只读方式打开pdb
SQL> alter pluggable database pdb1 open read only;--------以只读的方式打开pdb
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
修改pdb的global_name
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open restricted;---------以限制会话的方式打开pdb
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE YES
SQL> alter session set container=pdb1;--------切换到pdb容器下
Session altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE YES
SQL> alter pluggable database rename global_name to test1;
Pluggable database altered.
SQL> alter system register;
System altered.
修改之后对外提供的服务器名字也会改变
修改pdb中的参数
注意:多租户数据库的参数值依然是12c以前的spfile,pdb运行时可以有自己的运行特性,但是不会存放在spfile中,因为spfile时给整个CDB用的,如果pdb中有private local parameter,那么这些私有参数会存在 CDB中的PDB_SPFILE$字典表中以PDB_UID区别,当pdb被删除,oracle会自动清除这个数据字典的值,如果有新的pdb插入,oracle会再这个数据字典中新增相应的私有参数。如果pdb没有修改参数,默认用的时cdb$root的参数。
另外还有需要注意的是,我们执行alter system checkpoint命令产生检查点时作用于整个CDB的;
alter system switch logfile 切换日志命令必须要再cdb$root下面执行。
spfile只记录了cdb和pdb的通用参数,也就是只记录了cdb$root 的参数,我们可以通过v$parameter 视图去看哪些参数是pdb可以修改的。
SQL> select name,VALUE,ISPDB_MODIFIABLE from v$parameter order by ISPDB_MODIFIABLE;-------false为不允许修改的参数
NAME VALUE ISPDB
------------------------------ ------------------------------ -----
lock_name_space FALSE
processes 300 FALSE
license_max_sessions 0 FALSE
license_sessions_warning 0 FALSE
standby_db_preserve_states NONE FALSE
instance_abort_delay_time 0 FALSE
instance_groups FALSE
event FALSE
sga_max_size 838860800 FALSE
use_large_pages FALSE FALSE
pre_page_sga TRUE FALSE
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE YES
SQL> alter session set container=pdb1;
Session altered.
SQL> alter system set ddl_lock_timeout=10;-----在pdb中修改参数
System altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select NAME,PDB_UID,VALUE$ from pdb_spfile$;------以下可以看到被修改过的参数
NAME PDB_UID VALUE$
-------------------- ---------- --------------------
ddl_lock_timeout ########## 10
SQL> select name,VALUE,ISPDB_MODIFIABLE from v$parameter where name='ddl_lock_timeout';--------也可以这样看
NAME VALUE ISPDB
-------------------- ------------------------------ -----
ddl_lock_timeout 0 TRUE