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

lfree發表於2018-10-13
Database

[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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章