The TEMP tablespace is not created, causing the XMLTYPE content to be read incorrectly. tempxmltype

The TEMP tablespace is not created, causing the XMLTYPE content to be read incorrectly. tempxmltype

Today, the user environment describes the xmltype in all data tables. The query statement shows the following results:

For example, if this field is set to SYS. XMLTYPE In the table named ZLMXXXX, you can see that all values of this field are<Value Error>The error message is not reported because the field is 'null' and there is no content. Ask the user if any operation is performed before the problem occurs, the answer is that the database was migrated due to hardware changes. Because the database itself is small and the migrated environment platform is identical, therefore, the most primitive and simple migration method is 'cold backup 'recovery. However, after the migration, it is found that all fields in the XMLTYPE content are incorrect, while other fields are correct.

Troubleshooting 1. Check whether sys. xmltype is missing or valid

Because all XMLTYPE fields cannot be accessed, sys. whether the xmltypeTYPE package is damaged or invalid during migration. If the TYPE is faulty, all the TYPE objects will be inaccessible. Check the object status:

Here we can see that there is no problem. If there is a problem, we can execute the catproc. SQL script to fix it. The specific operation is as follows:

Run catproc.sql andutlrp.sql as SYS.sqlplus "/ assysdba"startup upgradespool validate.log@?/rdbms/admin/catproc.sql@?/rdbms/admin/utlrpspool offshutdown immediatestartup

Obviously, there is no problem here and we don't need to fix it. What should we do next?

Ii. Check the migration environment

Because the user uses the cold Backup Recovery Method for migration, the principle is the most secure migration method. If the environment is consistent, it will not cause problems, it is estimated that there were environmental differences during the migration and recovery process. I carefully checked the operating system, database parameters, and so on. There were no major discoveries. When detecting data files, it was found that the path of the data file was changed due to the installation path restriction, and it was okay to check v $ datafile. when checking the v $ tempfile view, it was found that there was no content in the view, as follows:

Ask the user about the migration process again, and tell the user that the source and target data file paths have changed. The user then generates a control file script on the source. After modifying the path, then, the control file is re-built on the target end for migration. This method is feasible in the recovery of inconsistent processing paths, and there is no error, but it is found during the creation process, I forgot to add a temporary tablespace file, because in the trace script of the control file, in addition to creating the control file itself, the following sentence is added:

-- Other tempfiles may require adjustment.

Alter tablespace temp add tempfile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ YDTEST \ TEMP01.DBF'

SIZE176160768 reuse autoextend off;

Alter tablespace zltoolstmp add tempfile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ YDTEST \ ZLTOOLSTMP'

SIZE467664896 reuse autoextend off;

Alter tablespace smitmp add tempfile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ YDTEST \ SMITMP01.DBF'

SIZE52428800 reuse autoextend off;

-- End of tempfile additions.

After changing the path, execute these statements and query the v $ tempfile view again. The result is as follows:

Finally, go back to the previous table and check that the XMLTYPE field is normal. The problem is completely solved as follows:

Key knowledge points

Sys. xmltype: this TYPE can be re-built by the script. If it fails, all XMLTYPE content will become invalid. In addition, temporary tables also affect the use of XMLTYPE objects.

Statement: original, please indicate the source of the post