DBA Day to Day commands

FRa size  db
______

set lines 100
col name format a60
select     name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used  / 1024 / 1024) "Used MB"
from       v$recovery_file_dest
order by name
/


NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
+FLASH01                                                         102400     102337

ALTER SYSTEM SET db_recovery_file_dest_size = 105g SCOPE = BOTH;


alter system set db_recovery_file_dest=

alter system set DB_RECOVERY_FILE_DEST='+FLASH01' SID='*';

ALTER SYSTEM SET db_recovery_file_dest_size = 53g; SCOPE = BOTH;





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';
10:33:13 AM: Lakshmidevi V: this is to check all the privs for a user




create user localbuduser default profile identified by loc@lbudus3r default tablespace LOCALBUD temporary tablespace TPS00001 QUOTA UNLIMITED ON LOCALBUD;

create user N507052 identified by N507052 default tablespace USERS temporary tablespace TPS00001;




http://dev.fyicenter.com/faq/oracle/oracle_basic_concept.php

STEP BY STEP RMAN RECOVERY CATALOG CREATION AND CONFIGURATION. (WINDOWS)

 RMAN Recovery Catalog Creation and Configuration. (Windows)

The RMAN Recovery Catalog is a database schema that holds the metadata detailing the RMAN backup operations performed on the target database. The metadata include the following information from about the target database: database structure, RMAN configuration, data file and archive log backups (backup sets, pieces and copies), archived redo logs and their copies. In addition to the metadata the recovery catalog can also store scripts that can be used by all registered target databases.

This document will detail the steps to create an RMAN recovery catalog using Oracle Database 11gR2 on Windows OS. The process is the same for other versions of UNIX, Solaris and AIX.

It is recommended that the recovery catalog be in a database separate from any of the target databases. Many organizations store the recovery catalog in the same database as their Enterprise Manager Grid Control repository. It is also recommended that the database that houses the recovery catalog to be in ARCHIVELOG mode. In this demo I am using catalog database with the name of CATDB.

First create a table space to house the recovery catalog schema.

C:\Users\rajjha>set oracle_sid=catdb

C:\Users\rajjha>sqlplus

SQL*Plus: Release 11.2.0.1.0

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace rec_catalog
     datafile ‘D:\app\rajjha\oradata\CATDB\rec_catalog.dbf’
     size 10M autoextend on
     extent management local uniform size 1M;

Next we create the recovery catalog owner. The user SYS cannot be the owner of the recovery catalog.

SQL> create user rcat identified by password
    default tablespace rec_catalog
    quota unlimited on rec_catalog;

User created.

SQL> grant recovery_catalog_owner to rcat;

Grant succeeded.

The role RECOVERY_CATALOG_OWNER has all of the privileges need to query and maintain the recovery catalog.

SQL> select privilege from dba_sys_privs where grantee = ‘RECOVERY_CATALOG_OWNER’;

PRIVILEGE
—————————————-
CREATE SYNONYM
CREATE CLUSTER
ALTER SESSION
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE SESSION
CREATE TYPE
CREATE VIEW
CREATE TRIGGER

11 rows selected.

SQL>

The actual creation of the recovery catalog is performed in RMAN while connected to the database in which the catalog is to be created. The command CREATE CATALOG will create the recovery catalog in the default table space of the user. If you need to store the catalog in a table space other than the users default table space you can provide the table space name using TABLESPACE.

C:\Users\rajjha>rman target / catalog rcat@CATDB

Recovery Manager: Release 11.2.0.1.0 –

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CATDB (DBID=2365959513)
recovery catalog database Password:
connected to recovery catalog database

RMAN> create catalog tablespace rec_catalog;

recovery catalog created

To the new database to be backed up, you have to register it on the rman catalog you just created: (Fon any new database you can use this catalog after register database in catalog)

RMAN>  register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Configure the persistent parameters.

RMAN> configure retention policy to recovery window of 2 days;

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>
RMAN> configure channel device type disk format ‘D:\oraclebackup\Backup%d_DB_%U_%S_%P’;

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘D:\oraclebackup\Backup%d_DB_%U_%S_%P’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Take database full backup. The full database backup should be taken first time. Afterwards you can use Incremental, Full or archivelog backup depend on your backup policy as well as space available.

C:\Users\rajjha>rman target / catalog rcat/password@CATDB
RMAN> run{
backup database plus archivelog;
delete noprompt obsolete;
}

RMAN> exit
Now the RMAN setup is completed successfully. Here is the info about RMAN.
Primary DB = MYDB
Catalog DB = CATDB
RMAN Backup location = D:\oraclebackup.
Now take the full backup. Every day run the below script that backup the new archive log files. You may run through scheduler

C:\Users\rajjha>rman target / catalog rcat@CATDB
RMAN> run{
delete noprompt obsolete;
backup archivelog all;
}

RMAN> exit

Asm and rdbms interation opening datafile

ASM & RDBMS Interactions: Opening a Datafile

Once your RDBMS instance has found the ASM instance it is relying on, what happens when the RDBMS needs to actually open a file that is held on ASM storage?

It seems fairly straightforward. The RDBMS maintains a series of connections to the ASM instance via the Onnn background processes. Requests to open a file go via these background processes to the ASM instance. The ASM instance then sends the extent map to the RDBMS instance.

The RDBMS can then use the extent map to perform I/O direct to the file. It is important to bare in mind that the I/O requests from the RDBMS do not go via the ASM instance, but straight to the devices by using the extent map.

The ASM instance only looks after the metadata that describes diskgroups and extent allocations. It would not be efficient for the RDBMS to have to send it’s I/O via the ASM instance and this does not happen.

ASM & RDBMS Interactions: Creating a file

How do new files get created when running with ASM?

It is the responsibility of the RDBMS instance to begin the procedure for creating the new file:

The RDBMS instance sends a request to the ASM instance to create the file via the Onnn background processes. This request contains information on the size of the file to create, the type, and the diskgroup name where to create the file in.

The ASM instance then uses this information to allocate the appropriate number of extents on the correct disks. After allocating the extents for the file ASM sends the extent map information back to the RDBMS instance and creates an entry in its Continuing Operations Directory (COD) metadata file.

Once the RDBMS has the extent map it can issue the I/O to initialise the file. Remember I/O to datafiles never goes through the ASM instance it is always from the RDBMS instance direct to the file in question. The ASM instance just takes care of the metadata only.

After this I/O has completed, the RDBMS instance messages the ASM instance that the creation of the file has completed successfully. The ASM instance then updates it’s metadata files to reflect the change.

Should the RDBMS decide to abort the file creation, then ASM can use the COD to rollback the creation of the file, which includes freeing the space that was allocated for the new file.


Thanks

Ref : https://jarneil.wordpress.com/2014/10/07/12c-upgrade-and-concurrent-stats-gathering/

ASM and Rdbms interation

ASM & RDBMS Interactions: Finding the ASM Instance

I thought it would be worthwhile having a couple of blog posts on how the RDBMS instance interacts with the ASM instance in various situations.

So I guess we should start at the beginning and ask how the RDBMS actually knows where to contact the ASM instance that it is storing its files in at all?

When an ASM instance is started it registers with the Cluster Synchronisation Services (CSS) daemon running as occsd.bin. This is part of Oracle Clusterware, but the occsd.bin runs in a single instance configuration as well.

Whenever an RDBMS instance needs for the first time to access files that reside on ASM storage and the RDBMS has not needed prior access to the ASM instance, the RDBMS needs to find the connection details for the ASM instance.

These connection details are NOT stored anywhere and you do not need to configure them. The RDBMS instance contacts CSS and gets passed the connect string for the ASM instance. The RDBMS then connects as sysdba to the ASM instance using OS authentication.

It’s actually the RDBMS’s ASMB background process that connects to the ASM instance and remains connected while there are ASM files open. Termination of this ASMB background process is fatal for the instance.

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.