Forum Stats

  • 3,836,728 Users
  • 2,262,174 Discussions
  • 7,900,088 Comments

Discussions

datapatch fails with Error :ORA-01422: "exact fetch returns more than requested number of rows" & OR

MartinDecker_ora-solutions
MartinDecker_ora-solutions Member Posts: 216 Bronze Badge
edited Oct 31, 2019 2:16PM in Multitenant

Dear Commnuity,

i am facing this issue in a multitenant database where the ORA-1422 errors appears during datapatch not only in CDB$ROOT, but also in PDB$SEED and customer PDB.

Is the workaround of MOS Note  (Doc ID 2294009.1)  of dropping and recreating registry$sqlpatch also applicable for multienant to perform in all containers?

Regards,
Martin

Take the backup of registry$sqlpatch

SQL>create table registry$sqlpatch_backup as select * from registry$sqlpatch ;

SQL>drop table registry$sqlpatch;

SQL>@?/rdbms/admin/catsqlreg.sql

Re-run ‘datapatch -verbose’

Best Answer

  • MartinDecker_ora-solutions
    MartinDecker_ora-solutions Member Posts: 216 Bronze Badge
    edited Oct 31, 2019 2:16PM Answer ✓

    I think I have found confirmation myself:

    Known Issues: While Opening the Pluggable Database (PDB) (Doc ID 2292143.1)

    1. PDB opened in restricted mode after PSU Patching.

    Applying the October 2016 PSU to the 12.1.0.2 Container Database with PDBs in UPGRADE mode results in PDBs getting stuck in restricted mode.
    Solution: Drop and recreate registry$sqlpatch of CDB and all PDBs with violation

    a) Take backup of CDB registry$sqlpatch, Drop the registry$sqlpatch and rerun the script to create the table
    b)Go to the PDBs, repeat the same

Answers

  • MartinDecker_ora-solutions
    MartinDecker_ora-solutions Member Posts: 216 Bronze Badge
    edited Oct 31, 2019 2:16PM Answer ✓

    I think I have found confirmation myself:

    Known Issues: While Opening the Pluggable Database (PDB) (Doc ID 2292143.1)

    1. PDB opened in restricted mode after PSU Patching.

    Applying the October 2016 PSU to the 12.1.0.2 Container Database with PDBs in UPGRADE mode results in PDBs getting stuck in restricted mode.
    Solution: Drop and recreate registry$sqlpatch of CDB and all PDBs with violation

    a) Take backup of CDB registry$sqlpatch, Drop the registry$sqlpatch and rerun the script to create the table
    b)Go to the PDBs, repeat the same

  • Groove Place
    Groove Place Member Posts: 2 Blue Ribbon

    Oracle Support gave me this working solution for 18.3 Multi Tenant database creation issue as above.

    Take a backup of $ORACLE_HOME/rdbms/admin/prvtsqlpatch.plb

    Add to prvtsqlpatch.plb "create table statement"

    XMLType COLUMN S_CURRENT_PATCH_DESCRIPTOR STORE AS CLOB

    To the "CREATE TABLE dbms_sqlpatch_state ( .....)" statement in the file $ORACLE_HOME/rdbms/admin/prvtsqlpatch.plb

    Execute:

    SQL>@?/rdbms/admin/prvtsqlpatch.plb

    SQL>exit

    cd $ORACLE_HOME/OPatch

    ./datapatch -verbose