Oracle Database Admin (DBA) Tutorial: FlashBack Database

1.   Flashback database

·       This a new feature introduced in 10g.

·       Flashbacking a database means going back to a previous database state.

·       The Flashback Database feature provides a way to quickly revert entire Oracle database to the state it was in at a past point in time.
This is different from traditional point in time recovery.

·       A new background process RVWR introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks 

o   One can use Flashback Database to back out changes that:

o   Have resulted in logical data corruptions.

o   Are a result of user error

o   This feature is not applicable for recovering the database in case of media failure.

o   The time required for flashbacking a database to a specific time in past is
DIRECTLY PROPORTIONAL to the number of changes made and not on the size of the database.

2.   How to Configure Flashback database

Prerequisites
Database must be in archivelog mode.
Last clean shutdown.
Enterprise and Personal Edition required

Configuration
Initialization Parameters required: -

·       DB_RECOVERY_FILE_DEST_SIZE (dynamically modifiable) --> Maximum size flashback logs can occupy in DB_RECOVERY_FILE_DEST.

·       DB_RECOVERY_FILE_DEST (dynamically modifiable) --> Physical location where              RVWR background process writes flashback logs.

·       DB_FLASHBACK_RETENTION_TARGET (dynamically modifiable) --> upper limit in minutes on how far back one can flashback the database. 

Note: - After setting these parameters in parameter file (init.ora) or spfile clean shutdown is mandatory.

SQL> Startup mount;
SQL> Alter database flashback on;
SQL> Alter database open;

SQL> select flashback_on from v$database;
FLASHBACK_ON
YES

3.   Lab Examples

•       Flashback Database

•       Flashback Table

•       Flashback Drop

•       Flashback Query

•       Flashback Versions Query

A.    Flashback Database

Using SCN:-

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to SCN 100;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

Using Timestamp:-

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to TIMESTAMP(sysdate - 2/24);
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

B.    Flashback Table

•       This technology allows you to flashback a table to a previous state after “wrong” DML statements, DDL statements are not flashback-able…

•       Enabling row movement for your test table is mandatory for flashback table:

Ex:

SQL> alter table salgrade enable row movement;

You can flashback the tables using two methods

·       Using SCN number

·       Using restore points

·       Using timestamp

C.    Flashback Drop

This technology is no more no less than Windows recycle bin and allows you to restore a wrongly dropped table. In fact objects are not really dropped but just renamed and they remain in tablespace until you need space to create other objects:

Ex:-

Flashback TABLE salgrade TO before DROP;

D.   Flashback Query

This flashback technology has the taste of flashback table except that you can see the past figures without actually restoring them.

Ex:

SELECT * FROM salgrade AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE

SELECT * FROM salgrade AS OF TIMESTAMP TO_TIMESTAMP('05-MAR-14 10.30.00 PM');

E.    Flashback Versions Query

This flashback technology is similar to flashback query but you can see different past figures of a column over an interval.

Oracle Database Admin (DBA) Tutorial: User Management (Users, Roles and Privileges)

User Management

• Creating Users
• Altering Users

·       Dropping Users

1.    Creating Users:

·       System privilege required to CREATE USER.

·       DBA or security administrator has the CREATE USER system privilege.

 Ex: CREATE USER testuser IDENTIFIED BY password

            DEFAULT TABLESPACE users

           TEMPORARY TABLESPACE temp

           QUOTA UNLIMITED ON users;

Description:

}  CREATE USER command to create a user called testuser.

}  IDENTIFIED BY clause to define the password.

}  DEFAULT TABLESPACE keyword to define the location of the default tablespace, which is USERS.

}  TEMPORARY TABLESPACE keyword defines the temporary tablespace that will be assigned to the user, which in our case is the TEMP tablespace.

}  Unlimited quota to testuser on users tablespace.

}  IDENTIFIED Clause - how Oracle Database authenticates the user.

}  BY password - User must specify password to log on to the database.

}  QUOTAS - How much space a user can take up in tablespace.

}  PROFILE Clause - Specify the profile you want to assign to the user.
 It limits the amount of database resources the user can use.

}  DEFAULT TABLESPACE Clause – Specifies for objects that the user creates.
 If omit - then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.

}  TEMPORARY TABLESPACE Clause Specify the tablespace or tablespace group for the user's temporary segments.

2.    Altering Users

·       System Privileges Required  - ALTER USER

·       Security administrators have this system privilege.

·       Includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

·       The following statement alters the security settings for the user, jiten:

Ex:-

ALTER USER testuser IDENTIFIED BY <password>

       DEFAULT TABLESPACE data_ts

       TEMPORARY TABLESPACE temp_ts

       QUOTA 100M ON data_ts ;

3.    Dropping Users

·       When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema.

·       Note: Do not attempt to drop the SYS or SYSTEM user. Doing so will corrupt your database.

·       A user that is currently connected to a database cannot be dropped.

·       To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause.

·       To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege.

·       For dependent schema objects, use the CASCADE option to drop the user and all associated objects and foreign keys.

·       For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.

·       The following statement drops the user, testuser and all associated objects and foreign keys that depend on the tables owned by testuser.

Ex:-

DROP USER testuser CASCADE;

4.    Listing All users and Associated Information

The following query lists all users and their associated information as defined in the database:

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

5.    Predefined Accounts: SYS and SYSTEM

·       The SYS and SYSTEM accounts have the database administrator(DBA) role granted to them by default.

·       The SYS account in addition has all privileges with ADMIN OPTION and owns the data dictionary.  To connect to the SYS account, you must use the AS SYSDBA clause.

·       Any user that is granted the SYSDBA  privilege can connect to the SYS account by using AS SYSDBA clause.

·       Only privileged users, who are granted the SYSDBA OR SYSOPER privilege, are allowed to start up and shut down the database instance.

·       The SYSTEM account is granted the DBA  role by default, but not the SYSDBA privilege.

·       The SYS and SYSTEM accounts are required accounts in the database. They can’t be dropped.

Privileges

·       A right to run a particular type of SQL statement, or the right to access an object belonging to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle Database.

A.      System Privileges

B.      Object Privileges

             

1.       System Privileges:

Defines what actions a user is allowed to take within a database. 

Ex: The privilege to create tablespaces is a system privilege.

It is granted by the administrator or by someone who explicitly gives permission to administer the privilege. There are more than a hundred system privileges.

The most important system privileges are:

                             create session.

                             create table

                             create view

                             create procedure

                             sysdba

                             sysoper

a.       SYSOPER privilege:

Allows operations such as: 

              Startup a database,

                             Shutdown a database,

                             Backup a database,

                             Recover a database and

                             Create  a database

              This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege: Includes all SYSOPER privileges plus full system privileges
        (with the ADMIN option), plus 'CREATE DATABASE' etc..

2.       Object Privileges: Object privileges allow a user to perform a particular action on a specific object, such as

                             Table

                             View

                             Sequence

                             Package

                             Directory

                             Procedure

                             Function

                             Package

                             Materialized View

·       Without specific permission, users can access only their own objects.

·       Object privileges can be granted by the owner of an object, by the administrator, or by someone who has been explicitly given permission to grant privileges on the object.

User Roles

·       A role is a set or group of privileges that can be granted to users or another role.

·       Oracle Database provides some predefined roles to help in database administration. 

·       These roles, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation.

·       You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define.

·       There are several roles that are defined automatically for Oracle databases when you can run database creation scripts. CONNECT is granted automatically to any user created with Enterprise manager. In earlier versions of the database(before 10g) the CONNECT role included more privileges, such as CREATE TABLE and CREATE DATABASE LINK, which have been removed for security reasons.

Oracle Database Admin (DBA) Tutorial: Tablespaces and Datafiles

1.   Table spaces

•       A tablespace is a logical storage unit within an Oracle database. It is logical because a table space is not visible in the file system of the machine on which the database resides. A table space, in turn, consists of at least one data file which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

•       Each table, index and so on that is stored in an Oracle database belongs to a table space. The table space builds the bridge between the Oracle database and the file system in which the table's or index' data is stored.

There are three types of table spaces in Oracle:

•       Permanent table spaces

•       Undo table spaces

•       temporary table spaces

2.   Database Hierarchy

1.       Database Architecture

Physical: control, data, redo log, etc. files

Logical: tablespaces, segments, etc.

2.       Tablespaces

Place to store tables

Belongs to one db at a time

One or more OS files

One or more segments

Can be brought online “live”

Can be taken offline “live”

SYSTEM tablespace is important

3.       Datafile

Belong to one tablespace

DBA can change size

4.       Segments

One or more in a tablespace

Made up of one or more extents

Cannot span tablespaces

Can span multiple datafiles

Allocated for an object (e.g., table)

5.       Extents

Set of contiguous Oracle blocks; as segment grows extents are added; May not span datafiles

6.       Data Blocks

Finest level of granularity

Data stored in blocks

Smallest unit of storage that Oracle can allocate, read/write

One data block = multiple of OS block

DB_BLOCK_SIZE sets the size

3.   SYSTEM and Non-SYSTEM Tablespaces

SYSTEM Tablespace	Non-SYSTEM
Created with database contains: ·       Data dictionary     information ·       SYSTEM rollback     segment ·       SYS, SYSTEM objects ·       Should not contain user data	Tablespace contains: ·       Rollback segments ·       Temporary segments ·       Application data ·       Application indexes ·       Other user objects

4.   Create Table Space

1.     Permanent tablespace

·       create tablespace testtbs datafile ‘/u02/oradata/orcl/testtbs1.dbf’ size 50m autoextend on next 32m maxsize 2048m extent management local;

·       create tablespace datatbs datafile ‘/u02/oradata/orcl/data1.dbf’ size 100M autoextend on maxsize 200M extent management local;

2.     Temporary tablespace

·       create temporary tablespace tempnew tempfile ‘/u02/oradata/orcl/tempnew.dbf’ size 50m autoextend on next 32m maxsize 2048m extent management local;

·       Note: a temporary tablespace has tempfiles, not data files.

·       Making default temporary tablespace:-

alter database default temporary tablespace tempnew;

·       Dropping Temporary Tablespace:-

drop tablespace tempnew including contents and datafile;

3.     UNDO Tablespace

create undo tablespace undonew datafile ‘/u02/oradata/orcl/undonew.dbf’ size 100M;

alter system set undo_tablespace='UNDONEW' scope=both;

Drop tablespace UNDONEW including contents and datafiles;

5.      Renaming Table spaces

·       This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

alter tablespace users rename to usersnew;

·       The system and sysaux table space cannot be renamed, though.

·       However, read only table spaces can be renamed.

·       After renaming a table space, the control files should immediately be backed up.

6.      The system and sysaux tablespace

1.     SYSTEM Tablespace

·       The system tablespace is always available when a database is open (it cannot be taken offline).

·       The system tablespace stores the data dictionary (or their base tables, respectively).

2.     The sysaux tablespace

·       The sysaux tablespace is new with Oracle 10g. It is used to store database components that were stored in the system tablespace in prior releases of the database.

·       Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g.

·       Additionally, it is the place where automatic workload repository stores its information.

7.   Space management

·       Oracle maintains extents for a tablespace. There are two different methods for Oracle to keep track of free and used (occupied) extents:

·       Dictionary managed tablespace

·       Locally managed tablespace

1.     Dictionary-managed tablespaces:

·       Free extents recorded in data dictionary

·       Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

·       Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes against these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.

2.     Locally managed tablespaces:

·       Free extents recorded in bitmap

·       Each bit corresponds to a data block or group of blocks

·       Bit value indicates free or used

·       Reduced recursive space management (can occur in dictionary managed ts where consuming or releasing of one extent causes consuming or releasing of another)

·       Reduced contention on data dictionary tables

·       No rollback generated (since there is no update to the tables in the data dictionary)

·       No coalescing required

Ex:-

CREATE TABLESPACE user_data

DATAFILE ‘/u02/oradata/orcl/user_data.dbf’ SIZE 500M

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 10M [or AUTOALLOCATE];

·       In a locally-managed tablespace, space management tasks are handled by bitmaps stored within the tablespace itself. 

·       A bitmap is used to keep track of the block status in each datafile, whether they are free or used.  Each bit in the bitmap maps to a block or a group of blocks in the datafile.

8.   State of Tablespaces

·       A table space is either online (ready to be accessed) or offline (not accessible).

·       A table space becomes offline either when the DBA explicitly makes the tablespace offline, or when an error occurs while Oracle accesses a datafile within the tablespace.

·       Offline table spaces cannot be transported to other databases.

1.     Read Only Table spaces

·       Read only table spaces have the benefit that they need be backed up only once.

9.   Examples on Tablespaces and datafiles

1.     Adding Data Files to a Tablespace

                  Ex:-

ALTER TABLESPACE app_data ADD DATAFILE ‘/DISK5/app03.dbf’ SIZE 200M;

2.     Adding Data Files to a Temporary Tablespace

      Ex:-

ALTER TABLESPACE temp ADD TEMPFILE ‘/DISK5/app03.dbf’ SIZE 200M;

3.     Enabling Automatic Extension of Data Files

Ex:-

ALTER TABLESPACE app_data ADD DATAFILE ‘/DISK6/app04.dbf’ SIZE 200M

AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

4.     Changing the Size of Data Files Manually

Ex:-

ALTER DATABASE DATAFILE ‘/DISK5/app02.dbf’ RESIZE 200M;

5.     OFFLINE Status 

·       The tablespace that is offline is not available for data access.

·        The SYSTEM tablespace and any tablespace with active rollback  segments cannot be taken offline.

Ex:-

ALTER TABLESPACE app_data OFFLINE;

6.     The READ-ONLY Tablespace Status

Ex:-

ALTER TABLESPACE app_data READ ONLY;

7.     Dropping Tablespaces

Ex:-

DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES; 

8.     Dropping Datafiles

You can not drop the first datafile of a tablespace.

Ex:-

alter tablespace tty  drop datafile '/u01/oradata/tt3.dbf‘