Oracle 建立PDB-from Scratch
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Scratch（五）：Scratch小遊戲之超級瑪麗遊戲
- Scratch第二講：scratch軟體介面介紹
- 從頭基於空映象scratch建立一個新的Docker映象Docker
- 【神器】Scratch JS外掛JS
- scratch要如何教學？
- Scratch詢問模組怎麼用? Scratch實現對話效果的方法教程
- Linux From Scratch（介紹）Linux
- Scratch3 二次開發系列
- 如何利用Scratch製作出好玩的遊戲？遊戲
- 哪個Scratch少兒程式設計好用？程式設計
- JavaScript最新教程：js-stack-from-scratchJavaScriptJS
- scratch程式設計第三講：對話程式設計
- 探索將scratch3移植到樹莓派樹莓派
- 魔法師槍戰～Scratch小朋友程式設計～程式設計
- Oracle OCP(20)：建立表Oracle
- Oracle 建立序列語句Oracle
- Oracle 建立使用者Oracle
- Oracle建立dblink MySQLOracleMySql
- oracle建立使用者Oracle
- [Oracle] 指令碼建立DBOracle指令碼
- oracle 手動建立ASMOracleASM
- oracle 建立物化檢視Oracle
- Oracle 建立分割槽表Oracle
- oracle建立表空間Oracle
- Oracle建立和使用序列Oracle
- Oracle 物化檢視建立Oracle
- Oracle JOB 建立例項Oracle
- oracle學習（建立表）Oracle
- 利用RMAN 建立 oracle dataguardOracle
- 圖形化語言Scratch之父談程式設計程式設計
- Scratch3之AI整合 - flappy bird AI版本AIAPP
- ORACLE建立資料庫時無法建立目錄Oracle資料庫
- Oracle 建立PDB-本地克隆Oracle
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- oracle 序列的建立與使用Oracle
- Oracle如何建立B樹索引Oracle索引
- 建立oracle 密碼檔案Oracle密碼
- 手動建立Oracle例項Oracle