Riccardo Riva

Archive for the ‘Database’ Category

To reset the sys password of a database, do the following:

1) rename/delete ${ORACLE_HOME}/database/PWD{ORACLE_SID}.ora
2) enter the command: orapwd file=${ORACLE_HOME}/database/PWD{ORACLE_SID}.ora password=newpassword
3) start “sqlplus /nolog”
4) login with sys and the newpassword (SQL> connect sys/newpassword as sysdba)
5) now reset other passwords or just be done

Hope this help (especially me)
Bye
Riccardo

Bookmark It

This is a list of handy MySQL commands that I use frequently.

Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.

To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.
mysql> create database [databasename];

List all databases on the sql server.
mysql> show databases;

Switch to a database.
mysql> use [db name];

To see all the tables in the db.
mysql> show tables;

To see database’s field formats.
mysql> describe [table name];

To delete a db.
mysql> drop database [database name];

To delete a table.
mysql> drop table [table name];

Show all data in a table.
mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];

Show certain selected rows with the value “whatever”.
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name “Bob” AND the phone number ‘3444444′.
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name “Bob” AND the phone number ‘3444444′ order by the phone_number field.
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444′.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444′ limit to records 1 through 5.
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.
mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.
mysql> SELECT COUNT(*) FROM [table name];

Sum column.
mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.
# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process.
Start again with no grant tables.
Login to MySQL as root.
Set new password.
Exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.
# mysqladmin -u root password newpassword

Update a root password.
# mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table.
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.
mysql> DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.
mysql> flush privileges;

Delete a column.
mysql> alter table [table name] drop column [column name];

Add a new column to db.
mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.
mysql> alter table [table name] add unique ([column name]);

Make a column bigger.
mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.
mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Read the rest of this entry »

Bookmark It

Recently I was trying to move VMware VirtualCenter database from one server to another and found that for 250MB database I have about more than 30GB of transaction log (LDF) files. I wanted to get rid of this file. There are 2-3 methods but the method I’ve used was thew following :

1) Open SQL Management Studio Express

2) Detach the database from the SQL Server Management Studio.

3) Go to the location of transaction log and rename it (don’t delete the file yet!!)

4) Attach the database without the log file. When you do this step make sure you highlight the log file entry in the wizard and remove it. (Final attach would happen only with one file i.e. with the MDF file).

At this point there would be a new log file created by the SQL Server.

5) Edit the database properties, and select the preferred increment policy of your newly created log file, enabling automtic grow, define increment step and also define maximum dimension to avoid the problem above.

4) Delete the log file once the database is attached and you have verified it.

You can save a lot of space with above steps.
I’ve tested this procedure with VMware VirtualCenter 2.5 Update4, Microsoft SQL 2005 Express with the latest service pack, Microsoft Management Studio Express, and all works fine.
If i remeber well, this would work even with SQL 2000.

Hope this help

Bye
Riccardo

Bookmark It

The following is a list of MySQL useful commands.

# To setup root password (first execution)
mysqladmin -u root password 'new_password'

# To login to MySQL
mysql -u root -p

# To create a database
create database dbname;

# To change database
use dbname;

# To create a user and assign to it permission to database
# Grant permission only from localhost connections
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

# Grant permission on all connections
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

# Manage MySQL export
# Copy all db1 content to /backup-db/db1 folder
mysqlhotcopy db1 /backup-db/db1

# Create a DB dump to a file
mysqldump db1 > db1_dump_db.sql -u root -p

Hope this help

Bye
Riccardo

Bookmark It

This post will assume you just have a fully functional working mail server and you want do download all emails from some mailboxes located on another mail server, tipically located on your service provider’s one.
This post guide will guide you to install and configure fetchmail and use it with your RHEL 5 or newer, you should use all the following information also with CentOS without change any line, but you shoud adapt it to every Linux distribution.
Most likely you want to setup fetchmail to download your domain mailbox over pop3 and inject into Postfix (or whatever you use as MTA).

To install fetchmail use the following :

yum install fetchmail -y

You should decide how to use fetchmail :

* as a daemon
* as a cron scheduled task

Read the rest of this entry »

Bookmark It

This post will show a backup script used to create copies for a server running Oracle 10.2, some application server like FourJS and JBoss and some custom application written in java.

The backup process is divided in some parts :
- Variable declaration and export
- Oracle export and compress
- Data folder export and compress
- Application Server export and compress
- Tape writing of all compress file
- Tape checking
- Mail logging

This post assume you have the following enviroments, if you have something different you should only change variable declaration and comment some lines to have this script works.

- Oracle SID : DB01
- Oracle main folder : /oracle/product/10.2/db_1
- Oracle user/password : system/system
- DATA main folder to backup : /data
- APPSERVER main Folder : /appsrv
- Main backup folder : /data/backup

- Tape device : /dev/st0
Read the rest of this entry »

Bookmark It

When using Oracle 10g Release 2, a call to “dbstart" might result (perhaps every time) in the following error message:

Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr

This is due to an internal path in the dbstart script.

To correct this, edit the “$ORACLE_HOME/bin/dbstart” script and replace the following line :

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle

With this:

ORACLE_HOME_LISTNER=$ORACLE_HOME

The dbstart script shold now start the listener as expected.

For more information see:

Automating Shutdown and Startup (10.2)

Automating Startup and Shutdown (10.1)

Automating Database Startup and Shutdown (9.2)

Hope this help

Bye
Riccardo

Bookmark It

The following is a very simple init script for Oracle on Red Hat Enterprise Linux.
I’m using it successfully in RHEL 5.2 but you can use it on other systems, only double check the various path.

#!/bin/sh
#
# ORACLE Control Script
# chkconfig: 3 80 20
#
# Description: Here is a little startup/shutdown script for Oracle 10g on RedHat systems
#
# Author : Riccardo Riva
#
# Source LSB function library.
[ -f /lib/lsb/init-functions ] && . /lib/lsb/init-functions

#Assuming have Oracle installed on :
# /oracle/product/10.2.0/db1
ORACLE_HOME=/oracle/product/10.2.0/db_1

#Assuming have “oracle” user
ORACLE_OWNER=oracle

# Edit this with your DB instance name
ORACLE_DB=DB_INSTANCE_NAME

if [ ! -f $ORACLE_HOME/bin/dbstart ]
then
echo “Oracle startup: cannot start”
exit
fi

case “$1″ in
’start’)
# Start the Oracle databases:
su – $ORACLE_OWNER -c “$ORACLE_HOME/bin/lsnrctl start”
su – $ORACLE_OWNER -c $ORACLE_HOME/bin/dbstart
;;
’stop’)
# Stop the Oracle databases:
su – $ORACLE_OWNER -c $ORACLE_HOME/bin/dbshut
su – $ORACLE_OWNER -c “$ORACLE_HOME/bin/lsnrctl stop”
;;
’status’)
if su -l $ORACLE_OWNER -c “${ORACLE_HOME}/bin/tnsping ${ORACLE_DB} >/dev/null 2>&1″
then
exit 0
else
exit 1
fi

;;
esac

Create this script and put it in your /etc/init.d/ directory

Make this script excutable

Use “chkconfig” to set the startup or the shutdown for this script in the desired runlevels.

Hope this help avoid wasting time

Bye
Riccardo

Bookmark It

Recover MySQL database root password

Sometimes you couls have to recover MySQL database server password.
You could do it follow these easy steps :

1: Stop the MySQL server process.

/etc/init.d/mysql stop

2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password

mysqld_safe –skip-grant-tables &

3: Connect to mysql server as the root user

mysql -u root

4: Setup new root password

mysql> use mysql;
mysql> update user set password=PASSWORD(”NEW-ROOT-PASSWORD“) where User=’root’;
mysql> flush privileges;
mysql> quit

5: Exit and restart MySQL server

/etc/init.d/mysql stop

6: Start MySQL server and test it

/etc/init.d/mysql start
mysql -u root -p

Hope this help

Bye
Riccardo

Bookmark It

Some days ago I had to install an Oracle 10.2.0 x64 on system with Red Hat Enterprise Linux 5.2 x64.

I found some problem, and the following is the prerequisites and some tricks to make the setup process works.

Prerequisites packages :

binutils-2.17.50.0.6-2.el5
compat-gcc-34-3.4.6-4
compat-gcc-34-c++-3.4.6-4
compat-libstdc++-33-3.2.3-61
compat-libstdc++-33-3.2.3-61(i386)
control-center-2.16.0-14.el5
gcc-4.1.1-52.el5
gcc-c++-4.1.1-52.el5
gdbm-1.8.0-26.2.1
glibc-2.5-12
glibc-common-2.5-12
glibc-devel-2.5-12
glibc-devel-2.5-12(i386)
libgcc-4.1.1-52.el5(i386)
libgcc-4.1.1-52.el5(x86_64)
libgnome-2.16.0-6.el5
libstdc++-devel-3.4.3-22.1
libXp-1.0.0-8.i386
libXp-1.0.0-8.x64
make-3.81-1.1
sysstat-7.0.0-3.el5.x86_64.rpm
util-linux-2.13-0.44.e15.x86_64

Tricks
- Make sure to put in your /etc/hosts the ip address and the name and FQDN name of your server as the folowing example :
192.168.1.1 server.myfactory.local server

- Make sure you have SELINUX disabled, using the following command :
cat /etc/selinux/config | grep SELINUX

- Create some necessary user and group :
groupadd oinstall
groupadd dba
groupadd oper
useradd –g oinstall –G dba oracle

- Assing a password to “oracle” user and don’t forget to assign it a usfeul shell, because you must login with “oracle” to an X-Session to run the installer.

Setup Procedure

After checking all the above, you must login as “oracle” to an X-Session (local or remote as you prefer) and run the following command :
runInstaller -ignoreSysPrereqs

You must use “-ignoreSysPrereqs” because RHEL5 is not in the operative system supported list.
This is not a problem, and you could use that option to avoid installer exit with a warning.

If all prerequisites and tricks is fine, you could now procede with Oracle installation.

Hope this help someone avoid wasting time.

Bye
Riccardo

Bookmark It

Here is the code for an advanced startup/shutdown script for JBoss.

I’m using it with JBoss 3.2.6 on a RHEL 5.2 Advanced Platform, and all works well.

I’ve added the “status” function for use it in a cluster suite and a new function to check if Oracle DB Server is up and running, otherwise JBoss doesn’t deploy well all developers files.

When you use “start” function, the script use a simple “tnsping” based function that loop into themselves until Oracle is not fully working and then sleep other 10 seconds to start JBoss.

JBoss startup/shutdown script with Oracle Support

Hope this help
Bye
Riccardo

Bookmark It

In these days I had to install Oracle 9i (a new version … don’t think so ..) on a RHEL 5 Advanced Platform and after some troubles all works good.

The followings is the steps for a correct setup.

Here we go (skipped basic steps as user creation, standard kernel params and limits settings):

1. Check for required packages:

rpm -q compat-db compat-gcc-34 compat-gcc-34-c++ compat-libgcc-296 compat-libstdc++-296
and
rpm -q compat-libstdc++-33 gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers libgcc make libXp

2. Set environment
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/920
ORACLE_SID=ORCL
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/binexport ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH

2. Workaround #1

su -
cd /usr/lib
ln -s libstdc++-3-libc6.2-2-2.10.0.so libstdc++-libc6.1-1.so.2

3. Download and Install:

compat-libcwait-2.1-1.i386.rpm

compat-oracle-rhel4-1.0-5.i386.rpm

rpm -Uvh compat-libcwait-2.1-1.i386.rpm

rpm -Uvh compat-oracle-rhel4-1.0-5.i386.rpm –nodeps

4. Workaround #2

su -
cd /usr/bin
ln -s gcc34 gcc32

5. Workaround #3
su -
cd /usr/lib
ln -s libgdbm.so.2.0.0 libdb.so.2

6. runInstaller

7. When NETCA/DBCA will fail

8. Workaround #4
I suggest to apply 9.2.0.8 patchset before.

cd $ORACLE_HOME
rm JRE
ln -s $ORACLE_BASE/jre/1.3.1 JRE
cd JRE/bin
ln -s java jre
cd i386/native_threads/
ln -s java jre

Hope this help someone to avoid waste my same time.
Bye
Riccardo

Bookmark It