在做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;