Shulou(Shulou.com)06/01 Report--

This article shows you how to understand the three parameters of Oracle Tablespace Offline, concise and easy to understand, absolutely can make your eyes shine, through the detailed introduction of this article I hope you can gain something.

Tablespace operations are the foundation of Oracle Database Management Foundations. Tablespace Offline is a knowledge point that we often come into contact with. Together with data file Offline, they form an important architecture for Oracle to maintain data consistency.

Generally, what we often come into contact with is the direct alter tablespace xxx offline operation. However, in practice, there are three parameter operations corresponding to different cases of tablespaces corresponding to data files. Like several parameters corresponding to database shutdown, different offline parameters correspond to different behaviors of Oracle, and lead to different subsequent processing methods.

Starting from the principle, the relationship between tablespace Offline, data file Offline, log archiving mode and backup restore is introduced in detail.

1, Offline simply said

An Oracle Offline tablespace is a type of "closed access" to the tablespace objects and data. Under normal Online conditions, several data files in a table space are maintained in a dynamically consistent state, and SCN on file headers maintain consistent or inconsistent states according to DBWR and CKPT.

There are three types of purposes for offline tablespaces:

·Make some data inaccessible. If a table space is used to divide multiple system data modules, the offline method can be used to make part of the data inaccessible;

ü When the front-end application is upgraded and maintained, you can use the offline method to shield some data accesses;

ü Renaming and relocation of data files (moving to other locations);

In database management, keeping enough backup and deleting data carefully is a very important principle. Never delete data without absolute certainty. The offline tablespace and lock user mentioned here are very good means of shielding data access.

A table space can have one or more data files. If a table space is offline, the corresponding data file is offline.

Not all tablespaces can be offlined. System, Undo, and Temporary tablespaces are ones that do not allow Offline operations. Offline operations are typically performed only in non-system tablespaces, that is, business data tablespaces.

Another important point is that if the table space to be offline is a user's Default table space, it is best to modify its default table space to prevent errors.

The command to Offline a table space is very simple, that is, alter table space xxx offline; depending on the situation, we can use three parameters to modify the command, namely normal, temporary and immediate. The three commands correspond to different behaviors of the Offline process. In the following steps, we experimentally examine the behavior of three command parameters.

2. Introduction to the experimental environment

The experiment was conducted on Oracle 11gR2 with the database in archive log mode. Note: Whether archive mode is critical to the datastore and file Offline behavior!

SQL> select * from v$version;

BANNER

------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

Archive schema database.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 21

Next log sequence to archive 23

Current log sequence 23

--Database in open state

SQL> select status from v$instance;

STATUS

------------

OPEN

--Oracle supports naming management OMF

SQL> show parameter db_create_file;

NAME TYPE VALUE

------------------------- ----------- ------------------------------

db_create_file_dest string /u01/app/oradata

Create the experimental table space testtbs. To make the experiment more obvious, we designed it to consist of two data files.

SQL> create tablespace testtbs datafile size 10m extent management local uniform. size 1m segment space management auto;

Tablespace created

SQL> alter tablespace testtbs add datafile size 10m autoextend off;

Tablespace altered

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TESTTBS';

TABLESPACE_NAME STATUS

------------------------------ ---------

TESTTBS ONLINE

SQL> select file_name, status, online_status from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME STATUS ONLINE_STATUS

-------------------- --------- -------------

/u01/app/oradata/ORA AVAILABLE ONLINE

11G/datafile/o1_mf_t

esttbs_94hpygrx_.dbf

/u01/app/oradata/ORA AVAILABLE ONLINE

11G/datafile/o1_mf_t

esttbs_94hq0dgm_.dbf

Notice the three Online states in which our experiment unfolds. Finally, add a data table for the space allocation process.

SQL> create table test tablespace testtbs as select * from dba_objects;

Table created

SQL> select tablespace_name from dba_segments where wner='SYS' and segment_name='TEST';

TABLESPACE_NAME

------------------------------

TESTTBS

SQL> select count(*) from test;

COUNT(*)

----------

75223

3. Data files in archive mode are offline normal

offline normal is our most common tablespace offline method and the default. If the datasheet and file status are both online, we can directly offline normal.

--Offline dataspace

SQL> alter tablespace testtbs offline normal;

Tablespace altered

--alert log content

Sun Sep 29 15:35:18 2013

alter tablespace testtbs offline normal

Completed: alter tablespace testtbs offline normal

At this point, the datasheet and datafile states are as follows:

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TESTTBS';

TABLESPACE_NAME STATUS

------------------------------ ---------

TESTTBS OFFLINE

SQL> select file_name, status, online_status from dba_data_files where tablespace_name='TESTTBS';

FILE_NAME STATUS ONLINE_STATUS

-------------------- --------- -------------

/u01/app/oradata/ORA AVAILABLE OFFLINE

11G/datafile/o1_mf_t

esttbs_94hpygrx_.dbf

/u01/app/oradata/ORA AVAILABLE OFFLINE

11G/datafile/o1_mf_t

esttbs_94hq0dgm_.dbf

A very important part of a data file is the SCN number of the file header. We know that if the database is completely shut down or check point, Oracle wants to ensure that the SCN of the control file and the file header is consistent.

SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;

FILE# STATUS RECOVER FUZZY CHECKPOINT_CHANGE#

---------- ------- ------- ----- ------------------

1 ONLINE NO YES 1054312

2 ONLINE NO YES 1054312

3 ONLINE NO YES 1054312

4 ONLINE NO YES 1054312

5 ONLINE NO YES 1054312

6 OFFLINE 0

7 OFFLINE 0

7 rows selected

SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile;

FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#

---------- ------------------ ---------------

1 1054312 787896

2 1054312 787896

3 1054312 787896

4 1054312 787896

5 1054312 819012

6 1059175 1058506

7 1059175 1058506

7 rows selected

When offline normal, the SCN of the data file header is consistent.

Data Recovery Advisor displays information in the alter log.

--There is an error in reporting content

Sun Sep 29 15:53:04 2013

Checker run found 2 new persistent data failures

RMAN> list failure all;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

122 HIGH OPEN 29-SEP-13 One or more non-system datafiles are offline

128 HIGH OPEN 29-SEP-13 Tablespace 7: 'TESTTBS' is offline

If it is normal, it can be returned online.

SQL> alter tablespace testtbs online;

Tablespace altered

Under normal database conditions, the SCN number of the data file header cannot be maintained consistently for performance reasons. After we use offline normal, all file headers SCN are the same. Therefore, the offline normal feature is that when offline, check points should be marked on all files in the table space. As long as the SCN number can be marked, the offline normal can be completed normally. In other words, offline normal is the close of consistency among files in the table space.

Datafile, in many cases, does not guarantee consistency. At this time, you need to use the Temporary and Immediate parameters.

The above is how to understand the three parameters of Oracle Tablespace Offline. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.