How do i backup and restore oracle database without RMAN utility step by step

Summary: Hi! We know that there is different type of backup utility of oracle database. In this tutorial you will learn how to export and import an oracle database without RMAN using OS command prompt or PLSQL. You can use any one.

           First of all we should take a backup of our database

 Then stop all running application like web logic server, business intelligent etc.

      Go to run and open command prompt by cmd. then change the path as below to which is your database home directory path.

            C:\Users\Administrator> D: app\Administrator\product\11.2.0\dbhome_1\BIN\

Now execute below command to take a backup

 D:\app\Administrator\product\11.2.0\dbhome_1\BIN>expdp ucbcap/password@bdfc directory=ucbcap_dir dumpfile=thisisnameofbackup.dmp schemas=ucbcapl

ucbcap = user,password =user password,@bdfc=instance , ucbcap=home directory, ucbcap=schema name

Output sample is below. We mentioned some rows to clear concept.

;;;

Export: Release 11.2.0.1.0 - Production on Sun May 10 23:44:53 2020

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "UCBCAP"."SYS_EXPORT_SCHEMA_50":  UCBCAP/********@ilcl directory=ucbcap_dir dumpfile=thisisfilename.dmp schemas=UCBCAP

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.344 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

|

Dump file set for UCBCAPL.SYS_EXPORT_SCHEMA_50 is:

  D:\ORACLEDB\UCBCAP_BASE\ADMIN\BDFC\DPDUMP\DUMP\thisisfilename.DMP

Job "UCBCAP"."SYS_EXPORT_SCHEMA_50" successfully completed at 23:47:42

 

After complete the process the backup file will be store to the following path

 

        D:\oracledb\ucbcap_base\admin\ucbcapl\dpdump\dump (check out the path according to your instance.)

     Now, we will do the following tasks to restore the backup database.

Login as sysdba user to sysdba. We are using PLSQL to drop and create user and assign the previledge to user etc. noted you can use any one like SQL plus, SQL developer, plsql

After login execute the following commands

 

drop user ucbcap cascade;

drop user ucbcap$LOG cascade;

create user ucbcap identified by (your password) default tablespace UCBCAP_SYSTEM_TS;

grant dba to ucbcap;

grant execute on DBMS_LOCK to ucbcap;

grant execute on DBMS_crypto to ucbcap;

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Go to RUN again and open command prompt by cmd. then change the path as earlier to which is path of home directory and get to the BIN folder absolutely.

            C:\Users\Administrator> D: app\Administrator\product\11.2.0\dbhome_1\BIN\

Now execute the command.

      D:\app\Administrator\product\11.2.0\dbhome_1\BIN>impdp ucbcap/password@bdfc directory=UCBCAP_DIR dumpfile=thisisfilenameofbackup.DMP       schemas=ucbcapl remap_schema=ucbcapl:ucbcap

Note:  ucbcap = user,password =user password,@bdfc=instance , ucbcap=home directory, ucbcapl=schema name

Output sample is below. All data will be import one by one. Here, we mentioned some rows that are just to clear concept. it take a minimum time to complete the process

;;;

Import: Release 11.2.0.1.0 - Production on Wed Mar 25 15:52:41 2020

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Master table "UCBCAP"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "UCBCAP"."SYS_IMPORT_SCHEMA_01":  ucbcap/********@bdfc directory=UCBCAP_DIR dumpfile=UCBCAPL_LIVE2020_03_25_14_47_26.DMP schemas=ucbcapl remap_schema=ucbcapl:ucbcap

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"ucbcap" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "UCBCAP"."INSTRUMENT_BALANCE_HIST"        358.1 MB 5296187 rows

. . imported "UCBCAP"."PS_TXN"                         140.9 MB   18120 rows

. . imported "UCBCAP"."IMPORT_EXT_FILES"               99.28 MB    4483 rows

. . imported "UCBCAP"."GL_MASTER_HIST"                 63.12 MB  996988 rows

Login as alpha_live database

Execute reverse and take a time to complete it.

begin pkg_sys_log.create_log_schema; end;

Restoration done.  Run all application and check now. If you show any error mention to the comments please.