When performing databasereplay preprocessing, you may encounter a problem. For the first time, it is an internal (function Stored Procedure) Error in executing the package call. It is a typical parameter passing error. SQLexecdbms_workload_replay.PROCESS_CAPTURE (AAA); BEGINdbms_workload_replay.PROCESS_CAPTURE (AAA); END; * ER
A problem occurs during database replay preprocessing. In the beginning, it is an internal (function/Stored Procedure) error in the execution package call. It is a typical parameter passing error. SQL exec dbms_workload_replay.PROCESS_CAPTURE ('aaa'); BEGIN dbms_workload_replay.PROCESS_CAPTURE ('aaa'); END; * ER
A problem occurs during database replay preprocessing. In the beginning, it is an internal (function/Stored Procedure) error in the execution package call. It is a typical parameter passing error.
SQL> exec dbms_workload_replay.PROCESS_CAPTURE('AAA');BEGIN dbms_workload_replay.PROCESS_CAPTURE('AAA'); END; *ERROR at line 1:ORA-06550: line 1, column 15:PLS-00306: wrong number or types of arguments in call to 'ADD_CAPTURE'ORA-06550: line 1, column 9:PL/SQL: Statement ignoredORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 2301ORA-06512: at line 1
This problem is generally caused by calling the function by 10046 trace. From the trace, we can see that dbms_wrr_internal.add_capture is running.
BEGIN :1 := dbms_wrr_internal.add_capture( :name, :db_id, :db_name, :db_version, 'tmp', '/tmp', 'TRUE', :status, NULL, NULL, :stime, NULL, :sscn, :dflt_action, NULL, NULL, :wid, :internal_stime ); END; END OF STMT PARSE #11529215044979176000:c=0,e=540,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=434859905841 ===================== PARSING IN CURSOR #11529215044979172808 len=8 dep=1 uid=0 oct=45 lid=0 tim=434859906846 hv=2761672982 ad='0' sqlid='8sst43uk9rk8q' ROLLBACK END OF STMT PARSE #11529215044979172808:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859906844 XCTEND rlbk=1, rd_only=1, tim=434859906921 EXEC #11529215044979172808:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859906939 CLOSE #11529215044979172808:c=0,e=2,dep=1,type=3,tim=434859906960 PARSE #11529215044979172808:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859907039 XCTEND rlbk=1, rd_only=1, tim=434859907062 EXEC #11529215044979172808:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=434859907074 CLOSE #11529215044979172808:c=0,e=1,dep=1,type=3,tim=434859907091 EXEC #11529215044981457272:c=10000,e=77698,p=0,cr=60,cu=29,mis=0,r=0,dep=0,og=1,plh=0,tim=434859907137 ERROR #11529215044981457272:err=6550 tim=434859907154SQL> desc dbms_wrr_internal FUNCTION ADD_CAPTURE RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN DB_ID NUMBER IN DB_NAME VARCHAR2 IN DB_VERSION VARCHAR2 IN DIR VARCHAR2 IN DPATH VARCHAR2 IN DPATH_SHARED VARCHAR2 IN STATUS VARCHAR2 IN ECODE NUMBER IN EMSG VARCHAR2 IN STIME DATE IN ETIME DATE IN SSCN NUMBER IN DEFAULT_ACTION VARCHAR2 IN AWR_DB_ID NUMBER IN AWR_BSNAP NUMBER IN WID VARCHAR2 INSQL> select NAME,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture where sql_id='3h55aw49j63g8'; no rows selected
I thought that 10046 could capture the bound variable values, but it does not exist here. v $ SQL _bind_capture could not capture the value of the bound variable. I suspect it has something to do with the internal package. Therefore, we cannot determine which parameter has a problem with the call type. There is no good way to deal with such a problem. I used to perform database replay in my own Linux environment, and it was successful, so the idea is to migrate this package from the Linux environment to the Hp environment. So we use grep to search for the specific file. I found the file named "prvtwrr. plb", which contains a bunch of encrypted items. Upload the file to HP for running. After running the file, the girl still reports the previous error. Depressing: Why is my Linux environment okay? I cannot do it in HP environment. When there was no way to go, my colleague executed the following command under the $ ORACLE_HOME directory.
w4sd23pa#[/home/oracle]find $ORACLE_HOME -name "prvtwrr.plb" /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtwrr.plb /oracle/app/oracle/product/11.2.0/db_1/.patch_storage/17411249_Apr_21_2014_22_29_21/files/rdbms/admin/prvtwrr.plb w4sd23pa#[/home/oracle] w4sd23pa#[/home/oracle]ls -l /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtwrr.plb -rw-r--r-- 1 oracle oinstall 151611 Apr 22 13:29 /oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/prvtwrr.plb w4sd23pa#[/home/oracle]ls -l /oracle/app/oracle/product/11.2.0/db_1/.patch_storage/17411249_Apr_21_2014_22_29_21/files/rdbms/admin/prvtwrr.plb -rw-r--r-- 1 oracle oinstall 149850 Sep 29 2013 /oracle/app/oracle/product/11.2.0/db_1/.patch_storage/17411249_Apr_21_2014_22_29_21/files/rdbms/admin/prvtwrr.plb
Execute the find command to find two files, and the other file is obviously the patch file 17411249. Use opatch to check the patch. The patch was recently installed.
w4sd23pa#[/home/oracle]opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.6 Copyright (c) 2013, Oracle Corporation. All rights reserved. Oracle Home : /oracle/app/oracle/product/11.2.0/db_1 Central Inventory : /oracle/app/oraInventory from : /oracle/app/oracle/product/11.2.0/db_1/oraInst.loc OPatch version : 11.2.0.3.6 OUI version : 11.2.0.4.0 Log file location : /oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-05-27_23-23-16PM_1.log Lsinventory Output file location : /oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-05-27_23-23-16PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.4.0 There are 1 product(s) installed in this Oracle Home. Interim patches (18) : Patch 17411249 : applied on Tue May 20 18:41:12 GMT+08:00 2014 Unique Patch ID: 17551033 Created on 21 Apr 2014, 22:29:21 hrs PST8PDT Bugs fixed: 17411249
After we see this information, we suspect that the patch has a problem. If yes, we will roll back and try again, so we have made a rollback for this patch. In readme, we also need to run a postinstall. SQL script. After the rollback is completed, the preprocessing operation is successfully performed. Then we installed the patch again and ran the postinstall. SQL script. We found that the preprocessing was successful. Later I asked my colleagues who installed the patch, and finally found that he forgot to execute the postinstall. SQL script. In general, this story is still very sad. If you don't execute a find command, you won't find any errors caused by installing new patches.
Original article address: A mad PLS-00306 error analysis, thanks to the original author to share.