Cheng's Oracle

RMAN commands

2008-05-17T10:31:00.000-07:00

To enable control file autobackup
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

To change the default format for autobackup file name
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/u1/oradata/cf_ORCL_auto_%F';

where %F is mandatory and translates into c-IIIIIIIIII-YYYYMMDD-QQ being:

IIIIIIII the DBID
YYYYMMDD timestamp of the backup
QQ a sequence that starts with 00 and ends with FF

Use the SHOW command to list current settings
RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT;
RMAN> SHOW EXCLUDE;
RMAN> SHOW ALL;

Use the CLEAR command to reset any persistent settings to its default value
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;
RMAN> CONFIGURE MAXSETSIZE CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;

Configure the default device type for automatic channel allocation
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

or manually allocate a channel

RMAN> RUN {
2> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
3> BACKUP DATAFILE '/u0/oradata/user01.dbf';
4> }

Configure parallelism
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 3;

Specify the maximum backup piece size
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

Format the name of the generated backup files
RMAN> RUN {
2> ALLOCATE CHANNEL d1 DEVICE TYPE disk
3> FORMAT '/disk1/backups/%U';
4> BACKUP DATABASE PLUS ARCHIVELOG; }

Some backup options (more on Administration Workshop II 3-9)
RMAN> BACKUP device type disk tag '%TAG' database include current controlfile;
RMAN> BACKUP database plus archivelog;
RMAN> BACKUP datafile '/u0/oradata/user01.dbf';
RMAN> BACKUP AS BACKUPSET TABLESPACE hr_data; (AS BACKUPSET can be omitted if RMAN is configured in mode BACKUPSET)

After a online backup, remember to archive current redo logs using:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
or all archive logs:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
or a specific redo group:
SQL> ALTER SYSTEM ARCHIVE LOG GROUP n;

More on backups at http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96519/backup.htm

See backup constraints at Administration Workshop II 3-10
See parallel backup commands at Administration Workshop II 3-11

To create a clone database or a standby database from backups of the target database
RMAN> DUPLICATE database to auxiliaryDb;

To flashback a database

RMAN> FLASHBACK DATABASE
2> TO TIME = TO_DATE
3> ('06/25/03 12:00:00','MM/DD/YY HH:MI:SS');

after you have performed flashback database you should remember to reset logs:

SQL> ALTER DATABASE RESETLOGS;

To restore/recover
RMAN> RESTORE/RECOVER database;
RMAN> RESTORE/RECOVER tablespace
RMAN> RESTORE/RECOVER datafile

Reporting commands

LIST
REPORT

For catalog maintenance (Administration Workshop II 3-8)

CROSSCHECK
DELETE
CHANGE
LIST

RMAN command-line arguments

2008-05-17T10:19:00.000-07:00

(Administration Workshop II 2-27)
First set the ORACLE_SID env
$ export ORACLE_SID=orcl

Writing RMAN output to a log file
$ rman TARGET sys/oracle LOG=$HOME/oradata/u03/rman.log APPEND

Executing a command file when RMAN is invoked
$ rman TARGET sys/oracle CMDFILE=$HOME/scripts/my_rman_script.rcv

Establishes database connections on RMAN startup (both TARGET and CATALOG DBs)
$ rman TARGET sys/oracle@orcl CATALOG rman/rman@rcat

Note: NOCATALOG is the default mode (rman will use the control file as rman repository) unless a CATALOG database is specified.

Administration Workshop II. 10-3. Table types

2008-05-16T15:27:00.000-07:00

Ordinary (heap organized table): Data is stored as an unordered collection (heap).
Partitioned table: data is divided into smaller, more manageable pieces.
Index-organized table (IOT): Rows (key and non-key values) are sorted and stored in a B-tree index structure.
Clustered table: related data from more than one table are stored together. In other words, a cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

Administration Workshop II. 4-14. re-creating indexes

2008-05-14T18:01:00.000-07:00

Use options to reduce the time it takes to create the index:

PARALLEL
NOLOGGING

SQL> CREATE INDEX rname_idx ON hr.regions (region_name) PARALLEL 4;

WARNING: if you created the index with PARALLEL option 4, then you'll need to change it back to PARALLEL 1 after the index is created so that it doesn't use 4 CPUs every time it access the index later!

Administration Workshop II. 2-16. Monitoring the Flash Recovery Area evolution with EM

2008-05-13T18:14:00.000-07:00

Go to Enterprise Manager -> Related Links -> All metrics. Click on Recovery Area link then again on the percentage number to see the chart.

Administration Workshop II. 2-5. RMAN Performance Enhancements

2008-05-13T18:02:00.001-07:00

Automatic parallelization of backup, restore, and recovery operations
No generation of extra redo during online database backups
Backups that are restricted to limit the number of reads per file per second to avoid interfering with OLTP work
The use of multiplexing, which can prevent flooding of any one file with reads and writes while still keeping a tape drive streaming

Administration Workshop II. 2-5. Use RMAN to detect block corruption

2008-05-13T17:58:00.000-07:00

You can use RMAN to detect block corruption. The information relating to the block corruption that is detected during backup can be obtained by using the V$BACKUP_CORRUPTION and V$COPY_CORRUPTION dynamic views.

Administration Workshop II. 2-14. Using a Flash Recovery Area with RMAN

2008-05-13T17:39:00.000-07:00

Flash Recovery Area simplifies managing disk space and files related to backup and recovery:

When the flash recovery area is used, RMAN automatically uses Oracle Managed Files (OMF) for its backup files.
Each time RMAN creates a file in the flash recovery area, the Oracle database updates the list of files that are no longer required on disk. When a file needs to be written into the flash recovery area and space is not available for that file, the Oracle database deletes a file that is on the obsolete files list and writes a notification to the alert log.
A warning is issued when the flash recovery area experiences space pressure or is low on free space because there are no files that can be deleted from the flash recovery area. To resolve the problem, you can:

- add disk space
- back up your files to a tertiary device
- delete the files from the flash recovery area using RMAN
- or change the RMAN retention policy.

The flash recovery area is configured by setting the DB_RECOVERY_FILE_DEST initialization parameter. The DB_RECOVERY_FILE_DEST_SIZE parameter specifies its size.

18-12. How to avoid generating REDO entries

2008-05-11T08:57:00.000-07:00

Even though a database is in archive log mode, it's possible to individually disable logging for a particular table. To do so:

Set the NOLOGGING attribute for the table or

Specifying the UNRECOVERABLE Clause in the Control File. Example:

UNRECOVERABLE
LOAD DATA
INFILE 'sample.dat'
INTO TABLE emp
(ename VARCHAR2(10), empno NUMBER(4));

Timestamp manipulation

2008-05-11T08:30:00.000-07:00

Get the timestamp 10 minutes earlier from now:

SELECT SYSTIMESTAMP - INTERVAL '10' minute FROM DUAL;

Get the timestamp 1 year later from the specified timestamp:

SELECT timestamp'2004-02-29 00:00:00' + INTERVAL '1' year(1) FROM dual;

Extract fields from a timestamp:

SELECT
EXTRACT(year FROM current_timestamp) EY,
EXTRACT(month FROM current_timestamp) EM,
EXTRACT(day FROM current_timestamp) ED,
EXTRACT(hour FROM current_timestamp) EH,
EXTRACT(minute FROM current_timestamp) EM,
EXTRACT(second FROM current_timestamp) ES,
EXTRACT(timezone_hour FROM current_timestamp) TH,
EXTRACT(timezone_minute FROM current_timestamp) TM,
EXTRACT(timezone_region FROM current_timestamp) TR,
EXTRACT(timezone_abbr FROM current_timestamp ) TA
FROM dual;

More on: Oracle Timestamp Data Type

17-25. Flashback Versions Query Considerations

2008-05-10T13:07:00.000-07:00

The VERSIONS clause cannot be used to query

External tables
Temporary tables
Fixed tables
Views

The VERSIONS clause cannot span DDL commands
The VERSIONS clause in a SELECT statement cannot produce versions of rows across the DDL statements that change the structure of the corresponding tables. This means that the query stops producing rows after it reaches a time in the past when the table structure was changed.
What if I want to flashback version query after a DDL? is it possible?

Segment shrink operations are filtered out
Certain maintenance operations, such as a segment shrink, may move table rows across blocks. In this case, the version query filters out such phantom versions because the row data remains the same.

17-9. Flashback Database Limitations

2008-05-10T13:00:00.000-07:00

You cannot use Flashback Database if the control file has been restored or re-created.
Data file dropped after Flashback target time cannot be recovered (the dropped data file is added to the control file and marked offline, but it is not flashed back)
Data file that has been shrunk since the Flashback target time cannot be flashed back. Any such data files must be taken offline before performing the flashback operation.

17-7. Flashback Database: Reducing Restore Time

2008-05-05T20:59:00.001-07:00

With Flashback Database, the time to recover a database is now proportional to the number of changes that need to be backed out (and not to the size of the database) because you do not have to restore data files.

The Oracle database periodically logs "before images" of data blocks in the Flashback Database logs. Bock images can be reused to quickly back out the data file changes to any time at which flashback logs are captured just before the desired target time. Then, changes from the redo log files are applied to fill in the gap (????? why applying the redo log?).

16-8. Loss of a Redo Log File

2008-05-05T19:20:00.000-07:00

Database Control does not allow you to clear a log group that has not been archived. Doing so breaks the chain of redo information. If you must clear an unarchived log group, you should immediately take a full backup of the whole database. Failure to do so may result in a loss of data if another failure ocurrs.

15-19. Flash Recovery Area

2008-05-04T17:42:00.001-07:00

The Flash Recovery Area contains:

Archived logs *
Backups
Flashback logs

Oracle recommends that the size of the flash recovery area be at least twice the size of the database so that it can hold one backup and several archived logs.

*If you have configured your archived logs to be written to this location (with the USE_DB_RECOVERY_AREA flag in one of the locations), then it is important to monitor this space to ensure that it des not reach its capacity.

15-16. Backup control file to a trace file

2008-05-04T13:47:00.000-07:00

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

It would be backed up at the location specified at USER_DUMP_DEST initialization parameter with a name sid_ora_pid.trc

15-7. Backup sets vs. image copies

2008-05-03T13:11:00.000-07:00

Data blocks
Image copies: all data blocks
Backup sets: no empty data blocks are copied (saves space). Typically 20% of database space is empty data block.

Granularity
Image copies: only the file we need has to be copied
Backup sets: all backup set must be retrieved even though only one of them is needed

A backup of a database running in NOARCHIVELOG mode must have all three attributes:

offline
full backup (level 0)
whole database

9-6 to 9-11

2008-04-21T20:55:00.000-07:00

13-15. SQL Access Advisor

2008-04-16T14:35:00.000-07:00

This advisor requires that you identify a SQL workload, which is a representative set of SQL statenments that access the schema. You can select you workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.
The SQL Access Advisor may make recommendations such as creating indexes or materialized views to improve your query performance for the given workload.

13-9. SQL Tuning Advisor

2008-04-16T12:57:00.000-07:00

Oracle's Tuning Advisor calls Automatic Tuning Optimizer (ATO) to perform 4 types of analysis:

Statistics Analysis: The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and makes recommendations to gather relevant statistics.
SQL Profiling (Tune SQL plan): The ATO verifies its own estimates and collects auxiliary information to remove estimation errors. It builds a SQL profile using the auxiliary information and makes a recommendation to create it. When a SQL profile is created, it enables the query optimizer to generate a well-tuned plan.
Access path Analysis: the ATO explores whether a new index can be used to significantly improve access to each table in the query and, when appropriate, makes recommendations to create such indexes.
SQL Structure Analysis: The ATO tries to identify SQL statements that use bad plans and makes relevant suggestions to restructure them. The suggested changes can be syntactic as well as semantic.

It's important to choose the appropriate scope for the tuning task. If you choose the Limited option, then recommendations are based on:

Statistics check
Access path analysis
SQL structure analysis

If you choose comprehensive option, then SQL profile recommendations are generated as well.

12-22. Server-generated alerts

2008-04-13T17:09:00.001-07:00

A few key metrics that can provide early problem notifications are:

Average File Read Time (centiseconds)
Dump Area Used (%)
Response Time (per transaction)
SQL Response Time (%)
Tablespace Used (%)
Wait Time (%)

By default, the following server-generated alerts are enabled:

Tablespace Space Usage (warnings 85%, critical 97%)
Snapshot Too Old
Recovery Area Low on Free Space
Resumable Session Suspended

To set or edit a threshold for your whole database, select Manage Metrics in the Related Links region of the database home page.

There are two kinds of server-generated alerts:

Threshold (stateful) alerts: they are configured by setting a warning and critical threshold values on database metrics. For example: Physical Reads per second, User commits per second, SQL Service Response Time, etc.
Except for the Tablespace Space Usage metric, which is database related, the other metrics are instance related.
Stateful alerts appear in DBA_OUTSTANDING_ALERTS and when cleared, go to DBA_ALERT_HISTORY.
Nonthreshold (stateless) alerts: corresponds to specific database events such as Snapshot Too Old errors, Recovery Area Low On Free Space, and Resumable Session Suspended.
Stateless alerts go directly to the history table. Clearing a stateless alert makes sense only in the Database Control environment because Database Control stores stateless alerts in its own repository.

12-21. The DBMS_ADVISOR Package

2008-04-13T16:54:00.000-07:00

The DBMS_ADVISOR package contains all constants and procedure declarations for all advisor modules. You can use this package to execute tasks via command line.
To execute advisor procedures, you must be granted the ADVISOR privilege. The ADVISOR privilege permits full access to the advisor procedures and views.

Procedures:

CREATE_TASK
DELTE_TASK
EXECUTE_TASK
INTERRUPT_TASK
GET_TASK_REPORT
RESUME_TASK
UPDATE_TASK_ATTRIBUTES
SET-TASK_PARAMETER
MARK_RECOMMENDATION
GET_TASK_SCRIPT

12-8. Using the Optimizer Statistics

2008-04-13T13:30:00.000-07:00

Go to the Enterprise Manager page for managing optimizer statistics by clicking Manage Optimizer Statistics on the Administration tabbed page. Note that GATHER_STATS_JOB should be enabled and for this to work, you should set STATISTICS_LEVEL initialization parameter is set to at least TYPICAL.

From Manage Optimizer Statistcs page, you can perform the following tasks on statistics:

Gather optimizer statistics manually. this action submits the job that GATHER_STATS_JOB automatically does. This should be done in the case where a table's contents have changed so much between automatic gathering jobs that the statistics no longer represent the table accurately. Examples of this are a table that is truncated in the middle of the day and a batch job that runs and adds large amounts of data to a table.
Restore optimizer statistics to a point in the past. The point in time chosen must be within the optimizer statistics retention period, which defaults to 30 days.
Lock optimizer statistics to guarantee that the statistics for certain objects are never overwritten. This is useful if statistics have been calculated for a certain table at a time when well-representative data is present, and you want to always have those statistics. No fluctuation in the table affects the statistics if they are locked.
Unlock optimizer statistics to undo the previously done lock
Delete optimizer statistics to delete statistics.

When to re-organize a table

2008-04-12T17:59:00.000-07:00

Notes from: All about chained rows @ Ask Tom

Before jumping into the topic, we need to first clarify some concepts:

A row that doesn't fit into a block A needs to be "chained" with another block B.
Now, if there was another row in the block A then it needs to be "migrated" to another block when block A fills up.
a Full table scan example is SELECT * FROM t
a query using an index is for example SELECT * FROM t WHERE t.col1 = 1 (it uses the index on col1 to find the row where t.col1 = 1)

Migrated rows and their impact on:

Full table scan: are not affected by migrated rows since it picks up data while reading the disks
Querying migrated rows with index (instead of full table scan): go through the ROWID pointer (that was left behind when a row was migrated) to find the data it needs so it adds 1 to Table Fetch Continue Row count.

Chained rows and their impact on:

Full table scan: since we can't find the data in one place only, we will need an additional read to find all the data
Querying chained rows with index (instead of full table scan): idem.

Conclusion
Chained rows are inevitable so there's not much we can do unless the data model could be changed so that fewer data is stored - but that's a functional problem. On the other hand, if we have lots of migrated rows that each of them would perfectly fit in one datablock, then we could potentially reorganize the table to reduce the additional disk lookup.

12-5. Optimizer Statistics

2008-04-12T16:51:00.000-07:00

Optimizer statistics include table, column, index and system statistics. They provide the optimizer a statistically correct snapshot of data storage and distribution, which the optimizer uses to make decisions on how to access data.

The statistics that are collected include:

Size of the table or index (in database blocks)
SELECT blocks FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
Number of rows
SELECT num_rows FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
Average row size and chain count (tables only)
SELECT avg_row_len, chain_cnt FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
Height and number of deleted leaf rows (indexes only)

Optimizer statistics are collected automatically by the preconfigured GATHER_STATS_JOB, which runs during predefined maintenance windows, once per day.

A large table that experiences 10 percent growth (or reduction) within a 24-hour period is usually considered too volatile for statistics collection once per day to be sufficient. For tables that experience this level of change, Oracle recommends collecting statistics more frequently so it won't exceed 10 percent growth between collection periods.

Statistics can be manually collected by using Enterprise Manager or through the use of the DBMS_STATS package as shown here:

SQL> EXEC dbms_stats.gather_table_stats('HR', 'EMPLOYEES');

9-9. Monitoring and Administering Undo

2008-04-02T15:52:00.000-07:00

In order to minimize the impact to the users, proactively monitor the alert log for the below errors and resize the undo tablespace accordingly:

ORA-01650 unable to extend rollback segment error message: happens when the undo tablespace runs out of room for undo data. Make sure users commit large transactions periodically.
ORA-01555 snapshot too old error message: occurs when the query needs to access undo information that has already been overwritten. This may happen in a long-running or flashback query. Configure an appropriate undo retention interval by setting UNDO_RETENTION in seconds and altering tablespace to have RETENTION GUARANTEE. This parameter UNDO_RETENTION is only used when:
- the undo tablespace has the AUTOEXTEND option enabled or
- you want to set undo retention for LOBS or
- you want to guarantee retention.

The recommendation is to set the AUTOEXTEND to true until we determined the space requirements for the undo tablespace. After that, we should disable it.

8-28 to 8-31. Lock Conflicts Causes, Detection and Resolution

2008-04-01T20:00:00.000-07:00

Causes

Uncommited changes
Long running transactions: Many applications use batch processing to perform bulk updates. Lock conflicts are common when transaction and batch processing are being performed simultaneously.
Unnecessarily high lock levels: Some database still lock at page or table level. As a consequence, developers writing applications to be run among multiple databases use artificially high lock levels which make Oracle behave like those less capable DBMS.

Detection
Use Blocking Sessions page in Enterprise Manager to locate lock conflicts.
Alternatively, the Automatic Diagnostic Monitor (ADDM) also automatically detects lock conflicts and can advise you on inefficient locking trends.

Resolution
The best way: inform the user and let them complete the transaction.
In emergency: kill the session in session detail page (Enterprise Manager) or manually using SQL:

SQL> select sid, serial#, username from v$session where sid in (select blocking_session from v$session);

The above selects sessions where its SID matches those SIDs that are blocking other sessions.

SQL> alter system kill session 'sid,serial_number' immediate;

8-23. Lock Modes

2008-04-01T19:55:00.000-07:00

Need more info on these locking modes (though we often don't use it since they are automatically obtained):

ROW SHARE
ROW EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE

Explicación extensa de Oracle: http://youngcow.net/doc/oracle10g/server.102/b14220/consist.htm

Intento de explicación (cualquier cosa): http://mioracle.blogspot.com/2008/03/bloqueo-base-de-datos.html

8-20. Triggering Events

2008-04-01T19:50:00.000-07:00

DML: INSERT, UPDATE, DELTE
DDL: CREATE, DROP, ALTER, GRANT, REVOKE, RENAME
Database: LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR

8-25. DML locks

2008-04-01T19:47:00.000-07:00

Transaction 1:
UPDATE employees
SET salary=salary*1.1
WHERE employee_id=107;

Transaction 2:
UPDATE employees
SET salary=salary*1.1
WHERE employee_id=106;

Each DML transaction must obtain two locks:

An EXCLUSIVE row lock for the row or rows being updated
A ROW EXCLUSIVE table-level lock on the table being updated. This is to prevent another session from locking the whole table (possibly to drop or truncate it) while the change is being made.

8-18. Built-in Packages

2008-04-01T08:43:00.000-07:00

DBMS_STATS: Gather, view, and modify optimizer statistics
DBMS_OUTPUT: Generate output from PL/SQL
DBMS_SESSION: PL/SQL access to the ALTER SESSION and SET ROLE statements
DBMS_RANDOM: Generate random numbers
DBMS_UTILITY: Get time, CPU time, and version information; compute a hash value, and perform many other miscellaneous functionalities
DBMS_SCHEDULER: Schedule functions and procedures that are callable from PL/SQL
DBMS_CRYPTO: Encrypt and decrypt database data
UTL_FILE: Read and write to operating system files from PL/SQL

Function-based index

2008-03-30T20:42:00.000-07:00

A function-based index indexes a funciton's return value.
It can be a:

Built-in SQL function
PL/SQL function
A user-written function

It relieves the server from having to invoke the function for every key value as it performs a search on the indxed expression.

For example:

CREATE INDEX table1_ix ON TABLE1(my_function(column1, column2));

Now any query that contains the expression my_function(column1, column2) in the WHERE clause may be able to take advantage of this index (otherwise, it would force a full table scan)

Reversed-key indexes

2008-03-30T20:28:00.000-07:00

From: Oracle reverse key index tips

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

Note: An Oracle reverse key index DOES NOT change the functionality of the index, and it's not the same as reversing the index key values.

In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.

With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed. For updates, Oracle updates the index nodes with each update statement.

Depending on the size of your update batch, it's often faster to drop, update and then re-build the index. If you have more than one CPU, you might consider doing a parallel index rebuild for faster speed. It splits-up the full-table scan.

ROWID

2008-03-30T19:55:00.000-07:00

More explanation at: Overview of ROWID and UROWID Datatypes

Oracle uses rowids internally for the construction of indexes. Each key in an index is associated with a rowid that points to the associated row's address for fast access. End users and application developers can also use rowids for several important functions:

Rowids are the fastest means of accessing particular rows.
Rowids can be used to see how a table is organized.
Rowids are unique identifiers for rows in a given table.

There are Restricted ROWIDs and Extended ROWIDs.

Structure of a Restricted ROWID

An example ROWID is

ROWID ENAME
------------------ ----------
00000DD5.0000.0001 KRISHNAN

As shown, a restricted rowid's VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:

The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Two rows with identical block numbers could reside in two different datafiles of the same tablespace.
The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.
The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are unique within a database.

Structure of an Extended ROWID

ROWID LAST_NAME
------------------ ----------
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.
FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).
BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.
RRR: The row in the block.

7-44. Data Dictionary

2008-03-30T17:46:00.000-07:00

The data dictionary can be used to query about users, objects, constraints and storage.

By querying the Oracle with SELECT * FROM dictionary WHERE table_name like 'USER_%', we get the list of views that the user owns.

For example:

To find out the name of the view used to retrieve the indexes that INVENTORY user owns, we query the SELECT * FROM dictionary WHERE table_name like 'USER_IND%'. There should be a row where the table_name is USER_INDEXES.

To list all constraints that the INVENTORY user can see, we should use the view ALL_CONSTRAINTS. We use ALL because we want to include other user's constraints and its own constraints.

To list the names of tables created in your schema: SELECT table_name, tablespace_name FROM user_tables;

To see significant information about any sequences in the database that you have access to: SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN ('MDSYS', 'XDB');

To see which users in this database are currently able to log in: SELECT username, account_status FROM dba_users WHERE account_status = 'OPEN';

To see what information you can view about all the indexes in the database: DESCRIBE dba_indexes;

7-29. B-Tree index leaf entry characteristics

2008-03-30T17:45:00.000-07:00

In a B-tree index on a nonpartitioned table:

Key value are repeated if there are multiple rows that have the same key value unless the index is compressed
There is no index entry corresponding to a row that has all key columns that are NULL. Therefore, a WHERE clase specifying NULL will always result in a full table scan.
Restricted ROWID is used to point to the rows of the table because all rows belong to the same segment.

7-24. Dropping a table

2008-03-30T17:02:00.000-07:00

Dropping a table removes:

Data
Table structure
Database triggers
Corresponding indexes
Associated object privileges

Optional clauses for the DROP TABLE statement:

CASCADE CONSTRAINTS: Dependent referential integrity constraints are removed as well.
PURGE: No flashback possible

Some considerations:

If you do not use the PURGE option, the table definition, associated indexes, and triggers are placed in a recycle bin. That means that the space taken up by the table and its indexes still counts against user's allowed quota for the tablespaces involved. Use PURGE RECYCLEBIN command to empty the recycle bin or FLASHBACK TABLE command to recover schema objects if PURGE clause was not used in DROP TABLE statement

7-19. Deferred and nondeferred constraints

2008-03-30T16:43:00.001-07:00

Constraints are checked at the time of:

Statement execution, for nondeferred constraints
COMMIT, for deferred constraints

So the order is (case: DML statement, followed by COMMIT):

Nondeferred constraints checked
COMMIT issued
Deferred constraints checked
COMMIT complete

Non-deferred constraints are enforced at the end of every DML statement and a violation causes the statement to roll back.

Deferred constraints are enforced when a transaction is committed and a violation causes the transaction to roll back.

A constraint that is defined as deferrable can be specified as one of the following:

Initially immediable: specifies that by default it must function as an immediate constraint unless explicitly set otherwise using SET CONSTRAINT {constraint name, ... ALL} {IMMEDIATE DEFERRED}
Initially deferred: specifies that by default the constraint must be enforced only at the end of the transaction.

7-17. Constraint States

2008-03-30T16:31:00.000-07:00

To better deal with situations where data must be temporarily in violation of a constraint, you can designate a constraint to be in various states.
An integrity constraint can be ENABLED or DISABLED. This applies to newly entered data.
An integrity constraint can VALIDATE or NOVALIDATE existing data.
To sumarize, an integrity constraint can be in one of the four states:

DISABLE NOVALIDATE (often used when the data is from an already validated source and the table is read-only, so no new data is being entered into the table).
DISABLE VALIDATE (used when the existing data must be validated but the data is not going to be modified and the index is not otherwise needed for performance).
ENABLE NOVALIDATE (used so that existing constraint violations can be corrected, and at the same time, new violations are not allowed to enter the system).
ENABLE VALIDATE (both new and existing data must conform to the constraint)

7-6. Naming database objects

2008-03-30T16:00:00.000-07:00

Non-quoted names are stored in uppercase and not case sensitive

6-28. Oracle Password Verification Function: VERIFY_FUNCTION

2008-03-27T20:37:00.000-07:00

The Oracle server provides a password complexity verification function named VERIFY_FUNCTION. This function is created with the /rdbms/admin/utlpwdmg.sql script. The password complexity verification function must be created in the SYS schema. It can be used as a template for you customized password verification.

The supplied password verification function enforces these password restrictions:

The minimum length is four characers.
The password cannot be the same as the username.
The password must have at least one alphabetic, one numeric, and one special character.
The password must differ from the prvious password by at least three letters.

In adittion to creating VERIFY_FUNCTION, the utlpwdmg script also changes the DEFAULT profile with the following ALTER PROFILE command:

ALTER PROFILE default LIMIT
PASSWORD_LIFE_TIME 60
PASSOWRD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFYH_FUNCTION verify_function;

6-23. Profiles and users

2008-03-27T20:08:00.000-07:00

Users are assigned only one profile at any given time.

Profiles:

Control resource consumption
Manage account status and password expiration

Control resource consumption

Profiles enable the administrator to control the following system resources:

CPU: may be limited on a per-session or per-call basis (in hundredths of a second)
Network/Memory: each DB session consumes system memory resources and network resources (if the session is from a user who is not local to the server). You can supply: Connect time, idle time, concurrent sessions or Private SGA.
Disk I/O: This limits the amount of data a user can read either at the per-session or per-call level. Reads/Session and Reads/Call place a limitation on the total number of reads from both memory and the disk.

Profiles also allow a composite limit. Composite limits are based on a weighted combination of CPU/Session, Reads/Session, Connect Time, and Private SGA. Composite limits are discussed in more detail in the Oracle Database Security Guide.

Manage account status and password expiration

Oracle password management is implemented with user profiles. You should set it by clicking on the Password tab in the desired Profile from Enterprise Manager. Profiles can provide many standard security features including the following:

Account locking:

The FAILED_LOGIN_ATTEMPTS parameter specifies the number of failed login attempts before the lockout of the account.
The PASSWORD_LOCK_TIME parameter specifies the number of days for which the account is locked after the specified number of failed login attempts.

Password aging and expiration:

The PASSWORD_LIFE_TIME parameter determines the lifetime of the password in days, after which the password expires.
The PASSWORD_GRACE_TIME parameter specifies a grace period in days for changing the password after the first successful login after the password has expired.

Password history (mutually exclusive parameters):

PASSWORD_REUSE_TIME: specifies that a user cannot reuse a password for a given number of days
PASSWORD_REUSE_MAX: specifies the number of password changes that are required before the current password can be reused.

Password complexity verification:

The PASSWORD_VERIFY_FUNCTION parameter names a PL/SQL function that performs a password complexity check before a password is assigned. Passowrd verification functions must be owned by SYS user and must return a Boolean value.

6-21. Secure Roles

2008-03-27T19:53:00.000-07:00

It is possible to:

Make a role nondefault: when the role is granted to a user, deselect the DEFAULT check box. The user must now explicitly enable the role before the role's privileges can be exercised. This is done by issuing SET ROLE ; command
Have a role require addtional authentication: they can be password, external or global
Create secure application roles that can be enabled only by executing a PL_SQL procedure successfully: The PL/SQL procedure can check things such as the user's network address, which program the user is running, time of day, or other elements needed to proerly secure a group of permissions. See http://www.dba-oracle.com/t_get_ip_address_utl_inaddr_sys_context.htm

6-19. Predefined Roles

2008-03-27T19:52:00.000-07:00

There are some predefined roles such as:

CONNECT
RESOURCE
SCHEDULER_ADMIN
DBA
SELECT_CATALOG_ROLE

Notes:

CONNECT is granted automatically to any user created with Enterprise Manager
In earlier versions of the database (before 10gR2), the CONNECT role included more privileges, such as CREATE TABLE and CREATE DATABASE LINK, which have been removed for security reasons.
Granting the RESOURCE ROLE includes granting the UNLIMITED TABLESPACE privilege

Functional Roles

Other roles that authorize you to administer special functions are created when that functionality is installed. For example:

XDBADMIN: contains the privileges required to administer XML database if that feature is installed.
AQ_ADMINISTRATOR_ROLE: privileges to administer advanced queuing
HS_ADMIN_ROLE: includes the privileges needed to administer heterogeneous services.

6-16. Revoking Object Privileges with GRANT OPTION

2008-03-27T19:51:00.001-07:00

Cascading effects can be observed when revoking a system privilege that is related to a DML operation. For example, if the SELECT ANY TABLE privilege is granted to a user, and that user has created procedures that use the table, all procedures that are contained in the user's schema must be recompiled before they can be used again.
Revoking object privileges also cascades when given WITH GRANT OPTION.

6-15. Revoking System Privileges

2008-03-27T19:47:00.000-07:00

System privileges, which have been granted directly with a GRANT command, can be revoked by using the REVOKE SQL statement. Users with ADMIN OPTION for a system privilege can revoke the privilege from any other database user.
There are no cascading effects when a system privilege is revoked, regardless of whether it is given the ADMIN OPTION.

6-12. System privileges

2008-03-27T12:04:00.000-07:00

RESTRICTED SESSION: allows to log in even if the database has been opened in restricted mode
SYSDBA and SYSOPER: These privileges allows you to shutdown, start up, and perform recovery and other administrative tasks in the database. SYSOPER allows a user to perform basic operational tasks, but without the ability to look at the user data. It includes the following system privileges:
- STARTUP and SHUTDOWN
- CREATE SPFILE
- ALTER DATABASE OPEN/MOUNT/BACKUP
- ALTER DATABASE ARCHIVELOG
- ALTER DATABASE RECOVER (complete recovery only, Any form of incomplete recovery, such as UNTIL TIME | CHANGE | CANCEL | CONTROLFILE requires connnecting as SYSDBA)
- RESTRICTED SESSION
The SYSDBA system privilege additionally authorizes incomplete recovery and deletion of a database. Effectively, the SYSDBA system privilege allows a user to connect as the SYS user.
DROP ANY object
CREATE, MANAGE, DROP, and ALTER TABLESPACE
CREATE ANY DIRECTORY (potential security hole)
GRANT ANY OBJECT PRIVILEGE: grant object permissions on objects you do not own
ALTER DATABASE and ALTER SYSTEM: allows renaming a data file or flushing the buffer cache

6-5. Predefined Accounts: SYS and SYSTEM

2008-03-27T12:03:00.000-07:00

SYS and SYSTEM accounts have the DBA role granted to them by default
SYS is granted SYSDBA privilege, SYSTEM is not
To connect to the SYS account, you must use the AS SYSDBA clase
SYS account in addition has all privileges with ADMIN OPTION
SYS account owns the data dictionary
SYS account owns the Automatic Workload Repository (AWR)
SYS account owns the data dictionary
Only users with SYSDBA or SYSOPER privilege, are allowed to start up and shutdown the database instance

For more on SYS and SYSOPER go to 6-12. System privileges

SYS and SYSTEM accounts are not used for routine operations. Create privileged users for that. For example, Jim has a low privilege account called jim and a privileged account called jim_dba. This method allows the principle of least privilege to be applied, eliminates the need for account sharing, and allows individual actions to be audited.

6-4. Database User Accounts

2008-03-27T12:02:00.000-07:00

A database user can belong to a consumer group which Oracle Resource Manager uses to balance the allocation of resources (CPU, memory, etc) according to user-defined rules. More info on
http://www.oracle.com/technology/oramag/oracle/04-nov/o64tuning.html

Oracle/PLSQL: NVL Function

2008-03-27T12:01:00.001-07:00

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

Null Values in Group By

2008-03-27T12:00:00.000-07:00

It is known that Null <> Null. When two different NULL values are compared, the result is NULL (not TRUE), i.e. the two NULL values are not considered to be equal. Applying the same rule to the GROUP BY clause would force SQL to place each row with a NULL grouping column into a separate group by itself.

But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.

Simply put if the grouping column contains more than one null value, the null values are put into a single group.

For example, the grouped query:

SELECT A, B,SUM(amount_purchased) AS 'C'
FROM customers
GROUP BY A, B
ORDER BY A, B

will display a results table similar to

A	B	C
NULL	NULL	61438.0000
NULL	101	196156.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	181632.0000

for CUSTOMERS that contain the following rows.

A	B	amount_purchased
NULL	NULL	45612.00000
NULL	NULL	15826.00000
NULL	101	45852.0000
NULL	101	74815.0000
NULL	101	75489.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	96385.0000
LA	NULL	85247.0000

Mechanics of Connecting over TCP/IP

2008-03-16T20:25:00.000-07:00

Expert Oracle Database Architecture 9i and 10g Programming Techniques And Solutions - page 60

We’ll investigate the most common networking case: a network-based connection request over a TCP/IP connection. In this case, the client is situated on one machine and the server resides on another machine, with the two connected on a TCP/IP network. It all starts with the client. The client makes a request using the Oracle client software (a set of provided application program interfaces, or APIs) to connect to database. For example, the client issues the following:

$ sqlplus scott/tiger@ora10g.localdomain

Here, the client is the program SQL*Plus, scott/tiger is the username/password, and ora10g.localdomain is a TNS service name. TNS stands for Transparent Network Substrate and is “foundation” software built into the Oracle client that handles our remote connections, allowing for peer-to-peer communication. The TNS connection string tells the Oracle software how to connect to the remote database. Generally, the client software running on yourmachine will read a file called tnsnames.ora. This is a plain-text configuration file commonly found in the [ORACLE_HOME]\network\admin directory ([ORACLE_HOME] represents the full path to your Oracle installation directory). It will have entries that look like this:

ORA10G.LOCALDOMAIN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)

This configuration information allows the Oracle client software to map the TNS connection string we used, ora10g.localdomain, into something useful—namely, a hostname, a port on that host on which a “listener” process will accept connections, the service name of the database on the host to which we wish to connect, and so on. A service name represents groups of applications with common attributes, service level thresholds, and priorities. The number of instances offering the service is transparent to the application, and each database instance may register with the listener as willing to provide many services. So, services are mapped to physical database instances and allow the DBA to associate certain thresholds and priorities with them.
This string, ora10g.localdomain, could have been resolved in other ways. For example, it could have been resolved using Oracle Internet Directory (OID), which is a distributed Lightweight Directory Access Protocol (LDAP) server, similar in purpose to DNS for hostname resolution. However, use of the tnsnames.ora file is common in most small to medium installations where the number of copies of such a configuration file is manageable.
Now that the client software knows where to connect to, it will open a TCP/IP socket connection to the server with the hostname localhost.localdomain on port 1521. If the DBA for our server has installed and configured Oracle Net, and has the listener listening on port 1521 for connection requests, this connection may be accepted. In a network environment, we will be running a process called the TNS listener on our server. This listener process is what will get us physically connected to our database. When it receives the inbound connection request,it inspects the request and, using its own configuration files, either rejects the request (e.g., because there is no such database, or perhaps our IP address has been disallowed connections to this host) or accepts it and goes about getting us connected.
If we are making a dedicated server connection, the listener process will create a dedicated server for us. On UNIX, this is achieved via a fork() and exec() system call (the only way to create a new process after initialization in UNIX is via fork()). The new dedicated server process inherits the connection established by the listener, and we are now physically connected to the database. On Windows, the listener process requests the database process to create a new thread for a connection. Once this thread is created, the client is “redirected” to it, and we are physically connected. Diagrammatically in UNIX, it would look as shown in Figure 2-4.

On the other hand, the listener will behave differently if we are making a shared server connection request. This listener process knows the dispatcher(s) we have running in the instance. As connection requests are received, the listener will choose a dispatcher process from the pool of available dispatchers. The listener will either send back to the client the connection information describing how the client can connect to the dispatcher process or, if possible, “hand off” the connection to the dispatcher process (this is operating system– and database version–dependent, but the net effect is the same). When the listener sends back the connection information, it is done because the listener is running on a well-known hostname and port on that host, but the dispatchers will be accepting connections on randomly assigned ports on that server. The listener is made aware of these random port assignments by the dispatcher and will pick a dispatcher for us. The client then disconnects from the listener and connects directly to the dispatcher. We now have a physical connection to the database.Figure 2-5 illustrates this process.

Automatic Storage Management

2008-03-14T16:35:00.000-07:00

5-26. What is ASM?

Is a portable and high-performance cluster file system
Manages Oracle database files
Spreads data across disks to balance load
Mirrors data
Solves many storage management challenges
Allows increasing the size of DB without having to shut down the DB to adjust storage allocation

5-27. ASM: Key Features and Benefits

Stripes files, but not logical volumes
Provides online disk reconfiguration and dynamic rebalancing
Allows for adjustable rebalancing speed
Provides redundancy on a per-file basis (rather than on a volume basis)
Supports only Oracle database files (data files, log files, control files, archive logs, Recovery Manager (RMAN) backup sets, etc)
Is cluster aware (so there's no need for a Cluster Logical Volume Manager or a Cluster File System)
Is automatically installed

5-28. ASM: Concepts

ASM disk group
ASM file
ASM disk
Allocation unit
Physical block

Startup and shutdown types

2008-03-09T21:19:00.000-07:00

http://mioracle.blogspot.com/2008/02/arranque-y-parada.html

4-21. Oracle Database Instance states

2008-03-09T19:04:00.000-07:00

Oracle DB can be in one of the following states:

SHUTDOWN
NOMOUNT (instance started): An instance is typically started only in NOMOUNT mode during database creation, during re-creation of control files, or during certain backup and recovery scenarios.
MOUNT (control file opened for this instance): here the data files and online redo log files are NOT opened so specific maintenance tasks can be performed such as 1) renaming data files (though data files on an offline tablespace can be renamed while the db is open), 2) enabling and disabling online redo log file archiving options, 3) perfoming full database recovery, etc.
Note: a database may be left in MOUNT mode even thoug an OPEN request has been made. This may be because the database needs to be recovered in some way.
OPEN: this includes opening the online data files and the redo log files.

You can also start up a database instance in restricted mode so that it is available to users with administrative privileges only. to start an instance in restricted mode, select the "Restrict access to database" option on the Advanced Startup Options page.

Starting an instance includes the following tasks:

Searching /database for a file of a particular name in this order:
- spfile.ora
- If not found, spfile.ora
- If not found, init.oraThis is the file that contains initialization parameters for the instance. Specifying the PFILE parameter with STARTUP overrides the default behavior.
Allocating the SGA
Starting the background processes
Opening the alert.log file and the trace files

4-15. Initialization Parameter Files

2008-03-09T18:56:00.000-07:00

There are two types of parameter files:

Server parameter file: This is the preferred type of initialization parameter file. It is a binary file that can be written to and read by the database server and must not be edited manually. It resides in the server that the Oracle database is executing on, and is persistent across shutdown and startup. this is often referred to as a server parameter file (SPFILE). The default name of this file, which is automatically sought at startup, is app/user/product/11.1.0/db_1/database/spfile.ora.
Text initialization parameter file: This type of initialization parameter file can be read by the database server, but it is not written to by the server. The initialization parameter settings must be set and changed manually by using a text editor so that they are persistent across shutdown and startup. the default name of this file, which is automatically sought at startup if an SPFILE is not found, is init.ora.

4-10. Setting up iSQL*PLUS for SYSDBA and SYSOPER access

2008-03-09T18:08:00.000-07:00

For a user to login to iSQL*PLUS as SYSDBA or SYSOPER you must set up the user in the OC4J user manager by performing the following steps:

Create a user
Grant the webDba role to the user


$ cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus
$ $JAVA_HOME/bin/java -Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home/config/jazn.security.props -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell

JAZN> add user "iSQL*Plus DBA" username password
JAZN> grantrole webDba "iSQL*Plus DBA" username

4-9. Using iSQL*Plus

2008-03-09T17:55:00.000-07:00

ISQL*Plus has a server-side listener process that must be started before you can connect with a browser. To start this server process, use:

isqlplusctl start

After thes server process is started, connect to it by entering the following URL in a browser:

http://host:port/isqlplus

The port number that is used by iSQL*Plus is usually 5560 unless Oracle Universal Installer detects that something is already using that port. Check $ORACLE_HOME/install/portlist.ini to find the port used by iSQL*PLUS

4-3. Management Framework

2008-03-09T16:22:00.000-07:00

There are three major components of the Oracle database management framework:

The database instance that is being managed
A listener that allows connections to the database
The management interface. This may be either a management agent running on the database server (which connects it to Oracle Enterprise Manager Grid Control) or the stand-alone Oracle Enterprise Manager Database Control. this is also referred to as Database Console.

Each of these componenets must be explicitly started before you can use the services of the component and must be shut down cleanly when shutting down the server hosting the Oracle database.

Start up order is important. these components must be started in this order:

Management interface. After this is activated, it can be used to start the other components. Use emctl start dbconsole
Database instance. Either from dbconsole or executing STARTUP command within SQL*PLUS
Listener. Notice that the listener could actually be started before the database instance but there is no point of starting a listener if there is no instance running. Use lsnrctl start

3-14. Using the DBCA to Delete a Database

2008-03-09T15:47:00.000-07:00

To delete (or configure) a database in UNIX or Linux, you must set ORACLE_SID in the shell from which DBCA is launched. Start the DBCA by entering dbca in a terminal window, and click Next on the Welcome page. to delete the database, perform the following steps:

On the Operations page, select Delete a Database, and click Next.
Select the database that you wanted to delete (in class, hist), and click Finish.
Click Yes to confirm your deletion.

Dropping a database involves removing its data files, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all the following conditions must apply:

The database must be mounted and closed.
The database must be mounted exclusively - not in shared mode.
The database must be mounted as RESTRICTED.

An example of this statement is:

DROP DATABASE;

The DROP DATABASE statement has no effect on archieved log files nor does it have any effect on copies or backups of the database. It is best to use Recovery Manager (RMAN) to delete such files. If the database is on raw disks, then the actual raw disk special files are not deleted.

2-19. Installation Option: Silent Mode

2008-03-03T07:56:00.001-08:00

To install and configure Oracle products with OUI in silent mode, perform the following steps:

Create the oraInst.loc file, if it does not already exist.
Prepare a response file based on file templates that are delivered with the Oracle software OR record a response file:
.runInstaller -record -destinationFile
Run OUI in silent or suppressed mode.
If required, run NetCA and DBCA in silent mode.

For details, check Oracle 10g: Administration Workshop I, page 2-19

2-18. Oracle Advanced Installation Options

2008-03-03T07:46:00.000-08:00

With OUI, you can create configurations that use Automatic Storage Management.
You can install and configure the Enterprise Manager (EM) framework. Oracle Enterprise Manager Database Control is installed in the same Oracle home as the database and is configured to run on a stand-alone OC4J instance. You have to perform a separate installation to get EM central management capabilities.
if you choose to use Oracle Enterprise Manager Database Control, you can optionally configure the database to use the Oracle-recommended default backup strategy.
If you choose to use Oracle Enterprise Manager Database Control during the installation, you can configure Enterprise Manager to send e-mail alerts to an e-mail address that you specify. These alerts can include issues such as disk space reaching a critical limit or a database shutting down unexpectedly.
The Oracle Database 10g installation supports RAC features, particularly the installation of Cluster Ready Services (CRS)
Oracle homes can be cloned by using the Enterprise Configuration Management tool. This tool enables users to create clone requests and then schedule and process them. This tool is available via EM Grid Control.

2-5. Command line tools

2008-03-03T05:12:00.001-08:00

To administer Enterprise Manager, use:

emctl start status set stop

To stop and start iSQL*Plus, use:

isqlplusctl start stop

To administer the listener, use:

lsnrctl help start status stop

1-21. Segments

2008-03-03T04:58:00.000-08:00

Data segments: Each nonclustered, non-indexed-organized table has a data segment. All of the table's data is stored in the extents of its data segment. For a partitioned table, each partition has a data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.
Index segments: each index has an index segment that stores all of its data. For a partitioned index. each partition has an index segment.
Undo segments: one UNDO tablespace is created by the database administrator to temporarily store undo information. This information in an undo segment is used to generate read-consistent database information and, during database recovery, to roll back uncommitted transactions for users.
Temporary segments: Temporary segments are created by the Oracle database when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment's extents are returned to the instance for future use. Specify a default temporary tablespace for every user or a default temporary tablespace, which is used databasewide.

1-19. Data block size

2008-03-03T04:54:00.000-08:00

The size of the data block can be set at the time of the creation of the database. The default size of 8 KB is adequate for most databases. If your database supports a data warehouse application that has large tables and indexes, then a larger block size may be beneficial.
If your database supports a transactional application where reads and writes are random, then specifying a smaller block size may be beneficial. The maximum block size depends on your OS. The minimum Oracle block size is 2 KB and should rarely (if ever) be used.

1-17. Tablespaces

2008-03-03T04:45:00.000-08:00

A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objetcs to simplify some administrative operations. You may have a tablespace for application data and an additional one for application indexes.