• External - no mirroring
• Normal - data is mirrored on two separate disks. This is the default setting.
• High Redundancy - data is mirrored on there separate disks providing three-way mirroring capabilities.

• Simplified daily administration
• The performance of raw disk I/O for all ASM files
• Compatibility with any type of disk configuration, be it JBOD or complex SAN
• Use of a specific file-naming convention to name files, enforcing an enterprise-wide file-naming convention
• Prevention of the accidental deletion of files, since there is no file system interface and ASM is solely responsible for file management
• Load balancing of data across all ASM managed disk drives, which helps improve performance by removing disk hot spots
• Dynamic load balancing of disks as usage patterns change and when additional disks are added or removed
• Ability to mirror data on different disks to provide fault tolerance
• Support of vendor-supplied storage offerings and features
• Enhanced scalability over other disk-management techniques

• When creating disk groups and allocating storage, most customers will be best served by using just two disk groups – one for a recovery area and one for their work area. However, customers with intensive OLTP applications but no disk cache may also need a separate disk group for online log files.
• When creating disk groups, you will need to tell ASM whether the storage is already mirrored by the hardware, or whether ASM should mirror the data

select ....
from ....
model [main] 
   [ reference models ] 
   [ partition by (<cols>)] 
   dimension by (<cols>) 
   measures (<cols>)
    [ ignore nav ] | [ keep nav ]
  [ rules
    [ upsert | update]
    [ automatic order | sequential order ]
    [ iterate (n) [ until <condition>] 
  ]
    (   <cell_assignment> = <expression> ...)

• The "RETURN UPDATED ROWS" clause following the keyword MODEL limits the results returned  to just those rows that were created or updated in this query.  Using this clause is a convenient way to limit result sets to just the newly calculated values.  We will use the RETURN UPDATED ROWS clause throughout our examples.
• The keyword  "RULES," shown in all our examples at the start of the formulas, is optional, but we include it for easier reading. 
• Likewise, many of our examples do not require ORDER BY on the Country column, but we include the specification for convenience in case readers want to modify the examples and use multiple countries.

Table 1: Anchoring Metacharacters Metacharacter Description ^ Anchor the expression to the start of a line $ Anchor the expression to the end of a line Table 2: Quantifiers, or Repetition Operators Quantifier Description * Match 0 or more times ? Match 0 or 1 time + Match 1 or more times {m} Match exactly m times {m,} Match at least m times {m, n} Match at least m times but no more than n times Table 3: Predefined POSIX Character Classes Character Class Description [:alpha:] Alphabetic characters [:lower:] Lowercase alphabetic characters [:upper:] Uppercase alphabetic characters [:digit:] Numeric digits [:alnum:] Alphanumeric characters [:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed [:punct:] Punctuation characters [:cntrl:] Control characters (nonprinting) [:print:] Printable characters Table 4: Alternate Matching and Grouping of Expressions Metacharacter Description | Alternation Separates alternates, often used with grouping operator () ( ) Group Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing [char] Character list Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters Table 5: The REGEXP_LIKE Operator Syntax Description REGEXP_LIKE(source_string, pattern [, match_parameter]) source_string supports character datatypes (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG). The pattern parameter is another name for the regular expression. match_parameter allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity. Example: SELECT ename FROM emp WHERE REGEXP_LIKE (ename, '^J.(N|M),S$'); ENAME ----- JONES JAMES In this example, we tell Oracle to retrieve any values that start with J, followed by any letter, then N or M, then any letter, then S Another one, the following regular expression would match fly, flying, flew, flown, and flies: SELECT c1 FROM t1 WHERE REGEXP_LIKE(c1, ‘fl(y(ing)?|(ew)|(own)|(ies))'); Table 6: The REGEXP_INSTR Function Syntax Description REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) This function looks for a pattern and returns the first position of the pattern. Optionally, you can indicate the start_position you want to begin the search. The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence. The default value of the return_option is 0, which returns the starting position of the pattern; a value of 1 returns the starting position of the next character following the match. Example: SELECT REGEXP_INSTR('5035 Forest Run Trace, Alpharetta, GA', '[^ ]+', 1, 6] "Test" FROM dual;       TEST         36 In this example, we are telling Oracle to examine the string, looking for occurrences of one or more non-blank characters and to return the sixth occurrence of one or more non-blank character. Table 7: Explanation of 5-digit + 4 Zip-Code Expression Syntax Description   Empty space that must be matched [:digit:] POSIX numeric digit class ] End of character list {5} Repeat exactly five occurrences of the character list ( Start of subexpression - A literal hyphen, because it is not a range metacharacter inside a character list [ Start of character list [:digit:] POSIX [:digit:] class [ Start of character list ] End of character list {4} Repeat exactly four occurrences of the character list ) Closing parenthesis, to end the subexpression ? The ? quantifier matches the grouped subexpression 0 or 1 time thus making the 4-digit code optional $ Anchoring metacharacter, to indicate the end of the line Table 8: The REGEXP_SUBSTR Function Syntax Description REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) The REGEXP_SUBSTR function returns the substring that matches the pattern. Example: SELECT REGEXP_SUPSTR('5035 Forest Run Trace, Alpharetta, GA',',[^,]+,') "Test"    FROM dual; Test ------------------ , Alpharetta, In this example we search for a comma, followed by one or more characters immediately followed by a comma. Table 9: The REGEXP_REPLACE Function Syntax Description REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) This function replaces the matching pattern with a specified replace_string, allowing complex search-and-replace operations. Example: SELECT  REGEXP_REPLACE(emp_phone,     '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',     '(\1) \2-\3') "Test"     FROM emp; Test ---------------- (404) 444-4321 (404) 555-5432 (404) 666-6543 In this eample we search for a pattern of numbers that looks like a European phone number listing such as 111.222.3333 and convert it to a normal USA format listing of (111) 222-3333. Table 10: Backreference Metacharacter Metacharacter Description \digit Backslash Followed by a digit between 1 and 9, the backslash matches the preceding digit-th parenthesized subexpression. (Note: The backslash has another meaning in regular expressions; depending on the context it can also mean the Escape character Table 11: Explanation of Pattern-Swap Regular Expression Regular-Expression Item Description ( Start of first subexpression . Match any single character except a newline * Repetition operator, matches previous . metacharacter 0 to n times ) End of first subexpression; result of the match is captured in \1 (In this example, it's Ellen.)   Empty space that needs to be present ( Start of the second subexpression . Match any single character except a newline * Repetition operator matches the previous . metacharacter 0 to n times ) End of second subexpression; result of this match is captured in \2 (In this example, it stores Hildi.)   Empty space ( Start of third subexpression . Match any single character except a newline * Repetition operator matches . metacharacter 0 to n times ) End of third subexpression; result of this match is captured in \3 (In this example, it holds Smith.) Table 12: Explanation of the Social Security Number Regular Expression Regular-Expression Item Description ^ Start of line character (Regular expression cannot have any leading characters before the match.) ( Start subexpression and list alternates separated by the | metacharacter [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {3} Repeat exactly three occurrences of character list - A hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {2} Repeat exactly two occurrences of character list - Another hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {4} Repeat exactly four occurrences of character list | Alternation metacharacter; ends the first choice and starts the next alternate expression [ Start of character list [:digit:] POSIX numeric digit class. ] End of character list {9} Repeat exactly nine occurrences of character list ) Ending parenthesis, to close the subexpression group used for alternation $ Anchoring metacharacter, to indicate the end of the line; no extra characters can follow the pattern

• Metadata (WRM$)
• Historical data (WRH$)
• AWR tables related to advisor functions (WRI$)

AWR Automated Snapshots

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically, when you create a new Oracle database under Oracle Database 10g. To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:
SELECT a.job_name, a.enabled, c.window_name, c.schedule_name, c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
  And a.schedule_name=b.window_group_name
  And b.window_name=c.window_name;

You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable('GATHER_STATS_JOB');

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable('GATHER_STATS_JOB');

• awrrpt.sql, awrrpti.sql:  Generate report for pairs of snapshot ids. awrrpti.sql allows you to pick a database, awrrpt.sql defaults to the current database
• awrddrpt.sql, awrddrpi.sql: Generates a comparison report between two AWR reports.  Can be for single database (compare prod at 2pm to prod at 4pm) or for two different databases (compare prod at 2pm to test at 2pm)
• awrsqrpt.sql, awrsqrpi.sql: Generates a report for a single SQL statement over multiple snapshots.  Report on plans used, resources used.
  
• awrinfo.sql, (must be connected as sys): To review general AWR information.  Tablespace usage, Schema/option usage, Segment usage, snapshot information, retention policies and so on.
• awrextr.sql, awrload.sql, (must be connected as sys) : Datapump exports/imports AWR data so you can move it to another system for analysis/maintaining a longer history.
• awrgrpti.sql, awrgrpt.sql: Generate RAC aware reports by choosing specific instances to include – or to use all available instances
• awrgdrpt.sql, awrgdrpi.sql: Generate RAC aware time period comparison reports – within a set of database instances or across databases
• ashrpt.sql, ashrpti.sql: Generates an ASH report … filter by
  Session
  SQL’s
  Wait Classes
  Database Services
  Modules/Actions/Client_ID
  PL/SQL entry point
  Shows major waits, top services/modules, top clients, SQL activity (parsing, executions, etc), top SQL and more…
• ashrpti.sql: Generates an ASH report.  The “I” version filters by
  Session
  SQL’s
  Wait Classes
  Database Services
  Modules/Actions/Client_ID
  PL/SQL entry point
  Shows major waits, top services/modules, top clients, SQL activity (parsing, executions, etc), top SQL and more…
  

Top User Events

Top Background Events

Top SQL Command Types

• 'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period

Top SQL with Top Events

• Statistics Analysis – the utility checks for stale or missing statistics, which may have a detrimental effect on the query's optimization.
• SQL Profiling – reviews past executions of the SQL statement to provide further information for recommendations.
• Access Path Analysis – determines if additional objects (indexes, materialized views) can be created to improve the statement's performance.
• SQL Structure Analysis – reviews the SQL statement's coding structure to determine if it can be altered to increase performance.

• •Create a new SQL tuning task using the function Create_Tuning_Task
• •Execute the SQL tuning task using the procedure Execute_Tuning_Task
•   Review the tuning recommendations using the function Report_Tuning_Task

select account_no from accounts where old_account_no = 11;

declare
   l_task_id     varchar2(20);
   l_sql         varchar2(2000);
begin
   l_sql := 'select account_no from accounts where old_account_no = 11';
   dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
   l_task_id := dbms_sqltune.create_tuning_task (
      sql_text  => l_sql,
      user_name  => 'SCOTT',
      scope      => 'COMPREHENSIVE',
      time_limit => 120,
      task_name  => 'FOLIO_COUNT' );
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);     
   dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/

set serveroutput on size 999999
set long 999999
spool recommendations.txt
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
spool off;

SET SERVEROUTPUT ON

------------------------------------------------------------------------------
-- Tuning task created for a Specific SQL
------------------------------------------------------------------------------
declare
   l_task_id     varchar2(20);
   l_sql         varchar2(2000);
begin
   l_sql := 'select account_no from accounts where old_account_no = 11';
   dbms_sqltune.drop_tuning_task ('TUNE_ACCOUNT');
   l_task_id := dbms_sqltune.create_tuning_task (
      sql_text  => l_sql,
      user_name  => 'SCOTT',
      scope      => 'COMPREHENSIVE',
      time_limit => 120,
      task_name  => 'TUNE_ACOUNT' );
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);  
   dbms_sqltune.execute_tuning_task ('TUNE_ACOUNT');
end;
/

------------------------------------------------------------------------------
-- Tuning task created for a Specific SQL sending parameters
------------------------------------------------------------------------------
DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql := 'SELECT e.*, d.* FROMM emp e , dept d WHERE e.deptno = d.deptno  AND NVL(empno, ''0'') = :empno';
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'SCOTT',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'EMP_DEPT_TUNING_TASK',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);  
  DBMS_SQLTUNE.execute_tuning_task('EMP_DEPT_TUNING_TASK');END;
/

------------------------------------------------------------------------------
-- Tuning task created for specific a statement from the AWR.
------------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_TUNING_TASK',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
  DBMS_SQLTUNE.execute_tuning_task('19v5guvsgcd1v_AWR_TUNING_TASK');
END;
/

------------------------------------------------------------------------------
-- Tuning task created for specific a statement from the cursor cache.
------------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_TUNING_TASK',
                          description => 'Tuning task for statement 19v5guvsgcd1v in Memory');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
  DBMS_SQLTUNE.execute_tuning_task('19v5guvsgcd1v_TUNING_TASK');
END;
/

------------------------------------------------------------------------------
-- Tuning task created from an SQL tuning set.
------------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'SQLSET_TUNING_TASK',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
  DBMS_SQLTUNE.execute_tuning_task('SQLSET_TUNING_TASK');
END;
/

select account_no from accounts a 
where account_name = 'HARRY' 
and sub_account_name not in 
  ( select account_name from accounts 
    where account_no = a.old_account_no and status is not null);

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The optimizer could not unnest the subquery at line ID 1 of the execution plan.

  Recommendation
  --------------
    Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
    on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
    either "NOT NULL" constraints or "IS NOT NULL" predicates.

  Rationale
  ---------
    A "FILTER" operation can be very expensive because it evaluates the
    subquery for each row in the parent query. The subquery, when unnested can
    drastically improve the execution time because the "FILTER" operation is
    converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might
    produce different results for "NULL" values.

begin
   dbms_sqltune.accept_sql_profile (
      task_name   => 'FOLIO_COUNT',
      name        => 'FOLIO_COUNT_PROFILE'
      description => 'Folio Count Profile',
      category    => 'FOLIO_COUNT');
end;

• Buffer cache (The Default pool) (db_cache_size)
• Shared pool (shared_pool_size)
• Large pool (large_pool_size)
• Java pool (java_pool_size)

begin
   dbms_stats.restore_table_stats (
      'ARUP',
      'REVENUE',
      '31-MAY-04 10.00.00.000000000 PM -04:00');
end;

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------
17-MAY-04 03.21.33.594053000 PM -04:00

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

• output specifies the file where the output is generated. When this option is not specified, the standard output is used for the output.
• session consolidates the trace information for the session specified. The session Id is a combination of session index and session serial number.
• clientid consolidates the trace information given client Id.
• service consolidates the trace information for the given service name.
• action consolidates the trace information for the given action name.
• module consolidates the trace information for the given module name.
• trace_files is a list of all trace file names, separated by spaces, in which trcsess will look for trace information. The wild card character * can be used to specify the trace file names. If trace files are not specified, all the files in the current directory are checked by trcsess.

• Helps the debugging of performance problems in multi-tier environments. In multi-tier environments, a request from an end client is routed to different database sessions by the middle tier.
• Proper accounting of workload for applications using services. Service Name, Module, and Action name provide a means to set apart important transactions in an application.

• client_id_stat_enable: Used to enable statistics accumulation for a given client identifier
• client_id_stat_disable: Used to disable statistics accumulation for a given client identifier
• serv_mod_act_stat_enable: Used to enable statistics accumulation for a given hierarchy of service name, module name, and action name
• serv_mod_act_stat_disable: Used to disable statistics accumulation for a given hierarchy of service name, module name, and action name

• sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the session
• sql_trace_waits—If session tracing is enabled, you can have the trace write wait information to the trace file; very useful in diagnosing performance issues.
• sql_trace_binds—If the session uses bind variables, you can have the trace write the bind variable values to the trace file. This column shows TRUE/FALSE.

• New columns in the v$session and v$session_wait views that track the resources sessions are waiting for.
• A history of waits per session, enabling diagnosis of performance problems for a desired time frame and period.
• Maintaining wait statistics of each SQL statement in the library cache
• Histograms of wait durations, rather than a simple accumulated average

How To Drop, Create And Recreate DB Control In 10g Database

A.Delete DB Control Objects:
There are several ways to delete DB Control Objects.
1).Delete DB Control Configuration Files and Repository Objects using EMCA
2).Delete DB Control Configuration Files using EMCA scripts
3).Delete DB Control Configuration Files Manually:
4).Delete DB Control Repository Objects using RepManager
5).Manually clean everything

1).Delete DB Control Configuration Files and Repository Objects using EMCA
In 10.1 run,   
emca -x SID
RepManager hostname listener_port sid -action drop

In 10.2 run,:
emca -deconfig dbcontrol db -repos drop

2).Delete DB Control Configuration Files using EMCA scripts
In 10.1g run, $emca -x sid
In 10.2g run, $emca -deconfig dbcontrol db Then enter sid as prompt and then y.

3).Delete DB Control Configuration Files Manually:
Remove the following directories from your filesystem:
$ORACLE_HOME/hostname_sid
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid

On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it

On, Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete service_name'
- where service_name is the DB Control service name (typically: OracleDBConsolesid)

Connect as SYSMAN to SQL PLus
C:\>sqlplus sysman
select substr(target_name,1,20) target_name, substr(target_type,1,17) target_type, substr(emd_url,1,40) emd_url
  from mgmt_targets;
TARGET_NAME                        TARGET_TYPE       EMD_URL
---------------------------------  ----------------- ----------------------------------------
machinename.aa.something:1831       oracle_emd       http://machinename.aa.something:1831/emd/main
machinename.aa.something            host             http://machinename.aa.something:1831/emd/main
UTF8                                oracle_database  http://machinename.aa.something:1831/emd/main
LISTENER1_seny-10-79.be.sterianet   oracle_listener  http://machinename.aa.something:1831/emd/main
Management Services and Repository  oracle_emrep

or
TARGET_NAME          TARGET_TYPE       EMD_URL
-------------------- ----------------- ----------------------------------------
machinename.domain   oracle_emd        http://machinename.domain:3938/
machinename.domain   host              http://machinename.domain:3938/
DEV10g2              oracle_database   http://machinename.domain:3938/
LISTENER_mach.domain oracle_listener   http://machinename.domain:3938/
Management Services  oracle_emrep

Now I purge the obsolete entries:
exec mgmt_admin.delete_target('target_name','target_type');
SQL> exec mgmt_admin.delete_target('machinename.aa.something','host');
SQL> exec mgmt_admin.delete_target('LISTENER1_machinename.aa.something','oracle_listener');
SQL> exec mgmt_admin.delete_target('UTF8','oracle_database');
SQL> exec mgmt_admin.delete_target('machinename.aa.something:1831','oracle_emd');

4).Delete DB Control Repository using RepManager:
Invoke following command:
RepManager hostname listener_port sid -action drop

But don't delete by RepManager. It puts the database in quiescence mode.

3 - Connect as SYS to SQLPLus and:
drop user SYSMAN cascade;
drop role mgmt_user;
drop user mgmt_view cascade;
drop public synonym mgmt_target_blackouts;
drop public synonym setemviewusercontext;
drop public synonym mgmt_current_availability;
drop public synonym mgmt_availability;

4- Now take notes of you listener status, host information, network cards,  and all the names (like: Server Name, SID, etc)
lsnrctl status
more /etc/hosts
/sbin/ifconfig

5- Check that the variables
echo $ORACLE_SID
echo $ORACLE_HOME
echo $ORACLE_HOSTNAME

6- Now you are ready to configure all again:
On 10.2  emca -config dbcontrol db -repos create

B.Create DB Control Objects
1)Create only DB Control configuration files:
on 10.1   emca -r
On 10.2  emca -config dbcontrol db

2)Create both the DB Control Repository Objects and Configuration Files:
On 10.1  emca
On 10.2  emca -config dbcontrol db -repos create

STARTED EMCA at Apr 25, 2008 6:25:26 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Enter the following information:
Database SID: DIE
Database Control is already configured for the database DIE
You have chosen to configure Database Control for managing the database DIE
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):

You have specified the following settings
Database ORACLE_HOME ................ C:\oracle\product\10.2.0\db_1
Database hostname ................ CONCORD
Listener port number ................ 1521
Database SID ................ DIE
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 25, 2008 6:26:29 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DIE\emca_2008-04-25_06-25-25-PM.log.
Apr 25, 2008 6:26:44 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 25, 2008 6:26:53 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Apr 25, 2008 6:30:54 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 25, 2008 6:31:49 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 25, 2008 6:32:39 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 25, 2008 6:32:44 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://CONCORD:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 25, 2008 6:32:44 PM

Now wait about 10 Minutes to complete!

Try to connect to the database Control:
http://server_name:1158/em                   For Oracle Enterprise Manager
http://server_name:5560/isqlplus            For iSQL*Plus
http://server_name:5620/ultrasearch       For Ultrasearch

If you have problems to connect, check the local configuration file located on:
$ORACLE_HOME/<hostname>_<service_name>/sysman/config/emoms.properties

Start and Stop the DB-Console
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

C.Recreate/ReConfig DB Control:
1)Recreate only DB Control configuration files:
$ emca -config dbcontrol db

2)Recreate both the DB Control Repository Objects and Configuration Files:
$ emca -config dbcontrol db -repos recreate

References :

    BLOCKS
----------
         8

    BLOCKS
----------
         2

Optional Clauses

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  DELETE WHERE (b.status = 'VALID');

ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ first_rows parallel(test) parallel(test2) */ INTO test
USING test5 new ON (test.pk = new.pk)
WHEN MATCHED THEN UPDATE SET
        fk = new.fk, fill = new.fill;

--Whitespace Support in Windows Path and File Names

SPOOL "My Report.txt"
@"My Report.sql"

• _DATE - Contains the current date (dynamic) by default or else a fixed string
• _PRIVILEGE - Contains privilege level such as AS SYSDBA, AS SYSOPER or blank.
• _USER - Contains the current username (like SHOW USER).
• _CONNECT_IDENTIFER contains the connection identifer used to connect
  

SET FEED OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET FEED ON
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "

-- SHOW RECYCLEBIN

show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BONUS            BIN$F5d+By1uRvieQy5o0TVxJA==$0 TABLE        2004-03-23:11:03:38
DEPT             BIN$Ie1ifZzHTV6bDhFraYImTA==$0 TABLE        2004-03-23:11:03:38
EMP              BIN$Vu5i5jelR5yPGTP2M99vgQ==$0 TABLE        2004-03-23:11:03:38
SALGRADE         BIN$L/27VyBRRP+ZGWnZylVbZg==$0 TABLE        2004-03-23:11:03:38
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

show recyclebin test1
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

utl_i18n.string_to_raw (p_in_val, 'AL32UTF8');

typ => dbms_cryptio.encrypt_aes256 + 
       dbms_cryptio.chain_cbc + 
	   dbms_cryptio.pad_pkcs5

utl_i18n.raw_to_char (l_enc_val, 'AL32UTF8')

Oracle10G's DBMS_FILE_TRANSFER PL/SQL package provides administrators with a mechanism to copy binary files between Oracle databases without using OS commands or FTP.   The transfer package can be executed locally to transfer files to another database server or can be executed remotely to transfer files between two remote databases.  Currently, the only files that can be copied using this mechanism are DataPump dump sets and tablespace data files.  In addition, the file size must be a multiple of 512 bytes and less than 2 terabytes.  Using the file transfer package in conjunction with Oracle's transportable tablespace feature allows administrators to totally automate tablespace data transfers from one database to another.   The process to unplug tablespace data files from the source database, copy the files to the destination server and plug the tablespace data files into the target database can now be executed on a recurring basis by batch jobs initiated by DBMS_JOBS, OEM, KRON, AT and third-party schedulers. The transferred files created on the target platforms are owned by the Oracle account and can be accessed by all database processes. For long copy operations, progress is displayed in the V$SESSION_LONGOPS view

In previous release of Oracle, all parameters were considerd equally important. This made the administration and tuning of the database very difficult because database administrators need to become familiar with over 200 parameters. Oracle 10g introduces two classes of parameter: basic and advanced. In most cases, you need only set up the basic parameters for an Oracle 10g instance.

These basic parameters include:

• cluster_database
• compatible
• control_files
• db_block_size
• db_create_file_dest
• db_create_online_log_dest_n
• db_domain
• db_name
• db_recovery_file_dest
• db_recovery_file_dest_size
• instance_number
• job_queue_processes
• log_archive_dest_n
• log_archive_dest_state_n
• nls_language
• nls_territory
• open_cursors
• pga_aggregate_target
• processes
• remote_listener
• remote_login_passwordfile
• rollback_segments
• sessions
• sga_target
• shared_servers
• star_transformation_enabled
• undo_management
• undo_tablespace

The following is an example of the parameter file generated by DBCR utility (The basic parameters are in bold):

###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=/u03/arch/grid'
log_archive_format=%t_%s_%r.dbf

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=world
db_name=grid

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/grid/bdump
core_dump_dest=/u01/app/oracle/admin/grid/cdump
user_dump_dest==/u01/app/oracle/admin/grid/udump
timed_statistics=TRUE

###########################################
# File Configuration
###########################################
control_files=("/u02/ctl/grid/control01.ctl", "/u02/ctl/grid/control02.ctl")

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Compatibility
###########################################
compatible=10.1.0.0.0

###########################################
# Optimizer
###########################################
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE

###########################################
# Pools
###########################################
java_pool_size=104857600
large_pool_size=8388608
shared_pool_size=104857600

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300

###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

In Oracle9i (9.2.0.3), there are 258 parameters in the v$parameter view. In Oracle 10g (10.1.0.0), there are more than 250 parameters in the v$parameter view. There are 233 parameters in both Oracle9i Release 2 and Oracle 10g Release 1.

Old Parameters

Twenty-five of the 258 parameters no longer exist in Oracle 10g's v$parameter view; these are:

New Parameters

There are twenty more new parameters in Oracle 10g Release 1:

V$SQLSTATS Performance View
Before we discuss the new V$SQLSTATS view, let's review some tuning information. V$SQLAREA is one of the best SQL tuning views. I use the two queries below to identify poorly performing SQL. I take the traditional "top down" tuning approach and start tuning the highest resource consuming SQL idenfified by the scripts below.

The following query dentifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, hash_value, sql_text
  FROM v$sqlarea
  WHERE disk_reads > 5000
  ORDER BY disk_reads;

The following query dentifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, hash_value, sql_text
  FROM v$sqlarea
  WHERE buffer_gets > 100000
  ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 --- for the disk read report and
BREAK ON buffer_gets SKIP 2 --- for the buffer get report

It's common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 100,000. These are good numbers to start with and you can adjust them according to the size of the system you are tuning. You'll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning. Heavy disk reads per statement execution usually means a lack of proper indexing. Heavy buffer reads usually means the exact opposite - indexes are being used when they shouldn't be.

But the SQLTEXT column in V$SQLAREA does not provide the entire text of the SQL statement. That's why I include the HASH_VALUE column in the report. I can use that value to dump the entire SQL statement from V$SQLTEXT using the statement below (where xxxxxxxx is the value in the HASH_VALUE column from the V$SQLAREA reports above):

SELECT sql_text FROM v$sqltext WHERE hash_value = 'xxxxxxxxx' ORDER BY piece;

Oracle 10G R2 provides a new view called V$SQLSTATS that contains a combination of columns that appear in V$SQL and V$SQLAREA. The benefits that V$SQLSTATS provides are as follows:

• Since V$SQLSTATS contains the entire text of the SQL statement AND its associated performance statistics, we are no longer required to access both the V$SQLTEXT and V$SQLAREA to obtain the information we need.
• Oracle states that V$SQLSTATS is faster and more scalable.
• The data in V$SQLAREA has a tendency to get its contents flushed out just when you need to get additional information from it. The V$SQLSTATS view provides users with a longer access window.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ACCOUNT          BIN$GJBH/nI3QIuQZWOapqJXHg==$0 TABLE        2007-10-17:09:42:45
CUSTOMER         BIN$qXjGDja/S2iCMedZ7wu3vg==$0 TABLE        2007-10-17:09:42:46
BANK             BIN$MFryFUanRRS2EVQD/hl9iQ==$0 TABLE        2007-11-09:09:42:45

This shows the original name of the table, BANK, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:

SQL> FLASHBACK TABLE BANK TO BEFORE DROP;
FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BANK                           TABLE

Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

5) Flashback Drop (Recycle Bin)
Dropping of objects by accident has always been a problem for users and DBAs alike. Users soon realize their mistake but then it's too late and historically there is no easy way to recover those dropped tables, indexes, constraints, triggers, etc.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.

What is the Recycle Bin?
The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it.
FLASHBACK TABLE dept_test TO BEFORE DROP;

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:
PURGE RECYCLEBIN;

Managing the Recycle Bin
If the tables are not really dropped in this process--therefore not releasing the tablespace--what happens when the dropped objects take up all of that space? The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed. Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace. In addition, there are several ways you can manually control the recycle bin. If you want to purge the specific table named TEST from the recycle bin after its drop, you could issue
PURGE TABLE TEST;

6) Flashback Database
We have talked abt object recovery before this, now lets talk what if something happens to database, this quickly rewinds the complete database to a previous time, to correct any logical data corruption.This can be used with both RMAN & SQL*Plus. Some of the options are :
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);  --Recover up to 5 minutes ago
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn'

We can also "mark the DB" to a point to restore:
    CREATE RESTORE POINT BEFORE_MY_CHANGES;
And then use the following to recover:
    FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

After the system comes back with FLASHBACK COMPLETE, open the database with Resetlogs. Eg :
-- Create a dummy table.
CREATE TABLE flashback_database_test (id  NUMBER(10)

-Flashback 5 Minutes
CONN sys/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

- Check that the table is gone.
DESC flashback_database_test

Querying Changes to a Table
In this example, I have used a bank's foreign currency management application. The database has a table called RATES to record exchange rate on specific times.

SQL> desc rates
 Name              Null?    Type
 ----------------- -------- ------------
 CURRENCY                   VARCHAR2(4)
 RATE                       NUMBER(15,10)

declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'create index in_bookings_hist_01 on bookings_hist '||
        '(booking_id, cust_name) tablespace users',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/   

Used Bytes      = 7501128
Allocated Bytes = 12582912

$IF boolean_static_expression $THEN text
  [ $ELSIF boolean_static_expression $THEN text ]
  [ $ELSE text ]
$END

CREATE OR REPLACE PROCEDURE debug (p_text  IN  VARCHAR2) AS
  $IF $$debug_on $THEN
    l_text  VARCHAR2(32767);
  $END
BEGIN
  $IF $$debug_on $THEN
    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
      l_text := SUBSTR(p_text, 1 ,233);
    $ELSE
      l_text := p_text;
    $END

    $IF $$show_date $THEN
      DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text);
    $ELSE
      DBMS_OUTPUT.put_line(p_text);
    $END
  $ELSE
    NULL;
  $END
END debug;
/

SET PAGESIZE 30
SELECT text
FROM   user_source
WHERE  name = 'DEBUG'
AND    type = 'PROCEDURE';

TEXT
----------------------------------------------------------------------------------------------------
PROCEDURE debug (p_text  IN  VARCHAR2) AS
  $IF $$debug_on $THEN
    l_text  VARCHAR2(32767);
  $END
BEGIN
  $IF $$debug_on $THEN
    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
      l_text := SUBSTR(p_text, 1 ,233);
    $ELSE
      l_text := p_text;
    $END

    $IF $$show_date $THEN
      DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text);
    $ELSE
      DBMS_OUTPUT.put_line(p_text);
    $END
  $ELSE
    NULL;
  $END
END debug;

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN
  DBMS_PREPROCESSOR.print_post_processed_source (
    object_type => 'PROCEDURE',
    schema_name => 'TEST',
    object_name => 'DEBUG');
END;
/
PROCEDURE debug (p_text  IN  VARCHAR2) AS
BEGIN
NULL;
END debug;