an immediate and complete backup.
How to relocate datafile in a single tablesapce?
Step:1 Use following query to know the specifiec file name or size.
select file_name,bytes from dba_data_files where tablespace_name='<tablespace_name>'; Step: 2 take the tablespace containing the datafiles offline:
alter tablespace <Tablespace_Name> 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 <Tablespace_Name> 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
'/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.
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.
Important: 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 makes 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 WHERE
tablespace_name ='<name of tablespace>'; 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 <tablespace name> 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.
If you have more than one datafile in the tablespace, and you wnat to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above: DROP TABLESPACE <tablespace name> 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 tablesapce 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 WHERE
tablespace_name ='<name of tablespace>';
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='<name of tablespace>'
Step : 3 Now, export all the objects that you wish to keep. Step : 4 Once the export is done, issue the
DROP TABLESPACE <tablespace name> 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, 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 <filename> 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 <datafile name> OFFLINE DROP
command is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.
Important : If you are running in archivelog mode, you can also use: ALTER DATABASE
DATAFILE <datafile name> 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 Control Files
A control file is a small binary file that records the physical structure of the database with database name, Names and locations of associated datafiles, online redo log files, timestamp of the database creation, current log sequence number and Checkpoint information.
Note:
Without the control file, the database cannot be mounted.
You should create two or more copies of the control file during database creation.
Role of Control File:
When Database instance mount, Oracle recognized all listed file in Control file and open it. Oracle writes and maintains all listed control files during database operation.
Important:
If you do not specify files for CONTROL_FILES before database creation, and you are not using the Oracle Managed Files feature, Oracle creates a control file in <DISK>:\ORACLE_HOME\DTATBASE\ location and uses a default filename. The default name is operating system specific.
Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down.
Oracle writes to all filenames listed for the initialization parameter CONTROL_FILES in the database's initialization parameter file.
The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle database server during database operation.
If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.
How to Create Control file at the time of database creation:
The initial control files of an Oracle database are created when you issue the CREATE
DATABASE statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation.
How to Create Additional Copies, Renaming, and Relocating Control Files
Step: 1 shut down the database.
Step: 3 edit the CONTROL_FILES parameter in the database's initialization parameter file to add
the new control file's name, or to change the existing control filename. Step: 4 restart the database.
When you Create New Control Files?
All control files for the database have been permanently damaged and you do not have a control file backup.
You want to change one of the permanent database parameter settings originally specified in the CREATE DATABASE statement. These settings include the database's name and the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.
Steps for Creating New Control Files
Step: 1 Make a list of all datafiles and online redo log files of the database.
SELECT MEMBER FROM V$LOGFILE; SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';
Step: 2 shut down the database.
Step: 3 back up all datafiles and online redo log files of the database.
Step: 4 Start up a new instance, but do not mount or open the database: STARTUP NOMOUNT
Step: 5 create a new control file for the database using the CREATE CONTROLFILE statement. Example:
CREATE CONTROLFILE REUSE DATABASE "<DB_NAME" NORESETLOGS NOARCHIVELOG MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE
GROUP 1 '<DISK>:\Directory\REDO01.LOG' SIZE 5024K, GROUP 2 '<DISK>:\Directory\REDO02.LOG' SIZE 5024K, GROUP 3 '<DISK>:\Directory\REDO03.LOG' SIZE 5024K # STANDBY LOGFILE
DATAFILE
'<DISK>:\Directory\SYSTEM.DBF', '<DISK>:\Directory\UNDOTBS.DBF' CHARACTER SET WE8MSWIN1252 ;
If you specify NORESETLOGS when creation the control file, use following commands: ALTER DATABASE OPEN;
If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.
ALTER DATABASE OPEN RESETLOGS; TIPS:
When creating a new control file, select the RESETLOGS option if you have lost any online redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs . You must also specify the RESETLOGS option if you have renamed the database. Otherwise, select the NORESETLOGS option.
Backing Up Control Files Method 1:
Back up the control file to a binary file (duplicate of existing control file) using the following statement:
ALTER DATABASE BACKUP CONTROLFILE TO '<DISK>:\Directory\control.bkp'; Method 2:
Produce SQL statements that can later be used to re-create your control file: ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
How to retrieve information related to Control File:
V$DATABASE (Displays database information from the control file) V$CONTROLFILE (Lists the names of control files)
V$CONTROLFILE_RECORD_SECTION (Displays information about control file record sections)
Managing Redo Log Files
Redo logs consists of two or more pre allocated files that store all changes made to the database. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.
Main points to consider before creating redo log files?
Members of the same group should be stores in separate disk so that no single disk failure can cause LGWR and database instance to fail.
Set the archive destination to separate disk other than redo log members to avoid contention between LGWR and Arch.
With mirrored groups of online redo logs , all members of the same group must be the same size.
Parameters related to redo log files are MAXLOGFILES
MAXLOGMEMEBERS
MAXLOGFILES and MAXLOGMEMEBERS parameters are defined while creation of
database. You can increase these parameters by recreating the control file. How do you create online Redo log group?
Alter database add logfile group <group Number> (‗<DISK>:\Directory\<LOG_FILE_NAME>.log‘,‘
(‗<DISK>:\Directory\<LOG_FILE_NAME>.log‘) size 500K; How to check the status of added redo log group?
Select * form v$log; Interpretation:
Here you will observe that status is UNUSED means that this redo log file is not being used by oracle as yet. ARC is the archived column in v$log , it is by default YES when you create a redo log file. It will returns to NO if the system is not in archive log mode and this file is used by oracle. Sequence# 0 also indicate that it is not being used as yet.
How to create online redo log member ? alter database add logfile member
'<DISK>:\Directory\<LOG_FILE_NAME>.log‘,'<DISK>:\Directory\<LOG_FILE_NAME>.log‘ ' to group <GROUP NUMBER>;
How to rename and relocate online redo log members?
Important: Take the backup before renaming and relocating. Step:1 Shutdown the database .
Step: 2 Startup the database as startup mount.
Step: 3 Copy the desired redo log files to new location. You can change the name of redo log file
in the new location.
Step:4
Alter database rename file ‗<DISK>:\Directory\<LOG_FILE_NAME>.log‘ to ‗<new path><DISK>:\Directory\<LOG_FILE_NAME>.log‘,
Step: 5 alter database open;
How to drop online redo log group? Important:
You must have at- least two online groups.
You can not drop a active online redo log group. If it active switch it by alter system switch logfile before dropping.
Also make sure that online redo log group is archived ( if archiving is enabled). Syntax:
If you want to drop log group:
Alter database drop logfile group <GROUP_NUMBER>; If you want to drop a logfile member:
Alter database drop logfile member ‘ <DISK>:\Directory\<LOG_FILE_NAME>.log‘; How to Viewing Online Redo Log Information?
Select * from v$log; Select * from v$logfile;
Note: If STATUS is blank for a member, then the file is in use. Temporary Tablespace
First we will discus about use of temporary tablespace. We use it to manage space for database sort operation. For example: if we join two large tables it require space for sort operation because oracle cannot do shorting in memory. This sort operation will be done in temperory tablespace. We must assign a temporary tablespace to each user in the database; if we don‘t assign temperory tablespace to user in the database oracle allocate sort space in the SYSTEM tablespace by default.
Important:
That a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.
When we create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.
TEMPFILEs are not recorded in the database's control file.
We cannot remove datafiles from a tablespace until you drop the entire tablespace but we can remove a TEMPFILE from a database:
SQL>ALTER DATABASE TEMPFILE ''<disk>:\<directory>\<Tablespace Name>.dbf' DROP INCLUDING DATAFILES;
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).
How does create Temporary Tablespaces? CREATE TEMPORARY TABLESPACE temp
TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf' size 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.
How can define Default Temporary Tablespaces?
We can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; Important:
The default ―Default Temporary Tablespace‖ is SYSTEM.
Each database can be assigned one and only one Default Temporary Tablespace. Temporary Tablespace is automatically assigned to users.
Restriction:
The following 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.
How to see the default temporary tablespace for a database? SELECT * FROM DATABASE_PROPERTIES where
PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; How to Monitoring Temporary Tablespaces and Sorting? Use following query to view temp file information:
Select * from dba_temp_files; or Select * from v$tempfile; Use following query for monitor temporary segment
Select * from v$sort_segments or Select * from v$sort_usage Use following query for free space in tablespace :
select TABLESPACE_NAME,BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
How to Dropping / Recreating Temporary Tablespace? (Method)
This should be performed during off ours with no user logged on performing work.
If you are working with a temporary tablespace that is NOT the default temporary tablespace for the database, this process is very simple. Simply drop and recreate the temporary tablespace: Step: 1 Drop the Tablespace
DROP TABLESPACE temp; Tablespace dropped.
Step: 2 Create new temporary tablespace.
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
How to Dropping / Recreating Default Temporary Tablespace? (Method)
You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:
DROP TABLESPACE temp; drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace Step: 1 Create another temperory tablespace.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf'SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;