Preface Knowledge has a characteristic, that is, the higher the degree of sharing, the faster it grows. This is what motivated me to write and complete this book. This book condenses the experience of database management in recent years. I hope the many experiences and tips in the book will help readers overcome their learning difficulties. How to read this book The chapters in this book are divided into several aspects according to their content. This book should be read from cover to cover. The time spent studying this book will pay off many times over in your future work. You can also adopt a modular approach and selectively read chapters that are more suitable for your knowledge level and purpose. If you are not familiar with Oracle or want to refresh your basic knowledge, you can start by reading Appendix A, which provides a concise introduction to Oracle, and then read the entire book from beginning to end. If you already have an understanding of Oracle's landscape and basics, you can start learning from any chapter that suits you or interests you. For example, those who need to learn the new features of Oracle8i's new database management in order to update their knowledge can read Chapter 9 "New Features of Oracle8i" before reading other chapters. Readers who wish to learn about Oracle's Internet computing capabilities and Oracle Application Server can refer directly to Chapter 10, "Internet DBA." The other chapters also cover a specific important area. How to Increase Knowledge The key to success in a certain field is to continuously enrich your knowledge. Knowledge is the decisive factor in competition. Because technology is developing at a rapid pace, we should all keep up with the pace of technological development. Visit the online community of database professionals (http://www.dbtips.com) to download many useful scripts, including many that appear in this book, as well as get a free copy of "How to Create SQL Scripts That Work," This copy provides tips and techniques for creating and customizing scripts to better suit your needs. I will be posting new tips and techniques, scripts, essays, and articles on this site. You are welcome to post your thoughts, experiences, questions, answers and comments on this site to share and exchange knowledge with everyone.
Table of contents:
Preface to the Greetings Chapter 1 Establishing and Configuring the Database
1.1 Database creation planning
1.1.1 Planning and asking the right questions
1.1.2 How to determine the appropriate data block size
1.2 Organizing file systems
1.2.1 How to name database files
1.2.2 Use the best flexible structure
1.2.3 How to configure an Oracle file system that complies with OFA
1.3 Plan database file layout
1.3.1 Planning to maximize availability
1.3.2 Planning to minimize disk contention
1.4 Create parameter file
1.4.1 Some notes on configuration parameters
1.4.2 Establish a connection to the parameter file
1.4.3 Optimize data dictionary storage
1.5 Understanding the CREATE DATABASE command
1.6 Techniques for creating databases
1.6.1 Create database using Oracle installer
1.6.2 Things to note when using the installer to create a database
1.6.3 How to create your own custom database creation script
1.6.4 How to clone a database from an existing database
1.6.5 How to use Database Configuration
Assistant creates database
1.7 Complete database configuration
1.7.1 Create table space
1.7.2 Execute data dictionary configuration script
1.7.3 Create additional rollback segments
1.7.4 Modify the default and temporary table spaces of the SYSTEM user
1.7.5 Change the default passwords of SYS and SYSTEM
1.7.6 Creating other user and schema objects
1.7.7 Enable archive log mode
1.7.8 Perform full database backup
1.7.9 Configuring database automatic startup and shutdown
1.7.10 Monitor the database after it is put into use
1.7.11 How to list and describe initialization parameters
1.7.12 How to list undocumented parameters
1.8 Review Chapter 2 Managing Data Storage, Objects, and Capacity
2.1 Configure data storage
2.1.1 Managing free space fragmentation
2.1.2 Techniques for aggregating available space
2.1.3 Fragments of the management area
2.1.4 Find objects close to the MAXEXTENTS value
2.1.5 Avoid data dictionary fragmentation
2.1.6 Local management area
2.1.7 Reorganize the entire database
2.1.8 11 Tips for Defining Extent Sizes and Preventing Fragmentation
2.1.9 Avoid distinguishing super errors
2.1.10 Avoid space excess errors
2.1.11 Minimizing row chaining and row migration
2.1.12 How to detect row linking/row migration
2.1.13 Check link lines in patterns using scripts
2.1.14 Eliminate row links
2.1.15 Eliminate row migration
2.1.16 Row linking/row migration techniques
2.1.17 How to define the size of the table
2.1.18 How to determine the optimal value of PCTFREE
2.1.19 How to determine the optimal value of PCTUSED
2.1.20 How to find the available data area of each data block
2.2 Manager objects
2.2.1 How to check for invalid objects
2.2.2 How to recompile invalid objects
2.2.3 Moving indexes between different table spaces
2.2.4 How to find the highest point
2.2.5 How to release unused space
2.3 DBMS_SPACE package
2.3.1 Using DBMS_SPACE.UNUSED
_SPACE
2.3.2 Using DBMS_SPACE.FREE
_BLOCK
2.4 Managing lock contention
2.4.1 How to find the SQL statement that generated the lock
2.4.2 How to release the lock
2.4.3 How to delete user session
2.4.4 How to extract view creation commands from the data dictionary
2.4.5 How to extract index definitions from the data dictionary
2.5 Review Chapter 3 Export and Import Technology
3.1 Overview of export/import features
3.1.1 Using export and import
3.1.2 General use of export/import
3.1.3 Export method
3.1.4 Import method
3.1.5 Create the necessary data dictionary views
3.1.6 Specify export/import parameters
3.1.7 Export and import data directly on tape
3.1.8 Estimating export file size
3.1.9 Export compressed files directly
3.1.10 Import directly from compressed export file
3.1.11 Create consistent export files
3.1.12 Check for export/import errors
3.1.13 Using export and import management areas
3.1.14 Organizing and naming export/import files
3.1.15 Display the contents of the exported file
3.1.16 You cannot use export and archive redo logs simultaneously for recovery
3.1.17 Incremental, cumulative and full export and import
3.2 How to optimize export performance
3.2.1 Export using DIRECT path
3.2.2 Use large for traditional path export
BUFFER value
3.2.3 Using RECORDL- for direct path export
ENGHT parameters
3.3 How to optimize import parameters
3.3.1 Using large rollback segments
3.3.2 Create several large online redo log files
3.3.3 Turn off archiving mode during import
3.3.4 Export dump files, data table spaces,
Rollback segments and online redo log files
3.3.5 Minimizing checkpoints
3.3.6 Create indexes separately
3.3.7 Set large initialization parameter SORT_AREA
_SIZE
3.3.8 Using large import buffers
3.3.9 Minimize the number of database submissions
3.4 New features of Oracle8i
3.4.1 Export and import of subpartitions
3.4.2 Export/import multiple dump files
3.4.3 Specify a query for the select statement of the export process of the unloaded table
3.4.4 Export/import precomputed optimizer statistics
3.4.5 Movable table spaces
3.5 Review Chapter 4 Designing a Highly Available Database
4.1 How to discover and protect “Achilles’ Weaknesses”
4.2 Reuse database control files
4.3 Mirroring control files at the hardware level
4.4 The difference between mirroring and reuse
4.5 Select disk type for high availability
4.6 General RAID techniques
4.7 Give control files room to grow
4.8 How to configure a log group
4.9 Three simple methods to protect the SYSTEM table space
4.10 Why ORACLE_HOME must be protected
4.11 Securing the operating system
4.12 How to protect rollback segments
4.13 Classifying and partitioning data
4.14 Prioritize table space partitioning
4.15 How to configure a highly available TEMP table space
4.16 Ensure there is sufficient free space in the archive log destination location
4.17 How to adjust online redo logs
4.18 Avoid LGWR waits by optimizing archive speed
4.19 Separate online redo logs and archived redo log files
4.20 How to speed up instance recovery
4.21 Review of Chapter 5 Using Hot Standby Databases
5.1 What is failover?
5.2 Hot standby database
5.2.1 Advantages of hot standby database
5.2.2 Disadvantages of hot standby databases
5.2.3 Establish and configure a hot standby database
5.2.4 Creating a standby database as a subset of the primary database
5.2.5 Select alternate site
5.2.6 Select backup node
5.2.7 Perform failover of hot standby
5.2.8 Three points to remember about backup activation
5.2.9 Four tasks after standby activation
5.2.10 Automatically transfer archived redo logs to the standby database
5.2.11 Starting administrative recovery
5.2.12 Open standby database read-only
5.2.13 Maintenance of hot standby database
5.3 Review of Chapter 6 Advanced Failed Switchover Methods
6.1 Using Oracle Parallel Server for failover
6.2 Advantages of parallel server failover
6.3 Disadvantages of parallel server failure switching
6.4 Performance considerations
6.5 Reduce the time for failed switching of parallel servers
6.6 Transparent application failure switching
6.7 Instructions for failed client switching
6.8 Using client failover for load balancing
6.9 Using client failover for scheduled node outage
6.10 Use Oracle replication to implement failover
6.10.1 Basic copying
6.10.2 Advantages of basic replication failover
6.10.3 Disadvantages of Basic Replication Failure Switchover
6.11 Using advanced replication for failover
6.11.1 Advantages of advanced replication failover
6.11.2 Disadvantages of advanced replication failover
6.12 Using Oracle Fail Safe
6.12.1 Select node configuration
6.12.2 Advantages of Oracle Fail Safe
6.12.3 Disadvantages of Oracle Fail Safe
6.13 Other high availability options
6.13.1 High Availability on a Shoelace Budget: Shipping Backup Tapes
6.13.2 Using remote mirroring
6.13.3 Application Mirroring
6.14 Leveraging hybrid configuration techniques
6.14.1 Utilizing parallel servers and remote hot standby databases
6.14.2 Using Oracle Fail Safe with a Remote Hot Standby Database (Windows NT Only
platform is valid)
6.14.3 Utilizing hot standby databases and remote mirroring
6.15 Review of Chapter 7 Backup and Recovery Techniques
7.1 Backup options
7.2 Operating system backup
7.2.1 Cold backup
7.2.2 OFA and backup
7.2.3 Hot backup
7.2.4 Minimizing the duration of backup methods
7.2.5 Why you should not back up online redo logs
7.2.6 ARCHIVELOG and NOARCHIV-
ELOG mode
7.2.7 Why you should use ARCHIVELOG
Way
7.2.8 Why archived redo logs should be reused
7.3 Server Management Backup
7.4 Logical backup
7.4.1 Text file backup
7.4.2 Logical control file backup
7.4.3 Using export and import
7.4.4 Import method
7.4.5 Incremental, cumulative and full export and import
7.4.6 Create consistent export files
7.4.7 You cannot use export and archived redo logs at the same time for recovery
7.5 Develop a backup and recovery plan
7.5.1 General backup techniques
7.5.2 Tips for quick backup
7.6 Recovery strategies and situations
7.6.1 Database recovery and involved database structure organization
7.7 Various situations requiring recovery
7.8 Recover lost data files
7.8.1 Loss of SYSTEM data files
7.8.2 Loss of data files containing active rollback segments
7.8.3 Loss of other data files
7.8.4 Performing table space recovery
7.8.5 Perform data file recovery
7.8.6 How to restore when there is no data file backup
7.8.7 Recovering a lost temporary tablespace
7.8.8 Read-only table space is lost
7.8.9 Index table space is lost
7.9 Restoring online redo logs
7.9.1 A member of the online redo log group is lost
7.9.2 Loss of inactive redo log groups
7.9.3 Loss of active redo log group
7.10 Recovering lost control files
7.10.1 The reused control file members are lost
7.10.2 Control file is completely lost
7.11 Review Chapter 8 Performance Optimization
8.1 Technology to optimize the global area of the system
8.1.1 How to optimize the data buffer cache
8.1.2 Measuring and optimizing library cache performance
8.1.3 Measuring and optimizing dictionary cache performance
8.1.4 How to estimate application performance
8.2 Fixed application code
8.2.1 How to determine the objects that should be fixed
8.2.2 How to determine the currently fixed program object
8.2.3 How to pin application code
8.2.4 Using DBMS_SHARED_POOL.KEEP
skills
8.2.5 How to generate scripts for fixed operations
8.2.6 Using DBMS_SHARED_POOL
.UNKEEP TIPS
8.3 Techniques for optimizing data sorting
8.3.1 Do all or most of the sorting in memory
8.3.2 Minimizing space management overhead during sorting
8.3.3 Using multiple TEMP table spaces to distribute sorting
8.4 Technologies to optimize data storage
8.4.1 Minimizing row chaining and row migration
8.4.2 Detecting row linking/migration
8.4.3 Determining linked lines in a schema
8.4.4 Preventing and correcting row chaining/migration
8.4.5 Tips for row linking/migration
8.4.6 Minimizing Free Space Fragmentation
8.4.7 Minimizing zone growth
8.4.8 How to determine the most expensive query
8.5 Tracing SQL
8.5.1 Setting up tracking within a user session
8.5.2 Setting up out-of-user session tracing using DBMS_SYSTEM
8.5.3 Generating system traces
8.5.4 Use tkprof to interpret trace files
8.5.5 Use AUTOTRACE to obtain SQL statement execution plan and statistical data
8.6 Optimize rollback section
8.6.1 Minimizing rollback segment contention
8.6.2 Minimizing dynamic expansion
8.6.3 Distributed rollback segment I/O
8.7 Optimizing indexes
8.7.1 How to identify and rebuild fragmented indexes
8.7.2 How to determine the index of the table
8.8 Optimizing disk I/O
8.8.1 How to find and avoid I/O hot spots
8.8.2 Using raw file systems
8.9 Generating optimizer statistics
8.9.1 Using DBMS_UTILITY.ANALYZE
_SCHEMA collects statistical data
8.9.2 Using DBMS_UTILITY.ANALYZE
_DATABASE Collect statistics
8.9.3 Using DBMS_STATS to collect performance statistics
8.10 Optimize the environment
8.10.1 How to optimize Net8/SQL*Net
8.10.2 Monitor and optimize system resources
8.11 Review of Chapter 9 New Features of Oracle8i
9.1 New performance optimization features
9.1.1 Design Stability and Storage Summary
9.1.2 Sorting improvements
9.1.3 Materialized views
9.1.4 Using DBMS_STATS to collect performance statistics
9.2 New index type
9.2.1 Function-based indexing
9.2.2 Reverse key index
9.2.3 Descending index
9.2.4 Indexed tables
9.2.5 New features for managing indexes
9.3 New backup and recovery features
9.3.1 Multi-target archiving
9.3.2 Multiple archive log processes
9.3.3 Using LogMiner
9.3.4 Quick Start Recovery
9.4 New export/import features
9.4.1 Using multiple export/import dump files
9.4.2 Selective query export
9.4.3 Export of precomputed optimizer statistics
/import
9.4.4 Movable table spaces
9.4.5 Mixed export/import features
9.5 New Standby Database Features
9.5.1 Automatic transfer of archived redo logs
9.5.2 Enabling administrative recovery
9.5.3 Open the standby database in read-only mode
9.6 New features for managing job queues
9.7 New features for managing storage and objects
9.7.1 Repositioning and organizing tables
9.7.2 Delete columns in a table
9.7.3 Mark table columns as unavailable
9.7.4 Locally managed tablespaces
9.8 Review of Chapter 10 Internet DBA
10.1 Oracle8i - Internet Database
10.2 Oracle and Java
10.3 Oracle8i Java Virtual Machine
10.4 How to communicate between Java VM and Oracle8i server
10.5 Java and Oracle Application Server
10.6 Oracle Jdeveloper
10.7 Java-related initialization parameters
10.8 Oracle Internet File System
10.9 Oracle Application Server Overview
10.10 OAS components
10.11 Adjusting OAS memory requests
10.12 Check recommended minimum hardware
10.13 Designing Oracle Application Server Configuration
10.14 Installation of Oracle Application Server
10.15 Using OAS Manager
10.16 How to start and stop OAS components
10.17 How OAS utilizes load balancing
10.18 Monitor CPU consumption
10.19 Monitor memory consumption
10.20 Review of Chapter 11 Using Packages Provided by Oracle
11.1.1 Packages: What are they?
11.1 DBMS_JOB and DBMS_IJOB packages
11.1.1 Concept of Oracle Job Queue
11.1.2 Tips for configuring SNP processes
11.1.3 Using DBMS_JOB and DBMS_IJOB
11.1.4 Scheduling jobs
11.1.5 Change jobs
11.1.6 Stop the job
11.1.7 Other job queue processes
11.1.8 Monitor job queue
11.1.9 Managing jobs belonging to other users
11.1.10 Periodic analysis of schema objects using job queues
11.2 DBMS_SYSTEM package
11.2.1 Using DBMS_SYSTEM.SET_SQL
_TRACE_IN_SESSION
11.2.2 Using DBMS_SYSTEM.SET_EV
11.2.3 Using DBMS_SYSTEM.READ_EV
11.2.4 Determining the event level set in the current session
11.2.5 Other DBMS_SYSTEM programs
11.3 DBMS_SPACE package
11.3.1 Using DBMS_SPACE.UNUSED
_SPACE
11.3.2 Using DBMS_SPACE.FREE
_BLOCKS
11.4 DBMS_SHARED_POOL package
11.4.1 Using DBMS_SHARED_POOL.SIZES
process
11.4.2 Using DBMS_SHARED_POOL.SIZES
skills
11.4.3 Using DBMS_SHARED_POOL.KEEP
process
11.4.4 Using DBMS_SHARED_POOL.KEEP
skills
11.4.5 Using DBMS_SHARED_POOL.UNKEEP
process
11.4.6 Using DBMS_SHARED_POOL.UNKEEP
skills
11.4.7 Using DBMS_SHARED_POOL
.ABORTED_REQUEST_THRESHOLD
process
11.4.8 Using DBMS_SHARED_POOL
.ABORTED_REQUEST_THRESHOLD
skills
11.5 DBMS_UTILITY
11.5.1 Using DBMS_UTILITY.COMPILE
_SCHEMA process
11.5.2 Using DBMS_UTILITY.COMPILE
_SCHEMA'S TIPS
11.5.3 Using DBMS_UTILITY.ANALYZE
_SCHEMA
11.5.4 Using DBMS_UTILITY.ANALYZE
_DATABASE
11.5.5 Using DBMS_UTILITY.GET
_PARAMETER_VALUE
11.5.6 Using DBMS_UTILITY.PORT
_STRING
11.5.7 Using DBMS_UTILITY.DB
_VERSION
11.5.8 Using DBMS_UTILITY.MAKE_DATA
_BLOCK_ADDRESS
11.5.9 Using DBMS_UTILITY.DATA_BLOCK
_ADDRESS_FILE
11.5.10 Using DBMS_UTILITY.DATA_BLOCK
_ADDRESS_BLOCK
11.5.11 Using DBMS_UTILITY.IS_PARALLEL
_SERVER
11.5.12 Using DBMS_UTILITY.CURRENT
_INSTANCE
11.5.13 Using DBMS_UTILITY.ACTIVE
_INSTANCES
11.6 DBMS_ROWID
11.6.1 Using DBMS_ROWID.ROWID
_BLOCK_NUMBER
11.6.2 Using DBMS_ROWID.ROWID
_CREATE
11.6.3 Using DBMS_ROWID.ROWID
_OBJECT
11.6.4 Using DBMS_ROWID.ROWID
_RELATIVE_FNO
11.6.5 Using DBMS_ROWID.ROWID_ROW
_NUMBER
11.6.6 Using DBMS_ROWID.ROWID_TO
_ABSOLUTE_FNO
11.6.7 Using DBMS_ROWID.ROWID
_TO_EXTENDED
11.6.8 Using DBMS_ROWID.ROWID_TO
_RESTRICTED
11.6.9 Using DBMS_ROWID.ROWID
_TYPE
11.6.10 Using DBMS_ROWID.ROWID
_VERIFY
11.6.11 Using DBMS_ROWID.ROWID
_INFO
11.7 Review Chapter 12 Oracle Installation and Upgrade
12.1 Oracle Software
12.2 Oracle Database
12.3 Configuring Oracle file system
12.3.1 Optimum Flexible Structure
12.3.2 How to configure an Oracle that complies with OFA
file system
12.3.3 How to name database files
12.3.4 Why you should use links for parameter files
12.4 Install Oracle server software
12.5 Pre-installation phase
12.5.1 Rule 1: Ensure software product version compatibility
12.5.2 Rule 2: Configuring the operating system for Oracle
12.5.3 Rule 3: Allocate enough disk space
12.5.4 Rule 4: Configure the installation environment
12.6 Installation phase
12.6.1 Step 1: Configure the installation environment
12.6.2 Step 2: Start Oracle installation
12.6.3 Step 3: Select and install software products
12.7 Post-Installation Phase: Stick to Five Points
12.7.1 Step 1: Check for errors
12.7.2 Step 2: Run the root.sh script
12.7.3 Step 3: Verify file permissions
12.7.4 Step 4: Test the Oracle installation
12.7.5 Step 5: Configure the environment
12.7.6 Other post-installation tasks
12.8 Five tips for quick Oracle installation
12.8.1 Installation from the preparation area of the hard disk
12.8.2 Documentation not installed
12.8.3 Documentation without installing the product
12.8.4 Reconnecting executables without selection
12.8.5 Set environment variable DEF_INSTALL =
TRUE and NO_README = TRUE
12.9 Installing Oracle on Windows NT
12.9.1 Pre-installation phase
12.9.2 Installation phase
12.9.3 Post-installation phase
12.10 Patching, Upgrading and Migrating Oracle
12.10.1 Always install Oracle software into a new ORACLE_HOME directory
12.10.2 Perform database cold backup before database upgrade
12.10.3 Migrating Oracle
12.10.4 Using Migration Utility to migrate to
Oracle 8.0.x
12.10.5 Migrating with export and import
12.11 Installing Oracle 8 on UNIX
12.11.1 Pre-installation phase
12.11.2 Installation phase
12.11.3 Post-installation phase
12.12 Review Appendix A Oracle Server Introduction Appendix B Dynamic Performance (V$) View
Expand
