Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 9 June 2012

Datafile/Tempfile/Undofile Alteration in Oracle

Datafiles are physical files of the operating system that store the data of all logical structures in the database. Database assigns each datafile two associated file numbers, an absolute file number and a relative file number that are used to uniquely identify it.
Note: Relative and absolute File Number usually having the same number however when the number of datafiles in database exceeds a threshold (1023) then relative file number differ from absolute file number.
Number and size of Datafiles:
The DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus the maximum number of datafiles that can be created for the instance. You 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.
When determining a value for DB_FILES, take the following into consideration:
·   If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.
·   If the value of DB_FILES is too high, memory is unnecessarily consumed.
Note: The number of datafiles contained in a tablespace, and ultimately the database, can have an impact upon performance. A Tablespace must have at least one datafile. Further you can add more datafile to increase the size of datafile.
Place Datafiles Separately:
Place the datafiles on separate disk so that users query information disk drives can work simultaneously retrieving data at the same time.
Store Datafiles Separate from Redo Log Files
Datafiles should not be stored on the same disk drive that stores the redo log files. If the datafiles and redo log files are stored on the same disk drive and that disk drive fails then the files cannot be used in your database recovery procedures. Multiplex your redo log files, then the chance of losing all of your redo log files is low and you can store datafiles on the same drive as some redo log files.
Creating and Adding Datafiles to a Tablespace: (Using any of the statement)
CREATE TABLESPACE
ALTER TABLESPACE ... ADD DATAFILE
CREATE DATABASE
ALTER DATABASE ... CREATE DATAFILE
Note: If you add new datafiles to a tablespace and do not fully specify the filenames then the location of datafiles in the default database directory or the current directory. Oracle recommends you always specify a fully qualified name for a datafile.
Changing Datafile Size:
There are two way to Alter the size of datafiles.
·   Enabling and Disabling Automatic Extension for a Datafile
·   Manually Resizing a Datafile
Enabling/Disabling Auto-Extension for a Datafile:
ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON NEXT 512K MAXSIZE 250M;
Note: The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;
Manually Resizing a Datafile:
For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
Note: The size can be decreased of a file to specific value. In case of bigfile tablespace you are not allowed to add a datafile.
Taking Datafiles Online or Taking Offline in ARCHIVELOG Mode:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Note: The datafiles of a read-only tablespace can be taken offline or brought online, but bringing a file online does not affect the read-only status of the tablespace. You cannot write to the datafile until the tablespace is returned to the read/write state. You can make all datafiles of a tablespace temporarily unavailable by taking the tablespace itself offline.
Taking Datafiles Offline/Online:
ALTER TABLESPACE <Tablespace_name> DATAFILE {ONLINE|OFFLINE};
ALTER TABLESPACE < Tablespace_name> TEMPFILE {ONLINE|OFFLINE};
Note: 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).
To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;
It does not actually drop the datafile. It remains it in the data dictionary, and you must drop it yourself using An ALTER TABLESPACE ... DROP DATAFILE statement.
Note: The datafiles of a read-only tablespace can be taken offline or online, but bringing a file online does not affect the read-only status of the tablespace. You cannot write to the datafile until the tablespace is returned to the read/write state. You can make all datafiles of a tablespace temporarily unavailable by taking the tablespace itself offline.
Renaming and Relocating Datafiles (Single Tablespace)
1.      Take the tablespace that contains the datafiles offline (database must be open).
ALTER TABLESPACE users OFFLINE NORMAL;
2.      Rename the datafiles using the operating system command.
3.      Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user02.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';
4.      Back up the database. After making any structural changes to a database.
Relocating Datafiles in a Single Tablespace
1.      Check the file name or size using DBA_data_files view.
SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
FILE_NAME                                  BYTES
------------------------------------------ ----------------
/u02/oracle/rbdb1/users01.dbf         102400000
/u02/oracle/rbdb1/users02.dbf         102400000
2.      Take the Tablespace containing the datafiles offline:
ALTER TABLESPACE users OFFLINE NORMAL;
3.      Copy or Move the datafiles to their new locations and rename them
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf' TO '/u03/oracle/rbdb1/users01.dbf', '/u04/oracle/rbdb1/users02.dbf';
4.      Back up the database. After making any structural changes to a database.
Renaming and Relocating Datafiles in Multiple Tablespaces
For that you must have the ALTER DATABASE system privilege.
1.      Ensure that the database is mounted.
2.      Copy the datafiles to be renamed to their new locations and new names.
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;
4.      Back up the database. After making any structural changes to a database.
Note: To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.
Dropping Datafiles
Alter Database Datafile 'C:\Oracle1\Oradata\Shaan\Users01.Dbf' Offline Drop;
Alter Tablespace Users Drop Datafile 'C:\Oracle1\Oradata\Shaan\Users01.Dbf';
Restrictions for Dropping Datafiles
·         The Database must be open.
·         If a datafile is not empty, it cannot be dropped.
·         You cannot drop the first or only datafile in a tablespace.
·         This means that DROP DATAFILE cannot be used with a bigfile tablespace.
·         You cannot drop datafiles in a read-only tablespace.
·         You cannot drop datafiles in the SYSTEM tablespace.
·         If a datafile in a locally managed tablespace is offline, it cannot be dropped.
Temp Tablespace and Tempfiles:
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, 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 such as the 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:
SQL> CREATE USER SNT DEFAULT TABLESPACE Data TEMPORARY TABLESPACE Temp;
SQL> ALTER USER SNT TEMPORARY TABLESPACE temp;
Note: Temporary tablespace cannot contain permanent objects so doesn't need to be backed up.
Tempfiles:
Tempfiles are similar to ordinary datafiles with the following exceptions:
·         Tempfiles are always in nologging mode and You cannot make a Tempfiles read only.
·         You cannot rename a Tempfiles and You cannot create a tempfiles with the ‘Alter Database’ command.
·         When you create a tempfiles, they are not always guaranteed allocation of disk space for the file size specified.
·         When you create a TEMPFILE, Oracle only writes to the header and last block of the file.
·         TEMPFILEs are not recorded in the database's control file that means one can just recreate them whenever you restore the database or after accidental delete.
·         One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database.
·         If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Then recreate a tempfile.
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;
Note: Except for adding a tempfile you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).
SQL> Create Temporary Tablespace Temp Tempfile '/Oradata/Mytemp_01.Tmp' Size 20m Extent Management Local Uniform Size 16m;
SQL> CREATE TEMPORARY TABLESPACE temp;
Dropping Tempfiles
SQL>Alter Tablespace Lmtemp Drop Tempfile '/U02/Oracle/Data/Lmtemp02.Dbf';
SQL>Alter Database Tempfile '/U02/Oracle/Data/Lmtemp02.Dbf' Drop Including Datafiles;
Default Temporary Tablespaces:
In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:
SQL>Alter Database Default Temporary Tablespace Temp;
The default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users.
Restrictions apply to default temporary tablespaces:
·         The default Temporary Tablespace must be of type temporary
·         The default Temporary Tablespace cannot be taken off-line
·         The default Temporary Tablespace cannot be dropped until you create another one.
Monitoring Temporary Tablespace
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor Temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
SQL> Select TABLESPACE_NAME, BYTES_USED, BYTES_FREE From V$TEMP_SPACE_HEADER;
TABLESPACE_NAME       BYTES_USED   BYTES_FREE
------------------    ----------   ----------
TEMP                  52428800     52428800
Monitoring Default Database Properties
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
Note: All new users that are not explicitly assigned a TEMPORARY Tablespace, will get the Default Temporary Tablespace as its TEMPORARY Tablespace. Also when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.
Undo?
Database creates and manages information that is used to roll back or undo, changes to the database. Such information consists of records of the actions of transactions before they are committed. These records are collectively referred to as undo.
The Undo records are used to:
·         Roll back transactions when a ROLLBACK statement is issued
·         Recover the database
·         Provide read consistency
·         Analyze data as of an earlier point in time by using Oracle Flashback Query
·         Recover from logical corruptions using Oracle Flashback features
Using CREATE DATABASE to Create an Undo Tablespace
CREATE DATABASE rbdb1
     CONTROLFILE REUSE
     .
     UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oracle/rbdb1/undo0101.dbf';
Using the CREATE UNDO TABLESPACE Statement:
SQL>Create Undo Tablespace Undotbs_02 Datafile '/U01/Oracle/Rbdb1/Undo0201.Dbf' Size 2m Reuse Autoextend On;
Adding a datafile to the Undo Tablespace:
Alter Tablespace Undotbs_01 Add Datafile '/U01/Oracle/Rbdb1/Undo0102.Dbf' Autoextend On Next 1m Maxsize Unlimited;
Dropping an Undo Tablespace:
An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions the DROP TABLESPACE statement fails.
SQL>DROP TABLESPACE undotbs_01;
Note: If you drop an Undo Tablespace all contents of UNDOTBS will drop. Be careful not to drop undo tablespace if undo information is needed by some existing queries.
Switching Undo Tablespaces:
SQL>ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
Note: If the parameter value for UNDO TABLESPACE is set to ‘’ (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Please use this statement with care.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = '';
Monitoring UNDO Information:
V$UNDOSTAT, V$ROLLSTAT, V$TRANSACTION, DBA_UNDO_EXTENTS, DBA_HIST_UNDOSTAT

1 comments:

  1. Teeth Night Guard is offering personalized fitting and high quality customized teeth guards.

    ReplyDelete