Database file management against filesystems

How to check whether filesystems can accommodate a database autoextensible datafiles?

First of all, we need to retrieve information about the filesystems in our server, so since this is a HP-UX server, we will use the "bdf" command and create a bdf.ksh file as below. If it is for Linux you can use df command.

#!/bin/ksh
/usr/bin/bdf |grep PSHR/sapdata

PS: I am grepping only for PSHR database filesystems.

Execute it:

Filesystem    1024-blocks Used Available Capacity Mounted on
/dev/vgdbPSHRdata1/PSHR_sapdata1
                   268304384 266199060 1973855   99% /oracle/PSHR/sapdata1
/dev/vgdbPSHRdata1/PSHR_sapdata2
                   268304384 261378897 6492762   98% /oracle/PSHR/sapdata2
/dev/vgdbPSHRdata1/PSHR_sapdata3
                   268304384 261514849 6365306   98% /oracle/PSHR/sapdata3


For instance, we can see /sapdata3 filesystem has 6365306 Kbytes of unused space.

Then we create a directory to point to any directory:

create or replace directory exec_dir as '/oracle/PSHR';

create or replace directory log_dir as '/oracle/PSHR/logs';

And an external table to query this information:

CREATE TABLE DF_DATA
(
  FSNAME     VARCHAR2(50 BYTE),
  BLOCKS     NUMBER,
  USED       NUMBER,
  AVAILABLE  NUMBER,
  CAPACITY   VARCHAR2(10 BYTE),
  MOUNT      VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXE_DIR
     ACCESS PARAMETERS 
       ( records delimited by newline
logfile log_dir:'dfmonitor.log'
preprocessor
exe_dir:'bdf.ksh'
skip 1
fields terminated by
whitespace ldrtrim
 )
     LOCATION (EXE_DIR:'bdf.ksh')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

Let's use it:

SELECT * FROM DF_DATA;
    
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgdbPSHRdata1/PSHR_sapdata1
                   268304384 266199060 1973855   99% /oracle/PSHR/sapdata1
/dev/vgdbPSHRdata1/PSHR_sapdata2
                   268304384 261378897 6492762   98% /oracle/PSHR/sapdata2
/dev/vgdbPSHRdata1/PSHR_sapdata3
                   268304384 261514849 6365306   98% /oracle/PSHR/sapdata3

We create one more table [DF_DATA_INFO], identical to DF_DATA, plus 2 more columns: TO_BE_USED, where we will sum the space still to be claimed by the autoextensible datafiles from DBA_DATA_FILES and DBA_TEMP_FILES, and OK, which will get value 'YES' if TO_BE_USED<AVAILABLE and 'NO' otherwise.

CREATE TABLE DF_DATA_INFO
(
  FSNAME      VARCHAR2(50 BYTE),
  MOUNT       VARCHAR2(40 BYTE),
  BLOCKS      NUMBER,
  USED        NUMBER,
  AVAILABLE   NUMBER,
  CAPACITY    VARCHAR2(8 BYTE),
  TO_BE_USED  NUMBER,
  OK          VARCHAR2(3 BYTE)
)
TABLESPACE DBA_TOOLS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

We run the following PL/SQL code:

DECLARE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE DF_DATA_INFO';

INSERT INTO DF_DATA_INFO(FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, TO_BE_USED, OK)
SELECT FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, 0, 'YES'
FROM DF_DATA;

UPDATE DF_DATA_INFO B SET (B.TO_BE_USED, B.OK)=
(SELECT NVL(SUM(A.MAXBYTES - A.USER_BYTES)/1024, 0), CASE WHEN SUM(A.MAXBYTES - A.USER_BYTES)/1024 >= B.AVAILABLE THEN 'NO' ELSE 'YES' END
FROM (SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_DATA_FILES UNION SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM  DBA_TEMP_FILES) A
WHERE A.AUTOEXTENSIBLE = 'YES'
AND INSTR(A.FILE_NAME, B.MOUNT||'/', 1, 1) = 1);

COMMIT;

END;
/

Now, we query DF_DATA_INFO:

SELECT MOUNT, ROUND(AVAILABLE/1024/1024, 2) "AVAILABLE GB", ROUND(TO_BE_USED/1024/1024, 2) "TO BE USED GB", OK FROM DF_DATA_INFO;
    
MOUNT                                    AVAILABLE_GB TO_B_USED_GB OK
---------------------------------------- ------------ ------------ ---
/oracle/PSHR/sapdata2                             6.19       135.59 NO
/oracle/PSHR/sapdata1                             1.88        17.13 NO
/oracle/PSHR/sapdata3                             61.07        49.25 YES


/sapdata3 filesystem is OK, because it has 61.07 GBytes of free space and all autoextensible datafiles in it could claim 49,25 GBytes maximum.
On the other hand, /sapdata2 has 6.19 GBytes of free space and 135,59 GBytes could be claimed. We should decrease one or more autoextensible datafiles' MAXSIZE.

Easy isn't it :)

SQL query to find archived log generation volume and count report

v$archived_log displays information about the archived logs generated by your database and haven't yet aged out from your control file. So we are going to use this view to generate the report.

Here we have got three queries based on requirement.

1. SQL query to display volume of archived log generated.
2. SQL query to display archived logs generated per hour per day.
3. Combination of above two query.

PS: This is basically for non RAC databases. If you run in a RAC database, you will get aggregated results for it.

The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN.

SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

The following report will display the number of archived logs generated per hour per day:

SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
            "00-01",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
            "01-02",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
            "02-03",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
            "03-04",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
            "04-05",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
            "05-06",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
            "06-07",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
            "07-08",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
            "08-09",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
            "09-10",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
            "10-11",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
            "11-12",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
            "12-13",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
            "13-14",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
            "14-15",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
            "15-16",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
            "16-17",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
            "17-18",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
            "18-19",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
            "19-20",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
            "20-21",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
            "21-22",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
            "22-23",
         SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
            "23-00",
         COUNT (*) TOTAL
    FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

The combination of these scripts is:

SELECT LOG_HISTORY.*,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
                      "00-01",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
                      "01-02",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
                      "02-03",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
                      "03-04",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
                      "04-05",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
                      "05-06",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
                      "06-07",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
                      "07-08",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
                      "08-09",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
                      "09-10",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
                      "10-11",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
                      "11-12",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
                      "12-13",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
                      "13-14",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
                      "14-15",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
                      "15-16",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
                      "16-17",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
                      "17-18",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
                      "18-19",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
                      "19-20",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
                      "20-21",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
                      "21-22",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
                      "22-23",
                   SUM (
                      DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
                      "23-00",
                   COUNT (*) TOTAL
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) LOG_HISTORY,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE LOG_HISTORY.DAY = SUM_ARCH.DAY AND SUM_ARCH.DAY = SUM_ARCH_DEL.DAY
ORDER BY TO_DATE (LOG_HISTORY.DAY, 'DD/MM/YYYY');

EM 12CR2 sizing guidelines

Introduction:

This document will lay out the basic minimum sizing and tuning recommendations for initial capacity planning for your Oracle Enterprise Manager deployment. This document assumes a basic understanding of Oracle Enterprise Manager components and systems.

This document is a starting point for site sizing. Every site has its own characteristics and should be monitor

ed and tuned as needed. This document is intended to complement the Oracle Enterprise Manager installation documentation and covers basic Oracle Enterprise Manager sizing.

Sizing Specifications:

The sizing guidelines for Oracle Enterprise Manager are divided into three sizes: Small, Medium and Large.

The definitions of each size are shown in below table.

Minimum Hardware Requirements:

Lists the minimum hardware requirements for each of the three configurations.

Small Configuration:

The Small configuration is based on the minimum requirements that are required by the Oracle Enterprise Manager installer

Minimum OMS Settings No additional settings are required. Minimum Repository Database Settings

Below table lists the minimum repository database settings that are recommended

Medium Configuration:

The Medium configuration modifies several out-of-the-box Enterprise Manager settings.

Minimum OMS Settings The Oracle Management Service (OMS) heap size should be set to 4096 MB.

Below table lists the minimum repository database settings that are recommended for a Medium configuration

.

Large Configuration:

The Large configuration modifies several out-of-the-box Enterprise Manager settings.

Below table lists the minimum OMS settings that are recommended for a Large configuration

Below table lists the minimum repository database settings that are recommended for a Large configuration

.

Additional Configurations: 

Some Enterprise Manager installations may need additional tuning settings based on larger

individual system loads.These additional settings are listed below.

Large Concurrent UI Load:

If more than 50 concurrent users are expected per OMS, the following settings should be altered as follows

Higher user loads will require more hardware capacity. An additional 2 cores are required for both the database and OMS hosts for every 50 concurrent users

.

Example: A site with 500 agents and 1000 targets with 150 concurrent users would require at a minimum

the setting modifications listed in table (based on a LARGE 2 OMS configuration)

Minimum Additional Hardware required is listed

The physical memory of each machine would have to be increased to support running this configuration as well.

Large Job System Load:

If the jobs system has a backlog for long periods of time or if you would like the backlog processed faster,

set the following parameters with the emctl set property command.

These settings assume that there are sufficient database resources available to support more load. These parameters are likely to be needed in a Large configuration with 2 OMS nodes

Over All:

The resources required for the Enterprise Manager Oracle Management (OMS) Service and Management

Repository tiers will vary significantly based on the number of monitored targets

.

How to copy files from ASM diskgroup to non ASM filesystem (11gR2).

Below is a script to accomplish copying files from ASM to non ASM (on Linux).

Below example is for copying archivelogs from inside ASM to NAS mount.

#!/bin/bash
#
# This script copies files from FRA on ASM to local disk
#
ORACLE_SID=+ASM1
ORACLE_HOME=/opt/grid/11.2.0.2  ##{Grid OH}
ASMLS=/users/apps/oracle/asm_ls.txt ##{ASM files list}
FRA=+FRA/CODREP/ARCHIVELOG/`date +%Y_%m_%d` ##{source location of files}
LOCALBACKUPDIR=/opt/dbadmin/CODREP/ARCHIVE_COPY  ##{destination filesystem}
LOG=/users/apps/oracle/asm_log.txt ##{log file}
#
# Get the list of files
#
$ORACLE_HOME/bin/asmcmd > $ASMLS <<EOF
ls $FRA
exit
EOF
#
# Clean the list by removing "ASMCMD>"
#
sed -i 's/ASMCMD> //g' $ASMLS
##cat $ASMLS
echo `date` > $LOG
#
# Copy files one by one
#

for FILENAME in `cat $ASMLS`
do
if [[ ! -f $LOCALBACKUPDIR/${FILENAME} ]]
then
$ORACLE_HOME/bin/asmcmd >> $LOG <<EOF
cp $FRA/$FILENAME $LOCALBACKUPDIR
EOF
fi
done
echo `date` >> $LOG

Oracle Database Upgrade Steps

Oracle up gradation from oracleOracle 9iR2 (9.2), Oracle 10gR1 (10.1), Oracle 10gR2 (10.2) or Oracle 11gR1 (11.1)  to  Oracle 11gR2 (11.2)

Purpose of the document

The purpose of the document is to describe the steps to upgrade from  oracleOracle 9iR2 (9.2), Oracle 10gR1 (10.1), Oracle 10gR2 (10.2) or Oracle 11gR1 (11.1)  to  Oracle 11gR2 (11.2). (Standalone single instance).The source database taken for reference is oracle 10.2.0.4 on Aix 64 bit power systems.

Oracle 10.2.0.4 Prechecks before upgrade.

Recommendations for Source database

Ensure that all database components/objects provided by Oracle are VALID  in the source database  prior to starting the upgrade

Ensure that you do not have duplicate objects in the SYS and SYSTEM schema.

The following objects are permissible duplicate objects:

OBJECT_NAME OBJECT_TYPE:

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

AQ$_SCHEDULES TABLE

AQ$_SCHEDULES_PRIMARY INDEX

DBMS_REPCAT_AUTH PACKAGE

DBMS_REPCAT_AUTH PACKAGE BODYInstallation Types and Associated Zip Files

CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM(Article NOTE.1030426.6). Note: All these checks are done when (dbupgdiag.sql) executed 

Requirements and recommendations for target database  

Check the certification of Oracle 11gR2 with your Platform/Operating system before downloading and installing Oracle 11gR2. Please check the certification information on My Oracle Support.

• Download and Install Oracle 11g Release 2 in a new Oracle Home and make sure there are no  relinking errors.
• Install the latest available Patchset from Metalink (if available).
• Install the latest opatch available for your platform and database version  (if available).
• Install the latest available Critical Patch Update (if available).
• Either take a Cold or Hot backup of your source database (advisable to have cold backup).
• If you have XDB installed then  please install the PSE for 10368698 to the 11.2.0.2 Home before doing the upgrade . If there is not an existing one-off patch for your platform please open an SR to request the one-off patch. This defect can cause certain databases that are XDB enabled to take a great deal of time to upgrade. The bug 10368698 is fixed in 11.2.0.3 .
• If you have XDB installed then  the installing the fix for Bug 10419629 in the 11.2.0.2.0 home prior to  upgrade . Please refer  Note 1305561.1 While Upgrading From 10.2.0.4.0 To 11.2.0.2.0 Catupgrd.sql=ORA-31061 ORA-19202 LSX-23
• If you are running XDB, you must set SHARED_POOL_SIZE = 250M and JAVA_POOL_SIZE = 250M or higher before upgrading otherwise you may run into the issue described in the following article
• Note 1127179.1 ORA-07445 [qmkmgetConfig()+52] During Catupgrd.sql (11.2.0.1).
• If ASMM is configured on the database, set both parameters as indicated above to guarantee a minimum value for those pools.
• For an awareness of performance-related issues in 11.2.0.2 . Please refer  Note 1320966.1 "Things to Consider Before Upgrade to 11.2.0.2 in Relation to Database Performance"
• For an awareness of SQL profile related known issue , please refer BUG 13646689- SQL PROFILES LOST AFTER UPGRADE ORA-00001 (SYS.I_SQLOBJ$AUXDATA_PKEY) . Currently development is working on this bug . SQL PROFILES will be lost when upgrading from 10.2 releases if  following SQL statement return rows .

select sp.signature, sp.category, count(*) from sqlprof$ sp,sqlprof$desc sd,sql$ s where sp.signature = sd.signature(+) and sp.signature = s.signature group by sp.signature, sp.category having count(*) > 1;

Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

• Log into the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle Home directory.
• Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory on your system.
• For 11.2.0.3 utlu112i_3.sql

  $ORACLE_HOME/rdbms/admin/utlu112i.sql

• Change to the directory where utlu112i.sql had been copied in the previous step.

• Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the source Oracle Home .

sqlplus '/ as sysdba'

SQL> spool upgrade_info.log

SQL> @utlu112i.sql

SQL> spool off

SQL>

Note:- SYSAUX Tablespace

• This section displays the minimum required size for the SYSAUX tablespace which is required in the new Oracle Database 11g release 2 (11.2). The SYSAUX tablespace must be created if it does not exist (in Oracle 9i ) after the new release is started and before the upgrade scripts are invoked.

• If SYSAUX was created in 9i then it must be dropped and re-created after starting in the new release. If created in 10g or later then it can be left there and used.

Check for the integrity of the source database

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

Check for logfiles for any SYS and SYSTEM objects and data dictionary invalidations.

Note:-required only for oracle 9.2.X to 11.2.X

Check for other schema object invalidations and run utlrp.sql.

$ORACLE_HOME/rdbms/admin/utlrp.sql

Deprecated CONNECT Role

After upgrading to Oracle Database 11g Release 2 (11.2) from Oracle database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs   WHERE granted_role = 'CONNECT' and  grantee NOT IN (  'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',  'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',  'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',  'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',  'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',  'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

GRANTEE

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

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT'    ;

GRANTEE                        PRIVILEGE

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

After upgrading give the above privileges have to be granted  to above users except create session.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'

GRANTEE PRIVILEGE
------- ----------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK

From Oracle 10.2, 'CONNECT' role only includes 'CREATE SESSION' privilege.

Check for TIMESTAMP WITH TIMEZONE Datatype.

The RDBMS DST patching has been greatly improved in 11gR2.
Unlike upgrading for older versions (upgrading 10.2.0.4 to 11.1.0.7 for example) there is no need anymore to apply "dst patches" on the old version *before* the upgrade.

If you upgrade from an older RDBMS version to 11gR2 the DST version in 11gR2 after the upgrade will be simply the same as the DST version that was used in the older RDBMS version.

There are however a few situations where some extra steps are needed, so please do check below notes before upgrading to 11gR2., depending on to what 11gR2 version you upgrade to

Note 815679.1 :  Actions For DST Updates When Upgrading To 11.2.0.1 Base Release
Note 1201253.1 : Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset
Note 1358166.1 : Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset
Simply follow above note ( depending on to what 11gR2 version you upgrade to ) in most cases there will be no action to take before the upgrade, but better be safe than sorry.

If the note say's to apply a RDBMS DST patch to the 11gR2 then do so.

Make sure that

SQL> select TZ_VERSION from registry$database;

returns the RDBMS DST version of your old version.( = the value found in step 1 of the above notes)

If this select gives an error or a different value then re-run the utlu112i.sql (Pre-Upgrade Information Tool) script and check again.

Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = NLS_NCHAR_CHARACTERSET';

VALUE

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

AL16UTF16

If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer to the following article:

Note 276914.1 The National Character Set in Oracle 9i and 10g.

Optimizer Statistics

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

EXECUTE dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);                                                                       

EXECUTE dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

PL/SQL procedure successfully completed.

EXECUTE dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

EXECUTE dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

EXECUTE dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE

,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE); 

Disable Oracle Database Vault

When upgrading from Oracle Database release 10.2, if you have enabled the Oracle Database Vault option in your current Oracle Home, then you must disable Oracle Database Vault in the target Oracle Home where the new release 11.2 software is installed before upgrading the database, and enable it again when the upgrade is finished. If Database Vault is enabled, then DBUA will return an error asking you to disable Database Vault prior to upgrade.

You must do this before upgrading the database. Enable Oracle Database Vault again once the upgrade is complete.

Please refer to the following Documentation/Articles for complete information to Disable/Enable Oracle Database Vault.

Disabling and Enabling Oracle Database Vault

OR

You can also refer to the following  documents for Disabling Oracle Database Vault before the upgrade and enabling it after the upgrade.

Note 453903.1  - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1  - Enabling and Disabling Oracle Database Vault in WINDOWS

Backing up Enterprise Manager Database Control Data

After upgrading to Oracle Database 11g release 2 (11.2), if you want to downgrade Oracle Enterprise Manager Database Control you must save your Database Control files and data before upgrading your database. The emdwgrd utility can be used to keep a copy of your database control files and data before upgrading your database. The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 11g release 2 (11.2) home.

1. Set ORACLE_HOME to your old Oracle Home
2. Set ORACLE_SID to the SID of the database being upgraded.
3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
4. Change directory to Oracle Database 11g release 2 (11.2) home.

5. Run the emdwgrd command.

Run the following command for single instance database:

$ emdwgrd -save -sid old_SID -path save_directory

where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data.

@Note 870877.1  How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release ?

Check for logical corruption

To check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):

Set verify off

Set space 0

Set line 120

Set heading off

Set feedback off

Set pages 1000

Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'

FROM dba_clusters

WHERE owner='SYS'

UNION

SELECT 'Analyze table "'||table_name||'" validate structure cascade;'

FROM dba_tables

WHERE owner='SYS'

AND partitioned='NO'

AND (iot_type='IOT' OR iot_type is NULL)

UNION

SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'

FROM dba_tables

WHERE owner='SYS'

AND partitioned='YES';

 spool off

This creates a script called analyze.sql.
Now execute the following steps:

$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

Check for MV refresh

Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.

SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;

Ensure no backup in place for any files.

SELECT * FROM v$recover_file;

select * from v$backup;

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

Password protected roles

In version 11.2 password protected roles are no longer enabled by default, if any of your applications relies on such roles being enabled by default and you take no measures to allow the user to enter the password with the set role command, it is recommended to remove the password from those roles to allow for existing privileges to remain available, for more information see :

Note 745407.1 : What Roles Can Be Set as Default for a User?

Oracle Database Security Guide 10g Release 2 (10.2) Part Number B14266-07

Oracle Database Security Guide 11g Release 1 (11.1) Part Number B28531-15

Oracle Database Security Guide 11g Release 2 (11.2) Part Number E16543-09

 Resolve outstanding distributed transactions prior to the upgrade.

SQL> select * from dba_2pc_pending; 

If this returns rows you should do the following:

SQL> SELECT local_tran_id
     FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

check if a standby database exists

SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

If this query returns a row, then sync the standby database with the primary database.
1. Make sure all the logs are transported to the standby server after a final log switch in the primary.
2. Start the recovery of the standby database with the NODELAY option.

Disable all batch and cron jobs

Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

SQL> SELECT username, default_tablespace  FROM  dba_users       WHERE username in ('SYS','SYSTEM'); 

ALTER USER SYS DEFAULT TABLESPACE SYSTEM;

User altered.

SQL> ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;

User altered.

Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace

SELECT owner,tablespace_name  FROM dba_tables    WHERE table_name='AUD$';

If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS . 

Note: If the AUD$ table exists and is in use, upgrade performance can be effected depending on the number of records in the table.

NOTE:1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later

Check whether database has any externally authenticated SSL users.

SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL    AND password = 'GLOBAL';

Note down the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.

SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;.

Check for V$parameter2.

select NAME,VALUE,ISDEFAULT from v$parameter2 where ISDEFAULT='FALSE';

processes  100 FALSE

timed_statistics  TRUE FALSE

shared_pool_size 218103808 FALSE

shared_pool_reserved_size 15728640 FALSE

java_pool_size 0 FALSE

enqueue_resources 200 FALSE

control_files  /ORACLE/DBATB02/cntrl1DBATB02.dbf FALSE

control_files /usr/oracle/admin/DBATB02/pfile/cntrl2DBATB02.dbf FALSE

db_block_size 8192 FALSE

db_keep_cache_size 318767104 FALSE

 Check Hidden parameters.

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

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

Collect all events if any.

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

Check external authenticated users.

select * from dba_2pc_pending;

SELECT name FROM sys.user$      WHERE ext_username IS NOT NULL      AND password = 'GLOBAL';

 Check for Deprecated and obsolete parameters from the precheck file.( utlu112i.sql).

SHARED_POOL_SIZE and JAVA_POOL_SIZE.

Shared pool size must be minimum 500M and Java pool must be minimum 100M.

Comment the following parameters

parallel_automatic_tuning    10.1     DEPRECATED                                              

--> enqueue_resources            10.2       OBSOLETE                                               

--> cursor_space_for_time        11.1       DEPRECATED                                             

--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"            

--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"            

--> cursor_space_for_time        11.2       DEPRECATED                                             

--> parallel_automatic_tuning    11.2       DEPRECATED.

DB Links

Create script for DBLINK (in case the database has to be downgraded again).

During the upgrade to Oracle Database 11g Release 2 (11.2) from Oracle database 9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)

||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING

'''||L.HOST||''''

||chr(10)||';' TEXT

FROM SYS.LINK$ L, SYS.USER$ U

WHERE L.OWNER# = U.USER#;

Stop the listeners.

Previous versions of the listener are not supported for use with an Oracle Database 11g release 2 (11.2) database. However, it is possible to use the new version of the listener with previous versions of Oracle Databases

Stop other executable such as dbconsole, isqlplus, etc.

$ emctl stop dbconsole

$ isqlplusctl stop

Shutdown the database.

sqlplus "/as sysdba"

SQL> shutdown immediate;

Backup the database.

Take the cold backup of the database.

shutdown immediate;Take cold backup of the database.

Or

Connect to RMAN:

rman "target / nocatalog"

RUN

{

ALLOCATE CHANNEL chan_name TYPE DISK;

BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>';

}

--> backup_directory >> Location of the Database backup.

--> controlfile_backup_directory >> Location of the Controlfile backup.

Make a backup of the init<SID>.ora file.

Comment out obsoleted parameters and change all deprecated parameters. The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST. Oracle recommends increasing the COMPATIBLE parameter only after complete testing of the upgraded database has been performed.

If you are upgrading from 10.1.0.x or 10.2.0.x then you can leave the COMPATIBLE parameter set to it's current value until the upgrade has been completed successfully. This will avoid any unnecessary ORA-942 errors from being reported in SMON trace files during the upgrade (because the upgrade is looking for 10.2 objects that have not yet been created).

Note: Once the parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (database directory on Windows) of 11gR2 Oracle Home.

OS specific tasks to set the environment variables in the new server where oracle 11.2.0.3 is installed.

Windows

1. Stop the Oracle database service.

C:\> NET STOP OracleServiceORCL

2. Delete Oracle service using ORADIM binary from which the database is upgraded to 11.2.

C:\> ORADIM -DELETE -SID ORCL

3. Create the Oracle Database 11g Release 2 (112) service at a command prompt using the ORADIM command of the new Oracle Database release:

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD  -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT<SID>.ORA

For Instance,

C:\> ORADIM -NEW -SID ORCL -INTPWD <PASSWORD> -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT<SID>.ORA. 

Unix:

Set the environment variables,Edit profile file and run it.Attached is the minimum environment variables need to be set.. 

Update oratab entry.

Update the oratab entry to set the new ORACLE_HOME pointing to ORCL and disable automatic startup.Automatic startup has to be enabled after upgrade is completed.

Vi /var/opt—solaris..Vi /etc/oratab---linux.

DBASID:/usr/oracle1123/product/11.2.0/dbhome_1:N----before upgrade.

DBASID:/usr/oracle1123/product/11.2.0/dbhome_1:Y—after upgrade.

Upgrading Database to 11gR2(11.2.0.3).

Change the directory to oracle home-rdbms-admin and run sqlplus and start in upgrade mode.

cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE

Note: If you are upgrading from 9.2 and the SYSAUX table already exists then drop the existing SYSAUX tablespace. And recreate it after database started in upgrade mode.

Start upgrade script.

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.the location of script is $ORACLE_HOME/rdbms/admin

SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters

Post upgrade status checking.

Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.

$ sqlplus "/as sysdba"
SQL> STARTUP

SQL> @utlu112s.sql

Stop upgrade mode and check validations.

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

SQL> @catuppst.sql 

Database  Object validation.

This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

Check for the integrity of the upgraded database by running dbupgdiag.sql.If this gives more errors,run utlrp.sql multiple times.

Post upgrade tasks.

Modify the listener.ora file.

For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener.

lsnrctl start

Check for all the environment variables.

·          Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
- ORACLE_BASE
- ORACLE_HOME
- PATH

• Modify /etc/oratab entry to use automatic startup.

Check the time zone and decide for DST patches.

SELECT version FROM v$timezone_file;

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

·         if this is HIGHER than 11 (for 11.2.0.1 ) or 14 (for 11.2.0.2 and 11.2.0.3) then set it to 14 as follows.

·         exec DBMS_DST.BEGIN_PREPARE(14)

Upgrade Statistics Tables.

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');

Upgrade if any external authenticated users.

Upgrade Externally Authenticated SSL Users.

ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring
<hostname:port_no:sid> --dbuser <db admin> --dbuserpassword
<password> -a

If you are upgrading from 10.2.0.x (or higher), then you are not required to run this command.
Enable Database Vault.

Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS

Grant privileges which was in connect role.

GRANT CREATE VIEW TO DM;

GRANT CREATE TABLE TO DM;

GRANT ALTER SESSION TO DM;

GRANT CREATE CLUSTER TO DM;

GRANT CREATE SESSION TO DM;

GRANT CREATE SYNONYM TO DM;

GRANT CREATE SEQUENCE TO DM;

GRANT CREATE DATABASE LINK TO DM;

List all deprecated parameters.

SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';

A warning message is displayed at instance startup if a deprecated parameter is specified in the parameter file. In addition, all deprecated parameters are logged to the alert log at instance startup

·         Create SP file from Pfile and start the database in SP file.

·         Shut down and take backup.

DB Registery

Query DB registery to ensure oracle 11.2.0.3 is set.