1. SQL> alter system reset "_time_based_rcv_ckpt_target" scope = spfile sid='aaa'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE
2. SQL> alter system reset "_bct_public_dba_buffer_size" sid='aaa' scope=spfile ;
System altered.
3. 😶
This error encountered in the alert log file of replay system. The replay was being using to assess the consequences of Oracle database upgrade from 12.2 to 19.16 version.
Actually I found this error trying to figure out the absence of SQL tuning set, generated during capture system, on the replay system. It was signaled in the alert.log during executing DBMS_WORKLOAD_CAPTURE.IMPORT_AWR procedure. This led to inability of generating 'compare sqlsets report' after replaying the captured load.
Moving on to searching the cause, I found out the dump wcr_ca_sts.dmp
file of STS in the <replay_directory>/cap directory. Inspecting it with help if impdp (using parameters SQLFILE and MASTER_ONLY=true), it turned out that dump file contained the only table "SYSTEM"."WRRSTSTAB", which was a stage table for STS from the capture side. And this table existed in the replay database already (created after executing IMPORT_AWR procedure).
Next I tried to unpack stage table and assign STS to different owner :
SQL> begin dbms_sqltune.unpack_stgtab_sqlset (sqlset_name => '%', sqlset_owner => 'NEW_OWNER', replace => false, staging_table_name => 'WRRSTSTAB', staging_schema_owner => 'SYSTEM') ; end ;
/
It generated the error
ORA-19377: no "SQL Tuning Set" with name like "%" exists for owner like "NEW_OWNER"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE", line 10526
ORA-06512: at line 1
19377. 00000 - "no \"SQL Tuning Set\" with name like \"%s\" exists for owner like \"%s\""
*Cause: The user specified a filter to a pack/unpack function for the SQL
Tuning Set that targets no STS in the SYS schema or the staging
table, respectively
*Action: Provide a different filter after checking the state of the system
I didn't try to change sqlset_name, I changed sqlset_owner instead to owner from the source database (SYS). After that execution of unpack_stgtab_sqlset procedure was succeeded.
At that point the last step was :
SQL> update wrr$_captures
set sqlset_owner = 'SYS', sqlset_name = 'sqlset_name' where id = <capture_id> ;
I queried dba_workload_replays view to confirm changes from previous update. Then I was able to generate the report comparing performance using SQL tuning sets from capture and replay processes :
set pages 100 lines 30000 serveroutput on long 2000000000 longchunksize 99999 trim on trimspool on termout off head off
var sql_r clob
spool replay_compare_sqlset_report.html
--alter session set events '19119 trace name context forever, level 0x400000';
declare
r clob ;
a varchar2 (20) ;
begin
r := 'test string' ;
a := dbms_workload_replay.compare_sqlset_report (replay_id1 => &1, replay_id2 => null, format => 'HTML', result => :sql_r) ;
-- dbms_output.put_line (r) ;
-- :sql_r := r ;
end ;
/
print :sql_r
spool off
exit
Good Luck !
I caught this error when I was running $ORACLE_HOME/OPatch/datapatch -verbose command to apply all the patches of 19.14 RU to 19.13 CDB and its PDBs. The error looked like this :
$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.14.0.0.0 Production on Wed Sep 14 20:31:36 2022
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_29306_2022_09_14_20_31_36/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
...
PDB PDB2:
Applied 19.13.0.0.0 Release_Update 211010063823 successfully on 16-NOV-21 07.25.29.732858 PM
Error: prereq checks failed!
patch 32218454: Error reading descriptor from registry: Couldn't open encmap cp866.enc:
No such file or directory
at /u01/app/oracle/product/19/db_1914/perl/lib/site_perl/5.28.1/sun4-solaris-thread-multi-64/XML/Parser.pm line 187.
XML::Simple called at /u01/app/oracle/product/19/db_1914/sqlpatch/sqlpatch.pm line 8123.
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_29306_2022_09_14_20_31_36/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Wed Sep 14 20:32:23 2022
I can only guess why this error happened. Recently I plugged PDB with codepage RU8PC866 over the network and deleted it after a while. So some information had left inside registry$sqlpatch, and I can assume, it has xml part presented in ru8pc866 codepage. The datapatch invokes perl. The information is parsed by xml-parser perl module (from ORACLE_HOME). It seemed that possible solution was rebuilding registry$sqlpatch (required downtime, sounacceptable) or trying to find missed file (I chose the second).
So, perl module xml-parser has got the following supported (from the box) codepages :
$ l $ORACLE_HOME/perl/lib/site_perl/5.28.1/sun4-solaris-thread-multi-64/XML/Parser/Encodings/
total 554
drwxr-xr-x 2 oracle oinstall 26 Sep 14 23:01 .
drwxr-xr-x 4 oracle oinstall 6 Mar 10 2022 ..
-rwxr-x--- 1 oracle oinstall 40706 Mar 26 2019 big5.enc
-rwxr-x--- 1 oracle oinstall 45802 Mar 26 2019 euc-kr.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 ibm866.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-2.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-3.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-4.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-5.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-7.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-8.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 iso-8859-9.enc
-rwxr-x--- 1 oracle oinstall 4821 Mar 26 2019 Japanese_Encodings.msg
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 koi8-r.enc
-rwxr-x--- 1 oracle oinstall 1950 Mar 26 2019 README
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 windows-1250.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 windows-1251.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 windows-1252.enc
-rwxr-x--- 1 oracle oinstall 1072 Mar 26 2019 windows-1255.enc
-rwxr-x--- 1 oracle oinstall 37890 Mar 26 2019 x-euc-jp-jisx0221.enc
-rwxr-x--- 1 oracle oinstall 37890 Mar 26 2019 x-euc-jp-unicode.enc
-rwxr-x--- 1 oracle oinstall 20368 Mar 26 2019 x-sjis-cp932.enc
-rwxr-x--- 1 oracle oinstall 18202 Mar 26 2019 x-sjis-jdk117.enc
-rwxr-x--- 1 oracle oinstall 18202 Mar 26 2019 x-sjis-jisx0221.enc
-rwxr-x--- 1 oracle oinstall 18202 Mar 26 2019 x-sjis-unicode.enc
I needed to find cp866.enc file somewhere. The README file says :
This directory contains binary encoding maps for some selected encodings.
If they are placed in a directory listed in @XML::Parser::Expat::Encoding_Path,
then they are automatically loaded by the XML::Parser::Expat::load_encoding
function as needed. Otherwise you may load what you need directly by
explicitly calling this function.
These maps were generated by a perl script that comes with the module
XML::Encoding, compile_encoding, from XML formatted encoding maps that
are distributed with that module. These XML encoding maps were generated
in turn with a different script, domap, from mapping information contained
on the Unicode version 2.0 CD-ROM. This CD-ROM comes with the Unicode
Standard reference manual and can be ordered from the Unicode Consortium
at http://www.unicode.org. The identical information is available on the
internet at ftp://ftp.unicode.org/Public/MAPPINGS.
So the task is to find both the scripts and get the source of 866 codepage (found CP866.TXT on ftp://ftp.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/PC)
Both compile_encoding and domap (actually, make_encmap) scripts I found in libxml-encoding-perl_2.09-1_all.deb (untar data.tar.xz from downloaded file). So, do these steps :
tar xf ./data.tar.xz
cd usr/bin
./make_encmap cp866 /temp/service_request/perl/CP866.TXT > cp866_make.out
Edit cp866_make.out (it's actually an xml description of codepage 866) to add expat='yes' at the first line and compile :
<encmap name='cp866' expat='yes'>
./compile_encoding -o cp866.enc cp866_make.out
Put the cp866.enc file into perl module's xml-parser encodings directory, for me it was /u01/app/oracle/product/19/db_1914/perl/lib/site_perl/5.28.1/sun4-solaris-thread-multi-64/XML/Parser/Encodings
and rerun datapatch. In my particular case I cought another error not related to this topic :
Unsupported named object type for bind parameter at /u01/app/oracle/product/19/db_1914/sqlpatch/sqlpatch.pm line 5783.
The cause of it was an absense of tempfile inside one of PDB's temporary tablespace. After adding I ended up with whole CDB patched.
The conclusion : do not use stale and deprecated codepages to keep your data in 😀 ! Oracle recommends all its customers to migrate to AL32UTF8 (but it requres much efforst in many cases and it depends, actually...)
PS. I still don't understand how the patch numbered 32218454 (database 19.10 RU) has turned out to be a cause of this issue. Only one assumption : it had been used with one of PDBs ORACLE_HOME before being plugged in into CDB.
As far as you know, cron provides the schedule on the following time attributes: minute, hour, day of the week, month, day of month. What if you need to set the specific schedule, for example, you need (not) to fire the event only on last working day of the current month and on the next day after it (2 days in a row) ?
The application is restarting every day. Let's assume that working days between Monday and Friday, the current month (September) has 30 days, and last working day is Friday, 28th. So, we need (not) to fire the event on 28th and 29th. How to do this using only crontab file ? Have a look :
#
# If END_OF_MONTH=true, the app server is not stopped and not restarted automatically.
# END_OF_MONTH is set to true when current day is last working day of month, and next day after it (2 days in row).
# a and b - intermediate variables to keep END_OF_MONTH intermediate value.
# Exception when 30.04 is Monday (as in 2018 and 2029; then end of month must be moved to Saturday, 28.04, because 01.05 (day off) is Tuesday),
# or 29.04 is Radonica (in 2025 and 2036, so 30.04 is moved to 03.05, end of month in this case 28.04).
#
cy='date +%Y'
cm='LANG=en date +%b'
nm1wd='$(test $(eval $cm) = "Dec") && eval $(echo "LANG=en date +%u -d \"$(LANG=en date +%b -d "next month") 1 +1year\"") || eval $(echo "LANG=en date +%u -d \"$(LANG=en date +%b -d "next m
onth") 1\"")'
lcmd='LANG=en date +%d -d "$(LANG=en date +%b -d "next month") 1 -1day"'
cday='date +%-d'
cwday='date +%u'
fr1="cal -m | tail -n +3 | awk '{print $5}' | sed -n '4p'"
fr2="cal -m | tail -n +3 | awk '{print $5}' | sed -n '5p'"
last_fr='test -z "$(eval $fr2)" && echo $(eval $fr1) || echo $(eval $fr2)'
sa1="cal -m | tail -n +3 | awk '{print $6}' | sed -n '4p'"
sa2="cal -m | tail -n +3 | awk '{print $6}' | sed -n '5p'"
last_sa='test -z "$(eval $sa2)" && echo $(eval $sa1) || echo $(eval $sa2)'
_END_OF_MONTH='$(test $(eval $nm1wd) -ge 3 -a $(eval $nm1wd) -le 5 -a $(eval $cday) -eq $(eval $lcmd) || (test $(eval $nm1wd) -eq 6 -a $(eval $cday) -eq $(eval $last_fr)) || (test $(eval $n
m1wd) -eq 7 -o $(eval $nm1wd) -eq 1 && test $(eval $cday) -eq $(eval $last_fr) -o $(eval $cday) -eq $(eval $last_sa)) || test $(eval $nm1wd) -eq 2 -a $(eval $cday) -eq $(eval $last_sa) || t
est $(eval $cday) -eq 1 -a $(eval $cwday) -ge 2 -a $(eval $cwday) -le 6) && echo true'
apr_d='LANG=en date +%d%b%a'
apr_eom='$(test $(eval $apr_d) = "28AprSat" -o $(eval $apr_d) = "29AprSun" -o $(eval $apr_d) = "28AprMon" -o $(eval $apr_d) = "29AprTue") && echo true || echo false'
apr_no_eom='$(test $(eval $apr_d) = "30AprMon" -o $(eval $apr_d) = "01MayTue" -o $(eval $apr_d) = "30AprWed" -o $(eval $apr_d) = "01MayThu") && echo true || echo false'
END_OF_MONTH='$(test $(eval $apr_eom) = "true" -a $(eval $cy) != "2031" ) && echo true || echo $(eval $_END_OF_MONTH)'
END_OF_MONTH='$(test $(eval $apr_no_eom) = "true" -a $(eval $cy) != "2031" ) && echo false || echo $(eval $_END_OF_MONTH)'
END_OF_MONTH='$(test \( $(eval $apr_d) = "27AprSat" -o $(eval $apr_d) = "28AprSun" \) -a $(eval $cy) = "2041") && echo true || echo $(eval $_END_OF_MONTH)'
END_OF_MONTH='$(test \( $(eval $apr_d) = "30AprTue" -o $(eval $apr_d) = "01MayWed" \) -a $(eval $cy) = "2041") && echo false || echo $(eval $_END_OF_MONTH)'
00 02 * * * [ "$(echo $(eval $END_OF_MONTH))" != "true" ] && su - app_user -c "/home/app_user/app stop"
00 03 * * 1-7 [ "$(echo $(eval $END_OF_MONTH))" != "true" ] && su - app_user -c "/home/app_user/app restart"
The logic is build around using bash eval and test buildin commands. If END_OF_MONTH is set to true, then nothing happens, app stop and restart tasks aren't run. There are couple of exceptions regarding to my country : 1st of May and Cristian Orthodox holiday named Radonica are always day off; because of this the movements of day off and working days are possible. I tried to count these exceptions in crontab too.
All of a sudden an application named dnf (a successor of yum, used as package manager on Oracle Linux OS) stopped functioning, but throwing the message from the title, even when it's being called with help option(s). The main cause was local machine time, it wasn't synchronized with real clock time. The solution was to sync it, as well as modify the chrony.conf (add string like 'server vm_host iburst' in order to sync time quickly after save_vm->restore_vm operation) and run the dnf with required options :
# systemctl stop chronyd
# chronyd -q 'pool pool.ntp.org iburst'
# systemctl start chronyd
# dnf help
Good Luck !
The assignment - to exclude maintenance of two equal user entities (in Microsoft AD and Oracle DB) and provide password-less entrance into database provided successful authorization in AD. User starts its session on Windows, and run 2-level applications which require authorization in the databases as well.
All the info below is a conspectus. Please look through official Oracle Security documents and blogs for the information in detail. Good starting point is :
1. Important database parameter is os_authent_prefix. Default value of it is "OPS$". It's a prefix to the operating system (OS) username. In order to have equal database and OS usernames, leave the null value for this parameter. It's not online and PDB modifiable - you need to restart the DB/CDB.
2. Authentication goes through Kerberos5 protocol, port 88 (default) should be accessible from database server(s).
3. Edition of database - Enterprise (EE)
4. You need to create so called proxy technological AD user on behalf of whum the kerberos5 ticket will be received on the database side. Toggle "Password never expires", deselect "DES encryption" and "Kerberos Pre-authentication".
On AD side enter the command :
c:> ktpass -princ oracle/db_server_dns_name.dns_domain_name@DOMAIN_NAME -pass <password> -mapuser technological_user@DOMAIN_NAME -crypto all -ptype KRB5_NT_PRINCIPAL -out c:\krb5.keytab.
DOMAIN_NAME (i.e. AD domain name) must always be in upper-case, including in database sql statements containing oracle usernames.
Put resulted file to $ORACLE_HOME/network/admin server directory.
!Important! Objects in AD have so called knum attribute (analog of SCN 😀 in Oracle DB), which goes forward after any alterations of the user. So keep the keytab file in sync with this AD user. After any alteration of technological user recreate keytab file, because database checks the equality of technological user's knum and knum inside keytab. Also, because of this, create dedicated AD user for every other (standby or else) database servers.
5. Example of Oracle server configuration files :
5.1 Add these lines into sqlnet.ora
# kerberos parameters
sqlnet.kerberos5_conf = /u01/app/oracle/product/1910/network/admin/krb5.conf
sqlnet.kerberos5_conf_mit = true
sqlnet.fallback_authentication = true
sqlnet.authentication_kerberos5_service = oracle
sqlnet.authentication_services = (beq, kerberos5pre, kerberos5)
names.directory_path = (ldap, tnsnames, ezconnect, hostname)
# server-side only
sqlnet.kerberos5_keytab = /u01/app/oracle/product/1910/network/admin/krb5.keytab
5.2 krb5.conf
[libdefaults]
default_realm = DOMAIN_NAME
clockskew = 6000
passwd_check_s_address = false
noaddresses = true
forwardable = yes
[realms]
DOMAIN_NAME = {
kdc = domain_controller_1.DOMAIN_NAME:88
kdc = domain_controller_2.DOMAIN_NAME:88
}
[domain_realm]
.domain_name = DOMAIN_NAME
domain_name = DOMAIN_NAME
.DOMAIN_NAME = DOMAIN_NAME
DOMAIN_NAME = DOMAIN_NAME
5.3 Also it's important to enable tracing on the server in case of troubleshooting (there gonna be lots of trace files with huge amount of space; so keep an eye on the free space in trace file directory). The example of enabled tracing in sqlnet.ora :
# trace
DIAG_ADR_ENABLED = off
TRACE_LEVEL_SERVER = on
TRACE_DIRECTORY_SERVER = /u01/app/oracle/product/1910/krb5/trace
TRACE_FILE_SERVER=oracle
Some of the settings are enabled on the fly, but very likely you should restart database to enable most of them.
6. On the client machine add authentication settings to sqlnet.ora :
sqlnet.kerberos5_conf = %ORACLE_HOME%\network\admin\krb5.conf
sqlnet.kerberos5_conf_mit = true
sqlnet.fallback_authentication = true
sqlnet.authentication_kerberos5_service = oracle
sqlnet.authentication_services = (kerberos5pre, kerberos5)
sqlnet.kerberos5_cc_name = OSMSFT://
krb5.conf may be replicated from the database server.
!Important! If the task or program doesn't support AD authentication and there's a need to logon to database user authenticated by the database, use another dedicated sqlnet.ora with sqlnet.authentication_services = none.
Also make sure that file %windir%\system32\drivers\etc\services contains lines:
kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos
kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos
7. Alter database user to authenticate using Microsoft AD :
SQL> alter user username identified externally as '<ad_user>.DOMAIN_NAME' ;
8. Check username and corresponding AD usernames (with domain) in rhw :
SQL> select username, external_name from dba_users where external_name is not null ;
9. Connect to the database without entering username/password, for example :
c:> sqlplus /@tns_name_for_connect
SQL> select user, sys_context('userenv','authentication_method'), sys_context('userenv','authenticated_identity') from dual ;
Basically, that's all. Hope it'll help ! Enjoy ;-)
Suppose we've got 2 Oracle databases connected via db link : client db and server db. On the client database the PL/SQL procedure (from the package) calls another procedure via database link (from package on the server database) and saves the result into a cursor (which is also declared via the same database link). Next the result is processed and data is delivered to the application.
Out of the sudden, an application started to get the error 6502 sometimes. As it's figured out the cause laid in the length of varchar2 variable (let's call it 'a'). So, 'a' is declared varchar2 (100) variable (in bytes) inside a pl/sql procedure on the client side. It gets data via database link from the cursor (declared via database link as I said before), created on the server side (the corresponding column also has varchar2 (100) datatype (in bytes)).
It seems when client database gets the value of the this column in language different from English it internally converts it into client database characterset (from server database characterset - CL8MSWIN1251 to AL32UTF8 - characterset of client database). When converted value's length is greater then 100 bytes - the error ORA-06502 is arising. At this point increasing variable length (to 1000) fixed the issue.
I hope you were able to comprehend the above ;-) Good Luck !!!
PS: simplified version of the client procedure :
PACKAGE BODY package_body AS
c_num varchar2(30);
FUNCTION getfullinfo(c VARCHAR2,date_from VARCHAR2,date_to VARCHAR2,flag NUMBER DEFAULT 0, cr NUMBER DEFAULT 0, megat NUMBER DEFAULT 0) RETURN curstype
IS
curs cursType;
recTableremote server_user.server_package.table_type@db_link;
recTablelocal local_user.local_package.table_type;
rec local_user.local_package.record_type;
BEGIN
recTablelocal := {initializing the type} ;
server_user.server_package.procedure@db_link(c, date_from, date_to, flag, cr, megat, recTableremote);
for i in 1 .. recTableremote.count
loop
rec.row_numb :=recTableremote(i).row_numb;
...
