資訊專欄INFORMATION COLUMN
oracledatabase12c之后推出了多租戶模式，這個功能也是云時代的一個需求，對于DBA來說更便捷和彈性。我們可以輕松的創建和遷移一個數據庫，比跨平臺傳輸表空間和datapump方便很多。這里我們介紹pdb遷移/升級的方法，總的來說分為在線和離線，其中在線是最省事的。
該方式對于相同版本的pdb之間的遷移沒問題。如果是跨版本的，比如從12.1到19c也可以使用。在19c上clone完之后，需要運行dbupgrade腳本。
select alter user ||username|| account lock; from dba_users where account_status=OPEN; |
alter pluggable database pdb1 close immediate instances=all; alter pluggable database pdb1 open read only instances=all; |
在新的容器數據庫上執行以下操作
create database link clone_link connect to system identified by oracle using (description=(address=(protocol=tcp)(host=192.168.10.21)(port=1521))(connect_data=(service_name=pdb1))); |
create pluggable database pdb1 from pdb1@clone_link; |
下面是在通過dblink遠程克隆時alert日志對應的輸出
This instance was first to open pluggable database PDB1 (container=3) Database Characterset for PDB1 is ZHS16GBK Deleting old file#319 from file$ Deleting old file#320 from file$ Deleting old file#321 from file$ Deleting old file#325 from file$ Deleting old file#326 from file$ Deleting old file#327 from file$ Deleting old file#328 from file$ Deleting old file#329 from file$ Adding new file#73 to file$(old file#319) Adding new file#74 to file$(old file#320) Adding new file#75 to file$(old file#321) Adding new file#76 to file$(old file#325) Adding new file#77 to file$(old file#326) Adding new file#78 to file$(old file#327) Adding new file#79 to file$(old file#328) Adding new file#80 to file$(old file#329) Successfully created internal service pdb1 at open ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local **************************************************************** Post plug operations are now complete. Pluggable database PPDBETC with pdb id - 3 is now marked as NEW. **************************************************************** Completed: create pluggable database pdb1 from pdb1@clone_link |
如果源端和目標端對應的patch不一致或者出現一些無效的組件等，PDB會處以restricted模式。
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE YES |
用來檢查補丁或沖突的SQL語句
select patch_id, patch_uid, version, status, description from dba_registry_sqlpatch; select inst_id,name,open_mode,restricted from gv$pdbs order by 1,2; select name,con_id,con_uid,open_mode,restricted,guid from v$pdbs order by 1,2; select status, message, action from pdb_plug_in_violations where status !=RESOLVED; |
如果是補丁不一致，通過datapatch一般能解決大部分問題
oracle> ./datapatch -verbose -pdbs PDB1 |
如果datapatch成功執行后，數據庫還處于restricted模式，那么大部分情況下，是因為一些無效對象導致的。在這個模式下，數據庫時不正常的，千萬不要切換和運行業務。
查詢無效對象
SQL> select owner,object_name,object_type,status from dba_objects where status=INVALID and OWNER IN (PUBLIC,SYS,SYSTEM,XDB,ORDSYS,ORDPLUGINS,ORDDATA,MDSYS,CTXSYS); OWNER OBJECT_NAME OBJECT_TYPE STATUS --------- ----------------- --------------- -------- XDB DBMS_XDBUTIL_INT PACKAGE BODY INVALID XDB DBMS_XDBT PACKAGE BODY INVALID CTXSYS DRILOAD PACKAGE BODY INVALID CTXSYS DRVDOC PACKAGE BODY INVALID MDSYS SDO_OLS PACKAGE BODY INVALID |
查詢組件狀態
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status from dba_registry; |
查詢后會發現，一些組件可能也是無效的,通過dba_errors去下鉆出現問題的根本原因
SQL> select text from dba_errors where name=DBMS_XDBUTIL_INT and owner=XDB; TEXT ------------------------------------------------------------------------------ PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored 10 rows selected. SQL> select text from dba_errors where name=DBMS_XDBT and owner=XDB; TEXT ------------------------------------------------------------------------ PLS-00201: identifier CTX_DOC must be declared PL/SQL: Statement ignored 2 rows selected. SQL> select text from dba_errors where name=DRILOAD and owner=CTXSYS; TEXT ------------------------------------------------------------------------- PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored 4 rows selected. SQL> select text from dba_errors where name=DRVDOC and owner=CTXSYS; TEXT ------------------------------------------------------------------------ PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored PLS-00201: identifier DBMS_SQL must be declared PL/SQL: Statement ignored 10 rows selected. SQL> select text from dba_errors where name=SDO_OLS and owner=MDSYS; TEXT ----------------------------------------------------------------------- PLS-00201: identifier UTL_HTTP must be declared PL/SQL: Item ignored PLS-00201: identifier UTL_HTTP must be declared PL/SQL: Item ignored PLS-00201: identifier UTL_HTTP must be declared PL/SQL: Statement ignored PLS-00320: the declaration of the type of this expression is incomplete or malformed PL/SQL: Statement ignored 10 rows selected. |
通過以上，可以發現這些無效對象是因為權限的問題，導致無法正常編譯。
SQL> grant execute on dbms_sql to XDB,CTXSYS; SQL> grant execute on CTX_DOC to XDB; SQL> grant execute on UTL_HTTP to MDSYS; |
授權后，可以通過這下面的命令進行編譯
SQL> exec dbms_pdb.exec_as_oracle_script(alter package XDB.DBMS_XDBT compile body); SQL> exec dbms_pdb.exec_as_oracle_script(alter package CTXSYS.DRVDOC compile body); SQL> exec dbms_pdb.exec_as_oracle_script(alter package CTXSYS.DRILOAD compile body); SQL> exec dbms_pdb.exec_as_oracle_script(alter package XDB.DBMS_XDBUTIL_INT compile body); SQL> exec dbms_pdb.exec_as_oracle_script(alter package MDSYS.SDO_OLS compile body); |
啟動新數據庫
alter pluggable database pdb1 open read write instances=all; |
解鎖數據庫用戶
select alter user ||username|| account unlock; from dba_users where account_status=LOCKED; |
與原庫進行對比
1.無效對象數： select count(*) from dba_objects where status = INVALID and owner in ( TEST1, TEST2, TEST3, TEST4, TEST5); 2.對象總數為： select count(*) from dba_objects where owner in ( TEST1, TEST2, TEST3, TEST4, TEST5); 3.用戶下對象所使用的表空間 select distinct TABLESPACE_NAME from dba_segments where owner in ( TEST1, TEST2, TEST3, TEST4, TEST5); |
確認無誤后，遷移完成。
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/130016.html
摘要：客戶可以通過一款移動應用監控其遷移狀態。表示，隨著產品在后續版本中不斷調整，將轉向自主交付。然而認為，提供了更加全面的解決方案，包括咨詢和教育服務。現在已經支持向和遷移的以及客戶。最終將把產品擴展到支持向轉移的和客戶，以及向轉移的客戶。Oracle Soar將一系列自動遷移工具與專業服務相結合，所有這些都由Oracle提供——這是一套完整的內部遷移解決方案。這種半自動化的解決方案，也讓Ora...
摘要：年月日，遷移服務解決方案在城市峰會中正式發布。遷移服務向分布式架構升級的直接路徑基于上述問題和挑戰，同時經過螞蟻十年數據庫架構升級的先進經驗，螞蟻金服為客戶打造了這款一站式數據遷移解決方案遷移服務，簡稱。 2019年1月4日，OceanBase遷移服務解決方案在ATEC城市峰會中正式發布。螞蟻金服資深技術專家師文匯和技術專家韓谷悅共同分享了OceanBase遷移服務的重要特性和業務實踐...
摘要：年月日，遷移服務解決方案在城市峰會中正式發布。遷移服務向分布式架構升級的直接路徑基于上述問題和挑戰，同時經過螞蟻十年數據庫架構升級的先進經驗，螞蟻金服為客戶打造了這款一站式數據遷移解決方案遷移服務，簡稱。 2019年1月4日，OceanBase遷移服務解決方案在ATEC城市峰會中正式發布。螞蟻金服資深技術專家師文匯和技術專家韓谷悅共同分享了OceanBase遷移服務的重要特性和業務實踐...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20
