EKTP

EKTP: 

BIG FILE Creation

BIG FILE Creation :

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview:

The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:

• An Oracle database can contain both bigfile and smallfile tablespaces.
  
• System default is to create the traditional smallfile tablespace.
  
• The SYSTEM and SYSAUX tablespaces are always created using the system default type.
   
• Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

There are two exceptions when bigfile tablespace segments are manually managed:

• Locally managed undo tablespace
  
• Temporary tablespace

Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.
However, you can also use The bigfile tablespace without ASM.

Bigfile Tablespace Benefits:

Bigfile tablespace has the following benefits:

• The bigfile tablespace simplifies large database tablespace management by reducing the number of datafiles needed.
  
  
• The bigfile tablespace simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
  
  
• The bigfile tablespace allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
  
  
• The bigfile tablespace follows the concept that a tablespace and a datafile are logically equivalent.

The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.


The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.

BLOCK SIZE	MAXIMUM DATA FILE SIZE	MAXIMUM DATABASE SIZE
32 K	131,072 GB	8,589,934,592 GB
16 K	65,536 GB	4,294,967,296 GB
8 K	32,768 GB	2,147,483,648 GB
4 K	16,384 GB	1,073,741,824 GB
2 K	8,192 GB	536,870,912 GB

 

Creating Big file tablespace :

sql>>CREATE BIGFILE TABLESPACE user_tbs
     DATAFILE '/disk1/oradata/ORCL/user_tbs01.dbf' SIZE 1024m;

 Resizing the datafile :

sql>>ALTER TABLESPACE user_tbs DATAFILE '/disk1/oradata/ORCL/user_tbs01.dbf' RESIZE  10G;

 Data Dictionary Views Enhancement

->A new column is added to both dba_tablespaces  and v$tablespace views
to indicate whether a particular tablespace is bigfile or smallfile

SQL> select name, bigfile  from v$tablespace;

SQL> select tablespace_name,bigfile from   dba_tablespaces;

Here is an example on how to use the dbms_rowid package to retrieve rowid information:

SYS>>select dbms_rowid.rowid_relative_fno(rowid, 'BIGFILE') 
       bigfile_rowid,
       dbms_rowid.rowid_relative_fno(rowid, 'SMALLFILE')
       smallfile_rowid,
       first_name, last_name
       FROM   hr.employees where  rownum < 3;

Bigfile Tablespace Rowid Format:

BIGFILE_ROWID SMALLFILE_ROWID FIRST_NAME           LAST_NAME
------------- --------------- -------------------- ----------
         1024               4 Mike                 Ault
         1024               4 Madhu              Tumma

Data Dictionary Views Enhancement

A new column is added to both dba_tablespaces  and v$tablespace views to indicate whether a particular tablespace is bigfile or smallfile:

SQL> select name, bigfile from v$tablespace;

 NAME                           BIGFILE
------------------------------ -------
SYSTEM                         NO
UNDOTBS01                NO
SYSAUX                          NO
TEMP                             NO
EXAMPLE                     NO
USERS                            NO
BIG_TBS                        YES

SQL> select tablespace_name,bigfile from   dba_tablespaces;

TABLESPACE_NAME                BIGFILE
------------------------------ ---------
SYSTEM                                   SMALLFILE
UNDOTBS01                          SMALLFILE
SYSAUX                                   SMALLFILE
TEMP                                      SMALLFILE|
EXAMPLE                               SMALLFILE
USERS                                      SMALLFILE
BIG_TBS01                              BIGFILE

Example 1: Create a database with default bigfile tablespace.

CREATE DATABASE GRID
SET DEFAULT BIGFILE TABLESPACE
DATAFILE ?/u02/oradata/grid/system01.dbf? SIZE 500 M,
SYSAUX DATA FILE ?/u02/oradata/grid/sysaux01.dbf? SIZE 500 M
DEFAULT TEMPORARY TABLESPACE tbs01
TEMPFILE ?/u02/oradata/grid/temp01.dbf? SIZE 1024 M
UNDO TABLESPACE undo01
DATAFILE ?/u02/oradata/grid/undo01.dbf? SIZE 1024 M;

Example 2: Moving data between smallfile and bigfile tablespaces.

ALTER TABLE employee MOVE TABLESPACE bigfile_tbs;

Example 3: Create a bigfile tablespace and change its size. 

CREATE BIGFILE TABLESPACE user_tbs
DATAFILE ?/u02/oradata/grid/user_tbs01.dbf? SIZE 1024 M;
ALTER TABLESPACE user_tbs RESIZE 10G;

In the previous release of Oracle server, K and M were used to specify storage size. Notice in this DDL statement, a user can specify size in gigabytes and terabytes using G and T respectively.

Example 4: Use DBVERIFY utility with bigfile. With small file tablespace, you can run multiple instances of DBVERIFY in parallel on multiple datafiles to speed up integrity checking for a tablespace.  You can achieve integrity checking parallelism with BFTs by starting multiple instances of DBVERIFY on parts of the single big file.

$dbv FILE=bigfile01.dbf  START=1 END=10000
$dbv FILE=bigfile01.dbf  START=10001

Note: START = Start Block; END = End Block

Constraints;
1.Unique
2.Not Null
3.check
4.primary key
5.foreign key


Table level : Constraints are set of rules or bussiness rules enforced on data to restrict the users in order to insert
duplicates and null values.

1.Unique constraints;
If we impose a unique constraints on a column it wont allow duplicates but it will allow null values.

2. Not null constraints :
If we impose a Not Null constraints on a column it wont null values  but it will allow Duplicates.

3. Check Constraints:

If we impose a Check constraints on a column it will check the value at the time of insertion.

4 Primary key :

It is a combination of unique constraint and Not null Constraint. If we impose a primary key it wont allow the Duplicates and Not null values

**Each table should have only one primary key, On which column we impose the primary key that column should not consist the duplicates and null values.

5.Foreign Key: It is refered as ------------------key,
if we impose a foreign key constraint on a column or table that column or table refered as another table.
--------------------------------------------------------------------------------------------------------------- Imposing the constraints while creating the table
---------------------------------------------------------------------------------------------------------------
Syntax for table level Constraints
In table level we cannot impose the not null constraint
create table <table name>(column1 datatype(size),column2 datatype(size),constrainttype(column1),constrainttype(column2);

eg:
select username,account_status from dba_users;

create table biomorf(sno number(10),name char(20),department varchar2(10),salary number(10),
unique(sno),primary key(salary));

insert into biomorf values(&sno,'&name','&department',&salary);

create table ramu1(sno number(8),name varchar(10), salary number(10),primary key(sno),unique(name));
desc USER_CONSTRAINTS
select * from user_constraints;   ---- data dictionary table for constraints
select * from user_cons_columns;  ---- data dictionary table for constraints with columnname
------------------------------------------------------------------------------------------------------------------------
to create a table whith constraint name
In table level we cannot impose the not null constraint
Syntax:
create table <tablename>(column1 datatype(size),column2 datatype(size)...,constraint <constraint name> constrainttype(column name);
select * from tab;

CREATE table infronics(sno number(10),name char(10),mobile number(10),constraint p_k primary key(sno),constraint u_k unique(name));
select * from user_constraints;   ---- data dictionary table for constraints
select * from user_cons_columns;  ---- data dictionary table for constraints with columnname

------------------------------------------------------------------------------------------------------------------------ 
Imposing the constraints to columns while creating the table
------------------------------------------------------------------------------------------------------------------------
Syntax for Column level Constraints
Only in column level we can impose the not null constraint
create table <table name>(column1 datatype(size) constraint_type,
column2 datatype(size) constraint_type,.....);

manual imposing constraint name:
create table <table name>(column1 datatype(size) constraint Constraint_name constraint_type,
column2 datatype(size)constraint Constraint_name constraint_type,.....);

Eg:
select * from tab;
create table info2(sno number(2) unique,name char(10) primary key,salary number(10) not null);
create table info31(sno number(2) constraint l_1 unique,
name char(10) constraint l_b primary key,
salary number(10)constraint l_c not null);
select * from user_constraints;   ---- data dictionary table for constraints
select * from user_cons_columns;  ---- data dictionary table for constraints with columnname

------------------------------------------------------------------------------------------------------------------------ Imposing the constraints for existing columns
------------------------------------------------------------------------------------------------------------------------
NOT Null Constraint: only for not null constraint will use the modify
Syntax:
>Alter table <table name> modify <column name> constraint <constraint name> <constraint type> ;
Eg:
alter table biomorf1 modify sno constraint l_l not null;

drop the constraint for particular column:
Syntax:
alter table <table name>drop constraint <constraint name>
Eg:
alter table biomorf1 drop constraint p_p;
---------------------------------------------------------------------------------------------------------
Unique constraint :

syntax :
>Alter table <table name>add constraint <constraint name> <constraint type>

Eg:alter table biomorf add constraint p_p unique(name);

drop the constraint for particular column:
Syntax:
alter table <table name>drop constraint <constraint name>
Eg:
alter table biomorf1 drop constraint p_p;
------------------------------------------------------------------------------------------------------------------------
check constraint:

Syntax:
>Alter table <table name> add constraint <constraint name> <constraint type>

Eg:
>Alter table test add constraints c_k1 check(salary>800>
------------------------------------------------------------------------------------------------------------------------
primary key and foreign key constraints;

Primary key :
syntax
alter table <table name> add constraint <constraint name> constraint type(column name)
Eg:
alter table biomorf add constraint p_k primary key(SALARY);
Foreign Key :
Syntax:
alter table <table name> add constraint <constraint name> constraint type(column name) references tablename<column name);
Eg:
alter table infronics add constraint f_k foreign key(salary) references biomorf(salary);

NOTE:
While inserting the data the child table will depends on the parent table
while deleting the data in parents table it will depends on the child table data

Drop constraints :
alter drop <tablename> drop constraint <constraint name>;
Eg:
alter table biomorf drop constraint p_k;
alter table infronics drop constraint f_k;
------------------------------------------------------------------------------------------------------------------------
Rename constraints :
Syntax :
alter table <table name> rename constraint <old constraint name > to < new constraint name>;
Eg:
alter table biomorf rename constraint p_k to f_k;
------------------------------------------------------------------------------------------------------------------------
ON Delete Cascade :
If we are imposing the foreign key with option on delete cascade . if we delete a record from the parent table , automatically all the rows will delete from
the child table related to that record
syntax :
alter table <table name> add constraint <constraint name> <table name(column name) on delete cascade;
Eg :
alter table infronics add constraint f_k foreign key(salary) references biomorf(salary) on delete cascade;
------------------------------------------------------------------------------------------------------------------------
ON delete Set Null :
If we are imposing the foreign key with option on delete set null . if we delete a record from the parent table , records from the child table on which ever column we impose a foreign key on that column automatically null will set
Syntax:
alter table <table name> add constraint <constraint name> <table name(column name) on delete set null;
Eg:
alter table infronics add constraint f_k foreign key(salary) references biomorf(salary) on delete set null;
------------------------------------------------------------------------------------------------------------------------

Components of SGA

COMPONENTS OF SGA

 

Components of SGA :

 Mandatory Components :

  1.DBBC(Database Buffer cache)

  2.Shared Pool

    a.Library Cache

    b.Data Dictionary cache

  3.Redolog buffer

 Optional Components :

  

   1.Large pool

   2.Java pool

   3.Streams pool

 Components of Background Process

  Mandatory Components 

   1.DB WRITER (DBWR)

   2.LOG WRITER (LGWR)

   3.CHECK POOL PROCESS (CKPP)

   4.SYSTEM MONITOR (SMON)

   5.PROCESS MONITOR (PMON)

   6.RECOVER PROCESS (RECO)

  Optional Components

   1.ARCHIVER (ARCH)

   2.MEMEORY MANAGER

   3.DISPATCH PROCESS

   4.CLOCK MONITOR

   5.JAK QUEE (CJQ)

 

Infomation table of datafiles

Brief notes on Database files

  Database files :

 There are 3 different database files

   1.Control Datafile
   2.Redolog Datafile
   3.Datafile

1. Control file:

   ->It controls the database
   ->the most important file in an oracle database
   ->it is small binary file necessary for DB to open or start database successfully.
   ->Every Oracle DB must have alteast 1 control file.

   The control file contains :

   1.It contains name of the database
   2.Time Stamp of database creation
   3.It contains all the files of database
   4.It Contains name and locations of datafiles and redolog.
   5.Archived log Information
   6.log sequence no (LSN)
   7.check point Information
   8.System Change no
   9.Backup set details


2.Redolog File:

  ->It Contains latest Transaction
  ->It contains all the modification or Updations
  ->This file is very important file during the recovery database.
  ->All committed  data goes redolog file
  ->In database we must create 2 redolog groups , in each group it should have alteast 1 redolog file


3 Datafile :

To create a database there will be 3 datafiles
     a.System datafile
     b.Sysaux datafile
     c.Undo datafile

a.System datafile :

  ->It is introduced in oracle version 9C
  ->It contains the metadata (Structure of database) stored in system file in the form base tables.
 

 b.Sysaux Datafile :

   ->It acts as secondary system file to reduce the performance related tables

         AWB (Automatic workload repository)
         ADDM (Automatic DB Diagnostic Moniter)

   ->To reduce the work load of the system file


 c.Undo Datafile :

   ->It contains the previous image of the transaction

Optional Database file :

 1.User datafile :it contains the user related Information .

 2.User index : It contains indexes information created by user.

 3.Temporary datafile : It contains temporary data for sorting purpose.

Database Arcitecture

Database Arcitecture

Database architecture is divided in to 2

   1.Database instance
   2.Database


1.Database Instance :

-->Database Instance Structure. When an instance is started, Oracle Database allocates a memory area
called the system global area (SGA) and starts one or more background processes. The SGA serves various
purposes, including the following:

-->The combination of both SGA and background process is called the database instance .

2.Database:  

              It is classified into 2 layers
                a. logical layer
                b. Physical layer

a)Logical layer - (Tables,Views,Index,Synonyms , Sequences)
b)Physical layer - ( DBfiles --> Filesystem --> Operating system --> Disk)

Physical Backup (Cold Backup)

Backup's

There are two types of backup's

1.Logical backup

     Exp & Imp
     Expdb & Impdb ( Datapump)


2.Physical Backup

     Cold Backup
     Hot Backup
     Rman

Physical Backup ( Cold Backup )

Cold Backup:

==>A cold backup is done when there is no user activity going on with the system. Also called as offline
backup, is taken when the database is not running and no users are logged in. all files of the database
are copied and no changes during the copy are made

==>The benefit of taking a cold backup is that it is typically easier to administer the backup
and recovery process. For cold backups the database does not require being in archive log mode and
thus there will be a slight performance gain as the database is not cutting archive logs to disk.


1. Cold backup is offline Backup, and Consistency backup

      shutdown Normal
      shutdoen transactional
      shutdown immediate

Two Different Types of failures

1.Instance Failure
2.Media failure

1) Instance Failure :

      Power failure
      Shutdown abort
      Killing Background Process

** SMON is responsible for performing instance crash recovery during the Next Startup

During the data recovery 2 process are invoked 

    1.Roll forward process
    2.Roll back process

2)Media failure

       Block Corruption
       Disk Error or Crashed
       If we loose Control,Redolog,Data files

During the media recovery 2 process are invoked

     1.Restore the previous Cold Backup
     2.Recovery --> Apply Archived & redolog files

Recovery Process:

    Is categorized into @

    1.Complete recovery -- Apply all archived log and Online redologfiles

    2.Incomplete Recovery

1.Complete Recovery will done when we have 

      present control file
      Archived redolog files
      redolog files


   -->Complete recovery can be done in 2 ways 

       1.Online
           Non system datafile

       2.Offline

           System Datafile
           Undo datafile

2. Incomplete recovery can be done 

       Until cancel -- Apply all archived logfiles generated
       Until time    -- Apply all archived redolog generated Until Time
       Until SCN    -- Applu all archived redolog generated until SCN

 After performing Incomplete Option recovery we have to open database in resetlogs

      open resetlog Option (SCN reset to 1)

commands :

To check the database is in archive log mode

  sys>>archive log list;
  sys>>select log_mode from v$database

To check the path of CRD files

   sys>> select name from v$controlfile;

   sys>> select name from v$datafile;

   sys>> select member from v$logfile;


    <============================>Loss of CRD file <=========================>

Steps to recovery when loss of CRD files:

Backup :

Take backup of CRD Files

$ mkdir Cold

$ cp * Cold/

sqlplus / as sysdba

sys>>Startup

sys>> conn u1/u1

sys>> insert records into table and commit;

sys>> alter system switch logfile

sys>> shutdown immediate


Restore the privious CRD file into

$ cd cold

$cp * ../

Recover will be done in mount state

sqlplus / as sysdba

sys>> startup mount;

sys>> alter database recover automatic using backup controlfile until cancel;

sys>> recover cancel;

sys>> alter database resetlogs;

>>desc v$logfile;

>>desc v$database_incardination

>>select incardination#,resetlogs_id from v$database_incardination;


<==========================>Loss Of Control Files<=========================>

For recovery need privious CRD files backup

Previous backup should contains  contains CRD files

Note :

If there is no previous Backup we cannot perform recovery

sqlplus / as sysdba
sys>> shut immediate;

Recovery steps :

sqlplus / as sysdba

sys>> startup;

sys>> insert records into the table

sys>> shutdown abort

Restore the previous control file backup

Recovery will done in mount state

sqlplus / as sysdba

sys>>startup mount

sys>> alter database recover automatic using backup controlfile until cancel;

sys>> recover cancel;

sys>> alter database open resetlogs;


<=======================> Loss of System Datafiles :<=========================>


For recovery need previous system data files  to recovery

Note :

If there is no privious backup of system datafiles we cannot perform the recovery

 sqlplus / as sysdba

 sys>> shutdown abort;

 $ ps -eaf |grep -i smon

 $kill -9 10250

Restore the previous systems files

 $cp * .dbf ../

 sqlplus / as sysdba

sys>> startup mount;
sys>> recover database

sys>> alter database open;

<=========================>Loss of Undo Datafile<==========================>

For recovery need previous CRD files backup

Previous backup contains CRD files

sqlplus / as sysdba

sys>> shutdown immediate

Restore the undo datafile

$ cp Undo.dbf ../

sqlplus / sysdba

sys>> startup mount

sys>> alter database datafile 3 offline;

sys>> alter database recover automatic datafile 3;

sys>> alter database datafile 3 online;

sys>> alter database open;



<=======================>Loss of Non System Datafile<========================>

For recovery need previous CRD files backup

Previous backup contains CRD files


sqlplus / as sysdba

sys>>shut immediate

Restore the non system datafile

$ cp * non system datafile

sqlplus / as sysdba

sys>> startup mount;

sys>> alter database datafile 4 offline;

sys>> alter database recover automatic datafile '/disk1/oradata/ORCL/undo.dbf';

sys>> alter database datafile 4 online;

sys>> alter database open;


<======================> Demo Unbackedup Datafile <========================>

sys>>create tablespace demo datafile '/disk1/oradata/ORCL/demo.dbf';

sys>> grant connect,resource to demo identified by demo;

sys>> alter user demo default tablespace demo;

sys>> select username,default_tablespace from dba_users;

Conn demo/demo

create table and insert some records

demo>> conn / as sysdba

sys>> alter system switch logfile;

Delete datafile

conn demo/demo

demo>>  inset records into table

Error will get and note datafile id

conn / as sysdba

sys>> alter database datafile 6 offline;

sys>> alter database create 6;

sys>> alter database datafile online;

sys>> alter database open;


<=========================>Loss of Redolog Files <=========================>


For recovery need privious CRD files backup

Privious backup contains CRD files

sqlplus / sysdba

sys>>startup

sys>> conn demo/demo

insert some records

sys>>conn / sysdba

sys>> alter system switch logfile;

sys>> conn demo/demo

sys>> insert some records into table


Delete all redolog files

 $ ps -eaf |grep -i smon

 $kill -9 10250

Copy the datafile and control file from previous backup

 $cp *.dbf ../

 $cp control.ctl ../

 sqlplus / as sysdba

 sys>>startup mount;

 sys>> alter database recover automatic using backup controlfile until cancel;

 sys>> recover cancel

 sys>> alter database open resetlogs;

Logical Backup (Datapump (expdp and impdp) )

Datapump (expdp and impdp) :

-->Introduced in oracle 10g

expdp help=y
impdp help=y

Advantages in Expdp and Impdp :

  1. Time Consuming Process
  2. Dumpfiles will not over written
  3. dumpfiles are in universal location
  4. can assaign a job name
  5. can stop and ongoing export operation
  6. can estimate the size of dumpfile.


  To create the directory Pump in db level and o/s level

Db level :

   sys>> create directory dpump as /disk1/oradata/ORCL/dpump
 
To drop the directory Pump

   sys>> drop directory dpump

O/S Level:
   
       $mkdir -p /disk1/oradata/ORCL/dpump

Master Process :

  -Name of the process(DMmn)
  -For a particular job only one master process will be there

Work process:

  -4 work process will be invoke in export process
  paralle=4
  PARALLEL Change the number of active workers for current job.

Theory :

  --During datapump operation a table called master table will be create to track of dp operation

  --This master table is created in the schema of the user running the import/export operation

  --this table is created by process called master process based on Job name

  --Only one master process runs per Job

  --Once the Job has finished it dumps a table content into dump files and delete the tables

  --there is another process called worker process (DWnn)

      This is the process that actually performs the work. we can have number of worket process is running in same Job

   Commands :

   expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump

                   :/ as sysdba

  To Override the Dump file in the location :

     REUSE_DUMPFILES -- Overwrite destination dump file if it exists [y].
     expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump reuse_dmpfiles=y

  To estimate the size of the dump
    
     ESTIMATE_ONLY -- Calculate job estimates without performing the export.
     expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump estimate_only=y

  To assign the job name of the export operation :
     
     JOB_NAME -- Name of export job to create.
     expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump job_name=y
  
   To Stop the Job

    Ctrl+C
    
export>Stop_job=immediate   (STOP_JOB Orderly shutdown of job execution and exits the client.)

Valid keyword values are: IMMEDIATE.

Are you sure you wish to stop this job ([yes]/no): yes

To resume the privious export Job

     $expdp attach=full
     To see the master table name
       
     export>> continue_client

Check the status of job name with the below command :

     sys>> select table_name from dba_tables where table_name=<jobname>



 Different types of imp and exp

    1.Full DB Backup
    2.Table Spaces Backup
    3.Users level Backup
    4.Tables Backup
    5.Query's Backup

expdp help=y gives the all parameters related to export
impdp help=y gives the all parameters related to import

1.Full DB Backup :
 
  Export Syntax :
 
   expdp dumpfile=full.dmp logfile=full.log full=y directory=dpump

            : / as sysdba

  Import Syntax
  
   impdp dumpfile=full.dmp logfile=full.log full=y directory=dpump

            : / as sysdba


 2.Table Spaces Backup
    Export Syntax :

     expdp dumpfile=ts.dmp logfile=ts.log tablespaces=<table spacename> directory=dpump

            : / as sysdba

    Import Syntax :
  
     impdump file=ts.dmp logfile=ts.log full=y directory=dpump

             : / as sysdba

3.Users level Backup
   
    Export Syntax :
      
        expdp dumpfile=usr.dmp logfile=usr.log schemas=<user name> directory=dpump

              : owner username/password
         
    
    Import Syntax :

impdp dumpfile=usr.dmp logfile=usr.log remap_schema=<export username>:<import  username> directory=dpump

              : owner username/password
         
   
4.Tables Backup

    Export Syntax :

         expdp dumpfile=table.dmp logfile=table.log tables=username.tablename1,username.tablename2 directory=dpump

               : owner username/password


                 
     Import Syntax :  
    
impdp dumpfile=table.dmp logfile=table.log remap_schema=<export username>:<import username> directory=dpump

                        : owner username/password
         
5.Query's Backup

     Export Syntax :

         expdp dumpfile=query.dmp logfile=query.log tables=username.<tablename> query=\'where deptno=30\' directory=dpump

               : owner username/password

     Import Syntax :
    
         impdp dumpfile=query.dmp logfile=query.log tables=username.<tablename> remap_schema=<export username>:<import username> directory=dpump

                                    : owner username/password

Draw backs:

1. we cannot perform the incremental backups






    
     

Logical Backup (Exp and Imp)

Backup's :

There are two types of backup's

1.Logical backup

     1.Exp & Imp
     2.Expdb & Impdb ( Datapump)

2.Physical Backup

     1.Cold Backup
     2.Hot Backup
     3.Rman

1.Logical backup

     1.Exp & Imp

Different types of Imp and Exp:

    1.Full DB Backup
    2.Table Spaces Backup
    3.Users level Backup
    4.Tables Backup
    5.Query's Backup
exp help=y gives the all parameters related to export
imp help=y gives the all parameters related to import

1.Full DB Backup :

  Export Syntax :
 
   exp file=full.dmp log=full.log full=y

            : / as sysdba

  Import Syntax
  
   imp file=full.dmp log=full.log full=y

            : / as sysdba


 2.Table Spaces Backup

    Export Syntax :

     exp file=ts.dmp log=ts.log tablespaces=<table spacename>

            : / as sysdba

    Import Syntax :
  
     imp file=ts.dmp log=ts.log full=y

             : / as sysdba

3.Users level Backup
   
    Export Syntax :
      
        exp file=usr.dmp log=usr.log owner=<user name>

              : owner username/password
         
    
    Import Syntax :

        imp file=usr.dmp log=usr.log fromuser=<export username> touser=<import username>

              : owner username/password
         
   
4.Tables Backup

    Export Syntax :
         exp file=table.dmp log=table.log tables=username.tablename1,username.tablename2

               : owner username/password


                 
     Import Syntax :  
    
         imp file=table.dmp log=table.log fromuser=<export username> touser=<import username>

              : owner username/password
         
5.Query's Backup :

     Export Syntax :

         exp file query.dmp log=query.log tables=username.<tablename> query=\'where deptno=30\'

               : owner username/password

     Import Syntax :
    
         imp file=query.dmp log=query.log tables=username.<tablename> fromuser=<export username> touser=<import username> ignore=y

                 : owner username/password


  Incrementals Backup :

There are 3 types of  Incremental Backups

    1.Complete
    2.Incremental
    3.Cumulative

Complete Backup :

Syntax:

         exp file=comp.dmp log=comp.log inctype=complete

Incremental Backup: 

Syntax:

         exp file=comp.dmp log=comp.log inctype=incremental
   
Cumulative :

Syntax:

         exp file=comp.dmp log=comp.log inctype=cumulative


Drawbacks in Exp and Imp :

  1. Time Consuming Process
  2. Dumpfiles willbe over written
  3. Dumpfiles are in scattered location
  4. cannot assaign a job name
  5. cannot stop and ongoing export operation
  6. cannot estimate the size of dumpfile.


   

Managing Users Priviliges and Roles

Managing Users  Privileges and Roles :

Two types of privileges :
1.System privileges
2.Object privileges


1.System privileges :

sys>>grant create session to user1,finance;

sys>>grand create user,alter table to user1,finance;

sys>>revoke create table from user1;


2.Object privileges :

alter   -- tables,sequences
delete  -- tables,views
execute -- procedures
index   -- tables
insert  -- tables,views
reference -- tables
select    -- tables,sequences,views
update    -- tables,views

Example:

sys>>grant insert,update on emp to u1;


Managing Users Roles :

system defined roles:

connect             ---- create session
resource            ---- create cluster,create procedures,create sequence,create table, create triggen, create dba                    ----  export tables ,users schema etc
exp_full_database   ---- export full database
imp_full_database   ---- import full database
delete_catalog_role ----delete privileges on all dictionary packages for this role
execute_catalog_role --- execute privileges on all catalog tables and viwes for this role
select_catalog_role  ---select privilege on all catalog tables and viwes for this role

Creating,Altering,Dropping,granting and revoking a Role:

create:

sys>>create role clerk identified by demo;

altering :

sys>>alter role clerk identified by <password>;

Dropping :

sys>>drop role clerk;

granting :

sys>>grant manager to user_01 with admin options;

revoking :

sys>>revoke clerk from user1;


Creating the profiles:

session_per_user         --limits the number of concurrent session for the user
cpu_per_session         --limits the CPU time for session. This is expressed in hundredths of seconds
cpu_per_call              --Limits CPU time for a call.This is expressed in hundredths of seconds
connect_time             --limits the total elapsed connect time a session
failed_login_attempts   --no of failed attempts after which accounts is going to locked
password_life_time      --no of days password is valid
password_reuse_max      --no of time password can be changed
paswword_verify_function--Function with which it is going to verify the password
password_lock_time      -- no of days password going to be locked
password_grace_time     --no of days it s going to prompt for password expiry
idle_time               -- defines the maximum amount of continuos inactive time span.
logical_reads_per_session--limits the number of data blocks read ina session
logical_reads_per_call   -- limits the number of data blocks read for a call to process a SQL statement
private_sga
composite_limit

Creating the Profile :

sys>>create PROFILE ramu limit
            SESSIONS_PER_USER   1
        CPU_PER_CALL        6000 
        CONNECT_TIME        560
        PASSWORD_LIFE_TIME  60 
        PASSWORD_GRACE_TIME 10
        IDLE_TIME           15
        FAILED_LOGIN_ATTEMPTS 3;

Changing the values for profile :

sys>>alter profile ramu limit
           LOGICAL_READS_PER_SESSION  20000
       CPU_PER_CALL  default
       LOGICAL_READS_PER_CALL 100;


 To drop the profile

sys>>drop profile ramu cascade;

To lock the Users account:

sys>>alter user ramu account lock;

 To check the status of the users :

sys>>select username,user_id,account_status,lock_date from dba_users where username='ramu';

To expire the users password :

sys>>alter user ramu password expire;

To unlock the user account :

sys>>alter user ramu account unlock;

 To list all system privilege grants :
sys>>select * from dba_sys_privs;

 To list all role grants :

sys>>select * from dba_role_privs;

To list object privileges granted to user :

sys>>select table_name,privilege,grantable from dba_tab_privs;

To list all the column specific privileges that have been granted :

sys>>select grantee,table_name,column_name,privilege from dba_col_privs;

sys>>select * from session_privs;

To list roles of the database :

sys>>select * from dba_roles;

sys>>select granted_role,admin_option from role_role_privs where role='system_admin';

To check the granted roles and their privileges to ramu user

sys>>select a.grantee,a.granted_role,b.privilege from dba_role_privs a,dba_sys_privs b
     where a.granted_role=b.grantee and a.grantee=user1;

Listing privilege and role information


     ALL_COL_PRIVS
     USER_COL_PRIVS
     ALL_TAB_PRIVS
     USER_TAB_PRIVS
     ALL_TAB_PRIVS_MADE
     USER_TAB_PRIVS_RECD
     DBA_ROLES
     DBA_COL_PRIVS
     DBA_SYS_PRIVS
     DBA_ROLE_PRIVS
     DBA_TAB_PRIVS
     ROLE_ROLE_PRIVS
     SESSION_PRIVS
     SESSION_ROLES