Ulfat`s DataBase Blog

Sometimes we get backup and did not use TAG or format clause via RMAN. However RMAN will give unique name for backup. After some period we need to clarify backup name, which of them controlfile name, which of them backup datafile 1 etc. For clarify Oracle RMAN backups, you need to use : list backup by file; Let`s to look at example take backup of current controlfile and spfile RMAN> backup current controlfile spfile; take backup of 1 data file of database, SYSTEM01.dbf RMAN> backup datafile 1; take backup of whole database and archivelog RMAN> backup database plus archivelog; [oracle@localhost 2010_08_12]$ ls -lh total 1.3G -rw-r----- 1 oracle oinstall 283M Aug 12 11:50 o1_mf_annnn_TAG20100812T115003_6676dx1p_.bkp -rw-r----- 1 oracle oinstall 17K Aug 12 11:51 o1_mf_annnn_TAG20100812T115147_6676j4h5_.bkp -rw-r----- 1 oracle oinstall 6.8M Aug 12 11:38 o1_mf_ncnnf_TAG20100812T113851_6675qw1y_.bkp -rw-r----- 1 oracle oinstall 6.9M Au...

Sometimes we have had request that grant to new or existing user privileges which already another user has. Example User2 is new database user and we have to grant him privilege(s) User1 table(s). To do it, we can use some methods, below is optimal solution. This PLSQL block can use DBA, USER1 or user which has USER1 tables WITH ADMIN OPTION rights. begin for cTab in (select * from dba_tables where owner='USER1') loop execute immediate 'grant select on '||cTab.owner||'.'||cTab.table_name || ' to ' || 'USER2'; dbms_output.put_line(cTab.owner||'.'||cTab.table_name || ' granted to '||'USER2'); end loop; end;

Today I will demonstrate how we can made changes on existing table column type. Describe that we have a table and column varchar2 and data on that column is not null. So, what should we do? We should add column on table with CLOB type. Please look at below example. Our table name is TEST_TBL and structure of table is: create table ulfet.test_tbl ( log_id number, log_date date, log_text varchar2(4000) ); We want change log_text column type VARCHAR2 to CLOB type. Add new column to TEST_TBL table. SQL> alter table test_tbl add log_text2 clob; Then update value of log_text to log_text2. update TEST_TBL set log_text2 = log_text; Drop log_text column. SQL> alter table test_tbl drop log_text; Rename log_text2 to log_text. SQL> alter table test_tbl rename column log_text2 to log_text; That is all :)

One of the DBA responsibility is checking listener log file size. If your Oracle is running 7 x 24 you can`t rename online log file as alert.log. It means that if you try "moving" hot listener.log file is useless - ORACLE will still write into the moved file. I faced with problem, my listener.log file was to big size. After reading some blog and searching OTN forum I found guide and now I am going to demonstrate it : [oracle@localhost log]$ ls -lh total 298M -rw-r--r-- 1 oracle oinstall 298M Jul  6 15:59 listener.log -rw-r--r-- 1 oracle oinstall  682 Jul  6 11:51 sqlnet.log [oracle@localhost admin]$ lsnrctl LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-JUL-2011 16:05:36 Copyright (c) 1991, 2005, Oracle.  All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> set current_listener listener Current Listener is listener LSNRCTL> show log_file Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhos...

Most essential commands for DBAs: #Find files find . -print |grep -i listener.ora #Kill similar processes ps -ef | grep mon | grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {} #Change owner of file, directory, sub-directory chown user:group file(s) or folder #Check particularly process count ps -ef | grep runner | wc -l #Display total file space in directory du -ks du -ks /home/oracle #Change permission chmod 777 runInstaller --give read, write and execute to user/group/all #Compress file compress file <file name> - compress file gzip <file name>    -    creates *.gz file gunzip <file name>  -    decompresses *.gz file #Replacing command - !! [runner@runner ~]$ df -k Filesystem           1K-blocks      Used Available Use% Mounted on /dev/sda1              20307...