I’m trying to create a library system (school work) using oracle 10g but i got stuck in creating the simple APEX report and form, the error message says:
ORA-20001: Unable to create modules. ORA-20001: Create pages error.
ORA-20001: Unable to create form page. ORA-20001: Error page=8
item=»P8_BRANCHID» id=»» ORA-20001: Error page=8 item=»P8_BRANCHID»
id=»» has same name as existing application-level item. ORA-0000:
normal, successful completionUnable to create application.
This is my schema, in case I did something wrong:
create table publisher(
PublisherName varchar2(30) not null,
Address varchar2(30) not null,
Phone number(20),
constraint publisher_pk primary key (PublisherName)
);
create table book(
BookId number(4) not null,
Title varchar2(50) not null,
PublisherName varchar2(30) not null,
constraint book_pk primary key (BookId),
constraint book_fk foreign key (PublisherName)
references publisher (PublisherName)
);
create table bookauthors(
BookId number(4) not null,
AuthorName varchar2(30) not null,
constraint bookauthors_pk primary key (BookId,AuthorName),
constraint bookauthors_fk foreign key (BookId) references book (BookId)
);
create table librarybranch(
BranchId number(4) not null,
BranchName varchar2(30) not null,
Address varchar2(30) not null,
constraint librarybranch_pk primary key (BranchId)
);
create table borrower(
CardNo number(4) not null,
BName varchar2(30) not null,
Address varchar2(30) not null,
Phone number(20) not null,
constraint borrower_pk primary key (CardNo)
);
create table bookcopies(
BookId number(4) not null,
BranchId number(4) not null,
No_Of_Copies number(4) not null,
constraint bookcopies_pk primary key (BookId,BranchId),
constraint bookcopies_fk foreign key (BookId) references book (BookId),
constraint bookcopies2_fk foreign key (BranchId) references librarybranch (BranchId)
);
create table bookloans(
BookId number(4) not null,
BranchId number(4) not null,
CardNo number(4) not null,
DateOut date,
DueDate date,
constraint bookloans_pk primary key (BookId,BranchId,CardNo),
constraint bookloans_fk foreign key (BookId) references book (BookId),
constraint bookloans2_fk foreign key (BranchId) references librarybranch (BranchId),
constraint bookloans3_fk foreign key (CardNo) references borrower (CardNo)
);
Thanks.
I encountered this issue now multiple times in my own environment. And I searched MOS and tried different things. But I couldn’t solve it yet. My Upgrade fails with ORA-20001 during datapatch run – and I’d like to show you how to bring the upgrade to an successful end.
It all starts with an upgrade
In my lab environment I upgrade two databases often in parallel. An 11.2.0.4 database and a 12.2.0.1. The 11.2.0.4 upgrades always flawless. But the 12.2.0.1 occasionally fails. This is the screen AutoUpgrade is showing me – but this is not an AutoUpgrade problem. It is not even a “upgrade” but a patching problem.
At first, my source databases are patched to the most recent patch bundles. At the moment while I write this, it means the July 2020 patch bundles.
I use AutoUpgrade here simply because it is easier. Why should I type instead or click DBUA screens. Especially as DBUA wouldn’t be able to upgrade two databases in parallel.
This is my config file:
global.autoupg_log_dir=/home/oracle/upg_logs # # Database number 1 # upg1.dbname=DB12 upg1.start_time=NOW upg1.source_home=/u01/app/oracle/product/12.2.0.1 upg1.target_home=/u01/app/oracle/product/19 upg1.sid=DB12 upg1.log_dir=/home/oracle/upg_logs upg1.upgrade_node=localhost upg1.target_version=19 upg1.timezone_upg=no upg1.restoration=no # # Database number 2 # upg2.dbname=FTEX upg2.start_time=NOW upg2.source_home=/u01/app/oracle/product/11.2.0.4 upg2.target_home=/u01/app/oracle/product/19 upg2.sid=FTEX upg2.log_dir=/home/oracle/upg_logs upg2.upgrade_node=localhost upg2.target_version=19 upg2.timezone_upg=no upg2.restoration=no
Nothing unusual.
Upgrade is failing
And while the 11.2.0.4 database upgrades nicely, the 12.2.0.1 fails.
$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config UP19.cfg -mode deploy AutoUpgrade tool launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 2 databases will be processed Type 'help' to list console commands upg> ------------------------------------------------- Errors in database [DB12] Stage [DBUPGRADE] Operation [STOPPED] Status [ERROR] Info [ Error: UPG-1400 UPGRADE FAILED [DB12] Cause: Database upgrade failed with errors For further details, see the log file located at /home/oracle/upg_logs/DB12/103/autoupgrade_20200730_user.log] ------------------------------------------------- Logs: [/home/oracle/upg_logs/DB12/103/autoupgrade_20200730_user.log] ------------------------------------------------- Job 102 completed
Let me find out what has happend.
autoupgrade_user.log
As the output proposed, I will start with: /home/oracle/upg_logs/DB12/103/autoupgrade_20200730_user.log
---------+-------------+
| DB12|UPGRADE [95%]|
+---------+-------------+
2020-07-30 12:24:30.259 ERROR
DATABASE NAME: DB12
CAUSE: ERROR at Line 756922 in [/home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db120.log]
REASON: Error: prereq checks failed!
ACTION: [MANUAL]
DETAILS:
2020-07-30 12:24:30.287 ERROR Database Upgrade Error in File [/home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db120.log] on Database [/home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db120.log]
2020-07-30 12:24:31.945 ERROR UPGRADE FAILED [DB12]
2020-07-30 12:24:31.945 ERROR Exception Error in Database Upgrade [UPG-1400#UPGRADE FAILED [DB12]]
2020-07-30 12:24:31.945 INFO End Upgrade on Database [DB12]
2020-07-30 12:24:34.524 ERROR UPGRADE FAILED [DB12]
2020-07-30 12:24:34.533 ERROR Exception Error in Database Upgrade [DB12]
2020-07-30 12:24:34.570 ERROR db12 Return status is ERROR
2020-07-30 12:24:34.572 ERROR Dispatcher failed: AutoUpgException [UPG-1400#UPGRADE FAILED [DB12]]
2020-07-30 12:24:34.574 INFO Starting error management routine
2020-07-30 12:24:34.585 INFO Ended error management routine
2020-07-30 12:24:34.590 ERROR Error running dispatcher for job 103
Cause: Database upgrade failed with errors
2020-07-30 12:24:34.590 ERROR Dispatcher failed:
Error: UPG-1400
UPGRADE FAILED [DB12]
Cause: Database upgrade failed with errors
For further details, see the log file located at /home/oracle/upg_logs/DB12/103/autoupgrade_20200730_user.log; aborting job 103 for database DB12
Hm … CAUSE: ERROR at Line 756922 in [/home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db120.log]
The main worker’s catupgr0.log
This is the main worker’s upgrade logfile.
[..]
Serial Phase #:105 [DB12] Files:1 Time: 3s
Serial Phase #:106 [DB12] Files:1 Time: 0s
Serial Phase #:107 [DB12] Files:1 Time: 23s
------------------------------------------------------
Phases [0-107] End Time:[2020_07_30 12:24:07]
------------------------------------------------------
Grand Total Time: 1869s
*** WARNING: ERRORS FOUND DURING UPGRADE ***
1. Evaluate the errors found in the upgrade logs
and determine the proper action.
2. Rerun the upgrade when the problem is resolved
REASON:
ERRORS FOUND: During Upgrade
FILENAME: /home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db120.log AT LINE NUMBER: 754911
------------------------------------------------------
Identifier DATAPATCH_ 20-07-30 12:21:32
SCRIPT = [/home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db12_datapatch_upgrade.log]
ERROR = [Error: prereq checks failed!
]
STATEMENT = []
------------------------------------------------------
LOG FILES: (/home/oracle/upg_logs/DB12/103/dbupgrade/catupgrd20200730114721db12*.log)
Upgrade Summary Report Located in:
/home/oracle/upg_logs/DB12/103/dbupgrade/upg_summary.log
End of Input Commands
------------------------------------------------------
Start of DataPatch Logs
------------------------------------------------------
stdout from running datapatch to install upgrade SQL patches and PSUs:
SQL Patching tool version 19.8.0.0.0 Production on Thu Jul 30 12:19:29 2020
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30237_2020_07_30_12_19_29/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Error: prereq checks failed!
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30237_2020_07_30_12_19_29/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Thu Jul 30 12:21:31 2020
stderr from running datapatch to install upgrade SQL patches and PSUs:
End of DataPatch Logs
------------------------------------------------------
So the upgrade itself completed successfully. But datapatch had an issue in the post upgrade phase.
ORA-20001
And this seems to be the problem:
Error: prereq checks failed! verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
The most famous ORA-20001. On MyOracle Support you will find this note: MOS Note:1602089.1 – Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table. But I need more information to get an idea what’s wrong in my case.
And again for the records, I upgrade to an 19.8.0 home, but I saw the same issue with 19.7.0 already, too.
sqlpatch_invocation.log
Ok, let’s go to the next logfile – the /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30237_2020_07_30_12_19_29/sqlpatch_invocation.log. Maybe it has more details?
[2020-07-30 12:21:29] verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table [2020-07-30 12:21:29] Error: prereq checks failed! [2020-07-30 12:21:29] verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table [2020-07-30 12:21:29] Prereq check failed, exiting without installing any patches. ... [2020-07-30 12:21:29] *** END final state end of patching *** [2020-07-30 12:21:31] Please refer to MOS Note 1609718.1 and/or the invocation log [2020-07-30 12:21:31] /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30237_2020_07_30_12_19_29/sqlpatch_invocation.log[2020-07-30 12:21:31] for information on how to resolve the above errors.
That is not really helpful.
But there is an sqlpatch_debug.log in the same directory. And this has this error sequence:
[2020-07-30 12:21:29] QPI: Caught error while querying opatch_xml_inv [2020-07-30 12:21:29] QPI: in REFRESH_OPATCH_DATA, ERROR code -29913 : ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error KUP-04095: preprocessor command /u01/app [2020-07-30 12:21:29] QPI: Releasing lock SUCCESSFUL, qp_result=0 at : 30-JUL-20 12.21.29.797707000 PM +02:00 [2020-07-30 12:21:29] QPI: Releasing lock FAILED, qp_result=4 at : 30-JUL-20 12.21.29.798750000 PM +02:00 [2020-07-30 12:21:29] QPI: in get_pending_activity, ERROR code -20001 : ORA-20001: Latest xml inventory is not loaded into table
Unfortunately this doesn’t bring me forward either as my error pattern isn’t covered in MOS Note:1602089.1.
alert.log?
Maybe the alert.log has more information?
SERVER COMPONENT id=DP_UPG_BGN: timestamp=2020-07-30 12:19:27 2020-07-30T12:19:47.883482+02:00 XDB initialized. 2020-07-30T12:19:49.188762+02:00 QPI: opatch file present, opatch QPI: qopiprep.bat file present 2020-07-30T12:21:32.945903+02:00 SERVER COMPONENT id=DP_UPG_END: timestamp=2020-07-30 12:21:32
No.
Basically I know that the inventory couldn’t be queried at the end of the upgrade. But my database is upgraded. Hence, no need to restore it. But how do I solve this?
Query the inventory or cleanup metadata
At this stage I wanted to save my upgrade. So I queried the inventory by myself – and it takes quite a bit as you can see here:
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 30 15:18:48 2020 Version 19.8.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 SQL> set timing on SQL> select * from OPATCH_XML_INV; XML_INVENTORY -------------------------------------------------------------------------------- <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Inv entoryInstance> <ora Elapsed: 00:00:35.22 SQL> exit
It may be also a solution to cleanup metadata with:
EXEC dbms_qopatch.clean_metadata();
Complete the upgrade
AutoUpgrade still waits for me – another reason why I don’t use the DBUA which typically is non-resumable. I will resume now my upgrade:
upg> lsj +----+-------+-----------+---------+--------+--------------+--------+-----------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+-----------+---------+--------+--------------+--------+-----------------+ | 102| FTEX|POSTUPGRADE| STOPPED|FINISHED|20/07/30 11:47|12:30:11|Completed job 102| | 103| DB12| DBUPGRADE| STOPPED| ERROR|20/07/30 11:48|12:24:34| UPG-1400| +----+-------+-----------+---------+--------+--------------+--------+-----------------+ Total jobs 2 upg> resume -job 103 Resuming job: [103][DB12]
The output looks promising:
And yes, my database upgrade finished now.
Alternative Workaround
You may use the following workaround – it will increase the threshold to read the inventory:
alter system set "_xt_preproc_timeout"=180 scope=both;
In this case, I set it to 180 seconds.
Annotation:
In a previous version of this blog post, I recommended to set _bug27355984_xt_preproc_timeout instead of _xt_preproc_timeout. Please use _xt_preproc_timeout since otherwise the upgrade to Oracle Database 21c and 23c will fail due to this parameter in your spfile.
Summary
The root cause for this issue may have been incorrect metadata from a previous patch run. Let me emphasize again that this is not an upgrade or AutoUpgrade error. It happens when datapatch gets invoked.
I’m very happy that AutoUpgrade (and the command line upgrade with “dbupgrade -R” are both resumable). Otherwise I wouldn’t have had a chance to fix this so easily. In case you still used the DBUA, you can still jump in with “dbupgrade -R” and complete the upgrade after cleaning up the metadata.
Why do I write such a blog post? Actually, when I hit such an issue with my limited number of upgrades, you may see it another day, too. And the solution is simple. But as I couldn’t find the key in MOS, I would like to show you here how easily you can resume a potentially failed database upgrade – even when the upgrade itself completed fully.
Further Links and Information
- Opatch … oh Opatch … why is Opatch so stubborn? – Dec 15, 2018
- MOS Note:1602089.1 – Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table
–Mike
July 16, 2019
MSSQL
During the 12c database creation process , you can see ORA-20001 error in the alert log file when the “SYS.ORA $ AT_OS_OPT_SY_ <NN>” auto job runs. To fix the error, it is necessary to drop the job and recreate it. Errors will be as follows.
|
ORA—12012: error on auto execute of job «SYS».«ORA$AT_OS_OPT_SY_72» ORA—20001: Statistics Advisor: Invalid task name for the current user ORA—06512: at «SYS.DBMS_STATS», line 47207 ORA—06512: at «SYS.DBMS_STATS_ADVISOR», line 882 ORA—06512: at «SYS.DBMS_STATS_INTERNAL», line 20059 ORA—06512: at «SYS.DBMS_STATS_INTERNAL», line 22201 ORA—06512: at «SYS.DBMS_STATS», line 47197 |
First of all, it is necessary to ensure that the tasks are created correctly with the following command.
|
SQL> EXEC dbms_stats.init_package(); PL/SQL procedure successfully completed. |
Afterwards, it is necessary to identify the owner of the job with the following query.
|
SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks where name in (‘AUTO_STATS_ADVISOR_TASK’,‘INDIVIDUAL_STATS_ADVISOR_TASK’); |
If you see an output like the one below, you should connect with SYS. If the job owner is a different user, you should connect with that user.
|
NAME CTIME HOW_CREATED —————————————————— ————————— ——————————————— OWNER_NAME ———————————————————————————————————————————————————————————— AUTO_STATS_ADVISOR_TASK 13—OCT—18 CMD SYS INDIVIDUAL_STATS_ADVISOR_TASK 13—OCT—18 CMD SYS |
In this case, you should connect with sys via sqlplus and drop and recreate the tasks correctly.
Drop operations can be done as follows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := ‘AUTO_STATS_ADVISOR_TASK’; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; / PL/SQL procedure successfully completed. SQL> DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := ‘INDIVIDUAL_STATS_ADVISOR_TASK’; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; / PL/SQL procedure successfully completed. |
It should then be re-created as follows.
|
SQL> EXEC DBMS_STATS.INIT_PACKAGE(); PL/SQL procedure successfully completed. |
Then there will be no errors in the alert.log file.
Описание некоторых распространенных сообщений об ошибках Oracle
ORA-00001 : unique constraint violated — Вы пытаетесь выполнить вставку или изменение поля, значение которого будет нарушать ограничение уникальности поля.
ORA-01001 : invalid cursor — Вы пытаетесь использовать несуществующий курсор. Вероятная причина – курсор не был открыт, или уже закрыт.
ORA-01012 : not logged on — Вы не авторизованы, но пытаетесь выполнить запрос. Попробуйте залогиниться и повторить запрос.
ORA-01017 : invalid username/password; logon denied — Неверная комбинация логин/пароль.
ORA-01476 : divisor is equal to zero — Вы пытаетесь выполнить деление на ноль.
ORA-01000 : maximum open cursors exceeded — количество открытых курсоров превысило значение параметра OPEN_CURSORS базы данных. Уменьшите количество используемых курсоров в БД в Вашей программе. Если это не помогло, затормозите сервис БД, увеличьте значение параметра OPEN_CURSORS в init.ora и перезапустите сервис БД.
ORA-06511 : PL/SQL: cursor already open — Вы пытаетесь открыть уже открытый курсор
ORA-12545: Connect failed because target host or object does not exist – что-то не то с хостом или ip-адресом, с которым вы пытаетесь соединиться. Проверьте существование хоста командами tnsping в unix и ping в windows.
ORA-12541 : TNS:no listener – Убедитесь, что на сервере запущен прослушивающий процесс (листенер). Если нет, то используйте команду lsnrctl, либо запустите сервис в службах Windows. Если процесс запущен, проверьте правильность настроек файла listener.ora.
ORA-12500 : TNS:listener failed to start a dedicated server process – вероятно, сервис базы данных не запущен. Для Windows проверьте что сервис запущен, обычно сервис имеет имя OracleService, где SID – название базы данных. Для Unix убедитесь, что запущен процесс smon при помощи команды ps: ps -ef | grep ora_smon
ORA-12154 : TNS:could not resolve service name – Вы пытаетесь выполнить подключение к БД, которая неизвестна Вашему клиенту. Проверьте существование и настройки файла tnsnames.ora.
ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor – Во-первых, проверьте что запущен сервис базы данных. Ошибка также может возникать, если сервис БД и листенер еще стартуют, в этом случае подождите минуту-две. Если сервисы запущены, то причина ошибки – сервисы БД и листенер не настроены друг на друга. Проверьте настройки файлов tnsnames.ora и listener.ora.
ORA-12560 : TNS:protocol adapter error – вероятно, не запущен сервис базы данных. Запустите сервис в службах Windows или выполните команду startup в sqlplus.
ORA-20001 : A user specified error message – ошибки с кодами 20000-20999 отданы на откуп разработчикам приложений. Разработчики могут возбуждать такие ошибки в своих приложениях функцией raise_application_error. Обратитесь к разработчикам приложения за более подробной информацией.
While 12.2 database is being started by srvctl, the alert log shows following messages,
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at «SYS.DBMS_QOPATCH», line 777
ORA-06512: at «SYS.DBMS_QOPATCH», line 864
ORA-06512: at «SYS.DBMS_QOPATCH», line 2222
ORA-06512: at «SYS.DBMS_QOPATCH», line 740
ORA-06512: at «SYS.DBMS_QOPATCH», line 2247
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================
DBMS_QOPATCH is introduced by 12.1 database as a cool new feature ‘Queryable OPatch’. It is implemented with a PL/SQL package (DBMS_QOPATCH) and a set of tables and directories. In order to understand what the errors really are, let’s do some details research,
system@orcl> select * from dba_directories where directory_name like ‘OPATCH%’;
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
——- ——————— ————————————————— ————-
SYS OPATCH_INST_DIR /u01/app/oracle/product/12.2.0/dbhome_1/OPatch 0
SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0/dbhome_1/QOpatch 0
SYS OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log 0
system@orcl> exit
[oracle@host01]$ ls -lrt /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log
-rw-r—— 1 oracle osasm 120 Feb 9 17:07 qopatch.log
-rw-r—r— 1 oracle osasm 144227 Feb 10 17:32 qopatch_log.log
[oracle@host01]$
It should be a good guess to start from looking into the log file qopatch_log.log which modification time is very close to the time when errors was reported in alert log,
LOG file opened at 02/10/18 17:32:22
KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.
Field Definitions for table OPATCH_XML_INV
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
XML_INVENTORY CHAR (100000000)
Terminated by «UIJSVTBOEIZBEFFQBL»
Trim whitespace same as SQL Loader
KUP-04095: preprocessor command /u01/app/oracle/product/12.2.0/dbhome_1/QOpatch/qopiprep.bat encountered error
«/u01/app/oracle/product/12.2.0/dbhome_1/QOpatch/qopiprep.bat[55]: /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/stout_orcl.txt: cannot create [Permission de«
The database starting got ORA-20001 while accessing external table OPATCH_XML_INV which has
preprocessor command ‘$ORACLE_HOME/QOpatch/qopiprep.bat’. The table definition is,
system@orcl> select owner,table_name from dba_external_tables where table_name=’OPATCH_XML_INV’;
OWNER TABLE_NAME
———- ———————
SYS OPATCH_XML_INV
system@orcl> select dbms_metadata.get_ddl(‘TABLE’,’OPATCH_XML_INV‘,’SYS‘) from dual;
DBMS_METADATA.GET_DDL(‘TABLE’,’OPATCH_XML_INV’,’SYS’)
———————————————————————————
CREATE TABLE «SYS».»OPATCH_XML_INV» SHARING=METADATA
( «XML_INVENTORY» CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY «OPATCH_SCRIPT_DIR»
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
DISABLE_DIRECTORY_LINK_CHECK
READSIZE 8388608
preprocessor opatch_script_dir:’qopiprep.bat’
BADFILE opatch_script_dir:’qopatch_bad.bad’
LOGFILE opatch_log_dir:’qopatch_log.log’
FIELDS TERMINATED BY ‘UIJSVTBOEIZBEFFQBL’
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION
( «OPATCH_SCRIPT_DIR»:’qopiprep.bat’
)
)
REJECT LIMIT UNLIMITED
system@orcl>
File ‘qopatch_log.log’ is defined as log file of the external table, and will be generated by external table utility while table ‘OPATCH_XML_INV’ is accessed.
According to the table’s definition, PREPROCESSOR-specified command (script file) ‘qopiprep.bat’ will convert ‘raw’ data to records of the table before the table is accessible. That’s why execution error of script ‘qopiprep.bat’ is found in log ‘qopatch_log.log’. The log shows file ‘stout_orcl.txt’ cannot be created while line 55 of the script is being executed, the script code looks as following,
54 rm -rf $ORABASE/rdbms/log/xml_file_$DBSID.xml
55 $ORACLE_HOME/OPatch/opatch lsinventory -xml $ORABASE/rdbms/log/xml_file_$DBSID.xml
-retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORABASE/rdbms/log/stout_$DBSID.txt
56 cat $ORABASE/rdbms/log/xml_file_$DBSID.xml | sed ‘s/^ *//’ | tr ‘n’ ‘ ‘
57 echo «UIJSVTBOEIZBEFFQBL»
58 rm $ORABASE/rdbms/log/xml_file_$DBSID.xml
59 rm $ORABASE/rdbms/log/stout_$DBSID.txt
Check the log directory $ORABASE/rdbms/log (here $ORABASE is same as $ORACLE_HOME) permission,
[oracle@host01]$ ls -ld $ORACLE_HOME/rdbms/log
drwxr-xr-x 3 oracle oinstall 14 Feb 10 18:36 /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log
[oracle@host01]$ id -a
uid=504(oracle) gid=512(oinstall) groups=512(oinstall),513(dba),515(asmdba),519(osasm),520(osdba)
[oracle@host01]$ ls -ld $ORACLE_HOME
drwxr-xr-x 77 oracle oinstall 81 Feb 10 19:22 /u01/app/oracle/product/12.2.0/dbhome_1
The database home owner ‘oracle’ is also the owner of the log directory, and both ‘sqlplus’ and’srvctl’ are executed by ‘oracle’ to start database, all read/write privilges should be inherited from user ‘oracle’ who has full control on the log directory. However, it is only true for sqlplus but not for srvctl.
IS srvctl accessing the external table as user other than oracle? Try to prove it by adding touch command to the script,
54 rm -rf $ORABASE/rdbms/log/xml_file_$DBSID.xml
55 touch /tmp/stout_$DBSID.test
56 $ORACLE_HOME/OPatch/opatch lsinventory -xml $ORABASE/rdbms/log/xml_file_$DBSID.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORABASE/rdbms/log/stout_$DBSID.txt
57 cat $ORABASE/rdbms/log/xml_file_$DBSID.xml | sed ‘s/^ *//’ | tr ‘n’ ‘ ‘
58 echo «UIJSVTBOEIZBEFFQBL»
Try to start database with sqlplus and srvctl respectively,
[oracle@host01]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@host01]$ ls -l /tmp/stout*.test
/tmp/stout*.test: No such file or directory
[oracle@host01]$ sqlplus / as sysdba
<<message truncated>>
SQL> startup
ORACLE instance started.
<<message truncated>>
Database opened.
SQL> exit
[oracle@host01]$ ls -l /tmp/stout*.test
-rw-r—r— 1 oracle oinstall 0 Feb 10 20:10 /tmp/stout_orcl.test
[oracle@host01]$
[oracle@host01]$ rm /tmp/stout_orcl.test
rm: remove /tmp/stout_orcl.test (yes/no)? yes
[oracle@host01]$ srvctl stop database -db orcl
[oracle@host01]$ srvctl start database -db orcl
[oracle@host01]$ ls -l /tmp/stout*.test
-rw-r—r— 1 grid oinstall 0 Feb 10 20:13 /tmp/stout_orcl.test
See, the external table (running script qopiprep.bat) is accessed as grid while srvctl is run, but oracle while sqlplus. Here, grid is the owner of standalone Grid Infrastructure (Oracle Restart) home. What if the external table is accessed directely from sqlplus?
[oracle@host01]$ ls -l /tmp/stout*.test
/tmp/stout*.test: No such file or directory
[oracle@host01]$
[oracle@host01]$ sqlplus system/oracle
<<message truncated>>
SQL> select count(*) from SYS.OPATCH_XML_INV;
COUNT(*)
———-
1
SQL> exit
[oracle@host01]$ ls -l /tmp/stout*.test
-rw-r—r— 1 oracle oinstall 0 Feb 10 20:39 /tmp/stout_orcl.test
[oracle@host01]$ rm /tmp/stout_orcl.test
rm: remove /tmp/stout_orcl.test (yes/no)? yes
[oracle@host01]$ sqlplus system/oracle@host01/orcl
<<message truncated>>
SQL> select count(*) from SYS.OPATCH_XML_INV;
select count(*) from SYS.OPATCH_XML_INV
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command
/u01/app/oracle/product/12.2.0/dbhome_1/QOpatch/qopiprep.bat encountered error
«/u01/app/oracle/product/12.2.0/dbhome_1/QOpatch/qopiprep.bat[56]:
/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/log/stout_orcl.txt: cannot create
[Permission de»
SQL> exit
[oracle@host01]$
[oracle@host01]$ ls -l /tmp/stout*.test
-rw-r—r— 1 grid oinstall 0 Feb 10 20:41 /tmp/stout_orcl.test
Apparently, it succeeded when logged onto database locally (bypass listener), and failed while remotely (going through listener. And the listener is running out of Oracle Restart home whose owner
is grid,
[oracle@host01]$ ps -ef | grep tnslsnr
grid 1887 1 0 Jan 11 ? 19:54 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 64664 36089 0 20:56:12 pts/3 0:00 grep tnslsnr
Although it, sometimes, made sense in previous version (10g? 11g?), it does not happen in 12.1. Therefore, it should be treated as bug :(.
As a temporary workaround, write permission can be granted to group of the log directory as grid is member of oinstall,
[oracle@magnum]$ id -a grid
uid=506(grid) gid=512(oinstall) groups=512(oinstall),514(asmadmin),515(asmdba),516(asmoper),519(osasm),520(osdba),513(dba)
[oracle@host01]$ cd $ORACLE_HOME/rdbms
[oracle@host01]$ ls -ld log
drwxr-xr-x 3 oracle oinstall 16 Feb 10 20:39 log
[oracle@host01]$ chmod g+w log
[oracle@host01]$ ls -ld log
drwxrwxr-x 3 oracle oinstall 16 Feb 10 20:39 log
i have written a test job like
begin
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'test_job'
,job_type =>'plsql_block'
,job_action => 'null'
,enabled => true
);
end;
/
after a while when the check the status
select * from user_scheduler_job_run_details where job_name='TEST_JOB';
the status is failed ,i see these two lines
ORA-20001: You are not allowed to logon from this terminal!!!
ORA-06512: at line 39
how can i resolve this error.
and
how to find the job id of a scheduled job(created using dbms_scheduler)
On checking the alert log file of database i am getting the following error:
Error
2018-03-07T01:38:40.435087-06:00
Errors in file E:ORACLEdiagrdbmsicictraceic_j000_532.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_5501"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
Cause:
Issue occurred while creating the database DBCA, the advisory package is not created properly. This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database.
Solution
1. Connect with the SYSDBA privilege user:
2. Run the following query for checking advisory package:
col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
no rows selected
3. Initialize the package with following commands.
EXEC dbms_stats.init_package();
4. Verify the package create. It will fixed the issue.
col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATED ------------------------------ --------- -------------- AUTO_STATS_ADVISOR_TASK 05-DEC-18 CMD INDIVIDUAL_STATS_ADVISOR_TASK 05-DEC-18 CMD
On a recent upgraded 19c database from 12.1, we got the following errors:
2022-02-10T17:01:20.301348-05:00
Errors in file /opt/oracle/diag/rdbms/PROD/trace/PROD_j002_3750136.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_10969"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 51871
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 891
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22158
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24290
ORA-06512: at "SYS.DBMS_STATS", line 51859There are lots of articles talking about this error on the internet. Basically “Invalid task name” means one of following tasks or both are missing.
- AUTO_STATS_ADVISOR_TASK
- INDIVIDUAL_STATS_ADVISOR_TASK
To fix it, need to recreate them. For example, for my sysetem:
SQL> col name format a30
SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATED OWNER_NAME
------------------------------ --------- ------------ -----------
INDIVIDUAL_STATS_ADVISOR_TASK 06-FEB-22 CMD SYS
I only saw 1 task listed. So I just dropped it and recreated 2 tasks:
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
PL/SQL procedure successfully completed.
SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
no rows selected
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
PL/SQL procedure successfully completed.Now query again and I see two tasks listed
SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME CTIME HOW_CREATED OWNER_NAME
------------------------------ --------- ------------- ----------
AUTO_STATS_ADVISOR_TASK 11-FEB-22 CMD SYS
INDIVIDUAL_STATS_ADVISOR_TASK 11-FEB-22 CMD SYSOptimizer Statistics Advisor is a new feature in 12.2 that runs everyday during the maintenance window. It is built-in diagnostic software that analyzes the quality of statistics and statistics-related tasks.
It runs a predefined task named AUTO_STATS_ADVISOR_TASK once per day in the maintenance window automatically. For the automated job to run, the STATISTICS_LEVEL initialization parameter must be set to TYPICAL or ALL. The task runs as part of the automatic optimizer statistics collection client. The automated task generates findings and recommendations, but does not implement actions automatically. As for any other task, you can configure the automated task, and generate reports. If the report recommends actions, then you can implement the actions manually.
To run the task manually, create your own task using the DBMS_STATS.CREATE_ADVISOR_TASK function, and then run it at any time using the EXECUTE_ADVISOR_TASK procedure. Unlike the automated task, the manual task can implement actions automatically. Alternatively, you can configure the task to generate a PL/SQL script, which you can then run manually.
I can not find anything from Oracle talking about INDIVIDUAL_STATS_ADVISOR_TASK other than it’s referenced with AUTO_STATS_ADVISOR_TASK. My best guess from the task name is that it is related to individual statistics.
Reference:
- How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
- SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
- How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)
- https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/optimizer-statistics-advisor.html#GUID-B934DB62-F4AE-420C-B7C7-EE7FC3747C09
- https://oracle-base.com/articles/12c/optimizer-statistics-advisor-12cr2