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

Cause of error:

If an unchecked error value is generated in the parameter file or static data dictionary in the database, an error will be reported.

Summary of experience:

Try not to directly modify the static data dictionary in the database, try not to directly modify the files related to the Oracle database, although Oracle also provides tools such as bbed to directly modify the physical files related to Oracle, but these tools are used for emergency repair.

Problem analysis:

Taking the modification of database configuration information as an example, we can modify the database configuration information directly by modifying the column values in database_properties, or indirectly through the alter database handle. The difference is that directly modifying the static data dictionary does not have any verification. If the modified value is incorrect, or the modified value may cause database downtime or some functions can not be used. None of this will prompt. Using the alter database handle will verify whether the value to be modified is correct before modifying the database_properties, evaluate whether the modification will cause database failure, and do uppercase conversion to ensure that the database configuration is safely and correctly modified.

Experiment:

Design idea: modify a configuration property of the database, taking the default temporary table space group of the database as an example. Use the alter database handle and the static data dictionary to directly modify the configuration attributes, respectively, using the correct configuration attribute value and the wrong configuration attribute value to report the ORA-00600 error.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD1 >

SYS@PROD1 >

SYS@PROD1 >

SYS@PROD1 >

SYS@PROD1 >

Create two temporary tablespaces and form a temporary tablespace group

SYS@PROD1 > create temporary tablespace temp1

2 tempfile'/ u01qqappActionoradataPlacement PROD1According to temp101.dbf'

3 size 50M

4 tablespace group temp_grp

Tablespace created.

SYS@PROD1 > create temporary tablespace temp2

2 tempfile'/ u01qqappAccord oradataUniqp201.dbf'

3 size 50M

4 tablespace group temp_grp

Tablespace created.

Query the static data dictionary to view the default temporary tablespace groups in the current database

SYS@PROD1 > select * from database_properties where property_name= 'DEFAULT_TEMP_TABLESPACE'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

1 rows selected.

Use the alter database handle to modify the default temporary tablespace group of the database

SYS@PROD1 > alter database default temporary tablespace temp_grp

Database altered.

Query the static data dictionary to prove that the modification is successful

SYS@PROD1 > select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

-

DEFAULT_TEMP_TABLESPACE TEMP_GRP Name of default temporary tablespace

Use the alter database handle to modify the database default temporary tablespace group and enter a value for a temporary tablespace group with incorrect naming conventions

SYS@PROD1 > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 'temp_grp'

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 'temp_grp'

Error reported, modification failed *

ERROR at line 1:

ORA-02216: tablespace name expected

Use the alter database handle to modify the database default temporary tablespace group and enter the group name of a temporary tablespace group that does not exist at all

SYS@PROD1 > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TETE

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TETE

*

Error reported, modification failed

ERROR at line 1:

ORA-00959: tablespace 'TETE' does not exist

Modify the static data dictionary directly to modify the database default temporary tablespace group and enter the value of a temporary tablespace group with incorrect naming conventions

SYS@PROD1 > UPDATE DATABASE_PROPERTIES SET PROPERTY_VALUE='temp_grp' where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'

There is no error reported, and the modification is successful, that is to say, directly modify the static data dictionary, there is no verification, this operation is very unsafe!

1 row updated.

Modify the database's tedious temporary tablespace group again and find that it cannot be modified and an error is reported.

SYS@PROD1 > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [ktstgdi2], []

Directly modify the static data dictionary and change the wrong value back to the correct value

SYS@PROD1 > UPDATE DATABASE_PROPERTIES SET PROPERTY_VALUE='TEMP_GRP' where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'

1 row updated.

When the database returns to normal, you can continue to maintain the configuration properties.

SYS@PROD1 > ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Database altered.

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.