Posts

Automatic DB Startup: The Linux Part (OEL 6 and 7)

This is part 2 of the automatic startup article. In the previous part, we talked about the basic building blocks for the automatic Oracle database and listener startup. We talked about the orastart and orashut scripts and the /etc/oratab that control which instances are automatically started.

In this part, we will put everything together and see how to configure Linux to use the scripts for automatic start. I will demonstrate two version of Linux here. I used Oracle Enterprise Linux 6 and 7 – which are similar to RedHat Enterprise Linux 6 and 7 (but free). These startup procedures are similar to most of the other distributions including CentOS and Ubuntu.
Read more →

/11 Comments/by /Estimated Reading Time: 8 Minutes

Automatic Startup for Oracle on Linux

A few years back, I worked with a junior DBA who was asked to create a new instance on a brand new machine. He created the instance using DBCA and everything went smoothly – or so he thought. A few weeks later, after the system became production, a planned maintenance rebooted that Linux server, and once the machine came back up – the database and listener processes were nowhere to be found.

So that junior DBA, stressed because he thought the database was lost called me in the middle of the night. My first sleepy question for him was “did you even start the database?” and his panicked reply was “of course not – it should be done automatically, I used DBCA!”…

Well, one of the things new DBA’s find hard to realize is that after installing and creating a database (even if we’re using DBCA), it will not start automatically. I’ve seen too many new DBA’s being puzzled by the logic behind automatic startup of Oracle databases on Linux (and UNIX in general) than I care to admit so I decided to put it here for their reference…

In this article I will describe the building blocks for automatic startup, and in my next post I will show the Linux commands needed to put everything together.

Read more →

/1 Comment/by /Estimated Reading Time: 6 Minutes

Moving Datafiles Online in Oracle 12c

I wrote about this feature in Hebrew quite some time ago but in the last week I was asked by couple of my customers about the correct way to move datafiles with minimal downtime so I thought it would be nice to show the Oracle 12c solution before talking about the solution I gave them.

For years, moving datafiles was a pain in the DBA behinds. It required some database action, then some OS actions and then some other database actions. Starting Oracle 12c we can now move datafile online, without interfering the application and without taking the tablespace offline or read only.

Even though Oracle 12c brought a lot of new features and changes, this feature must be one of my favorites. It made thing so easy and so simple that it made me wonder what took them so long…

Read more →

/2 Comments/by /Estimated Reading Time: 3 Minutes

Oracle Pre-Built Developer VMs and VMBox

, ,

Virtual machines (VM) are not new –it has been around for quite some time, and as a consultant I find myself use them all the time. As a matter of fact, just on my laptop and external drive there are at least 15 or 20 different virtual environment which I use for testing, experimenting, and for creating new blog posts.

The thing with virtual machines that you need to be a little more than just a simple DBA to set it up – you need to know how to install an operating system, configure storage, and get your system ready for database installation, which many junior and less experienced DBAs find problematic at times.

Well, no more! Oracle comes to the rescue and provide us with pre-build developer virtual machines.

Read more →

/0 Comments/by /Estimated Reading Time: 2 Minutes

I have seen the future and it is glorious: SQLcl the next generation of SQL*Plus

, , ,

The first time I started using commercial databases was in 1998, in my basic programming course. The course was split into segments and at the first databases lesson, if my memory serves me right, the instructor began the class with the words: “Today we will learn about databases – first, let’s connect to the database using SQLPlus”.

This amazing tool, which almost everyone who works with Oracle databases knows, is one of the simplest yet powerful command line interface for working with the database. SQLPlus has been with us for many, many years – I used to use it back when I was using Oracle 7 and that says a lot. Since this is one of the most convenient tools to understand, and while it’s not very user friendly it does work great with scripts and other automatic procedures. SQLPlus have great integration with the cluster tools – for example, if we’re using it to stop an instance (regular shutdown), the grid infrastructure is aware the shutdown was on purpose, and it will not try to start up the instance node again. It even has other Oracle tools provide its users and its capabilities often (AWR reports for example).

The fact that SQLPlus has been around for so many years could have been considered a disadvantage because we would think it might not have changed over the years but this is not the case. The tools did change over the years – features and other capabilities were added and that allows us to do cool stuff like output our result to HTML format or interact with our scripts. Unfortunately that’s pretty much everything and the basics of the tool did not change.

I Recently became aware of a new tool from Oracle which will ultimately replace SQLPlus. It’s name is SQLcl and I was really impressed.

Read more →

/0 Comments/by /Estimated Reading Time: 10 Minutes

Oracle ANSI SQL and the WITH syntax

, ,

SQL language has been around for so long, that some people feel it never changes. This is obviously not true – SQL is a dynamic language that changes all the time: some changes come from the vendors themselves but often this is the result of the standard changing and Oracle adopting the new standard in a later version.

Let’s start with a short history lesson. As we all know, SQL is not a unique language for Oracle. It’s a common language with standards set by ANSI. The first version of the language standardization was presented back in 1986 with the issuance of SQL86. In 1992, a revised version of the language came out (SQL-92). When Oracle tried to implement this standard at full, they noticed that some of the things there are really different from how Oracle did things so it adopted only a part of the standard in Oracle RDBMS 8 (and 8i).
Read more →

/0 Comments/by /Estimated Reading Time: 3 Minutes