Cloning Using Rman

1. Creating Catalog DB called “Cloning Using Rman Buckup”.

2. Register the target DB “Source “with catalog db “rman_buckup” using rman client.

3. Configure and full back up the target db “source” using rman catalog mode.

4. Now we are going to duplicate the target db “source” using rman catalog. Duplicate database name is DEST

A. Create all necessary folders on duplicate database location “dest”
     Bdump, cdump, udump, pfile, create
B. Backup spfile or copy pfile from target db “source” to duplicate db “dest” location.
    SQL> Create pfile=’/oracle/admin/dest/pfile/initdest.ora’ from spfile;
Or
   $ cp ‘/oracle/admin/source/pfile/initsource.ora’ ‘/oracle/admin/dest/pfile/initsdest.ora’

5. Edit the parameter file and add additional parameters in dest db pfile

  Replace db_name=source to db_name=dest

  Replace all location (path) names to duplication database location

  Include additional parameters in the duplication “dest” database pfile

  Db_file_name_convert= (‘/oracle/oradata/source’, ‘/oracle/oradata/dest’)

  log_file_name_convert= (‘/oracle/oradata/source’, ‘/oracle/oradata/dest’)

6. Create a password file for duplicate database “dest”

  $ orapwd file=’/u01/app/oracle/product/9.2.0/dbs/PWDdest.ora’ password=dest entries=5

7. Make an entry in TNSNAME and LISTNER for “dest” database

8. Reload the listener : lsnrctl reload

9. Now login and connect the “dest” database in nomount stage

    $ sqlplus "sys@dest as sysdba"
    SQL*Plus: Release 9.2.0.1.0 - Production on Sat Jun 21 19:05:53 2008
    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
    Enter password:
    Connected to an idle instance.
    SQL> startup pfile='E: \oracle\admin\dest\pfile\initdest.ora' nomount;
    ORACLE instance started.
    Total System Global Area 97589952 bytes
    Fixed Size 453312 bytes
    Variable Size 46137344 bytes
    Database Buffers 50331648 bytes
   Redo Buffers 667648 bytes

 · Leave this session alive and move to rman session

10. Invoke the rman session and connect the catalog db, target db and duplicate db

 $ rman catalog rman/rman@rman target sys/admin@source auxiliary sys/admin@dest
 Recovery Manager: Release 9.2.0.1.0 - Production
 Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
 connected to target database: SOURCE (DBID=596212533)
 connected to recovery catalog database
 connected to auxiliary database: dest (not mounted)

 OR another way to connect rman

  $ rman

   Recovery Manager: Release 9.2.0.1.0 - Production
   Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved

 
   Rman> connect catalog rman/rman@rman
               connected to recovery catalog database
   Rman> connect target sys/admin@source
                connected to target database: SOURCE (DBID=596212533)
   Rman> connect auxiliary sys/admin@dest
               connected to auxiliary database: dest (not mounted)

11. Now Execute the below script to clone the new database.

   RMAN> run {allocate auxiliary channel c1 type disk;
          2> duplicate target database to dest};

12. After executing the script, rman start execute some internal script to create instance for the duplicate database and also restore target database data files to the duplicate database location, then start applying archive logs from target database backup locations to duplicate database locations.

13. While run this script, rman itself internally run some scripts to allocated instance for the duplicate database. In middle of the script running that is “executing script: Memory Script “rman may stop execute further. So no need to worry, leave the session remain or close it. Move to your duplicate “dest” database session and execute the below script.

        SQL> alter database open resetlogs;
        Database altered.

Before that ensure all files are restore properly to duplicate database location “dest”

        SQL> select name,dbid from v$database;
        NAME DBID
       --------- ----------
       DEST 596212533

Both database source and dest database ID will be remain same. 

So we need to change the dbid in dest db using DBNEW ID utility.

Backup Using Rman

What is Rman?
Recovery Manager is a tool that manages the process of creating backup and also
Manages the process of restoring and recovering
The recovery catalog can also hold RMAN stored Scripts, sequences for common backup tasks. Centralized storage of scripts in recovery catalog can be more convenient than working with command files

Why use Rman ?
§ RMAN is an intelligent tool
§ Maintains repository of backup metadata.
§ Remembers backup locations
§ Knows what needs backup set locations
§ Knows what needs to be backed up
§ Knows what is required for recovery
§ Know what backups are redundant
§ It handles database corruptions
§ Parallel operation are supported
§ Better Querying facility for knowing different details of backup.
§ No Extra redo generated when backup is taken. Compared to online backup
§ Proper Security
§ It contains details of backup taken in the central repository

The Rman Architecture
RMAN This could be present and fired even from client side
Target This is the database which needs to be backed up
Auxiliary This is the database which needs to duplicate
Catalog Catalog is optional otherwise backup details are stored in target database control file.

It is a repository of information queried and updated by Recovery Manager
It is a schema or user stored in Recovery Manager
One schema can support many databases
It contains information on Physical schema of Target Database data file and archive log,

backup sets and pieces
Recovery Catalog is must in following scenarios
- In order to store scripts For Table space Point in time recovery

Media Management Software
Media management software is must if you are using RMAN for storing backup in Tape drive directly
Image copy

The advantage of image copy is it is not in RMAN proprietary format
Backup Format
RMAN backup is not in oracle format but in RMAN proprietary format.
Oracle backup comprises of backup sets and consists of backup pieces
Backup sets are logical entity
In Oracle 9i it gets stored in default locations ($ORACLE_HOME/dbs)
There are two types of backup sets

§ Datafile backup sets

§ Archivelog Backup sets
One more important point of data file backup sets is it do not include empty blocks,
A backup set will contain many backup pieces
A single backup piece consists of physical files which are in RMAN proprietary format.

Types Backups in Rman
§ Complete backup
§ Incremental backup
§ Image Copy (not in Rman nature)
These backup are of RMAN proprietary nature
Connecting Rman
1. OS authentication
2. Password File authentication

1. OS authentication
] export ORACLE_SID=targetdb
] Rman catalog mark/mike@catdb target /

2. Oracle Net authentication*
Way 1 ->] Rman target sys/sys@dbname catalog mark/mike@catdb
Way 2 ->] Rman
Rman] connect catalog mark/mike@catdb
Rman] connect target sys/sys@dbname
* TNS and LISTENER must be configured and password file must be அவைலப்லே

RMAN configuration parameters
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F: \ORACLE\ORA92\DATABASE\SNCFMUB.ORA';

Temp and Undo Tablespace

Temporary Tablespace
Default Temporary Tablespace
Temporary Tablespaces are used to manage space for database sort operations
For e.g.: if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:
Syntax:
Create temporary tablespace temp_name tempfile ‘filespec’ size 50m extent management local uniform size 16m;
Or we can assign the temporary tablespace later using alter database command
Alter database default temporary tablespace temp;
How to assign temporary tablespace to user
Create user user_name default tablespace tbs_1 temporary tablespace temp;
Or we can assign later also using alter command
Alter user user_name temporary tablespace temp;
Drop temporary tablespace
Alter database tempfile ‘filespec’ drop including datafiles;
Note: You get an error ORA-25153
Add datafile to temporary tablespace
Alter tablespace temp_nam add tempfile ‘filespec’ size 50m;
Note:
Temporary tablespace cannot contain permanent object, so no need to be back up.
We cannot take offline or drop until new default is made available.
Views :
Database_properties
V$tempfile, dba_temp_files – list the temp file information
V$sort_segment and v$sort_usage – monitor temporary segments
V$temp_space_header – record the free space in temp

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. Oracle refers to these records collectively as undo.
Undo records are used to
Ø Transaction rollback when Rollback statement issued
Ø Transaction recovery
Ø Read consistency
Specifying the mode of undo management
Ø Auto
Ø Manual
Auto undo management
Undo_management = auto (dp)
The following parameter setting causes the startup command to start an instance in automatic undo management mode.An undo tablespace must be available into oracle will store undo info. Default undo tablespace is create at database creation or create explicitly

Assign parameter in pfile or spfile
undo_tablespace=undo_tbs – (dp) specify undo tablespace name
Undo_retention =300 – (dp) length of time to retain the undo. Default is 900 sec
Undo_suppress_errors= true false – (dp) suppress the error message if manual undo are issued

Manual undo Management
Undo_management = manual
If the UNDO_MANAGEMENT initialization parameter is not specified, the instance starts in manual undo management mode. If an UNDO_TABLESPACE initialization parameter is found, it is ignored
Initialization parameters that can be specified with manual undo management mode
ROLLBACK_SEGMENTS – Specifies the rollback segments to be acquired at instance startup
TRANSACTIONS – Specifies the maximum number of concurrent transactions
TRANSACTIONS_PER_ROLLBACK_SEGMENT - Specifies the number of concurrent transactions that each rollback segment is expected to handle
MAX_ROLLBACK_SEGMENTS - Specifies the maximum number of rollback segments that can be online for any instance

Managing undo tablespace
Oracle strongly recommends operating in automatic undo management mode. Automatic undo management mode is less complex to implement and manage.
Creating undo tablespace
There are two methods of creating an undo tablespace. The first method creates the undo tablespace when the CREATE DATABASE statement is issued. This occurs when you are creating a new database, and the instance is started in automatic undo management mode (UNDO_MANAGEMENT = AUTO). The second method is used with an existing database. It uses the CREATE UNDO TABLESPACE statement.
You cannot create database objects in an undo tablespace
Now we see the second method of creating undo tablespace in existing tablespace.
Create undo tablespace undo_tbs datafile ‘filespace’ size 100m reuse Autoextend on;
Altering an undo tablespace
. Adding and renaming datafile
. Bringing the datafile online or offline
. Beginning and ending an open backup on a datafile
These are only attribute you are permitted to alter in undo tablespace.
If undo tablespace runs out of space, you can add datafile to it or resize the datafiles.
Alter tablespace undo_tbs add datafile ‘filespec’ Autoextend on next 1 maxsize unlimited;
Or you can use alter database command to resize the undo tablespace
Alter database datafile ‘filespec’ resize 200m;
Dropping an undo tablespace
An undo tablespace can only dropped if it is not currently used by any instance and if any outstanding transactions.
Since drop tablespace drop an undo tablespace even if it contains unexpired undo info (within retention time). You must careful on dropping the undo.
Drop tablespace undo_tbsl including contents;
Views
V$undostat – contain statistics for monitoring and tuning undo space.
V$rollstat
V$transaction – contain undo segment infoDba_undo_extents –show commit time for each extent

Backup using Uman

In general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the data should that loss occur. The reconstructing of data is achieved through media recovery, which refers to the various operations involved in restoring, rolling forward, and rolling back a backup of database files.

Physical Backup
Hot backup
1. DB is in open stage
2. Check whether Archive is enabling or not. If not it must be enable.
3. Datafile and archive are to be backed up
4. Media recovery required.
5. Backup everything except binary files.
Method
Method
1. Get the listing of tablespace and Datafile.
a.Select tablespace_name, file_name from dba_data_files;
2. Put the tablespace in backup mode.
a. Alter tablespace tbsname begin backup;
3. Copy each Datafile in os level using CP command and move file to backup location
4. End the tablespace in backup mode
a.Alter tablespace tbsname end backup;
5. Repeat steps from 2 to 4 for each tablespace.
6. Backup the controlfile trace to bdump.
a. Alter database backup controlfile to trace;
7. Confirm all tablespace returned to normal mode.
a. Select file#, status, change#, time from v$backup where status != ‘NOT ACTIVE’;
8. Perform an archive log switch.
a. Alter system archive log current;
9. Backup all archive log files
Process
When executing BEGIN BACKUP the Datafile header corresponding to the tablespace get freeze until executing END BACKUP. In between the END BACKUP time Datafile has to be copied from one location to another location (BL) using OS command CP. While moving the Datafile from one location to backup location data block will be split due to the OS block size. Still the transaction has been gone thought but the SCN number will not update in the Datafile header until trigger end backup.
Drawback
1. Block corruption cannot be finding in HB.
2. Split block will occur.
3. Database inconsistence
4. Partial SCN will update

Cold Backup
1. Database is in closed stage
2. Not available to the users
3. Get the down time from client
4. Schedule the time and plan it
5. Copy each datafiles and control file.
Method
1. Shutdown immediate
2. Copy each Datafiles and control file in os level
----------------------------------------------------------------------------------------------------------------
Logical Backup
Export and Import
Export and import is an Oracle utility in operating system level. It will be installed automatically while installing the oracle software itself. It is a tool allows you to extract data from the database and stored in a file called dump file in binary format. We can easily carry the binary file from one operating system to another operating system and also we move the data from one oracle database to another oracle database. So this tool is absolutely platform independent. The sizes of the dump (binary) file are compress into 1/3 of our Datafile size.

Export modes
1. Full database
2. Tablespace level
3. Schema level (user)
4. Table level
5. Record level (using query clause)

Role
1. Exp_full_database
2. Imp_full_database

Export clause
Full=no
File=’path’
Filesize=0
Help=none
Log=’path’
Feedback=0
Owner=user
userid=un/pws
Buffer=0 - Os dependent. As a result, the max no. of rows in an array fetch by export.
Buffer_size = rows_in_array * maximum_row_size
xRecordlength=0
Direct=n
Indexes=y
Grants=y
Consistent=n
Compress=y
Constraints=y
Statistics=none/compute/estimate (d)
Rows=y
Tables=schema.tblname
Tablespace=tblname
Transport_tablespace=y
Triggers=y
Query= where condition
Parfile=path

Database Cloning
Hot Clone
1. Create required Directories in Clone db location.
2. Put the tablespace in backup mode.
a. Alter tablespace begin backup;
3. Copy the snapshot of each data files in this tablespaces.
4. End the tablespace in backup mode.
a. Alter tablespace end backup;
5. Repeat steps 2, 3, and 4 for each Tablespaces.
6. Backup parameter file.
a. Create pfile from spfile to clone db location;
b. edit the path and db name
7. Trace the control file.
a. Alter database backup control file to trace;
b. edit this file: removing the head, strike out "reuse", change "noresetlogs" to "resetlogs”
8. Copy each redo log file to clone db location.
9. Backup all archive files.
10. create new service id and password file
a. oradim –new –sid xx
b. orapwd file=xx.ora password=xx entries=5
11. Set the environment
a. Set oracle_sid=xx
b. Sqlplus “/as sysdba”
12. Startup the clone db with new pfile and put it in mount state
a. Startup pfile=/initxx.ora nomount
13. Run the control file script to re-create control file and other Datafile.
a. @create.sql
14. Recover the database using controlfile if required.
a. Recover database using backup controlfile until cancel
b. Apply the archive files
15. Open the database with resetlogs mode.
a. Alter database open resetlog

Cold Clone
1. Trace the control file to udump.
Alter database backup control file to trace;
2. Create pfile from spfile for clone db.
3. Shutdown the database
4. Create required Directories in Clone destination
5. Copy and paste all the Datafile from target database.
6. Edit the parameter file and replace db name and path.
7. Edit the control file also
8. Startup the clone db with new pfile and place it in mount state
9. Run the control file script to re-create control file and other datafiles.
10. Alter database open resetlogs;

Oracle 9i Installation on RH3,4,5

Overview
1. Pre-request
2. Creation of Os User (Oracle)
3. Create Corresponding Directories
4. Set Semaphore and share memory segment
5. Set the Environment Variables (.bash_profile)
6. Run the runInstaller

1. Pre - Request
You have to partition your hard drive for Linux box
> /temp - 500
> /boot - 200
> Swap - double the size of RAM (e.g.: 1 GB Ram, assign 2 GB (2048 bytes))
> / - remaining space will be assign to root

2. Creation of Os user and group

Enter into “root” user and do the following steps
] # groupadd oinstall expel oinstall is group for installing oracle software
] # groupadd dba expel dba is a group for oracle user to store database files
] # useradd -g oinstall –G dba oracle
Expl -g means that oinstall is primary group for oracle user
-G means that dba is a secondary group for oracle user
] # passwd oracle

3. Create required directories on which the oracle software will be installed.

] # mkdir -p /u01/app/oracle/product/9.2.0
Expl :make a directory
-p means will create the directory from base directory with the following directory /u01/---
] # chown -R oracle.oinstall /u01
Expl: change the ownership of oracle user to oinstall group for /u01
So here oracle software will be installed. This is your $ORACLE_HOME directory
-R means Recursive, ownership application to all directories available inside the u01
] # mkdir /var/opt/oracle
] # chown oracle.dba /var/opt/oracle
Expl: same scenario but here is used for temporary use by oracle user when using oracle
] # chmode 755 /var/opt/oracle
Expl: change the mode of your /var/... directory. It can be accessed by oracle user
7-rwx (owner), 5-rx (group), 5-rx (others).

4. Set semaphore and shared memory segments

We can set the values later also after installing oracle software. if we don’t set the value database will not be run and we can’t use DBCA also
It’s better to set the parameter early
] # cd ect\
] # VI sysctl.conf
Add these parameters
Kernel.shmmax = {half of Ur RAM}
Expl: if the RAM size is 1 GB. Give 512mb (512 x 1024 x 1024) bytes
Kernel.sem = 250 3200 100 128
Expl: you are increasing the number of process to 3200
Semmsi semmns semopm semmni
250 3200 100 128
] # cat sem –> to see the value of semaphore
] # init 6
It will restart the machine.
Now login into oracle user with password oracle.

5. Set the environment variables
Now we need to setup the environment variables in .batch_profile, so that when ever login to oracle user, he can able to use the oracle software without exporting the path.
.batch_profile is available for all users and it is hidden file also.
] $ ls –a -> will list the hidden file
] $ vi .batch_profile -> edit this file with vi command and set the following values
Export LD_ASSUME_KERNEL = 2.4.1 (not mandatory for AS 5)
Export ORACLE_BASE = /u01/app/oracle
Export ORACLE_HOME = $ORACLE_BASE/product/9.2.0
Export ORACLE_SID = demo (not mandatory)
Export ORACLE_TERM = xterm
Export NSL_LANG = American
Export ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH = $ORACLE_HOME/lib: /lib: /usr/lib
LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/local/lib
Export PATH = $PATH: $ORACLE_HOME/bin
Export TNS_ADMIN = $ORACLE_HOME/network/admin (access to tns, listener file)
: wq –> save and exit
Restart the machine

6. Run the runInstaller

1. Extract the cd
Unzip
gunzip ship_9204_linux_disk1.cpio.gz
gunzip ship_9204_linux_disk2.cpio.gz
gunzip ship_9204_linux_disk3.cpio.gz
Unpack
cpio -idmv <>2. Run the runInstaller
] $ . /runInstaller
3. Welcome screen will appear , press NEXT
4. Inventory Location
/u01/app/oracle/orainventory - base directory
5. Unix Group Name [oinstall ]
Press NEXT; you will be prompted to run the orainstroo.sh shell script.
Run the script from the “root” user
6. File location
7. Available product components
8. Privileged operating system group
OSDBA - oradba (startup, shutdown)
OSOPER - oraoper (granted lesser privilege)
9. Summary, then run root.sh shell script.
What are the privilege enabled by the orainsta.sh are revoked by the root.sh
10. Oracle net config
Perform typical configuration
11. DBCA – automatically create a database while installing oracle s/w
12. EIO

If you need more information about the installation process. You can visit
http://www.puschitz.com/InstallingOracle9i.shtml
http://www.oracle-base.com/articles/9i/Oracle9iInstallationOnRedHatAS3.php#DownloadSoftware
http://people.redhat.com/drepper/assumekernel.html

I hope this document will help you to install oracle software on Linux box. If you find any mistakes on my installation method. Please advise me to correct the errors.

Tablespace in oracle 9i

Tablespace is a logical storage unit in database. It a bridge between the datafiles and oracle instance.
Hierarchy of tablespace structure.
1. Tablespace can belonging to only database at a time
2. T tablespace can have more than one datafiles

Creating tablespace
Syntax: Create tablespace tbs_name datafile ‘file location/file_name.dbf’ size 100m

Tablespace clauses
Blocksize – set nonstandard block size for the tablespace.
You must set db_cache_size and at least one db_nk_cache_size.
Logging (d) nologging – all tables and indexes within the tablespace have all changes
written to redo.
Default – specify default storage for all objects created in the tablespace creation. (DMT only)
(Initial, next, pctincrease, minextends, maxenteds)
Online offline (d) – tablespace availability
Permanent temporary – tablespace can be used to hold permanent object
or temporary object (sorting)
Autoextend – this enable automatic extension of datafile
Next – this increment of next disk space to be allocated auto when more extents are required.
Maxsize - maximum disk space allowed for auto extension of datafile.
Unlimited – unlimited disk space allowed for auto extension of datafile.
Extent management – how the extent of tablespace are managed (LMT or DMT)
Segment management – track the used and free space in the segment in the tablespace
using free lists or bitmap (LMT only) . Pctfree , Pctused, Freelist, Freelist group
Autoallocate (d) – tablespace is system managed, user cannot specify an extent size
Uniform – tablespace is managed with uniform extents of size 32m or bytes.
By default 1mb in size.
Reuse – reuse the existing file.
Resize – enlarging the tablespace size.

Types of Tablespace
Ø System tablespace
- Created with the database
- Contain data-dictionary and stored program units

Ø Non-system tablespace
- Undo
- Temporary
- User data and easy to manage by dba.

Managing Space in Tablespaces.
When oracle allocates space to a segment (table or index), a group of contiguous free blocks (extent), is added to the segment. Metadata regarding the extent allocation and unallocated extents are either stored in the data dictionary are called DMT or stored within the tablespace header are called LMT

Locally managed tablespace – LMT
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary
Create tablespace tsb_lmt datafile ‘file spec ‘size 100m extent management local
uniform size 100k ;

Advantage
1. Avoid recursive space management. It won’t consume or release space in the
data-dictionary table.
2. Avoid contention on data-dictionary table.
3. Extents automatically tracks adjacent free space, by eliminating the need of coalesce
free extents.
4. Do not generate undo info bcos don’t update tables in the data-dictionary.

Autoallocate
Create tablespace tbs_aa datafile ‘filespec’ size 100m extent management local
autoallocate;
UniformSize
Create tablespace tbs_uni datafile ‘filespec’ size 100m extent management local uniform
size 32k;

Segment Space Management – ASSM in LMT

DBA has no longer to manage the segment management. Oracle done a major implementation in oracle 9i to manage the segment space automatically.

Create tablespace tbs_ssm datafile ‘filespec’ size 100m entent management local

segment space management auto

Create tablespace tbs_ssm1 datafile ‘filespec’ size 100m extent management local
segment space management manual (pctused 20 pctfree 20)

Dictionary managed tablespace – DMT

In DMT, to keep track of the free or used status of blocks, oracle uses data dictionary tables. When an extent is allocated or freed for reuse, 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 insert and deletes against these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Create tablespace tbs_dmt datafile ‘file spec’ size 100m extent management dictionary default storage (initial 1m next 2m pctincrease 0 maxextents 10

Default Storage clause – when DMT is created default storage parameters are specified. These values override any default settings to become the default for the objects created in that tablespace.

Syntax:
STORAGE ( {

INITIAL int [KM] NEXT int [KM] MINEXTENTS int MAXEXTENTS {int UNLIMITED} PCTINCREASEஇந்த் FREELISTS int FREELIST GROUPS int OPTIMAL [intr [KM] NULL] BUFFER_POOL {KEEP RECYCLE DEFAULT}} )

Calculation = initial + ( next * pctincrease / 100)

INITIAL integer [K M] -- default value is 5 x db_block_size
Specify the size of the object's first extent. The space for this extent is allocated when the object is created. Must be specified in a CREATE statement. See INITIAL_EXTENT column in DBA_TABLESPACES

NEXT integer [K M] -- default value is 5 x db_block_size
Specify in bytes the size of the next extent to be allocated to the object. The default value is the size of 5 data blocks. The minimum value is 1 data block. If the value of NEXT is changed using the ALTER statement, the NEXT allocated extent will have the newly specified NEXT size, regardless of the size of the most recently allocated extent and the value of the PCTINCREASE parameter.See NEXT_EXTENT column in DBA_TABLESPACES.

MINEXTENTS integer -- default and minimum value is 1 in the form of the INITIAL extent. Exception is rollback segments which require 2.
Specify the total number of extents to allocate when the object is created. Allows a large amount of space to be allocated when the object is created, even if the space available is not contiguous. The default and minimum value is 1, meaning SMON allocates only the INITIAL extent, except for rollback segments, for which the default and minimum is 2. If MINEXTENTS > 1, then SMON calculates the size of subsequent extents based on the values of INITIAL, NEXT, and PCTINCREASE. The value of MINEXTENTS can be reduced but not increased using the ALTER statement. Reducing the value of MINEXTENTS to a smaller value may be useful before a TRUNCATE ... DROP STORAGE, to ensure a segment will maintain a minimum number of extents after the TRUNCATE. The value of MINEXTENTS cannot be changed for an object residing in a locally managed tablespace. See MIN_EXTENTS column in DBA_TABLESPACES.
MAXEXTENTS {integer UNLIMITED} -- minimum value is 1. default depends on db_block_size. Exception is rollback segments which require 2.
Specify the total number of extents, including the INITIAL, that can be allocated for the object. The minimum value is 1 (except for rollback segments, which always have a minimum value of 2). The default value depends on db_block_size. MAXEXTENTS cannot be changed for an object residing in a locally managed tablespace.
PCTINCREASE integer -- Minimum value is 0. Default value is 50.
Specify the percent by which the third and subsequent extents grow over the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. If the value of the PCTINCREASE parameter is changed using the ALTER statement, SMON calculates the size of the next extent using the new value and the size of the most recently allocated extent. Setting PCTINCREASE to 0 will prevent SMON from coalescing extents and keep all extents the same size. Setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing. PCTINCREASE cannot be specified and therefore is always 0 for rollback segments. See: storage.htm
FREELISTS integer

FREELISTS is ignored for a locally managed tablespace in automatic segment-space management mode. For objects other than tablespaces, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If a value for FREELISTS is too large, an error is returned indicating the maximum value. FREELISTS can be specified in the storage_clause of any statement except when creating or altering a tablespace or rollback segments.

FREELIST GROUPS integer

FREELIST GROUPS is ignored for a locally managed tablespace in automatic segment-space management mode. Specify the number of groups of free lists for the database object being creating. The default and minimum value is 1. The instance number of RAC instances is used to map each instance to one free list group. FREELIST GROUPS can only be specified in CREATE TABLE, CREATE CLUSTER, CREATE MATERIALIZED VIEW, CREATE MATERIALIZED VIEW LOG, and CREATE INDEX.

OPTIMAL [integer [KM] NULL]

Only relevant to rollback segments. Specifies an optimal size in bytes for a rollback segments. Oracle tries to maintain this size for the rollback segments by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segments below the OPTIMAL value. The value of OPTIMAL cannot be less than the space initially allocated by MINEXTENTS, INITIAL, NEXT, and PCTINCREASE. The maximum value depends O/S. Specify NULL for no optimal size for the rollback segments, meaning that extents are never deallocated for rollback segment's. This is the default behavior.

BUFFER_POOL {KEEP RECYCLE DEFAULT}

The BUFFER_POOL clause allows a default buffer pool (cache) to be defined for a schema object. All blocks for the object are stored in the specified cache. Unless overridden by a partition-level definition, partitions inherit the buffer pool value from table or index specifications. BUFFER_POOL clause cannot be specified for cluster tables, tablespaces or rollback segments. It can be specified for clusters.
DEFAULT - Specify to indicate the default buffer pool.
KEEP - Specify to put blocks from the segment into the KEEP buffer pool. Maintaining an appropriately sized KEEP buffer pool retains the schema object in memory to avoid I/O operations. KEEP takes precedence over any NOCACHE clause specified for a table, cluster, materialized view, or materialized view log.
RECYCLE - Specify to put blocks from the segment into the RECYCLE pool. An appropriately sized RECYCLE pool reduces the number of objects whose default pool is the RECYCLE pool from taking up unnecessary cache space.
----------------------------------------------------
Converting DMT to LMT
exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
Converting LMT to DMT
exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2')

Changing the availability of tablespace

Ø Read only
Alter tablespace tbs_name read only;

a. Transaction read only, no further write operation except for the rollback of existing

transaction that previously made either committed or rollback.
b. You can drop item such as tables and indexes from read only tablespace.

Ø Offline, Online [ normal temporary immediate for recover]

Alter tablespace tbs_oo offline;
Alter tablespace tbs_oo online;

Normal (d) – flushes all blocks in all datafile in the tablespace out of sga.
We need to perform media recovery before bringing it back to online. (Recommend).
Temporary – perform checkpoint for all datafile, even if some file could not be written.
Immediate - does not perform checkpoint. Recovery needed.
For recover – for TPITR

a. SYSTEM tablespace cannot bring to offline mode
b. Default temporary tablespace cannot bring to offline until new temp tablespace create.
c. Active undo segment cannot be taken offline
d. Tablespace goes offline or comes back online the event is recorded in the data dictionary
and control file.
e. OI automatically switches a tablespace from online to offline when certain errors are
encountered. Eg: dbwr file in several attempt to write to a datafile.

Ø Move and Rename
Alter tablespace tbs_rn rename datafile ‘filespec’ to ‘filespec’;
Note:
a. Database must be mounted and file must exist
b. Tablespace must be in offline mode.
c. Copy and move file in os level using cp or mv command.
d. Execute the above statement
e. Bring the tablespace online.

Ø Adding datafile to a tablespace

Alter tablespace tbs_name add datafile ‘filespec’ size 100m;

Ø Dropping tablespace
Drop tablespace tbs_name including contents and datafiles cascade constraints;
Ø Resizing datafile
Alter database datafile ‘filespec’ resize 200m;
Ø Enable auto extension of datafile
Alter database datafile ‘filespec’ Autoextend on next 1m maxsize 5;

Important
1. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespace in such database have to be locally managed as well.
2. Locally managed temporary tablespace cannot be of type "permanent".

Views
dba_data_files, v$datafile, - list datafile information
dba_free_space - space usage in tablespace
dba_tablespaces, v$tablespace - list tablespace information

Oracle 9i Architecture

Oracle 9i Architecture
Oracle server Consist of database, instance and oracle software.
Oracle instance consist of Memory structure (sga) and background process.
Oracle database consist of datafile, logfile, controlfile and other files
Oracle datafile consist of tablespace, segment, extent, block

Shared Pool
Shared pool can holds the most recently used SQL/PLSQL statements. The statement can be shared among the oracle users. The SP is divided into two cache call Library Cache (LC) and Data Dictionary Cache (DDC). SP is maintaining its room based on LRU list.

Library Cache
The LC has an area called Shared SQL and Shared PL/SQL. Where the SQL statements and PLSQL packages get stored. When a query execute for the first time. It will be parsed. So, what is Parse?.

How Parsing is done
Syntax : Checking the spelling errors in the statement
Semantic : Checking whether the object (table, index, cluster) are present the DB
Checking Whether the user has privilege to access those objects.
Hash Value : Generate hash value for the statement
Execution Plan : Generate and execution plan and choose the best path to obtain the data

Cursor :
Cursor stores the Hash Value and Execution plan in its area. When another executing similar or exact statement. The result will fetch quickly.

Data Dictionary Cache
DDC hold the information about the Dictionary information. Meta information.

Database Buffer Cache - DBBC
It’s a Cache to hold the piece of information for frequent access. The Blocks are maintained by LRU list. Once the block gets completely filled it is called Dirty Block, it will be maintained by Dirty List.

Redo Log Buffer – RLBC
What are the changes made to database has recorded in RLBC.
How SELECT Statement Work
1. The server process work for user process. Take the select query to the Library Cache in SP.
2. The Library Cache in SP parses the select statement. Check the syntax, whether object available or not. Whether the user has access to select the object is called symantic. If these are fine, then it will generate a hash value for the query and create a execution plan.
3. If the object is already present in the DBBC, it quickly obtains the data from the buffer cache.
4. If the object not present in the DBBC, the server process obtain the block from Datafile and place it in buffer cache for subsequent usage.
5. Step 3 and 4 are execution. Based on the execution plan it will get the data from buffer.
6. If the select statement has any sorting e.g. Order by, group by. These sorting processes are done in PGA sort area. After the statement has been sorted it will fetch to user

When Commit executes
LGWR start writes the block from RLBC to ONRLF. Then the check point fined the committed statements CNS