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

Automatic Undo Management

                                                                UNDO MANAGEMENT

Automatic Undo Management takes the entire issue of sizing and allocation of undo segments from the DBA’s hands and makes it Oracle’s responsibility. Oracle will dynamically create undo (rollback) segments and adjust their number to match the instance’s workload requirements. The database will automatically allocate and deallocate undo segments to match the transaction throughput in the instance.

Setting up Automatic Undo Management

To enable the automatic management of your undo space, you first need to specify the automatic undo mode in the init.ora file or your SPFILE. By default, the database uses AUM in Oracle Database 11g. Second, you need to create a dedicated tablespace to hold the undo information.

If you want to choose AUM when you create a new database, you need to configure the following three initialization parameters:

• undo_management

• undo_tablespace

• undo_retention

we can get this information by issuing the command:

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

undo_management        string      AUTO

undo_retention               integer     900

undo_tablespace              string      UNDOTBS1

The UNDO_MANAGEMENT Parameter:

You specify AUM in your initialization parameter file by adding the following line:

UNDO_MANAGEMENT = auto

The default for undo management in Oracle Database 11g is automatic, which means you can leave out the UNDO_MANAGEMENT initialization parameter if you want.

The UNDO_TABLESPACE Parameter:

The UNDO_TABLESPACE parameter isn’t mandatory—if you only have a single undo tablespace, you don’t have to specify this parameter in your initialization parameter file, because Oracle will use the one available undo tablespace automatically. If you specify AUM and don’t have an undo tablespace in your database at all, Oracle will be forced to use the System tablespace. You should avoid using the System tablespace for storing the undo data, since that tablespace also stores the data dictionary, and you don’t want to use up space there and cause problems such as fragmentation. Note that you can’t create application tables and indexes in an undo tablespace, since it’s exclusively reserved for undo data.

If you have multiple undo tablespaces in your database, however, you must specify which undo tablespace you want the database to use, by specifying the UNDO_TABLESPACE parameter in the initialization parameter file. You can have multiple tablespaces in your database, but only one of them can be active at any given time. You activate an undo tablespace by using

                the ALTER SYSTEM SET UNDO_TABLESPACE command

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

The UNDO_RETENTION Parameter:

When a transaction commits, the undo data for that transaction isn’t needed any more. That undo

data will stay in the undo tablespace, however, until space is needed to record undo data for newer

transactions. When the newer transactions’ undo data comes in, it may overwrite the old undo data

(from the committed transactions) if there isn’t enough free space left in the undo tablespace. For a

long-running query that needs to retain older undo data for consistency purposes, there is a possibility that some of the undo data it needs has been overwritten by other, newer transactions. In this case, you could get an error message (“snapshot too old”) from the database indicating that the before-image of the transaction has been overwritten.

To prevent this, Oracle provides the UNDO_RETENTION configuration parameter, which you can set

to the interval you wish. Note that in the older manual undo management mode, DBAs don’t have the option of determining how long Oracle retains undo information.

Let’s briefly review how undo information is managed in an undo segment. Undo information

can be classified in two broad types:

• If a transaction that has generated the undo data is still active, the undo data is said to be active (uncommitted). Oracle will always retain active undo data to support ongoing uncommitted

transactions.

• If the transaction that generated the undo data is inactive (committed), then the undo data is

said to be committed. The committed undo data can be either expired or unexpired. Expired data can be overwritten by new transactions. Oracle will try to save the unexpired data as long as it can, subject to undo space limitations. When there is no more room in the undo tablespace for newer transactions, Oracle will finally overwrite the unexpired data, depending on how you configure the UNDO_RETENTION parameter.

The V$UNDOSTAT table provides an indicator for helping figure out the undo retention

interval. Query the V$UNDOSTAT view as follows:

SQL> select max(MAXQUERYLEN) from v$undostat;

MAX(MAXQUERYLEN)

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

            1129

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

• UndoSpace is the number of undo blocks
• UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
• UPS is undo blocks for each second
• overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)

As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;

How to create UNDO tablespace:

SQL> CREATE UNDO TABLESPACE undotbs02 DATAFILE '/u02/oradata/mydb/undotbs02.dbf'

SIZE 50M AUTOEXTEND ON;

Tablespace created.

How to add datafile to exisiting undo tablespace:

SQL> alter tablespace UNDOTBS1 add datafile'/u01/app/oracle/oradata/mydb/undo3.dbf' size 50m;

Tablespace altered.

How to drop undo tablespace:

SQL> drop tablespace undotbs02 including contents and datafiles;

Tablespace dropped.

In Automatic Undo Management, the database is in charge of creating, allocating, and deallocating the undo segments as necessary. You can query the DBA_ROLLBACK_SEGS  view to find out which of your undo segments are online, as shown here:

SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME            TABLESPACE_NAME                STATUS

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

SYSTEM                                        SYSTEM                           ONLINE

_SYSSMU10_84651485$         UNDOTBS1                       ONLINE

_SYSSMU9_3163463872$       UNDOTBS1                       ONLINE

_SYSSMU8_625026625$          UNDOTBS1                       ONLINE

_SYSSMU7_2494227896$         UNDOTBS1                       ONLINE

Guaranteed Undo Retention

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

WARNING:

Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE  statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY, where NOT APPLY is used for tablespaces other than the undo tablespace.

How to Manage undo retention:

SQL>SELECT tablespace_name, retention FROM dba_tablespaces;

SQL>ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SQL>select tablespace_name, retention from dba_tablespaces;

SQL>ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

SQL> select TABLESPACE_NAME,RETENTION from dba_tablespaces;

TABLESPACE_NAME      RETENTION

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

SYSTEM                               NOT APPLY

SYSAUX                                NOT APPLY

UNDOTBS1                       NOGUARANTEE

TEMP                                    NOT APPLY

USERS                                   NOT APPLY

EXAMPLE                        NOT APPLY

The following data dictionary views are useful in managing undo space information:

• V$UNDOSTAT: This is the view Oracle uses to tune undo space allocation in the database.

This view can indicate whether your current allocation of space in the undo tablespace is enough. It also indicates whether you have set the UNDO_RETENTION parameter correctly. The TUNED_UNDORETENTION column in the V$UNDOSTAT view tells you the length of time undo is

retained in your undo tablespace.(check maximum query length)  

• DBA_ROLLBACK_SEGS: You can use this view to find out the undo segment name, initial,

next, and maximum extents, and other related information.(check status is online)

• DBA_TABLESPACES: This view will show whether the guaranteed undo retention feature is

enabled for a certain undo tablespace.(check for retention)

• V$TRANSACTION: You can get transaction information from this view.

• V$ROLLSTAT: You can join V$ROLLSTAT and V$ROLLNAME to get a lot of information on the

behavior of the undo segments.

• DBA_UNDO_EXTENTS: This view provides detailed information on the undo extents within

the undo tablespace.