Oracle 11gR2 Core DBA Course-1

OrSkl Education
Oracle Database Administration - I
By,
Kumar
Oracle DBA - Architect
OCA, OCP -10g
OCP - 11g
OCE - 11g
OCP - 12c
OrSkl Education Oracle Database Administration - I www.OrSkl.com
Content:
1. Install Oracle database
∙ Software's introduction
∙ OEL installation
∙ OS Network configuration
∙ Putty configuration
∙ OS - pre requisites
∙ Install Oracle software
∙ Install oracle network configuration
∙ Install oracle database
∙ Demonstrate connecting method to database ∙
purpose of oraenv
∙ start/stop listener and database
∙ purpose of /etc/oratab file
2. Oracle database Architecture
∙ Connecting to a Server
∙ Oracle Database Architecture: Overview
∙ Connecting to the Database
∙ Interacting with an Oracle Database
∙ Oracle Database Server Structures
∙ Oracle Database Memory Structures
∙ Database Buffer Cache
∙ Redo Log Buffer
∙ Shared Pool
∙ Allocation and Reuse of Memory in the Shared Pool ∙
Large Pool
∙ Java Pool and Streams Pool
∙ Process Architecture
∙ Database Writer Process (DBWn)
∙ LogWriter Process (LGWR)
∙ Checkpoint Process (CKPT)
∙ System Monitor Process (SMON)
∙ PMON
∙ RECO
∙ ARCn
∙ Redo Cycle
∙ Other background processes
∙ Server process and buffer cache
∙ Introduction to Storage architecture Linux Installation 3.
Storage Architecture
∙ Logical and Physical Database Structures
∙ Tablespaces and Data Files
∙ SYSTEM and SYSAUX Tablespaces
∙ Segments, Extents, and Blocks
www.orskl.com Page 2
4. Initialization Parameter Files

∙ SPFILE and PFILE
∙ Simplified Initialization Parameters
∙ Change dynamic and static parameters with SPFILE ∙
Change dynamic and static parameters with PFILE ∙
Create spfile from pfile
∙ create pfile from spfile
5. Database Startup and Shutdown
∙ Startup Options: Examples
∙ Shutdown Options
∙ Shutdown Options: Examples
∙ Viewing the Alert Log
∙ Using Trace Files
∙ Dynamic Performance Views
∙ Data Dictionary
6. Configuring the Oracle Network Environment ∙
Oracle Net Services
∙ Oracle Net Listener
∙ Establishing Net Connections
∙ Establishing a Connection
∙ User Sessions
∙ Tools for Configuring and Managing the Oracle Network ∙
Listener Control Utility
∙ Changing Listener NAME
∙ Database Service Registration
∙ Naming methods
∙ Local Naming
∙ Directory Naming
∙ External Naming Method
∙ Testing Oracle Net Connectivity
∙ User Sessions: Dedicated Server
∙ Configuring Communication Between Databases 7.
Managing Database Storage Structures
∙ Storage Structures
∙ How Table Data Is Stored
∙ Database Block: Contents
∙ Space Management in Tablespaces
∙ Creating a New Tablespace
∙ Tablespaces in the Preconfigured Database
∙ Dropping Tablespaces
∙ Viewing Tablespace Information
∙ Viewing Tablespace Contents
∙ Enlarging the Database
∙ Automatic Storage Management
∙ ASM: Key Features and Benefits

8. Administering User Security
∙ Database User Accounts
∙ Predefined Accounts: SYS and SYSTEM
∙ Creating a User
∙ Drop a user
∙ Administrator Authentication
∙ Unlocking a User Account and Resetting the Password ∙
Reset password with HASH value
∙ System and Object Privileges
∙ Benefits of Roles
∙ Assigning Privileges to Roles and Assigning Roles to Users ∙
Predefined Roles
∙ Profiles and Users
9. Managing Data and Concurrency
∙ Manipulating Data by Using SQL
∙ COMMIT and ROLLBACK Commands
∙ PL/SQL
∙ Administering PL/SQL Objects
∙ PL/SQL Objects
∙ Functions
∙ Procedures
∙ Packages
∙ Built-in Packages
∙ Triggers
∙ Triggering Events
∙ Locks
∙ Locking Mechanism
∙ Data Concurrency
∙ DML Locks
∙ Enqueue Mechanism
∙ Lock Conflicts
∙ Possible Causes of Lock Conflicts
∙ Detecting Lock Conflicts
∙ Resolving Lock Conflicts
∙ Deadlocks
10. Managing Undo Data
∙ Data Manipulation
∙ Undo Data
∙ Transactions and Undo Data
∙ Storing Undo Information
∙ Undo Data Versus Redo Data
∙ Managing Undo
∙ Configuring Undo Retention
∙ Guaranteeing Undo Retention
∙ Changing an Undo Tablespace to a Fixed Size 11.
Database Maintenance & Performance Management ∙
Terminology
∙ Oracle Optimizer: Overview
∙ Optimizer Statistics
∙ Statistic Levels
∙ Preferences for Gathering Statistics
∙ Automatic Workload Repository(AWR)
∙ AWR Infrastructure
∙ Baselines
∙ Managing the AWR
∙ Automatic Database Diagnostic Monitor (ADDM) ∙
Advisory Framework
∙ Automated Maintenance Tasks
∙ Performance Monitoring
∙ Managing Memory Components
∙ Dynamic Performance Statistics
∙ Troubleshooting and Tuning Views
∙ Invalid and Unusable Objects
12. Backup and Recovery Concepts
∙ Part of Your Job
∙ Categories of Failure
∙ Statement Failure
∙ User Process Failure
∙ Network Failure
∙ User Error
∙ Instance Failure
∙ Understanding Instance Recovery
∙ Phases of Instance Recovery
∙ Media Failure
∙ Configuring for Recoverability
∙ Configuring the Flash Recovery Area
∙ Multiplexing Control Files
∙ Redo Log Files
∙ Archive Log Files
∙ Enabling ARCHIVELOG Mode
13. Performing Database Backups
∙ Backup Solutions: Overview
∙ Oracle Secure Backup
∙ User-Managed Backup
∙ Terminology
∙ Differential Incremental level 1
∙ Cumulative Incremental level 1
∙ Recovery Manager (RMAN)
∙ Configuring Backup Settings
∙ Backing Up the Control File to a Trace File

∙ Managing Backups
∙ Viewing Backup Reports
∙ Monitoring the Flash Recovery Area
14. Performing Database Recovery
∙ Keeping a Database Open
∙ Data Recovery Advisor
∙ Data Failure: Examples
∙ Data Recovery Advisor Views
∙ Loss of a Control File
∙ Loss of a Redo Log File
∙ Loss of a Data File in NOARCHIVELOG Mode
∙ Loss of a Noncritical Data File in ARCHIVELOG Mode ∙ Loss
of a System-Critical Data File in ARCHIVELOG Mode 15. Moving
Data
∙ Moving Data: General Architecture
∙ Directory Objects: Overview
∙ Creating Directory Objects
∙ SQL*Loader: Overview
∙ Loading Data with SQL*Loader
∙ SQL*Loader Control File
∙ Loading Methods
∙ External Table Population
∙ Using External Tables
∙ Oracle Data Pump: Overview
∙ Oracle Data Pump: Benefits
∙ Data Pump Export and Import: Overview
∙ Data Pump Utility: Interfaces and Modes
∙ Data Pump Import
∙ Data Pump Import: Transformations
∙ Data Pump: Performance Considerations
∙ Performance Initialization Parameters
16. Working With Support
∙ Using the Support Workbench
∙ Creating a Service Request
∙ Packaging and Uploading Diagnostic Data to Oracle Support ∙
Working with Oracle Support
∙ MetaLink Integration
∙ Using MetaLink
∙ Researching an Issue
∙ Logging Service Requests
∙ Managing Patches
∙ Applying a Patch Release - SPU patch example
∙ Identify list of patches installed
∙ Find OPatch version
1. Install Oracle database
∙ Softwares introduction
1. VMware workstation
2. OEL 6.4 - 32 bit
3. Oracle database 11.2.0.3 - 32bit
4. Winscp - to Copy softwares from windows to linux
5. Xming - to allow GUI based connections to local host via putty
6. Putty
7. Oracle client software for windows.
∙ OEL installation
Done in LINUX sessions.
∙ OS Network configuration
∙ Putty configuration
∙ OS - pre requisites
- From now we will be using ONLY PUTTY.
OS pre-requisites : root
- Disable Firewall
- Create OS user groups : oinstall dba
- create oracle user
- Set password to oracle user
- change permissions to /oracle and /oradata mount point to ORACLE user
- create /etc/oraInst.loc
- set kernel parameters

- install missing RPM's
- set secutiry limits on OS.
- entry to /etc/hosts
- disable SELINUX
To do:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
chown -R oracle:oinstall /oracle
chown -R oracle:oinstall /oradata
chmod -R 775 /oracle
chmod -R 775 /oradata
vi /etc/oraInst.loc
inventory_loc=/oracle/orainventory
install_group=oinstall
inst_group=oinstall
vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1054504960
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586
/sbin/sysctl -p
• Hardware check
At least 1 GB of physical memory is needed.
grep MemTotal /proc/meminfo
A minimum of 1 GB of swap space is required.
grep SwapTotal /proc/meminfo
The /tmp directory should be at least 400 MB.
df -h /tmp
• RPM's installation
Install the below missing rpms on server.
rpm -Uvh <rpm_insall>
• Set security limits
vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
• add entries in /etc/hosts
vi /etc/hosts
192.168.122.20 OEL-11g
• Disable SELINUX
vi /etc/sysconfig/selinux
Restart the server.
∙ Install Oracle software
You will have to login as ORACLE user.
Copy the software - 1.zip and 2.zip to server using winscp.
unzip <zipName>
cd database
./runInstaller
∙ Install oracle network configuration
cd $ORACLE_HOME/bin
./netca
∙ Install oracle database
cd $ORACLE_HOME/bin
./dbca
∙ Demonstrate connecting method to database

∙ purpose of oraenv
[oracle@OEL-11g ~]$ . oraenv
ORACLE_SID = [oracle] ? ORADB^C
[oracle@OEL-11g ~]$
[oracle@OEL-11g ~]$ echo $ORACLE_SID
[oracle@OEL-11g ~]$ echo $ORACLE_BASE
[oracle@OEL-11g ~]$ echo $ORACLE_HOME

[oracle@OEL-11g ~]$ echo $PATH
/usr/lib/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
[oracle@OEL-11g ~]$
ORACLE_SID = [oracle] ? ORADB
The Oracle base has been set to /oracle/base
[oracle@OEL-11g ~]$ echo $ORACLE_SID
ORADB
[oracle@OEL-11g ~]$ echo $ORACLE_BASE
/oracle/base
[oracle@OEL-11g ~]$ echo $ORACLE_HOME
/oracle/base/product/11.2.0/db
[oracle@OEL-11g ~]$ echo $PATH
/usr/lib/qt
3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/oracle/base/product/11.2.0/
db/bin
[oracle@OEL-11g ~]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 25 08:24:07 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
[oracle@OEL-11g ~]$ which sqlplus
/oracle/base/product/11.2.0/db/bin/sqlplus
∙ start/stop listener and database
login as: oracle
[email protected]'s password:
Last login: Thu Jun 25 07:41:32 2015 from 192.168.122.1
ORACLE_SID = [oracle] ? ORADB
The Oracle base has been set to /oracle/base
[oracle@OEL-11g ~]$ lsnrctl start^C
[oracle@OEL-11g ~]$
[oracle@OEL-11g ~]$ sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With
the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup;^C
SQL> exit
[oracle@OEL-11g ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 25-JUN-2015 08:30:55
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OEL-11g)(PORT=1521)))
The command completed successfully
[oracle@OEL-11g ~]$ sqlplus / as sysdba
Connected to:
SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@OEL-11g ~]$ su
Password:
[root@OEL-11g oracle]# shutdown -h now
[root@OEL-11g oracle]#
Broadcast message from oracle@OEL-11g
(/dev/pts/0) at 8:32 ...
The system is going down for halt NOW!
∙ purpose of /etc/oratab file
To find out all the databases installed on the server.
[oracle@OEL-11g ~]$ cat /etc/oratab
# This file is used by ORACLE utilities. It is created by root.sh # and
updated by either Database Configuration Assistant while creating # a
database or ASM Configuration Assistant while creating ASM instance. # A
colon, ':', is used as the field terminator. A new line terminates # the
entry. Lines beginning with a pound sign, '#', are comments. #
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home #
directory of the database respectively. The third filed indicates # to
the dbstart utility that the database should , "Y", or should not, #
"N", be brought up at system boot time.
# Multiple entries with the same $ORACLE_SID are not allowed.
ORADB:/oracle/base/product/11.2.0/db:N
OrSkl Education Oracle Database Administration - I www.OrSkl.com 2. Oracle database
Architecture
∙ Connecting to a Server
The Oracle relational database management system(RDBMS) provides an open, comprehensive, integrated
approach to information management.
∙ Oracle Database Architecture: Overview

∙ Connecting to the Database
∙ Connection: Communication between a user process and an instance
∙ Session: Specific connection of a user to an instance through a user process
∙ Interacting with an Oracle Database
∙ Oracle Database Server Structures

∙ Oracle Database Memory Structures
∙ Database Buffer Cache

∙ Is part of the SGA (System Global Area)
∙ Holds copies of data blocks that are read from data files( from Storage components) ∙ Is
shared by all concurrent users
∙ It follows LRU algorithm to clean up data blocks in the database buffer cache.
∙ Size of the buffer cache is not fixed, it is dynamically allocated by instance in run-time. Ex:
10 rows are in cache.
2 rows fetched by 1 query
same 2 rows are fetched by another query.
rest of 8 rows will be marked as LRU
When buffer requires memory, these 8 rows will be erased and that space will be used for other data
blocks.
∙ Redo Log Buffer

∙ Is a circular buffer in the SGA
∙ Holds information about changes made to the database
∙ Contains redo entries that have the information to redo changes made by operations such as
DML and DDL
∙ Shared Pool
∙ Is a portion of the SGA
∙ Contains:
– Library cache - That will all the queries that runs in the database. It used LRU algorithm to clean up
space.
— Shared SQL area
– Data dictionary cache: Details of all the objects and their permissions in the database.
– Control structures
∙ Allocation and Reuse of Memory in the Shared Pool

∙ Server process checks the shared pool to see if a shared SQL area already exists for an identical
statement.
∙ Server process allocates a private SQL area on behalf of the session.
∙ Large Pool
Provides large memory allocations for:

∙ Session memory for the shared server and the Oracle XA interface
∙ I/O server processes
∙ Oracle Database backup and restore operations
∙ Java Pool and

Streams Pool
∙ Java pool memory is used in server memory for all session-specific Java code and data in the JVM. ∙
Streams pool memory is used exclusively by Oracle Streams to:
– Store buffered queue messages
– Provide memory for Oracle Streams processes
∙ Process Architecture
• User process
– Is started when a database user or a batch process connects to Oracle Database
• Database processes
– Server process: Connects to the Oracle instance and is started when a user establishes a session. It is
also called PGA
– Background processes: Are started when an Oracle instance is started
∙ Database Writer Process (DBWn)

Writes modified (dirty) buffers in the database buffer cache to disk:
• Asynchronously while performing other processing
• Periodically to advance the checkpoint
∙ LogWriter Process (LGWR)
• Writes the redo log buffer to a redo log file on disk

• Writes:
– When a user process commits a transaction
– When the redo log buffer is one-third full
- For every 3 seconds
– Before a DBWn process writes modified buffers to disk
∙ Checkpoint Process (CKPT)

• Records checkpoint information (SCN number - System Change Number) in
– Control file
– Each data file header
∙ System Monitor Process (SMON)
• Performs recovery at instance startup

• Cleans up unused temporary segments
∙ PMON
• Performs process recovery when a user process fails

– Cleans up the database buffer cache
– Frees resources that are used by the user process
• Monitors sessions for idle session timeout
• Dynamically registers database services with listeners
∙ RECO
• Used with the distributed database configuration

• Automatically connects to other databases involved in in-doubt distributed transactions
• Automatically resolves all in-doubt transactions
• Removes any rows that correspond to in-doubt transactions
∙ ARCn
• Copy redo log files to a designated storage device after a log switch has occurred
• Can collect transaction redo data and transmit that data to standby destinations
∙ Redo Cycle
∙ Other background processes
• MMON: Performs manageability-related background tasks
• MMNL: Performs frequent and light weight manageability-related tasks
• MMAN: Performs automatic memory management tasks
• CJQ0: Runs user jobs used in batch processing
• QMNx: Monitors the Streams Advanced Queuing message queues
∙ Server process and buffer cache
∙ Introduction to Storage architecture
The files that constitute an Oracle database are organized into the following:
• Control files: Contain data about the database itself (that is, physical database structure information). These files
are critical to the database. Without them, you cannot open data files to access the data in the database.
To get the location of control file of the database:

SQL>show parameter control_files
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
control_files string /oradata/ORADB/control01.ctl,
/oradata/ORADB/control02.ctl
• Data files: Contain the user or application data of the database, as well as metadata and the data dictionary
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ORADB/app_tbs1.dbf
/oradata/ORADB/app_tbs2.dbf
/oradata/ORADB/users01.dbf
/oradata/ORADB/undotbs01.dbf
/oradata/ORADB/sysaux01.dbf
/oradata/ORADB/system01.dbf
• Online redo log files: Allow for instance recovery of the database. If the database server crashes and does not
lose any data files, the instance can recover the database with the information in these files.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/ORADB/redo03.log
select GROUP#,member from v$logfile;

GROUP# MEMBER
---------- ------------------------------
3 /oradata/ORADB/redo03.log
The following additional files are important to the successful running of the database:
• Parameter file: Is used to define how the instance is configured when it starts up
SQL> select count(*) from v$spparameter;
COUNT(*)
----------
348
Naming convention : init<DBNAME>.ora
Default location: $ORACLE_HOME/dbs
[oracle@OEL-11g ~]$ cd $ORACLE_HOME/dbs

[oracle@OEL-11g dbs]$ ls -ltr init*
-rw-r--r-- 1 oracle oinstall 849 Feb 12 19:05 initORADB.ora
SQL> select name,value from v$parameter;
SQL> select count(*) from v$parameter;
COUNT(*)
----------
347
• Password file: Allows sysdba, sysoper, and sysasm to connect remotely to the database and perform
administrative tasks
Naming convention : orapw<DBNAME>

Default location: $ORACLE_HOME/dbs/
[oracle@OEL-11g dbs]$ ls -ltr orapw*

-rw-r----- 1 oracle oinstall 2048 Feb 12 18:53 orapwORADB
• Backup files: Are used for database recovery. You typically restore a backup file when a media failure or user error
has damaged or deleted the original file.
• Archived redo log files: Contain an ongoing history of the data changes (redo) that are generated by the instance.
Using these files and a backup of the database, you can recover a lost data file. That is, archive logs enable the
recovery of restored data files.
SQL> archive log list;

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/base/product/11.2.0/db/dbs/arch
Oldest online log sequence 3
Current log sequence 5
SQL> select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/oradata/fra/ORADB/archivelog/2015_02_17/o1_mf_1_11_bg6bhqt3_.arc
• Trace files: Each server and background process can write to an associated trace file. When an internal error is
detected by a process, the process dumps information about the error to its trace file. Some of the information
written to a trace file is intended for the database administrator, whereas other information is for Oracle Support
Services.
ADUMP: audit dump - For auditing logs

SQL> show parameter audit_
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/base/admin/ORADB/adump
BDUMP: background dump - It is used for backuground processes to write their status
SQL> show parameter background_dump
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/base/diag/rdbms/oradb/
ORADB/trace
UDUMP: User dump - It is same location as bdump from 11g, If end user enables any monitoring in the database eg:
SQL trace then log file are written into this area.
SQL> show parameter user_dump
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
user_dump_dest string /oracle/base/diag/rdbms/oradb/
ORADB/trace
CDUMP: Core dump - Any memory related issues are captured to this location
SQL> show parameter core
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
core_dump_dest string /oracle/base/diag/rdbms/oradb/
ORADB/cdump
• Alert log file: These are special trace entries. The alert log of a database is a chronological log of messages and
errors. Each instance has one alert log file. Oracle recommends that you review this periodically.
Naming convention: alert_<DBNAME>.log - BDUMP/UDUMP
[oracle@OEL-11g dbs]$ cd /oracle/base/diag/rdbms/oradb/ORADB/trace

[oracle@OEL-11g trace]$ ls -ltr alert_*
-rw-r----- 1 oracle oinstall 135518 Feb 17 17:21 alert_ORADB.log
3. Storage Architecture
∙ Logical and Physical Database Structures
The database has logical structures and physical structures.
Tablespaces
A database is divided into logical storage units called tablespaces, which group related logical structures
together. For example, tablespaces commonly group all of an application’s objects to simplify some
administrative operations. You may have a tablespace for application data and an additional one for application
indexes.
Databases, Tablespaces, and Data Files

The relationship among databases, tablespaces, and data files is illustrated in the slide. Each databaseis logically
divided into one or more Tablespaces. One or more data files are explicitly created for each tablespace to
physically store the data of all logical structures in a tablespace. If it is a TEMPORARY tablespace instead of a
data file, the tablespace has a temporary file.
Schemas
A schema is a collection of database objects that are owned by a database user. Schema objects are the logical
structures that directly refer to the database’s data. Schema objects include such structures as tables, views,
sequences, stored procedures, synonyms, indexes, clusters, and database links. In general, schema objects
include everything that your application creates in the database.
Data Blocks
At the finest level of granularity, an Oracle database’s data is stored in data blocks. One data block corresponds
to a specific number of bytes of physical database space on the disk. A data block sizeis specified for each
tablespace when it is created. A database uses and allocates free database space in Oracle data blocks.
Extents
The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks
(obtained in a single allocation) that are used to store a specific type of information.
Segments
The level of logical database storage above an extent is called a segment. A segment is a set of extents that are
allocated for a certain logical structure. Different types of segments include:
• Data segments: Each non clustered, non-index-organized table has a data segment, with the exception of
external tables, global temporary tables, and partitioned tables in which each table has one or more segments.
All of the table’s data is stored in the extents of its data segment. For a partitioned table, each partition has a
data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s
data segment.
• Index segments: Each index has an index segment that stores all of its data. For a partitioned index, each
partition has an index segment.
• Undo segments: One UNDO tablespace is created for each database instance. This tablespace contains
numerous undo segments to temporarily store undo information. The information in an undo segment is used
to generate read-consistent database information and, during database recovery, to roll back uncommitted
transactions for users.
• Temporary segments: Temporary segments are created by the Oracle database when a SQL statement needs
a temporary work area to complete execution. When the statement finishes execution, the temporary
segment’s extents are returned to the instance for future use. Specify either a default temporary tablespace for
every user, or a default temporary tablespace that is used database-wide.
The Oracle database dynamically allocates space. When the existing extents of a segment are full,additional
extents are added. Because extents are allocated as needed, the extents of a segment may or may not be
contiguous on the disk.
• Tablespaces consist of one or more data files.

• Data files belong to only one tablespace.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
APP_TBS
TEMP
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE
------------------------------ ----------
/oradata/ORADB/app_tbs1.dbf APP_TBS
/oradata/ORADB/app_tbs2.dbf APP_TBS
/oradata/ORADB/users01.dbf USERS
/oradata/ORADB/undotbs01.dbf UNDOTBS1
/oradata/ORADB/sysaux01.dbf SYSAUX
/oradata/ORADB/system01.dbf SYSTEM
∙ SYSTEM and SYSAUX Tablespaces
• The SYSTEM and SYSAUX tablespaces are mandatory tablespaces that are created at the time of database
creation. They must be online.
• The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). • The
auxiliary SYSAUX tablespace is used for additional database components (such as the Enterprise
Manager Repository and for your AWR snapshots).
∙ Segments, Extents, and Blocks
• Segments exist in a tablespace.

• Segments are collections of extents.
• Extents are collections of data blocks.
• Data blocks are mapped to disk blocks.
4. Initialization Parameter Files
∙ SPFILE and PFILE
• Server parameter file (SPFILE): This is the preferred type of initialization parameter file. It is a binary file that
can be written to and read by the database server and must not be edited manually. It resides in the server on
which the Oracle database is executing; it is persistent across shutdown and startup. The default name of this
file, which is automatically sought at startup, is spfile<SID>.ora.
• Text initialization parameter file: This type of initialization parameter file can be read by the database server,
but it is not written to by the server. The initialization parameter settings must be set and changed manually by
using a text editor so that they are persistent across shutdown and startup. The default name of this file (which
is automatically sought at startup if an SPFILE is not found) is init<SID>.ora.
Location: $ORACLE_HOME/dbs
SQL> create pfile from spfile;
SQL> show parameter spfile
[oracle@OEL-11g dbs]$ more initORADB.ora

ORADB.__db_cache_size=75497472
ORADB.__java_pool_size=33554432
ORADB.__large_pool_size=4194304
ORADB.__oracle_base='/oracle/base'#ORACLE_BASE set from environment
ORADB.__pga_aggregate_target=180355072
ORADB.__sga_target=239075328
ORADB.__shared_io_pool_size=0
ORADB.__shared_pool_size=117440512
ORADB.__streams_pool_size=0
*.audit_file_dest='/oracle/base/admin/ORADB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/ORADB/control01.ctl','/oradata/ORADB/control02
.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_name='ORADB'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/oradata/fra'
*.diagnostic_dest='/oracle/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORADBXDB)'
*.log_archive_dest_1=''
*.memory_max_target=419430400
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.service_names='ORADB'
*.sessions=170
*.sga_max_size=419430400
*.undo_tablespace='UNDOTBS1
∙ Simplified Initialization Parameters

• Static parameters:
– Can be changed only in the parameter file –
Require restarting the instance before taking effect
• Dynamic parameters:
– Can be changed while database is online
– Can be altered at:
— Session level
— System level
– Are valid for duration of session or based on SCOPE setting
– Are changed by using ALTER SESSION and ALTER SYSTEM commands
• SCOPE=SPFILE: The change is applied in the server parameter file only. No change is made to the current instance.
For both dynamic and static parameters, the change is effective at the next startup and is persistent. This is the only
SCOPE specification allowed for static parameters.
• SCOPE=MEMORY: The change is applied in memory only. The change is made to the current instance and is
effective immediately. For dynamic parameters, the effect is immediate but not persistent because the server
parameter file is not updated. For static parameters, this specification is not allowed.
• SCOPE=BOTH: The change is applied in both the server parameter file and memory. The change is made to the
current instance and is effective immediately. For dynamic parameters, the effect is persistent because the server
parameter file is updated. For static parameters, this specification is not allowed.
Static parameter - processes

Dynamic parameter - db_recovery_file_dest_size
Changing Parameter Values: Examples
SQL> alter system set processes=200 scope=both;
alter system set processes=200 scope=both
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set processes=200 scope=memory;
alter system set processes=200 scope=memory
ERROR at line 1:
SQL> alter system set processes=200 scope=spfile;
System altered.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 200
SQL> startup;
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1345352 bytes
Variable Size 335546552 bytes
Database Buffers 75497472 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
SQL> show parameter processe
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SQL> select name,ISSYS_MODIFIABLE from v$parameter where name='processes';
NAME
--------------------------------------------------------------------------------
ISSYS_MOD
---------
processes
FALSE
SQL> select name,ISSYS_MODIFIABLE from v$parameter where name='db_recovery_file_dest_size';
NAME
--------------------------------------------------------------------------------
ISSYS_MOD
---------
db_recovery_file_dest_size
IMMEDIATE
∙ Change dynamic and static parameters with SPFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/base/product/11.2.0/db/dbs/spfileORADB.ora SQL>
db_recovery_file_dest_siz^C
SQL> show parametrer
SP2-0158: unknown SHOW option "parametrer"
SQL> show parameter db_recovery_file_dest_siz
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 0

SQL>
SQL>
SQL> alter system set db_recovery_file_dest_size=1G scope=spfile;
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SQL>
SQL> alter system set db_recovery_file_dest_size=1G scope=both;
System altered.
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 1G
SQL> alter system set db_recovery_file_dest_size=1G scope=memory;
System altered.
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SQL>
SQL>
SQL>
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SQL>

ERROR at line 1:
SQL> alter system set processes=170 scope=memory; alter
system set processes=170 scope=memory
ERROR at line 1:
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Database closed.
SQL> startupl
SP2-0042: unknown command "startupl" - rest of line ignored.
SQL> startup;
Database mounted.
Database opened.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SQL>
∙ Change dynamic and static parameters with PFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
SQL>
SQL>
SQL> alter system set db_recovery_file_dest_size=1G scope=spfile;
alter system set db_recovery_file_dest_size=1G scope=spfile *
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
SQL> alter system set db_recovery_file_dest_size=1G scope=both;

alter system set db_recovery_file_dest_size=1G scope=both *
ERROR at line 1:
SQL>
System altered.
SQL> exit
[oracle@OEL-11g dbs]$ vi initORADB.ora
[oracle@OEL-11g dbs]$ cat initORADB.ora
*.audit_trail='db'
*.control_files='/oradata/ORADB/control01.ctl','/oradata/ORADB/control02.ctl'
*.db_domain=''
*.db_name='ORADB'
*.open_cursors=300
*.processes=150
*.sessions=170
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=1G
[oracle@OEL-11g dbs]$
[oracle@OEL-11g dbs]$
[oracle@OEL-11g dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 30 07:58:10 2015
Connected to:
SQL> alter system set processes=170 scope=memory;
alter system set processes=170 scope=memory
ERROR at line 1:
ERROR at line 1:
alter system set processes=170 scope=spfile
ERROR at line 1:
Database closed.
SQL> exit
[oracle@OEL-11g dbs]$ vi initORADB.ora
[oracle@OEL-11g dbs]$ cat initORADB.ora
*.audit_trail='db'
*.control_files='/oradata/ORADB/control01.ctl','/oradata/ORADB/control02.ctl'
*.db_domain=''
*.db_name='ORADB'
*.open_cursors=300
*.processes=170
*.sessions=170
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=1G
Connected to an idle instance.
SQL> startup;
Database mounted.
Database opened.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
∙ Create spfile from pfile
SQL> create spfile from memory;
File created.
SQL> exit
[oracle@OEL-11g dbs]$ ls -ltr
total 40
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Jun 25 08:19 lkORADB
-rw-r----- 1 oracle oinstall 1536 Jun 25 08:19 orapwORADB
-rw-r----- 1 oracle oinstall 2560 Jun 30 07:49 spfileORADB.ora.bkp
-rw-r--r-- 1 oracle oinstall 812 Jun 30 07:59 initORADB.ora
-rw-rw---- 1 oracle oinstall 1544 Jun 30 07:59 hc_ORADB.dat
-rw-r----- 1 oracle oinstall 13824 Jun 30 08:05 spfileORADB.ora
Connected to:
the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
create spfile from pfile;
File created.
∙ create pfile from spfile
total 28
Connected to:
Database closed.
SQL> startup;
Database mounted.
Database opened.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/base/product/11.2.0/db
/dbs/spfileORADB.ora
SQL>
SQL> create pfile from spfile;
File created.
SQL> exit
total 28
OrSkl Education Oracle Database Administration - I www.OrSkl.com 5. Database Startup and
Shutdown
∙ Startup Options: Examples

SQL> startup;
Fixed Size 1348216 bytes Variable Size

494931336 bytes Database Buffers
343932928 bytes Redo Buffers 5136384
bytes Database mounted.
Database opened.
Database closed.
SQL> alter^C
SQL> startup nomount;
bytes SQL>
SQL> alter database mount;
Database altered.
SQL>
SQL> alter database open;
Database altered.
Database closed.
SQL> startup mount;
bytes
Database mounted.
SQL>
Database altered.
Database closed.

bytes SQL> select status from v$instance;
STATUS
------------
STARTED
Database altered.
SQL>
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Database altered.
STATUS
------------
OPEN
∙ Shutdown Options
Shutdown
shutdown immediate
shutdown abort
shutdown transactional - RAC environment
∙ Shutdown Options: Examples
SQL> shutdown;
Database closed.
SQL> startup;
Variable Size 494931336 bytes Database
Buffers 343932928 bytes Redo Buffers
5136384 bytes
Database mounted.
Database opened.
Database closed.
SQL> startup;

Database opened.
SQL> shutdown abort;
SQL>
SQL> startup;
Database opened.
∙ Viewing the Alert Log
SQL> show parameter back
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /oracle/base/diag/rdbms/oradb/
ORADB/trace
backup_tape_io_slaves boolean FALSE
db_flashback_retention_target integer 1440
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> exit
[oracle@OEL-11g dbs]$ cd /oracle/base/diag/rdbms/oradb/ORADB/trace
[oracle@OEL-11g trace]$ tail -100 alert_ORADB.log
Using Trace Files
-rw-r----- 1 oracle oinstall 199369 Mar 9 18:23 alert_ORADB.log
-rw-r----- 1 oracle oinstall 60 Mar 9 18:23 ORADB_ckpt_3203.trm
-rw-r----- 1 oracle oinstall 964 Mar 9 18:23 ORADB_ckpt_3203.trc
-rw-r----- 1 oracle oinstall 88 Mar 9 18:24 ORADB_dbrm_3193.trm
-rw-r----- 1 oracle oinstall 1225 Mar 9 18:24 ORADB_dbrm_3193.trc
-rw-r----- 1 oracle oinstall 73 Mar 9 18:28 ORADB_mmon_3209.trm
-rw-r----- 1 oracle oinstall 1064 Mar 9 18:28 ORADB_mmon_3209.trc
Dynamic Performance Views
Provide access to information about changing states of the instance memory structures
Dynamic performance views include information about:
• Sessions
• File states
• Progress of jobs and tasks
• Locks
• Backup status
• Memory usage and allocation

• System and session parameters
• SQL execution
• Statistics and metrics
SQL> -- v$instance
SQL> -- v$database
SQL> -- v$tablespace
SQL> -- v$datafile
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN ORADB
SQL> select name,value from v$spparameter where name='control_files';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
control_files
control_files
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/ORADB/rcat1.dbf
6 rows selected.
SQL> desc v$lock
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
KADDR RAW(4)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
SQL> select table_name from dict where table_name like 'V$%';
Dynamic Performance Views: Considerations

• These views are owned by the SYS user.
• Different views are available at different times:
– The instance has been started.
– The database is mounted.
– The database is open.
• You can query V$FIXED_TABLE to see all the view names.
• These views are often referred to as “v-dollar views.”
• Read consistency is not guaranteed on these views because the data is dynamic.
Usage Examples
Database closed.
STATUS
------------
STARTED
select name from v$datafile

*
ERROR at line 1:
OA-01507: database not mounted
Database altered.
NAME
--------------------------------------------------------------------------------
6 rows selected.
SQL> desc v$fixed_table
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
OBJECT_ID NUMBER
TYPE VARCHAR2(5)
TABLE_NUM NUMBER
SQL> select count(*) from v$fixed_table;
COUNT(*)
----------
2013
Data Dictionary
dictionary --
Data dictionary views are residing in your system tablespace. If you want to query a data dictionary view, you
will have get the database open.
Data Dictionary Views
Data Dictionary: Usage Examples

SQL> -- dba_data_files
SQL> -- dba_temp_files
SQL> -- dba_tables
SQL> -- dba_objects
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
Database altered.
COUNT(*)
----------
2832
COUNT(*)
----------
2832
SQL> select count(*) from all_tables;
COUNT(*)
----------
2832
SQL> select count(*) from user_tables;
COUNT(*)
----------
982
SQL> conn test1/oracle
Connected.
select count(*) from dba_tables *
ERROR at line 1:
ORA-00942: table or view does not exist

COUNT(*)
----------
99
COUNT(*)
----------
SQL> create table t1(c1 number(2));
Table created.
COUNT(*)
----------
COUNT(*)
----------
6. Configuring the Oracle Network Environment
∙ Oracle Net Services

∙ Oracle Net Listener
∙ Establishing Net Connections
To make a client or middle-tier connection, Oracle Net requires the client to know the:
• Host where the listener is running
• Port that the listener is monitoring
• Protocol that the listener is using
• Name of the service that the listener is handling

∙ Establishing a Connection
∙ User Sessions
∙ Tools for Configuring and Managing the Oracle Network •
Enterprise Manager Net Services Administration page
• Oracle Net Manager
• Oracle Net Configuration Assistant launched by Oracle Universal Installer
• Command line
∙ Listener Control Utility
Oracle Net listeners can be controlled with the lsnrctl
command-line utility (or from EM).
lsnrctl
LSNRCTL for Linux: Version 11.1.0.3.0 - Beta on 30-MAY-2007 22:38:19
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
Commands from the listener control utility can be issued
from the command line or from the LSNRCTL prompt.
• UNIX or Linux command-line syntax:
$ lsnrctl <command name>
$ lsnrctl start
$ lsnrctl status
LSNRCTL> <command name>
LSNRCTL> start
LSNRCTL> status
[oracle@OEL-11g ~]$ ps -ef | grep lsnr
oracle 2227 1 0 17:23 ? 00:00:00 /oracle/base/product/11.2.0/db/bin/tnslsnr LISTENER -inherit oracle
3918 2185 0 18:09 pts/0 00:00:00 grep lsnr
[oracle@OEL-11g ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-MAR-2015 18:09:49
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 10-MAR-2015 17:23:36
Uptime 0 days 0 hr. 46 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/base/product/11.2.0/db/network/admin/listener.ora
Listener Log File /oracle/base/diag/tnslsnr/OEL-11g/listener/alert/log.xml Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL-11g)(PORT=1521)))
Services Summary...
Service "ORADB" has 1 instance(s).
Instance "ORADB", status READY, has 1 handler(s) for this service...
Service "ORADBXDB" has 1 instance(s).
Instance "ORADB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@OEL-11g ~]$ cd $ORACLE_HOME/network/admin
[oracle@OEL-11g admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-MAR-2015 18:23:24
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
∙ Changing Listener NAME
Static Listener
lsnr_oradb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.122.20)(PORT = 1521))
SID_LIST_lsnr_oradb =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORADB)
(ORACLE_HOME = /oracle/base/product/11.2.0/db)
(SID_NAME = ORADB)
(SERVICE_NAME = ORADB)
Dynamic Listener
- Name LISTENER
∙ Database Service Registration
• Dynamic service registration: Oracle8i and later instances automatically register with the default listener on
database startup. No additional listener configuration is required for the default listener.
• Static service registration: Earlier releases of the Oracle database do not automatically register with the
listener and, therefore, require that the listener configuration file contain a list of all database services that the
listener will serve. You may still choose to use static service registration with newer releases if:
- Your listener is not on the default port of 1521, and you do not want to configure
your instance to register with a non default port
- Your application requires static service registration
SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORADB
∙ Naming methods
Oracle Net supports several methods of resolving connection information:
• Easy connect naming: Uses a TCP/IP connect string
• Local naming: Uses a local configuration file
• Directory naming: Uses a centralized LDAP-compliant directory server • External

naming: Uses a supported non-Oracle naming service
1. Hostname/IP address
2. Port number - listener
3. Service name - listener supports
Easy Connect
• Is enabled by default
• Requires no client-side configuration
• Supports only TCP/IP (no SSL)
• Offers no support for advanced connection options such as:
– Connect-time failover
– Source routing
– Load balancing
SQL> CONNECT hr/[email protected]:1521/dba11g
C:\Users\Hai>sqlplus system/Oracle123@//192.168.122.20:1521/ORADB
∙ Local Naming
• Requires a client-side Names Resolution file (tnsnames.ora)
It will reside in $CLIENT_HOME/network/admin/tnsnames.ora
• Supports all Oracle Net protocols
• Supports advanced connection options such as:
– Connect-time failover
– Source routing
– Load balancing
Contents in tnsnames.ora file:
CLIENT_ORA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.122.20)(PORT = 1521)) )
(CONNECT_DATA =
(SERVER = dedicated)
(SERVICE_NAME = ORADB)
C:\Users\Hai>sqlplus system/Oracle123@CLIENT_ORA
∙ Directory Naming
• Requires LDAP with Oracle Net Names Resolution information loaded:

– Oracle Internet Directory
– Microsoft Active Directory Services
• Supports all Oracle Net protocols
• Supports advanced connection options
∙ External Naming Method
• Uses a supported non-Oracle naming service
• Includes:
– Network Information Service (NIS) External Naming
– Distributed Computing Environment (DCE) Cell Directory Services (CDS)
∙ Testing Oracle Net Connectivity
The tnsping utility that tests Oracle Net service aliases:
• Ensures connectivity between the client and the Oracle Net Listener
• Does not verify that the requested service is available
• Supports Easy Connect Names Resolution:
• Supports local and directory naming:
tnsping db.us.oracle.com:1521/dba11g
tnsping orcl
∙ User Sessions: Dedicated Server
∙ Configuring Communication Between Databases
• Sending data or messages between sites requires network configuration on both sites.
• You must configure the following:
– Network connectivity (for example, TNSNAMES.ora)
– Database links
CREATE DATABASE LINK <remote_global_name>
CONNECT TO <user> IDENTIFIED BY <pwd>
USING '<connect_string_for_remote_db>';
SQL> create database link DB_LINK_TEST connect to system identified by Oracle123 using 'TEST1';
Database link created.
NAME
--------------------------------------------------------------------------------
6 rows selected.
SQL> select name from v$datafile@DB_LINK_TEST;
NAME
--------------------------------------------------------------------------------
+DATA/oradb/datafile/system.257.871201587
+DATA/oradb/datafile/sysaux.258.871201623
+DATA/oradb/datafile/undotbs1.259.871201647
+DATA/oradb/datafile/users.261.871201653
SQL> select DB_LINK,USERNAME,OWNER,HOST from DBA_DB_LINKS;

7. Managing Database Storage Structures
∙ Storage Structures
∙ How Table Data Is Stored
SQL> select segment_name,segment_type,bytes/1024 from dba_segments where segment_name='DEPT' and

owner='SCOTT';
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE BYTES/1024
------------------ ----------
DEPT
TABLE 64
∙ Database Block: Contents
SQL> show parameter db_block_size
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> create table test (c1 number(1));
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name,segment_type,bytes/1024 from dba_segments where

segment_name='TEST' and owner='SYS';
SEGMENT_NAME
--------------------------------------------------------------------------------
------------------ ----------
TEST
TABLE 64
SQL> select * from test;
C1
----------
1
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name,segment_type,bytes/1024 from dba_segments where

segment_name='TEST' and owner='SYS';
SEGMENT_NAME
--------------------------------------------------------------------------------
------------------ ----------
TEST
TABLE 64
The Oracle database stores data logically in tablespaces and physically in data files.
• Tablespaces:
– Can belong to only one database
– Consist of one or more data files
– Are further divided into logical units of storage (Segments)
• Data files:
– Can belong to only one tablespace and one database
– Are a repository for schema object data
∙ Space Management in Tablespaces

• Locally managed tablespace:
– Free extents are managed in the tablespace.
– A bitmap is used to record free extents.
– Each bit corresponds to a block or group of blocks.
– The bit value indicates free or used extents.
– Use of locally managed tablespaces is recommended.
• Dictionary-managed tablespace:
– Free extents are managed by Oracle.
– Appropriate tables are updated when extents are allocated or unallocated in your
SYSTEM/SYSAUX tablespaces.
– These tablespaces are supported only for backward compatibility.
∙ Creating a New Tablespace
SQL> create tablespace TEST datafile '/oradata/ORADB/test1.dbf' size 10m;
Tablespace created.
SQL> create tablespace TEST2 datafile '/oradata/ORADB/test21.dbf' size 10m,

'/oradata/ORADB/test22.dbf' size 10m;
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='TEST2';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ORADB/test21.dbf
/oradata/ORADB/test22.dbf
SQL> select EXTENT_MANAGEMENT,TABLESPACE_NAME from dba_tablespaces;
∙ Tablespaces in the Preconfigured Database
• SYSTEM
• SYSAUX
• TEMP
• UNDOTBS1
• USERS --- optional
• EXAMPLE --- optional
∙ Dropping Tablespaces
SQL> drop tablespace TEST including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace TEST2;
Tablespace dropped.
∙ Viewing Tablespace Information

SQL>select name from v$tablespace;
SQL> Select file_name,tablespace_name from dba_data_files;
∙ Viewing Tablespace Contents
SQL> select segment_name,segment_type from dba_segments where tablespace_name='USERS';
∙ Enlarging the Database
You can enlarge the database in the following ways:

• Creating a new tablespace
• Adding a data file to an existing tablespace
• Increasing the size of a data file
• Providing for the dynamic growth of a data file
∙ Automatic Storage Management
• Is a portable and high-performance cluster file system

• Manages Oracle database files
• Spreads data across disks to balance load
• Mirrors data
• Solves storage-management challenges

Oracle 11gR2 Core DBA Course-1

Uploaded by

Copyright:

Available Formats

Oracle 11gR2 Core DBA Course-1

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle 11gR2 Core DBA Course-1

Uploaded by

Copyright:

Available Formats

OrSkl Education

Oracle Database Administration - I

Oracle DBA - Architect

OCA, OCP -10g

4. Initialization Parameter Files

∙ ASM: Key Features and Benefits

∙ Backing Up the Control File to a Trace File

1. Install Oracle database

2. OEL 6.4 - 32 bit

3. Oracle database 11.2.0.3 - 32bit

4. Winscp - to Copy softwares from windows to linux

5. Xming - to allow GUI based connections to local host via putty

7. Oracle client software for windows.

Done in LINUX sessions.

Done in LINUX sessions.

Done in LINUX sessions.

- From now we will be using ONLY PUTTY.

- Create OS user groups : oinstall dba

- create oracle user

- Set password to oracle user

- change permissions to /oracle and /oradata mount point to ORACLE user

- set kernel parameters

- set secutiry limits on OS.

useradd -g oinstall -G dba oracle

chown -R oracle:oinstall /oracle

chown -R oracle:oinstall /oradata

chmod -R 775 /oracle

chmod -R 775 /oradata

# semaphores: semmsl, semmns, semopm, semmni

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

At least 1 GB of physical memory is needed.

grep MemTotal /proc/meminfo

A minimum of 1 GB of swap space is required.

grep SwapTotal /proc/meminfo

The /tmp directory should be at least 400 MB.

Install the below missing rpms on server.

rpm -Uvh <rpm_insall>

• Set security limits

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

• add entries in /etc/hosts

Restart the server.

∙ Install Oracle software

You will have to login as ORACLE user.

Copy the software - 1.zip and 2.zip to server using winscp.

∙ Install oracle network configuration

∙ Install oracle database

∙ Demonstrate connecting method to database

[oracle@OEL-11g ~]$ . oraenv

ORACLE_SID = [oracle] ? ORADB^C

[oracle@OEL-11g ~]$ echo $ORACLE_SID

[oracle@OEL-11g ~]$ echo $ORACLE_BASE

[oracle@OEL-11g ~]$ echo $ORACLE_HOME

[oracle@OEL-11g ~]$ . oraenv

ORACLE_SID = [oracle] ? ORADB

The Oracle base has been set to /oracle/base

[oracle@OEL-11g ~]$ echo $ORACLE_SID