Subscribe: Amardeep Sidhu's Blog
Added By: Feedage Forager Feedage Grade B rated
Language: English
create  database  file  files  import  new  oracle  redo  sidhu  system  tablespace  transactions  undo tablespace  undo  user  users 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Amardeep Sidhu's Blog

Amardeep Sidhu's Blog

I have moved to

Updated: 2015-09-16T22:26:42.088+05:30


I have moved...


Well, I have registered by own domain name and moved all the posts there. Please point your bookmarks & feeds to

Thank You !!!

Oracle from Dizwell...


Howard has posted a pdf on Oracle Administration on his new website. Do check out. Its awesome.


ORA-03113 Refresh of a mview in Oracle 10g


At my workplace we were facing a problem with refresh of a mview. Say it was created in schema of user1 but when I tried to refresh it from user2 it would give ORA-03113: end-of-file on communication channel. Then we raised a SR and have been following up with Oracle support for long but it was not getting anywhere. Yesterday that guy seemed to have reached some point. The mviews that we have created and are having problem with refresh are created on top of both local & remote objects and he said that up to 11gr2 there is no possibility of creating mviews on both local and remote objects. I did validate this thing. All the mviews failing to refresh are created on top of both local & remote objects. But again from the owner the refresh is fine but from another user it gives problem. By the way that guy hinted at bug 4084125 and also suggested a work around. I haven't tried that yet. Will try and update about the results.


Trained in AIX...


Last week I attended a training on AIX system administration from IBM (organized by company, obviously ;) It was a 7 days course covering all of the system administration stuff. There was a lot of new stuff to learn, LVM being the most number of times uttered word, once we did the chapter on LVM. It was a nice experience as a whole as for the first time I attended any training on Unix.

The sessions (specially after lunch) were sleepy also. I find this ppt method of training pretty boring. The trainer (most of) strictly, stupidly follows the slides and slide, I feel is a dumb sort of thing, makes you feel sleepy except at the moments when there are few eye opening bullet points.

There should be bare minimum number of slides and for rest of the things, trainer should use white board so that everybody follows that and doesn't sleep :)

Anyways it was really enjoying to be familiar with so many things in Unix.


Oracle from Eddie Awad...


Eddie Awad started a new series Oracle in 3 minutes on his blog. In the first post he has discussed about multi-versioning. Its a must watch for everyone who is working on Oracle. Hoping to get more of such stuff from Eddie.


Howard's decision...


Howard Rogers has done a post on his blog that is the site Dizwell worth it ? Do post your opinion. If he plans to close it down, we will miss a great resource for Oracle.


Asking Tom ? Read this !


Tom Kyte did a post on his blog about posting of reviews of questions on Asktom. In a nutshell the reviews not related to the original question will be ignored/deleted (not decided yet, as Tom said).

As other people said in the comments, personally I too like this idea very much. Earlier, many times, there were questions which started with someone asking about appropriate SGA size, then there were some other twists and discussions and then the thread ended in discussion about good or bad authors or something similar light years away from the original topic.

Now this action will make the discussions flow in a very controlled and neat & clean manner, all about the original topic. Hoping to see all the "great content" in a very orderly manner :)


Official Oracle wiki


Today I was checking Eddie Awad's blog. From there I came to know that Oracle has started Official Oracle Wiki hosted by Wetpaint. Check out. I just made my login. Hoping to contribute whatever little I can


UNDO tablespace in Oracle...


Today, I was following a thread on Oracle Forums. Someone asked a question about UNDO tablespace wrt to a scenario. The question was:There is a database and its hot backup is taken on Friday. Now for Saturday, Sunday and Monday there are archive logs but no backups. Suppose the machine crashes on Monday. After we restore the database to Friday (from backup), recovery will happen. As UNDO tablespace is of Friday so it has no information related to transactions that happened on Saturday, Sunday and Monday. So in the end of recovery process when we need to rollback some transactions from where that required information will come ?Howard did 3 beautiful follow ups of this post, explaining how UNDO works. Just saving it here for quick reference. Hope its no copyright mess :)Follow up 1: Yes, it can certainly be confusing, especially when you get told completely incorrect information! As has already (and thankfully!) been pointed out, redo logs contain redo change records from both committed and uncommitted transactions. The answer to your question is that as we re-perform transactions by applying redo in a recovery session, we redo exactly what would have been done when the transactions were first performed. That is, we'd see a redo change record that says (in effect) update EMP set sal=900 where ename='Bob', so we'd find the Bob record in the restored Friday copy of the data file, and we'd lock that record. Then we'd store details of Bob's existing salary in an undo block. Then we'd store the new and old salaries in redo (yup, recovery generates redo!). Then we'd change the Bob record itself. If that's all that's in the archives and online redo logs, that's all that happens: Bob's record is left locked and changed. At the end of the recovery process, we realise that a lot of re-performed transactions need rolling back, so SMON does that.... and it knows what to roll the stuff back to because in re-performing the transactions, we generated fresh undo.So your question says, "how does the uncommited transactions rolled back with the Friday undo tablespace since they do [not] have latest uncommited trnsactions", but that's not right. The undo tablespace certainly STARTS at the state it was in on Friday. But as recovery proceeds, it gets 'freshened up', because the new transactions generate fresh undo.(A slightly more accurate description would be to point out that when you generated undo on Saturday and Sunday, those changes to the undo blocks would themselves have generated redo. Therefore, your redo stream, archives and online alike, have the necessary information to recover the undo tablespace. Personally, I don't find that any more informative than thinking that applying redo generates fresh undo, but it's up to you which mental model you prefer to work with).Follow up 2:>does "recovery generates redo" mean that during recovery we regenerate the same amount >of redo that was generated since the last backupNo, it's not the same. If you do a simple test, you'll see that. Update EMP, commit, check with Log Miner that the redo is in the logs in analyzable form. The blow up your database, restore it and recover it. Your log sequence number will have moved on, redo will have been generated... but you can mine the logs till Christmas and you won't find a second 'update EMP' set of redo records.That's why I mentioned the 'more accurately' bit further on in my original reply. Recovery is a bit more subtle than just sitting there issuing a lot of insert/update/delete statements as if from the keyboard of an incredibly fast typist! Metaphorically you can say, "We repeat transactions during recovery". But actually, it's "we apply redo change vectors"... and that doesn't generate a one-for-one amount of redo as the original transactions did. >Will there be duplicate archive logs then?No. Do the Log Mining test to see this [...]

Google friendliness of ...


Being a new kid on the block, I think, my post will not fire any serious and "scary" discussion as it has happened many times in the past. Just writing my experience. Whenever I search something related to Oracle in Google there are few sites that are bound to come up in the very first results ( No surety about the relevance and completeness of the content, though). Today I was searching about scheduling new jobs in oracle and I was, sort of, surprised to see the results [Though, I got from there, what I was looking for & I would like to say Thanks for that]. Many other times also, I have seen these websites pop up like anything.

When I had started my job and after that put the CV on few job sites. Everybody used to say: put more number of keywords in the CV as their search bots select the resumes on the basis of keywords only. Perhaps same thing applies here also. They have included each and every possible keyword in Oracle on their websites [& I think in a better way than it's been done in Oracle documentation ;)]. If its not one of their own websites then some books website (that also their own, obviously) will come up [& the keywords matched here are from table of contents or some portion from some chapter Ctrl+C'ed and Ctrl+V'ed there] with advertisement all around imitating the big bang universe theory. Some special experience, with making websites Google friendly, they have got ;)


Learning AWK...


I was searching for some good tutorials on awk. Found a very nice (brilliant indeed) article on Oracle website by Emmett Dulaney. A very good introduction for beginners. I searched for some other links as well. Have a read:

1. AWK: The Linux Administrators' Wisdom Kit

2. A Guided Tour Of Awk

3. AWK Programming

4. UNIX Utilities - awk

Happy awk'ing :)


Tracing Wikipedia...


Today Stumble gave me a link to an article on a website. That article talks about a guy Virgil Griffith who wrote a piece of software to track IP addresses of the people, editing the Wikipedia content. Some really interesting things came up: Apple attacking Microsoft and then Microsoft taking revenge. Have a look at the full article. It makes an interesting read. Read more about this guy on Wikipedia here.


Being an Oracle trainer...


Few days back, I had to give Oracle DBA training to a group of about 20-25 semi-technical people (Semi-technical, because most of them were not really DBA kinda folks doing all the techie stuff with Oracle, but in-fact having learned some bits & bytes of Oracle sometime back and these days looking into application functionality from technical perspective). I, having just about 10 months of experience with DBA profile, had to cover, everything about Oracle starting from creating database and up to performance tuning :) Its really an interesting job if the audience is good. But not an easy game. You have to know everything and have to be ready to answer people's queries (some stupid & dumb questions also :) I had to cover a total of about 350 slides in a day or less. So at times, really went fast and skipping some of things. It was a nice experience as a whole :)

& in the end when I finished it, was dead tired :(

Hats off to the trainers, who stand for the full day and that too for many days continuously :)


Importing a full database...


Many times, we are required to restore a database from an export dmp file. Its a simple task but sometimes there are some issues left like invalid objects or some objects missing, in the newly created database. Following steps, followed in order can help in creating an error free database:
  1. Create a blank database: The very first step is to create a blank database which is to be used as the target database. That can be done using Database Configuration Assistant. (In last step of the DBCA, change redo log file sizes to 500 MB each (or some appropriate values depdening upon the size of the databaes), as during import, lot of redo will be generated, so large redo size helps in that scenario)

  2. Extract DDLs and create tablespaces: Now run the import with show=Y option and create a log of all DDL statements. The main things to be looked for in the log are DDLs to create tablespaces and DB links. You may need to change the create tablespace statements according to the version of the Oracle you are using. If you have the export taken in an older version, where dictionary tablespaces were being used, you will need to change the statements accordingly, to create locally managed tablespaces.
    (If you have the dmp file in compressed (.Z) format check here, to run the import directly from compressed file)

  3. Adjust the size of SYSTEM, TEMP, USERS and UNDO: As SYSTEM, TEMP, USERS and UNDO tablespaces will get created with the database itself, so you can alter the sizes as per the sizes in the old database.

  4. Edit tnsnames.ora and create dblinks: Now edit tnsnames.ora to include all the databases used in the db links and create db links using the statements from DDL log.

  5. Run the import: Finally, run the import with FULL=Y option and after the import finishes, look for any errors in the log. At last, compile all the invalid objects in the database. (If the import terminates with ORA-01435, then have a look at this post.)
To read about all the options with imp have a look at Original Import & Export Utilities chapter of Oracle Utilities guide.


@ my baby...


A very funny and interesting thing, a Chinese couple wants to name their baby "@".


Oracle 11g...


The day when Oracle 11g was made available for download on OTN, there was sort of, flood of posts in the Oracle blogsphere. Here is a quick recap of few of the posts (Whatsoever I could find through OraNA and my Netvibes)

Eddie Awad about 11g, I think he was the first one to post

Then Doug Burns here

Howard on installing 11g

Another interesting article from Howard

Tim Hall about 11g

Tim Hall on installing 11g

Then Laurent

An article on ADR by Virag Sharma on his blog

Jaffar about Active Standby database

A 11g PL/SQL article on AMIS blog

Well, if you don't want to get into any hassles and just want to download Oracle 11g, you can get it here. (As of now its available for Linux x86 only).


Why Linux cries about "1024 cylinders thing" at the time of installation...


I have been installing Linux for last 6 years and for more than half the number of times, came across a message something like "This partitions is beyond the 1024 cylinder boundary and may not be bootable". But never cared for it much and understood what exactly it meant to say ?

Yesterday I was reading System Admin guide to Linux by Lars Wirzenius (Thanks Howard for the link :) From there I came to know what exactly that message meant. Quoting from the guide itself:

Unfortunately, the BIOS has a design limitation, which makes it impossible to specify a track number that is larger than 1024 in the CMOS RAM, which is too little for a large hard disk. To overcome this, the hard disk controller lies about the geometry, and translates the addresses given by the computer into something that fits reality. For example, a hard disk might have 8 heads, 2048 tracks, and 35 sectors per track. Its controller could lie to the computer and claim that it has 16 heads, 1024 tracks, and 35 sectors per track, thus not exceeding the limit on tracks, and translates the address that the computer gives it by halving the head number, and doubling the track number. The mathematics can be more complicated in reality, because the numbers are not as nice as here (but again, the details are not relevant for understanding the principle). This translation distorts the operating system's view of how the disk is organized, thus making it impractical to use the all-data-on-one-cylinder trick to boost performance.
When using IDE disks, the boot partition (the partition with the bootable kernel image files) must be completely within the first 1024 cylinders. This is because the disk is used via the BIOS during boot (before the system goes into protected mode), and BIOS can't handle more than 1024 cylinders. It is sometimes possible to use a boot partition that is only partly within the first 1024 cylinders. This works as long as all the files that are read with the BIOS are within the first 1024 cylinders. Since this is difficult to arrange, it is a very bad idea to do it; you never know when a kernel update or disk defragmentation will result in an unbootable system. Therefore, make sure your boot partition is completely within the first 1024 cylinders.

Hope it clears the logic why Linux cries about 1024 cylinder issue at the time of installation.

You can read the guide online from the link above and download the pdf here. Its simple and concise and just too good. Small thing covering much :)


Plugged-in back to www...


Last to last week, we shifted to a new house. As there was no internet connection, so we were without any internet access for last 2 weeks. Today we got the new internet connection. Its a DSL one. And the guy who came to do the installation threw a little bit of technical jargon like rebooting the router and so on.

It feels so good to be back in the world of www :)


Moving datafiles,control files and log files-1


Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can't take SYSTEM tablespace offline)
SQL> alter tablespace system offline;
alter tablespace system offline
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
Well lets try moving USERS tablespace.
SQL> select substr(file_name,1,50),tablespace_name from dba_data_files where tablespace_name='USERS';

-------------------------------------------------- ------------------------------

The current location of the datafile is c:\oracle\oradata\orcl1. Suppose I have to move it to c:\oracle\oradata. First take the tablespace offline
SQL> alter tablespace USERS offline;

Tablespace altered.

Now copy the datafile to the new location.
C:\oracle\oradata\orcl1>copy USERS01.DBF c:\oracle\oradata\USERS01.DBF
1 file(s) copied.

Now make database aware of the new location of the datafile using alter database rename file.
SQL> alter database rename file 'C:\ORACLE\ORADATA\ORCL1\USERS01.DBF' to 'C:\ORACLE\ORADATA\USERS01.DBF';

Database altered.

The last thing is the bring back the tablespace online. If everything has gone rightly the message like this will appear and you can view the new location of the datafile.
SQL> alter tablespace users online;

Tablespace altered.

SQL> select substr(file_name,1,50),tablespace_name from dba_data_files where tablespace_name='USERS';

-------------------------------------------------- ------------------------------

In next post we will discuss moving datafiles, controlfiles and logfiles.


Batch file for ftp'ing files...


Today I came across a requirement where users needed to ftp files time and again. So ftp'ing again and again is not a very good option. I wrote a small batch file for the same. Just sharing the same over here. I created a folder ftp in C drive and a file get_file.bat
Contents of get_file.bat are:

set /p file_name=Enter the name of the file you want to ftp:
echo oracle>c:\ftp\param.cfg
echo oracle123>>c:\ftp\param.cfg
echo cd /home/oracle>>c:\ftp\param.cfg
echo lcd c:\ftp>>c:\ftp\param.cfg
get %file_name%
ftp -s:param.cfg

It will create a file param.cfg having all the things like username, password and command to get the file in the same folder (c:\ftp). Then we invoke ftp with -s option with specifying the file param.cfg. It will ask the user to enter the file name and ftp the file from server to c:\ftp


Import ORA-01435: user does not exist...


One may encounter this error while importing from a dmp file from older versions of Oracle. Genereally this error is caused by some statement like alter session set current_schema=scott; And the simple reason is that the user scott doesn't exist. Yesterday I came across this error. And the reason was that user was not created. As in case of import we generally create tablespaces first (by creating the DDL using option show=Y) but creation of users is done by import itself. In older versions of Oracle, the temp tablespaces were no different from other tablespaces. But in newer versions temp tablespaces are different. So in dmp files from thoese older versions create user statements are written like create user t1 identified by t1 default tablespace temp temporary tablespace temp. This thing worked fine in older versions but in newer versions we cannot specify the TEMP tablespace as the default tablespace for a user. So the statement create user t2 identified by t2 default tablespace temp temporary tablespace temp throws ORA-12910: cannot specify temporary tablespace as default tablespace. In such cases the users (for which default statement is specified as TEMP) have to be created manually by specifying the appropriate tablespace as default tablespace and then the import should be run with ignore=Y.


Hats off...


Since I switched to this new job, my profile has changed. Here I work as a DBA. So my interaction with Oracle & anything related to Oracle has also increased. Started exploring Oracle related forums and websites specially OTN forums ( This is the only page thats almost always open on my Desktop in office & Laptop at home and I refresh more than my office Lotus Notes. Today I was reading APCs blog , that Jonathan Lewis has also started posting on OTN forums and being so busy man, from where he finds the time ? I too think the same. There are so many people having many years of experience in industry, answering the questions on OTN, uesnet groups and various other forums and everything is for free. They are not paid anything for the same thing. Its like taking time out of your time, understand somebody's problem, create same scenario your PC, try out and then post the answer ! I am, sort of new to the forums and sometimes for whole of week, I am unable to post any answers, even knowing something about the issue someone has posted. Just the "time" thing. These days I am very close to OTN forums, visit for whole of the day and also post answers to the questions I know something about. There are many people who are regular visitors and are answering questions on the daily basis. Hats off ! to all these "big bosses" of the technology !


Shell script to spool a no of tables into .xls files...


On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don't have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.

cat list.txt | while read a
echo "spooling $a"
sqlplus username/password@string <set feed off markup html on spool on
spool /home/oracle/$a.xls
select * from $a;
spool off
set markup html off spool off
I didnt see any work around for Windoze as SQLPLUS << EOF thing doesn't seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.


Think before you type...


Today morning, I was reading an article in The Times of India. The title "Your darkest secrets in office comp" was pretty interesting, article was too. Talk about monitoring of the things you do on your office computer. Well, the question is "is it right to monitor everything that they do ?" There is no denying the fact that there are people who are sort of "threat" to company policies, security and whatever...but the other side or opinion is that for people working in IT companies spend most of their time sitting in the offices (due to workload, end dates and all...) So the office life is not only their professional life, its personal also. Very less they interact with society (not like others, at least). Their friends, close ones are again the people working in IT companies. So if one sends a mail containing something personal sort of, to his friend from his office id and the same is being monitored (again by humans). This is definitely not fair. Not only this monitoring there are hell lot of other things too...


Is multiplication faster than division ?


An interesing post by Laurent. Check out

My findings on 10gR2 on Windoze XP

SQL> var z number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> set timi off
SQL> print y


SQL> exec :z := power(2,-104)*2e31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> set timi off
SQL> print y