Sql script to generate script to Create Audit table.

Set serveroutput on size unlimited

Declare

         tab varchar2(50);       
         owne varchaR2(50);
          cursor c2 is select owner,table_name,column_name,column_id,data_type,data_length,data_precision,data_scale from dba_tab_columns where table_name='&tab' and owner='&owne' order by column_id;
          
          v_id number;
      Begin
          select max(column_id) into v_id from dba_tab_columns where table_name='&tab';
          for c2_rec in c2 loop
              if c2_rec.column_id=1 and c2_rec.data_type in ('VARCHAR2','CHAR') then
                   dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                    dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_precision IS NULL then
                          dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                          dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||',');
                          dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||',');     
                          elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_scale IS NULL then
                                dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                                dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
                                dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');     
                                elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_precision is not null then
                                      dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                                      dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
                                      dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');     
                                      elsif c2_rec.column_id=1 and c2_rec.data_type='DATE' then
                                            dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
                                            dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||',');
                                            dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||',');                               
                   elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type in ('VARCHAR2','CHAR')then
                          dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                          dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                          elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_precision IS NULL then
                                dbms_output.put_line('OLD_'||c2_rec.column_name||'                '||c2_rec.data_type||',');
                                dbms_output.put_line('NEW_'||c2_rec.column_name||'                '||c2_rec.data_type||',');     
                                elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_scale IS NULL then
                                      dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
                                      dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');     
                                      elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_precision is not null then
                                            dbms_output.put_line('OLD_'||c2_rec.column_name||'                '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
                                            dbms_output.put_line('NEW_'||c2_rec.column_name||'                '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');     
                                            elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='DATE' then
                                                  dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||',');
                                                  dbms_output.put_line('NEW_'||c2_rec.column_name||'                 '||c2_rec.data_type||',');     
              elsif c2_rec.column_id=v_id and c2_rec.data_type in ('VARCHAR2','CHAR')then
                    dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    dbms_output.put_line('NEW_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_length||'),');
                    elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_precision IS NULL then
                          dbms_output.put_line('OLD_'||c2_rec.column_name||'                '||c2_rec.data_type||',');
                          dbms_output.put_line('NEW_'||c2_rec.column_name||'                '||c2_rec.data_type||',');     
                          elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_scale IS NULL then
                                dbms_output.put_line('OLD_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
                                dbms_output.put_line('NEW_'||c2_rec.column_name||'               '||c2_rec.data_type||'('||c2_rec.data_precision||'),');     
                                elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND  c2_rec.data_precision is not null then
                                      dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
                                      dbms_output.put_line('NEW_'||c2_rec.column_name||'                 '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');     
                                      elsif c2_rec.column_id=1 and c2_rec.data_type='DATE' then
                                            dbms_output.put_line('OLD_'||c2_rec.column_name||'                 '||c2_rec.data_type||',');
                                            dbms_output.put_line('NEW_'||c2_rec.column_name||'                  '||c2_rec.data_type||',');                     
              end if;
            end loop;            
             dbms_output.put_line('CHANGE_DATE                                    DATE,');
             dbms_output.put_line('CHANGE_BY                                      VARCHAR2(30),');
             dbms_output.put_line('OPERATION                                      CHAR(1),');
             dbms_output.put_line('ADDRESS                                        VARCHAR2(200));');
        end;

Sql Script to get audit trigger Script for any table In Oracle

-- This Script is shared by Ashfaq Ahmed.

This script help you to generate script to create audit trigger for any table.
Audit are used to record history for the modification made to data.

Before creating the trigger use following script to create table to store audit data.

Script to create audit table

Set serveroutput on size unlimited

Declare
  tab varchar2(50);
   owne varchar2(20);
           cursor c2 is select owner,table_name,column_name,column_id,data_type,data_length,data_precision,data_scale from dba_tab_columns where table_name='&tab' and owner='&owne' order by column_id;
           v_id number;
       Begin
           select max(column_id) into v_id from dba_tab_columns where table_name='&tab';
           for c2_rec in c2 loop
               if c2_rec.column_id=1 then
                  dbms_output.put_line('CREATE TRIGGER AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('AFTER INSERT OR DELETE OR UPDATE ON '|| c2_rec.owner||','||c2_rec.table_name);
                  dbms_output.put_line('FOR EACH ROW');
                  dbms_output.put_line('DECLARE');
                  dbms_output.put_line('INFO VARCHAR2(200);');
                  dbms_output.put_line('BEGIN');
                  dbms_output.put_line('SELECT sys_context(''USERENV'',''TERMINAL'')'||'||'||''' - '''||'||'||'sys_context(''USERENV'', ''OS_USER'')'||'||'||''' - '''||'||'||'sys_context(''USERENV'',''IP_ADDRESS'')' ||' INTO'|| ' INFO'|| ' FROM'|| ' dual'||';');
                  DBMS_OUTPUT.PUT_LINE('-----------INSERTING------------');
                  dbms_output.put_line('    IF INSERTING THEN');
                  dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('I');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
                     ------------------------------------
            DBMS_OUTPUT.PUT_LINE('-----------UPDATING------------');
            for c2_rec in c2 loop
               if c2_rec.column_id=1 then
                     dbms_output.put_line('    ELSIF UPDATING THEN');
                      dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('U');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
          DBMS_OUTPUT.PUT_LINE('-----------DELETING------------');
          for c2_rec in c2 loop
              if c2_rec.column_id=1 then
            DBMS_OUTPUT.PUT_LINE('ELSIF DELETING THEN');
                  dbms_output.put_line ('INSERT INTO AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'');
                  dbms_output.put_line('(OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id>1 and c2_rec.column_id<v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||',');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||',');
               elsif c2_rec.column_id=v_id then
                  dbms_output.put_line('OLD_'||c2_rec.column_name||')');
                  dbms_output.put_line('NEW_'||c2_rec.column_name||')');
                  dbms_output.put_line('CHANGE_DATE,');
                  dbms_output.put_line('CHANGE_BY,');
                  dbms_output.put_line('OPERATION,');
                  dbms_output.put_line('ADDRESS)');
                  dbms_output.put_line('Values');
               end if;
            end loop;
            for c3_rec in c2 loop
               if c3_rec.column_id=1 then
                  dbms_output.put_line('(:OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id>1 and c3_rec.column_id<v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
               elsif c3_rec.column_id=v_id then
                  dbms_output.put_line(':OLD.'||c3_rec.column_name||',');
                  dbms_output.put_line(':NEW.'||c3_rec.column_name||',');
                  dbms_output.put_line(SYSDATE||',');
                  dbms_output.put_line(USER||',');
                  dbms_output.put_line('D');
                  dbms_output.put_line('INFO);');
               end if;
            end loop;
       dbms_output.put_line('END IF;');
       dbms_output.put_line('END;');     
   end;

Funtion to verify Date and number data in a table.

Following functions can be used to extract invalid data from table and are usefull in ETL.

First function accepts two parameters one is value and other is date format.
It will return 1 if the data in the column is according to date format provided else it will return 0.
This type of function can be used in following situation.

Data is loaded from excel or text file into a temporary table with all columns of character type and hiredate is loaded with mix of date entries i.e.
10/01/12,10/1/12,13-mar-2012 , 1/2/2012.
In this situation one need to identify and make same pattern of date to load data in actual table.

In following way this function can be used.


select is_date('31-mar-2013 20:10:30','dd-mon-yyyy hh24:mi:ss')
from dual;

select * from temptable
where is_date(hiredate,'dd-mon-yyyy hh24:mi:ss')<>1;

create or replace function is_date (p_value varchar2,p_format varchar2) return number is
v_date date;
begin
v_date:=to_date(p_value,p_format) ;
return 1;
exception when others then
return 0;
end;


This is same function to identify the valid number in column.

create or replace function is_number (p_value varchar2) return number is
v_date number;
begin
v_date:=p_value ;
return 1;
exception when others then
return 0;
end;


select is_number('221a34') from dual;

select * from temptable
where is_number(salary)<>1;

Sql Script to generate script to reorganize tables to remove fragmentation

This script is shared by Ashfaq Ahmed.


-----------------

Following script will generate script to reorganize tables to remove fragmentation of Scott schema and users tablespace.

declare
cursor c1 is select table_name from dba_tables where tablespace_name='USERS' and owner='SCOTT';
a  number;
d  varchar2(30);
b  number;
begin
 dbms_output.enable(60000);
   open c1;
     b:=0;
     select count(*) into a from dba_tables where tablespace_name='USERS';
     while b<a loop
           fetch c1 into d;
           b:=b+1;
           dbms_output.put_line('alter table '||d||' move tablespace users;');
           declare
             cursor c2 is select index_name from dba_indexes where table_name=d;
             a1    number;
        d1 varchar2(30);
             b1 number;
           begin
             open c2;
               b1:=0;
               select count(*) into a1 from dba_indexes where table_name=d;
               while b1<a1 loop
                  fetch c2 into d1;
                  b1:=b1+1;
                  dbms_output.put_line('alter index '||d1||' rebuild online;');
               end loop;
            end;
       end loop;
end;

How to unload data from multiple tables into Multiple files

Here the task is to unload all data from all tables of scott schema in to different files.

Create folder scott at any drive. As in my case I have created scott folder in c: drive.

now create spoolmultiple.sql file and add following script to this file.

-----------------Start-------------------

SET LINESIZE 8000 TRIMSPOOL ON PAGESIZE 0 FEEDBACK OFF
--col well_operator format a100
set long 2000

SET TERMOUT OFF
--SET LINES 108
--SET PAGES 0

select * from emp;

spool c:\scott\emp.txt
/
spool off

select * from dept
.
spool c:\scott\dept.txt
/
spool off

select * from salgrade
.
spool c:\scott\salgrade.txt
/
spool off

---------------end-------------------


Now login to database with scott user.

c:\> sqlplus scott/tiger@ardb

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 20 20:22:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @spoolmultiple_queries.sql
SQL> exit;
c:\>


Now you should have three files in scott folder.

How To Restore Oracle Database Using Cold Backup

Sometimes we are working on test database and database is curropted or we want to refresh the database.
In that case we can use cold backup to restore the database to previous state.

Click here to see How to take cold backup

To restore the database follow the below instructions.

Login to Oracle with Sys as sysdba

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 19 20:12:04 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Shutdown database.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@arzaidi Desktop]$


First delete existing files to clean the database.

remove all datafiles from the ../oradata/database folder in my case it is /oracle/flash_recovery_area/ardb.
Remove all control files. Usually second control file is found in flash_recovery_are/database folder in my case it is /oracle/flash_recovery_area/ardb.


Now copy all files from the backup and put them in the apporipate folders as /oracle/oradata/database in my case it is /oracle/flash_recovery_area/ardb.

Copy second control file in flash_recovery_area/database in my case it is /oracle/flash_recovery_area/ardb.

Go to Shell Prompt and login to Oracle with sys user as sysdba and start database.

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 19 20:04:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@arzaidi Desktop]$


your database has been restored to previous point.

How To Recover Data files in oracle database without shutting down the database with Hot/Cold backup.

Connect to oracle database.


[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:10:26 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Create new user.


SQL> create user ali identified by ali;

User created.

Give rights.

SQL> grant resource,connect,dba to ali;

Grant succeeded.

make apps_data tablespace as default tablespace.


SQL> alter user ali default tablespace apps_data;

User altered.

connect to new created user.

SQL> connect ali/ali@ardb
Connected.


SQL> create table sales1 as select * from sh.sales;

Table created.

Go to oradata folder and then databse folder and delete APPS_DATA01.DBF file.

Now try to execute following command this will give error beacuse datafile APPS_DATA01.DBF is deleted which contains sh.sales data.


SQL> create table sales2 as select * from sh.sales;
create table sales2 as select * from sh.sales
                                        *
ERROR at line 1:
ORA-01565: error in identifying file '/oracle/oradata/ardb/apps_data01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

--But the following table is created even if apps_data01.dbf which contains data of ali user does not exists beacue it is using undo files to store new data
for the ali user.


SQL> create table dba_tables as select * from dba_tables;

Table created.

SQL> select count(0) from dba_tables;

  COUNT(0)
----------
      2784

SQL>  exit;

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:32:11 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> exit;

[oracle@arzaidi Desktop]$ sqlplus ali/ali@ardb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:35:48 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Do not shutdown database just login as sysdba and offline the table space.

SQL> conn sys/oracle as sysdba
Connected.

Take offline apps_data tablespace.


SQL> alter tablespace apps_data offline immediate;

Tablespace altered.


Now copy apps_data01.dbf from backup to the oradata/ardb folder.

SQL> recover automatic tablespace apps_data;
Media recovery complete.
SQL> alter tablespace apps_data online;

Tablespace altered.

SQL> conn ali/ali@ardb
Connected.
SQL> select count(0) from dba_tables;

How to Recover Data files Using database backup in Oracle

At first step we will curropt the database and then try to recover it from the cold /hot backup.

Suppose your database is running go to oradatafolder and then to your database folder in my case it is /ardb.

Delete some database files as follows.
users01.dbf
system01.dbf
sysaux01.dbf


Now try to execute following command and you will receive error.

SQL> create table scott.sales as select * from sh.sales;
create table scott.sales as select * from sh.sales
                                             *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/oradata/ardb/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

shutdown database;


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 08:56:39 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

Try to start the database and you will again receive error with mean you need recovery.


SQL> startup;  
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/ardb/system01.dbf'


Shutdown immediate with following command and copy missing files to the database folder from the backup.


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Startup database to mount point.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.

execute following command to recover the database.

SQL> recover automatic database;
 Media recovery complete.

 Now open the database.

 SQL> alter database open;

Database altered.

SQL>                

How to enable database archive mode in oracle

Login sys as sysdba.

sqlplus sys/oracle as sysdba


To check if archiving status

SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Current log sequence       25


shutdown database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

start up database at mount point


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.

To check if archiving status

SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Current log sequence       25

To enable archiving .


SQL> alter database archivelog;

Database altered.

To verify archiving


SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence       25
SQL>

Now open the database.

SQL> alter database open;

Database altered.

SQL>

How to take Hot backup of Oracle database.

To take hot backup of oracle database first set the oracle in archive mode.

Note: To enable archive mode please view How to enable database archive mode in oracle

Create Hot backup directory and make oracle user as owner of this directory.

[root@arzaidi /]# mkdir /oracle_backup/hotbackup
[root@arzaidi /]# chown -R oracle.oinstall oracle_backup/
[root@arzaidi /]#


Now login as sysdba to database.

At sql prompt execute following script to get path and copy script for later use of control ,data and redolog file path in windows you can use copy paste.

select 'cp ' ||name|| ' /oracle_backup/hotbackup' from v$datafile


you will get following script as output for later use.

cp /oracle/oradata/ardb/system01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/sysaux01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/users01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/example01.dbf /oracle_backup/hotbackup


execute following script to view all tablespaces.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.


Note: When system tablespace is being backuped oracle uses undo tablespace and when the undo tablespace is begin backuped the it uses system tablespace so
make backup of both tablespaces trun by trun.

First set system table space for backup.

SQL> alter tablespace system begin backup;


Now open another shell and execute copy command as oracle user for system tablespace or from graphical view copy and paste file in hotbackup folder from oradata/dbdatafile folder.


[oracle@arzaidi /]$ cp /oracle/oradata/ardb/system01.dbf /oracle_backup/hotbackup


SQL> alter tablespace system end backup;

Tablespace altered.


SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

[oracle@arzaidi /]$ cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/hotbackup

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.


execute following script to get script to set tablespace in backup mode.

SQL> select 'Alter tablespace '|| tablespace_name ||' begin backup ;' from dba_tablespaces;

-------------------------------------------------------------
--Alter tablespace SYSTEM begin backup ;  -- do not put syatem and undo tablespaces at the same time
--Alter tablespace UNDOTBS1 begin backup ; -- do not put syatem and undo tablespaces at the same time
--------------------------------------------------------------
--Alter tablespace TEMP begin backup ; -- hot backup do not apply to this tablespace
Alter tablespace SYSAUX begin backup ;
Alter tablespace USERS begin backup ;
Alter tablespace EXAMPLE begin backup ;
6 rows selected.



execute following script to get script to unset tablespace from backup mode.

SQL> select 'Alter tablespace '|| tablespace_name ||' end backup ;' from dba_tablespaces;

-------------------------------------------------------------
--Alter tablespace SYSTEM end backup ;  -- do not put syatem and undo tablespaces at the same time
--Alter tablespace UNDOTBS1 end backup ; -- do not put syatem and undo tablespaces at the same time
--------------------------------------------------------------
Alter tablespace SYSAUX end backup ;
Alter tablespace USERS end backup ;
Alter tablespace EXAMPLE end backup ;
6 rows selected.

alter database backup controlfile to '/oracle_backup/hotbackup/controlfilebackup.bkp';

Now the database hot backup is complete.

How to take cold backup of oracle database

Login to linux shell prompt with root user and create directory /oracle_backup/coldbackup for the backup.

mkdir oracle_backup

mkdir /oracle_backup/coldbackup

To change owner to oracle admin user and group to oinstall.

chown -R oracle.oinstall /oracle_backup

To set oracle SID whose backup will be created if there is only one database may not need to execute it.

export ORACLE_SID=ardb

Login to database with sys user


sqlplus sys/oracle as sysdba

At sql prompt execute following script to get path of control ,data and redolog file path.

select 'cp ' ||name|| ' /oracle_backup/coldbackup' from v$datafile
UNION ALL
select 'cp ' ||name|| ' /oracle_backup/coldbackup' from v$controlfile
UNION ALL
select 'cp ' ||name|| ' /oracle_backup/coldbackup' from v$tempfile
UNION ALL
select 'cp ' ||member|| ' /oracle_backup/coldbackup' from v$logfile
order by 1;


shutdown database to take cold backup..

shutdown immediate;

exit;


------------------------
you will get output like below copy this output and paste on shell prompt with oracle user this will copy all oracle files to coldbackup folder.

cp /oracle/oradata/ardb/system01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/sysaux01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/users01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/example01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/control01.ctl /oracle_backup/coldbackup
cp /oracle/flash_recovery_area/ardb/control02.ctl /oracle_backup/coldbackup
cp /oracle/oradata/ardb/temp01.dbf /oracle_backup/coldbackup
cp /oracle/oradata/ardb/redo03.log /oracle_backup/coldbackup
cp /oracle/oradata/ardb/redo02.log /oracle_backup/coldbackup
cp /oracle/oradata/ardb/redo01.log /oracle_backup/coldbackup


Now login again to oracle with sys user.

sqlplus sys/oracle as sysdba

To start the database;

startup;

exit;

your database cold backup is complete.

How to get data in file from Oracle using spool

SET TERMOUT OFF
SET LINES 108
SET PAGES 0


SELECT
ename            ,
empno            ,
SUBSTR(job,1,20),
hiredate      ,
salary
FROM
emp
WHERE ename='ALI'
AND   SALARY>5000
ORDER BY 2
.

SPOOL D:\EMPLOYEE_DATA.TXT
/
SPOOL OFF

Automating Database Startup and Shutdown on Linux

To automatically start the database and listener

with root user

1) Edit the /etc/oratab file as

[root@server1 ~]# gedit /etc/oratab

e.g your database name is orcl then
in this file there will be a line as

orcl:/oracle/product/10.2.0/db_1:N--------only change this 'N' to 'Y'

2) Create dbora file under /etc/init.d/  using following script .

---------------dbora start--------------------



#!/bin/sh

# The script in dbora starts and shuts down the Oracle database automatically.
# Description: This script calls the dbstart script to start oracle and dbshut to stop oracle.

ORA_HOME=/app/oracle/product/11.2/db_home
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ];
then
        echo "Sorry!!! The $ORA_HOME/bin/dbstart is missing"
        echo "Cannot start Oracle"
        exit
fi

case "$1" in
'start') # Starts the Oracle database and listeners
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
# su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl start"
                ;;
'stop') # Stops the Oracle databases and listeners
 # su - $ORA_OWNER -c "$ORA_HOME/bin/isqlplusctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
;;
esac


---------------dbora end--------------------------------

3)[root@server1 ~]# chmod 755 /etc/init.d/dbora

4) run these commands one by one

   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc1.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc2.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc4.d/K10dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc5.d/S99dbora
   [root@server1 ~]# ln -s /etc/init.d/dbora /etc/rc6.d/K10dbora

5) [root@server1 ~]# reboot