ORACLE11G 流复制环境常用script

CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/dev/raw/raw9' SIZE 40M BLOCKSIZE 512,
GROUP 2 '/dev/raw/raw11' SIZE 40M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/dev/raw/raw4',
'/dev/raw/raw5',
'/dev/raw/raw7',
'/dev/raw/raw8'
CHARACTER SET US7ASCII
/////////////// own online redo log files
CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/app/oracle/oracle/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/app/oracle/oracle/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/app/oracle/oracle/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/app/oracle/oracle/system01.dbf',
'/opt/app/oracle/oracle/sysaux01.dbf',
'/opt/app/oracle/oracle/undotbs01.dbf',
'/opt/app/oracle/oracle/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
______________________________________________________________________
CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/dev/raw/raw9' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/dev/raw/raw10' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/dev/raw/raw11' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/dev/raw/raw4',
'/dev/raw/raw5',
'/dev/raw/raw7',
'/dev/raw/raw8'
CHARACTER SET WE8MSWIN1252
ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/raw/raw6' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
_______________________________________________________________________
ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORACLE/archivelog/2011_10_16/o1_mf_1_2_79mhgtcs_.arc';
ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORACLE/a
rchivelog/2011_10_16/o1_mf_1_1_%u_.arc';
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/app/oracle/oracle/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
#################################
### no online redo log file
CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/app/oracle/oracle/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/app/oracle/oracle/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/app/oracle/oracle/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFI

LE
'/opt/app/oracle/oracle/system01.dbf',
'/opt/app/oracle/oracle/sysaux01.dbf',
'/opt/app/oracle/oracle/undotbs01.dbf',
'/opt/app/oracle/oracle/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORACLE/a
rchivelog/2011_10_16/o1_mf_1_1_%u_.arc';
ALTER DATABASE REGISTER LOGFILE '/opt/app/oracle/flash_recovery_area/ORACLE/a
rchivelog/2011_10_16/o1_mf_1_1_%u_.arc';
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/app/oracle/oracle/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
run{
allocate channel t1 type disk;
backup database include current controlfile;
alter database switch all logfile;
release channel t1;
}
begin
dbms_aqadm.alter_propagation_schedule(
queue_name=>'SOURCE_QUEUE',
destination=>'ora29',
latency=>0);
end;
/
exp userid='system/oracle@ora28' system file='/usr/tmple/src.dbf' log='/usr/tmple/src.log' object_consistent=y statistics=noe rows=no
imp userid=system/oracle@ora29 full=y constraints=y file='/usr/tmpple/src.dbf' log='/usr/tmple/src.log' streams_instantiation=y ignore=y
set serveroutput on
declare
iscn number;
begin
iscn:=dbms_flashback.get_system_change_number();
dbms_output.put_line('Instantiation scn is:' || iscn);
end;
/
exec dbms_propagation_adm.start_propagation('SOURCE_TO_TARGET');
exec dbms_propagation_adm.stop_propagation('SOURCE_TO_TARGET');
begin
dbms_capture_adm.start_capture(
capture_name=>'ORACLE$CAP');
end;
/
begin
dbms_capture_adm.stop_capture(
capture_name=>'capture_stream');
end;
/
begin
dbms_capture_adm.drop_capture(
capture_name=>'capture_stream');
end;
/
create directory DIR_SRC1 as '/usr/tmple/dir_src1';
create tablespace DAVE datafile '/opt/app/oracle/oracle/dave.dbf' size 100m;
maintain_TTS
declare
v_tables dbms_utility.uncl_array;
begin
v_tables(1):='tianle.test1';
v_tables(2):='tianle.test2';
dbms_streams_adm.maintain_tables(
table_names=>v_tables,
source_directory_object=>null,
destination_directory_object=>null,
source_database=>'oracle',
destination_database=>'ora29',
perform_actions=>true,
bi_directional=>false,
include_ddl=>true,
instantiation=>dbms_streams_adm.instantiation_table_network);
end;
/
declare
v_script_id varchar2(32);
begin
select script_id into v_script_id from dba_recoverable_script;
dbms_streams_adm.RECOVER_OPERATION(v_script_id,'PURGE');
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINe('no data found');
end;
/
select invoking_package_owner as owner ,
invoking_package as package ,
invoking_procedure as procedure ,
status ,total_blocks ,done_block_num
from dba_recoverable_script
where script_id='AFB45D991F4496C5E040A8C01C014580';
select forward_block,forward_block_

dblink
from dba_recoverable_script_blocks
where script_id='AFB45D991F4496C5E040A8C01C014580'
and block_num=11;
select error_number ,error_message from
dba_recoverable_script_errors
where script_id='AFB45D991F4496C5E040A8C01C014580'
and block_num=11;
select apply_name ,local_transaction_id,
source_transaction_id ,error_message from dba_apply_error;
exp userid=system/oracle@oracle owner=system file=/usr/tmple/exp01.dbf object_consistent=y statistics=none rows=no