Sumário Itens Encontrados: 506PART IDatabase Architecture1 Getting Started with the Oracle Architecture 32 Upgrading to Oracle Database 12c 513 Planning and Managing Tablespaces 614 Physical Database Layouts and Storage Management 77PART IIDatabase Management5 Developing and Implementing Applications 1176 Monitoring Space Usage 1577 Managing Transactions with Undo Tablespaces 2098 Database Tuning 2459 In-Memory Option 29310 Database Security and Auditing 30311 Multitenant Database Architecture 371PART IIIHigh Availability12 Real Application Clusters 44113 Backup and Recovery Options 45314 Using Recovery Manager (RMAN) 481vii15 Oracle Data Guard 53716 Miscellaneous High Availability Features 559PART IVNetworked Oracle17 Oracle Net 57718 Managing Large Databases 61119 Managing Distributed Databases 657Index 697An Overview of Databases and Instances 4Databases 5Instances 5Oracle Logical Storage Structures 6Tablespaces 6Blocks 7Extents 7Segments 7Oracle Logical Database Structures 8Tables 9Constraints 16Indexes 18Views 20Users and Schemas 22Profiles 22Sequences 23Synonyms 23PL/SQL 23External File Access 24Database Links and Remote Databases 25Oracle Physical Storage Structures 26Datafiles 27Redo Log Files 27ixx Oracle Database 12c DBA HandbookControl Files 28Archived Log Files 28Initialization Parameter Files 28Alert and Trace Log Files 29Backup Files 30Oracle Managed Files 30Password Files 30Multiplexing Database Files 31Automatic Storage Management 31Manual Multiplexing 31Oracle Memory Structures 33System Global Area 33Program Global Area 36Software Code Area 36Background Processes 37Backup/Recovery Overview 39Export/Import 39Offline Backups 40Online Backups 40RMAN 40Security Capabilities 41Privileges and Roles 41Auditing 41Fine-Grained Auditing 42Virtual Private Database 42Label Security 42Real Application Clusters 42Oracle Streams 43Oracle Enterprise Manager 43Oracle Initialization Parameters 44Basic Initialization Parameters 44Advanced Initialization Parameters 49Summary 49Choosing an Upgrade Method 53Before Upgrading 54Using the Database Upgrade Assistant 55Performing a Manual Direct Upgrade 56Using Data Pump Export and Import 58Export and Import Versions to Use 58Performing the Upgrade 58Using the Data-Copying Method 59After Upgrading 60Summary 60Tablespace Architecture 62Tablespace Types 62Optimal Flexible Architecture 68Oracle Installation Tablespaces 73SYSTEM 74SYSAUX 74TEMP 74UNDOTBS1 74USERS 75EXAMPLE 75Segment Segregation 75Summary 76Traditional Disk Space Storage 78Resizing Tablespaces and Datafiles 78Moving Datafiles 86Moving Online Redo Log Files 90Moving Control Files 91Automatic Storage Management 93ASM Architecture 94Creating an ASM Instance 94ASM Instance Components 97ASM Dynamic Performance Views 99ASM Filename Formats 99ASM File Types and Templates 102Administering ASM Disk Groups 102Summary 114Tuning by Design: Best Practices 118Do As Little As Possible 119Do It As Simply As Possible 121Tell the Database What It Needs to Know 123Maximize the Throughput in the Environment 124Divide and Conquer Your Data 124Test Correctly 125Standard Deliverables 127Resource Management 130Implementing the Database Resource Manager 130Sizing Database Objects 135Using Global Temporary Tables 142Supporting Tables Based on Abstract Datatypes 142Using Object Views 143Security for Abstract Datatypes 146Indexing Abstract Datatype Attributes 148Quiescing and Suspending the Database 149Supporting Iterative Development 151Iterative Column Definitions 151Forcing Cursor Sharing 152Managing Package Development 153Generating Diagrams 153Space Requirements 153Tuning Goals 153Security Requirements 153Data Requirements 154Version Requirements 154Execution Plans 154Acceptance Test Procedures 154The Testing Environment 155Summary 155Common Space Management Problems 158Running Out of Free Space in a Tablespace 159Insufficient Space for Temporary Segments 159Too Much or Too Little Undo Space Allocated 159Fragmented Tablespaces and Segments 160Oracle Segments, Extents, and Blocks 161Data Blocks 161Extents 163Segments 164Data Dictionary Views and Dynamic Performance Views 165DBA_TABLESPACES 165DBA_SEGMENTS 166DBA_EXTENTS 166DBA_FREE_SPACE 167DBA_LMT_FREE_SPACE 167DBA_THRESHOLDS 167DBA_OUTSTANDING_ALERTS 168DBA_OBJECT_USAGE 168DBA_ALERT_HISTORY 168V$ALERT_TYPES 169V$UNDOSTAT 169V$SORT_SEGMENT 169V$TEMPSEG_USAGE 170Space Management Methodologies 170Locally Managed Tablespaces 170Using OMF to Manage Space 172Bigfile Tablespaces 173Automatic Storage Management 174Undo Management Considerations 176SYSAUX Monitoring and Usage 177Archived Redo Log File Management 179Built-in Space Management Tools 179Segment Advisor 179Undo Advisor and the Automatic Workload Repository 182Index Usage 184Space Usage Warning Levels 185Resumable Space Allocation 187Managing Alert and Trace Files with ADR 190OS Space Management 193Space Management Scripts 193Segments That Cannot Allocate Additional Extents 193Used and Free Space by Tablespace and Datafile 194Automating and Streamlining the Notification Process 195Using DBMS_SCHEDULER 195Cloud Control and Monitoring 196Summary 207Transaction Basics 210Undo Basics 211Rollback 211Read Consistency 211Database Recovery 212Flashback Operations 212Managing Undo Tablespaces 212Creating Undo Tablespaces 212Undo Tablespace Dynamic Performance Views 219Undo Tablespace Initialization Parameters 219Multiple Undo Tablespaces 221Sizing and Monitoring the Undo Tablespace 223Read Consistency vs. Successful DML 227Flashback Features 227Flashback Query 228DBMS_FLASHBACK 230Flashback Transaction Backout 232Flashback Table 232Flashback Version Query 234Flashback Transaction Query 237xiv Oracle Database 12c DBA HandbookFlashback Data Archive 238Flashback and LOBs 242Migrating to Automatic Undo Management 242Summary 243Tuning Application Design 247Effective Table Design 247Distribution of CPU Requirements 248Effective Application Design 249Tuning SQL 251Impact of Order on Load Rates 252Additional Indexing Options 253Generating Explain Plans 254Tuning Memory Usage 257Managing SGA Pools 257Specifying the Size of the SGA 260Using the Cost-Based Optimizer 261Tuning Data Access 262Identifying Chained Rows 262Using Index-Organized Tables 263Tuning Issues for Index-Organized Tables 264Tuning Data Manipulation 265Bulk Inserts: Using the SQL*Loader Direct Path Option 265Bulk Data Moves: Using External Tables 267Bulk Inserts: Common Traps and Successful Tricks 267Bulk Deletes: The TRUNCATE Command 268Using Partitions 269Reducing Network Traffic 270Replication of Data Using Materialized Views 270Using Remote Procedure Calls 272Using the Automatic Workload Repository 273Managing Snapshots 274Managing Baselines 274Generating AWR Reports 274Running the Automatic Database Diagnostic Monitor Reports 275Using Automatic SQL Tuning Advisor 275Performance Tuning in a Multitenant Environment 277Tuning Methodology 279Sizing the CDB 279Using Memory Advisors 282Leveraging AWR Reports 283Using the SQL Tuning Advisor 283Managing Resource Allocation Within a PDB 284Using Shares to Manage Inter-PDB Resources 284Creating and Modifying Resource Manager Plans 284Performing Database Replay 289Analyze the Source Database Workloads 289Capture Source Database Workloads 290Process Workloads on Target System 290Replay Workloads on Target CDB 290Verify Replay Results 290Summary 290Overview of Oracle In-Memory Option 294System Requirements and Setup 294In-Memory Case Study 295Data Dictionary Views 299V$IM_SEGMENTS 299V$INMEMORY_AREA 300V$SGA 300Summary 301Non-database Security 305Database Authentication Methods 305Database Authentication 306Database Administrator Authentication 306Operating System Authentication 309Network Authentication 3103-Tier Authentication 312Client-Side Authentication 312User Accounts 313Database Authorization Methods 318Profile Management 319System Privileges 326Object Privileges 329Creating, Assigning, and Maintaining Roles 333Using a VPD to Implement Application Security Policies 340Auditing 358Auditing Locations 358Statement Auditing 360Privilege Auditing 364Schema Object Auditing 365Fine-Grained Auditing 366Auditing-Related Data Dictionary Views 368Protecting the Audit Trail 368Data Encryption Techniques 369DBMS_CRYPTO Package 369Transparent Data Encryption 369Summary 370Understanding the Multitenant Architecture 373Leveraging Multitenant Databases 373Understanding Multitenant Configurations 374Provisioning in a Multitenant Environment 376Understanding Pluggable Database Provisioning 376Configuring and Creating a CDB 379Understanding New Data Dictionary Views: The Sequel 384Creating PDBs 387Unplugging and Dropping a PDB 392Managing CDBs and PDBs 395Understanding CDB and PDB Service Names 395Connecting to a CDB or PDB Using SQL Developer 396Creating Services for CDBs or PDBs 397Switching Connections Within a CDB 398Starting Up and Shutting Down CDBs and PDBs 399Changing Parameters in a CDB 406Manage Permanent and Temporary Tablespaces in CDB and PDBs 407Multitenant Security 410Managing Common and Local Users 410Managing Common and Local Privileges 412Managing Common and Local Roles 413Enabling Common Users to Access Data in Specific PDBs 414Backup and Recovery in Multitenant Environments 416Performing Backups of a CDB and All PDBs 416Backing Up CDBs 420Backing Up PDBs 422Recovering from PDB Datafile Loss 424Using the Data Recovery Advisor 432Identifying Block Corruption 436Duplicating PDBs Using RMAN 437Summary 438Overview of Real Application Clusters 442Hardware Configuration 443Software Configuration 443Network Configuration 443Disk Storage 444RAC Characteristics 444Server Parameter File Characteristics 445RAC-Related Initialization Parameters 446Dynamic Performance Views 446RAC Maintenance 448Starting Up a RAC 448Redo Logs in a RAC Environment 448Undo Tablespaces in a RAC Environment 449Failover Scenarios and TAF 449Tuning a RAC Node 451Summary 451Backup Capabilities 454Logical Backups 454Physical Backups 455Offline Backups 455Online Backups 456Using Data Pump Export and Import 457Creating a Directory 457Data Pump Export Options 458Starting a Data Pump Export Job 462Data Pump Import Options 466Implementing Offline Backups 474Implementing Online Backups 475Getting Started 475Performing Online Database Backups 477Integration of Backup Procedures 478Integration of Logical and Physical Backups 478Integration of Database and Operating System Backups 479Summary 480RMAN Features and Components 482RMAN Components 483RMAN vs. Traditional Backup Methods 484Backup Types 486Overview of RMAN Commands and Options 487Running SQL Commands in RMAN 487Frequently Used Commands 488Setting Up a Repository 490Registering a Database 491Persisting RMAN Settings 493Initialization Parameters 497Data Dictionary and Dynamic Performance Views 498Backup Operations 500Full Database Backups 500Tablespace Backups 503Datafile Backups 506Image Copy Backups 506Control File, SPFILE Backup 507Archived Redo Log Backup 509Incremental Backups 509Incrementally Updated Backups 511Incremental Backup Block Change Tracking 513Using a Fast Recovery Area 515Validating Backups 516Recovery Operations 519Block Media Recovery 519Restoring a Control File 520Restoring a Tablespace 520Restoring a Table 522Restoring a Datafile 524Restoring an Entire Database 524Validating Restore Operations 525Point-in-Time Recovery 527Data Recovery Advisor 527Miscellaneous Operations 531Cataloging Other Backups 531Catalog Maintenance 532REPORT and LIST 533Summary 534Data Guard Architecture 538Physical vs. Logical Standby Databases 539Data Protection Modes 539LOG_ARCHIVE_DEST_n Parameter Attributes 540Creating the Standby Database Configuration 542Preparing the Primary Database 543Creating Logical Standby Databases 547Using Real-Time Apply 549Managing Gaps in Archive Log Sequences 550Managing Roles: Switchovers and Failovers 550Switchovers 550Failovers 554Administering the Databases 555Startup and Shutdown of Physical Standby Databases 555Opening Physical Standby Databases in Read-Only Mode 556Managing Datafiles in Data Guard Environments 556Performing DDL on a Logical Standby Database 557Summary 557Recovering Dropped Tables Using Flashback Drop 560The Flashback Database Command 562Using LogMiner 564How LogMiner Works 565Extracting the Data Dictionary 565Analyzing One or More Redo Log Files 566Online Object Reorganization 569Creating Indexes Online 570Rebuilding Indexes Online 570Coalescing Indexes Online 570Rebuilding Index-Organized Tables Online 570Redefining Tables Online 570Moving a Datafile Online 572Summary 573Overview of Oracle Net 578Connect Descriptors 581Net Service Names 582Replacing tnsnames.ora with Oracle Internet Directory 583Listeners 583Using the Oracle Net Configuration Assistant 588Configuring the Listener 588Naming Methods Configuration 590Local Net Service Name Configuration 590Directory Usage Configuration 591Using the Oracle Net Manager 592Starting the Listener Server Process 594Controlling the Listener Server Process 596The Oracle Connection Manager 598Using the Oracle Connection Manager 599Configuring the Oracle Connection Manager 600Using the Connection Manager Control Utility (CMCTL) 601Directory Naming with Oracle Internet Directory 603Oracle Internet Directory Architecture 603Setting Up an Oracle Internet Directory 604Using Easy Connect Naming 604Using Database Links 605Tuning Oracle Net 607Limiting Resource Usage 608Using Compression 609Debugging Connection Problems 609Summary 610Creating Tablespaces in a VLDB Environment 613Bigfile Tablespace Basics 613Creating and Modifying Bigfile Tablespaces 614Bigfile Tablespace ROWID Format 615DBMS_ROWID and Bigfile Tablespaces 616Using DBVERIFY with Bigfile Tablespaces 618Bigfile Tablespace Initialization Parameter Considerations 619Bigfile Tablespace Data Dictionary Changes 620Advanced Oracle Table Types 620Index-Organized Tables 621Global Temporary Tables 622External Tables 624Partitioned Tables 625Materialized Views 652Using Bitmap Indexes 653Understanding Bitmap Indexes 653Using Bitmap Indexes 654Using Bitmap Join Indexes 654Summary 655Remote Queries 659Remote Data Manipulation: Two-Phase Commit 660Dynamic Data Replication 661Managing Distributed Data 662The Infrastructure: Enforcing Location Transparency 663Managing Database Links 667Managing Database Triggers 669Managing Materialized Views 670Using DBMS_MVIEW and DBMS_ADVISOR 674What Kind of Refreshes Can Be Performed? 685Using Materialized Views to Alter Query Execution Paths 689Managing Distributed Transactions 690Resolving In-Doubt Transactions 691Commit Point Strength 691Monitoring Distributed Databases 692Tuning Distributed Databases 693Summary 695