来自农村的老实娃
分类: Oracle
2009-04-09 12:39:01
建议为streams的管理帐号创建独立表空间,方便管理的维护。
JSSWEB> create tablespace strmtbs datafile ¨/data/oradata/jssweb/strmtbs01.ora¨ size 300m;
Tablespace created.
JSSWEB> create user strmadmin identified by strmadmin default tablespace strmtbs quota unlimited on strmtbs;
User created.
JSSWEB> grant dba to strmadmin;
Grant succeeded.
JSSWEB> revoke unlimited tablespace from strmadmin;
Revoke succeeded.JSSWEB> conn strmadmin/strmadmin
Connected.
JSSWEB> create database link jssstr connect to strmadmin identified by strmadmin using ¨jssstr _172.25.13.231 ¨;
Database link created.提示:此时jssstr确实尚不存在,没有关系,PRE_INSTANTIATION_SETUP过程在执行时并不会验证数据库的有效性,因此仍然创建。
JSSWEB> conn strmadmin/strmadmin
Connected.
JSSWEB > 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 => ¨jssweb.jss.cn¨,
8 destination_database => ¨jssstr.jss.cn¨,
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 exclude_schemas => ¨strmadmin¨,
14 exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_ FULL +
15 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
16 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
17 END;
18 /
PL/SQL procedure successfully completed.下面逐条说明PRE_INSTANTIATION_SETUP各个参数 :
有如下可选值:
- DBMS_STREAMS_ADM.EXCLUDE_FLAGS_FULL :排除指定schemas中所有对象操作。
- DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED :排除指定schemas中不支持的对象操作。
- 上述两值仅能同时选一个,如果两个同时选择在配置时会抛出异常。同时还有下列两个附加选项:
- DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML :排除对不支持对象的DML操作。
- DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL :排除对不支持对象的DDL操作。
上述四个属性值可以通过"+"号连接来达到同时支持的目的。
比如,对于exclude_schemas参数中指定的schemas对象,复制其DML操作但不复制DDL操作,则设置exclude_flags参数值如下可满足要求:
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_FULL +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL又比如,对于exclude_schemas参数中指定的schemas不支持的对象,即不复制其DDL操作也不复制其DML操作,则设置如下即可:
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL提示:这个参数仅在maintail_mode设置为GLOBAL,并且exclude_schemas参数设置为非空值是有效,其它情况下即使设置也会自动忽略该参数值。
在本例中我们的设置相当于不复制strmadmin下的所有对象,也不同步对该schema下对象的操作。
[oracle@yans1 ~]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on 星期三 11月 26 11:17:37 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: JSSWEB (DBID=3439008274)
RMAN> run{
2> allocate channel c1 device type disk format ¨/data/backup/jssweb/%U¨;
3> backup database plus archivelog delete input;
4> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=126 devtype=DISK
Starting backup at 26-11 月-08
current log archived
......................
...........................
..................................
archive log filename=/data/oradata/jssweb/archivelog/1_41_671128850.dbf thread=1 sequence=41
Finished backup at 26-11 月-08
released channel: c1然后马上查看一下当前系统的scn,并且归档当前的redo
JSSWEB> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
192553
JSSWEB> alter system archive log current;
System altered.方式很多,过程不详述 。注意保持目录相同(如果不相同,需要通过rman catalog命令等重新注册备份文件,总之就是要保证rman duplicate执行时,要能找的到备份集和需要读取的归档文件)。
注意此处set until scn为前文操作中获取的scn。
[oracle@yans1 ~]$ rman target / auxiliary sys/tfad04@jssstr
Recovery Manager: Release 10.2.0.3.0 - Production on 星期三 11月 26 14:32:57 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: JSSWEB (DBID=3439008274)
connected to auxiliary database: JSSSTR (not mounted)
RMAN> run{
2> set until scn 192553;
3> duplicate target database to jssstr
4> DB_FILE_NAME_CONVERT=(jssweb,jssstr) LOGFILE
5> ¨/data/oradata/jssstr/redo01.log¨ SIZE 50M,
6> ¨/data/oradata/jssstr/redo02.log¨ SIZE 50M,
7> ¨/data/oradata/jssstr/redo03.log¨ SIZE 50M
8> open restricted;
9> }
executing command: SET until clause
using target database control file instead of recovery catalog
Starting Duplicate Db at 26-11 月-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
set until scn 192553 ;
set newname for datafile 1 to
"/data/oradata/jssstr/system01.dbf";
set newname for datafile 2 to
"/data/oradata/jssstr/undotbs01.dbf";
set newname for datafile 3 to
"/data/oradata/jssstr/sysaux01.dbf";
set newname for datafile 4 to
"/data/oradata/jssstr/users01.dbf";
set newname for datafile 5 to
"/data/oradata/jssstr/strmtbs01.ora";
restore
check readonly
clone database
;
}
executing Memory Script
...............
...................
.........................
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 26-11 月-08JSSSTR> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------------------
JSSWEB.JSS.CN如果不等于db_name+db_domain,则通过alter database rename命令修改:
JSSSTR> alter database rename global_name to jssstr.jss.cn;
数据库已更改。JSSSTR> conn strmadmin/strmadmin
已连接。
JSSSTR> create database link jssweb connect to strmadmin identified by strmadmin using ¨jssweb _172.25.13.229 ¨;
数据库链接已创建。不过由于jssstr数据库是由jssweb复制过来的,因此strmadmin用户下也存在一个jssstr.jss.cn的dblink,如果看其不爽可以将它删除,不过直接删除是不行的,肯定会报ORA-02082错误,因为不能删除与global_name同名的dblink。如果确实想删除该dblink,方法有二:
A>. 先修改global_name,再删除dblink
JSSSTR> alter database rename global_name to test.jss.cn;
数据库已更改。
JSSSTR> drop database link jssstr.jss.cn;
数据库链接已删除。
JSSSTR> alter database rename global_name to jssstr.jss.cn;
数据库已更改。
B>. 直接删除字典表
注:本方法由yangtingkun老大提供,号称其对drop database link做过精确的trace分析,确认该项操作实际就是delete sys.link$字典。
JSSSTR> conn / as sysdba
已连接。
JSSSTR> delete link$ where name=¨JSSSTR.JSS.CN¨;
已删除 1 行。
JSSSTR> commit;
提交完成。
JSSWEB> 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 => ¨jssweb.jss.cn¨,
8 destination_database => ¨jssstr.jss.cn¨,
9 perform_actions => true,
10 bi_directional => true,
11 include_ddl => true,
12 start_processes => true,
13 instantiation_scn => 192552 ,
14 exclude_schemas => ¨strmadmin¨,
15 exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_ FULL +
16 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
17 DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
18 END;
19 /
PL/SQL procedure successfully completed.如果没报错,至此,双向同步的streams整库复制环境就算完成了,最后,在目标端执行,取消受限连接:
JSSSTR> conn / as sysdba
已连接。
JSSSTR> ALTER SYSTEM DISABLE RESTRICTED SESSION;
系统已更改。