ASM DISKGROUP ADDING

The system i’ll be adding the disk on is a linux system using asmlib.

Current Set-up

In the current set-up we have 3 disks with 1 disk in the FLASH disk group and the 2 remaning disks in the DATA diskgroup

SQL> select PATH, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;

PATH HEADER_STATU MODE_ST STATE
————— ———— ——- ——–
ORCL:VOLUA MEMBER ONLINE NORMAL
ORCL:VOLUB MEMBER ONLINE NORMAL
ORCL:VOLUC MEMBER ONLINE NORMAL
SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME TYPE TOTAL_MB FREE_MB
—————————— —— ———- ———-
DATA EXTERN 4094 3273
FLASH EXTERN 2047 1820

Set-up the 2 new disks

We have 2 new disks added /dev/sde & /dev/sdf so we need to set-up a parttaion on these disks and configure them in asmlib

# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):
Using default value 261

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):
Using default value 261

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

#
# /etc/init.d/oracleasm createdisk VOLUD /dev/sde1
Marking disk “/dev/sde1” as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOLUE /dev/sdf1
Marking disk “/dev/sdf1” as an ASM disk: [ OK ]
# /etc/init.d/oracleasm listdisks
VOLUA
VOLUB
VOLUC
VOLUD
VOLUE
#

Create the new diskgroup

Now we can create the new diskgroup as we have the 2 new disks VOLUD & VOLUE

SQL> select PATH, NAME, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;

PATH NAME HEADER_STATU MODE_ST STATE
————— —————————— ———— ——- ——–
ORCL: VOLUD PROVISIONED ONLINE NORMAL
ORCL:VOLUE PROVISIONED ONLINE NORMAL
ORCL:VOLUA VOLUA MEMBER ONLINE NORMAL
ORCL:VOLUB VOLUB MEMBER ONLINE NORMAL
ORCL:VOLUC VOLUC MEMBER ONLINE NORMAL
SQL> create diskgroup DATA2
2 external redundancy
3 disk ‘ORCL:VOLUD’, ‘ORCL:VOLUE’;

Diskgroup created.

SQL> select PATH, NAME, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;

PATH NAME HEADER_STATU MODE_ST STATE
————— —————————— ———— ——- ——–
ORCL:VOLUA VOLUA MEMBER ONLINE NORMAL
ORCL:VOLUB VOLUB MEMBER ONLINE NORMAL
ORCL:VOLUC VOLUC MEMBER ONLINE NORMAL
ORCL:VOLUD VOLUD MEMBER ONLINE NORMAL
ORCL:VOLUE VOLUE MEMBER ONLINE NORMAL

SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME TYPE TOTAL_MB FREE_MB
—————————— —— ———- ———-
DATA EXTERN 4094 3273
FLASH EXTERN 2047 1820
DATA2 EXTERN 4094 4042

DATA2 diskgroup created.

ASM queries

Disk Group Information

set pages 1000 lines 120
col NAME for a15
select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,
TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

DG# NAME                 AU_SZ STATE       TYPE     TOTAL_MB    FREE_MB OFFLINE_DISKS
———- ————— ———- ———– —— ———- ———- ————-
1 DATA               1048576 MOUNTED     EXTERN       5114       3353             0
2 FRA                1048576 MOUNTED     EXTERN       5114       2613             0

Disk Information

set pages 1000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,
PATH FROM V$ASM_DISK;

DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH
———– ——- ———— ——- ——– ——————————
0 CACHED  MEMBER       ONLINE  NORMAL   /dev/asmdisk1
0 CACHED  MEMBER       ONLINE  NORMAL   /dev/asmdisk2

Rebalancing Information

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES from v$asm_operation;

GROUP_NUMBER OPERA STAT    ACTUAL  SOFAR      EST_MINUTES
———— —– —- ———- ———- ———–
2            REBAL RUN           1         49          16

Removing Disk from a ASM diskgroup

So we have the following disks and the plan is to remove RAVE

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME TOTAL_MB FREE_MB
------------ ------------------------------ ---------- ----------
0 1019 0
1 RAVA 1019 842
1 RAVB 1019 841
1 RAVC 1019 841
2 RAVD 1019 792
1 RAVE 1019 841

Drop the disk from the Disk group

SQL> alter diskgroup DATADG drop disk 'RAVE';

Diskgroup altered.

Check the re-balance status

SQL> select sysdate, GROUP_NUMBER, OPERATION, STATE, POWER, ACTUAL, SOFAR, EST_WORK
2 EST_RATE, EST_MINUTES from v$asm_operation;

SYSDATE GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_RATE
--------- ------------ ----- ---- ---------- ---------- ---------- ----------
EST_MINUTES
-----------
12-SEP-14 1 REBAL RUN 1 1 1 193
3

Now the disk is not assigned to a disk group

SQL> select group_number, name, TOTAL_MB, FREE_MB from V$asm_disk_stat;

GROUP_NUMBER NAME TOTAL_MB FREE_MB
------------ ------------------------------ ---------- ----------
0 1019 0
1 RAVA 1019 783
1 RAVB 1019 783
1 RAVC 1019 782
2 RAVD 1019 792

Check the disk status now RAVE should be listed as FORMER and is ready to be re-used

SQL> select PATH, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;
PATH HEADER_STATU MODE_ST STATE
-----------
ORCL:RAVE FORMER ONLINE NORMAL
ORCL:RAVF PROVISIONED ONLINE NORMAL
ORCL:RAVA MEMBER ONLINE NORMAL
ORCL:RAVB MEMBER ONLINE NORMAL
ORCL:RAVC MEMBER ONLINE NORMAL
ORCL:RAVD MEMBER ONLINE NORMAL
6 rows selected.

Oracle Day to Day queries

Database start time:
++++++++++++++++

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance

check the tablespace size
--------------------------------------
set linesize 200
set pagesize 300
select t.tablespace,  t.totalspace as " Totalspace(MB)",round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free" from
(select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace
from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f
group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;


select min(creation_time),df.ts#,ts.name,sum(df.bytes)/1024/1024 curr_size_mb,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) growth_per_day,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 120 growth_120_days,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 240 growth_240_days,
(sum(bytes)/1024/1024)+((sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time))*365) projection_365_days_mb
from v$datafile df ,v$tablespace ts where df.ts#=ts.ts#  group by df.ts#,ts.name order by df.ts#;


check the tablespace size in percentages
-----------------------------------------------------------
select a.tablespace_name,  a.alloc_megs,  b.total_megs, ( ( b.total_megs - a.alloc_megs ) / b.total_megs) * 100 as "Percentage Used" from alloc_space a,avail_space b where a.tablespace_name = b.tablespace_name;

check the database size
-------------------------------------
 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size",
round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used,
(select sum(bytes) as p
from dba_free_space) free
group by free.p;

SQL>select 'Complete Database Size is', sum(bytes/1024/1024/1024) tt, 'Gb.' from sys.dba_data_files;

find the datafiles details of tablespaces
--------------------------------------------------------
 select file_name,tablespace_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024,maxblocks,increment_by from dba_data_files where tablespace_name='SYSTEM';
other activities in database
--------------------------------------

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select profile,resource_name,limit from dba_profiles order by profile;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';

select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;

select owner,last_analyzed,num_rows from dba_tables where table_name=''

select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;

select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session  where trunc(logon_time)<sysdate;

select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4  order by logon_time;

select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';

select holding_session from dba_blockers a where not exists (select  'x'  from dba_waiters b where b.waiting_session = a.holding_session)

select default_tablespace from dba_users ;

select username,account_status from dba_users where username like ''

 select PRIVILEGE from  dba_sys_privs where GRANTEE=MULLIGAN_N';

select GRANTED_ROLE from DBA_ROLE_PRIVS  where GRANTEE='HSERVER';

select PRIVILEGE from  dba_sys_privs where GRANTEE='HSERVER';

select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';

select * from v$access where object=''

select tablespace_name, username, bytes, max_bytes  from dba_ts_quotas where username='MKALDAS';


select count(*),trunc(first_time) from v$log_history group by trunc(first_time);

select sum(bytes/1024/1024/1024) from dba_segments;

select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';

select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';

select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='';

select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'

select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select sid,serial#,status,logon_time from v$session where program like 'rman%';

select count(*) from v$session where type='USER';

select count(*),trunc(logon_time)  from v$session where type='USER' group by trunc(logon_time);

select fuzzy,count(*) from v$datafile_header group by fuzzy;

select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');

select sid,serial#,username from v$session where lockwait is not null;

select * from gv$session where lockwait is not null;

select * from v$session where lockwait is not null;

s

select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);

select inst_id from gv$instance;

select inst_id,username,count(*) from gv$session group by inst_id,username;

select hash_value from v$sql where sql_text like ' ';

select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;

select * from gv$lock where block <1;

 select  TABLESPACE_NAME,
  2  ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';

select file_name from dba_temp_files;

select memeber from v$logfile;

select name from v$controlfile;

select open_mode from v$database;

select instance_name from v$instance;

select name from v$tablespace;

select name,status from v$datafile;

select temporary_tablespace from dba_users;

select * from dba_synonyms where owner='';

select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';

select max(sequence#) from v$log_history;

select host_name from v$instance;

select host from dba_db_links;

select dbid from v$database;

select * from registry$history;

select * from dba_registry;

select * from v$backup;

select * from v$recoverfile;

select distinct status from v$datafile;

select TEXT from dba_source where name='VERIFY_FUNCTION';

select * from V$PWFILE_USERS;

select * from ejbtimer;
===========================================================================================================================

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

alter user username identified by

alter database datafile '' offline drop;

alter database datafile 'PATH'  resize ;

alter database open;

alter database default temporary tablespace '';

alter user QUEST default tablespace QUEST_INDEX;

alter tablespace LARGE_FACT_X add datafile '/u20/oradata/GDWQ/LARGE_FACT_x02.dbf' size 2850m;

alter database backup controlfile to trace;

alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL

alter user SRIRAM_S PASSWORD EXPIRE;

ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;

alter view STAGING.TD_D_SCORE_V compile;

CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";

===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;

drop temporary tablespace tablespace_name including contents and datafiles;

drop user username;

drop user username cascade;

drop table tablename purge;

drop table tablename cascade constraints;

drop table tablename;
====================================================================================================

find tablespaces is online or offiline :

select  tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';

find tablespaces is autoexten, tbalespace name :

select file_name , tablespace_name, autoextensible from dba_data_files;


find the privilege in database;



select  privilege||' '||table_name  "Privileges for " from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';
10:33:13 AM: Lakshmidevi V: this is to check all the privs for a user





SQL> alter user ISMDBA11 account unlock;

User altered.

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,PROFILE from dba_users;


SELECT s.sid, s.serial#, s.username, s.program,
          i.block_changes
          FROM v$session s, v$sess_io i
          WHERE s.sid = i.sid
          ORDER BY 5 desc;


SELECT s.sid, s.serial#, s.username, s.program,
         t.used_ublk, t.used_urec
          FROM v$session s, v$transaction t
          WHERE s.taddr = t.addr
          ORDER BY 5 desc, 6 desc;


Use this query to find the session information from OS Process id
----------------------------------------------------------------------------------------------------------------------

select sid,serial#,status,machine,program,sql_hash_value,to_char(sysdate,'DD/MM/YYYY HH24:MI')  from v$session
where paddr in (select addr from v$process where spid=&spid)
/

select spid from v$process where addr =(select paddr from v$session
where sid=&sid)
/

====================================================================================================
find . -name "030609_GSM.txt" -print (for searching a file from the filesystem)
find . -print|xargs grep -i v\$session
find . -mtime -1 -print (To find Recently created files)
find . -size +10000 -print (find largest files on the server)
find . -mtime +7 (Delete files in bulk)
find . -size +100000 -exec ls -alrt {} \;
find . -size +10000 -exec ls -alrt {} \;
find / -name Chapter1 -type f -print
find /usr -name Chapter1 -type f -print
find /usr -name "Chapter*" -type f -print
find /usr/local -name "*.html" -type f -print
find /usr/local -name "*.html" -type f -exec chmod 644 {} \;
find . -type f -exec ls -l {} \;
find . -type d -exec ls -ld {} \;
find . -type f -name "Poop*" -exec rm {} \;
find . -type d -name CVS -exec rm -r {} \;
find . -type f -name -exec chmod 644 {} \;
find . -type d -name -exec chmod 755 {} \;
locate lost-file
====================================================================================================
Memory Check

$prtconf   |  grep  "Mem" (Solaris)
$prtconf|grep -i mem (Solaris)
$lsdev -C|grep mem (AIX)
$ipcs -pmb (Allocated memory Segments)
$lsdev -C|grep Process|wc -l (Dispays the number of CPU's)
$psrinfo -v|grep "Status of processor"|wc -l (Dispays the number of CPU's in Solaris)
$kmtune|grep sem (Displays values for semaphores)
$grep error /var/adm/syslog/syslog.log|more (Show server log in HP-UX)
$errpt -a|more (Show server log in AIX)
$lsps -a (Displays swap usage in AIX)
$swapinfo -tam (Displays swap usage in HP-UX)
====================================================================================================
Script to create DB link

select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by d3vf0rsys# using '''|| HOST||''';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='PROD');


select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
 substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'br0adc#st using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
 where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='PROD')
and  substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid


Script to Drop DB link

select 'drop database link '||DB_LINK||';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='DEV');


select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
 substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'d3cis1#n using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
 where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='DEV')
and  substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid
===================================================================================================
SNAPSHOT_FEATURE PROCEDURE (REBUILD)

select OWNER,NAME,MASTER_OWNER,MASTER,to_char(LAST_REFRESH,'dd/mm/yyyyhh24:mi') from DBA_SNAPSHOT_REFRESH_TIMES where name like '%SERVICE_FEATURE%'

select host,OWNER from dba_db_links where db_link='CUSTDB.WORLD';

 select OWNER,NAME,RNAME,REFGROUP,JOB,NEXT_DATE from DBA_REFRESH_CHILDREN;

nohup ./run.sh @snapshot_rebuild.sql &

BEGIN
         DBMS_REFRESH.ADD(
           name => '"DBLUSGO"."MPSGUIDE_SNAPS"',
           list => '"DBLUSGO"."SERVICE_FEATURE_SNP"',
           lax => TRUE);
      END;

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');
====================================================================================================
This script provides the backup status of the datafiles in the database.
This is a good check to perform on an oracle database before the start of every business day to make sure that none of the oracle data files are left in a backup mode.

set lines 132
col name format a50
select * from (
select f.name, to_char(b.time,'mm/dd hh24:mi'), b.status
from v$backup b, v$datafile f
where b.file# = f.file#
order by b.time desc)
where rownum < 11
/
====================================================================================================
set pages 300
set linesize 3000
select rpad(a.tablespace_name,20,'.')tablespace_name,
   sum(a.bytes)/(1024*1024) Allocated,
   sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
   max(nvl(b.space,0)) Free,
   max(nvl(b.cont,0))/(1024*1024)  Contiguous
from dba_data_files a,
   (select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
    from dba_free_space
    group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
group by a.tablespace_name order by TABLESPACE_NAME
/
====================================================================================================
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",
decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB",
decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || '  **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by tablespace_name
/
====================================================================================================
select index_name ,column_position colp,rtrim( column_name) colu
from user_ind_columns
where index_name in (
select index_name from user_indexes
where table_name = '&tname')
order by  index_name,column_position
/

select index_type from user_indexes where index_name in ('IX_sms_content_1','IX_sms_content_2');

drop index "IX_sms_content_1";

create index "IX_sms_content_1" on "sms_content" (CUSTOMERID,FOLDERID,MESSAGECONTENTID) online compute statistics tablespace O2OM_DATA_INDEX_01;
====================================================================================================
execute dbms_stats.gather_table_stats (ownname => 'TAP_USER',tabname=>'TAPOUT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'EPIMM',METHOD_OPT=>'FOR ALL INDEXED COLUMNS ',DEGREE=>8,CASCADE=>TRUE);

exec DBMS_JOB.BROKEN( job=>192, broken=>FALSE );

exec dbms_system.SET_SQL_TRACE_IN_SESSION('&SID','&SR',&TF);

exec statspack.snap (i_snap_level=>7)

exec DBMS_JOB.remove (212);

exec DBMS.JOB.RUN (214);
====================================================================================================
===========================================================================================================================
select table_name from dict where table_name like '%CONS%';
===========================================================================================================================
select 'GRANT EXECUTE ON '||OWNER||'.'||OBJECT_NAME||' TO CHS_READ;' FROM DBA_OBJECTS WHERE OWNER='DSSPRIVATE' AND OBJECT_TYPE ='PROCEDURE';

select 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||OWNER||'."'||OBJECT_NAME||'" TO ROK_S;' FROM DBA_OBJECTS WHERE OWNER='O2OMDDL' AND OBJECT_TYPE IN ('TABLE');

select 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO MCIVER_F;' FROM DBA_OBJECTS WHERE OWNER='OADBA' AND OBJECT_TYPE IN ('TABLE');

select distinct owner from dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');

select 'GRANT SELECT ON '||OWNER||'.'||SEGMENT_NAME||' TO BI_DSTAGE_EXT;'  FROM dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');

select  FREE_SPACE,MAX_FREE_SIZE,USED_SPACE,AVG_USED_SIZE from v$shared_pool_reserved;
select 'exec DBMS_JOB.BROKEN (job==> '||JOB|| ',broken==>TRUE);'  from dba_jobs where LOG_USER!='SYS;

select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||' ;' from dba_role_privs where GRANTEE='C_NALABOTHU_R';

select 'execute dbms_stats.gather_table_stats (ownname =>'||''''||OWNER||''''||',tabname=> '||''''||TABLE_NAME||''''||',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);' from dba_tables where OWNER='STAGING' and LAST_ANALYZED IS NULL;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select profile,resource_name,limit from dba_profiles order by profile;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';

select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;

select owner,last_analyzed,num_rows from dba_tables where table_name=''

select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;

select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session  where trunc(logon_time)<sysdate;

select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4  order by logon_time;

select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';

select holding_session from dba_blockers a where not exists (select  'x'  from dba_waiters b where b.waiting_session = a.holding_session)

select default_tablespace from dba_users ;

select username,account_status from dba_users where username like ''

 select PRIVILEGE from  dba_sys_privs where GRANTEE=MULLIGAN_N';

select GRANTED_ROLE from DBA_ROLE_PRIVS  where GRANTEE='HSERVER';

select PRIVILEGE from  dba_sys_privs where GRANTEE='HSERVER';

select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';

select * from v$access where object=''

select tablespace_name, username, bytes, max_bytes  from dba_ts_quotas where username='MKALDAS';


select count(*),trunc(first_time) from v$log_history group by trunc(first_time);

select sum(bytes/1024/1024/1024) from dba_segments;

select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';

select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';

select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='SYSTEM';

select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'

select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select sid,serial#,status,logon_time from v$session where program like 'rman%';

select count(*) from v$session where type='USER';

select count(*),trunc(logon_time)  from v$session where type='USER' group by trunc(logon_time);

select fuzzy,count(*) from v$datafile_header group by fuzzy;

select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');

select sid,serial#,username from v$session where lockwait is not null;

select * from gv$session where lockwait is not null;

select * from v$session where lockwait is not null;

s

select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);

select inst_id from gv$instance;

select inst_id,username,count(*) from gv$session group by inst_id,username;

select hash_value from v$sql where sql_text like ' ';

select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;

select * from gv$lock where block <1;

 select  TABLESPACE_NAME,
  2  ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';

select file_name from dba_temp_files;

select memeber from v$logfile;

select name from v$controlfile;

select open_mode from v$database;

select instance_name from v$instance;

select name from v$tablespace;

select name,status from v$datafile;

select temporary_tablespace from dba_users;

select * from dba_synonyms where owner='';

select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';

select max(sequence#) from v$log_history;

select host_name from v$instance;

select host from dba_db_links;

select dbid from v$database;

select * from registry$history;

select * from dba_registry;

select * from v$backup;

select * from v$recoverfile;

select distinct status from v$datafile;

select TEXT from dba_source where name='VERIFY_FUNCTION';

select * from V$PWFILE_USERS;

select * from ejbtimer;
===========================================================================================================================

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

alter user username identified by

alter database datafile '' offline drop;

alter database datafile 'PATH'  resize ;

alter database open;

alter database default temporary tablespace '';

alter user QUEST default tablespace QUEST_INDEX;

alter tablespace UNDOTBS1 add datafile '/crmc1p_db17/oradata/undotbs05.dbf' size 2000m;

alter database backup controlfile to trace;

alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL

alter user SRIRAM_S PASSWORD EXPIRE;

ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;

alter view STAGING.TD_D_SCORE_V compile;

CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";

===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;

drop temporary tablespace tablespace_name including contents and datafiles;

drop user username;

drop user username cascade;

drop table tablename purge;

drop table tablename cascade constraints;

drop table tablename;
====================================================================================================

find tablespaces is online or offiline :

select  tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';

find tablespaces is autoexten, tbalespace name :

select file_name , tablespace_name, autoextensible from dba_data_files;


find the privilege in database;



select  privilege||' '||table_name  "Privileges for " from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';



continues ....

Oracle interview Question

Oracle Architecture Interview Questions and Answers



What is difference between oracle SID and Oracle service name?
Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.

What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?
Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.

What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

What is SGA? Define structure of shared pool component of SGA?
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three major area:
Library cache (parse SQL statement, cursor information and execution plan),
data dictionary cache (contain cache, user account information, privilege user information, segments and extent information,
data buffer cache for parallel execution message and control structure.


What is the difference between SMON and PMON processes?
SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.

What is a system change number (SCN)?SCN is a value that is incremented whenever a dirty read occurs.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.

What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
 A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.


The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600;  # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.

What happens when we fire SQL statement in Oracle?
First it will check the syntax and semantics in library cache, after that it will create execution plan.
If already data is in buffer cache it will directly return to the client.
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

What is the use of large pool, which case you need to set the large pool?
You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.

What does database do during the mounting process?
While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.

What are logfile states?
“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.

What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
How to check Oracle database version?
SQL> Select * from v$version;

Explain 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

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.

File type
Extension
Default  location (when created with OMF)
Pfile :
ORA
C:\oracle\product\10.2.0\admin\orcl\pfile
Spfile:
ORA
C:\oracle\product\10.2.0\db_1\database
Control file:
CTL
C:\oracle\product\10.2.0\oradata\orcl
Redo log file:
LOG
C:\oracle\product\10.2.0\oradata\orcl
Archive log file:
LOG
C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG
Data file:
DBF
C:\oracle\product\10.2.0\oradata\orcl
Alert log files:
LOG
C:\oracle\product\10.2.0\admin\orcl\adump
Trace log files:
TRC
C:\oracle\product\10.2.0\admin\orcl\udump|bdump|cdump
Password file:
ORA
C:\oracle\product\10.2.0\db_1\database


Oracle Database 11g 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

What is the 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.
What is 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
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=”{}”

What is RESULT Cache?

11G Backgroung 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

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 backgroud processes should be monitored and analyzed from the trace files generated and the alert log.

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?

What is a Baseline?

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

Hard Parse Vs. Soft Parse?

What is semaphores, semaphores?

What is latch?

What is Enqueue?

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.
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

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.

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 Database 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;

What is cloud computing

cloud computing?

What is the cloud? Where is the cloud? Are we in the cloud now? These are all questions you’ve probably heard or even asked yourself. The term “cloud computing” is everywhere.

In the simplest terms, cloud computing means storing and accessing data and programs over the Internet instead of your computer’s hard drive. The cloud is just a metaphor for the Internet. It goes back to the days of flowcharts and presentations that would represent the gigantic server-farm infrastructure of the Internet as nothing but a puffy, white cumulus cloud, accepting connections and doling out information as it floats.

What cloud computing is not about is your hard drive. When you store data on or run programs from the hard drive, that’s called local storage and computing. Everything you need is physically close to you, which means accessing your data is fast and easy, for that one computer, or others on the local network. Working off your hard drive is how the computer industry functioned for decades; some would argue it’s still superior to cloud computing, for reasons I’ll explain shortly.

The cloud is also not about having a dedicated network attached storage (NAS) hardware or server in residence. Storing data on a home or office network does not count as utilizing the cloud. (However, some NAS will let you remotely access things over the Internet, and there’s at least one brand from Western Digital named “My Cloud,”just to keep things confusing.)

For it to be considered “cloud computing,” you need to access your data or your programs over the Internet, or at the very least, have that data synced with other information over the Web. In a big business, you may know all there is to know about what’s on the other side of the connection; as an individual user, you may never have any idea what kind of massive data processing is happening on the other end. The end result is the same: with an online connection, cloud computing can be done anywhere, anytime.

What cloud computing really means

The cloud computing trend sounds nebulous, but it’s not so fuzzy when you view the value proposition from the perspective of IT professionals.

Cloud computing is all the rage. “It’s become the phrase du jour,” says Gartner senior analyst Ben Pring, echoing many of his peers. The problem is that (as with Web 2.0) everyone seems to have a different definition.

As a metaphor for the Internet, “the cloud” is a familiar cliché, but when combined with “computing,” the meaning gets bigger and fuzzier. Some analysts and vendors define cloud computing narrowly as an updated version of utility computing: basically virtual servers available over the Internet. Others go very broad, arguing anything you consume outside the firewall is “in the cloud,” including conventional outsourcing.

Cloud computing comes into focus only when you think about what IT always needs: a way to increase capacity or add capabilities on the fly without investing in new infrastructure, training new personnel, or licensing new software. Cloud computing encompasses any subscription-based or pay-per-use service that, in real time over the Internet, extends IT’s existing capabilities.

Cloud computing is at an early stage, with a motley crew of providers large and small delivering a slew of cloud-based services, from full-blown applications to storage services to spam filtering. Yes, utility-style infrastructure providers are part of the mix, but so are SaaS (software as a service) providers such as Salesforce.com. Today, for the most part, IT must plug into cloud-based services individually, but cloud computing aggregators and integrators are already emerging.

How Cloud Computing Works

Let’s say you’re an executive at a large corporation. Your particular responsibilities include making sure that all of your employees have the right hardware and software they need to do their jobs. Buying computers for everyone isn’t enough — you also have to purchase software or software licenses to give employees the tools they require. Whenever you have a new hire, you have to buy more software or make sure your current software license allows another user. It’s so stressful that you find it difficult to go to sleep on your huge pile of money every night.

Soon, there may be an alternative for executives like you. Instead of installing a suite of software for each computer, you’d only have to load one application. That application would allow workers to log into a Web-based service which hosts all the programs the user would need for his or her job. Remote machines owned by another company would run everything from e-mail to word processing to complex data analysis programs. It’s called cloud computing, and it could change the entire computer industry.

In a cloud computing system, there’s a significant workload shift. Local computers no longer have to do all the heavy lifting when it comes to running applications. The network of computers that make up the cloud handles them instead. Hardware and software demands on the user’s side decrease. The only thing the user’s computer needs to be able to run is the cloud computing system’s interface software, which can be as simple as a Web browser, and the cloud’s network takes care of the rest.

There’s a good chance you’ve already used some form of cloud computing. If you have an e-mail account with a Web-based e-mail service like Hotmail, Yahoo! Mail or Gmail, then you’ve had some experience with cloud computing. Instead of running an e-mail program on your computer, you log in to a Web e-mail account remotely. The software and storage for your account doesn’t exist on your computer — it’s on the service’s computer cloud.

Software as a service (SaaS)

Cloud-based applications—or software as a service—run on distant computers “in the cloud” that are owned and operated by others and that connect to users’ computers via the Internet and, usually, a web browser.

The benefits of SaaS

• You can sign up and rapidly start using innovative business apps
• Apps and data are accessible from any connected computer
• No data is lost if your computer breaks, as data is in the cloud
• The service is able to dynamically scale to usage needs

Platform as a service (PaaS)

Platform as a service provides a cloud-based environment with everything required to support the complete lifecycle of building and delivering web-based (cloud) applications—without the cost and complexity of buying and managing the underlying hardware, software, provisioning, and hosting.

The benefits of PaaS

• Develop applications and get to market faster
• Deploy new web applications to the cloud in minutes
• Reduce complexity with middleware as a service

Infrastructure as a service (IaaS)

Infrastructure as a service provides companies with computing resources including servers, networking, storage, and data center space on a pay-per-use basis.

The benefits of IaaS

• No need to invest in your own hardware
• Infrastructure scales on demand to support dynamic workloads
• Flexible, innovative services available on demand

Public cloud

Public clouds are owned and operated by companies that offer rapid access over a public network to affordable computing resources. With public cloud services, users don’t need to purchase hardware, software, or supporting infrastructure, which is owned and managed by providers.

Key aspects of public cloud

• Innovative SaaS business apps for applications ranging from customer resource management (CRM) to transaction management and data analytics
• Flexible, scalable IaaS for storage and compute services on a moment’s notice
• Powerful PaaS for cloud-based application development and deployment environments

Private cloud

A private cloud is infrastructure operated solely for a single organization, whether managed internally or by a third party, and hosted either internally or externally. Private clouds can take advantage of cloud’s efficiencies, while providing more control of resources and steering clear of multi-tenancy.

Key aspects of private cloud

• A self-service interface controls services, allowing IT staff to quickly provision, allocate, and deliver on-demand IT resources
• Highly automated management of resource pools for everything from compute capability to storage, analytics, and middleware
• Sophisticated security and governance designed for a company’s specific requirements

Hybrid cloud

A hybrid cloud uses a private cloud foundation combined with the strategic integration and use of public cloud services. The reality is a private cloud can’t exist in isolation from the rest of a company’s IT resources and the public cloud. Most companies with private clouds will evolve to manage workloads across data centers, private clouds, and public clouds—thereby creating hybrid clouds.

Key aspects of hybrid cloud

• Allows companies to keep the critical applications and sensitive data in a traditional data center environment or private cloud
• Enables taking advantage of public cloud resources like SaaS, for the latest applications, and IaaS, for elastic virtual resources
• Facilitates portability of data, apps and services and more choices for deployment models

Archive Generation is more in oracle

chceck tbs in begin backup mode:
================================

select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name;


Query to find the session that is generating more Archives
This Query is to find the session that is generating more Archives.
====================================================================

col program for a10
col username for a10
select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;





Check which session is generating more redo.
============================================
set pages 1000
set lines 140
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


The o/p of the query gives the sid , Number of undo blocks used and Number of undo records used that were happening during that time.
From the sid we can get the request id and the request name.



select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;