QUERY - BACKUP

QUERY - BACKUP



Oracle Home and Inventory Backup


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



Note: 
tar -cvf <destination_location> <source_location>


ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC)
cd /u01/before_patch_backup
tar -cvf oracle_base_jun30.tar $ORACLE_BASE
tar -cvf oracle_home_jun30.tar $ORACLE_HOME
tar -cvf oracle_crs_home_jun30.tar $ORACLE_CRS_HOME




All Users passwords backup


spool ./users_pwd_backup.log
select 'alter user '||username||' identified by values '||password||';' from dba_users;  ( For 10g database)
select 'alter user '||name||' identified by values '||password||';' from SYS.USER$; ( For 11g database)
spool off


Database links backup


All DBlinks backup:
set pages 0
set long 90000
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;


For a specific DBlink backup:
set pages 0
set long 90000
SELECT DBMS_METADATA.GET_DDL('DB_LINK','DB_LINK_NAME','USER_NAME') FROM dba_db_links;


Controlfile Backup


alter database backup controlfile to trace; 


show parameter user_dump_dest
(go to udump dest and make the note of controlfile trace)



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

QUERY - DDL OF PACKAGE

PACKAGE DDL


spool packagebackup.sql
select text from dba_source where owner = 'OWNERNAME' and name = 'PACKAGENAME' and type = 'PACKAGE BODY' order by line;
spool 0ff
(OR)
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 50000
select dbms_metadata.get_ddl('PACKAGE','PACKAGENAME') from dual ;


PACKAGE DROP


col object_name for a30
col object_type for a25
col owner for a25
spool packagedrop.sql
select object_name,object_type,owner from dba_objects where object_name='objectname';
drop package <owner>.<package_name>;
spool 0ff



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

Query - DBA views

Query - DBA views

DBA Views Queries

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

select * from dict;

select * from cat;

TABLE COLUMNS

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

select * from dba_tab_columns where table_name = 'table_name';

CONSTRAINTS

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

select table_name, constraint_name, constraint_type, status from dba_constraints where owner in('', '')

select constraint_name, r_constraint_name, constraint_type from dba_constraints where constraint_name = ''; 

select constraint_name, constraint_type from dba_constraints where r_constraint_name = '';

DATAFILES

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

select substr(file_name,1,70) from dba_data_files order by file_name;

DEPENDENCIES

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

select * from user_dependencies where referenced_name = ''

INDEXES

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

select index_name, column_name from dba_ind_columns where table_name = '' order by index_name, column_position

select index_name from dba_indexes where table_name = '';

JOBS

--------

select job, this_date, this_sec, next_date,  next_sec, interval, what,  failures, broken from dba_jobs;

OBJECTS

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

select object_name from dba_objects where status <> 'VALID';

TABLE PRIVILEGES

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

desc dba_tab_privs 

select * from dba_tab_privs where grantee = '';

select * from dba_tab_privs where table_name = '';

ROLE PRIVILEGES

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

select * from dba_role_privs where grantee='';

SYSTEM PRIVILEGES

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

select * from dba_sys_privs where grantee='';

OTHERS

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

DBA_COL_PRIVS

DBA_PRIV_AUDIT_OPTS

SEGMENTS

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

select segment_name, count(*), sum(bytes) from dba_extents where segment_name = '' group by segment_name ;

select * from dba_segments where segment_name = '';

select * from dba_segments@p1aqcis where segment_name = '';

select segment_name, count(*), sum(bytes) from dba_extents group by segment_name having count(*) > 1;

select * from dba_tables where table_name = '';

select segment_name, segment_type, bytes from dba_segments where segment_name in ('','','','','');

select sum(bytes) from dba_segments where segment_name in ('','','','','');

SELECT substr(segment_name, 1, 30) "OBJECT", max(extents) FROM dba_segments group by segment_name having max(extents) > &max_extents_greater_than;

  

SOURCE CODE

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

select owner,name,type,text from dba_source where upper(text) like '%%';

select name,type,text from dba_source where upper(text) like '%%';

select name,type,text from dba_source where upper(text) like '%%';

select substr(line,1,4) line, text from dba_source where type = 'PACKAGE BODY' and name = ''

select name,type,text from dba_source where upper(text) like '%%';

TABLES

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

space allocation for tables using computed statistics

select table_name, num_rows, avg_row_len, (num_rows * avg_row_len) from dba_tables where owner in ('','');

TABLESPACES

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

select tablespace_name, bytes, file_name from dba_data_files order by tablespace_name;

USERS

----------

set pagesize 0

set echo off

select 'ROLE '||GRANTED_ROLE from dba_role_privs where grantee='';

select 'SYSTEM PRIVILEGE '||privilege from dba_sys_privs where grantee='';

select privilege||' ON '||owner||'.'||table_name from dba_tab_privs where grantee='';

set echo on

select * from dba_users where username = '';

VIEWS

----------

select view_name from dba_views where view_name like '%%';

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

COMMAND - DELETING FILES AT OS LEVEL

DELETING FILES AT OS LEVEL


$ df -h .
$ find . -name "*.trc" -mtime +7 -exec ls -ltr {} \;
$ find . -name "*.trc" -mtime +7 -exec rm {} \;


df -h .
$ find . -name "*.trm" -mtime +7 -exec ls -ltr {} \;
$ find . -name "*.trm" -mtime +7 -exec rm {} \;


$  df -h .
$ find . -name "*.gz" -mtime +7 -exec ls {} \;
$ find . -name "*.gz" -mtime +7 -exec rm -rf {} \;


NOTE
find . -name "*.trc" -mtime +7 -exec gzip  {} \;



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

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