Metrics Global Cache Blocks lost is at 11

Undesirable Global Cache Statistics
The following are undesirable statistics, or statistics for which the values should always be zero or near-zero.

global cache blocks lost--

This statistic reveals any block losses during transfers and high values may indicate network problems. When using an unreliable IPC protocol such as UDP, the value for 'global cache blocks lost' may be non-zero. If this is the case, then the ratio of 'global cache blocks lost' divided by 'global cache current blocks served' plus 'global cache cr blocks served' should be as small as possible. A non-zero value for 'global cache blocks lost' does not necessarily indicate a problem, because Oracle retries the block transfer operation until it is successful. Unlike TCP/IP, UDP/IP is considered unreliable because UDP/IP provides very few error recovery services.

global cache blocks corrupt--

This statistic indicates whether any blocks were corrupted during transfers. High values for this statistic indicate an IPC, network, or hardware problem. "

Getting "Reached maximum capacity of pool" messages in logs

Issue: Unable to login to Oracle VM Manager. Getting the following error:

Error in the Admin Server Log:

####<2015-02-28T08:52:55.833+0530> <Info> <Common> <servername> <AdminServer> <Odof Tcp Client Thread: /127.x.x.x:54321/2795858> <<anonymous>> <> <1b95d8d1-24dd-4acc-a6f3-4c025158e6a7-000000
03> <1425093775833> <BEA-000627> <Reached maximum capacity of pool "poolname", making "0" new resource instances instead of "1".>
####<2015-02-28T08:52:55.896+0530> <Error> <com.oracle.odof.core.storage.RelationalStore> <servername> <AdminServer> <Odof Tcp Client Thread: /127.x.x.x:54321/2795865> <<anonymous>> <> <1b95
d8d1-24dd-4acc-a6f3-4c025158e6a7-00000003> <1425093775896> <BEA-000000> <Waiting for connection (7 of 720 tries)>
####<2015-02-28T08:52:58.446+0530> <Info> <Common> <servername> <AdminServer> <Odof Tcp Client Thread: /127.x.x.x:54321/2795898> <<anonymous>> <> <1b95d8d1-24dd-4acc-a6f3-4c025158e6a7-000000
03> <1425093778446> <BEA-000627> <Reached maximum capacity of pool "poolname", making "0" new resource instances instead of "1".>
####<2015-02-28T08:52:58.469+0530> <Error> <com.oracle.ovm.mgr.api.exception.FormatException> <servername> <AdminServer> <[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (sel
f-tuning)'> <<anonymous>> <> <1b95d8d1-24dd-4acc-a6f3-4c025158e6a7-00293343> <1425093778469> <BEA-000000> <com.oracle.odof.exception.TimeoutException: Receive timed out - timeout value(30000)

Solution:

1. Log into the Weblogic console: https://:7002/console. The username should be 'weblogic' and the password should be the same one used to log into the manager.

2. Go to Services --> Data Sources --> ovm-odof-ds --> Monitoring. Click on "Customize this table." Add the following columns to the table: 'Leaked Connection Count', 'Active Connections Current Count', 'Active Connections High Count'.
3. Go to Services --> Data Sources --> ovm-odof-ds --> Connection Pool --> Advanced. Set the "Inactive Connection Timeout" field to 30, and save.

4. Go to Services --> Data Sources --> ovm-odof-ds --> Diagnostics. Check "Profile Connection Leak" and save.

5. Click on "Activate Changes" on the left so that all the config changes take effect.

6. Continue using the manager. Now, weblogic should close any inactive connections that are still open.

7. Go to Diagnostics --> Log Files. View the DataSource log. Information here could help us figure out if the manager is leaving connections open.

Also, as a precaution and to avoid this issue in future, you can increase the maximum capacity of connections for the pool. To do that, follow steps below:

a. Login to Weblogic console
b. Navigate to Domain Structure -> Services -> Data Sources -> -> Connection Pool >> Maximum Capacity
c. Increase the parameter "Maximum Capacity" of the connection pool (If it is 1, increase to 100)

Exception :: ORA-00904: "NEED_BAL_FLAG": invalid identifier

Issue: Issue occurred while running the 9651687 fix script.

SQL> @xla_mulent_fix.sql;
Exception occured :: ORA-20001:
DECLARE
*
ERROR at line 1:
ORA-20001:
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at line 468

Log Details

Exception :: ORA-00904: "NEED_BAL_FLAG": invalid identifier
in side Print1
Exception occured When Creating xla_ae_headers Backup Table :: ORA-20001:
Exception occured :: ORA-20001: 

Cause:

The backup table is not having the column NEED_BAL_FLAG

Solution:

ALTER TABLE XAH_9651687_BKP ADD(NEED_BAL_FLAG VARCHAR2(1));


SQL> select column_name from ALL_TAB_COLUMNS where table_name='XAH_9651687_BKP';

COLUMN_NAME
------------------------------
NEED_BAL_FLAG
CLOSE_ACCT_SEQ_VERSION_ID
COMPLETION_ACCT_SEQ_VALUE


.....

If the column appears in the output then run the fix script it should complete now.

SQL> @xla_mulent_fix.sql;
-------------------------------------------------------------------------------
Please Note the Following information. Kindly open the Out file to proceed
further
Out file Location would be  /usr/tmp/9651687-fix-15:23:56.html
Log file Location would be  /usr/tmp/9651687-fix.log
-------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL> exit

You have insufficient privileges for the current operation. Please contact your System Administrator

Issue: "You have insufficient privileges for the current operation. Please contact your System Administrator" ,the URL changed from https (SSL) to http (non-SSL) . 

Cause: The issue occurred after configuring the SSL on R12 applications, this caused in the WORKFLOW notification approvals, whenever they click the approve button in the notification the issue occurred,

-in the $CONTEXT_FILE we did not enable the flag for ssl terminated environments (ie. SSL is enabled on the load balancer/SSL accelerator): 
<sslterminator oa_var="s_enable_sslterminator">#</sslterminator> 


Solution:

As per the document for SSL : 

Enabling SSL or TLS in Oracle E-Business Suite Release 12 
( Doc ID 376700.1 ) --------->Section 3--->step 8->table "Changes when using an SSL Accelerator" 

1. Check the httpd.conf to confirm that ssl_terminator.conf is included as this is required for SSL Accelerators. 

grep ssl_terminator.conf $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf 

2. Check that the "s_enable_sslterminator" autoconfig parameter is not disable with #. 

grep s_enable_sslterminator $INST_TOP/appl/admin/$CONTEXT_FILE 

If this shows # as value, it must be removed and Autoconfig must be run to update the httpd.conf to include the ssl_terminator.conf. 

3. start the applications so the changes to the httpd.conf is seen by the web server. 

ORA-39213: Metadata processing is not available

Issue: Export: Release 11.2.0.3.0 - Production on Wed Sep 30 19:44:23 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

Solution:

Verify if all the components in the database are valid.

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID                        VERSION                        STATUS

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

OWB                            11.2.0.1.0                     VALID

APEX                           3.2.1.00.10                    VALID

EM                             11.2.0.3.0                     VALID

AMD                            11.2.0.3.0                     VALID

SDO                            11.2.0.3.0                     VALID

ORDIM                          11.2.0.3.0                     VALID

XDB                            11.2.0.3.0                     VALID

CONTEXT                        11.2.0.3.0                     VALID

EXF                            11.2.0.3.0                     VALID

RUL                            11.2.0.3.0                     VALID

OWM                            11.2.0.3.0                     VALID

CATALOG                        11.2.0.3.0                     VALID

CATPROC                        11.2.0.3.0                     VALID

JAVAVM                         11.2.0.3.0                     VALID

XML                            11.2.0.3.0                     VALID

CATJAVA                        11.2.0.3.0                     VALID

APS                            11.2.0.3.0                     VALID

XOQ                            11.2.0.3.0                     VALID

Run the below statement and retest the issue.

SQL> execute dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

SQL> exit

 expdp parfile=expdp_cltp_tables.par

Export: Release 11.2.0.3.0 - Production on Wed Sep 30 19:47:32 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."CLTB_TABLES_EXP":  /******** AS SYSDBA parfile=expdp_cltp_tables.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 3.375 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "GFCPROD"."CLTB_EVENT_ENTRIES":"SYS_P369"   1.278 MB    4788 rows

. . exported "GFCPROD"."CLTP_ACCOUNT_SCHEDULES":"SYS_P378"  327.8 KB    2383 rows

. . exported "GFCPROD"."CLTB_ACCOUNT_APPS_MASTER":"FUNDED"  296.3 KB     281 rows

. . exported "GFCPROD"."CLTP_ACCOUNT_COMP_BALANCES":"SYS_P371"  53.75 KB     785 rows

. . exported "GFCPROD"."CLTP_ACCOUNT_COMP_SCH":"SYS_P374"  63.08 KB     418 rows

. . exported "GFCPROD"."CLTP_ACCOUNT_COMP_SCH":"SYS_P375"  61.03 KB     398 rows

The issue should resolve.

ORA-39065: unexpected master process exception in DISPATCH

Issue while running the export command:

Export: Release 11.2.0.3.0 - Production on Wed Sep 30 19:34:01 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-00600: internal error code, arguments: [kokle_lob2lob13:input mismatch], [1], [], [], [], [], [], [], [], [], [], []

ORA-39097: Data Pump job encountered unexpected error -600

Cause: JOB_QUEUE_PROCESSES was  set to 0

show parameter JOB_QUEUE_PROCESSES

job_queue_processes                  integer

0

Solution: Modified the parameter and restarted the database.

alter system set JOB_QUEUE_PROCESSES=2 scope=spfile;

show parameter JOB_QUEUE_PROCESSES

job_queue_processes                  integer

2

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 4710043648 bytes

Fixed Size                  2236248 bytes

Variable Size            3758096552 bytes

Database Buffers          838860800 bytes

Redo Buffers              110850048 bytes

Database mounted.

Database opened.

SQL> 

Retest the issue it should resolve the expdp error.

Delete Histograms on a specific Schema

Step 1: Script to Check Histograms on Schema 

Following script would have to be executed in the schema as the schema owner.

select distinct table_name

from

(

select table_name from user_tab_columns where histogram!='NONE'

)

If the above script return rows, then proceed to run the below step 2.

Step 2: Script to Remove Histograms on a Schema 

Following script would have to be executed in the schema as the schema owner, if there are any rows returned from the step 1.

declare

cursor cur_tables is

select distinct table_name

from

(

select table_name from user_tab_columns where histogram!='NONE'

);

begin

for rec_tables in cur_tables

loop

dbms_stats.gather_table_stats(ownname=>'<SCHEMA_OWNER>',tabname=>rec_tables.table_name,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',CASCADE=>TRUE,DEGREE=>2,ESTIMATE_PERCENT=>NULL);

end loop;

end; 

Spool SqlPlus Output to Microsoft Excel Format

SET PAGESIZE 50000

SET FEEDBACK OFF

SET MARKUP HTML ON SPOOL ON

SET NUM 24

SPOOL example.xls

Run the query.

SPOOL OFF

SET MARKUP HTML OFF SPOOL OFF

Example:

SET PAGESIZE 50000

SET FEED OFF MARKUP HTML ON SPOOL ON

SET NUM 24

SPOOL online_accouting.xls

select * from fnd_log_messages where log_sequence between 35644336 and 35644404;

SPOOL OFF

SET MARKUP HTML OFF SPOOL OFF

Moving Spfile From File-System To ASM

1. Create spfile in ASM "+DATA" disk group :

SQL> connect / as sys

SQL> show parameter spfile
 
NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora
 
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';

SQL> create pfile from spfile
File created.
 
SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.

SQL> exit

2. Modify inittestpk1.ora on rac1 and inittestpk2.ora on rac2 files to point to location in ASM

[oracle@rac1 dbs]$ echo "SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora
[oracle@rac1 dbs]$ ssh rac2 "echo \"SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'\" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb12.ora"
 

3. Update OCR with new SPFILE location

[oracle@rac1 dbs]$ srvctl modify database -d racdb1 -p +RACDB_DATA/racdb1/spfileracdb1.ora 

4. Rename any existing spfiles in $ORACLE_HOME/dbs

5. Restart all instances to switch to new SPFILE 

6. Check New Location For Spfile .

How to drop and recreate a Corrupt Index

1. Verify the error.

2. Try to rebuild the index as below.

alter index <index name> rebuild;

If the still the issue exists.

3. Query for the Index script from dbms_metadata.get_ddl as below.

select dbms_metadata.get_ddl('INDEX','<INDEX_NAME>','OWNER') from dual;

DBMS_METADATA.GET_DDL('INDEX','PO_VENDORS_F1','PO')

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

  CREATE INDEX "OWNER"."<INDEX_NAME>" ON "OWNER"."<INDEX_NAME>" (NVL("END_DATE_ACTI

VE",TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "APPS_TS_TX_IDX"

4. Use the above script to recreate the index.

Script to find the Usage of the Responisibilities and the Porduct License details in Oracle Apps R12 or 11i

Script to check the last login to the Reponsibilities by the users
==============================================

select max(flr.start_time), flr.responsibility_id ,fu.user_name, 
flt.responsibility_name 
from fnd_logins fl, fnd_login_Responsibilities flr, fnd_user fu , 
fnd_responsibility_tl flt 
where flr.login_id = fl.login_id 
and flt.responsibility_id = flr.responsibility_id 
and fu.user_id = fl.user_id 
group by fl.user_id, flr.RESPONSIBILITY_ID ,fu.user_name, 
flt.responsibility_name 
order by fl.user_id

Script to check the Products Installed and Shared in Oracle Apps
================================================

select a.application_short_name,a.APPLICATION_NAME,decode(fpi.status,'I', 'INSTALLED','S', 'SHARED','N', 'Inactive', 'N/A') status from apps.fnd_application_vl a, apps.fnd_product_installations fpi where fpi.application_id = a.application_id and fpi.status in ('I','S') order by 3,1;

SELECT a.application_name,DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id and PATCH_LEVEL like '%&1%';

Script to Delete Applied archive logs on the Physical Standby Database

###################### START ########################

#!/bin/bash
drhost=`GFCDROBIEEDB1`
tstamp=`date +"%c"`
email="ravi@ecsme.ae"
myLog="/home/oracle/rmanDelStandbyArclogs.log"

if [ "$1" == "" ]; then
    echo "Error: Improper arguments"
    echo ""
    echo "Correct format"
    echo "$0 ORACLE_SID"
    exit -1
fi

export ORACLE_SID="$1"
export ORACLE_HOME="/swapphome/oracle/app/product/11.2.0/db_2"
export PATH="$ORACLE_HOME/bin:$PATH"

sequence=`sqlplus -s '/as sysdba' << EOF
set head off
set feed off
set echo off
select max(sequence#) from v\\\$archived_log where applied='YES' group by applied;

exit;
EOF`

 if [ `echo $sequence | grep ORA- | wc -l` -ge "1" ]; then
     echo "Error: [$sequence]"
     echo $sequence | mailx -s "[$tstamp] Error: Removing applied standby archived logs from standby database [$1] on host [$drhost]" $email
     exit -1
 fi

sequence=`echo "$sequence" |  tr -dc '[:alnum:]'`

rmanCmd="delete noprompt archivelog until sequence $sequence"

rmanDel=$(rman target / append log ${myLog} 2>&1 << END_OF_RMAN
$rmanCmd;
exit;
END_OF_RMAN
)

if [ `echo $rmanDel | grep RMAN- | wc -l` -ge "1" ]; then
     echo "Error: [$rmanDel]"
     echo $rmanDel | mailx -s "[$tstamp] Error: RMAN error while deleting applied archive logs from standby database [$1] on host [$drhost]" $email
     exit -1
 fi

exit 0
############################### END #############################

ORA-600[2619] During Physical Standby Recovery

Issue:
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.

Media Recovery Log /swapphome/oracle/app/oradata/obieeproddb/archives/1_7011_876410455.dbf

Errors in file /swapphome/oracle/app/diag/rdbms/obieestbydb/obieestbydb/trace/obieestbydb_pr00_27961.trc:

ORA-00600: internal error code, arguments: [2619], [7011], [], [], [], [], [], [], [], [], [], []
Slave exiting with ORA-600 exception
Errors in file /swapphome/oracle/app/diag/rdbms/obieestbydb/obieestbydb/trace/obieestbydb_pr00_27961.trc:
ORA-00600: internal error code, arguments: [2619], [7011], [], [], [], [], [], [], [], [], [], []
Sun Aug 09 17:11:31 2015
Dumping diagnostic data in directory=[cdmp_20150809171131], requested by (instance=1, osid=27961 (PR00)), summary=[incident=6283].
Errors in file /swapphome/oracle/app/diag/rdbms/obieestbydb/obieestbydb/trace/obieestbydb_mrp0_27959.trc  (incident=6274):
ORA-00600: internal error code, arguments: [2619], [7011], [], [], [], [], [], [], [], [], [], []
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave

Cause while applying the above log the mount point got 100% occupied no space left in the mount point.

RFS[11]: Opened log for thread 1 sequence 7009 dbid 912770197 branch 876410455

Non critical error ORA-48181 caught while writing to trace file "/swapphome/oracle/app/diag/rdbms/obieestbydb/obieestbydb/trace/obieestbydb_rfs_32359.trc"

Error message: Linux-x86_64 Error: 28: No space left on device

Additional information: 1

Writing to the above trace file is disabled for now on...

RFS[10]: Possible network disconnect with primary database

RFS[11]: Possible network disconnect with primary database

Solution:

1. Clear the disk space where archive log stored on standby site

2. Copy the problem archive log (eg: 1_7011_876410455.dbf) from the primary site and replace the one on the standby, then restart Managed Recovery.

Archive log should be applied properly now.

DBCA fails with ORA-27102

Issue: ORA-27102 Out of Memory messages 

Below errors found in the trace files. 

Fatal Error: ORA-01092 
Fatal Error: ORA-01034 
Fatal Error: ORA-03114 
Fatal Error: ORA-12560 

ORA-27102 Out of Memory messages 

After installing the 11.2.0.1 software binaries, dbca was throwing the ORA-27102.

Resources:

RAM - 128:
AMM - enabled

Observation: 

1. Installations of 11.2.0.1 binaries was successful on windows 2008 64bit.

2. While running the DBCA it hangs at 2% and then we see in the task manager the process which is running dbca consumes 100% memory and once it reaches 100% we get the below error

3. The process hangs at the RMAN Clone process in the installation.

ORA-27102 Out of Memory messages 

Cause: This is due to the bug in 11.2.0.1 version.


Solution:

1. Deinstall the 11.2.0.1 software binaries.

2. Install the 11.2.0.4 binaries.

3. Rerun the DBCA it should complete successfully.

EBS R12 Migration from Linux 32 bit OS to 64 bit

Below are the Metalink documents to refer for Migration.

Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit [ID 471566.1]
Adconfig.Sh appsTier error: afcpnode.sh INSTE8_SETUP 1 [ID 1271670.1]
What Are The Requirements For E-Business Suite When Upgrading Operating Systems? [ID 1061303.1]
R12: "error while loadingSHARED libraries: libc.so.6" Error When Migrating From Linux 32bit To Linux 64bit [ID 1473917.1]

Working on detailed steps.................

CVU report PRVG-1533 while upgrade from 11.2.0.3 to 11.2.0.4

Issue:

While running runcluvfy (from 11.2.0.4 Binary location) to check upgrade readiness from 11.2.0.2 to 11.2.0.4, gives below PRVG-1533 error

runcluvfy.sh stage -pre crsinst -upgrade -n racnode1,racnode2,racnode3 -rolling -src_crshome /oragrid/product/11.2.0.3 -dest_crshome /oragrid/product/11.2.0.4/gi_1 -dest_version 11.2.0.4.0 -fixup -fixupdir /tmp -verbose

ERROR:
PRVG-1533 : The IPMP interfaces "bxge0,nxge0" classified as public network do not belong to the subnet "10.20.31.1" on node "racnode1"

Cause:

This was due to bug Bug 17333157

Solution:

Verify If IPMP group configuration shows consistent when runcluvfy is executed from 11.2.0.3 GI home.

Then you can  ignore the error and proceed with upgrade.

rootcrs.pl -patch Failed

rootcrs.pl -patch Failed after applying the patch 12539000

Using configuration parameter file: ./crsconfig_params
Undefined subroutine &main::read_file called at crspatch.pm line 86.

Workaround.

Its a Bug below is the workaround –

Undefined subroutine &main::read_file called at /u01/oracle/product/11.2.0/grid/crs/install/crspatch.pm line 86.

 Take a backup of the file <GridHome>/crs/install/crsconfig_lib.pm

cd /u01/oracle/product/11.2.0/grid/crs/install

 cp crsconfig_lib.pm crsconfig_lib.pm.bak

Make the following change in that file crsconfig_lib.pm

From
     my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR
To
     my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR read_file

Now lock and relink the GI HOME as root user….

root@ # diff crsconfig_lib.pm crsconfig_lib.pm_bkp

700c700

<   my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR read_file

---

>   my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR

root@ # perl rootcrs.pl -patch

Using configuration parameter file: ./crsconfig_params

ACFS-9200: Supported

CRS-4123: Oracle High Availability Services has been started.

root@ #

Manual Database upgrade from 11.2.0.2 to 11.2.0.4

1. Install the 11.2.0.4 in a new Home Location using runInstaller

Ex:
Unzip the patch 13***
$/u01/app/oracle/11.2.0.4

2. Copy Pre upgrade Information gathering Script:

    Log in to the system as the owner of the Oracle Database 11.2.0.4 Oracle home directory.
    
Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11.2.0.4 $ORACLE_HOME/rdbms/admin 

directory to a directory outside of the Oracle home, such as the temporary directory on your system.

$ORACLE_HOME/rdbms/admin/utlu112i.sql

3. Execute Pre Upgrade Script:

Should be 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>

Check the spool file and examine the output of the upgrade information tool.

 3. Check for the integrity of the source database:

Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from below My Oracle Support article

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Avoid this step if don’t have support access)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in thenumber of invalid objects.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

4. Check that National Characterset:

Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

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

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

5. 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

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

6. Ensure that no files need media recovery:

Sql> SELECT * FROM v$recover_file;

7. Stop the listener for the database:

Make pfile from spfile;

$ lsnrctl stop

8. Shutdown the Database:

Shutdown the database.

$ sqlplus "/as sysdba"
SQL> shutdown immediate;

9. Back Up the Database:

1- Perform Cold Backup
(or)
2- Take a backup using RMAN

Connect to RMAN:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}

10. Backup and change pfile:

Make a backup of the init.ora file.
Comment out obsolete parameters

* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.

Note: Once the Parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (11g Oracle Home )

11. Set Environment Variables:

If your operating system is UNIX then complete this step, else skip to next Step.

1. Make sure the following environment variables point to the Oracle database software 11g Release directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH

$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=

Note : If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.

$ orabase
/uo1/app/oracle

2. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup

Sample /etc/oratab

#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N

Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.

for Instance,

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost ~]$

12. Upgrade Database:

At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.

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

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.

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.

13. Post Upgrade:

Start the database and 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

14. Recompile Invalid Objects:

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

15. Check for the integrity of the source database:

Check for the integrity of the upgraded database by running dbupgdiag.sql script from below Metalink article

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

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in thenumber of invalid objects.

After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

16. Configure and Start Listener.ora :

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

17. Crosscheck Environment Variables:

Set Environment Variables

1. Make sure the following environment variables point to the Oracle 11g Release directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH

Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle database software11g Release 2 (11.2) home.

Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

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

SID:ORACLE_HOME:Y

For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y

18. Spfile from Pfile:

 Edit init.ora:

- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your initialization parameter file to a server parameter file.

Create a server parameter file with a initialization parameter file

SQL> create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).

19. Start the Database with spfile:

Shutdown the database:

Sql> shutdown immediate

Now start the database it will user spfile

Sql> startup

Check the Alert log file for any Error.

Database is ready to use now with Database Software Oracle 11g.

20. Validate the database components as below.

23:07:25 SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

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

TEST      READ WRITE

Oracle Database 11.2 Post-Upgrade Status Tool           07-02-2015 22:46:05

23:02:08 SQL> /

COMP_NAME                                VERSION                        STATUS

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

Oracle Enterprise Manager                11.2.0.4.0                     VALID

OLAP Catalog                             11.2.0.4.0                     VALID

Spatial                                  11.2.0.4.0                     VALID

Oracle Multimedia                        11.2.0.4.0                     VALID

Oracle XML Database                      11.2.0.4.0                     VALID

Oracle Text                              11.2.0.4.0                     VALID

Oracle Expression Filter                 11.2.0.4.0                     VALID

Oracle Rules Manager                     11.2.0.4.0                     VALID

Oracle Workspace Manager                 11.2.0.4.0                     VALID

Oracle Database Catalog Views            11.2.0.4.0                     VALID

Oracle Database Packages and Types       11.2.0.4.0                     VALID

JServer JAVA Virtual Machine             11.2.0.4.0                     VALID

Oracle XDK                               11.2.0.4.0                     VALID

Oracle Database Java Packages            11.2.0.4.0                     VALID

OLAP Analytic Workspace                  11.2.0.4.0                     VALID

Oracle OLAP API                          11.2.0.4.0                     VALID

Oracle Real Application Clusters         11.2.0.4.0                     INVALID

Total Upgrade Time: 02:03:31

23:07:12 SQL> SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID' GROUP by object_type;

OBJECT_TYPE                                                                  COUNT(*)

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

VIEW                                                                                2

TRIGGER                                                                             1

SYNONYM                                                                             1

PACKAGE BODY                                                                        6