Backup the Relational Database of Planning Application

Backup the Relational Database of Planning Application

Steps:   

• 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

Optional:

• 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

Note: 

• 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

References:

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php