月归档:十二月 2012

ORACLE 12C varchar2支持32k长度字符串

在Oracle的以前版本中如果要存储超过4000byte的字符串需要使用clob字段,而lob本身操作就麻烦,而且效率不高。从12C开始Oracle提供了 VARCHAR2, NVARCHAR2, and RAW支持32k长度在字符串,大大提高了Oracle程序在处理4000到32k的字符串的处理效率.
数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

max_sql_string_size参数

SQL> show parameter max_sql_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_sql_string_size                  string      LEGACY

max_sql_string_size:controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL
max_sql_string_size, must be from among EXTENDED, LEGACY

创建测试表

SQL> create table t_xifenfei(id number,name varchar2(4001));
create table t_xifenfei(id number,name varchar2(4001))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> create table t_xifenfei(id number,name varchar2(4000));

Table created.

SQL> insert into t_xifenfei values(1,rpad('www.xifenfei.com',4000,0));

1 row created.

SQL> insert into t_xifenfei values(2,lpad('www.xifenfei.com',4009,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,length(name) from t_xifenfei;

        ID LENGTH(NAME)
---------- ------------
         1         4000
         2         4000

SQL> select id,substr(name,-10,10) from t_xifenfei;

        ID SUBSTR(NAME,-10,10)
---------- ----------------------------------------
         1 0000000000
         2 fenfei.com

SQL>  select id,substr(name,3990) from t_xifenfei;

        ID SUBSTR(NAME,3990)
---------- --------------------------------------------
         1 00000000000
         2 ifenfei.com

测试说明几点:
1.默认情况下varchar2长度不能超过4000
2.插入varchar2超过4000的字段(列长度为4000),自动被截断

修改max_sql_string_size参数

SQL> alter system set max_sql_string_size='EXTENDED';
alter system set max_sql_string_size='EXTENDED'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02096: specified initialization parameter is not modifiable with this option


SQL> alter system set max_sql_string_size='EXTENDED' scope=spfile;

System altered.

执行utl32k.sql脚本

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2259912 bytes
Variable Size             243270712 bytes
Database Buffers           62914560 bytes
Redo Buffers                4714496 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utl32k.sql
--包含编译无效对象

QL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2259912 bytes
Variable Size             251659320 bytes
Database Buffers           54525952 bytes
Redo Buffers                4714496 bytes
Database mounted.
Database opened.

SQL> show parameter max_sql_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
max_sql_string_size                  string      EXTENDED

测试varchar2(32767)

SQL> conn xff/xifenfei
Connected.
SQL> create table t_xifenfei_1(id number,name varchar2(32768));
create table t_xifenfei_1(id number,name varchar2(32768))
                                                  *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> create table t_xifenfei_extend(id number,name varchar2(32767));

Table created.

SQL> insert into t_xifenfei_extend values(3,lpad('www.xifenfei.com',32767,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,substr(name,-10,10) from t_xifenfei_extend;

        ID SUBSTR(NAME,-10,10)
---------- ----------------------------------------
         3 fenfei.com

SQL> select id,substr(name,32760) from t_xifenfei_extend;

        ID SUBSTR(NAME,32760)
---------- --------------------------------
         3 nfei.com

SQL> select id,length(name) from t_xifenfei_extend;

        ID LENGTH(NAME)
---------- ------------
         3        32767

Oracle 12C支持字符串32K处理过程
1.修改max_sql_string_size=’EXTENDED’
2.重启数据库至upgrade状态
3.执行@?/rdbms/admin/utl32k.sql
4.重启数据库至正常open状态

发表在 ORACLE 12C | 标签为 | 4 条评论

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
发表在 EXADATA | 2 条评论

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 | 评论关闭