UNDO File Management

Undo file management :

-->It consist of set of sql statement.starts with insert,update,delete and ends with commit and rollback

The data will commit when we 

         1.commited 

         2.exit

         3.close a session

         4.DDL Operation

The data will rollback when :

     
         1. rollback
         2.during the instance failure

         3.when auser process fails
         4.Insufficient space


-->It is composed of one or more files containing undo segments.for each oracle instance ,you only have toallocate enough disk space for the workload in that instance in an undo tablespace.


->rollback segments are still used but are internallycreated and maintained and are called undo segments.
->with automatic undo management,you cannot CREATE DROPor alter undo segments
->undo segments have the same structure as normal rollback segments but they.
->support automatic creation
->use a modified policy compared to oracle8I
->support dynamic extents transfer.
->SMON shrinks undo segments when needed.


->If yo use the rollback segment method of managing undospace,you are said to be operating in the manual undo management mode.

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS
UNDO_RETENTION=900


UNDO RETENTION:
->retention is specified in units of seconds.it is persistent and can survive system crashes .that is,
undo generated before an instance crash,is retained until its retention time has expired even across restarting the instance.
the oracle flashback features depend upon the availability of older undo information.

sys>>alter system set undo_retention=900;


STEPS to create and maintain the Undo tablespace:
1.create an undo tablespace:

sys>>create undo tablespace undo_02 datafile '/disk1/oradata/ORCL/undo_01.dbf' size 10m;

->An undo tablespace is a permanent,locally managed tablespace,read-write and in logging mode with default block size. values for
  MINIMUM EXTENT and default storage are system generated.

2.altering or resizing a datafile

sys>>alter database datafile '/disk1/oradata/ORCL/undo1.dbf' resize 10m;

3.Adding the undofile to undo tablespace

sys>>alter tablespace undotbs add datafile '/disk1/oradata/ORCL/undo01.dbf' size autoextend on;



SQL> alter system set undo_tablespace=UNDOTBS_1 scope=both;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     2700
undo_tablespace                      string      UNDOTBS_1


Set UNDO_RETENTION in the initialization parameter file.

    UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 2400;


SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.


->Different status of undo tablespace

   1.ONLINE
   2.OFFLINE
   3.PENDING OFFLINE

->Different status of undo segments :
    1.active
    2.unexpired
    3.expired

Retention Guarantee:

sys>>create undo tablespace undo_02 datafile '/disk1/oradata/ORCL/redolog1a.log'
                                                   size 10m retention guarantee;


Existing undo tablespace can also be made to comply with the quarantee by altering them ,as in
sys>>alter tablespace undo_02 retention gurantee;

Incase we dont want quarantee retention then

sys>>alter tablespace undo_02 retention nogurantee;

You can verify that the tablespace has guaranteed undo retention with :

sys>>select retention from dba_tablespaces where tablespace_name='undo_01';

To set undao tablespace  :

sys>>alter system undo tablespace=undo01;

show queries :
sys>>show parameter undo_retention;
sys>>show parameter und_tablespace;



for more information check the Data dictionary views:

DBA_UNDO_EXTENTS
DBA_SEGMENTS
USER_SEGMENTS
v$UNDOSTAT
v$TRANSACTION

Rollback Segment Management

Rollback segments :

ROllback segments stores undo information and are used for following purpose

1.To undo the privious command
2.For read consistency and
3.For crash recovery

Each rollment segment consists of:

->consistes of several rollback entries from multiple transaction.
->stores block information such as files and blocked,as well as data as it existed before being modified
->must be created under special circumstances and brought online before being used.
->may increase due to large transacton
->will automatically shrink to optimal if extented
->transaction can be assaigned automatically or explicity

-->Rollback segment maintains a table for every transaction,which is identified by SMON during recovery.when
you create database ,system tablespace and system rollback segment is created .you cannot drop this segment .depending on the number of
transaction you have to decide how amny rollback segments are needed. they are 2 types of rollback segments
   1.PUBLIC and
   2.PRIVATE

sys>>select segment_type,segment_name from dba_segments;

   
If we want to create a new rollback segment RBS1 in tablespace rbs_demo

sys>>create rollback segment rbs1 tablespace rbs_demo storage (initial 40k next 40k OPTIMAL 120k MINEXTENDS 2 maxextends 10);

->If you want to make it ONLINE or want to change storage parameters you have to use alter command as follows :

note :rollback segment cannot be created in a locally managed tablespaces.to crete rollback segment the tablespace
must be dictionary-managed tablespace

To convert a locally manged tablespace to dictionary managed

sys>>EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL ('RESDATA');


To change the storage parameters:

sys>>alter rollback segment rbs1 storage (maxextends 15);

To make it Online :
sys>>alter rollback segment rbs1(ONLINE/OFFLINE);

To bring rollback segments online automatically,set rollback_segments parameter in init<ORACLE_SID>,ora parameter file

to explcity assaign a rollback segment to a transaction

sys>>set transaction use rollback segment rbs1;


sys>>alter rollback segment rbs1 SHINK to 100k;


Droping a rollback segment
sys>>alter rollback segment rbs1 offline;
sys>>drop rollback segment rbs1

Redolog File Management

Redolog file management :

The purpose of checkpoint process to synchronize the buffer cache information with
the information on the disk

-->Redolog record all the block level updating made to the database.
  Every database must have at least two redolog groups and one file per group.

-->These files can be mirrored to avoid single point failure.

-->These are used by oracle during instance recovery and media failure.these files are written
  in circular fashion to save disk space.

-->The filled redolog files will be archived if the databse is running in archivedlog mode.

-->It is strongly recommended that database should archivelog mode
 
For example:

   If power failures abruptly and data in memory cannot be written on datafiles,however oracle  recovers the unrecorded data in datafiles by applying redologs.the process of applying the redolog during recovery operation is called as rolling forward.

Mirrored REDO Logs :

The recommended redolog file configuration is,at least 2 redolog members per group.

All memebers of a group of logfiles contain the same information.
Group member update simultaneously.
Each group may or may not contain the same number of members as other groups.


Log switches :
A log switches occurs when oracle switches from one redolog to another.
A log switc occurs LGWR has filled one log file group.
A log switch can be forced by a DBA when the current redo log needs to be archived.


At alog switch the current  redolog file is assigned a log sequence number that identifies the information stored
in that redolog and is also used for synchornization.A checkpoint automatically occurs at a logswitch.


Number of stages in redolog file

      1.UNUSED
      2.CURRENT
      3.ACTIVE
      4.INACTIVE

1. UNUSED: New creates redolog file.

2.CURRENT: Is the redolog file the log writter is currently writing to disk.

3.ACTIVE : when ever logwritter occurs the sattus of current redolog file changes to active and check point process is invoke.the checkpoint process invoke the and it take the snap shot of redolog file and stored it into at archive redolog file.after that the status of redolog file changes into inactive.
 

-->When ever a new data gets into database buffer cache it moves to redolog buffer and from redolog b-uffer to it moves to redolog file.

-->In case of 1st redolog file filled up automatically a log switch occurs and a log sequence number is
generated is also know as (LSN).

-->oracle generates log sequence number to uniquely identify every log switch status.if the second file is alsofilled up logwriter starts overwriting the first redolog file.therefore recovery of entire of data impossible from theredolog file.


-->by default archivelog mode is disabled


 To check the name of the database instance

sys>>select name from v$database;

 To check the archive log status

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

Steps to enable the archive log mode;

Step 1: Shutdown the database
  sys>>shutdown immediate;

Step 2: Edit the p file

  $cd $ORACLE_HOME/dbs

  $vi init$ORACLE_SID.ora

   log_archive_dest=/disk1/oradata/ORCL/arch/

   :wq (save and exit)

   $mkdir -p /disk1/oradata/ORCL/arch

Step 3: sqlplus / as sysdba
  sys>>startup mount;

Step 4 : Enable the archive log mode
  sys>>alter database archivelog;
 

Step 5: sys>>alter database open;

 

 Check the status of databse and archivelog.

sys>>select status from v$instance;

sys>>select log_mode from v$database;

sys>>archive log list;

        

 To switch logfile

sys>>alter system switch logfile;


sys>>select GROUP#,members,sequence#,archived,status from v$log;

sys>>select group#,bytes/1024/1024 from v$log;


 ADDING redolog GROUP:

sys>>alter database add logfile group 3 ('/disk1/oradata/ORCL/redolog3a.log',
                                         '/disk1/oradata/ORCL/redolog3a.log') size 4m;


 ADDING redolog file  :

sys>>alter database add logfile member '/disk1/oradata/ORCL/redolog2b.log' to group 2;

 

 To rename redolog file :

Bring the database to mountstate

Step 1: Sshutdown the database
  sys>>shutdown immediate;

Step 2: Copy and move the redolog file to different location

  $cp /disk1/oradata/ORCL/redolog2a.log /disk2/oradata/ORCL/redolog2a.log

Step 3: Bring the databse to mount
  sys>>startup mount;

Step 4: Alter database rename file '/disk1/oradata/ORCL/redolog2a.log'
                          To '/disk1/oradata/ORCL/redolog2new.log';

Step 5: Open the database
  sys>>alter database open;

 To clear Online redolog files

sys>>alter database clear unarchived logfile group 2;

Dropping a redolog group :

sys>>alter database drop logfile group 3;

 Dropping a logfile from Group
sys>>alter database drop logfile member '/disk1/oradata/ORCL/redolog2a.log';


Note :
we cannot drop a file which is active or current

SQL> select group#,members,status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE


for more information check the Data dictionary views :

V$LOG
V$LOGFILE
V$LOG_HISTORY
V$LOGHIST
V$RECOVERY_LOG
V$ARCHIVED_LOG

ARCHIVED Redolog File Management

ARCHIVED redolog files:

-->It is the dba's whether to run the database in ARCHIVELOG mode or in NOARCHIVELOG mode

-->If the database is runing in archivelog mode ,contents of the online redolog files are
copied to an archive area by an oracle background processes archiver (ARCn)there archive file
is know as archived redolog files,archived redologs or simply archived redo

-->These files are sometimes referred to as the offline redolog files since they are not open
during normal opertaion of the database and are required only during recovery.

-->The redolog files(online and archived)are essential for database recovery since they contain
information on all changes  made to the databse.

-->If the database is chosen to operate in noarchivelog mode online backups and database recovery
will not be possible

  To convert the database into archivelog mode

Step 1: shutdown the database
sys>>shutdown immediate;

Step 2: Edit the pfile and add the parameter

  $cd $ORACLE_HOME/dbs
  
   log_archive_dest=/disk1/oradata/ORCL/arch
  
   :wq  (save and quit)
  
Step 3: Create the folder with name arch

   $mkdir -p /disk1/oradata/ORCL/arch

Step 4 : Check the archivelog status

  sys>>startup mount;

Check the archive log list status :

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

  if the status noarchivelog_mode type the below command

  sys>>alter database archivelog;

Step 5: Open the Database

  sys>>alter database open;


 for more info check the  Data dictionary views

  v$ARCHIVED_LOG
  v$ARCHIVE_DEST
  v$ARCHIVE_PROCESSES

Control File Management

Control file management :

1.Backing up control file

       A.Backup to binary file
       B.Backup to human read able format
 

Backup control file to binary format

sys>>alter database backup controlfile to '/disk1/oradata/backup/control.ctl';
 

 Backup control file to txt format

sys>>alter database backup controlfile to trace;

 Backup control file to txt format to particular path

sys>>alter database backup controlfile to trace as '/disk1/oradata/backup/control.ctl';

Multiplexing control file :

Step 1: shutdown the database
    sys>>shutdown immediate;

Step 2: Go to the controlfile location and copy the controlfile to new location

   $cd /maggy/app/oracle/ORCL
   $mkdir backup
   $cp control.ctl backup/

Step 3: Edit the pfile (add the new control file path in pfile)
  $cd $ORCALE_HOME/dbs

  control_files='/maggy/app/oracle/ORCL/control.ctl','/maggy/app/oracle/ORCL/backup/control.ctl'

  :wq

Step 4: sqlplus / as sysdba
   start the database

   sys>>startup

Demultiplexing control file:

Step 1: shutdown the database
    sys>>shutdown immediate;

Step 2:Edit the pfile (remove the newcontrol file path in pfile)
  $cd $ORCALE_HOME/dbs

  control_files='/maggy/app/oracle/ORCL/control.ctl'
  :wq

Step 3: sqlplus / as sysdba
   start the database

   sys>>startup

check the path of the control file

sys>>select name from v$controlfile;

sys>>show parameter control_files;


for more info check Data dictionary views:

v$CONTROLFILE
v$CONTROLFILE_RECORD_SECTION

Schema Management

Schema Management :

Visible and invisble Indexes :

Indexes can be visible or invisible , an invisble index is maintained by DML operations and cannot be
used by the optimizer. making an index invisible is an alternative to making it unusable or dropping it.

Oracle 11g New Feature: Invisible index advantages for performance tuning

Oracle 11g introduced a new feature called invisible indexes. We can make an index to invisible mode when it is not needed. Once we make an index to invisible the index will not come to optimizer reach. The syntax for making an index to invisible is as below.



ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

Invisible index is maintained by Oracle database but, ignored by the optimizer unless you specify explicitly.
How to check the visibility parameter of an index?
It can be identified from VISIBILITY column of the dba_indexes table.

SQL>select index_name, visibility from dba_indexes where index_name='EXAMPLE_INVI_IDX';

INDEX_NAME VISIBILIT
------------------------------ ---------
EXAMPLE_INVI_IDX VISIBLE


Creating an invisible index:

sys>>create INDEX emp_ename ON emp(ename)
tablespace users
storage (initial 20k
next 20k
pctincrease 75)
invisible;

Making an index invisible

sys>>alter index emp_ename INVISIBLE;

 Making invisble index to visible

sys>>alter index emp_ename visible;

  To check an index or Invisble or visble

sys>>select INDEX_NAME,VISIBILITY from users_INDEXES;


Read Only tables;

In oracle 11g, can place a table in read only mode by merely executing alter table read only statements

 Creating a table

sys>create table emp (a number);

sys>>insert into emp values (1);

>>commit;

  Using alter table_read only statement

sys>>alter table emp read only;

note:
cannot insert ,update,delete contents in the read only table.


sys>>select table_name,read_only from user_tables;

 Table back to read write

sys>>alter table emp read write;


DDL Wait Options:

sys>>alter session set ddl_lock_timeout=10;

sys>>alter table sales add(taxe_code varchar1(10));


Virtual columns:

If sales_amt           and sale_amt is less     then sale_category
is more than           that or equal to             is

 0                            1000                             LOW

 10001                    100000                         MEDIUM

 100001                  1000000                       HIGH

 1000001                unlimited                     ULTRA




 sys>>create table sales
        (
       sales_id     number,
       cust_id      number,
       sales_amt   number,
           sale_category  varchar2(6)
       generate always as
       (
         case
            when sales_amt <= 10000 then 'LOW'
        when sales_amt > 10000  and sales_amt <= 100000 then 'MEDIUM'
        when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
        else 'ULTRA'
        end
        ) virtual
        );



OLTP TABLE Comparison:

BASIC COMPARISON:

sys>>create table emp (tno number) compress basic;
sys>>create table dept (sno number) compress;


sys>>select user_table,compression,compress_for from user_tables;


OLTP compression:

sys>>create table sr1 (no number) compress for OLTP;

sys>>select user_table,compression,compress_for from user_tables;


Disable the compress the table;


sys>>alter able <tablename> nocompress;

sys>>alter table emp nocompress;

Steps to Rename a Datafile

Renaming the datafile :

There are 2 methods to rename the datafile

     1.Shutdown database
     2.Alter status OFFLINE

Method 1 :

Step 1:

shutdown the database

sys>>shutdown immediate

Step 2:

At the o/s level copy the datafiles to new location

    $cd disk1/oradata/ORCL
    $mkdir backup
    $cp * backup/

Step 3:

sys>>startup mount

Step 4 :

alter database rename file '/disk1/oradata/ORCL/user01.dbf' to '/disk1/oradata/ORCL/user01.dbf'

Step 5:

open the database

  sys>>alter database open;


check the status :

sys>> select filename,tablespace_name from dba_data_files;

Method 2 :

Step 1: Make the particular tablespace offline taht the datafile belogs to..
   
    sys>>alter tablespace users OFFLINE;

Step 2: At the o/s level copy the datafiles to new location
  

  $cd disk1/oradata/ORCL
    $mkdir backup
    $cp * backup/

Step 3: 

alter tablespace users rename datafile '/disk1/oradata/ORCL/user01.dbf' to '/disk1/oradata/ORCL/user01.dbf'

Step 4: 

Make the particular tablespace ONLINE

  sys>>alter tablespace users ONLINE;


check the status:
sys>> select filename,tablespace_name from dba_data_files;

Creating the Temporary TableSpace

Creating the Temporary TableSpace:

How to create the temporary tablespace :

-->Use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace, which is an allocation of space
in the database that can contain schema objects for the duration of a session. If you subsequently
assign this temporary tablespace to a particular user, then Oracle will also use this tablespace for
sorting operations in transactions initiated by that user.

Creating the Temporary TableSpace:

sys>>CREATE TEMPORARY TABLESPACE temp1
                    TEMPFILE '/disk1/oradata/ORCL/temp01.dbf' SIZE 5M AUTOEXTEND ON;

Adding the tempfile to temporary tablespace:

sys>>alter tablespace temp1 add tempfile '/disk1/oradata/ORCL/temp01.dbf' size autoextend on;

Increasing the size of the tempfile :
  
sys>>alter database tempfile '/disk1/oradata/ORCL/temp01.dbf' resize 10m;

 Droping the temp file :

sys>>alter tablespace temp1 drop tempfile /disk1/oradata/ORCL/temp01.dbf';


sys>>select filename,tablespace_name from dba_temp_files;

for more information check the data dictionary views
 
sys>desc dba_temp_files;

To make the tablespace default temporary tablespace :

sys>>alter database default temporary tablespace temp1;

Creating the UNDO Tablespace

Creating the Undo Tablespace :

How to create undo tablespace :

->Every Oracle Database must have a method of maintaining information that is used to roll back,

or undo, changes to the database. Such information consists of records of the actions of

transactions, primarily before they are committed. These records are collectively referred

to as undo.

sys>>show parameter undo_tablespace;

sys>>create undo tablespace undotab datafile '/disk1/oradata/ORCL/undotab.dbf' size 10m;

SQL> show parameter undo;

NAME                                 TYPE          VALUE

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

undo_management                string           AUTO

undo_retention                       integer        2700

undo_tablespace                    string           UNDOTBS

Creating the undo tablespace :

SQL> create undo tablespace undotbs_1 datafile ''/disk1/oradata/ORCL/undotab1.dbf'' size 10m;

Tablespace created.

SQL> alter system set undo_tablespace=UNDOTBS_1 scope=both;

System altered.

SQL> show parameter undo;

NAME                                 TYPE           VALUE

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

undo_management                 string           AUTO

undo_retention                       integer          2700

undo_tablespace                      string          UNDOTBS_1

Set UNDO_RETENTION in the initialization parameter file.

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 2400;

Dropping a tablespace:

SQL> drop tablespace undotbs including contents and datafiles;

Adding the undofile to undo tablespace:

sys>>alter tablespace undotbs add datafile '/disk1/oradata/ORCL/undo01.dbf' size autoextend on;

Increasing the size of the undofile:

sys>>alter database datafile '/disk1/oradata/ORCL/undo1.dbf' resize 10m;

Tablespace Management

Tablespace Management :

-->A tablespace is logical group of datafiles in a database.

-->A database is divided into one or more logical storage units called "Tablespace"
-->A tablespace must have atleast one datafile in a file system which is located physically.

 Server --> Oracle version --> database --> Tablespace --> Datafiles

 ->when a existing datafile in a tablespace completely filled up then add a extra datafile or extend the existing datafile


Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

                                                      Datafiles and Tablespaces

       

Databases, tablespaces, and datafiles are closely related, but they have important differences:

• An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database's data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
  
  
• Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle Database is running.
  
  
• A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
  

Oracle-Managed Files:

Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames. Oracle Database internally uses standard file system interfaces to create and delete files as needed for the following database structures:

• Tablespaces
• Redo log files
• Control files

Through initialization parameters, you specify the file system directory to be used for a particular type of file. Oracle Database then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

Allocate More Space for a Database:

 The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.

You can enlarge a database in three ways:

• Add a datafile to a tablespace
• Add a new tablespace
• Increase the size of a datafile

When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace. Figure 3-2 illustrates this kind of space increase.

                         Enlarging a Database by Adding a Datafile to a Tablespace 


             

 ==>Alternatively, you can create a new tablespace (which contains at least one additional datafile) to increase the size of a database

        Enlarging a Database by Adding a New Tablespace

                                                       The third option for enlarging a database is to change a datafile's size or let datafiles in existing tablespaces grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension properties 

              Enlarging a Database by Dynamically Sizing Datafiles

-->when a create a tablespace by default it is
   ONLINE ,READ WRITE, loging =yes ,pluggedin =no, permanent

-->we can transport the tablespace(introduced in oracle 8i) from one database to another database

-->we cannot drop the tablespace which holds the data, still we can drop the table space
  which holds data by command
  sys>>drop tablespace <tablespace name> including contents;

->Based on content we are having permanent,Temporary, and undo tablespace

->The number of dataspace we required in database depends on the number of application
  that we support
->depending on storage we have two tablespaces

   BIG FILE-- can have multiple datafile (default) size can grow upto 128TB

   SAMLL FILE -- can have the only datafile  --1024 datafiles can have in small file dataspace


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

Practicals for tablespace management :

To check the datafile path

sys>>select name from v$datafile
sys>>select name from v$controlfile
sys>>select member from v$logfile

Creating tablespace

sys>>create tablespace ts1 datafile '/disk1/oradata/ORCL/ts1.dbf' size 100m;

sys>>desc dba_tablespaces

sys>>select tablespace_name from dba_tablespaces;

sys>>select tablespace_name,status,contents,logging,plugged_in,BIGFILE from dba_tables;
 

                   To make tablespace OFFLINE

sys>>alter tablespace ts1 Offline;

                   To make tablespace READONLY

sys>>alter tablespace ts1 readonly;

                    To make tablespace READ WRITE;

sys>>alter tablespace ts1 read write;

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

                   Enlarging a Database by Adding a Datafile to a Tablespace

sys>> alter tablespace ts1 add datafile '/disk1/oradata/ORCL/ts2.dbf' size 4m;

sys>> select filename,tablespace_name from dba_data_files;

sys>> desc dba_data_files

sys>>select filename,tablespace_name,bytes/1024/1024 from dba_data_files;


                 Enlarging a Database by Dynamically Sizing Datafiles

sys>> alter database datafile '/disk1/oradata/ORCL/ts1.dbf' resize 10m;

                Enlarging a Database by  automatically when the datafile get filled

sys>> alter database datafile '/disk1/oradata/ORCL/ts1.dbf' autoextend on maxsize 10m;

                
               Enlarging a Database by Adding a New Tablespace 

sys>>create tablespace users datafile ' /disk1/oradata/ORCL/users1.dbf ' size 4m;

 How to drop a datafile

sys>> alter tablespace drop datafile '/disk1/oradata/ORCL/ts1.dbf'
 

 Rename the tablespace

sys>>alter tablespace ts1 rename to ts2;

 Rename the datafile
          
sys>>ALTER TABLESPACE ts2
    RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
                    '/u02/oracle/rbdb1/user2.dbf'
                 TO '/u02/oracle/rbdb1/users01.dbf',
                    '/u02/oracle/rbdb1/users02.dbf';



                   To make the default tablespace for a datafile

sys>> alter database default tablespace ts2;

sys>>select * from database_properties;


sys>>alter dabase default temporary tablespace temp1;


                  Rename a temporary tablespace:

sys>>alter tablespace temp1 rename to temp2;



**Note :
-->The system datafile cannot be drop,cannot rename, and offline

-->cannot rename ,drop the sysaux datafile.

 

Creating an ORACLE 11g Database with the CREATE DATABASE Statement

Creation of database

There are 3 types to create the database:

 1.Creating database Manually.
 2.Creating database using OSMPF.
 3.Creating database using DBCA command

Creating a Database with the CREATE DATABASE Statement:

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.
If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features.
The instructions in this section apply to single-instance installations only. Refer to the Oracle Real Application Clusters (Oracle RAC) installation guide for your platform for instructions for creating an Oracle RAC database.

Note:

Single-instance does not mean that only one Oracle instance can reside on a single host computer. In fact, multiple Oracle instances (and their associated databases) can run on a single host computer. A single-instance database is a database that is accessed by only one Oracle instance, as opposed to an Oracle RAC database, which is accessed concurrently by multiple Oracle instances on multiple nodes. See Oracle Real Application Clusters Administration and Deployment Guide for more information on Oracle RAC.

 Creation of database:

Steps to create the database manually

STEP1 : 

Edit the .bash_profile
 
    $vi .bash_profile

       ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
       ORACLE_BASE=/maggy/app/oracle; export ORACLE_BASE
       ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
       ORACLE_SID=test; export ORACLE_SID
       ORACLE_TERM=xterm; export ORACLE_TERM
       PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH; export PATH


    :wq  (save and quit)
 
   check the oracle_home,oracle_sid,oracle_base
   $echo $ORACLE_HOME
   $echo $ORACLE_SID
   $echo $ORACLE_BASE

STEP 2: 

 cp init.ora init$ORACLE_SID.ora

STEP 3:

Edit the init$ORACLE_SID.ora file
 
    $cd $ORACLE_HOME/dbs
 dbs$vi init$ORACLE_SID.ora
     db_name='client'
     diagnostic_dest=/disk1/oradata/client
     undo_tablespace='UNDOTBS'
     undo_management=auto
     control_files =/disk1/oradata/client/cont.ctl

    :wq   (Save and Quit)

STEP 4:

  login to sql ideal instance
   $sqlplus / as sysdba

   sys>>startup nomount
   

STEP 5:

 Create the database script

   $vi dbcreate.sql

   create database "client"
   datafile '/disk1/oradata/client/system.dbf' size 100m autoextend on
   sysaux datafile '/disk1/oradata/client/sysaux.dbf' size 100m autoextend on
   undo tablespace undotbs datafile '/disk1/oradata/client/undotbs.dbf' size 100m autoextend on
   default tablespace userdata datafile '/disk1/oradata/client/userdata.dbf' size 100m autoextend on
   default temporary tablespace temp '/disk1/oradata/client/temp.dbf' size 100m autoextend on
   logfile
   group 1('/disk1/oradata/client/redo1.log' size 5m),
   group 2('/disk2/oradata/client/redo2.log' size 5m)
   controlfile reuse;

   :wq     (save and quit)


STEP 6: 

Create data dictionary views

   $vi postdb.sql

   @$ORACLE_HOME/rdms/admin/catalog.sql
   @$ORACLE_HOME/rdms/admin/catproc.sql
   connect system/manager
   @$ORACLE_HOME/sqlplus/admin/pupbld.sql

   :wq     (save and quit)


STEP 7:

sqlplus / as sysdba
 
  sys>>startup nomount
  sys>>@dbcreate.sql
 
  check the status of the database

  To check the status of the database

  sys>>select status from v$instance;
       status--open

  To check the database name
  sys>>select name from v$database;
  
  To check the patch of the datafile

  sys>>select name from v$datafile;
 
  To check the path of the redolog file

  sys>>select member from v$logfile;


  sys>>@postdb.sql

  sys>> show parameter user_dump_dest

  sys>>show parameter background_dump_dest



  for more information check the data dictionary views

  v$SGA
  v$INSTANCE
  v$DATABASE
  v$PROCESS
  v$SYSAUX_OCCUPANTS

Creating an Oracle 11g Database using DBCA

Creating an Oracle 11g Database using DBCA

 In this example, I’ve already installed the Oracle Database 11gR2 software (11.2.0.4 in this example) on one of my Linux 6.4 machines, and I’m going to create a new database instance simply called ‘ORCL’ (as it’s going to be used for my EM12c database repository) using the DBCA tool.

The data files will be stored under the /u02/oradata/{DB_NAME} directory, setup by ‘root’ as follows:

Create the directory and give the permission

mkdir -p /ramu/oradata/ORCL
chown -R oracle:oinstall /ramu
chmod -R 775 /ramu

Start the Database Configuration Assistant (DBCA)

Open an X-Windows session to the server as the ‘oracle’ user.

Set your ORACLE_HOME environment variable appropriately, then start the assistant by running the following:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4

$ORACLE_HOME/bin/dbca

Once the GUI starts, respond as follows:

• Welcome
  • Next
• Step 1 of 12: Operations
  • Create a Database
  • Next

• Step 2 of 12: Database Templates
  • General Purpose or Transaction Processing
  • Next

• Step 3 of 12: Database Identification
  • Global Database Name: emrep (or any name you want providing it’s no more than 8 characters long, and starts with an alphabetic character)
  • SID: emrep
  • Next

• Step 4 of 12: Management Options
  • Tick/Untick ‘Configure Enterprise Manager’ according to your needs.

If you do want to configure Enterprise Manager locally, and a listener hasn’t yet been configured, you’ll be presented with a warning about having to configure one before being able to continue.  If you need to create a listener service, take a look at this post.

• Leave everything else as default
• Next

• Step 5 of 12: Database Credentials
  • Use the Same Administrative Password for All Account (unless you’d rather not)
  • Password: ******
  • Confirm Password: ******
  • Next

• Step 6 of 12: Management Options
  • Specify storage type and locations for database files
  • Storage Type: File System
  • Use Common Location for All Database Files
  • Database Files Location: /u02/oradata
  • Next

• Step 7 of 12: Recovery Configuration
  • Tick Specify Fast Recovery Area
  • Fast Recovery Area: {ORACLE_BASE}/fra
  • Tick Enable Archiving
  • Click on Edit Archive Mode Parameters…
    • Archive Log File Format: %t_%s_%r.arc
    • OK
  • Next

 

• Step 8 of 12: Database Content
  • Optionally, check ‘Sample Schemas’ (this will create an ‘EXAMPLE’ tablespace and some sample schemas).  I recommend leaving this unticked if this is to become a live environment.
  • Next

• Step 9 of 11: Initialization Parameters
  • Use a Typical configuration
  • Tick ‘Use Automatic Memory Management’
  • Change the ‘Memory Size’ value accordingly (or leave default as 40% of total machine memory)
  • Review any additional parameters by clicking ‘All Initialization Parameters’
  • Next

   

• Step 10 of 11: Database Storage
  • Check storage locations/options and update accordingly if required
  • Next

    

• Step 11 of 11: Creation Options
  • Optionally tick ‘Generate Database Creation Scripts’ if you prefer to keep it for future reference.
  • Finish
• Confirmation
  • Check the ‘Create Database – Summary’ details
  • OK

   

• Once the database has been created, note the message about all accounts being locked with the exception of SYS and SYSTEM.

If you need to create a listener service, take a look at this post.

Creating and Managing a Database with DBCA

 http://docs.oracle.com/cd/E11882_01/server.112/e10897/install.htm#ADMQS023

 

Steps Involve to Startup Database

Steps Involve to Startup Database 

Startup nomount:

->oracle will read the parameters file , the parameter file contains the SGA Parameters.
->SGA will allocate
->Will start the mandatory Backgrounf process.
->It will create the file called alert log file , contains the database activities

 diagnostic_dest  path the alert log file will create .

 ->Starting the instance


 Startup Mount:

 ->During mount state it reads the control file.
 ->Opens the controlfile to obtain the names and location of datafile and redolog files.
 ->In mount state can rename the datafiles.
 ->can enable or disable the archivelog
 ->can perform the recovery.


 Alter database open :

 ->It check and verify the control file and redolog file is present physical location or not .
 ->open the redolog and control files.



                                       
                                              |
                                              |OPEN
                                              |                                       
                                   ---------
                                 |
                                 | mount
                     - ---------
                   |
                   |Start nomount
     -----------
     Shutdown


Shutdown Process :

there are 4 types of shutdown process is there
   1.Shutdown Normal
   2.Shutdown Transactional
   3.Shutdown Immediate
   4.Shutdown Abort


  1.Shutdown Normal :

   ->No new connection are allowed.
   ->Server waits for all the connected users to disconnect before shutting down the DB.
   ->Buffer information (DBBC,redolog Buffer) will be written into the Disk.
   ->SGA memory allocation will be released.
   ->Terminated the Background Process.
   ->database is closed and dismounted

    Syntax: sys>>shutdown or shutdown normal
   ->It is default options


  2.Shutdown transactional:
  
   ->No new connection are allowed.
   ->Existing users cannot start the new transaction
   ->Connected users will be disconnect the session as soon as the current transaction is ends.
   ->shutdown proceeds all the transaction are completed
   ->Buffer information (DBBC,redolog Buffer) will be written into the Disk.
   ->SGA memory allocation will be released.
   ->Terminated the Background Process.
   ->database is closed and dismounted

    Syntax: shutdown transactional

  3.Shutdown Immediate :
 
   ->No new connection are allowed
   ->users will be terminate immediately & sql statements in process will be terminated
   ->Oracle will rollback the active transaction.
   ->Buffer information (DBBC,redolog Buffer) will be written into the Disk.
   ->SGA memory allocation will be released.
   ->Terminated the Background Process.
   ->database is closed and dismounted

     Syntax : sys>>shutdown immediate

  4.Shutdown abort :
   
    ->No new connection are allowed
    ->users will be terminate immediately & sql statements in process will be terminated
    ->uncommited transaction will be rollback
    ->Buffer information (DBBC,redolog Buffer) will be written into the Disk.
    ->instance will be terminated without closing the file
    ->database notclosed and dismounted.
    ->During the next startup system monitor system monitor(SMON) will perform instance crash recovery(ICR)

    Syntax : sys>> shutdown abort

    Note : we cannot take the consistance backup once we shutdown the DB with shutdown abort command


  

Select Statement Work Flow

Select Statement Work Flow :

-->User fire the sql statement for firsttime the process is called "Hard parsing'.
-->User fire the sql statement for second time the process is called "Soft parsing".


->User process will create the user process cannot communicate to the server directly .
->Server process will communicate to user process and create the session called session sort (private
global area)

->Only server process can access the PGA(Private Gobal Area)

library cache create a

     1.SQL ID /Statement ID -- Alpha numeric

     2.Hash Value will generate for the sql statement -- Numeric

     3.Parsing:
    
     Syntax check  -- command is correct or not
     Sematic Check -- It will check the users,Priviliges,tables present in DB or not

     4.Optimization:

        A.Query Transformer -- Rewrite the given query to different ways )
        B.Cost Estimator       -- It will calculate the CPU usage ,Memory usage ,I/O, Time
                                              to execute for all the queries
        C.Plan generator        -- It generate some plan (execution plan for the given
                                              sql statement to get the data from disk).
        D.Optimizer               -- It will choose the best execution plan based on Cost estimator calc.                                                  
  The Whole process is called "Hard Parsing".

  If the second time user fire the same query is called the "Soft Parsing".

Detail Notes on Components of SGA

Detail Notes on Components of SGA

 Mandatory Components :

 

  1.DBBC(Database Buffer cache)

  2.Shared Pool

  3.Redolog buffer

1.Database Buffer cache :

-->An area in memory where Oracle keeps recently used data blocks so that they do not need to be
constantly reread from disk.

-->Database buffer is classified into 3 components

              A.Dirty Buffer
              B.Dinned Buffer
              C.Free Buffer


A.Dirty Buffer : A dirty buffer refers to blocks in the database buffer cache that are changed,
                          but are not yet written to the disk.

       >    It is modified but not written into the disks.

B.Dinned Buffer : It is currently active task view

C.Free Buffer : It contains no data.

==> Parameter to increase the database cache
       "db_cache_size"

2.Shared Pool:

-->The area in the SGA that contains the data dictionary cache and shared parsed SQL statements.

-->It classified into components called
               A.Data Dictionary Cache
               B.Library cache

A.Data Dictionary Cache  : 

 -->It contains the all system related information.
 -->The data dictionary cache is a key area to tune because the dictionary is accessed so frequently,
 especially by Oracle's internals of Oracle. At startup, the data dictionary cache contains no data.

B.Library Cache :

-->Recently executed SQL statements

--> The Library Cache is a piece of memory within the SGA that Oracle uses in order to store
SQL Statements. Whenever a Process issues an SQL Statement, the text of the Statement goes into the Library Cache where the statement is parsed an validated. If for example I do a insert into city
(name, abbr) values ('Geneva', 'GE');. the Library Cache checks if there is a table named city having
the columns name and abbr. As an additional task, the Library Cache also checks if the user's
privileges are sufficient to execute the statement.

-->In a similar way, the Library Cache also caches PL/SQL Statements and Objects

==> Parameter to increase the database cache(only can increase the size of shared pool)
       "shared_pool_size"

3.Redolog Buffer :