虚拟机 内存各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, 测试通过,很快!