达梦 8 使用 dmhs 实现 DM 8 — DM 8数据双向与单向同步

[root@dba ~]# mv dmhs_V4.3.32_dm8_rev163952_rh6_64_20240710.bin /home/dmdba

[root@dba ~]# chown dmdba:dinstall /home/dmdba/dmhs

dmhs/

dmhs_V4.3.32_dm8_rev163952_rh6_64_20240710.bin

[root@dba ~]# chown dmdba:dinstall /home/dmdba/dmhs_V4.3.32_dm8_rev163952_rh6_64_20240710.bin

[root@dba ~]# chmod 755 /home/dmdba/dmhs_V4.3.32_dm8_rev163952_rh6_64_20240710.bin

2.1 图形方式安装dmhs

[dmdba@dba ~]$ export DISPLAY=192.168.6.1:0.0

[dmdba@dba ~]$ pwd

/home/dmdba

[dmdba@dba ~]$ ./dmhs_V4.3.32_dm8_rev163952_rh6_64_20240710.bin

3 准备数据库环境

注：节点一和节点二做相同的操作

3.1 开归档

Alter database mount;

alter database archivelog;

alter database add archivelog 'DEST=/home/dmdba/arc, TYPE=LOCAL, FILE_SIZE=64, SPACE_LIMIT=0, ARCH_FLUSH_BUF_SIZE=0';

Alter database open;

SQL> select arch_mode from v$database;

行号 ARCH_MODE

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

1 Y

3.2 开启逻辑日志

SQL> SP_SET_PARA_VALUE(1,'RLOG_APPEND_LOGIC',1);

DMSQL 过程已成功完成

已用时间: 16.797(毫秒). 执行号:1904.

SQL> select para_value from v$dm_ini where para_name in ('RLOG_APPEND_LOGIC');

行号 PARA_VALUE

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

1 1

3.3 配置DDL辅助表和触发器

使用manager工具执行如下脚本：

/home/dmdba/dmhs/scripts/ddl_sql_dm8.sql

（通过ftp下载到本地，打开复制粘贴里面的内容到manager工具执行）

注：使用manager工具执行脚本。这里使用disql执行会报错。报177行的17列else附近有错误。

并且提示输入变量的值（set define off）

查看创建的辅助表：

SQL> select owner, table_name from dba_tables where owner = 'SYSDBA' and table_name like 'DMHS%' and status = 'VALID';

行号     OWNER TABLE_NAME

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

1          SYSDBA DMHS_DDL_COL

2          SYSDBA DMHS_DDL_SQL

3          SYSDBA DMHS_DDL_CONS

4          SYSDBA DMHS_DDL_IDX

5          SYSDBA DMHS_DDL_RENAME

6          SYSDBA DMHS_DDL_SEQ

7          SYSDBA DMHS_DDL_PART

8          SYSDBA DMHS_DDL_COMMENT

9          SYSDBA DMHS_DDL_LOG

查询创建的触发器：

SQL> select owner, trigger_name from dba_triggers where owner = 'SYSDBA' and trigger_name like 'DMHS%' and status = 'Y';

行号     OWNER TRIGGER_NAME

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

1          SYSDBA DMHS_DDL_TRIGGER_AFTER

2          SYSDBA DMHS_DDL_TRIGGER_BEFORE

3          SYSDBA DMHS_DDL_TRIGGER_GRANT

4          SYSDBA DMHS_DDL_TRIGGER_REVOKE

这里共创建了9个表，4个触发器。如果以上脚本报错，创建的表数和触发器数会少。

3.4 创建dmhs连接数据库的用户

这里为了方便操作授予dba权限了。

SQL> create tablespace dmhstbs datafile '/dm8/data/DAMENG/dmhstbs.dbf' size 200;

操作已执行

已用时间: 121.212(毫秒). 执行号:66107.

SQL> create user dmhsuser identified by dameng123 default tablespace dmhstbs;

操作已执行

已用时间: 15.739(毫秒). 执行号:66108.

SQL> grant dba to dmhsuser;

操作已执行

4 源端和目标端环境准备

4.1 同步目标说明

如图实现两个目标：

双向同步：

双向同步适用于“双活”数据库场景，将节点1：192.168.6.220：5236的DMSERVER库的HR模式的数据和192.168.6.221：5237的DMSERVER数据库的HR进行双向同步。

单向同步：

将节点1：192.168.6.220:5236的DMSERVER库的HR模式的test表单向同步到节点二：192.168.6.221:5237 DMSERVER数据库中的sales模式。

4.2 源端和目标端环境准备

4.2.1 准备源端HR模式的数据

节点一数据库：192.168.6.220:5236

SQL> show parameter instance_name

行号 PARA_NAME PARA_VALUE

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

1 INSTANCE_NAME DMSERVER

DMHR模式的准备，只需在通过dbca.sh工具建库时，勾选了该模式：

创建的结果：

将DMHR数据导出，建立hr模式，将DMHR的数据导入到HR模式。

[dmdba@dba ~]$ dexp sysdba/SYSDBA schemas=dmhr file=/home/dmdba/dmhr.dump log=/home/dmdba/dmhr.log

SQL>create tablespace dmhrtbs datafile '/dm8/data/DM/dmhrtbs.dbf' size 200;

SQL>create user hr identified by dameng123 default tablespace dmhrtbs;

SQL> grant VTI to hr;

操作已执行

已用时间: 10.965(毫秒). 执行号:64410.

SQL> grant public,resource to hr;

操作已执行

已用时间: 9.845(毫秒). 执行号:64411.

SQL> grant dba to hr;

操作已执行

[dmdba@dba ~]$ dimp sysdba/SYSDBA file=/home/dmdba/dmhr.dump log=/home/dmdba/dmhr.log remap_schema=dmhr:hr

4.2.2 准备目标端DMHR及SALES模式

节点二：DM数据库，192.168.6.221：5237

Dbca.sh建库时不创建sample模式。

当前模式为如下图：

创建两个空用户：此时为了操作方便授予dba权限了。

SQL> create tablespace dmhrtbs datafile '/dm8/data/DM/dmhrtbs.dbf' size 200;操作已执行

已用时间: 75.199(毫秒). 执行号:64403.

SQL> create tablespace dmsaletbs datafile '/dm8/data/DM/dmsaletbs.dbf' size 200;

操作已执行

已用时间: 72.351(毫秒). 执行号:64404.

SQL> create user dmhr identified by dameng123 default tablespace dmhrtbs;

操作已执行

已用时间: 12.768(毫秒). 执行号:64405.

SQL> create user sales identified by dameng123 default tablespace dmsaletbs;

操作已执行

已用时间: 11.725(毫秒). 执行号:64406.

SQL> grant VTI to sales;

操作已执行

已用时间: 12.552(毫秒). 执行号:64407.

SQL> grant public,resource to sales;

操作已执行

已用时间: 4.165(毫秒). 执行号:64408.

SQL> grant dba to sales;

操作已执行

已用时间: 9.164(毫秒). 执行号:64409.

SQL> grant VTI to dmhr;

操作已执行

已用时间: 10.965(毫秒). 执行号:64410.

SQL> grant public,resource to dmhr;

操作已执行

已用时间: 9.845(毫秒). 执行号:64411.

SQL> grant dba to dmhr;

操作已执行

已用时间: 9.136(毫秒). 执行号:64412.

5 dmhs服务配置

注：节点一和节点二做相同的操作

5.1 配置服务

[dmdba@dba ~]$ cd dmhs/

[dmdba@dba dmhs]$ ls

bin   desktop   include jdk             log      tool         web

bin2 doc       install license_en.txt samples uninst.sh

db    hs_agent JAPI     license.txt     scripts version.txt

[dmdba@dba dmhs]$ cd bin

[dmdba@dba bin]$ cp TemplateDmhsService DmhsService

[dmdba@dba bin]$ vi DmhsService

DMHS_HOME=/home/dmdba/dmhs

#REPLACE program dir

PROG_DIR=/home/dmdba/dmhs/bin

#REPLACE program config path

CONF_PATH=/home/dmdba/dmhs/bin/dmhs.hs

#REPLACE need library path, LD_LIBRARY_PATH/LIBPATH

NEED_LIB_PATH=/home/dmdba/dmhs/bin:/dm8/bin:/home/dmdba/dmhs/hs_agent

HS_NLS_LANG="american_america.AL32UTF8"

节点二：192.168.6.221做以上相同的操作。

5.2 配置dmhs用于同步的配置文件

5.2.1 节点一 : 配置文件

[dmdba@dba bin]$ pwd

/home/dmdba/dmhs/bin

[dmdba@dba bin]$ cat dmhs.hs

<?xml version="1.0" encoding="GB2312" standalone="no"?>

<dmhs>

    <base>

        <lang>en</lang>

        <mgr_port>5345</mgr_port>

        <ckpt_interval>60</ckpt_interval>

        <siteid>20</siteid>

        <version>2.0</version>

    </base>

    <cpt>

        <name>cpt0620</name>

        <db_type>dm8</db_type>

        <db_server>192.168.6.220</db_server>

        <db_user>DMHSUSER</db_user>

        <db_ssl_path/>

        <db_ssl_pwd/>

        <db_pwd>dameng123</db_pwd>

       #<char_code>PG_UTF8</char_code>

        <db_port>5236</db_port>

      <ddl_mask>op:TABLE:VIEW:PROCEDURE:FUNCTION:TRIGGER:INDEX:CHECK:SEQUENCE:TYPE:PACKAGE:SYNONYM</ddl_mask>

        <parse_thr>1</parse_thr>

        <arch>

            <clear_flag>1</clear_flag>

            <clear_interval>600</clear_interval>

        </arch>

        <send>

            <ip>192.168.6.221</ip>

            <mgr_port>5345</mgr_port>

            <data_port>5346</data_port>

            <trigger>1</trigger>

            <constraint>1</constraint>

            <identity>1</identity>

            <net_turns>0</net_turns>

            <filter>

                <enable>

<item>HR.CITY</item>

<item>HR.DEPARTMENT</item>

<item>HR.EMPLOYEE</item>

<item>HR.JOB</item>

<item>HR.JOB_HISTORY</item>

<item>HR.LOCATION</item>

<item>HR.REGION</item>

<item>HR.TEST</item>

                </enable>

            </filter>

            <map>

<item>HR.CITY==DMHR.CITY</item>

<item>HR.DEPARTMENT==DMHR.DEPARTMENT</item>

<item>HR.EMPLOYEE==DMHR.EMPLOYEE</item>

<item>HR.JOB==DMHR.JOB</item>

<item>HR.JOB_HISTORY==DMHR.JOB_HISTORY</item>

<item>HR.LOCATION==DMHR.LOCATION</item>

<item>HR.REGION==DMHR.REGION</item>

<item>HR.TEST==SALES.TEST</item>

            </map>

        </send>

    </cpt>

    <exec>

        <recv>

            <mgr_port>5345</mgr_port>

            <data_port>5346</data_port>

        </recv>

        <enable>1</enable>

        <name>exec0620</name>

        <db_type>DM8</db_type>

        <db_server>192.168.6.220</db_server>

        <db_user>DMHSUSER</db_user>

        <db_pwd>dameng123</db_pwd>

        <db_port>5236</db_port>

<exec_thr>1</exec_thr>

<exec_sql>1024</exec_sql>

<exec_trx> 5000 </exec_trx>

<exec_rows>1000</exec_rows>

<save_mask>EXEC</save_mask>

<exec_policy>2</exec_policy>

<ddl_continue>1</ddl_continue>

    </exec>

</dmhs>

5.2.2 节点二：配置文件

[dmdba@dba2 bin]$ pwd

/home/dmdba/dmhs/bin

[dmdba@dba2 bin]$ cat dmhs.hs

<?xml version="1.0" encoding="GB2312" standalone="no"?>

<dmhs>

    <base>

        <lang>en</lang>

        <mgr_port>5345</mgr_port>

        <ckpt_interval>60</ckpt_interval>

        <siteid>21</siteid>

        <version>2.0</version>

    </base>

    <cpt>

        <name>cpt0621</name>

        <db_type>dm8</db_type>

        <db_server>192.168.6.221</db_server>

        <db_user>DMHSUSER</db_user>

        <db_ssl_path/>

        <db_ssl_pwd/>

        <db_pwd>dameng123</db_pwd>

       # <char_code>PG_UTF8</char_code>

        <db_port>5237</db_port>

        <ddl_mask>op:TABLE:VIEW:PROCEDURE:FUNCTION:TRIGGER:INDEX:CHECK:SEQUENCE:TYPE:PACKAGE:SYNONYM</ddl_mask>

        <parse_thr>1</parse_thr>

        <arch>

            <clear_flag>1</clear_flag>

            <clear_interval>600</clear_interval>

        </arch>

        <send>

            <ip>192.168.6.220</ip>

            <mgr_port>5345</mgr_port>

            <data_port>5346</data_port>

            <trigger>1</trigger>

            <constraint>1</constraint>

            <identity>1</identity>

            <net_turns>0</net_turns>

            <filter>

                <enable>

<item>DMHR.CITY</item>

<item>DMHR.DEPARTMENT</item>

<item>DMHR.EMPLOYEE</item>

<item>DMHR.JOB</item>

<item>DMHR.JOB_HISTORY</item>

<item>DMHR.LOCATION</item>

<item>DMHR.REGION</item>

                </enable>

            </filter>

            <map>

<item>DMHR.*==HR.*</item>

            </map>

        </send>

    </cpt>

    <exec>

        <recv>

            <mgr_port>5345</mgr_port>

            <data_port>5346</data_port>

        </recv>

        <enable>1</enable>

        <name>exec0621</name>

        <db_type>DM8</db_type>

        <db_server>192.168.6.221</db_server>

        <db_user>DMHSUSER</db_user>

        <db_pwd>dameng123</db_pwd>

        <db_port>5237</db_port>

<exec_thr>1</exec_thr>

<exec_sql>1024</exec_sql>

<exec_trx> 5000 </exec_trx>

<exec_rows>1000</exec_rows>

<save_mask>EXEC</save_mask>

<exec_policy>2</exec_policy>

<ddl_continue>1</ddl_continue>

    </exec>

</dmhs>

注：以上的配置文件中的内容可以参考dem的手册来查看，手册所在位置为$DMHS_HOME/doc目录下。这里不再详细解释。

5.3 启动dmhs服务

5.3.1 启动节点一服务

[dmdba@dba bin]$ ./DmhsService start

Starting DmhsService: [ OK ]

[dmdba@dba bin]$ pwd

/home/dmdba/dmhs/bin

[dmdba@dba bin]$ netstat -anlp | grep 5345

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 0.0.0.0:5345 0.0.0.0:* LISTEN 31588/dmhs_server

5.3.2 启动节点二服务

节点2:/home/dmdba/dmhs 启动服务：

[dmdba@dba2 bin]$ ./DmhsService start

Starting DmhsService:                                      [ OK ]

[dmdba@dba2 bin]$ ps -ef | grep dmhs

dmdba     73016      1 0 10:43 pts/4    00:00:00 /home/dmdba/dmhs/bin/dmhs_server /home/dmdba/dmhs/bin/dmhs.hs -noconsole

dmdba     73101 19978 0 10:47 pts/4    00:00:00 grep --color=auto dmhs

[dmdba@dba2 bin]$ netstat -anlp | grep 5345

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:5345            0.0.0.0:*               LISTEN      73016/dmhs_server

5.3.3 服务启动的日志

如果服务启动异常可以查看日志

[dmdba@dba dmhs]$ cd log

[dmdba@dba log]$ ll

total 360

-rw-rw-rw- 1 dmdba dinstall 10366 Jul 31 14:05 DmhsService.log

-rwxr-xr-x 1 dmdba dinstall 355430 Jul 30 13:58 install.log

[dmdba@dba log]$ pwd

/home/dmdba/dmhs/log

[dmdba@dba log]$ tail DmhsService.log

MGR[WARN]: License will expire on 2024-10-10

MGR[INFO]: load config file successful,site no:91, manager port :5345, poll interval:3, max mem size:64(GB)

MGR[INFO]: manager listening port:5345

MGR[INFO]: loading the execute module...

error in load libodbcinst.so!!: No such file or directory

EXE[INFO]: CONNECT: SERVER=192.168.6.220;DRIVER=DM8 ODBC DRIVER;UID=dmhsuser;PWD=******;TCP_PORT=5236;

EXE[INFO]: DM8 SAVEPOINT_LIMIT of dm.ini is 512, so SAVEPOINT optimization is enabled.

EXE[INFO]: EXEC_V4.3.32_D64

REV[INFO]: exec server data receiving thread created successfully, listening data port : 5346

2024-07-31 14:05:50 Dmhs server starting finished...

6 Load装载数据即初始化

6.1 节点二执行：

[dmdba@dba2 bin]$ ./dmhs_console

DMHS console tool: V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

Copyright (c) 2020, DMHS. All rights reserved.

Type ? or "help" for help, type "quit" to quit console.

Connected to DMHS: 127.0.0.1:5345

execute success

Dameng HS Server V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

6.1.1 DMHS> start exec

execute success

6.1.2 查看日志

[dmdba@dba2 dmhs]$ cd bin/log

[dmdba@dba2 log]$ ls

csl_202408.log dmhs_202408.log

[dmdba@dba2 log]$ tail -f dmhs_202408.log

2024-08-01 13:06:39 MGR[INFO]: loading the execute module...

2024-08-01 13:06:39 EXE[INFO]: CONNECT: SERVER=192.168.6.221;DRIVER=DM8 ODBC DRIVER;UID=DMHSUSER;PWD=******;TCP_PORT=5237;

2024-08-01 13:06:39 EXE[INFO]: DM8 SAVEPOINT_LIMIT of dm.ini is 512, so SAVEPOINT optimization is enabled.

2024-08-01 13:06:39 EXE[WARN]: create table: DMHS_TRXID_TABLE

2024-08-01 13:06:39 EXE[WARN]: create table: DMHS_DTYPE_MAP

2024-08-01 13:06:40 EXE[WARN]: create table: DMHS_ERROR_TSK_TABLE

2024-08-01 13:06:40 EXE[WARN]: create table: DMHS_TABLE_SEQID

2024-08-01 13:06:40 EXE[WARN]: create table: DMHS_CHECKPOINT_TABLE

2024-08-01 13:06:40 EXE[WARN]: create table: DMHS_ERROR_TABLE

2024-08-01 13:06:40 EXE[WARN]: create table: DMHS_DPC_SYNC_TABLE

2024-08-01 13:06:40 EXE[INFO]: EXEC_V4.3.32_D64

2024-08-01 13:06:40 REV[INFO]: exec server data receiving thread created successfully, listening data port : 5346

6.1.3 查看5346端口

[dmdba@dba2 ~]$ netstat -anlp |grep 5346

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 0.0.0.0:5346 0.0.0.0:* LISTEN 19008/dmhs_server

6.2 节点一执行：

[dmdba@dba bin]$ ./dmhs_console

DMHS console tool: V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

Copyright (c) 2020, DMHS. All rights reserved.

Type ? or "help" for help, type "quit" to quit console.

Connected to DMHS: 127.0.0.1:5345

execute success

Dameng HS Server V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

6.2.1 start exec

DMHS> start exec

execute success

6.2.4 set clear lsn

DMHS> set clear lsn

execute success

6.2.5 同步hr模式的字典

DMHS> copy 0 "sch.name='HR'" CLEAR|DICT

CSL[WARN]: Detect the CLEAR mask, the mask will delete dict files, Whether to continue?(Y/N)Ycopy mask is : |DICT|PARTITION|REP

execute finish, please look up log file of exec module to check data load result

6.2.6 节点一查看日志

/home/dmdba/dmhs/bin/log/dmhs_202408.log

6.2.7 节点二查看日志

/home/dmdba/dmhs/bin/log/dmhs_202408.log

6.2.8 节点一执行装载数据

DMHS> copy 0 "sch.name='HR'" drop|create|insert|nolock|fast

CSL[WARN]: Detect the DROP mask, this mask will drop target db: TABLE, confirm to continue?(Y/N)

Y

copy mask is : |CREATE|DROP|NOLOCK|INSERT|TABLE|FAST|PARTITION|OBJID|REP

execute finish, please look up log file of exec module to check data load result

注：如上copy命令也是参考dem的手册，位置同样在$DMHS_HOME目录。

6.3 验证数据

在节点二上查看dmhr模式的数据情况：

7 启动cpt，实现双向同步和单向同步

7.1 初始化节点一和节点二exec和cpt字典

节点一操作：

[dmdba@dba bin]$ ./dmhs_console

DMHS console tool: V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

Copyright (c) 2020, DMHS. All rights reserved.

Type ? or "help" for help, type "quit" to quit console.

Connected to DMHS: 127.0.0.1:5345

execute success

Dameng HS Server V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

DMHS> clear exec lsn

execute success

DMHS> copy 0 "sch.name='hr'" CLEAR|DICT

CSL[WARN]: Detect the CLEAR mask, the mask will delete dict files, Whether to continue?(Y/N)

Y

copy mask is : |DICT|PARTITION|REP

execute finish, please look up log file of exec module to check data load result

节点二操作：

[dmdba@dba2 bin]$ ./dmhs_console

DMHS console tool: V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

Copyright (c) 2020, DMHS. All rights reserved.

Type ? or "help" for help, type "quit" to quit console.

Connected to DMHS: 127.0.0.1:5345

execute success

Dameng HS Server V4.3.32-Build(2024.07.10-163952trunc)_D64_2407

DMHS> start exec

execute success

DMHS> clear exec lsn

execute success

DMHS> copy 0 "sch.name='dmhr'" CLEAR|DICT

CSL[WARN]: Detect the CLEAR mask, the mask will delete dict files, Whether to continue?(Y/N)

Y

copy mask is : |DICT|PARTITION|REP

execute finish, please look up log file of exec module to check data load result

7.2 节点一启动CPT

DMHS> start cpt

execute success

DMHS> state

MGR: Capture Execute

TYPE VID SITEID EXEC/CPT IP PORT DBNAME

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

Capture 0 91 192.168.6.221 5345

execute success

7.3 节点二启动CPT

DMHS> start cpt

CSL[ERROR]: cannot start analysis module

日志报错：

DMHS> copy 0 "sch.name='DMHR'" CREATE|INSERT

copy mask is : |CREATE|INSERT|TABLE|PARTITION|OBJID|REP

execute finish, please look up log file of exec module to check data load result

注：目的端要启动cpt。如果没有启动的话，反向同步会有问题。当没有启动时，查看后台报错。

DMHS> start cpt

execute success

DMHS> state

MGR: Execute

TYPE    VID SITEID EXEC/CPT IP   PORT DBNAME

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

Capture 0   92     192.168.6.220 5345

Execute 0   91     192.168.6.220 5345

execute success

7.4 数据对比

节点二：DMHR

create table count_all_tables(tab_owner varchar(100),tab_name varchar(100),tab_count int);

declare

begin

for rec in (select owner,object_name from all_objects where owner in ('DMHR') and object_type='TABLE') loop

begin

execute immediate 'insert into count_all_tables select '''|| rec.owner ||''','''|| rec.object_name ||''',count(*) from '|| rec.owner || '."' || rec.object_name||'"';

exception when others then

dbms_output.put_line( rec.owner || '.' || rec.object_name || 'get count error');

end;

end loop;

end;

select tab_owner,tab_name,tab_count from count_all_tables where TAB_OWNER='DMHR' order by 3 desc;

节点一：HR

create table count_all_tables(tab_owner varchar(100),tab_name varchar(100),tab_count int);

declare

begin

for rec in (select owner,object_name from all_objects where owner in ('HR') and object_type='TABLE') loop

begin

execute immediate 'insert into count_all_tables select '''|| rec.owner ||''','''|| rec.object_name ||''',count(*) from '|| rec.owner || '."' || rec.object_name||'"';

exception when others then

dbms_output.put_line( rec.owner || '.' || rec.object_name || 'get count error');

end;

end loop;

end;

select tab_owner,tab_name,tab_count from count_all_tables where TAB_OWNER='HR' order by 3 desc;