Importing 11.2.0.3 database schema into 11.2.0.1 version

Hi,
Today I have got an a mail from my manager says, import a production database schema into our UAT environment for testing purpose.

Source database version : 11.2.0.3
Used method for Export: Data pump
Target database version : 11.2.0.1.

I had bit confusion in my mind that we need to import from higher version to lower version..is that possible ?
Other side have clarity that both are 11g ...I think it may work (no any prior exp like this)..
So started importing ..

Source log file:

Steps I followed :
1) Dump file having 125 MB size. so I thought using the default data pump directory.
Select * from dba_directories;

2) I copied dump file,log file into above DATA_PUMP_DIR location.
3)Connected to server ran bash profile.
4)Created two table spaces as per requirement. We need not to create same name table spaces, we can use remap_tablespaces parameter. But i got an errors related spatial indexes.

In some of the blogs i found we can ran out of these issues if we gave create table, sequence like privileges to the imported schema.But no luck to me...

5) Executed the below query by going to respected data pump directory..

Target Log file :

Alert Log file errors in 11.2.0.1 DB.Fatal NI connect error 12537 and 12547.

Hi,

I have found below messages in one of our 11.2.0.1 Oracle development DB alert log file.

1)

  Fatal NI connect error 12537, connecting to:

  (LOCAL=NO)

  VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 09-DEC-2013 20:27:35

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12537

    

TNS-12537: TNS:connection closed

    ns secondary err code: 12560

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

opiodr aborting process unknown ospid (12547) as a result of ORA-609

Mon Dec 09 20:27:35 2013

2) 

Fatal NI connect error 12547, connecting to:

 (LOCAL=NO)

  VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 09-OCT-2013 23:05:36

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12547

    

TNS-12547: TNS:lost contact

    ns secondary err code: 12560

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

opiodr aborting process unknown ospid (10574) as a result of ORA-609

Wed Oct 09 23:05:56 2013

Clearing Resource Manager plan via parameter

Thu Oct 10 03:03:13 2013

3) 

Fatal NI connect error 12170.

  VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 10-OCT-2013 03:03:13

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

    

TNS-12535: TNS:operation timed out

    ns secondary err code: 12560

    nt main err code: 505

    

TNS-00505: Operation timed out

    nt secondary err code: 110

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.12.161)(PORT=55325))

Thu Oct 10 05:49:45 2013

4) Errors in file /u01/app/diag/rdbms/epm/epm/trace/epm_j000_15056.trc  (incident=31383):

ORA-00600: internal error code, arguments: [17059], [0x0B98A25A0], [0x0B98A2E38], [0x0C24173C8], [], [], [], [], [], [], [], []

Incident details in: /u01/app/diag/rdbms/epm/epm/incident/incdir_31383/epm_j000_15056_i31383.trc

Thu Jan 02 21:34:12 2014

Errors in file /u01/app/diag/rdbms/epm/epm/trace/epm_j000_15056.trc  (incident=31384):

ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [17059], [0x0B98A25A0], [0x0B98A2E38], [0x0C24173C8], [], [], [], [], [], [], [], []

Incident details in: /u01/app/diag/rdbms/epm/epm/incident/incdir_31384/epm_j000_15056_i31384.trc

Thu Jan 02 21:34:12 2014

Trace dumping is performing id=[cdmp_20140102213412]

Pfile and Startup Process :

 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileidm.ora

System parameters with non-default values:

  processes                = 500

  sessions                 = 772

  memory_target            = 2400M

  control_files            = "/u01/app/oracle/oradata/idm/control01.ctl"

  control_files            = "/u01/app/oracle/oradata/idm/control02.ctl"

  db_block_size            = 8192

  compatible               = "11.2.0.0.0"

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = "bias.com"

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=idmXDB)"

  audit_file_dest          = "/u01/app/oracle/admin/idm/adump"

  audit_trail              = "DB"

  db_name                  = "idm"

  open_cursors             = 800

  diagnostic_dest          = "/u01/app/oracle"

• Thu Oct 17 21:42:11 2013 PMON started with pid=2, OS id=2818 
• Thu Oct 17 21:42:11 2013 VKTM started with pid=3, OS id=2820 at elevated priority
• Thu Oct 17 21:42:11 2013 GEN0 started with pid=4, OS id=2824 
• VKTM running at (10)millisec precision with DBRM quantum (100)ms
• Thu Oct 17 21:42:11 2013 DIAG started with pid=5, OS id=2826 
• Thu Oct 17 21:42:11 2013 DBRM started with pid=6, OS id=2828 
• Thu Oct 17 21:42:11 2013 PSP0 started with pid=7, OS id=2830 
• Thu Oct 17 21:42:11 2013 DIA0 started with pid=8, OS id=2832 
• Thu Oct 17 21:42:11 2013 MMAN started with pid=9, OS id=2834 
• Thu Oct 17 21:42:11 2013 DBW0 started with pid=10, OS id=2836 
• Thu Oct 17 21:42:11 2013 LGWR started with pid=11, OS id=2838 
• Thu Oct 17 21:42:11 2013 CKPT started with pid=12, OS id=2840 
• Thu Oct 17 21:42:11 2013 SMON started with pid=13, OS id=2842 
• Thu Oct 17 21:42:11 2013 RECO started with pid=14, OS id=2844 
• Thu Oct 17 21:42:11 2013 MMON started with pid=15, OS id=2846 

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Thu Oct 17 21:42:11 2013

MMNL started with pid=16, OS id=2848 

starting up 1 shared server(s) ...

ORACLE_BASE not set in environment. It is recommended

that ORACLE_BASE be set in the environment

Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle

Thu Oct 17 21:42:12 2013

ALTER DATABASE   MOUNT

Successful mount of redo thread 1, with mount id 961017972

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Thu Oct 17 21:42:16 2013

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

Alert Log Errors :

ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option

Hii,

To day I have faced the problem with  ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option errors while opening the database which is created newly.
There may be 3 possibilites to get above errors. I have solved my problem by using 3 rd option.

1) giving wrong or old ORACLE_HOME or PATH variables while opening newly upgraded DAtabase.
Solution:
set or export ORACLE_HOME= <newly upgraded ORACLE_HOME>
set or export PATH=<newly upgraded PATH variables>

2) After upgraing the ORACLE_HOME, if you missed to run catupgrd.sql script, we will get above errors while starting the datbase.

Solution:

sqlplus / as sysdba
sql>startup upgrade
sql>@<ORACLE_HOME>/rdbms/admin/catupgrd.sql
sql>@<ORACLE_HOME>/rdbms/admin/utlrp.sql
sql>shut immediate
sql>startup

3) After creation of new database if we missed to execute catalog.sql,catproc.sql or  executed with errors also we will get above errors.

Solution:

sqlplus / as sysdba
sql>startup upgrade
sql>@<ORACLE_HOME>/rdbms/admin/catalog.sql
sql>@<ORACLE_HOME>/rdbms/admin/catproc.sql
sql>shut immediate
sql>startup

Queries I often used on table spaces sizes and space used

To check the allocated space and used space in tablespaces:

1) SELECT d.status "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
        d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
        (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
        "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
        WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
        D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND D.CONTENTS LIKE 'TEMPORARY';

2)
 SELECT /* + RULE */  DF.TABLESPACE_NAME "Tablespace",
       DF.BYTES / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY DF.TABLESPACE_NAME,FS.BYTES,DF.BYTES_FREE,DF.BYTES_USED
 ORDER BY 4 DESC;
 SELECT * FROM dba_temp_free_space; 

2)  To check the schemas sizes in database: 

SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB
FROM dba_segments s
GROUP BY S.OWNER;

3) Learn about TEMPORARY   TABALE SPACE HERE

4) Query to find the long running queries in database :

select distinct sesid,serial, spid pid, osuser,schemaname,machine, floor(last_call_et/60) last_call, EVENT,program, sql_text, Mins, P1, P2, P3,hash_value
from
    (select sesid,serial, spid, sql_text, osuser,schemaname,machine, last_call_et, floor((sysdate - to_date(decode(start_time,'// ::',to_char(sysdate,'dd/mm/yyyy
hh24:mi:ss'), start_time), 'dd/mm/yyyy hh24:mi:ss'))* 1440) Mins, EVENT,program, P1, P2, P3,hash_value
    from
        (SELECT s.sid sesid,s.serial# serial,s.program, t.sql_text, s.osuser,s.schemaname,s.machine, s.last_call_et, substr(last_load_time,9,2) || '/' || substr(last_load_time,6,2) ||'/' ||
substr(last_load_time,1,4) || ' ' ||
        substr(last_load_time,12,2) || ':' || substr(last_load_time,15,2) || ':' || substr(last_load_time,18,2) start_time, U.event, U.p1, U.p2, U.p3,
        V.spid,T.hash_value
        FROM v$session S,
        v$sql T,
        v$session_wait U,
        v$process V
        WHERE S.status = 'ACTIVE'
        AND S.sql_address = T.address
        and exists (select 1 from v$sqltext_with_newlines y where y.hash_value = S.sql_hash_value)
        and T.SQL_TEXT <> 'COMMIT'
        AND S.sql_hash_value = T.hash_value
        AND U.SID = S.SID
        and V.addr = S.paddr
        and S.type='USER'
        )
    )
order by LAST_CALL desc;

5) 

## To truncate all tables ###

1.      Select 'truncate TABLE schemaowner.'||table_name||'  ;' FROM dba_tables where owner='schemaowner';

           

2.     

## To move all the Tables and indexes from one TBS to another ##

 

1.      select 'ALTER TABLE schemaowner.'||table_name|| ' MOVE TABLESPACE  BI_EDW_DEV_DATA;' FROM dba_tables  where owner='schemaowner';

2.      select 'ALTER INDEX schemaowner.'||index_name||' REBUILD TABLESPACE BI_EDW_DEV_IDX ;' FROM DBA_INDEXES WHERE OWNER='schemaowner';

WARNING: Oracle executable binary mismatch detected.

WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages

This message was showing in my one of development database after performing some disabling Database Vault configurations.

Oracle recommends for this warning set _disable_image_check= true

what I did:

I bounce my database solved the issue. No message showing in alert log file.

*** As we dont have chance to bounce the production DB better to set the parameter