Oracle 创建PDB-from Scratch
一：Creating a PDB from Scratch
从PDB$SEED 创建新PDB
1 ：数据库版本
Oracle Database 19 c Enterprise Edition Release 19.0 .0.0.0 - Production
Version 19.3 .0.0.0
2 ：查看pdbs
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CJCPDB01 READ WRITE NO
3 ：查看数据文件
---CDB$ROOT
SQL > select file_name from dba_data_files order by 1 ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle12 / oradata / cjcdb01 / sysaux01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / system01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / undotbs01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / users01.dbf
---CJCPDB
SQL > alter session set container = cjcpdb01 ;
Session altered.
SQL > select file_name from dba_data_files order by 1 ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle12 / oradata / cjcdb01 / cjcpdb01 / cjctbs01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / cjcpdb01 / sysaux01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / cjcpdb01 / system01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / cjcpdb01 / undotbs01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / cjcpdb01 / users01.dbf
---PDB$SEED
SQL > alter session set container = pdb$seed ;
Session altered.
SQL > select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle12 / oradata / cjcdb01 / pdbseed / system01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / pdbseed / sysaux01.dbf
/ u01 / app / oracle12 / oradata / cjcdb01 / pdbseed / undotbs01.dbf
4 ：创建PDB
--- 创建目录
[oracle @ cjcos oradata]$ pwd
/ u01 / app / oracle12 / oradata
[oracle @ cjcos oradata]$ mkdir cjcpdb02
--- 创建PDB
SQL >
CREATE PLUGGABLE DATABASE cjcpdb02
ADMIN USER cjc IDENTIFIED BY oracle
FILE_NAME_CONVERT = ( '/u01/app/oracle12/oradata/cjcdb01/pdbseed/' ,
'/u01/app/oracle12/oradata/cjcpdb02/' );
Pluggable database created.
5 ：查看其它信息
--- 查看对应告警日志
---alert_cjcdb01.log
[oracle @ cjcos ~]$ cd / u01 / app / oracle19 / diag / rdbms / cjcdb01 / cjcdb01 / trace /
[oracle @ cjcos trace ]$ tail - f alert_cjcdb01.log
......
2020 - 03 - 30 T08 : 41 : 17.416476 + 08 : 00
CREATE PLUGGABLE DATABASE cjcpdb02
ADMIN USER cjc IDENTIFIED BY *
FILE_NAME_CONVERT = ( '/u01/app/oracle12/oradata/cjcdb01/pdbseed/' ,
'/u01/app/oracle12/oradata/cjcpdb02/' )
2020 - 03 - 30 T08 : 41 : 19.487247 + 08 : 00
PDB$SEED ( 2 ): AUDSYS.AUD$UNIFIED ( SQL_TEXT ) - CLOB populated
2020 - 03 - 30 T08 : 42 : 26.341020 + 08 : 00
CJCPDB02 ( 4 ): Endian type of dictionary set to little
2020 - 03 - 30 T08 : 42 : 28.661816 + 08 : 00
****************************************************************
Pluggable Database CJCPDB02 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 - 0 x00000000000000e1
****************************************************************
CJCPDB02 ( 4 ): Autotune of undo retention is turned on.
2020 - 03 - 30 T08 : 42 : 30.270217 + 08 : 00
CJCPDB02 ( 4 ): Undo initialization recovery : err : 0 start : 1161433871 end : 1161433873 diff : 2 ms ( 0.0 seconds )
CJCPDB02 ( 4 ): [ 7314 ] Successfully onlined Undo Tablespace 2 .
CJCPDB02 ( 4 ): Undo initialization online undo segments : err : 0 start : 1161433874 end : 1161433912 diff : 38 ms ( 0.0 seconds )
CJCPDB02 ( 4 ): Undo initialization finished serial : 0 start : 1161433871 end : 1161433915 diff : 44 ms ( 0.0 seconds )
CJCPDB02 ( 4 ): Database Characterset for CJCPDB02 is AL32UTF8
CJCPDB02 ( 4 ): JIT : pid 7314 requesting stop
CJCPDB02 ( 4 ): Buffer Cache flush started : 4
CJCPDB02 ( 4 ): Buffer Cache flush finished : 4
2020 - 03 - 30 T08 : 42 : 32.282539 + 08 : 00
Completed : CREATE PLUGGABLE DATABASE cjcpdb02
ADMIN USER cjc IDENTIFIED BY *
FILE_NAME_CONVERT = ( '/u01/app/oracle12/oradata/cjcdb01/pdbseed/' ,
'/u01/app/oracle12/oradata/cjcpdb02/' )
--- 查看新PDB对应的文件
[oracle @ cjcos cjcpdb02]$ pwd
/ u01 / app / oracle12 / oradata / cjcpdb02
[oracle @ cjcos cjcpdb02]$ ll - rth
total 951 M
- rw - r ----- 1 oracle oinstall 65M Mar 30 08:42 temp012020-01-19_13-04-59-427-PM.dbf
- rw - r ----- 1 oracle oinstall 231M Mar 30 08:42 undotbs01.dbf
- rw - r ----- 1 oracle oinstall 411M Mar 30 08:42 sysaux01.dbf
- rw - r ----- 1 oracle oinstall 311M Mar 30 08:42 system01.dbf
--- 查看cjcpdb02状态
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CJCPDB01 READ WRITE NO
4 CJCPDB02 MOUNTED
---open cjcpdb02
SQL > alter session set container = cjcpdb02 ;
Session altered.
SQL > startup
Pluggable Database opened.
---open cjcpdb02 对应的告警日志信息
---alert_cjcdb01.log
[oracle @ cjcos ~]$ cd / u01 / app / oracle19 / diag / rdbms / cjcdb01 / cjcdb01 / trace /
[oracle @ cjcos trace ]$ tail - f alert_cjcdb01.log
......
2020 - 03 - 30 T08 : 44 : 31.694096 + 08 : 00
CJCPDB02 ( 4 ): ALTER PLUGGABLE DATABASE OPEN
CJCPDB02 ( 4 ): Autotune of undo retention is turned on.
2020 - 03 - 30 T08 : 44 : 32.783077 + 08 : 00
CJCPDB02 ( 4 ): Endian type of dictionary set to little
CJCPDB02 ( 4 ): Undo initialization recovery : err : 0 start : 1161556516 end : 1161556522 diff : 6 ms ( 0.0 seconds )
CJCPDB02 ( 4 ): [ 7314 ] Successfully onlined Undo Tablespace 2 .
CJCPDB02 ( 4 ): Undo initialization online undo segments : err : 0 start : 1161556522 end : 1161556648 diff : 126 ms ( 0.1 seconds )
CJCPDB02 ( 4 ): Undo initialization finished serial : 0 start : 1161556516 end : 1161556656 diff : 140 ms ( 0.1 seconds )
CJCPDB02 ( 4 ): Deleting old file#5 from file$
CJCPDB02 ( 4 ): Deleting old file#6 from file$
CJCPDB02 ( 4 ): Deleting old file#8 from file$
CJCPDB02 ( 4 ): Adding new file#14 to file$ ( old file#5 ) . fopr - 1 , newblks - 39680 , oldblks - 19200
CJCPDB02 ( 4 ): Adding new file#15 to file$ ( old file#6 ) . fopr - 1 , newblks - 52480 , oldblks - 15360
CJCPDB02 ( 4 ): Adding new file#16 to file$ ( old file#8 ) . fopr - 1 , newblks - 29440 , oldblks - 12800
CJCPDB02 ( 4 ): Successfully created internal service CJCPDB02 at open
****************************************************************
Post plug operations are now complete.
Pluggable database CJCPDB02 with pdb id - 4 is now marked as NEW.
****************************************************************
2020 - 03 - 30 T08 : 44 : 33.776519 + 08 : 00
CJCPDB02 ( 4 ): Database Characterset for CJCPDB02 is AL32UTF8
2020 - 03 - 30 T08 : 44 : 39.211696 + 08 : 00
CJCPDB02 ( 4 ): Opening pdb with no Resource Manager plan active
CJCPDB02 ( 4 ): joxcsys_required_dirobj_exists : directory object exists with required path / u01 / app / oracle19 / product / 19.0 .0 / dbhome_1 / javavm / admin /, pid 7314 cid 4
Pluggable database CJCPDB02 opened read write
CJCPDB02 ( 4 ): Completed : ALTER PLUGGABLE DATABASE OPEN
CJCPDB02 ( 4 ): TABLE AUDSYS.AUD$UNIFIED : ADDED INTERVAL PARTITION SYS_P268 ( 69 ) VALUES LESS THAN ( TIMESTAMP ' 2020-04-01 00:00:00' )
--- 查看数据文件
SQL > select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/ u01 / app / oracle12 / oradata / cjcpdb02 / system01.dbf
/ u01 / app / oracle12 / oradata / cjcpdb02 / sysaux01.dbf
/ u01 / app / oracle12 / oradata / cjcpdb02 / undotbs01.dbf
--- 为cjcpdb02配置tnsname
[oracle @ cjcos admin ]$ pwd
/ u01 / app / oracle19 / product / 19.0 .0 / dbhome_1 / network / admin
[oracle @ cjcos admin ]$ vim tnsnames.ora
CJCPDB02 =
( DESCRIPTION =
( ADDRESS = ( PROTOCOL = TCP )( HOST = cjcos )( PORT = 1521 ))
( CONNECT_DATA =
( SERVER = DEDICATED )
( SERVICE_NAME = cjcpdb02 )
)
)
--- 连接cjcpdb02
SQL > conn cjc / oracle @ cjcpdb02
Connected.
SQL > show user con_name
USER is "CJC"
CON_NAME
------------------------------
CJCPDB02
6 ：创建PDB报错
---CDB$ROOT
SQL > conn / as sysdba
Connected.
SQL > CREATE PLUGGABLE DATABASE cjcpdb02 ADMIN USER sys IDENTIFIED BY oracle ;
CREATE PLUGGABLE DATABASE cjcpdb02 ADMIN USER sys IDENTIFIED BY oracle
*
ERROR at line 1 :
ORA - 65016 : FILE_NAME_CONVERT must be specified
--- 没有启用OMF
SQL > show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
--- 创建PDB时指定FILE_NAME_CONVERT即可
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
