Management of Oracle data files and temporary files

I. Overview of data Files
In an Oracle database, the system and Sysaux table spaces need to contain at least one data file, as well as several additional tablespaces and their associated data files and temporary files. Oracle's data files and temporary files are operating system files that belong to the database's physical structure and are used to store data in the logical structure of the database. When you create a tablespace, you must explicitly specify a data file for each tablespace.

Oracle assigns numbers to files in two ways: absolute file numbers that uniquely identify data files in the database, absolute file numbers can be queried through the file# column of the v$datafile or V$tempfile view, or through Dba_data_files or Dba_ file_id column query for temp_files view; a relative file number that uniquely identifies a data file within a tablespace. In most cases, absolute file numbers and relative file numbers are equal, but when the number of data files in a database exceeds a threshold (say 1023), they are no longer equal. The relative file number of a large file tablespace data file is always 1024.

Query the absolute file number and relative file number of the data file:
Select T.name tablespace_name,d.file#,d.rfile#,d.name file_name from V$tablespace t,v$datafile D where t.ts#=d.ts#;
Tablespace_name file# rfile# file_name
-------------------- ---------- ---------- --------------------------------------------------
SYSTEM 1 1/u01/app/oracle/oradata/stdb/system01.dbf
UNDOTBS1 2 2/u01/app/oracle/oradata/stdb/undotbs01.dbf
Sysaux 3 3/u01/app/oracle/oradata/stdb/sysaux01.dbf
USERS 4 4/U01/APP/ORACLE/ORADATA/STDB/USERS01.DBF
USERS 8 8/U01/APP/ORACLE/ORADATA/STDB/USER02.DBF
EXAMPLE 5 5/u01/app/oracle/oradata/stdb/example01.dbf
TEST 7 7/u01/app/oracle/oradata/stdb/test02.dbf
TEST 6 6/U01/APP/ORACLE/ORADATA/STDB/TEST01.DBF
Bigtbs 9 1024/u01/app/oracle/oradata/stdb/bigfile01.dbf
9 rows selected.

The number of data files that can be created by the database ... The effect of the Maxdatafiles statement and the Db_files parameter. Also, be aware of the mandatory restrictions on how many files the operating system creates. During instance startup, Oracle allocates the space in the SGA for storing data file information based on the initialization parameter db_files, which can be used to determine the maximum number of files that can be created by this parameter value. The parameter can be modified, but the database must be restarted to take effect, which is valid throughout the lifetime of the instance.

The amount of data the data file contains in a tablespace can ultimately affect the performance of the database. Oracle allows the number of online files to exceed the operating system default limit, the DBWN process can open all online data files, and the ability to cache all processed files, when the amount of open file data reaches the operating system's default limit, Oracle will automatically close the file. This can have a negative impact on performance, and it is recommended that you adjust the operating system's default limit value to be larger than the number of online files in the database.

Ii. Creating and adding files for a table space
The method of creating and adding data files to a tablespace has been studied before, and here is just a brief review.
1. Create a data file while creating a table space
Create Tablespae ... datafile '/xxx/xxxx/xxxx ... ' size xx
Create temporary tablespace datafile '/xxx/xxxx/xxxx ... ' size xx
2. Add a data file for the table space that already exists
Alter tablespace ... add datafile '/xxx/xxxx/xxxx ... ' Size XX
Alter tablespace ... add tempfile '/xxx/xxxx/xxxx ... ' Size XX

Third, modify the data file size
To add a data file to a tablespace using the auto-extend clause:
sql> Create Tablespace Test_tbs
2 datafile '/u01/app/oracle/oradata/stdb/test03.dbf ' size 10m
3 Autoextend on
4 Next 1m
5 maxsize 100m;
Tablespace created.

Open Data File Auto-expansion:
sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST02.DBF '
2 Autoextend on
3 Next 1m
4 maxsize 100m;
Database altered.

To turn off automatic extension of data files:
sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST02.DBF '
2 autoextend off;
Database altered.

Manual Resize data File size:
sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST03.DBF ' resize 20m;
Database altered.

iv. Modification of data file availability
You can modify the availability of the data file by performing both online and offline operations of the data file, and the offline data file cannot be accessed by the database until it is restored to the online state. The data files in the read-only table space can also be offline or online, and the data files in the read-only table space do not affect the state of the tablespace itself, regardless of whether they are not writable until the table space is not read-write.

1. Offline data file in archive mode
sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST03.DBF ' offline;
Database altered.

sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST03.DBF ' online;
ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST03.DBF ' online
*
ERROR at line 1:
Ora-01113:file needs Media recovery//test03.dbf file is offline without triggering a checkpoint, so the file resumes online and prompts for media recovery.
Ora-01110:data file: '/u01/app/oracle/oradata/stdb/test03.dbf '

Sql> Archive Log List
Database Log Mode Archive mode
Automatic Archival Enabled
Archive destination/u02/arch_1
Oldest online log sequence 5
Next Log sequence to archive 7
Current Log Sequence 7

sql> recover datafile 10;
Media recovery complete.

sql> ALTER DATABASE datafile '/U01/APP/ORACLE/ORADATA/STDB/TEST03.DBF ' online;
Database altered.

2. Offline data file in non-archive mode
Use the ALTER DATABASE ... offline for DROP statement offline data file in non-archive mode. The offline keyword marks the data file offline, whether or not it is damaged, so you can open the database, and the For Drop keyword marks the data file that is then deleted and the data file cannot be restored to the online state again. (In fact, before the online log group has not switched on, you can still return to the online state)

sql> ALTER DATABASE datafile ' D:\APP\MANGANESE\ORADATA\ORCL\TEST01.DBF ' offline for drop;
The database has changed.

sql> alter system switch logfile;
The system has changed.

sql> alter system switch logfile;
The system has changed.

sql> alter system switch logfile;
The system has changed.

sql> ALTER DATABASE datafile ' D:\APP\MANGANESE\ORADATA\ORCL\TEST01.DBF ' online;
ALTER DATABASE datafile ' D:\APP\MANGANESE\ORADATA\ORCL\TEST01.DBF ' online
*
An error occurred on line 1th:
ORA-01113: File 7 requires media recovery
ORA-01110: Data file 7: ' D:\APP\MANGANESE\ORADATA\ORCL\TEST01. DBF '

sql> recover datafile 7;
ORA-00279: Change 1108304 (generated on 03/19/2014 15:44:41) is required for thread 1
ORA-00289: Recommended:
D:\APP\MANGANESE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_19\O1_MF_1_9_%U_. ARC
ORA-00280: Change 1108304 (for thread 1) in the sequence #9
Specify log: {=suggested | filename | AUTO | CANCEL}
Auto
ORA-00308: Unable to open archive log
' D:\APP\MANGANESE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_19\O1_MF_1_9_%U_. ARC '
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 2) The system cannot find the specified file.
ORA-00308: Unable to open archive log
' D:\APP\MANGANESE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_19\O1_MF_1_9_%U_. ARC '
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 2) The system cannot find the specified file.

3. Modify the availability of all data files and temporary files in the tablespace
Alter tablespace ... datafile {online | offline}
Alter tablespace ... tempfile {online | offline}

Five, rename (Modify storage location) data File
1. Take the table space that contains the data file offline.
2. Modify the data file name using the operating system command.
3. Use ALTER DATABASE ... rename datafile ... Statement changes the data file name in the database.
4, back up the database.

Example one: Renaming a data file in the same tablespace
sql> alter tablespace test_tbs offline normal;
Tablespace altered.

[Email protected] stdb]$ MV TEST03.DBF test04.dbf

Sql> Alter Tablespace TEST_TBS
2 rename datafile '/u01/app/oracle/oradata/stdb/test03.dbf '
3 to '/u01/app/oracle/oradata/stdb/test04.dbf ';
Tablespace altered.

sql> alter tablespace TEST_TBS online;
Tablespace altered.

Example two: Renaming a data file in a different table space
Sql> select status from V$instance;
STATUS
------------
Mounted//The operation must be performed under Mount

Sql> ALTER DATABASE
2 rename file '/u01/app/oracle/oradata/stdb/test01.dbf ',
3 '/u01/app/oracle/oradata/stdb/test02.dbf ',
4 '/u01/app/oracle/oradata/stdb/test04.dbf '
5 to
6 '/u01/app/oracle/oradata/stdb/test1.dbf ',
7 '/u01/app/oracle/oradata/stdb/test2.dbf ',
8 '/u01/app/oracle/oradata/stdb/test3.dbf ';

Database altered.

sql> ALTER DATABASE open;
Database altered.

vi. Deleting data files

To delete a data file from within a tablespace:
Alter tablespace. Drop datafile ...;
Alter tablespace. Drop Tempfile ...;

To delete a data file from the database:
ALTER DATABASE Tempfile '/xxx/xxxx/... ' drop including datafiles;

sql> ALTER DATABASE Tempfile '/u01/app/oracle/oradata/stdb/temp03.dbf ' drop including datafiles;
Database altered.

sql> ALTER DATABASE datafile '/u01/app/oracle/oradata/stdb/test1.dbf ' drop including datafiles; Note: Data files cannot be deleted in such a way
ALTER DATABASE datafile '/u01/app/oracle/oradata/stdb/test1.dbf ' drop including datafiles
*
ERROR at line 1:
Ora-01916:keyword ONLINE, OFFLINE, RESIZE, autoextend or End/drop expected

Attention:
1. Cannot be deleted when migrating from dictionary management to data files in a locally managed read-only tablespace. In addition, data files in other read-only table spaces can be deleted.
2. Data files in the system tablespace cannot be deleted.
3. If a locally managed table space is offline, the data file cannot be deleted.
sql> alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf ';
Alter tablespace Test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf '
*
ERROR at line 1:
Ora-03264:cannot drop offline datafile of locally managed tablespace
4. If the tablespace contains only one data file, the data file cannot be deleted.
sql> alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf ';
Alter tablespace Test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf '
*
ERROR at line 1:
Ora-03261:the tablespace Test_tbs have only one file
5. If the data file is not empty, the data file cannot be deleted.
6. Deleting a data file must ensure that the data block is open.

zhuan:http://blog.itpub.net/29515435/viewspace-1125105/

Management of Oracle data files and temporary files