数据迁移：DataGuard配置-QX项目实战-19.跨库数据同步-Mysql EXPLAIN

当前位置: 数据库>其它

本页文章导读:

▪数据迁移：DataGuard配置本次快速搭建单实例物理DataGuard，为解决某些同事担心“那如果我在primary里面使用提交的事务操作时按照rowid去检索数据，到备库执行的和主库操作不一致” 数据库软件安装过程不再赘述。 .........

▪QX项目实战-19.跨库数据同步这是发自联调现场的文章。两套系统现在采用不同的数据结构来存储数据，故而需要使用一种办法来进行数据库的同步。这里我们采用存储过程来实现跨库的同步作业。即B库的B_table表的增删.........

▪Mysql EXPLAIN 使用EXPLAIN可以看到以下SQL的执行情况 EXPLAIN SELECT * FROM questionnaire WHERE user_id='wqef' Explain具体含义参见此链接：http://dev.mysql.com/doc/refman/5.1/en/using-explain.html 作者：kevin_Luan 发.........

[1]数据迁移：DataGuard配置

来源: 互联网发布时间: 2013-11-07

本次快速搭建单实例物理DataGuard，为解决某些同事担心“那如果我在primary里面使用提交的事务操作时按照rowid去检索数据，到备库执行的和主库操作不一致”

数据库软件安装过程不再赘述。

1: 检查主库否为 force logging .

select inst_id , force_logging from gv$database;
alter database force logging;

2:备库创建数据库实例

oradim -new -sid tjcshow -startmode a

3:配置主/备库tnsnames,listener

增加主库TNSNAMES节点：

TJCSHOW1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.111.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tjcshow)
    )
  )


TJCSHOW2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.111.131)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tjcshow)
    )
  )

将主库listener/SQLNET/TNSNAMES拷贝到备库待用

修改监听：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = tjcshow)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (SID_NAME = tjcshow)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = DKD-O13022802)(PORT = 1521))
    )
  )

4：主库生成pfile,拷贝到备库作为基础参数文件,修改主/备库pfile

---PRIMAY
*.log_archive_config=''
*.log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tjcshow'
*.log_archive_dest_2='service=tjcshow2 lgwr async  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tjcshow'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.log_archive_max_processes=1
*.fal_client='tjcshow1'
*.fal_server='tjcshow2'
*.DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\tjcshow','C:\oracle\product\10.2.0\oradata\tjcshow' 
*.LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\tjcshow','C:\oracle\product\10.2.0\oradata\tjcshow'
*.standby_file_management=auto

---STSNDBY

*.log_archive_config=''
*.log_archive_dest_1='LOCATION=C:\oracle\product\10.2.0\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tjcshow'
*.log_archive_dest_2='service=tjcshow1 lgwr async  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tjcshow'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.log_archive_max_processes=1
*.fal_client='tjcshow2'
*.fal_server='tjcshow1'
*.DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\tjcshow','C:\oracle\product\10.2.0\oradata\tjcshow' 
*.LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\tjcshow','C:\oracle\product\10.2.0\oradata\tjcshow'
*.standby_file_management=auto

5：节点联通性测试，节点1 tnsping tjcshow1 tnsping tjcshow2 节点2 tnsping tjcshow1 tnsping tjcshow2

startup nomount pfile=c:\standby.ora;

c:\oracle\product\10.2.0\db_1\BIN>TNSPING.EXE tjcshow1

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 13-MAR-2
013 09:23:20

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1
11.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tjcsh
ow)))
OK (0 msec)

c:\oracle\product\10.2.0\db_1\BIN>TNSPING.EXE tjcshow2

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 13-MAR-2
013 09:23:21

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1
11.131)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tjcsh
ow)))
OK (0 msec)

6:RMAN 备份主库

sql 'alter system archive log current' ;
backup as compressed backupset full format='c:\backupset-%d_%s.bak' database include current controlfile for standby plus archivelog ;

7：拷贝文件，还原备库

Set oracle_sid=tjcshow
Startup pfile=’c:\standby.ora’ nomount;
rman target  sys/oracle@tjcshow1  auxiliary sys/oracle@tjcshow2 
duplicate target database for standby dorecover nofilenamecheck;

8:添加Standby redo log 重做日志组配置

( 01) 主库添加Standby Redo Log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo004') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo005') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo006') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo007') SIZE 50M;

( 02) 备库添加Standby Redo Log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo004') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo005') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo006') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('C:\oracle\product\10.2.0\oradata\tjcsho\redo007') SIZE 50M;

9:启动到管理模式

shutdown immediate
startup nomount
alter database mount standby database
alter database recover managed standby database disconnect from session;

10:测试

@primary

--查看DG配置是否正确，主/备库查询：

select status,destination, error from v$archive_dest; 
VALID	C:\oracle\product\10.2.0\archivelog	
VALID	tjcshow2	
INACTIVE		
INACTIVE		
INACTIVE		
INACTIVE		
INACTIVE		
INACTIVE		
INACTIVE		
INACTIVE

--创建表空间，测试表进行测试

create tablespace rowidtbs datafile 'C:\oracle\product\10.2.0\oradata\tjcshow\rowidtbs01.dbf' size 100M;
create table rowidt (id int,name varchar(20)) tablespace rowidtbs;

--插入1000条数据

begin
for x  in 1..1000 loop
insert into rowidt values(x,'oracledg');
end loop;
end;
/

--强制数据库日志切换

alter system switch logfile;

--将备库从恢复模式置于只读模式：

alter database recover managed standby database cancel;
alter database open ;

--查询备库数据

select rowid,id,name from test
AAAMpBAABAAAOvaAAA	1	oracle    
AAAMpBAABAAAOvaAAB	2	oracle    
AAAMpBAABAAAOvaAAC	3	oracle    
AAAMpBAABAAAOvaAAD	4	oracle    
AAAMpBAABAAAOvaAAE	5	oracle    
aaampbaabaaaovaaaf	6	oracle    
aaampbaabaaaovaaag	7	oracle    
............................

1000条测试数据全部同步到备库，且ROWID没有发生变化。

PS：摘录 Data Guard Concepts and Administration

Physical standby database Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

所以主库到备库的后的ROWID不会发生变化，所以同事的担心没有必要。

作者：oracle_baidu 发表于2013-3-13 9:57:42 原文链接

阅读：66 评论：0 查看评论

[2]QX项目实战-19.跨库数据同步

来源: 互联网发布时间: 2013-11-07

这是发自联调现场的文章。两套系统现在采用不同的数据结构来存储数据，故而需要使用一种办法来进行数据库的同步。这里我们采用存储过程来实现跨库的同步作业。即B库的B_table表的增删改操作，同步到A库的A_table表。因为两个表的列名即类型大小存在不一致，所以需要在存储过程中进行调试。
--使用sp_helptext查看某触发器具体内容

--exec sp_helptext ‘triggerName';

--查看系统所有的触发器
--select * from sysobjects where xtype='TR'

USE [db_QX]

--删除触发器
drop trigger deleteB2A,insertB2A,updateB2A;

--创建新增记录之后同步的触发器

create trigger insertB2A
on B_table
after insert
as
declare @iiiii nvarchar(5)
declare @archivesno nvarchar(5)
declare @station nvarchar(30)
declare @latitude nvarchar(11)
declare @longitude nvarchar(13)
declare @altitude nvarchar(6)
declare @altitude_p nvarchar(6)
declare @height_windsensor nvarchar(4)
declare @height_windplatform nvarchar(4)
declare @builttime nvarchar(14)
select @iiiii=metadata_identifiers,@archivesno=station_file_number,
@station=station_referred_chinese,@latitude=latitude,
@longitude=longitude,@altitude=station_altitude,
@altitude_p=barometer_altitude,@height_windsensor=wind_speed_sensor_platform_height,
@height_windplatform=observation_platform_height,@builttime=tiem_up
from inserted
begin

--可以使用DB.dbo.Table的形式，访问其它数据库的信息
insert into A_Table(iiiii,archivesno,station,latitude,longitude,altitude,altitude_p,height_windsensor,height_windplatform,builttime,starttime,endtime) values(
@iiiii,@archivesno,@station,@latitude,@longitude,@altitude,@altitude_p,@height_windsensor,@height_windplatform,@builttime,@builttime,'99999999');
END

--删除记录之后同步的触发器

create trigger deleteB2A
on B_table
after delete
as declare @iiiii nvarchar(5)
select @iiiii=metadata_identifiers from deleted
begin
delete from A_Table where iiiii=@iiiii;
END

--修改记录之后同步的触发器

create trigger updateB2A
on B_table
after update
as
declare @iiiii nvarchar(5)
declare @archivesno nvarchar(5)
declare @station nvarchar(30)
declare @latitude nvarchar(11)
declare @longitude nvarchar(13)
declare @altitude nvarchar(6)
declare @altitude_p nvarchar(6)
declare @height_windsensor nvarchar(4)
declare @height_windplatform nvarchar(4)
declare @builttime nvarchar(14)
select @iiiii=metadata_identifiers,@archivesno=station_file_number,
@station=station_referred_chinese,@latitude=latitude,
@longitude=longitude,@altitude=station_altitude,
@altitude_p=barometer_altitude,@height_windsensor=wind_speed_sensor_platform_height,
@height_windplatform=observation_platform_height,@builttime=tiem_up
from inserted
begin
update A_Table set
iiiii=@iiiii,archivesno=@archivesno,station=@station,latitude=@latitude,
longitude=@longitude,altitude=@altitude,altitude_p=@altitude_p,
height_windsensor=@height_windsensor,height_windplatform=@height_windplatform,
builttime=@builttime
where iiiii=@iiiii;
END

--这是测试语句
insert into B_table (
metadata_identifiers,station_referred_chinese,tiem_up,metadata_update_time,WMORegionName,WMORegionNumber,countryAreaName)
values('55555','滕州','19991213','1111-11-11','亚洲','Ⅱ','中国');

delete from B_table where metadata_identifiers='55555';

update B_table set station_referred_chinese ='滕州A' where metadata_identifiers= '55555'

--这是查看代码语句

select * from B_table;
select * from A_Table;

需要注意的是：在查看触发器的时候，需要注意触发器的级别，如服务器、数据库、数据表级别的数据库。执行上述代码时，create trigger 语句必须在执行语句的第一句。另外由于这三个触发器内部使用的变量有重名现象，所以他们不能同时执行，应该顺序依次新建。

作者：gongqingkui 发表于2013-3-13 9:13:54 原文链接

阅读：82 评论：0 查看评论

[3]Mysql EXPLAIN

来源: 互联网发布时间: 2013-11-07

使用EXPLAIN可以看到以下SQL的执行情况

EXPLAIN SELECT * FROM questionnaire WHERE user_id='wqef'

Explain具体含义参见此链接：http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

作者：kevin_Luan 发表于2013-3-13 11:20:14 原文链接

阅读：57 评论：0 查看评论