Database upgrade from Oracle 10.2.0.4(10g) to 11.2.0.4(11g) - Using Manual upgrade
Fix : Oracle XML Database status INVALID in dba_registry during Upgrade
Activity:
I started to upgrade one of my production database which oracle version is 10.2.0.4. I have to upgrade as Oracle 11.2.0.4. Following steps I have completed: ( Manual Upgrade)
1) Downloaded Oracle 11.2.04 version software and copy to a location. Sure about Oracle Homes. Start runinstaller. Select different Oracle Home location as like mentioned below.
10g Home : /u01/app/oracle/product/10.2.0/db_1
11g Home : /u01/app/oracle/product/11.2.0/db_1
Caution: Don't install Oracle 11g software to exiting home. There will be change of DB crash.
2) After runinstaller, Select the following:
- New Oracle Home and Base ( as above)
- Software Only
- Enterprise / Standard Edition
3) Rune the pre-upgrade tool: ( from 11g Home) -- must run
- SQL> spool "upgrade_stat.log";
- SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
It will give what to set or do. Here are sample history of log :
- Minimum undo tbs size=512MB
- Increase share_pool size = 224MB
- Any patch set to apply ( if lower version, like 10.2.1 to 11.2.0.4) etc.
4) If any patchset to apply, download and apply with following steps:
a) Take a full RMAN backup and down the database.
b) Install patch set to any other location and start runinstaller and continue up to END..
c) export ORACLE_SID
d) Start the DB in Upgrade mode.
e) SQL> @?/rdbms/admin/catupgrd.sql
f) SQL> @?/rdbms/admin/utlrp.sql
g) Shut immediate and startup again.
5) After patch set apply, set the recommended values like undo, shared_pool etc.
6) Again run the pre-upgrade tool. No issue should come. "No update parameter changes required" Message should come.
7) Shut down 10g database.
8) Change the location of parameter file(pfile) to new 11g 'dbs'.( $ORACLE_HOME/dbs)
9) Edit the /etc/oratab file and set new 11g home.
10) Edit the bash profile in case of Linux .
11) Edit the parameter file and set 11g specific parameters like diag_dest, audit_dest etc.
12) Now we will upgrade:
$ sqlplus / as sysdba
SQL> startup upgrade;
SQL> @?/rdbms/admin/catupgrd.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> shut immediate;
SQL> startup;
These are my basic steps. All steps are over but when I executed "catupgrd.sql" and "utlrp.sql". I faced the following:
a) catupgrd.sql completed very fast which should not and shows below errors.
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
Misc: Verify your optimizer values like below. If not matched, then change values using 'alter system set ...'
SQL> show parameter optimizer;
NAME TYPE VALUE
------------------------------------ ----------- ---------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
Fix : Oracle XML Database status INVALID in dba_registry during Upgrade
Activity:
I started to upgrade one of my production database which oracle version is 10.2.0.4. I have to upgrade as Oracle 11.2.0.4. Following steps I have completed: ( Manual Upgrade)
1) Downloaded Oracle 11.2.04 version software and copy to a location. Sure about Oracle Homes. Start runinstaller. Select different Oracle Home location as like mentioned below.
10g Home : /u01/app/oracle/product/10.2.0/db_1
11g Home : /u01/app/oracle/product/11.2.0/db_1
Caution: Don't install Oracle 11g software to exiting home. There will be change of DB crash.
2) After runinstaller, Select the following:
- New Oracle Home and Base ( as above)
- Software Only
- Enterprise / Standard Edition
3) Rune the pre-upgrade tool: ( from 11g Home) -- must run
- SQL> spool "upgrade_stat.log";
- SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
It will give what to set or do. Here are sample history of log :
- Minimum undo tbs size=512MB
- Increase share_pool size = 224MB
- Any patch set to apply ( if lower version, like 10.2.1 to 11.2.0.4) etc.
4) If any patchset to apply, download and apply with following steps:
a) Take a full RMAN backup and down the database.
b) Install patch set to any other location and start runinstaller and continue up to END..
c) export ORACLE_SID
d) Start the DB in Upgrade mode.
e) SQL> @?/rdbms/admin/catupgrd.sql
f) SQL> @?/rdbms/admin/utlrp.sql
g) Shut immediate and startup again.
5) After patch set apply, set the recommended values like undo, shared_pool etc.
6) Again run the pre-upgrade tool. No issue should come. "No update parameter changes required" Message should come.
7) Shut down 10g database.
8) Change the location of parameter file(pfile) to new 11g 'dbs'.( $ORACLE_HOME/dbs)
9) Edit the /etc/oratab file and set new 11g home.
10) Edit the bash profile in case of Linux .
11) Edit the parameter file and set 11g specific parameters like diag_dest, audit_dest etc.
12) Now we will upgrade:
$ sqlplus / as sysdba
SQL> startup upgrade;
SQL> @?/rdbms/admin/catupgrd.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> shut immediate;
SQL> startup;
These are my basic steps. All steps are over but when I executed "catupgrd.sql" and "utlrp.sql". I faced the following:
a) catupgrd.sql completed very fast which should not and shows below errors.
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
b) utlrp.sql shows the following errors at end:
warning for:
Warning: XDB now invalid, could not find xdbconfig
ORDIM registered 5 XML schemas.
The following XML schemas are not registered:
http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0
http://xmlns.oracle.com/ord/dicom/anonymity_1_0
http://xmlns.oracle.com/ord/dicom/attributeTag_1_0
http://xmlns.oracle.com/ord/dicom/constraint_1_0
http://xmlns.oracle.com/ord/dicom/datatype_1_0
http://xmlns.oracle.com/ord/dicom/manifest_1_0
http://xmlns.oracle.com/ord/dicom/mapping_1_0
http://xmlns.oracle.com/ord/dicom/mddatatype_1_0
http://xmlns.oracle.com/ord/dicom/metadata_1_0
http://xmlns.oracle.com/ord/dicom/orddicom_1_0
http://xmlns.oracle.com/ord/dicom/preference_1_0
http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0
http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0
http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0
ORDIM DICOM repository has 0 documents.
The following default DICOM repository documents are not installed:
ordcman.xml
ordcmcmc.xml
ordcmcmd.xml
ordcmct.xml
ordcmmp.xml
ordcmpf.xml
ordcmpv.xml
ordcmsd.xml
ordcmui.xml
Fix/ Solution:
Investigation:
1) Verify the registry after upgrade:
set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;
select COMP_NAME, VERSION,status from dba_registry;
COMP_NAME VERSION STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 VALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 INVALID
Oracle XML Database 11.2.0.4.0 INVALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 VALID
Oracle OLAP API 11.2.0.4.0 INVALID
Here few components are not upgraded. Focus on Oracle XML Database which is require for my environment.
Misc: Verify your optimizer values like below. If not matched, then change values using 'alter system set ...'
SQL> show parameter optimizer;
NAME TYPE VALUE
------------------------------------ ----------- ---------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
2) Find the output for below query:
SQL> select any_path from resource_view;
select any_path from resource_view
*
ERROR at line 1:
ORA-31000: Resource 'http://xmlns.oracle.com/xdb/acl.xsd' is not an XDB schema document
From the above two investigation, it is confirmed that pre-upgraded tool is not executed or during startup upgrade session may be canceled or terminated or some other issue may came.
Fix/ Solution:
To validate Oracle Multimedia you can check Oracle Support note: How To Reload Oracle Multimedia Related Information When XML Database (=XDB) Has Been Reinstalled [ID 965892.1]. To validate Oracle Multimedia we followed the following procedure.
sqlplus / as sysdba
SQL>@?/rdbms/admin/catnoqm.sql
SQL>@?/rdbms/admin/catqm.sql
SQL>@?/rdbms/admin/utlrp.sql
Now no message ( as above seen) are not cumming. Now do the following also.
SQL> alter session set current_schema="ORDSYS";
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imxreg.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/impbs.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/impvs.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imtyb.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/implb.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imxrepos.sql;
SQL> exit
sqlplus / as sysdba
SQL> set serveroutput on
SQL> exec validate_ordim;
SQL> exit
Now all of the components are valid.
Conclusion:
Before you upgrade your database version using the manual method always check OS environment variables pointing to new ORACLE HOME.
No comments:
Post a Comment