--Long operations
select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,
time_remaining remaning,elapsed_seconds elapsed,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 (37,43)
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.program
from v&session a, v&process b
where a.paddr=b.addr and a.username is not null
--and (sid=163 or spid=28179)
order by status, sid;
--Kill session
alter system kill session '&sid,&serial';
Showing posts with label Architecture Of Oracle. Show all posts
Showing posts with label Architecture Of Oracle. Show all posts
Sunday, December 23, 2007
simple-log-management
select * from v&log; --status of logfile groups
select * from v&logfile order by group#; --status of logfiles
select * from v&instance; --status of the archiver
alter system archive log start; --restart the archiver
alter system switch logfile; --switch online log
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;
alter system archive log start; -- restarts the archiver
select * from v&archive_dest; -- archiver destinations
--Altering 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_size
from v&archived_log
where first_time >= (trunc(sysdate)-30)
group by trunc(first_time);
select * from v&logfile order by group#; --status of logfiles
select * from v&instance; --status of the archiver
alter system archive log start; --restart the archiver
alter system switch logfile; --switch online log
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;
alter system archive log start; -- restarts the archiver
select * from v&archive_dest; -- archiver destinations
--Altering 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_size
from v&archived_log
where first_time >= (trunc(sysdate)-30)
group by trunc(first_time);
Thursday, October 11, 2007
Managing Tablespace
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.
Type 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 ……. REUSE
MENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)
BLOCKSIZE
LOGGING | 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 clause
Example:
CREATE TABLESPACE "USER1"
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSE
BLOCKSIZE 8192
LOGGING
ONLINE
PERMANENT
EXTENT 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 2
MAXEXTENTS 121
PCTINCREASE 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
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.
Type 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 ……. REUSE
MENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)
BLOCKSIZE
LOGGING | 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 clause
Example:
CREATE TABLESPACE "USER1"
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10m REUSE
BLOCKSIZE 8192
LOGGING
ONLINE
PERMANENT
EXTENT 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 2
MAXEXTENTS 121
PCTINCREASE 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 users
DATAFILE 'C:\LOCAL\ORADATA\USER_DATA.DBF' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
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 Query related to Tablesapce
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;
TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
RBS 1048576 1048576 2 40 0
SYSTEM 106496 106496 1 99 1
TEMP 106496 106496 1 99 0
TESTTBS 57344 16384 2 10 1
USERS 57344 57344 1 99 1
How to retrieve information tablesapce and associated datafile?
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;
FILE_NAME BLOCKS TABLESPACE_NAME
------------ ---------- -------------------
/U02/ORACLE/IDDB3/RBS01.DBF 1536 RBS
/U02/ORACLE/IDDB3/SYSTEM01.DBF 6586 SYSTEM
/U02/ORACLE/IDDB3/TEMP01.DBF 6400 TEMP
/U02/ORACLE/IDDB3/TESTTBS01.DBF 6400 TESTTBS
/U02/ORACLE/IDDB3/USERS01.DBF 384 USERS
How to retrive 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;
TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL
---------- ------- ------ ------- ------- ------- ------
RBS 2 1 955 955 955 955
SYSTEM 1 1 119 119 119 119
TEMP 4 1 6399 6399 6399 6399
TESTTBS 5 5 6364 3 1278 6390
USERS 3 1 363 363 363 363
PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.
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;
TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE
---------- ----------- -------- ------- ------- ------------
RBS 1048576 1048576 2 40 0
SYSTEM 106496 106496 1 99 1
TEMP 106496 106496 1 99 0
TESTTBS 57344 16384 2 10 1
USERS 57344 57344 1 99 1
How to retrieve information tablesapce and associated datafile?
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;
FILE_NAME BLOCKS TABLESPACE_NAME
------------ ---------- -------------------
/U02/ORACLE/IDDB3/RBS01.DBF 1536 RBS
/U02/ORACLE/IDDB3/SYSTEM01.DBF 6586 SYSTEM
/U02/ORACLE/IDDB3/TEMP01.DBF 6400 TEMP
/U02/ORACLE/IDDB3/TESTTBS01.DBF 6400 TESTTBS
/U02/ORACLE/IDDB3/USERS01.DBF 384 USERS
How to retrive 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;
TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL
---------- ------- ------ ------- ------- ------- ------
RBS 2 1 955 955 955 955
SYSTEM 1 1 119 119 119 119
TEMP 4 1 6399 6399 6399 6399
TESTTBS 5 5 6364 3 1278 6390
USERS 3 1 363 363 363 363
PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.
How to drop a datafile from a tablespace?
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 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
WHERE
tablespace_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 wnat 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 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 ='';
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, 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 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 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.
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 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
WHERE
tablespace_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
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 wnat to remove all datafiles and also no need the information contained in that tablespace, then use the same command as above:
DROP TABLESPACE
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 ='
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
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
Important : The ALTER DATABASE DATAFILE
Important : If you are running in archivelog mode, you can also use: ALTER DATABASE 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 teblespace. 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 execting 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 specifiec 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.
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 teblespace. 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 execting tablespace?
alter tablespace
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
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
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 specifiec 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
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
'/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.
Tuesday, October 9, 2007
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 topics:
Oracle Database Data Blocks
Extents
Segments
Tablespaces
Oracle Database Data Blocks
At 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.
Extents
The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
Segments
Above 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.
Tablespaces
A 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 smallfile tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as smallfile 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 datafiles.
This section includes the following topics:
Oracle Database Data Blocks
Extents
Segments
Tablespaces
Oracle Database Data Blocks
At 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.
Extents
The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
Segments
Above 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.
Tablespaces
A 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 smallfile tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as smallfile 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 datafiles.
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 Files
Every 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 Files
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 Files
When 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 Files
Parameter files contain 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 of maintaining initialization parameters.
Alert and Trace Log Files
Each 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 Files
To 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.
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 Files
Every 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 Files
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 Files
When 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 Files
Parameter files contain 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 of maintaining initialization parameters.
Alert and Trace Log Files
Each 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 Files
To 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.
Subscribe to:
Posts (Atom)