Saturday, September 26, 2020
dbametrix
More
    Home Oracle DBA Tips Recover database from dump File

    Recover database from dump File

    Detail explanation and step by step guide for how to recover database using import dump file manually.

    How To Recover Database From Dumpfile

    Sometimes we only have oracle dump as only backup available and it looks easy to restore the entire database and it’s easy to but it requires to follow
    restoration steps in a sequential manner otherwise restoration will end with a bunch of errors.

    BACKUP USE : full_dump.dmp

    - Advertisement -
    dbametrix

    Create tablespace with minimum size by deriving tablespace DDL from the below statement as any database needs tablespace first using the following commands…

    impdp system/password FULL=Y include=TABLESPACE directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_tablespace.log sqlfile=full_dump_tablespace.sql

    Create profiles by deriving profile DDL from the below statement…

    impdp system/password FULL=Y include=PROFILE directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_profile.log sqlfile=full_dump_profile.sql

    - Advertisement -
    dbametrix

    Create a user by deriving user DDL from the below statement…

    impdp system/password FULL=Y include=USER directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_user.log sqlfile=full_dump_user.sql

    Create a role by deriving role DDL from the below statement…

    impdp system/password FULL=Y include=ROLE directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_role.log sqlfile=full_dump_role.sql

    System grant by deriving system_grant DDL from the below statement…

    impdp system/password FULL=Y include=SYSTEM_GRANT directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_system_grant.log sqlfile=full_dump_system_grant.sql

    Grant roles by deriving role_grant DDL from the below statement…

    impdp system/password FULL=Y include=ROLE_GRANT directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_role_grant.log sqlfile=full_dump_role_grant.sql

    Create dblink (system and public user’s link)…

    impdp system/password FULL=Y include=DB_LINK directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_dblink.log sqlfile=full_dump_dblink.sql

    Now Recreate Materialized View…

    impdp system/password FULL=Y include=MATERIALIZED_VIEW directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_mv.log sqlfile=full_dump_mv.sql

    After the above step, you should need to recreate Refresh Group…

    impdp system/password FULL=Y include=REFRESH_GROUP directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_refresh_grp.log sqlfile=full_dump_refresh_grp.sql

    Now Import data for all schema (Import data for the full database)

    impdp system/password FULL=Y directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump.log

    Grant privileges (Import grant for the full database)…

    impdp system/password INCLUDE=GRANT Full=Y directory=DATA_PUMP_DIR dumpfile=full_dump.dmp logfile=full_dump_ALL_schema_grant.log

    Finally, Count objects if you would miss anything checking and investigating.

    select c.OWNER,c.object_type,count(1)
    from dba_objects c
    where 1=1 and c.CREATED < trunc(sysdate)
    and c.owner in (select username from DBA_USERS where default_tablespace not in ('SYSAUX','SYSTEM'))
    group by OWNER,object_type
    order by

    owner,object_type;

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments