How
to manage datafiles??
The earlier discussions are on the control files and redo log files as also archived log files. Control files and redo log files are part of the database files and they count as part of the number of data files a database can have. OMF (Oracle Managed Files) and User Managed Files are the two important system. OMF is to help reduce the management of the data files. This is good for any kind of development environment/s. We shall deal with this separately. The following are some of the important considerations while managing the data files for your database. (1)
Number of data files We
have already discussed how and why there is a limitation to the number
of files. So, be sure of (1)
The limitations of the OS as to the size it can handle The data files that are associated with the database are added at various stages of the database. Before creating the database you are deciding on the number of control files and their locations, trace files and their destinations such background dump destination (bdump) , core dump destination (cdump) and user dump destination (udump). While creating the database, the numbers of redo log files, the system tablespace and associated data file location, temporary tablespace and associated data file/s location, the undo tablespace and associated data file/s location and in the case of Oracel 10G additional tablespace for creating SYSTEM user schema objects and associated data files are decided. After creating the database, the required tablespaces and associated data files and their location is decided. During
the life cycle of the database, additional data files are added existing
datafiles are extended which directly or indirectly influence the number
of data files and their individual sizes. If a statement that creates a datafile fails, Oracle removes any created operating system files. However, because of the large number of potential errors that can occur with file systems and storage subsystems, there can be situations where you must manually remove the files using operating system commands. The following command impact the datafiles: (1)
Create tablespace How can I change the data file size?? It
is done in two ways. How can I set Automatic extension for a data file?? You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum. Setting your datafiles to extend automatically provides these advantages: Reduces
the need for immediate intervention when a tablespace runs out of space
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements: CREATE
DATABASE The following example enables automatic extension for a data file added to the users tablespace: ALTER
TABLESPACE users
The next example disables the automatic extension for the data file. ALTER
DATABASE DATAFILE 'd:\oracle\oradata\whs\users\users_data_01.dbf' How can I Resize a data file?? You can manually increase or decrease the size of a data file using the ALTER DATABASE statement. Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database. Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements. In the next example, assume that the data file d:\oracle\oradata\whs\users\users_data_01.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the data file can be reduced in size. The following statement decreases the size of data file d:\oracle\oradata\whs\users\users_data_01.dbf : ALTER
DATABASE DATAFILE 'd:\oracle\oradata\whs\users\users_data_01.dbf' How the data files can be set online and taken offline and why they are to be done so?? You can take individual datafiles or temp files of a tablespace offline or similarly, bring them online. Offline datafiles are unavailable to the database and cannot be accessed until they are brought back online. You also have the option of taking all datafiles or temp files comprising a tablespace offline or online simply by specifying the name of a tablespace. One example of where you might be required to alter the availability of a data file is when Oracle has problems writing to a data file and automatically takes the data file offline. Later, after resolving the problem, you can bring the data file back online manually. The files of a read-only tablespace can independently be taken offline or brought online just as for read-write tablespaces. Bringing a data file online in a read-only tablespace makes the file readable. No one can write to the file unless its associated tablespace is returned to the read-write state. To take a data file offline, or bring it online, you must have the ALTER DATABASE system privilege. To take all datafiles or temp files offline using the ALTER TABLESPACE statement, you must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. In an Oracle Real Application Clusters environment, the database must be open in exclusive mode. Is there any difference between taking data files offline for the databases with archivelog and noarchivelog options?? Yes. Here are some examples. Archivelog Mode – bringing a data file online ALTER DATABASE DATAFILE '< data file name with path >’ ONLINE; - taking the data file offline ALTER DATABASE DATAFILE '< data file name with path >’ OFFLINE; NOARCHIVELOG Mode To take a data file offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE DROP clauses. This enables you to take the data file offline and drop it immediately. It is useful, for example, if the data file contains only data from temporary segments and has not been backed up and the database is in NOARCHIVELOG mode. ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP; For taking the data files ONLINE there is no difference between the databases with archive log mode on or off. Some times ‘alter database’ and some times ‘alter tablespace’ is used to take data files offline or online. Why is it so?? In most cases the above ALTER TABLESPACE statements can be issued whenever the database is mounted, even if it is not open. However, the database must not be open if the tablespace is the system tablespace, an undo tablespace, or the default temporary tablespace. The ALTER DATABASE DATAFILE and ALTER DATABASE TEMPFILE statements also have ONLINE/OFFLINE clauses, however in those statements you must enter all of the filenames for the tablespace. The syntax is different from the ALTER TABLESPACE ... ONLINE|OFFLINE statement that alters a tablespace's availability, because that is a different operation. The ALTER TABLESPACE statement takes datafiles offline as well as the tablespace, but it cannot be used to alter the status of a temporary tablespace or its tempfile(s). Can I rename the data files associated with a single tablespace?? If it is not a SYSTEM tabelspace, you can do this. Here are the steps (1)
Take the non-SYSTEM tablespace that contains the datafiles offline It is always advisable to backup the database after every structural change. Or at the least backup the control file to trace. How to Relocate and Rename Datafiles in a Single Tablespace??
SELECT
FILE_NAME, BYTES FROM DBA_DATA_FILES FILE_NAME
BYTES
The datafiles of the users tablespace are to be relocated to different and separate disk drives. Get connected as: You are currently connected with administrator privileges to the open database like SYS. You have a current backup of the database. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace. Copy the datafiles to their new locations and rename them using the operating system. You can execute an operating system command to copy a file by using the SQL*Plus HOST command. Rename the datafiles within Oracle. The data file pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names. If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement. ALTER
TABLESPACE users
If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. Can I rename the data files associated with a multiple tablespaces?? You can rename and relocate datafiles of one or more tablespaces using ALTER DATABASE statement with the RENAME FILE clause. This option is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation, or rename or relocate datafiles of the SYSTEM tablespace. If the database must remain open, consider instead the procedure outlined in the previous section. To rename datafiles of several tablespaces in one operation or to rename datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system privilege. Ensure that the database is mounted but closed. Copy the datafiles to be renamed to their new locations and new names, using the operating system. Use ALTER DATABASE to rename the file pointers in the database's control file. For example, the following statement renames the datafiles C:\ORADATA\WHS\TOOLS\TOOLS01.DBF
and ALTER
DATABASE
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup. How
can I drop the data files?? (As Said by Oracle) File Mapping Components
FMON is responsible for: Building
mapping information, which is stored in the SGA. This information is composed
of the following structures: Refreshing
mapping information when a change occurs because of: External
Process (FMPUTL) The external process is responsible for discovering the mapping libraries and dynamically loading them into its address space. Mapping
Libraries Mapping libraries need to exist for all levels of the stack for the mapping to be complete, and different libraries may own their own parts of the I/O mapping stack. For example, a VERITAS VxVM library would own the stack elements related to the VERITAS Volume Manager, and an EMC library would own all EMC storage specific layers of the I/O mapping stack. Mapping libraries are vendor supplied. However, Oracle currently supplies a mapping library for EMC storage. The mapping libraries available to a database server are identified in a special file named filemap.ora. Mapping Structures The mapping structures and Oracle's representation of these structures are described in this section. You will need to understand this information in order to interpret the information in the mapping views. The following are the primary structures that compose the mapping information: Files A file mapping structure provides a set of attributes for a file, including file size, number of file system extents that the file is composed of, and the file type. File system extents A file system extent mapping structure describes a contiguous chunk of blocks residing on one element. This includes the device offset, the extent size, the file offset, the type (data or parity), and the name of the element where the extent resides. Note:
Elements An element mapping structure is the abstract mapping structure that describes a storage component within the I/O stack. Elements may be mirrors, stripes, partitions, RAID5, concatenated elements, and disks. These structures are the mapping building blocks. Sub-elements A sub-element mapping structure describes the link between an element and the next elements in the I/O mapping stack. This structure contains the sub-element number, size, the element name where the sub-element exists, and the element offset.
|