记录一下scheduler job执行失败，而不知到job拥有者密码的情况下，如何重建私有db link的案例
1.查询job执行情况
1.1查看执行失败的job概况
select * from dba_scheduler_job_log where status<>'SUCCEEDED'
LOG_ID LOG_DATE OWNER JOB_NAME STATUS OPERATION
305351 22-5月 -23 06.10.44.119197000 下午 +07:00 EWF Migration_Update_SingStatus FAILED RUN
305352 22-5月 -23 06.25.44.108925000 下午 +07:00 EWF Migration_Update_SingStatus FAILED RUN
305354 22-5月 -23 06.40.44.101936000 下午 +07:00 EWF Migration_Update_SingStatus FAILED RUN
305356 22-5月 -23 06.55.44.103044000 下午 +07:00 EWF Migration_Update_SingStatus FAILED RUN
1.2.进一步查看
select ADDITIONAL_INFO from dba_scheduler_job_RUN_DETAILS where LOG_ID IN (select LOG_ID from dba_scheduler_job_log where status<>'SUCCEEDED')
ADDITIONAL_INFO
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
"
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
"
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
"
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
2.确认根因
以上输出，看上去有一个对象：EWF.PRO_1540200_03失效了，重新编译一下
ALTER PROCEDURE EWF.PRO_1540200_03 COMPILE
ORA-04052: error occurred when looking up remote object FM.SUBMIT_REC@ORCLDB01IY.FM
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred
04052. 00000 - "error occurred when looking up remote object %s%s%s%s%s"
*Cause: An error occurred when trying to look up a remote object.
*Action: Fix the error. Make sure the remote database system has run
CATRPC.SQL to create necessary views used for querying or looking up
objects stored in the database.
原来是dblink肇事，看看这个link指向哪里
SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='ORCLDB01IY.FM'
OWNER DB_LINK USERNAME HOST
EWF ORCLDB01IY.FM FM (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.20.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))
发现db link指向的目标主机ip不对，原来目标主机的ip已经修改为172.19.5.217,而DB LINK没有修改
这个db link是属于用户ewf下的私有Link,而我不知道ewf的密码，于是想到使用current_schema试试为ewf重现创建db_link:
先获取原来dblink的ddl
SELECT DBMS_METADATA.GET_DDL('DB_LINK','ORCLDB01IY.FM','EWF') AS DDL_SQL FROM DUAL
DDL_SQL
CREATE DATABASE LINK "ORCLDB01IY.FM"
CONNECT TO "FM" IDENTIFIED BY VALUES '05EEF5EF712A07D0C698D886FB43958BAF'
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.20.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))'
3.重建DB LINK
3.1使用current_schema方式
切换current_schema,并drop原来的dblink
alter session set current_schema=ewf;
Session 已更改.
drop database link "EWF"."ORCLDB01IY.FM"
SQL 錯誤: ORA-02024: database link not found
02024. 00000 - "database link not found"
*Cause: Database link to be dropped is not found in dictionary
*Action: Correct the database link nam
不管他，尝试创建db link
CREATE DATABASE LINK ORCLDB01IY.FM
CONNECT TO "FM" IDENTIFIED BY VALUES '05EEF5EF712A07D0C698D886FB43958BAF'
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.5.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))'
Database link ORCLDB01IY.FM 已建立.
确认一下
SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='ORCLDB01IY.FM'
OWNER DB_LINK USERNAME
EWF ORCLDB01IY.FM FM
SYSTEM ORCLDB01IY.FM FM
新的dblink却被创建在system用户，看来current_schema不支持私有db link的创建
3.2使用procedure方式
最后使用procedure的方式：
procedure:
CREATE OR REPLACE PROCEDURE ewf.crdb_link
IS
BEGIN
EXECUTE IMMEDIATE 'drop database link ORCLDB01IY.FM';
EXECUTE IMMEDIATE 'CREATE DATABASE LINK ORCLDB01IY.FM
CONNECT TO FM IDENTIFIED BY VALUES ''05EEF5EF712A07D0C698D886FB43958BAF''
USING ''(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.5.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))''';
end;
drop 掉上面创建于system下的db liink,再执行这个procedure
SHOW USER;
USER 為 "SYS"
drop database link ORCLDB01IY.FM
Database link ORCLDB01IY.FM 已刪除.
exec ewf.crdb_link
Procedure CRDB_LINK 已編譯
已順利完成 PL/SQL 程序.
再次验证
EWF ORCLDB01IY.FM FM (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.5.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))
可以看到，db_link已经顺利重建
4.重新编译失效的对象，并手动执行job
ALTER PROCEDURE EWF.PRO_1540200_03 COMPILE;
Procedure EWF.PRO_1540200_03 已更改.
EXEC dbms_scheduler.run_job('EWF."Migration_Update_SingStatus"');
已順利完成 PL/SQL 程序.
