noncdb轉pdb
參考文件blog.csdn.net/su377486/article/details/103104705
學習並記錄一下
背景：
目標端 19c cdb pdb 10.*.98.103
源端 19c noncdb 10.*.98.150
方式：
1、可以使用拔出插入的方式將一個非CDB庫轉換為PDB
2、使用dblink克隆的方式.
################################################################################
第一種方式：將非cdb作為pdb插入cdb
1、檢查資料庫相容性
在noncdb建立xml檔案
SQL> begin 2 dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/noncdb.xml'); 3 end; 4 / PL/SQL procedure successfully completed. SQL>
將xml檔案傳到cdb同樣的目錄，在cdb中進行相容性檢查。
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROD_PDB1 MOUNTED SQL> set serveroutput on SQL> declare 2 compatible constant varchar2(3):= 3 case DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/home/oracle/noncdb.xml',pdb_name=>'noncdb') 4 WHEN TRUE THEN 'YES' 5 ELSE 'NO' 6 end; 7 begin dbms_output.put_line(compatible); 8 end; 9 / YES PL/SQL procedure successfully completed. SQL>
並在cdb中檢視錶PDB_PLUG_IN_VIOLATIONS檢視檢查結果，
這裡的NONCDB需要大寫。
SQL> select message,action from pdb_plug_in_violations where name='NONCDB'; MESSAGE ACTION ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------ PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. Run noncdb_to_pdb.sql. CDB parameter processes mismatch: Previous 320 Current 1000 Please check the parameter in the current CDB CDB parameter sga_target mismatch: Previous 2352M Current 3568M Please check the parameter in the current CDB CDB parameter pga_aggregate_target mismatch: Previous 782M Current 1185M Please check the parameter in the current CDB Service name or network name of service prodXDB in the PDB is invalid or conflicts with an existing Drop the service and recreate it with an appropriate name. service name or network name in the CDB. Service name or network name of service prod in the PDB is invalid or conflicts with an existing ser Drop the service and recreate it with an appropriate name. vice name or network name in the CDB. 6 rows selected.
如上：
a、需要執行nocdb_to_pdb.sql
b、因為我的非cdb的sid也是prod，所以插入的時候注意一下修改。
檢查ok之後就是正式的操作了
非CDB開啟到read only模式， 再生成xml檔案。原來的xml兩臺機器上的記得刪掉。
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup open read only; ORACLE instance started. Total System Global Area 2466249672 bytes Fixed Size 8899528 bytes Variable Size 536870912 bytes Database Buffers 1912602624 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> begin 2 dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/noncdb.xml'); 3 end; 4 / PL/SQL procedure successfully completed. SQL>
計劃好複製過去cdb之後 此非cdb的路徑以及pdb的名稱
源目錄（非cdb）/u01/app/oracle/oradata/PROD/
目標目錄（CDB）/u01/app/oracle/oradata/PROD/noncdb/
關閉源非cdb資料庫，複製資料檔案和xml檔案到目標端
[oracle@dbserver PROD]$ scp * 10.8.98.103:/u01/app/oracle/oradata/PROD/noncdb/ oracle@10.8.98.103's password: control01.ctl 100% 10MB 60.2MB/s 00:00 control02.ctl 100% 10MB 52.0MB/s 00:00 noncdb01.dbf 100% 100MB 76.7MB/s 00:01 redo01.log 100% 200MB 100.0MB/s 00:02 redo02.log 100% 200MB 100.0MB/s 00:02 redo03.log 100% 200MB 100.0MB/s 00:02 sysaux01.dbf 100% 530MB 88.3MB/s 00:06 system01.dbf 100% 890MB 89.0MB/s 00:10 temp01.dbf 100% 32MB 69.4MB/s 00:00 undotbs01.dbf 100% 340MB 85.0MB/s 00:04 users01.dbf 100% 5128KB 58.2MB/s 00:00 [oracle@dbserver PROD]$ cd [oracle@dbserver ~]$ scp noncdb.xml 10.8.98.103:/home/oracle/ oracle@10.8.98.103's password: noncdb.xml 100% 7635 4.1MB/s 00:00 [oracle@dbserver ~]$
插入資料庫
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROD_PDB1 MOUNTED
SQL> create pluggable database noncdb using '/home/oracle/noncdb.xml' source_file_name_convert=('/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/PROD/noncdb') nocopy tempfile reuse ;
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PROD_PDB1 MOUNTED
4 NONCDB MOUNTED
SQL>
特別注意，原來測試老是出錯，參考上面文章操作才發現問題。
注意點
1.這裡指定的source_file_name_convert指定xml檔案中資料檔案的路徑和我們實際存放資料檔案的路徑
2.這裡使用了nocopy而沒有指定file_name_convert這是因為資料檔案我們是透過手工進行複製的，
所以不需要oracle再來幫我們複製了,所以不需要中file_name_convert,而是使用了nocopy
3.這裡設定了tempfile reuse,如果不設定oracle會自動嘗試建立tempfile,但是此tempfile已經手工複製了,就會報錯:
執行轉換指令碼 noncdb_to_pdb.sql
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 NONCDB MOUNTED SQL> alter session set container = noncdb; Session altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NONCDB MOUNTED SQL> @?/rdbms/admin/noncdb_to_pdb.sql SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 80 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 100 SQL> SET VERIFY OFF 。。。。。。。。。。。。。。。。。。。。。 。。。。。。。。。。。。。。。。。。。。 09:02:36 SQL> set time OFF SQL> set timing OFF SQL> set trimout ON SQL> set trimspool ON SQL> set underline "-" SQL> set verify OFF SQL> set wrap ON SQL> set xmloptimizationcheck OFF
會執行挺長時間。執行完成之後就可以開啟資料庫了
SQL> alter pluggable database open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NONCDB READ WRITE NO SQL> [oracle@dbserver ~]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2022 09:18:13 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 29-JAN-2022 09:16:55 Uptime 0 days 0 hr. 1 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "d69dee198a891431e0539662080a487d" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "noncdb" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prod" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... The command completed successfully
############################################################
第二種方式克隆
1、也需要做第一種方式的相容性檢查（同上）
2、在源端建立使用者用於dblink
SQL> create user remote_clone_user identified by remote_clone_user; User created. SQL> grant create session,create pluggable database to remote_clone_user; Grant succeeded.
3、在目標端建立到非cdb的資料庫連結
首先目標端的tnsnames.ora新增本地連結名稱給dblink使用，我這裡加了tns_150
[oracle@dbserver admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) tns_150 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) LISTENER_PROD = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)) [oracle@dbserver admin]$
4、目標端建立dblink
SQL> create database link noncdb_clone_link connect to remote_clone_user identified by remote_clone_user using 'tns_150'; Database link created.
這裡我直接用了源端非cdb的建立的使用者。
登入測試
SQL> desc user_tables@NONCDB_CLONE_LINK; Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(128) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(128) IOT_NAME VARCHAR2(128) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
5、在目標端執行資料庫克隆，記住 先在目標端建立好對應的目錄
mkdir -p /u01/app/oracle/oradata/PROD/noncdb
SQL> create pluggable database noncdb from NON$CDB@NONCDB_CLONE_LINK file_name_convert=('/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/PROD/noncdb/');
Pluggable database created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/sysaux01.dbf
/u01/app/oracle/oradata/PROD/undotbs01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/PROD/users01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/PROD/noncdb/system01.dbf
/u01/app/oracle/oradata/PROD/noncdb/sysaux01.dbf
/u01/app/oracle/oradata/PROD/noncdb/undotbs01.dbf
/u01/app/oracle/oradata/PROD/noncdb/users01.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/noncdb/noncdb01.dbf
12 rows selected.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 NONCDB MOUNTED
SQL>
注意點
6、執行 noncdb_to_pdb.sql，時間有點長等待。
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
7、開啟pdb
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 NONCDB MOUNTED SQL> alter database open; Database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 NONCDB READ WRITE NO [oracle@dbserver noncdb]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2022 10:35:35 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 29-JAN-2022 09:16:55 Uptime 0 days 1 hr. 18 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dbserver/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "d6afe25e92641fd4e0536762080a7812" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "noncdb" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prod" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s). Instance "prod", status READY, has 1 handler(s) for this service... The command completed successfully
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70004783/viewspace-2854421/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PDB克隆遷移轉換
- 【PDB】 為Oracle pdb新增服務（pdb add service）Oracle
- Oracle 建立PDB-Plugging In an Unplugged PDBOracle
- 【PDB】Oracle pdb維護常用sql命令OracleSQL
- 【PDB】pdb閃回，Oracle還原點Oracle
- 【PDB】Oracle跨PDB檢視查詢Oracle
- 【PDB】Oracle PDB資源管理參考Oracle
- 【PDB】Oracle 建立pdb說明(create pluggable database)OracleDatabase
- Oracle 12c系列(七) | Non-CDB轉換為PDBOracle
- Oracle 12.2 新特性: Online PDB relocate (PDB hot move)Oracle
- kubernetes之PDB
- 1.3.2.2 Creation of a PDB by Plugging In(通過插入的方式建立PDB)
- Z001-001 從 PDB$SEED 種子容器建立 PDB
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- pdb受限解決思路
- Oracle：PDB 引數管理Oracle
- 2.6.2 Overview of Flashback PDB in a CDBView
- 2.2.7 Overview of PDB Lockdown ProfilesView
- 2.1.3. PDB型別型別
- Oracle 18C新特性之PDB snapshot Carousel--PDB快照輪播Oracle
- oracle 12c PDB隨CDB啟動和連結PDB的方式Oracle
- 【PDB】Oracle資料庫如何檢查和設定pdb最大儲存大小Oracle資料庫
- PDB插拔操作手冊
- 1.3.2. 建立一個PDB
- 1.3.2.4 建立一個代理PDB
- 1.3.2.2.1. 插拔方式建立PDB
- oracle19c連pdbOracle
- Oracle 建立PDB-from ScratchOracle
- Oracle 建立PDB-本地克隆Oracle
- 12c pdb基本操作
- 【12c cdb pdb】實驗
- Oracle 建立PDB-遠端克隆Oracle
- 使用pdb進行Python除錯Python除錯
- 【CDB】Oracle CDB/PDB常用管理命令Oracle
- oracle 19c pdb遷移Oracle
- 1.3.2.3. 透過Relocating方式建立PDB
- 1.3.2.1.2.1. 通過快照方式建立PDB
- oracle 19c CDB vs pdb 建立Oracle