home directory
ASM installation on, 58
Oracle installation in Linux on, 51
Oracle installation in Windows on,
46–47
Oracle software installation on, 64
hot backups, 130
I
identity column, SQL Server, 242
immediate shutdowns, 85
implicit cursors, Oracle transactions, 254–255
importing
with Data Movement tab in OEM, 106
with Data Pump utility, 157–161
incremental backups, 134–135
Independent Oracle User Group (IOUG), 124
index_stats table, 182
indexed views, 214–215
indexes
bitmap, 215–216
detecting corruption between tables and,
173–174
enabling monitoring, 210
function-based, 212–214, 260
general tasks, 173
invisible, 218–219
overview of, 209–210
partitioned, 217
primary key, 211–212
rebuilding, 181–184, 191–192, 218
reorganizing tables, 184–187

key index with, 211–212
IOUG (Independent Oracle User Group), 124
IP addresses, configuring RAC, 279–280
ipcs-b system command, 19
isolation, SQL Server, 219
J
JAVA_POOL_SIZE parameter, 17
JDBC (Java Database Connectivity)
client failover in RAC, 284
connections, 123
managing SQL Server databases, 10
JOB_QUEUE_PROCESSES parameter,
scheduling jobs, 37, 82
jobs
Data Pump, 157–158
scheduling.
See
scheduling jobs
K
kernel parameters, Oracle installation on
Linux, 54–55
L
languages, choosing character sets, 89–91
large pages, and memory, 19
LARGE_POOL_SIZE parameter, 17
LCRs (logical change records), Oracle
Streams, 306
ldap.ora file, 122
.ldf files, 62
least recently used (LRU) blocks, sizing SGA

file system in Windows for Oracle
installation, 47
locking data, 219–221
log groups, creating with database, 82
LOG_ARCHIVE_DEST parameter, 35–36
logical change records (LCRs), Oracle
Streams, 306
logical standby databases, 290–291
logs
alert.
See
alert log
archive.
See
archive logs
audit, 36, 189
overview of, 38
primary/standby databases and, 290–296
redo.
See
redo logs
resizing, 198–199
SQL Server and Oracle backup
options, 135
storage requirements for, 56–57, 62
transaction logs.
See
transaction logs,
SQL Server
transaction process flow and, 40–42

user job setup, 196–197
man (manual) pages, Linux commands in,
49–50
man command, Linux, 49
Manage Scheduler privilege, 197
master database
backing up in SQL Server, 135
not existing in Oracle, 22
system-level information in Oracle
vs., 23–26
materialized views, creating indexes on,
214–215
MAX_JOB_SLAVE_PROCESSES
parameter, 37
MAXDATAFILES parameter, 35
Maximum Availability Architecture (MAA),
Oracle.
See also
high-availability
architecture, 274, 296
Maximum Availability, Oracle Data
Guard, 290
Maximum Performance, Oracle Data
Guard, 290
Maximum Protection, Oracle Data
Guard, 290
MAXLOGFILES parameter, database
creation, 35
.mdf files, 62
Index

DBA responsibility for, 7–8
tools for, 104
most recently used (MRU) blocks, sizing SGA
memory, 22
MOVE command, tables, 186
Move command, Windows, 49
MRU (most recently used) blocks, sizing SGA
memory, 22
msdb system database, SQL Server
backing up, 135
job scheduling in, 191
not existing in Oracle, 22
multimaster replication, 307
mv command, Linux, 49
My Oracle Support
managing patches, 70–71
overview of, 124
N
naming conventions
Data Pump jobs, 157
Linux disk storage, 57
listeners, 85–89
Oracle database, choosing unique
name, 80
Oracle database, name definitions,
76, 119
recycle bin objects, 162
navigation, OEM, 105–107
Net Configuration Assistant (NETCA), creating
listener, 85–89

nowelcome option, response files, 68
NTFS file system, installing database software
on, 47
O
object maintenance
grants, 189–190
index rebuild, 181–184
invalid objects, 187–188
overview of, 181
synonyms, 190–191
table reorganization, 184–187
objects
backing up and restoring, 156–161
granting individual permissions
against, 189
importing with OEM, 160–161
324
Oracle Database Administration for Microsoft SQL Server DBAs
querying information about database,
24–25
recompiling invalid, 187–188
OCR (Oracle Cluster Registry), configuring
RAC, 278, 280
OEM (Oracle Enterprise Manager)
activity monitors, 224–225
ASM disk groups and files, 300–301
AWR reports, 233–235
backup settings, 133–134
backups, managing, 154–155
backups, scheduling jobs, 137–141

DBAs working with configuration of, 4
Oracle installation preparation, 44–46
optimizer parameters, 36
Optimizer Statistics Gathering task, 176
OPTIMIZER_INDEX_COST_ADJ database
parameter, 232
OPTIMIZER_MODE parameter, 36, 232
options, retrieving values of, 33–34
Options tab, Oracle Scheduler, 193
“ORA-1555: snapshot too old” error, 40
Oracle
PL/SQL language in.
See
PL/SQL
skills needed for managing, 6–7
SQL Server database migration to, 9–13
SQL Server vs.
See
SQL Server vs.
Oracle
storage requirements for files, 61–62
Oracle, internal structures
data dictionary views, 26–27
database creation parameters, 35
jobs and schedules, 28
location and destination parameters, 36
master, msdb and tempd not
existing in, 22
memory parameters, 17–20
memory structures, 16

OEM (Oracle
Enterprise Manager)
Oracle Grid Control, deploying RAC,
286–287
Oracle home directory
planning location of, 47
Windows for Oracle installation
setup, 46
Oracle Internet Directory, client connections,
121–122
Index
325
Oracle Scheduler, OEM
creating job in, 191–194
overview of, 191
Server tab, 109
Oracle Server installation
Linux setup, 50–55
Linux/Unix commands, 48–50
operating system preparations, 44–46
Oracle database components, 63–64
overview of, 44
Windows setup, 46–48
Oracle SQL Developer
creating new package framework,
243–245
creating unit test, 263–264
database development with, 117–119
database migrations, 10
debugging procedures, 262–263

duplicating databases with scripts, 83
function-based index, 213
location and destination of, 36
managing in Database Configuration of
OEM Server tab, 108
materialized views, 215
memory, 17–20
optimizer and performance, 36–37
Oracle Streams, 304
other, 37–38
overview of, 32–33
performance tuning using, 231–233
RAC, 287–289
storage requirements, 62
transaction log, 34–35
viewing and setting, 33–34
partitioned indexes, 217
partitioned tables, 183–184
passwords
securing schema by not giving out, 101
storage requirements, 62
Patch Set Updates (PSU), 70–71
patches
applying, 70–71
applying with Software and Support tab
in OEM, 106
Configuration Manager for assistance
in, 124
for RAC environments, 286
patchsets, 70

Oracle Database Administration for Microsoft SQL Server DBAs
schema, 98–101
server, 95–98
SQL Server database owners, 99
SQL Server vs. Oracle, 243
viewing session, 51–52
pfile, 34
PGA (Program Global Area) memory,
17–18, 22
PGA_AGGREGATE_TARGET parameter, 22
PGA_AGGREGATE_TARGET parameter, 36
Physical Address Extension (PAE), 19
physical standby databases, 290–291
pipelined table functions, 259–261
platforms, mental preparedness for multiple,
310–314
PL/SQL
database coding, 240–243
debugging procedures and unit testing,
262–264
error handling, 264–269
functions, 258–261
overview of, 240
packages and package bodies, 243–245
triggers, 246–248
updates and conditions, 249–250
using DBMS packages, 270–271
PL/SQL transactions
beginning, 251–253
cursor processing, 254–257

17–18, 22
programs, job, 28
protection modes, Oracle Data Guard, 290
ps -ef command, Linux, 49
PSU (Patch Set Updates), 70–71
public IP addresses, RAC, 279–280
purging recycle bin, 163–164
pwd command, Linux, 48
Q
QMN0 process, Oracle Streams, 31
queries
executing in SQL*Plus, 112–117
exporting with Data Pump, 157
flashing back, 167–168
invisible indexes testing performance
of, 218
reducing time with indexes.
See
indexes
tools for, 104
troubleshooting on RAC database, 289
QUERY_REWRITE_ENABLED parameter, 37
QUERY_REWRITE_ENABLED=TRUE
parameter, 213, 215
QUERY_REWRITE_INTEGRITY=TRUSTED
parameter, 213, 215
queues, Oracle Streams advanced, 305
R
RAC (Real Application Clusters)
Automatic Storage Management in,

purging obsolete files, 155–156
RMAN restore and recovery options, 143
Recovery Manager.
See
RMAN (Recovery
Manager)
RECOVERY WINDOW parameter, purging
obsolete files, 155–156
RECOVERY_CATALOG_OWNER role, 131
recycle bin, 37, 161–164
RECYCLEBIN parameter, 37
redo logs
created with database, 82
managing in Storage category of OEM
Server tab, 108
planning backup strategy for, 142
resizing, 198–199
transaction logs vs., 38–39
transaction process flow, 40–41
REDUNDANCY parameter, purging obsolete
files, 155–156
REF CURSOR, 256–257
RELIES_ON clause, Oracle, 261
REMOTE_LOGIN_PASSWORDFILE
parameter, 38
REPAIR FAILURE command, data recovery
advisor, 149
REPAIR FAILURE PREVIEW command, data
recovery advisor, 149
replace operations, 241, 243

recovering to specific point, 144–145
SQL Server/Oracle tools for, 104
tablespaces, datafiles, and blocks,
146–147
testing RAC, 283
using standby database for, 291
viewing available backups for,
152–155
what can go wrong, 141–143
restore database command, 143
restore point
creating in OEM, 139–141
flashing back database to, 166–168
recovering to, 145
result cache, 261
retention policies
OEM configuration for backups,
133–134
purging obsolete files, 155–156
RMAN configuration for backups,
132–133
undo, 39–40
reverse key indexes, 216–217
rm command, Linux, 48–49
rm -r command, Linux, 49
RMAN (Recovery Manager)
backup and restore commands,
129–131
configuration for backups, 131–134
configuring/scheduling backup jobs in

beginning transactions in Oracle with,
251–253
PL/SQL error handling with, 265
scalar-valued functions, Oracle, 258–259
Schedule tab, Oracle Scheduler, 193
scheduling jobs
for automatic statistics gathering, 176
after creating database, 82
after database migration, 11
defined, 28
in OEM, for backups, 138–140
in OEM, for Data Pump, 158–160
overview of, 191
for system and user jobs, 196–197
for table reorganization, 186
tools for, 104
using DBMS_SCHEDULER package,
194–196
using DBMS_STATS package, 179–180
using Oracle Scheduler, 109, 191–194
schema locks, SQL Server, 220
schema owner, 75
Schema tab, OEM, 106, 184–186
schemas
creating backups of, 156–157
exporting with Data Pump, 157
implementing Oracle Streams for, 305
overview of, 74–75
permissions, 98–101
recompiling invalid objects at level

SELECT FOR UPDATE statement, locking
data manually, 219
SELECT INTO, using implicit cursors, 255
SELECT statement
cursor processing in PL/SQL,
255–257
setting up client failover in RAC,
283–284
UPDATE statement in Oracle vs., 249
semicolon (;), database coding, 242, 262
sequences, Oracle, 241–243
server
defined, 74
naming, 76–77, 119
permissions for, 95–98
server log, DBAs monitoring, 9
Server tab, OEM
Database Configuration category, 108
defined, 106
Oracle Scheduler, 109
Security category, 109–110
SQL Server Management Studio
vs., 107
Statistics Management category, 109
Storage category, 107–108
services, running in database, 29–32
Session Monitor, SQL Server, 209
SESSION_CACHED_CURSORS parameter, 37
SESSIONS parameter, 35
Set command, Windows, 49

SIMPLE option, SQL Server
backup strategy, 128
point-in-time recovery in, 145
transaction logs, 34
single-instance database, patching RAC
like, 286
sizing
configuring flashback recovery area, 165
datafiles, 198–201
files, 197–198
logs, 198–199
SGA and PGA memory, 20–22
tablespaces, 108
SMON background process, 30
snapshot database configuration, 291
software, configuring RAC with
Clusterware, 278
software, installing Oracle
overview of, 64–67
patches, 70–71
removing software, 68
upgrading database, 68–70
using response file, 67–68
Software and Support tab, OEM, 106
sort order, choosing character set, 89
sp_configure, SQL Server parameters, 33
sp_updatestats, update statistics, 176
sp_who command, 113
SPFILE parameter, 37
spfile.ora file

error functions, 265
function types, 258–259
general maintenance tasks, 172–173
health checks, 175
high-availability options, 274–275
index types, 211
installation checklists, 6
lock types, 220–221
locking, 219–221
memory structures, 16
mental preparedness for multiple
platforms, 310–314
performance tuning, 209
querying database objects, 24
scheduling jobs, 192
server roles, 96–98
setup, 77–78
SID (system identifier), 76
system-level information, 23–26
tools for database tasks, 104–105
transaction logs vs. redo logs, 38–39
330
Oracle Database Administration for Microsoft SQL Server DBAs
triggers, 246–247
UPDATE statement, 249–250
update statistics procedure, 178
viewing and setting parameters, 33–34
SQL statements, library cache for, 236–237
SQL*Loader
database migrations using, 9

performance tuning by viewing, 209
validating for performance, 230–231
Statistics Management category, OEM Server
tab, 109
STATISTICS_LEVEL parameter, 37, 176
Storage category, Server tab of OEM, 107–108
storage requirements
disks, 56–57
managing with ASM, 57–61
for Oracle files, 61–62
overview of, 55–56
stored procedures
database migrations and, 10
debugging, 262–264
declaring in PL/SQL packages, 243–245
validation after database migration, 11
streams.
See
Oracle Streams
synchronous capture, Oracle Streams, 306
synonyms, database maintenance, 190–191
SYS, 24–25
sysadmin role, SQL Server, 97–98
SYSASM role, Oracle, 98
SYSAUX tablespace
creating with database, 82
monitoring, 203
viewing/changing, 107–108
SYSDBA role, Oracle
auditing grants, 189

SYSTEM user, Oracle, 98
system views.
See
views, current activity
System V-style shared memory, 19
system-defined errors, PL/SQL, 265
system-defined functions, 259–260
T
tables
backups of, 156–157
creating with flashed back data,
167–168
detecting corruption between indexes
and, 173–174
locks, 221
partitioned indexes for, 217
Index
331
tables
(cont.)
primary indexes for constraints in, 211
recompiling invalid synonyms on,
189–190
reorganizing fragmented, 184–187
restoring from recycle bin, 163
triggers on, 247–248
tuning using explain plans, 228–230
updating statistics, 178–181
validating performance with statistics,
230–231

monitoring, 202
not included in Oracle backups, 135
overview of, 28–29
test environment
avoid mixing production and, 160
copying database for, 149–150
importing system statistics into, 177–178
PL/SQL unit tests, 263–264
Real Application Clusters, 282–283
thick clients, JDBC, 123
thin clients, JDBC, 123
TIME_WAITED column, AWR Active Session
History view, 236
times, validating after migration, 11
timestamps, validating after migration, 11
TNS Listener service, 29
tnsnames.ora file
overview of, 121–123
possible connection issues, 124
setting up client failover in RAC, 284
setting up database alias, 124
setting up RAC listeners, 285
TO_functions, Oracle, 259–260
TOO_MANY_ROWS error, SELECT INTO, 255
tools, DBA.
See
DBA (database administrator)
tools
Top Activity, OEM, 224–225
trace files, maintaining, 204

U
undo area, 39–40
UNDO tablespace
creating with database, 81–82
monitoring, 202
332
Oracle Database Administration for Microsoft SQL Server DBAs
overview of, 39–40
transaction process flow, 41
viewing/changing, 107–108
UNDO_MANAGEMENT parameter, 36
UNDO_RETENTION parameter, 39–40
UNDO_TABLESPACE parameter, 36
Unicode character set (AL32UTF8),
international language databases, 91–92
unique scans, tuning indexes, 229
unit test, PL/SQL, 263–264
Unix commands, 48–49
UNLIMITED TABLESPACE role, 100
“until time,” recovering to, 145
update locks, SQL Server, 220
update operations
PL/SQL conditions and, 249–250
with PL/SQL FORALL loop, 257–258
triggers for, 247
UPDATE statement
SQL Server vs. Oracle, 249–250
using implicit cursors, 255
update statistics
maintenance tasks, 173

Users area, Security category of OEM Server
tab, 109–110
utlrp.sql script, 188
V
v$ views, ASM information, 302–303
v$database view, 142, 145
v$db_cache view, 21
v$flash_recovery_area_usage view,
165–166
v$flashback_database_log
view, 165
v$log_history view, 41, 198–199
v$object_usage table, 210
v$pga_target_advice view, 22
v$pgainfo view, 22
v$process view, 22
v$recovery_file_dest view, 166
v$session view, 222
v$session_wait view, 225, 236
v$sga view, 20
v$sgainfo view, 20
v$sgastat view, 21
v$undostat view, 40
validation of data, in database migration, 11
variables, declaring in PL/SQL packages,
243–245
version control, SQL Developer tool, 242
View Database Feature Usage, OEM
Server tab, 108
views

236–237
AWR reports in OEM, 234–235
checking performance issues, 225
WAIT_TIME column, AWR Active Session
History view, 236
whoami command, Linux, 49
Windows, Oracle installation setup
checklist for, 48
command-line commands, 49
file system, 47
hardware, 45
network connectivity, 47
Oracle home directory, 46
software, 64
user account, 47
WITH RECOVERY option, 145
WORKAREA_SIZE_POLICY=AUTO parameter,
PGA memory, 22
workload repository, managing with
OEM, 109
334
Oracle Database Administration for Microsoft SQL Server DBAs