Boobooke training

You can use SQLPlus to connect to an idle instance. Then you issue "startup" to start the database.

>sqlplus /nolog ------ [[I realized that we did not specify the SID, how is that?]]
SQL>conn / as sysdba
connected to an idle instance
SQL>startup
Oracle instance started.

Total System Global area.....
....
Database mounted.
Database opened.
SQL> select * from v$sga_dynamic_components;

At this point, the database is started. Now you need to start listener:

> lsnrctl start

After this one is done, you can connect to the database from other machine.

-------------------------------------------------

Initialization Parameter Files, two of them; one is PFILE; one is SPFILE; PFILE is a text, initSID.ora (Here SID is the SID for your instance), it's under $ORACLE_HOME/dbs (under windows it's $ORACLE_HOME/database); only takes affect after restart.

v$parameter, this table has all the parameters in it. For those parameters, you can use

show parameter sga;

It will try show you all parameters containing "sga".

SQL> col name format a10;

name is the name of the column. a10 is ascii 10 char;

SQL>connect / as sysdba;
connected to an idel instance

at this step, the db is in shutdown mode; if you check the process, you will only see that the command above actually started a listener process:

oracle 3989 3987 3 22:47 pts/0 00:00:00 oraclewilson (DESCRPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

also you can issue "ipcs" command in unix environment, and it will show you that the shared memory is not allocated b/c it's still in the shutdown mode.

Now if you start it up:

SQL>startup nomount;
ORACLE instance started.

Total System Global Area 236....... bytes.
Fixed Size...
....
SQL>

At this moment, if you ps -afe, you will see a lot of background processes are started:
ora_pmon_wilson
ora_dbw0_wilson
...

and ipcs will show that the shared memory segments, Semaphore Arrays are assigned.
And since it's nomount, no database is connected to this instance yet. All the settings in the spfile have been read and applied. You can query them through the "show parameter " sqlplus command.

Next is the mount step.

The instance mounts a database to associate the database with that instance. To mount the database, the instance finds the database control files and opens them. Control files are specified in the CONTROL_FILES initialization parameter in teh parameter file used to start the instance. Oracle database then reads the cotnrol files to get the names of the database's datafiles and redo log files. At this point, database is still closed and is accessible only to the database admin. The database admin can keep the db closed while completing specific maintenance operations. However, the db is not yet available for normal operations.

Now how do we mount the database?

SQL> alter database mount;
Database altered.
SQL>

Next mode is the OPEN mode. All files are opened as described by the control file for this instance.

Opening a mounted db makes it available for normal database operation. Any valid user can connect to an open db and access its information. Usually, a db admin opens the db to make it available for general use.
When you open the db, Oracle Database opens the online datafiles and redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you reopen the database.
If any of the datafiles or redo log files are not present when you attempt to open the database, then Oracle Database returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.

To open a database, you do

SQL> alter database open;
Database altered.
SQL>

ALTER DATABASE db01 MOUNT;
ALTER DATABASE db01 OPEN READ ONLY;

Note, you can only go from nomount -> mount -> open, can't go other way around.

You can do this also:

SQL>startup mount;

At this point, you can not change it to unmount:

SQL> alter database unmount;

this will give you an error.

SQL>startup RESTRICT

Or you can change it to restricted mode:

SQL>alter system enable restricted session;

To start a listener, do this:

lsnrctl start

sqlplus system/oracle@oracle9ivm

To kick a user out, all the sessions are in the v$session table.

SQL> select saddr, sid, serial#, username from v$session;
SQL> alter system kill session '15,6';

Here 15 is the sid, 6 is the serial#.


Table v$transaction contains the data not committed. If you do some transaction (insert), but do not commit it, you will see the entry in the v$transaction; if you then commit it, you will see it's gone from that table.

conn id/password as anotherid

To run a script

SQL> @$ORACLE_HOME/dbs/crdb01.sql;
SQL> @?/rdbms/admin/catalog.sql

In above, the ? is the same as $ORACLE_HOME.