Oracle metadata object Invalid Repair Process

During Oracle operation, Operation errors may cause damage to internal metadata, which affects some basic functions and normal processing of the system. Once such a fault occurs, rebuilding the internal data dictionary is what we need to do.

This article describes how to reconstruct Oracle metadata objects from a fault point.

1. Fault scenarios

A friend's test database has encountered many "weird" errors recently, such as abnormal interruption of exp/imp operations, failure to display metadata in pl/SQL, and error reporting. In this case, I ask a friend for the corresponding alert_log file. Many error messages are found.

Sat Jun 23 06:36:37 2012

Errors in file/u01/diag/rdbms/wilson/trace/wilson_m0020.7.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04063: package body "SYS. PRVT_HDM" has errors

ORA-06508: PL/SQL: cocould not find program unit being called: "SYS. PRVT_HDM"

ORA-06512: at line 1

(The reason for the length is omitted .....)

Sat Jun 23 06:53:39 2012

Exception [type: SIGSEGV, Address not mapped to object] [ADDR: 0x34] [PC: 0x92C0E13, kzpchkc () + 4425] [flags: 0x0, count: 1]

Errors in file/u01/diag/rdbms/wilson/trace/wilson_ora_3619.trc (incident = 12215 ):

ORA-07445: å ‡ ç å ‚ å é è () + 4425] [SIGSEGV] [ADDR: 0x34] [PC: 0x92C0E13] [Address not mapped to object] []

Incident details in:/u01/diag/rdbms/wilson/incident/incdir_12215/wilson_ora_3619_i12215.trc

Sat Jun 23 06:53:40 2012

Trace dumping is refreshing Ming id = [cdmp_20120623065340]

Sat Jun 23 06:53:45 2012

Sweep [inc] [12215]: completed

Sweep [inc2] [12215]: completed

Sat Jun 23 06:55:23 2012

ORA-942 encountered when generating server alert SMG-4120

ORA-942 encountered when generating server alert SMG-4121

Such types of error messages are distributed in the error logs in recent days. However, when executing some failed commands (such as desc), an exception error message is displayed in the log.

In the preceding error log, the MMON process restarts repeatedly after an exception or interruption.

The MMON process is an AWR report library collection process, which periodically collects the system status and stores it in the database dictionary. The MMON restarts repeatedly after an exception or interruption, which seems to affect AWR collection.

Contact your friends for further information.

The AWR snapshot of the day is only two. It seems that there is a real problem. At the same time, the sys. standard package (invalid) in the automatic job is found to be invalid, and many packages and methods are merged.

The reset script catexp. SQL of exp/imp was executed. It seems that the script is being executed, destroying the internal dictionary information and data, resulting in a series of problems.

2. Problem Solving preparation

After discovering the root cause of the problem, we can have the basic idea of solving the problem: re-create the data dictionary view and program object. We have stored database creation scripts and programs in the Oracle program installation directory, so we can call the corresponding scripts to update the data dictionary and program objects.

Because the processing information is sensitive, a full backup is required before processing. I chose the method of Cold Standby for downtime.

First, locate the control file and data file location information and prepare to copy the file.

[Root @ www.bkjia.com/] # chown-R oracle: oinstall backup

[Root @ www.bkjia.com/] # ls-l | grep backup

Drwxr-xr-x 2 oracle oinstall 4096 Jun 25 backup

 

-- Control File

SQL> select 'cp' | name | '/backup' from v $ controlfile;

'Cp' | NAME | '/BACKUP'

--------------------------------------------------------------------------------

Cp/u01/app/oradata/ORA11G/controlfile/o1_mf_7vpyvypo _. ctl/backup

Cp/u01/app/flash_recovery_area/ORA11G/controlfile/o1_mf_7vpyw16z _. ctl/backup

 

-- Data File

SQL> select 'cp' | file_name | '/backup' from dba_data_files;

'Cp' | FILE_NAME | '/BACKUP'

--------------------------------------------------------------------------------

Cp/u01/app/oradata/ORA11G/datafile/o1_mf_users_7vpyc2xd _. dbf/backup

Cp/u01/app/oradata/ORA11G/datafile/o1_mf_undotbs1_7vpyc2py _. dbf/backup

Cp/u01/app/oradata/ORA11G/datafile/o1_mf_sysaux_7vpyc2hb _. dbf/backup

Cp/u01/app/oradata/ORA11G/datafile/o1_mf_system_7vpyc1x7 _. dbf/backup

Then, shut down and copy the file to the backup directory.

[Oracle @ bsplinux ~] $ Sqlplus/nolog

SQL * Plus: Release 11.2.0.1.0 Production ction on Mon Jun 25 20:42:06 2012

Copyright (c) 1982,200 9, Oracle. All rights reserved.

 

SQL> conn/as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Use the generated script statement to copy data.

[Oracle @ bsplinux backup] $ ls-l

Total 1720628

-Rw-r ----- 1 oracle oinstall 9748480 Jun 25 o1_mf_7vpyvypo _. ctl

-Rw-r ----- 1 oracle oinstall 9748480 Jun 25 o1_mf_7vpyw16z _. ctl

-Rw-r ----- 1 oracle oinstall 723525632 Jun 25 o1_mf_sysaux_7vpyc2hb _. dbf

-Rw-r ----- 1 oracle oinstall 744497152 Jun 25 o1_mf_system_7vpyc1x7 _. dbf

-Rw-r ----- 1 oracle oinstall 267395072 Jun 25 o1_mf_undotbs1_7vpyc2py _. dbf

-Rw-r ----- 1 oracle oinstall 5251072 Jun 25 o1_mf_users_7vpyc2xd _. dbf

The cold backup is complete.

Note: before performing any restoration operations, if possible, make sure that the data is retained on site for backup! In this way, we can at least avoid deteriorating the situation.

1 2 Next Page