ITPub博客

首页 > 数据库 > Oracle > Oracle 创建PDB-from Scratch

Oracle 创建PDB-from Scratch

原创 Oracle 作者:chenoracle 时间:2020-03-30 09:07:13 0 删除 编辑

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",共同学习,共同成长!!!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2683257/,如需转载,请注明出处,否则将追究法律责任。

全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    537
  • 访问量
    982115