oracle如何重建job,批量重建DB link

在做EBS迁移的时候，我们往往要停掉JOB和重建DBlink。而这2种对象由于在常规模式下，不能用sys删除其他用户下的job或dblink，我们通常需要登录到某用户下，停掉该用户下的job和删掉该用户下的DBlink，在迁移时，对于大型系统，里面有很多用户，很多用户甚至不知道密码，无法登陆，那么上述的操作就非常不便了。

其实我们可以用下面的方法来操作：

一、对于job，我们可以用dbms_ijob来操作，这个就比较简单：

1.准备恢复脚本：

sqlplus "/ as sysdba"

set line 500

select 'exec sys.dbms_ijob.broken('||job||',false);' as text from dba_jobs where broken='N';

记下出来的结果

1

2

3

4

5

1.准备恢复脚本：

sqlplus"/ as sysdba"

setline500

select'exec sys.dbms_ijob.broken('||job||',false);'astextfromdba_jobswherebroken='N';

记下出来的结果

2.批量停job

sqlplus "/ as sysdba"

set line 500

select 'exec sys.dbms_ijob.broken('||job||',true);' as text from dba_jobs where broken='N';

执行上述的结果

1

2

3

4

5

2.批量停job

sqlplus"/ as sysdba"

setline500

select'exec sys.dbms_ijob.broken('||job||',true);'astextfromdba_jobswherebroken='N';

执行上述的结果

3.迁移后恢复

执行第一步中备份的脚本即可。

1

2

3.迁移后恢复

执行第一步中备份的脚本即可。

二、对于dblink，我们利用exec immediate的存储过程来执行：

1.检查dblink，如：

select owner,db_link,username from dba_db_links;

OWNER DB_LINK USERNAME

------------------------------ -------------------- ------------------------------

EPSINTF LINK1 PLANINTF

SYS LINK2.WORLD PLANINTF

PUBLIC PUB_LINK1 VOU_LOADER

EPSINTF EPSLINK123.WORLD PLANINTF

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

1

2

3

4

5

6

7

8

9

10

11

12

1.检查dblink，如：

selectowner,db_link,usernamefromdba_db_links;

OWNERDB_LINKUSERNAME

--------------------------------------------------------------------------------

EPSINTFLINK1PLANINTF

SYSLINK2.WORLDPLANINTF

PUBLICPUB_LINK1VOU_LOADER

EPSINTFEPSLINK123.WORLDPLANINTF

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

在各个用户下建立用于执行的存储过程:

select

'create or replace procedure '||DECODE(owner,'PUBLIC','',owner||'.')||'p_execute(p_str in varchar2) as'||CHR(10)

||'begin'||CHR(10)

||'execute immediate p_str;'||CHR(10)

||'end;'||CHR(10)

||'/' as TEXT from dba_db_links

group by owner

TEXT

-------------------------------------------------------------------------------

create or replace procedure EPSINTF.p_execute(p_str in varchar2) as

begin

execute immediate p_str;

end;

/

create or replace procedure p_execute(p_str in varchar2) as

begin

execute immediate p_str;

end;

/

create or replace procedure SYS.p_execute(p_str in varchar2) as

begin

execute immediate p_str;

end;

/

执行上述语句生成的脚本：

sys@ORA9I(192.168.1.23)> create or replace procedure EPSINTF.p_execute(p_str in varchar2) as

2 begin

3 execute immediate p_str;

4 end;

5 /

Procedure created.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

sys@ORA9I(192.168.1.23)> create or replace procedure p_execute(p_str in varchar2) as

2 begin

3 execute immediate p_str;

4 end;

5 /

Procedure created.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

sys@ORA9I(192.168.1.23)> create or replace procedure SYS.p_execute(p_str in varchar2) as

2 begin

3 execute immediate p_str;

4 end;

5 /

Procedure created.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

在各个用户下建立用于执行的存储过程:

select

'create or replace procedure '||DECODE(owner,'PUBLIC','',owner||'.')||'p_execute(p_str in varchar2) as'||CHR(10)

||'begin'||CHR(10)

||'execute immediate p_str;'||CHR(10)

||'end;'||CHR(10)

||'/'asTEXTfromdba_db_links

groupbyowner

TEXT

-------------------------------------------------------------------------------

createorreplaceprocedureEPSINTF.p_execute(p_strinvarchar2)as

begin

executeimmediatep_str;

end;

/

createorreplaceprocedurep_execute(p_strinvarchar2)as

begin

executeimmediatep_str;

end;

/

createorreplaceprocedureSYS.p_execute(p_strinvarchar2)as

begin

executeimmediatep_str;

end;

/

执行上述语句生成的脚本：

sys@ORA9I(192.168.1.23)>createorreplaceprocedureEPSINTF.p_execute(p_strinvarchar2)as

2begin

3executeimmediatep_str;

4end;

5/

Procedurecreated.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

sys@ORA9I(192.168.1.23)>createorreplaceprocedurep_execute(p_strinvarchar2)as

2begin

3executeimmediatep_str;

4end;

5/

Procedurecreated.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

sys@ORA9I(192.168.1.23)>createorreplaceprocedureSYS.p_execute(p_strinvarchar2)as

2begin

3executeimmediatep_str;

4end;

5/

Procedurecreated.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

3.生成备份dblink的脚本(特别注意，备份dblink脚本只在9i有效，因为9i以上dblink的密码不保存在sys.link$中)：

select

'exec '||DECODE(u.name, 'SYS','','PUBLIC','',u.name||'.')||'p_execute('||''''

||'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '

|| L.NAME

||' CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||nvl(L.PASSWORD,'')||'" USING '||''''''||L.HOST||''''''''||');' as TEXT

FROM SYS.LINK$ L, SYS.USER$ U

WHERE L.OWNER# = U.USER#;

TEXT

--------------------------------------------------------------------------------------------------------------------------------------

exec EPSINTF.p_execute('CREATE DATABASE LINK LINK1 CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD1" USING ''tns1''');

exec p_execute('CREATE DATABASE LINK LINK2.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD2" USING ''tns2''');

exec p_execute('CREATE public DATABASE LINK PUB_LINK1 CONNECT TO VOU_LOADER IDENTIFIED BY "MYPASSWORD3" USING ''tns3''');

exec EPSINTF.p_execute('CREATE DATABASE LINK EPSLINK123.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD4" USING ''tns4''');

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

记录下上述的脚本

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

3.生成备份dblink的脚本(特别注意，备份dblink脚本只在9i有效，因为9i以上dblink的密码不保存在sys.link$中)：

select

'exec '||DECODE(u.name,'SYS','','PUBLIC','',u.name||'.')||'p_execute('||''''

||'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '

||L.NAME

||' CONNECT TO '||L.USERID||' IDENTIFIED BY "'||nvl(L.PASSWORD,'')||'" USING '||''''''||L.HOST||''''''''||');'asTEXT

FROMSYS.LINK$L,SYS.USER$U

WHEREL.OWNER# = U.USER#;

TEXT

--------------------------------------------------------------------------------------------------------------------------------------

execEPSINTF.p_execute('CREATE DATABASE LINK LINK1 CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD1" USING ''tns1''');

execp_execute('CREATE DATABASE LINK LINK2.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD2" USING ''tns2''');

execp_execute('CREATE public DATABASE LINK PUB_LINK1 CONNECT TO VOU_LOADER IDENTIFIED BY "MYPASSWORD3" USING ''tns3''');

execEPSINTF.p_execute('CREATE DATABASE LINK EPSLINK123.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD4" USING ''tns4''');

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

记录下上述的脚本

4.批量删除dblink(注：所以oracle版本都适用)

select

'exec '||DECODE(u.name, 'SYS','','PUBLIC','',u.name||'.')||'p_execute('||''''

||'DROP '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '

|| L.NAME||''''||');' as TEXT

FROM SYS.LINK$ L, SYS.USER$ U

WHERE L.OWNER# = U.USER#;

TEXT

--------------------------------------------------------------------------------------------

exec EPSINTF.p_execute('DROP DATABASE LINK LINK1');

exec p_execute('DROP DATABASE LINK LINK2.WORLD');

exec p_execute('DROP public DATABASE LINK PUB_LINK1');

exec EPSINTF.p_execute('DROP DATABASE LINK EPSLINK123.WORLD');

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

执行上述语句即可批量删除不同用户下的dblink：

sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('DROP DATABASE LINK LINK1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> exec p_execute('DROP DATABASE LINK LINK2.WORLD');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> exec p_execute('DROP public DATABASE LINK PUB_LINK1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('DROP DATABASE LINK EPSLINK123.WORLD');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

检查dblink已经删除干净：

sys@ORA9I(192.168.1.23)> select owner,db_link,username from dba_db_links;

no rows selected

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

4.批量删除dblink(注：所以oracle版本都适用)

select

'exec '||DECODE(u.name,'SYS','','PUBLIC','',u.name||'.')||'p_execute('||''''

||'DROP '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '

||L.NAME||''''||');'asTEXT

FROMSYS.LINK$L,SYS.USER$U

WHEREL.OWNER# = U.USER#;

TEXT

--------------------------------------------------------------------------------------------

execEPSINTF.p_execute('DROP DATABASE LINK LINK1');

execp_execute('DROP DATABASE LINK LINK2.WORLD');

execp_execute('DROP public DATABASE LINK PUB_LINK1');

execEPSINTF.p_execute('DROP DATABASE LINK EPSLINK123.WORLD');

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

执行上述语句即可批量删除不同用户下的dblink：

sys@ORA9I(192.168.1.23)>execEPSINTF.p_execute('DROP DATABASE LINK LINK1');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>execp_execute('DROP DATABASE LINK LINK2.WORLD');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>execp_execute('DROP public DATABASE LINK PUB_LINK1');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>execEPSINTF.p_execute('DROP DATABASE LINK EPSLINK123.WORLD');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

检查dblink已经删除干净：

sys@ORA9I(192.168.1.23)>selectowner,db_link,usernamefromdba_db_links;

norowsselected

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

5.迁移后，恢复dblink：

用第3步记下的脚本恢复：

sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('CREATE DATABASE LINK LINK1 CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD1" USING ''tns1''');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> exec p_execute('CREATE DATABASE LINK LINK2.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD2" USING ''tns2''');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> exec p_execute('CREATE public DATABASE LINK PUB_LINK1 CONNECT TO VOU_LOADER IDENTIFIED BY "MYPASSWORD3" USING ''tns3''');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('CREATE DATABASE LINK EPSLINK123.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD4" USING ''tns4''');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)> select owner,db_link,username from dba_db_links;

OWNER DB_LINK USERNAME

------------------------------ -------------------- ------------------------------

EPSINTF LINK1 PLANINTF

SYS LINK2.WORLD PLANINTF

PUBLIC PUB_LINK1 VOU_LOADER

EPSINTF EPSLINK123.WORLD PLANINTF

Elapsed: 00:00:00.00

sys@ORA9I(192.168.1.23)>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

5.迁移后，恢复dblink：

用第3步记下的脚本恢复：

sys@ORA9I(192.168.1.23)>execEPSINTF.p_execute('CREATE DATABASE LINK LINK1 CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD1" USING ''tns1''');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>execp_execute('CREATE DATABASE LINK LINK2.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD2" USING ''tns2''');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>execp_execute('CREATE public DATABASE LINK PUB_LINK1 CONNECT TO VOU_LOADER IDENTIFIED BY "MYPASSWORD3" USING ''tns3''');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>execEPSINTF.p_execute('CREATE DATABASE LINK EPSLINK123.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD4" USING ''tns4''');

PL/SQLproceduresuccessfullycompleted.

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>selectowner,db_link,usernamefromdba_db_links;

OWNERDB_LINKUSERNAME

--------------------------------------------------------------------------------

EPSINTFLINK1PLANINTF

SYSLINK2.WORLDPLANINTF

PUBLICPUB_LINK1VOU_LOADER

EPSINTFEPSLINK123.WORLDPLANINTF

Elapsed:00:00:00.00

sys@ORA9I(192.168.1.23)>

6.清扫战场，删除用来批量清理dblink的存储过程。

select

'drop procedure '||DECODE(owner,'PUBLIC','',owner||'.')||'p_execute;' as TEXT from dba_objects

where object_name=upper('p_execute') and object_type='PROCEDURE'

group by owner;

1

2

3

4

5

6.清扫战场，删除用来批量清理dblink的存储过程。

select

'drop procedure '||DECODE(owner,'PUBLIC','',owner||'.')||'p_execute;'asTEXTfromdba_objects

whereobject_name=upper('p_execute')andobject_type='PROCEDURE'

groupbyowner;