Oracle Processes
Oracle server processes running on the operating system perform all the database operations, such as inserting and deleting data. These Oracle processes, together with the memory structures allocated to Oracle by the operating system, form the working Oracle instance. There is a set of mandatory Oracle processes that need to be up and running for the database to function at all. In addition, other processes exist that are necessary only if you are using certain specialized features of the Oracle databases (e.g., replicated databases). This section's focus is on the set of mandatory processes that every Oracle instance uses to perform database activities.
Processes are essentially a connection or thread to the operating system that performs a task or job. The Oracle processes you'll encounter in this section are continuous, in the sense that they come up when the instance starts, and they stay up for the duration of the instance's life. Thus, they act like Oracle's "hooks" into the operating system resources. Note that what I call a process on a UNIX system is analogous to a thread on a Windows system.
Oracle instances need two different types of processes to perform various types of database activities. This distinction is made for efficiency purposes and the need to keep client processes separate from the database server's tasks. The first set of processes is called user processes. These processes are responsible for running the application that connects the user to the database instance. The second and more important set of processes is called the Oracle processes. These processes perform the Oracle server's tasks, and you can divide the Oracle processes into two major categories: the server processes and the background processes. Together, these processes perform all the actual work of the database, from managing connections to writing to the logs and data files to monitoring the user processes.
Interaction Between the User and Oracle Processes
User processes execute program code of application programs and that of Oracle tools such as SQL*Plus. The user processes communicate with the server processes through the user interface. These processes request that the Oracle server processes perform work on their behalf. Oracle responds by using its server processes to service the user processes' requests.
When you connect to an Oracle database, you have to use software such as SQL Net, which is Oracle's proprietary networking tool. Regardless of how you connect and establish a session with the database instance, Oracle has to manage these connections. The Oracle server creates server processes to manage the user connections. It's the job of the server processes to monitor the connection, accept requests for data, and so forth from the database, and hand the results back from the database itself. All selects, for example, involve reading data from the database, and it's the server processes that bring the output of the select statement back to the users.
You'll examine the two types of Oracle processes, the server processes and the background processes, in detail in the following sections.
The Server Process
The server process is the process that services the user process. The server process is responsible for all interaction between the user and the database. When the user submits requests to select data, for example, the server process checks the syntax of the code and executes the SQL code. It will then read the data from the data files into the memory blocks. If another user intends to read the same data, the new user's server process will read it not from disk again, but from Oracle's memory, where it usually remains for a while. Finally, the server process is also responsible for returning the requested data to the user.
The most common configuration for the server process is where you assign each user a dedicated server process. However, Oracle provides for a more sophisticated means of servicing several users through the same server process called the shared server architecture, which enables you to service a large number of users efficiently. A shared server process enables a large number of simultaneous connections using fewer server processes, thereby conserving critical system resources such as memory.
The Background Processes
The background processes are the real workhorses of the Oracle instance. These processes enable large numbers of users to use the information that is stored in the database files. By being continuously hooked into the operating system, these processes relieve Oracle software from having to constantly start numerous, separate processes for each task that needs to be done on the operating system server.
BACKGROUND PROCESS
PROCESS FUNCTION
Database writer
Writes modified data from buffer cache to disk (data files)
Log writer
Writes redo log buffer contents to online redo log files
Checkpoint
Signals database to flush its contents to disk
Process monitor
Cleans up after finished and failed processes
System monitor
Performs crash recovery and coalesces extents
Archiver
Archives filled online redo log files
Recoverer
Used only in distributed databases
Dispatcher
Used only in shared server configurations
Coordination job
Coordinates job queues to expedite job processes queue process
I discuss in detail the main Oracle background processes in the following sections.
The Database Writer
The database writer (DBW) process is responsible for writing data from the memory areas known as database buffers to the actual data files on disk. It is the database writer process's job to monitor the usage of the database buffer cache and help manage it. For example, if the free space in the database buffer area is getting low, the database writer process has to make room there by writing some of the data in the buffers to the disk files. The database writer process uses an algorithm called least recently used (LRU), which basically writes data to the data files on disk based on how long it has been since someone asked for it. If the data has been sitting in the buffers for a very long time, chances are it is "dated" and the database writer process clears that portion of the database buffers by writing the data to disk.
The Process Monitor
The process monitor (PMON) process cleans up after failed user processes and the server processes. Essentially, when processes die, the PMON process ensures that the database frees up the resources that the dead processes were using. For example, when a user process dies while holding certain table locks, the PMON process would release those locks, so other users could use them without any interference from the dead process. In addition, the PMON process restarts failed server processes. The PMON process sleeps most of the time, waking up to see if it is needed. Other processes will also wake up the PMON process if necessary.
In Oracle9i, the PMON process automatically performs dynamic service registration. When you create a new database instance, the PMON process registers the instance information with the listener. (The listener is the entity that manages requests for database connections. This dynamic service registration eliminates the need to register the new service information in the listener.ora file, which is the configuration file for the listener.
The System Monitor
The system monitor (SMON) process, as its name indicates, performs system monitoring tasks for the Oracle instance. The SMON process performs crash recovery upon the restarting of an instance that crashed. The SMON process determines if the database is consistent following a restart after an unexpected shutdown. This process is also responsible for coalescing free extents if you happen to use dictionary managed tablespaces. Coalescing free extents will enable you to assign larger contiguous free areas on disk to your database objects. In addition, the SMON process cleans up unnecessary temporary segments. Like the PMON process, the SMON process sleeps most of the time, waking up to see if it is needed. Other processes will also wake up the SMON process if they detect a need for it.
The Archiver
The archiver (ARCH) process is used when the system is being operated in an archive log mode—that is, the changes logged to the redo log files are being saved and not being overwritten by new changes. The archiver process will archive the redo log files to a specified location, assuming you chose the automatic archiving option in the init.ora file. If a huge number of changes are being made to your database, and consequently your logs are filling up at a very rapid pace, you can use multiple archiver processes up to a maximum of ten. The parameter log_archive_maximum_processes in the initialization file will determine how many archiver processes Oracle will invoke. If the log writer process is writing logs faster than the default single archiver process can archive them, it will be necessary to enable more than one archiver process.
Note that besides the processes discussed here, there are other Oracle background processes that perform specialized tasks that may be running in your system. For example, if you use Oracle Real Application Clusters (ORAC), you'll see a background process called the lock (LCKn) process, which is responsible for performing interinstance locking. If you use the Oracle Advanced Replication Option, you'll notice the background process called recoverer (RECO), which recovers terminated transactions in a distributed database environment.
Friday, August 28, 2009
Tuesday, August 18, 2009
Oracle 10g New Features
SYSAUX Tablespace
The SYSAUX tablespace is an auxiliary tablespace that provides storage for all non sys-related tables and indexes that would have been placed in the SYSTEM tablespace. SYSAUX is required for all Oracle Database 10g installations and should be created along with new installs or upgrades. Many database components use SYSAUX as their default tablespace to store data. The SYSAUX tablespace also reduces the number of tablespaces created by default in the seed database and user-defined database. For a RAC implementation with raw devices, a raw device is created for every tablespace, which complicates raw-device management. By consolidating these tablespaces into the SYSAUX tablespace, you can reduce the number of raw devices. The SYSAUX and SYSTEM tablespaces share the same security attributes.
When the CREATE DATABASE command is executed, the SYSAUX tablespace and its occupants are created. When a database is upgraded to 10g, the CREATE TABLESPACE command is explicitly used. CREATE TABLESPACE SYSAUX is called only in a database-migration mode.
Rename Tablespace Option
Oracle Database 10g has implemented the provision to rename tablespaces. To accomplish this in older versions, you had to create a new tablespace, copy the contents from the old tablespace to the new tablespace, and drop the old tablespace. The Rename Tablespace feature enables simplified processing of tablespace migrations within a database and ease of transporting a tablespace between two databases. The rename tablespace feature applies only to database versions 10.0.1 and higher. An offline tablespace, a tablespace with offline data files, and tablespaces owned by SYSTEM or SYSAUX cannot be renamed using this option. As an example, the syntax to rename a tablespace from REPORTS to REPORTS_HISTORY is as follows.
SQL> ALTER TABLESPACE REPORTS RENAME TO REPORTS_HISTORY;
Automatic Storage Management
Automatic Storage Management (ASM) is a new database feature for efficient management of storage with round-the-clock availability. It helps prevent the DBA from managing thousands of database files across multiple database instances by using disk groups. These disk groups are comprised of disks and resident files on the disks. ASM does not eliminate any existing database functionalities with file systems or raw devices, or Oracle Managed Files (OMFs). ASM also supports RAC configurations.
In Oracle Database 10g Release 2, the ASM command-line interface (ASMCMD) has been improved to access files and directories within ASM disk groups. Other enhancements include uploading and extracting of files into an ASM-managed storage pool, a WAIT option on disk rebalance operations, and the facility to perform batch operations on multiple disks.
Now it's time to take a deeper look into the new storage structures in Oracle Database 10gtemporary tablespace groups and BigFile tablespaces. These will help you to better utilize the database space and to manage space more easily.
Temporary Tablespace Group
A temporary tablespace is used by the database for storing temporary data, which is not accounted for in recovery operations. A temporary tablespace group (TTG) is a group of temporary tablespaces. A TTG contains at least one temporary tablespace, with a different name from the tablespace. Multiple temporary tablespaces can be specified at the database level and used in different sessions at the same time. Similarly, if TTG is used, a database user with multiple sessions can have different temporary tablespaces for the sessions. More than one default temporary tablespace can be assigned to a database. A single database operation can use multiple temporary tablespaces in sorting operations, thereby speeding up the process. This prevents large tablespace operations from running out of space.
In Oracle Database 10g, each database user will have a permanent tablespace for storing permanent data and a temporary tablespace for storing temporary data. In previous versions of Oracle, if a user was created without specifying a default tablespace, SYSTEM tablespace would have become the default tablespace. For Oracle Database 10g, a default permanent tablespace can be defined to be used for all new users without a specific permanent tablespace. By creating a default permanent tablespace, nonsystem user objects can be prevented from being created in the SYSTEM tablespace. Consider the many benefits of adding users in a large database environment by a simple command and not having to worry about them placing their objects in the wrong tablespaces!
BigFile Tablespace
We have gotten past the days of tablespaces in the range of a few megabytes. These days, database tables hold a lot of data and are always hungry for storage. To address this craving, Oracle has come up with the Bigfile tablespace concept. A BigFile tablespace (BFT) is a tablespace containing a single, very large data file. With the new addressing scheme in 10g, four billion blocks are permitted in a single data file and file sizes can be from 8TB to 128TB, depending on the block size. To differentiate a regular tablespace from a BFT, a regular tablespace is called a small file tablespace. Oracle Database 10g can be a mixture of small file and BigFile tablespaces.
BFTs are supported only for locally managed tablespaces with ASM segments and locally managed undo and temporary tablespaces. When BFTs are used with Oracle Managed Files, data files become completely transparent to the DBA and no reference is needed for them. BFT makes a tablespace logically equivalent to data files (allowing tablespace operations) of earlier releases. BFTs should be used only with logical volume manager or ASM supporting dynamically extensible logical volumes and with systems that support striping to prevent negative consequences on RMAN backup parallelization and parallel query execution. BFT should not be used when there is limited free disk space available.
Prior to Oracle Database 10g, K and M were used to specify data file sizes. Because the newer version introduces larger file sizes up to 128TB using BFTs, the sizes can be specified using G and T for gigabytes and terabytes, respectively. Almost all the data warehouse implementations with older versions of Oracle database utilize data files sized from 16GB to 32GB. With the advent of the BigFile tablespaces, however, DBAs can build larger data warehouses without getting intimidated by the sheer number of smaller data files
Oracle block size Maximum file size
2KB 8TB
4KB 16TB
8KB 32TB
16KB 64TB
32KB 128TB
For using BFT, the underlying operating system should support Large Files. In other words the file system should have Large File Support (LFS).
Cross-Platform Transportable Tablespaces
In Oracle 8i database, the transportable tablespace feature enabled a tablespace to be moved across different Oracle databases using the same operating system. Oracle Database 10g has significantly improved this functionality to permit the movement of data across different platforms. This will help transportable tablespaces to move data from one environment to another on selected heterogeneous platforms (operating systems). Using cross-platform transportable tablespaces, a database can be migrated from one platform to another by rebuilding the database catalog and transporting the user tablespaces. By default, the converted files are placed in the flash recovery area (also new to Oracle Database 10g), which is discussed later in this chapter. A list of fully supported platforms can be found in v$transportable_platform.
A new data dictionary view, v$transportable_platform, lists all supported platforms, along with the platform ID and endian format information. The v$database dictionary view has two new columns (PLATFORM_ID, PLATFORM_NAME) to support it.
The table v$TRansportable_platform has three fields: PLATFORM_ID (number), PLATFORM_NAME (varchar2(101)), and ENDIAN_FORMAT (varchar2(14)). Endianness is the pattern (big endian or little endian) for byte ordering of data files in native types. In big-endian format, the most significant byte comes first; in little-endian format, the least significant byte comes first.
Performance Management Using AWR
Automatic Workload Repository (AWR) is the most important feature among the new Oracle Database 10g manageability infrastructure components. AWR provides the background services to collect, maintain, and utilize the statistics for problem detection and self-tuning. The AWR collects system-performance data at frequent intervals and stores them as historical system workload information for analysis. These metrics are stored in the memory for performance reasons. These statistics are regularly written from memory to disk by a new background process called Memory Monitor (MMON). This data will later be used for analysis of performance problems that occurred in a certain time period and to do trend analysis. Oracle does all this without any DBA intervention. Automatic Database Diagnostic Monitor (ADDM), which is discussed in the next section, analyzes the information collected by the AWR for database-performance problems.
By default, the data-collection interval is 60 minutes, and this data is stored for seven days, after which it is purged. This interval and data-retention period can be altered.
This captured data can be used for system-level and user-level analysis. This data is optimized to minimize any database overhead. In a nutshell, AWR is the basis for all self-management functionalities of the database. It helps the database with the historical perspective on its usage, enabling it to make accurate decisions quickly.
The AWR infrastructure has two major components:
In-memory statistics collection. The in-memory statistics collection facility is used by 10g components to collect statistics and store them in memory. These statistics can be read using v$performance views. The memory version of the statistics is written to disk regularly by a new background process called Memory Monitor or Memory Manageability Monitor (MMON). We will review MMON in background processes as well.
AWR snapshots. The AWR snapshots form the persistent portion of the Oracle Database 10g manageability infrastructure. They can be viewed through data dictionary views. These statistics retained in persistent storage will be safe even in database instance crashes and provide historical data for baseline comparisons.
AWR collects many statistics, such as time model statistics (time spent by the various activities), object statistics (access and usage statistics of database segments), some session and system statistics in v$sesstat and v$sysstat, some optimizer statistics for self-learning and tuning, and Active Session History (ASH). We will review this in greater detail in later chapters.
Automatic Database Diagnostic Monitor (ADDM)
Automatic Database Diagnostic Monitor (ADDM) is the best resource for database tuning. Introduced in 10g, ADDM provides proactive and reactive monitoring instead of the tedious tuning process found in earlier Oracle versions. Proactive monitoring is done by ADDM and Server Generated Alerts (SGAs). Reactive monitoring is done by the DBA, who does manual tuning through Oracle Enterprise Manager or SQL scripts.
Statistical information captured from SGAs is stored inside the workload repository in the form of snapshots every 60 minutes. These detailed snapshots (similar to STATSPACK snapshots) are then written to disk. The ADDM initiates the MMON process to automatically run on every database instance and proactively find problems.
Whenever a snapshot is taken, the ADDM triggers an analysis of the period corresponding to the last two snapshots. Thus it proactively monitors the instances and detects problems before they become severe. The analysis results stored in the workload repository are accessible through the Oracle Enterprise Manager. In Oracle Database 10g, the new wait and time model statistics help the ADDM to identify the top performance issues and concentrate its analysis on those problems. In Oracle Database 10g Release 2, ADDM spans more server components like Streams, RMAN, and RAC.
DROP DATABASE Command
Oracle Database 10g has introduced a means to drop the entire database with a single command: DROP DATABASE. The DROP DATABASE command deletes all database files, online log files, control files, and the server parameter (spfile) file. The archive logs and backups, however, have to be deleted manually.
The SYSAUX tablespace is an auxiliary tablespace that provides storage for all non sys-related tables and indexes that would have been placed in the SYSTEM tablespace. SYSAUX is required for all Oracle Database 10g installations and should be created along with new installs or upgrades. Many database components use SYSAUX as their default tablespace to store data. The SYSAUX tablespace also reduces the number of tablespaces created by default in the seed database and user-defined database. For a RAC implementation with raw devices, a raw device is created for every tablespace, which complicates raw-device management. By consolidating these tablespaces into the SYSAUX tablespace, you can reduce the number of raw devices. The SYSAUX and SYSTEM tablespaces share the same security attributes.
When the CREATE DATABASE command is executed, the SYSAUX tablespace and its occupants are created. When a database is upgraded to 10g, the CREATE TABLESPACE command is explicitly used. CREATE TABLESPACE SYSAUX is called only in a database-migration mode.
Rename Tablespace Option
Oracle Database 10g has implemented the provision to rename tablespaces. To accomplish this in older versions, you had to create a new tablespace, copy the contents from the old tablespace to the new tablespace, and drop the old tablespace. The Rename Tablespace feature enables simplified processing of tablespace migrations within a database and ease of transporting a tablespace between two databases. The rename tablespace feature applies only to database versions 10.0.1 and higher. An offline tablespace, a tablespace with offline data files, and tablespaces owned by SYSTEM or SYSAUX cannot be renamed using this option. As an example, the syntax to rename a tablespace from REPORTS to REPORTS_HISTORY is as follows.
SQL> ALTER TABLESPACE REPORTS RENAME TO REPORTS_HISTORY;
Automatic Storage Management
Automatic Storage Management (ASM) is a new database feature for efficient management of storage with round-the-clock availability. It helps prevent the DBA from managing thousands of database files across multiple database instances by using disk groups. These disk groups are comprised of disks and resident files on the disks. ASM does not eliminate any existing database functionalities with file systems or raw devices, or Oracle Managed Files (OMFs). ASM also supports RAC configurations.
In Oracle Database 10g Release 2, the ASM command-line interface (ASMCMD) has been improved to access files and directories within ASM disk groups. Other enhancements include uploading and extracting of files into an ASM-managed storage pool, a WAIT option on disk rebalance operations, and the facility to perform batch operations on multiple disks.
Now it's time to take a deeper look into the new storage structures in Oracle Database 10gtemporary tablespace groups and BigFile tablespaces. These will help you to better utilize the database space and to manage space more easily.
Temporary Tablespace Group
A temporary tablespace is used by the database for storing temporary data, which is not accounted for in recovery operations. A temporary tablespace group (TTG) is a group of temporary tablespaces. A TTG contains at least one temporary tablespace, with a different name from the tablespace. Multiple temporary tablespaces can be specified at the database level and used in different sessions at the same time. Similarly, if TTG is used, a database user with multiple sessions can have different temporary tablespaces for the sessions. More than one default temporary tablespace can be assigned to a database. A single database operation can use multiple temporary tablespaces in sorting operations, thereby speeding up the process. This prevents large tablespace operations from running out of space.
In Oracle Database 10g, each database user will have a permanent tablespace for storing permanent data and a temporary tablespace for storing temporary data. In previous versions of Oracle, if a user was created without specifying a default tablespace, SYSTEM tablespace would have become the default tablespace. For Oracle Database 10g, a default permanent tablespace can be defined to be used for all new users without a specific permanent tablespace. By creating a default permanent tablespace, nonsystem user objects can be prevented from being created in the SYSTEM tablespace. Consider the many benefits of adding users in a large database environment by a simple command and not having to worry about them placing their objects in the wrong tablespaces!
BigFile Tablespace
We have gotten past the days of tablespaces in the range of a few megabytes. These days, database tables hold a lot of data and are always hungry for storage. To address this craving, Oracle has come up with the Bigfile tablespace concept. A BigFile tablespace (BFT) is a tablespace containing a single, very large data file. With the new addressing scheme in 10g, four billion blocks are permitted in a single data file and file sizes can be from 8TB to 128TB, depending on the block size. To differentiate a regular tablespace from a BFT, a regular tablespace is called a small file tablespace. Oracle Database 10g can be a mixture of small file and BigFile tablespaces.
BFTs are supported only for locally managed tablespaces with ASM segments and locally managed undo and temporary tablespaces. When BFTs are used with Oracle Managed Files, data files become completely transparent to the DBA and no reference is needed for them. BFT makes a tablespace logically equivalent to data files (allowing tablespace operations) of earlier releases. BFTs should be used only with logical volume manager or ASM supporting dynamically extensible logical volumes and with systems that support striping to prevent negative consequences on RMAN backup parallelization and parallel query execution. BFT should not be used when there is limited free disk space available.
Prior to Oracle Database 10g, K and M were used to specify data file sizes. Because the newer version introduces larger file sizes up to 128TB using BFTs, the sizes can be specified using G and T for gigabytes and terabytes, respectively. Almost all the data warehouse implementations with older versions of Oracle database utilize data files sized from 16GB to 32GB. With the advent of the BigFile tablespaces, however, DBAs can build larger data warehouses without getting intimidated by the sheer number of smaller data files
Oracle block size Maximum file size
2KB 8TB
4KB 16TB
8KB 32TB
16KB 64TB
32KB 128TB
For using BFT, the underlying operating system should support Large Files. In other words the file system should have Large File Support (LFS).
Cross-Platform Transportable Tablespaces
In Oracle 8i database, the transportable tablespace feature enabled a tablespace to be moved across different Oracle databases using the same operating system. Oracle Database 10g has significantly improved this functionality to permit the movement of data across different platforms. This will help transportable tablespaces to move data from one environment to another on selected heterogeneous platforms (operating systems). Using cross-platform transportable tablespaces, a database can be migrated from one platform to another by rebuilding the database catalog and transporting the user tablespaces. By default, the converted files are placed in the flash recovery area (also new to Oracle Database 10g), which is discussed later in this chapter. A list of fully supported platforms can be found in v$transportable_platform.
A new data dictionary view, v$transportable_platform, lists all supported platforms, along with the platform ID and endian format information. The v$database dictionary view has two new columns (PLATFORM_ID, PLATFORM_NAME) to support it.
The table v$TRansportable_platform has three fields: PLATFORM_ID (number), PLATFORM_NAME (varchar2(101)), and ENDIAN_FORMAT (varchar2(14)). Endianness is the pattern (big endian or little endian) for byte ordering of data files in native types. In big-endian format, the most significant byte comes first; in little-endian format, the least significant byte comes first.
Performance Management Using AWR
Automatic Workload Repository (AWR) is the most important feature among the new Oracle Database 10g manageability infrastructure components. AWR provides the background services to collect, maintain, and utilize the statistics for problem detection and self-tuning. The AWR collects system-performance data at frequent intervals and stores them as historical system workload information for analysis. These metrics are stored in the memory for performance reasons. These statistics are regularly written from memory to disk by a new background process called Memory Monitor (MMON). This data will later be used for analysis of performance problems that occurred in a certain time period and to do trend analysis. Oracle does all this without any DBA intervention. Automatic Database Diagnostic Monitor (ADDM), which is discussed in the next section, analyzes the information collected by the AWR for database-performance problems.
By default, the data-collection interval is 60 minutes, and this data is stored for seven days, after which it is purged. This interval and data-retention period can be altered.
This captured data can be used for system-level and user-level analysis. This data is optimized to minimize any database overhead. In a nutshell, AWR is the basis for all self-management functionalities of the database. It helps the database with the historical perspective on its usage, enabling it to make accurate decisions quickly.
The AWR infrastructure has two major components:
In-memory statistics collection. The in-memory statistics collection facility is used by 10g components to collect statistics and store them in memory. These statistics can be read using v$performance views. The memory version of the statistics is written to disk regularly by a new background process called Memory Monitor or Memory Manageability Monitor (MMON). We will review MMON in background processes as well.
AWR snapshots. The AWR snapshots form the persistent portion of the Oracle Database 10g manageability infrastructure. They can be viewed through data dictionary views. These statistics retained in persistent storage will be safe even in database instance crashes and provide historical data for baseline comparisons.
AWR collects many statistics, such as time model statistics (time spent by the various activities), object statistics (access and usage statistics of database segments), some session and system statistics in v$sesstat and v$sysstat, some optimizer statistics for self-learning and tuning, and Active Session History (ASH). We will review this in greater detail in later chapters.
Automatic Database Diagnostic Monitor (ADDM)
Automatic Database Diagnostic Monitor (ADDM) is the best resource for database tuning. Introduced in 10g, ADDM provides proactive and reactive monitoring instead of the tedious tuning process found in earlier Oracle versions. Proactive monitoring is done by ADDM and Server Generated Alerts (SGAs). Reactive monitoring is done by the DBA, who does manual tuning through Oracle Enterprise Manager or SQL scripts.
Statistical information captured from SGAs is stored inside the workload repository in the form of snapshots every 60 minutes. These detailed snapshots (similar to STATSPACK snapshots) are then written to disk. The ADDM initiates the MMON process to automatically run on every database instance and proactively find problems.
Whenever a snapshot is taken, the ADDM triggers an analysis of the period corresponding to the last two snapshots. Thus it proactively monitors the instances and detects problems before they become severe. The analysis results stored in the workload repository are accessible through the Oracle Enterprise Manager. In Oracle Database 10g, the new wait and time model statistics help the ADDM to identify the top performance issues and concentrate its analysis on those problems. In Oracle Database 10g Release 2, ADDM spans more server components like Streams, RMAN, and RAC.
DROP DATABASE Command
Oracle Database 10g has introduced a means to drop the entire database with a single command: DROP DATABASE. The DROP DATABASE command deletes all database files, online log files, control files, and the server parameter (spfile) file. The archive logs and backups, however, have to be deleted manually.
Subscribe to:
Posts (Atom)