Oracle的数据库备份与还原

创建备份存放目录，并对指定MICS方案进行全量备份
mkdir -p /u01/app/mics_$(date +%Y%m%d) && expdp mics/GZL13mics@micsocc schemas=mics dumpfile=mics_backup_$(date +%Y%m%d).dmp logfile=mics_backup_$(date +%Y%m%d).log directory=ORACLE_BASE

删除原有用户前检查会话

SELECT s.sid, s.serial# FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.username = 'MICS';

-- 替换为实际查询到的SID和SERIAL#

ALTER SYSTEM KILL SESSION 'sid,serial#';

重建用户

CREATE USER MICS IDENTIFIED BY GZL13mics DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;

赋权

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE TRIGGER, CREATE SEQUENCE TO MICS;

GRANT CREATE PUBLIC DATABASE LINK TO MICS;

 GRANT EXECUTE ON sys.dbms_lock, SELECT ON sys.v_$session TO MICS; 

--重建以后分批导入数据对象

仅导入元数据
impdp MICS/GZL13mics@MICSOCC schemas=mics dumpfile=expdp_mics_central_20250625202105.dmp logfile=import_structure.log directory=ORACLE_BASE content=METADATA_ONLY  transform=OID:N exclude=USER,TRIGGER,JOB,REFRESH_GROUP,PROCEDURE,FUNCTION
仅导入数据

impdp MICS/GZL13mics@MICSOCC schemas=mics dumpfile=expdp_mics_central_20250625202105.dmp logfile=import_data.log directory=ORACLE_BASE content=DATA_ONLY transform=OID:N table_exists_action=TRUNCATE exclude=USER,TRIGGER,JOB,REFRESH_GROUP,PROCEDURE,FUNCTION

 

  仅导入存储过程，函数，视图，包，自定义类

impdp MICS/GZL13mics@MICSOCC schemas=mics dumpfile=expdp_mics_central_20250625202105.dmp logfile=import_PROC.log directory=ORACLE_BASE content=METADATA_ONLY  transform=OID:N include=PROCEDURE,FUNCTION,PACKAGE,TYPE

仅导入触发器

impdp MICS/GZL13mics@MICSOCC schemas=mics dumpfile=expdp_mics_central_20250625202105.dmp logfile=import_TRIGGER.log directory=ORACLE_BASE content=METADATA_ONLY  transform=OID:N include=TRIGGER