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