Friday 26 July 2013

Fix-ORA-39014


 

  ORA-39014: One or more workers have prematurely exited.




Solution:

1. If available for your Platform Download Patch 5879865 to resolve this issue. 

2. In the meantime, please use one of the following as a workaround:
* set both of the following in the init/spfile

o "_complex_view_merging" = false
o event="38066 trace name context forever, level 1" or level 2

OR

* set "_optimizer_cost_based_transformation"=off
 
Hope it do wonders !!!!!

Thursday 25 July 2013

Upgrade Oracle Database using DBUA

Upgrade Oracle Database using DBUA

Steps to Upgrade an Oracle database
11.2.0.1.0
11- Is a major database release number
2- Is a database maintenance release number
0- Is a application server release number
1- Is a component specific release number/Patchset number
0- Is a platform specific release number

If you upgrade/migrate the database, the FIRST number and the SECOND number will changes. TheFOURTH digit indicate the patchset number, this number will change whenever you apply the patchsets to the database.
We can use below steps to upgrade from below Oracle versions to a higher Oracle Version (10gr1, 10gr2, 11gr1 and 11gr2)

9.2.0.4 or higher release   -> 10gr1, 10gr2, 11gr1 and 11gr2
 10.1.0.2 or higher            ->   10gr2, 11gr1 and 11gr2
 10.2.0.1 or higher            ->   11gr1 and 11gr2
 11.1.0.6 or higher            ->   11gr2 and 12c

Oracle Upgrade Summary
1)      Must install the new Oracle version in separate ORACLE_HOME to which we are upgrading the database.
2)      Both the old Oracle Version and New/Upgraded Oracle Version software must be installed properly before starting the upgrade.
3)      Take the backup of database before starting the upgrade.
4)      Before starting the upgrade Run the pre upgrade script from new $ORACLE_HOME/rdbms/admin
utlu112i.sql      11.2
utlu111i.sql      11.1
utlu102i.sql      10.2
utlu101i.sql      10.1
5)  Run the database upgrade from New Oracle Home using
DBUA
or
catupgrd.sql  (Manual Upgrade )
6)      Once the upgrade is complete validate the upgrade using Post Upgrade Script  from new ORACLE_HOME/rdbms/admin/
utlu112s.sql      11.2
utlu111s.sql      11.1
utlu102s.sql      10.2
utlu101s.sql      10.1

Oracle Upgrade Detailed Steps
Note: The steps in this document can be used to upgrade a 9i/10gr1/10gr2/11gr1 to 10gr1/10gr2/11gr1/11gr2.
In this document we are upgrading from 11gr1 to 11gr2 so both software must be installed and 11gr1 database with ORACLE_SID=orcl must be present before we start the upgrade.
Our Example will use
11gr1 ORACLE_HOME=/u01/app/oracle/product/11g
11gr1 ORACLE_SID=orcl
11gr2 ORACLE_HOME=/u01/app/oracle/product/11gr2

Steps
1) Shutdown the database
2) Take a comple backup of datafiles,controlfiles, redo logfiles, Spfile/pfile and password file.The complete backup of database is needed to revert back if the Database upgrade fails.

A) Pre upgrade Steps
Copy the preupgrade script to any temp location for example /u01/app/oracle
cp /u01/app/oracle/product/11gr2/rdbms/admin/utlu112i.sql /u01/app/oracle
cd /u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11g
export ORACLE_SID=orcl
If you are upgrading to 11gr2 run below script
@utlu112i.sql
If you are upgrading to 11gr1 run below script
@ utlu111i.sql
If you are upgrading to 10gr2 run below script
@ utlu102i.sql

Below we are spooling to a log file so that we have the pre upgrade validations stored in upgrade_info.log file that we can refer later.
sql>      spool upgrade_info.log
sql>      @utlu112i.sql
sql>      spool off
The Pre upgrade script suggest any database changes that needs to be done before starting the Database Upgrade using DBUA or catupgrd.sql Script.
The pre upgrade script validates following information:
  • Database version.
  • Tablespace sizes.
  • Updated, renamed and deprecated initialization parameters.
  • Init.ora or Spfile Parameters that needs to be resize before starting the actual upgrade. ( This step should not be ignored)
  • Database Components that will be upgraded or installed
  • SYSAUX tablespace present (if missing).
  • Miscellaneous Warnings
    • Warning for Old Timezone
    • Warning for Stale Optimizer Statistics
    • Warning for EM Database Control Repository
Note: Each of the warning suggested by Pre Upgrade tool must be fixed.
Fix all the issues reported by Pre-upgrade script before starting DBUA

B) Upgrade Steps
(Two Methods to do the actual Database Upgrade, either use DBUA
or catupgrd.sql (manual method)  which has many steps but it is more flexible)
Oracle recommends using DBUA to upgrade database.
1. Login in as Oracle 11g user, and initiate all the variables need for the environment
$ export ORACLE_BASE=/u01/app/o11g
$ export ORACLE_HOME=/u01/app/o11g/product/11.1.0/db_1
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export PATH=$ORACLE_HOME/bin:$PATH
2. Confirm the Oracle 10g database name and ORACLE_HOME path is entered in /etc/oratab file
$ cat /etc/oratab
orcl:/u01/app/o10g/product/10.2.0/db_1:N
3. Execute the dbca utility from Oracle 11g’s path
$ $ORACLE_HOME/bin/dbua
export ORACLE_SID=orcl
Run the DBUA from New 11gr2 ORACLE_HOME/bin
$ cd /u01/app/oracle/product/11gr2/bin
$./dbua
Once the DBUA (Database Upgrade Assistant) GUI starts. Follow below points to upgrade the database. DBUA takes about 60 minutes to complete even for very large databases.
  • Select the Database that needs to be upgraded.
  • If the database name that needs to be upgraded is not present in DBUA,
Add the entry $ORACLE_SID:$ORACLE_HOME:N to /etc/oratab
For Example: ora11g:/u01/app/oracle/product/orcl:N
Note: The $ORACLE_HOME should be the old oracle home that is getting  upgraded.
  • Oracle 11g requires the “Diagnostic Destination”. Set diagnostic destination to oracle base.
  • Select “Do not move Database as part of upgrade”
  • Do not specify Flash Recovery Area option
  • Select “Configure database with Enterprise Manager” or Database Control if required.
  • Select “Recompile Invalid objects at the end of upgrade” options in dbua
  • Do not select the backup option if we have already have taken backup before starting Database Upgrade, Also it is not needed if we already have RMAN backup.
C) Post Upgrade Steps
Once the upgrade is complete modify the profile file to use the new ORACLE_HOME
Run the post upgrade script to validate the upgrade using new ORACLE_HOME
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @/u01/app/oracle/product/11gr2/rdbms/admin/utlu112s.sql

This completes the Oracle upgrade from 11gr1 to 11gr2 for orcl database!!!!
———————————————————————————-

Miscellaneous Commands that may be required before running DBUA
Sometimes the Pre Upgrade tool (utlu112i.sql) complains that some pre steps needs to be done before starting the Database Upgrade. Few of these Miscellaneous Warnings can be fixed by using below commands:

SQL> SHOW PARAMETER JAVA_POOL_SIZE
ALTER SYSTEM SET JAVA_POOL_SIZE=200M sid=’orcl’;
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> PURGE DBA_RECYCLEBIN;

SQL>
exec dbms_stats.gather_schema_stats(‘SYS‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
SQL>
exec dbms_stats.gather_schema_stats(‘WKSYS‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
SQL>
exec dbms_stats.gather_schema_stats(‘SYSMAN‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
SQL>
exec dbms_stats.gather_schema_stats(‘OLAPSYS‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);

Monday 22 July 2013

FIX- ORA-03113: end-of-file on communication channel


Process ID: 12400
Session ID: 96 Serial number: 3

Solution:

1.Check your alert log file

One of the suggested action is to look in the alert.log file.  To find the location of alert.log, you do:
  1. Find the location of diagnostic destination from the initialization parameter file (i.e., dbs/init<sid>.ora ).  For example, we have this entry:
    • diagnostic_dest=/slot/fiz7865/log

  2. From there, you can find alert.log file in the following sub directory:
    • <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

In the alert_<sid>.log, we have found the following messages:

-rw-r----- 1 oracle oinstall 178164407 Jul 22 15:51 alert_app.log

[oracle@exilant trace]$ tail -200 alert_app.log

Output of alert log file:

Errors in file /opt/app/oracle/diag/rdbms/apple/apple/trace/apple_arc3_3238.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 18179072 bytes disk space from 4070572032 limit
ARC3: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/APPLE/archivelog/2013_03_10/o1_mf_1_615_%u_.arc'
Sun Mar 10 16:05:32 2013
Errors in file /opt/app/oracle/diag/rdbms/apple/apple/trace/apple_arc0_3201.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/apple/apple/trace/apple_arc0_3201.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 18179072 bytes disk space from 4070572032 limit
ARC0: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/APPLE/archivelog

2. Go to SQLPLUS

         SQL> startup                                         — Failed to startup, the same ORA-03113 Error
 SQL> startup nomount                          — ORACLE Instance Started
 SQL> alter database mount                   — Database altered
 SQL> exit
 $ rman target /
 RMAN> crosscheck archivelog all      — Here you will see all the names of archivelogs still exist
 RMAN> delete expired archivelog all
 RMAN> exit
 $ sqlplus / as sysdba
 SQL> alter database open                    — Database altered
$ ps -ef | grep pmon
oracle   12734     1  0 16:10 ?        00:00:00 ora_pmon_app
oracle   12880 12257  0 16:12 pts/5    00:00:00 grep pmon