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

How to understand the three parameters of Oracle tablespace Offline, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Temporary Offline operation in archive mode

Offline Normal is an ideal situation. In many cases, when Offline is a Tablespace, there are other factors to consider. For example, during offline operations, offline may be affected if there are ongoing DDL and DML operations on tablespace objects.

In addition, if there is a data file in a table space that has been passed by Offline, we are normally not able to do offline normal. At this point, we need to use temporary.

We first process the tablespace online, and then offline one of the data files. Note: this operation can only be done in Archivelog mode.

SQL > alter tablespace testtbs online

Tablespace altered

SQL > alter database datafile'/ u01qapplash oradata offline ORA11G Uniqpl datafileplico1cm

Database altered

After that, observe the tablespace and file status. We found that the data file status of the offline is Recover.

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 RECOVER

11G/datafile/o1_mf_t

Esttbs_94hpygrx_.dbf

/ u01/app/oradata/ORA AVAILABLE ONLINE

11G/datafile/o1_mf_t

Esttbs_94hq0dgm_.dbf

Note that Oracle maintains data file consistency, which is a dynamic consistency process. If a file or object temporarily exits the consistency mechanism, it means that the file or object is no longer consistent. Regardless of the length of time, Recover is required if the object wants to return to the original consistency system. The Recover means of Oracle is a continuous column of redo log files.

Here, the recover status of the file we see indicates that Recover needs to be done if the file is to return to the Online state.

Back to our Offline theme. At this point, the states of several files under the Online status table space are inconsistent.

SQL > alter tablespace testtbs offline normal

Alter tablespace testtbs offline normal

ORA-01191: file 6 is offline-cannot be offline normally

ORA-01110: data file 6:'/ u01 ActionPlacement oradataUniplicORA11G Universe datafileCharpy O1m frenquency testtbsdata 94hpygrxroom.dbf'

SQL > create table mm tablespace testtbs as select * from dba_objects where 1: 0

Table created

Normal normal offline is no longer successful. We can use the temporary parameter at this time.

SQL > alter tablespace testtbs offline temporary

Tablespace altered

-- Log information in Alert Log

Sun Sep 29 16:02:34 2013

Alter tablespace testtbs offline temporary

Completed: alter tablespace testtbs offline temporary

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 RECOVER

11G/datafile/o1_mf_t

Esttbs_94hpygrx_.dbf

/ u01/app/oradata/ORA AVAILABLE OFFLINE

11G/datafile/o1_mf_t

Esttbs_94hq0dgm_.dbf

We implemented Offline using the temporary parameter. But for the data file that offline ahead of time, the status is still recover. Presumably, the Temporary Offline process is an inconsistent shutdown.

Attempts to v$datafile and v$datafile_header reflect the record of the file SCN number in the control file and the data file header, respectively. In offline normal, we saw the consistency of the various files. When doing offline normal, Oracle inserts a check point (internal) to synchronize the header SCN of each file.

What is the view state when using Temporary?

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 YES YES 1058660

7 OFFLINE NO NO 1058696

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 1058660 1058506

7 1058696 1058506

7 rows selected

The SCN numbers of the two files above the control file and the file header are different, indicating that the SCN number of the file within a tablespace is inconsistent.

Note: in Temporary Offline, the status of some "problematic" data files and "no problem" data files can be different. "No problem" data files have the same SCN number.

If an Online tablespace is required at this time, an error will be reported.

SQL > alter tablespace testtbs online

Alter tablespace testtbs online

ORA-01113: file 6 requires media recovery

ORA-01110: data file 6:'/ u01 ActionPlacement oradataUniplicORA11G Universe datafileCharpy O1m frenquency testtbsdata 94hpygrxroom.dbf'

In online, Oracle must go to online a "consistent" tablespace. At this point, it finds inconsistencies in the SCN within the tablespace. According to the prompt, we need to recover the files manually.

-- perform media recovery process

SQL > recover datafile 6

Media recovery complete.

SQL > alter tablespace testtbs online

Tablespace altered

Online succeeded. In alert log, we see the redo log apply and even archived redo log apply processes used in media recovery. One thing to note: we have only restored datafile 6 at this time.

Sun Sep 29 16:07:22 2013

ALTER DATABASE RECOVER datafile 6

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0

Mem# 0: / u01/app/oradata/ORA11G/onlinelog/o1_mf_3_92t73782_.log

Mem# 1: / u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_3_92t737fj_.log

Media Recovery Complete (ora11g)

Completed: ALTER DATABASE RECOVER datafile 6

Sun Sep 29 16:07:36 2013

Alter tablespace testtbs online

Completed: alter tablespace testtbs online

The characteristic of Temporary Offline is that when Offline is carried out, the files in the tablespace will still be synchronized with Check Point typing action. Unlike Normal, it can continue if a file or files cannot enter check point to synchronize SCN,Offline actions for various reasons. Temporary ensures that only part of the files are consistent.

When Online occurs in the Temporary Offline tablespace, Oracle checks the SCN consistency of the files in the tablespace. Note: at this time, Oracle only recognizes the tablespace by adding a SCN number to the existing database, and will reject the online operation if there is internal inconsistency.

If online is rejected, all we need to do is (note that we only need) to recover the original problem files. Use Redo Log in recover to push forward and push the problem file to be consistent with other files in the tablespace.

Finally, in online, there is no problem.

Note: even with Temporary Offline, the check point action still exists, but becomes non-mandatory. If there is no "problem child" in the tablespace file, even if the Temporary Offline command is used, the effect is no different from Normal. Moreover, in online, there is no need for the recover process.

Let's discuss the Immediate parameter method, which is more straightforward than Temporary.

Immediate parameters in archive mode

We first restore the scene to Online, and still offline the data file 6.

SQL > alter tablespace testtbs online

Tablespace altered

SQL > alter database datafile'/ u01qapplash oradata offline ORA11G Uniqpl datafileplico1cm

Database altered

Failed to attempt a normal shutdown

SQL > alter tablespace testtbs offline normal

Alter tablespace testtbs offline normal

ORA-01191: file 6 is offline-cannot be offline normally

ORA-01110: data file 6:'/ u01ActionPlacement oradataUniplicORA11G _ swap _ datafileCharactO1parts mfcharts testtbsdatabases 94hpygrxcodes. Dbf'

We use the offline immediate operation here.

-- offline now

SQL > alter tablespace testtbs offline immediate

Tablespace altered

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

FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#

1 1059725 787896

2 1059725 787896

3 1059725 787896

4 1059725 787896

5 1059725 819012

6 1059634 1059175

7 1059725 1059175

7 rows selected

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

FILE# STATUS RECOVER FUZZY CHECKPOINT_CHANGE#

--

1 ONLINE NO YES 1059725

2 ONLINE NO YES 1059725

3 ONLINE NO YES 1059725

4 ONLINE NO YES 1059725

5 ONLINE NO YES 1059725

6 OFFLINE YES YES 1059634

7 OFFLINE YES YES 1059725

7 rows selected

Similar to Temporary, the tablespace can still operate with Offline in the presence of a problem data file. But the difference is that Oracle will not perform check point unified SCN action on any data file in the case of the immediate parameter.

This approach is similar to shutdown abort. Regardless of whether there is a problem with the file or not, Oracle does not check or unify the action.

Another detail that requires global attention is the recover column in v$datafile_header. In the normal parameter, this column is not displayed, which means that the problem does not require attention and attention. In Tempory mode, only those "problem" files are marked as YES, that is, Recover is required. Other files with no problems are in the status of NO, that is, there is no need for recover. The above experiment also proves this point.

In the case of the immediate parameter, the status of all files is YES, which means that recover is required for good or bad.

Let's try the online action.

SQL > alter tablespace testtbs online

Alter tablespace testtbs online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6:

'/ u01 apprenticeship oradataPlacement ORA11G scarp datafileUniplemeO1mfharmtesttbscharacters 94hpygrxwalking .dbf'

The Online action failed and needs to be restored.

SQL > recover datafile 6

Media recovery complete.

-- it still doesn't work. You need to recover all the files.

SQL > alter tablespace testtbs online

Alter tablespace testtbs online

*

ERROR at line 1:

ORA-01113: file 7 needs media recovery

ORA-01110: data file 7:

'/ u01appranumoradataPlacement ORA11G _ q0dgmparts. Dbf'

Just restore the tablespace directly.

SQL > recover tablespace testtbs

Media recovery complete.

-- Online action

SQL > alter tablespace testtbs online

Tablespace altered.

Normal, Temporary, and Immediate are three parameter methods that are used in turn, and the strict level decreases layer by layer.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.