2012年7月23日星期一

深入解析oracle--总结

1.alter database mount;
  这一过程数据库需要计算 mount id 并将其记录在控制文件中，然后开始启动心跳(heartbeat)，每3秒更新一次控制文件。可用以下命令间隔3秒转储2次控制文件信息：
alter session set enents 'immediate trace name CONTROLF level 8';
cd dump 下用diff xx.trc  xx.trc 比较两个文件的不同。只有heartbeat的值不同。
2.也可用sys查内部表得到heartbeat的值X$KCCCP(Kernel Cache Controlfile managerment Checkpoint Progress);
select CPHBT from X$KCCCP;
SQL> select event#,name from v$event_name where name like '%heart%';

    EVENT# NAME
---------- --------------------------------------------------
        75 heartbeat monitor sleep
       380 ASM mount : wait for heartbeat
       563 control file heartbeat

SQL>
3.备份控制文件;
alter database backup controlfile to trace;
4.db_unique_name
5.orapwd file=orapworcl password=oracle entries=5
6.oracle_base在11g引入到了数据库的内部，增加了隐含参数。
SQL> select x.ksppinm name,y.ksppstvl value,x.KSPPDESC PDESC from sys.x$ksppi x,sys.x$ksppcv y where x.indx=y.indx and x.ksppinm like '%&par%' ;
SQL> /
输入par的值： oracle_base
old: 2: x.indx=y.indx and x.ksppinm like '%&par%'
new: 2: x.indx=y.indx and x.ksppinm like '%oracle_base%'

NAME                 VALUE                PDESC
-------------------- -------------------- --------------------
__oracle_base        /extdata/oracle      ORACLE_BASE

SQL>
7.ADR:automatic diagnostic repository.
  select * from v$diag_info ;其中diag_alert和diag trace对应目录分别存储了xml和文本格式的街区日志文件。
8.ADR之于诊断文件，就类似于OFA（Optimal Flexible Architecture)之于数据库文件，FRA（Flash Recovery Area)之于备份文件。
9.ADR 是 11g的新特性 故障诊断基础架构（Fault Diagnosability Infrastructure)的一角。
ips 属于FDI 的事件打包服务(Incident Packaging Service,IPS).
10.adrci
   adrci> show incident
   adrci> show incident -mode DETAIL -p "incident_id=14601"
   cd /extdata/oracle/diag/rdbms/orcl/orcl/incident/incident_14601
   ls -l
   使用如下命令可以为14601号incident创建逻辑package
   adrci> set homepath diag/rdbms/orcl/orcl/
   adrci> ips create package incident 14601
   进一步用如下命令生成物理package
   adrci> ips generate package 1 in /extdata/oracle/diag

1.2 数据库的访问
   select sid,username,machine,program from v$session where username='SYSTEM' ;
   select instance_name,startup_time,version from v$instance;
   select sid,program from v$session ;

    监听器不是默认的名称、端口 要设置 LOCAL_LISTENER参数：
    local_listener=listener_alias
    对于共享服务器模式，可以设置为：
    DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener1)"

 对于RAC环境，监听器可以向远程服务器注册：
 orcl1的remote_listener=listener_orcl2
 orcl2的remote_listener=listener_orcl1
 在orcl1的tnsnames.ora中:
 listener_orcl2=
 (description=
 (address=(protocol=tcp)(host=orcl2-server)(port=1521)))
 在orcl2上同样把listener_orcl1解析。

 show parameter remote_lis
 alter system set REMOTE_LISTENER='LISTENERS_ALIAS' scope=both sid='*';
 listener.ora 中：
 LISTENER_ORCL =
   (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.100)(PORT=1521))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.101)(PORT=1521))
   )

RAC环境的tnsnames.ora多个实例地址信息，同时支持负载均衡和在多实例之间的Failover

smsrac =
  (description =
     (address=(protocol=tcp)(host=192.168.100.100)(port=1521))
     (address=(protocol=tcp)(host=192.168.100.101)(port=1521))
     (load_balance=yes)
     (connect_data=
        (service=dedicated)
        (service_name=smsrac)
        (failover_mode =
           (type=select)
           (method=basic)
           (retries=180)
           (delay=5)
      )
     )
    )













发帖者 时间： 没有评论:
标签：

2012年7月20日星期五

V$PWFILE_USERS和密码文件关系

1、验证密码文件已经修改
SQL> create user xff01 identified by xifenfei;
User created.
SQL> grant sysdba to xff01;
Grant succeeded.

SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
04cedb56b62d94fd7e14124619722348  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> revoke sysdba from xff01;
Revoke succeeded.

SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
1f6d120acb913a1877cfb0ab57702744  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

2、查看基表
SQL> col owner for a20
SQL> col object_name for a30
SQL> SELECT owner,object_name,object_type FROM DBA_objects WHERE object_NAME ='V$PWFILE_USERS';

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
PUBLIC               V$PWFILE_USERS                 SYNONYM

SQL> SELECT table_owner,TABLE_name FROM Dba_Synonyms a WHERE a.synonym_name='V$PWFILE_USERS';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$PWFILE_USERS

SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_name='V_$PWFILE_USERS';

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
SYS                  V_$PWFILE_USERS                VIEW

SQL> set long 1000
SQL> set line 200
SQL> SELECT dbms_metadata.get_ddl('VIEW','V_$PWFILE_USERS','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','V_$PWFILE_USERS','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PWFILE_USERS" ("USERNAME", "SYSDBA", "SYSOPER", "SYSASM") AS
  select "USERNAME","SYSDBA","SYSOPER","SYSASM" from v$pwfile_users

SQL> select * from v$fixed_table where name ='V$PWFILE_USERS';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
V$PWFILE_USERS                 4294951116 VIEW       65537

SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------
select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id = USERENV('Instance')

SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$PWFILE_USERS';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE','FALSE'),
 decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and username != 'INTERNAL'

SQL> set line 100
SQL> desc x$kzsrt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 USERNAME                                                       VARCHAR2(30)
 SYSDBA                                                         NUMBER
 SYSOPER                                                        NUMBER
 SYSASM                                                         NUMBER
 VALID                                                          NUMBER

SQL> col username for a10
SQL> select * from x$kzsrt;

ADDR                   INDX    INST_ID USERNAME       SYSDBA    SYSOPER     SYSASM      VALID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00002B9563678690          0          1 INTERNAL            1          1          0          1
00002B9563678690          1          1 SYS                 1          1          0          1
00002B9563678690          2          1 XFF01               0          0          0          0
  
从这里可以看出V$PWFILE_USERS视图的基表是x$kzsrt，这里和我们查看的密码文件一样，这些记录都存在，而是在密码文件中有不被strings显示的字符表示了账号是否启用，x$kzsrt.valid用1和0标示
发帖者 时间： 没有评论:

2012年7月19日星期四

oracle passord file

错误日志：Oracle启动报ORA-01031: insufficient privileges错误！
解决：
C:/Documents and Settings>orapwd file=%ORACLE_HOME%/database/PWD%ORACLE_SID%.ora password=sys entries=5
orapwd
注释:271338.1
1.Ensure REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set in the init.ora for the database
密码文件是有格式的
2.Create a password file:
Unix: $ orapwd file=$ORACLE_HOME/dbs/orapwd$ORACLE_SID password=sys entries=5
Windows: C:/> orapwd file=%ORACLE_HOME%/database/pwd%ORACLE_SID% password= sys entries=5

3. grant sysdba to sys;
4. alter user sys identified by sys; --重新修改下密码
发帖者 时间： 没有评论:

move table/index/lob column/partiton table/partiton index

-- purge recyclebin
purge user_recyclebin ;
-- move table to dest_data
select 'alter table '||segment_name||' move tablespace dest_data ;' from user_segments where segment_type='TABLE';
-- move index to dest_data
select 'alter index '||segment_name||' rebuild tablespace dest_data ;' from user_segments where segment_type='INDEX';
-- move lob column
select 'alter table '||table_name||' move lob ('||column_name||') store as (tablespace dest_data) ;' from user_lobs ;
-- move partition table
select 'alter table '||table_name||' move partition '||partition_name||' tablespace dest_data ;' from user_tab_partitions ;
-- move partiton index
select 'alter index '||index_name||' rebuild partition '||partition_name||' tablespace dest_data ;' from user_ind_partitions ;
发帖者 时间： 没有评论:
标签：

2012年7月17日星期二

Oracle连接MySQL-使用DG4ODBC.

Oracle连接MySQL-使用DG4ODBC.

最近做了一次Oracle连接到MySQL的实验,主要是通过DG4ODBC来连接的.以下是实验环境和实验步骤.
Oracle服务器:
IP:192.168.0.193
Database Version:11.2.0.1
MySQL服务器
IP:192.168.0.100
MySQL Version:5.5.18
1.首先要检查Oracle和DG4ODBC是32位还是64位.
[oracle@11g ~]$ file $ORACLE_HOME/bin/dg4odbc
/oracle/app/oracle/product/11.2.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
这里可以看到是64位的.所以必须使用64位的ODBC Driver Manager和64位的ODBC Driver.
2.下载并安装64位的ODBC Driver Manager UnixODBC 2.2.14.
下载地址:http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download
#这里下载到了/home/oracle目录下面
[oracle@11g ~]$ ls -l unixODBC*
-rw-r--r-- 1 oracle dba 756418 Dec 13 18:17 unixODBC-2.2.14-linux-x86-64.tar.gz
[oracle@11g ~]$ mkdir -p ~/app/unixodbc-2.2.14
[oracle@11g ~]$ cd ~/app/unixodbc-2.2.14/
[oracle@11g unixodbc-2.2.14]$ gunzip -c ~/unixODBC-2.2.14-linux-x86-64.tar.gz | tar xvf -
#因为UnixODBC的包lib和bin是在/usr/local文件夹里面.为了方便其见,我们把他们移动到上层目录上来.
[oracle@11g unixodbc-2.2.14]$ mv ~/app/unixodbc-2.2.14/usr/local/* .
[oracle@11g unixodbc-2.2.14]$ rm -rf usr/
#检查一下目录的内容
[oracle@11g unixodbc-2.2.14]$ ls -l
total 12
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 bin
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 include
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 lib
3.下载并安装ODBC Driver
下载地址:http:http://ftp.ntu.edu.tw/pub/MySQL/Downloads/Connector-ODBC/5.1/
#这里下载到了/home/oracle目录下面
[oracle@11g ~]$ ls -l mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
-rw-r--r-- 1 oracle oinstall 5725402 Dec 14 22:40 mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
[oracle@11g ~]$ cd app/
[oracle@11g app]$ gunzip -c ~/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz | tar xvf -
[root@11g app]# ln -s mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit myodbc-5.18
4.配置MYSQL Connector(ODBC driver)
首先在mysql服务器上新建一个用户,并建相关的表
mysql> grant all privileges on test.* to mysql_user@localhost identified by "mysql_user";
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table t (id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t values (20);
Query OK, 1 row affected (0.04 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
然后在Oracle所在的服务器上面的/oracle/home/app下面建立odbc.ini文件,添加加以下内容
[myodbc5]
Driver = /home/dbs/app/myodbc-5.18/lib/libmyodbc5.so
Description = Connector/ODBC 5.1 Driver DSN
SERVER = 192.168.0.100
PORT = 3306
USER = mysql_user
PASSWORD = mysql_user
DATABASE = test
OPTION = 0
TRACE = OFF
5.使用isql来进行验证
[oracle@11g ~]$ export ODBCINI=/home/oracle/app/odbc.ini
[oracle@11g ~]$ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH
[oracle@11g bin]$ ./isql myodbc5 -v
[S1000][unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on '192.168.0.100' (113)
[ISQL]ERROR: Could not SQLConnect
这里报错.这是因为mysql新建的mysql_user用户不允许其他ip访问.切换到mysql服务器上面进行检查.
[root@buddy ~]# mysql --user=mysql_user --password=mysql_user --host=192.168.0.100 --port=3306
ERROR 1130 (HY000): Host '192.168.0.100' is not allowed to connect to this MySQL server
进入数据库查询可以清楚的看到只有localhost才能访问mysql_user;
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------+
| host      | user       |
+-----------+------------+
| 127.0.0.1 | root       |
| ::1       | root       |
| buddy     |            |
| buddy     | root       |
| localhost |            |
| localhost | mysql_user |
| localhost | root       |
+-----------+------------+
7 rows in set (0.00 sec)
#修改权限.
mysql> grant all privileges on test.* to 'mysql_user'@'%' identified by 'mysql_user';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+-----------+------------+
| host      | user       |
+-----------+------------+
| %         | mysql_user |
| 127.0.0.1 | root       |
| ::1       | root       |
| buddy     |            |
| buddy     | root       |
| localhost |            |
| localhost | mysql_user |
| localhost | root       |
+-----------+------------+
8 rows in set (0.00 sec)
如果还是不行,就需要把mysql服务器的防火墙关闭.
[root@buddy init.d]# /etc/init.d/iptables stop
然后切换到oracle服务器上执行isql命令测试
[oracle@11g bin]$ ./isql myodbc5 -v
+----------------------+
| Connected!           |
|                      |
| sql-statement        |
| help [tablename]     |
| quit                 |
|                      |
+----------------------+
SQL> show tables;
+----------------------+
| Tables_in_test       |
+----------------------+
| t                    |
+----------------------+
SQLRowCount returns 1
1 rows fetched
6.配置tnsnames.ora文件,增加下列内容.
myodbc5=
(DESCRIPTION=
(ADDRESS=(PROTOCOL = TCP) (HOST = 192.168.0.193) (PORT = 1521))
(CONNECT_DATA =
(SID = myodbc5)
)
(HS=OK)
)
7.配置listenner.ora文件,增加下列内容.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14)
)
)
8.配置静态监听,初始化参数文件.initmyodbc5.ora
[oracle@11g ~]$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora
#加入下列内容
[注意]:HS_FDS_CONNECT_INFO是Data Source的名字.这个名字是odbc.ini文件里面定义的.
HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini
# HS_FDS_TRACE_LEVEL=user
HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN.AMERICA.ZHS16GBK
# ODBC env variables
set ODBCINI=/home/oracle/app/odbc.ini
9.重启监听,让刚才修改的文件生效.
[oracle@11g ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 19:50:34

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.193)(PORT=1521)))
The command completed successfully
[oracle@11g ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 19:50:45

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.193)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.193)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 15-DEC-2011 19:50:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.193)(PORT=1521)))
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
重启监听之后,可以看到myodbc5已经注册上了,但是状态是UNKNOWN,这种情况是正常的.因为我们还没有尝试使用该服务,最后尝试去TNSPING一下.看看能不能ping通.
[oracle@11g admin]$ tnsping myodbc5
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 20:00:10

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL = TCP) (HOST = 192.168.0.193) (PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS=OK))
OK (0 msec)
10.创建Database Link.
[oracle@11g admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 15 20:02:16 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create public database link myodbc5 connect to "mysql_user" identified by "mysql_user" using 'myodbc5';

Database link created.

SQL> select * from "t"@myodbc5;

id
----------
20
[注意]:当通过Link查询mysql的表,推荐在命名使用双引号,除非MySQL Server上设置了ANSI_QUOTES.
至此.Oracle已经能够正常通过DG4ODBC连接到MySQL了.
发帖者 时间： 没有评论:
标签：

How To Access MySQL from Oracle With ODBC and SQL

Posted by Grégory Guillou on Mar 13, 2009
The Oracle gateway for ODBC provides an almost seamless data integration between Oracle and other RDBMS. I won’t argue about its performance, limits, or relevance. It serves a few purposes; set it up and you’ll be able, for example, to create database links between Oracle and MySQL. After all, wouldn’t it be nice if you could run some of the following SQL statements?
  • select o.col1, m.col1 from oracle_tab
    o, mysql_tab@mysql m where o.col1=m.col1;
  • insert into oracle_tab (select * from mysql_tab@mysql);
This post is intended to share, the same way Karun did it for SQL Server last year, some tips related to the setup of the Oracle Gateway for ODBC with MySQL Connector ODBC on Linux.

Prerequisites

I’ve installed all the configuration on my laptop to test it. It’s running Ubuntu Intrepid Ibex 32bits, but I won’t dig into the challenge of installing the MySQL Connector ODBC 5.1 on it. All I’ll tell you is that, if I understand correctly, the version of iodbc that comes with Intrepid doesn’t support MySQL Connector ODBC 5.1 too well, and the messages that it returns are not quite explicit. To be frank, what I did is put that monkey on Augusto’s back. He sorted out everything in a few minutes. I guess I have to thank Augusto twice, just for this post! I’ve also followed his “Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex” post to install the Oracle part.
But let’s talk about the prerequisites! You need to have installed and configured the following components:

Creating a MySQLDEMO database, user and table

For the purpose of the demonstration, I’ve created a database, a user and a table named DEMO with the mysql client. You’ll find the script below.
Important Note:
The gateway for ODBC doesn’t look to work correctly when data are stored in utf8 in MySQL, whether or not the Connector/ODBC does the transformation into a non-utf8 character set. For this reason, I set the MySQL database default character set to latin1. I suspect somehow the issue is related to the ODBC driver: if I use latin1 on the client side, Oracle should not see any difference, whatever the storing character set is. Anyway, there is also a limitation on the Oracle side, and it doesn’t handle utf8 correctly with the Connector (see MySupport note 756186.1) 
$ mysql -uroot -p

create database demo character set latin1;
grant all privileges on demo.* to 'demo'@'localhost'
   identified by 'demo' with grant option;
flush privileges;
exit;

$ mysql -udemo -pdemo -Ddemo

create table demo (
   col1 integer,
   col2 date,
   col3 varchar(10),
   col4 varchar(10) character set utf8,
   col5 varbinary(10)) engine innodb;

insert into demo(col1, col2, col3, col4, col5)
   values(1, cast(now() as date), '0123456789', '0123456789', '0123456789');

select * from demo \G

*********** 1. row ************
col1: 1
col2: 2009-03-11
col3: 0123456789
col4: 0123456789
col5: 0123456789

exit;

Creating an ODBC DSN to access the DEMO database

Once the database was created, I created a user DSN in the Oracle owner, so that the the listener can get it via the dg4odbc program. By default the file that store the user DSN is $HOME/.odbc.ini, but you can change it to any file/location that fits your needs. This is how the file looks like on my server: 
$ cat ~oracle/.odbc.ini
[ODBC Data Sources]
demo = MySQL ODBC Driver 5.1

[demo]
Driver      = /home/oracle/mysql515/lib/libmyodbc5.so
DATABASE    = demo
DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample
PORT        = 3306
SERVER      = 127.0.0.1
# UID         = demo
# PWD         = demo
CHARSET     = latin1
TRACEFILE   = /tmp/myodbc-demodsn.trc
TRACE       = OFF
Make sure the CHARSET parameter is set so that it doesn’t use utf8.

Configuring dg4odbc to use the DSN

dg4odbc gets its settings from a file named init[SID].ora located in $ORACLE_HOME/hs/admin. In this case, SID is an arbitrary parameter we’ll configure in the listener.orafile (see next section). I’ve used SID=mysql for this demo, and the initmysql.ora file looks like the one below:
$ cat $ORACLE_HOME/hs/admin/initmysql.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=demo
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/home/oracle/mysql515/lib/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
# HS_NLS_NCHAR=AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini
set LD_LIBRARY_PATH=/home/oracle/mysql515/lib

#
# Environment variables required for the non-Oracle system
#
set HOME=/home/oracle
As you can see above:
  • ODBCINI is the location odbc.ini the file.
  • HS_FDS_CONNECT_INFO points to the right DSN.
  • HS_FDS_SHAREABLE_NAME points to ODBC driver shared library.
  • HS_LANGUAGE is set to avoid the problem described in Oracle MySupport “756186.1: Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql”.

Configuring the listener

To configure the listener, I had to change the listener.ora file to add the SID defined in the previous section and associate it with the gateway for ODBC; here is a copy of my setup used; I’ve kept all the settings (host, port, dynamic registration) default:
$ cat $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = mysql)
      (PROGRAM = dg4odbc)
      (ENVS ="LD_LIBRARY_PATH=/home/oracle/mysql515/lib:/usr/lib:$ORACLE_HOME/lib")
    )
  )
Once the listener is setup, you can bounce or reload it; if it’s not started, just start it:
$ lsnrctl start
And add an entry in the listener.ora file like the one below; make sure you’ve added HS=OK and that it’s not in the CONNECT_DATA clause:
MYSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)
                 (HOST = localhost)
                 (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SID = mysql)
    )
    (HS = OK)
  )

Creating a database link to connect to MySQL from Oracle

At this point, and after a little debugging of the different layers, I was able to create a database link from Oracle to MySQL. To do so, I connected to Oracle and used the CREATE DATABASE LINK command:
$ sqlplus / as sysdba

create database link mysql
  connect to "demo"
  identified by "demo"
  using 'mysql';

select "col3" from "demo"@mysql;

col3
---------------------------------
0123456789
Note:
The case policy differs between Oracle and MySQL, and you must
always surround the table and columns name with double quotes.

Using the Gateway for ODBC

If you remember correctly, I stored the string “0123456789″ in col3, col4, and col5. You’ll see some of the issues with the character set by querying a datum stored in utf8:
select "col4" from "demo"@mysql;

col4
----------------------------------------
0 1 2 3 4
and one stored in a varbinary:
select "col5" from "demo"@mysql;

col5
--------------------
30313233343536373839
But you’ll also be able to enjoy some of the features of your new Oracle/MySQL integrated environment, and be able to create a table in Oracle using data from MySQL:
create table demo as
  select "col1" col1, "col2" col2, "col3" col3
    from "demo"@mysql;

select * from demo;

COL1 COL2      COL3
---- --------- ----------
   1 11-MAR-09 0123456789
Unfortunately, you cannot insert data directly from Oracle into MySQL with an insert as select:
insert into "demo"@mysql("col1").
  select 2 from dual;

ERROR at line 2:
ORA-02025: all tables in the SQL statement must be at the remote database
But you can workaround that issue with some PL/SQL (I’m not saying it’s efficient):
begin
  for i in (select col1, col2, col3 from demo) loop
     insert into "demo"@mysql("col1","col2", "col3")
       values (2,i.col2, i.col3);
  end loop;
end;
/

select "col1","col2", "col3"
  from "demo"@mysql;

col1 col2      col3
---- --------- ----------
   1 11-MAR-09 0123456789
   2 11-MAR-09 0123456789
To prevent the access to MySQL from Oracle, you can drop the database link:
drop database link mysql;
That is it. It works pretty well so far and, despite the limits of such an approach, it can be quite useful for those that want to migrate from MySQL to Oracle.
发帖者 时间： 没有评论:
标签：

2012年7月12日星期四

让Linuxt和oracle显示中文

一、对于linux 如下：
修改服务器端语言环境，将i18n里的信息修改成以下代码
vi /etc/sysconfig/i18n
1. LANG="zh_CN.GB18030"
2. LANGUAGE="zh_CN.GB18030:zh_CN.GB2312:zh_CN"
3. SUPPORTED="zh_CN.GB18030:zh_CN:zh:en_US.UTF-8:en_US:en"
4. SYSFONT="lat0-sun16"
重新登录终端后再显示字符编码# echo $LANGzh_CN.GB18030以上这两种方法即可解决windows上传文件到linux在终端显示乱码问题。
然后安装中文字库，我的安装盘共有5张，这两个字库是在第4张盘中，文件名分别是：fonts-chinese-3.02-9.6.el5.noarch.rpm和fonts-ISO8859-2-75dpi-1.0-17.1.noarch.rpm。装完不用重启就显示正常了。
我在安装字体的时候rpm -ivh fonts-chinese-3.02-12.el5.noarch.rpm
rpm -ivh fonts-ISO8859-2-75dpi-1.0-17.1.noarch.rpm
安装完毕重启！
二、对于oracle 修改oracle用户的.bash_profile环境变量文件：
 
1、以root登录，设置linux系统语言为中文，即zh_CN(可在/etc/sysconfig/i18n文件中修改，当然前提是你需要有中文的安装包）；设置完毕之后需要重启（远程登录的话好像只能重启了）或者按ctrl+alt+backspace组合键，使语言设置生效。
之所以设置系统语言为中文，是为了我们用客户端（比如secureCRT工具）登录服务器的时候可以显示中文，以验证oracle的正确。
2、以oracle用户登录编辑家目录下的.bash_profile文件，在其后添加 export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" #for 中文
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
nls_lang是数据库的字符集变量，默认的为AMERICAN
3、以sysdba来修改数据库的字符集，步骤如下：
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> ALTER SESSION SET SQL_TRACE=TRUE;//语句跟踪
System altered.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


提示我们的字符集：新字符集必须为旧字符集的超集，这时我们www.linuxidc.com可以跳过超集的检查做更改：

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

Database altered.
查看字符集是否改变：
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

Database altered.

SQL> select * from v$nls_parameters;

PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE ZHS16GBK ——已经改变，之前为AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

19 rows selected.
重启刷新再检查一次：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$nls_parameters;

PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE ZHS16GBK
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

19 rows selected.

4、最后导入.dmp文件
$imp test/test1234
____________________________________________________________________________________导入的提示信息
Import file: expdat.dmp > mydata.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no > yes
—————————————————————————————————————————————————
上述步骤完成之后，如果在1.120服务器中运行select语句之后出现乱码（其实已经和之前出现？有很大的区别了，之所以出现乱码是因为终端字符集默认为UTF-8,我们可以更改linux下terminal的字符集为GBK登录试试看是否还有乱码出现）,这时候也可以用客户端工具 secureCRT以oracle用户登录192.168.1.120服务器，并设置后客户端的字符编码为简体中文，进行select语句查看，显示出来的就是中文了。这即是说，往数据表中插入数据的时候可以用客户端工具来完成。
发帖者 时间： 没有评论:
标签：
订阅： 博文 (Atom)