Managing Oracle Database Instance

Managing Oracle Database Instance

Product Release Number

Oracle Database Release Number Format

SELECT * FROM PRODUCT_COMPONENT_VERSION;
SELECT * FROM V$VERSION

1.2.3.4.5
1 Major database release number

2 Maintenance release number

3 application server release number

4 component specific release number

5 platform specific release number

Obtaining License Information

-- Number of users and CPU/Processors
select L.SESSIONS_MAX, L.SESSIONS_WARNING, L.SESSIONS_CURRENT,
L.SESSIONS_HIGHWATER,
.USERS_MAX, L.CPU_COUNT_CURRENT, L.CPU_SOCKET_COUNT_CURRENT,
L.CPU_COUNT_HIGHWATER,
L.CPU_CORE_COUNT_CURRENT, L.CPU_CORE_COUNT_HIGHWATER,
L.CPU_SOCKET_COUNT_HIGHWATER
from v$license l;

-- Database Edition
select banner from v$version where BANNER like '%Edition%';

-- Oracle Partitioning installed
select decode(count(*), 0, 'No', 'Yes')
from dba_part_tables
where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;

-- Oracle Spatial installed:
select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;

-- Oracle RAC installed:
select decode(count(*), 0, 'No', 'Yes')
from v$active_instances where rownum <= 2;

Managing the Instance Architecture

Obtaining Information about the Instance Processes

ps –ef | grep ora

select name, description from V$BGPROCESS order by 1;

Obtaining Information about the SGA

select value from v$parameter where upper(name)='MEMORY_TARGET';
select value/1024/1024 mb
from v$parameter where upper(name) = 'SGA_MAX_SIZE'

-- size taken by a memory component
select pool, round(sum(BYTES)/1024/1024) MB
from V$SGASTAT
group by pool

select name , value/1024/1024 MB
from v$parameter
where upper(name) in (
'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE')

-- 10g
SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0

-- Oracle 11g
SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;
-- To know how Oracle has modified the memory area sizes by time
select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME

Clearing the Buffer Cache

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE ;

Database Administration Authentication

Using Operating System Authentication

Following are the steps to create an Oracle administrator using OS authenticaion. Creating
normal user who is capable of using OS authentication is a bit different. See External (OS)
Authentication.

1. Create an operating system account for the user.
2. Add the account to the OSDBA or OSOPER operating system defined groups.
Unix User Groups: dba, oper
Windows: ORA_DBA, ORA_OPER
3. login to OS as the created user
4. In SQL*Plus
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER

Using Password File Authentication

5. Create the password file
6. orapwd FILE=filename PASSWORD=password ENTRIES=max_users
7. alter system set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE scope=SPFILE;
8. CONN / AS SYSDBA
9. GRANT SYSDBA TO GRANTEDUSER
10. CONN GRANTEDUSER/USERPASSWORD AS SYSDBA

Identifying Users SYSDBA or SYSOPER Users

SELECT * FROM V$PWFILE_USERS

Data Dictionary and Dynamic Performance Views

Data Dictionary Creation

sql.bsq creates base tables
catalog.sql creates data dictionary views
catproc.sql runs scripts required for server-side PL/SQL

 they run as SYSDBA
 they are located in \rdbms\admin

Startup and Shutdown

Startup Levels

STARTUP [FORCE] [RESTRICT] [PFILE=filename] [OPEN [RECOVER] [database] |MOUNT
|NOMOUNT]
ALTER DATABASE { MOUNT | OPEN }
ALTER DATABASE OPEN [READ WRITE| READ ONLY]
ALTER SYSTEM ENABLE | DISABLE RESTRICTED SESSION;

Shutdown Levels

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Autostart of Database in Windows

 As a service or name OracleServiceSID. You can manage the service using ORADIM utility.
 You can control the shutdown type of the database when you shutdown its corresponding
     service by editing the registry entry ORA__SHUTDOWNTYPE in Oracle home entry.

Automatically Starting Databases in Unix

 For Oracle 10.1, refer to Note 222813.1

/* Example 1 */
# compatible with Note 281912.1 (and Note 760051.1)
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y

#(3) Create dbora script in the directory /etc/init.d
# fix ORA_HOME and ORA_OWNER values as required
vi /etc/init.d/dbora

#!/bin/bash
#
# description: Start/Stop the Databases..
#
# chkconfig: 2345 99 10
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid

# Source function library.
. /etc/init.d/functions

RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/11.2.0/db_1"

prog="oracle"

start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora
return $RETVAL
}

stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -r /var/lock/subsys/dbora
return $RETVAL
}

restart() {
stop
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac

exit $?
#(4) as root:
chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora

cd /sbin
chkconfig --add dbora
chkconfig --list | grep ora

# to test, restart or:
/etc/init.d/dbora start

/* Example 2 */
-- have been tested on an Oracle 10g Db on Linux 4
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y

#(3) Create dbora script in the directory /etc/init.d
# fix ORACLE_HOME value as required

#!/bin/sh
#
# /etc/rc.d/init.d/dbora
# Description: Starts and stops the Oracle database and listeners
#
case "$1" in
start)
echo -n "Starting Oracle Databases: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Starting Oracle Databases as part of system up."
>> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Listeners: "
su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle Listeners: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Shutting Down Oracle Databases as part of system
down." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c "lsnrctl stop" >> /var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
echo -n "Shutting Down Oracle Databases: "
su - oracle -c dbshut >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
;;
restart)
echo -n "Restarting Oracle Databases: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Restarting Oracle Databases as part of system up."
>>/var/log/oracle

echo "------------------------------------" >> /var/log/oracle
 - oracle -c dbstop >> /var/log/oracle
su - oracle -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Listeners: "
su - oracle -c "lsnrctl stop" >> /var/log/oracle
su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;

*)
echo "Usage: oracle {start|stop|restart}"
exit

esac

# (4) set file group and permissions
chgrp dba dbora
chmod 750 dbora

# (5) Create the symbolic links: In Linux,
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

/* Example 3 */
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y

#(3) Create dbora script in the directory /etc/init.d
# fix ORACLE_HOME value as required
vi /etc/init.d/dbora

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script
# Source function library.
. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
echo -n "Starting Oracle Listeners:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
echo -n "Stopping Oracle Listeners:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
echo "OK"
;;

*)
echo $"Usage: $0 {start|stop}"
esac

# (4) set file group and permissions
chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora

# (5) Create the symbolic links: In Linux,
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

# (6) configuring execution for specific runlevels
chkconfig --add dbora --level 0356

Quiescing a Database

 Users will remain logged in, and they can continue to execute their requests that are in
progress, while the database is in the quiesced state. The database, however, will block all
new transactional requests except those made by the users SYS and SYSTEM (not DBA
grantees).

ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;

Suspending a Database

 All reads from and writes to the datafiles and control files are suspended.

ALTER SYSTEM SUSPEND;
ALTER SYSTEM RESUME;
SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;

Dropping a Database

 Datafiles, redo log files, and control files are removed automatically. Alert log and
parameter file are not deleted.

CONNECT sys/sys_passwd AS SYSDBA
SHUTDOW IMMEDIATE
STARTUP RESTRICT MOUNT
SELECT name FROM v$database;
DROP DATABASE;

Initialization Files

Managing Initialization Files

 During startup, in $ORACLE_HOME/dbs (for UNIX/Linux) Oracle will look for the correct
initialization file to use, in the following order:

o spfile$ORACLE_SID.ora
o spfile.ora
o init$ORACLE_SID.ora

# currently used SPFiles (if null, pfile was used)
select * from v$parameter where name = 'spfile'
# create pfile
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM
PFILE='/u01/oracle/dbs/test_init.ora'
# use pecific spfile during startup
STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora
# PFILE can indicate to use SPFILE
SPFILE = /database/startup/spfileDBA1.ora

In Oracle 11g

# write current values of instance parameters
CREATE PFILE FROM MEMORY;
CREATE SPFILE FROM MEMORY;

Managing Parameters in SPFILE

-- display current value of a parameter
select * from v$parameter where name = 'spfile'
-- parameter values set in SPFILE
select NAME, ISSPECIFIED from V$SPPARAMETER where name like '%dest%';
-- in SQL Plus
show parameter target

-- set parameter value
alter system set parameter=value scope=SPFILE|MEMORY|BOTH

-- delete a parameter from SPFILe
ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='node01';

-- Only sessions started after the statement is executed are affected
-- this option is a must for the parameters whose ISSYS_MODIFIABLE column
-- in V$PARAMETER is DEFERRED. You cannot use it, if the column value is
FALSE
ALTER SYSTEM SET parameter_name DEFERRED;

-- undocumented parameters

SELECT
a.ksppinm parameter, a.ksppdesc description,
b.ksppstvl session_value, c.ksppstvl instance_value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE
a.indx = b.indx
AND a.indx = c.indx
AND SUBSTR (a.ksppinm,1,1) = '_'
ORDER BY a.ksppinm;

Alert and Trace Files

Monitoring Alert and Trace Files

# to obtain the Location of Trace Files
column NAME format a35
column VALUE format a35
select name, value from v$parameter
where name in ('background_dump_dest','user_dump_dest')

# to set maximum size of trace files (excluding the alert file)
# in number of block unless you sepcify K or M
select * from v$parameter where upper(name )= 'MAX_DUMP_FILE_SIZE'
alter session set MAX_DUMP_FILE_SIZE='100M'