Newbie Guide to Oracle 11g Database Common Problems - Create & Start Database, Listener , Enterprise Manager and more
Oracle Database 11g Setup and Problems
I recently setup the Oracle database 11g on my server at home, and went through a lot of troubleshooting to solve the many different errors I came across thought I would make this hub to help fellow database newbies find answers to there questions. :)
Create an Oracle Database
Creating an oracle database can be done in two basic ways either through sql (sqlplus used with oracle 11g) or using the Database configurarion Assistant.
The simplest way to setup a database is to use the database configuration assistant or dbca , but this of course can only be used for people installing there oracle database on a gui interface operating system such as a linux desktop O/S.
Now to run the database configuration assistant locate the home directory for your Oracle database installation from here ensure that you are logged in as the user with permissions to use and setup the oracle database.
Many people will install oracle using the ROOT user but this is a very bad idea and you will run into security issues down the line as well the fact that Oracle hates to use the root user for the database user.
So once your in your Oracle Database directory simply run
or sh dbca ( to shell to the database configuration assistant)
The Gui will then startup and you can simply follow the prompts to install the database you require.
Common Problem Oracle Permission Denied
Another very common problem you may run into is the permission denied error message and this can occur when you try to perform an action that is not permitted by the user you are logged in as , and this is usually the most common reason.
The best thing to do when using a GUI operating system is to actually check the permissions of specific files that are giving you problems and for an example this is how I do it on Ubuntu. Make sure you are doing everything with the USER that installed the Oracle Database to avoid these sorts of problems.
now type sudo nautilus in the textbox and hit enter
You can now browse folders with root permissions and check permissions as well as alter them ( be careful with this you can end up creating quite a mess if you don't know what you're doing)
Through Command Line
Otherwise you can do it straight through the command line by viewing the files in your current directory
a file will look like the following d rwx r-x --- example.sh
here we have Read Permissions, Write Permissions, and whether or not it is Executable
You can use the chmod command to alter permissions
An example of the chmod command is
chmod u-x,g+w,o+rw yourfile
Oracle Common Problem - SID or DISPLAY not set correctly
This is another very common error and it simply the Oracle System asking you to specify the SID (system identifier = Oracle DB name) or your DISPLAY which simply refers to whereabouts your server is located as in IP address.
For a simple Oracle SID not specified error simple use the follow
export ORACLE_SID = "database name"
For a DISPLAY error
export DISPLAY = 192.168.1.129:0.0
In the format above, 192.168.1.129 is my server address with no specific port setup, where the oracle database is installed, alternatively you can simply specify localhost as your server location but the IP usually is best.
Oracle Database - Creating Listener, Listener Services and Status
So a major part of the Oracle database system is the Listener and its role is to listener to a specified port for request to the database, and your database setup will not function correctly without it and you will receive errors such as no listener setup or listener supports no services.
To create a database and start it we can first create it using the Net configuration assistant, which can be found in you oracle home directory
by running sh netca or ./netca , may differ depending on your linux o/s
Once setup we can access all the listener controls using the listener comand line we can login to that using "lsnctrl" , from here we can start or shutdown the listener.
Listener Supports no Services
For most new to database this error usually occurs when we start our listener before our database has actually been created and mounted, the listener will support no services as it will be the only thing running at the time. For me it always worked fine starting the listener after the database was mounted correctly.
The best way to startup everything without the use of a script was to login to SQL and start all database services then start the listener after that.
Starting SQL*PLUS and mounting your Database, Followed by Listener initialization
From your home directory run SQL by the following
sqlplus 'as sysdba' ( this is a default way to get in and the easiest to setup more users later on)
From here simply type startup
This will then go through the process of starting up the database and mounting it, you will be presented with database created and mounted when it is successful.
from here you can now start the listener using the listener control lsnctrl and everything should be fine, if you are still getting no services supported by the listener you should take a look at the setup of your listener.ora and tsnames.ora (check the settings there and ensure its pointing to the correct directory), also using lsnctrl status can show you what is registed with your listener
can be found /oracle/product/11/db_1/network/admin
Starting Oracle Enterprise Manager
The oracle Enterprise Manager is a great software program that comes with Oracle 11g and can be used to carry out administrator tasks and even startup listeners and database's as well as check on the health of the server.
To run the Enterprise Manager once again, you have to be in the Oracle home directory and use the emctl
now type emctl start dbconsole to start the enterprise manager it will then give you the address of the Enterprise Manager which is usually https://serverip/em
Use emctl status dbconsole to see details of your Enterprise Manger and URL