Does open database really open?

Ra ra ra, the database has been created, then the tables were created, and even data was entered into the table. Ok, so now I want to shut this thing
down and see if it starts again.

OK, so I shutdown the database, then was about to issue a startup when I thought wait a minute does this thing really open any files when I say open
database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size                  2213976 bytes
Variable Size            1.7448E+10 bytes
Database Buffers         9395240960 bytes
Redo Buffers              145174528 bytes
Database mounted.

I then found out the process id (PID's) of the Database Writer process.

[root@isvx3 ~]#  ps -ef | grep dbw
oracle    9433     1  0 Dec22 ?        00:00:00 asm_dbw0_+ASM
oracle   32005     1  0 15:43 ?        00:00:00 ora_dbw0_abc
oracle   32007     1  0 15:43 ?        00:00:00 ora_dbw1_abc
oracle   32009     1  0 15:43 ?        00:00:00 ora_dbw2_abc
root     32039 31716  0 15:44 pts/3    00:00:00 grep dbw

Then straced one of the database writed process and collected the output in file.

[root@isvx3 ~]# strace -o /tmp/dbw.txt -p 32005 &

Then I issued the database open command at the sql prompt

SQL> alter database open;

Database altered.

SQL>

Now the moment of truth, Ta-Da....

-bash-3.2$ cat /tmp/dbw.txt | grep open
open("/proc/31987/stat", O_RDONLY)      = 18
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_system_6kt8jg4q_.dbf", O_RDONLY) = 18
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_system_6kt8jg4q_.dbf", O_RDONLY) = 18
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_system_6kt8jg4q_.dbf", O_RDWR|O_SYNC) = 18
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_sysaux_6kt8jg6r_.dbf", O_RDONLY) = 19
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_sysaux_6kt8jg6r_.dbf", O_RDONLY) = 19
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_sysaux_6kt8jg6r_.dbf", O_RDWR|O_SYNC) = 19
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_undotbs1_6kt8jg8b_.dbf", O_RDONLY) = 20
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_undotbs1_6kt8jg8b_.dbf", O_RDONLY) = 20
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_undotbs1_6kt8jg8b_.dbf", O_RDWR|O_SYNC) = 20
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf", O_RDONLY) = 21
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf", O_RDONLY) = 21
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf", O_RDWR|O_SYNC) = 21
open("/u02/app/oracle/oradata/ABC/datafile/foo1.dbf", O_RDONLY) = 22
open("/u02/app/oracle/oradata/ABC/datafile/foo1.dbf", O_RDONLY) = 22
open("/u02/app/oracle/oradata/ABC/datafile/foo1.dbf", O_RDWR|O_SYNC) = 22
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_temp_6kt8lr7z_.tmp", O_RDONLY) = 23
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_temp_6kt8lr7z_.tmp", O_RDONLY) = 23
open("/u02/app/oracle/oradata/ABC/datafile/o1_mf_temp_6kt8lr7z_.tmp", O_RDWR|O_SYNC) = 23
open("/u02/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb", O_RDONLY) = 24
-bash-3.2$

We can see a list of all the files that were opened after open database was called at the sqlplus prompt.

The case of the missing table!!!

 Now that I had created a database 'abc' using dbca I decided to take create a little database to keep track of my favourite Bollywood movies.

-bash-3.2$ export ORACLE_SID=abc
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 31 09:29:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table movies (
  2  id number,
  3  movie_name varchar2(30),
  4  actor varchar2(30),
  5  actress varchar2(30),
  6  year_released number
  7  );

Table created.

SQL>

SQL> insert into movies
  2  values ( 1, 'Sholay', 'Amitabh Bachchan', 'Hema Malini', 1975);

1 row created.

SQL>
SQL> insert into movies
  2  values (2, 'Khilona', 'Sanjeev Kumar', 'Mumtaz', 1970);

1 row created.

SQL>
SQL> insert into movies
  2  values (3, 'Chupke Chupke', 'Dharamendar', 'Sharmila Tagore',1975);

1 row created.

So now I want to know my table was really created, and if so on what tablespace

SQL> select owner, table_name, tablespace_name
  2  from dba_tables
  3  where table_name='movies';

no rows selected

SQL>

What??? Where is my table??? Well Ok, don't forget we are working with Oracle, and that would have been too easy.

SQL> select owner, table_name, tablespace_name
  2  from dba_tables
  3  where table_name='MOVIES';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
SYS                            MOVIES
SYSTEM

Yes!!! It's bloody case sensitive. Now why would they do that, and even it was, why would Oracle not fix it. Don't
ask cause we are dealing with Oracle out here.

Anyroad, the table has been created and it's in the SYSTEM tablespace

SQL> select table_name, tablespace_name
  2  from user_tables
  3  where table_name = 'MOVIES';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
MOVIES                         SYSTEM

It's never big enough!!!

Yeah! Yeah! I'm talking about the USER tablespace out here. I wanted my USER Tablespace to be big, bigger, biggest , so I decided to extend it's size by adding a datafile to it.

Ok, so should I create a file and then give it to the tablespace or should I just tell it to create one and add it to the USER tablespace. Well, I tried both methods.

Here I created a file foo2.dbf and used it to extend the tablespace. As we see it failed with an error message.

SQL> alter tablespace users add datafile '/u02/app/oracle/oradata/ABC/datafile/foo2.dbf' size 500M;
alter tablespace users add datafile '/u02/app/oracle/oradata/ABC/datafile/foo2.dbf' size 500M
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u02/app/oracle/oradata/ABC/datafile/foo2.dbf'
ORA-27038: created file already exists
Additional information: 1

Next, I added the datafile with creating the file beforehand, and it worked fine.

SQL> alter tablespace users add datafile '/u02/app/oracle/oradata/ABC/datafile/foo1.dbf' size 500M;

Tablespace altered.

SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf
USERS

/u02/app/oracle/oradata/ABC/datafile/foo1.dbf
USERS


SQL>

Moral of the story, you don't need to create the file in advance to extend tablespace. You just have to pass the file name along with the path, and the rest is taken care for you.

Tablespace and their underlying datafiles

Now that I know where the control, data and the redo log files of my database "abc" are, I'm now interested in getting some information about the tablespaces. After reading the reams and reams of our lovely Oracle documentation I now know that tablespace are made up of one of more datafiles.

I was to first find out what the names of the tablespaces are for my database "abc" created by Oracle dbca.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP

Ok, now what are the files that are associated with these tablespaces.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf
USERS

/u02/app/oracle/oradata/ABC/datafile/o1_mf_undotbs1_6kt8jg8b_.dbf
UNDOTBS1

/u02/app/oracle/oradata/ABC/datafile/o1_mf_sysaux_6kt8jg6r_.dbf
SYSAUX


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_system_6kt8jg4q_.dbf
SYSTEM


SQL>

Where are my control, data, and redo log files?

I used Oracle's dbca tool under $ORACLE_HOME/bin/dbca to create a database using the "General Purpose or Transactional Processing" template, which included the datafiles.

I used File system for the Storage Type, and used Oracle-Managed files for the storage location.

Database Area: $ORACLE_BASE/oradata

Flash Recovery Area: $ORACLE_BASE/flash_recovery_area

The server parameter file: $ORACLE_HOME /dbs/spfileabc.ora

-bash-3.2$ echo $ORACLE_HOME
/u02/app/oracle/product/11.2.0/dbhome_1
-bash-3.2$ echo $ORACLE_SID

-bash-3.2$ export ORACLE_SID=abc
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 30 16:26:30 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_system_6kt8jg4q_.dbf
/u02/app/oracle/oradata/ABC/datafile/o1_mf_sysaux_6kt8jg6r_.dbf
/u02/app/oracle/oradata/ABC/datafile/o1_mf_undotbs1_6kt8jg8b_.dbf
/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/ABC/controlfile/o1_mf_6kt8lgnc_.ctl
/u02/app/oracle/flash_recovery_area/ABC/controlfile/o1_mf_6kt8lgxg_.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/ABC/onlinelog/o1_mf_3_6kt8lo0w_.log
/u02/app/oracle/flash_recovery_area/ABC/onlinelog/o1_mf_3_6kt8lomz_.log
/u02/app/oracle/oradata/ABC/onlinelog/o1_mf_2_6kt8lms1_.log
/u02/app/oracle/flash_recovery_area/ABC/onlinelog/o1_mf_2_6kt8lnf2_.log
/u02/app/oracle/oradata/ABC/onlinelog/o1_mf_1_6kt8lkp2_.log
/u02/app/oracle/flash_recovery_area/ABC/onlinelog/o1_mf_1_6kt8lm49_.log

6 rows selected.

SQL>

ORA-12162: TNS:net service name is incorrectly specified

-bash-3.2$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 30 16:29:38 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified


Enter user-name:

Yes, this is one of Oracle's friendly and useful error messages. It is so clear from the error message that as soon as we get it, we'll go and set ORACLE_SID to the name of our database and everything will work fine after that.

-bash-3.2$ export ORACLE_SID=abc
-bash-3.2$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 30 16:34:22 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

fsck.ext3: No such file or directory while trying to open /dev/dm-1 [FAILED]

I was mucking around with my Storwize V7000 storage array that was connected to my Red Hat Linux server. Then I went back to my Red Hat server and started configuring couple of applications on it, and then rebooted the machine.

The boot process stopped, and I got the message:

Checking filesystems
fsck.ext3: No such file or directory while trying to open /dev/dm-1 [FAILED]
*** An error occurred during the file system check.
*** Dropping you to a shell; the system will reboot
*** when you leave the shell.
Give root password for maintainance
(or type Control-D to continue):

I realised  that in the processing of mucking around with the storage I had removed then volume(LUN) that I had created, and then mapped onto the Red Hat server. I had then created a ext3 file system on that volume.

It was this volume(LUN) that the boot process was complaining about.

To fix the issue, I logged into the machine in single user mode with my root password. The fix is rather straight forward, uncomment the mount point line in the /etc/fstab file.

You will see that you won't be able to do that, so what you need to do is mount / in read write mode.

#  mount -o remount,rw /

# vi /etc/fstab

comment out the line, and then reboot the machine. There you go, it's fixed!!!