Oracle session level, Managing tablespace, Managing

Datafiles, Logical and physical structure


Session Level

Long Operations:

select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds lapsed,last_update_time last_time from v&session_longops where sid=73 order by last_update_time desc;

All active sessions
Select * from v&session where status='ACTIVE'and sid in (Sid, Serial #) order by sid;

Find session's sid or process id by it's sid or process id

Select sid, a.serial#, spid, a.username, status, taddr, a.programfrom v&session a, v&process b where a.paddr=b.addr and a.username is not nulland (sid=163 or spid=28179) order by status, sid;

Kill Session

Alter system kill session '&sid,&serial';

Log Management

Status of Logfile Groups

Select * from v&log;

Status of Logfiles

Select * from v&logfile order by group#;

status of the archiver

Select * from v&instance;

Restart the archiver

Alter system archive log start;



Switch online log

Alter system switch logfile;

Alter system set log_archive_max_processes=4;


Add logfile group

Alter database add logfile group 4 ('&logfilename1','&logfilename2') size 64M;

Drop logfile group and all members in it

Alter database drop logfile group &N;

Add logfile member

Alter database add logfile member '&logfilename' reuse to group 4;

Drop logfile member

Alter database drop logfile member '&logfilename';

Checking archivelog mode
Select dbid, name, resetlogs_time, log_mode from v&database;

Archiver destinations

Select * from v&archive_dest;

Altering Archive destination

Alter system set log_archive_dest_1='location=&path';
Alter system set log_archive_dest_state_1='enable';

Archived log info from the control file

Select * from v&archived_log;

The sequence# of last backed up log

Select thread#, max (sequence#) from v&archived_log where BACKUP_COUNT>0 group by thread#;

Redo size (MB) per day, last 30 days
Select trunc (first_time) arc_date, sum (blocks * block_size)/1048576 arc_sizefrom v&archived_log where first_time >= (trunc (sysdate)-30)group by trunc(first_time);

MANAGING TABLESPACES

A tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.
Types of tablespace?
System Tablespace:• Created with the database • Required in all database • Contain the data dictionary
Non System Tablespace: • Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user’s objects • Enable more flexibility in database administration
How to Create Tablespace?
CREATE TABLESPACE "tablespace name"DATAFILE clause SIZE ……. REUSEMENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)BLOCKSIZELOGGING NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging : tablespace do not have all changes written to redo)ONLINE OFFLINE (OFFLINE: tablespace unavailable immediately after creation)PERMANENT TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)EXTENT MANAGEMENT clauseExample: CREATE TABLESPACE "USER1"DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSEBLOCKSIZE 8192
LOGGING
ONLINEPERMANENTEXTENT MANAGEMENT LOCAL

How to manage space in Tablespace?

Tablespace allocate space in extent.






Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don’t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.

Extent Management [Local Dictionary]
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.
To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).
If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace
When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.
How to Create a Locally Managed Tablespace?
The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.
CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;How to Create a Dictionary Managed Tablespace?The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50MEXTENT MANAGEMENT DICTIONARYDEFAULT STORAGE (INITIAL 64KNEXT 64KMINEXTENTS 2MAXEXTENTS 121PCTINCREASE 0);
What is Segment Space Management Options?
Two choices for segment-space management, one is manual (the default) and another auto.
Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.
Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management
Example:CREATE TABLESPACE usersDATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSEEXTENT MANAGEMENT LOCAL UNIFORM SIZE 512KSEGMENT SPACE MANAGEMENT AUTOPERMANENTONLINE;
How to Convert between LMT and DMT Tablespace?
The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert
between LMT and DMT mode. Look at these examples:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');PL/SQL procedure successfully completed.
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');PL/SQL procedure successfully completed

Important Queries Related to Tablespace

How to retrieve tablespace default storage Parameters?
SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT "INITIAL_EXT",NEXT_EXTENT "NEXT_EXT",MIN_EXTENTS "MIN_EXT",MAX_EXTENTS "MAX_EXT",PCT_INCREASE FROM DBA_TABLESPACES;

How to retrieve information tablespace and associated datafile?
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;
How to retrieve Statistics for Free Space (Extents) of Each Tablespace?

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,COUNT(*) "PIECES",MAX(blocks) "MAXIMUM",MIN(blocks) "MINIMUM",AVG(blocks) "AVERAGE",SUM(blocks) "TOTAL" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID;

How to drop a datafile from a Tablespace?Important:
Oracle does not provide an interface for dropping datafiles in the same way you would drop a schema object such as a table or a user.Reasons why you want to remove a datafile from a tablespace:• You may have mistakenly added a file to a tablespace. • You may have made the file much larger than intended and now want to remove it. • You may be involved in a recovery scenario and the database won't start because a datafile is missing.

Once the DBA creates a datafile for a tablespace, the datafile cannot be removed. If you want to do any critical operation like dropping datafiles, ensure you have a full backup of the database.
Step: 1

Determining how many datafiles make up a tablespace to determine how many and which datafiles make up a tablespace, you can use the following query:
SELECT file_name, tablespace_name FROM dba_data_files WHEREtablespace_name ='';

Case 1
If you have only one datafile in the tablespace and you want to remove it. You can simply drop the entire tablespace using the following:
DROP TABLESPACE INCLUDING CONTENTS;
The above command will remove the tablespace, the datafile, and the tablespace's contents from the data dictionary.Important: Oracle will not drop the physical datafile after the DROP TABLESPACE command. This action needs to be performed at the operating system.
Case 2
If you have more than one datafile in the tablespace, and you want to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:
DROP TABLESPACE INCLUDING CONTENTS;
Case 3
If you have more than one datafile in the tablespace and you want to remove only one or two (not all) datafile in the tablespace or you want to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace.
Step: 1 Gather information on the current datafiles within the tablespace by running the following query in SQL*Plus:SELECT file_name, tablespace_name FROM dba_data_files WHEREtablespace_name ='';
Step: 2 you now need to identify which objects are inside the tablespace for the purpose of running an export. To do this, run the following query:SELECT owner, segment_name, segment_type FROM dba_segments WHERE tablespace_name=''
Step: 3 now, export all the objects that you wish to keep.
Step: 4 Once the export is done, issue the

DROP TABLESPACE INCLUDING CONTENTS.
Step: 5 delete the datafiles belonging to this tablespace using the operating system.

Step: 6 Recreate the tablespace with the datafile(s) desired, and then import the objects into that tablespace.
Case: 4
If you do not want to follow any of these procedures, there are other things that can be done besides dropping the tablespace.
• If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
• If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE RESIZE;
Command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.
Important: The ALTER DATABASE DATAFILE OFFLINE DROP command is not meant to allow you to remove a datafile. What the command really means is that you are off lining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use:

ALTER DATABASE DATAFILE OFFLINE;

Instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup.

(This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

MANAGING DATAFILES

What is datafile?

Datafiles are physical files of the OS that store the data of all logical structures in the database. Datafile must be created for each tablespace.
How to determine the number of datafiles?
At least one datafile is required for the SYSTEM tablespace. We can create separate datafile for other tablespace. When we create DATABASE, MAXDATAFILES may be or not specify in create database statement clause. Oracle assassin db_files default value to 200. We can also specify the number of datafiles in init file.
When we start the oracle instance, the DB_FILES initialization parameter reserve for datafile information and the maximum number of datafile in SGA. We can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance. Important: If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit. Example: if init parameter db_files set to 2 then you can not add more then 2 in your database.
If the value of DB_FILES is too high, memory is unnecessarily consumed. When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Note:
If you add new datafiles to a tablespace and do not fully specify the filenames, the database creates the datafiles in the default database directory. Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.
How to add datafile in existing tablespace?
Alter tablespace add datafile ‘/............../......./file01.dbf’ size 10m autoextend on; How to resize the datafile? Alter database datafile '/............../......./file01.dbf' resize 100M; How to bring datafile online and offline?Alter database datafile '/............../......./file01.dbf' online;alter database datafile '/............../......./file01.dbf' offline; How to renaming the datafile in a single tablesapce?Step: 1 Take the tablespace that contains the datafiles offline. The database must be open. Alter tablespace offline normal; Step: 2 Rename the datafiles using the operating system. Step: 3 Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

Alter tablespace rename datafile '/...../..../..../user.dbf' to '/..../..../.../users1.dbf';
Step 4: Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How to relocate datafile in a single tablesapce?Step: 1 Use following query to know the specific file name or size.
Select file_name, bytes from dba_data_files where tablespace_name=''; Step: 2 Take the tablespace containing the datafiles offline: alter tablespace offline normal;
Step: 3 Copy the datafiles to their new locations and rename them using the operating system. Step: 4 Rename the datafiles within the database.
ALTER TABLESPACE RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf' TO '/u03/oracle/rbdb1/users01.dbf','/u04/oracle/rbdb1/users02.dbf';
Step:5 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How to Renaming and Relocating Datafiles in Multiple Tablespaces?

Step: 1 Ensure that the database is mounted but closed. Step: 2 Copy the datafiles to be renamed to their new locations and new names, using the operating system. Step: 3 Use ALTER DATABASE to rename the file pointers in the database control file. ALTER DATABASE RENAME FILE
'/u02/oracle/rbdb1/sort01.dbf','/u02/oracle/rbdb1/user3.dbf'TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.dbf;

Step: 4 Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.


Overview of Logical Structure of a Database

This section discusses logical storage structures: data blocks, extents, segments, and tablespaces. These logical storage structures enable Oracle Database to have fine-grained control of disk space use.This section includes the following topic

Oracle Database Data Blocks Extents Segments Tablespaces

Oracle Database Data BlocksAt the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.
ExtentsThe next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
SegmentsAbove extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files.When the extents of a segment are full, Oracle Database dynamically allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

TablespacesA database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.
Each database is logically divided into two or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Every Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle Database creates them automatically when the database is created. The system default is to create a small file tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as small file tablespaces.
Oracle Database also lets you create bigfile tablespaces, which are made up of single large file rather than numerous smaller ones. Bigfile tablespaces let Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. As a result, Oracle Database can scale up to 8 exabytes in size. With Oracle-Managed Files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafile.

Overview of Physical Structure of a Database

The following sections explain the physical database structures of an Oracle database, including datafiles, control files, redo log files, archive log files, parameter files, alert and trace log files, and backup files.This section includes the following topics

Datafiles
Control Files Online Redo Log Files Archived Redo Log Files Parameter Files Alert and Trace Log Files Backup Files
Datafiles
Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

Datafiles have the characteristics:A datafile can be associated with only one database. Datafiles can be defined to extend automatically when they are full. One or more datafiles form a logical unit of database storage called a tablespace.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access
some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.
Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).
Datafiles that are stored in temporary tablespaces are called tempfiles. Tempfiles are subject to some restrictions.Control FilesEvery Oracle database has a control file. A control file contains entries that specify the physical structure of the database, including the following information:Database name

Names and locations of datafiles and redo log files Timestamp of database creation Oracle Database can multiplex the control file, that is, simultaneously maintain a number of identical control file copies, to protect against a failure involving the control file.
Every time an instance of an Oracle database is started, its control file identifies the datafiles, tempfiles, and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle Database to reflect the change. A control file is also used in database recovery.
Online Redo Log File

Every Oracle Database has a set of two or more online redo log files. These online redo log files, together with archived copies of redo log files, are collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records), which record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

To protect against a failure involving the redo log itself, Oracle Database lets you create a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.

Archived Redo Log Fileswhen online redo log files are written to disk, they become archived redo log files. Oracle recommends that you enable automatic archiving of the redo log. Oracle Database automatically archives redo log files when the database is in ARCHIVELOG mode.

Parameter FilesParameter files contains a list of configuration parameters for that instance and database. Both parameter files (pfiles) and server parameter files (spfiles) let you store and manage your initialization parameters persistently in a server-side disk file. A server parameter file has these additional advantages:The file is concurrently updated when some parameter values are changed in the active instance. The file is centrally located for access by all instance in a Real Application Services database. Oracle recommends that you create a server parameter file as a dynamic means
maintaining initialization parameters.
Alert and Trace Log FilesEach server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. Trace file information is also used to tune applications and instances. The alert file, or alert log, is a special trace file. The alert log of a database is a chronological log of messages and errors.The following features provide automation and assistance in the collection and interpretation of trace and alert file information:

The Automatic Diagnostic Repository (ADR) is a system-managed repository for storing and organizing trace files and other error diagnostic data. ADR provides a comprehensive view of all the critical errors encountered by the database and maintains all relevant data needed for problem diagnosis and eventual resolution. When the same type of incident occurs too frequently, ADR performs flood control to avoid excessive dumping of diagnostic information.
The Incident Packaging Service (IPS) extracts diagnostic and test case data associated with critical errors from the ADR and packages the data for transport to Oracle.
Backup FilesTo restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file.User-managed backup and recovery requires you to actually restore backup files before you can perform a trial recovery of the backups.

Server-managed backup and recovery manages the backup process, such as scheduling of backups, as well as the recovery process, such as applying the correct backup file when recovery is needed.