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