My Blog

October 8, 2008

Encountered error when using Mysql ODBC client connects to server

Filed under: Mysql — by enotebook @ 3:50 pm

I actually got this error before while using Mysql ODBC client connects to Mysql server, however i did not note it down at once, so when i face the same problem i even don’t know how to fix it, surely it has been solved after doing some research, here i place a post as note.
Mysql ODBC driver: MySQL ODBC 3.51 Driver
Mysql Server version: MySQL Community Server 5.0.51a
Error message: Client does not support authentication protocol requested by server; consider upgrading MySQL client
MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients, when using an older client to connect the server, you may get with the error message.
To solve the problem, it needs to issue a command to reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program like following:
mysql> SET PASSWORD FOR
-> [email=’some_user’@’some_host’] = OLD_PASSWORD(‘newpwd’);
Alternatively, use UPDATE and FLUSH PRIVILEGES:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(‘newpwd’)
-> WHERE Host = ‘some_host’ AND User = ‘some_user’;
mysql> FLUSH PRIVILEGES;

Done.

Advertisements

September 25, 2008

Add new diskgroup in oracle rac

Filed under: Oracle — by enotebook @ 8:56 am

Host information – Linux & Oracle
$ uname -a
Linux rac1.andy.com 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL>

Use sqlplus to connect ASM instance on node1(rac1). Check the value of instance_type to make sure it’s connecting to ASM instance
$ export ORACLE_SID=+ASM1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Sep 24 16:03:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> show parameter instance_type;
NAME TYPE VALUE
———————————— ———– ——————————
instance_type string asm
SQL>

Check the raw devices to determine if they have been detected.
SQL> select path from v$asm_disk;
PATH
——————————————————————————–
/dev/raw/raw5 # detected – this is the newly bounded raw device
/dev/raw/raw4
/dev/raw/raw3
/dev/raw/raw2
4 rows selected.
If there is no newly bounded raw devices detected, try to modify the value of parameter – asm_diskstring, and re-check
SQL> show parameter asm_diskstring
NAME TYPE VALUE
———————————— ———– ——————————
asm_diskstring string
SQL> alter system set asm_diskstring=’/dev/raw/raw*’;
System altered.

Issue the command to create diskgroup
SQL> create diskgroup DG2 external redundancy disk ‘/dev/raw/raw5’;
Diskgroup created.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
———— —————————— ———– ———-
1 DG1 MOUNTED 6134
2 RECOVERYDEST MOUNTED 2047
3 DG2 MOUNTED 3067
SQL>

Diskgroup mounted automatically on this node, but not mounted on node2, need to mount it manually.
$ ssh rac2
Last login: Wed Sep 24 16:00:05 2008 from rac1.andy.com
$ export ORACLE_SID=+ASM2
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Sep 24 17:08:31 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
———— —————————— ———– ———-
1 DG1 MOUNTED 6134
2 RECOVERYDEST MOUNTED 2047
0 DG2 DISMOUNTED 0
SQL> alter diskgroup DG2 mount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
———— —————————— ———– ———-
1 DG1 MOUNTED 6134
2 RECOVERYDEST MOUNTED 2047
3 DG2 MOUNTED 3067
SQL>
Create tablespace.
SQL> create tablespace test datafile ‘+DG2/test.dbf’ size 10M;
Tablespace created.

Done.

September 23, 2008

Processes to solve problem of ora-12514(sometimes comes out as ora-12523)

Filed under: Oracle — by enotebook @ 5:28 pm

Just finished the Oracle 10g RAC installation on linux with using VMware server, everything runs smoothly without testing connection to the instance on first node(rac1). Details like blowing.
$ sqlplus [email=system@devdb1]system@devdb1
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 23 15:14:30 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
It raised an error when trying to connect devdb1(instance on first node named rac1), enven i was allowed to connect devdb2 and devdb. crs_stat -t was issued:
$ crs_stat -t
Name Type Target State Host
————————————————————
ora…..crm.cs application ONLINE ONLINE rac1
ora….db1.srv application ONLINE ONLINE rac2
ora.devdb.db application ONLINE ONLINE rac1
ora….b1.inst application ONLINE ONLINE rac1
ora….b2.inst application ONLINE ONLINE rac2
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
$
Every resource is running fine. Then try to check the status of listener.

$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-SEP-2008 15:15:30
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$
Outputs like above showing us the listener does not even ‘trurely started'(started incorrectly). Tried to restart it and re-check the status.

$ crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.

The outputs are same with above while excuting command of lsnrctl status. Tried to compare the content of listener.ora in rac1 with rac2 and found that HOST string is not same within rac2, HOST = 172.16.113.32 in rac2, but HOST = 127.0.0.1 in rac1, i guess it causes the problem. Modify it and make it sync with rac2.

$ more listener.ora
# listener.ora.rac1 Network Configuration File: /u01/app/oracle/product/10g/db/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.andy.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10g/db)
(PROGRAM = extproc)
)
)
$ vi listener.ora

Restart listener on rac1.
$ crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.

Check the status.
$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-SEP-2008 15:18:07
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date 23-SEP-2008 15:18:01
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10g/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10g/db/network/log/listener_rac1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.113.131)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.113.31)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
$

Then connect to devdb1.
$ sqlplus [email=system@devdb1]system@devdb1
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 23 15:29:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>

It works!
Done.

August 20, 2008

MySql installation on Linux

Filed under: Mysql — by enotebook @ 3:16 pm

Information about Host & MySql as belows:
# uname -a
Linux localhost.localdomain 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 5.0.51a-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> select version();
+————-+
| version() |
+————-+
| 5.0.51a-log |
+————-+
1 row in set (0.00 sec)
mysql>

Install Mysql using RPM
rpm -ivh rpmfile (you will encounter error like the mysql package conflicts since it has been installed when you installing OS, use rpm -e to uninstalled them and try again)
default Installation location:
1、data folder
/var/lib/mysql/
2、configuration files
/usr/share/mysql(mysql.server etc.)
3、binary file
/usr/bin(mysqladmin mysqldump etc.)
4、booting scripts
/etc/rc.d/init.d/

Install Mysql using distributed file
1. create folder and grant proper permission
# mkdir -p /u01/CfgFiles
# chown -R mysql /u01/
# chgrp -R mysql /u01/

2. transfer file(mysql-5.0.51a-linux-i686.tar.gz) to linux host

3. unzip
# tar -xf mysql-5.0.51a-linux-i686.tar
# mv mysql-5.0.51a-linux-i686 MySql
# ls
app lost+found MySql mysql-5.0.51a-linux-i686.tar oradata

4. copy my-large.cnf & mysql.server to a specific location and moodify them accordingly
# cd MySql/
# cp support-files/my-large.cnf support-files/mysql.server /u01/CfgFiles/
# cd /u01/CfgFiles/
# mv my-large.cnf my.cnf
# mv mysql.server mysqld
# vi mysqld
1). specify the location path to basedir&datadir in line number 46:
46 basedir=/u01/MySql
47 datadir=/u01/MySql/data
2). specify the configuration file in line number 195:
195 #conf=/etc/my.cnf
196 conf=/u01/CfgFiles/my.cnf
# vi my.cnf
1). specify the location path for socket file in line number 20 & 28:
20 #socket = /tmp/mysql.sock
21 socket =/u01/MySql/mysql.sock
28 #socket = /tmp/mysql.sock
29 socket =/u01/MySql/mysql.sock

5. startup Mysql
# ./mysqld start
Starting MySQL [ OK ]
#
6. Modify password for root:
# mysqladmin -u root password ‘newpassword’

7. Enable remote login:
mysql> grant all on *.* to [email=root@’%’]root@’%’ identified by ‘password’;

Done.

September 17, 2007

x$ table & v$ view in oracle

Filed under: Oracle — by enotebook @ 3:07 pm

We can get the structure of both objects of x$&v$, like follows:
The names for the x$ tables can be queried with:
select kqftanam from x$kqfta

The names for the v$ views can be queried with:
select * from v$fixed_view_definition order by view_name

Additional, a oracle tip from Donald K.Burleson:
Querying the X$ structures
One of the most hidden areas of the Oracle database is the X$ structures. As we may know, the X$ structures are the innermost tables that are used to build the common v$ views that we use to query the data dictionary.
Here is an idea of the naming of these X$ structures. Of course, these internal structures change between Oracle releases, and this list is from Oracle 8.1.5.

X$ks – Kernel Services

x$ksmfs kernel services, memory fixed SGA
x$ksmfsv kernel services, memory fixed SGA vectors
x$ksmjs kernel services, memory java_pool summary
x$ksmlru kernel services, memory LRU
x$ksmls kernel services, memory large_pool summary
x$ksmmem kernel services, memory
x$ksmpp kernel services, memory process pool
x$ksmsd kernel services, memory SGA definition
x$ksmsp kernel services, memory shared pool
x$ksmspr kernel services, memory shared pool reserved
x$ksmss kernel services, memory shared_pool summary
x$ksmup kernel services, memory user pool
x$ksqst kernel services, enqueue status
x$ksulop kernel services, user long operation
x$ksulv kernel services, user locale value
x$ksupr kernel services, user process

X$kc – kernel cache

x$kcbfwait kernel cache, block file wait
x$kcbwait kernel cache, block wait
x$kcccp kernel cache, controlfile checkpoint progress
x$kcfio kernel cache, file I/O
x$kclfh kernel cache, lock file header
x$kclfi kernel cache, lock file index
x$kcluh kernel cache, lock undo header
x$kclui kernel cache, lock undo index

X$kq – kernel query

x$kqfco kernel query, fixed table columns
x$kqfdt kernel query, fixed table
x$kqfp kernel query, fixed procedure
x$kqfsz kernel query, fixed size
x$kqfta kernel query, fixed table
x$kqfvi kernel query, fixed view
x$kqfvt kernel query, fixed view table

X$kg – kernel generic

x$kghlu kernel generic, heap LRUs
x$kgllk kernel generic, library cache lock
x$kglob kernel generic, library cache object
x$kglpn kernel generic, library cache pin
x$kglst kernel generic, library cache status

X$kz – Kernel security

x$kzsro kernel security, system role

X$le – lock element

x$le lock element
x$le_stat lock element status

The X$ views are completely undocumented, and Oracle does not provide any assistance for deriving the meaning of the contents of the X$ structures.

June 5, 2007

db file sequential read & db file scattered read

Filed under: Oracle — by enotebook @ 1:53 pm

Why is a random-access operation known as “db file sequential read” and a sequential-access operation known as “db file scattered read”?
But the wait-events are named more for the way that the I/O calls themselves are made, rather than for their footprint on the I/O subsystem. The random-access single-block reads are performed sequentially one right after another, synchronously. This is because the descent, from root to branch to leaf, of a tree-structure hierarchy is a performed by reading a block in order to find out where to go next. Thus, we cannot read several blocks together, because it is possible that the next node we will visit will be behind us. Hence, the naming with the word sequential, since we have to progress sequentially from one block to another.

On the other hand, when performing sequential-access multi-block reads for a FULL table-scan, there is no unpredictability involved. We know exactly which blocks we are going to access, so the only question is getting them all read quickly enough. To do that, we can employ parallel processing and asynchronous operations. Hence the naming with the word scattered, since we can scatter dozens or hundreds of I/O requests to the operating system, and just sort the results when we get them back.

May 9, 2007

Redo Logs and Recovery

Filed under: Oracle — by enotebook @ 5:57 pm

After I read John Hibbard’s article which is named ‘Redo Logs and Recovery’, try to record my understanding as following:

Redo log operation:
1: Towards the SQL statement of UPDATE emp SET sal = 10 WHERE id = 1234, firstly, search in data buffer cache to determine whether the block of the record has already been there. If not, then access the data file and retrieve it into data buffer cache.

2. Retrieve the rollback block and create the image in data buffer cache. Then log an entry in the log buffer cache:
Entry = TransID + File# + block# + Row# + Column# + value

3. Change the value of sal in data buffer cache, and place an entry with new value into log buffer cache.

4. When the user commits the transaction, then place an entry into log buffer cache:
Entry = TransID + Commit + SCN + Timestamp

For the log buffer cache, it invokes LGWR process to write the log into redo log file when the following events occurred:
1) Commit;
2) One third full of log buffer cache
3) 1Migabytes log
4) DBWR process writes the data into data file
5) 3 seconds
6) Checkpoint

Database recovery:
When the database startup after a crash event, it does following steps to recover the database:
1. Roll toward
Redo all the committed entries exist in log file, called‘re-committed’ .

2. Open database
Database begins to work.

3. Rollback
Rollback the changes which have been already recorded into data file according to the redo log. Just need to find the fist log entry of each transaction, and then use its values to rollback.

Notice that after the version of 9i, DBWR events have been recorded into log file, it enables the recovery process much faster (it’s just used for Rollback process, the roll toward process will skip all the entries with its File# + block# = File_block# ).
DBWR entry = DBWR + File_block# + File_block# + File_block# + File_block#
(File_block# = File# + block#)

« Previous Page

Blog at WordPress.com.