30 September 2011

Accessing XML data from SQL

Oracle has powerful utilities for developeers. The one that i like is utility function for playing XML data. Utility functions allows you to access XML file as if the XML data is inside a database table. Suppose that you have a company XML file and you have to access the XML data without uploading/importing/loading XML file into database. XMLTABLE is just stands for this reason. It has a special syntax-XQuery- for accessing XML data and its attributes. To understand better, please follow the demonstration below. XML file(company.xml) is resides server side file system(/home/oracle/Documents/). XMLTYPE, BFILE and XMLTABLE will be used:


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as mennan

SQL> 
SQL> 
SQL> 
SQL> 
SQL> CREATE OR REPLACE DIRECTORY COMPANY_REPORT_DIR AS '/home/oracle/Documents/';

Directory created
SQL> CREATE TABLE COMPANY_LIST
  2  (
  3    ID                VARCHAR2(4),
  4    COMPANY_TITLE     VARCHAR2(32)
  5  );

Table created
SQL> SELECT XMLTYPE(BFILENAME('COMPANY_REPORT_DIR', 'company.xml'), nls_charset_id('UTF8')).GetStringVal() AS XML_DATA FROM DUAL;

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-9"?>
<CompanyList>
  <Company>
    <Identifier>0001</Identifier>
    <Title>Oracle</Title>
  </Company>
  <Company>
    <Identifier>0002</Identifier>
    <Title>Microsoft</Title>
  </Company>
  <Company>
    <Identifier>0003</Identifier>
    <Title>Apple</Title>
  </Company>
  <Company>
    <Identifier>0004</Identifier>
    <Title>Google</Title>
  </Company>
</CompanyList>

SQL> INSERT INTO COMPANY_LIST ( ID, COMPANY_TITLE )
  2    SELECT x.ID, x.COMPANY_TITLE
  3      FROM (SELECT XMLTYPE(BFILENAME('COMPANY_REPORT_DIR', 'company.xml'), nls_charset_id('UTF8')) AS XML_DATA FROM DUAL) e,
  4           XMLTABLE('for $i in /CompanyList
  5                     return $i/Company'
  6                     PASSING XML_DATA
  7                     COLUMNS ID            VARCHAR2(4)   PATH 'Identifier',
  8                             COMPANY_TITLE VARCHAR2(32)  PATH 'Title'
  9                     ) x;

4 rows inserted
SQL> COMMIT;

Commit complete
SQL> SELECT * FROM COMPANY_LIST;

ID   COMPANY_TITLE
---- --------------------------------
0001 Oracle
0002 Microsoft
0003 Apple
0004 Google
SQL> DROP DIRECTORY COMPANY_REPORT_DIR;

Directory dropped
SQL> DROP TABLE COMPANY_LIST;

Table dropped

SQL> 




For more information please read the documentation : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB5121

29 September 2011

unx:Exception during creation of the process


I had created some automated test scripts for one of my projects. For utility functions, i had created a simple shell executor which is a java source and compiled in the oracle database in order to help me to execute unix commands from pl/sql.  After i did some tests, i realized that the shell executor results an error as shown below:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as mennan

SQL> set serveroutput on;
SQL>
SQL> DECLARE
  2    CommandText  varchar2(4000);
  3  BEGIN
  4    CommandText := ShellExecutor(pis_command =>'/bin/ls -1tr');
  5    dbms_output.put_line( 'Return : ' ||  CommandText);
  6  END;
  7  /

Return : RET:unx:Exception during creation of the process

PL/SQL procedure successfully completed



 When i grant “java_deploy” role to the my user, the problem was solved:


SQL> connect sys/sysadm@testdb.vm as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as SYS

SQL> grant java_deploy to mennan;

Grant succeeded

SQL> connect mennan/sysadm@testdb.vm
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as mennan

SQL> set serveroutput on;
SQL>
SQL> DECLARE
  2    CommandText  varchar2(4000);
  3  BEGIN
  4    CommandText := ShellExecutor(pis_command =>'/bin/ls -1tr');
  5    dbms_output.put_line( 'Return : ' ||  CommandText);
  6  END;
  7  /

Return : RET:unx:init.ora
initdw.ora
lktestdb
hc_testdb.dat
orapwtestdb
spfiletestdb.ora

The source files of ShellExecutor is shown below:


Java source to be compiled in the database:
create or replace and compile java source named Shell as

import java.io.*;
public class Shell {
 
  public static String executeCommand(String _command) {
    String commandOutput, temp, inputStreamOutput = "", errorStreamOutput = "";

    try {
   
      String[] shellCommandArray;
      if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
        shellCommandArray = new String[4];
        shellCommandArray[0] = "C:\\windows\\system32\\cmd.exe";
        shellCommandArray[1] = "/y";
        shellCommandArray[2] = "/c";
        shellCommandArray[3] = _command;
      }
      else {
        shellCommandArray = new String[3];
        shellCommandArray[0] = "/bin/sh";
        shellCommandArray[1] = "-c";
        shellCommandArray[2] = _command;
      }

      Process runtimeProcess = Runtime.getRuntime().exec(shellCommandArray);
      BufferedReader readerInputStream = new BufferedReader(new InputStreamReader(runtimeProcess.getInputStream()));
      temp = null;
      while ((temp = readerInputStream.readLine()) != null) {
      inputStreamOutput+=temp+ System.getProperty("line.separator") ;
      }
      readerInputStream.close();
     
      BufferedReader readerErrorStream = new BufferedReader(new InputStreamReader(runtimeProcess.getErrorStream()));
      temp = null;
      while ((temp = readerErrorStream.readLine()) != null) {
      errorStreamOutput+=temp + System.getProperty("line.separator") ;
      }
      readerErrorStream.close();

    }
    catch (Exception ex) {
    errorStreamOutput=ex.getMessage();
    }
   
    if ( errorStreamOutput.length() > 0  ){
    commandOutput = "E:"+errorStreamOutput;
    }else{
    commandOutput = "S:"+inputStreamOutput;
    }   
   
    return commandOutput;
   
     }

}


PL/,SQL Wrapper function:
CREATE OR REPLACE FUNCTION ShellExecutor(pis_CommandText IN VARCHAR2 )
  RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Shell.executeCommand( java.lang.String ) return java.lang.String';
/



Sample test script:
set serveroutput on;


DECLARE
  vs_CommandText VARCHAR2(4000);
  vs_ReturnText  VARCHAR2(4000);
  vs_SuccessFail VARCHAR2(2);
BEGIN
  --To list directories
  --vs_CommandText := '/bin/ls -1tr';
 
  --To remove files
  vs_CommandText := '/bin/rm  /home/oracle/temp/dummy.txt';
 
  vs_ReturnText  := ShellExecutor(vs_CommandText);
  vs_SuccessFail := substr(vs_ReturnText, 1, 2);
  vs_ReturnText  := substr(vs_ReturnText, 3);

  IF vs_SuccessFail <> 'S:' THEN
    dbms_output.put_line('Failed!!!');
  END IF;

  dbms_output.put_line(vs_ReturnText);
END;
/

26 September 2011

Parallelization of Scheduler Jobs with Advanced Queueing


One of the useful utilities of Oracle database is Scheduler.With Scheduler, it is possible to use an Oracle database as a complex,configurable job scheduling mechanism, like cron of unix. I will not go deepinside of the capabilities of Oracle’s Scheduler.(you can search previousposts) The main reason that made me to write this post is, making the sameScheduler Job to be run at the same time, as multiple instances. That is, it isnot possible to run the same job simultaneously. You can run the Job, after the running-onefinishes. This is not something bad, asit sounds. On the contrary, it is a must in order to keep atomicity of programunits, jobs. Remember usage of semaphores. For the shared resources, a controlmechanism should exist in order prevent common resources to be usedsimultaneously at the same time. This is main concept of parallel computing.

Suppose that, running the same job more than once at thesame time is not *something* bad for your application design. Again, supposethat your jobs has parameters and you are controlling atomicity of the job byyourself. So, you want to get rid of serial execution restrictions ofScheduler. But how?
I like finding workarounds for the problems. There arealways some problems in our lives. In order to overcome the problems that arenot created by you, you should find an exit-gate. Finding an exit-gate is notas easy as in the movies. …

Let’s go…

To make a Scheduler job to be run simulataneously, youshould take advantages of Advanced Queueing(AQ). Again, this is anotherpowerful utilities of Oracle for developers. You are not forced to createtables, programs, algorithms for a simple queue. With Oracle’s AQ, you have atoolkit for queue generation. Those interfaces that exists in the toolkit, willhelp you to create complex queues.(for more info, please check Oracle’s doc.)

I simulated serial execution(only Scheduler) and parallelexecution(Sheduler + AQ) below. I think analyzing the code pieces below, willmake you to understand better.


Connected to Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0
Connected as mennan

SQL> --serial execution: scheduler program-job
SQL> CREATE TABLE LOG_TABLE
2 (
3 ID NUMBER,
4 CALLER VARCHAR2(32),
5 CALL_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP
6 );

Table created
SQL> CREATE OR REPLACE PROCEDUREInsertLogTable(pin_Id IN LOG_TABLE.ID%TYPE) AS
2 BEGIN
3 INSERT INTO LOG_TABLE (ID,CALLER) VALUES(pin_Id, 'SERIAL_EXECUTION_PRG');
4 COMMIT;
5 DBMS_LOCK.sleep(3);
6 END;
7 /

Procedure created
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_PROGRAM(program_name => 'SERIAL_EXECUTION_PRG',
3 program_type =>'STORED_PROCEDURE',
4 program_action =>'InsertLogTable',
5 number_of_arguments => 1,
6 enabled => FALSE,
7 comments => 'The program that simulatesserial execution of dbms_scheduler.');
8 END;
9 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => 'SERIAL_EXECUTION_PRG',
3 argument_position => 1,
4 argument_name => 'pin_Id',
5 argument_type => 'NUMBER',
6 default_value => NULL);
7 END;
8 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(job_name => 'SERIAL_EXECUTION_JOB',
3 program_name=> 'SERIAL_EXECUTION_PRG',
4 enabled => FALSE,
5 auto_drop => FALSE,
6 comments => 'The job that simulates serialexecution of dbms_scheduler by inserting log table.');
7 END;
8 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_SCHEDULER.enable(NAME =>'SERIAL_EXECUTION_PRG');
3 END;
4 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_SCHEDULER.enable(NAME =>'SERIAL_EXECUTION_JOB');
3 END;
4 /

PL/SQL procedure successfully completed
SQL> pause;
SQL> ----TEST:run 1
SQL> BEGIN
2 DBMS_SCHEDULER.set_job_argument_value(job_name => 'SERIAL_EXECUTION_JOB',
3 argument_position => 1,
4 argument_value => 1);
5 DBMS_SCHEDULER.run_job(job_name => 'SERIAL_EXECUTION_JOB',
6 use_current_session=> FALSE);
7 END;
8 /

PL/SQL procedure successfully completed
SQL> --run 2
SQL> BEGIN
2 DBMS_SCHEDULER.set_job_argument_value(job_name => 'SERIAL_EXECUTION_JOB',
3 argument_position => 1,
4 argument_value => 2);
5 DBMS_SCHEDULER.run_job(job_name => 'SERIAL_EXECUTION_JOB',
6 use_current_session =>FALSE);
7 END;
8 /

BEGIN
DBMS_SCHEDULER.set_job_argument_value(job_name => 'SERIAL_EXECUTION_JOB',
argument_position => 1,
argument_value => 2);
DBMS_SCHEDULER.run_job(job_name => 'SERIAL_EXECUTION_JOB',
use_current_session=> FALSE);
END;

ORA-27478:"MENNAN.SERIAL_EXECUTION_JOB" işi çalışıyor
ORA-06512: konum"SYS.DBMS_ISCHED", satır 185
ORA-06512: konum"SYS.DBMS_SCHEDULER", satır486
ORA-06512: konum satır 6


SQL> select * from log_table where id is notnull;

ID CALLER CALL_TIMESTAMP
---------- ---------------------------------------------------------------------------------
1SERIAL_EXECUTION_PRG 26/09/2011 20:26:30,689920

SQL>

---rollback
DROP TABLE LOG_TABLE;
DROP PROCEDURE InsertLogTable;
BEGIN
DBMS_SCHEDULER.drop_job(job_name => 'SERIAL_EXECUTION_JOB');
END;
/

BEGIN
DBMS_SCHEDULER.drop_program(program_name => 'SERIAL_EXECUTION_PRG');
END;
/






SQL> --parallel execution: scheduler program-job + advanced queue
SQL> CREATE TABLE LOG_TABLE
2 (
3 ID NUMBER,
4 CALLER VARCHAR2(32),
5 CALL_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP
6 );

Table created
SQL> CREATE OR REPLACE TYPE LOG_TYPE IS OBJECT
2 (
3 ID NUMBER
4 );
5 /

Type created
SQL> BEGIN
2 DBMS_AQADM.create_queue_table(queue_table => 'AQ_LOG_QUEUE_TABLE',
3 queue_payload_type => 'LOG_TYPE',
4 multiple_consumers => TRUE);
5 END;
6 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_AQADM.create_queue(queue_name => 'AQ_LOG_QUEUE',
3 queue_table =>'AQ_LOG_QUEUE_TABLE');
4 END;
5 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_AQADM.start_queue(queue_name =>'AQ_LOG_QUEUE',
3 enqueue => TRUE,
4 dequeue => TRUE);
5 END;
6 /

PL/SQL procedure successfully completed
SQL> --select * from all_queue_tables wherequeue_table = 'AQ_TEST_QUEUE_TABLE';
SQL> --select * from all_queues where NAME ='AQ_TEST_QUEUE';
SQL> CREATE OR REPLACE PROCEDUREInsertLogTable2(pit_LogType IN LOG_TYPE) AS
2 BEGIN
3 INSERT INTO LOG_TABLE (ID,CALLER) VALUES(pit_LogType.ID, 'PARALLEL_EXECUTION_PRG');
4 COMMIT;
5 DBMS_LOCK.sleep(3);
6 END;
7 /

Procedure created
SQL> BEGIN
2 DBMS_SCHEDULER.create_program(program_name => 'PARALLEL_EXECUTION_PRG',
3 program_type =>'STORED_PROCEDURE',
4 program_action =>'InsertLogTable2',
5 number_of_arguments => 1);
6 DBMS_SCHEDULER.define_metadata_argument(program_name => 'PARALLEL_EXECUTION_PRG',
7 argument_position => 1,
8 metadata_attribute=> 'EVENT_MESSAGE');
9 DBMS_SCHEDULER.enable('PARALLEL_EXECUTION_PRG');
10 END;
11 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_SCHEDULER.create_job(job_name => 'PARALLEL_EXECUTION_JOB',
3 program_name => 'PARALLEL_EXECUTION_PRG',
4 event_condition=> 'TAB.USER_DATA.ID IS NOT NULL',
5 queue_spec => 'AQ_LOG_QUEUE',
6 auto_drop => FALSE);
7 DBMS_SCHEDULER.enable('PARALLEL_EXECUTION_JOB');
8 END;
9 /

PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_SCHEDULER.set_attribute(NAME => 'PARALLEL_EXECUTION_JOB',
3 ATTRIBUTE=> 'parallel_instances',
4 VALUE => TRUE);
5 END;
6 /

PL/SQL procedure successfully completed
SQL> --select * from all_scheduler_job_log where job_name = 'PARALLEL_EXECUTION_JOB'order by 1 desc;
SQL> --select * fromall_scheduler_job_run_details wherejob_name = 'PARALLEL_EXECUTION_JOB' order by 1 desc;
SQL> -- TEST : run 1
SQL> DECLARE
2 vt_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
3 vt_MessagePropertiesDBMS_AQ.MESSAGE_PROPERTIES_T;
4 vt_RequestObject LOG_TYPE;
5 vr_MessageId RAW(16);
6 BEGIN
7 vt_RequestObject := LOG_TYPE(123);
8
9 DBMS_AQ.enqueue(queue_name => 'AQ_LOG_QUEUE',
10 enqueue_options => vt_EnqueueOptions,
11 message_properties =>vt_MessageProperties,
12 payload => vt_RequestObject,
13 msgid => vr_MessageId);
14 dbms_output.put_line('vr_MessageId : ' ||vr_MessageId);
15 COMMIT;
16 END;
17 /

PL/SQL procedure successfully completed
SQL> -- run 2
SQL> DECLARE
2 vt_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;
3 vt_MessagePropertiesDBMS_AQ.MESSAGE_PROPERTIES_T;
4 vt_RequestObject LOG_TYPE;
5 vr_MessageId RAW(16);
6 BEGIN
7 vt_RequestObject := LOG_TYPE(124);
8
9 DBMS_AQ.enqueue(queue_name => 'AQ_LOG_QUEUE',
10 enqueue_options => vt_EnqueueOptions,
11 message_properties =>vt_MessageProperties,
12 payload => vt_RequestObject,
13 msgid => vr_MessageId);
14 dbms_output.put_line('vr_MessageId : ' ||vr_MessageId);
15 COMMIT;
16 END;
17 /

PL/SQL procedure successfully completed
SQL> select * from LOG_TABLE;

IDCALLER CALL_TIMESTAMP
---------- ---------------------------------------------------------------------------------
123PARALLEL_EXECUTION_PRG 26/09/2011 20:42:34,078989
124PARALLEL_EXECUTION_PRG 26/09/2011 20:42:34,080255

SQL>
SQL>

--rollback
BEGIN
dbms_scheduler.drop_job('PARALLEL_EXECUTION_JOB');
dbms_scheduler.drop_program('PARALLEL_EXECUTION_PRG');
dbms_aqadm.stop_queue('AQ_LOG_QUEUE');
dbms_aqadm.drop_queue('AQ_LOG_QUEUE');
dbms_aqadm.drop_queue_table('AQ_LOG_QUEUE_TABLE');
END;
/
DROP TABLE LOG_TABLE;
DROP TYPE LOG_TYPE;
DROP PROCEDURE InsertLogTable2;

25 September 2011

ORA-27300: OS system dependent operation:semget failed with status: 28


ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed withstatus: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

Although theerror says “No space left on device”, but it is not true. The problem is duekernel semaphore configuration. After setting a proper value(echo"250 1500 100 128" > /proc/sys/kernel/sem), the problemwill be solved:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on MonJun 21 08:20:51 2010

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

Connected to an idle instance.

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependentoperation:semget failed with status: 28
ORA-27301: OS failure message: No spaceleft on device
ORA-27302: failure occurred at:sskgpsemsper
SQL> exit
Disconnected
[oracle@localhost ~]$ df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
304G 173G 115G 61% /
/dev/hda1 99M 12M 82M 13% /boot
tmpfs 1.3G 0 1.3G 0% /dev/shm
[root@localhost kernel]# cat /proc/sys/kernel/sem
250 100 32 128
[root@localhost kernel]# echo "250 1500 100 128" > /proc/sys/kernel/sem
[root@localhost kernel]# cat /proc/sys/kernel/sem
250 500 100 128
[root@localhost kernel]#

ORA-01078: failure in processing system parameters


One of my collegues has asked me to help him to start thedatabase that has been created before. When i check, i saw that the databaseserver parameter files –somehow- has been deleted from its own place($ORACLE_HOME/dbs).Then, i opened the database from the init.ora which is created during databasecreation. Finally, i created spfile form this init.ora(pfile).


[oracle@localhost dbs]$/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun21 08:58:08 2010

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing systemparameters
LRM-00109: could not open parameter file'/app/oracle/product/11.2.0/dbhome_1/dbs/initTESTDB.ora'
SQL> exit
Disconnected
[oracle@localhost dbs]$


[oracle@localhost pfile]$/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun21 09:08:29 2010

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomountpfile=/app/oracle/admin/TESTDB/pfile/init.ora
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 209718360 bytes
Database Buffers 96468992 bytes
Redo Buffers 6336512 bytes
SQL> create spfile from pfile
2 ;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing systemparameters
LRM-00109: could not open parameter file
'/app/oracle/product/11.2.0/dbhome_1/dbs/initTESTDB.ora'


SQL> create spfile frompfile=/app/oracle/admin/TESTDB/pfile/init.ora;
create spfile frompfile=/app/oracle/admin/TESTDB/pfile/init.ora
*
ERROR at line 1:
ORA-02236: invalid file name


SQL> create spfile frompfile='/app/oracle/admin/TESTDB/pfile/init.ora';

File created.

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options
[oracle@localhost pfile]$ pwd
/app/oracle/admin/TESTDB/pfile
[oracle@localhost pfile]$ ls
init.ora
[oracle@localhost pfile]$ ls -ltr $ORACLE_HOME/dbs
total 72
-rw-r--r-- 1 oracle dba 2851 May 15 2009 init.ora
drwx------ 2 oracle dba 4096 Jun 20 14:19peshm_DBUA0_0
-rw-rw---- 1 oracle dba 1544 Jun 20 14:19hc_DBUA0.dat
drwx------ 2 oracle dba 4096 Jun 20 22:52peshm_TESTDB_0
-rw-r----- 1 oracle dba 1536 Jun 20 22:52orapwTESTDB
-rw-rw---- 1 oracle dba 1544 Jun 20 22:52hc_TESTDB.dat
-rw-r----- 1 oracle dba 24 Jun 20 22:52 lkTESTDB
-rw-r----- 1 oracle dba 1536 Jun 21 09:14spfileTESTDB.ora
-rw-r--r-- 1 oracle dba 476 Jun 21 09:14 initTESTDB.ora
[oracle@localhost pfile]$/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun21 09:15:44 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options

SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> startup
ORA-01081: cannot start already-running ORACLE -shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 209718360 bytes
Database Buffers 96468992 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
SQL> select name from v$database;

NAME
---------
TESTDB

SQL>