[20181011]ORA-44777 – Pluggable database service cannot be started.txt

[20181011]ORA-44777 – Pluggable database service cannot be started.txt

--//链接:https://blog.dbi-services.com/oracle-12cr2-ora-44777-pluggable-database-service-cannot-be-started/
--//该链接演示了如果删除删除pdb里面的服务,会导致pdb 数据库无法连接的情况.

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
         4 TEST02P                        READ WRITE NO

SYS@test> select con_id, name from v$services where con_id = 4;
    CON_ID NAME
---------- --------------------
         4 test02p        

2.删除服务:

SYS@test> alter session set container=test02p;
Session altered.

SYS@test> show con_name
CON_NAME
------------------------------
TEST02P

SYS@test> exec dbms_service.STOP_SERVICE(SERVICE_NAME=>'test02p');
PL/SQL procedure successfully completed.

SYS@test> exec dbms_service.DELETE_SERVICE(SERVICE_NAME=>'test02p');
PL/SQL procedure successfully completed.

SYS@test> select con_id, name from v$services where con_id = 4;
    CON_ID NAME
---------- --------------------
         4 test02p

--//服务test02p还在.实际上查底层访问的是X$表.
 
3.问题再现:
SYS@test> alter pluggable database test02p close;
Pluggable database altered.

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-44304: service  does not exist
ORA-44777: Pluggable database service cannot be started.

4.问题分析:
SYS@test> select con_id, name from v$services where con_id = 4;
no rows selected

SYS@test> select name, open_mode from v$pdbs where name = 'TEST02P';
NAME                 OPEN_MODE
-------------------- ----------
TEST02P              READ WRITE

--//https://blog.dbi-services.com/oracle-12cr2-ora-44777-pluggable-database-service-cannot-be-started/
What is strange, is that the pdb was opened read-write, however, I am not able to connect to it in any way. Of course,
not with sqlplus via service, but also not via the alter session set container command. Oracle immediately kicks me out
with the famous ORA-03113 error.

--//test02p已经打开,但是服务test02p不存在,无法连接pdb=test02p数据库.
--//另外我的测试执行如下,整个数据库会崩溃..做了2次...
alter session set container=test02p;

5.问题解决:

SQL> alter pluggable database test02p close;
Pluggable database altered.

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

SYS@test> select name, open_mode from v$pdbs where name = 'TEST02P';
NAME                 OPEN_MODE
-------------------- ----------
TEST02P              MOUNTED

SYS@test> drop pluggable database TEST02P;
Pluggable database dropped.

SYS@test> select name, open_mode from v$pdbs where name = 'TEST02P';
no rows selected

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

SYS@test> alter pluggable database TEST02P open;
Pluggable database altered.
--//从这里看出第一次open时候,要修改信息.

SYS@test> select name, open_mode from v$pdbs where name = 'TEST02P';
NAME                 OPEN_MODE
-------------------- ----------
TEST02P              READ WRITE

SYS@test> select con_id, name from v$services ;
    CON_ID NAME
---------- --------------------
         5 test02p
         1 testXDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         1 test
         3 test01p
6 rows selected.

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

--//https://blog.dbi-services.com/oracle-12cr2-ora-44777-pluggable-database-service-cannot-be-started/
Conclusion

Applications should not work with the default PDB service which comes out of the box with any PDB. This service is for
internal use only. And dropping the default service of the pluggable database is also not a good idea, even it works.
-) From my point of view, Oracle should not allow a DBA to do that.

6.实际上这个服务定义在各个pdb下SYS.SERVICE$表中,如果能恢复原来的样子估计也是ok的.:
SCOTT@test02p> select rowid,service_id,name,name_hash,network_name,creation_date,deletion_date from SYS.SERVICE$;
ROWID              SERVICE_ID NAME                  NAME_HASH NETWORK_NA CREATION_DATE       DELETION_DATE
------------------ ---------- -------------------- ---------- ---------- ------------------- -------------------
AAAAHBAABAAAFMhAAA          9 test02p              2419093932 test02p    2018-10-12 21:46:06 2018-10-12 22:06:08
AAAAHBAABAAAFMhAAB          1 test02p              2419093932 test02p    2018-10-12 22:37:43

--//登录test01p看看.
SCOTT@test01p> select rowid,service_id,name,name_hash,network_name,creation_date,deletion_date from SYS.SERVICE$;
ROWID              SERVICE_ID NAME                  NAME_HASH NETWORK_NA CREATION_DATE       DELETION_DATE
------------------ ---------- -------------------- ---------- ---------- ------------------- -------------------
AAAAHBAABAAAFMhAAA          7 test01p              2872249700 test01p    2018-10-06 23:27:57
--//注意test02p从test01p克隆过来的,两者rowid一样.不过要使用bbed修改还不是很好操作.我估计DELETION_DATE设置为Null 就ok了.

SCOTT@test01p> @ rowid AAAAHBAABAAAFMhAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
       449          1      21281          0   0x405321           1,21281              alter system dump datafile 1 block 21281

--//通过bbed观察:
BBED> set dba 28,21282
        DBA             0x07005322 (117461794 28,21282)
--//注:windows下bbed访问数据块存在1个偏差,必须加+1. 文件号是28,上面看到实际上是相对文件号.
--//select file#||' '||name c70 from v$dbfile order by file#;
1 D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
2 D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF
3 D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF
4 D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF
5 D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
6 D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF
7 D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF
8 D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF
9 D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF
10 D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF
11 D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
28 D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSTEM01.DBF
29 D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSAUX01.DBF
30 D:\APP\ORACLE\ORADATA\TEST\TEST02P\UNDOTBS01.DBF
31 D:\APP\ORACLE\ORADATA\TEST\TEST02P\USERS01.DBF

SCOTT@test02p> SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO ( 'AAAAHBAABAAAFMhAAA' ,'SYS' ,'SERVICE$') N10 FROM DUAL;
 N10
----
  28
--//要确定绝对文件号,需要schema,object_name参数.

BBED> p kdbr
sb2 kdbr[0]                                 @110      7654
sb2 kdbr[1]                                 @112      7530

7530+92 = 7622

SCOTT@test02p> @ bbedcol12.sql SYS SERVICE$;
DISPLAY BBED EXAMINE(X) FORMAT
C80
---------------------------
ncnctntccnnnnnnccnncccncnnn

BBED> x /rncnctntccnnnnnnccnncccncnnn * kdbr[0]
rowdata[124]                                @7746
------------
flag@7746: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7747: 0x02
cols@7748:   17

col    0[2] @7749: 9
col    1[7] @7752: test02p
col    2[6] @7760: 2419093932
col    3[7] @7767: test02p
col    4[7] @7775: 2018-10-12 21:46:06
col    5[6] @7783: 3424783539
col    6[7] @7790: 2018-10-12 22:06:08
~~~~~~~~~->对应DELETION_DATE字段信息.
col    7[0] @7798: *NULL*
col    8[0] @7799: *NULL*
col    9[0] @7800: *NULL*
col   10[0] @7801: *NULL*
col   11[0] @7802: *NULL*
col   12[0] @7803: *NULL*
col   13[0] @7804: *NULL*
col   14[3] @7805: 136
col   15[0] @7809: *NULL*
col   16[7] @7810: TEST02P

--//这条记录是修改后的,实际上修改前的偏移 7810+7+1=7818处.(注:数据从数据块底部开始插入的).

BBED> x /rncnctntccnnnnnnccnncccncnnn offset 7818
rowdata[196]                                @7818
------------
flag@7818: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7819: 0x00
cols@7820:   17

col    0[2] @7821: 9
col    1[7] @7824: test02p
col    2[6] @7832: 2419093932
col    3[7] @7839: test02p
col    4[7] @7847: 2018-10-12 21:46:06
col    5[6] @7855: 3424783539
col    6[0] @7862: *NULL*
col    7[0] @7863: *NULL*
col    8[0] @7864: *NULL*
col    9[0] @7865: *NULL*
col   10[0] @7866: *NULL*
col   11[0] @7867: *NULL*
col   12[0] @7868: *NULL*
col   13[0] @7869: *NULL*
col   14[3] @7870: 136
col   15[0] @7874: *NULL*
col   16[7] @7875: TEST02P

--//上下比较就知道偏移offset=7818对应修改前的记录,注意这里是绝对偏移,写入kdbr[0]是相对偏移.
--//7746-7654 = 92,相差92.
--//使用map /v观察: struct kdbh, 14 bytes  @92 ,通过kdbh的位置也可以确定偏移量.
--//7818-92 = 7726,也就是修改:

BBED> assign kdbr[0]=7726
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbr[0]                                 @110      7726

BBED> x /rncnctntccnnnnnnccnncccncnnn * kdbr[0]
rowdata[196]                                @7818
------------
flag@7818: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7819: 0x00
cols@7820:   17

col    0[2] @7821: 9
col    1[7] @7824: test02p
col    2[6] @7832: 2419093932
col    3[7] @7839: test02p
col    4[7] @7847: 2018-10-12 21:46:06
col    5[6] @7855: 3424783539
col    6[0] @7862: *NULL*
col    7[0] @7863: *NULL*
col    8[0] @7864: *NULL*
col    9[0] @7865: *NULL*
col   10[0] @7866: *NULL*
col   11[0] @7867: *NULL*
col   12[0] @7868: *NULL*
col   13[0] @7869: *NULL*
col   14[3] @7870: 136
col   15[0] @7874: *NULL*
col   16[7] @7875: TEST02P
--//这样再修正一些检查和以及相关信息就ok了.
--//取消修复操作.还原:
BBED> undo
BBED> p kdbr
sb2 kdbr[0]                                 @110      7654
sb2 kdbr[1]                                 @112      7530

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSTEM01.DBF
BLOCK = 21281
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

7.看看是否修改参数实现:
SELECT name
      ,display_value
      ,isdefault
      ,isses_modifiable
      ,issys_modifiable
      ,ispdb_modifiable
  FROM v$parameter
 WHERE LOWER (name) LIKE '%service%';

NAME                 DISPLAY_VALUE                  ISDEFAULT ISSES ISSYS_MOD ISPDB
-------------------- ------------------------------ --------- ----- --------- -----
service_names        test                           TRUE      FALSE IMMEDIATE FALSE

--//ispdb_modifiable=FALSE.视乎这个参数不能在pdb下修改的.也就是解决这个问题只能按照作者的建议实现.
--//参考连接:http://blog.itpub.net/267265/viewspace-1072674/=>[20140118]oracle参数在PluggableDatabases
--//保存在cdb的sys.pdb_spfile$表中.

SCOTT@test02p> alter system set service_names=test,test02p ;
alter system set service_names=test,test02p
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

--//在插件数据库不允许修改.如果在cdb下修改会出现什么情况呢?

8.继续测试再cdb下修改参数如何:
--//重复前面的破坏,在cdb下,执行:
SYS@test> alter session set container=test02p;
Session altered.

SYS@test> show con_name
CON_NAME
------------------------------
TEST02P

SYS@test> exec dbms_service.STOP_SERVICE(SERVICE_NAME=>'test02p');
PL/SQL procedure successfully completed.

SYS@test> exec dbms_service.DELETE_SERVICE(SERVICE_NAME=>'test02p');
PL/SQL procedure successfully completed.

--//问题再现:
SYS@test> alter pluggable database test02p close;
Pluggable database altered.

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-44304: service  does not exist
ORA-44777: Pluggable database service cannot be started.

--//退出在登录:
SYS@test> alter system set service_names=test,test02p scope=memory ;
System altered.

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-65019: pluggable database TEST02P already open

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

SYS@test> alter pluggable database test02p open;
alter pluggable database test02p open
*
ERROR at line 1:
ORA-44304: service  does not exist
ORA-44777: Pluggable database service cannot be started.

--//不行!!

9.突然想起可以修改listener.ora参数,支持sid模式登陆:

--//通过在监听配置文件listener.ora中加入如下:USE_SID_AS_SERVICE_listener=on
--//链接: =>[20170527]12c connect Pluggable database using SID.txt

lsnrctl stop
lsnrctl start

--//感觉应该不行,服务没有起来,这个参数作用应该是把服务当作sid.

SYS@test> connect scott/btbtms@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = test02p)))
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

SYS@test> connect sys/btbtms@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SID = test01p))) as sysdba
Connected.

--//连接pdb=test01p,ok!!
--//还原与修复:

SYS@test01p> connect sys as sysdba
Enter password: a
Connected.

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

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

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> alter pluggable database TEST02P open;
alter pluggable database TEST02P open
*
ERROR at line 1:
ORA-44303: service name exists
ORA-44775: Pluggable database service cannot be created.
--//这回是服务存在,实际上是冲突了,可能和前面执行alter system set service_names=test,test02p scope=memory ;有关.
--//重启数据库与监听ok.

10.总结:
--//不知不觉写的太长,也太乱.

--//附上bbedcol12.sql
prompt
PROMPT DISPLAY BBED EXAMINE(X) FORMAT
prompt

SELECT REPLACE (LISTAGG (c1, ',') WITHIN GROUP (ORDER BY column_id), ',') c80
  FROM (  SELECT data_type
                ,column_id
                ,column_name
                ,DECODE
                 (
                    data_type
                   ,'NUMBER', 'n'
                   ,'CHAR', 'c'
                   ,'VARCHAR', 'c'
                   ,'VARCHAR2', 'c'
                   ,'DATE', 't'
                   ,' '
                 )
                    c1
            FROM dba_tab_cols
           WHERE     owner = UPPER (NVL ('&1', USER))
                 AND TABLE_NAME = UPPER ('&2')
                 AND hidden_column = 'NO'
        ORDER BY column_id);

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

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