ebook img

Pro Oracle Database 12c Administration, 2nd Edition PDF

746 Pages·2013·8.36 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Pro Oracle Database 12c Administration, 2nd Edition

www.it-ebooks.info For your convenience Apress has placed some of the front matter material after the index. Please use the Bookmarks and Contents at a Glance links to access them. www.it-ebooks.info Contents at a Glance About the Author �����������������������������������������������������������������������������������������������������������xxxiii About the Technical Reviewers ��������������������������������������������������������������������������������������xxxv Acknowledgments ��������������������������������������������������������������������������������������������������������xxxvii Introduction �������������������������������������������������������������������������������������������������������������������xxxix ■ Chapter 1: Installing the Oracle Binaries ���������������������������������������������������������������������������1 ■ Chapter 2: Implementing a Database ������������������������������������������������������������������������������25 ■ Chapter 3: Configuring an Efficient Environment ������������������������������������������������������������55 ■ Chapter 4: Tablespaces and Data Files ����������������������������������������������������������������������������77 ■ Chapter 5: Managing Control Files, Online Redo Logs, and Archiving �����������������������������99 ■ Chapter 6: Users and Basic Security �����������������������������������������������������������������������������127 ■ Chapter 7: Tables and Constraints ���������������������������������������������������������������������������������153 ■ Chapter 8: Indexes ���������������������������������������������������������������������������������������������������������205 ■ Chapter 9: Views, Synonyms, and Sequences ���������������������������������������������������������������237 ■ Chapter 10: Data Dictionary Fundamentals �������������������������������������������������������������������259 ■ Chapter 11: Large Objects ���������������������������������������������������������������������������������������������277 ■ Chapter 12: Partitioning: Divide and Conquer ���������������������������������������������������������������303 ■ Chapter 13: Data Pump��������������������������������������������������������������������������������������������������343 ■ Chapter 14: External Tables �������������������������������������������������������������������������������������������387 ■ Chapter 15: Materialized Views�������������������������������������������������������������������������������������403 ■ Chapter 16: User-Managed Backup and Recovery ��������������������������������������������������������451 v www.it-ebooks.info ■ Contents at a GlanCe ■ Chapter 17: Configuring RMAN ��������������������������������������������������������������������������������������483 ■ Chapter 18: RMAN Backups and Reporting �������������������������������������������������������������������511 ■ Chapter 19: RMAN Restore and Recovery ���������������������������������������������������������������������539 ■ Chapter 20: Oracle Secure Backup ��������������������������������������������������������������������������������585 ■ Chapter 21: Automating Jobs ����������������������������������������������������������������������������������������613 ■ Chapter 22: Database Troubleshooting ��������������������������������������������������������������������������637 ■ Chapter 23: Pluggable Databases ���������������������������������������������������������������������������������667 Index ���������������������������������������������������������������������������������������������������������������������������������699 vi www.it-ebooks.info Introduction Many companies, large and small, use Oracle products. At the heart of this technology is an Oracle database. Businesses use the technology to store and manage mission critical data. This information is the basis for making smart business decisions. Companies that effectively transform data into business intelligence quickly gain a competitive edge in the marketplace. Oracle database administrators (DBAs) play a pivotal role in implementing and leveraging Oracle database technology. DBAs add value by ensuring that databases are created in an efficient manner and optimally maintained. DBAs are often queried for architectural advice on features, implementation, data migration, replication, SQL coding, tuning, and so on. DBAs fill the role of the go-to person for anything related to Oracle. The job of an Oracle DBA is frequently complex and challenging. This book focuses on practical examples and techniques for providing a smoothly operating database environment. The content is drawn from years of experience working with Oracle technology. The book shows you from the ground up how a senior DBA manages a multifaceted database environment. I try to focus on demonstrating how to correctly implement features, with scalability and maintainability in mind. I hope you find the material in this book useful. The goal is to elevate you to a professional level as a DBA. Being a DBA doesn’t have to be constantly painful. The key is to correctly implement the technology the first time, not paint yourself into a corner with a badly implemented feature, and proactively manage your surroundings. This book doesn’t show you the most complex and sophisticated techniques used in database administration. I try to keep my techniques as simple as possible, yet robust enough to manage any level of chaos and complexity. You should be able to take the concepts elucidated in this book and build on them to help you manage any type of database environment. Who This Book Is For This book is for DBAs who want real-world guidance on how to efficiently configure and maintain complex database environments. Whether you are a novice or an expert, this book contains practical examples of how to implement Oracle database technology. This book is for those who want advice from a real DBA on how Oracle database technology is effectively implemented and maintained. How This Book Is Structured The book is divided into several sections, each covering a logical group of database administration topics, as follows: Part 1 concentrates on creating a working environment. This includes installing the Oracle software and creating databases. Part 2 deals with managing critical database files. Topics explored are tablespaces, data files, control files, and online redo log files. Part 3 discusses configuring users and database objects, such as tables, constraints, indexes, views, synonyms, sequences, and so on. xxxix www.it-ebooks.info ■ IntroduCtIon Part 4 details how to create and maintain large database objects and partitioned tables and indexes. Part 5 shows how DBAs use tools such as Data Pump, external tables, and materialized views to manage and distribute large amounts of data. Part 6 takes a deep dive into backup-and-recovery (B&R) concepts. Both user-managed backups and Oracle Recovery Manager (RMAN) B&R are presented in detail. Part 7 focuses on techniques used to automate database jobs and how to troubleshoot typical problems that DBAs encounter. Part 8 describes how to implement and manage container and pluggable databases. Conventions The following typographical conventions are used in this book: • $ is used to denote Linux/Unix commands that can be run by the operating system (OS) owner of the Oracle binaries (usually named oracle). • # is used to denote Linux/Unix commands that should be run as the root OS user. • SQL > is used to denote one-line SQL*Plus statements. • Monospaced font is used for code examples, utility names, file names, URLs, and directory paths. • Italic is used to highlight a new concept or term. • UPPERCASE indicates names of database objects, such as views, tables, and corresponding column names. • < > is used where you need to provide input, such as a file name or password. Downloading the Code The code for the examples shown in this book is available on the Apress web site (www.apress.com). A link can be found on the book’s information page, under the Source Code/Downloads tab. This tab is located beneath the Related Titles section of the page. Contacting the Author If you have any questions regarding the book, please feel free to contact me directly at the following e-mail address: [email protected]. xl www.it-ebooks.info Chapter 1 Installing the Oracle Binaries Oracle installations can be large, complex, and cumbersome. This is one reason you usually ask an Oracle database administrator (DBA) to install the software. You want someone who has previously performed installations and who knows how to troubleshoot when problems arise. Accordingly, installing the Oracle software (binaries) is a task at which every DBA must be proficient. ■ Tip If you’re fairly new to Oracle, this chapter may seem like an overwhelming way to start a book on database administration. Don’t worry too much about this. If you’re already working in an Oracle environment, chances are that another DBA has probably already installed the Oracle binaries. If you don’t need to install the Oracle binaries, make sure you read the following section, “Understanding the Optimal Flexible Architecture,” and then feel free to proceed to Chapter 2. Many DBAs don’t use techniques for automating installations. Some are unaware of these methods; others perceive them as unreliable. Therefore, most DBAs typically use the graphical mode of the Oracle Universal Installer (OUI). Although the graphical installer is a good tool, it doesn’t lend itself to repeatability and automation. Running the graphical installer is a manual process during which you’re presented with options to choose from on multiple screens. Even if you know which options to select, you may still inadvertently click an undesired choice. The graphical installer can also be problematic when you’re performing remote installations, and the network bandwidth is insufficient. In these situations you can find yourself waiting for dozens of minutes for a screen to repaint itself on your local screen. You need a different technique for efficient installation on remote servers. This chapter focuses on techniques for installing Oracle in an efficient and repeatable manner. This includes silent installations, which rely on a response file. A response file is a text file in which you assign values to variables that govern the installation. DBAs often don’t realize the powerful repeatability and efficiency that can be achieved by using response files. ■ Note This chapter only covers installing the Oracle software. The task of creating a database is covered in Chapter 2. Understanding the OFA Before you install Oracle and start creating databases, you must understand Oracle’s Optimal Flexible Architecture (OFA) standard. This standard is widely employed for specifying consistent directory structures and the file-naming conventions used when installing and creating Oracle databases. 1 www.it-ebooks.info ChApTer 1 ■ InsTAllIng The OrACle BInArIes ■ Note One irony of this ubiquitous OFA “standard” is that almost every DBA, in some manner, customizes it to fit the unique requirements of his or her environment. Because most shops implement a form of the OFA standard, understanding this structure is critical. Figure 1-1 shows the directory structure and file names used with the OFA standard. Not all the directories and files found in an Oracle environment appear in this figure (there isn’t enough room). However, the critical and most frequently used directories and files are displayed. / rdbms audit *.aud root directory logs installActions<date>.log admin sql files /etc oraInst.loc oraInventory ContentsXML inventory.xml bin binaries: oracle, sqlplus, oratab rman, lsnrctl, expdp, oraenv,... oraset product (v1e2r.s1io.0n) ORACdLbE__1HOME network admin lsiqstlneenter..oorraa TNS_ADMIN /u01…/u0N app oracle cfgtoollogs tnsnames .ora ORACLE_BASE adump dbs spfile or init.ora /var/opt/oracle oraInst.loc checkpoints cdump orapw pwd file (Solaris) oratab bdump alert_dbname1.log oraset admin dbname1 udump 10g diagnostic info (old databases) /home HoOraMclEe ...bbpaarossfhhil_recprofile tnslsnr host scrlsipntasme alert log.xml trace alert_instname1.log diag diagnostic_dest rdbms dbuname1 instname1 alert log.xml (init parameter) ADR_HOME incident 12c diagnostic info log_archive_dest_N archive redo log oradata dbname1 datafiles, online redo logs, controlfiles: (init parameter) tbspNN .dbf, redoNN.log, control.ctl Fast Recovery Area (optional FRA) backupset YYYY_MM_DD backup piece autobackup YYYY_MM_DD backup piece db_recovery_file_dest (init parameter) dbname1 datafile image copy onlinelog online redo log controlfile control file archivelog YYYY_MM_DD archive redo log flashback flashback log Figure 1-1. Oracle’s OFA standard The OFA standard includes several directories that you should be familiar with: • Oracle inventory directory • Oracle base directory (ORACLE_BASE) • Oracle home directory (ORACLE_HOME) • Oracle network files directory (TNS_ADMIN) • Automatic Diagnostic Repository (ADR_HOME) These directories are discussed in the following sections. 2 www.it-ebooks.info ChApTer 1 ■ InsTAllIng The OrACle BInArIes Oracle Inventory Directory The Oracle inventory directory stores the inventory of Oracle software installed on the server. This directory is required and is shared among all installations of Oracle software on a server. When you first install Oracle, the installer checks to see whether there is an existing OFA-compliant directory structure in the format /u[01–09]/app. If such a directory exists, then the installer creates an Oracle inventory directory, such as /u01/app/oraInventory If the ORACLE_BASE variable is defined for the oracle operating system (OS) user, then the installer creates a directory for the location of Oracle inventory, as follows: ORACLE_BASE/../oraInventory For example, if ORACLE_BASE is defined as /ora01/app/oracle, then the installer defines the location of Oracle inventory as /ora01/app/oraInventory If the installer doesn’t find a recognizable OFA-compliant directory structure or an ORACLE_BASE variable, then the location for Oracle inventory is created under the HOME directory of the oracle user. For instance, if the HOME directory is /home/oracle, then the location of Oracle inventory is /home/oracle/oraInventory Oracle Base Directory The Oracle base directory is the topmost directory for Oracle software installation. You can install one or more versions of the Oracle software beneath this directory. The OFA standard for the Oracle base directory is as follows: /<mount_point>/app/<software_owner> Typical names for the mount point include /u01, /ora01, /oracle, and /oracle01. You can name the mount point according to whatever your standard is for your environment. I prefer to use a mount-point name such as /ora01. It’s short, and when I look at the mount points on a database server, I can immediately tell which are used for the Oracle database. Also, a short mount-point name is easier to use when you’re querying the data dictionary to report on the physical aspects of your database. Additionally, a shorter mount-point name makes for less typing when you’re navigating through directories via OS commands. The software owner is typically named oracle. This is the OS user you use to install the Oracle software (binaries). Listed next is an example of a fully formed Oracle base directory path: /u01/app/oracle Oracle Home Directory The Oracle home directory defines the installation location of software for a particular product, such as Oracle Database 12c or Oracle Database 11g. You must install different products or different releases of a product in separate Oracle homes. The recommended OFA-compliant Oracle home directory is as follows: ORACLE_BASE/product/<version>/<install_name> 3 www.it-ebooks.info ChApTer 1 ■ InsTAllIng The OrACle BInArIes In the previous line of code, possible versions include 12.1.0.1 and 11.2.0.3. Possible install_name values include db_1, devdb1, test2, and prod1. Here is an example of an Oracle home name for a 12.1 database: /u01/app/oracle/product/12.1.0.1/db_1 ■ Note some DBAs dislike the db_1 string on the end of the ORACLE_HOME directory and see no need for it. The reason for the db_1 is that you may have two separate installations of binaries: a development installation and a test installation. If you don’t require that configuration in your environment, feel free to drop the extra string (db_1). Oracle Network Files Directory Some Oracle utilities use the value TNS_ADMIN to locate network configuration files. This directory is defined as ORACLE_HOME/network/admin. It typically contains the tnsnames.ora and listener.ora Oracle Net files. ■ Tip sometimes DBAs will set TNS_ADMIN to point at one central directory location (such as /etc or /var/opt/oracle). This allows them to maintain one set of Oracle network files (instead of one for each ORACLE_HOME). This approach also has the advantage of not requiring the copying or moving of files when a database upgrade occurs, potentially changing the location of ORACLE_HOME. Automatic Diagnostic Repository Starting with Oracle Database 11g, the ADR_HOME directory specifies the location of the diagnostic files related to Oracle. These files are crucial for troubleshooting problems with the Oracle database. This directory is defined as ORACLE_BASE/diag/rdbms/lower(db_unique_name)/instance_name. You can query the V$PARAMETER view to get the values of db_unique_name and instance_name. For example, in the next line, the lowercase database unique name is o12c, and the instance name is O12C: /u01/app/oracle/diag/rdbms/o12c/O12C You can verify the location of the ADR_HOME directory via this query: SQL> select value from v$diag_info where name='ADR Home'; Here is some sample output: VALUE ------------------------------------------------------------ /u01/app/oracle/diag/rdbms/o12c/O12C Now that you understand the OFA standard, you’ll next see how it’s used when installing the Oracle binaries. For instance, you’ll need to specify directory values for the ORACLE_BASE and ORACLE_HOME directories when running the Oracle installer. 4 www.it-ebooks.info

Description:
Pro Oracle Database 12c Administration is a book focused on results. Author Darl Kuhn draws from a well of experience over a decade deep to lay out real-world techniques that lead to success as an Oracle Database administrator. He gives clear explanations on how to perform critical tasks. He weaves
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.