SQL Server Password Policy

SQL Server supports two authentication modes, Windows authentication mode Mixed mode. Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials. Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server. The windows password policy check against password minimum length, password history (password can’t be used if … Continue reading SQL Server Password Policy

~First week at a new Environment~

    As a DBA, your first week at a new environment can be testing to say the least. Worst case scenario is that you arrive in an environment where there is no SOP (Standard Operating Procedure), best practice is unheard of, the databases are running themselves (maybe in fantasy land), and they have no idea why you were hired.  If all the mentioned are true, then all I can say is RUN! We are DBAs, not Magicians, although that depends on whom you ask. The first couple weeks are challenging if you are not prepared, but if you come … Continue reading ~First week at a new Environment~

How to Move schema data from USERS tablespace to their own tablespace

#TASK – – Move schema (ABC) data from USERS tablespace to their own tablespace It is very common for objects to be created in wrong tablespaces especially the USERS tablespace. Part of the DBA’s task is to move these objects to the right tablespace. First. you want to note all objects belonging to the schema. –Create new tablespaces to host schema data CREATE SMALLFILE TABLESPACE XXXX_ts DATAFILE ‘XXXX_01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 512M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; — Add enough datafiles on the tablespace (This depends on the size of the data to … Continue reading How to Move schema data from USERS tablespace to their own tablespace


set pagesize 200 set linesize 200 col username format a30 col osuser format a20 –Archive log dest. %used col name for a45 SELECT NAME,TO_CHAR(SPACE_LIMIT, ‘999,999,999,999’) AS SPACE_LIMIT, TO_CHAR(SPACE_LIMIT – SPACE_USED + SPACE_RECLAIMABLE, ‘999,999,999,999’) AS SPACE_AVAILABLE, ROUND((SPACE_USED – SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST; –To check file consuming the most space du -sh * du -h /u01/app/oracle | grep ‘[0-9\.]\+G’ –To get difference between 2 files diff ./initinvprd2.ora /u01/app/oracle/ANTHONY/initinvprd2.ora or diff ./initPROD.ora initTEST.ora –To check cpu usage on OS mpstat 2 100 — Schema_names, tables & Size MB set pagesize 200 set linesize 200 col username format a30 col … Continue reading QUICK & EASY TO USE SCRIPTS


Before scheduling a job on crontab, the DBA must have created and tested the script for a successful execution. You can view the current cron setting using the below command; cat /etc/crontab * * * * * Command_to_execute | | | | | | | | | +?? Day of week (0?6) (Sunday=0) or Sun, Mon, Tue,… | | | +???- Month (1?12) or Jan, Feb,… | | +????-? Day of month (1?31) | +??????? Hour (0?23) +????????- Minute (0?59) Below are some examples of cron jobs; 0 6,18 * * /home/oracle/dba/scripts/rm_listener.sh >> /home/oracle/rmlistener.log 0 1 * * * /home/oracle/dba/scripts/expdp_orcl.sh … Continue reading CRONTAB FOR SCHEDULING JOBS

SQL Server Database Instant File Initialization

After building new SQL server one of the most important post configuration required to setup for increasing database performances  is Instant File Initialization (IFI)( you can also change it on existing server). When SQL server require to perform some specific operations like; Create a new database. Add new file to an existing database. Increase the size of an existing files including Auto growth Restore a database For example, when you perform any of the above tasks,  the default setting is for sql server to reserve space using zeros ‘0’, but under IFI, the space is allocated without  the initial zeros … Continue reading SQL Server Database Instant File Initialization