Sizing the Shared Pool

Resizing the shared pool
To size the shared pool we have only one parameter available to influence the library
cache size.The parameter name is "SHARED_POOL_SIZE".this parameter sizes the shared pool and then The dictionary cache takes some portion of the shared pool as determined by an internal algorithm.The calculations that have been used by the internal algorithm to determine the size of the shared pool
are based on many estimates about which objects were cached, the number of concurrent users,
and concurrent open cursors. These estimates can lead to wildly different values.

Oracle Support recommends using Automatic Shared Memory Management (ASMM). This is
enabled by setting the SGA_TARGET parameter to the value that the entire SGA will use. The
Memory Manager (MMAN) process adjusts the memory allocated to dynamic pools to obtain
the best performance within the memory allowed.

When the database is already in operation, the Shared Pool Advisor gives an indication of an
estimated optimal size. As with any estimate, check other indicators such as latch waits, hard
parses, and reloads for confirmation.
When there is no baseline, set the SHARED_POOL_SIZE to approximately 40% of the
available SGA.We can change this value after monitoring the performance and determine if we need more memory or decreasing the memory

The shared pool requires a certain amount of memory to even allow the instance to start. This
value varies with the packages that are invoked at startup, and that depends on the features and
options that are enabled. Enterprise Manager will invoke certain packages as soon as it starts.

Shared Pool Advisory
The STATISTICS_LEVEL initialization parameter controls the shared pool advisory. The
shared pool advisory statistics track the library cache’s use of shared pool memory and predict
the change in total instance-wide parse times for different sizes of the shared pool.

The below 2 views help you determine how much memory the library cache is using and how much does the library cache needs?.

SQL> select * from  V$LIBRARY_CACHE_MEMORY;

LC_NAMESPACE    LC_INUSE_MEMORY_OBJECTS LC_INUSE_MEMORY_SIZE LC_FREEABLE_MEMORY_OBJECTS LC_FREEABLE_MEMORY_SIZE
--------------- ----------------------- -------------------- -------------------------- -----------------------
BODY                                  0                    0                         34                       2
CLUSTER                               0                    0                          0                       0
INDEX                                 0                    0                          0                       0
JAVA DATA                             0                    0                          0                       0
JAVA RESOURCE                         0                    0                          0                       0
JAVA SOURCE                           0                    0                          0                       0
OBJECT                                0                    0                          0                       0
OTHER/SYSTEM                          0                    0                          0                       0
PIPE                                  0                    0                          0                       0
SQL AREA                            208                    3                        231                       4
TABLE/PROCEDURE                       0                    0                        391                       3

TRIGGER                               0                    0                          0                       0

12 rows selected.
The V$LIBRARY_CACHE_MEMORY view displays information about memory allocated to
library cache memory objects in different namespaces. A memory object is an internal grouping
of memory for efficient management. A library cache object may consist of one or more
memory objects.
V$LIBRARY_CACHE_MEMORY, provide information to help
you determine how much memory the library cache is using, how much is currently pinned, how
much is on the shared pool’s LRU list, and how much time might be lost or gained by changing
the size of the shared pool. These statistics are reset if the STATISTICS_LEVEL parameter is
set to BASIC, or when the instance is restarted.

In order to estimate  how much memory does the library cache needs?.
SQL> SELECT shared_pool_size_for_estimate AS
pool_size, estd_lc_size,
estd_lc_time_saved
FROM V$SHARED_POOL_ADVICE;

 POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED
---------- ------------ ------------------
       132            8              86003
       148           24              87333
       164           40              88893
       180           56              89500
       196           72              90087
       212           88              90518
       228          104              90742
       244          120              90911
       260          136              91032
       276          152              91118

10 rows selected.

The V$SHARED_POOL_ADVICE view should be the first tool to use when sizing the shared
pool. If the advisory indicates that a larger pool is not useful for the library cache memory
objects, you can drill down to see whether changing the SQL (to improve use of shared cursors)
or other activity (such as deferring DDL to off hours) improves performance.

The V$SHARED_POOL_ADVICE view displays information about the estimated time saved
during parsing using different shared pool sizes. The sizes range from 50% to 200%, in equal
intervals of the current shared pool size, and are not configurable. If rows in the
V$SHARED_POOL_ADVICE view have the same values of parse time savings (given in the
ESTD_LC_TIME_SAVED column), this indicates that there would be no additional hits on
those size ranges for library cache objects. However, if time saving values increase for larger
pool sizes, this indicates that it may help to increase the shared pool size.

The Statspack/AWR reports also present the shared pool advisory.

Attach and de-attach oracle home

To de-attach an oracle home:

/app/home/oracle/produInstaller -silent -attachHome -local -noClusterEnabled -invPtrLoc /app/home/oracle/oraInventory/oraInst.loc -ignorePreReq ORACLE_HOME=/app/home/oracle/product/agent10g ORACLE_HOME_NAME=agent1025g CLUSTER_NODES=node1

Starting Oracle Universal Installer...




Checking Temp space: must be greater than 150 MB.   Actual 510 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 120262 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-07-03_03-17-58PM. Please wait ...oracle@bscsstage01:/app/home/oracle/product/stage/solaris/agent [bscsdb1] >The inventory pointer is located at /app/home/oracle/oraInventory/oraInst.loc

The inventory is located at /app/home/oracle/oraInventory

'AttachHome' was successful.

To attach an oracle home:

/app/home/oracle/produInstaller -silent 
-attachHome    -local -noClusterEnabled -invPtrLoc /app/home/oracle/oraInventory/oraInst.loc -ignorePreReq ORACLE_HOME=/app/home/oracle/product/agent10g ORACLE_HOME_NAME=agent1025g CLUSTER_NODES=node1

Where node1 is the actual hostname.

Setting up Network ACLs in Oracle 11gr SMTP server

From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Fine-Grained Access to these packages by creating an Access Control List to use any external network resource through these packages. Before this any user who had an execute privilege on these packages was able to do anything to any network resource like web and local mail servers etc. But now a user needs a little more then just an execute privilege on the network packages.

Lets talk about using ACL in oracle 11g:

$ sqlplus / as sysdba

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

select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and   grantee = 'PUBLIC';

GRANTEE    TABLE_NAME     PRIVILEGE
---------- -------------- -------------
PUBLIC     UTL_HTTP       EXECUTE

-- By default access on UTL_HTTP is granted to PUBLIC.
-- Revoke from public and grant to specific user who needs it.

revoke execute on utl_http from public;
grant execute on utl_http to scott;

select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and   grantee in ('PUBLIC','SCOTT')

GRANTEE    TABLE_NAME     PRIVILEGE
---------- -------------- -------------
SCOTT      UTL_HTTP       EXECUTE

-- Now only SCOTT has execute rights on UTL_HTTP.

SQL> conn scott/tiger
Connected.

create or replace procedure getTitle(pUrl VARCHAR2)
is
  vResult CLOB;
begin
  vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
  vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
  dbms_output.put_line(vResult);
end;
/

SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
BEGIN getTitle('http://www.oracleflash.com'); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCOTT.GETTITLE", line 5
ORA-06512: at line 1

As you may see that even in the presence of EXECUTE privilege, SCOTT is not able to access the web page using UTL_HTTP and has encountered an error "network access denied by access control list (ACL)". This literally means that the user is being denied access by the Access Control List.

How to configure Access Control List



We need to configure an Access Control List (ACL) and grant "connect" privilege on that ACL to user SCOTT. Then we need to assign host "www.oracleflash.com" to this ACL and any other host to which user SCOTT needs access.




DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()
 creates a new Access Control List. Following are the parameters that it takes.






acl
 => Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.


Description
 => Description of the ACL. 


Principal
 => Name of the user or role (case sensitive) to whom the permissions are being granted or denied.


is_grant
 => TRUE or FALSE, whether to grant access or deny access.


privilege
 => connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.


start_date
 => Start date (optional) of the access to the user.


end_date
 => End date (optional) of the access to the user.



SQL> conn / as sysdba
Connected.
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'oracleflash.xml',
    description  => 'Permissions to access http://www.oracleflash.com',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

Add a privilege to Access Control List



First access to the ACL to any user is granted when the ACL is created with the CREATE_ACL procedure. If any other user or role needs permission on the ACL you may user the procedure ADD_PRIVILEGE.




DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE()
 Add access for more users or roles in an already existing ACL. It takes similar parameters as CREATE_ACL procedure except there is no description parameter and a new parameter position which is used in ADD_PRIVILEGE but not in CREATE_ACL.



The position parameter decides the precedence of the rights for multiple users. For example we grant access to a role ORACLEFLASH at position 1, grant this role to user HR and deny access to user HR at position 2 in ACL. The user HR will still be able to use the network resource because he is granted access via role ORACLEFLASH which takes precedence in the ACL. When granting access to multiple roles and user set the precedence appropriately.

create role oracleflash;

-- A role is created. Now we grant connect to this role on our ACL.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'oracleflash.xml',                
    principal    => 'ORACLEFLASH',
    is_grant     => TRUE, 
    privilege    => 'connect',
    position     => null);
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


So far we have created an ACL and have granted connect access to user SCOTT and role ORACLEFLASH on this ACL. Now is the time to assign network hosts that this ACL can be used to access. In our case the host is "www.oracleflash.com".




Assign a network host to Access Control List






DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()
 assigns a network host local or remote to an ACL. It takes the following parameters:






acl
 => Name of the Access Control List.


host
 => Name of the host. 


lower_port
 => Lower port (optional) from the range of ports allowed on this host.


upper_port
 => Upper port (optional) from the range of ports allowed on this host



Default for lower and upper port is null, which means all ports can be used on this host. And if you provide a port in lower_port and null in upper_port oracle assumes the upper_port=lower_port.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'oracleflash.xml',                
    host         => '*.oracleflash.com');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'oracleflash.xml',                
    host         => '*.oracle.com');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


NOTE: you may assign multiple hosts to one ACL, but you can't assign one host to multiple ACLs. If you do that then the previous assignment will be removed and new will become in effect.




Host Assignments:

Host assignment can be done in many ways. For example if you assign a host to an ACL like www.oracleflash.com, the users can only access www.oracleflash.com. But if you assign a host like *.oracleflash.com, the users can assign any sub-domain on the oracleflash.com. And *.com will grant access to the whole web using .com domains. You need to be careful with this as you may be granting access to more servers then you should.




DBMS_NETWORK_ACL_UTILITY.DOMAINS()
 package contains functions to help determine possible matching domains. The DOMAINS table function returns all possible references against a host, that may be specified in ASSIGN_ACL procedure, in order of precedence.



SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.oracleflash.com'));

COLUMN_VALUE
------------------------------------
www.oracleflash.com
*.oracleflash.com
*.com
*

SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('192.168.0.132'));

COLUMN_VALUE
------------------------------------
192.168.0.132
192.168.0.*
192.168.*
192.*
*


The precedence here means that if you have assigned all these hosts to the ACLs then which host entry will take precedence on others. The above query returns results in order of precedence.

We can verify the ACL's host assignment and privileges via two dictionary views, DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES.

column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com
/sys/acls/oracleflash.xml      *.oracle.com

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true
/sys/acls/oracleflash.xml      ORACLEFLASH          connect    true


Lets now see if the access is enabled or not.

SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
OracleFlash.com: Oracle Articles, Tutorials, Step by Step Install Guides, Scripts.

PL/SQL procedure successfully completed.

SQL> execute getTitle('http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm');
Configuring Privilege and Role Authorization

PL/SQL procedure successfully completed.


The user SCOTT is able to access both the oracleflash.com and oracle.com hosts. Lets see how the ACL grant to a role works.

SQL> conn / as sysdba
Connected.
SQL> grant execute on utl_http to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual;
select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual
              *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1


Even after granting the EXECUTE privilege on UTL_HTTP to user HR, it is not able to access the host www.oracleflash.com. This is because the user HR has no access on the ACL we created for oracleflash.com. Now we will grant the role ORACLEFLASH to user HR, which has access on the ACL for oracleflash.com and see what happens.

SQL> conn / as sysdba
Connected.
SQL> grant oracleflash to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,112) oracleflash
  2  from dual;

ORACLEFLASH
-----------------------------------------------------------------------------------------





This time the HR can access the web page as it has the role ORACLEFLASH, which has access on the ACL.




Cleaning Up the Access Control List




Remove a host from Access Control List

Following procedure can be used to remove a host from the ACL.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com
/sys/acls/oracleflash.xml      *.oracle.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'oracleflash.xml',
    host        => '*.oracle.com'); 
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com





Delete a privilege from Access Control List

Following procedure can be used to delete a privilege from the ACL.

SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true
/sys/acls/oracleflash.xml      ORACLEFLASH          connect    true

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege ( 
    acl         => 'oracleflash.xml', 
    principal   => 'ORACLEFLASH',
    is_grant    => TRUE, 
    privilege   => 'connect');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true





Drop an Access Control List

Following procedure can be used to drop the ACL.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL ( 
    acl         => 'oracleflash.xml');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

no rows selected


See also
Managing Fine-Grained Access to External Network Services
ORA-24248 XMLDB extensible security not installed

Creating ACL for SMTP server:

BEGIN

   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (

 acl        => 'mailserver_acl.xml',

 host       => 'CAINCCHX01.EG01.yahoo.net',

 lower_port => 25,

 upper_port => 25);

 END;

 /

   COMMIT;

   BEGIN

   DBMS_NETWORK_ACL_ADMIN.drop_acl('mailserver_acl.xml');

   END;

   /

   commit;

 

   BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl(

    acl           => 'mailserver_acl.xml',

    description   => 'ACL that lets me talk to the my email server',

    principal     => 'PPM_USER',

    is_grant      => TRUE,

    privilege     => 'connect'

    );

/

    DBMS_NETWORK_ACL_ADMIN.assign_acl(

    acl          => 'mailserver_acl.xml',

    HOST         => 'CAINCCHX01.EG01.Yahoo.net',

    lower_port   => 25,

    upper_port   => 25

    );



    COMMIT;

END;

/

References:
http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html
http://oracledbajourney.blogspot.com/2012/09/ora-24247-network-access-denied-by.html 
 

Applying patch 12949905 to upgrade the time zone to version 17 on RDBMS 11g Release 2-Linux 64Bit

1-Unzip the patch file then CD to the patch directory then issue opatch apply command as shown below:

[oracle@salesincdbstag1 12949905]$ /app/11_2/oracle/product/11.2.0/dbhome_1/OPat                                                             ch/opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /app/11_2/oracle/product/11.2.0/dbhome_1
Central Inventory : /app/11_2/grid/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /app/11_2/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/                                                             opatch2012-04-24_12-42-47PM.log

Applying interim patch '12949905' to OH '/app/11_2/oracle/product/11.2.0/dbhome_                                                             1'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...

Patching component oracle.oracore.rsf, 11.2.0.3.0...
Patch 12949905 successfully applied
Log file location: /app/11_2/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/o                                                             patch2012-04-24_12-42-47PM.log

OPatch succeeded.

2- Check current RDBMS DST version and "DST UPGRADE STATUS":

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

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;  2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        14

Now Prepare the database for patch apply:

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> exec DBMS_DST.BEGIN_PREPARE(17);

PL/SQL procedure successfully completed.
 


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

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       17
DST_UPGRADE_STATE              PREPARE

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
Table truncated.

SQL>
Table truncated.

SQL>

Table truncated.
 


SQL> set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/SQL>   2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.dst$affected_tables;

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
 ROW_COUNT ERROR_COUNT
---------- -----------
SYS                            SCHEDULER$_WINDOW
NEXT_START_DATE
         4           0


SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

no rows selected


SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.



Now We will do the real change:

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

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1954160640 bytes
Fixed Size                  2229624 bytes
Variable Size            1241516680 bytes
Database Buffers          704643072 bytes
Redo Buffers                5771264 bytes
Database mounted.
Database opened.

SQL> set serveroutput on
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
Table truncated.

SQL>
Table truncated.

SQL>

Table truncated.

SQL>
SQL>
SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(17);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         17
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UP                                                             GRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES
SYSMAN                         MGMT_PROV_ASSIGNMENT           YES
SYSMAN                         MGMT_PROV_BOOTSERVER           YES
SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES
SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES
SYSMAN                         MGMT_PROV_IP_RANGE             YES
SYSMAN                         MGMT_PROV_NET_CONFIG           YES
SYSMAN                         MGMT_PROV_OPERATION            YES

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES
SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

14 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UP                                                             GRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_IP_RANGE             YES
SYSMAN                         MGMT_PROV_NET_CONFIG           YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES
SYSMAN                         MGMT_PROV_BOOTSERVER           YES
SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES
SYSMAN                         MGMT_PROV_ASSIGNMENT           YES
SYSMAN                         MGMT_PROV_OPERATION            YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

14 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UP                                                             GRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_IP_RANGE             YES
SYSMAN                         MGMT_PROV_NET_CONFIG           YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES
SYSMAN                         MGMT_PROV_BOOTSERVER           YES
SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES
SYSMAN                         MGMT_PROV_ASSIGNMENT           YES
SYSMAN                         MGMT_PROV_OPERATION            YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

14 rows selected.
SQL> desc sysman.MGMT_PROV_NET_CONFIG;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NET_CONFIG_GUID                           NOT NULL RAW(16)
 LAST_MODIFIED_TIME                                 TIMESTAMP(6) WITH TIME ZONE
 NET_CONFIG_NAME                                    VARCHAR2(255)
 NET_DOMAIN_NAME                                    VARCHAR2(255)
 NET_SUBNET_MASK                                    VARCHAR2(255)
 NET_GATEWAY_ADDRS                                  VARCHAR2(512)
 NET_DNS_ADDRS                                      VARCHAR2(512)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1954160640 bytes
Fixed Size                  2229624 bytes
Variable Size            1241516680 bytes
Database Buffers          704643072 bytes
Redo Buffers                5771264 bytes



Database mounted.
Database opened.
SQL> SQL> SQL> SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
Session altered.

SQL>

Session altered.

SQL>
SQL> set serveroutput on
VAR numfail number
SQL> SQL> BEGIN
  2  DBMS_DST.UPGRADE_DATABASE(:numfail,
  3  parallel => TRUE,
  4  log_errors => TRUE,
  5  log_errors_table => 'SYS.DST$ERROR_TABLE',
  6  log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
  7  error_on_overlap_time => FALSE,
  8  error_on_nonexisting_time => FALSE);
  9  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
 10  END;
 11  /
Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_RPM_REP"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_OPERATION"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"
Number of failures: 0
Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"
Number of failures: 0
Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         17
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE

SQL>
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_17.dat              17

SQL>    VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL>   2    3    4    5
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         17
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_17.dat              17

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        14

SQL> select version FROM v$timezone_file
  2  ;

   VERSION
----------
        17

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        17

SQL> commit;

Commit complete.



End Of the steps