Pro Oracle Database 18c Administration, 3rd edition / Профессиональное администрирование Oracle Database 18c, 3-е издание Год издания: 2019 Автор: Malcher M., Kunh D. / Малчер М., Кун Д. Жанр или тематика: СУБД Издательство: Apress ISBN: 978-1-4842-4423-4 Серия: The Expert's Voice® In Oracle Язык: Английский Формат: PDF Качество: Издательский макет или текст (eBook) Интерактивное оглавление: Да Количество страниц: 1009 Описание: Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family. This book will help elevate you to the level of Professional Oracle Database Administrator. This book provides information and techniques for keeping an Oracle database stable and running on-premise, and is fully updated to cover Oracle Database 18c. New in this edition is coverage of cloud administration in the Oracle Public Cloud, automation of tasks using the autonomous database features, and data movement with multi-tenant databases. The book covers everything from architecture of the database engine, securing objects and users, strategies for performing maintenance tasks and resolving performance problems, through to backup and recovery. Администрирование СУБД Oracle Database 18c.
Примеры страниц
Оглавление
Table of Contents Chapter 1: Installing the Oracle Binaries .................................................................. 1 Understanding the OFA ................................................................................................................. 2 Oracle Inventory Directory ....................................................................................................... 4 Oracle Base Directory .............................................................................................................. 4 Oracle Home Directory ............................................................................................................ 5 Oracle Network Files Directory ................................................................................................ 6 Automatic Diagnostic Repository ............................................................................................ 6 Installing Oracle ............................................................................................................................ 7 Step 1. Create the OS Groups and User ................................................................................... 8 Step 2. Ensure That the OS Is Adequately Configured ........................................................... 11 Step 3. Obtain the Oracle Installation Software ..................................................................... 12 Step 4. Unzip the Files ........................................................................................................... 13 Step 5. Creating oraInst.loc File ............................................................................................ 14 Step 6. Configure the Response File, and Run the Installer ................................................... 15 Step 7. Troubleshoot Any Issues ............................................................................................ 20 Step 8. Apply Any Additional Patches .................................................................................... 21 Installing with a Copy of an Existing Installation ........................................................................ 22 Step 1. Copy the Binaries, Using an OS Utility ....................................................................... 22 Step 2. Attach the Oracle Home ............................................................................................ 24 Installing Read-Only Oracle Home .............................................................................................. 25 Upgrading Oracle Software ......................................................................................................... 26 vi Reinstalling After Failed Installation ........................................................................................... 27 Applying Interim Patches ............................................................................................................ 28 Installing Remotely with the Graphical Installer ......................................................................... 30 Step 1. Install X Software and Networking Utilities on the Local PC ..................................... 31 Step 2. Start an X Session on the Local Computer ................................................................ 31 Step 3. Copy the Oracle Installation Media to the Remote Server ......................................... 32 Step 4. Run the xhost Command ........................................................................................... 33 Step 5. Log In to the Remote Computer from X ..................................................................... 33 Step 6. Ensure that the DISPLAY Variable Is Set Correctly on the Remote Computer ............ 33 Step 7. Execute the runInstaller Utility .................................................................................. 34 Step 8. Troubleshoot .............................................................................................................. 35 Installation in the Cloud .............................................................................................................. 35 Summary .................................................................................................................................... 36 Chapter 2: Creating a Database .............................................................................. 39 Setting OS Variables .................................................................................................................... 40 A Manually Intensive Approach ............................................................................................. 41 Oracle’s Approach to Setting OS Variables ............................................................................ 41 My Approach to Setting OS Variables .................................................................................... 43 Creating a Database .................................................................................................................... 46 Step 1. Set the OS Variables .................................................................................................. 46 Step 2. Configure the Initialization File .................................................................................. 47 Step 3. Create the Required Directories ................................................................................ 50 Step 4. Create the Database .................................................................................................. 50 Step 5. Create a Data Dictionary ........................................................................................... 56 Configuring and Implementing the Listener ................................................................................ 57 Implementing a Listener with the Net Configuration Assistant ................................................... 58 Manually Configuring a Listener ............................................................................................ 59 Connecting to a Database through the Network ................................................................... 61 Creating a Password File ............................................................................................................ 62 Table of Contents vii Starting and Stopping the Database ........................................................................................... 64 Understanding OS Authentication .......................................................................................... 64 Starting the Database ............................................................................................................ 65 Stopping the Database .......................................................................................................... 68 Using the dbca to Create a Database .......................................................................................... 70 Dropping a Database ................................................................................................................... 73 How Many Databases on One Server? ........................................................................................ 74 Understanding Oracle Architecture ............................................................................................. 77 Summary .................................................................................................................................... 80 Chapter 3: Configuring an Efficient Environment ................................................... 83 Customizing Your OS Command Prompt ..................................................................................... 84 Customizing Your SQL Prompt .................................................................................................... 87 Creating Shortcuts for Frequently Used Commands ................................................................... 89 Using Aliases ......................................................................................................................... 89 Using a Function .................................................................................................................... 91 Rerunning Commands Quickly .................................................................................................... 93 Scrolling with the Up and Down Arrow Keys ......................................................................... 94 Using Ctrl+P and Ctrl+N ........................................................................................................ 94 Listing the Command History ................................................................................................ 94 Searching in Reverse ............................................................................................................. 95 Setting the Command Editor ................................................................................................. 95 Developing Standard Scripts ...................................................................................................... 96 dba_setup .............................................................................................................................. 97 dba_fcns ................................................................................................................................ 98 tbsp_chk.bsh ......................................................................................................................... 99 conn.bsh .............................................................................................................................. 102 filesp.bsh ............................................................................................................................. 103 login.sql ............................................................................................................................... 106 top.sql .................................................................................................................................. 107 lock.sql ................................................................................................................................ 108 users.sql .............................................................................................................................. 110 Table of Contents viii Organizing Scripts ..................................................................................................................... 111 Step 1. Create Directories ................................................................................................... 112 Step 2. Copy Files to Directories ......................................................................................... 112 Step 3. Configure the Startup File ....................................................................................... 113 Automating Scripts ................................................................................................................... 114 Summary .................................................................................................................................. 114 Chapter 4: Tablespaces and Data Files ................................................................. 117 Understanding the First Five ..................................................................................................... 119 Understanding the Need for More ............................................................................................. 120 Creating Tablespaces ................................................................................................................ 122 Renaming a Tablespace ............................................................................................................ 127 Changing a Tablespace’s Write Mode ....................................................................................... 128 Dropping a Tablespace .............................................................................................................. 129 Using Oracle Managed Files ..................................................................................................... 132 Creating a Bigfile Tablespace .................................................................................................... 133 Enabling Default Table Compression Within a Tablespace Tablespace ..................................... 134 Displaying Tablespace Size ....................................................................................................... 135 Altering Tablespace Size ........................................................................................................... 137 Toggling Data Files Offline and Online ...................................................................................... 138 Renaming or Relocating a Data File .......................................................................................... 141 Performing Online Data File Operations .............................................................................. 142 Performing Offline Data File Operations .............................................................................. 142 Using ASM for Tablespaces ....................................................................................................... 148 Summary .................................................................................................................................. 149 Chapter 5: Managing Control Files, Online Redo Logs, and Archivelogs .............. 151 Managing Control Files ............................................................................................................. 151 Viewing Control File Names and Locations ......................................................................... 155 Adding a Control File ........................................................................................................... 155 Moving a Control File ........................................................................................................... 159 Removing a Control File ...................................................................................................... 160 Table of Contents ix Online Redo Logs ...................................................................................................................... 162 Displaying Online Redo Log Information ............................................................................. 166 Determining the Optimal Size of Online Redo Log Groups .................................................. 168 Determining the Optimal Number of Redo Log Groups ....................................................... 169 Adding Online Redo Log Groups .......................................................................................... 172 Resizing and Dropping Online Redo Log Groups ................................................................. 172 Adding Online Redo Log Files to a Group ............................................................................ 175 Removing Online Redo Log Files from a Group ................................................................... 175 Moving or Renaming Redo Log Files ................................................................................... 176 Controlling the Generation of Redo ...................................................................................... 177 Implementing Archivelog Mode ................................................................................................ 179 Making Architectural Decisions ........................................................................................... 179 Setting the Archive Redo File Location ................................................................................ 181 Enabling Archivelog Mode ................................................................................................... 186 Disabling Archivelog Mode .................................................................................................. 187 Reacting to a Lack of Disk Space in Your Archive Log Destination ...................................... 188 Backing Up Archive Redo Log Files ..................................................................................... 190 Summary .................................................................................................................................. 190 Chapter 6: Users and Basic Security .................................................................... 193 Managing Default Users ............................................................................................................ 193 Locking Accounts and Expiring Passwords ......................................................................... 196 Identifying DBA-Created Accounts ...................................................................................... 198 Checking Default Passwords ............................................................................................... 199 Creating Users .......................................................................................................................... 200 Choosing a Username and Authentication Method .............................................................. 201 Assigning Default Permanent and Temporary Tablespaces ................................................. 206 Modifying Passwords ................................................................................................................ 209 Schema Only Account ............................................................................................................... 210 Modifying Users ........................................................................................................................ 212 Dropping Users ......................................................................................................................... 213 Enforcing Password Security and Resource Limits .................................................................. 214 Table of Contents x Basic Password Security ..................................................................................................... 215 Password Strength .............................................................................................................. 219 Limiting Database Resource Usage ..................................................................................... 221 Managing Privileges .................................................................................................................. 223 Assigning Database System Privileges ............................................................................... 224 Assigning Database Object Privileges ................................................................................. 225 Grouping and Assigning Privileges ...................................................................................... 226 Summary .................................................................................................................................. 229 Chapter 7: Tables and Constraints ........................................................................ 231 Understanding Table Types ....................................................................................................... 232 Understanding Data Types ........................................................................................................ 233 Character ............................................................................................................................. 234 Numeric ............................................................................................................................... 236 Date/Time ............................................................................................................................ 237 RAW ..................................................................................................................................... 238 ROWID .................................................................................................................................. 239 LOB ...................................................................................................................................... 240 JSON .................................................................................................................................... 241 Creating a Table ........................................................................................................................ 241 Creating a Heap-Organized Table ........................................................................................ 242 Implementing Virtual Columns ............................................................................................ 246 Implementing Invisible Columns ......................................................................................... 250 Making Read-Only Tables .................................................................................................... 251 Understanding Deferred-Segment Creation ........................................................................ 252 Creating a Table with an Autoincrementing (Identity) Column ............................................. 253 Allowing for Default Parallel SQL Execution ........................................................................ 256 Compressing Table Data ...................................................................................................... 257 Avoiding Redo Creation ....................................................................................................... 260 Creating a Table from a Query ............................................................................................. 263 Table of Contents xi Modifying a Table ...................................................................................................................... 266 Obtaining the Needed Lock ................................................................................................. 266 Renaming a Table ................................................................................................................ 267 Adding a Column ................................................................................................................. 267 Altering a Column ................................................................................................................ 268 Renaming a Column ............................................................................................................ 270 Dropping a Column .............................................................................................................. 270 Displaying Table DDL ................................................................................................................. 271 Dropping a Table ....................................................................................................................... 273 Undropping a Table ................................................................................................................... 274 Removing Data from a Table ..................................................................................................... 275 Using DELETE ...................................................................................................................... 276 Using TRUNCATE .................................................................................................................. 276 Viewing and Adjusting the High-Water Mark ............................................................................ 278 Tracing to Detect Space Below the High-Water Mark ......................................................... 279 Using DBMS_SPACE to Detect Space Below the High-Water Mark .................................... 280 Selecting from Data Dictionary Extents View ...................................................................... 282 Lowering the High-Water Mark ........................................................................................... 282 Creating a Temporary Table ....................................................................................................... 286 Creating an Index-Organized Table ........................................................................................... 288 Managing Constraints ............................................................................................................... 289 Creating Primary Key Constraints ........................................................................................ 289 Enforcing Unique Key Values ............................................................................................... 291 Creating Foreign Key Constraints ........................................................................................ 293 Checking for Specific Data Conditions ................................................................................ 295 Enforcing Not Null Conditions .............................................................................................. 296 Disabling Constraints .......................................................................................................... 297 EnablingConstraints ............................................................................................................ 299 Summary .................................................................................................................................. 302 Table of Contents xii Chapter 8: Indexes ................................................................................................ 303 Deciding When to Create an Index ............................................................................................ 304 Proactively Creating Indexes ............................................................................................... 305 Reactively Creating Indexes ................................................................................................ 306 Planning for Robustness ........................................................................................................... 308 Determining Which Type of Index to Use ............................................................................. 308 Estimating the Size of an Index Before Creation ................................................................. 311 Creating Separate Tablespaces for Indexes ........................................................................ 313 Creating Portable Scripts ..................................................................................................... 316 Establishing Naming Standards .......................................................................................... 317 Creating Indexes ....................................................................................................................... 318 Creating B-tree Indexes ....................................................................................................... 318 Creating Concatenated Indexes ........................................................................................... 322 Implementing Function-Based Indexes ............................................................................... 324 Creating Unique Indexes ...................................................................................................... 325 Implementing Bitmap Indexes ............................................................................................. 327 Creating Bitmap Join Indexes .............................................................................................. 328 Implementing Reverse-Key Indexes .................................................................................... 329 Creating Key-Compressed Indexes ..................................................................................... 330 Parallelizing Index Creation ................................................................................................. 331 Avoiding Redo Generation When Creating an Index ............................................................. 331 Implementing Invisible Indexes ........................................................................................... 332 Maintaining Indexes .................................................................................................................. 335 Renaming an Index .............................................................................................................. 336 Displaying Code to Re-create an Index ............................................................................... 336 Rebuilding an Index ............................................................................................................. 337 Making Indexes Unusable ................................................................................................... 338 Monitoring Index Usage ....................................................................................................... 339 Dropping an Index ............................................................................................................... 341 Indexing Foreign Key Columns .................................................................................................. 341 Table of Contents xiii Implementing an Index on a Foreign Key Column ............................................................... 342 Determining if Foreign Key Columns Are Indexed ............................................................... 344 Summary .................................................................................................................................. 347 Chapter 9: Views, Synonyms, and Sequences ...................................................... 351 Implementing Views .................................................................................................................. 351 Creating a View .................................................................................................................... 352 Checking Updates ................................................................................................................ 354 Creating Read-Only Views ................................................................................................... 355 Updatable Join Views .......................................................................................................... 356 Creating an INSTEAD OF Trigger .......................................................................................... 358 Implementing an Invisible Column ...................................................................................... 360 Modifying a View Definition ................................................................................................. 362 Displaying the SQL Used to Create a View .......................................................................... 363 Renaming a View ................................................................................................................. 364 Dropping a View .................................................................................................................. 365 Managing Synonyms ................................................................................................................. 365 Creating a Synonym ............................................................................................................ 366 Creating Public Synonyms ................................................................................................... 367 Dynamically Generating Synonyms ..................................................................................... 368 Displaying Synonym Metadata ............................................................................................ 369 Renaming a Synonym .......................................................................................................... 370 Dropping a Synonym ........................................................................................................... 370 Managing Sequences ................................................................................................................ 371 Creating a Sequence ........................................................................................................... 371 Using Sequence Pseudocolumns ........................................................................................ 373 Autoincrementing Columns ................................................................................................. 374 Scalable Sequences ............................................................................................................ 375 Implementing Multiple Sequences That Generate Unique Values ....................................... 376 Creating One Sequence or Many ......................................................................................... 377 Viewing Sequence Metadata ............................................................................................... 378 Renaming a Sequence ......................................................................................................... 379 Table of Contents xiv Dropping a Sequence .......................................................................................................... 379 Resetting a Sequence .......................................................................................................... 379 Summary .................................................................................................................................. 381 Chapter 10: Data Dictionary Fundamentals .......................................................... 383 Data Dictionary Architecture ..................................................................................................... 384 Static Views ......................................................................................................................... 384 Dynamic Performance Views ............................................................................................... 387 A Different View of Metadata .................................................................................................... 389 A Few Creative Uses of the Data Dictionary ............................................................................. 392 Derivable Documentation .................................................................................................... 392 Displaying User Information ................................................................................................ 394 Displaying Table Row Counts ............................................................................................... 398 Showing Primary Key and Foreign Key Relationships ......................................................... 401 Displaying Object Dependencies ......................................................................................... 402 Summary .................................................................................................................................. 406 Chapter 11: Large Objects .................................................................................... 407 Describing LOB Types ................................................................................................................ 408 Illustrating LOB Locators, Indexes, and Chunks ........................................................................ 409 Distinguishing Between BasicFiles and SecureFiles ................................................................ 411 BasicFiles ............................................................................................................................ 412 SecureFiles .......................................................................................................................... 412 Creating a Table with a LOB Column ......................................................................................... 413 Creating a BasicFiles LOB Column ...................................................................................... 413 Implementing a LOB in a Specific Tablespace ..................................................................... 415 Creating a SecureFiles LOB Column .................................................................................... 416 Implementing a Partitioned LOB .......................................................................................... 417 Maintaining LOB Columns ......................................................................................................... 419 Moving a LOB Column ......................................................................................................... 420 Adding a LOB Column .......................................................................................................... 420 Removing a LOB Column ..................................................................................................... 421 Table of Contents xv Caching LOBs ...................................................................................................................... 421 Storing LOBs In- and Out of Line ......................................................................................... 422 Implementing SecureFiles Advanced Features ......................................................................... 424 Compressing LOBs .............................................................................................................. 424 Deduplicating LOBs ............................................................................................................. 425 Encrypting LOBs .................................................................................................................. 426 Migrating BasicFiles to SecureFiles .................................................................................... 428 Loading LOBs ............................................................................................................................ 432 Loading a CLOB ................................................................................................................... 432 Loading a BLOB ................................................................................................................... 434 Measuring LOB Space Consumed ............................................................................................. 435 BasicFiles Space Used ........................................................................................................ 436 SecureFiles Space Used ...................................................................................................... 437 Reading BFILEs ......................................................................................................................... 439 Summary .................................................................................................................................. 440 Chapter 12: Partitioning: Divide and Conquer ...................................................... 441 What Tables Should Be Partitioned? ......................................................................................... 443 Creating Partitioned Tables ....................................................................................................... 445 Partitioning by Range .......................................................................................................... 445 Placing Partitions in Tablespaces ........................................................................................ 450 Partitioning by List ............................................................................................................... 453 Partitioning by Hash ............................................................................................................ 454 Blending Different Partitioning Methods ............................................................................. 455 Creating Partitions on Demand ............................................................................................ 457 Partitioning to Match a Parent Table ................................................................................... 462 Partitioning on a Virtual Column .......................................................................................... 466 Giving an Application Control Over Partitioning ................................................................... 467 Maintaining Partitions ............................................................................................................... 467 Viewing Partition Metadata ................................................................................................. 468 Moving a Partition ............................................................................................................... 469 Automatically Moving Updated Rows .................................................................................. 471 Table of Contents xvi Partitioning an Existing Table .............................................................................................. 472 Adding a Partition ................................................................................................................ 474 Exchanging a Partition with an Existing Table ..................................................................... 476 Renaming a Partition ........................................................................................................... 479 Splitting a Partition .............................................................................................................. 479 Merging Partitions ............................................................................................................... 480 Dropping a Partition ............................................................................................................. 482 Generating Statistics for a Partition .................................................................................... 483 Removing Rows from a Partition ......................................................................................... 484 Manipulating Data Within a Partition ................................................................................... 485 Partitioning Indexes .................................................................................................................. 486 Partitioning an Index to Follow Its Table .............................................................................. 486 Partitioning an Index Differently from Its Table ................................................................... 490 Partial Indexes ..................................................................................................................... 492 Partition Pruning ....................................................................................................................... 494 Modifying the Partition Strategy ............................................................................................... 496 Summary .................................................................................................................................. 496 Chapter 13: Data Pump ......................................................................................... 499 Data Pump Architecture ............................................................................................................ 500 Getting Started .......................................................................................................................... 505 Taking an Export .................................................................................................................. 505 Importing a Table ................................................................................................................. 509 Using a Parameter File ........................................................................................................ 509 Exporting and Importing with Granularity ................................................................................. 511 Exporting and Importing an Entire Database ....................................................................... 511 Schema Level ...................................................................................................................... 513 Table Level ........................................................................................................................... 514 Tablespace Level ................................................................................................................. 515 Transferring Data ...................................................................................................................... 516 Exporting and Importing Directly Across the Network ......................................................... 516 Copying Data Files ............................................................................................................... 519 Table of Contents xvii Features for Manipulating Storage ........................................................................................... 521 Exporting Tablespace Metadata ........................................................................................... 522 Specifying Different Data File Paths and Names ................................................................. 522 Importing into a Tablespace Different from the Original ...................................................... 523 Changing the Size of Data Files ........................................................................................... 524 Changing Segment and Storage Attributes ......................................................................... 525 Filtering Data and Objects ......................................................................................................... 526 Specifying a Query .............................................................................................................. 526 Exporting a Percentage of the Data ..................................................................................... 528 Excluding Objects from the Export File ............................................................................... 528 Excluding Statistics ............................................................................................................. 530 Including Only Specific Objects in an Export File ................................................................ 531 Exporting Table, Index, Constraint, and Trigger DDL ............................................................ 531 Excluding Objects from Import ............................................................................................ 532 Including Objects in Import ................................................................................................. 532 Common Data Pump Tasks ....................................................................................................... 533 Estimating the Size of Export Jobs ...................................................................................... 533 Listing the Contents of Dump Files ...................................................................................... 534 Cloning a User ..................................................................................................................... 535 Creating a Consistent Export ............................................................................................... 535 Importing When Objects Already Exist ................................................................................. 537 Renaming a Table ................................................................................................................ 539 Remapping Data .................................................................................................................. 539 Suppressing a Log File ........................................................................................................ 541 Using Parallelism ................................................................................................................. 541 Specifying Additional Dump Files ........................................................................................ 543 Reusing Output File Names ................................................................................................. 543 Creating a Daily DDL File ..................................................................................................... 544 Compressing Output ............................................................................................................ 545 Changing Table Compression Characteristics on Import ..................................................... 546 Encrypting Data ................................................................................................................... 546 Exporting Views as Tables ................................................................................................... 548 Table of Contents xviii Disabling Logging of Redo on Import .................................................................................. 548 Attaching to a Running Job ................................................................................................. 549 Stopping and Restarting a Job ............................................................................................ 550 Terminating a Data Pump Job ............................................................................................. 550 Monitoring Data Pump Jobs ...................................................................................................... 551 Data Pump Log File ............................................................................................................. 551 Data Dictionary Views .......................................................................................................... 552 Database Alert Log .............................................................................................................. 553 Status Table ......................................................................................................................... 553 Interactive Command Mode Status ..................................................................................... 554 OS Utilities ........................................................................................................................... 554 Summary .................................................................................................................................. 555 Chapter 14: External Tables .................................................................................. 557 SQL*Loader vs. External Tables ................................................................................................ 558 Loading CSV Files into the Database ........................................................................................ 560 Creating a Directory Object and Granting Access ................................................................ 561 Creating an External Table ................................................................................................... 561 Generating SQL to Create an External Table ........................................................................ 563 Viewing External Table Metadata ........................................................................................ 565 Loading a Regular Table from the External Table ................................................................ 566 Performing Advanced Transformations ..................................................................................... 568 Viewing Text Files from SQL ...................................................................................................... 570 Unloading and Loading Data Using an External Table ............................................................... 572 Enabling Parallelism to Reduce Elapsed Time ..................................................................... 575 Compressing a Dump File ................................................................................................... 576 Encrypting a Dump File ....................................................................................................... 577 Summary .................................................................................................................................. 580 Chapter 15: Materialized Views ............................................................................ 581 Understanding MVs ................................................................................................................... 581 MV Terminology ................................................................................................................... 584 Referencing Useful Views .................................................................................................... 585 Table of Contents xix Creating Basic Materialized Views ............................................................................................ 586 Creating a Complete Refreshable MV .................................................................................. 587 Creating a Fast Refreshable MV .......................................................................................... 591 Going Beyond the Basics .......................................................................................................... 598 Creating MVs and Specifying Tablespace for MVs and Indexes .......................................... 598 Creating Indexes on MVs ..................................................................................................... 598 Partitioning MVs .................................................................................................................. 599 Compressing an MV ............................................................................................................. 600 Encrypting MV Columns ...................................................................................................... 600 Building an MV on a Prebuilt Table ...................................................................................... 602 Creating an Unpopulated MV ............................................................................................... 603 Creating an MV Refreshed on Commit ................................................................................. 604 Creating a Never Refreshable MV ........................................................................................ 605 Creating MVs for Query Rewrite .......................................................................................... 606 Creating a Fast Refreshable MV Based on a Complex Query .............................................. 607 Viewing MV DDL .................................................................................................................. 611 Dropping an MV ................................................................................................................... 611 Modifying MVs .......................................................................................................................... 612 Modifying Base Table DDL and Propagating to MVs ............................................................ 612 Toggling Redo Logging on an MV ........................................................................................ 617 Altering Parallelism ............................................................................................................. 618 Moving an MV ...................................................................................................................... 619 Managing MV Logs .................................................................................................................... 619 Creating an MV Log ............................................................................................................. 620 Indexing MV Log Columns ................................................................................................... 622 Viewing Space Used by an MV Log ..................................................................................... 622 Shrinking the Space in an MV Log ....................................................................................... 623 Checking the Row Count of an MV Log ............................................................................... 624 Moving an MV Log ............................................................................................................... 625 Dropping an MV Log ............................................................................................................ 626 Refreshing MVs ......................................................................................................................... 627 Table of Contents xx Manually Refreshing MVs from SQL*Plus ............................................................................ 627 Creating an MV with a Refresh Interval ............................................................................... 629 Efficiently Performing a Complete Refresh ......................................................................... 630 Handling the ORA-12034 Error ............................................................................................ 631 Monitoring MV Refreshes .......................................................................................................... 632 Viewing MVs’ Last Refresh Times ...................................................................................... 632 Determining Whether a Refresh Is in Progress .................................................................... 632 Monitoring Real-Time Refresh Progress ............................................................................. 633 Checking Whether MVs Are Refreshing Within a Time Period ............................................. 634 Creating Remote MV Refreshes ................................................................................................ 635 Understanding Remote-Refresh Architectures .................................................................... 636 Viewing MV Base Table Information .................................................................................... 638 Determining How Many MVs Reference a Central MV Log .................................................. 639 Managing MVs in Groups .......................................................................................................... 641 Creating an MV Group .......................................................................................................... 642 Altering an MV Refresh Group ............................................................................................. 642 Refreshing an MV Group ...................................................................................................... 643 DBMS_MVIEW vs. DBMS_REFRESH .................................................................................... 643 Determining MVs in a Group ................................................................................................ 644 Adding an MV to a Refresh Group ........................................................................................ 645 Removing MVs from a Refresh Group .................................................................................. 645 Dropping an MV Refresh Group ........................................................................................... 645 Summary ................................................................................................................................ 646 Chapter 16: User-Managed Backup and Recovery ............................................... 647 Implementing a Cold-Backup Strategy ..................................................................................... 649 Making a Cold Backup of a Database .................................................................................. 649 Restoring a Cold Backup in Noarchivelog Mode with Online Redo Logs ............................. 652 Restoring a Cold Backup in Noarchivelog Mode Without Online Redo Logs ........................ 653 Scripting a Cold Backup and Restore .................................................................................. 655 Implementing a Hot Backup Strategy ....................................................................................... 660 Making a Hot Backup .......................................................................................................... 660 Table of Contents xxi Scripting Hot Backups ......................................................................................................... 665 Understanding the Split-Block Issue ................................................................................... 668 Understanding the Need for Redo Generated During Backup ............................................. 672 Understanding That Data Files Are Updated ........................................................................ 673 Performing a Complete Recovery of an Archivelog Mode Database ......................................... 675 Restoring and Recovering with the Database Offline .......................................................... 676 Restoring and Recovering with a Database Online ............................................................. 681 Restoring Control Files ........................................................................................................ 682 Performing an Incomplete Recovery of an Archivelog Mode Database .................................... 687 Summary .................................................................................................................................. 691 Chapter 17: Configuring RMAN ............................................................................. 693 Understanding RMAN ................................................................................................................ 694 Starting RMAN .......................................................................................................................... 699 RMAN Architectural Decisions .................................................................................................. 700 1. Running the RMAN Client Remotely or Locally ................................................................ 704 2. Specifying the Backup User ............................................................................................ 704 3. Using Online or Offline Backups ...................................................................................... 705 4. Setting the Archivelog Destination and File Format ........................................................ 705 5. Configuring the RMAN Backup Location and File Format ................................................ 706 6. Setting the Autobackup of the Control File ...................................................................... 709 7. Specifying the Location of the Autobackup of the Control File ........................................ 710 8. Backing Up Archivelogs ................................................................................................... 711 9. Determining the Location for the Snapshot Control File .................................................. 711 10. Using a Recovery Catalog .............................................................................................. 712 11. Using a Media Manager ................................................................................................. 713 12. Setting the CONTROL_FILE_RECORD_KEEP_TIME Initialization Parameter .................. 714 13. Configuring RMAN’s Backup Retention Policy ............................................................... 715 14. Configuring the Archivelogs’ Deletion Policy ................................................................. 717 15. Setting the Degree of Parallelism .................................................................................. 718 16. Using Backup Sets or Image Copies .............................................................................. 719 17. Using Incremental Backups ........................................................................................... 720 Table of Contents xxii 18. Using Incrementally Updated Backups .......................................................................... 721 19. Using Block Change Tracking ........................................................................................ 721 20. Configuring Binary Compression .................................................................................. 722 21. Configuring Encryption .................................................................................................. 723 22. Configuring Miscellaneous Settings .............................................................................. 724 23. Configuring Informational Output .................................................................................. 725 Segueing from Decision to Action ............................................................................................. 727 Summary .................................................................................................................................. 732 Chapter 18: RMAN Backups and Reporting .......................................................... 733 Preparing to Run RMAN Backup Commands ............................................................................ 734 Setting NLS_DATE_FORMAT ................................................................................................ 734 Setting ECHO ....................................................................................................................... 735 Showing Variables ............................................................................................................... 736 Running Backups ...................................................................................................................... 736 Backing Up the Entire Database .......................................................................................... 736 Backing Up Tablespaces ...................................................................................................... 738 Backing Up Data Files ......................................................................................................... 739 Backing Up the Control File ................................................................................................. 739 Backing Up the spfile ........................................................................................................... 740 Backing Up Archivelogs ....................................................................................................... 740 Backing Up FRA ................................................................................................................... 741 Excluding Tablespaces from Backups ................................................................................. 742 Backing Up Data Files Not Backed Up ................................................................................. 743 Skipping Read-Only Tablespaces ........................................................................................ 743 Skipping Offline or Inaccessible Files ................................................................................. 744 Backing Up Large Files in Parallel ....................................................................................... 745 Adding RMAN Backup Information to the Repository .......................................................... 745 Taking Backups of Pluggable Databases .................................................................................. 747 While Connected to the Root Container ............................................................................... 747 While Connected to a Pluggable Database .......................................................................... 748 Table of Contents xxiii Creating Incremental Backups .................................................................................................. 749 Taking Incremental-Level Backups ..................................................................................... 750 Making Incrementally Updating Backups ............................................................................ 751 Using Block Change Tracking .............................................................................................. 753 Checking for Corruption in Data Files and Backups .................................................................. 754 Using VALIDATE .................................................................................................................... 754 Using BACKUP...VALIDATE .................................................................................................... 756 Using RESTORE...VALIDATE .................................................................................................. 756 Using a Recovery Catalog ......................................................................................................... 757 Creating a Recovery Catalog ............................................................................................... 757 Registering a Target Database ............................................................................................. 759 Backing Up the Recovery Catalog ....................................................................................... 760 Synchronizing the Recovery Catalog ................................................................................... 760 Recovery Catalog Versions .................................................................................................. 761 Dropping a Recovery Catalog .............................................................................................. 761 Logging RMAN Output ............................................................................................................... 762 Redirecting Output to a File ................................................................................................. 762 Capturing Output with Linux/Unix Logging Commands ....................................................... 763 Logging Output to a File ...................................................................................................... 764 Querying for Output in the Data Dictionary ......................................................................... 764 RMAN Reporting ........................................................................................................................ 765 Using LIST ............................................................................................................................ 765 Using REPORT ...................................................................................................................... 766 Using SQL ............................................................................................................................ 767 Summary .................................................................................................................................. 772 Chapter 19: RMAN Restore and Recovery ............................................................ 773 Determining if Media Recovery Is Required .............................................................................. 775 Determining What to Restore .................................................................................................... 777 How the Process Works ....................................................................................................... 777 Using Data Recovery Advisor ............................................................................................... 779 Table of Contents xxiv Using RMAN to Stop/Start Oracle .............................................................................................. 783 Shutting Down ..................................................................................................................... 783 Starting Up ........................................................................................................................... 783 Complete Recovery ................................................................................................................... 784 Testing Restore and Recovery ............................................................................................. 785 Restoring and Recovering the Entire Database ................................................................... 787 Restoring and Recovering Tablespaces ............................................................................... 789 Restoring Read-Only Tablespaces ....................................................................................... 790 Restoring Temporary Tablespaces ....................................................................................... 791 Restoring and Recovering Data Files ................................................................................... 791 Restoring Data Files to Nondefault Locations ..................................................................... 793 Performing Block-Level Recovery ....................................................................................... 794 Restoring a Container Database and Its Associated Pluggable Databases ......................... 796 Restoring Archivelog Files ......................................................................................................... 799 Restoring to the Default Location ........................................................................................ 800 Restoring to a Nondefault Location ..................................................................................... 800 Restoring a Control File ............................................................................................................. 801 Using a Recovery Catalog .................................................................................................... 801 Using an Autobackup ........................................................................................................... 802 Specifying a Backup File Name ........................................................................................... 803 Restoring the spfile ................................................................................................................... 803 Incomplete Recovery ................................................................................................................ 805 Determining the Type of Incomplete Recovery .................................................................... 808 Performing Time-Based Recovery ....................................................................................... 808 Performing Log Sequence-Based Recovery ........................................................................ 809 Performing SCN-Based Recovery ........................................................................................ 810 Restoring to a Restore Point ................................................................................................ 811 Restoring Tables to a Previous Point ................................................................................... 811 Flashing Back a Table ............................................................................................................... 813 FLASHBACK TABLE TO BEFORE DROP .................................................................................. 813 Flashing Back a Table to a Previous Point in Time .............................................................. 815 Table of Contents xxv FLASHING BACK A DATABASE .................................................................................................... 816 Restoring and Recovering to a Different Server ....................................................................... 819 Step 1. Create an RMAN Backup on the Originating Database ........................................... 821 Step 2. Copy the RMAN Backup to the Destination Server .................................................. 821 Step 3. Ensure That Oracle Is Installed ................................................................................ 822 Step 4. Source the Required OS Variables ........................................................................... 822 Step 5. Create an init.ora File for the Database to Be Restored .......................................... 822 Step 6. Create Any Required Directories for Data Files, Control Files, and Dump/Trace Files .......................................................................................................... 823 Step 7. Start Up the Database in Nomount Mode ................................................................ 824 Step 8. Restore the Control File from the RMAN Backup .................................................... 824 Step 9. Start Up the Database in Mount Mode .................................................................... 824 Step 10. Make the Control File Aware of the Location of the RMAN Backups ..................... 824 Step 11. Rename and Restore the Data Files to Reflect New Directory Locations .............. 825 Step 12. Recover the Database ........................................................................................... 828 Step 13. Set the New Location for the Online Redo Logs .................................................... 829 Step 14. Open the Database ................................................................................................ 830 Step 15. Add the Temp File .................................................................................................. 831 Step 16. Rename the Database ........................................................................................... 831 Summary .................................................................................................................................. 834 Chapter 20: Automating Jobs ............................................................................... 837 Automating Jobs with Oracle Scheduler ................................................................................... 839 Creating and Scheduling a Job ............................................................................................ 839 Viewing Job Details ............................................................................................................. 841 Modifying Job Logging History ............................................................................................ 842 Modifying a Job ................................................................................................................... 842 Stopping a Job ..................................................................................................................... 843 Disabling a Job .................................................................................................................... 843 Enabling a Job ..................................................................................................................... 843 Copying a Job ...................................................................................................................... 844 Running a Job Manually ...................................................................................................... 844 Deleting a Job ...................................................................................................................... 845 Table of Contents xxvi Oracle Scheduler vs. cron ......................................................................................................... 845 Automating Jobs via cron ......................................................................................................... 846 How cron Works .................................................................................................................. 847 Enabling Access to cron ...................................................................................................... 849 Understanding cron Table Entries ........................................................................................ 850 Scheduling a Job to Run Automatically ............................................................................... 851 Redirecting cron Output ...................................................................................................... 855 Troubleshooting cron ........................................................................................................... 856 Examples of Automated DBA Jobs ............................................................................................ 857 Starting and Stopping the Database and Listener ............................................................... 858 Checking for Archivelog Destination Fullness ..................................................................... 859 Truncating Large Log Files .................................................................................................. 862 Checking for Locked Production Accounts .......................................................................... 864 Checking for Too Many Processes ....................................................................................... 865 Verifying the Integrity of RMAN Backups ............................................................................. 866 Autonomous Database .............................................................................................................. 868 Summary .................................................................................................................................. 869 Chapter 21: Database Troubleshooting ................................................................. 871 Quickly Triaging ......................................................................................................................... 871 Checking Database Availability ............................................................................................ 872 Investigating Disk Fullness .................................................................................................. 875 Inspecting the Alert Log ....................................................................................................... 878 Identifying Bottlenecks via OS Utilities ..................................................................................... 882 Identifying System Bottlenecks ........................................................................................... 883 Mapping an Operating System Process to an SQL Statement ............................................. 888 Finding Resource-Intensive SQL Statements ............................................................................ 891 Monitoring Real-Time SQL Execution Statistics .................................................................. 891 Running Oracle Diagnostic Utilities ..................................................................................... 894 Detecting and Resolving Locking Issues ................................................................................... 899 Resolving Open-Cursor Issues .................................................................................................. 902 Troubleshooting Undo Tablespace Issues ................................................................................. 904 Table of Contents xxvii Determining if Undo Is Correctly Sized ................................................................................ 904 Viewing SQL That Is Consuming Undo Space ...................................................................... 907 Handling Temporary Tablespace Issues .................................................................................... 908 Determining if Temporary Tablespace Is Sized Correctly .................................................... 909 Viewing SQL That Is Consuming Temporary Space ............................................................. 910 Summary .................................................................................................................................. 911 Chapter 22: Pluggable Databases ......................................................................... 915 Understanding Pluggable Architecture ..................................................................................... 919 Paradigm Shift ..................................................................................................................... 922 Backup and Recovery Implications ..................................................................................... 924 Tuning Nuances ................................................................................................................... 925 Creating a CDB .......................................................................................................................... 926 Using the Database Configuration Assistant (DBCA) ........................................................... 927 Generating CDB Create Scripts via DBCA ............................................................................ 928 Creating Manually with SQL ................................................................................................ 929 Verifying That a CDB Was Created ....................................................................................... 932 Administrating the Root Container ............................................................................................ 934 Connecting to the Root Container ........................................................................................ 934 Displaying Currently Connected Container Information ....................................................... 935 Starting/Stopping the Root Container .................................................................................. 936 Creating Common Users ...................................................................................................... 936 Creating Common Roles ...................................................................................................... 937 Creating Local Users and Roles ........................................................................................... 938 Reporting on Container Space ............................................................................................. 938 Switching Containers .......................................................................................................... 940 Creating a Pluggable Database Within a CDB ........................................................................... 941 Cloning the Seed Database ................................................................................................. 942 Cloning an Existing PDB ...................................................................................................... 943 Cloning from a Non-CDB Database ...................................................................................... 945 Unplugging a PDB from a CDB ............................................................................................. 947 Plugging an Unplugged PDB into a CDB .............................................................................. 948 Table of Contents xxviii Using the DBCA to Create a PDB from the Seed Database .................................................. 949 Checking the Status of Pluggable Databases ...................................................................... 950 Administrating Pluggable Databases ........................................................................................ 951 Connecting to a PDB ............................................................................................................ 951 Managing a Listener in PDB Environment ........................................................................... 952 Showing the Currently Connected PDB ............................................................................... 954 Starting/Stopping a PDB ...................................................................................................... 955 Modifying Initialization Parameters Specific to a PDB ........................................................ 956 Renaming a PDB .................................................................................................................. 957 Limiting the Amount of Space Consumed by PDB ............................................................... 957 Restricting Changes to SYSTEM at PDB .............................................................................. 958 Viewing PDB History ............................................................................................................ 958 Dropping a PDB ................................................................................................................... 959 Refreshable Clone PDB ............................................................................................................. 960 Databases in the Cloud ............................................................................................................. 961 Summary .................................................................................................................................. 961 Index ................................................................................................................. 963
The Expert\'s Voice® In Oracle - Malcher M., Kunh D. / Малчер М., Кун Д. - Pro Oracle Database 18c Administration, 3rd edition / Профессиональное администрирование Oracle Database 18c, 3-е издание [2019, PDF, ENG] download torrent for free and without registration
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
![[Quote]](./templates/default/images/lang/en/icon_quote.gif "Reply with quote"){kind=icon}
![[Profile]](./templates/default/images/lang/en/icon_profile.gif "View user's profile"){kind=icon}
![[PM]](./templates/default/images/lang/en/icon_pm.gif "Send private message"){kind=icon}
