oracle11g标准版dbua升级出现ORA-04050

ORA-04050解决办法：

alter system set _system_trig_enabled=FALSE scope=spfile;

alter system set job_queue_processes=0 scope=spfile;

alter system set aq_tm_processes=0 scope=spfile;

ORA-4045 Error During Recompilation/Revalidation Of SYS.DBMS_STANDARD (文档 ID 457415.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and later
Information in this document applies to any platform.
***Checked for relevance on 04-Feb-2016***

SYMPTOMS

Catproc.sql fails with the following error:

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_STANDARD
ORA-04021: timeout occurred while waiting to lock object SYS.CDC_ALTER_CTABLE_BEFORE

CAUSE

Trying to create a database from a database that already exists.

SOLUTION

1. Modify init.ora to contain:

_system_trig_enabled=FALSE
job_queue_processes=0
aq_tm_processes=0

Save init.ora

The following instructions are to be completed from sqlplus as the SYS user:

2. Issue a shutdown immediate:
SQL> shutdown immediate

3. SQL> startup pfile='<insert full path of init.ora here>'

4. SQL> @catalog.sql

5. SQL> @catproc.sql

6. Check for invalids from dba_objects:

    SQL> select owner, object_name from dba_objects where status='INVALID';

7. Run utlrp.sql:
    SQL> @utlrp.sql

8. Check for invalids again.

SQL> select owner, object_name from dba_objects where status='INVALID';

NOTE! This note is only applicable to non-RAC system.

更新时区:

[[email protected]:/home/oracle]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on 木 5月 16 07:42:59 2019

SQL> conn /as sysdba

接続されました。

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

2 FROM DATABASE_PROPERTIES

3 WHERE PROPERTY_NAME LIKE 'DST_%'

4 ORDER BY PROPERTY_NAME;

PROPERTY_NAME

------------------------------

VALUE

--------------------------------------------------------------------------------

DST_PRIMARY_TT_VERSION

DST_SECONDARY_TT_VERSION

DST_UPGRADE_STATE

NONE

[[email protected]:/home/jyc/11.2.0.4]$ unzip DBMS_DST_scriptsV1.9.zip

Archive: DBMS_DST_scriptsV1.9.zip

creating: DBMS_DST_scriptsV1.9/

inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql

inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql

inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql

inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql

[[email protected]:/home/jyc/11.2.0.4]$ PWD

-bash: PWD: command not found

[[email protected]:/home/jyc/11.2.0.4]$ cd DBMS_DST_scriptsV1.9

[[email protected]:/home/jyc/11.2.0.4/DBMS_DST_scriptsV1.9]$ ls

countstarTSTZ.sql countstatsTSTZ.sql upg_tzv_apply.sql upg_tzv_check.sql

[[email protected]:/home/jyc/11.2.0.4/DBMS_DST_scriptsV1.9]$ pwd

/home/jyc/11.2.0.4/DBMS_DST_scriptsV1.9

Make sure to use version 1.9 or higher of the upg_tzv_check.sql and upg_tzv_apply.sql scripts.

After the Oracle RDBMS version upgrade to 11.2.0.4 DBMS_DST can then be used to do an upgrade of the RDBMS DST version of the 11.2.0.4 database.

A) Applying 11.2.0.4 on 11.2.0.3, 11.2.0.2 or 11.2.0.1

Check the current RDBMS DST version of the 11.2.0.3, 11.2.0.2 or 11.2.0.1 Oracle database - this needs to be done for ALL databases in the 11.2.0.3, 11.2.0.2 or 11.2.0.1 home:

Conn / as sysdba

-- this gives the current RDBMS DST version
SELECT version FROM v$timezone_file;

-- check also

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- the output gives

-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <current DST version> <<<<------ this should match version FROM v$timezone_file if not make sure the database is open when selecting from v$timezone_file;
-- DST_SECONDARY_TT_VERSION 0 <<<<------ this should be "0" if not then see point 3a) in note 977512.1
-- DST_UPGRADE_STATE NONE <<<<------ this should be "NONE" if not then see point 3a) in note 977512.1

A.1) The result is lower than 14 for all databases (typically 11)

If the source database is using a timezone file lower than version 14 then utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning (this is normal).
Example output:

WARNING: Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.2.0.2.0 database timezone version
.... to the latest version which comes with the new release.

Note : Every database in the old 11.2.0.3, 11.2.0.2 or 11.2.0.1 home needs to be checked, if one or more has a result higher than 14 you need to follow step A.3)

If your current RDBMS time timezone version is lower than 14 , install 11.2.0.4 in a new home and update the 11.2.0.3, 11.2.0.2 or 11.2.0.1 database to 11.2.0.4
You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.4
The 11.2.0.4 RDBMS DST version after the update to 11.2.0.4 will be the same as your 11.2.0.3, 11.2.0.2 or 11.2.0.1 system.

After the upgrade to 11.2.0.4 you can
* (recommended) update to DSTv14 (standard DST version of 11.2.0.4) by:

or using the upg_tzv_check.sql and upg_tzv_apply.sql scripts in note 1585343.1 Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database .
or following Note 977512.1 Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST from step 3a) onwards using "14" as (<the new DST version number>) in that note.

SQL> conn /as sysdba

接続されました。

SQL> select * from v$timezone_file;

FILENAME VERSION

-------------------- ----------

timezlrg_14.dat 14

SQL> show parameter comp;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cell_offload_compaction string ADAPTIVE

compatible string 11.2.0.0.0

nls_comp string BINARY

plsql_v2_compatibility boolean FALSE

SQL> alter system set compatible='11.2.0.4.0' scope=spfile;

システムが変更されました。

SQL> shutdown immediate

データベースがクローズされました。

データベースがディスマウントされました。

ORACLEインスタンスがシャットダウンされました。

SQL> startup

ORACLEインスタンスが起動しました。

Total System Global Area 2137886720 bytes

Fixed Size 2254952 bytes

Variable Size 553650072 bytes

Database Buffers 1577058304 bytes

Redo Buffers 4923392 bytes

データベースがマウントされました。

データベースがオープンされました。