Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to master the management of Oracle data files and temporary files". In daily operation, I believe many people have doubts about how to master the management of Oracle data files and temporary files. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to master the management of Oracle data files and temporary files". Next, please follow the editor to study!

I. Overview of data files

In an Oracle database, SYSTEM and SYSAUX tablespaces need to contain at least one data file, in addition to multiple other tablespaces and their associated data files and temporary files. The data files and temporary files of Oracle are operating system files, which belong to the physical structure of the database and are used to store the data of the logical structure in the database. When creating tablespaces, you must explicitly specify data files for each tablespace.

Oracle assigns numbers to files in two ways: absolute file numbers, which are used to uniquely identify data files in the database, absolute file numbers that can be queried through the FILE# column of v$datafile or v$tempfile views, or FILE_ID columns of DBA_DATA_FILES or DBA_TEMP_FILES views, and relative file numbers, which are used to uniquely identify data files in the tablespace. In most cases, absolute and relative file numbers are equal, but when the number of data files in the database exceeds a threshold (such as 1023), they are no longer equal. The relative file number of a large file tablespace data file is always 1024.

Query the absolute and relative file numbers of the data file:

Select t.name tablespace_name,d.file#,d.rfile#,d.name file_name from v$tablespace tgramme vandalism 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 is determined by CREATE DATABASE. The influence of MAXDATAFILES statements and DB_FILES parameters. In addition, you should also pay attention to the mandatory restrictions imposed by the operating system on the number of files created. During the instance startup process, Oracle allocates the space used to store data file information in SGA according to the initialization parameter DB_FILES. The instance can determine the maximum number of files that can be created based on this parameter value. This parameter can be modified, but the database must be restarted to take effect, and it is valid for the entire lifecycle of the instance.

The amount of data contained in the data file in the table space will eventually affect the performance of the database. The number of online files allowed by Oracle exceeds the default limit of the operating system. The DBWn process can open all online data files and has the ability to cache all files in processing. When the number of open files reaches the default limit of the operating system, Oracle will automatically close the files. This may have a negative impact on performance, and it is recommended that you adjust the default limit of the operating system to greater than the number of online files in the database.

Create and add files for tablespaces

The method of creating and adding data files to tablespaces has been learned before, and here is only a brief review.

1. Create a data file while creating a tablespace

Create tablespae... Datafile'/ xxx/xxxx/xxxx...' Size xx

Create temporary tablespace datafile'/ xxx/xxxx/xxxx...' Size xx

2. Add data files to existing tablespaces

Alter tablespace... Add datafile'/ xxx/xxxx/xxxx...' Size xx

Alter tablespace... Add tempfile'/ xxx/xxxx/xxxx...' Size xx

Third, modify the data file size

Use the automatic extension clause to add a data file to the tablespace:

SQL > create tablespace test_tbs

2 datafile'/ u01 size

3 autoextend on

4 next 1m

5 maxsize 100m

Tablespace created.

Open the data file automatic extension:

SQL > alter database datafile'/ u01qqappActionoradataUniplicate.oracle.oradataUniplicate.stdbUniple

2 autoextend on

3 next 1m

4 maxsize 100m

Database altered.

Turn off automatic extension of data files:

SQL > alter database datafile'/ u01qqappActionoradataUniplicate.oracle.oradataUniplicate.stdbUniple

2 autoextend off

Database altered.

Manual RESIZE data file size:

SQL > alter database datafile'/ u01 resize

Database altered.

Fourth, modify the availability of data files

The availability of the data file can be modified by performing online and offline operations of the data file, and the offline data file cannot be accessed by the database until it is restored online. Data files in read-only tablespaces can also be offline or online, and the online or offline status of data files in read-only tablespaces does not affect the state of the tablespace itself, in any case, before the tablespace is read and written, these files are not writable.

1. Data files in archive mode are offline

SQL > alter database datafile'/ u01 offline

Database altered.

SQL > alter database datafile'/ u01 online

Alter database datafile'/ u01 online

*

ERROR at line 1:

ORA-01113: the checkpoint operation is not triggered when the file 10 needs media recovery / / test03.dbf file is offline, so it is prompted that media recovery is needed when the file is restored online.

ORA-01110: data file 10:'/ u01 * app *

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 online

Database altered.

2. Data files in non-archived mode are offline

Use alter database in non-archive mode. Offline for drop statement offline data file. The offline keyword marks the data file offline, regardless of whether it is corrupted or not, so the database can be opened; the for drop keyword marks that the data file is then deleted and the data file cannot be brought back online again. (in fact, you can return to the online state before the online log group is switched over.)

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 1:

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: a change of 1108304 (generated at 15:44:41 in 03Accord 19ax 2014) is required for thread 1

ORA-00289: recommended:

D:\ APP\ MANGANESE\ FLASH_RECOVERY_AREA\ ORCL\ ARCHIVELOG\ 2014 March 19\ O1_MF_1_9_%U_.ARC

ORA-00280: change 1108304 (for thread 1) in 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 / MFF / 1 / 9% Ubun.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 / MFF / 1 / 9% Ubun.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 table space

Alter tablespace... Datafile {online | offline}

Alter tablespace... Tempfile {online | offline}

5. Rename (modify storage location) data file

1. Take the tablespace containing the data file offline.

2. Use operating system commands to modify the data file name.

3. Use alter database... Rename datafile... Statement to change the data file name in the database.

4. Back up the database.

Example 1: rename data files in the same tablespace

SQL > alter tablespace test_tbs offline normal

Tablespace altered.

[oracle@stdb stdb] $mv test03.dbf test04.dbf

SQL > alter tablespace test_tbs

2 rename datafile'/ u01qqapp.oracle.oradataAction.oradataUniplicate.stdbUniple test03.dbf'.

3 to'/ u01qqappActionoradataActionoradataUniplicatest04.dbf'

Tablespace altered.

SQL > alter tablespace test_tbs online

Tablespace altered.

Example 2: rename data files in different tablespaces

SQL > select status from v$instance

STATUS

-

MOUNTED / / this operation must be performed under mount

SQL > alter database

2 rename file'/ u01qqapp.oracle.oradataAction.oradataUniplicate.stdbUniple test01.dbf'

3'/ u01qApplash oracleUniple oradataAccording to stdbscarp test02.dbf'

4'/ u01qApplash oracleUniple oradataAccording to stdbscarp test04.dbf'

5 to

6'/ u01Gap appUnitionoracleUniple oradataUniverse stdbUniple test1.dbf'

7'/ u01Gap appUnitionoracleUniple oradataUniverse stdbUniple test2.dbf'

8'/ u01Gap appUnitionoracleUniple oradataUniverse stdbUniple test3.dbf'

Database altered.

SQL > alter database open

Database altered.

VI. Delete data files

Delete the data file from the tablespace:

Alter tablespace... Drop datafile...

Alter tablespace... Drop tempfile...

Delete the data file from the database:

Alter database tempfile'/ xxx/xxxx/....' Drop including datafiles

SQL > alter database tempfile'/ u01 drop including datafiles

Database altered.

SQL > alter database datafile'/ u01ActionAccessoradata / / Note: data files cannot be deleted in this way

Alter database datafile'/ u01 drop including datafiles

*

ERROR at line 1:

ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

Note:

1. Data files cannot be deleted when migrating from dictionary management to locally managed read-only tablespaces. In addition, other data files in the read-only tablespace can be deleted.

2. The data files in the system tablespace cannot be deleted.

3. If a locally managed table space is offline, the data files in it cannot be deleted.

SQL > alter tablespace test_tbs drop datafile'/ u01ActionAccording to oradataAccord, oradataCharpy, stdbUniverse test4.dbf'

Alter tablespace test_tbs drop datafile'/ u01qqappUniqr oracle.oradataAccording to stdbUnip test4.dbf'

*

ERROR at line 1:

ORA-03264: cannot drop offline datafile of locally managed tablespace

4. If there is only one data file in the tablespace, the data file cannot be deleted.

SQL > alter tablespace test_tbs drop datafile'/ u01qqappActionoradataUniplicateoracle.oradataUniple

Alter tablespace test_tbs drop datafile'/ u01qqappUniqr oracle.oradataAccording to stdbUnip test3.dbf'

*

ERROR at line 1:

ORA-03261: the tablespace TEST_TBS has only one file

5. If the data file is not empty, the data file cannot be deleted.

6. To delete a data file, you must ensure that the data block is open.

At this point, the study on "how to master the management of Oracle data files and temporary files" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.