1、配置数据库监听和.Net 服务名一、A 为主服务器，B 为备份服务器（在 B 服务器安装 oracle 的时候重命名下数据库，避免和 LMTIS 重名而冲突）在 A 服务器上打开 oracle 自带工具 Net Configuration Assistant1、新建 LISTENERNEW 监听器，端口默认 1521，如下所示：SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:oracleproduct10.1.0Db_1)(PROGRAM = extproc)LISTENERNEW
2、 =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = yy-44126be2998b)(PORT = 1521)LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = yy-44126be2998b)(PORT = 1521)SID_LIST_LISTENERNEW =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = LMTIS)(ORACLE_HOME = D:oracleproduct1
3、0.2.0db_1)(SID_NAME = LMTIS)2、新建本地 NET 服务名配置，服务名为 B 服务器数据库名，主机名为 B 的 IP。端口默认1521。这样，在 A 服务器oracleproduct10.1.0db_2NETWORKADMIN 下的 tnsnames.ora中如下所示：LMES =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.77)(PORT = 1521)(CONNECT_DATA =(SERVICE_NAME = LMES)LMTIS =(DESCRIPTION
4、=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)(CONNECT_DATA =(SERVICE_NAME = LMTIS)配置完成，从 A 服务器上打开 PLSQL Developer，就可以选择登录 A 的数据库和 B 的数据库了创建数据库连接，跨数据库建立视图二、创建 A,B 数据库连接，和表触发器（以下全部在 A 服务器数据库 LMTIS 中创建）1、创建 database link 跨数据库建立视图 - Create database link create public datab
5、ase link DBLINKTESTconnect to SYSTEM identified by newqtusing LMES; -DBLINKTEST 触发器名称-SYSTEM 数据库用户名-newqt 数据库密码-LMES 为远程数据库 B 的 SID2、创建成功后可在 sqlplus 中查看异地数据库信息，执行 select * from TEST_STURECORDDBLINKTEST触发器：触发器名称：updatedb_TEST_STURECORD表名：TEST_STURECORD触发器含有的方法：insert or update or deletecreate or repl
6、ace trigger updatedb_TEST_STURECORDbefore insert or update or delete on TEST_STURECORDfor each rowbeginif updating thenupdate TEST_STURECORDDBLINKTEST set STUR_ID =:New.STUR_ID, stur_where=:New.stur_where where STUR_ID=:Old.STUR_ID;end if; if inserting theninsert into TEST_STURECORDDBLINKTEST(STUR_I
7、D,STU_UNP_ID,STUR_STIME,stur_where,STUR_WORK,STUR_FTIME) values (:New.STUR_ID,:New.STU_UNP_ID,:New.STUR_STIME,:New.stur_where,:New.STUR_WORK,:New.STUR_FTIME);end if;if deleting thendelete from TEST_STURECORDDBLINKTEST where STUR_ID=:Old.STUR_ID;end if;end;测试语句：select * from TEST_STURECORDDBLINKTEST; select * from TEST_STURECORD;insert into TEST_STURECORD(STUR_ID,STU_UNP_ID,STUR_STIME,stur_where,STUR_WORK,STUR_FTIME) values (2,3,09:00,测试 111,服务员 11,15:00);commit;delete from TEST_STURECORD where STUR_ID=2;commit;update TEST_STURECORD set stur_where=阿萨德撒 where STUR_ID=1;commit;
