ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用EM设置流生成的脚本源文件(2)

利用EM设置流生成的脚本源文件(2)

原创 Linux操作系统 作者:tcczxhj 时间:2009-03-06 17:12:44 0 删除 编辑

------script2----Export_Import.sql------------------------------------------------------------

ACCEPT strm_pwd_src PROMPT 'Enter Password of Streams Admin "strmadmin" at Source : ' HIDE 

 ACCEPT strm_pwd_dest PROMPT 'Enter Password of Streams Admin "strmadmin" at Destination : ' HIDE 

connect "STRMADMIN"/&strm_pwd_dest@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.16.206.6)(PORT=1521)))(CONNECT_DATA=(SID=TCCZ)(SERVER=DEDICATED)))";

set serverout on;

DECLARE

  handle1 number;

  ind number;

  percent_done number;

  job_state VARCHAR2(30);

  le ku$_LogEntry;

  js ku$_JobStatus;

  jd ku$_JobDesc;

  sts ku$_Status;

BEGIN  

  handle1 := DBMS_DATAPUMP.OPEN('IMPORT','SCHEMA', 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM');

  DBMS_DATAPUMP.ADD_FILE(handle1, 'StreamImport_1220083929948.log', 'DATA_PUMP_DIR', '',  DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  DBMS_DATAPUMP.SET_PARAMETER(handle1, 'FLASHBACK_SCN', 650972);

   DBMS_DATAPUMP.METADATA_FILTER(handle1, 'SCHEMA_EXPR', 'IN (''CF123'',''FS3I'',''GZHB'')');

  DBMS_DATAPUMP.SET_PARAMETER(handle1, 'INCLUDE_METADATA', 1);

  DBMS_DATAPUMP.START_JOB(handle1); 

  percent_done :=0;

  job_state := 'UNDEFINED';

  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop

  dbms_datapump.get_status(handle1, dbms_datapump.ku$_status_job_error + dbms_datapump.ku$_status_job_status + dbms_datapump.ku$_status_wip,-1,job_state,sts);

  js := sts.job_status;

  if js.percent_done != percent_done

  then

     dbms_output.put_line('*** Job percent done = ' || to_char(js.percent_done));

     percent_done := js.percent_done;

  end if;

  if(bitand(sts.mask, dbms_datapump.ku$_status_wip) != 0)

  then

    le := sts.wip;

  else

     if(bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)

     then

       le := sts.error;

     else

       le := null;

     end if;

  end if;

  if le is not null

  then

    ind := le.FIRST;

    while ind is not null loop

      dbms_output.put_line(le(ind).LogText);

      ind := le.NEXT(ind);

    end loop;

  end if;

  end loop;

  dbms_output.put_line('Job has completed');

  dbms_output.put_line('Final job state = ' || job_state);

  dbms_datapump.detach(handle1);

END;  

/

------script3------Startup.sql------

ACCEPT strm_pwd_src PROMPT 'Enter Password of Streams Admin "strmadmin" at Source : ' HIDE 

 ACCEPT strm_pwd_dest PROMPT 'Enter Password of Streams Admin "strmadmin" at Destination : ' HIDE 

connect "STRMADMIN"/&strm_pwd_dest@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.16.206.6)(PORT=1521)))(CONNECT_DATA=(SID=TCCZ)(SERVER=DEDICATED)))";

set serverout on;

BEGIN

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

    source_schema_name   => '"CF123"',

   source_database_name => 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM',

   instantiation_scn    => 650974,

   recursive            => true);

END;

/

BEGIN

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

    source_schema_name   => '"FS3I"',

   source_database_name => 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM',

   instantiation_scn    => 650974,

   recursive            => true);

END;

/

BEGIN

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

    source_schema_name   => '"GZHB"',

   source_database_name => 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM',

   instantiation_scn    => 650974,

   recursive            => true);

END;

/

DECLARE

   v_started number;

BEGIN

SELECT DECODE(status, 'ENABLED', 1, 0) INTO v_started

 FROM DBA_APPLY where apply_name = 'STREAMS_APPLY';

 if (v_started = 0) then

  DBMS_APPLY_ADM.START_APPLY(apply_name => '"STREAMS_APPLY"');

 end if;

END;

/

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

connect "STRMADMIN"/&strm_pwd_src;

set serverout on;

DECLARE

   v_started number;

BEGIN

SELECT DECODE(status, 'ENABLED', 1, 0) INTO v_started

 FROM DBA_CAPTURE where CAPTURE_NAME = 'STREAMS_CAPTURE';

 if (v_started = 0) then

  DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => '"STREAMS_CAPTURE"');

 end if;

END;

/

BEGIN

DBMS_OUTPUT.PUT_LINE('*** Progress Message ===> Started the capture process STREAMS_CAPTURE at source database tczf and the apply process STREAMS_APPLY at the destination database successfully. ***');

END;

/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10804699/viewspace-563102/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 与ORACLE
请登录后发表评论 登录
全部评论

注册时间:2009-03-06

  • 博文量
    47
  • 访问量
    101554