Some useful Linux Command for DBA

ls: List files
cp: Copy files
mv: Move and rename files
mkdir: Make a directory
alias: Define command macros
rm: Remove files and directories
more: Page through output
head: Show beginning of file contents
tail: Show end of file contents
df: Display filesystem space usage
du: Display directory disk space usage
cat: Show and concatenate files
grep: Search for patterns in files
chmod: Change permissions of files
chown: Change owner of files
zip: Compress and package files together
gedit: A WYSIWYG text editor
export: Make environment settings global
ps: List running processes
touch: Change file time stamps
id: Show information about the current user
sudo: Execute commands as another user

Standard Measurement Tools
• Top Resource Consumers: top
• System Activity Reporter: sar
• Virtual Memory Statistics: vmstat
• I/O Statistics: iostat
• System Log files: /var/log/messages
Linux Tools
• X-based tools: xosview
• The /proc virtual file system
• Free and used memory: free
Tools for monitoring and tuning CPU include:
• top
• pstree and free
• vmstat
• Syntax: vmstat
• Example : # vmstat 2 5
• mpstat –p All
• sar –u
• Syntax: #sar -B
#sar -R
• Example : #sar -B 2 3
#sar -R 2 3
• xosview
• xload
• System Monitor
Measuring Total Memory
• top
• free
• cat /proc/meminfo
Monitoring and Tuning I/O

• /proc file system
• sar -d
• I/O statistics by device [iostat –d]
Syntax : iostat -d
Eample : #iostat -d 2 2
• I/O activity by partition
iostat –d -p
• vmstat
• xosview

Oracle & Iron Man – Great Combination

http://www.oracle.com/us/ironman2/index.html

How to use trigger to check database constant

We can use trigger to check database constant and customize error message. For example we have two tables name Department and Employee. I have written two trigger on Update and Delete on Department table, which will responsible to check referential integrity constraint and customize the error message.

Creating Tables Department and Employee

CREATE TABLE DEPARTMENT

  (

    Department_Id          Number(*,0) Not Null Enable,

    Department_Name        Varchar2(45 Byte) Not Null Enable,

    Department_Description Varchar2(500 Byte),

    Primary Key (Department_Id) 

  );

CREATE TABLE EMPLOYEE

  (

    Employee_Name        Varchar2(45 Byte) Not Null Enable,

    Employee_Ssn         Varchar2(45 Byte) Not Null Enable,

    Employee_Phone       Varchar2(45 Byte) Not Null Enable,

    Employee_Cellular    Varchar2(45 Byte) Not Null Enable,

    Employee_Description Varchar2(500 Byte),

    Department_Id        Number(*,0) Not Null Enable,

    Primary Key (Employee_Ssn) ,

    Foreign Key (Department_Id) References Department (Department_Id) Enable

  );

Insert some data on Department and Employee Table

Insert Into Department (Department_Id, Department_Name, Department_Description) 

Values ('1001', 'Tecnical', 'Tecnical Department');

Insert Into Department (Department_Id, Department_Name, Department_Description) 

Values ('1002', 'Merketing', 'Merketing Department');

Insert Into EMPLOYEE (Employee_Name, Employee_Ssn, Employee_Phone, Employee_Cellular, Department_Id) 

Values ('Tamim', '100001', '880175307713', '880175307713', '1001');

Trigger on Delete of Department

Create Or Replace Trigger Td_Department 

  After Delete on Department 

  FOR EACH row

    DECLARE numrows INTEGER;

  BEGIN

    SELECT COUNT(*) INTO numrows

    FROM Employee

    WHERE  Employee.Department_ID = :old.Department_ID;

    IF (numrows> 0) THEN

      raise_application_error( -20001, 'Cannot DELETE Department because Employee exists.' );

    END IF;

  END;

  /

Test a Delete SQL Statement on Department Table

  Delete 

  From Department 

  Where Department_Id = 1001 

  /*

  SQL Error: ORA-20001: Cannot DELETE Department because Employee exists.

  */

Trigger on Update of Department

Create Or Replace Trigger Tu_Department 

  After Update On Department 

  For Each Row

    Declare Numrows Integer;

  Begin

    IF (:old.Department_ID <> :new.Department_ID) THEN

      Select Count(*) Into Numrows    

      FROM Employee

      Where Employee.Department_Id = :Old.Department_Id;

      IF (numrows > 0) THEN

        raise_application_error( -20005, 'Cannot UPDATE Department because Employee exists.' );

      END IF;

    END IF;

  END;

  /

Test a Update SQL Statement on Department Table

  Update Department 

  Set Department_Id = 1003

  Where Department_Id = 1001 

  /*

  SQL Error: ORA-20005: Cannot UPDATE Department because Employee exists.

  */

How To Block Websites Without Using Any Software.

Sometimes we want to restrict access to some particular

websitefrom our PC but we don’t know how to do it without using some software
for it.

Here I m sharing with you a method to do it without using any software......

1. Run -> drivers

2. Open The file ..\drivers\etc\ hosts using note pad

3. Under "127.0.0.1 localhost" Add 127.0.0.2 www.yahoo.com

Code:

127.0.0.1                 localhost

127.0.0.2                 www.yahoo.com

127.0.0.2                 yahoo.com

That site will no longer be accessible.

Even if you ping www.yahoo.com from command prompt it will not response from original source.

N.B: Please restart your browser before you perform the test.

Auto Draft

Auditing Table Data using Trigger

Create New User in Oracle

CREATE User <UserName> IDENTIFIED BY <Passsword>;

Grant User necessary privileges

GRANT CREATE session TO <username>;

GRANT CREATE TABLE TO <username>;

GRANT CREATE TRIGGER TO <username>;

ALTER USER <username> QUOTA UNLIMITED ON <TablespaceName>

Create a Transactional Table

CREATE TABLE AD_DURATIONS
  (
      Ad_Duration_Cn    Varchar2(10 Byte)     Not Null Enable,
      Ad_Start_Date     Date Not              Null Enable,
      Ad_End_Date       Date Not              Null Enable,
      Modified_By       Varchar2(10 Byte),
      Modified_Date     Date                  Default Sysdate,
      Constraint CJ_AD_DURATIONS_PK Primary Key (AD_DURATION_CN)
  );

Insert value in the AD_DURATIONS Table

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10001', To_Date('26-JUL-10', 'DD-MON-RR'),
                              To_Date('30-JUL-10', 'DD-MON-RR'), 'Tamim');

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10002', To_Date('31-JUL-10', 'DD-MON-RR'),
                              To_Date('05-Aug-10', 'DD-MON-RR'), 'Khan');

Create a Log Table for AD_DURATIONS Data

Here Modified_By, Modified_Date and Action is Audit Column.

CREATE TABLE AD_DURATIONS_LOG
  (
      Ad_Duration_Cn    Varchar2(10 Byte),
      Ad_Start_Date     Date,
      Ad_End_Date       Date,
      Modified_By       Varchar2(20 Byte),
      Modified_Date     Timestamp (6),
      Action            Varchar2(20 Byte)
  );

Create a Trigger on AD_DURATIONS table

Create Or Replace TRIGGER AD_DURATIONS_LOG
AFTER DELETE OR UPDATE ON AD_DURATIONS
FOR EACH ROW
Begin
  If Updating Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date,:Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Update');
  Elsif Deleting Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date, :Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Delete');
  End If;
END;

Update on AD_DURATIONS Table

Update Ad_Durations
Set Ad_End_Date = To_Date('31-JUL-10', 'DD-MON-RR')
Where Ad_Duration_Cn = 10001;

Update from AD_DURATIONS Table

Delete From Ad_Durations
Where Ad_Duration_Cn = 10002;

Now Selecting data from AD_DURATIONS_LOG Table.

Select * From Ad_Durations_Log;

CONTROLFILE AUTO BACKUP

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN has following default parameters and its default values:

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 OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFORCL.ORA'; # default

Or

RMAN>  SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

By default CONTROLFILE AUTOBACKUP is OFF. But it is strongly recommended enabling CONTROLFILE AUTOBACKUP ON.

Advantage:

• RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.


• Restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file auto backup.


• Control file auto backup can keep track of add a data file, resize, increase/decrease the size of data files or etc.


• If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up

To perform control file auto backup on issue the following command

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

ORA-19809: limit exceeded for recovery files

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 28 10:25:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
select * from tab
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
 

Shutdown the database first

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Start the database

SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  1250356 bytes
Variable Size             180358092 bytes
Database Buffers          411041792 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

Problem: The value db_recovery_file_dest_size is not enough for generate archive log

          
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest_size           big integer 2G

Solution: Increase the value of db_recovery_file_dest_size following way.

 
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

System altered.

Now open the database.

SQL> alter database open;

Database altered.

Cancel-Based Recovery

A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database). The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.

In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.

A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.

Recovery Scenario	Preferred Recovery Method
Some important table is dropped	Oracle Time-based Recovery based Recovery
Some bad data is committed in a table	Oracle Time-based Recovery based Recovery
Lost archive log results in failure of complete recovery	Oracle Cancel-based Recovery
Backup control file does not know anything about the arhivelogs	Oracle Cancel-based Recovery
All unarchived Redo Logs and datafiles are lost	Oracle Cancel-based Recovery
Recovery is needed up to a specific archived log file	Oracle Cancel-based Recovery
Recovery through Resetlogs when media failure occurs before backup completion.	Oracle Change-based Recovery
A Tablespace is dropped	Recovery with a backup control file

1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

sqlplus '/ AS SYSDBA'

2. Start a new instance and mount the database:

STARTUP MOUNT

3. Begin cancel-based recovery by issuing the following command:

RECOVER DATABASE UNTIL CANCEL

If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

CANCEL

Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:

ALTER DATABASE OPEN RESETLOGS;

For More Detail please visit here

SQL Tuning Advisor in Oracle 10g

In Oracle 10g, the SQL tuning process has been automated by introducing a new manageability feature call Automatic SQL Tuning. This feature is designed to work equally well for OLTP and Data Warehouse workloads. Unlike existing tools, automatic SQL Tuning id preformed in the database server by the Oracle query optimizer itself, running in special mode. When running in this mode, the Oracle query optimizer is referred to us the Automatic Tuning Optimizer.

Create a Table to Test a SQL

Create Table TestEmp
       As Select * from Scott.emp;

Write a SQL that you need to tune.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM TESTEMP
ORDER by SAL;

Execute DBMS_STATS Package for Gather Table Satieties

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(
  3    ownname          => 'TEST', 
  4    tabname          => 'TESTEMP',
  5    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  6    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'
  7    );
  8  END;
  9  /
PL/SQL procedure successfully completed.

How to use dbms_sqltune Package

CREATE OR REPLACE PROCEDURE P_SQL_TUNING (
        p_sql CLOB
  )
AS
  v_task VARCHAR2(30);
BEGIN
  /* Drop the task in case we are re-running... */
  BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'sql_tuning_task');
      EXCEPTION
         WHEN OTHERS THEN -- ORA-13605
            NULL;
  END;
 
  /* Create a SQL Tuning task for our SQL... */
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                   sql_text    => p_sql,
                   time_limit  => 1,
                   scope       => 'COMPREHENSIVE',
                   task_name   => 'sql_tuning_task',
                   description => 'Demo of DBMS_SQLTUNE'
                   );          
  /* Execute the task... */
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task');
  
  /* We want to run this again... */
  ROLLBACK;
END P_SQL_TUNING;

Execute p_Sql_Tuning Procedure

SQL> exec P_SQL_TUNING('SELECT emp.EMPNO, emp.ENAME, emp.JOB,
                        emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO
                        FROM TESTEMP emp,TESTEMP empmgr
                        Where emp.empno=empmgr.mgr
                        ORDER by SAL'
                       );
 
PL/SQL procedure successfully completed.

Tuning Result

SQL> set long 80000
SQL> col recs format a90
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS result
     FROM   DUAL;
 
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_task
Tuning Task Owner                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1
Completion Status                 : COMPLETED
Started at                        : 06/14/2010 12:24:18
Completed at                      : 06/14/2010 12:24:18
Number of Index Findings          : 1
 
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : grfz43h6vdf3d
SQL Text   : SELECT emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE,
             emp.SAL, emp.COMM, emp.DEPTNO FROM TESTEMP emp,TESTEMP empmgr
             Where emp.empno=empmgr.mgr ORDER by SAL
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
 
  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TEST.IDX$$_041C0001 on TEST.TESTEMP('MGR');
 
  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 719480185
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    13 |   533 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |         |    13 |   533 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |         |    13 |   533 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TESTEMP |    13 |    52 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TESTEMP |    14 |   518 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."EMPNO"="EMPMGR"."MGR")
   3 - filter("EMPMGR"."MGR" IS NOT NULL)
 
2- Using New Indices
--------------------
Plan hash value: 2840960280
 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    13 |   533 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |                |    13 |   533 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS      |                |    13 |   533 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TESTEMP        |    14 |   518 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX$$_041C0001 |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."EMPNO"="EMPMGR"."MGR")
       filter("EMPMGR"."MGR" IS NOT NULL)

Clean up

The DBMS_SQLTUNE package provides APIs to remove the objects we created as follows.

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
 
PL/SQL procedure successfully completed.

Note:

The Following error message means user have no permission to user Advisor privilege

ORA-13616: The current user <User Name> has not been granted the ADVISOR privilege.

Solutions:

Grant ADVISOR to <User Name>;