Ulfat`s DataBase Blog

One day I get request from my friend such as his database crashed and after restart he could not start it up. His database version is Oracle 11g R2. I tried to start up database but it raised : SQL> startup ORA-00845: MEMORY_TARGET not supported on this system SQL> exit Hmmmmm, that`s new for me, never faced before. Checked disk space [root@localhost ~]# df -h Filesystem            Size  Used Avail Use% Mounted on /dev/cciss/c0d0p3     209G   27G  171G  14% / /dev/cciss/c0d1p1     903G  265G  592G  31% /oradata /dev/cciss/c0d0p1      99M   24M   71M  26% /boot tmpfs                 3.9G     0  3.9G   0% /dev/shm [root@localhost ~]# Increased tmpfs size. To do that open fstab file via any editable program (gedit, vi etc) [root@localhost ~]# vi /etc/fstab ... tmpfs      /...

Today I will demonstrate Recovery Manager features, especially encryption and some other techniques. First of all let me note about RMAN backup encryption. Staring from Oracle 10g RMAN now creates encrypted backups that cannot be restored by unauthorized people. There are 3 modes of backup encryption: * Transparent encryption * Password encryption * Dual-mode encryption using either transparent or password encryption All RMAN backups are not encrypted but you can encrypt any RMAN backup in the form of a backup set. In this tutorial I will show you how to configure Password encryption. Let`s finish talking and start to demonstrate. check parameters with "SHOW ALL" command, by default encryption is OFF RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTR...

Today one of the our developer asked me that, one query works very slowly. After investigation I have found out plan of the query is incorrect and query use substr function which incorrect place. While using select I`ve received ORA-01502. What is the this error? ORA-01502: index 'string.string' or partition of such index is in unusable state Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition     I am going to check my partition indexes which is unusable state. SQL> select * from DBA_IND_PARTITIONS where index_name = 'MY_ARCH_TEST_DB_IDX2' and status <> 'USABLE' Ups! Found. P2008_201010 partition is unusable state. Checked how many rows has in this partition: SQL> select count(1) from my_arch_test partition (P2008_201010) 126339601 rows...

As OS administrator, DBA should be check filesystem, clean up unnecessary and old trace files. Below command will delete files older than 7 days. find *.trc -mtime +7 -type f -local -exec rm  {} \;

Sometimes someone creates/drop/modify db object(s). However it depends requester. To prevent this dislike events I suggest create DDL trigger. This trigger will fire every DDL action. First of all we should determine which kind of DDL we have to store. So, create table: CREATE TABLE SYS.DDL_WATCH_LOG (   ACTION             CHAR(6 BYTE),   ORA_EVENT       VARCHAR2(20 BYTE),   ORACLE_LOGIN_USER      VARCHAR2(30 BYTE),   ORA_DATABASE_NAME   VARCHAR2(50 BYTE),   ORA_OBJECT_TYPE   VARCHAR2(20 BYTE),   ORA_OBJECT_NAME   VARCHAR2(30 BYTE),   ORA_OBJECT_OWNER  VARCHAR2(30 BYTE),   ORA_SESSIONUSER        VARCHAR2(30 BYTE),   ORA_HOSTNAME           VARCHAR2(60 BYTE),   ORA_IPADDRESS   ...

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;