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

6. Offline processing for non-archived situations

The above series of discussions are all experiments conducted in archived file mode. If we are not in the case of archiving, the problems we face are different.

First, tablespaces can be normal offline operated in non-archived mode.

SQL > alter database noarchivelog

Database altered.

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 22

Current log sequence 24

Create a tablespace to view the status of the file.

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 20m autoextend on

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_94hsw8oo_.dbf

/ u01/app/oradata/ORA AVAILABLE ONLINE

11G/datafile/o1_mf_t

Esttbs_94hswx27_.dbf

Normal Offline Tablespace.

SQL > alter tablespace testtbs offline normal

Tablespace altered

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_94hsw8oo_.dbf

/ u01/app/oradata/ORA AVAILABLE OFFLINE

11G/datafile/o1_mf_t

Esttbs_94hswx27_.dbf

SQL > alter tablespace testtbs online

Tablespace altered

In non-archived mode, it is not allowed to offline data files separately.

SQL > alter database datafile'/ u01 offline App'/ u01 alter database datafile'/ u01 apprenticeship oradata offline

Alter database datafile'/ u01 _ offline _ ORA11G _

ORA-01145: immediate offline is not allowed unless media recovery is enabled

Just imagine, this process is understandable. Oracle believes that: if you offline the file, it is inconsistent with the tablespace. Then, once the file online, you must need to do recover to "catch" the other files in the tablespace. This process requires continuous redo log to perform apply actions.

In non-archived mode, continuous operation redo log file is not easy to get. From the Oracle theory, that is to say, it is impossible to get. Therefore, at this time, Oracle simply forbids this kind of operation.

So, is it possible that offline of individual files is not allowed in non-archived mode? No, as long as you "promise" that you won't come back.

Through the background, we deleted the data file.

-delete the data file

[oracle@SimpleLinux ~] $rm / u01/app/oradata/ORA11G/datafile/o1_mf_testtbs_94hswx27_.dbf

[oracle@SimpleLinux ~] $

SQL > alter system checkpoint

Alter system checkpoint

At this point, the database finds a failure and the instance terminates. Note: different versions of Oracle behave differently on this issue. CKPT terminates the instance.

Sun Sep 29 16:59:08 2013

Beginning global checkpoint up to RBA [0x18.ad3e.10], SCN: 1062240

Errors in file / u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ckpt_27311.trc:

ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode

ORA-01116: error in opening database file 7

ORA-01110: datafile 7:'/ u01 *

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file / u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ckpt_27311.trc:

ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode

ORA-01116: error in opening database file 7

ORA-01110: datafile 7:'/ u01 *

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Sun Sep 29 16:59:08 2013

System state dump requested by (instance=1, sid=27311 (CKPT)), summary= [abnormal instance termination].

System State dumped to trace file / u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_27299.trc

CKPT (ospid: 27311): terminating the instance due to error 1242

Dumping diagnostic data in directory= [CDMP _ 20130929165908], requested by (instance=1, sid=27311 (CKPT)), summary= [abnormal instance termination].

Instance terminated by CKPT, pid = 27311

-- Database termination

[oracle@SimpleLinux ~] $ps-ef | grep pmon

Oracle 27487 27404 0 17:00 pts/2 00:00:00 grep pmon

[oracle@SimpleLinux ~] $

We start the database to the mount state, and then we can offline drop the file.

-- Boot to mount state.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup mount

ORACLE instance started.

Total System Global Area 376635392 bytes

Fixed Size 1345072 bytes

Variable Size 306186704 bytes

Database Buffers 62914560 bytes

Redo Buffers 6189056 bytes

Database mounted.

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

FILE# STATUS RECOVER FUZZY CHECKPOINT_CHANGE#

--

1 ONLINE NO YES 1062240

2 ONLINE NO YES 1062240

3 ONLINE NO YES 1062240

4 ONLINE NO YES 1062240

5 ONLINE NO YES 1062240

6 ONLINE NO YES 1062240

7 ONLINE 0

7 rows selected

Direct offline is not allowed

SQL > alter database datafile 7 offline

Alter database datafile 7 offline

ORA-01145: immediate offline is not allowed unless media recovery is enabled

-- the status of the tablespace is not clear when it is not open

SQL > alter tablespace testtbs offline

Alter tablespace testtbs offline

ORA-01109: the database is not open

-- Offline Drop

SQL > alter database datafile 7 offline drop

Database altered

SQL > alter database open

Database altered

SQL > drop tablespace testtbs

Tablespace dropped

As the name implies, Offline Drop is a permanent deletion of this object, so it is not necessary. Not to mention returning to online.

7. Conclusion

Finally, let's summarize the three parameters of Offline.

Offline normal: is the most commonly used scenario, but also the least prone to problems. When Offline Normal, Oracle will perform Check Point actions inside the tablespace to ensure that the SCN above each file header within the tablespace is consistent, that is, the data is consistent. If there is a situation in which the data file cannot be pushed forward to SCN, such as already Offline, offline normal failure reports an error.

Offline temporary: a shutdown mode that is slightly looser than Normal requirements. In Temporary mode, Oracle still "tries" to unify the SCN number of the file header inside the tablespace. If the data file can be unified, the Check Point action is performed, and if the file is not unified, the operation will not report an error, but its status will be marked as inconsistent. When the tablespace Online of Offline in Temporary mode, those inconsistent files with "problems" need to be media recovey. There is no problem, enter the check point data file, there is no need for recovery action.

Offline immediate: the loosest offline mode. In Immediate mode, Oracle does not perform check point actions, and regardless of whether there is a problem with Datafile, it will be set to require a Recover process. When re-online, the table space needs to be re-fully empty media recover.

We tend to be strict in our daily choices. Because non-Normal offline requires the help of external redo log to carry out media recover actions. The order of selection is normal,temporary and immediate.

The Offline action of Oracle tablespaces is a very common means of daily maintenance.

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.