[20181011]ORA-65086 cannot openclose the pluggable database.txt

[20181011]ORA-65086 cannot openclose the pluggable database.txt

--//最近一段時間一直在折騰"學習"12cR2(以前12cR1許多功能不支援),今天差點導致pdb=test02p無法open.
--//重複演示:

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SYS@test> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST01P                        READ WRITE NO
         5 TEST02P                        MOUNTED

2.問題演示:

--//alter pluggable database test02p close immediate;
SYS@test> alter pluggable database test02p unplug into 'd:/app/oracle/pdb_xml/test02p.xml';
Pluggable database altered.

SYS@test> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST01P                        READ WRITE NO
         5 TEST02P                        MOUNTED

SYS@test> select pdb_id,pdb_name,dbid,con_id,guid,status from dba_pdbs;
    PDB_ID PDB_NAME                   DBID     CON_ID GUID                             STATUS
---------- -------------------- ---------- ---------- -------------------------------- --------------------
         2 PDB$SEED             3884133861          2 9EDCAED6B35A4E0EB0FFC7987DB01F33 NORMAL
         3 TEST01P              2923790755          3 C287357CE3D5470AA01668B945336F73 NORMAL
         5 TEST02P              4197665802          5 5FC5F5FEAFFB4FB8976B61874A94D666 UNPLUGGED

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

--//對於unplug的理解,我的第一感覺就是快速遷移,unplug後生成了xml文字,只要複製資料檔案到對應伺服器.
--//再plug就ok了.
--//而實際上unplug以後,無法在open開啟pdb database.

3.官方文件解析:

After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED. The only
operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB. You must
drop the PDB before you can plug it into the same CDB or another CDB.

--//感覺oracle這裡提示不清楚,應該有1個狀態表示UNPLUGGED.(查詢dbs_pdbs檢視)
--//我的感覺oracle這樣設計不好,應該設計有一個更簡單的方式回到正常狀態,或者根本不必修改狀態(UNPLUGGED).
--//而要經歷一個drop pluggable database過程,這樣的感覺不是很好.

4.問題解決:
SYS@test> drop pluggable database test02p;
Pluggable database dropped.

SYS@test> CREATE pluggable DATABASE test02p USING 'd:/app/oracle/pdb_xml/test02p.xml' NOCOPY;
Pluggable database created.

SYS@test> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST01P                        READ WRITE NO
         4 TEST02P                        MOUNTED

SYS@test> select pdb_id,pdb_name,dbid,con_id,guid,status from dba_pdbs;
    PDB_ID PDB_NAME                   DBID     CON_ID GUID                             STATUS
---------- -------------------- ---------- ---------- -------------------------------- --------------------
         2 PDB$SEED             3884133861          2 9EDCAED6B35A4E0EB0FFC7987DB01F33 NORMAL
         3 TEST01P              2923790755          3 C287357CE3D5470AA01668B945336F73 NORMAL
         4 TEST02P              4197665802          4 5FC5F5FEAFFB4FB8976B61874A94D666 NEW

--//status=NEW.

SYS@test> alter pluggable database test02p open;
Pluggable database altered.

SYS@test> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST01P                        READ WRITE NO
         4 TEST02P                        READ WRITE NO

SYS@test> select pdb_id,pdb_name,dbid,con_id,guid,status from dba_pdbs;
    PDB_ID PDB_NAME                   DBID     CON_ID GUID                             STATUS
---------- -------------------- ---------- ---------- -------------------------------- --------------------
         2 PDB$SEED             3884133861          2 9EDCAED6B35A4E0EB0FFC7987DB01F33 NORMAL
         3 TEST01P              2923790755          3 C287357CE3D5470AA01668B945336F73 NORMAL
         4 TEST02P              4197665802          4 5FC5F5FEAFFB4FB8976B61874A94D666 NORMAL
--//pdb_id變成4,原來是5.

5.看看是否可以直接修改狀態.

--//重複試驗:
SYS@test> alter pluggable database test02p close immediate;
Pluggable database altered.

SYS@test> alter pluggable database test02p unplug into 'd:/app/oracle/pdb_xml/test02p.xml';
Pluggable database altered.

--//如果查詢檢視dba_pdbs,可以發現如下:
 (
             c.status
            ,0, 'UNUSABLE'
            ,1, 'NEW'
            ,2, 'NORMAL'
            ,3, 'UNPLUGGED'
            ,5, 'RELOCATING'
            ,6, 'REFRESHING'
            ,7, 'RELOCATED'
            ,'UNDEFINED'
          )
--//修改為2就修改回來了.

SYS@test> column status format 9999
SYS@test> select obj#,con_id#,dbid,con_uid,status from sys.container$;
      OBJ#    CON_ID#       DBID    CON_UID STATUS
---------- ---------- ---------- ---------- ------
       240          1 2286984624          1      2
       241          2 3884133861 3884133861      2
     76335          3 2923790755 2923790755      2
     76475          4 4197665802 3435848889      3

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database
    
--//con_id=4,status=3,直接修改為2看看.

SYS@test> update sys.container$ set status=2 where CON_ID#=4;
1 row updated.

SYS@test> commit ;
Commit complete.

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database
--//不行,估計要重新整理一下共享池看看.

SYS@test> alter system flush shared_pool;
System altered.

SYS@test> alter pluggable database test02p open;
Pluggable database altered.

SCOTT@test02p> select * from dept where rownum=1;

    DEPTNO DNAME                LOC
---------- -------------------- -------------
        10 ACCOUNTING           NEW YORK

--//ok!沒有問題,不建議在生產系統這樣操作,還是按照官方的文件來操作安全一下!!!