java.sql.SQLException: ORA-28040: No matching authentication protocol

When connecting to 10g ,11g or 12c databases using a  JDBC thin driver , fails with following errors:

The Network Adapter could not establish the connection

ORA-28040: No matching authentication protocol

This happens because you are using a non compatible version of JDBC driver.

To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions.


Oracle Client/Server Compatibility Matrix: 

Oracle Client Version Database Version 12.2.0 12.1.0 11.2.0 11.1.0 10.2.0 10.1.0 9.2.0 12.2.0 Yes Yes Yes No No No No 12.1.0 Yes Yes Yes Was MDS No No 11.2.0 Yes Yes Yes Was MDS No Was 11.1.0 No Was Was Was Was Was Was 10.2.0 No MDS MDS  Was MDS Was Was 10.1.0 No No Was  Was Was Was Was 9.2.0 No No Was  Was Was Was Was Key: Yes Supported ES Supported but fixes only possible for customers with Extended Support . LES or MDS Supported but fixes only possible for customer with a valid Limited Extended Support or Market-Driven Support contract respectively. Was Was a supported combination but one of the releases is no longer covered by any of Premier Support, Primary Error Correct support, Extended Support, nor Extended Maintenance Support. Fixes are no longer possible. No Has never been Supported Courtesy: My Oracle Support

OBIEE 11g Graphs Do not display after applying patch on 11.1.1.6.x

I have applied patch bundle 11.1.1.6.5 on my OBIEE 11.1.1.6.0. After applying the patch, I have noticed that none of my graphs/charts are displayed on the browser. I have deleted the browser cache and cookies, but still not displaying.

After referring the oracle support, I have did the following to resolve the issue.

We have customization for custom skins and styles in custom directory, so we did the below section first.

Section 1 Copy BIChart.swf  file to custom directory

1. Copied the BIChart.swf file from ORACLE_BI_HOME'bifoundation\web\app\res\sk_blafp\dvt directory to our custom directory

Custom directory located at <MW_HOME>/Oracle_BI/bifoundation/OracleBIPresentationServiceComponent/coreapplication_obips1/analyticsRes/<custom_dir>/dvt

2. Stop and Restart the BI Presentation Service components

cd <INSTANCE_HOME>/bin
opmnctl stopproc ias-component=coreapplication_obips1

opmnctl startproc ias-component=coreapplication_obips1

Section 2 Clear the managed server temporary files

1. Stop the Services
2. backup $DOMAIN_HOME/servers/bi_server1/tmp
3. cd $DOMAIN_HOME/servers/bi_server1/tmp
4. rm -rf * (including the hidden .internal directory)
5. Start the services

Now, open the browser and clear the cache and cookies.
Login to the analytics dashboard. If graphs/charts not visible, refresh the page by pressing ctrl+F5 (On Internet Explorer)

This should resolve the issue.

R12 Error: Application Diagnostics is not a valid responsibility for the current user. Please contact your System Administrator.

User Must have one of the below roles to be assigned to run Oracle Diagnostics.

• Diagnostics Super user
• Application Super user
• Application End User

To Assign role:

1. Login to E-Business Suite as System Administrator
2. Select the "User Management" responsibility
3. Click 'Users' in the right hand side menu
4. Enter 'User Name' and click 'Go'
5. Click 'Update' on the correct user
6. Click 'Assign Roles' button
7. Enter role name (or partial with %) you want to assign. You can enter any of the seeded roles (Diagnostics Super User Role, Application Super User Role, Application End User Role), or your own one
8. Check the box next to the appropriate role and click 'Select'
9. Enter 'Justification' and click 'Save'

Now ask the user to logoff and login again to  E-Business Suite and try again to access Oracle Diagnostics.

EBusiness - "Authentication Failed" while Save As Concurrent Output in the Internet Explorer

How to solve Oracle Apps "Authentication Failed" error while saving a concurrent output in IE

Got the error "Authentication Failed" when i try to save a concurrent output on my computer.
The saved concurrent request output does not contain anything other than "Authentication Failed"

Solution

1.  From Internet Explorer, select Tools > Interent Options
2.  On the General tab, in the "Temporary Internet files"  select the Settings
      For IE 8 and Above: Click on the Settings button under Browsing history
3. Change "Check for newer versions of stored pages:" to "Automatically"
4. Save changes

Salvage Data from corrupted Oracle Table

Recently, when i query a table with full table scan it gives error and i failing.
It given error ORA-8103 object no longer exists

Then i tried to do analyze the table using validate structure option, that also given same error.
SO it look like there is some kond of corruption. I restored some old backup and checked the table again, that also reported the same error.
Now i will not be able to restore the table from the backup as error persist for long time.

I have two indexs attached to this table, so I tried analyzing the Indexes and that also reported same error.
Then i tried to repair the table using DBMS_REPAIR package and it crashed with ORA-600 error.

Now i have only one option left, somehow extract the data that can be retrived from the table.

Oracle is providing a method to retrive recoverable rows by getting rowid of the row using index. Recovered rows will be insert into new table.

table data can be saved by skipping the blocks that are causing the ORA-8103 error:

Following are the step i performed to recover data.

Disclaimer: This is just for information and accademic purpose only.

Pre-requisite: Table must have an indexed column.

Step 1: Create a new table structure similar to original table

Create table <new table name> as select * from <original table name>
where 1<>1

Step 2: Create a table to store bad row's rowid and error numnber.

create table bad_rows (row_id rowid
                      ,oracle_error_code number);

Step 3: Recover Data by executing below pl/sql block (Replace the table names, columns as per your need)

set serveroutput on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

  CURSOR c1 IS select /*+ index(tab1) */ rowid
  from <original table name> tab1
  where <indexed column> is NOT NULL;

  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into <new table name>
     select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
     from <original table name> A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

Step 4: Note down the indexes on original table for creating on new table

Step 5: Rename the Original Table

Step 6: Drop the indexes

Step 7: Rename the new table to original table name

Step 8: Recreate the indexes

Step 9: Recreate the synoyms & grants

Step 10: Compile invalid objects any depends on this table.

========================

Restore RMAN Incremental backup to new server

I will present in this blog how to restore 10gR2 Database incremental backup to a new server using RMAN. The method I used here will not use RMAN catalog database assuming that you have only database backup.

RMAN Backup Configuration

Daily :  Incremental Level 1 cumulative

Weekly:  Incremental Level 0

Retention Policy:   RECOVERY WINDOW OF 7 DAYS

Device: Disk

Control File Autobackup:  on

In this, assume that following directories are used to restoring DB

DB Name	ORCL1
u04/ORCL1/flash_recovery_area	Flash Recovery Area
/u01/ ORCL1/oracle/admin	ORACLE_BASE
/u04/ ORCL1/oradata	To store datafile

Pre-requisite for restore

1.       You must know DBID of the database to be restored.

2.       A valid backup is required.

3.       Sufficient Disk Space on the server to restore the backup.

4.       If restoring from tape, Tape Library must be configured to the server for restoring.

5.       Server OS/Architecture must be same as the backed up database server.

6.       All recommended patches and tuning must be done on the server as per Oracle documentation.

7.       Oracle Software of same version of backed up database must be installed.

8.       Oracle Software must be patched similar to backed up database.

Performing Complete Recovery

1.       Restore the backup: Once the server is ready with all pre-requisites, restore the backup to the specific directory/directories. Here it will be to the flash recovery area

2.       Verify the directory and file permissions: Oracle user and dba group must have full permission on the restored directory and files. Owner of the files must be oracle user.

3.       Parameter file:  Identify the backup piece from the Latest CONTROLFILE and SPFILE backups located in restored $Flash_Recovery_Area/autobackup/

4.       Parameter file:  Identify the backup piece from the Latest CONTROLFILE and SPFILE backups located in restored $Flash_Recovery_Area/autobackup/<latest date>. Filename usually starts with o1_mf_s_ if you are backing up spfile with controlfile autobackup, otherwise Filename look like o1_mf_n_

If the parameter file is not backed up as part of your backup strategy, you must create it manually.

5.       Add entry for LISTENER_ORCL1 in $ORACLE_HOME/network/admin/tnsnames.ora

6.       Restore SPFILE as follows. SPIFLE will be restored in $ORACLE_HOME/dbs  (If you don’t have spfile in autobackup you will have to create it manually)

$ export ORACLE_SID= ORCL1

$ rman target /

RMAN> startup nomount

RMAN> set DBID=<DBID of your database>

RMAN> restore spfile from "<$FLASH_RECOVERY_AREA>/autobackup/<latest directory>/o1_mf_s_751194987_6wyrfc7d_.bkp ";

7.       Create PFILE (initORCL1.ora) from SPFILE;

RMAN> sql 'create pfile from spfile';

8.       Modify the PFILE(initORCL1.ora) and make the required changes.

9.       Correct the following parameters as per new environment.

Control File = /new/path/controln1.dbf,/new/path/controln1.dbf

user_dump_dest = <ORACLE_BASE>/admin/<DBNAME>/udump

background_dump_dest = <ORACLE_BASE>/admin/<DBNAME>/bdump

core_dump_dest = <ORACLE_BASE>/admin/<DBNAME>/bdump

log_archive_dest_1 = 'location=/new/path'

10. Restore control file as follows

restore controlfile to '<path>' from "<restored/controlfile/autobackup/path>";

11.   Force Mount the Instance as follows:

RMAN> startup force mount pfile=$ORACLE_HOME/dbs/initORCL1.ora

12.   Create SPFILE from PFILE.

RMAN> sql 'create spfile from pfile'

13.   Shutdown the Database and Mount the database:

RMAN> shutdown

RMAN> startup mount;

14.    Disable Flashback Database as follows:

SQ> alter database flashback off;

15.   Disable Block Change Tracking as follows:

SQ> alter database disable block change tracking;

16.   Restore the Database as follows. Tail the alert log file and make sure database files are getting restored.  Since we are restoring datafiles to new location, files to be renamed(Yu can generate a script to do this). This needs to be executed as one command in run

$ export ORACLE_SID= ORCL1

$ rman target /

RMAN>

run{

set newname for datafile '<old/path/>orcl1_system01.dbf' to '/u04/ORCL1/oradata/orcl1_system01.dbf';

.

.

.

<Rename all your DBFs>

.

.

restore database;

switch datafile all;

recover database;

}

17.   Rename Log File:

If the restored log file path is different, then rename the restored log files in the control fileUse the below SQL to generate script to rename all data files and execute the generated script.

select 'alter database rename file '''|| member ||''' to '''|| replace(member,'<old/path/>','</new/path/>')||''';' from v$logfile

18.   Open Database: Open the Database by executing following command

SQL> Alter database open resetlogs;

19.   Create Temp Tablespace

SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/new/path/<db_name>_temp02.dbf' size 500m autoextend off ;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> DROP TABLESPACE temp;

20.    Configure & change Flash Recovery Area:

SQL> alter system set db_recovery_file_dest='</new/path> /flash_recovery_area' scope=both;