一个stream试验-pre_instantiation_setup/post_instantiation_setup 模仿piner的全库stream试验

原创 于 2011-04-12 22:14:00 发布 · 1.2k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#instantiation #stream #database #command #disk #system

本文详细记录了使用Oracle RMAN工具进行数据库复制的过程，并介绍了如何利用Oracle Streams实现数据库之间的实时数据同步。从预处理阶段到复制后的配置调整，再到流复制的设置，提供了丰富的实践经验和技巧。

 

虚拟机 内存各2G，单个cpu ，然后Copy os.

删除smartd和sendmail. /etc/rc1.2.3.4.5.d

 

加新硬盘 －挂接到

Fdisk->mkfs

mount /dev/sdb1 /u01/app/oracle/flash_recovery_area

chown oracle:oinstall /u01/app/oracle/flash_recovery_area

/etc/fstab

/dev/sdb1               /u01/app/oracle/flash_recovery_area     ext2    defaults        0       2

网卡重新探测

网络设备控制-->设备-->硬件设备-->探测

重新激活.

修改IP和hostname

测试网络ok

 

Oracle & root

/home/.bashrc -->unset LANG

 

Oracle archive configure:

SQL>shutdown immediate

SQL>startup mount
SQL>alter database archivelog

 

Oracle backup strategy---还没有设置

 

Oracle parameter

global_names:true

alter system set global_names=true;

Db_link format db_name.domain.

alter system set db_domain=net scope=spfile;shutdown immediate-->startup

Compatible 10.2.0 above

alter system set compatible='10.2.0.4.0' scope=spfile;--->中途改的 不知道有没有问题

Job_queue_processes:>=5

Alter system set job_queue_processes=5

Aq_tm_processes

Alter system set aq_tm_processes=4

Sga_target 600m

Spfile..

规划 数据库2 str2 数据库3 str3

 

2 拷贝os两份，加上原来的一共三分.

1.111 rh1 orcl

1.112 rh2 strm2

1.113 rh3 strm3

3 pre work

Create tablespace strm_tbs datafile '/u01/app/oracle/oradata/orcl/strms01.dbf' size 500m;

没有空间了，删除安装文件--->database & Disk1 (patch10204)

Create user strmadmin identified by strmadmin default tablespace strm_tbs quota unlimited on strm_tbs;

grant dba to strmadmin;

建立网络参数

$ORACLE_HOME/network/admin/tnsnames.ora

STRM2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rh2)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = strm2)

    )

  )

 

STRM3 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rh3)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = strm3)

    )

  )

 

Connect strmadmin/strmadmin

Create database link strm2.net connect to strmadmin identified by strmadmin using 'STRM2';

alter database rename global_name to orcl.net;

SQL> select * from global_name;

 

GLOBAL_NAME

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

ORCL.NET

 

Declare

Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

Begin

Dbms_streams_adm.pre_instantiation_setup(

Maintain_mode=>'GLOBAL',

Tablespace_names=>empty_tbs,

Source_database=>'orcl.net',

Destination_database=>'strm2.net',

Perform_actions=>true,

Bi_directional=>true,

Include_ddl=>true,

Startup_processes=>false,

Exclude_schemas=>null,

Exclude_flags=>dbms_streams_adm.exclude_flags_unsupported+dbms_streams_adm.exclude_flags_dml+dbms_streams_adm.exclude_flags_ddl);

End;

/

ERROR at line 4:

ORA-06550: line 4, column 2:

PLS-00306: wrong number or types of arguments in call to

'PRE_INSTANTIATION_SETUP'

ORA-06550: line 4, column 2:

PL/SQL: Statement ignored

 

DECLARE

empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;

BEGIN

DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(

maintain_mode =>'GLOBAL',

tablespace_names => empty_tbs,

source_database =>'orcl.net',

destination_database =>'strm2.net',

perform_actions => true,

bi_directional => true,

include_ddl => true,

start_processes => true,

exclude_schemas => NULL,

exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED+

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

END;

/

 

Pasted from <http://www.ningoo.net/html/2007/oracle10gr2_streams_database_level_replication_1.html>

 

Good this time, long time to run.

 

10 minutes. Jj

 

Rman备份

Backup database plus archivelog delete input;

5 minutes;

 

SQL> set serveroutput on size 1000000

declare

u number;

begin

u:=dbms_flashback.get_system_change_number;

dbms_output.put_line(u);

end;

/

1102934

 

Archive again

alter system archive log current;

 

拷贝备份+归档到远程同样的路径下.

chown -R oracle:oinstall /u01/app/oracle/flash_recovery_area

 

源文件压缩一下再传输

 

sftp传输

Gunzip

Tar xvf

 

Get initorcl.ora , mv initstrm1.ora

Update, orcl --> strm2

 

目标数据库nomount

 

Update .bash_profile sid=strm2

 

mkdir -p /u01/app/oracle/admin/strm2/udump

mkdir -p /u01/app/oracle/admin/strm2/adump

mkdir -p /u01/app/oracle/oradata/strm2/

mkdir -p /u01/app/oracle/admin/strm2/cdump

 recovery的没有改

 

Create spfile from pfile;

Startup nomount

 

删除一些不必要的空间Disk1.

 

第二台机器的原来orcl数据库删除.

3.9G空余 可以进行

 

bdump没有的话，

SQL> startup nomount

ORA-00444: background process "PMON" failed while starting

ORA-07446: sdnfy: bad value '' for parameter .

 

建立之

 

lsnrctl启动不来

 

修改后ok, listener的服务为xxx.net

 

tnsping strm2

 

conn sys/sys@orcl as sysdba

 

SQL> conn sys/sys@strm2 as sysdba

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

 

到2的机器上，一样的

SQL> conn sys/sys@strm2 as sysdba

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

 

动态注册总是搞不明白，改成静态注册了.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

lisnetr中总是显示unkown

 

这次第二台上显示权限不足了

[oracle@rh2 admin]$ sqlplus sys/sys@strm2 as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 29 22:03:39 2011

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

ERROR:

ORA-01031: insufficient privileges

 

cp orapworcl orapwstrm2

 

Works now!!!---密码文件可以拷贝！

 

run

{ 

set until scn 1102934;

duplicate target database to strm2 open restricted;    

}

 

executing command: SET until clause

using target database control file instead of recovery catalog

 

Starting Duplicate Db at 29-MAR-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 03/29/2011 22:15:11

RMAN-05501: aborting duplication of target database

RMAN-05001: auxiliary filename /u01/app/oracle/oradata/orcl/strms01.dbf conflicts with a file used by the target database

 

需要转换.

 

改这种参数还有其他办法

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  629145600 bytes

Fixed Size                  1269040 bytes

Variable Size             239076048 bytes

Database Buffers          385875968 bytes

Redo Buffers                2924544 bytes

SQL> alter system set LOG_FILE_NAME_CONVERT='orcl','strm2';

alter system set LOG_FILE_NAME_CONVERT='orcl','strm2'

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

 

SQL> alter system set LOG_FILE_NAME_CONVERT='orcl','strm2' scope=spfile;

 

System altered.

 

SQL> alter system set DB_FILE_NAME_CONVERT='orcl','strm2' scope=spfile;

 

System altered.

 

SQL> shutdown immediate

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  629145600 bytes

Fixed Size                  1269040 bytes

Variable Size             239076048 bytes

Database Buffers          385875968 bytes

Redo Buffers                2924544 bytes

Database mounted.

Database opened.

 

或者还有其他办法－如pfile转换.

 

目的数据库改，不是源改参数

 

改两个convert参数，然后继续备份

 

终于开始干活了!!!!!

 

RMAN> connect target /

 

connected to target database: ORCL (DBID=1180319985)

 

RMAN> connect auxiliary sys/sys@strm2

 

connected to auxiliary database: STRM2 (not mounted)

 

RMAN> run

2> {

3> set until scn 1102934;

4> duplicate target database to strm2 nofilenamecheck open restricted;

5> }

 

executing command: SET until clause

using target database control file instead of recovery catalog

 

Starting Duplicate Db at 29-MAR-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

 

contents of Memory Script:

{

   set until scn  1102934;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/strm2/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/strm2/undotbs01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/strm2/sysaux01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/strm2/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/strm2/example01.dbf";

   set newname for datafile  6 to

 "/u01/app/oracle/oradata/strm2/strms01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 29-MAR-11

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/strm2/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/strm2/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/strm2/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/strm2/users01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/strm2/example01.dbf

restoring datafile 00006 to /u01/app/oracle/oradata/strm2/strms01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_nnndf_TAG20110329T200152_6s3lkrg2_.bkp

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_nnndf_TAG20110329T200152_6s3lkrg2_.bkp tag=TAG20110329T200152

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:35

Finished restore at 29-MAR-11

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "STRM2" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '/u01/app/oracle/oradata/strm2/redo01.log' ) SIZE 50 M  REUSE,

  GROUP  2 ( '/u01/app/oracle/oradata/strm2/redo02.log' ) SIZE 50 M  REUSE,

  GROUP  3 ( '/u01/app/oracle/oradata/strm2/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/strm2/system01.dbf'

 CHARACTER SET ZHS16GBK

 

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

released channel: ORA_AUX_DISK_1

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=747096831 filename=/u01/app/oracle/oradata/strm2/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=747096831 filename=/u01/app/oracle/oradata/strm2/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=747096831 filename=/u01/app/oracle/oradata/strm2/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=747096832 filename=/u01/app/oracle/oradata/strm2/example01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=5 stamp=747096832 filename=/u01/app/oracle/oradata/strm2/strms01.dbf

 

contents of Memory Script:

{

   set until scn  1102934;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 29-MAR-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

 

starting media recovery

 

archive log thread 1 sequence 48 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_29/o1_mf_1_48_6s3lndrk_.arc

archive log thread 1 sequence 49 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_29/o1_mf_1_49_6s3lxz5j_.arc

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_29/o1_mf_1_48_6s3lndrk_.arc thread=1 sequence=48

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_29/o1_mf_1_49_6s3lxz5j_.arc thread=1 sequence=49

media recovery complete, elapsed time: 00:00:03

Finished recover at 29-MAR-11

 

contents of Memory Script:

{

   shutdown clone;

   startup clone nomount ;

}

executing Memory Script

 

 

 

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 03/29/2011 23:54:15

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

…..后续还有脚本吗?

 

 

修复

Strm2:

没有recover backup control file  until cancel. 直接SQL> alter database open resetlogs;

 

另外一种方法

 

Strms2

SQL> conn strmadmin/strmadmin

Connected.

SQL> create database link orcl.net connect to strmadmin identified by strmadmin using 'orcl';

 

Database link created.

 

SQL> conn / as sysdba

Connected.

SQL> create database link orcl.net connect to strmadmin identified by strmadmin using 'orcl';

 

Database link created.

 

SQL> drop database link strm2.net;

drop database link strm2.net

                           *

ERROR at line 1:

ORA-02082: a loopback database link must have a connection qualifier

 

可以先改global name 再删本地link名字，防止拷贝过来的出错。

 

测试两个数据库都用

SQL> alter database rename global_name to strm2.net;

 

Database altered.

 

SQL>

SQL> select instance_name from v$instance@orcl.net;

 

INSTANCE_NAME

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

orcl

 

SQL> select instance_name from v$instance@strm2.net;

 

INSTANCE_NAME

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

strm2

 

SQL> select instance_name from v$instance@strm2

  2  ;

 

INSTANCE_NAME

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

Strm2

 

最后一步复制post

Declare

Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

Begin

Dbms_streams_adm.post_instantiation_setup(

Maintain_mode=>'GLOBAL',

tablespace_names => empty_tbs,

Source_database=>'orcl.net',

Destination_database=>'strm2.net',

Perform_actions=>true,

Bi_directional=>true,

Include_ddl=>true,

Start_processes=>true,

Instantiation_scn=>1102933,

Exclude_schemas=>null,

exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED+

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

End;

/

 

SQL>    Declare

  2     Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

  3     Begin

  4     Dbms_streams_adm.post_instantiation_setup(

  5     Maintain_mode=>'GLOBAL',

  6     tablespace_names => empty_tbs,

  7     Source_database=>'orcl.net',

  8     Destination_database=>'strm2.net',

  9     Perform_actions=>true,

 10     Bi_directional=>true,

 11     Include_ddl=>true,

 12     Start_processes=>true,

 13     Instantiation_scn=>1102933,

 14     Exclude_schemas=>null,

 15     exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED+

 16     DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +

 17     DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

 18     End;

 19     /

        Declare

*

ERROR at line 1:

ORA-23616: Failure in executing block 1 for script

9FADA373DFD57D12E040A8C06F016F44

ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 560

ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 583

ORA-06512: at "SYS.DBMS_STREAMS_MT", line 8333

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2831

ORA-06512: at line 4

 

SQL> select * from dba_recoverable_script_errors

  2  ;

 

SCRIPT_ID                         BLOCK_NUM ERROR_NUMBER

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

ERROR_MESSAGE

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

ERROR_CRE

---------

9FADA373DFD57D12E040A8C06F016F44          1       -25153

ORA-25153: Temporary Tablespace is Empty

30-MAR-11

 

Alter database backup controlfile to trace

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/strm2/temp01.dbf'

  2  SIZE 31457280  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

 

又出错

SQL> select * from dba_recoverable_script_errors;

 

SCRIPT_ID                         BLOCK_NUM ERROR_NUMBER

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

ERROR_MESSAGE

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

ERROR_CRE

---------

9FADA373DFD57D12E040A8C06F016F44         22          100

ORA-01403: no data found

30-MAR-11

 

9FADA373DFD57D12E040A8C06F016F44         22          100

ORA-01403: no data found

30-MAR-11

 

SCRIPT_ID                         BLOCK_NUM ERROR_NUMBER

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

ERROR_MESSAGE

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

ERROR_CRE

---------

SQL> col forward_block heading 'Forward Block' format A50

SQL> col forward_block_dblink heading 'Forward Block|Database Link' format A13

SQL> col status heading 'Status' format A12

 

SQL> select forward_block,forward_block_dblink,status from dba_recoverable_script_blocks

  2  where script_id='9FADA373DFD57D12E040A8C06F016F44' and block_num=22;

 

                                                   Forward Block

Forward Block                                      Database Link Status

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

--                                                 ORCL.NET      ERROR

-- Enable propagation schedule for "SYS"."ORCL$CAP

Q"

-- to STRM2.NET

--

DEC

 

2011-3-30

再次来过.

同步的时候有点问题。

1.停止，先capture，再propagation，再apply

Orcl:

SQL> select capture_name,queue_name,status from dba_capture;

 

CAPTURE_NAME                   QUEUE_NAME                     Status

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

ORCL$CAP                       ORCL$CAPQ                      ENABLED

Exec dbms_capture_adm.stop_capture('ORCL$CAP');

Exec dbms_capture_adm.drop_capture('ORCL$CAP');

 

Orcl:propagation

SQL> select propagation_name,status from dba_propagation;

 

PROPAGATION_NAME               Status

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

PROPAGATION$_18                DISABLED

 

Exec dbms_propagation_adm.stop_propagation('PROPAGATION$_18');

Exec  dbms_propagation_adm.drop_propagation('PROPAGATION$_18');

 

Orcl:apply

 

Strm2:

 

SQL> select apply_name,queue_name,status from dba_apply;

 

APPLY_NAME                     QUEUE_NAME                     STATUS

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

APPLY$_ORCL_28                 ORCL$APPQ                      ENABLED

SQL> Exec dbms_apply_adm.stop_apply('APPLY$_ORCL_28');

 

PL/SQL procedure successfully completed.

 

最后删除所有配置

SQL> exec dbms_apply_adm.drop_apply('APPLY$_ORCL_28');

 

PL/SQL procedure successfully completed.

 

exec dbms_streams_adm.remove_streams_configuration;

 

重新做一遍  -- 还有一个案例（现有的库如何复制)

strmadmin不用删

第二个库删除,

rm一顿…

 

Pre

SQL> conn strmadmin/strmadmin

Connected.

SQL>    DECLARE

  2     empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;

  3     BEGIN

  4     DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(

  5     maintain_mode =>'GLOBAL',

  6     tablespace_names => empty_tbs,

  7     source_database =>'orcl.net',

  8     destination_database =>'strm2.net',

  9     perform_actions => true,

 10     bi_directional => true,

 11     include_ddl => true,

 12     start_processes => true,

 13     exclude_schemas => NULL,

 14     exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED+

        DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +

 15   16        DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

 17     END;

 18     /

 

PL/SQL procedure successfully completed.

 

删除rman原来备份－空间不够了

List backup set

Delete backupset

再次rman备份

Backup database plus archivelog delete input;

 

declare

u number;

begin

u:=dbms_flashback.get_system_change_number;

dbms_output.put_line(u);

end;

/

1210514

 

alter system archive log current;

 

拷贝文件 － 备份＋归档

 

Rman dumplicate

 

strm2启动到nomount

然后启动监听

 

run

{ 

set until scn 1210514;

duplicate target database to strm2 open restricted;    

}

 

看下完整的这次记录

RMAN>   run

2>      { 

3>      set until scn 1210514;

4>      duplicate target database to strm2 open restricted;    

5>      }

 

executing command: SET until clause

 

Starting Duplicate Db at 30-MAR-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

 

contents of Memory Script:

{

   set until scn  1210514;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/strm2/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/strm2/undotbs01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/strm2/sysaux01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/strm2/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/strm2/example01.dbf";

   set newname for datafile  6 to

 "/u01/app/oracle/oradata/strm2/strms01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 30-MAR-11

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/strm2/system01.dbf

restoring datafile 00002 to /u01/app/oracle/oradata/strm2/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/strm2/sysaux01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/strm2/users01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/strm2/example01.dbf

restoring datafile 00006 to /u01/app/oracle/oradata/strm2/strms01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_03_30/o1_mf_nnndf_TAG20110330T184437_6s62drqc_.bkp

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_03_30/o1_mf_nnndf_TAG20110330T184437_6s62drqc_.bkp tag=TAG20110330T184437

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:19

Finished restore at 30-MAR-11

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "STRM2" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '/u01/app/oracle/oradata/strm2/redo01.log' ) SIZE 50 M  REUSE,

  GROUP  2 ( '/u01/app/oracle/oradata/strm2/redo02.log' ) SIZE 50 M  REUSE,

  GROUP  3 ( '/u01/app/oracle/oradata/strm2/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/strm2/system01.dbf'

 CHARACTER SET ZHS16GBK

 

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

released channel: ORA_DISK_1

released channel: ORA_AUX_DISK_1

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=747169166 filename=/u01/app/oracle/oradata/strm2/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=747169166 filename=/u01/app/oracle/oradata/strm2/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=747169166 filename=/u01/app/oracle/oradata/strm2/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=747169166 filename=/u01/app/oracle/oradata/strm2/example01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=5 stamp=747169166 filename=/u01/app/oracle/oradata/strm2/strms01.dbf

 

contents of Memory Script:

{

   set until scn  1210514;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 30-MAR-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=159 devtype=DISK

 

starting media recovery

 

archive log thread 1 sequence 59 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_30/o1_mf_1_59_6s62ghyl_.arc

archive log thread 1 sequence 60 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_30/o1_mf_1_60_6s62l6rn_.arc

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_30/o1_mf_1_59_6s62ghyl_.arc thread=1 sequence=59

archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_03_30/o1_mf_1_60_6s62l6rn_.arc thread=1 sequence=60

media recovery complete, elapsed time: 00:00:02

Finished recover at 30-MAR-11

 

contents of Memory Script:

{

   shutdown clone;

   startup clone nomount ;

}

executing Memory Script

 

database dismounted

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area     629145600 bytes

 

Fixed Size                     1269040 bytes

Variable Size                239076048 bytes

Database Buffers             385875968 bytes

Redo Buffers                   2924544 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "STRM2" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '/u01/app/oracle/oradata/strm2/redo01.log' ) SIZE 50 M  REUSE,

  GROUP  2 ( '/u01/app/oracle/oradata/strm2/redo02.log' ) SIZE 50 M  REUSE,

  GROUP  3 ( '/u01/app/oracle/oradata/strm2/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/strm2/system01.dbf'

 CHARACTER SET ZHS16GBK

 

 

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/strm2/temp01.dbf";

   switch clone tempfile all;

   catalog clone datafilecopy  "/u01/app/oracle/oradata/strm2/undotbs01.dbf";

   catalog clone datafilecopy  "/u01/app/oracle/oradata/strm2/sysaux01.dbf";

   catalog clone datafilecopy  "/u01/app/oracle/oradata/strm2/users01.dbf";

   catalog clone datafilecopy  "/u01/app/oracle/oradata/strm2/example01.dbf";

   catalog clone datafilecopy  "/u01/app/oracle/oradata/strm2/strms01.dbf";

   switch clone datafile all;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed temporary file 1 to /u01/app/oracle/oradata/strm2/temp01.dbf in control file

 

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/strm2/undotbs01.dbf recid=1 stamp=747169179

 

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/strm2/sysaux01.dbf recid=2 stamp=747169179

 

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/strm2/users01.dbf recid=3 stamp=747169179

 

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/strm2/example01.dbf recid=4 stamp=747169179

 

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/strm2/strms01.dbf recid=5 stamp=747169179

 

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=747169179 filename=/u01/app/oracle/oradata/strm2/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=747169179 filename=/u01/app/oracle/oradata/strm2/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=747169179 filename=/u01/app/oracle/oradata/strm2/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=747169179 filename=/u01/app/oracle/oradata/strm2/example01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=5 stamp=747169179 filename=/u01/app/oracle/oradata/strm2/strms01.dbf

 

contents of Memory Script:

{

   sql clone 'alter system enable restricted session';

   Alter clone database open resetlogs;

}

executing Memory Script

 

sql statement: alter system enable restricted session

 

database opened

Finished Duplicate Db at 30-MAR-11

 

SQL> create database link orcl.net connect to strmadmin identified by strmadmin using 'orcl';

 

Database link created.

 

SQL> alter database rename global_name to test;

 

Database altered.

 

SQL> drop database link strm2.net;

 

Database link dropped.

 

SQL> alter database rename global_name to strm2.net;

 

Database altered.

 

校验ok

SQL> select instance_name from v$instance@strm2.net;

 

INSTANCE_NAME

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

strm2

 

SQL> select instance_name from v$instance@orcl.net;

 

INSTANCE_NAME

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

orcl

 

strm2的临时表空间

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/strm2/temp01.dbf'

SIZE 31457280  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

 

不知道为啥这次有了--上次duplicate没完成???

 

Ok, post过程.

 

Declare

Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

Begin

Dbms_streams_adm.post_instantiation_setup(

Maintain_mode=>'GLOBAL',

tablespace_names => empty_tbs,

Source_database=>'orcl.net',

Destination_database=>'strm2.net',

Perform_actions=>true,

Bi_directional=>true,

Include_ddl=>true,

Start_processes=>true,

Instantiation_scn=>1210513,

Exclude_schemas=>null,

exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED+

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL,

CAPTURE_QUEUE_USER=>'STRMADMIN',

APPLY_QUEUE_USER=>'STRMADMIN');

End;

/

 

一样的错误 -  no data found.

select forward_block,forward_block_dblink,status from dba_recoverable_script_blocks

where script_id='9FADA373DFD57D12E040A8C06F016F44' and block_num=22;

 

SQL> select forward_block,forward_block_dblink,status from dba_recoverable_script_blocks

  2  where script_id='9FADA373DFD57D12E040A8C06F016F44' and block_num=22;

 

Forward Block

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

Forward Block

Database Link Status

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

--

-- Enable propagation schedule for "SYS"."ORCL$CAPQ"

-- to STRM2.NET

--

DEC

ORCL.NET      ERROR

 

填了点数据继续

Begin

Dbms_streams_adm.recover_operation(

Script_id=>'9FADA373DFD57D12E040A8C06F016F44',

Operation_mode=>'FORWARD');

End;

/

 

(错误原因估计"SYS"."ORCL$CAPQ"错误，修正,strmadmin)

 

select * from dba_propagation

 

PROPAGATION_NAME               SOURCE_QUEUE_OWNER

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

SOURCE_QUEUE_NAME              DESTINATION_QUEUE_OWNER

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

DESTINATION_QUEUE_NAME

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

DESTINATION_DBLINK

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

RULE_SET_OWNER                 RULE_SET_NAME

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

NEGATIVE_RULE_SET_OWNER        NEGATIVE_RULE_SET_NAME         QUEUE STATUS

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

ERROR_MESSAGE

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

ERROR_DAT

---------

PROPAGATION$_32                STRMADMIN

ORCL$CAPQ                      STRMADMIN

ORCL$APPQ

STRM2.NET

STRMADMIN                      RULESET$_33

STRMADMIN                      RULESET$_36                    FALSE DISABLED

 

回滚

Begin

Dbms_streams_adm.recover_operation(

Script_id=>'9FADA373DFD57D12E040A8C06F016F44',

Operation_mode=>'PURGE');

End;

/

 

原因就是那个queue的owner不知道为啥错了

重新加入新的参数到post过程中

 

purge后

再次执行了一遍post

good!!

 

SQL> conn strmadmin/strmadmin

Connected.

SQL>    Declare

  2     Empty_tbs dbms_streams_tablespace_adm.tablespace_set;

  3     Begin

  4     Dbms_streams_adm.post_instantiation_setup(

  5     Maintain_mode=>'GLOBAL',

  6     tablespace_names => empty_tbs,

  7     Source_database=>'orcl.net',

  8     Destination_database=>'strm2.net',

  9     Perform_actions=>true,

 10     Bi_directional=>true,

 11     Include_ddl=>true,

 12     Start_processes=>true,

 13     Instantiation_scn=>1210513,

 14     Exclude_schemas=>null,

 15     exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED+

 16     DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +

 17     DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL,

 18     CAPTURE_QUEUE_USER=>'STRMADMIN',

 19     APPLY_QUEUE_USER=>'STRMADMIN');

 20     End;

 21     /

 

PL/SQL procedure successfully completed.

 

目标数据库解除restricted session.

alter system disable restricted session;

 

测试数据.

 

测试

2011-3-31

忽略无关紧要的错误:

Begin

Dbms_apply_adm.set_parameter(apply_name=>'APPLY$_STRM2_28',parameter=>'disable_on_error',value=>'N');

End;

/

Exec dbms_apply_adm.start_apply(apply_name=>'APPLY$_STRM2_28');

 

APPLY$_ORCL_42

Begin

Dbms_apply_adm.set_parameter(apply_name=>'APPLY$_ORCL_42',parameter=>'disable_on_error',value=>'N');

End;

/

Exec dbms_apply_adm.start_apply(apply_name=>'APPLY$_ORCL_42');

 

Good, 测试通过，很快!

评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值