ORA-28003: password verification for the specified password failed

The following error occurs when trying to change user password in Oracle Database.

SQL> alter user TESTUSER identified by "TestPassword";
alter user TESTUSER identified by "TestPassword";
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed

Check user profile:

SQL> select profile from dba_users where username='TESTUSER';

PROFILE
------------------------------------------------------
TEST_USER_PROFILE

Find password verify function

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT 
FROM DBA_PROFILES 
WHERE PROFILE='TEST_USER_PROFILE'  
AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME                    RESOURCE   LIMIT
--------------------------       ---------  ----------------------
PASSWORD_VERIFY_FUNCTION         PASSWORD    TEST_USER_VFY_FN

Extract code of password verify function

SQL> select OWNER,substr(TEXT,1,50)  
from dba_source 
where NAME='TEST_USER_VFY_FN' 
order by line;

OWNER      SUBSTR(TEXT,1,50)
---------- --------------------------------------------------
SYS        FUNCTION test_user_vfy_fn (username varchar2,
SYS        BEGIN

...
..
.

SYS                IF LENGTH(password) >= 25 THEN
SYS                        RETURN(TRUE);
SYS                ELSE
SYS                        RETURN(FALSE);
SYS                END IF;
SYS        END;

The password length should be equal or greater than 25.

How to Restore a PDB into another CDB from Tape Backups

This is to demonstrate how to restore a pdb from backups, then plug into a different cdb with same or another pdb name.

Environment

Source database name CDBDBA , pdb name pdba
Target database name CDBDBB, pdb name pdbb

Prepare Auxiliary Database

create pfile from target db

SQL> create pfile='/tmp/initCDBDUP1.ora' from spfile;

File created.

drop pdbb on target if already exists

SQL> ALTER PLUGGABLE DATABASE PDBB  CLOSE IMMEDIATE INSTANCES=ALL;

SQL> DROP PLUGGABLE DATABASE  PDBB  including datafiles;

SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024 from  v$asm_diskgroup order by NAME;

startup auxiliary instance

Modify /tmp/initCDBDUP1.ora file with the following values:

cluster_database = false
control_files ='+DATA1','+FRA' 
db_unique_name = CDBDUP 
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

SQL> startup nomount pfile='/tmp/initCDBDUP1.ora'

The create a spfile, which is compulsory for duplicate database.

SQL>  create spfile from pfile='/tmp/initCDBDUP1.ora';

Make sure $ORACLE_HOME/dbs/initCDBDUP1.ora pointing to newly created spfile either in $ORACLE_HOME/dbs or in ASM

Then force instance to use spfile.

SQL> startup nomount force
ORACLE instance started.

Total System Global Area 5.3687E+10 bytes
Fixed Size 37495560 bytes
Variable Size 9261023232 bytes
Database Buffers 4.4292E+10 bytes
Redo Buffers 96718848 bytes

SQL> show parameter pfile;

NAME    TYPE  VALUE
-----   ----  -------------------------------------------------------------
spfile string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileCDBDUP1.ora

Maybe the spfile created in ASM :

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/CDBDUP/PARAMETERFILE/spfile.859.1175610247

Duplicate Database with Required pdb:

$ nohup rman auxiliary / catalog rman@rman_catalog/Passwd cmdfile=duplicate_CDBDBA_PDBA_to_CDBDUP.rmna log= rman_duplicate_CDBDBA_PDBA_to_CDBDUP.log &

$ cat duplicate_CDBDBA_PDBA_to_CDBDUP.rman
run
{
Allocate auxiliary channel c1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
Allocate auxiliary channel c2 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
Allocate auxiliary channel c3 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
SET NEWNAME FOR DATABASE TO '+DATA';
duplicate database CDBDBA to CDBDUP pluggable database pdba until time "TO_DATE('2024-07-10 11:11:11','YYYY-MM-DD HH24:MI:SS')";
}

While duplicate a PDB, the following error might occur at the end of the RMAN process when dropping a tablespace, though it can be ignored because the PDB has been open.

Executing: drop tablespace "AUDIT_AUX" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/19/2025 19:39:47
RMAN-05501: aborting duplication of target database
RMAN-06136: Oracle error from auxiliary database: ORA-22868: table with LOBs contains segments in different tablespaces

Recovery Manager complete.

Unplug and Plug pdb

The rest of the work is to unplug the PDB from the temporary auxiliary PDB, drop the auxiliary CDB and finally plug the PDB into the target CDB.

Uuplug pdb

SQL> select name from v$database;

NAME
-----
CDBDUP

SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO
 3     pdba       READ WRITE NO

Better bounce the Auxiliary database here.

SQL> shutdown immediate;

SQL> startup;

SQL> alter pluggable database pdba close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database  pdba unplug into '/tmp/pdba.xml';

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO

plug pdb into new CDB

SQL> select name from v$database;

NAME
-----
CDBDBB

SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO

$ grep -v "<parameter>" /tmp/pdba.xml > /tmp/pdba_no_param.xml

Check compatibility:

set serveroutput on
declare
compatible constant varchar2(3) :=
case dbms_pdb.check_plug_compatibility(
pdb_descr_file=>'/tmp/pdba_no_param.xml',
pdb_name=>'pdba')
when true then 'YES'
else 'NO'
END;
begin
dbms_output.put_line(compatible);
end;

/

YES

PL/SQL procedure successfully completed.

SQL> create pluggable database pdbb using '/tmp/pdba_no_param.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

here there are two options
— copy
SQL> create pluggable database pdbb using ‘/tmp/pdba_no_param.xml’ copy;
— NOCOPY TEMPFILE REUSE

SQL> alter pluggable database pdbb open instances=all;

Pluggable database altered.

SQL> alter pluggable database pdbb save state;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO
 3     pdba       READ WRITE NO

Now we can drop the auxiliary database.

drop auxillary duplicate database

-- no need this step, cos after the auxiliary database, the orphan PDB pdba will be dropped with CDB auxiliary database CDBDUP.

SQL> drop pluggable database pdba keep datafiles;

Pluggable database dropped.

SQL> startup mount restrict 

ORACLE instance started.

Total System Global Area 5.3687E+10 bytes
Fixed Size 37495560 bytes
Variable Size 7918845952 bytes
Database Buffers 4.5634E+10 bytes
Redo Buffers 96718848 bytes
Database mounted.

SQL> drop database;

Database dropped.

If you use COPY option to create the pdb, then you are able to remove the ASM files of the old pdb manually if still there.

ASMCMD> pwd
+data2/CDBDUP/05CBB1A7A1FCD520E0630C21010A57ED/datafile
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> pwd
+data2/CDBDUP/05CBB1A7A1FCD520E0630C21010A57ED/datafile
ASMCMD> cd ../
ASMCMD-8002: entry 'CDBDUP' does not exist in directory '+data2/'

run datapatch if rquired when the patch versions are different.

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

SQL> select NAME,CAUSE,MESSAGE,TYPE,CON_ID  from PDB_PLUG_IN_VIOLATIONS where STATUS !='RESOLVED';

If still get Errors in PDB_PLUG_IN_VIOLATIONS

check rollback/applied sqlpatch to confirm.
SQL>set linesize 150col logfile for a90
SQL>select patch_id, action, logfile, status from registry$sqlpatch;
Bounce the database, make PDB online on all nodes, the it should be fine.

SQL> select NAME,CAUSE,MESSAGE,TYPE,CON_ID  from PDB_PLUG_IN_VIOLATIONS where STATUS !='RESOLVED';

How to Create BI Publisher Report in Oracle OEM

Create a Database Connection

Go to BI Publisher -> Administration->JDBC Connection

Create a Data Model

New->Data Model -> Upper Left ‘+’ icon -> SQL Query -> Data -> View -> Table View -> Save as Sample Data -> Top Right Save Data Model

Create a Report

Select Blank( Portrait ) -> Text Item ( for Header ) -> Drag “Data Table” -> Save ( Top Right ) -> Done

Schedule a Job

New -> Report Job ->Select a report to schedule …..

How to Change Global_Name for a PDB after Added Default Domain

For some reason, a database has to be reconfigured with a domain name “.world”. After change the spfile and bounced the database, we need change the global_name manually.

Check current global_name in CDB:

SQL>  select * from global_name;

GLOBAL_NAME
--------------
TESTDB

2. Drop database links now, and need recreate them later. because they are inaccessible after global_name changed. Otherwise you will get the following errors:

SQL>  drop  database link TESTDBLINK;
 drop  database link TESTDBLINK
                     *
ERROR at line 1:
ORA-02024: database link not found

To fix this issue, you have to rename global_name back to without ‘.world’, and remove domain from GLOBAL_NAME. The only way to remove the domain is to manually update the props$ table.

SQL> connect / as sysdba

SQL> show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
db_domain                            string      world

SQL> alter database rename global_name to "TESTDB";

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
-------------
TESTDB

SQL> update sys.props$ set value$ = 'TESTDB' where name = 'GLOBAL_DB_NAME';

1 row updated.

SQL>  select * from global_name;

GLOBAL_NAME
--------------
TESTDB

SQL> commit;

SQL> drop database link TESTDB; -> Drop old dblinks

Database link dropped.

at this point, you should define db_domain to a not NULL value, rename GLOBAL_NAME to db_name.db_domain and drop and recreate all dblinks.

SQL>  alter database rename global_name to "TESTDB.WORLD";

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------
TESTDB.WORLD

3. Change global name in CDB.

QL> alter database TESTDB rename global_name to "TESTDB.WORLD";
Database altered.

SQL> select * from global_name;
GLOBAL_NAME
----------------------------------
TESTDB.WORLD

4. Connect to PDB and restart

This is can be done node by node to take advantage of RAC high availability. One node with domain and other one without domain. so the application can still be running during this change.

SQL> select * from global_name;

GLOBAL_NAME
----------------------------------
PDB1

SQL> alter session set container=PDB1;
Session altered.

SQL> alter pluggable database PDB1 close;
Pluggable database altered.

SQL> alter pluggable database PDB1 open;
Pluggable database altered.

-- Check global_name for PDB
SQL> select * from global_name;
GLOBAL_NAME
----------------------------------
PDB1.WORLD

Finally recreate all the necessary database links.

Interesting Case

After changing CDB global_name to “TESTDB.WORLD”, the PDB global_name ( PDB1) is not changed after database bounced. “Invalid Username/Password” errors occur when using user created services attached to PDB.

After trying to change CDB global_name to “TESTDB” without domain name, bounced the database, then CDB global_Name is still “TESTDB.WORLD”. The more interesting thing happed. PDB global_name changed to “PDB1.WORLD” strangly.

Just record those for future reference.

ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments: [qksopGetBindName:1]

When running Oracle PRE-Upgrade Fixup Script “preupgrade_fixups.sql” for upgrading 11g to 19c, the following errors occur :

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  purge_recyclebin          YES         None.
    2.  parameter_obsolete        NO          Manual fixup recommended.
    3.  invalid_objects_exist     NO          Manual fixup recommended.
    4.  default_resource_limit    NO          Manual fixup recommended.
    5.  network_acl_priv          NO          Manual fixup recommended.
    6.  exclusive_mode_auth       NO          Manual fixup recommended.
    7.  case_insensitive_auth     NO          Manual fixup recommended.
    8.  streams_setup             NO          Manual fixup recommended.
    9.  mv_refresh                NO          Manual fixup recommended.
   10.  hidden_params             NO          Informational only.
                                              Further action is optional.
   11.  underscore_events         NO          Informational only.
                                              Further action is optional.
   12.  dictionary_stats          YES         None.
   13.  trgowner_no_admndbtrg     YES         None.
DECLARE
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments: [qksopGetBindName:1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_PREUP", line 3282
ORA-06512: at "SYS.DBMS_PREUP", line 3354
ORA-06512: at line 412

Alert and trace file:

ORA-00600: internal error code, arguments: [qksopGetBindName:1], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 219052 (ORA 600 [qksopGetBindName:1]) ========

*** 2024-05-06 13:03:58.038
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=dvnyw9d47b9fv) -----
/* SQL Analyze(0) */ select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  */to_char(count("ADDR")),to_char(substrb(dump(min("ADDR"),16,0,32),1,120)),to_char(substrb(dump(max("ADDR"),16,0,32),1,120)),to_char(count("INDX")),to_char(substrb(dump(min("INDX"),16,0,32),1,120)),to_char(substrb(dump(max("INDX"),16,0,32),1,120)),to_char(count("INST_ID")),to_char(substrb(dump(min("INST_ID"),16,0,32),1,120)),to_char(substrb(dump(max("INST_ID"),16,0,32),1,120)),to_char(count("HADDR_KKOCS")),to_char(substrb(dump(min("HADDR_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("HADDR_KKOCS"),16,0,32),1,120)),to_char(count("PHADD_KKOCS")),to_char(substrb(dump(min("PHADD_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("PHADD_KKOCS"),16,0,32),1,120)),to_char(count("HASHV_KKOCS")),to_char(substrb(dump(min("HASHV_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("HASHV_KKOCS"),16,0,32),1,120)),to_char(count("SQLID_KKOCS")),to_char(substrb(dump(min("SQLID_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("SQLID_KKOCS"),16,0,32),1,120)),to_char(count("CHILDNO_KKOCS")),to_char(substrb(dump(min("CHILDNO_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("CHILDNO_KKOCS"),16,0,32),1,120)),to_char(count("PRED_KKOCS")),to_char(substrb(dump(min("PRED_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("PRED_KKOCS"),16,0,32),1,120)),to_char(count("RANGEID_KKOCS")),to_char(substrb(dump(min("RANGEID_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("RANGEID_KKOCS"),16,0,32),1,120)),to_char(count("LOWSEL_KKOCS")),to_char(substrb(dump(min("LOWSEL_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("LOWSEL_KKOCS"),16,0,32),1,120)),to_char(count("HIGHSEL_KKOCS")),to_char(substrb(dump(min("HIGHSEL_KKOCS"),16,0,32),1,120)),to_char(substrb(dump(max("HIGHSEL_KKOCS"),16,0,32),1,120)) from "SYS"."X$KKOCS_SELECTIVITY" t  /* NDV,NIL,NIL,ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x9cedd8288       145  package body SYS.DBMS_SQLTUNE_INTERNAL
0x9cedd8288     12098  package body SYS.DBMS_SQLTUNE_INTERNAL
0x9cedd8288     12732  package body SYS.DBMS_SQLTUNE_INTERNAL
0x9cedd8288     17413  package body SYS.DBMS_SQLTUNE_INTERNAL

SQL>  EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;
 
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments: [qksopGetBindName:1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 25345
ORA-06512: at "SYS.DBMS_STATS", line 25887
ORA-06512: at line 1

SQL>  select count(*) from  "SYS"."X$KKOCS_SELECTIVITY" ;
 select count(*) from  "SYS"."X$KKOCS_SELECTIVITY"
                             *
ERROR at line 1:
ORA-00600: internal error code, arguments: [qksopGetBindName:1], [], [], [],
[], [], [], [], [], [], [], []

WORKAROUND

Subscribe to get access

Read more of this content when you subscribe today.

SQL>  @ /u01/app/oracle/cfgtoollogs/testdb/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 13
Generated on:            2024-05-06 08:54:07

For Source Database:     TESTDB
Source Database Version: 11.2.0.4.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  purge_recyclebin          YES         None.
    2.  parameter_obsolete        NO          Manual fixup recommended.
    3.  invalid_objects_exist     NO          Manual fixup recommended.
    4.  default_resource_limit    NO          Manual fixup recommended.
    5.  network_acl_priv          NO          Manual fixup recommended.
    6.  exclusive_mode_auth       NO          Manual fixup recommended.
    7.  case_insensitive_auth     NO          Manual fixup recommended.
    8.  streams_setup             NO          Manual fixup recommended.
    9.  mv_refresh                NO          Manual fixup recommended.
   10.  hidden_params             NO          Informational only.
                                              Further action is optional.
   11.  underscore_events         NO          Informational only.
                                              Further action is optional.
   12.  dictionary_stats          YES         None.
   13.  trgowner_no_admndbtrg     YES         None.
   15.  component_info            NO          Informational only.
                                              Further action is optional.
   16.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
   17.  parameter_deprecated      NO          Informational only.
                                              Further action is optional.
   18.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.
   19.  pre_disable_bct_upg       NO          Informational only.
                                              Further action is optional.
   20.  invalid_all_obj_info      NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

After database upgrated

SQL>  desc "SYS"."X$KKOCS_SELECTIVITY" ;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 CON_ID                                                         NUMBER
 HADDR_KKOCS                                                    RAW(8)
 PHADD_KKOCS                                                    RAW(8)
 HASHV_KKOCS                                                    NUMBER
 SQLID_KKOCS                                                    VARCHAR2(13)
 CHILDNO_KKOCS                                                  NUMBER
 PRED_KKOCS                                                     VARCHAR2(40)
 RANGEID_KKOCS                                                  NUMBER
 LOWSEL_KKOCS                                                   VARCHAR2(10)
 HIGHSEL_KKOCS                                                  VARCHAR2(10)


SQL> select count(*) from "SYS"."X$KKOCS_SELECTIVITY" ;

  COUNT(*)
----------
         0

SQL>  exec dbms_stats.gather_table_stats('SYS','X$KKOCS_SELECTIVITY');

PL/SQL procedure successfully completed.


SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.