We used to refresh our DEV database from production.so we need to preserve the DEV dblinks before we start refresh . we need to restore after refresh.
We can export the all the dblinks by using expdp.
Please create a para.par file with below commands.
full=y
INCLUDE=DB_LINK:"IN(SELECT db_link FROM dba_db_links)"
Then execute below command to export
expdp "'/as sysdba'" directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink-restore_JAYDEV.log parfile=para.par
After Refresh,Please execute below impdp command to recreate dblinks.
Note: DBLINK we are going to import should not exist in the target database(DEV).please drop dblinks if it already exists.
impdp "'/as sysdba'" directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink_restore_JAYDEV.log
ALTERNATE METHOD:
Before Refresh,We can take ddl of dblinks by using below query and spool it. Then we can connect to each schema and recreate the dblinks after refresh.
set long 100000 head off
spool exp_dblinks.dat
SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'PUBLIC') FROM DBA_DB_LINKS WHERE OWNER = 'PUBLIC';
SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'XXRA') FROM DBA_DB_LINKS WHERE OWNER = 'JAY';
We can export the all the dblinks by using expdp.
Please create a para.par file with below commands.
full=y
INCLUDE=DB_LINK:"IN(SELECT db_link FROM dba_db_links)"
Then execute below command to export
expdp "'/as sysdba'" directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink-restore_JAYDEV.log parfile=para.par
After Refresh,Please execute below impdp command to recreate dblinks.
Note: DBLINK we are going to import should not exist in the target database(DEV).please drop dblinks if it already exists.
impdp "'/as sysdba'" directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink_restore_JAYDEV.log
ALTERNATE METHOD:
Before Refresh,We can take ddl of dblinks by using below query and spool it. Then we can connect to each schema and recreate the dblinks after refresh.
set long 100000 head off
spool exp_dblinks.dat
SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'PUBLIC') FROM DBA_DB_LINKS WHERE OWNER = 'PUBLIC';
SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'XXRA') FROM DBA_DB_LINKS WHERE OWNER = 'JAY';
Not clear, if you're doing an export with full=y, why do you have to specify include="anything"?
ReplyDeleteIf one executes a "full=y" without specifying an "include", it exports the entire DB. If you want just one subset of the database, you can use "include" which acts as a filter to the "full" export - meaning it will export only the objects specified in the "include" statement. In this case "full=y" + "include=db_link" will make the export only be db links. If all you want are db links, and your DB is 10TB in size, then this will ignore everything but the few MB worth of metadata that make up the DB links for your export.
DeleteConversely there's an "exclude" as well, which, when combined with full=y does exactly what you'd expect - export the entire database, minus any objects specified in exclude statements.
Thank you, was looking for this.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete