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

Database environment: rhel6.8 64bit oracle 11.2.0.3.11

Get up at 1: 00 in the morning and perform the planned database operation. Several large tables are redefined online as partitioned tables to facilitate space release. It is normal to execute CAN_REDEF_TABLE, but it is wrong to execute START_REDEF_TABLE Times. The error message is as follows:

ORA-42008: error instantiating redefinition

ORA-22060: parameter [] is an invalid or uninitialized value

ORA-06512: in "SYS.DBMS_SNAPSHOT_UTL", line 1613

ORA-06512: in line 1

ORA-06512: in "SYS.DBMS_REDEFINITION", line 56

ORA-06512: in "SYS.DBMS_REDEFINITION", line 1490

ORA-06512: in line 2

The first reaction at that time was that there was something wrong with the table. I had encountered a bug whose comments on the table were too long and led to a redefinition of an error. I changed the table to perform these operations again, but I still reported an error in START_REDEF_TABLE.

No way, the situation is urgent, first went to Baidu search, and then went to Google search, mos also looked for, Mao did not find a single, separate ORA-42008,ORA-22060 these two errors, but almost none of the time together.

However, there are still some gains in online search, drawing lessons from other people's methods of troubleshooting errors. Some people use 10046 to track session to find errors, I also use to try.

However, the situation has changed. Everyone else's has a clear error message, but it is not in my 10046 report. There is no way, I based on this error message ORA-06512: in "SYS.DBMS_SNAPSHOT_UTL", line 1613 at 10046 to find out where the DBMS_SNAPSHOT_UTL was executed. Finally, I found this place sys.dbms_snapshot_utl.get_log_name, I completed the parameters of the stored procedure, tested, the error is ORA-22060, so it can be determined that it is the problem of dbms_snapshot_utl. But I don't see any invalid objects in the system, so the package itself in the database is fine.

The final solution is experience, this library has encountered dbms_stats run error situation before, saying that a bug patch does not exist, http://blog.itpub.net/29822273/viewspace-2139924/. At that time, I checked mos and said that it was because the package in the database did not match the actual program, such as not running catbundle psu apply after patching. Because the library was migrated from the windows platform to the linux platform, there was no exact matching small version of the two platforms at that time, so we can only find a linux version that is closest to the windows version as the target version of the migration. Then the upgrade is finished and catbundle psu apply is not running.

The way I came up with this is to reinstall this package. There are specific steps for oracle to install some packages. I was afraid of stepping on thunder. I searched everywhere, but I still couldn't find a way to rebuild dbms_snapshot_utl. Finally, the time is more and more up to the agreed 3: 00 lock time, the heart is horizontal, directly use the grep method to find in the admin, found two scripts:

Prvtsnap.plb:CREATE OR REPLACE PACKAGE BODY dbms_snapshot_utl wrapped

Prvtsnps.plb:CREATE OR REPLACE PACKAGE dbms_snapshot_utl wrapped

One is package, the other is package body. Be careful to run the package script first, and then the body script, otherwise an error will be reported.

After the execution, the online redefinition operation has not been reported wrong.

The online redefinition of a regular table to a partitioned table, which is scheduled to be performed at 1 am, was delayed until 3 am.

Finally, I found a related article in mos, but I couldn't find it before. Search is still a skill.

Bug 14657634: DBMS_REDEFINITION.START_REDEF_TABLE ENDS WITH ORA-22060

To Bottom

Bug Attributes

TypeB-DefectFixed in Product Version

Severity2-Severe Loss of ServiceProduct Version11.2.0.3Status92-Closed, Not a BugPlatform226-Linux x86-64Created21-Sep-2012Platform VersionNO DATAUpdated17-Jan-2018Base BugN/ADatabase Version11.2.0.3Affects PlatformsGenericProduct SourceOracleKnowledge, Patches and Bugs related to this bug

Related Products

LineOracle Database ProductsFamilyOracle Database SuiteAreaOracle DatabaseProduct5-Oracle Database-Enterprise Edition

Hdr: 14657634 11.2.0.3 RDBMS 11.2.0.3 UNKNOWN PRODID-5 PORTID-226 ORA-22060Abstract: DBMS_REDEFINITION.START_REDEF_TABLE ENDS WITH ORA-22060-SAP customer message 729961-2012 TetraPak PROBLEM:- BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME = >'"SAPPB3"', ORIG_TABLE = >'"/ BIC/B0001904000"', INT_TABLE = >'/ BIC/B0001904000#$ "', OPTIONS_FLAG = > DBMS_REDEFINITION.CONS_USE_PK); END ORA-42008: error occurred while instantiating the redefinitionORA-22060: argument [] is an invalid or uninitialized numberORA-6512: at "SYS.DBMS_SNAPSHOT_UTL", line 1613ORA-6512: at line 1ORA-6512: at "SYS.DBMS_REDEFINITION", line 56ORA-6512: at "SYS.DBMS_REDEFINITION", line 1490ORA-6512: at line 1 this is constantly reproducable at customer system. Table / BIC/B0001904000 holds 545columns and was compressed by mistake TABLE_NAME COMPRESS COMPRESS_FOR---/ BIC/B0001904000 ENABLED OLTP Using online reorganisation it should be decompressed again. DIAGNOSTIC ANALYSIS:- Executing this command directly in SQLPLUS using "/ as sysdba" It reproduces the error. Created errorstack withalter session set events' 22060 trace name errorstack level 4 hours; got the underlying sql wich is failing:- Error Stack Dump-ORA-22060: argument [] is an invalid or uninitialized number- Current SQL Statement for this session (sql_id=4jkx2nsqpz6rt)-begin sys.dbms_snapshot_utl.get_log_name (: master,: mowner,: lognm,: chkpk,: status,: objid); end In stack psdexsp () throws the error PROCEDURE GET_LOG_NAME Argument Name Type In/Out Default?-MASTER VARCHAR2 IN MOWNER VARCHAR2 IN LOGNM VARCHAR2 OUT CHKFLG BINARY_INTEGER IN STATUS BINARY_INTEGER OUT MASOBJID NUMBER OUT binds given :-Bind Info (kkscoacd)-Bind#0 oacdty=01 mxl=32 (16) mxlc=00 mal=00 scl=00 pre=00 kxsbbbfp=7faea7caac22 bln=32 avl=16 flg=09 value= "/ BIC/B0001904000" Bind#1 oacdty=01 mxl=32 (06) mxlc=00 mal=00 scl=00 pre=00 kxsbbbfp=7faea7caac00 bln=32 avl=06 flg=09 value= "SAPPB3" Bind#2 oacdty=01 mxl=32 (30) mxlc=00 mal=00 scl=00 pre=00 kxsbbbfp=7faea7caac46 bln=30 avl=00 flg=09 Bind#3 oacdty=02 mxl=22 (22) mxlc=00 mal=00 scl=00 pre=00 kxsbbbfp=7faea7b86db8 bln=22 avl=02 flg=05 value=36 Bind#4 oacdty=02 mxl=22 (22) mxlc=00 mal=00 scl=00 pre=00 kxsbbbfp=7faea7b86dd0 bln=22 avl=00 flg=01 Bind#5 oacdty=02 mxl=22 (22) mxlc=00 mal=00 Scl=00 pre=00 kxsbbbfp=7faea7b86de8 bln=22 avl=00 flg=01 Interim table is created like given in ddl.sqlIts the same structure for source table. Checking data dictionory by event 10827 and found no error: alter session set events' 10827 trace name context forever, level 1 PL/SQL procedure successfully completed; SQL > set serverout onSQL > EXECUTE dbms_registry_sys.validate_components; VPROC: CATALOG DBMS_REGISTRY_SYS.VALIDATE_CATALOG-+ 000000 00:00:00.665670000VPROC: CATPROC DBMS_REGISTRY_SYS.VALIDATE_CATPROC-+ 0000 PL/SQL procedure successfully completed 00.030478000. Even only two objects are invalid, which are owned by SAP user.One is a VIEW other a SYNONYM. Recreating procedure with:SQL > @? / rdbms/admin/prvtsnap.plb does not fix the error. WORKAROUND:-use datapump for offline reorganization. RELATED BUGS:- REPRODUCIBILITY:- TEST CASE:- STACK TRACE:-skdstdst

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.