Steps - Adding Space to Temporary Tablespace

STEPS - Adding Space to Temporary Tablespace


Set Environment
---------------
export ORACLE_SID=
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin:.


To Check Database
-----------------
set pages 200 lines 170
col HOST_NAME for a15
select instance_name,name db_name,host_name,version db_version from gv$instance,v$database;


TEMP TABLESPACE REPORT
----------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off


COLUMN tablespace_name       FORMAT a18               HEAD 'Tablespace Name'
COLUMN tablespace_status     FORMAT a9                HEAD 'Status'
COLUMN tablespace_size       FORMAT 999,999,999,999   HEAD 'Size'
COLUMN used                  FORMAT 999,999,999,999   HEAD 'Used'
COLUMN used_pct              FORMAT 999               HEAD 'Pct. Used'
COLUMN current_users         FORMAT 9,999             HEAD 'Current Users'


SELECT
    d.tablespace_name                      tablespace_name
  , d.status                               tablespace_status
  , NVL(a.bytes, 0)                        tablespace_size
  , NVL(t.bytes, 0)                        used
  , TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct
  , NVL(s.current_users, 0)                current_users
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
  , v$sort_segment  s
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = s.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
/


To Find the free space in Temp:
-------------------------------
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


Temp Free Space:
----------------
SELECT tablespace_name, SUM(bytes_used)/1024/1024/1024 "used(GB)", SUM(bytes_free)/1024/1024/1024 "free(GB)" FROM V$temp_space_header GROUP BY tablespace_name;


Tempfile location:
------------------
col tablespace_name for a15
col file_name for a35
select TABLESPACE_NAME,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_temp_files where tablespace_name='TABLESPACENAME' order by 1,2;


Filesystem
----------
$ df -h /tempfile/path/
or
$ df -g /tempfile/path/


Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on


set pages 200 lines 160
spool ./resize.log
set echo on feed on time on term on


To resize a datafile:-
----------------------
alter database tempfile '/tempfile/path/tempfilename.dbf' resize 4096m;
To resize a datafile:-(ASM)
----------------------
ALTER DATABASE TEMPFILE '&FILE_NAME' RESIZE 4096M;


spool off


BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE
---------------------------------------------------------------
SQL> Select file_name from dba_temp_files where file_name like '%&tempdatafilename%';


set pages 200 lines 160
spool ./add.log
set echo on feed on time on term on


Adding space in temp tablespace:
--------------------------------
ALTER TABLESPACE <temptablespacename> ADD TEMPFILE '/tempfile/path/tempfilename.dbf' SIZE 4096M;


Adding space in temp tablespace:(ASM)
--------------------------------
ALTER TABLESPACE <temptablespacename> ADD TEMPFILE '+DATA' SIZE 64G;


spool off


Take Table Space Report.

"Hope for the best, prepare for the worst"
-Dharmendra

STEPS - Adding Space to Tablespace

Set Environment
-----------------
export ORACLE_SID=
export ORACLE_HOME=
export PATH=$PATH:$ORACLE_HOME/bin:.

To Check Database
---------------------
set pages 200 lines 170
col HOST_NAME for a15
select instance_name,name db_name,host_name,version db_version from gv$instance,v$database;

Table Space Report
---------------------
col tablespace_name format a15
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used(vs)|(Allocated)" form 99999999.99
select a.tablespace_name
        ,a.alloc_size/1024/1024 Allocated_size
        ,a.cur_size/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
                ,nvl(sum(s.bytes),0) used
          from  dba_segments s
               ,dba_tablespaces t1
          where t1.tablespace_name=s.tablespace_name(+)
          group by t1.tablespace_name) u
        ,(select d.tablespace_name
                ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
                ,sum(d.bytes) cur_size
                ,count(*) file_count
          from dba_data_files d
          group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
  and t.tablespace_name=a.tablespace_name
order by t.tablespace_name;

Datafiles of a particular TableSpace
-------------------------------------
set pages 200
set lines 175
col tablespace_name for a27
col file_name for a70
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAX_MB from dba_data_files  where tablespace_name='TABLESPACENAME' order by 1,2;

ASM Space Report
--------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    gv$asm_diskgroup
ORDER BY
    name;
/

OTHER ASM QUERIES
--------------------------
select distinct name, total_mb/1024 , (total_mb-free_mb)/1024 , free_mb/1024, SYSDATE from GV$ASM_DISKGROUP;

select GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB from gv$asm_diskgroup order by NAME;
select table_name from dict where table_name like '%ASM%';
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from v$asm_disk;
select INST_ID,GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from gv$asm_disk;

Filesystem
-----------
$ df -h /datafile/path/
or
$ df -g /datafile/path/

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

set pages 200 lines 160
spool ./resize.log
set echo on feed on time on term on

To resize a datafile
-------------------
alter database datafile '/datafile/path/datafilename.dbf' resize 4096m;

To resize a datafile  (ASM)
----------------------------
ALTER DATABASE DATAFILE '&FILE_NAME' RESIZE 4096M;

spool off;

BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE
------------------------------------------------------------------------------------------------
For datafile
-----------
select file_name from dba_data_files where file_name like '%&datafilename%';

set pages 200 lines 160
spool ./add.log
set echo on feed on time on term on

To add a new datafile in a tablespace
--------------------------------------
alter tablespace <TABLESPACE NAME> add datafile '/datafile/path/datafilename.dbf' size 4096m;

To add a new datafile in a tablespace  (ASM)
-----------------------------------------------
ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;

To Create a new tablespace  (ASM)
--------------------------------------
CREATE TABLESPACE <TABLESPACE NAME> DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;
alter tablespace <TABLESPACE NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;

spool off;

Take Table Space Report.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Tablespaces that are >=80% full, and how much to add to make them 80% again
---------------------------------------------------------------------------
set pages 999 lines 100
col    "Tablespace"    for a50
col    "Size MB"     for 999999999
col    "%Used"     for 999
col    "Add (80%)"     for 999999
select    tsu.tablespace_name "Tablespace"
,    ceil(tsu.used_mb) "Size MB"
,    100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
,    ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
    from    dba_data_files group by tablespace_name) tsu
,        (select ts.tablespace_name
    ,       nvl(sum(bytes)/1024/1024, 0) free_mb
    from    dba_tablespaces ts, dba_free_space fs
    where   ts.tablespace_name = fs.tablespace_name (+)
    group by ts.tablespace_name) tsf
where    tsu.tablespace_name = tsf.tablespace_name (+)
and    100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order    by 3,4
/

"Hope for the best, prepare for the worst"
-Dharmendra

ORACLE DBA NOTES PART 4

Oracle Architecture

Oracle Instance:

a means to access an Oracle database,always opens one and only one database and consists of memory structures and background process.

Oracle server:

a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.

Oracle database:

a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Instance memory Structures:

System Global Area (SGA):

Allocated at instance startup, and is a fundamental component of an Oracle Instance.

SGA Memory structures:

Includes Shared Pool,  Database Buffer Cache, Redo Log Buffer among others.

Shared Pool :

Consists of two key performance-related memory structures Library Cache and  Data Dictionary Cache.

Library Cache:

Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.

Data Dictionary Cache :

Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.

Database Buffer Cache:

Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.

Redo Log Buffer :

Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.

User process:

Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.

Server process:

Connects to the Oracle Instance and is Started when a user establishes a session.

fulfills calls generated and returns results.

Each server process has its own nonshared PGA when the process is started.

Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.

In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.

Program Global Area (PGA):  

Memory area used by a single Oracle server process.

Allocated when the server process is started, deallocated when the process is terminated and used by only one process.

Used to process SQL statements and to hold logon and other session information.

Background processes: 

Started when an Oracle Instance is started.

Background Processes Maintains and enforces relationships between physical and memory structures

There are two types of database processes:

      1.      Mandatory background processes

      2.      Optional background processes

Mandatory background processes:

– DBWn, PMON, CKPT,  LGWR,  SMON

Optional background processes:

– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

DBWn writes when:

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

Log Writer (LGWR) writes:

• At commit

• When 1/3rd full

• When there is 1 MB of redo

• Every 3 seconds

• Before DBWn writes

System Monitor (SMON) Responsibilities:

• Instance recovery

– Rolls forward changes in redo logs

– Opens database for user access

– Rolls back uncommitted transactions

• Coalesces free space

• Deallocates temporary segments.

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

Checkpoint (CKPT) Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

Archiver (ARCn)

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database

Upgrading to Oracle Database 11g

Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

1.Manual upgrade which involves the following steps:

Backup the database.

In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.

Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script, explained below.

Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.

Recompile invalid objects.

Restart the database.

Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.

Troubleshoot any issues or abort the upgrade.

Backup the database

-------------------

rman "target / nocatalog"

RUN

{

ALLOCATE CHANNEL chan_name TYPE DISK;

BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';

}

The easiest way to abandon the upgrade is to restore that backup.

      rman "target / nocatalog"

            STARTUP NOMOUNT

            RUN

            {

            REPLICATE CONTROLFILE FROM 'save_controlfile_location';

            ALTER DATABASE MOUNT;

            RESTORE DATABASE FROM TAG before_upgrade

            ALTER DATABASE OPEN RESETLOGS;

      }

In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.

Creating the SYSAUX tablespace which is required for 11g.

                        SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'

                        SIZE 500M REUSE

                        EXTENT MANAGEMENT LOCAL

                        SEGMENT SPACE MANAGEMENT AUTO

                        ONLINE;

RMAN Cloning using active database feature - Oracle 11g feature

Normally we well knew about RMAN cloning.

Oracle9i/10g using duplicate cloning command, we do the RMAN cloning.

We need a RMAN full backup for cloning the database in oracle9i/10g.

In oracle 11g provides a new feature, without RMAN database backup we can clone the database.

Environment Details:

Operating system: Windows XP service pack 2

Database Version: 11.1.0.7 (32 bit)

Source database name: troy

Clone database name: clonedb

How its work?

In an active duplication process, target database online image copies and archived redo log files were copied through the auxiliary instance service name. So we no need the target database backup.

Target database must be in archive log mode.

Database duplication process RMAN does the following things

1.Generate the unique DBID for auxiliary database.

2.Copy the data files & archived log files from target database to auxiliary database.

3.Recreate the new control files for auxiliary database.

4.Recreates the online redo log files.

5.Restart the auxiliary instance.

6.Open the database with RESETLOGS.

Real Application Clusters

Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all your business applications. Oracle RAC provides the foundation for enterprise grid computing.

Oracle’s Real Application Clusters (RAC) option supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages. Oracle RAC running on clusters provides Oracle’s highest level of capability in terms of availability, scalability, and low-cost computing.

One DB opened by multipe instances so the the db ll be Highly Available if an instance crashes.

Cluster Software. Oracles Clusterware or products like Veritas Volume Manager are required to provide the cluster support and allow each node to know which nodes belong to the cluster and are available and with Oracle Cluterware to know which nodes have failed and to eject then from the cluster, so that errors on that node can be cleared. 

Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk. 

The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.

The voting disk is used to determine if a node has failed, i.e. become separated from the majority. If a node is deemed to no longer belong to the majority then it is forcibly rebooted and will after the reboot add itself again the the surviving cluster nodes.

Data Guard

Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

Data Guard Configurations:

A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. 

Primary Database:

A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.

Standby Databases:

A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.

The types of standby databases are as follows:

Physical standby database:

Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

Logical standby database:

Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

Snapshot Standby Database (UPDATEABLE SNAPSHOT FOR TESTING)

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.

Row Chaning and Migration

Row Migration:

A row migrates when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

Row Chaining:

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. 

Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. 

So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

Top 10 Things to Look for in AWR Report and

STATSPACK Output

Many DBAs already know how to use STATSPACK but are not always sure what to check regularly.

Remember to separate OLTP and Batch activity when you run STATSPACK, since they usually

generate different types of waits. The SQL script “spauto.sql” can be used to run STATSPACK

every hour on the hour. See the script in $ORACLE_HOME/rdbms/admin/spauto.sql for more

information (note that JOB_QUEUE_PROCESSES must be set > 0). Since every system is different,

this is only a general list of things you should regularly check in your STATSPACK output:

¦ Top 5 wait events (timed events)

¦ Load profile

¦ Instance efficiency hit ratios

¦ Wait events

P:\010Comp\OracleT&T\305-9\ch14.vp

Color profile: Generic CMYK printer profile

Composite  Default screen

¦ Latch waits

¦ Top SQL

¦ Instance activity

¦ File I/O and segment statistics

¦ Memory allocation

¦ Buffer waits

Oracle Architecture

An Oracle server: a DBMS that provides an open, comprehensive, integrated approach to information management, Consists of an Instance and a database.

An Oracle Instance: a means to access an Oracle database, always opens one and only one database and consists of memory structures and background process.

An Oracle database: a collection of data that is treated as a unit, Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?

orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.

Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

orainstRoot.sh

[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory to 770.

Changing groupname of /u01/app/oraInventory to dba.

The execution of the script is complete

root.sh

[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

For Oracle installation on unix/linux, we will be prompted to run a script 'root.sh' from the oracle inventory directory.

this script needs to run the first time only when any oracle product is installed on the server. 

It creates the additional directories and sets appropriate ownership and permissions on files for root user.

What would be the first thing you would do if an end user complains that performance is poor?

I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.

As for the answer to the question, one should touch upon all these points:

- Check alert log, for any errors and UN even entries

- Check for archive log destination/file system full

- Check for database locks # Click here for a notes on locks

- Check server performance (CPU/IO)

- Check network performance

- Check if statistics is up to date

- If new SQL, then tune SQL (maybe take trace)

- Check if any parameters have been modified recently

- Check if any new updates as been done from the Application team.

Oracle Database 11g Top New Feature for DBAs

1) Automatic Diagnostic Repository [ADR]

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Performance Analyzer (SPA) New

13) DBMS_STATS Enhancements

14) The Result Cache

15) Total Recall (Flashback Data Archive)

Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

How to Download Patchset or Opatch from metalink

Update:

If you are using latest support.oracle.com then after login to metalink Dashboard 

- Click on Patches and Updates tab

- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".

- A new window will appear. 

- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.

- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.

- You will go the download page. From the download page you can also change your platform and patchset version.

http://docs.oracle.com/cd/E11857_01/em.111/e12255/e_oui_appendix.htm

Oracle® Universal Installer and OPatch User's Guide

11g Release 2 (11.2) for Windows and UNIX

Part Number E12255-11

How to apply a patch ?

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch. 

2. Make sure you have a good backup of database. 

3. Make a note of all Invalid objects in the database prior to the patch. 

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc. 

5. You MUST Backup your oracle Home and Inventory

tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches 

7. cd to the patch direcory and do opatch -apply to apply the patch. 

8. Read the output/log file to make sure there were no errors.

Patching Oracle Software with OPatch

Example 7-1

opatch napply <patch_location> -skip_subset -skip_duplicate

Example 7-2 applies patches 1, 2, and 3 that are under the <patch_location> directory. OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).

Table 7-1 OPatch OUI-based Commands

Command

Description

apply

Installs an interim patch. See "Apply Command for OUI-based Oracle Homes" for more information.

napply

Applies Oracle Clusterware patches. See the "Auto Command for OUI-based Oracle Homes" for more information.

auto

Applies Oracle Clusterware patches. See the "Auto Command for OUI-based Oracle Homes" for more information.

lsinventory

Lists what is currently installed on the system. See "Lsinventory Command for OUI-based Oracle Homes" for more information.

query

Queries a given patch for specific details. See "Query Command for OUI-based Oracle Homes" for more information.

rollback

Removes an interim patch. See "Rollback Command for OUI-based Oracle Homes" for more information.

nrollback

Removes n number of patches (hence nrollback). See "Nrollback Command for OUI-based Oracle Homes" for more information.

version

Prints the current version of the patch tool. See "Version Command for OUI-based Oracle Homes" for more information.

Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch

http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/

OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.

Difference Between Local Inventory and Global Inventory

What is oraInventory ?

oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.

There are basically two kind of inventories,

One is Local Inventory (also called as Oracle Home Inventory) and other is  Global Inventory (also called as Central Inventory).

What is Global Inventory ?

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)

You will see entry like

HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

What is Local Inventory ?

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

Can I have multiple Global Inventory on a machine ?

Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

What to do if my Global Inventory is corrupted ?

No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option

-attachHome

./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc

ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”

CLUSTER_NODES=”{}”

Do I need to worry about oraInventory during oracle Apps 11i cloning ?

No, Rapid Clone will update both Global & Local Inventory with required information , you don’t have to worry about Inventory during Oracle Apps 11i cloning.

Central Inventory

The Central Inventory contains the information relating to all Oracle products installed on a host. It contains the following files and folders:

·         Inventory File

·         Logs Directory

Oracle Home Inventory

Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:

$ORACLE_HOME/inventory

It contains the following files and folders:

·         Components File

·         Home Properties File

·         Other Folders

Incremental backup levels:

Level 0 – full backup that can be used for subsequent incrementals

RMAN> backup incremental level 0 database;

Differential Level 1 – only the blocks that have changed since the last backup (whether it is level 0 or level 1) 

RMAN> backup incremental level 1 differential database;

Cumulative Level 1 – all changes since the last level 0 incremental backup

RMAN> backup incremental level 1 cumulative database;

A full backup cannot be used for a cumulative level 1 backup. 

A cumulative level 1 backup must be done on top of an incremental level 0 backup.

To Find and Delete bigger size and older files in Linux

--To find out files size more than 5MB

find . -size +5000  -exec ls -ltr {} \;  

-- To **Remove** files size more than 5MB

find . -size +5000k  -exec rm -rf {} \;

--To find out files older than 30days 

find . -mtime +30 -exec ls -ltr {} \;

--To find **Remove** files older than 30days 

find . -mtime +30  -exec rm -rf {} \; 

Oracle DBA Questions

Oracle DBA QUESTIONS


Architecture
==========


11G New Features @ Architecture level? RESULT Cache,...


11G Backgroung Processes?


Literal Vs. Bind Variables?
Select * from emp where dept=10; what level should i configure for better performance Where Clause .... Literal or Bind Variables? Expalin?


Incarnation? Explain in detail? Where the incarnation information will be stored?


What happens when we give command ALTER DATABASE OPEN RESET LOGS?


What is SCN? SCN vs Sequence Number?


Hard Parse Vs. Soft Parse?


Backup&Recovery
=============
My Database has Level 1 backup, tell me what are all backed up ? with Example?


Databse is UP and has taken Level 0 backup, is the backup taken is Consistent or Inconsistent?
How do you say a backup is Consistent or Inconsistent, Oracle Terminology?


Can we take backup when the Database is down?


If i have backup of Sun @9PM, on Mon 9PM taken incremental Level 1 backup. What type of backup do you get and what is actually backedup?


A table got dropped between 9AM - 11AM how to get the Table backup using RMAN, DB size 500GB available mount point space for table recovery is 15GB?


Sys Admin has changed the time from 10:00 AM to 9:30  AM, table dropped, How do you recover the Table?


Performance Tuning
==================
As a DBA what Pro active steps do you take for a Highly utilized Undo?
AWR Vs. ASH?
AWR Contents?
Wait Events?
Difference between DB File Sequential Read and DB file Scattered Reads?
Database Replay?


Datapump
========
How Datapump  works internally? What resources will be utilized at Network level, in Memory level?
How to Export Dumpfile to different mount points (multiple dumpfiles) sametime both in EXP and EXPDP,  as the destination has less space available?


Dataguard
========
What is Active Dataguard?
What is Snapshot Standby?
Primary DB Dataguard Parameter?
Parameters responsible for Failover in Dataguard?
Difference between FAL_SERVER (Fetch Archive Log) Vs. FAL_Client
What is RFS?
What is DELAY?
By default in what mode will the Standby will be, either SYNC or ASYNC?


RAC
===
SCAN?
LMON?
VIP?
TAF?
FAN?
OCR?
VD?
How to take VD backup?
How to take OCR backup? Do you take OCR backup?
How do you know which is the Master Node without connecting to Database?


Cloning
=======
What is Cloning?
How to do take RMAN Cloning? Explain Steps?


MISC
====


RAC:
1) About OCR and VOTING files?
2)Cache Fusion?
3)Cache coherence?
4)Node evicted scenario?
5)what is SCAN_LISTENER?
6)OCSSD?
7) STAND ALONE DATABASE TO RAC SETUP?
8)how many voting disks using ur organization?


INDEXES:
1)When u rebuild the INDEX?
2)diff b/w Bitmap INDEX and B tree INDEX?
3)reverse key index?
4)local index and global index?
5)PARTITIONS?


1)Explain RESULT CACHE? How to enable and how to disable?
2)when u applied ‘ALTER DATABASE OPEN RESETLOGS’?
3)One of user drop my dept table In prod how to recover it?
4)in RMAN level 11gR2 NF?
5)Cloning of database ? 10g and 11g
6)Explain LOCKs, LATCHES AND ENQUES?
7)DIFF b/w soft parsing and hard parsing?
8)which process bring the data from datafiles to database buffer cache?
9)NF 11gR1 and 11gR2?
10)what is RFS?
11)diff b/w cumulative, differential and level 0 backup?
12)about DB architecture?




1) If one of my mandatory background process goes down what will happen
2) What is SGA_TARGET and SGA_MAX_SIZE
3) What is MMON, MMAN
4) If I keep SGA_TARGET =0 then what will happen
5) In 2 RAC, how many IP’s are r using
6) What is difference between RAC ipaddresses
7) Can application developer access the private ip
8) What is the VIP
9) What is voting disk
10) How many voting disks are u maintaining
11) Why we need to keep odd number of voting disks
12) What is the difference between DG and STANDBY database
13)  What are new background processes are there in 10g and 11g
14) When will use Reset Logs Option?
15) What is OPROCD?
16) ASM Redundancy Levels?
17) Dataguard Parameters?
18) Backup Stratergy u r using?
19) Incremental vs Cumulative Backups?
20) Can application developer access the private ip?



"Hope for the best, prepare for the worst"
-Dharmendra

ORACLE DBA NOTES PART 3

ARCHITECHTURE

http://rajeevjhaoracle.wordpress.com/2012/01/12/pfile-and-spfile-initialization-files-in-oracle/

What are new background processes are there in 10g and 11g

http://oracleinstance.blogspot.in/2009/11/oracle-background-processes.html

http://www.dba-oracle.com/concepts/background_processes_concepts.htm

10g new background processes

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.(ASMM)

RVWR(Recovery Writer.)

Process responsible for writing flashback logs which stores pre-image of data-blocks.

These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

M000

MMON background slave (m000) processes. 

RBAL

RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx

These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB

The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

11g new background processes ?

The following process are added in 11g as new background processes.

1 dbrm DB resource manager 

2 dia0 Diagnosability process 

3 fbda Flashback data archiver process 

4 vktm Virtual Timekeeper 

5 w000 Space Management Co-ordination process 

6 smc0 Space Manager process 

NOTE : The above six are mandatory processes.

But 11g has 56 new processes added which can be queried using

What is MMON, MMAN ?

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management(ASMM).

How to find out background processes ?

To findout background processes from database:

SQL> select SID,PROGRAM from v$session where TYPE='BACKGROUND';

SQL> select name,description from V$bgprocess;

To findout background processes from OS:

$ ps -ef|grep ora_|grep SID

If one of my mandatory background process goes down what will happen ?

http://satya-dba.blogspot.in/2009/08/background-processes-in-oracle.html

If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted

Background processes are started automatically when the instance is started.

Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional, will be invoked if that particular feature is activated.

If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted.

Any issues related to background processes should be monitored and analyzed from the trace files generated and the alert log.

What is SGA_TARGET and SGA_MAX_SIZE ?

SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don't use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow.

SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE.

SGA_MAX_SIZE and SGA_TARGET both are the parameter are used to change the SGA SIZE.

SGA_MAX_SIZE sets the maximum value for sga_target.

SGA_TAGET is 10G feature used to change the sga size dynamically .it specifies the total amount of SGA memory available to an instance.

this feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size are affected. 

SGA_MAX_SIZE & SGA_TARGET

http://maxwellmiranda.wordpress.com/2009/09/17/sga_max_size-sga_targe/

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.

The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.

I.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can’t resize the SGA_TARGET value to more than 4GB.

It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET

Do not dynamically set or unset the SGA_TARGET parameter. This should be set only at startup.

http://www.orafaq.com/wiki/SGA_target

SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.

SGA_TARGET provides the following:

§  Single parameter for total SGA size 

§  Automatically sizes SGA components

§  Memory is transferred to where most needed

§  Uses workload information 

§  Uses internal advisory predictions 

§  STATISTICS_LEVEL must be set to TYPICAL 

§  SGA_TARGET is dynamic 

§  Can be increased till SGA_MAX_SIZE

§  Can be reduced till some component reaches minimum size

§  Change in value of SGA_TARGET affects only automatically sized components

If I keep SGA_TARGET =0 then what will happen ?

Disable automatic SGA tuning by setting sga_target=0

Disable ASMM by setting SGA_TARGET=0

http://www.orafaq.com/wiki/SGA_target

SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.

Default value     0 (SGA auto tuning is disabled)

What happens when you run ALTER DATABASE OPEN RESETLOGS ?

The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.

The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don't come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can't open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files. 

In what scenarios open resetlogs required ?

An ALTER DATABASE OPEN RESETLOGS statement is required,

1.after incomplete recovery (Point in Time Recovery) or

2.recovery with a backup control file.

3. recovery with a control file recreated with the reset logs option.

http://onlineappsdba.com/index.php/2009/09/11/oracle-database-incarnation-open-resetlogs-scn/

http://web.njit.edu/info/limpid/DOC/backup.102/b14191/osrecov009.htm

Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation..

Difference between RESETLOGS and NORESETLOGS ?

http://oracleappstechnology.blogspot.in/2008/05/difference-between-resetlogs-and.html

After recover database operation, open the database with: ALTER DATABASE OPEN [NO]RESETLOGS

NORESETLOGS:

The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

RESETLOGS:

CAUTION: Never use RESETLOGS unless necessary.

Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!

Before using the RESETLOGS option take an offline backup of the database.

What is SCN (System Change Number) ?

The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.

Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.

What is Database Incarnation ?

Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.

Database incarnation falls into following category Current, Parent, Ancestor and Sibling

i) Current Incarnation : The database incarnation in which the database is currently generating redo.

ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.

iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.

iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

How to view incarnation history of Database ?

Using SQL> select * from v$database_incarnation;

Using RMAN>LIST INCARNATION;

However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.

•For example my current database INCARNATION is 3 and now I have used

FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,

RMAN> RESET DATABASE TO INCARNATION 2;

RMAN> RECOVER DATABASE TO SCN 3000;

DATAGUARD PARAMETERS

http://sarith.wordpress.com/tag/oracle-10g-data-guard/

Set Primary Database Initialization Parameters

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.

DB_NAME=chicago

DB_UNIQUE_NAME=chicago

LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'

CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'

LOG_ARCHIVE_DEST_1=

 'LOCATION=/arch1/chicago/

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_2=

 'SERVICE=boston LGWR ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

  DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

Primary Database: Standby Role Initialization Parameters

FAL_SERVER=boston

FAL_CLIENT=chicago

DB_FILE_NAME_CONVERT='boston','chicago'

LOG_FILE_NAME_CONVERT=  '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'

STANDBY_FILE_MANAGEMENT=AUTO

Prepare an Initialization Parameter File for the Standby Database

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:

CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Modifying Initialization Parameters for a Physical Standby Database

.

DB_NAME=chicago

DB_UNIQUE_NAME=boston

LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'

CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'

DB_FILE_NAME_CONVERT='chicago','boston'

LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=  'LOCATION=/arch1/boston/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 

DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_2=  'SERVICE=chicago LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=chicago'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=chicago

FAL_CLIENT=boston

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

RAC

====

http://rajeevjhaoracle.wordpress.com/2012/01/02/oracle-rac-interview-question-answer/

Requirement for Oracle Clusterware

1. External Shared Disk to store Oracle Cluster ware file (Voting Disk and Oracle Cluster Registry - OCR) 

2. Two netwrok cards on each cluster ware node (and three set of IP address) -

Network Card 1 (with IP address set 1) for public network

Network Card 2 (with IP address set 2) for private network (for inter node communication between rac nodes used by clusterware and rac database)

IP address set 3 for Virtual IP (VIP) (used as Virtual IP address for client connection and for connection failover)

3. Storage Option for OCR and Voting Disk - RAW, OCFS2 (Oracle Cluster File System), NFS, …..

Which enable the  load balancing of applications in RAC?

Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.

In 2 node RAC, how many NIC’s are r using

2 network cards on each clusterware node 

Network Card 1 (with IP address set 1) for public network

Network Card 2 (with IP address set 2) for private network (for inter node communication between rac nodes used by clusterware and rac database)

In 2 node RAC, how many IP’s are r using

6 - 3 set of IP address

## eth1-Public:  2

## eth0-Private: 2

## VIP: 2

How to find IP’s information in RAC

      Edit the /etc/hosts file as shown below:

# Do not remove the following line, or various programs

# that requires network functionality will fail.

127.0.0.1               localhost.localdomain localhost

## Public Node names

 192.168.10.11          node1-pub.hingu.net     node1-pub

192.168.10.22          node2-pub.hingu.net     node2-pub

## Private Network (Interconnect)

 192.168.0.11            node1-prv               node1-prv

192.168.0.22            node2-prv               node2-prv

## Private Network (Network Area storage)

 192.168.1.11            node1-nas               node1-nas

192.168.1.22            node2-nas               node2-nas

192.168.1.33            nas-server              nas-server

## Virtual IPs

 192.168.10.111          node1-vip.hingu.net     node1-vip

192.168.10.222          node2-vip.hingu.net     node2-vip

What is difference between RAC ip addresses ?

Public IP adress is the normal IP address typically used by DBA and SA to manage storage, system and database. Public IP addresses are reserved for the Internet.

Private IP address is used only for internal clustering processing (Cache Fusion) (aka as interconnect). Private IP addresses are reserved for private networks.

VIP is used by database applications to enable fail over when one cluster node fails. The purpose for having VIP is so client connection can be failover to surviving nodes in case there is failure

Can application developer access the private ip ?

No. private IP address is used only for internal clustering processing (Cache Fusion) (aka as interconnect)

 What is a virtual IP address or VIP?

A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.

What is the use of VIP?

If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.

Give situations under which VIP address failover happens:-

VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.

Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.

What is the significance of VIP address failover?

When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.

What is voting disk

Voting Disk is a file that sits in the shared storage area and must be accessible by all nodes in the cluster. All nodes in the cluster registers their heart-beat information in the voting disk, so as to confirm that they are all operational. If heart-beat information of any node in the voting disk is not available that node will be evicted from the cluster. The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat of all nodes to the voting disk. When any node is not able to send heartbeat to voting disk, then it will reboot itself, thus help avoiding the split-brain syndrome.

For high availability, Oracle recommends that you have a minimum of three or odd number (3 or greater) of votingdisks.

Voting Disk - is file that resides on shared storage and Manages cluster members.  Voting disk reassigns cluster ownership between the nodes in case of failure.

The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently members of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity.

Oracle Database 11g Release 2 provides the ability to store the voting disks in ASM along with the OCR. Oracle Clusterware can access the OCR and the voting disks present in ASM even if the ASM instance is down. As a result CSS can continue to maintain the Oracle cluster even if the ASM instance has failed.

How many voting disks are u maintaining ?

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/RACR2ARC6/Default.aspx

By default Oracle will create three voting disk files in ASM.

Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.

You should plan on allocating 280MB for each voting disk file. For example, if you are using ASM and external redundancy then you will need to allocate 280MB of disk for the voting disk. If you are using ASM and normal redundancy you will need 560MB.

Why we need to keep odd number of voting disks ?

Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.