thanks, but where it'll know the backup files location?

and this DBID will be of the source ... right?

location:
catalog start with '//backup files location/';

dbid is from source
select dbid from v$database;

thanks Greet, sorry forgot to mention one thing; Source file system is ASM and target is regular file system, I used db_file_convert and log_*_convert in the parameter file.

Also one more, it is still trying to read from the location mention at the Source backup folder, where as I've already catalog this target backup location.

Please advice ... thanks.

Here is the error & show all too ... from target:

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00064 to +DATA02/orcl/datafile/orcl_data.18.dbf
channel ORA_DISK_1: reading from backup piece /EMC-Backup/PHDBborcl/PHDBorcl/20140506_full/L09gp7k8gt_1_1
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /backup/20140506_full/L09gp7k8gt_1_1
ORA-19504: failed to create file "+DATA02/orcl/datafile/orcl_data.18.dbf"
ORA-17502: ksfdcre:3 Failed to create file +DATA02/orcl/datafile/orcl_data.18.dbf
ORA-15001: diskgroup "DATA02" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Servic
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00059 to +DATA02/orcl/datafile/orcl_data.16.dbf
channel ORA_DISK_1: reading from backup piece /EMC-Backup/PHDBborcl/PHDBorcl/20140506_full/L09hp7k8li_1_1

RMAN> show all;

RMAN configuration parameters for database with db_unique_name orcl are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

and this error is coming, when doing recover database;

creating datafile file number=1 name=/u01/oradata/orcl/datafile/system.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/14/2014 06:27:14
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA01/orcl/datafile/system.297.838480937'

after dong the newname too, still giving error

I'm good now, restore is happening, will update, if encounter any issues ... thanks.

Guys stuck again, restore was doing fine, but now it's only restoring to one file system, and it got 100% full, how to distribute them, I'm using this link & command:

http://gavinsoorma.com/2013/02/restoring-a-asm-backup-to-non-asm-and-restoring-from-rac-to-single-instance/

RCV:
run {
set newname for database to '/u01/oradata/orcl/%b';
set newname for database to '/u02/oradata/orcl/%b';
set newname for database to '/u03/oradata/orcl/%b';
set newname for tempfile 1 to '/u03/oradata/orcl/%b';
restore database;
switch datafile all;
switch tempfile all;
}

this is working perfect, only restoring on /u03 ... only :(

Can you assist here?

instead of 'SET NEWNAME for DATABASE to ....'

try:

get the number for each datafile from the source:

set lines 200 pages 9999 trims on
col name for a50
select file#,name from v$datafile order by 1 asc;

     FILE# NAME
---------- --------------------------------------------------
         1 /data1/ORCL/system01.dbf
         2 /undo1/ORCL/undotbs01.dbf
         3 /data1/ORCL/sysaux01.dbf
         4 /data1/ORCL/ORCLDATA01.dbf
         5 /data1/ORCL/ORCLDATA.dbf
         6 /data1/ORCL/users01.dbf
         7 /data1/ORCL/ORCLDATA02.dbf
         8 /data1/ORCL/PADATA01
         9 /data1/ORCL/PAINDEX01
        10 /data1/ORCL/ORCLDATA03.dbf
        11 /data1/ORCL/ORCLDATA04.dbf
        12 /data1/ORCL/sysaux02.dbf
        13 /data1/ORCL/ORCLDATA05.dbf
        14 /data1/ORCL/tools01.dbf

14 rows selected.

script to get highest SCN from most recent backup on source (if you are using the most recent backup):

/* Gets the highest SCN from the most recent backup to use with 'SET UNTIL SCN...' in RMAN restore */

SELECT MIN(VBD.CHECKPOINT_CHANGE#), MAX(VBD.CHECKPOINT_CHANGE#)
FROM V$BACKUP_DATAFILE VBD, V$DATAFILE VDF,
(SELECT
CREATION_CHANGE#,
MAX(COMPLETION_TIME) COMPLETION_TIME
FROM V$BACKUP_DATAFILE
WHERE CREATION_CHANGE# IN (
SELECT CREATION_CHANGE# FROM V$DATAFILE)
GROUP BY CREATION_CHANGE#
) QUERY1
WHERE VBD.CREATION_CHANGE# = VDF.CREATION_CHANGE#
AND VBD.CREATION_CHANGE# = QUERY1.CREATION_CHANGE#
AND VBD.COMPLETION_TIME = QUERY1.COMPLETION_TIME;

example of set the NEWNAME for the datafiles (RMAN script to run on target):

RUN {
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
ALLOCATE CHANNEL CH3 TYPE DISK;
ALLOCATE CHANNEL CH4 TYPE DISK;
SET UNTIL SCN 18251556422;        
SET NEWNAME FOR DATAFILE 1 TO '/data2/ORCL/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/undo2/ORCL/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/data2/ORCL/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/data2/ORCL/ORCLDATA01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/data2/ORCL/ORCLDATA.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/data2/ORCL/users01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/data2/ORCL/ORCLDATA02.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/data3/ORCL/PADATA01';
SET NEWNAME FOR DATAFILE 9 TO '/data3/ORCL/PAINDEX01';
SET NEWNAME FOR DATAFILE 10 TO '/data3/ORCL/ORCLDATA03.dbf';
SET NEWNAME FOR DATAFILE 11 TO '/data3/ORCL/ORCLDATA04.dbf';
SET NEWNAME FOR DATAFILE 12 TO '/data3/ORCL/sysaux02.dbf';
SET NEWNAME FOR DATAFILE 13 TO '/data3/ORCL/ORCLDATA05.dbf';
SET NEWNAME FOR DATAFILE 14 TO '/data3/ORCL/tools01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}


The 'SET NEWNAME FOR DATAFILE <number>' TO...' lets you specify the new filename for the files on the target server, so you can specify which filesystem they go to.

by using restore database preview

it is giving me the SCN in the backup files, should I use that one, basically what I've just kicked off,

what I was planning to do, new the run { set newname for all the datafiles + tempfile too and restore database and switch all datafile & tempfile too

and then recover database until that SCN

what do you think, should I do that or yours ... please advice?

you can do a combination of the 2.  i left out the tempfile piece, you can add that in, and then do the 'SWITCH DATAFILE ALL;' and 'SWITCH TEMPFILE ALL;'  before doing the recover.

Can you post the output that you are getting the SCN from?  (just to see what it is showing you, and to make sure it is the right one to use).

As far as getting the SCN to use for the 'SET UNTIL SCN...' the idea is to get the SCN that will allow you to do a full restore to a point in time where the database is stable, and not have to mess around with doing a recovery and applying numerous archive logs.

Example - Finding the High SCN in SQLPlus:

ORCL_TEST> l
  1  SELECT MIN(VBD.CHECKPOINT_CHANGE#) MIN_SCN, MAX(VBD.CHECKPOINT_CHANGE#) MAX_SCN
  2  FROM V$BACKUP_DATAFILE VBD, V$DATAFILE VDF,
  3  (SELECT
  4  CREATION_CHANGE#,
  5  MAX(COMPLETION_TIME) COMPLETION_TIME
  6  FROM V$BACKUP_DATAFILE
  7  WHERE CREATION_CHANGE# IN (
  8  SELECT CREATION_CHANGE# FROM V$DATAFILE)
  9  GROUP BY CREATION_CHANGE#
 10  ) QUERY1
 11  WHERE VBD.CREATION_CHANGE# = VDF.CREATION_CHANGE#
 12  AND VBD.CREATION_CHANGE# = QUERY1.CREATION_CHANGE#
 13* AND VBD.COMPLETION_TIME = QUERY1.COMPLETION_TIME

   MIN_SCN          MAX_SCN
   ----------              ----------
   9606561879    9606561882


Now locate it in RMAN (to cross reference).  Notice the 2 bold SCNs toward the bottom of the output.  They are higher than the SCN we have listed as our MAX_SCN...BUT, they are dealing with archive logs that would need to be applied during recovery after the restore.  For what we are wanting here, if we use the SCN that was listed as the MAX_SCN, we should be able to just restore the database (without performing recovery), and then do an 'alter database open resetlogs' and be up and running at that point:

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================


.
.
.
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2808    52.47M     DISK        00:00:16     14-MAY-14
        BP Key: 2808   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163028
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_ntp89cd5_1_1_20140514.al

  List of Archived Logs in backup set 2808
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1103    9606482006 14-MAY-14 9606561749 14-MAY-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2809    118.48M    DISK        00:00:32     14-MAY-14
        BP Key: 2809   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163028
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_nrp89cd5_1_1_20140514.al

  List of Archived Logs in backup set 2809
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1099    9606240162 13-MAY-14 9606331292 14-MAY-14
  1    1100    9606331292 14-MAY-14 9606353723 14-MAY-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2810    127.05M    DISK        00:00:33     14-MAY-14
        BP Key: 2810   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163028
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_nsp89cd5_1_1_20140514.al

  List of Archived Logs in backup set 2810
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1101    9606353723 14-MAY-14 9606429015 14-MAY-14
  1    1102    9606429015 14-MAY-14 9606482006 14-MAY-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2811    Full    4.50G      DISK        00:32:00     14-MAY-14
        BP Key: 2811   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163106
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_o0p89ceb_1_1_20140514.bk
  List of Datafiles in backup set 2811
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 9606561881 14-MAY-14 /undo1/ORCL_TEST/undotbs01.dbf
  5       Full 9606561881 14-MAY-14 /data1/ORCL_TEST/ORCLDATA.dbf
  6       Full 9606561881 14-MAY-14 /data1/ORCL_TEST/users01.dbf
  14      Full 9606561881 14-MAY-14 /data1/ORCL_TEST/tools01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2812    Full    4.66G      DISK        00:32:08     14-MAY-14
        BP Key: 2812   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163106
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_o1p89ceb_1_1_20140514.bk
  List of Datafiles in backup set 2812
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 9606561882 14-MAY-14 /data1/ORCL_TEST/system01.dbf
  7       Full 9606561882 14-MAY-14 /data1/ORCL_TEST/ORCLDATA02.dbf
  8       Full 9606561882 14-MAY-14 /data1/ORCL_TEST/PADATA01
  9       Full 9606561882 14-MAY-14 /data1/ORCL_TEST/PAINDEX01

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2813    Full    5.77G      DISK        00:38:11     14-MAY-14
        BP Key: 2813   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163106
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_nvp89cea_1_1_20140514.bk
  List of Datafiles in backup set 2813
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 9606561880 14-MAY-14 /data1/ORCL_TEST/sysaux01.dbf
  11      Full 9606561880 14-MAY-14 /data1/ORCL_TEST/ORCLDATA04.dbf
  12      Full 9606561880 14-MAY-14 /data1/ORCL_TEST/sysaux02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2814    Full    12.26G     DISK        01:04:41     14-MAY-14
        BP Key: 2814   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T163106
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_nup89cea_1_1_20140514.bk
  List of Datafiles in backup set 2814
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 9606561879 14-MAY-14 /data1/ORCL_TEST/ORCLDATA01.dbf
  10      Full 9606561879 14-MAY-14 /data1/ORCL_TEST/ORCLDATA03.dbf
  13      Full 9606561879 14-MAY-14 /data1/ORCL_TEST/ORCLDATA05.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2815    4.38M      DISK        00:00:01     14-MAY-14
        BP Key: 2815   Status: AVAILABLE  Compressed: YES  Tag: TAG20140514T173554
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_o2p89g7q_1_1_20140514.al

  List of Archived Logs in backup set 2815
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1104    9606561749 14-MAY-14 9606566542 14-MAY-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2816    Full    14.55M     DISK        00:00:01     14-MAY-14
        BP Key: 2816   Status: AVAILABLE  Compressed: NO  Tag: TAG20140514T173555
        Piece Name: /backup/flash_recovery_area/ORCL_TEST/backupset/ORCL_TEST_c-1577115223-20140514-00.cf
  Control File Included: Ckp SCN: 9606566553   Ckp time: 14-MAY-14

RMAN>

thanks all, I was able to restore and then recover too, but I think it is looking for redo logs, please assist ... thanks.

[oracle@qhdborcl rman]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 14 21:35:19 2014

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

connected to target database: orcl (DBID=1758995321, not open)

RMAN> sql 'alter database open resetlogs';

using target database control file instead of recovery catalog
sql statement: alter database open resetlogs
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/14/2014 21:35:33
RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs
ORA-00349: failure obtaining block size for '+DATA01/orcl/onlinelog/group_5.redo1.lo

why I'm not able to add a logfile, I've already dropped that group and permissions and space are good there:

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u02/orcl/onlinelog/group_5.redo1.log', '/u03/orcl/onlinelog/group_5.redo2.log') SIZE 300M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u02/orcl/onlinelog/group_5.redo1.log', '/u03/orcl/onlinelog/group_5.redo2.log') SIZE 300M
*
ERROR at line 1:
ORA-00301: error in adding log file '/u02/orcl/onlinelog/group_5.redo1.log' - file cannot be created
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

I'm good ... sorry, missing in directory name.

now stuck here ... :(

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database drop logfile group 10;
alter database drop logfile group 10
*
ERROR at line 1:
ORA-01567: dropping log 10 would leave less than 2 log files for instance VERIWIS2 (thread 2)
ORA-00312: online log 10 thread 2: '/u02/orcl/onlinelog/group_10.redo1.log'
ORA-00312: online log 10 thread 2: '/u03/orcl/onlinelog/group_10.redo2.log'

good again ... :)

do I need to do anything else after startup open?

because seeing some errors in alert.log & trace file too.

ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmon_10973.trc  (incident=143645):
ORA-00600: internal error code, arguments: [kewrpwrc_2: Invalid SWRF version], [3], [5], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_143645/orcl_mmon_10973_i143645.trc
Use ADRCI or Support Workbench to package the incident.

Go to OS command line and type 'adrci' to access the adrci interface.  Then you will need to create a package for the incident/problem.  You can check Oracle support doc 411.1 for details on how to do this.  Since this is an internal error, you will need to create an SR and upload the package to the SR.

Example:

adrci> show incident

(lists incidents)

adrci> ips create package incident <incident_number>

This will create a package and assign it a package number.  This is a logical package, and you will now need to create a physical package from it:

adrci>IPS GENERATE PACKAGE <package_number> IN path_on_server

Then FTP the package (zip file) to your PC and upload to your SR.

When you are creating the SR and get to the line where you list error codes, you should be able to list the first few arguments of the error code (it should prompt you).

Example:

ORA-00600 [kewrpwrc_2: Invalid SWRF version], [3], [5]

From there, it will probably be able to gather more info on the issue from the support site and create a guided SR for you, where it fills in the rest of the SR and creates it.

Found following details, sounds like it may be related to a version mismatch (yours is [3],[5], but it is the same principle):

This is discussed in
Bug 11817679 - ORA-00600 [KEWRPWRC_2: INVALID SWRF VERSION] WHEN CREATING BASELINE
which is closed as not a bug.

The error ORA-00600: [kewrpwrc_2: Invalid SWRF version], [3], [4] shows argument values 3 and 4 which are not equal and here it means that there is a version mismatch.

[3] means it is coming from 11.1 binary,
[4] means it is coming from the 11.2 binary,
and does not match the entry in wrh$_wr_control.swrf_version.

You are not allowed to create a baseline if the AWR schema is not up to date.

If this system still has version 3, then your upgrade from 11gR1 to 11gR2 was not done correctly, or not done successfully.

Refer to c1101000.sql you will see the update commented...

Rem =======================================================
Rem == Update the SWRF_VERSION to the current version. ==
Rem == (11gR2 = SWRF Version 4) ==
Rem == This step must be the last step for the AWR ==
Rem == upgrade changes. Place all other AWR upgrade ==
Rem == changes above this. ==
Rem =======================================================

SOLUTION

Review the c1101000.sql script and see if any of the other changes it makes have not been run.
This error would not be signaled if the upgrade was successful.

Please advice, what should I do, now the there are so many incidents and problems too ... what to report and what not to?

Please assist :(

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              
-------------------- ----------------------------------------------------------- ----------------------------------------
140777               ORA 600 [kkkichkrmappri:0=nr pend]                          2014-05-15 05:12:09.530000 -05:00      
140778               ORA 600                                                     2014-05-15 05:12:13.616000 -05:00      
140779               ORA 600 [kkkichkrmappri:0=nr pend]                          2014-05-15 05:12:15.970000 -05:00      
140780               ORA 600                                                     2014-05-15 05:12:17.108000 -05:00      
143645               ORA 600 [kewrpwrc_2: Invalid SWRF version]                  2014-05-15 05:14:16.211000 -05:00      
143653               ORA 7445 [kewmcord_calc1rdif()+254]                         2014-05-15 05:14:21.268000 -05:00      
146527               ORA 600 [kewrpwrc_2: Invalid SWRF version]                  2014-05-15 05:56:04.249000 -05:00      
146535               ORA 7445 [kewmcord_calc1rdif()+254]                         2014-05-15 05:56:05.996000 -05:00      
146599               ORA 600 [kkkichkrmappri:0=nr pend]                          2014-05-15 08:01:52.834000 -05:00      
146600               ORA 600                                                     2014-05-15 08:01:54.886000 -05:00      
146601               ORA 600 [kkkichkrmappri:0=nr pend]                          2014-05-15 08:01:57.780000 -05:00      
146602               ORA 600                                                     2014-05-15 08:01:58.895000 -05:00      
146603               ORA 7445 [eomg_migrate_if_oldnew()+61]                      2014-05-15 08:04:16.555000 -05:00      
146604               ORA 7445 [eomg_migrate_if_oldnew()+61]                      2014-05-15 08:06:19.313000 -05:00      
146605               ORA 7445 [eomg_migrate_if_oldnew()+61]                      2014-05-15 08:07:14.642000 -05:00      
149416               ORA 600 [kewrpwrc_2: Invalid SWRF version]                  2014-05-15 08:15:08.217000 -05:00      
149424               ORA 7445 [kewmcord_calc1rdif()+254]                         2014-05-15 08:15:12.670000 -05:00      
155178               ORA 600 [kewrpwrc_2: Invalid SWRF version]                  2014-05-15 09:05:00.456000 -05:00      
155186               ORA 7445 [kewmcord_calc1rdif()+254]                         2014-05-15 09:05:02.219000 -05:00      
19 rows fetched

adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                            
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2                    ORA 600 [kkkichkrmappri:0=nr pend]                          146601               2014-05-15 08:01:57.780000 -05:00      
1                    ORA 600                                                     146602               2014-05-15 08:01:58.895000 -05:00      
5                    ORA 7445 [eomg_migrate_if_oldnew()+61]                      146605               2014-05-15 08:07:14.642000 -05:00      
4                    ORA 600 [kewrpwrc_2: Invalid SWRF version]                  155178               2014-05-15 09:05:00.456000 -05:00      
3                    ORA 7445 [kewmcord_calc1rdif()+254]                         155186               2014-05-15 09:05:02.219000 -05:00      
5 rows fetched

adrci>

The one that you mentioned in your previous comment is Problem 4:

adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                            
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2                    ORA 600 [kkkichkrmappri:0=nr pend]                          146601               2014-05-15 08:01:57.780000 -05:00      
1                    ORA 600                                                     146602               2014-05-15 08:01:58.895000 -05:00      
5                    ORA 7445 [eomg_migrate_if_oldnew()+61]                      146605               2014-05-15 08:07:14.642000 -05:00      
4                    ORA 600 [kewrpwrc_2: Invalid SWRF version]                  155178               2014-05-15 09:05:00.456000 -05:00      
3                    ORA 7445 [kewmcord_calc1rdif()+254]                         155186               2014-05-15 09:05:02.219000 -05:00      
5 rows fetched

adrci>

So for that one, you would need to do the following:

adrci> IPS CREATE PACKAGE PROBLEM 4

This will create a logical package with details about the problem including relevant trace files and any other relevant information that Oracle support needs.  In ADR, a "problem" is a grouping of multiple occurrences of the same "incident".  Each incident is a single occurrence of some type of issue/alert/error.

After packaging that problem, you need to generate a physical package.  This will create a zip file on the filesystem of your choice, and you can then ftp it to your computer (if needed) and upload it to the SR for Oracle support to review.  Assuming that the package created is number 1:

adrci> IPS GENERATE PACKAGE 1 IN /home/oracle/dba


Now, as for all of the other incidents and problems you have, it looks like you have numerous ORA-600 errors, which are internal errors, and several ORA-7445 errors, which are core dumps.  Seems like your attempt to upgrade the database version from 11.1.0.7 to 11.2.0.4 by restoring to the new server may not have completed with desirable results.  You need to run these errors by Oracle support to see what is causing them.  Is this a temporary database  (11.2.0.4) to pull some data out of?  Or were you planning to use it long term?

thanks man ... you are really awesome :)

we are just testing our backups, how we'll restore, at the time of need, tell me, what you want me to do?

after doing a lot of work ... XDB is the culprit.

ah, yes, I have had several problems with xdb in the past and have had to reinstall and reconfigure it after the fact.  happy to help and glad to see progress is being made.  are you going  to setup xdb again?

yes trying/working on it

Hi cheers4beers, I'm not able to drop this MDSYS user, can you please assist:

SQL> drop user MDSYS cascade;
drop user MDSYS cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_DROP_USER
ORA-01031: insufficient privileges

tried this and got this ... :(

SQL> drop trigger mdsys.sdo_drop_user;

Trigger dropped.

SQL> drop user mdsys cascade;
drop user mdsys cascade
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qmrcInitSGA], [57], [], [], [], [], [], [], [], [], [], []

Are you just trying to remove and re-install Oracle Spatial?

Doc ID 270588.1

Manual installation of Spatial 10g / 11g

Prerequisites

To be able to do a successful Spatial 10g / 11g installation you need to have the following products already installed:

JServer JAVA Virtual Machine
Oracle interMedia    
Oracle XML Database

To verify if the products are installed and valid run:

SQL> select comp_id,version,status from dba_registry
     where comp_id in ('JAVAVM','ORDIM','XDB');  

To (re-)install JServer see: Note:276554.1
To (re-)install XDB see: Note:1292089.1
To (re-)install interMedia: Note:337415.1

Installation of Spatial

If the MDSYS user does NOT exist:

    create the user MDSYS by running following command:

    SQL> create user MDSYS identified by <password> default tablespace SYSAUX account lock;

    grant the required privileges to MDSYS by running:

    SQL> @?/md/admin/mdprivs.sql

If the MDSYS user already exists then you are advised to verify if the installation has been done correctly and de-install Spatial first in case of re-installation.

    See the verification checks at the bottom of this note and a link to de-installation note.
 
    Install Spatial by executing the steps shown below. Note you need to run this as a SYSDBA user!

    SQL> connect / as sysdba
    SQL> spool spatial_installation.lst
    SQL> @?/md/admin/mdinst.sql
    SQL> spool off

    At the end of the installation some verification steps are automatically executed!

You can also manually run the the verification steps later on.
See the Spatial verification section further down this note.

It is strongly recommended that the MDSYS user account remains locked. The MDSYS user is
created with administrator privileges; therefore, it is important to protect this account from unauthorized
use.  To lock the MDSYS user, connect as SYS and enter the following command:

    SQL> alter user MDSYS account lock;


Verification of an Oracle 10g / 11g Spatial Installation

Execute the following steps to verify if Spatial is installed correctly:

    SQL> connect / as sysdba  
    SQL> set serveroutput on
    SQL> execute validate_sdo;
    SQL> select comp_id, control, schema, version, status, comp_name from dba_registry
              where comp_id='SDO';
    SQL> select object_name, object_type, status from dba_objects
              where owner='MDSYS' and status <> 'VALID'
              order by object_name;

    A sample valid 10.2.0.4.0 installation shows the following output:

    SQL> execute validate_sdo;

    PL/SQL procedure successfully completed.

    SQL> select comp_id, control, schema, version, status, comp_name from dba_registry
              where comp_id='SDO';

    COMP_ID                        CONTROL
    ------------------------------ ------------------------------
    SCHEMA                         VERSION                        STATUS
    ------------------------------ ------------------------------ -----------
    COMP_NAME
    --------------------------------------------------------------------------------
    SDO                            SYS
    MDSYS                          10.2.0.4.0                     VALID
    Spatial            


    no rows selected

NOTE:179472.1 - Steps for Manual De-installation of Oracle Spatial
NOTE:220481.1 - Master Note for Oracle Spatial and Oracle Locator Installation

Give up ... it's not working, now planning to drop and re-restore ... what do you think and there was no requirement of upgrade to 11204, I just did myself and got stuck.

Please advice, what do you think, it's so weird that when I execute this utlrp.sql script, after a while, it starts giving ... not connected to Oracle error.

Please assist, as you always, thanks cheers4beers for all your help and assistance.

FYI ... have to drop database and recreate and everything works perfect ... thanks both of you :)