Tablespace online solution

Tablespace online solution

During the demonstration today, two sets of database software were installed on the virtual machine, Oracle 10g and 11g. We still encountered some unexpected problems during the demonstration of migration of common data files. from the current situation, it seems quite strange, so it's okay to switch to another environment and try again. After analyzing the problem, I found some simple and common errors, but I still did not know some details. The migration process for common data files was very simple, the database can be migrated in the open state,

The basic steps are as follows:
Alter tablespace xxxx offline;
Cp datafiles
Alter tablespace xxx rename sourcexxxxx to targetxxxxx;
Alter tablespace xxxxx online;
In the demonstration of the 11G environment, I directly used the offline immediate option to save time and speed up offline.

SQL> alter tablespace data offline immediate;

Tablespace altered.
Then, I typed the following command to try to copy the data file, but the error thrown seems to be full of depth, which is actually caused by the last single quotes.
SQL>! Cp/u02/ora11g/oradata/TEST/disk4/data01.dbf/u02/ora11g/oradata/TEST/disk3/data01.dbf'
/Bin/bash:-c: line 0: unexpected EOF while looking for matching '''
/Bin/bash:-c: line 1: syntax error: unexpected end of file

[Ora11g @ oel1 ~] $ Cp/u02/ora11g/oradata/TEST/disk4/data01.dbf/u02/ora11g/oradata/TEST/disk3/data01.dbf
[Ora11g @ oel1 ~] $ Orasql
Then copy the data file directly.

SQL> alter tablespace data rename datafile '/u02/ora11g/oradata/TEST/disk4/data01.dbf' to '/u02/ora11g/oradata/TEST/disk3/data01.dbf ';

Tablespace altered.
Then begin the online operation. This is also a regular operation, but the following error is thrown.

SQL> alter tablespace data online;
Alter tablespace data online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u02/ora11g/oradata/TEST/disk3/data01.dbf'

In this case, the log shows that the data file needs to be restored by media.
As a result, if you want to fix the problem immediately, you can use the recover database directly. The result is more mysterious from the error.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1-file is in use or recovery
ORA-01110: data file 1: '/u02/ora11g/oradata/TEST/disk5/system01.dbf'
It seems that the restoration is still a seemingly unfinished task at this time, and the Environment is abandoned and switched to the 10 Gb environment. The file migration has been done honestly, so there will be no problem this time.
The procedure is as follows:

SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP

SQL> create tablespace data datafile '/u02/ora11g/oradata/TEST/disk3/data01.dbf' size 10 M;

Tablespace created.

SQL> alter tablespace data offline;

Tablespace altered.

SQL>! Cp/u02/ora11g/oradata/TEST/disk3/data01.dbf/u02/ora11g/oradata/TEST/disk4/data02.dbf

SQL> alter tablespace data rename datafile '/u02/ora11g/oradata/TEST/disk3/data01.dbf' to '/u02/ora11g/oradata/TEST/disk4/data02.dbf ';

Tablespace altered.

SQL> alter tablespace data online;

Tablespace altered.
From the whole process, the only difference is that the problematic environment I used the offline immediate option
From the official viewing of the offline options, we can see that there are still a lot of differences, the default is to use the normal option.

Offline normal Specify NORMAL to flush all blocks in all data files in the tablespace out of the system global area (SGA ). you need not perform media recovery on this tablespace before bringing it back online. this is the default.

Offline temporary If you specify TEMPORARY, then Oracle Database performs a checkpoint for all online data files in the tablespace but does not ensure that all files can be written. files that are offline when you issue this statement may require media recovery before you bring the tablespace back online.

Offline immediate If you specify IMMEDIATE, then Oracle Database does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.
Therefore, when you use offline immediate, there is no checkpoint at this time. If you want to use the online option, you still need to perform the recover operation.
I have understood so much about it. How can I fix it,

SQL> alter tablespace data online;
Alter tablespace data online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u02/ora11g/oradata/TEST/disk4/data02.dbf'

SQL> recover datafile 4;
Media recovery complete.
SQL> alter tablespace data online;

Tablespace altered.
In other words, it is better to perform checkpoint in some key operations, that is, to use the default offline normal option.
This is a very simple and basic problem, but it may not be realized in a specific period of time. It seems that it still needs to be consolidated and practiced more :)