export and import in oracle database using exp and imp.

export and import in oracle database using exp and imp.


-- Export


exp apps/apps@orcl FILE=E:\apps_exp.dmp LOG=E:\exp_apps.log

-- import

imp apps/apps@orcl file=E:\apps_exp.dmp LOG=E:\imp_apps.log  full=y

Creating Database Link in Orale 11G or 12c Database?

Creating Database Link in Orale 11G or 12c Database?


Before creating DB link first verify  remote database TNS name details are added into your database tnsnames.ora file.

VIS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VIS)
    )
  )






Example:  CREATE DATABASE LINK db_link_name CONNECT TO remote_db_username IDENTIFIED BY remote_db_password USING 'remove_db_sid';



CREATE DATABASE LINK appsr12 CONNECT TO apps IDENTIFIED BY apps USING 'vis';

How to kill Database session?

How to kill Database session?

Find SID and SERIAL# values from V$Session view (only admin users can access this table like SYSTEM, SYS..) or Admin role privileged users.

select sid,serial# from v$session where status='ACTIVE' and USERNAME=':MYUSER';

Use below command for killing running session.



ALTER SYSTEM KILL SESSION 'sid,serial#';
alter system kill session'368,169';

Note: Be care full while killing sessions in your database and verify SID and Serieal# values before killing.

ERROR at line 1: ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []

SQL> create user mrep identified by mrep;
create user mrep identified by mrep
                               *
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []

SOLUTION: Database need to be recovered using below command.


SQL> alter database recover database;

Database altered.

SQL> alter database open;

Database altered.

SQL> create user mrep identified by mrep;

User created.

ATG Upgrade error in BIAPPS 11.1.1.8.1 RA-01418: specified index does not exist

SEVERE : Error encountered executing SQL statement  FileName: 'C:\OBIEE\Oracle_BI1\biapps\admin\provisioning\update\11.1.
1.7.2\from11.1.1.7.0\schema\oracle\atg\upgrade.sql' LineNumber: '1985' Script log file: null
Jan 05 2015 01:18:45 SEVERE : Exception in runing sql script
oracle.sysman.assistants.common.dbutil.SQLFatalErrorException: java.sql.SQLException: ORA-01418: specified index does not exist

        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.onException(JDBCEngine.java:869)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:833)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:773)
        at oracle.sysman.assistants.common.dbutil.jdbc.OracleDropStatement.execute(ANSISQLStatementType.java:749)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeNextSQLStatement(JDBCEngine.java:1468)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.parseNexecuteScript(JDBCEngine.java:1372)
        at oracle.as.biapps.upgrade.util.DBUpgradeUtil.executeJDBCEngineScript(DBUpgradeUtil.java:117)
        at oracle.as.biapps.upgrade.atg.ATGSchemaUpgradeTask.execute(ATGSchemaUpgradeTask.java:46)
        at oracle.as.biapps.upgrade.atg.ATGSchemaUpgradeTask.execute(ATGSchemaUpgradeTask.java:15)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.executeTask(AbstractUpgradeCommand.java:78)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.executeUpgradeTasks(AbstractUpgradeCommand.java:68)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.upgrade(AbstractUpgradeCommand.java:28)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.execute(AbstractUpgradeCommand.java:21)
        at oracle.as.biapps.upgrade.BIAppsRepositoryUtil.main(BIAppsRepositoryUtil.java:47)
Caused by: java.sql.SQLException: ORA-01418: specified index does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1009)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.runSqlStatement(JDBCEngine.java:1090)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.callRunSqlStatement(JDBCEngine.java:779)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:794)
        ... 12 more


Solution:
------------
 edit update.sql file, available in
C:\OBIEE11G\Oracle_BI1\biapps\admin\provisioning\update\11.1.1.7.2\from11.1.1.7.0\schema\oracle\atg

issue with duplicate values in below table. due to this it is not allowing unique index.
CREATE UNIQUE INDEX  "FND_ISO_LANGUAGES_TL_U2" ON  "FND_ISO_LANGUAGES_TL" ("NAME", "LANGUAGE","ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;
  cannot CREATE UNIQUE INDEX; duplicate keys found

comment below lines before upgrading ATG and run those indexex manually in DEV_BIACOMP scema

line no 1985 to 2068

find the below code.

DROP INDEX FND_APPL_TAXONOMY_U1 ;
DROP INDEX FND_APPL_TAXONOMY_U2 ;
DROP INDEX FND_APPL_TAXONOMY_U3 ;
DROP INDEX FND_APPL_TAXONOMY_U4 ;
DROP INDEX FND_APPL_TAXONOMY_U5 ;
DROP INDEX FND_APPL_TAXONOMY_U6 ;
DROP INDEX FND_APPL_TAXONOMY_U8 ;
DROP INDEX FND_ISO_LANGUAGES_TL_U2 ;
DROP INDEX FND_SESSION_USERS_U1 ;
DROP INDEX FND_SESSION_USERS_U2 ;

 --- ('INDEX','FND_APPL_TAXONOMY_U1' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U1" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'PRODUCT_LINE',TO_CHAR("PRODUCT_LINE")||'#'||TO_CHAR("ALTERNATIVE_ID")||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U2' ) ---

  CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U2" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'FAMILY',TO_CHAR("PRODUCT_LINE")||'#'||TO_CHAR("ALTERNATIVE_ID")||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U3' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U3" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'APPLICATION',TO_CHAR("PRODUCT_LINE")||'#'||TO_CHAR("ALTERNATIVE_ID")||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U4' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U4" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'PRODUCT_LINE',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_KEY"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U5' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U5" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'FAMILY',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_KEY"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U6' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U6" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'APPLICATION',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_KEY"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL), "MODULE_ID","ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

--- ('INDEX','FND_APPL_TAXONOMY_U8' ) ---

CREATE BITMAP INDEX  "FND_APPL_TAXONOMY_U8" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'APPLICATION',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_NAME"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL), "MODULE_ID","ENTERPRISE_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_NATURAL_LANGUAGES_TL_U2' ) ---


/* 
CREATE UNIQUE INDEX  "FND_ISO_LANGUAGES_TL_U2" ON  "FND_ISO_LANGUAGES_TL" ("NAME", "LANGUAGE","ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;*/
--- ('INDEX','FND_SESSION_USERS_U2' ) ---


  CREATE UNIQUE INDEX  "FND_SESSION_USERS_U2" ON  "FND_SESSION_USERS" ("USER_NAME", "ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255
  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_SESSION_USERS_U1' ) ---


  CREATE UNIQUE INDEX  "FND_SESSION_USERS_U1" ON  "FND_SESSION_USERS" ("USER_GUID", "ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255
  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

Unable to lock Central Inventory. OPatch will attempt to re-lock

Unable to lock Central Inventory. OPatch will attempt to re-lock


 OiiolLogger.addFileHandler:Error while adding file handler - C:\Program Files\Oracle\Inventory/logs\OPatch2015-06-18_12-24-59-PM.logjava.io.FileNotFoundException: C:\Program Files\Oracle\Inventory\logs\OPatch2015-06-18_12-24-59-PM.log (Access is denied)Unable to lock Central Inventory.  OPatch will attempt to re-lock.Do you want to proceed? [y|n]Y
(auto-answered by -silent)User Responded with: Y

OPatch will sleep for few seconds, before re-trying to get the lock...

It is an issue with privileges.


Solution:
-----------
 In Windows:  C:\Program Files\Oracle\Inventory\ContentsXML
 right click on directory=>properties=>Security=>select full control for your windows users.
In Unix or Linux:   /oracle/oraInventory/ContentsXML/

we need to grant  777  (read , write and execute privileges) to ContextsXML

BI Publisher error : Server not initialized. Please make sure the repository is ready

BI Publisher error : Server not initialized. Please make sure the repository is ready

Solution:
----------
 MW_HOME\user_projects\domains\bifoundation_domain\config\bipublisher

Edit  xmlp-server-config.xml file add below two lines and if already exists verify both repository path.


<?xml version="1.0" encoding="UTF-8" standalone="no"?><xmlpConfig xmlns="http://xmlns.oracle.com/oxp/xmlp">
  
   <resource>
      <file path="${xdo.server.config.dir}/repository"/>
   </resource>
   <config>
      <file path="${xdo.server.config.dir}/repository"/>
   </config>
</xmlpConfig>

ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_DATAPUMP" error

ORA-31626: job does not exist
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1470
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 5887
 ORA-06512: at line 1

Solution:

----------

there are different situation we will get this error.

1) If your database user dont have access like execute privileges for DBMS_DATAPUMP 

   package we will get this error

Sol: grant execute privileges to user.

      SQL> conn SYS as SYSDBA

      SQL> grant execute on DBMS_DATAPUMP to username;

2) IF previous jobs are not finished or idle state then we will get this error.

    using below query we can verify existing jobs.

SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs;
  
OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
SYS        BIAPPSDBJOB          IMPORT     FULL       NOT RUNNING   0

drop table using

SQL>  DROP TABLE BIAPPDBJOB;

verify it is available in recyclebin using above query again, then  purge using like below.

SQL> PURGE TABLE BIAPPDBJOB;