If you execute the STARTUP command without any PFILE specification, Oracle will search the default location (e.g. $ORACLE_HOME/dbs) in the following order:
1. spfile{$ORACLE_SID}.ora
2. spfile.ora
3. init{$ORACLE_SID}.ora (Server Side)

For further details, please refer to our self paced training at http://st-curr.us.oracle.com/oracle9i/Manage_Server/lesson04.htm.

ORA -1555 errors are generated when the undo data required to create a read consistent snapshot is not available. This can happen due to two reasons.

1. The undo_retention parameter, which tells the database instance how long should it preserve a piece of undo data, is set to smaller than the required value.

  2. The undo tablespace is not large enough.

  You may therefore need to adjust the setting of the undo_retention parameter based on the duration of the longest running query. Executing the following query should give you an idea of what this value should be:

select max(maxquerylen) from v$undostat;

The view v$undostat displays the undo usage information for the last one week and hence, should be able to guide you reasonable well in setting the value of the undo_retention parameter. This must also be backed up making enough space available in the undo tablespace, since the database may be forced to dishonor the undo retention in order to avoid a transaction failure. The EM undo tablespace advisory can tell you how much space is needed to support a given retention setting.

To summarize therefore, you first need to set the parameter undo_retention appropriately (using v$undostat) and, then make sure that enough undo space is available (using EM undo space advisory). Many administrators hate using the datafile autoextend feature but this is one of the cases where it may help you avoid ORA-1555 errors if you don't wish to go through the trouble of sizing the undo tablespace upfront.

Yes.

Oracle Database Resource Manager can not run concurrently with Solaris Resource Manager. Results are unpredictable because neither "knows" about the other. But resource managers are justified based upon predictability!

• Each instance is assigned to a dedicated operating system resource manager group or managed entity.
• The dedicated entity running all the instance's processes must run at one priority (or resource consumption) level.
• Memory management and process management are not enabled.

NOTE: Management of individual Oracle processes at different priority levels is not supported. Severe consequences, including instance crashes, can result. You can get the same nasty results if SRM is permitted to manage memory on which an Oracle instance is pinned.

If a user is not assigned to a user group, he/she is a member of DEFAULT_CONSUMER_GROUP. One reason for this consumer group is administrative time savings. For example, 25% of all users might be explicitly assigned to OLTP, with another 5% specified as members of the DSS group. The remaining 70% would already be members of the DEFAULT_CONSUMER_GROUP. If the remaining 70% should be prioritized similarly, and therefore managed as a single group, no more work is required to assign users to groups. Because of DEFAULT_CONSUMER_GROUP, everyone belongs to a group (either explicitly-assigned, or DEFAULT_CONSUMER_GROUP). Like all consumer groups, specifying the DEFAULT_CONSUMER_GROUP in the active plan is optional.

OTHER_GROUPS is not a group in that users can not be assigned to it. Users map to user groups one-to-one, and every user belongs to a group (DEFAULT or explicitly assigned, see above). When a group is not included in the active plan, its members default to the resources assigned to OTHER_GROUPS. To ensure that this "safety net" is always in place, and therefore that the administrator explicitly assigns resources to "everyone else", OTHER_GROUPS must be part of every plan.

Making a few changes to a complex application is the practical way to implement Database Resource Manager. As the Oracle Applications team proved in their 11i implementation, the changes did not require a redesign, nor lots of new code.

Since Oracle Applications starts a new session anytime responsibilities change (e.g., when accessing a different module), not much added effort was required to include the single-command (Database Resource Manager) procedure call to switch the session's consumer group assignment at session start-up. Oracle Applications required changes in just one place to integrate with Database Resource Manager. Oracle developers in the core applications infrastructure group implemented the required changes in libraries called by the various application modules.

The Oracle Applications Release 11i support is very granular. DBA's can associate an individual program to a resource consumer group or a group of programs to a resource consumer group. They can also associate a class of online users to a resource consumer group. For instance, they can say all "Order Entry" application users belong to consumer group 1 and "Human Resources" application users belong to consumer group 2.

All wait events associated with Resource Manager are prefixed with "resmgr:". For a complete listing of Resource manager events, execute the following query:

SELECT name from v$event_name where name like 'resmgr:%';

NAME
----------------------------------------------------------------
resmgr:wait in actses run
resmgr:waiting in end wait
resmgr:waiting in check
resmgr:waiting in system stop
resmgr:waiting in enter
resmgr:waiting in shutdown
resmgr:waiting in check2
resmgr:waiting in enter2
resmgr:waiting in run (queued)
resmgr:waiting in end wait2

10 rows selected.

Any user who is not assigned a consumer group explicitly automatically becomes a member of "DEFAULT_CONSUMER_GROUP" which is automatically created at the time of database set up. Thereafter an administrator has the option including this group in a resource plan if they want to assign resources to these users explicitly. Otherwise all the consumer groups which are not explicitly assigned resources in a resource plan share the resources allocated to "OTHER_GROUPS" (this one is automatically created as well). For this reason every valid plan must have a resource plan directive to allocate resources to "OTHER_GROUPS".

Note: Users can not be assigned to "OTHER_GROUPS" directly since it is not a regular consumer group. Think of it as a place holder for all the explicitly defined consumer groups in the database which is not included in the current (active) resource plan.

Beginning with Oracle9i, Oracle does have the capability to support dynamic system reconfiguration. In Oracle9i, the Database Server periodically pings the OS and adjusts the CPU_COUNT parameter, if required. This allows new workload to take advantage of the newly added processors. Similarly, the new dynamic memory management features of Oracle9i (i.e. Dynamic SGA, Automatic SQL Execution Memory Management) make it possible to alter the amount of memory used by an active instance without requiring an instance reboot. Using these capabilities, an Oracle9i Database can adjust itself in response to any dynamic resource changes at the hardware or OS level.

Unlike CPU, where the changes are detected automatically, the memory areas must be resized manually using the appropriate ALTER SYSTEM command before or after a dynamic reconfiguration operation. That is, one must execute these commands to shrink the SGA and PGA before taking away memory from an Oracle instance. Conversely, the SGA and PGA must be grown in order to take advantage of newly added memory. Some operating system vendors, like Sun, provide wrapper scripts to automate this process.

Also, since not all configuration parameters, whose values are calculated based on CPU_COUNT at the time of start up, currently adjust themselves automatically to changed number of CPUs, any drastic dynamic change in the number of CPUs may lead to sub-optimal database performance. In other words, if the number of CPU on a machine is dynamically changed from 2 to, let us say 32, the database may not be able to take the full advantage of additional processing power due to sub-optimal configuration. However Oracle will be able to take full advantage of dynamically adding CPUs in situations where the number of CPUs is modestly increased -- e.g., from 8 to 10 or 12.

Everything stated above applies to a RAC database as well. However, one must be using Oracle9i Release 2 to be able to dynamic resize SGA in a RAC environment since the dynamic feature is not supported for RAC in Release 1.

It is now possible to administer the Database Resource Manager using Oracle Enterprise Manager. The GUI will make it extremely easy to manage even the most complex Resource Manager implementations.

Oracle9i also allows users to quickly create a simple resource plan, adequate for most environments, using a single command (DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN). This procedure significantly simplifies the creation of a resource plan as DBAs no longer need to execute different commands to create a pending area, create necessary consumer groups and specify resource allocation directives.

In order to make life simpler for DBAs, Oracle9i "Oracle Managed File" (OMF) feature simplifies database administration by eliminating the need for administrators to directly manage the files of an Oracle database. This feature allows for specifying operations in terms of database objects. Oracle internally use the standard operating system (OS) file system interfaces to create and delete files as needed for tablespaces, online logs and controlfiles. DBAs merely need to specify the location of these files using new initialization parameters. Oracle then ensure creation of a file with a unique name and delete it when the corresponding object is dropped.

OMF allows you to specify one default location for datafiles and up to five default locations for confrolfile and online redo log files. It can significantly simply the management of database files for small test or development databases where performance is not the key concern. In order to optimize I/O performance, we recommend using a volume manager with SAME configuration. Refer to our OOW 2000 paper titled "Optimal Storage Configuration Made Easy"  for detailed information on the SAME architecture.

Also, since this feature is dependent on file system interfaces, it can not be used with raw devices.

Defragmentation or coalescing is an in-place data operation that compacts the data file and frees up unused disk space. No additional disk space is required during the compacting process. Oracle9i can defragment indexes in place.

Data re-organizations such as rebuilding indexes and moving a table or index requires additional temporary disk space during the re-organization process. These operations essentially make a new copy of the object. After the reorganization is complete, the original object is deleted and its disk space freed up. Oracle9i can re-organizing indexes, partitioned indexes, IOTs and partitioned IOTs.

Both operations make tables and indexes more contiguous, free up disk space and improve database performance.

• Index reorganization (create, rebuild, coalesce)
• Index-organized table reorganization (move)

Oracle recommends using online table redefinition feature for:

• Heap table reorganization
• Changing heap or IOT table physical or storage attributes (i.e. partition)
• Adding, dropping, and renaming columns in a table (normal drop or rename column command should be used for simple cases)
• Transforming data in a table
• When administrators want to validate data before completing a table redefinition or have control over when to complete a table redefinition (i.e. application upgrade)