Showing posts with label Upgrade and Migration. Show all posts
Showing posts with label Upgrade and Migration. Show all posts

Tuesday, October 22, 2019

DBUA 12.2 Failed while PDBS Recompile Invalid Objects

When upgrading 12.1 database to 12.2 with DBUA, got following error,

Server errors encountered during the execution of "PDBS Recompile Invalid Objects".

Following information is found in DBUA log file,
SQL> EXECUTE dbms_registry_sys.validate_components;
...(20:04:27) Starting validate_apex for APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_180200
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_180200


These privileges were granted by SYS (SYS is grantor) in original database, and missing during upgrading.

Object privileges in original database,
SQL> select GRANTEE,GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
  2   where owner='MDSYS' and grantee='APEX_180200'
  3   order by grantor,table_name;

GRANTEE        GRANTOR    OWNER     TABLE_NAME                     PRIVILEGE
-------------- ---------- --------- ------------------------------ ----------------------
APEX_180200    MDSYS      MDSYS     MDERR                          EXECUTE
APEX_180200    MDSYS      MDSYS     SDO_GEOM_METADATA_TABLE        DELETE
APEX_180200    MDSYS      MDSYS     SDO_GEOM_METADATA_TABLE        SELECT
APEX_180200    MDSYS      MDSYS     SDO_GEOM_METADATA_TABLE        INSERT
APEX_180200    MDSYS      MDSYS     SDO_META                       EXECUTE
APEX_180200    MDSYS      MDSYS     SDO_UTIL                       EXECUTE
APEX_180200    MDSYS      MDSYS     USER_SDO_INDEX_INFO            SELECT
APEX_180200    SYS        MDSYS     SDO_DIM_ARRAY                  EXECUTE
APEX_180200    SYS        MDSYS     SDO_DIM_ELEMENT                EXECUTE
APEX_180200    SYS        MDSYS     SDO_ELEM_INFO_ARRAY            EXECUTE
APEX_180200    SYS        MDSYS     SDO_GEOMETRY                   EXECUTE
APEX_180200    SYS        MDSYS     SDO_ORDINATE_ARRAY             EXECUTE
APEX_180200    SYS        MDSYS     SDO_POINT_TYPE                 EXECUTE


Object privileges in current (partly upgraded) database,
SQL> select GRANTEE,GRANTOR,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
  2   where owner='MDSYS' and grantee='APEX_180200'
  3   order by grantor,table_name;

GRANTEE        GRANTOR    OWNER      TABLE_NAME                     PRIVILEGE
-------------- ---------- ---------- ------------------------------ --------------------
APEX_180200    MDSYS      MDSYS      MDERR                          EXECUTE
APEX_180200    MDSYS      MDSYS      SDO_GEOM_METADATA_TABLE        DELETE
APEX_180200    MDSYS      MDSYS      SDO_GEOM_METADATA_TABLE        INSERT
APEX_180200    MDSYS      MDSYS      SDO_GEOM_METADATA_TABLE        SELECT
APEX_180200    MDSYS      MDSYS      SDO_META                       EXECUTE
APEX_180200    MDSYS      MDSYS      SDO_UTIL                       EXECUTE
APEX_180200    MDSYS      MDSYS      USER_SDO_INDEX_INFO            SELECT


Workaround

Manually grant the missing privileges and click retry in DBUA.

Wednesday, August 21, 2019

12.2: Preupgrade Utility Fails with ORA-06512: at "SYS.DBMS_PREUP"

While running 12.2 Pre-Upgrade Utility (manually or with DBUA) on 12.1 database, received following errors,
Error in preupgrade tool execution.
ERROR -
ERROR - Unable to run sqlplus due to:
ORA-06512: at "SYS.DBMS_PREUP", line 4401
ORA-06512: at "SYS.DBMS_PREUP", line 9714
ORA-06512: at line 8

declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 56

if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate permissions to the Oracle binaries owner.


This is the bug of the Pre-Upgrade Utility installed in current 12.2 home. It can be fixed by installing new version of the utility as following,

1. Download utility ZIP file from My Oracle Support following the instruction of document "How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)"
  
2. The current available ZIP file for 12.2 (target upgrade-to version) is preupgrade_12201_cbuild_22_lf.zip, it contains following files,
   dbms_registry_basic.sql         
   dbms_registry_extended.sql
   preupgrade.jar
   preupgrade_driver.sql
   preupgrade_messages.properties
   preupgrade_package.sql

3. Unzip downloaded file to directory $ORACLE_HOME/rdbms/amdin, here $ORACLE_HOME is 12.2 home.

   Note: Rename existing files before unzipping, do not override any files.

Sunday, November 18, 2018

Patch or Upgrade Single instance Database and Oracle Restart Using OEM 13c Fleet Maintenance

Oracle Enterprise Manager (OEM) 13c Database Fleet Maintenance is a centralized mechanism for patching/upgrading of Oracle homes and maintain consistency. It allows OEM Cloud Control administrators to patch database, Oracle Restart (Standalone Grid Infrastructure) or Grid Infrastructure with minimal downtime.

Friday, August 3, 2018

DBUA 12.2 failed with ORA-01157 at preupgrade tool execution

DBUA got following errors at 'Prerequisite Check' (perupgrade tool execution) while upgrading Oracle database 11.2.0.4 to 12.2.0.1
Error in preupgrade tool execution. ERROR - ERROR - Unable to run sqlplus due to: ORA-06512: at "SYS.DBMS_PREUP",
line 4386 ORA-06512: at "SYS.DBMS_PREUP", line 9108 ORA-06512: at line 8 declare * ERROR at line 1: ORA-01157:
cannot identify/lock data file 202 - see DBWR trace file ORA-01110: data file 202: '+DATA' ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed
in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate
permissions to the Oracle binaries owner. (more details)

Monday, February 19, 2018

ORA-01017 invalid username/password after 11g database upgraded to 12.2 pluggable database

Oracle database 11.2.0.3 was upgraded to 12.2.0.1 and plugged into CDB as pluggable database with 'create pluggable database ... using ...' command. The container database (CDB) was creted with 12.2 dbca and all built-in users' passwords (sys,system,dbsnmp, etc) were set to same ones as in original 11g database.

Saturday, January 20, 2018

Upgrading Grid Infrastructure from 12.1 to 12.2 failed with [INS-20802]

Customer is upgrading two-node Oracle Grid Infrastructure from version 12.1.0.2 to version 12.2.0.1, and gridSetup.sh returned error [INS-20802] during Preparing for deploying Oracle Grid Infrastructure Management Repository.

Wednesday, January 17, 2018

ORA-01403: no data found on ctxposup.sql while upgrading from 11g to 12.2

From: Oracle Database - Enterprise Edition - Version 11.2.0.3 + PSU 11.2.0.3.5 (14727310)
To:   Oracle Database - Enterprise Edition - Version 12.2.0.1.0
Operation System -  Solaris 10 1/13 s10s_u11wos_24a SPARC


It failed to upgrade database from 11.2.3 to 12.2 with error 'ORA-01403: no data found', and following message is found from log file 'Oracle_Server.log',

REASON:
      ERRORS FOUND: During Upgrade
         FILENAME: /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-01-17_11-42-08-AM/DB01/catupgrd0.log AT LINE NUMBER: 825076
------------------------------------------------------
Identifier CONTEXT 18-01-17 12:37:20
SCRIPT      = [/u01/app/oracle/product/12.2.0/dbhome_1/ctx/admin/ctxposup.sql] <===== STATEMENT 1
ERROR      = [ORA-01403: no data found ORA-06512: at line 28
ORA-06512: at line 8
]
STATEMENT = [declare
  l_owner#  number;
  l_errnum  number;
  l_count   number;

begin
  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
  select idx_id into l_count from dr$index where        <===== STATEMENT 2
    idx_name = 'ENT_EXT_DICT_OBJ' and          
    idx_owner# = l_owner#;                       

  /* if this object does not exist yet, create it. */  
  if (0 = l_count) then
    CTX_DDL.create_policy('CTXSYS.ENT_EXT_DICT_OBJ',    <===== STATEMENT 3
      filter        => 'CTXSYS.NULL_FILTER',
      section_group => 'CTXSYS.NULL_SECTION_GROUP',
      lexer        => 'CTXSYS.BASIC_LEXER',
      stoplist        => 'CTXSYS.EMPTY_STOPLIST',
      wordlist        => 'CTXSYS.BASIC_WORDLIST'
  );
  end if;
exception
  when others then
    l_errnum := SQLCODE;
    if (l_errnum = -20000) then
      null;
    else
      raise;
    end if;
end;]

Script ctxposup.sql (STATEMENT 1) which upgrades Oracle Text raised ORA-01403 while running PL/SQL block listed in the above. Testing individual statements in the block as following:

sys@DB01> select user#  from sys."_BASE_USER" where name='CTXSYS';

     USER#
----------
       100

sys@DB01> ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;

Session altered.

sys@DB01> select idx_id from dr$index where
  2    idx_name = 'ENT_EXT_DICT_OBJ' and
  3    idx_owner# = 100;

no rows selected

The object CTXSYS.ENT_EXT_DICT_OBJ (a policy of Oracle Text) does not exist in the database. Therefore, STATEMENT 2 (select ... into ... from) returned ORA-01403. Apparently, The purpose of STATEMENT 2 is to test whether CTXSYS.ENT_EXT_DICT_OBJ exists, but listing records is not a better choice for exist-testing than counting the number of records. Let's try to replace STATEMENT 2 in script filectxposup.sql with following code

 select count(idx_id) into l_count from dr$index where

Start the upgrade again, it succeeded!

Wednesday, June 10, 2015

Failed to retrieve the password file location used by ASM asm

After Oracle Restart (Grid Infrastructure standalone) is upgraded from 11g to 12c, srvctl get errors:
$ srvctl config asm
ASM home: 
PRCA-1057 : Failed to retrieve the password file location used by ASM asm
PRCR-1097 : Resource attribute not found: PWFILE

Check environment and password file:
$ echo $ORACLE_HOME
/u01/app/grid/product/12.1.0/grid

$ ls -l /u01/app/grid/product/12.1.0/grid/dbs
total 28
-rw-rw---- 1 grid oinstall 2783 Jun  9 14:27 ab_+ASM.dat
-rw-rw---- 1 grid oinstall 1544 Jun  9 14:27 hc_+ASM.dat
-rw-r--r-- 1 grid oinstall 2992 Feb  3  2012 init.ora
-rw-r----- 1 grid oinstall 8704 Jun  9 14:10 orapw+ASM
-rw-r----- 1 grid oinstall 1536 Jun  9 14:27 spfile+ASM.ora

The password file was created and ORACLE_HOME is set correctly. Checked with DBA, the upgrade was done successfully. There was no errors/exceptions during upgrade.
Oracle Support explains that:
12c ASM instance has a attribute called PWFILE(password file), but 11g ASM does not. Looks like the upgrade utility does not create the new attribute for an ASM instance which is upgraded from 11g.
Try to manually re-create the ora.asm resource with -pwfile option:
$ crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on host01

$ srvctl stop asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg', but the force option was not specified
$ srvctl stop asm -f
$ 
$ srvctl config asm
PRCR-1001 : Resource ora.asm does not exist
$ 
$ srvctl add asm -listener LISTENER -spfile /u01/app/grid/product/12.1.0/grid/dbs/spfile+ASM.ora -pwfile /u01/app/grid/product/12.1.0/grid/dbs/orapw+ASM

Test if it is fixed:
$ srvctl sconfig asm
ASM home: 
Password file: /u01/app/grid/product/12.1.0/grid/dbs/orapw+ASM
ASM listener: LISTENER
Spfile: /u01/app/grid/product/12.1.0/grid/dbs/spfile+ASM.ora
ASM diskgroup discovery string: ++no-value-at-resource-creation--never-updated-through-ASM++

There is no error any more.

Friday, January 9, 2015

Relink Oracle Grid Infrastructure Standalone (Oracle Restart) home and Database home after OS is upgraded from AIX 6.1 to 7.0

Oracle Database 11.2.0.3
Operating System AIX 7.0
The server system is upgraded from AIX 6.1 to 7.0, generally IBM guarantees operating system binary compatibility, therefore, no relink of the Oracle
software is required after the upgrade. However, Oracle recommends performing manual relinking of Oracle Home binaries after OS upgrade.

1.Stop Oracle databases (as owner of Oracle Database Home):
$ id  oracle
uid=207(oracle) gid=110(oinstall) groups=60005(dba),60015(asmdba)
$ . oraenv
ORACLE_SID = [oracle] ? ORADB
The Oracle base has been set to /u01/app/oracle
$ srvctl stop home -o /u01/app/oracle/product/11.2.0/db11203 -s stophome.txt -t immediate

2.Stop the OHAS stack (as owner of Oracle Restart Home):
$ id 
uid=208(grid) gid=110(oinstall) groups=60005(dba),60014(asmadmin),60015(asmdba)
$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /u01/app/grid
$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'host01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'host01'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'host01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'host01'
CRS-2677: Stop of 'ora.DATA.dg' on 'host01' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'host01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'host01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'host01' succeeded
CRS-2677: Stop of 'ora.asm' on 'host01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'host01'
CRS-2677: Stop of 'ora.cssd' on 'host01' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'host01'
CRS-2677: Stop of 'ora.evmd' on 'host01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'host01' has completed
CRS-4133: Oracle High Availability Services has been stopped.

3. Remove audit files from Oracle Restart Home if applicable
SQL> show parameter  audit_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------
audit_file_dest string /u01/app/grid/product/11.2.0/gi11203/rdbms/audit

Audit files are saved in Oracle Restart Home, we have to move (backup) audit files out of the home. Otherwise, the following 'unlock' step will take long time.
$ mv /u01/app/grid/product/11.2.0/gi11203/rdbms/audit/*.aud /u01/app/grid/backup

4. Connect as root user and unlock the Oracle Restart installation as follows:
# . oraenv
ORACLE_SID = [root] ? +ASM
The Oracle base has been set to /u01/app/grid
#
# echo $ORACLE_HOME
/u01/app/grid/product/11.2.0/gi11203
#
# $ORACLE_HOME/crs/install/roothas.pl -unlock
Using configuration parameter file: /u01/app/grid/product/11.2.0/gi11203/crs/install/crsconfig_params
Successfully unlock /u01/app/grid/product/11.2.0/gi11203

5. Relink the Oracle Restart installation (as owner of Oracle Restart Home):
$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base remains unchanged with value /u01/app/grid
$
$ ls -l $ORACLE_HOME/rdbms/lib/config.o
-rw-r--r-- 1 grid oinstall 764 Oct 18 2013 /u01/app/grid/product/11.2.0/gi11203/rdbms/lib/config.o
$
$ mv $ORACLE_HOME/rdbms/lib/config.o $ORACLE_HOME/rdbms/lib/config.o.bak
$
$ ls -l $ORACLE_HOME/rdbms/lib/config.o*
-rw-r--r-- 1 grid oinstall 764 Oct 18 2013 /u01/app/grid/product/11.2.0/gi11203/rdbms/lib/config.o.bak
$
$ $ORACLE_HOME/bin/relink all
writing relink log to: /u01/app/grid/product/11.2.0/gi11203/install/relink.log
$

6. Check the relink log:
$ cat /u01/app/grid/product/11.2.0/gi11203/install/relink.log
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 12800 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-01-08_02-01-58PM. Please wait ...Please find the log file at /u01/app/grid/product/11.2.0/gi11203/install/relinkActions2015-01-08_02-02-06-PM.log
... ...
ld: 0711-224 WARNING: Duplicate symbol: .aio_nwait_timeout64
... ...
ld: 0711-773 WARNING: Object /u01/app/grid/product/11.2.0/gi11203/lib/libxml11.a[lgx.o], imported symbol id__Q2_3std8numpunctXTc_
Symbol was expected to be local. Extra instructions
are being generated to reference the symbol.
... ...
ld: 0711-783 WARNING: TOC overflow. TOC size: 185792 Maximum size: 65536
... ...

Oops. Thousands of 'ld: 0711-773' and 'ld: 0711-783' warning messages! Don'g worry, that's expected while Oracle home is relinked on AIX, they can be safely ignored.
We can find new binary file created:
$ ls -l  /u01/app/grid/product/11.2.0/gi11203/bin/oracle*
-rwsr-s--x 1 grid oinstall 265201550 Jan 8 14:04 /u01/app/grid/product/11.2.0/gi11203/bin/oracle
-rwsr-s--x 1 grid oinstall 265345822 Jun 27 2014 /u01/app/grid/product/11.2.0/gi11203/bin/oracleO

7. Connect as root user and lock back the Oracle Restart installation
# $ORACLE_HOME/crs/install/roothas.pl -patch
Using configuration parameter file: /u01/app/grid/product/11.2.0/gi11203/crs/install/crsconfig_params
CRS-4123: Oracle High Availability Services has been started.

8. Verify OHAS services (As owner of Oracle Restart Home):
$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ora.FLASH.dg
ONLINE ONLINE host01
ora.LISTENER.lsnr
ONLINE ONLINE host01
ora.asm
ONLINE ONLINE host01 Started
ora.ons
OFFLINE OFFLINE host01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE host01
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE host01
ora.oradb.db
1 OFFLINE OFFLINE Instance Shutdown

9. Relink Database home  (as owner of Database home)
$ . oraenv
ORACLE_SID = [oracle] ? ORADB
The Oracle base remains unchanged with value /u01/app/oracle
$
$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 287983743 Jun 27 2014 /u01/app/oracle/product/11.2.0/db11203/bin/oracle
$
$ $ORACLE_HOME/bin/relink all
writing relink log to: /u01/app/oracle/product/11.2.0/db11203/install/relink.log
$
$ ls -l $ORACLE_HOME/bin/oracle*
-rwsr-s--x 1 oracle oinstall 287912357 Jan 8 15:17 /u01/app/oracle/product/11.2.0/db11203/bin/oracle
-rwsr-s--x 1 oracle asmadmin 287983743 Jun 27 2014 /u01/app/oracle/product/11.2.0/db11203/bin/oracleO

10. Start databases:
$ srvctl start home -o /u01/app/oracle/product/11.2.0/db11203 -s stophome.txt

 References:
"Relink All" On AIX Raises Many Warnings And "ld: 0706-032 Linking mode is not specified" for Liborasdk (Doc ID 809153.1)