Haven't been updating my blog for the last couple of years - But here I am, planning to update it frequently.
My wife gifted me a new Kindle (A Kindle 3 Wi-Fi) and I liked it a lot. I think its the best gift one can get/give. I'll keep posting the list of books I read with some reviews.
- Vish.
Monday, February 21, 2011
Monday, March 30, 2009
A nice "SEARCH" plugin for wordpress
Here's a nice "SEARCH" plugin for wordpress. I'm impressed with the features it has. Does a good job of highlighting the search words, also showing a small part of the each post/page, so that you can see all the posts related to your search.
http://urbangiraffe.com/plugins/search-unleashed/
-- Vish.
http://urbangiraffe.com/plugins/search-unleashed/
-- Vish.
Friday, February 20, 2009
RMAN incremental COLD Backup and Restore
Here's a brief overview of how to do a cold backup with RMAN and restore/duplicate it.
1. RMAN FULL COLD Backup.
rman target / catalog rman/rman@prman
run
{
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP INCREMENTAL LEVEL 0 DATABASE include current controlfile;
ALTER DATABASE OPEN;
}
2. RMAN Incremental COLD Backup.
rman target / catalog rman/rman@prman
run
{
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP INCREMENTAL LEVEL 1 DATABASE include current controlfile;
ALTER DATABASE OPEN;
}
3. RMAN Duplicate to restore the cold backup to a different server with a different Name.
rman target sys/password@ catalog rman/rman@rman
RMAN> connect auxiliary /
run
{
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=,NB_ORA_CLIENT=';
set newname for datafile 1 to '/uXX/oradata//system01.dbf';
set newname for datafile 2 to '/uXX/oradata//undotbs01.dbf';
set newname for datafile 3 to '/uXX/oradata//sysaux01.dbf';
set newname for datafile 4 to '/uXX/oradata//users01.dbf';
set newname for tempfile 1 to '/uXX/oradata//temp01.dbf';
duplicate target database to
LOGFILE
GROUP 1 ('/uXX/oradata//redo01a.log') SIZE 50M REUSE,
GROUP 2 ('/uXX/oradata//redo02a.log') SIZE 50M REUSE,
GROUP 3 ('/uXX/oradata//redo03a.log') SIZE 50M REUSE;
release channel ch00;
}
RMAN Duplicate for a cold backup will automatically use "Recover NOREDO", since it knows that its a cold backup and hence does not look for online-redo-logs.
4. Restore the database to the same server.
run
{
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=,NB_ORA_CLIENT=';
#set until time "to_date('18-JUN-08 11:56:38','DD-MON-YY HH24:MI:SS')";
restore controlfile;
sql 'alter database mount';
restore database;
recover database noredo; ==> This is to tell RMAN that its a cold backup restore
and do not look for online-redo-logs.
}
1. RMAN FULL COLD Backup.
rman target / catalog rman/rman@prman
run
{
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP INCREMENTAL LEVEL 0 DATABASE include current controlfile;
ALTER DATABASE OPEN;
}
2. RMAN Incremental COLD Backup.
rman target / catalog rman/rman@prman
run
{
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP INCREMENTAL LEVEL 1 DATABASE include current controlfile;
ALTER DATABASE OPEN;
}
3. RMAN Duplicate to restore the cold backup to a different server with a different Name.
rman target sys/password@
RMAN> connect auxiliary /
run
{
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=
set newname for datafile 1 to '/uXX/oradata/
set newname for datafile 2 to '/uXX/oradata/
set newname for datafile 3 to '/uXX/oradata/
set newname for datafile 4 to '/uXX/oradata/
set newname for tempfile 1 to '/uXX/oradata/
duplicate target database to
LOGFILE
GROUP 1 ('/uXX/oradata/
GROUP 2 ('/uXX/oradata/
GROUP 3 ('/uXX/oradata/
release channel ch00;
}
RMAN Duplicate for a cold backup will automatically use "Recover NOREDO", since it knows that its a cold backup and hence does not look for online-redo-logs.
4. Restore the database to the same server.
run
{
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=
#set until time "to_date('18-JUN-08 11:56:38','DD-MON-YY HH24:MI:SS')";
restore controlfile;
sql 'alter database mount';
restore database;
recover database noredo; ==> This is to tell RMAN that its a cold backup restore
and do not look for online-redo-logs.
}
Monday, January 12, 2009
Slumdog Millionaire !!
Watched the movie "Slumdog Millionaire" and really liked it. It is one of those classic movies where you won't get bored of watching it. Good direction, photography, TIGHT screenplay and a new story makes it worth watching.
-- Vish.
-- Vish.
Three mistakes of my life !! (Chetan Bhagat)
"Three mistake of my life", a book by Chetan Bhagat is an interesting read. Its a small book and can be completed within 3-4 hrs. Its an interesting story about a businessman who wants to achieve something big in his life and how his mistakes cost him, almost his life.
-- Vish.
-- Vish.
A speech by Chetan Bhagat
A good speech from Chetan Bhagat, a writer from India. It is about how to keep yourself engaged in life and not feel bored. The moral of the story is "Always have a goal and try to keep acheiving it. Also, have a balanced life (work and family)"
http://d.scribd.com/docs/1qzpkyw5ih8sc69agzkp.pdf
-- Vish.
http://d.scribd.com/docs/1qzpkyw5ih8sc69agzkp.pdf
-- Vish.
remsh or ssh?
You might have a centralized server that has remsh/ssh authentication setup with all the other servers in your environment. You want to write a script to, lets say, get a crontab listing of all the servers. The command below shows if you can either remsh or ssh.
=========================================
ssh -o BatchMode=yes $i /bin/truex
if [[ $? -eq 0 ]];then
echo "ssh enabled"
else
echo "remsh enabled"
fi
=========================================
-- Vish
=========================================
ssh -o BatchMode=yes $i /bin/truex
if [[ $? -eq 0 ]];then
echo "ssh enabled"
else
echo "remsh enabled"
fi
=========================================
-- Vish
Friday, January 09, 2009
My book library !
1. The Book Thief
A book based on a German backdrop.
2. Three cups of Tea
One Man's Mission to Promote Peace . . . One School at a Time.
3. Darfur Diaries : Stories of survival
A genocide in Darfur taking place and the world ignores it !!
4. Three mistake of my life
A book by Chetan Bhagat about three mistakes a businessman did in his life !!
5. The last lecture
A book by Randy Paush, about acheiving childhood dreams.
A book based on a German backdrop.
2. Three cups of Tea
One Man's Mission to Promote Peace . . . One School at a Time.
3. Darfur Diaries : Stories of survival
A genocide in Darfur taking place and the world ignores it !!
4. Three mistake of my life
A book by Chetan Bhagat about three mistakes a businessman did in his life !!
5. The last lecture
A book by Randy Paush, about acheiving childhood dreams.
Script to push any script to all the servers in a unix environment
It is always helpful for any dba to have a script that can push any regularly used scripts to all the servers. In my case, we had a centralized server where have all the scripts and there was authentication setup, either remsh or ssh. The script below checks if 1) it can ping the server, 2) if it can ping it, can it ssh? 3) If it cannot ssh, can it remsh?
I run this script from Sun OS. If you run it from any other OS like linux, the ping command would be different. Change it accordingly.
Operating System : SunOS
cat push_script.sh
#!/usr/bin/ksh
list=`cat /tmp/complete_server_list.lst`
for i in $list
do
ping -s $i 64 1 |grep -w "0% packet loss" # Check if we can ping the server
if [[ $? -eq 0 ]];then
ssh -o BatchMode=yes $i /bin/true # Check if ssh is enabled, if not use remsh/rcp
if [[ $? -eq 0 ]];then
scp -p /tmp/test_script $i:/tmp/test_script
else
rcp -p /tmp/test_script $i:/tmp/test_script
fi
else
echo "${i}" >> /tmp/cantping.lst
fi
done
===================== Script End==================================
A good way to list the filesystem space in MB's or GB's on a unix server
A good way to list the filesystem space in MB's or GB's on a unix server.
The following commands will give the listing of the free space on the server in descending order. In my case, I'm searching for mountpoints which have /u*. This way, I get a quick overview of the free space on the server.
HP-UX:
bdf /u* | awk '($0 !~ /ounted/ && $5 !~ /100/) {printf("%s\t%dM\n",$6,$4/1024)}' | sort -n -r +1|grep /u |grep -v "/usr"
SUN OS:
df -k /u* | awk '($0 !~ /ounted/ && $5 !~ /100/) {printf("%s\t%dG\n",$6,$4/1024/1024)}' | sort -n -r +1 |grep /u |grep -v "/usr"
The following commands will give the listing of the free space on the server in descending order. In my case, I'm searching for mountpoints which have /u*. This way, I get a quick overview of the free space on the server.
HP-UX:
bdf /u* | awk '($0 !~ /ounted/ && $5 !~ /100/) {printf("%s\t%dM\n",$6,$4/1024)}' | sort -n -r +1|grep /u |grep -v "/usr"
SUN OS:
df -k /u* | awk '($0 !~ /ounted/ && $5 !~ /100/) {printf("%s\t%dG\n",$6,$4/1024/1024)}' | sort -n -r +1 |grep /u |grep -v "/usr"
Monday, October 27, 2008
export ORACLE_HOME from oratab
The script below can export ORACLE_HOME without any issues with commented lines. If would first cat oratab file, then trim it out, so that we get "#" at the start of the line. Then it would remove any lines with "#" as the start of the line. This would remove any commented out lines. And then it will get you the second column out, which is ORACLE_HOME.
ORACLE_HOME=`cat /etc/oratab| sed 's/^ *\(.*\) *$/\1/' |grep -v ^\# |grep -w "$SID"|awk -F: '{print $2}'`
-- Vish.
ORACLE_HOME=`cat /etc/oratab| sed 's/^ *\(.*\) *$/\1/' |grep -v ^\# |grep -w "$SID"|awk -F: '{print $2}'`
-- Vish.
Thursday, May 08, 2008
EXP-00002: error in writing to export file
I've run the script to take a full export of my database and it has run successfully, however when I've included it in the crontab, it failed with the error below.
When I've checked the size of the dump file, it was exactly 1.999 GB, which means that it has hit the file size limit of 2GB. Again, when I ran it manually, it did work.
It was because crontab did not know anything about the ulimits of user "Oracle". It was using the default ulimit value and when it hit the 2GB size limit, my export errored out. I've edited my crontab entry to use the .profile file to set the environment, before it ran my export and it worked fine.
The crontab entry was modified as below.
Hope this helps.
-- Vish.
EXP-00002: error in writing to export file
EXP-00002: error in writing to export fileerror closing export file
EXP-00000: Export terminated unsuccessfully
It was because crontab did not know anything about the ulimits of user "Oracle". It was using the default ulimit value and when it hit the 2GB size limit, my export errored out. I've edited my crontab entry to use the .profile file to set the environment, before it ran my export and it worked fine.
The crontab entry was modified as below.
# +---------------------------------------------------------+------------------------+
# | Daily FULL database export for DB TEST1 | Daily at 12:00 AM |
# +---------------------------------------------------------+------------------------+
00 00 * * * (./home/oracle/.profile; /oracle/app/admin/scripts/daily_export.sh TEST1 >/tmp/export_TEST1.log 2>&1)
Hope this helps.
-- Vish.
Wednesday, April 23, 2008
Active dataguard - oracle 11g !!
Oracle 11g dataguard has this nice feature called Active dataguard, where you can have your standby database open read only and still have the redologs being applied to the standby database. This is definitely a huge improvement to standby functionality, 'cos you can leverage all your select queries to the standby database, while using the primary for all DDL/DML activity.
Here are the steps to follow.
1. Disable the redolog apply.
2. Alter database open readonly; (on standby database)
3. Enable the redolog apply.
and you are done.
-- Vish.
Here are the steps to follow.
1. Disable the redolog apply.
2. Alter database open readonly; (on standby database)
3. Enable the redolog apply.
and you are done.
-- Vish.
Tuesday, April 22, 2008
Command history for sqlplus - rlwrap !!
There’s a utility called rlwrap that you can install and once that is done, you can use UP arrow and sqlplus remembers your previous commands (as in MS-DOS).
This procedure is for Sun Solaris Sparc 64 bit OS.
Step 1 :
1. Download ncurses-5.4-sol10-sparc-local
2. Download readline and rlwrap packages from http://ivan.kartik.sk/index
Step 2 :
To install rlwrap on Solaris execute following commands:
gunzip ncurses-5.4-sol10-sparc-local
gunzip readline-5.2-solaris9-sparc.gz
gunzip rlwrap-0.28-solaris9-sparc.gz
pkgadd -d readline-5.2-solaris9-sparc
pkgadd -d rlwrap-0.28-solaris9-sparc
pkgadd -d ncurses-5.4-sol10-sparc-local
Step 3 :
As readline library is located in /usr/local/lib don't forget to add that line to LD_LIBRARY_PATH.
Also put following line to your .profile:
alias sqplus='/usr/local/bin/rlwrap sqlplus'
-
Monday, April 07, 2008
Who is using temporary tablespace
John Emmons has given a nice script to show who is using the temporary tablespace and how much, a very useful script.
Here's the script that he has on his site.
set pagesize 1000
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;
-- Vish.
Here's the script that he has on his site.
set pagesize 1000
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;
-- Vish.
Om Shanti Om
I could not believe that an Indian movie "Om Shanti Om" is rated as top 47th movie among the top 50 movies on movies.yahoo.com for the year 2007.
You can check it out here.
http://movies.yahoo.com/mvc/top10?lid=660
-- Vish.
You can check it out here.
http://movies.yahoo.com/mvc/top10?lid=660
-- Vish.
Spiritualism Vs Hunger !!
Its been a long time that I've visited this blog. I've been busy with the last two jobs and didn't really get anytime to get here. But I plan to be active from now on. I've decided to include non-oracle related stuff also here, so that I have a way to communicate myself with the whole world.
Coming to Spiritualism vs Hunger. I've always wanted to write about it and finally the time has come. I believe in spiritualism and it is definitely needed for humans. I do respect people who believe in spiritualism. It gives you peace of mind and strength to face difficulties.
Coming to the point, I've seen people donating money to temples and people in India do that a lot. I don't agree with them at all. There are people in India who are in need of food. In my view hunger comes first and then spiritualism, luxury or whatever it is. HUNGER is the first thing that has to be taken care of. Feed the people first and then the temples. There's a saying in telugu (my mother tongue), "MANAVASEVE MADAVASEVA", which I believe in. It means "Service to human is Service to God", which is absolutely true.
And why do we donate money to the temples? I don't think a lot of people understand or even try to understand the reason behind it. I was doing it because my parents taught me to do so and I believe that so is the reason with so many other people. It is just that people believe in God and they believe that donating money to God is a good thing. And what is the money used for? To promote religion and to build temples? Forget about building temples when there are people around dying with hunger. Lets feed the hungry people first. I'm not saying that you leave your worldly luxuries and then spare the money to feed these people. All I'm saying is that lets spare the money that we donate to the temples and use that money to feed these hungry people.
I don't think any religion would say NO to feed hungry people. Also, at the same time, I DO NOT encourage giving money to beggars. Its an absolute NO-NO. Instead, lets pass on the money to charitable organizations who use the money in an organized way to feed hungry people and may be to send poor children to school.
I've started sponsoring a child in India and so can you. Ashanet.org is a non-profit organization where you can sponsor a child in india. They will be given food, shelter and also sent to school. I would encourage all of you to have a look at the link below and forward it to as many people as you can. You can sponsor a child at $20 to $30 a month. You can also google for "sponsor a child ashanet", in case the link below does not work.
https://www.ashanet.org/siliconvalley/sac//index.html
Lets start working towards feeding the hungry people. You can do your part by feeding one person from your earnings and if every one of us do that, the world will be HUNGER-FREE, a dream come true!!
Let me reiterate the saying, "Service to human is Service to God".
-- Vish.
Coming to Spiritualism vs Hunger. I've always wanted to write about it and finally the time has come. I believe in spiritualism and it is definitely needed for humans. I do respect people who believe in spiritualism. It gives you peace of mind and strength to face difficulties.
Coming to the point, I've seen people donating money to temples and people in India do that a lot. I don't agree with them at all. There are people in India who are in need of food. In my view hunger comes first and then spiritualism, luxury or whatever it is. HUNGER is the first thing that has to be taken care of. Feed the people first and then the temples. There's a saying in telugu (my mother tongue), "MANAVASEVE MADAVASEVA", which I believe in. It means "Service to human is Service to God", which is absolutely true.
And why do we donate money to the temples? I don't think a lot of people understand or even try to understand the reason behind it. I was doing it because my parents taught me to do so and I believe that so is the reason with so many other people. It is just that people believe in God and they believe that donating money to God is a good thing. And what is the money used for? To promote religion and to build temples? Forget about building temples when there are people around dying with hunger. Lets feed the hungry people first. I'm not saying that you leave your worldly luxuries and then spare the money to feed these people. All I'm saying is that lets spare the money that we donate to the temples and use that money to feed these hungry people.
I don't think any religion would say NO to feed hungry people. Also, at the same time, I DO NOT encourage giving money to beggars. Its an absolute NO-NO. Instead, lets pass on the money to charitable organizations who use the money in an organized way to feed hungry people and may be to send poor children to school.
I've started sponsoring a child in India and so can you. Ashanet.org is a non-profit organization where you can sponsor a child in india. They will be given food, shelter and also sent to school. I would encourage all of you to have a look at the link below and forward it to as many people as you can. You can sponsor a child at $20 to $30 a month. You can also google for "sponsor a child ashanet", in case the link below does not work.
https://www.ashanet.org/siliconvalley/sac//index.html
Lets start working towards feeding the hungry people. You can do your part by feeding one person from your earnings and if every one of us do that, the world will be HUNGER-FREE, a dream come true!!
Let me reiterate the saying, "Service to human is Service to God".
-- Vish.
Saturday, August 18, 2007
Installed oracle 11g !!
Installed oracle 11g today. Not a big deal. Just the same old stuff as oracle 10g. No issues at all during the installation.
Installed VMWare, Oracle enterprise linux 5, and then oracle 11g.
As always, Tim Hall and Dizwell are out there to help us out with kernel parameters and security limits etc..., saving us from goin' through oracle documentation. Thanks to these guys !!
Tim Hall's oracle 11g installation can be found using the link below.
http://oracle-base.com/articles/11g/OracleDB11gR1InstallationOnEnterpriseLinux4and5.php
Dizwell also has oracle 11g installation on Centos 5, which is released very recently. Did not really go for it since its released recently and not sure how stable it was with kernel parameter setttings and all.
The only difference I have found (installation wise) is that they have introduced a configuration manager into the list of installations you want, which will be associated with your metalink CSI account and collect information required in case you open up a SR with oracle. Not sure how useful it is. I've ignored it anyway. They have renamed HTML DB to Oracle Application Express and looks like they have introducted sql developer into all client installations.
Did not really dig into the new features yet, but very much interested in doin' so.
Next steps :
1. New features in oracle 11g
2. Install oracle 11g RAC.
-- Vish.
Installed VMWare, Oracle enterprise linux 5, and then oracle 11g.
As always, Tim Hall and Dizwell are out there to help us out with kernel parameters and security limits etc..., saving us from goin' through oracle documentation. Thanks to these guys !!
Tim Hall's oracle 11g installation can be found using the link below.
http://oracle-base.com/articles/11g/OracleDB11gR1InstallationOnEnterpriseLinux4and5.php
Dizwell also has oracle 11g installation on Centos 5, which is released very recently. Did not really go for it since its released recently and not sure how stable it was with kernel parameter setttings and all.
The only difference I have found (installation wise) is that they have introduced a configuration manager into the list of installations you want, which will be associated with your metalink CSI account and collect information required in case you open up a SR with oracle. Not sure how useful it is. I've ignored it anyway. They have renamed HTML DB to Oracle Application Express and looks like they have introducted sql developer into all client installations.
Did not really dig into the new features yet, but very much interested in doin' so.
Next steps :
1. New features in oracle 11g
2. Install oracle 11g RAC.
-- Vish.
Friday, July 13, 2007
RMAN backup and restore without a catalog
Andy C has written a good article to backup and restore a db without a catalog.
http://oracleandy.wordpress.com/rman-refresh-without-a-catalog/
http://oracleandy.wordpress.com/rman-refresh-without-a-catalog/
Wednesday, January 31, 2007
Installed 2 node, 2 instance RAC on my laptop
Finally installed 2 node, 2 instance RAC on my laptop after quite a number of trials. Followed Tim Hall's document to configure it.
http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnCentos4UsingVMware.php
I was having problems when I was installing the clusterware. At the end of installation, the clvfy utility checks for cluster integrity and it fails every time. Tried a couple of things to repair it, but it never worked. Finally I tried to sychronize the time of each virutal machine with the host OS and it worked. It always throws up an error like below , if the nodes are not synchronized.
http://www.oracle.com/technology/pub/articles/chan-ubl-vmware.html
Tired it out and it worked.
Now i've a single node-2 instance and 2 node-2 instance RAC installed on my laptop.
Trying to explore more of it now. (Understanding cache fusion, Transparent Application Failover (TAF) etc...
-- Vish.
http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnCentos4UsingVMware.php
I was having problems when I was installing the clusterware. At the end of installation, the clvfy utility checks for cluster integrity and it fails every time. Tried a couple of things to repair it, but it never worked. Finally I tried to sychronize the time of each virutal machine with the host OS and it worked. It always throws up an error like below , if the nodes are not synchronized.
"/bin/tar: ./inventory/Components21/oracle.ordim.server/10.2.0.1.0: timeThe article below explains how to synhronize the guest OS time with host OS.
stamp 2006-11-04 06:24:04 is 25 s in the future"
http://www.oracle.com/technology/pub/articles/chan-ubl-vmware.html
Tired it out and it worked.
Now i've a single node-2 instance and 2 node-2 instance RAC installed on my laptop.
Trying to explore more of it now. (Understanding cache fusion, Transparent Application Failover (TAF) etc...
-- Vish.
Sunday, January 07, 2007
Installed RAC on my new Laptop
Recently bought a 2GB RAM/160GB Hard Disk laptop ('cos i failed miserably trying to install RAC on my 512MB RAM laptop) and tried to install RAC on my new machine.
I've followed dizwell's document and installed RAC on my laptop. It was pretty smooth. No issues at all. Here's the link to follow.
http://www.dizwell.com/prod/node/26?page=0%2C3
what a pretty good documentation. Amazing. Since RAC is new (may be not)... i mean fairly new, and it needs a lot of linux, network settings that are quite complicated. Dizwell has done a very good job in explaining them in a step by step procedure. kudos to dizwell. This is a single node two instance RAC.
Tim Hall is also gave another excellent documentation. He wrote an article to create a two node RAC as opposed to single node RAC documented by dizwell.
Here's the link to follow.
http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnCentos4UsingVMware.php
I've tried that too, but as of now i'm stuck with the ASM instance not mounting on the second node. It gives me an error saying "network or resource busy" for the ASM mounted file system when it tries to mount on the second node. Need to figure it out. Will post here once i figure it out.
Regards,
vish.
I've followed dizwell's document and installed RAC on my laptop. It was pretty smooth. No issues at all. Here's the link to follow.
http://www.dizwell.com/prod/node/26?page=0%2C3
what a pretty good documentation. Amazing. Since RAC is new (may be not)... i mean fairly new, and it needs a lot of linux, network settings that are quite complicated. Dizwell has done a very good job in explaining them in a step by step procedure. kudos to dizwell. This is a single node two instance RAC.
Tim Hall is also gave another excellent documentation. He wrote an article to create a two node RAC as opposed to single node RAC documented by dizwell.
Here's the link to follow.
http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnCentos4UsingVMware.php
I've tried that too, but as of now i'm stuck with the ASM instance not mounting on the second node. It gives me an error saying "network or resource busy" for the ASM mounted file system when it tries to mount on the second node. Need to figure it out. Will post here once i figure it out.
Regards,
vish.
Sunday, October 29, 2006
connect to a database without tnsnames.ora entry
sqlplus username/pwd@//hostname:1521/sidname
Just another new feature in 10g
Thursday, October 05, 2006
Tuesday, August 22, 2006
Friday, July 07, 2006
Traditional HOT backups !!
Ok..you traditional HOT backup (not RMAN backup) failed or you have killed the HOT backup. Then you have check if any of the files are still active in V$backup and then do a "alter tablespace end backup" for each and every tablespace. In 10g you don't have to do that. You can do a "alter database begin backup" and "alter database end backup". These commands will either put/take the tablespaces in/out of HOT backup mode. Not sure if this is goin' to help a lot of people, 'cos now most of the people use RMAN. In case you are using traditional HOT backups ( I prefer using RMAN backups), then this might be of help to you.
Just another new feature in 10g.
Hope this helps.
-- Vish.
Monday, June 19, 2006
Reverse engineer the DDL
Here is how to reverse engineer the DDL commands. I've found this pretty useful sometimes. For example, in case you want to recreate the db and you want all the tablespace creation scrips. Instead of doin' it from scratch you can use the package, GET_METADA.GET_DDL to get the generate the script for you and then you can use these scripts to create the tablespaces once the db is recreated.
For example, to reverse engineer the tablespace creation script,
sql > set long 900000
sql > select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;
For example, if i want to get the tablespace creation script for the tablespace users, i use
sql > select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;
and i set long as 900000 before doin' this, otherwise my output is truncated and i can't see the whole script.
To reverse engineer the table creation script,
select DBMS_METADATA.GET_DDL('TABLE','table_name') from dual;
You can do this to get the DDL of all the objects in the database.
Hope this helps.
-- Vish.
ALL ABOUT TABLESPACES
ALL ABOUT TABLESPACES
Here is all that you might need to know about when working with tablespaces. I've just given the syntax taking only some requirements into account. Change them according to your requirement.
DISCLAIMER: All the views experessed here are my own. I'm not responsible for any problem that you might get into by following the below syntax. Use them at your own risk. But, this is pretty much i use and they worked fine for me until now.
NOTE:
1. Never, Ever, Ever delete a datafile. You can only resize the datafile to five oracle blocks. You can drop the whole tablespace though !! If you delete the datafile, then you’ve to recover it. The control file remembers that the datafile is still there eventhough you drop it at the database level. Also, remember that there is no RECYCLE BIN in unix.
2. You can’t drop the default temp and undo tablespaces. You need to create a new temp/undotbs, make it default temp/undotbs and then drop the old temp/undo tbs.
3. Make sure you fill up the temp datafile once you add them to the temp tablespaces, ‘cos it does not really occupy the whole OS space until it is used completely. If you are on solaris, use mkfile command to do that.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
1. To get the script of an already existing tablespace, use the following. (Reverse engineering the tablespace creation script)
select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;
For Example, to get the script of the SYSTEM tablespace,
select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;
2. To create a tablespace: (with extent management local, uniform size 128K extent sizes)
CREATE TABLESPACE "tablespace_name" DATAFILE
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M,
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
3. To alter/add a datafile :
alter database datafile '/db/xxxx/xxx/datafilename.dbf' resize 2048M;
To add a datafile to an existing tablespace:
alter tablespace tablespace_name add datafile ‘datafile_name’ size xxxxM;
For example, to add a datafile of size 512M to the SYSTEM tablespace,
Alter tablespace system add datafile ‘/db/dbxxx/oracle/xxxxxxx/datafile_name.dbf’ size 512M;
4. To drop a tablespace:
drop tablespace tablespace_name including contents and datafiles;
5. To get the tablespace info of a database:
Note that the values are rounded to the nearest integer.
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;
6. To check the default temporary tablespace of the database:
select property_name, property_value from database_properties;
select property_name, property_value from database_properties where property_name like ‘%TEMP%’;
7. To change the default temporary tablespace of the whole database:
alter database default temporary tablespace temp_tablespace_name;
8. To change the default temporary tablespace of a particular user :
alter user username default temporary tablespace temp_tablespace_name;
9. To create a temp tablespace:
On solaris:
host /usr/sbin/mkfile 1024m /db/db004/oracle/TOR_M704/dsmtemp.df01.dbf
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' reuse
extent management local uniform size 128K;
If the OS is not solaris, then
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' size 1024M
extent management local uniform size 128K;
NOTE : When you create the tempfile without using mkfile, the file is created but the entire space is not occupied at the OS level. Lets say that there is 1GB in that mount point where you have created the datafile of size 512M. It still shows that there is 1GB of space in that mountpoint eventhough this tempfile is created. This space is occupied only when that space is used. You can use the following script to recursively sort until the space is filled.
Select * from dba_source a, dba_source b, dba_source c order by 1;
This script basically keeps on sorting and is never ending. So, it fills up the temp space continiously until the whole tempfile is full. Once the whole tempfile is full, you can see that the space in that mountpoint (where there was 1GB of space ) is now only 512MB, ‘cos 512MB is used for this tempfile.
10. To add a tempfile to the existing temp tablespace:
alter tablespace temp add tempfile ‘/db/dbxxx/xxx/temp02.dbf’ size 1024M;
11. To resize the tempfile
alter database tempfile ‘/db/dbxxx/xxx/temp02.dbf’ resize 512M;
12. To drop and recreate a temp tablespace (lets say temp1):
a) first create another temp tablespace ..lets say temp2
b) then make this the default tablespace for the database, also for the users who had default temp tablespace as temp1
c) then drop the tablespace temp1
Note: The database won’t let you drop the default temporary tablespace of the database anyway.
13. To drop and recreate the undotablespace:
Follow the same steps as in step 12.
14. To drop a tempfile:
In 10g:
alter database tempfile '/db/dbxxx/xxxx/xxxx/temp01.dbf' drop including datafiles;
In 9i:
Alter database tempfile ‘/db/dbxxx/xxx/xxxx/temp01.dbf’ offline drop;
NOTE : Be careful in deleting the temp files. Refer to the documentation before doin’ that and remember that there is no RECYCLE BIN for unix. So, be extra careful when you delete something in unix.
15. To get the space usage of the datafiles:
COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Author : Kam Muhamad Sirajdin (Acknowledgement)
-- Will keep on adding stuff as i remember.....so keep checking.....
Here is all that you might need to know about when working with tablespaces. I've just given the syntax taking only some requirements into account. Change them according to your requirement.
DISCLAIMER: All the views experessed here are my own. I'm not responsible for any problem that you might get into by following the below syntax. Use them at your own risk. But, this is pretty much i use and they worked fine for me until now.
NOTE:
1. Never, Ever, Ever delete a datafile. You can only resize the datafile to five oracle blocks. You can drop the whole tablespace though !! If you delete the datafile, then you’ve to recover it. The control file remembers that the datafile is still there eventhough you drop it at the database level. Also, remember that there is no RECYCLE BIN in unix.
2. You can’t drop the default temp and undo tablespaces. You need to create a new temp/undotbs, make it default temp/undotbs and then drop the old temp/undo tbs.
3. Make sure you fill up the temp datafile once you add them to the temp tablespaces, ‘cos it does not really occupy the whole OS space until it is used completely. If you are on solaris, use mkfile command to do that.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
1. To get the script of an already existing tablespace, use the following. (Reverse engineering the tablespace creation script)
select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;
For Example, to get the script of the SYSTEM tablespace,
select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;
2. To create a tablespace: (with extent management local, uniform size 128K extent sizes)
CREATE TABLESPACE "tablespace_name" DATAFILE
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M,
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
3. To alter/add a datafile :
alter database datafile '/db/xxxx/xxx/datafilename.dbf' resize 2048M;
To add a datafile to an existing tablespace:
alter tablespace tablespace_name add datafile ‘datafile_name’ size xxxxM;
For example, to add a datafile of size 512M to the SYSTEM tablespace,
Alter tablespace system add datafile ‘/db/dbxxx/oracle/xxxxxxx/datafile_name.dbf’ size 512M;
4. To drop a tablespace:
drop tablespace tablespace_name including contents and datafiles;
5. To get the tablespace info of a database:
Note that the values are rounded to the nearest integer.
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;
6. To check the default temporary tablespace of the database:
select property_name, property_value from database_properties;
select property_name, property_value from database_properties where property_name like ‘%TEMP%’;
7. To change the default temporary tablespace of the whole database:
alter database default temporary tablespace temp_tablespace_name;
8. To change the default temporary tablespace of a particular user :
alter user username default temporary tablespace temp_tablespace_name;
9. To create a temp tablespace:
On solaris:
host /usr/sbin/mkfile 1024m /db/db004/oracle/TOR_M704/dsmtemp.df01.dbf
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' reuse
extent management local uniform size 128K;
If the OS is not solaris, then
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' size 1024M
extent management local uniform size 128K;
NOTE : When you create the tempfile without using mkfile, the file is created but the entire space is not occupied at the OS level. Lets say that there is 1GB in that mount point where you have created the datafile of size 512M. It still shows that there is 1GB of space in that mountpoint eventhough this tempfile is created. This space is occupied only when that space is used. You can use the following script to recursively sort until the space is filled.
Select * from dba_source a, dba_source b, dba_source c order by 1;
This script basically keeps on sorting and is never ending. So, it fills up the temp space continiously until the whole tempfile is full. Once the whole tempfile is full, you can see that the space in that mountpoint (where there was 1GB of space ) is now only 512MB, ‘cos 512MB is used for this tempfile.
10. To add a tempfile to the existing temp tablespace:
alter tablespace temp add tempfile ‘/db/dbxxx/xxx/temp02.dbf’ size 1024M;
11. To resize the tempfile
alter database tempfile ‘/db/dbxxx/xxx/temp02.dbf’ resize 512M;
12. To drop and recreate a temp tablespace (lets say temp1):
a) first create another temp tablespace ..lets say temp2
b) then make this the default tablespace for the database, also for the users who had default temp tablespace as temp1
c) then drop the tablespace temp1
Note: The database won’t let you drop the default temporary tablespace of the database anyway.
13. To drop and recreate the undotablespace:
Follow the same steps as in step 12.
14. To drop a tempfile:
In 10g:
alter database tempfile '/db/dbxxx/xxxx/xxxx/temp01.dbf' drop including datafiles;
In 9i:
Alter database tempfile ‘/db/dbxxx/xxx/xxxx/temp01.dbf’ offline drop;
NOTE : Be careful in deleting the temp files. Refer to the documentation before doin’ that and remember that there is no RECYCLE BIN for unix. So, be extra careful when you delete something in unix.
15. To get the space usage of the datafiles:
COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Author : Kam Muhamad Sirajdin (Acknowledgement)
-- Will keep on adding stuff as i remember.....so keep checking.....
Monday, May 29, 2006
Installed linux successfully !!
Finally, i was able to install the ubuntu version of linux on my laptop successfully. Now, i've a dual boot system (linux and windows).
Below are the steps i've followed for creating this dual boot system.
1. Installed partition magic
2. Resized my 60GB NTFS hard disk to 39GB, created a 16GB ext3 format partition, a 3GB FAT32 format partition, and a 1GB linux swap partition. I've created the FAT32 partition 'cos this format allows both linux and windows to read/write onto this file system and files on this mount point can be accessed in both OS.
3. Then i've downloaded ubuntu 5.10 (first the live cd and then the direct install cd). You can download it from here.
http://www.ubuntu.com/download/
4. You can try out the live cd to see how it looks (it doesn't do anything except that it lets you to try it out)
5. Once i've downloaded the direct install cd, i've booted my laptop from that cd and it was a pretty straight forward install from there.
6. Just be careful when creating the mountpoints. I've assinged the 16GB ext3 format for my root (/) partition and named 3GB partition as /windows, 1GB linux swap partion for swap. The remaining mount points are left as it is (the default file system given by the partitioner. It shows the 39GB windows ntfs and the other ones. Just leave them as it is).
7. Now, when i asked it to install them i've asked the partitioner to format those /root and /windows partitions and clicked Install.
8. After sometime, BOOM !! got the message "Linux installed successfully".
9. During the install, it was configuring my DHCP client and it asked me to choose my internal network card or the internal wireless card, i chose internal network card and then once it is installed i've configured my wireless card.
Thats it. Now i've a dual boot system, everything working.
PS : I've tried the ubuntu draper drake 6.06, which is still in the beta version and it said "Installer crashed" a couple of times. I've stopped trying it out 'cos i did not want to screw up existing windows OS. Lets try and install it once the final version is released.
Lets install oracle on linux next week.
Now i wonder, why do i want to pay a 100 bucks for windows professional when i'm getting ev. thing here through linux for FREE ?? Lets get used to LINUX !!
-- Vish.
Below are the steps i've followed for creating this dual boot system.
1. Installed partition magic
2. Resized my 60GB NTFS hard disk to 39GB, created a 16GB ext3 format partition, a 3GB FAT32 format partition, and a 1GB linux swap partition. I've created the FAT32 partition 'cos this format allows both linux and windows to read/write onto this file system and files on this mount point can be accessed in both OS.
3. Then i've downloaded ubuntu 5.10 (first the live cd and then the direct install cd). You can download it from here.
http://www.ubuntu.com/download/
4. You can try out the live cd to see how it looks (it doesn't do anything except that it lets you to try it out)
5. Once i've downloaded the direct install cd, i've booted my laptop from that cd and it was a pretty straight forward install from there.
6. Just be careful when creating the mountpoints. I've assinged the 16GB ext3 format for my root (/) partition and named 3GB partition as /windows, 1GB linux swap partion for swap. The remaining mount points are left as it is (the default file system given by the partitioner. It shows the 39GB windows ntfs and the other ones. Just leave them as it is).
7. Now, when i asked it to install them i've asked the partitioner to format those /root and /windows partitions and clicked Install.
8. After sometime, BOOM !! got the message "Linux installed successfully".
9. During the install, it was configuring my DHCP client and it asked me to choose my internal network card or the internal wireless card, i chose internal network card and then once it is installed i've configured my wireless card.
Thats it. Now i've a dual boot system, everything working.
PS : I've tried the ubuntu draper drake 6.06, which is still in the beta version and it said "Installer crashed" a couple of times. I've stopped trying it out 'cos i did not want to screw up existing windows OS. Lets try and install it once the final version is released.
Lets install oracle on linux next week.
Now i wonder, why do i want to pay a 100 bucks for windows professional when i'm getting ev. thing here through linux for FREE ?? Lets get used to LINUX !!
-- Vish.
Monday, May 08, 2006
virtual indexes
I was goin' through one of the blogs (forgot which one it was), but it mentioned that we can create virtual indexes. Basically, what it does is that -- it doesn't create the index physically, but acts as a index virtually.
Sometimes you might be wondering wether creating index on a certain column is goin' to decrease the cost of the execution plan. Then this might be of great help.
Will post the exact syntax next time. Just got reminded of it and wanted to mention it here.
-- Vish.
Sometimes you might be wondering wether creating index on a certain column is goin' to decrease the cost of the execution plan. Then this might be of great help.
Will post the exact syntax next time. Just got reminded of it and wanted to mention it here.
-- Vish.
Installing unix...not quite successful !
Its been quite sometime i'm here. Well, moved on to a new job -- Fannie Mae, VA as a production suport DBA. But, here we go.
I was trying to install solaris on my laptop to make it a dual boot system (windows and solaris), but i was not quite successful.
After 15 hrs of work on it, i could install it, but still can't connected to the internet. Not sure if i don't have the drivers for my network card or something like that.(I have a new dell xps M140 laotop). Went through a lot of threads but could not really get the solution. It really sucked !!
Now, planning to move to ubuntu flavor of unix. Just went through some blogs and it has some excellent reviews. The new version, drapper drape is still in the beta stage, but the best thing with this is that you can have a "LIVE CD", which means that you can just try out your install on your windows machine without really installing it. May be -- u can call it a virtual install. You can even connect to internet through this live cd. If it goes fine and if you like it, then you can go ahead with the acutual install. What else do you need ? You can check out an OS even without installing it ? Amazing !!
You don't even need partition magic to format your drivers. ubuntu does that. Also, i've read somewhere that it even recognises your USB flash drivers ( I think its an NTFS format). Amazing once again (if thats true .. :) !!
I haven't tried it out yet, but i'm goin' to. By this weekend, i should have it ready (hopefully...ev. thing working).
Lets find out how amazing it is by the end of the week.
I was trying to install solaris on my laptop to make it a dual boot system (windows and solaris), but i was not quite successful.
After 15 hrs of work on it, i could install it, but still can't connected to the internet. Not sure if i don't have the drivers for my network card or something like that.(I have a new dell xps M140 laotop). Went through a lot of threads but could not really get the solution. It really sucked !!
Now, planning to move to ubuntu flavor of unix. Just went through some blogs and it has some excellent reviews. The new version, drapper drape is still in the beta stage, but the best thing with this is that you can have a "LIVE CD", which means that you can just try out your install on your windows machine without really installing it. May be -- u can call it a virtual install. You can even connect to internet through this live cd. If it goes fine and if you like it, then you can go ahead with the acutual install. What else do you need ? You can check out an OS even without installing it ? Amazing !!
You don't even need partition magic to format your drivers. ubuntu does that. Also, i've read somewhere that it even recognises your USB flash drivers ( I think its an NTFS format). Amazing once again (if thats true .. :) !!
I haven't tried it out yet, but i'm goin' to. By this weekend, i should have it ready (hopefully...ev. thing working).
Lets find out how amazing it is by the end of the week.
Monday, February 20, 2006
killing a session immediately
To kill a session in oracle, i generally use,
alter system kill session 'sid,serial#';
(where i get sid, serial# by querying v$session. I also get the unix process id from here).
But this many not kill the processes at the unix level. So, i generally kill the process at the unix level too.
I've found on one of the blogs that you can do this at sql itself (without kill -9 ing) by using
alter system disconnect session 'sid,serial#' immediate;
alter system kill session 'sid,serial#';
(where i get sid, serial# by querying v$session. I also get the unix process id from here).
But this many not kill the processes at the unix level. So, i generally kill the process at the unix level too.
I've found on one of the blogs that you can do this at sql itself (without kill -9 ing) by using
alter system disconnect session 'sid,serial#' immediate;
Thursday, February 09, 2006
Size of the table in bytes
To find the size of bytes allocated to a table:
sql > analyze table emp compute statistics;
sql > select num_rows * avg_row_len "Bytes Used" from dba_tables where table_name = 'TEST';
Bytes Used
-----------
560
sql > select bytes "Bytes Allocated" from dba_segments where segment_name = 'TEST';
Bytes Allocated
----------
524288
The result of the query shows that the emp table is using 560 bytes of the 524,288 bytes allocated to it.
**** you can use user_segments too...if u don't have the dba privileges ******
sql > analyze table emp compute statistics;
sql > select num_rows * avg_row_len "Bytes Used" from dba_tables where table_name = 'TEST';
Bytes Used
-----------
560
sql > select bytes "Bytes Allocated" from dba_segments where segment_name = 'TEST';
Bytes Allocated
----------
524288
The result of the query shows that the emp table is using 560 bytes of the 524,288 bytes allocated to it.
**** you can use user_segments too...if u don't have the dba privileges ******
10g installation on windows
I was trying to install oracle 10g on my personal laptop. Tried a couple of times without goin' through much of a documentation and failed. The network configuration assistant fails each time. Its a pretty straight forward installation except that it needs a fixed IP address (you can't have one in a DHCP enabled environment). But you can fake it using the Microsoft loopback adapter. You can see the following link to see how u can install microsoft loopback adapter.
http://support.microsoft.com/default.aspx?scid=kb;en-us;839013
Once i've installed this, i've included the following line in my hosts file (which is located in C:\WINDOWS\system32\drivers\etc). Now my hosts file looks like this (where vish is my computer name, 10.101.10.10 is the fixed IP address i've mentioned in the microsoft loopback adapter).
-----------------------------
10.10.10.10 vish (this is the new line u need to include)
127.0.0.1 localhost (this line is already present..just leave it there)
-----------------------------
Once i've done this, my network configuration assistant was successful.
Here is the link which shows a pretty staright forward way of installing oracle. Similar to 9i.
http://www.compiere.org/support/install/installOracleSteps.html
Now that i've installed 10g successuflly, i need to start learning the new features of oracle. In my next post i'll post the installation of oracle internet directory whcih i've implemented for my home network, so that we can connect to a centralized db instead of having the oracle server on each and ev. laptop.
http://support.microsoft.com/default.aspx?scid=kb;en-us;839013
Once i've installed this, i've included the following line in my hosts file (which is located in C:\WINDOWS\system32\drivers\etc). Now my hosts file looks like this (where vish is my computer name, 10.101.10.10 is the fixed IP address i've mentioned in the microsoft loopback adapter).
-----------------------------
10.10.10.10 vish (this is the new line u need to include)
127.0.0.1 localhost (this line is already present..just leave it there)
-----------------------------
Once i've done this, my network configuration assistant was successful.
Here is the link which shows a pretty staright forward way of installing oracle. Similar to 9i.
http://www.compiere.org/support/install/installOracleSteps.html
Now that i've installed 10g successuflly, i need to start learning the new features of oracle. In my next post i'll post the installation of oracle internet directory whcih i've implemented for my home network, so that we can connect to a centralized db instead of having the oracle server on each and ev. laptop.
Oracle Project Raptor
Finally, the much awaited TOAD like software is released by oracle. Thanks to oracle for recognizing that they need to have a software like TOAD delivered free of cost.
They have not yet implemented all the functionalities of TOAD, but its pretty OK. (Its free ofcourse). A delight for oracle pl/sql developers who don't have access to TOAD.
Its more intended towards developers than DBA's.
You can download Oracle project raptor here.
http://www.oracle.com/technology/software/products/sql/index.html
I've started to use it and almost got familiar with it.
They have not yet implemented all the functionalities of TOAD, but its pretty OK. (Its free ofcourse). A delight for oracle pl/sql developers who don't have access to TOAD.
Its more intended towards developers than DBA's.
You can download Oracle project raptor here.
http://www.oracle.com/technology/software/products/sql/index.html
I've started to use it and almost got familiar with it.
Wednesday, January 18, 2006
ORA-01114: IO error writing block to file name block %s
Problem Description: got this error when trying to read from a table/view: ORA-01114: IO error writing block to file name block %s
******From metalink: ORA-01114: IO error writing block to file string (block # string)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space. *****
In my case, this has happened b'cos we have created a temporary datafile in a mountpoint and did not extend it completely and now when it is trying to extend it, it can't ... ...this might be the cause of the error. As you know, when you create a tempfile, it doesn't immediately occupy the OS space unless, otherwise all the segments are extended. It just says database altered even though there is no space in that mountpoint.
We can see the the usage in enterprise manager. I've seen that and i've observed that i've allocated about 1GB to that tempfile..but there was only 0.9GB left....and it was trying to extend beyond that size. so, it was giving that error.
SOlUTION:
drop and recreate your temp tablespace.
procedure: create a new temp tablespace, make this as the default temp tablespace and drop the old one (in 9i , drop tablespace temp including contents and datafiles).
Any comments ??
******From metalink: ORA-01114: IO error writing block to file string (block # string)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space. *****
In my case, this has happened b'cos we have created a temporary datafile in a mountpoint and did not extend it completely and now when it is trying to extend it, it can't ... ...this might be the cause of the error. As you know, when you create a tempfile, it doesn't immediately occupy the OS space unless, otherwise all the segments are extended. It just says database altered even though there is no space in that mountpoint.
We can see the the usage in enterprise manager. I've seen that and i've observed that i've allocated about 1GB to that tempfile..but there was only 0.9GB left....and it was trying to extend beyond that size. so, it was giving that error.
SOlUTION:
drop and recreate your temp tablespace.
procedure: create a new temp tablespace, make this as the default temp tablespace and drop the old one (in 9i , drop tablespace temp including contents and datafiles).
Any comments ??
Tuesday, January 17, 2006
Tablespaces
Here are some scripts, tips regarding the tablespaces. This script basically gives you all the tablespace info in a database.
****************** BEGIN -- Tablespace info script ******************
set linesize 150;
select ddf.TABLESPACE_NAME Tablespace,
ddf.BYTES/1024/1024 MBytes_allocated,
(ddf.BYTES-DFS.BYTES)/1024/1024 MBytes_used,
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) Percent_used,
dfs.BYTES/1024/1024 MBytes_free,
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) Percent_free
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc;
****************** END-- Tablespace info script *****************
****************** BEGIN -- Tablespace info script ******************
set linesize 150;
select ddf.TABLESPACE_NAME Tablespace,
ddf.BYTES/1024/1024 MBytes_allocated,
(ddf.BYTES-DFS.BYTES)/1024/1024 MBytes_used,
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) Percent_used,
dfs.BYTES/1024/1024 MBytes_free,
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) Percent_free
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc;
****************** END-- Tablespace info script *****************
Monday, January 16, 2006
Oracle posts
This blog is to demonstrate my experiences with oracle. planning to post the new things i learn.
Subscribe to:
Posts (Atom)