Read-Only tablespace processing when using trace file reconstruction Control File

When using the trace file to recreate the control file will often ignore the read-only tablespace, this method after the reconstruction of the read-only tablespace is actually set to read and write will appear similar to the ORA-0

When using the trace file to recreate the control file will often ignore the read-only tablespace, this method after the reconstruction of the read-only tablespace is actually set to read and write will appear similar to the ORA-0

When the trace file is used to reconstruct the control file, the read-only tablespace is often ignored. In this way, the re-built read-only tablespace will appear when it is set to read/write.
ORA-01135: file 6 accessed for DML/query is offline
ORA-01111: name for data file 6 is unknown-rename to correct file
ORA-01110: data file 6: '/u01/app/Oracle/product/10.2.1/db_1/dbs/MISSING00006'
Errors, which are roughly classified as follows:
When the read-only tablespace exists, the reconstruction control file can be divided into two situations:
1. The script for recreating controlfile contains the datafile corresponding to read only, and datafile does not require rename.
2. The script for recreating controlfile does not contain the datafile corresponding to read only. After recreating the controlfile, rename datafile must be in the correct position.

Test environment: Oracle 10G database: PROD

Demo:
1. view the tablespace status
Note that the oltp tablespace is read only.
SYS @ PROD> select tablespace_name, STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
UNDOTBS ONLINE
SYSAUX ONLINE
TEMP01 ONLINE
TEMP02 ONLINE
USERS ONLINE
TOOLS ONLINE
OLTP READ ONLY
MSSM ONLINE
9 rows selected.

2. Back up the control file to the trace
SYS @ PROD> alter database backup controlfile to trace;
Database altered.

3. Start the reconstruction control file process: (stop the database => nomount status => execute the trace reconstruction script)
SYS @ PROD> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS @ PROD> startup nomount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 100664720 bytes
Database Buffers 209715200 bytes
Redo Buffers 2973696 bytes

4. In this example, the unique control file is deleted.
SYS @ PROD> show parameter control_files
NAME TYPE VALUE
-----------------------------------------------------------------------------
Control_files string/u01/app/oracle/oradata/PROD/d
Isk1/control1.ctl

PROD @ rhel1 $ rm disk1/control1.ctl

5. Execute the trace script.
1) if you open the trace file generated by the backup, we can see the reconstruction statement of the control file later (the trace file can find a lot of important information about the control file)
The generated trace file is located in the $ ORACLE_BASE/admin/$ ORACLE_SID/udump/directory. If you do not know which file is generated, you can use the following statement to find it:

Select
D. value | '/' | lower (rtrim (I. instance, chr (0) | '_ ora _' | p. spid | '. trc 'trace_file_name
From
(Select p. spid
From sys. v $ mystat m, sys. v $ session s, sys. v $ process p
Where m. statistic # = 1 and s. sid = m. sid and p. addr = s. paddr) p,
(Select t. instance from sys. v $ thread t, sys. v $ parameter v
Where v. name = 'thread' and (v. value = 0 or t. thread # = to_number (v. value) I,
(Select value from sys. v $ parameter where name = 'user _ dump_dest ') d;
Return Value:
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/U01/app/oracle/admin/PROD/udump/prod_ora_13341.trc
Or use the following method to obtain the pid:
Select a. spid from
V $ process a, v $ session B
Where a. addr = B. paddr and B. username = 'sys ';
Return Value:
SPID
------------
13341
2) Find the section in the trace file:
========================================================== ==============================
Create controlfile reuse database "PROD" NORESETLOGS ARCHIVELOG
MAXLOGFILES 30
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
# MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/U01/app/oracle/oradata/PROD/disk1/redo01.log ',
'/U01/app/oracle/oradata/PROD/disk2/redo01_ B .log'
) SIZE 100 M,
GROUP 2 (
'/U01/app/oracle/oradata/PROD/disk1/redo02.log ',
'/U01/app/oracle/oradata/PROD/disk2/redo02_ B .log'
) SIZE 100 M,
GROUP 3 (
'/U01/app/oracle/oradata/PROD/disk1/redo03.log ',
'/U01/app/oracle/oradata/PROD/disk2/redo03_ B .log'
) SIZE 100 M
-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/PROD/disk1/system01.dbf ',
'/U01/app/oracle/oradata/PROD/disk1/undotbs01.dbf ',
'/U01/app/oracle/oradata/PROD/disk1/sysaux01.dbf ',
'/U01/app/oracle/oradata/PROD/disk1/users_01.dbf ',
'/U01/app/oracle/oradata/PROD/disk1/tools_01.dbf ',
'/U01/app/oracle/oradata/PROD/disk1/mssm_01.dbf'
Character set US7ASCII;
========================================================== ==============================
= Note that there is no read-only tablespace information in the trace file at this time. If the correct oltp location name is added to the datafile of this script, there will be no rename process.

3) save it as a script for recovery:
PROD @ rhel1/home/oracle $ vi con. SQL