Show All online SID Database Oracle 11 in Linux SuSe and Find Database file [closed]

Question

I'm new with Oracle DBA in my new office, and they gave me a development server that already have a lot SID database online in SuSe (I checked with lsnrctl status)

My questions are :

1. How to check what SID instance that really live/online, because turns out when I checked again with ps ax|grep ora, only 3 of databases live/online?

2. If only 3 SID databases that really online like in 1st question, how do I know where all the 3 SID the database file position that already live/online? what do I have to type to know it?

3. I try to login sqlplus sys as sysdba when I checked with select name from v$database; I was logging in 1 SID database. Continuing from 2nd question, How do I logout from current SID database and then switch to other 2 SID database?

   (example in MongoDB, I type use tutorial to enter tutorial database and type use dbdevelopment to switch to database dbdevelopment)

Answer 1

1. cat /etc/oratab

2. As a DBA, you need to learn the Oracle Data Dictionary.

"One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database"

SQL>select name from v$datafile;

# set environment
. oraenv <<< SID1; 
# login as sys user (IPC)
sqlplus / as sysdba
# list datafiles
SQL>select name from v$datafile;
SQL>exit
# repeat for SID2 and SID3

Side note.

One Oracle Database System (SID) can serve multiple databases (schemas). It is expensive, in terms of resources, to have multiple systems running.

# create database 1
SQL>create user DB_OWNER_1 identified by 'pass';
SQL>create table db_owner_1.t (x char(1));
# create database 2
SQL>create user DB_OWNER_2 identified by 'pass';
SQL>create table db_owner_2.t (x char(1));


# login as 'app user A' who has privileges to manipulate tables (DML) in database 1.
sqlplus appuserA/pass@TNS_ALIAS_1 
SQL>alter session set current_schema = "DB_OWNER_1"
SQL>insert into t values ('x');

# login as 'app user B' who has privileges to manipulate tables (DML) in database 2.
sqlplus appuserB/pass@TNS_ALIAS_2 
SQL>alter session set current_schema = "DB_OWNER_2"
SQL>insert into t values ('x');

You now have two applications operating on two different databases served by the one and same Oracle Database System.

Best of luck!

Answer 2

First, let's square away some of your terminology. In the Oracle world, a "SID" is the collection of processes and memory structures that manages a "database". A "database" is the collection of files that contain the data. One SID manages exactly one database, but in RAC (Real Application Cluster) configuration, one database can be managed by multiple SIDs. Thus your constant use of the term "sid database" is at best a bit awkward. While the SID and the DATABASE are two separate entities, their close relation leads to the term 'database' to be commonly used a bit more generiacly to refer to both the SID and the database.

As previously pointed out, in the oracle world, the term 'database' refers to a different concept than it does in other rdbms systems, such as MS Sql Server, IBM DB2, Sybase, etc. When those systems refer to 'database', they are talking about what oracle refers to as a 'schema'. In oracle a 'schema' is the collection of objects owned by a database user account, and is a logical structure within a database. A database has many schemas.