Wednesday, October 18, 2017

Oracle Database Manual upgrade from 11.2.0.1 to 11.2.0.4 on linux

Oracle Database Upgrade from 11.2.0.1 to 11.2.0.4

Hai, this article will illustrate you for upgrading Oracle Database from 11.2.0.1 to 11.2.0.4

Assumptions:
11.2.0.1 HOME_Location =/u01/app/oracle/product/11.2.0/dbhome_1
11.2.0.4 HOME_Location =/u02/app/oracle/product/11.2.0/dbhome_1


 Database Environment files
[oracle@rac1 ~]$ cat 11201.env
export ORACLE_SID=DELL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cat 11204.env
export ORACLE_SID=DELL
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH
[oracle@rac1 ~]$


================================================================================================================
Source database details

[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 4 19:03:07 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 
SQL>
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
-------------------------------- ----------- ------------------------------
spfile                           string      /u01/app/oracle/product/11.2.0
                                              /dbhome_1/dbs/spfileDELL.ora
SQL>
SQL> select NAME, OPEN_MODE, PLATFORM_NAME from v$database;

NAME      OPEN_MODE            PLATFORM_NAME
--------- -------------------- -----------------------------------
DELL      READ WRITE           Linux x86 64-bit

SQL>

SQL> col COMP_NAME for a38
SQL> col VERSION for a15
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME                              VERSION         STATUS
-------------------------------------- --------------- -----------
OWB                                    11.2.0.1.0      VALID
Oracle Application Express             3.2.1.00.10     VALID
Oracle Enterprise Manager              11.2.0.1.0      VALID
OLAP Catalog                           11.2.0.1.0      VALID
Spatial                                11.2.0.1.0      VALID
Oracle Multimedia                      11.2.0.1.0      VALID
Oracle XML Database                    11.2.0.1.0      VALID
Oracle Text                            11.2.0.1.0      VALID
Oracle Expression Filter               11.2.0.1.0      VALID
Oracle Rules Manager                   11.2.0.1.0      VALID
Oracle Workspace Manager               11.2.0.1.0      VALID

COMP_NAME                              VERSION         STATUS
-------------------------------------- --------------- -----------
Oracle Database Catalog Views          11.2.0.1.0      VALID
Oracle Database Packages and Types     11.2.0.1.0      VALID
JServer JAVA Virtual Machine           11.2.0.1.0      VALID
Oracle XDK                             11.2.0.1.0      VALID
Oracle Database Java Packages          11.2.0.1.0      VALID
OLAP Analytic Workspace                11.2.0.1.0      VALID
Oracle OLAP API                        11.2.0.1.0      VALID

18 rows selected.

SQL>


SQL>
SQL>

================================================================================================================
Database Version details

SQL> select INSTANCE_NAME, VERSION from v$instance;

INSTANCE_NAME    VERSION
---------------- ---------------
DELL             11.2.0.1.0

SQL>



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>


SQL> col PRODUCT for a20;
SQL> col version for a12;
SQL> col STATUS for a20;
SQL> select * from product_component_version;


PRODUCT              VERSION      STATUS
-------------------- ------------ --------------------
NLSRTL               11.2.0.1.0   Production
Oracle Database 11g  11.2.0.1.0   64bit Production
Enterprise Edition

PL/SQL               11.2.0.1.0   Production
TNS for Linux:       11.2.0.1.0   Production

SQL>


SQL> col platform_name format a35;
SQL> select platform_id, platform_name from v$database;

PLATFORM_ID PLATFORM_NAME
----------- -----------------------------------
         13 Linux x86 64-bit

SQL>

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
SQL>

================================================================================================================
CRD Files of Source Database

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/control01.ctl
/u01/app/oracle/flash_recovery_area/DELL/control02.ctl

SQL>


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/redo03.log
/u01/app/oracle/DELL/redo02.log
/u01/app/oracle/DELL/redo01.log

SQL>
SQL>
SQL>
SQL> col file_name for a35
SQL>
SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME                           TABLESPACE_NAME
----------------------------------- ------------------------------
/u01/app/oracle/DELL/users01.dbf    USERS
/u01/app/oracle/DELL/undotbs01.dbf  UNDOTBS1
/u01/app/oracle/DELL/sysaux01.dbf   SYSAUX
/u01/app/oracle/DELL/system01.dbf   SYSTEM

SQL>

================================================================================================================
Archive Mode Status

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>

SQL> select username, account_status from dba_users;


================================================================================================================
Creation of User and table

SQL> grant connect, resource to u1 identified by u1;


Grant succeeded.

SQL> conn u1/u1
Connected.


SQL> show user
USER is "U1"

SQL> create table emp (EName varchar(15), ENo number);

Table created.

SQL> insert into emp (EName, ENo) values ('aaa',10);

1 row created.

SQL> insert into emp (EName, ENo) values ('bbb',20);

1 row created.

SQL> insert into emp (EName, ENo) values ('ccc',30);

1 row created.

SQL> commit;

Commit complete


SQL> select * from emp;

ENAME                  ENO
--------------- ----------
aaa                     10
bbb                     20
ccc                     30

SQL>

================================================================================================================
Gathering Database Statistics

SQL> sho user
USER is "SYS"
SQL>
SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SQL>

================================================================================================================
Check List of INVALID objects and clear it

SQL> sho user
USER is "SYS"
SQL>
SQL>
SQL> select owner, object_name, object_type, status from dba_objects where status='INVALID';

 OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
EMD_DATABASE
PACKAGE BODY        INVALID


216 rows selected.

SQL>
SQL>
oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 4 19:03:07 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 
SQL>


SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2017-10-04 19:31:38

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2017-10-04 19:31:49

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

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

no rows selected

SQL>

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 admin]$
Perform a full Database Backup either by RMAN, Cold Backup


================================================================================================================
Install Oracle RDBMS (11.2.0.4) in new Location “/u02/app/oracle/product/11.2.0/dbhome_1”

[oracle@rac1 ~]$ cd /u01/sftwr/11204
[oracle@rac1 ~]$ ll
total 2489644
drwxr-xr-x 7 oracle oinstall       4096 Aug 27  2013 database
-rwxr-xr-x 1 oracle oinstall 1395582860 Oct 18 13:15 p13390677_112040_Linux-x86-64_1of7.zip
-rwxr-xr-x 1 oracle oinstall 1151304589 Oct 18 13:16 p13390677_112040_Linux-x86-64_2of7.zip
[oracle@rac1 ~]$

================================================================================================================
Copy dbs, listener, tns  from old home to new home and change home locations path

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@rac1 dbs]$ ls
hc_DBUA0.dat  hc_DELL.dat  init.ora  lkDELL  orapwDELL  spfileDELL.ora
[oracle@rac1 dbs]$ cp * /u02/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cd
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . 11204.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_HOME
/u02/app/oracle/product/11.2.0/dbhome_1
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_SID
DELL
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$
[oracle@rac1 admin]$ ls utlu112i.sql utlrp.sql catupgrd.sql utlu112s.sql
catupgrd.sql  utlrp.sql  utlu112i.sql  utlu112s.sql
[oracle@rac1 admin]$
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 4 19:36:05 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL>
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size            1275070360 bytes
Database Buffers          855638016 bytes
Redo Buffers                4923392 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-04-2017 19:39:31
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          DELL
--> version:       11.2.0.1.0
--> compatible:    11.2.0.0.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V11
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 885 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 563 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]  VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
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.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
while connected AS SYSDBA:

    SELECT name,description from SYS.V$PARAMETER WHERE name
        LIKE '\_%' ESCAPE '\'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
SQL>

SQL>
SQL>
SQL>
If you found any further warnings and errors then fix it before running upgrade script.




SQL> @catupgrd.sql
....
....
....
....
....
....
.
Oracle Database 11.2 Post-Upgrade Status Tool           10-18-2017 15:01:02
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:04:55
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:02:59
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:17
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:09
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:21
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:12
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:01:00
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:23
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:12
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:01:27
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:07
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:01:26
Spatial
.                                         VALID      11.2.0.4.0  00:01:01
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:04
Oracle Rules Manager
.                                         VALID      11.2.0.4.0  00:00:03
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:14:44

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;

Commit complete.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above sql script is the final step of the upgrade. Please
DOC>   review any errors in the spool log file. If there are any errors in
DOC>   the spool file, consult the Oracle Database Upgrade Guide for
DOC>   troubleshooting recommendations.
DOC>
DOC>   Next restart for normal operation, and then run utlrp.sql to
DOC>   recompile any invalid application objects.
DOC>
DOC>   If the source database had an older time zone version prior to
DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade
DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC>   with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM                This forces user to start a new sqlplus session in order
SQL> REM                to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 admin]$
After Upgrade Process completes, Oracle will shut down Database. You need to start database manually.

 ================================================================================================================
[oracle@rac1 admin]$
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 4 20:12:11 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size            1375733656 bytes
Database Buffers          754974720 bytes
Redo Buffers                4923392 bytes
Database mounted.
Database opened.
SQL>
SQL> select owner, object_name, object_type, status from dba_objects where status='INVALID';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
DEPTH
OPERATOR            INVALID

PUBLIC
USER_EXPFIL_TEXT_INDEX_ERRORS
SYNONYM             INVALID


427 rows selected.

SQL>
SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2017-10-04 20:16:00

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2017-10-04 20:16:56

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

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

no rows selected

SQL>
NOTE: INVALID objects should be Less than or Equal to Source Database after UPGRADE.


SQL>
SQL>
SQL>
SQL>
SQL> @utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           10-04-2017 20:18:54
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:06:57
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:03:29
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:28
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:21
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:29
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:17
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:01:35
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:26
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:21
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:01:52
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:11
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:01:58
Spatial
.                                         VALID      11.2.0.4.0  00:01:28
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:05
Oracle Rules Manager
.                                         VALID      11.2.0.4.0  00:00:04
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:20:09

PL/SQL procedure successfully completed.

SQL>

SQL>
SQL>
SQL>
SQL>
SQL> show parameter spfile;

NAME                           TYPE        VALUE
------------------------------ ----------- ------------------------------
spfile                         string      /u02/app/oracle/product/11.2.0
                                            /dbhome_1/dbs/spfileDELL.ora
SQL>
SQL>
SQL>
SQL> show parameter compatible

NAME                              TYPE        VALUE
--------------------------------- ----------- ------------------------------
compatible                        string      11.2.0.0.0
SQL>
SQL>
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

================================================================================================================
Verify Target Database Version

SQL>
SQL> select INSTANCE_NAME,VERSION from v$instance;

INSTANCE_NAME    VERSION
---------------- -----------------
DELL             11.2.0.4.0

SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>
SQL>
SQL>
SQL> col PRODUCT for a20;
SQL> col version for a12;
SQL> col STATUS for a20;
SQL> select * from product_component_version;

PRODUCT              VERSION      STATUS
-------------------- ------------ --------------------
NLSRTL               11.2.0.4.0   Production
Oracle Database 11g  11.2.0.4.0   64bit Production
Enterprise Edition

PL/SQL               11.2.0.4.0   Production
TNS for Linux:       11.2.0.4.0   Production

SQL>
SQL> col platform_name format a35;
SQL> select platform_id, platform_name from v$database;

PLATFORM_ID PLATFORM_NAME
----------- -----------------------------------
         13 Linux x86 64-bit

SQL>
================================================================================================================
Verify CRD Files and Location

SQL>
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/control01.ctl
/u01/app/oracle/flash_recovery_area/DELL/control02.ctl

SQL>
SQL>
SQL>
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/redo03.log
/u01/app/oracle/DELL/redo02.log
/u01/app/oracle/DELL/redo01.log

SQL>
SQL>
SQL> col file_name for a35
SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME                           TABLESPACE_NAME
----------------------------------- ------------------------------
/u01/app/oracle/DELL/users01.dbf    USERS
/u01/app/oracle/DELL/undotbs01.dbf  UNDOTBS1
/u01/app/oracle/DELL/sysaux01.dbf   SYSAUX
/u01/app/oracle/DELL/system01.dbf   SYSTEM

SQL>


================================================================================================================
Verify User “U1” status and table

SQL>
SQL> select USERNAME, ACCOUNT_STATUS from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYSTEM                         OPEN
SYS                            OPEN
U1                             OPEN
MGMT_VIEW                      EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
DBSNMP                         EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED


31 rows selected.

SQL>
SQL> conn u1/u1
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

SQL> select * from emp;

ENAME                  ENO
--------------- ----------
aaa                     10
bbb                     20
ccc                     30

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 admin]$


                       
This article helps those who like to upgrade there database


Thanks for Reading

Regards,
Mohammed Areefuddin.

 Suggested Topics :
Posted by at
Labels: ,

1 comment:

  1. Sathish AMarch 19, 2019 at 7:21 AM

    Excellent document, thanks for the same, it works for me.

    ReplyDelete

Subscribe to: Post Comments (Atom)