INDEX
@ command, 76t
A
access paths, 459
access structures, SQL Access Advisor, 473
actions, chain rule, 613
ACTIVE log file status, 266t
ACTIVE redo log group, recovering from loss of, 270
active sessions, number of, 531
ACTIVE state, 270
ADR. See Automatic Diagnostic Repository
ADRCI (Automatic Diagnostic Repository Command Interpreter) tool, 559–562
Advanced Options section, Recommendation Options page, SQL Access Advisor, 473
Advanced Scheduler, 621–624
ADVISE FAILURE command, 76t, 580, 583
Advisor Central page, EM, 462, 464f, 471, 527f
aggregate PGA, 444
aggregate rows, 348
AL16UTF16 character set, 646
Alert section, home page, EM, 498, 499f
alerts, flash recovery area, 79, 91
alias filenames
overview, 32
with templates, 32–33
ALL option, LIST FAILURE command, 580
ALLOCATE CHANNEL command, 76t, 356
allocating channels, 353–354
ALTER DATABASE BACKUP CONTROLFILE TO TRACE command, 8, 31
ALTER DATABASE BEGIN BACKUP command, 292, 294
ALTER DATABASE CHARACTER SET command, 648
ALTER DATABASE CLEAR LOGFILE command, 268
ALTER DATABASE command, 10, 176, 240, 390
alter database enable block change tracking command, 175
ALTER DATABASE FLASHBACK OFF command, 409
ALTER DATABASE MOUNT command, 248
ALTER DATABASE NATIONAL CHARACTER SET command, 648
ALTER DATABASE OPEN command, 221
ALTER DATABASE RENAME command, 176, 246
ALTER DATABASE RENAME FILE command, 166, 230
ALTER DISKGROUP ADD ALIAS command, 32
ALTER DISKGROUP ALL MOUNT command, 28
ALTER DISKGROUP commands, 46t
ALTER FLASHBACK ARCHIVE command, 403–404
ALTER SESSION command, 371, 495–496, 651
ALTER SESSION SET SQL_TRACE=TRUE command, 10
ALTER SYSTEM command, 9–10, 371
ALTER TABLESPACE . . . BEGIN BACKUP command, 294
ALTER TABLESPACE command, 218, 263, 383, 409
AMM. See Automatic Memory Management
application error data loss, 262
archival backups
managing, 185–186
overview, 183
performing, 184–185
archive log file destinations
ARCHIVELOG mode, configuring, 78–80
identifying, 80–82
leveraging multiple
defining minimal successful destinations, 82
local and remote destinations, 82
local destinations, 80–82
archive logs, 34t
ARCHIVED column, 268
archived inactive redo log group, clearing, 268–269
archived log files, 8–9
archived redo log files, 86, 118
ARCHIVELOG mode, 8–9, 17, 68, 72, 216, 246, 290, 577
backing up control file in, 294–296
backing up database in, 292–293
configuring, 78–80
user-managed backups, 289
ARCHIVELOG system template, 35t
archiver process (ARCn), 17–18
ASM. See Automatic Storage Management
ASM_DISKGROUPS parameter, 27, 28
ASM_DISKSTRING parameter, 28
ASM_POWER_LIMIT parameter, 28, 40–41
ASM_PREFERRED_READ_FAILURE_GROUPS parameter, 29
ASMM. See Automatic Shared Memory Management
ASSM. See Automatic Segment Space Management
authentication method flowchart, 272f
AUTO_DROP argument, 597
autobackup, 34t
restoring control file from, 241–245
restoring SPFILE from, 241
AUTOBACKUP system template, 35t
AUTOEXTEND parameter, 7
autoextensible tablespaces, 378
Automated Maintenance Tasks Configuration page, EM, 520, 521f
Automatic Diagnostic Repository (ADR)
ADR Command Interpreter (ADRCI) tool, 559–562
overview, 556–559
Automatic Diagnostic Repository (ADR)
base directory, 557
home directory structure, 557, 558f
Automatic Diagnostic Repository Command Interpreter (ADRCI) tool, 559–562
AUTOMATIC keyword, 285
incomplete recovery, 286
RECOVER command, 280
Automatic Memory Management (AMM)
configuring, 433–436
memory structures
Program Global Area (PGA), 432
software code area, 432–433
System Global Area (SGA), 429–432
monitoring, 436–438
overview, 428–429
switching to, from ASMM, 441
automatic rebalancing, 19–20
Automatic Segment Space Management (ASSM), 519
Automatic Shared Memory Management (ASMM)
overview, 439–440
switching to, 440–442
from AMM, 441
disabling, 441
identifying manually tuned parameters, 442
from manual memory management, 440–441
modifying auto-tuned parameters, 442
modifying SGA_TARGET parameter, 442
Automatic Storage Management (ASM), 46–48, 322
architecture of, 19–23
disk groups, 514
altering, 44–46
architecture of, 39
asmcmd command, 48–53
dynamic rebalancing, 40–42
fast mirror resync, 43–44
mirroring and failure groups, 39–40
overview, 38
initialization parameter files, setting up, 26–35
accessing instances, 27
dynamic performance views, 29–30
file types, 33–35
filename formats, 31–33
initialization parameters, 27–29
overview, 26–27
templates, 33–35
instances
creating, 23–26
finding new ASM-related processes in, 20–23
shutting down, 37–38
starting up, 36–38
overview, 18–19
automatic undo segments, 5–6
auxiliary channels, allocating, 317–318
auxiliary destination, defined, 330
auxiliary instance, 313
configuring, 314
starting in NOMOUNT mode and creating SPFILE, 316–317
auxiliary set, 330
B
background processes, 16f
archiver process (ARCn), 17–18
checkpoint process (CKPT), 18
database writer process (DBWn), 17
Log Writer (LGWR), 17
overview, 15–16
process monitor (PMON), 16–17
recoverer process (RECO), 18
system monitor process (SMON), 16
BACKGROUND_DUMP_DEST initialization parameter, 10–11
BACKUP AS COPY commands, 135, 166
BACKUP . . . BACKUPSET command, 182
BACKUP command, 76t, 120, 141, 169, 355, 356–357
backup control file, 241–245
BACKUP CURRENT CONTROLFILE command, 294
backup destinations, RMAN, 136–137
BACKUP DURATION parameter, BACKUP command, 352, 356–357
backup files, 11
backup mode, identifying need for, 293–294
backup sets, parallelizing, 353–354
BACKUP_TAPE_IO_SLAVES initialization parameter, 358, 360
BACKUP...KEEP FOREVER command, 108
BACKUP...KEEP UNTIL TIME command, 108
backups, 68–69
backing up recovery catalog, 125–126
backing up SPFILE with asmcmd command, 52–53
configuring optimization, 141–142
creating for DUPLICATE command, 317
datafile, 577
BACKUPSET system template, 35t
compressed, 164–166
creating, 163–166
duplexed
configuring multiple disk locations for, 180–182
creating, 179–182
base catalogs, 129
base directory, ADR, 557
BEGIN BACKUP mode, 289
big-endian systems, 502
bigfile tablespaces, 3–4, 186–187
block change tracking, 173
monitoring, 178
overview, 174
relocating file, 176–177
block media recovery
block corruption, 575
Data Recovery Advisor, 579–586
DB_BLOCK_CHECKING parameter, 575–576
overview, 576–578
block-level recovery, 161–162
bottlenecks, throughput, 353
Broker config files, 34t
buffer caches, 12–13, 430–431, 443
buffer, flashback, 406
buffer sizing, datafile, 355t
BZIP2 algorithm, 190
C
cache recovery, 277
CAN_UNDROP column, 373
CANCEL_REPLAY procedure, 483
CANCEL-based incomplete recovery, 286
CASCADE option, DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure, 397
case-sensitivity, database passwords, 274
CATALOG START WITH command, 127
cataloging, 162
cd command, 50t
chain objects, 611–612
chain rules, 613
chain steps, 612–613
CHANGE command, 76t, 183, 185–186
CHANGE FAILURE command, 580–581
change tracking bitmaps, 34t
change tracking files, 173–174
CHANGETRACKING system template, 35t
CHANNEL commands, 356
checkpoint process (CKPT), 18
CHGTRK tablespace, 280–281
CKPT (checkpoint process), 18
CLEARING log file status, 266t
CLEARING_CURRENT log file status, 266t
client requests, 480–481
client-side environment settings, 649–650
Clone Database link, 322
closed database recovery
applying redo log files, 279–283
identifying recovery-related files, 278–279
overview, 277–278
recovering dropped table using, 327–329
restoring recovery-related files, 279
CLOSED option, LIST FAILURE command, 580
cmdfile command line option, 74
coarse striping, 39
cold backups, 71
COMMIT statements, 482
COMMIT_SCN column, FLASHBACK_TRANSACTION_QUERY, 390t
COMMIT_TIMESTAMP column, FLASHBACK_TRANSACTION_QUERY, 390t
committed transactions, 174
COMPACT keyword, 521
compaction, 518–519
COMPATIBLE initialization parameter, 501–502
complete recovery
closed database recovery
applying redo log files, 279–283
identifying recovery-related files, 278–279
overview, 277–278
restoring recovery-related files, 279
open database recovery, 283–284
overview, 276–277
performing using RMAN
critical datafile, 220–221
noncritical datafile, 217–220
overview, 216
RMAN RESTORE and RECOVER commands, 217
COMPLETED option, 182
component relationships, Resource Manager, 530f
compressed backups, 136
compressed backupsets, 164–166
compressing backups, 190
conditions, chain rule, 613
CONFIGURE CHANNEL command, 356
CONFIGURE command, 76t, 141–142, 179–180, 190, 354
CONNECT TARGET command, 247
consistent backups, 134
autobackups, 140–141
automatic backups, 87
backing up in ARCHIVELOG mode, 294–296
copies, 118
recovering, 296–298
restoring from autobackup, 241–245
using for RMAN metadata, 107
CONTROL_FILE_RECORD_KEEP_TIME initialization parameter, 84, 107, 117, 222
CONTROL_FILES parameter, 242, 246, 315t
CONTROLFILE system template, 35t
CONVERT command, 76t
COPIES parameter, 179
copy phase, 353
core dumps, 559
CPU allocations, 538
CPU time limits, 531
CPU usage, 531
CREATE ANY JOB privilege, 601
CREATE CATALOG command, 76t, 111, 127
Create Chain page, Scheduler, EM, 612f
CREATE DATABASE command, 646
CREATE FLASHBACK ARCHIVE command, 402–403
CREATE GLOBAL SCRIPT command, 121
Create Job Class page, EM, 617–619
Create Job page, Scheduler, EM, 607
CREATE JOB privilege, 601
Create Program page, Scheduler, EM, 604–605
Create Resource Consumer Group page, EM, 536f
Create Resource Plan page, EM, 535f
Create Schedule page, Scheduler, EM, 606
CREATE SCRIPT command, 76t, 121
Create Table As Select (CTAS), 384–385
Create Window page, EM, 615–617
CREATE_CHAIN procedure, 611
CREATE_FILE procedure, 478
CREATE_JOB procedure, 596–597, 602
CREATE_JOB_CLASS procedure, 599
CREATE_OBJECT procedure, 522–523
CREATE_PENDING_AREA procedure, 536
CREATE_PLAN_DIRECTIVE procedure, 538
CREATE_PROGRAM procedure, 597–598, 606, 608–609
CREATE_SCHEDULE procedure, 598
CREATE_TASK procedure, 522–523
CREATE_TUNING_TASK procedure, 466
CREATE_WINDOW procedure, 600, 624
critical datafiles, performing complete recovery of, 220–221
critical failures, defined, 262
CRITICAL option, LIST FAILURE command, 580
CROSSCHECK command, 76t, 194, 200–201
cross-platform data files, 34t
CTAS (Create Table As Select), 384–385
cumulative incremental backups, 172–173
CURRENT log file status, 266t
CURRENT redo log group, recovering from loss of, 270–271
cursors, 444
D
data block checking, 574–575
Data Definition Language (DDL), 281, 330, 389, 399, 494
data dictionary, 13, 91, 431, 595
data dictionary views
DBA_ADVISOR_FINDINGS, 522
DBA_ADVISOR_LOG, 467
DBA_ADVISOR_RECOMMENDATIONS, 522, 524
DBA_FLASHBACK_ARCHIVE_TABLES, 405
DBA_FLASHBACK_ARCHIVE_TS, 403–404
DBA_FLASHBACK_ARCHIVE_TS. DBA_FLASHBACK_ARCHIVE, 403
DBA_RECYCLEBIN, 372–373
DBA_RESUMABLE, 495
DBA_TABLESPACES, 383
DBA_/USER_ADVISOR_TASKS, 467
FLASHBACK_TRANSACTION_QUERY, 389–392
job chains, 614–615
multisection, 189–190
Resource Manager, 541–542
USER_RECYCLEBIN, 372–373
USER_RESUMABLE, 495
V$ADVISOR_PROGRESS, 467
data failures, EM, 579
data file copies, 118
data files, 34t
Data Manipulation Language (DML), 292
Data Pump dumpset, 34t
Data Pump Export, 69–70
Data Pump Import, 69–70
Data Pump transfer directory, 327–328
Data Recovery Advisor, 579–586
data segments, 5
database. See also backups
database architecture
database diagnostics
database globalization
database tuning advisors
performing recovery of entire database, 247–248
restoring onto new host, 237–240
starting without tempfile, 265
database architecture
logical storage structures
blocks, 4
extents, 5
segments, 5–6
tablespaces, 3–4
memory structures
background processes, 15–18
program global area (PGA), 14–15
software code area, 15
system global area (SGA), 11–14
overview, 2–3
physical storage structures
alert and trace log files, 10–11
archived log files, 8–9
backup files, 11
control files, 8
datafiles, 7
initialization parameter files, 9–10
overview, 6–7
redo log files, 7–8
Database Character Set Scanner, 647
database character sets, 645
Database Control, 218f
Database Creation Assistant (DBCA), 10, 273, 646
Automatic Diagnostic Repository (ADR)
ADR Command Interpreter (ADRCI) tool, 559–562
overview, 556–559
block media recovery
block corruption, 575
Data Recovery Advisor, 579–586
DB_BLOCK_CHECKING parameter, 575–576
using block media recovery, 576–578
Support Workbench
managing service requests, 565–571
understanding alerts, problems, and incidents, 563–565
using Health Monitor, 571–574
database globalization
customizing language-dependent behavior
character sets, 637–638, 645–648
client-side environment settings, 649–650
within database, 648
instance level, 649
language support, 638–641
NLS settings, 643–644
session-level globalization settings, 651–652
statement globalization settings, 652–654
territory support, 641–643
NLS character sets
linguistic sorting and selection, 654–656
Locale Builder, 656–657
time zones, 658–660
database identifier (DBID), 113–117, 237, 312
Database installation Recovery Configuration and Locations window, 86f
Database Replay, 458–459
analysis and reporting, 483
overview, 479–480
workload capture, 480–481
workload preprocessing, 481–482
workload replay, 482–483
Database Resource Manager
allocation methods, 531
consumer groups, 536–537
DEFAULT_PLAN, 532–535
methods, 537–538
monitoring, 543
resource plans, 535–536, 538–541
terminology, 529–530
views, 541–542
database tuning advisors
Database Replay
analysis and reporting, 483
overview, 479–480
workload capture, 480–481
workload preprocessing, 481–482
workload replay, 482–483
overview, 458–459
SQL Access Advisor, 470–479
SQL Tuning Advisor
configuring, 460–462
DBMS_SQLTUNE, 466–470
overview, 459–460
running, 462
viewing recommendations, 462–466
Database Writer (DBWR), 293
datafile buffer sizing, RMAN, 355t
datafile copies, 87
Datafile Destination page, Integrate Transportable Tablespaces, EM, 510f
DATAFILE parameter, 232
DATAGUARDCONFIG system template, 35t
day of week numbering, 642
DB_BLOCK_CHECKING parameter, 575–576
DB_BLOCK_SIZE parameter, 4, 315t, 645
DB_CREATE_FILE_DEST initialization parameter, 87–88, 315t
DB_CREATE_ONLINE_LOG_DEST_n initialization parameter, 87–88, 315t
DB_FILE_NAME_CONVERT initialization parameter, 315t
DB_FLASHBACK_RETENTION_TARGET initialization parameter, 407
DB_NAME column, DBINC view, 130
DB_NAME initialization parameter, 315t
DB_RECOVERY_FILE_DEST initialization parameter, 78, 87, 315t
DB_RECOVERY_FILE_DEST_SIZE initialization parameter, 89, 91
DB_UNIQUE_NAME parameter, 28
DB_WRITER_PROCESSES parameter, 17
DBA_ADVISOR_FINDINGS data dictionary view, 522
DBA_ADVISOR_LOG data dictionary view, 467
DBA_ADVISOR_RECOMMENDATIONS data dictionary view, 522, 524
DBA_DATA_FILES data dictionary view, 292
DBA_FLASHBACK_ARCHIVE_TABLES data dictionary view, 405
DBA_FLASHBACK_ARCHIVE_TS data dictionary view, 403–404
DBA_RECYCLEBIN data dictionary view, 372–373
DBA_RESUMABLE data dictionary view, 495
DBA_RSRC_CONSUMER_GROUP_PRIVS data dictionary view, 542t
DBA_RSRC_CONSUMER_GROUPS data dictionary view, 542t
DBA_RSRC_GROUP_MAPPINGS data dictionary view, 542t
DBA_RSRC_MANAGER_SYSTEM_PRIVS data dictionary view, 542t
DBA_RSRC_MAPPING_PRIORITY data dictionary view, 542t
DBA_RSRC_PLAN_DIRECTIVES data dictionary view, 542t
DBA_RSRC_PLANS data dictionary view, 542t
DBA_SCHEDULER_JOB_RUN_DETAILS view, 623
DBA_TABLESPACES data dictionary view, 383
DBA_/USER_ADVISOR_TASKS data dictionary view, 467–468
DBA_USERS data dictionary view, 542t
DBCA (Database Creation Assistant), 10, 273, 646
DBID (database identifier), 113–117, 237, 312
DBINC dynamic performance view, 133
DBMS_ADVISOR.EXECUTE_TASK procedure, 523
DBMS_ADVISOR PL/SQL package, 522
DBMS_ADVISOR, using SQL Access Advisor to tune workload, 477–479
DBMS_ADVISOR.CREATE_OBJECT procedure, 523
DBMS_ADVISOR.CREATE_TASK procedure, 523
DBMS_ADVISOR.QUICK_TUNE procedure, 477–478
DBMS_ADVISOR.SET_TASK_PARAMETER procedure, 523
DBMS_FILE_TRANSFER PL/SQL package, 512
DBMS_FILE_TRANSFER procedure, 513–515
DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure, 397
DBMS_HM PL/SQL package, 572
DBMS_RESOURCE_MANAGER PL/SQL package, 535–536
DBMS_SCHEDULE.CREATE_JOB procedure., 596–597
DBMS_SCHEDULER.CREATE_JOB_CLASS procedure, 599
DBMS_SCHEDULER.CREATE_PROGRAM procedure, 597–598
DBMS_SCHEDULER.CREATE_SCHEDULE procedure, 598
DBMS_SCHEDULER.CREATE_WINDOW procedure, 600
DBMS_SQLTUNE, 466–470
DBMS_TTS.TRANSPORT_SET_CHECK procedure, 514
DBMS_WORKLOAD_CAPTURE PL/SQL package, 481
DBMS_WORKLOAD_REPLAY PL/SQL package, 482–483
dbnames, 31
DBWR (Database Writer), 293
DBWR_IO_SLAVES initialization parameter, 357
DDL (Data Definition Language), 281, 330, 389, 399, 494
DEBUG OFF command, 351
DEBUG ON command, 351
debugging, RMAN, 350–351
decimal separators, 642
decision support systems (DSS), 445
dedicated server configuration, 15, 432
DEFAULT keyword, 402
DEFAULT_PLAN, Database Resource Manager, 532–535
DEFINE_CHAIN_RULE procedure, 613
DEFINE_CHAIN_STEP procedure, 612–613
degree of parallelism, 531
DELETE EXPIRED command, 194, 201
DELETE INPUT option, 182
DELETE OBSOLETE command, 139, 194, 199
DELETE SCRIPT command, 125
dependencies for nonzero MEMORY_TARGET, 433t
dependencies for zero MEMORY_TARGET, 434t
dependent transactions, 396–397
Destination Characteristics page, Generate Transport Tablespaces, EM, 506f
destination credentials, 324f
destination directory, 79
detail rows, 348
diagnostic data directory locations, ADR, 559t
DIAGNOSTIC_DEST initialization parameter, 11, 557
dictionary managed tablespaces, 4
dictionary views, Flashback Data Archive, 403–404
differential backups, 172–173
incremental, 171–172
directory structure, ADR, 557, 558f
dirty blocks, 17
disaster recovery
overview, 245–246
performing in NOARCHIVELOG mode, 246
recovery of entire database, 247–248
using incremental backups in NOARCHIVELOG mode, 246–247
DISCRETE_BYTES_PER_SECOND column, 359
disk groups, 18–19
altering, 44–46
architecture of, 39
asmcmd command, 48–53
dynamic rebalancing, 40–42
Enterprise Manager (EM) Database Control, 46–48
fast mirror resync, 43–44
mirroring and failure groups
external redundancy, 39
high redundancy, 40
normal redundancy, 39–40
overview, 38
querying, 30
statistics, 49f
Disk Groups tab, ASM administration page, 48f
disk quota, 402
disk space
overview, 492
reclaiming from tables and indexes using segment shrink functionality
automating Segment Advisor, 520
overview, 518–520
using EM, 525–528
using SQL, 520–525
resumable space allocation
configuring, 494–496
DDL commands, 494
DML commands, 494
for HR user, 497–500
overview, 493–494
SELECT statements, 494
SQL*Loader operations, 494
transportable tablespaces
configuring, 501–504
using EM to transport tablespace, 504–512
using SQL to transport tablespace, 512–517
DML. See Data Manipulation Language
DML commands, resumable space allocation, 494
DROP ANY TABLE system privilege, 379
DROP CATALOG command, 128
DROP DATABASE command, 76t
DROP TABLE . . . PURGE command, 380
DROP TABLESPACE command, 380
DROP USER . . . CASCADE command, 380
DSS (decision support systems), 445
dual mode encryption, 193–194
dump file locations
ADR, 559t
transportable tablespace, 506, 507f
DUMPSET system template, 35t
duplex backups
configuring multiple disk locations for, 180–182
creating, 179–182
overview, 178
read-only tablespaces, 182
DUPLICATE command, 76t, 237, 315
creating backups for, 317
overview, 320–321
duplicate database, 312
creating using RMAN
allocating auxiliary channels, 317–318
configuring auxiliary instance, 314
creating backups for DUPLICATE command, 317
creating initialization parameter file, 315–316
Enterprise Manager (EM), 321–327
establishing network connectivity, 314
overview, 312–313
running RMAN DUPLICATE command, 318–321
starting auxiliary instance in NOMOUNT mode, 316–317
starting source database in MOUNT or OPEN mode, 317
using, 327–329
DURATION argument, 600
dynamic performance views
associated with ASM instances, 29–30
V$ASM_ALIAS, 29t
V$ASM_CLIENT, 29t
V$ASM_DISK, 29t
V$ASM_DISKGROUP, 29t
V$ASM_FILE, 29t
V$ASM_OPERATION, 29t
V$ASM_TEMPLATE, 29t
V$BACKUP_ASYNC_IO, 359
V$BACKUP_DATAFILE, 178
V$BACKUP_SYNC_IO, 359
V$BLOCK_CHANGE_TRACKING, 178
V$DATABASE_BLOCK_CORRUPTION, 577–578
V$DATAFILE, 31
V$DIAG_INFO, 557–558
V$FLASH_RECOVERY_AREA_USAGE, 90
V$HM_CHECK, 572
V$IR_FAILURE, 585
V$IR_MANUAL_CHECKLIST, 585
V$IR_REPAIR, 585
V$IR_REPAIR_SET, 585
V$LOG, 79
V$MEMORY_DYNAMIC_COMPONENTS, 438
V$MEMORY_RESIZE_OPS, 438
V$MEMORY_TARGET_ADVICE, 438
V$PARAMETER, 444
V$PGA_STAT, 445–446
V$PROCESS, 345
V$RSRC_CONSUMER_GROUP, 543
V$SESSION, 345
V$SESSION_LONGOPS, 345
V$SESSTAT, 543
V$SYSSTAT, 543
V$TRANSPORTABLE_PLATFORM, 502–504
dynamic rebalancing, 40–42
E
Edit Resource Plan page, EM, 537f
electronic book, 675
EM. See Enterprise Manager
ENABLED argument, 597
encrypting backups
using dual mode encryption, 193–194
using password encryption, 192–193
using transparent encryption, 190–192
END BACKUP option, 289
endian requirements, 502–504
Enterprise Manager (EM), 313
Alert section, home page, 498, 499f
cloning running database, 326
configuring SQL Tuning Advisor, 460–461
Create Program page, Scheduler, 604–605
Create Window page, 615–617
creating duplicate database using, 321–327
Memory Advisors page, 446, 447f
monitoring AMM, 436–437
Scheduler Windows page, 616f
using SQL Access Advisor to tune workload, 471–477
using to reclaim disk space, 525–528
using to transport tablespace, 504–512
using with Flashback Transaction Query, 392–396
Enterprise Manager (EM) Database Control
configuring backup destination, 140
disk groups and, 46–48
persisting repository credentials, 111f
querying recovery catalog, 198, 199f
Recovery Catalog Settings page, 113, 114f
Recovery Settings window, 88, 89f
RMAN persistent parameters, 138f
error alerts, EM, 564f
error messages
identifying message output, 350
using RMAN DEBUG command, 350–352
EVENT_CONDITION parameter, 604
event-based jobs, 604
EXCLUSIVE value, REMOTE_LOGIN_PASSWORDFILE parameter, 274
EXECUTE privilege, 601
EXECUTE_TASK procedure, 522–523
EXECUTE_TUNING_TASK procedure, 466, 468
exit command, 50t
expdp utility, 127, 512, 514–515
expired backups, 200
export dump file, 70
exporting
data, 70
recovery catalog, 127
external redundancy, 39
F
failure groups
external redundancy, 39
high redundancy, 40
normal redundancy, 39–40
Failure# option, LIST FAILURE command, 580
fast incremental backups
block change tracking, 174, 178
enabling, 175–177
overview, 173
fast mirror resync, 43–44
file system backups, 70
file.incarnation pair, 32
filename formats
alias, 32–33
fully qualified, 31–32
incomplete, 33
numeric, 32
Files page
Generate Transport Tablespaces, EM, 507f
Integrate Transportable Tablespaces, EM, 510f
files, RMAN, 87
FILESPERSET parameter, BACKUP command, 355–356
fine striping, 39
fixed-width character sets, 637
fixed-width multibyte character sets, 639t
flash recovery area, 136–137
querying location, contents, and size of, 90–92
using, 89–92
FLASHBACK ANY TABLE privilege, 399
FLASHBACK ARCHIVE ADMINISTER system privilege, 404
FLASHBACK ARCHIVE object privilege, 404
flashback buffer, 406
Flashback Data Archive
assigning permissions, 404
assigning tables to, 404–405
creating archives, 402–403
dictionary views, 403–404
managing, 404
overview, 401–402
querying, 405–406
configuring, 407
functionality, 284
incomplete recovery, 270
monitoring, 410–412
overview, 406–407
using, 407–410
excluding tablespaces from, 409
overview, 407
performing, 408–409
using guaranteed restore points, 409–410
FLASHBACK DATABASE command, 73, 77t, 408–409
flashback features. See also Flashback Data Archive
Flashback Database
Flashback Query
Flashback Transaction Query
overview, 370
restoring dropped tables from recycle bin
accessing tables, 380–381
bypassing recycle bin, 380
moving objects to recycle bin, 373–375
overview, 371–372
process, 375–378
querying recycle bin, 372–375
space reclamation, 378–379
flashback logs, 34t, 86, 370, 577
flashback parameters, 382–383
Flashback Query
configuring flashback parameters, 382–383
Flashback Version Query, 385–389
using, 383–385
FLASHBACK system template, 35t
FLASHBACK TABLE command, 398–399
Flashback Table operations
configuring, 399
overview, 398
using, 399–400
FLASHBACK TABLE . . . TO BEFORE DROP command, 375–376, 381
Flashback Transaction Query, 392–396
choosing back-out options, 396–398
overview, 389–391
using, 391–392
Flashback Transaction wizard, 393–396, 397f
Flashback Version Query, 385–389
FLASHBACK_TRANSACTION_QUERY data dictionary view, 389–392
FORMAT parameter, 166, 184–185
fractured blocks, 293
FROM keyword, RECOVER command, 280
full backups, 135
overview, 168–170
of two tablespaces, 169–170
fully qualified filenames, 31–32
G
GENERIC_BASELETTER sort order, 655–656
GLOBAL parameter, 121
GLOBAL_BACKUP_DB script, 121, 124
GRANT CATALOG command, 131
Grid Control, 313
groups, 31
guaranteed restore points, 407
H
hardware platforms, 501–502
help command, 50t
HIGH option, LIST FAILURE command, 580
high redundancy, 40
high water mark (HWM), 518–519
historical tracking, 401
hit ratios, buffer cache, 443
host, restoring database onto new, 237–240
HR user, resumable space allocation, 497–500
HWM (high water mark), 518–519
I
idle time limits, 531
image copies, 135
creating, 166–167
recovering, 227
switching to for fast recovery, 229–236
image copy strategy, 227–229
image file backups
backupsets
compressed, 164–166
creating, 163–166
image copies, creating, 166–167
IMMEDIATE command, 247
importing
data, 70
recovery catalog, 127
transportable tablespaces, 507, 509–512, 513f
INACTIVE log file status, 266t
INACTIVE redo log group, recovering from loss of
clearing archived inactive redo log group, 268–269
clearing nonarchived inactive redo log group, 269–270
incident dumps, 559
INCLUDING CONTENTS clause, DROP TABLESPACE command, 380
incomplete database recovery
overview, 284–285
point-in-time (PIT) recovery method, 285–286
time-based, 286–289
incomplete filenames, 33
incomplete recovery
defined, 263
performing using RMAN
creating restore points, 221–223
USERS tablespace, 224–226
inconsistent backups, 134
incremental backups, 135
cumulative, 172–173
differential, 171–172
fast
block change tracking, 174, 178
enabling, 175–177
overview, 173
level 0, 171
overview, 170–171
incrementally updated backups, 161, 169–170
implementing image copy strategy, 227–229
recovering image copies, 227
index segments, 5
reclaiming disk space from with segment shrink functionality
automating Segment Advisor, 520
overview, 518–520
using SQL, 520–525
shrinking, 519
Inherit Options checkbox, Advisor Central page, EM, 471
init>SID<.ora file, 9
initialization parameter files, 9–10
creating, 315–316
setting up for ASM and database instances
accessing, 27
dynamic performance views, 29–30
file types, 33–35
filename formats, 31–33
initialization parameters, 27–29
overview, 26–27
templates, 33–35
initialization parameters, 34t
ASM_DISKGROUPS, 28
ASM_DISKSTRING, 28
ASM_PREFERRED_READ_FAILURE_GROUPS, 29
BACKGROUND_DUMP_DEST, 10–11
BACKUP_TAPE_IO_SLAVES, 358, 360
COMPATIBLE, 501–502
CONTROL_FILE_RECORD_KEEP_TIME, 84, 107, 117
DB_BLOCK_SIZE, 4
DB_CREATE_FILE_DEST, 87–88
DB_CREATE_ONLINE_LOG_DEST_n, 87–88
DB_FLASHBACK_RETENTION_TARGET, 407
DB_RECOVERY_FILE_DEST_SIZE, 89, 91
DB_UNIQUE_NAME, 28
DBWR_IO_SLAVES, 357
DIAGNOSTIC_DEST, 557
DIAGNOSTIC_DEST
USER_DUMP_DEST, 11
INSTANCE_TYPE, 27
LOCK_SGA, 443
LOG_ARCHIVE_DEST_n, 79, 82, 136
LOG_ARCHIVE_MAX_PROCESSES, 18
MEMORY_TARGET, 433–435
PGA_AGGREGATE_TARGET, 434, 443–445
RECYCLEBIN, 371
RESOURCE_MANAGER_PLAN, 534–535, 538
RESUMABLE_TIMEOUT, 494–495
SHARED_POOL_SIZE, 13
STATISTICS_LEVEL, 348
STREAMS_POOL_SIZE, 14
UNDO_MANAGEMENT, 382
UNDO_RETENTION, 382
WORKAREA_SIZE_POLICY, 15
INSTANCE_TYPE parameter, 27
Integrate Transportable Tablespaces, EM, 507, 509–512, 513f
INTERVAL DAY TO SECOND column syntax, 600
I/O channels, 162
IO_COUNT column, 359
J
Java Virtual Machine (JVM), 14
Jnnn processes, 595
job chains, 594
creating chain objects, 611–612
defining chain rules, 613
defining chain steps, 612–613
monitoring, 614–615
overview, 611
starting chain, 614
job classes, creating with Scheduler, 617–619
job commands, 75
job prioritization, 594
job slaves, 595
JOB_ACTION argument, 597
JOB_CLASS argument, 599
JOB_TYPE argument, 597
jobs, Scheduler, 599
JVM (Java Virtual Machine), 14
K
KEEP UNTIL clause, 184
L
Language and Character Set File Scanner, 647
language-dependent behavior, customizing
character sets, 637–638, 645–648
client-side environment settings, 649–650
within database, 648
instance level, 649
language support, 638–641
NLS settings, 643–644
session-level globalization settings, 651–652
statement globalization settings, 652–654
territory support, 641–643
LARGE_POOL_SIZE parameter, 14, 28, 357
LearnKey Technical Support, 675
level 1 backup, 228–229
LGWR (Log Writer), 14, 17, 266, 431
lightweight jobs, Scheduler, 608–610
LIST command, 77t, 123, 194, 195–196
LIST FAILURE command, 580
LIST SCRIPT NAMES command, 123
little-endian systems, 502
local destinations, 80–82
Locale Builder, 656–657
locally managed tablespaces, 4
LOCK_SGA initialization parameter, 443
log command line option, 74
log group member failures, recovering from, 266–268
log group status, 265–266
Log Writer (LGWR), 14, 17, 266, 431
LOG_ARCHIVE_DEST_n initialization parameter, 79, 82, 136
LOG_ARCHIVE_MAX_PROCESSES initialization parameter, 18
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter, 82
logging levels, 623
logical memory structures, 12f, 430f
logical storage structures, 3f
blocks, 4
extents, 5
segments, 5–6
tablespaces, 3–4
login page, ASM instance, 47f
LOGON trigger, 496
LOGON_USER column, FLASHBACK_TRANSACTION_QUERY, 390t
LONG_WAIT_TIME_TOTAL column, 359
LONG_WAITS column, 359
LOW option, LIST FAILURE command, 580
lsct command, 50t
lsdsk command, 50t
M
MANAGE SCHEDULER privilege, 601
MANDATORY parameter, 82
manual backup, identifying files for, 290–291
manual rollback segments, 5–6
manually resynchronizing recovery catalog, 120
MasterExam, 674
MAXOPENFILES parameter, 355–356
MAXPIECESIZE parameter, 356
MAXSIZE parameter, 7
md_backup command, 50t
md_restore command, 50t
Mean Time to Recovery (MTTR), 18
media failure data loss, 262
memory management
Automatic Memory Management (AMM)
configuring, 433–436
memory structures, 429–433
monitoring, 436–438
overview, 428–429
automatic PGA memory management, 443–447
configuring, 444–445
managing PGA memory, 445–447
understanding PGA components, 443–444
overview, 428
SGA parameters
switching to ASMM, 440–442
tuning SGA components, 443
understanding Automatic Shared Memory Management (ASSM), 439–440
Memory Manager (MMAN) background process, 439
memory structures
background processes
archiver process (ARCn), 17–18
checkpoint process (CKPT), 18
database writer process (DBWn), 17
Log Writer (LGWR), 17
overview, 15–16
process monitor (PMON), 16–17
recoverer process (RECO), 18
system monitor process (SMON), 16
program global area (PGA), 14–15
Program Global Area (PGA), 432
software code areas, 15, 432–433
System Global Area (SGA)
MEMORY_MAX_TARGET initialization parameter, 433, 436
MEMORY_TARGET parameter, 12, 15, 433–435
metadata, RMAN
overview, 106–107
retrieving, 123–124
using control file for, 107
using recovery catalog for, 107–108
mirroring groups
external redundancy, 39
high redundancy, 40
normal redundancy, 39–40
mkalias command, 50t
mkdir command, 50t
MMAN (Memory Manager) background process, 439
monitoring
Automatic Memory Management (AMM), 436–438
backup, 69
Flashback Database, 410–412
RMAN sessions and jobs
leveraging error logs and messages, 350–352
using V$SESSION and V$PROCESS views, 345–348
using V$SESSION_LONGOPS view, 348–350
MOUNT mode, 217
applying Redo Log Files, 280
clearing redo log files, 269
starting source database in, 317
MTTR (Mean Time to Recovery), 18
MULTI_SECTION column, 189
multibyte character sets, 637–638
multiplexed control files, 296
multiplexing, 7–8
multisection backups
data dictionary views, 189–190
section size
specifying, 187–188
validating backups with, 188–189
N
National Character Set, 645–646
National Language Support (NLS), 636
character sets
linguistic sorting and selection, 654–656
Locale Builder, 656–657
time zones, 658–660
nesting chains, 612
network configuration files, location and passwords, 325f
network connectivity, establishing, 314
nid command, 113
NLS. See National Language Support
NLS_CALENDAR variable, 643
NLS_COMP variable, 643
NLS_CURRENCY variable, 643
NLS_DATABASE_PARAMETERS view, 648
NLS_DATE_FORMAT variable, 643
NLS_DUAL_CURRENCY variable, 643
NLS_INSTANCE_PARAMETERS view, 649
NLS_ISO_CURRENCY variable, 643
NLS_LANG environment variable, 649
NLS_LENGTH_SEMANTICS variable, 643
NLS_NCHAR_CONV_EXCP variable, 643
NLS_NUMERIC_CHARACTERS variable, 643
NLS_SORT parameter, 655–656
NLS_TERRITORY variable, 643
NLS_TIMESTAMP_FORMAT variable, 643
NLS_TIMESTAMP_TZ_FORMAT variable, 643
NOARCHIVELOG mode, 8, 216, 245–246, 290
backing up database in, 291
performing recovery in, 246
using incremental backups in, 246–247
NOCASCADE option, DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure, 396–397
NOCASCADE_FORCE option, DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure, 397
nocatalog command line option, 74
NOFILENAMECHECK option, 321
NOKEEP parameter, 186
NOMOUNT mode, 316–317
nonarchived inactive redo log group, clearing, 269–270
NONCONFLICT_ONLY option, DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure, 397–398
noncritical datafiles, performing complete recovery of, 217–220
noncritical failures, defined, 262
NONE value, REMOTE_LOGIN_PASSWORDFILE parameter, 274
nonzero MEMORY_TARGET dependencies, 433t
NOREDO command, 247
NORMAL command, 247
normal redundancy, 39–40
number format masks, 654
numeric filenames, 32
O
offline backups, 71–72
OG_FILE_NAME_CONVERT initialization parameter, 315t
OLTP (online transaction processing), 439, 445
OMF (Oracle Managed Files), 4, 136, 324
online logs, 34t
online redo log files, 8, 71, 86, 291
online table redefinition, 518
online transaction processing (OLTP), 439, 445
ONLINELOG system template, 35t
open database backups, 161
open database recovery, 283–284
OPEN mode, starting source database in, 317
OPEN RESTRICTED option, 321
operating system (OS) authentication, 271–272
operating system groups, 25
OPERATION column, FLASHBACK_TRANSACTION_QUERY, 390t
Options page, Integrate Transportable Tablespaces, EM, 511f
ORA$AUTOTASK_SUB_PLAN, 533–534
ORA$DIAGNOSTICS, 533
ORA_ROWSCN pseudocolumn, 408
ORA-598 error messages, 563–564
ORA-1578 errors, 575
Oracle Data Guard, 9
Oracle Database Resource Manager, 492
Oracle Managed Files (OMF), 4, 136, 324
Oracle Support, 556
Oracle trace file, 350
Oracle Universal Installer (OUI), 24f, 273
ORACLE_SID variable, 316
orapwd command, 273
OS (operating system) authentication, 271–272
OTHER_GROUPS, 533
OUI (Oracle Universal Installer), 24f, 273
out-of-place restore, 385
owners
recovery catalog, creating, 110
virtual private catalog
creating, 130–131
granting permissions to, 131
P
parallelizing backup sets, 353–354
PARAMETERFILE system template, 35t
PARTIAL option, BACKUP DURATION parameter, BACKUP command, 357
password encryption, 192–193
password files
authentication methods, 271–273
re-creating, 272–276
PAUSE_REPLAY procedure, 483
pending areas, 536
permissions, Flashback Data Archive, 404
persistent lightweight jobs, 608
persisting repository credentials, 111f
persisting RMAN settings
device type, 139–140
overview, 137–138
retention policies, 138–139
PFILEs, 9
PGA. See Program Global Area
PGA_AGGREGATE_TARGET parameter, 15, 434, 443–445
physical backups, 68
offline backups, 71–72
online backups, 72–73
overview, 70–71
physical storage structures, 6f
alert log files, 10–11
archived log files, 8–9
backup files, 11
control files, 8
datafiles, 7
initialization parameter files, 9–10
overview, 6–7
redo log files, 7–8
trace log files, 10–11
PIT (point-in-time) recovery, 284, 285–286
platform independence, 162
platforms, hardware, 501–502
PL/SQL packages
DBMS_ADVISOR, 522
DBMS_FILE_TRANSFER, 512
DBMS_HM, 572
DBMS_RESOURCE_MANAGER, 535–536
DBMS_WORKLOAD_CAPTURE, 481
DBMS_WORKLOAD_REPLAY, 482–483
PMON (process monitor), 16–17
point-in-time (PIT) recovery, 284, 285–286
precedence, globalization settings, 645
preconfigured jobs, Scheduler, 619–621
PRINT command, 123–124
PRIORITY argument, 601
privileges, Scheduler, 601
Problem Details page, EM, 565, 566f, 568
problem keys, 564
process monitor (PMON), 16–17
Program Global Area (PGA), 14–15, 429
automatic memory management
components, 443–444
configuring, 444–445
managing, 445–447
overview, 432
programs, Scheduler, 597–598
pseudocolumns, Flashback Version Query, 387t
purge clause, 404
PURGE command, 378–379
PURGE DBA_RECYCLEBIN command, 375, 379
PURGE option, 330
PURGE RECYCLEBIN command, 375
PURGE_LOG job, 624
pwd command, 50t
Q
querying
disk groups and available raw devices, 30
Flashback Data Archive, 405–406
location, contents, and size of flash recovery area, 90–92
retention policies, 84–85
QUEUE_SPEC parameter, 604
Quick Packaging, EM, 565–568
QUOTA keyword, 402
R
RAC (Real Application Cluster), 19
RATE parameter, CHANNEL command, 356
raw devices, querying, 30
RBAL (rebalancer), 20
RC_ views, 107
RDBMS instances, finding new ASM-related processes in, 20–23
READ ONLY mode, 223
read phase, 353
read-only tablespaces, 182, 517
Real Application Cluster (RAC), 19
rebalancer (RBAL), 20
RECO (recoverer process), 18
Recommendation Details for Tablespace page, Segment Advisor, 527f
recommendation summary, SQL Access Advisor, 476–477
Recommendations Options page, SQL Access Advisor, 473
recommendations, SQL Tuning Advisor, 465–466f
RECOVER DATABASE command, 248
RECOVER DATABASE UNTIL CANCEL command, 246
recoverer process (RECO), 18
recovery. See user-managed backup and recovery
Recovery Advice page, Recovery Advisor, EM, 584f
allocating channels to use in backing up, 141
backing up, 125–126
backup optimization, configuring, 141–142
creating
configuring database, 109
initializing, 111–112
overview, 108
owner, 110
dropping, 128
exporting and importing, 127
metadata
overview, 106–107
using control file for, 107
using recovery catalog for, 107–108
recovering lost, 126–127
settings
control file autobackups, 140–141
destinations, 136–137
persisting settings, 137–140
types of RMAN backups, 134–136
stored scripts
executing, 121–123
managing, 124–125
retrieving metadata, 123–124
synchronizing
cataloging additional backup files, 118–120
Database Identifier (DBID), changing, 113–117
manually resynchronizing, 120
registering databases, 112–113
unregistering database, 117–118
upgrading, 128–129
virtual private catalogs
creating, 131–132
overview, 129–130
owners, 130–131
using, 132–133
Recovery Catalog Settings page, EM, 113, 114f
recovery, database, 69
Recovery Manager (RMAN), 321–327
archival backups
managing, 185–186
overview, 183
performing, 184–185
archive log backup pieces, 34t
archive log file destinations
ARCHIVELOG mode, configuring, 78–80
identifying, 80–82
leveraging multiple, 80–82
backup files, 11
commands, 74–77
compressing backups, 190
configuring for asynchronous I/O
monitoring, 359
monitoring synchronous I/O, 359–360
overview, 358
control files, 8
creating duplicate database using
allocating auxiliary channels, 317–318
configuring auxiliary instance, 314
creating backups for DUPLICATE command, 317
creating initialization parameter file, 315–316
establishing network connectivity, 314
overview, 312–313
running RMAN DUPLICATE command, 318–321
starting auxiliary instance in NOMOUNT
mode, 316–317
starting source database in MOUNT or OPEN
mode, 317
datafile backup pieces, 34t
datafile copies, 34t
duplex backups
configuring multiple disk locations for, 180–182
creating, 179–182
overview, 178
read-only tablespaces, 182
encrypting backups
using dual mode encryption, 193–194
using password encryption, 192–193
using transparent encryption, 190–192
fast incremental backups
block change tracking, 174, 178
enabling, 175–177
overview, 173
flash recovery area, 87
configuring, 85–89
querying location, contents, and size of, 90–92
using, 89–92
identify situations that require tablespace point-in-time recovery (TSPITR), 330–331
image file backups
backupsets, creating, 163–166
image copies, creating, 166–167
incremental backup pieces, 34t
incrementally updated backups
implementing image copy strategy, 227–229
recovering image copies, 227
invoking, 74
logical backups, 69–70
maintaining backups
overview, 194
using CROSSCHECK command, 200–201
using DELETE command, 199
monitoring sessions and jobs
leveraging error logs and messages, 350–352
using V$SESSION and V$PROCESS views, 345–348
using V$SESSION_LONGOPS view, 348–350
multiplexing, 354–355
multisection backups
data dictionary views, 189–190
specifying section size, 187–188
validating with section size, 188–189
online backups, 72–73
perform automated tablespace point-in-time recovery (TSPITR), 331–334
identifying objects lost after TSPITR, 332–333
performing automated TSPITR, 333–334
verifying tablespace dependencies, 332
perform incomplete recovery using
creating restore points, 221–223
USERS tablespace, 224–226
performing complete recovery using
critical datafile, 220–221
noncritical datafile, 217–220
overview, 216
RMAN RESTORE and RECOVER commands, 217
performing disaster recovery
overview, 245–246
performing recovery in NOARCHIVELOG mode, 246
performing recovery of entire database, 247–248
using incremental backups in NOARCHIVELOG mode, 246–247
offline backups, 71–72
online backups, 72–73
overview, 70–71
recover using backup control file
restoring control file from autobackup, 241–245
restoring SPFILE from autobackup, 241
recovery catalog
allocating channels to use in backing up, 141
backing up, 125–126
backup optimization, configuring, 141–142
creating, 108–112
dropping, 128
exporting and importing, 127
metadata, 106–108
recovering lost, 126–127
settings, 134–141
stored scripts, 121–125
synchronizing, 112–120
upgrading, 128–129
virtual private catalogs, 129–133
reporting on backups
overview, 194
using LIST command, 195–196
using REPORT command, 196–199
restoring database onto new host, 237–240
retention policies
overview, 83–85
querying and changing, 84–85
switch to image copies for fast recovery, 229–236
tuning
configuring LARGE_POOL_SIZE parameter, 357
identifying backup and restore steps, 353
overview, 352–353
parallelizing backup sets, 353–354
RMAN multiplexing, 354–355
tuning BACKUP command, 356–357
tuning RMAN channels, 356
using duplicate database, 327–329
using to create backups, 161–163
whole database backups
full backups, 168–170
incremental backups, 170–173
overview, 167–168
recovery set, defined, 330
Recovery Settings window, 88, 89f
Recovery Writer (RVWR) background process, 406
RECOVERY_CATALOG_OWNER role, 129
recovery-related files, 278–279
recycle bin, 370
accessing tables in, 380–381
bypassing, 380
moving objects to, 373–375
overview, 371–372
querying, 372–375
restoring tables from
keeping original dependent object names, 376–378
overview, 375–376
space reclamation
automatic, 378
manual, 378–379
RECYCLEBIN initialization parameter, 371
redo log files, 7–8, 17–18, 78–79, 279–283
redo log group, 265–271
log group status, 265–266
recovering from log group member failures, 266–268
recovering from loss of, 268–271
lost ACTIVE redo log group, 270
lost CURRENT redo log group, 270–271
lost INACTIVE redo log group, 268–270
redo log stream, 390
redundancy retention policy, 83, 138
REGISTER DATABASE command, 77t
remap command, 50t
remapping objects, 509
remote destinations, 82
REMOTE_LOGIN_PASSWORDFILE parameter, 274
RENAME TO clause, 376
Repair Advisor, SQL, 568, 569f
REPAIR FAILURE command, 77t, 580
REPEAT_INTERVAL argument, 598–599
REPLACE [GLOBAL] SCRIPT command, 124
REPORT command, 77t, 194, 196–199
REPORT NEED BACKUP command, 194
REPORT OBSOLETE command, 194
REPORT SCHEMA...AT command, 108
REPORT_TUNING_TASK procedure, 466
reporting on backups
overview, 194
using LIST command, 195–196
using REPORT command, 196–199
repository credentials, persisting, 111f
RESETLOGS command, 223, 248, 271
RESETLOGS option, 286, 331, 409
resource consumer groups, 529
resource management, Database Resource Manager
allocation methods, 531
consumer groups, 536–537
DEFAULT_PLAN, 532–535
methods, 537–538
monitoring, 543
resource plans, 535–536, 538–541
terminology, 529–530
views, 541–542
Resource Manager Statistics page, EM, 543
resource plan directives, 530
resource plans, 529–530, 535–536, 538–541, 622
Resource Plans, EM, 532f
RESOURCE_CONSUMER_GROUP argument, 599
RESOURCE_MANAGER_PLAN initialization parameter, 534–535, 538
RESOURCE_PLAN argument, 600
restoration, 69
RESTORE CONTROLFILE FROM AUTOBACKUP command, 248
RESTORE DATABASE command, 248
restore points, 221–223
guaranteed, 407
RESTORE SPFILE FROM AUTOBACKUP command, 247
resumable space allocation, 492
configuring, 494–496
DDL commands, 494
DML commands, 494
for HR user, 497–500
overview, 493–494
SELECT statements, 494
SQL*Loader operations, 494
resumable statements, 493
RESUMABLE system privilege, 495
RESUMABLE_TIMEOUT initialization parameter, 494–495
RESUME_REPLAY procedure, 483
RESYNC CATALOG command, 120
resynchronizing recovery catalog, manually, 120
RETENTION clause, 402
retention policies, 138–139
overview, 83–85
querying and changing, 84–85
Review page
Generate Transport Tablespaces, EM, 508f
Integrate Transportable Tablespaces, EM, 512f
Review page, SQL Access Advisor, 474–476
REVOKE command, 26
rm command, 50t
rmalias command, 50t
RMAN. See Recovery Manager
RMAN BACKUP command, 136
RMAN CATALOG START WITH command, 127
RMAN DEBUG command, 350–352
RMAN default tablespace, 109
RMAN DUPLICATE command, 318–321
RMAN RECOVER . . . BLOCK command, 577–578
RMAN RECOVER command, 218
RMAN RESTORE command, 218
RMAN SET NEWNAME, using with fast switch, 236
rollback segments, 5
roll-forward operation, 7
root directory, ADR, 557
row movement, 399
ROW_ID column, FLASHBACK_TRANSACTION_QUERY, 390t
ROWID values, 520–521
Run Checkers recommendation page, EM, 573f
RUN command, 77t
RUN_CHAIN procedure, 614
RUN_JOB procedure, 607–608
RVWR (Recovery Writer) background process, 406
S
SAVEFOREVER tag, 186
sbt devices, 139
Schedule page
Generate Transport Tablespaces, EM, 508f
Integrate Transportable Tablespaces, EM, 511f
Schedule page, SQL Access Advisor, 473, 474f
Schedule SQL Tuning Advisor page, EM, 461, 462–463f
Scheduler
creating job classes, 617–619
creating windows, 615–617
event-based jobs, 604
job chains
creating chain objects, 611–612
defining chain rules, 613
defining chain steps, 612–613
monitoring, 614–615
overview, 611
starting chain, 614
jobs, 599
lightweight jobs, 608–610
overview, 595–597
preconfigured jobs, 619–621
privileges, 601
programs, 597–598
programs and schedules, 604–607
schedules, 598–599
time-based jobs, 602–603
using Advanced Scheduler to prioritize jobs, 621–624
windows, 600–601
Scheduler Job Classes page, EM, 618f
Scheduler Jobs page, EM, 620f
Scheduler Windows page, EM, 616f
SCHEDULER_ADMIN role, 601
Schema objects page, Segment Advisor, EM, 526f
SCN-based incomplete recovery, 286
SCNs (System Change Numbers), 18, 134, 217, 247, 269, 384, 431
Scope page, Segment Advisor, EM, 525
scripts, RMAN, 162
section size
specifying, 187–188
validating backups with, 188–189
SECTION SIZE parameter, 187–189
SECTION_SIZE column, 189
Segment Advisor, automating, 520
Segment Advisor, EM, 518, 525–526, 527–528f
segment shrink functionality, 492, 525–528
reclaiming disk space from tables and indexes using
automating Segment Advisor, 520
overview, 518–520
using SQL, 520–525
SELECT statements, 380–381, 402, 494
Select Tablespaces page, Generate Transport Tablespaces, EM, 505f
server parameter file (SPFILE), 9–10
creating backup with asmcmd command, 52–53
loss susceptibility, 241
restoring from autobackup, 241
using auxiliary instance to create, 316–317
server-managed backups
backing up database in ARCHIVELOG mode, 292–293
backing up database in NOARCHIVELOG mode, 291
identifying files for manual backup, 290–291
overview, 289–290
SERVICE argument, 599
service requests
creating and tracking SR, 565–568
implementing repairs, 568–569
using custom packaging, 569–571
session memory, 444
session-level globalization settings, 651–652
SET COMMAND ID command, 347
SET COMMAND option, 347
SET DBID command, 247
SET ENCRYPTION command, 192–194
SET NEWNAME command, 229, 236, 240
SET SQLPROMPT command, 327
SET_ATTRIBUTE procedure, 622–623
SET_TASK_PARAMETER procedure, 522–523
SET_TUNING_TASK_PARAMETER procedure, 468
seven-bit character sets, 638
SGA. See System Global Area
SGA_MAX_SIZE parameter, 12
SGA_TARGET parameter, 12, 434, 439–442
shared memory structures, 11–12
shared password files, 274
shared server configuration, 15, 432
SHARED value, REMOTE_LOGIN_PASSWORDFILE parameter, 274
SHARED_POOL_SIZE initialization parameter, 13
SHARED_POOL_SIZE parameter, 442
SHORT_WAIT_TIME_TOTAL column, 359
SHOW ALL command, 141
show hm_run command, 573–574
SHOW PARAMETER TARGET command, 435
Show SQL button, Review page, SQL Access Advisor, 474–476
SHUTDOWN ABORT command, 36
single-byte character sets, 637, 639t
SKIP READONLY option, BACKUP command, 182
SKIP READONLY option, DUPLICATE command, 321
SKIP TABLESPACE option, 321
SMON (system monitor process), 16
snapshots, database, 183
SOFAR column, 349–350
software code areas, 15, 432–433
sort orders, 640–641
source instance, 313
source server, host credentials for, 323f
sources, SQL Access Advisor, 471, 472f
space pressure, 380
SPFILE. See server parameter file
SQL
using to reclaim disk space, 520–525
using to transport tablespace, 512–517
SQL Access Advisor, 458, 471–477
using with DBMS_ADVISOR, 477–479
understanding, 470–471
SQL Advisors page, EM, 460, 461f
SQL command, 77t
SQL profiling, 459
SQL statements, 431, 444, 652–653
configuring, 460–462
DBMS_SQLTUNE, 466–470
overview, 459–460
running, 462
viewing recommendations, 462–466
SQL*Loader operations, 494
standalone commands, 74–75
START WITH option, 118
START_SCN column, FLASHBACK_TRANSACTION_QUERY, 390t
START_TIMESTAMP column, FLASHBACK_TRANSACTION_QUERY, 390t
STARTUP command, 77t
STARTUP FORCE NOMOUNT command, 248
STARTUP NOMOUNT command, 36, 247
STARTUP RESTRICT command, 36
statement globalization settings, 652–654
statistics analysis, 459
statistics, table, 399
STATISTICS_LEVEL initialization parameter, 348
storage mechanisms, 23f
stored scripts, RMAN
executing, 121–123
managing, 124–125
retrieving metadata, 123–124
STREAMS_POOL_SIZE initialization parameter, 14
striping, 39
structure analysis, 459
substitution variables, 164
Support Workbench
managing service requests
creating and tracking SR, 565–568
implementing repairs, 568–569
using custom packaging, 569–571
understanding alerts, problems, and incidents, 563–565
using Health Monitor, 571–574
Support Workbench home page, 570f
suspended statements, 493
synchronizing recovery catalog
cataloging additional backup files, 118–120
Database Identifier (DBID), changing, 113–117
manually resynchronizing, 120
registering databases, 112–113
unregistering database, 117–118
synchronous I/O, 359–360
SYS_GROUP, 533
SYSASM privilege, 25–27, 273, 274
SYSAUX recommendations, Automatic Segment Advisor, 528f
SYSDBA privileges, 25, 27, 49, 273
SYSOPER privileges, 27, 273, 274
System Change Numbers (SCNs), 18, 134, 217, 247, 269, 384, 431
System Global Area (SGA), 9, 73
manually configuring parameters
Automatic Shared Memory Management (ASSM), 439–440
switching to, 440–442
tuning components, 443
system monitor process (SMON), 16
SYSTEM tablespace, 3–4, 109, 278
T
TABLE_NAME column, FLASHBACK_TRANSACTION_QUERY, 390t
TABLE_OWNER column, FLASHBACK_TRANSACTION_QUERY, 390t
tables, 3
reclaiming disk space from with segment shrink functionality, 525–528
automating Segment Advisor, 520
overview, 518–520
using SQL, 520–525
restoring dropped from recycle bin
accessing tables, 380–381
bypassing recycle bin, 380
moving objects to recycle bin, 373–375
overview, 371–372
process, 375–378
querying recycle bin, 372–375
space reclamation, 378–379
TABLESPACE parameter, 232
tablespace point-in-time recovery (TSPITR), 312
identifying situations that require, 330–331
performing automated, 331–334
tablespaces, 3–4
autoextensible, 378
backup mode, 71
backup mode and, 293
bigfile, 186–187
dependencies, verifying, 332
excluding from Flashback Database, 409
full backups of two, 169–170
to move recovery catalog schemas, 127
overview, 10
read-only, backing up, 182
RMAN default, 109
transportable, 492
UNDO, 109
tape manager support, 162
TARGET argument, 313
target command line option, 74
target time, defined, 330
tempfile
losing, 263–264
starting database without, 265
TEMPFILE system template, 35t
templates
alias filenames with, 32–33
ASM, 33–35
incomplete filenames with, 33
temporary segments, 5
temporary tablespaces, 4
testing, backup plan, 69
throughput bottlenecks, 353
time windows, 40–41
time zones, 658–660
time-based incomplete database recovery, 286–289
time-based incomplete recovery, 285
time-based jobs, Scheduler, 602–603
TIMESTAMP WITH LOCAL TIME ZONE data type, 658
TIMESTAMP WITH TIME ZONE data type, 658
TO_CHAR function, 653
TO_DATE function, 653
TO_NCHAR function, 653
TO_NUMBER function, 653
Top Activity page, EM, 461, 463f
TOTALWORK column, 349–350
trace log files, 10–11
tracking files, 173–175
tracking, historical, 401
transaction recovery, 277
TRANSACTIONAL command, 247
transparent encryption, 190–192
TRANSPORT TABLESPACE command, 77t
Transport Tablespaces start page, EM, 504, 505f, 509f
transportable tablespaces, 127, 492, 504–512
configuring
determining compatibility requirements, 501–502
determining endian requirements, 502–504
using SQL to transport tablespace, 512–517
using SQL to transport tablespace, transport tablespace
using SQL and PL/SQL, 513–517
triggers, 496
TRUNCATE TABLE statement, 312
TS_PITR_CHECK data dictionary view, 332
TS_PITR_OBJECTS_TO_BE_DROPPED data dictionary view, 332
TSPITR. See tablespace point-in-time recovery
TUNE_MVIEW procedure, 470
tuning
components, 443
RMAN
configuring LARGE_POOL_SIZE parameter, 357
identifying backup and restore steps, 353
overview, 352–353
parallelizing backup sets, 353–354
RMAN multiplexing, 354–355
tuning BACKUP command, 356–357
tuning RMAN channels, 356
SGA components, 443
U
undo segments, 5–6
undo space, 531
UNDO_CHANGE# column, FLASHBACK_TRANSACTION_QUERY, 390t
UNDO_MANAGEMENT parameter, 382
UNDO_RETENTION parameter, 382
UNDO_SQL column, FLASHBACK_TRANSACTION_QUERY, 390t, 392
Unicode character sets, 638, 646
unique_id portion, recycle bin names, 372
Universal Installer screen, 23f
UNRECOVERABLE DATAFILE keywords, 270
UNREGISTER command, 117
unregistering databases, 117–118
UNUSED log file status, 266t
UNUSED status, 269
UPGRADE CATALOG command, 128–129
upgrading recovery catalog, 128–129
Use Database Area And Flash Recovery Area radio button, 324
user error data loss, 262
USER_RECYCLEBIN data dictionary view, 372–373
USER_RESUMABLE data dictionary view, 495
user-managed backup and recovery
complete database recovery
closed database recovery, 277–283
open database recovery, 283–284
overview, 276–277
control file
backing up in ARCHIVELOG mode, 294–296
recovering, 296–298
identifying need for backup mode, 293–294
incomplete database recovery
overview, 284–285
point-in-time (PIT) recovery method, 285–286
time-based, 286–289
password file
authentication methods, 271–273
re-creating, 272–276
redo log group
log group status, 265–266
recovering from log group member failures, 266–268
recovering from loss of, 268–271
tempfile
losing, 263–264
starting database without, 265
user-managed and server-managed backups
backing up database in ARCHIVELOG mode, 292–293
backing up database in NOARCHIVELOG
mode, 291
identifying files for manual backup, 290–291
overview, 289–290
USERS tablespace
incomplete recovery using RMAN, 224–226
restore and recover, 219–220
user-specified RMAN trace files, 350
USING FILE clause, 175
UTF8 character set, 646
V
V$ADVISOR_PROGRESS data dictionary view, 467–468
V$ASM_ALIAS dynamic performance view, 29t
V$ASM_CLIENT dynamic performance view, 29t
V$ASM_DISK dynamic performance view, 29t
V$ASM_DISKGROUP dynamic performance view, 29t
V$ASM_FILE dynamic performance view, 29t
V$ASM_OPERATION dynamic performance view, 29t
V$ASM_TEMPLATE dynamic performance view, 29t
V$BACKUP_ASYNC_IO dynamic performance view, 359
V$BACKUP_DATAFILE dynamic performance view, 178
V$BACKUP_SYNC_IO dynamic performance view, 359
V$BLOCK_CHANGE_TRACKING dynamic performance view, 178
V$DATABASE_BLOCK_CORRUPTION dynamic performance view, 577–578
V$DATAFILE dynamic performance view, 31, 292
V$DIAG_INFO dynamic performance view, 557–558
V$FLASH_RECOVERY_AREA_USAGE dynamic performance view, 90
V$FLASHBACK_DATABASE_LOG view, 410–411
V$HM_CHECK dynamic performance view, 572
V$IR_FAILURE dynamic performance view, 585
V$IR_MANUAL_CHECKLIST dynamic performance view, 585
V$IR_REPAIR dynamic performance view, 585
V$IR_REPAIR_SET dynamic performance view, 585
V$LOG dynamic performance view, 79, 266t, 268
V$LOGFILE dynamic performance view, 267, 268
V$MEMORY_DYNAMIC_COMPONENTS dynamic performance views, 438
V$MEMORY_RESIZE_OPS dynamic performance views, 438
V$MEMORY_TARGET_ADVICE dynamic performance views, 438
V$NLS_VALID_VALUES view, 638–639, 641–642
V$PARAMETER dynamic performance view, 444
V$PGA_STAT dynamic performance view, 445–446
V$PROCESS view, 345–348
V$PWFILE_USERS dynamic performance view, 275
V$RECOVER_FILE dynamic performance view, 277, 278, 283
V$RECOVERY_FILE_DEST dynamic performance view, 90, 136
V$RECOVERY_LOG dynamic performance view, 277, 278, 283
V$RSRC_CONSUMER_GROUP dynamic performance view, 543
V$SESSION view, 345–348
V$SESSION_LONGOPS dynamic performance views, 345
V$SESSION_LONGOPS view, 348–350
V$SESSTAT dynamic performance view, 543
V$SYSSTAT dynamic performance view, 543
V$TRANSPORTABLE_PLATFORM dynamic performance view, 502–504
VALIDATE command, 77t, 187–189
VALIDATE_PENDING_AREA procedure, 536
validation, 353
variable width character sets, 637, 639t
variables, substitution, 164
varying-width multibyte character sets, 639t
vendor trace files, 350
version portion, recycle bin names, 372
VERSION_END{SCN|TIME} pseudocolumn, 387t
VERSIONS BETWEEN clause, 387
VERSIONS clause, 388–389
VERSIONS_OPERATION pseudocolumn, 387t
VERSIONS_START{SCN|TIME} pseudocolumn, 387t
VERSIONS_XID pseudocolumn, 387t
View Alert Log Contents page, EM, 562f
View and Manage Failures page EM, 579f, 582f
View Resource Plan page, EM, 541f
virtual private catalogs
creating, 131–132
overview, 129–130
owners
creating, 130–131
granting permissions to, 131
using, 132–133
W
wallet-based encryption, 190–192
whole database backups
full backups, 168–170
incremental backups
cumulative, 172–173
differential, 171–172
level 0, 171
overview, 170–171
overview, 167–168
WORKAREA_SIZE_POLICY initialization parameter, 15
workload
capture, 480–481
preprocessing, 481–482
replay, 482–483
using SQL Access Advisor to tune
with DBMS_ADVISOR, 477–479
overview, 470–471
workload sources, SQL Access Advisor, 471, 472f
workload, tuning, 471–477
write phase, 353
X
XID column, FLASHBACK_TRANSACTION_QUERY, 390t
XTRANSPORT system template, 35t
Z
zero MEMORY_TARGET dependencies, 434t
ZLIB algorithm, 190