DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.

com

Managing Oracle Database Instance

Agenda:-

1. Startup and Shutdown of Oracle databases

2. SPFILE ,PFILE and parameters in oracle database

3. Alert Log, Trace Files and ADR

4. Data Dictionary and Dynamic Performance View

5. Password File

1. Startup and Shutdown of Oracle Databases

Startup Modes:-

A. startup nomount - oracle reads either spfile or pfile and allocates memory.

It will read spfile from $ORACLE_HOME/dbs , if spfile is not found then it will read pfile.

Transactions not possible.

B. startup mount - oracle reads either spfile or pfile, allocates memory and then it reads
controlfiles.

Transactions not possible.

Alter database mount;

C. startup - oracle reads either spfile or pfile , allocates memory , reads controlfiles and then
open data files and redo log files.

Transactions possible.

Alter database open;

Shutdown Modes:-

A. shutdown normal - it will wait for transaction to complete and wait for all session to
disconnect from database, then it will perform shutdown normal.

No-one prefer in production environment.

DBA Trainer - Online Oracle DBA Training Institute Page 1

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

it will not allow new connections.

B. shutdown transactional - it will wait for transaction to complete but will not wait for session
to disconnect and then it will perform shutdown trsansactional.

it will not allow new connections.

C. shutdown immediate - it will not wait for transaction to complete - it rollback the
transactions which are pending and also it will not wait for session to disconnect , and it
perform shutdown immediate.

it will not allow new connections.

D. shutdown abort - it will not wait for transaction to complete - it will leave transactions as it is
, and also it will not wait for session to disconnect , and it perform shutdown abort.

Next time when you start database , then instance recover perform by Oracle SMON
background process.

it will not allow new connections.

When power failure or because of any other reason , oracle database server down then
internally shutdown abort occurred.

Practical:-

1.SQL>Startup nomount

SQL>alter database mount;

SQL>alter database open read only;

SQL>shutdown immediate;

DBA Trainer - Online Oracle DBA Training Institute Page 2

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

A.SQL>startup mount;

SQL>alter database open;

or

B.SQL>startup

2. SPFILE and PFILE - parameter files.

SPFILE - parameter file so it stores oracle instance / database parameters.

it is binary files mean you can't edit it directly with vi editor or notepad.if you want to edit it ,
you can edit it with alter system command.

When you start your database , oracle read first SPFILE file.

location - $ORACLE_HOME/dbs

$ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1

$ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1/dbs/

Name convention - spfile[instancename].ora

By Default , it is created when you create database with DBCA.

PFILE - parameter file so it stores oracle instance / database parameters.

it is text base file , you can edit it directly with vi editor or notepad.

When you start your database , oracle read first SPFILE but if SPFILE is not found , then oracle
read PFILE.

If SPFILE or PFILE not available , then you can't start your database i.e. you can't read upto
nomount mode.

IF you wants to create pfile , then you can create it with spfile.

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

Parameters:-

*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'

DBA Trainer - Online Oracle DBA Training Institute Page 3

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/
flash_recovery_area/ORCL/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ORCL'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

*.memory_target=420478976

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

How to check DB is running with SPFILE or PFILE ?

SQL>show parameter spfile

under value column , if it return not null value then your db is running with SPFILE.

under value column , if it return null value then your db is running with PFILE

How to start Database with PFILE ?

SQL>startup pfile='/u01/pfile.ora'

How to modify parameter in SPFILE ?

SQL>alter system set [parametername]=[value] scope=[both| memory | spfile];

scope=spfile - it will change value in spfile but it will effect only after restart of your database.
DBA Trainer - Online Oracle DBA Training Institute Page 4
DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

scope=memory - it will change value in spfile immediately and effect immediately but it comes
original value after restart of database.this is temporary change.

scope=both - it will change value in spfile immediately and also effect immediately and it will
keep same value even after restart of database.

How to create SPFILE from PFILE ?

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

Why we created PFILE from SPFILE ?

PFILE - static file - if you are doing any change in parameter , it always require instance restart
to take effect.

SPFILE - dynamic file - if you are doing any change in parameter , it may / may not be require
instance restart to take effect.

Task - kindly change 10 parameter values as requested ? - your db is running SPFILE .

Two options:-

1. use ALTER SYSTEM command - 10 times as you have 10 parameter value needs to change ( it
doesn't require down time )

SQL>alter system set open_cursors=500 scope=BOTH;

SQL>alter system set memory_target=500M scope=BOTH;

or

2. CREATE PFILE FROM SPFILE and modify those 10 parameters.( it require down time )

If You are going 2 option , then you need to follow below steps:-

A. CREATE PFILE='/u01/pfile.ora' from spfile.

B. Open PFILE , modify PFILE with vi editor.

C. shutdown database

C. Startup database with PFILE

D. CREATE SPFILE from PFILE

E. shutdown immediate

DBA Trainer - Online Oracle DBA Training Institute Page 5

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

F. startup database with SPFILE

Why we create SPFILE from PFILE ?

As , DB can run with PFILE but if you needs to change any parameter and take into effect , it
always require db to be restart. and that is not possible in production environment where down
time is not possible.

So better to keep db running with SPFILE , so any changes in parameter can taken into effect
immediately.

3. ADR :-

ADR is stands for Automatic Diagnostic Repository. New in Oracle Database 11g.

It stores information about database as below:-

1. DB Health Monitoring Reports.

2. Any kind issue with block corruption

3. it stores information about ORA-600 errors - internal error. - CDUMP

4. Alter Log and Trace Files.

5. Incident Information.

Before 11g , below parameter are separate :-

background_dump_destination - stores alert log file and trace files

core_dump_destination - stores core oracle errors like ORA-600 or any internal error.

ADR Information can be find:-

SQL>desc v$diag_info;

ADR_BASE=/u01/app/oracle

ADR_HOME=/u01/app/oracle/diag/rdbms/orcl/ORCL

Alter Log File and Trace File.

When you doing any kind of activity in Oracle database like startup , shutdown , converting db
from no-archive to archive , any kind of ORA- error recorded in Alert Log File.

As a DBA , you needs always gone through alert log file in case of any issue in database.

DBA Trainer - Online Oracle DBA Training Institute Page 6

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

Location trace / alert file:-

oracle provides alert file in .log or .xml

.log - $ADR_HOME/trace

.xml - $ADR_HOME/alert

SQL>show parameter background_dump_dest

Trace File:-

Trace file gives you more details information about any errors which are in alert log files.

Also for each background processes , oracle generates its own trace files and maintaining
activity done by the background processes.

Periodically you can delete old traces files which you don't require like you can keep only 30
days trace files or 60 days trace file based on your requirements.

Also , you can delete alert file and oracle automatically create new alert log file or you can
rename alert file log , then also oracle create automatically new alert log file.this activity not
going to be impacted to database.

4. Data Dictionary and Dynamic Performance View:-

Data Dictionary:-

It stores static information about database like no. of tablespaces , no. of users , no. of data files
, privileges information , table information , sequence info , index information , procedure
information , triggers.

All Data Dictionary information stores under SYSTEM tablespace.

There are 3 category of data dictionary:-

DBA_ - DBA can use this data dictionary view.it will give you all the information regarding data
dictionary.

DBA_TABLES , USER_TABLES , ALL_TABLES

USER_- Anyone can use this but it only shows information which owner owns it.

ALL_ - Anyone can use this but it shows information which owner owns it as well owner has
access / privileges on any db objects.

DBA Trainer - Online Oracle DBA Training Institute Page 7

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

User TEST:-

EMP , SCOTT.DEPT

select * from user_tables;

EMP

select * from user_Tables;

EMP

select * from all_tables;

EMP

SCOTT.DEPT

DBA_TABLES / USER_TABLES / ALL_TABLES

DBA_DATA_FILES

DBA_TABLESPACES

DBA_USERS / USER_USERS / ALL_USERS

DBA_INDEXES

DBA_PROFILES

DBA_TABLES / ALL_TABLES / USER_TABLES

Dynamic Performance View:-

It stores dynamic information about oracle databases like no. of session , no . of process ,
locking information , blocking session information , dead lock information , memory
information-SGA,PGA , instance information , db status information, tablespace information ,
data files information

v$database

v$instance

v$locks

DBA Trainer - Online Oracle DBA Training Institute Page 8

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

v$session

v$processes

v$tablespaces

v$datafiles

v$sql

v$sgainfo

5. Password File:-

Oracle password file stores passwords for users with administrative privileges.

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate
the DBA.

DBA password cannot be stored in the database, because Oracle cannot access the database
before the instance is started up. Therefore, the authentication of the DBA must happen
outside of the database.

There are two distinct mechanisms to authenticate the DBA:-

(i) Using the password file or
(ii) Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.

The default location for the password file is:-

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix,

REMOTE_LOGIN_PASSWORDFILE parameter

The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to

authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will
be used.

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be

changed without bouncing the database.

NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed
over non secure connections. If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or
SHARED and the password file is missing, this is equivalent to setting
REMOTE_LOGIN_PASSWORDFILE to NONE.

DBA Trainer - Online Oracle DBA Training Institute Page 9

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

EXCLUSIVE (default) - Password file is exclusively used by only one (instance of the) database.
Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE
password file enables you to add, modify, and delete users. It also enables you to change the
SYS password with the ALTER USER command.

SHARED - The password file is shared among databases. A SHARED password file can be used by
multiple databases running on the same server, or multiple instances of an Oracle Real
Application Clusters (RAC) database. However, the only user that can be added / authenticated
is SYS.

A SHARED password file cannot be modified i.e. you cannot add users to a SHARED password
file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or
SYSOPER or SYSASM (this is from Oracle 11g), SYSKM, SYSDG and SYSBACKUP (these 3 are
from Oracle 12c R1) privileges generates an error. All users needing SYSDBA, SYSOPER, SYSASM,
SYSKM, SYSDG and SYSBACKUP system privileges must be added to the password file when
REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change
REMOTE_LOGIN_PASSWORDFILE to SHARED.

ORAPWD

You can create a password file using orapwd utility. For some Operating systems, you can
create this file as part of standard installation.

Users are added to the password file when they are granted the SYSDBA, SYSOPER and
SYSASMprivilege.

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N]

[ignorecase=Y|N] [nosysdba=Y|N]

Examples:-

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle
$ orapwd file=orapwprod entries=32 force=y
$ orapwd file=orapwPRODB3 password=abc123 entries=12 ignorecase=n
$ orapwd file=orapworcl1 password=oracle1 ignorecase=y

orapwd file=orapwORCL password=oracle ignorecase=y format=12

Granting SYSDBA, SYSOPER and SYSASM privileges

SQL> select * from v$pwfile_users;

DBA Trainer - Online Oracle DBA Training Institute Page 10

DBA Trainer Oracle DBA - Managing Oracle DB Instance www.dbatrainer.com

If orapwd has not yet been executed or password file is not available, attempting to grant
SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP privileges will result in the following
error:

SQL> grant sysdba to scott; -- error

DBA Trainer - Online Oracle DBA Training Institute Page 11