[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!没有问题,不建议在生产系统这样操作,还是按照官方的文档来操作安全一下!!!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2216289/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2216289/