Sunday, 22 December 2013

Backup the Relational Database of Planning Application

Backup the Relational Database of Planning Application

  • Open the cmd prompt
  • Connect to Oracle
  • Sqlplus / as sysdba
  • Unlock the schema account which we are going to export
  • Alter user plan1 identified by password account unlock
  • Create a Directory
  • Create or replace directory TEST_DIR1 as 'D:\app_db\Administrator\oradata\TEST_DIR1';
  • Grant the access to that directory
  • Grant read, write on directory TEST_DIR1 to plan1
  • Export the plan1 schema
      • expdp plan1/password@EPMDB schemas=plan1directory=TEST_DIR1 dumpfile=test.dmp logfile=expdptest.log

  • Create a user
      • Create user test identified by password;
  • Grant access to that user
      • Grant connect, resource to test;
  • Grant access for export and import to that user
      • Grant exp_full_database, imp_full_database to test;
  • Import the schema with different name (remap_schema) from existing dump file
      • impdp test/password@EPMDB schemas=plan1 directory=TEST_DIR1 remap_schema=plan1:test1234 dumpfile=test.dmp logfile=impdptest.log

  • No need to create the target schema manually, the impdp command itself will create that (new_schema name should be mentioned in remap_schema) with same Password what we have mentioned for the source schema.
  • With the same db users or different db users, we can export and import the dumpfiles.(prefer same db users to avoid confusion).
  • The user who are used to import or export the dump file should have exp_full_database & imp_full_database roles.
  • Expdp & Impdp command should be executed at OS level not DB level


No comments:

Post a Comment