資訊專欄INFORMATION COLUMN
oracledatabase12c之后推出了多租戶模式，這個(gè)功能也是云時(shí)代的一個(gè)需求，對(duì)于DBA來說更便捷和彈性。我們可以輕松的創(chuàng)建和遷移一個(gè)數(shù)據(jù)庫，比跨平臺(tái)傳輸表空間和datapump方便很多。這里我們介紹pdb遷移/升級(jí)的方法，總的來說分為在線和離線，其中在線是最省事的。
該方式對(duì)于相同版本的pdb之間的遷移沒問題。如果是跨版本的，比如從12.1到19c也可以使用。在19c上clone完之后，需要運(yùn)行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; |
在新的容器數(shù)據(jù)庫上執(zhí)行以下操作
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遠(yuǎn)程克隆時(shí)alert日志對(duì)應(yīng)的輸出
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 |
如果源端和目標(biāo)端對(duì)應(yīng)的patch不一致或者出現(xiàn)一些無效的組件等，PDB會(huì)處以restricted模式。
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE YES |
用來檢查補(bǔ)丁或沖突的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; |
如果是補(bǔ)丁不一致，通過datapatch一般能解決大部分問題
oracle> ./datapatch -verbose -pdbs PDB1 |
如果datapatch成功執(zhí)行后，數(shù)據(jù)庫還處于restricted模式，那么大部分情況下，是因?yàn)橐恍o效對(duì)象導(dǎo)致的。在這個(gè)模式下，數(shù)據(jù)庫時(shí)不正常的，千萬不要切換和運(yùn)行業(yè)務(wù)。
查詢無效對(duì)象
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 |
查詢組件狀態(tài)
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status from dba_registry; |
查詢后會(huì)發(fā)現(xiàn)，一些組件可能也是無效的,通過dba_errors去下鉆出現(xiàn)問題的根本原因
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. |
通過以上，可以發(fā)現(xiàn)這些無效對(duì)象是因?yàn)闄?quán)限的問題，導(dǎo)致無法正常編譯。
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; |
授權(quán)后，可以通過這下面的命令進(jìn)行編譯
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); |
啟動(dòng)新數(shù)據(jù)庫
alter pluggable database pdb1 open read write instances=all; |
解鎖數(shù)據(jù)庫用戶
select alter user ||username|| account unlock; from dba_users where account_status=LOCKED; |
與原庫進(jìn)行對(duì)比
1.無效對(duì)象數(shù)： select count(*) from dba_objects where status = INVALID and owner in ( TEST1, TEST2, TEST3, TEST4, TEST5); 2.對(duì)象總數(shù)為： select count(*) from dba_objects where owner in ( TEST1, TEST2, TEST3, TEST4, TEST5); 3.用戶下對(duì)象所使用的表空間 select distinct TABLESPACE_NAME from dba_segments where owner in ( TEST1, TEST2, TEST3, TEST4, TEST5); |
確認(rèn)無誤后，遷移完成。
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/130016.html
摘要：客戶可以通過一款移動(dòng)應(yīng)用監(jiān)控其遷移狀態(tài)。表示，隨著產(chǎn)品在后續(xù)版本中不斷調(diào)整，將轉(zhuǎn)向自主交付。然而認(rèn)為，提供了更加全面的解決方案，包括咨詢和教育服務(wù)?，F(xiàn)在已經(jīng)支持向和遷移的以及客戶。最終將把產(chǎn)品擴(kuò)展到支持向轉(zhuǎn)移的和客戶，以及向轉(zhuǎn)移的客戶。Oracle Soar將一系列自動(dòng)遷移工具與專業(yè)服務(wù)相結(jié)合，所有這些都由Oracle提供——這是一套完整的內(nèi)部遷移解決方案。這種半自動(dòng)化的解決方案，也讓Ora...
摘要：年月日，遷移服務(wù)解決方案在城市峰會(huì)中正式發(fā)布。遷移服務(wù)向分布式架構(gòu)升級(jí)的直接路徑基于上述問題和挑戰(zhàn)，同時(shí)經(jīng)過螞蟻十年數(shù)據(jù)庫架構(gòu)升級(jí)的先進(jìn)經(jīng)驗(yàn)，螞蟻金服為客戶打造了這款一站式數(shù)據(jù)遷移解決方案遷移服務(wù)，簡(jiǎn)稱。 2019年1月4日，OceanBase遷移服務(wù)解決方案在ATEC城市峰會(huì)中正式發(fā)布。螞蟻金服資深技術(shù)專家?guī)熚膮R和技術(shù)專家韓谷悅共同分享了OceanBase遷移服務(wù)的重要特性和業(yè)務(wù)實(shí)踐...
摘要：年月日，遷移服務(wù)解決方案在城市峰會(huì)中正式發(fā)布。遷移服務(wù)向分布式架構(gòu)升級(jí)的直接路徑基于上述問題和挑戰(zhàn)，同時(shí)經(jīng)過螞蟻十年數(shù)據(jù)庫架構(gòu)升級(jí)的先進(jìn)經(jīng)驗(yàn)，螞蟻金服為客戶打造了這款一站式數(shù)據(jù)遷移解決方案遷移服務(wù)，簡(jiǎn)稱。 2019年1月4日，OceanBase遷移服務(wù)解決方案在ATEC城市峰會(huì)中正式發(fā)布。螞蟻金服資深技術(shù)專家?guī)熚膮R和技術(shù)專家韓谷悅共同分享了OceanBase遷移服務(wù)的重要特性和業(yè)務(wù)實(shí)踐...
閱讀 1356·2023-01-11 13:20
閱讀 1707·2023-01-11 13:20
閱讀 1215·2023-01-11 13:20
閱讀 1906·2023-01-11 13:20
閱讀 4165·2023-01-11 13:20
閱讀 2757·2023-01-11 13:20
閱讀 1402·2023-01-11 13:20
閱讀 3671·2023-01-11 13:20
