Ameer Khan

Step 1. create the shell script for checking dbstatus vi /home/oracle/dbcheck.sh #!/bin/bashhostname=server1.soumya.comoracle_sid=primexport instance_name=ora_pmon_primstatus=`ps -ef |grep pmon | head -1 | awk ‘{print $8}’`if [ “$status” == “$instance_name” ]; thenecho “DB IS UP”elseecho “DB down”mail -s “Attention!! $oracle_sid Database is Down at $hostname!! ” yourmailid@gmail.comfiexit 0 :wq Step 2. Schedule it for checkup in every 5… Continue Reading

SQL>select grantee, granted_role, admin_optionfrom   sys.dba_role_privswhere  granted_role in (‘DBA’, ‘AQ_ADMINISTRATOR_ROLE’,                       ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,                       ‘OEM_MONITOR’)  and  grantee not in (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’,                       ‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,                       ‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’,                       ‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’,                       ‘TIMESERIES_DBA’); GRANTEE                        GRANTED_ROLE                                                     ADM —————————— ——————————                                              —IX                                    … Continue Reading

Linux version :Rhel 6.3Mongodb version: 2.6 Components of a sharded cluster:-Every sharded cluster has three main components:Shards: This are the actual places where the data is stored. Each of the shards can be amongod instance or a replica set. Config Servers: The config server has the metadata about the cluster. It is in charge ofkeeping… Continue Reading

SQL> column file# format a10SQL> column TO_CHAR format a60SQL> column FILE_NAME format a30SQL> set linesize 150SQL> SELECT TO_CHAR(SYSDATE,’DD/MM/YYYY HH24:MI:SS’) FROM dual; TO_CHAR(SYSDATE,’DD ——————- 05/02/2016 09:22:48SQL> SELECT FILE# file_nr, TO_CHAR(CHECKPOINT_TIME,’DD/MM/YYYY HH24:MI:SS’) checkpoint_time, NAME file_name FROM v$datafile_header;    FILE_NR CHECKPOINT_TIME     FILE_NAME———- ——————- ————————————————————         1 05/02/2016 09:22:19 /u01/app/oracle/oradata/prim/system01.dbf         2 05/02/2016 09:22:19 /u01/app/oracle/oradata/prim/sysaux01.dbf … Continue Reading

To get current scn:- SQL> set time on10:12:32 SQL> col scn for 999999999999999910:12:36 SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual; GET_SYSTEM_CHANGE_NUMBER————————                 1208594 10:12:38 SQL> / GET_SYSTEM_CHANGE_NUMBER————————                 1208595 Now to find out scn for a specific time:- SQL> select timestamp_to_scn(to_date(’02/02/2015 10:12:38′,’mm/dd/yyyyhh24:mi:ss’)) from dual;TIMESTAMP_TO_SCN(TO_DATE(’02/02/201510:12:38′,’MM/DD/YYYYHH24:MI:SS’))———————————————————————-   … Continue Reading

When you start doing DML operations such as deletes and updates constantly in a table,you may end up having a fragmented table in your database.As we know the high water mark oftable actually defines the border line between used and unused space for tables. While performingfull table scan, oracle will always read the data up… Continue Reading

SGA_% parameters are enabled when AMM(Automatic memory management) is disable. AMM enabled = SGA and PGA sizes are automatically tuned by oracle. We just set Memory_MAX_TARGET and MEMORY_TARGET params and oracle give enough memory to the SGA and PGA whatever they need. For ex: afternoon SGA has no empty memory space but PGA has a… Continue Reading

vi /backups/postgre_backup.sh #!/bin/bashdate1=`date +%d%m%Y_%H%M%S`export path1=/backups/postgre_backupOUTPUTDIR=”$path1/$date1″/usr/bin/find /backups/postgre_backup/* -type d -mtime +32 -exec rm -r {} \; 2> /dev/nullsu postgres -c ‘pg_dumpall’ > “$OUTPUTDIR.sql”/bin/tar -zcvf /backups/postgre_backup/backup_$date1.tar.gz $OUTPUTDIR.sqlrm $OUTPUTDIR.sqlexit :wq Schedule in crontab:-# crontab -e 0 0 * * * /backups/postgre_backup.sh > /dev/null Continue Reading

From oracle 11g password can be case sensitive by settingSEC_CASE_SENSITIVE_LOGON = true in  initialization parameter file. SQL> show parameter SEC_CASE_SENSITIVE_LOGON NAME                                 TYPE        VALUE———————————— ———– ——————————sec_case_sensitive_logon             boolean     TRUE Lets… Continue Reading

Before using the parameter we must know about the parameter:- sample example:-expdp username/password directory=dump_dir filesize=1G dumpfile=full%U.dmp logfile=fulllog.log parallel=2 exclude=statistics impdp username/password directory=dump_dir dumpfile=full%U.dmp logfile=full.log parallel=2 To get your server’s no of cpu core:- #lscpuroot@server1 dpdump]# lscpuArchitecture:          x86_64CPU op-mode(s):        32-bit, 64-bitByte Order:            Little EndianCPU(s):                1On-line CPU(s) list:   0Thread(s) per core:    1Core(s) per socket:    1Socket(s):             1NUMA node(s):         … Continue Reading