ORACLE DATABASE ADMINISTRATION
Module 3: Configuring An Efficient
Environment
ORACLE DATABASE ADMINISTRATION SERIES
S.N STEPHEN N NJOROGE
LinkedIn: www.linkedln.com/in/stephen-njoroge
SENIOR DATABSE ADMINISTRATOR | MSSQL | ORACLE| PostgreSQL |OCI |AWS |
DATABASE ADMINISTRATION SERIES |
Contents
............................................................................................................................................................................ 1
Module 3: Configuring an Efficient Environment ............................................................................................... 3
Customizing OS Command Prompt. ................................................................................................................... 4
# Hostname ................................................................................................................................................... 4
# Oracle SID .................................................................................................................................................. 4
# Working Directory ..................................................................................................................................... 4
# Color-Coded Prompt .................................................................................................................................. 5
# Root User ................................................................................................................................................... 5
# Regular User .............................................................................................................................................. 5
# Dev Environment ....................................................................................................................................... 5
# Prod Environmnet. .................................................................................................................................... 5
Customizing SQL Prompt .................................................................................................................................... 6
# Show User .................................................................................................................................................. 6
# Show database Name ................................................................................................................................. 6
Using Aliases ........................................................................................................................................................ 6
Common Oracle Admin Aliases .................................................................................................................... 6
# Oracle Environment Info ........................................................................................................................... 6
# Connect to DB ............................................................................................................................................ 7
# Shutdown the database.............................................................................................................................. 7
# Start the database ...................................................................................................................................... 7
# Alert log view ............................................................................................................................................. 8
# Listener control .......................................................................................................................................... 8
# RMAN access ............................................................................................................................................. 9
# List running processes ............................................................................................................................... 9
# Export and Import (Data Pump) ............................................................................................................... 9
# Show tnsnames and listener config ........................................................................................................... 9
# Quick Oracle base check ............................................................................................................................ 9
# Go to diagnostic trace directory .............................................................................................................. 10
shell functions: ................................................................................................................................................... 10
# Show datafiles .......................................................................................................................................... 10
# Show tempfiles ......................................................................................................................................... 10
# Show redo log files .................................................................................................................................... 11
# Show control files...................................................................................................................................... 11
# Show SPFILE or PFILE location .............................................................................................................. 11
# Show all initialization parameters (sorted) ..............................................................................................12
# Show memory-related parameters ...........................................................................................................12
Module 3: Configuring an Efficient Environment
Shell scripts. ........................................................................................................................................................ 13
# Startup Script ............................................................................................................................................ 13
# Shutdown Script ....................................................................................................................................... 13
# Check Tablespace Usage ...........................................................................................................................14
# List Active Sessions ...................................................................................................................................14
# Check Archive Log Usage.......................................................................................................................... 15
# RMAN Full Backup Script ........................................................................................................................16
# Monitor Alert Log for Errors ....................................................................................................................16
# Check Listener Status ..................................................................................................................................16
Sql Scrips ............................................................................................................................................................. 17
# Check Database Status.............................................................................................................................. 17
# List All Tablespaces and Their Usage ....................................................................................................... 17
# Show All Datafiles .................................................................................................................................... 18
# Check Tempfile Usage ............................................................................................................................. 18
# List Control Files ...................................................................................................................................... 18
# List Redo Log Files....................................................................................................................................19
# Check Archive Log Status .........................................................................................................................19
# List Users and Status ................................................................................................................................19
# Current Active Sessions ........................................................................................................................... 20
# Long Running Queries ............................................................................................................................. 20
# Blocking Sessions ..................................................................................................................................... 20
# Show Init Parameters (Alphabetically) ...................................................................................................21
# Check FRA (Fast Recovery Area) Usage ...................................................................................................21
Module 3: Configuring an Efficient Environment
After installation of Oracle binaries and creation of a database, we will configure our environment to enable us to
operate efficiently. This module lays the foundation for efficient use of the OS and SQL to manage databases. The
mostly used OS and database features to configure for effectiveness are:
OS variables
Shell aliases
Shell functions
Shell scripts
SQL scripts
Module 3: Configuring an Efficient Environment
Customizing OS Command Prompt.
You can run the following types of commands to identify your current working environment:
# Hostname
$ hostname
$ id
$ who am i
$ echo $ORACLE_SID
$ pwd
# Oracle SID
Configure command prompt to display information regarding its environment, such as the machine name and database
SID.
$ PS1='[\h:\u:${ORACLE_SID}]$ '
If you want the OS prompt automatically configured when you log in, then you need to set it in a startup file
PS1='[\h:\u:${ORACLE_SID}]$ '
# Working Directory
Display the current working directory information.
$ PS1='[\h:\u:\w:${ORACLE_SID}]$ '
Include the ORACLE_SID variable within the string.
$ export PS1=['hostname'':"${ORACLE_SID}"]$ '
Module 3: Configuring an Efficient Environment
# Color-Coded Prompt
PS1="\[\e[1;34m\]\u@\h\[\e[0m\]:\[\e[1;32m\]\w\[\e[0m\]\$ "
# Root User
For root, use a red prompt to signal danger:
PS1="\[\e[1;31m\]\u@\h \w #\[\e[0m\] "
# Regular User
For regular users, keep it green:
PS1="\[\e[1;32m\]\u@\h \w \$\[\e[0m\] "
# Dev Environment
Dev:
ENV="(DEV)"
PS1="\[\e[1;36m\]$ENV \u@\h:\w\$ \[\e[0m\]"
# Prod Environmnet.
Prod:
ENV="(PROD)"
PS1="\[\e[1;36m\]$ENV \u@\h:\w\$ \[\e[0m\]"
Module 3: Configuring an Efficient Environment
Customizing SQL Prompt
When connected to a database, you can run the following commands to verify information such as your username,
database connection and hostname:
# Show User
SQL> show user;
# Show database Name
SQL> select name from v$database;
Set SQL prompt to display the above.
SQL> SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
Using Aliases
Common Oracle Admin Aliases
# Oracle Environment Info
alias oh='echo $ORACLE_HOME'
Module 3: Configuring an Efficient Environment
alias osid='echo $ORACLE_SID'
alias oenv='env | grep ORACLE'
# Connect to DB
alias sysdba='sqlplus / as sysdba'
# Shutdown the database
alias dbshut='sqlplus / as sysdba @$HOME/shut.sql'
# Start the database
alias dbstart='sqlplus / as sysdba @$HOME/start.sql'
Module 3: Configuring an Efficient Environment
# Alert log view
alias alertlog='tail -f
/$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
# Listener control
alias lsnrstart='lsnrctl start'
alias lsnrstop='lsnrctl stop'
alias lsnrstatus='lsnrctl status'
Module 3: Configuring an Efficient Environment
# RMAN access
alias rmancon='rman target /'
# List running processes
alias psora='ps -ef | grep ora_ | grep -v grep'
# Export and Import (Data Pump)
alias expdp="expdp \'/ as sysdba\'"
alias impdp="impdp \'/ as sysdba\'"
# Show tnsnames and listener config
alias tns='cat $ORACLE_HOME/network/admin/tnsnames.ora'
alias lsnr='cat $ORACLE_HOME/network/admin/listener.ora'
# Quick Oracle base check
alias obase='echo $ORACLE_BASE'
Module 3: Configuring an Efficient Environment
# Go to diagnostic trace directory
alias gotrace='cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace'
shell functions:
# Show datafiles
show_datafiles() {
sqlplus -s / as sysdba <<EOF
SET LINES 200
COL file_name FOR A80
SELECT file_id, file_name FROM dba_data_files;
EXIT
EOF
}
# Show tempfiles
show_tempfiles() {
sqlplus -s / as sysdba <<EOF
SET LINES 200
COL file_name FOR A80
SELECT file_id, file_name FROM dba_temp_files;
EXIT
EOF
}
Module 3: Configuring an Efficient Environment
# Show redo log files
show_redologs() {
sqlplus -s / as sysdba <<EOF
SET LINES 200
COL member FOR A80
SELECT group#, member FROM v\$logfile ORDER BY group#;
EXIT
EOF
}
# Show control files
show_controlfiles() {
sqlplus -s / as sysdba <<EOF
SET LINES 200
COL name FOR A80
SELECT name FROM v\$controlfile;
EXIT
EOF
}
# Show SPFILE or PFILE location
show_spfile() {
sqlplus -s / as sysdba <<EOF
SET LINES 200
SHOW parameter spfile;
EXIT
EOF
}
Module 3: Configuring an Efficient Environment
# Show all initialization parameters (sorted)
show_parameters() {
sqlplus -s / as sysdba <<EOF
SET PAGESIZE 100
SET LINES 200
COL name FOR A40
COL value FOR A80
SELECT name, value FROM v\$parameter ORDER BY name;
EXIT
EOF
}
# Show memory-related parameters
show_memory_parameters() {
sqlplus -s / as sysdba <<EOF
SET PAGESIZE 100
SET LINES 200
COL name FOR A40
COL value FOR A80
SELECT name, value FROM v\$parameter WHERE name LIKE '%memory%' ORDER BY name;
EXIT
EOF
}
Module 3: Configuring an Efficient Environment
Shell scripts.
# Startup Script
#!/bin/bash
export ORACLE_SID=dev
export ORAENV_ASK=NO
. oraenv
sqlplus / as sysdba <<EOF
STARTUP;
EXIT;
EOF
# Shutdown Script
#!/bin/bash
export ORACLE_SID=dev
export ORAENV_ASK=NO
. oraenv
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
Module 3: Configuring an Efficient Environment
# Check Tablespace Usage
#!/bin/bash
export ORACLE_SID=dev
export ORAENV_ASK=NO
. oraenv
sqlplus -s / as sysdba <<EOF
SET LINES 200
COLUMN tablespace_name FORMAT A20
COLUMN used_mb FORMAT 999999
COLUMN free_mb FORMAT 999999
SELECT
df.tablespace_name,
df.total_space_mb,
fs.free_space_mb,
(df.total_space_mb - fs.free_space_mb) AS used_mb,
ROUND((df.total_space_mb - fs.free_space_mb) / df.total_space_mb * 100, 2) AS pct_used
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_space_mb FROM dba_data_files GROUP BY
tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_space_mb FROM dba_free_space GROUP BY
tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;
EXIT;
EOF
# List Active Sessions
#!/bin/bash
export ORACLE_SID=dev
Module 3: Configuring an Efficient Environment
export ORAENV_ASK=NO
. oraenv
sqlplus -s / as sysdba <<EOF
SET LINESIZE 200
COL username FORMAT A20
COL status FORMAT A10
SELECT sid, serial#, username, status, osuser, machine FROM v\$session WHERE username IS NOT
NULL ORDER BY status;
EXIT;
EOF
# Check Archive Log Usage
#!/bin/bash
export ORACLE_SID=dev
export ORAENV_ASK=NO
. oraenv
sqlplus -s / as sysdba <<EOF
SET LINESIZE 200
COL DEST_NAME FORMAT A25
COL STATUS FORMAT A10
COL SPACE_LIMIT FORMAT 999,999,999
COL SPACE_USED FORMAT 999,999,999
COL PCT_USED FORMAT 999.99
SELECT DEST_NAME, STATUS, SPACE_LIMIT/1024/1024 AS LIMIT_MB,
SPACE_USED/1024/1024 AS USED_MB,
ROUND((SPACE_USED/SPACE_LIMIT)*100,2) AS PCT_USED
FROM V\$RECOVERY_FILE_DEST;
EXIT;
EOF
Module 3: Configuring an Efficient Environment
# RMAN Full Backup Script
#!/bin/bash
export ORACLE_SID=dev
export ORAENV_ASK=NO
. oraenv
rman target / <<EOF
RUN {
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE NOPROMPT OBSOLETE;
}
EOF
# Monitor Alert Log for Errors
#!/bin/bash
export ORACLE_SID=dev
export ORAENV_ASK=NO
. oraenv
ALERT_LOG=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log
tail -n 100 $ALERT_LOG | egrep -i "ORA-|error"
# Check Listener Status
#!/bin/bash
lsnrctl status
Module 3: Configuring an Efficient Environment
Sql Scrips
# Check Database Status
SELECT status, instance_name, host_name, version
FROM v$instance;
# List All Tablespaces and Their Usage
SELECT
df.tablespace_name,
ROUND(df.total_mb, 2) AS total_mb,
ROUND(fs.free_mb, 2) AS free_mb,
Module 3: Configuring an Efficient Environment
ROUND(df.total_mb - fs.free_mb, 2) AS used_mb,
ROUND((df.total_mb - fs.free_mb)/df.total_mb * 100, 2) AS pct_used
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;
# Show All Datafiles
SELECT file_id, file_name, tablespace_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
ORDER BY tablespace_name, file_id;
# Check Tempfile Usage
SELECT tablespace_name, file_id, file_name, bytes/1024/1024 AS size_mb
FROM dba_temp_files;
# List Control Files
SELECT name FROM v$controlfile;
Module 3: Configuring an Efficient Environment
# List Redo Log Files
SELECT group#, thread#, sequence#, status, bytes/1024/1024 AS size_mb, member
FROM v$logfile lf
JOIN v$log l ON lf.group# = l.group#
ORDER BY group#;
# Check Archive Log Status
ARCHIVE LOG LIST;
# List Users and Status
SELECT username, account_status, default_tablespace, created
FROM dba_users
ORDER BY username;
Module 3: Configuring an Efficient Environment
# Current Active Sessions
SELECT sid, serial#, username, status, osuser, machine, program
FROM v$session
WHERE username IS NOT NULL
ORDER BY status, username;
# Long Running Queries
SELECT a.username, a.sid, a.serial#, b.sql_text, a.status, a.osuser
FROM v$session a
JOIN v$sql b ON a.sql_id = b.sql_id
WHERE a.status = 'ACTIVE' AND a.username IS NOT NULL;
# Blocking Sessions
SELECT
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait,
state
FROM v$session
WHERE blocking_session IS NOT NULL;
Module 3: Configuring an Efficient Environment
# Show Init Parameters (Alphabetically)
SELECT name, value
FROM v$parameter
ORDER BY name;
# Check FRA (Fast Recovery Area) Usage
SELECT
name,
space_limit/1024/1024 AS limit_mb,
space_used/1024/1024 AS used_mb,
ROUND((space_used/space_limit)*100,2) AS pct_used
FROM v$recovery_file_dest;
Module 3: Configuring an Efficient Environment
