Ameer Khan

If you enable auditing then the auditing information, (stored in table SYS.AUD$) will startgrowing and because is relies on SYSTEM tablespace you might have performance problems in thefuture. Auditing housekeeping must be setup. 1. Create a SYSTEM.AUD$_BU table stored in a different tablespace(AUDIT_DATA) where you willove all you auditing produced. [oracle@server1]$ sqlplus /  as sysdba… Continue Reading

Summary:-Today while taking backup of a schema i faced a problem in expdp and the job was not donesuccessfully.Here is the errorUDE-04031: operation generated ORACLE error 4031ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”select obj#,type#,ctime,mtim…”,”SQLA”,”tmp”)ORA-06512: at “SYS.KUPV$FT_INT”, line 2904ORA-06512: at “SYS.KUPC$QUE_INT”, line 572ORA-25254: time-out in LISTEN while waiting for a messageORA-06512: at… Continue Reading

How to start/stop oracle database from linux command prompt:-[oracle@configsrv1 ~]$vi /etc/oratab# # This file is used by ORACLE utilities.  It is created by root.sh# and updated by the Database Configuration Assistant when creating# a database. # A colon, ‘:’, is used as the field terminator.  A new line terminates# the entry.  Lines beginning with a… Continue Reading

vi /backups/mongodb_backup.sh #!/bin/bashdate1=`date +%d%m%Y_%H%M%S`export path1=/backups/mongodb_backupOUTPUTDIR=”$path1/$date1″/usr/bin/find /backups/mongodb_backup/* -type d -mtime +30 -exec rm -r {} \; 2> /dev/nullmongodump -o $path1/$date1/bin/tar -zcvf  /backups/mongodb_backup/backup_$date1.tar.gz $path1/$date1rm -rf $path1/$date1exit #Schedule in crontab:- crontab -e 0 0 * * * /backups/mongodb_backup.sh > /dev/null Continue Reading

Prior to Oracle 10g, the redo log files generated after opening the database with RESETLOGScould not be used with the backups taken before the RESETLOGS was performed. Therefore, whenever a resetlogs was done, it was important to take an immediate full databasebackup, since all previous backups became invalid. A RESETLOGS needs to be performed when… Continue Reading

SQL> SELECT * FROM (SELECT END.dt, ‘Incremental’ BACKUP, STAR.TIME started, END.TIME END,DECODE(TRUNC(END.TIME – star.TIME), 0, NULL, TRUNC(END.TIME – star.TIME) || ‘ Days’ || ‘ + ‘) ||TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME – star.TIME,1) * 86400), ‘SSSSS’), ‘HH24:MI:SS’) TIMEFROM(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE=’I’GROUP BY TRUNC(start_time)) END,(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE=’I’GROUP BY… Continue Reading

When installing Oracle 11g  you may get the following error : Error in invoking target ‘agent nmb nmo nmhs tclexec’ of makefile ‘/u01/app/oracle/product/11.1.0/db_1/sysman/lib/ins_emagent.mk Solution Within the file detailed in the error edit it and find the line that reads ‘$(MK_EMAGENT_NMECTL)’ change this to ‘$(MK_EMAGENT_NMECTL) -lnnz11’ Note : edit this file while you still have the installer… Continue Reading

[oracle@server1 ~]$ sqlplus /  as sysdbaSQL> SET SQLPROMPT “_USER @ _CONNECT_IDENTIFIER> “SYS @ prim> select instance_name from v$instance;INSTANCE_NAME—————-prim Continue Reading

This script will check the flash recovery area and will shoot a mail if the space is over 80%  filled up. From oracle user: [root@server1 ~]# vi /home/oracle/flashback.sql col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999 col round(space_used/1048576) heading “Space Used (MB)” format 99999 col round((space_used/space_limit)*100) heading ” % used ”  format 99999 col name… Continue Reading

Summary :-As a DBA you may need to log in as another user, for example to test an application afterdoing some workarounds to solve a problem. But you don’t know the password and you don’t wantto wait. You can change the password, as dba, but how to change it back to previous? The right way… Continue Reading