Basic Oracle

                                Basic Commands and startup and Shutdown Modes

After the db installation, you can check the oracle is running or not:

Os command:

ps -ef | grep pmon  or ps -ef | grep smon

After connecting database,

sql> select open_mode,name from v$database; 

open_mode should be "OPEN" status:

In windows you have to check this by going to servers.msc oracleserveicedbname is running or not? and the connect to sqlplus and type below command:

sql> select open_mode,name from v$database;

 open_mode should be "OPEN" status:

after the installation by running the scripts root.sh in linux in "/etc/oratab" dbname entry will be added:

[root@standalone /]# cat /etc/oratab

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by the Database Configuration Assistant when creating

# a database.

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

    mydb:/u01/app/oracle/product/11.2.0/db_1:N

To See the Oracle Home location

-bash-3.2$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/db_1

Procedure For connecting the database:

-bash-3.2$ . oraenv

ORACLE_SID = [oracle] ? mydb

The Oracle base has been set to /u01/app/oracle

-bash-3.2$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/db_1

bash-3.2$ echo $ORACLE_SID

mydb

-bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 2 08:11:58 2014

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

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

****************************************************************

Important Locations of the Database:

Oracle_Home	/u01/app/oracle/product/11.2.0/db_1                         (linux,unix) C:\app\ oracle\product\11.2.0\db_1                             (windows)
Oracle_Base	/u01/app/oracle                                                                (linux,unix) C:\app\ oracle                                                                    (windows)
Listener TnsNames	/u01/app/oracle/product/11.2.0/db_1/network/admin  (linux,unix) C:\app\ oracle\product\11.2.0\db_1\dbs\ network\admin   (windows)
Pfile(parameter file) Spfile(serverParameter file) Pwdfile(password file)	/u01/app/oracle/product/11.2.0/db_1/dbs                 (linux,unix) C:\app\ oracle\product\11.2.0\db_1\dbs                    (windows)
Alert log file Trace file	/u01/app/oracle/diag/rdbms/mydb/MYDB/trace     (linux,unix) C:\u01\app\oracle\diag\rdbms\mydb\MYDB\trace  (windows)

All the configuration of the database is kept in the initilization parameter file(parameter) i.e. initmydb.ora or server parameter file i.e spfilemydb.ora

location of the init.ora or spfile will be $ORACLE_HOME/dbs/ directory in unix

bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

bash-3.2$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

bash-3.2$ ls -ltr

total 24

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-rw---- 1 oracle oinstall 1544 Jan  1 11:45 hc_DBUA0.dat

-rw-rw---- 1 oracle oinstall 1544 Jan  1 11:46 hc_mydb.dat

-rw-r----- 1 oracle oinstall   24 Jan  1 11:51 lkMYDB

-rw-r----- 1 oracle oinstall 1536 Jan  1 11:58 orapwmydb

-rw-r----- 1 oracle oinstall 2560 Jan  2 08:06 spfilemydb.ora    /**** spfile format: spfile(databasename).ora*****

-rw-r--r-- 1 oracle oinstall  908 Jan  2 08:34 initmydb.ora      /***** pfile format: init(databasename).ora*********

Difference Between Pfile and SPfile:

·         Pfile is a parameter file.it is in human readable mode. we can able to see

Ø  cat initmydb.ora

In Pfile,any changes can be applied after restart the server

·         SPfile is a server parameter file it is in binary format. we are unable to read spfile. if we want to see the contents of spfile we need to create pfile. In SPfile, any changes can be applied with out restarting the server  

other location to file init.ora file in unix is

bash-3.2$ cd /u01/app/oracle/admin/mydb/pfile/

bash-3.2$ ls -ltr

total 4

-rw-r----- 1 oracle oinstall 1787 Jan  1 11:46 init.ora.012014115658

location of the init.ora or spfile will be $ORACLE_HOME/database/ directory in windows.

other location to find pfile is oraclebase\admin\dbname\pfile i.e : C:\app\prashanth\admin\mydb\pfile

Password file location:

windows: C:\app\prashanth\product\11.2.0\db_1\database\PWDmydb.ora          
password file name format: PWD(databasename).ora

Linux: /u01/app/oracle/product/11.2.0/db_1/dbs/orapwmydb                   
password filename format: orapw(databasename)

if you lost the password file you can generate new password file using "orapwd" utility in both unix and linux.

C:\Users\prashanth>orapwd

Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  where

    file - name of password file (required),

    password - password for SYS will be prompted if not specified at command line,

    entries - maximum number of distinct DBA (optional),

    force - whether to overwrite existing file (optional),

    ignorecase - passwords are case-insensitive (optional),

    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).

HOW TO CHECK DB IS RUNNING WITH PFILE OR SPFILE:

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/spfilemydb.ora

By default oracle start with spfile. if spfile is not present it will check for pfile and it will start with pfile:

show to start the database using pfile:

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initmydb.ora'

ORACLE instance started.

Total System Global Area  709836800 bytes

Fixed Size                  2229880 bytes

Variable Size             461375880 bytes

Database Buffers          243269632 bytes

Redo Buffers                2961408 bytes

Database mounted.

Database opened.

SQL> show parameter spfile  /*** here the value is blank so this is not started with spfile ****/

NAME                                 TYPE        VALUE

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

spfile                               string

SQL>

How to create pfile to a specific location:

SQL> create pfile='/u01/app/oracle/initbackup.ora' from spfile;

File created.

HOW TO create spfile from pfile:

SQL> create spfile from pfile='/u01/app/oracle/initbackup.ora';

File created.

SQL> create spfile from pfile;   /*** here the pfile locatoin will be default location: i.e /u01/app/oracle/product/11.2.0/db_1/dbs/initmydb.ora  ***/

Based on os version the commands are going to change:

In unix servers,  we have to create oracle user and dba, oinstall groups and we have to add the oracle user to dba groups.

but in windows no need to create oracle user and dba groups, it's going to create automatically during the instalation time.

to check the dba group location in windows

In windows,  you are going to see the oracle related services in services.msc and the oracle instance is going to register in the registry,

but these services won't be persent in unix servers.

The person who belongs to dba groups becomes super user and he can start and stop the oracle databases so we should not add any other users apart from oracle users:

computer management-->local users groups--> groups.

***********************************************************************************

                                                STARTUP AND SHUTDOWN MODES

STARUP MODES:

1.startup nomount: oracle instace will be started using pfile or spfile. db is not accessable during this period, during the control file creation will go for this option and for some recovery options.

When you issue the startup command, the first thing the database will do is enter the nomount stage. During the nomount stage, Oracle first opens and reads the

initialization parameter file (init.ora) to see how the database is configured. For example, the sizes of all of the memory areas in Oracle are defined within the parameter file.

After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started.

Together, we call these processes and the associated allocated memory the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage.

If you issue the startup command, then Oracle will automatically move onto the next stage of the startup, the mount stage.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  709836800 bytes

Fixed Size                  2229880 bytes

Variable Size             461375880 bytes

Database Buffers          243269632 bytes

Redo Buffers                2961408 bytes

2.startup mount: oracle instance will be started using spfile or pfile and the database will read the control file to check the location of the datafile files.

we will open this mode in recovery time or to enable archivelog mode. It determines the location of the datafiles and the database is ready to be opened.

When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information,

 such as the location of the database datafiles.

In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified,

 the database is ready to be opened.

SQL> startup mount

ORACLE instance started.

Total System Global Area  709836800 bytes

Fixed Size                  2229880 bytes

Variable Size             461375880 bytes

Database Buffers          243269632 bytes

Redo Buffers                2961408 bytes

Database mounted.

3.startup : When DB is in this stage, it opens the database, it tries to access all of the datafiles associated with the database.

Once it has accessed the database datafiles, it makes sure that all of the database datafiles are consistent.

The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles,

Oracle makes sure that all of the database datafiles are consistent.

SQL> startup

ORACLE instance started.

Total System Global Area  709836800 bytes

Fixed Size                  2229880 bytes

Variable Size             461375880 bytes

Database Buffers          243269632 bytes

Redo Buffers                2961408 bytes

Database mounted.

Database opened.

Opening the Database in Restricted Mode:

Opening the Database in Restricted Mode

You can also start the database in restricted mode. Restricted mode will only allow users with special privileges to access the database (typically DBA’s), even though the database is technically open.

We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict

You can take the database in and out of restricted mode with the alter database command as seen in this example:

-- Put the database in restricted session mode.

SQL> alter system enable restricted session;

-- Take the database out of restricted session mode.

SQL> alter system disable restricted session;

To open the database in nomount stage:

 steps:

SQL> startup nomount

ORACLE instance started.

Total System Global Area  709836800 bytes

Fixed Size                  2229880 bytes

Variable Size             461375880 bytes

Database Buffers          243269632 bytes

Redo Buffers                2961408 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

ORACLE SHUTDOWN MODES:

1.SHUTDOWN :

This is because the normal shutdown waits for everyone to complete their work and then logoff in an orderly fashion.

When a normal shutdown occurs, the database is closed in a normal manner, and all changes made in the database are flushed to the database datafiles.

 This is known as a “clean shutdown”.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SHUTDOWN IMMEDIATE:

This command will prevent any new logins, then rollback any uncommitted transactions, and then bring down the database.

In the process of bringing down the database, Oracle will flush all the changes in memory out to the database datafiles too,  just like a regular shutdown does.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SHUTDOWN ABORT: It’s a “hard crash” of the database, and this can result in a longer time to start the database back up. This is going to do the instance recovery

during the start time.

SQL> shutdown abort

ORACLE instance shut down.

**********************************************************************************

                                                Alert Log File

To See the Alert log file:

SQL> show parameter dump

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/mydb/mydb/trace  /**** alert log file and trace files locations ******/    

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/mydb/mydb/cdump /***** memory related trace files will generate here *****/

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/mydb/mydb/trace   /**** to see user generated trace files *****/

audit_file_dest                      string      /u01/app/oracle/admin/mydb/adump             **** to store the audit relted trace files ******

How to find alert log location from db:

alert log location init.ora parameter is background_dump_dest

SQL> show parameter back

NAME                                 TYPE        VALUE

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

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/mydb/mydb/trace   /*** this location contains oracle related trace files and alert log file ****/

SQL> select value from v$diag_info where name='Diag Trace';

VALUE

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

/u01/app/oracle/diag/rdbms/mydb/mydb/trace

when the database is down how to check the alert log location:

$ORACLE_BASE/diag/rdbms/mydb/mydb/trace

Linux/Unix

[oracle@oracloud3 /]$ find / -name alert_dbname.log 2>/dev/null

-bash-3.2$ ls -ltr aler*

-rw-r----- 1 oracle oinstall 98461 Jan  3 07:23 alert_mydb.log       /**** alert log format: alert_dbname.log *****/

Information Recorded in Alertlogfile:

In alert log it caputers

oracle instace startup/shutdown steps,

oracle realted errors like ora-600, ora-3136,

datapump(export/import) jobs information,

tablespace add, alter infromation and trace files location information.

in oracle 10g the alert log location : bdump

$ORACLE_HOME/admin/$ORACLE_SID/bdump 

to check alert log information currently:

-bash-3.2$ tail -f alert_mydb.log

vi alert_mydb.log ///*** to view the entire alertlog ****/

How to check the oracle version:

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production