标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
分类目录归档:EXADATA
dcli完成多节点对等ssh配置
dcli是Python脚本,可以实现在多节点中的之间非交换命令的执行,因为dcli执行需要通过ssh来实现,所以dcli也提供了ssh配置功能.本文通过dcli来完成多节点间的对等ssh配置
dcli整体描述
The dcli utility runs commands on multiple cells in parallel threads. However, it does not support an interactive session with a remote application on a cell. To use the dcli utility, copy the utility from the bin directory on a cell to a host computer from which central management can be performed. You can issue a command to be run on multiple cells, or use files that can be copied to cells and then run. The cells are referenced by their domain name or IP address. The dcli utility requires Python version 2.3 or later. You can determine the version of Python by running the python -V command. In addition, use of this tool assumes prior setup of SSH user-equivalence to a cell. You can use the dcli utility initially with the -k option to set up SSH user-equivalence to a cell.
dcli使用说明
[xifenfei@db1 ~]$ dcli Error: No command specified. usage: dcli [options] [command] options: --version show program's version number and exit -c CELLS comma-separated list of cells -d DESTFILE destination directory or file -f FILE file to be copied -g GROUPFILE file containing list of cells -h, --help show help message and exit -k push ssh key to cell's authorized_keys file -l USERID user to login as on remote cells (default: celladmin) -n abbreviate non-error output -r REGEXP abbreviate output lines matching a regular expression -s SSHOPTIONS string of options passed through to ssh --scp=SCPOPTIONS string of options passed through to scp if different from sshoptions -t list target cells -v print extra messages to stdout --vmstat=VMSTATOPS vmstat command options -x EXECFILE file to be copied and executed
服务器相关ip配置
[xifenfei@db1 ~]$ more xifenfei.txt 192.168.30.10 192.168.30.20 192.168.30.30 [xifenfei@db1 ~]$ more /etc/hosts 127.0.0.1 localhost.localdomain localhost 192.168.30.30 db1 192.168.30.10 cell1 192.168.30.20 cell2
操作系统用户
说明:dcli配置对等ssh不需要uid完全一样,不需要用户密码完全一样,因为是双向对等,需要用户名一致
[root@cell2 ~]# id xifenfei uid=8001(xifenfei) gid=8001(xifenfei) groups=8001(xifenfei) [root@cell1 ~]# id xifenfei uid=8001(xifenfei) gid=8001(xifenfei) groups=8001(xifenfei) [root@db1 ~]# id xifenfei uid=8001(xifenfei) gid=8001(xifenfei) groups=8001(xifenfei)
db1节点配置ssh
[xifenfei@db1 ~]$ ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/home/xifenfei/.ssh/id_dsa): Created directory '/home/xifenfei/.ssh'. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/xifenfei/.ssh/id_dsa. Your public key has been saved in /home/xifenfei/.ssh/id_dsa.pub. The key fingerprint is: 63:95:13:ba:4a:4c:13:93:67:7f:4f:e8:18:13:3c:4f xifenfei@db1 [xifenfei@db1 ~]$ dcli -k -g xifenfei.txt -l xifenfei The authenticity of host '192.168.30.10 (192.168.30.10)' can't be established. RSA key fingerprint is 1b:b6:91:11:58:89:b1:6a:c6:eb:72:df:68:d4:dd:5b. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.10's password: The authenticity of host '192.168.30.20 (192.168.30.20)' can't be established. RSA key fingerprint is 1b:b6:91:11:58:89:b1:6a:c6:eb:72:df:68:d4:dd:5b. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.20's password: The authenticity of host '192.168.30.30 (192.168.30.30)' can't be established. RSA key fingerprint is 54:ea:84:ae:38:24:07:31:9f:dd:8a:8b:4b:c2:a8:fe. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.30's password: 192.168.30.10: Warning: Permanently added '192.168.30.10' (RSA) to the list of known hosts. 192.168.30.10: ssh key added 192.168.30.20: Warning: Permanently added '192.168.30.20' (RSA) to the list of known hosts. 192.168.30.20: ssh key added 192.168.30.30: Warning: Permanently added '192.168.30.30' (RSA) to the list of known hosts. 192.168.30.30: ssh key added [xifenfei@db1 ~]$ for host in `cat xifenfei.txt` > do > scp /home/xifenfei/.ssh/id_dsa.pub ${host}:/home/xifenfei/.ssh/authorized_keys > done id_dsa.pub 100% 602 0.6KB/s 00:00 id_dsa.pub 100% 602 0.6KB/s 00:00 id_dsa.pub 100% 602 0.6KB/s 00:00 [xifenfei@db1 ~]$ dcli -g xifenfei.txt -l xifenfei "chmod -R 700 /home/xifenfei/.ssh" [xifenfei@db1 ~]$ dcli -g xifenfei.txt -l xifenfei "chown -R xifenfei /home/xifenfei/.ssh" [xifenfei@db1 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 07:45:17 2012 from 192.168.30.30 [xifenfei@db1 ~]$ ssh 192.168.30.20 Last login: Tue Dec 25 19:17:30 2012 from 192.168.30.10 [xifenfei@db1 ~]$ ssh 192.168.30.10 Last login: Tue Dec 25 20:17:20 2012 from 192.168.30.20 --ssh为单向,正向可以ssh成功,逆向需要输入密码 [xifenfei@db1 ~]$ ssh 192.168.30.10 xifenfei@192.168.30.10's password:
拷贝ip文件到其他节点
[xifenfei@db1 ~]$ for host in `cat xifenfei.txt` > do > scp /home/xifenfei/xifenfei.txt ${host}:/home/xifenfei/xifenfei.txt > done xifenfei.txt 100% 42 0.0KB/s 00:00 xifenfei.txt 100% 42 0.0KB/s 00:00 scp: /home/xifenfei/xifenfei.txt: Permission denied --自身节点不能拷贝
cell1节点配置
[xifenfei@cell1 ~]$ ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/home/xifenfei/.ssh/id_dsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/xifenfei/.ssh/id_dsa. Your public key has been saved in /home/xifenfei/.ssh/id_dsa.pub. The key fingerprint is: f2:b6:88:5c:c6:97:5e:38:c2:df:f1:58:49:8a:8d:90 xifenfei@cell1 [xifenfei@cell1 ~]$ dcli -k -g xifenfei.txt -l xifenfei The authenticity of host '192.168.30.10 (192.168.30.10)' can't be established. RSA key fingerprint is 1b:b6:91:11:58:89:b1:6a:c6:eb:72:df:68:d4:dd:5b. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.10's password: xifenfei@192.168.30.20's password: xifenfei@192.168.30.30's password: 192.168.30.10: Warning: Permanently added '192.168.30.10' (RSA) to the list of known hosts. 192.168.30.10: ssh key added 192.168.30.20: ssh key added 192.168.30.30: ssh key added [xifenfei@cell1 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 07:48:24 2012 from 192.168.30.30 --cell1 to cell2 正向成功,逆向失败 [xifenfei@cell1 ~]$ ssh 192.168.30.20 Last login: Tue Dec 25 19:23:42 2012 from 192.168.30.30 [xifenfei@cell2 ~]$ ssh 192.168.30.10 xifenfei@192.168.30.10's password: --cell1和db1正逆向均可以ssh [xifenfei@cell1 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 20:24:15 2012 from 192.168.30.30 [xifenfei@db1 ~]$ ssh 192.168.30.10 Last login: Tue Dec 25 20:27:27 2012 from 192.168.30.10
cell2节点配置
[xifenfei@cell2 ~]$ ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/home/xifenfei/.ssh/id_dsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/xifenfei/.ssh/id_dsa. Your public key has been saved in /home/xifenfei/.ssh/id_dsa.pub. The key fingerprint is: 87:80:02:e1:27:b8:d0:af:c0:5f:e0:f3:5e:95:29:cb xifenfei@cell2 [xifenfei@cell2 ~]$ dcli -k -g xifenfei.txt -l xifenfei The authenticity of host '192.168.30.10 (192.168.30.10)' can't be established. RSA key fingerprint is 1b:b6:91:11:58:89:b1:6a:c6:eb:72:df:68:d4:dd:5b. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.10's password: The authenticity of host '192.168.30.20 (192.168.30.20)' can't be established. RSA key fingerprint is 1b:b6:91:11:58:89:b1:6a:c6:eb:72:df:68:d4:dd:5b. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.20's password: The authenticity of host '192.168.30.30 (192.168.30.30)' can't be established. RSA key fingerprint is 54:ea:84:ae:38:24:07:31:9f:dd:8a:8b:4b:c2:a8:fe. Are you sure you want to continue connecting (yes/no)? yes xifenfei@192.168.30.30's password: 192.168.30.10: Warning: Permanently added '192.168.30.10' (RSA) to the list of known hosts. 192.168.30.10: ssh key added 192.168.30.20: Warning: Permanently added '192.168.30.20' (RSA) to the list of known hosts. 192.168.30.20: ssh key added 192.168.30.30: Warning: Permanently added '192.168.30.30' (RSA) to the list of known hosts. 192.168.30.30: ssh key added --ssh测试 [xifenfei@cell2 ~]$ ssh 192.168.30.10 Last login: Tue Dec 25 20:11:02 2012 from 192.168.30.30 [xifenfei@cell2 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 07:53:27 2012 from cell1 [xifenfei@cell2 ~]$ ssh 192.168.30.20 Last login: Tue Dec 25 19:30:16 2012 from 192.168.30.10
ssh等效性测试汇总
--db1节点 [xifenfei@db1 ~]$ ssh 192.168.30.10 Last login: Tue Dec 25 20:30:24 2012 from 192.168.30.20 [xifenfei@db1 ~]$ ssh 192.168.30.20 Last login: Tue Dec 25 19:33:07 2012 from 192.168.30.20 [xifenfei@db1 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 07:57:56 2012 from cell2 --cell1节点 [xifenfei@cell1 ~]$ ssh 192.168.30.20 Last login: Tue Dec 25 19:34:05 2012 from 192.168.30.30 [xifenfei@cell1 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 07:59:29 2012 from 192.168.30.30 [xifenfei@cell1 ~]$ ssh 192.168.30.10 Last login: Tue Dec 25 20:33:59 2012 from 192.168.30.30 --cell2节点 [xifenfei@cell2 ~]$ ssh 192.168.30.10 Last login: Tue Dec 25 20:35:42 2012 from 192.168.30.10 [xifenfei@cell2 ~]$ ssh 192.168.30.30 Last login: Tue Dec 25 08:00:56 2012 from cell1 [xifenfei@cell2 ~]$ ssh 192.168.30.20 Last login: Tue Dec 25 19:35:31 2012 from 192.168.30.10
到此证明三个节点之间的xifenfei用户的ssh等效配置完成,实现使用dcli完成多节点ssh等效配置
整体处理思路总结
1.确定需要配置ssh用户 --第一节点 2.编辑需要配置ssh等效连接ip列表 3.ssh-keygen -t dsa 4.dcli -k -g xifenfei.txt -l xifenfei 5. for host in `cat xifenfei.txt` do scp /home/xifenfei/.ssh/id_dsa.pub ${host}:/home/xifenfei/.ssh/authorized_keys done 6.dcli -g xifenfei.txt -l xifenfei "chmod -R 700 /home/xifenfei/.ssh" 7.dcli -g xifenfei.txt -l xifenfei "chown -R xifenfei /home/xifenfei/.ssh" 8. for host in `cat xifenfei.txt` do scp /home/xifenfei/xifenfei.txt ${host}:/home/xifenfei/xifenfei.txt done --其他节点 9. ssh-keygen -t dsa 10. dcli -k -g xifenfei.txt -l xifenfei
cellcli命令简介
cellcli用途描述
The CellCLI utility is the command-line administration tool for Exadata Cell. CellCLI runs on each cell to enable you to manage an individual cell. You use CellCLI to start and stop the cell, to manage cell configuration information, to enable or disable cells, and to manage objects in the cell environment. The command-line utility is already installed when Exadata Cell is shipped.
cellcli语法
cellcli [port_number] [-n] [-m] [-xml] [-v | -vv | -vvv] [-x] [-e command]
cellcli登录
[root@cell2 ~]# cellcli CellCLI: Release 11.2.1.2.0 - Production on Tue Dec 25 16:07:01 PST 2012 Copyright (c) 2007, 2009, Oracle. All rights reserved. Cell Efficiency Ratio: 24M CellCLI>
cellcli help
CellCLI> help HELP [topic] Available Topics: ALTER ALTER ALERTHISTORY ALTER CELL ALTER CELLDISK ALTER GRIDDISK ALTER IORMPLAN ALTER LUN ALTER THRESHOLD ASSIGN KEY CALIBRATE CREATE CREATE CELL CREATE CELLDISK CREATE FLASHCACHE CREATE GRIDDISK CREATE KEY CREATE THRESHOLD DESCRIBE DROP DROP ALERTHISTORY DROP CELL DROP CELLDISK DROP FLASHCACHE DROP GRIDDISK DROP THRESHOLD EXPORT CELLDISK IMPORT CELLDISK LIST LIST ACTIVEREQUEST LIST ALERTDEFINITION LIST ALERTHISTORY LIST CELL LIST CELLDISK LIST FLASHCACHE LIST FLASHCACHECONTENT LIST GRIDDISK LIST IORMPLAN LIST KEY LIST LUN LIST METRICCURRENT LIST METRICDEFINITION LIST METRICHISTORY LIST PHYSICALDISK LIST THRESHOLD SET SPOOL START CellCLI> help list Enter HELP LIST <object_type> for specific help syntax. <object_type>: {ACTIVEREQUEST | ALERTHISTORY | ALERTDEFINITION | CELL | CELLDISK | FASHCACHE | FLASHCACHECONTENT | GRIDDISK | IORMPLAN | KEY | LUN | METRICCURRENT | METRICDEFINITION | METRICHISTORY | PHYSICALDISK | THRESHOLD } CellCLI> help list PHYSICALDISK Usage: LIST PHYSICALDISK [<name> | <filters>] [<attribute_list>] [DETAIL] Purpose: Displays specified attributes for physical disks. Arguments: <name>: The name of the physical disk to be displayed. <filters>: an expression which determines which physical disks should be displayed. <attribute_list>: The attributes that are to be displayed. ATTRIBUTES {ALL | attr1 [, attr2]... } Options: [DETAIL]: Formats the display as an attribute on each line, with an attribute descriptor preceding each value. Examples: LIST PHYSICALDISK DETAIL
cellcli describe
CellCLI> DESCRIBE PHYSICALDISK name ctrlFirmware ctrlHwVersion deviceId diskType enclosureDeviceId errCmdTimeoutCount errHardReadCount errHardWriteCount errMediaCount errOtherCount errSeekCount errorCount foreignState hotPlugCount id lastFailureReason luns makeModel notPresentSince physicalFirmware physicalInsertTime physicalInterface physicalPort physicalRPM physicalSerial physicalSize physicalUseType sectorRemapCount slotNumber status
cellcli list
CellCLI> LIST PHYSICALDISK attributes name ,physicalSize /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH01 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH02 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH03 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH04 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk01 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk02 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk03 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk04 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk05 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk06 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk07 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk08 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk09 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk10 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk11 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/disk12 450M CellCLI> LIST PHYSICALDISK attributes name ,physicalSize where name like '.*FLASH.*' /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH01 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH02 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH03 450M /opt/oracle/cell11.2.1.2.0_LINUX_091102/disks/raw/FLASH04 450M
attributes后面可以加上对应的属性,”.*”表示通配符
这里通过help和describe来实现cellcli的基本操作,这里只是提供了一种处理问题的思路,通过help操作来完成cellcli的基本操作.
发表在 EXADATA
评论关闭
EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试
随着xd的越来越普及,不少的企业使用了xd,但是不少企业因为资金有限,只有一台xd,但是为了实现数据的容灾,可能会使用一台非xd的机器来通过dataguard来实现容灾,但是因为xd的ehcc新特性,官方宣传是只在xd中支持,如果dg的备库不是xd。那么会怎么样,这里通过测试得出如下一些结论:xd与非xd可以构造dg,ehcc功能在xd上无法高效使用。对于这样的环境条件下,使用ORACLE自带压缩效率更高.针对ehcc压缩效率很低,个人猜测,是因为xd检查到备库是非xd环境,直接对ehcc进行了降级压缩处理,从而出现了ehcc的压缩效率比oltp还低(牺牲了xd的性能,确保了数据的安全,看来xd的设计还是考虑的比较全面)
xd基本信息
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> show parameter clu; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 cluster_interconnects string SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE; OPEN_MODE DATABASE_ROLE NAME -------------------- ---------------- --------- READ WRITE PRIMARY xxxxxx SQL> !uname -a Linux dm01db02 2.6.18-194.3.1.0.4.el5 #1 SMP Sat Feb 19 03:38:37 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
xd创建模拟表
SQL> create table t_FF_c compress as select * from dba_objects; Table created. SQL> create table t_FF_c_o compress for oltp as select * from dba_objects; Table created. SQL> create table t_FF_q_l compress for query low as select * from dba_objects; Table created. SQL> create table t_FF_q_h compress for query high as select * from dba_objects; Table created. SQL> create table t_FF_a_l compress for archive low as select * from dba_objects; Table created. SQL> create table t_FF_a_h compress for archive high as select * from dba_objects; Table created. SQL> create table t_ff as select * from dba_objects; Table created.
xd查询模拟表
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; 2 3 OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 10 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC
通过这里发现,带有非dg的xd使用ehcc压缩效率都低了很多
非xd备库基本信息
SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE; OPEN_MODE DATABASE_ROLE NAME -------------------- ---------------- --------- READ ONLY WITH APPLY PHYSICAL STANDBY xxxxxx SQL> show parameter clu; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string SQL> !uname -a Linux oradg 2.6.18-238.el5xen #1 SMP Sun Dec 19 14:42:02 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
查询非xd dg备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 10 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC SQL> SELECT COUNT(*) FROM T_FF_Q_L; COUNT(*) ---------- 94709 SQL> SELECT COUNT(*) FROM T_FF_Q_H; COUNT(*) ---------- 94710 SQL> SELECT COUNT(*) FROM T_FF_C_O; COUNT(*) ---------- 94708 SQL> SELECT COUNT(*) FROM T_FF_C; COUNT(*) ---------- 94707 SQL> SELECT COUNT(*) FROM T_FF_A_L; COUNT(*) ---------- 94711 SQL> SELECT COUNT(*) FROM T_FF_A_H; COUNT(*) ---------- 94712 SQL> select count(*) from t_FF; COUNT(*) ---------- 94713
通过这里测试证明,对于非xd dg库,可以正常的查询xd上的ehcc相关表,而且相关大小也相同(物理dg当然相同了)
测试xd与非xd dg测试ehcc的dml操作
--xd 主库 SQL> update t_ff_a_h set owner='www.xifenfei.com'; 94712 rows updated. SQL> commit; Commit complete. SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; 2 3 OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 11 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC 7 rows selected. SQL> alter system switch logfile; System altered. --非xd 备库 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 11 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC
证明对于xd与非xd构成的dg环境,可以执行dml操作.
测试xd与非xd dg的ehcc的append操作
--xd准备三张测试空表 SQL> create table t_FF_a_l_1 compress for archive low as select * from t_FF where 1=0; Table created. SQL> create table t_FF_a_h_1 compress for archive high as select * from t_FF where 1=0; Table created. SQL>truncate table t_FF; Table truncated. --插入数据(每个表执行5次) SQL> insert /*+ APPEND */ into t_FF_a_l_1 select * from dba_objects; 94714 rows created. SQL> commit; Commit complete. SQL> insert /*+ APPEND */ into t_FF_h_l_1 select * from dba_objects; 94714 rows created. SQL> commit; Commit complete. SQL> insert /*+ APPEND */ into t_FF select * from dba_objects; 94714 rows created. SQL> commit; Commit complete. --查看相关表数据量 SQL> select count(*) from t_FF_a_l_1; COUNT(*) ---------- 473570 SQL> select count(*) from t_FF_a_h_1; COUNT(*) ---------- 473570 SQL> select count(*) from t_FF; COUNT(*) ---------- 473570 --查看xd主库 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 52 SYS T_FF_A_H 11 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC SYS T_FF_A_H_1 47 ARCHIVE HIGH SYS T_FF_A_L_1 47 ARCHIVE LOW --查看非xd备库 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 52 SYS T_FF_A_H 11 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC SYS T_FF_A_H_1 47 ARCHIVE HIGH SYS T_FF_A_L_1 47 ARCHIVE LOW
试验整体结论
1.xd可以与非xd机器构建dg容灾环境(不会因为非dg不支持ehcc而导致无法搭建他们之间的dg)
2.xd与非xd的dg,ehcc功能大大缩水,基本上和非压缩状态差不多,比OLTP低很多
3.xd与非xd的dg在备库中支持select,dml,hint append等操作,这些操作是因为ehcc表在xd端就进行了ehcc降级导致
发表在 Data Guard, EXADATA
一条评论