Install Oracle 11.2.0.3 with ASM on Centos 6.3

http://dba010.wordpress.com/2013/02/18/install-oracle-11-2-0-3-with-asm-on-centos-6-3/

Linux Basic commands

HOw to install VLC on linux

http://superuser.com/questions/620693/install-vlc-2-0-7-in-centos-6-4

Define Multiple Location for archive log

alter system set log_archive_dest_2='location=/u01/app/dest2/arch' scope=both;

Basic SQL statement performance diagnosis - HOW TO, step by step instructions

http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

PLSQL Tutorial

Declarations 
Declare
message varchar2(20):='Hello World';
Begin
dbms_output.put_line(message);
End;
/

Materialized Views in Oracle

A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to achieve replication of data between sites. The example code in this article assumes DB1 is the master instance and DB2 is the materialized view site.

Basic syntax

CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;

• IMMEDIATE : The materialized view is populated immediately.
• DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.

• FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
• COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
• FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

• ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
• ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

 The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations.

The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

Grant  Permissions
Check the user who will own the materialized views has the correct privileges. At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them CREATE DATABASE LINK privilege also.

CONNECT sys@db2

GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;

Create Materialized View 

Connect to the materialized view owner and create the database link and the materialized view itself.

CONNECT scott/tiger@db2

CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@db1.world;

Remember to gather stats after building the materialized view.

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'SCOTT',
    tabname => 'EMP_MV');
END;
/ 

Create Materialized View Logs

Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view.
To take advantage of the of the fast refresh, connect to the master instance and create the materialized view log.

CONNECT scott/tiger@db1

CREATE MATERIALIZED VIEW LOG ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Refresh Materialized Views

If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.
For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.
The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.

BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

BEGIN
   DBMS_REFRESH.add(
     name => 'SCOTT.MINUTE_REFRESH',
     list => 'SCOTT.EMP_MV',
     lax  => TRUE);
END;
/

A materialized view can be manually refreshed using the DBMS_MVIEW package.

EXEC DBMS_MVIEW.refresh('EMP_MV');

Cleaning Up

CONNECT scott/tiger@db2
DROP MATERIALIZED VIEW emp_mv;
DROP DATABASE LINK DB1.WORLD;

BEGIN
  DBMS_REFRESH.destroy(name => 'SCOTT.MINUTE_REFRESH');
END;
/

CONNECT scott/tiger@db1
DROP MATERIALIZED VIEW LOG ON scott.emp;

Aggregations and Transformations

Materialized views can be used to improve the performance of a variety of queries, including those performing aggregations and transformations of the data. This allows the work to be done once and used repeatedly by multiple sessions, reducing the total load on the server.
The following query does an aggregation of the data in the EMP table.

CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

Create a materialized view to perform the aggregation in advance, 

making sure you specify the ENABLE QUERY REWRITE clause. 

CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE 
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM   emp
GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno; 

Before using materialized views and materialized view logs, consider the following:

• Populating a materialized view adds load to both servers involved. The source server is queried to capture the data, which is inserted into the destination server. Be sure the additional load does not adversely affect your primary system.
• Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DDL on the base table. Check the additional work does not adversely affect performance on the primary system.
• If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits.
• Depending on the Oracle version and the complexity of the associated query, fast refreshes may not be possible.
• When using materialized views to improve performance of transformations and aggregations, the QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED parameters must be set or the server will not be able to automatically take advantages of query rewrites. These parameters may be set in the pfile or spfile file if they are needed permanently. Later releases have them enabled by default.

 

Oracle installation on Linux 6

http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-6.php

http://www.tecmint.com/oracle-database-11g-release-2-installation-in-linux/

ftp://mirror.aarnet.edu.au/pub/oraclelinux/OL6/U1/x86/

https://wikis.oracle.com/display/oraclelinux/Downloading+Oracle+Linux

Oracle Basic Commands for linux

How to start Enterprise Manager in DB console
emctl status dbconsole

here you get also your http-adress to call

if it's not running:

emctl start dbconsole


export ORACLE_SID=<database_sid>

emctl start dbconsole



Set ur path env variable in your .bash_profile or .bashrc file

export PATH=$ORACLE_HOME/bin:$PATH

Then just issue the command in your prompt

linux]dbca
linux]netmgr

NB: if your are in a remote machine you need to set DISPLAY variable too

or you can directly to ur bin folder where you can see all the utilities.. you can run it like

linux ]./dbca

Oracle Auto Start on Linux

From Oracle 11gR2 onward the use of the dbstart and dbshut scripts is deprecated. In Oracle 11gR2 the preferred replacement is Oracle Restart. Oracle Restart is itself deprecated in Oracle 12c, with no nominated replacement at the time of writing.
Both dbstart and dbshut are still present in Oracle 11gR2 and Oracle 12cR1, so you can continue to use them for now (I still use them). In order to use Oracle Restart you must install Grid Infrastructure (GI), which you will already have if you are using RAC or ASM for a standalone instance. In these cases, Oracle Restart will already be present and running. For single instance databases that don't use ASM, I think it is unreasonable to expect people to install GI, so the following describes a method for those cases, while avoiding dbstart and dbshut.

Create a file called "/etc/init.d/dbora" as the root user, containing the following.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database software.

ORA_OWNER=oracle

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        # Remove "&" if you don't want startup as a background process.
        su $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1" &

        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        rm -f /var/lock/subsys/dbora
        ;;
esac


Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/dbora

Associate the dbora service with the appropriate run levels and set it to auto-start using 

the following command. 

 chkconfig --add dbora

Next, we must create the "startup.sh" and "shutdown.sh" scripts in the "/home/oracle/scripts". First create the directory.

    # mkdir -p /home/oracle/scripts
    # chown oracle.oinstall /home/oracle/scripts

#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOSTNAME=ol6-121.localdomain
export ORACLE_UNQNAME=db12c

export ORACLE_SID=db12c
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Start Listener
lsnrctl start

# Start Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF

The "/home/oracle/scripts/shutdown.sh" script is similar.

#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOSTNAME=ol6-121.localdomain
export ORACLE_UNQNAME=db12c

export ORACLE_SID=db12c 
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Stop Database
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Stop Listener
lsnrctl stop 

Make sure the permissions and ownership of the files is correct.

# chmod u+x /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh
# chown oracle.oinstall /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh

The listener and database will now start and stop automatically with the
 machine. You can test them using the following command as the "root" 
user.

# service dbora start
# service dbora stop 

For more help visit 

http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php#known_issues