rescuing E1local from a complete reinstall

We’ve all had it, after a package install, you cannot connect to e1local!  Arrgghh!

Some clients get it more than others, it seems that virus scanning and other specifics about the client (taking VM snaps) are killing all of the oracle databases at once.  They seem to be unrecoverable.

C:\Oracle\diag\rdbms\e1local\e1local\alert\log.xml

ocal_ora_98068.trc:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\E920\DV920\SPEC\SPEC_DV7012000.DBF'
</txt>
</msg>
<msg time='2017-0

And jde.log

74644/74648 MAIN_THREAD                           Thu Mar 23 15:36:51.370000    jdb_ctl.c4199
    Starting OneWorld

74644/74648 MAIN_THREAD                           Thu Mar 23 15:37:00.529000    dbinitcn.c929
    OCI0000065 - Unable to create user session to database server

74644/74648 MAIN_THREAD                           Thu Mar 23 15:37:00.530000    dbinitcn.c934
    OCI0000141 - Error - ORA-01033: ORACLE initialization or shutdown in progress
 
74644/74648 MAIN_THREAD                           Thu Mar 23 15:37:00.530001    dbinitcn.c542
    OCI0000367 - Unable to connect to Oracle ORA-01033: ORACLE initialization or shutdown in progress
 
74644/74648 MAIN_THREAD                           Thu Mar 23 15:37:00.530002    jdb_drvm.c794
    JDB9900164 - Failed to connect to E1Local

This is painful.

Make sure that the current OS user is a member of the highlighted group below

Make sure you are using the server based SQLPlus after changing the security in sqlnet.ora to be NTS (see previous post)

C:\Oracle\E1Local\BIN\sqlplus.exe
C:\Oraclient\product\12.1.0\client_1\BIN\sqlplus.exe

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 23 15:39:25 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             381682384 bytes
Database Buffers          415236096 bytes
Redo Buffers                5337088 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\E920\DV920\SPEC\SPEC_DV7012000.DBF'

SQL> alter database datafile 'C:\E920\DV920\SPEC\SPEC_DV7012000.DBF' offline drop ;

Database altered.

Then impdp your datafile, you need a user to be able to do this, I created jdeupg

sqlplus / as sysdba

SQL> create user jdeupg identified by myP@55# ;

User created.

SQL> grant dba to jdeupg
  2  ;

Grant succeeded.

SQL> quit

Then at the command line

C:\Windows\system32>impdp jdeupg/myP@55# TRANSPORT_DATAFILES='C:\E920\DV920\spec\spec_dv7012000.dbf' DIRECTORY=PKGDIR DUMPFILE='spec_dv7012000.dmp' REMAP_TABLESPACE=SPEC__DV7012000:SPEC_DV7012000 REMAP_SCHEMA=SPEC__DV7012000:SPEC_DV701200
0 LOGFILE='impspec_dv7012000.log'

now, you need to be careful about the database directory and how this was last set.  It’ll be set to the location of the last full package.  You need to set this for the file that you are trying to rescue, in my case DV920\spec

select * form all_directories:

ORACLE_HOME    /
ORACLE_BASE    /
OPATCH_LOG_DIR    C:\Oracle\E1Local\QOpatch
OPATCH_SCRIPT_DIR    C:\Oracle\E1Local\QOpatch
OPATCH_INST_DIR    C:\Oracle\E1Local\OPatch
DATA_PUMP_DIR    C:\Oracle/admin/e1local/dpdump/
XSDDIR    C:\Oracle\E1Local\rdbms\xml\schema
XMLDIR    C:\Oracle\E1Local\rdbms\xml
ORACLE_OCM_CONFIG_DIR    C:\Oracle\E1Local/ccr/state
ORACLE_OCM_CONFIG_DIR2    C:\Oracle\E1Local/ccr/state
PKGDIR    C:\E920\UA920\data\

drop tablespace SPEC_DV7012000 including contents, when in sqlplus:

select * from all_directories ;
drop directory PKGDIR;
create  directory PKGDIR as 'C:\E920\DV910\spec ;

Note that you might need to delete the previous imp and exp log files too, I was getting:  These are .logs in the directory (DV920\spec) that you are importing to.

C:\E920\DV920\spec>impdp jdeupg/PAss# TRANSPORT_DATAFILES='C:\E920\DV920\spe
c\spec_dv7012000.dbf' DIRECTORY=PKGDIR DUMPFILE='spec_dv7012000.dmp' REMAP_TABLE
SPACE=SPEC__DV7012000:SPEC_DV7012000 REMAP_SCHEMA=SPEC__DV7012000:SPEC_DV7012000
LOGFILE='impspec_dv7012000.log'

Import: Release 12.1.0.2.0 - Production on Thu Mar 23 16:21:36 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Finally I have my ducks lined up, time to run the import

Starting "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01":  jdeupg/******** TRANSPORT_DATA
FILES='C:\E920\DV920\spec\spec_dv7012000.dbf' DIRECTORY=PKGDIR DUMPFILE='spec_dv
7012000.dmp' REMAP_TABLESPACE=SPEC__DV7012000:SPEC_DV7012000 REMAP_SCHEMA=SPEC__
DV7012000:SPEC_DV7012000 LOGFILE='impspec_dv7012000.log'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'SPEC_DV7012000' already exists

Job "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Thu Mar
23 16:22:07 2017 elapsed 0 00:00:10

Crappo, need to drop this

sqlplus / as sysdba

drop tablespace SPEC_DV7012000 including contents ;

Go again:

Starting "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01":  jdeupg/******** TRANSPORT_DATA
FILES='C:\E920\DV920\spec\spec_dv7012000.dbf' DIRECTORY=PKGDIR DUMPFILE='spec_dv
7012000.dmp' REMAP_TABLESPACE=SPEC__DV7012000:SPEC_DV7012000 REMAP_SCHEMA=SPEC__
DV7012000:SPEC_DV7012000 LOGFILE='impspec_dv7012000.log'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19721: Cannot find datafile with absolute file number 13 in tablespace SPEC_
DV7012000

Job "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Thu Mar
23 16:26:29 2017 elapsed 0 00:00:03

Damn, this means that my file is truly is corrupt.  Right, grab a fresh one from the deployment server spec directory.

C:\E920\DV920\spec>impdp jdeupg/Pass# TRANSPORT_DATAFILES='C:\E920\DV920\spe
c\spec_dv7012000.dbf' DIRECTORY=PKGDIR DUMPFILE='spec_dv7012000.dmp' REMAP_TABLE
SPACE=SPEC__DV7012000:SPEC_DV7012000 REMAP_SCHEMA=SPEC__DV7012000:SPEC_DV7012000
LOGFILE='impspec_dv7012000.log'

Import: Release 12.1.0.2.0 - Production on Thu Mar 23 16:29:31 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
Master table "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01":  jdeupg/******** TRANSPORT_DATA
FILES='C:\E920\DV920\spec\spec_dv7012000.dbf' DIRECTORY=PKGDIR DUMPFILE='spec_dv
7012000.dmp' REMAP_TABLESPACE=SPEC__DV7012000:SPEC_DV7012000 REMAP_SCHEMA=SPEC__
DV7012000:SPEC_DV7012000 LOGFILE='impspec_dv7012000.log'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "JDEUPG"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Mar 23
16:30:15 2017 elapsed 0 00:00:43

Working, and I can log into JDE.  What a saga.  But, now that I have the knowledge, this is going to save me time going forward.