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