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

Managing Users and Resources

 Managing Users and Resources

Creating users :
      You can create a new database user by using the create user dialog box in SQL*DBA or the SQL command creates users .when you create a new user giving username and password is mandatory , where as following will take default values if not provited

    1.Default tablespace
    2.temporary tablespace
    3.tables space quotas
    4.Profile


1.Default tablespace :

       Each user is associated with a default tablespace.the default setting for every users default tablespace is system tablespace.if a user creates any type of object you should specifically assaign the user a default tablespace


2.Temporary tablespace:

      Each user is associated with a temporary tablespace , when a user execute the SQL statement that requires the creation of temporary segments,the user temporary tablespace contains the emporary segments.

 To create the new database user(ramu)

sys>>create user ramu identified by ramu
         default tablespace tbs1
         quota 5m ON tbs1
         quota 3m ON system
         profile clerk;

 sys>>alter user ramu profile clerk;

 To change the password for the user

 sys>> alter user ramu identified by <newpassword>;

To drop the user
 sys>> drop user ramu cascade;

 Creating and alter a 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 

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;

SESSIONS and user licensing:

LICENCE_MAX_SESSIONS
LICENCE_SESSION_WARNING
LICENCE_MAX_USERS

  To set the maximum sessions for an Instance:

sys>>ALTER system set licence_max_sessions=100;

 To set both WARNING limit and maximun limit

sys>>alter system set licence_max_session=64
                      licence_sessions_warning=54;

 To set USER limit :

sys>>alter system set licence_max_users=30;

Password file Athentication:

$cd $ORACLE_HOME/dbs
$orapwd file=<filename> password=<password> entries=<max_users>

Example:
$orapwd file=orapwDEMO password=oracle entries=3


Viewing Information about database users and profiles:

DBA_USERS
ALL_USERS
USER_USERS
DBA_TS_QUOTAS
USER_TS_QUOTAS
USER_PASSWORD_LIMITS
USER_RESOURCE_LIMITS
DBA_PROFILES
RESOURCE_COST
v$session
v$sessat
v$statname



examples:

sys>>create PROFILE prof_clerk limit
         session_per_user 1
        idle_time 30
        connect_time 600;


sys>>create user ramu identified by ramu
                  default tablespace ts1
          temporary tablespace tmp1
          quota 50m on ts1
          profile ramu;

  Listing all users and associated information :

sys>>select username,account_status,default_tablespace,temporary_tablespace,
                                             created,profile from dba_users;

  Viewing memory use for each session :

sys>>select username,value || 'BYTES' "current UGA memory"
            from v$SESSION sess,v$SESSTAT stat,v$STATNAME name where
        sess.SID=stat.SID AND stat.STATISTIC# = name.STATISTIC#
        and name.NAME = 'session uga memory';

  To see the current licensing limits;

sys>> select sessions_max,sessions_warning,sessions_current s_current,sessions_highwater s_high,
      users_max from V$License;