Tablespace management - I
• Data blocks: A data block is the smallest building block of the Oracle database and consists ofa specific number of bytes on the disk.
• Extents: An extent is two or more consecutive Oracle data blocks, and this is the unit of spaceallocation.
• Segments: A segment is a set of extents that you allocate to a logical structure like a table or anindex (or some other object).
• Tablespaces: A tablespace is a set of one or more datafiles, and usually consists of related segments. The datafiles contain the data of all the logical structures that are part of a tablespace,like tables and indexes.
Types of Tablespaces:
Besides the System and Sysaux tablespaces, youll most likely also have undo and temporary tablespaces.Youll also use several other permanent tablespaces to hold your data and indexes. Here is a summary of the key types of tablespaces youre likely to encounter.
• Bigfile tablespaces are tablespaces with a single large datafile, whose maximum size can arrange from 8 to 128 terabytes, depending on the database block size. Thus, your database could conceivably be stored in just one bigfiletablespace.
• Smallfile tablespaces can contain multiple datafiles, but the files cannot be as large as a bigfiledatafile. Smallfiletablespaces, which are the traditional tablespaces, are the default in OracleDatabase 11g, and Oracle creates both System and Sysaux tablespaces as smallfile tablespaces. • Temporary tablespaces contain data that persists only for the duration of a users session.Usually Oracle uses these tablespaces for sorting and similar activities for users. • Permanent tablespaces include all the tablespaces that arent designated as temporarytablespaces. • Undo tablespaces contain undo records, which Oracle uses to roll back, or undo, changes tothe database. • Read-only tablespaces dont allow write operations on the datafiles in the tablespace. Youcan convert any normal (read/write) tablespace to a read-only tablespace in order to protectdata or to eliminate the need to perform backup and recovery of large datafiles that dontchange.
Physical Database Structures
Physical database structures refer to the actual Oracle database files at the operating system level.
The Oracle database consists of the following three main types of files:
• Datafiles: These files store the table and index data.
• Control files: These files record changes to all database structures.
• Redo log files: These online files contain the changes made to table data.
In addition to these three types of files, an Oracle database makes use of several other operatingsystem files to manage its operations. These include initialization files (like init.ora and the serverparameter file [SPFILE]), network administration files (like tnsnames.ora and listener.ora), alert logfiles, trace files, and the password file. In addition, you also have backup files, which you must restore in case of a media failure.
Create tablespace:
create tablespace users datafile '/prod/bsprod/oradata/users01.dbf' size 100m;
Add datafile:
Alter tablespace users add datafile /prod/bsprod/oradata/users02.dbf size 100m;
Resize datafile :
Alter database datafile /prod/bsprod/oradata/users01.dbf resize 200m;
Identify the permanent, undo and temporary tablespace details in the database :
Verify status of the Tablespaces :
1. Online
2. Offline
3. Read-only
Rename datafile :
We can do renaming datafiles in 3 ways.
1. Tablespace level
2. Datafile level
3. Database level
Suppose if you added a datafile with wrong convention .daf instead of .dbf
Alter tablespace users add datafile '/prod/bsprod/oradata/users02.daf ' size 200m;
As per the Oracle standards we should rename it to users02.dbf
Tablespace level
Alter tablespace users offline;
Copy the datafile in os level
$cp /prod/bsprod/oradata/users02.daf /prod/bsprod/oradata/users02.dbf
sqlplus / as sysdba
SQL>alter database rename file '/prod/bsprod/oradata/users02.daf' to '/prod/bsprod/oradata/users02.dbf';
SQL>alter tablespace users online;
Remove the .daf file from OS level
Archivelog mode:
Enable Archivelogmode :
SQL>shut immediate
Add below parameters to the pfile
Log_archive_dest='/prod/bsprod/admin/arch/'
Log_archive_format=redo_%r_%s_%t.arc
SQL>startup mount
SQL> alter database archivelog;
SQL>archive log list
SQL>select log_mode fromv$database;
SQL>alter database open;
Disable Archivelogmode:
SQL>shut immediate
Remove below parameters to the pfile
Log_archive_dest='/prod/bsprod/admin/arch/'
Log_archive_format=redo_%r_%s_%t.arc
SQL>startup mount
SQL>archive log list
SQL> alter database noarchivelog;
SQL>select log_mode fromv$database;
SQL>alter database open;
Datafile level
Pre-requisite: Database should be in Archive log mode
SQL>alter database datafile '/prod/bsprod/oradata/users02.daf' offline;
Copy datafile
$cp /prod/bsprod/oradata/users02.daf /prod/bsprod/oradata/users02.dbf
sqlplus / as sysdba
SQL>alter database rename file '/prod/bsprod/oradata/users02.daf' to '/prod/bsprod/oradata/users02.dbf';
SQL>recover datafile '/prod/bsprod/oradata/users02.dbf';
SQL>alter database datafile '/prod/bsprod/oradata/users02.dbf' online;
Database level
SQL>shutdown the database
Copy the datafile
$cp /prod/bsprod/oradata/users02.daf /prod/bsprod/oradata/users02.dbf
SQL>startup mount
SQL>alter database rename file '/prod/bsprod/oradata/users02.daf' to '/prod/bsprod/oradata/users02.dbf';
SQL>alter database open;
Tablespace management - II
Auto-extend ON and OFF:
You can use the AUTOEXTEND provision when you create a tablespace or when you add datafiles to a tablespace to tell Oracle to automatically extend the size of the datafiles in the tablespace . If we give maxsize then it will grow till the value we given while adding a datafile.
SQL>alter database datafile '/prod/bsprod/oradata/bsprod/users01.dbf' autoextend on;
BIGFILE TABLESPACE :
Oracle Database 11g can contain up to 8 exabytes (8 million terabytes) of data. Don͛t panic, however,thinking how many millions of datafiles you͛d need to manage in order to hold this much data.You have the option of creating really big tablespaces called, appropriately, bigfile tablespaces. A bigfile tablespace (BFT) contains only one very large datafile. If you͛re creating a bigfile-based permanent tablespace, it͛ll be a single datafile, and if it͛s a temporary tablespace, it will be a single temporary file. The maximum number of datafiles in Oracle is limited to 64,000 files. It's not possible to add a datafile to bigfile tablespace, we have only one datafile inside it. Depending on the block size, a bigfile tablespace can be as large as 128 terabytes.
SQL> create bigfile tablespace BIGTS datafile '/prod/bsprod/oradata/bsprod/bigts.dbf' size 50m;
TEMPORARY TABLESPACE :
The data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle doesn͛t allow users to create objects in a temporary tablespace.
Create temporary tablespace :
SQL>create temporary tablespace tempx tempfile '/prod/bsprod/oradata/bsprod/tempx01.dbf͛' size 100m;
Add tempfile to the temporary tablespace :
SQL> alter tablespace tempx add tempfile '/prod/bsprod/oradata/basprod/tempx02.dbf' size 100m;
TO VERIFY THE TEMPORARY TABLESPACE & TEMPFILES:
Temporary tablespace groups :
Oracle now has the concept of grouping multiple tablespaces together in what they call a tablespace group. This allows you to create multiple temporary tablespaces, assign these temporary tablespaces to a tablespace group, and then use the tablespace group as the default temporary tablespace for the database or user. The benefit this has is that a single SQL statement or set of SQL statements may use more than the one temporary tablespace you have created in the past and now span multiple tablespaces.
Tablespace managemet - III
Extent management :
Extent management devided in to 2 types
Dictionary
Local
Dictionary managed tablespace :
Extents information stored in the system tablespace.
We should mention the storage parameters to create a dictionary managed tablespace.
If you don’t mention storage parameters extents will be created based on the block size. Extent size would be 5 times of the current block size in the database means 5 blocks will be created for 1 extent.
Example: Extent size = 2k * 5 = 10k = 10240 (If block size 2k) Extent size = 4k * 5 = 20k =20480 (If block size 4k)
Locally managed tablespace :
When you create a tablespace, if you do not specify extent management, the default is locally managed.Extent information stored with in the tablespace itself. We can’t create a locally managed tablespace with extent management manual option.Database automatically manages the extents if the tablespace is locally managed. Extents allocation divided in to 2 types
AUTO UNIFORM
Tablespaces with AUTOALLOCATE extents created by oracle automatically. If we create tablespace with Local/Autoallocate then allocated minimum extent sizes is 64K with a minimum of 5 database blocks per extent.
If we create a tablespace wih UNIFORM extent size then all the extents size would be same.
Segment space management
Segment space management divided in to 2 types
Manual segment space management
Automatic segment space management
Manual segment space management:
Segment space management manual is possible for both Local/Dict managed tablespaces. We need to mention PCT USED, PCT FREE parameters while creating a table. If you don’t mention PCT parameters while creating table, table will be created with default PCT values.
Automatic segment space management :
Automatic Segment Space Management (ASSM):
The ASSM tablespace is new in Oracle9i and is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.
AUTO Segment space management is possible for Local extent management. AUTO segment space management not valid with DICTIONARY extent management. If you mention PCT FREE, PCT USED for ASSM tablespaces then it will take the PCT USED value automatically. It will not consider the PCT USED mentioned value but it will consider the PCT FREE value.
• Data blocks: A data block is the smallest building block of the Oracle database and consists ofa specific number of bytes on the disk.
• Extents: An extent is two or more consecutive Oracle data blocks, and this is the unit of spaceallocation.
• Segments: A segment is a set of extents that you allocate to a logical structure like a table or anindex (or some other object).
• Tablespaces: A tablespace is a set of one or more datafiles, and usually consists of related segments. The datafiles contain the data of all the logical structures that are part of a tablespace,like tables and indexes.
Types of Tablespaces:
Besides the System and Sysaux tablespaces, youll most likely also have undo and temporary tablespaces.Youll also use several other permanent tablespaces to hold your data and indexes. Here is a summary of the key types of tablespaces youre likely to encounter.
• Bigfile tablespaces are tablespaces with a single large datafile, whose maximum size can arrange from 8 to 128 terabytes, depending on the database block size. Thus, your database could conceivably be stored in just one bigfiletablespace.
• Smallfile tablespaces can contain multiple datafiles, but the files cannot be as large as a bigfiledatafile. Smallfiletablespaces, which are the traditional tablespaces, are the default in OracleDatabase 11g, and Oracle creates both System and Sysaux tablespaces as smallfile tablespaces. • Temporary tablespaces contain data that persists only for the duration of a users session.Usually Oracle uses these tablespaces for sorting and similar activities for users. • Permanent tablespaces include all the tablespaces that arent designated as temporarytablespaces. • Undo tablespaces contain undo records, which Oracle uses to roll back, or undo, changes tothe database. • Read-only tablespaces dont allow write operations on the datafiles in the tablespace. Youcan convert any normal (read/write) tablespace to a read-only tablespace in order to protectdata or to eliminate the need to perform backup and recovery of large datafiles that dontchange.
Physical Database Structures
Physical database structures refer to the actual Oracle database files at the operating system level.
The Oracle database consists of the following three main types of files:
• Datafiles: These files store the table and index data.
• Control files: These files record changes to all database structures.
• Redo log files: These online files contain the changes made to table data.
In addition to these three types of files, an Oracle database makes use of several other operatingsystem files to manage its operations. These include initialization files (like init.ora and the serverparameter file [SPFILE]), network administration files (like tnsnames.ora and listener.ora), alert logfiles, trace files, and the password file. In addition, you also have backup files, which you must restore in case of a media failure.
Create tablespace:
create tablespace users datafile '/prod/bsprod/oradata/users01.dbf' size 100m;
Add datafile:
Alter tablespace users add datafile /prod/bsprod/oradata/users02.dbf size 100m;
Resize datafile :
Alter database datafile /prod/bsprod/oradata/users01.dbf resize 200m;
Identify the permanent, undo and temporary tablespace details in the database :
Verify status of the Tablespaces :
1. Online
2. Offline
3. Read-only
Rename datafile :
We can do renaming datafiles in 3 ways.
1. Tablespace level
2. Datafile level
3. Database level
Suppose if you added a datafile with wrong convention .daf instead of .dbf
Alter tablespace users add datafile '/prod/bsprod/oradata/users02.daf ' size 200m;
As per the Oracle standards we should rename it to users02.dbf
Tablespace level
Alter tablespace users offline;
Copy the datafile in os level
$cp /prod/bsprod/oradata/users02.daf /prod/bsprod/oradata/users02.dbf
sqlplus / as sysdba
SQL>alter database rename file '/prod/bsprod/oradata/users02.daf' to '/prod/bsprod/oradata/users02.dbf';
SQL>alter tablespace users online;
Remove the .daf file from OS level
Archivelog mode:
Enable Archivelogmode :
SQL>shut immediate
Add below parameters to the pfile
Log_archive_dest='/prod/bsprod/admin/arch/'
Log_archive_format=redo_%r_%s_%t.arc
SQL>startup mount
SQL> alter database archivelog;
SQL>archive log list
SQL>select log_mode fromv$database;
SQL>alter database open;
Disable Archivelogmode:
SQL>shut immediate
Remove below parameters to the pfile
Log_archive_dest='/prod/bsprod/admin/arch/'
Log_archive_format=redo_%r_%s_%t.arc
SQL>startup mount
SQL>archive log list
SQL> alter database noarchivelog;
SQL>select log_mode fromv$database;
SQL>alter database open;
Datafile level
Pre-requisite: Database should be in Archive log mode
SQL>alter database datafile '/prod/bsprod/oradata/users02.daf' offline;
Copy datafile
$cp /prod/bsprod/oradata/users02.daf /prod/bsprod/oradata/users02.dbf
sqlplus / as sysdba
SQL>alter database rename file '/prod/bsprod/oradata/users02.daf' to '/prod/bsprod/oradata/users02.dbf';
SQL>recover datafile '/prod/bsprod/oradata/users02.dbf';
SQL>alter database datafile '/prod/bsprod/oradata/users02.dbf' online;
Database level
SQL>shutdown the database
Copy the datafile
$cp /prod/bsprod/oradata/users02.daf /prod/bsprod/oradata/users02.dbf
SQL>startup mount
SQL>alter database rename file '/prod/bsprod/oradata/users02.daf' to '/prod/bsprod/oradata/users02.dbf';
SQL>alter database open;
Tablespace management - II
Auto-extend ON and OFF:
You can use the AUTOEXTEND provision when you create a tablespace or when you add datafiles to a tablespace to tell Oracle to automatically extend the size of the datafiles in the tablespace . If we give maxsize then it will grow till the value we given while adding a datafile.
SQL>alter database datafile '/prod/bsprod/oradata/bsprod/users01.dbf' autoextend on;
BIGFILE TABLESPACE :
Oracle Database 11g can contain up to 8 exabytes (8 million terabytes) of data. Don͛t panic, however,thinking how many millions of datafiles you͛d need to manage in order to hold this much data.You have the option of creating really big tablespaces called, appropriately, bigfile tablespaces. A bigfile tablespace (BFT) contains only one very large datafile. If you͛re creating a bigfile-based permanent tablespace, it͛ll be a single datafile, and if it͛s a temporary tablespace, it will be a single temporary file. The maximum number of datafiles in Oracle is limited to 64,000 files. It's not possible to add a datafile to bigfile tablespace, we have only one datafile inside it. Depending on the block size, a bigfile tablespace can be as large as 128 terabytes.
SQL> create bigfile tablespace BIGTS datafile '/prod/bsprod/oradata/bsprod/bigts.dbf' size 50m;
The data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle doesn͛t allow users to create objects in a temporary tablespace.
Create temporary tablespace :
SQL>create temporary tablespace tempx tempfile '/prod/bsprod/oradata/bsprod/tempx01.dbf͛' size 100m;
Add tempfile to the temporary tablespace :
SQL> alter tablespace tempx add tempfile '/prod/bsprod/oradata/basprod/tempx02.dbf' size 100m;
TO VERIFY THE TEMPORARY TABLESPACE & TEMPFILES:
Temporary tablespace groups :
Oracle now has the concept of grouping multiple tablespaces together in what they call a tablespace group. This allows you to create multiple temporary tablespaces, assign these temporary tablespaces to a tablespace group, and then use the tablespace group as the default temporary tablespace for the database or user. The benefit this has is that a single SQL statement or set of SQL statements may use more than the one temporary tablespace you have created in the past and now span multiple tablespaces.
Extent management :
Extent management devided in to 2 types
Dictionary
Local
Dictionary managed tablespace :
Extents information stored in the system tablespace.
We should mention the storage parameters to create a dictionary managed tablespace.
If you don’t mention storage parameters extents will be created based on the block size. Extent size would be 5 times of the current block size in the database means 5 blocks will be created for 1 extent.
Example: Extent size = 2k * 5 = 10k = 10240 (If block size 2k) Extent size = 4k * 5 = 20k =20480 (If block size 4k)
Locally managed tablespace :
When you create a tablespace, if you do not specify extent management, the default is locally managed.Extent information stored with in the tablespace itself. We can’t create a locally managed tablespace with extent management manual option.Database automatically manages the extents if the tablespace is locally managed. Extents allocation divided in to 2 types
AUTO UNIFORM
Tablespaces with AUTOALLOCATE extents created by oracle automatically. If we create tablespace with Local/Autoallocate then allocated minimum extent sizes is 64K with a minimum of 5 database blocks per extent.
If we create a tablespace wih UNIFORM extent size then all the extents size would be same.
Segment space management
Segment space management divided in to 2 types
Manual segment space management
Automatic segment space management
Manual segment space management:
Segment space management manual is possible for both Local/Dict managed tablespaces. We need to mention PCT USED, PCT FREE parameters while creating a table. If you don’t mention PCT parameters while creating table, table will be created with default PCT values.
Automatic segment space management :
Automatic Segment Space Management (ASSM):
The ASSM tablespace is new in Oracle9i and is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.
AUTO Segment space management is possible for Local extent management. AUTO segment space management not valid with DICTIONARY extent management. If you mention PCT FREE, PCT USED for ASSM tablespaces then it will take the PCT USED value automatically. It will not consider the PCT USED mentioned value but it will consider the PCT FREE value.
No comments:
Post a Comment