一、描述
最近工作中遇到一个案例,客户9i数据库系统,由于主机宕机,主机无法启动,只有exp的备份文件,在我们不知道表空间名称和业务用户的情况下需要对数据进行恢复。
二、操作
1、找到exp备份文件,要求备份文件不要备份的本地,找一台备份机。
2、通过strings命令可以得知创建表空间和用户的脚本。
3、可以按照用户导入,也可以全库导入,可以忽略告警
4、不建议使用vi打开几十G的备份文件。
exp文件内容如下:
<roidb2:orcl2:/home/oracle>$strings expdat.dmp |more
TEXPORT:V11.02.00
DSYSTEM
RENTIRE
8192
Fri May 25 21:15:4 2018expdat.dmp
#G#G
#G#G
+00:00
BYTE
UNUSED
INTERPRETED
DISABLE:ALL
METRICSE
BEGINSYS
CONNECT SYSTEM
METRICSEtablespace definitions
CREATE TABLESPACE "SYSAUX" BLOCKSIZE 8192 DATAFILE '+DATADG/orcl/datafile/sysaux.257.954803993' SIZE 775946240 REUSE AUT
OEXTEND ON NEXT 10485760 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMEN
T AUTO
CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE '+DATADG/orcl/datafile/undotbs1.258.954803993' SIZE 104857600
REUSE AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL
CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE '+DATADG/orcl/tempfile/temp.263.954804133' SIZE 139460608 REU
SE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE '+DATADG/orcl/datafile/users.259.954803993' SIZE 962068480 REUSE AUTOE
XTEND ON NEXT 1310720 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT A
UTO
CREATE UNDO TABLESPACE "UNDOTBS2" BLOCKSIZE 8192 DATAFILE '+DATADG/orcl/datafile/undotbs2.264.954804283' SIZE 52428800 R
EUSE AUTOEXTEND ON NEXT 26214400 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL
METRICEE 5
METRICSEprofiles
CREATE PROFILE "MONITORING_PROFILE" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER DEFAULT CPU_PER_SESSION DEFAULT CPU_P
ER_CALL DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TIME DEFAULT P
RIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME DEFAULT PASSWORD_REUSE_TIME DEFAULT PASSWORD_REUSE_
MAX DEFAULT PASSWORD_LOCK_TIME DEFAULT PASSWORD_GRACE_TIME DEFAULT
CREATE PROFILE "SESS" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER 2 CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGI
CAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TIME DEFAULT PRIVATE_SGA DEFAULT F
AILED_LOGIN_ATTEMPTS DEFAULT PASSWORD_LIFE_TIME DEFAULT PASSWORD_REUSE_TIME DEFAULT PASSWORD_REUSE_MAX DEFAULT PASSWORD_L
OCK_TIME DEFAULT PASSWORD_GRACE_TIME DEFAULT
METRICEE 7
METRICSEuser definitions
ALTER USER "SYS" IDENTIFIED BY VALUES '8A8F025737A9097A' TEMPORARY TABLESPACE "TEMP"
ALTER USER "SYSTEM" IDENTIFIED BY VALUES '2D594E86F93B17A1' TEMPORARY TABLESPACE "TEMP"
CREATE USER "OUTLN" IDENTIFIED BY VALUES '4A3BA55E08595C81' TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "ORDDATA" IDENTIFIED BY VALUES 'A93EC937FCD1DC2A' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP" PAS
SWORD EXPIRE ACCOUNT LOCK
CREATE USER "OLAPSYS" IDENTIFIED BY VALUES '4AC23CC3B15E2208' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP" PAS
SWORD EXPIRE ACCOUNT LOCK
CREATE USER "MDDATA" IDENTIFIED BY VALUES 'DF02A496267DEE66' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSW
ORD EXPIRE ACCOUNT LOCK
CREATE USER "SPATIAL_WFS_ADMIN_USR" IDENTIFIED BY VALUES '7117215D6BEE6E82' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPA
CE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "SPATIAL_CSW_ADMIN_USR" IDENTIFIED BY VALUES '1B290858DD14107E' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPA
CE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "SYSMAN" IDENTIFIED BY VALUES '2CA614501F09FCCC' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP"
CREATE USER "MGMT_VIEW" IDENTIFIED BY VALUES '2272C972949F3455' TEMPORARY TABLESPACE "TEMP"
CREATE USER "FLOWS_FILES" IDENTIFIED BY VALUES 'A615F72D8D4B7A3D' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "APEX_PUBLIC_USER" IDENTIFIED BY VALUES '3C91E6A20F0770EC' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "T
EMP" PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "APEX_030200" IDENTIFIED BY VALUES '059E9CA418F39DED' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "OWBSYS" IDENTIFIED BY VALUES '46FFE7B1869A6388' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP" PASS
WORD EXPIRE ACCOUNT LOCK
CREATE USER "OWBSYS_AUDIT" IDENTIFIED BY VALUES '3378320729DADFE1' DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP
" PASSWORD EXPIRE ACCOUNT LOCK
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWO
RD EXPIRE
CREATE USER "TEST" IDENTIFIED BY VALUES '7A0F2B316C212D67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
CREATE USER "ROIDBA" IDENTIFIED BY VALUES '53DD2C8A4A014988' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFI
LE "SESS"
CREATE USER "ROIDB" IDENTIFIED BY VALUES '707608A65CE9B1F4' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
METRICEE 26
转载于:https://blog.51cto.com/roidba/2121071