Oracle tablespace from uniform to autoallocate

There is no "alter tablespace" syntax for changing it

You must re-define the tablespace to change the extent management:

• Backup the tablespace
• Export the tablespace data
• Drop and re-allocate the tablespace
• Import the tablespace

You should move the database Audit Trail to a Different Tablespace

You may want to move the database audit trail tables to a different tablespace if the SYSTEM tablespace is too busy

Run the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION PL/SQL procedure to specify the name of the destination tablespace and move it to that tablespace.

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
  AUDIT_TRAIL_LOCATION_VALUE  => 'AUD_AUX');
END;

A way to remove the duplicate records

First you need to query the database to find all duplicate rows, and then you need to run a statement to delete one of each duplicate record that is found

DECLARE
  CURSOR emp_cur IS
  SELECT *
  FROM   employees
  ORDER BY employee_id;
  emp_count         number := 0;
  total_count         number := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('You will see each duplicated employee listed more ');
  DBMS_OUTPUT.PUT_LINE('than once in the list below.  This will allow you to ');
  DBMS_OUTPUT.PUT_LINE('review the list and ensure that indeed...there are more ');
  DBMS_OUTPUT.PUT_LINE('than one of these employee records in the table.');
  DBMS_OUTPUT.PUT_LINE('Duplicated Employees: ');
-- Loop through each player in the table
  FOR emp_rec IN  emp_cur LOOP
-- Select the number of records in the table that have the same ID as the current record
     SELECT count(*)
     INTO emp_count
     FROM employees
     WHERE employee_id = emp_rec.employee_id; 
-- If the count is greater than one then a duplicate has been found, so print it out.
     IF emp_count > 1 THEN
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || emp_rec.first_name ||
                   ' ' || emp_rec.last_name || ' - ' || emp_count);
                  total_count := total_count + 1;
     END IF;

  END LOOP;
END;


Next, you need to delete the duplicated rows that have been found. The following DELETE statement
will ensure that one of the duplicates is removed:

DELETE FROM employees A WHERE ROWID > (
SELECT min(rowid) FROM employees B
WHERE A.employee_id = B.employee_id);

When to use SYS_CONTEXT function

The SYS_CONTEXT function allows you to define a namespace and then place parameters
within it so that they can be retrieved for use at a later time. The general syntax for the use of
SYS_CONTEXT is as follows:

SYS_CONTEXT('namespace','parameter'[,length])

A namespace can be any valid SQL identifier, and it must be created using the CREATE_CONTEXT
statement. The parameter must be a string or evaluate to a string, and it must be set using the
DBMS_SESSION.SET_CONTEXT procedure. The call to SYS_CONTEXT with a valid namespace and parameter will result in the return of a value that has a VARCHAR2 datatype.
The USERENV namespace is automatically available for use because it is a built-in namespace
provided by Oracle. The USERENV namespace contains session information for the current user.

query the database for the user’s information

If you want to obtain environment and session information such as the name and IP address of the
current user so that the values can be stored into local variables for logging purposes you can make use of the SYS_CONTEXT built-in function to query the database for the user’s information. Once you
have obtained the information, then store it into a local variable. At that point, you can do whatever
you’d like with it, such as save it in a logging table.

DECLARE
  username      varchar2(100);
  ip_address    varchar2(100); 

BEGIN
  SELECT SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS')
  INTO username, ip_address
  FROM DUAL;

  DBMS_OUTPUT.PUT_LINE('The connected user is: ' || username || ', and the IP address
 is ' ||
                                                      ip_address);
END;

How to configure the IBM storage subsystem to achieve the best performance and availability possible

• Designate a hot spare disk drive in each expansion drawer.
• Create a RAID 10 array across as many disk drives as possible
• Create a logical drive that is two-thirds the capacity of the RAID 10 array
• Set the stripe size to 512 KB for each logical drive. The 512 KB stripe size provided
  the best results in IBM testing using the ORION utility
• Change cache settings on the logical drive
• Disable dynamic cache read prefetch.
• Map the logical drive to the host system

If you are considering or implementing an Enhanced Remote Mirroring solution, segment the data structure to the smallest size necessary. Small segments limit the amount of data to be transferred across the WAN links

Do not multiplex to the same disk drive

Multiplexing to the same disk drive introduces twice the amount of I/O to the disk drive. Also, no
redundancy exists in case of failure or corruption of the disk drive

How to move lobsegment and lobindex to a different Tablespace

You can move a LOBSEGMENT with the

ALTER TABLE owner.table_name MOVE LOB (column_name) STORE AS (tablespace_name)

command.
You cannot specify a tablespace for the LOBINDEX -- it is automatically created and moved with the LOBSEGMENT.

The mapping between a Table's LOB column and it's LOBSEGMENT (or vice versa , if you start with a LOBSEGMENT and want to know which Table it belongs to) is ALL/DBA/USER_LOBS where
table_name and column_name are available with segment_name. You can even identify the LOBINDEX from index_name in the same view.

Note : "small" LOBs stored inline (ie in the row itself) are not in a seperate LOBSEGMENT at all. That is called STORAGE IN ROW and is the default for LOBs of 4000bytes or less.

Why you should use Oracle RMAN for backups and recovery

• You can take advantage of the powerful Data Recovery Advisor feature, which enables
  you to easily diagnose and repair data failures and corruption
• There are simpler backup and recovery commands
• It automatically manages the backup files without DBA intervention
• It automatically deletes unnecessary backup datafiles and archived redo log files both
  from disk and tape
• It provides you with detailed reporting of backup actions
• It provides considerable help in duplicating a database or creating a standby database
• It lets you test whether you can recover your database, without actually restoring data
• It lets you verify that available backups are usable for recovery
• It lets you make incremental backups, which isn’t possible by any other means of
  backup
• It lets you perform database duplication without backups by using the network-enabled database duplication feature, also known as active duplication
• It automatically detects corrupt data blocks during backups, with the corruption rele-vant information recorded in the V$DATABASE_BLOCK_CORRUPTION view
• When only a few data blocks are corrupted, you can recover at the data block level,
  instead of recovering an entire datafile
• You can take advantage of the unused block compression feature, wherein RMAN skips
  unused data blocks during a backup
• Only RMAN provides the ability to perform encrypted backups
• You can use RMAN with a variety of third-party storage systems
• You can use a powerful yet easy-to-use scripting language, which lets you write custom
  backup and recovery scripts quickly

You should start the X-Window manager only when you really need it

I tend to do a lot of routine tasks from the command line. Most commercial Linux distributions, however, default to starting an X-Window manager following system initialization. That's a lot of system resources to have running.

To change this behavior, edit the /etc/inittab file and locate the line that reads: id:5:initdefault and change it to id:3:initdefault.The system will start with a command line login, and when the need arises to run a window manager, it's easy to simply type startx

Best way to delete oracle flashback logs

The deletion of flashback logs is handled internally, which means you should not delete the flashback logs manually unless you disable the FLASHBACK DATABASE

According to the Oracle Documentation, flashback log files are deleted automatically when:

1. If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
   
2. If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.

Make sure there aren't any guaranteed restore points in place, causing flashback logs to be retained longer than they should. Use this query to check:

select * from v$restore_point;

When to use "SELECT FOR UPDATE NOWAIT"

• Verify the data has not changed since you queried it out (preventing lost updates). 
• Lock the row (preventing the UPDATE or DELETE from blocking).

Solution to a blocked SELECT FOR UPDATE

add the NOWAIT clause and it will no longer block. Instead, your application will report back to the end user that the row is already locked

How to relink oracle binaries

$ORACLE_HOME/bin/relink all

How to easily identify your trace file

It can be hard to identify individual trace files. One way to
make it easier is to specify a trace file identifier for your session. This can be done
by setting the TRACEFILE_IDENTIFIER parameter from within your session:

ALTER SESSION SET tracefile_identifier=GUY

Now when we look in the trace file directory, the tracefile can be identified
by the trailing “GUY”

Oracle Flashback monitoring scripts

PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
 from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)
 from  v$RECOVERY_FILE_DEST;

Upgrading from 11gr1 to 11gr2 Using Data Pump Export/Import

• Export data from the current database using the Export utility shipped with the
  current database
• Install the new Oracle Database software
• If the new database has the same name as the current database, then shut down
  the current database before creating the new database
• Create the new database
• Start SQL*Plus in the new Oracle Database environment
• Connect to the database instance as a user with SYSDBA privileges
• Start an Oracle Database instance using STARTUP
• Optionally, you can change the storage parameters from the source database
• Use the Import utility of the new database to import the objects exported from the
  current database
• After the import, check the import log file for information about which imports of
  which objects completed successfully and, if there were failures, which failed
• If changes are made to the current database after the export, then make sure those
  changes are propagated to the new database before making it available to users

Script to Check for invalid objects

spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off

Automatic memory management

What is a log file sync ?

Log file sync is a client wait event.  It is the wait event your clients wait on when 
they say "commit".  It is the wait for LGWR to actually write their redo to disk and 
return back to them.  You can "tune" this by making lgwr faster (no raid 5 for example) 
and committing less frequently and generating less redo (BULK operations generate less 
redo than row by row do)

The other one is a background wait. LGWR is waiting for forgrounds to finish a current 
copy affecting the data LGWR is about to process.

HOWEVER, that said, tuning either of these will have no noticable affect on your systems 
performance whatsoever!  It certainly looks like "enqueue" is your wait and that is all 
about application design - those are heavyweight locks induced by the application logic 
itself.  You would be best served by looking at the *application* not at the "system" at 
this point. 

More infos here

Best Practices for Creating an Oracle Database

Best Practice	Reasoning
Make the SYSTEM tablespace locally managed	Doing this enforces that all tablespaces created in this database are locally managed
Use the REUSE clause with caution. Normally, you should use it only when you’re re-creating a database	The REUSE clause instructs Oracle to overwrite existing files, regardless of whether they’re in use. This is dangerous
Create a default temporary tablespace with TEMP somewhere in the name	Every user should be assigned a temporary tablespace of type TEMP, including the SYS user. If you don’t specify a default temporary tablespace, the SYSTEM tablespace is used. You never want a user to be assigned a temporary tablespace of SYSTEM. If your database doesn’t have a default temporary tablespace, use the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement to assign one
Create a default permanent tablespace named USERS	This ensures that users are assigned a default permanent tablespace other than SYSTEM. If your database doesn’t have a default permanent tablespace, use the ALTER DATABASE DEFAULT TABLESPACE statement to assign one
Use the USER SYS and USER SYSTEM clauses to specify nondefault passwords	Doing this creates the database with nondefault passwords for database accounts that are usually the first targets for hackers
Create at least three redo log groups with two members each	At least three redo log groups provides time for the archive process to write out archive redo logs between switches. Two members mirror the online redo log members, providing some fault tolerance
Name the redo logs something like redoNA.rdo	This deviates slightly from the OFA standard, but it's better to avoid to name it with the extension of .log because you can accidentally delete it

cached I/O vs direct I/O

cached I/O

direct I/O

1. Application issues a read request
2. Kernel looks for requested data in file buffer cache
3. Requested data not present in file buffer cache
4. Kernel reads data from disk
5. Read data is cached in file buffer cache
6. Read data is copied from file buffer cache to applicatio buffer

1. Application issues a read request
2. Kernel initiates a disk request
3. Requested data transfered from disk to application buffer