DB link migration (the password in dblink is unknown), dbdblink

DB link migration (the password in dblink is unknown), dbdblink
I don't know if DBAs sometimes encounter such a situation where the Public db link is used for Schema migration. However, the target account password in db link is unknown, therefore, you cannot create a new DB link in the new environment.
The idea of this experiment is to migrate the base table link $ of the dba_db_links view to and replace it with the new environment. The password is saved in the link $ base table.
---- First, let us explain the two environments, both of which are versions 11.2.0.3 on Linux and have been tested on 10.2.0.4. One database is seven while the other database is ginna. Assume that the database link named test on the seven end is migrated to the ginna end without knowing the scott password.
Source Operation:First, create a public db link named test at the source end.

seven >create public database link test connect to scott identified by tiger using 'ginna';Database link created.seven >select * from global_name@test;GLOBAL_NAME---------------------GINNAseven >select * from dba_db_links;OWNER                          DB_LINK                        USERNAME                       HOST                           CREATED------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------PUBLIC                         TEST                           SCOTT                          ginna                          06-MAR-15

Create the intermediate table trans1 and CTAS base table sys. link $.

seven >create table trans1 as select * from sys.link$;Table created.seven >select count(*) from trans1;  COUNT(*)----------         1

Create the second intermediate table trans2 under the seven user (seven has the dba permission ).

Seven> conn seven/oracleConnected. seven> create table trans2 as select * from sys. link $; ----- of course, you cannot directly CTAS base table. Create table trans2 as select * from sys. link $ * ERROR at line 1: ORA-01031: insufficient privilegesseven> create table trans2 as select * from sys. trans1; Table created. seven> select count (*) from trans2; COUNT (*) ---------- 1 seven>

OK. The intermediate table trans2 is created successfully.

Target operation:Create a Public db link named test2 on the target end.

ginna >show userUSER is "SYS"ginna >select * from dba_db_links;no rows selectedginna >create public database link test2 connect to seven identified by oracle using 'seven';Database link created.ginna >select * from global_name@test2;GLOBAL_NAME----------------------SEVENginna >set line 200ginna >col db_link for a30ginna >col host for a30ginna >select * from dba_db_links;OWNER                          DB_LINK                        USERNAME                       HOST                           CREATED------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------PUBLIC                         TEST2                          SEVEN                          seven                          06-MAR-15

Use the db link of test2 to CTAS the trans2 table on the seven end to trans3.

ginna >create table trans3 as select * from trans2@test2;Table created.ginna >select count(*) from trans3;  COUNT(*)----------         1

Truncate drops base table sys. link $. For security reasons, you can also back up the base table and insert trans3 into base table link $.

ginna >truncate table sys.link$;Table truncated.ginna >insert into sys.link$ select * from trans3;1 row created.ginna >commit;Commit complete.

Check dba_db_links again to find that the db link named test has been successfully migrated to the ginna end.

ginna >select * from dba_db_links;OWNER                          DB_LINK                        USERNAME                       HOST                           CREATED------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------PUBLIC                         TEST                           SCOTT                          ginna                          06-MAR-15

Test the effectiveness of db link:

ginna >select * from global_name@test;GLOBAL_NAME------------------GINNA

OK. Now there is another problem. Does the db link named test2 still exist? Can it be rebuilt? Obviously, it cannot:

ginna >create public database link test2 connect to seven identified by oracle using 'seven';create public database link test2 connect to seven identified by oracle using 'seven'                            *ERROR at line 1:ORA-02011: duplicate database link name

To refresh the shared pool. And then re-build it.

ginna >alter system flush shared_pool;System altered.ginna >create public database link test2 connect to seven identified by oracle using 'seven';Database link created.ginna >select * from dba_db_links;OWNER                          DB_LINK                        USERNAME                       HOST                           CREATED------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------PUBLIC                         TEST                           SCOTT                          ginna                          06-MAR-15PUBLIC                         TEST2                          SEVEN                          seven                          06-MAR-15ginna >

Of course, if you do not flush shared pool, directly Delete the db link will encounter an internal ora-600 error:

SQL> drop public database link test2;drop public database link test2                          *ERROR at line 1:ORA-00603: ORACLE server session terminated by fatal errorORA-00600: internal error code, arguments: [16500], [kqdDBLinkUpdate], [3], [0], [TEST2], [], [], [], [], [], [], []Process ID: 11165Session ID: 152 Serial number: 359SQL> SQL> create public database link test2 connect to seven identified by oracle using 'seven';ERROR:ORA-03114: not connected to ORACLESQL> conn / as sysdbaConnected.SQL> create public database link test2 connect to seven identified by oracle using 'seven';Database link created.SQL> drop public database link test2;Database link dropped.SQL> 

Of course, we can also not create the db link test2, which is mainly to migrate the intermediate table trans1 to the ginna database. There are many ways to do this, you can directly use exp/imp or data pump. There are many ways to migrate the base table sys. link $ on the seven end to the ginna end and replace sys. link $ on the ginna end.