[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181011]ORA-65086 cannot openclose the pluggable database.txtDatabase
- ORA-65122: Pluggable database GUID conflicts with the GUID of an existingDatabaseGUI
- 【PDB】Oracle 建立pdb說明(create pluggable database)OracleDatabase
- [20181018]12c Pluggable Database save state.txtDatabase
- Oracle Database Cloud - Database as a Service Quick StartOracleDatabaseCloudUI
- Guide to Database as a Service (DBaaS)GUIIDEDatabase
- ORA-01102: cannot mount database in EXCLUSIVE modeDatabase
- [fabric]Cannot start service orderer: Mounts denied: In MacMac
- SQL1116N A connection to or activation of database "TESTDB" cannot be madeSQLDatabase
- 在 Google Kubernetes Cluster 上使用 HANA Expression Database ServiceGoExpressDatabase
- 雲關係型資料庫（Relational Database Service，RDS）資料庫Database
- [20181011]12c Service "5fc5f5feaffb4fb8976b61874a94d666".txt
- 關於Cannot resolve scoped service from root provider解決方案IDE
- oracle ORA-01102: cannot mount database in EXCLUSIVE mode（無法起到獨佔模式）OracleDatabase模式
- Oracle 19c透過recover standby database from service修復GAP案例OracleDatabase
- [20181011]12c set FEEDBACK only.txt
- The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DB_NAME'DatabaseAST
- Error querying database. XXXXXXXXXXXXX, No database selected。ErrorDatabase
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- Database TimeoutDatabase
- Database OverallDatabase
- database no shardingDatabase
- Oracle 12.2 使用Database Link優化Standby Database WorkloadOracleDatabase優化
- ORACLE database vaultOracleDatabase
- Relationship Database DesignDatabase
- 3.4 Quiescing a DatabaseUIDatabase
- idea--DatabaseIdeaDatabase
- Oracle clone databaseOracleDatabase
- database的connectDatabase
- Oracle 12.2 新特性：線上PDB資料庫克隆(Pluggable Hot Clone)Oracle資料庫
- PostgreSQL 原始碼解讀（200）- PG 12 Pluggable storage for tables介面淺析SQL原始碼
- ERROR: Cannot uninstall ‘PyYAML‘. It is a distutils installed project and thus we cannot accuratelyErrorYAMLProject
- Password is required when adding a database to AG group if the database has a master keyUIDatabaseAST
- ORA-16649: possible failover to another database prevents this database from beiAIDatabase
- 解決cannot find module providing package或cannot find main modulePackageAI
- Service
- xxx cannot be resolved to a type
- diskgroup "DATADG" cannot be mounted