How to restore dump file in Oracle 12c

Question

I want to restore a database in oracle 12c. I have done something for it but while importing data I got some errors about non-existing users in db.Because the database needs some users which must defined in oracle 12c. I created users for db which db needs but in oracle 12 creating user system was changed about session issues. I had to put c## expression in front username and problem still persists. I tried different methods to restore but it wasn't imported all tables and data. I think it's about the user problem.My question how can i create a user same as with the backup db user and restore database.

Answer 1

Step One: create pdbuser

1. Run Sqlplus sys/ as sysdba , (if has password you should type it next)

2. run alter session set container=pdbDBName---; (eg:pdborcl)

3. run create user test identified by password default tablespace users temporary tablespace temp;

4. run grant pdb_dba to test;

5. run grant dba to test;

Step two: Add pdbname in the tns file

1. run CMD

2. run tnsping cdbDB (eg: tnsping orcl)

3. find tns path and open tnsnames.ora file( if not exist make it in same directory of current file)

4. add pdbDBName like cdbDBName(eg:orcl) see below example

example:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = orcl) 
    ) 
  ) 

pdbORCL =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
    (CONNECT_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE_NAME = pdborcl)
    )
  )

Step three: Check tnsping from command prompt

1. Run CMD

2. tnsping pdbDB(eg: tnsping pdborcl)

3. see tns status. it should be ok with path details.

Step Four connect to pdbDB

1. Run Sqlplus

2. test/password@pdborcl-- do not use semicolon. now you are connected to pdb database

Step Five: Import Dump file

1. Run CMD

2. Type imp test/password@pdborcl file=myfile.dump now it should start importing process

Note : if pluggable database is on shutdown mode. mount it to open mode before starting import process

3. Run Sqlplus

4. connect with sys/ as sysdba (it will connect to cdbdatabase)

5. alter pluggable database all open

Answer 2

Based on OP's input via answer to my comments :

/ AS SYSDBA will by default connect to CDB and not your local PDB. There are many ways, however, as of now, you need to first connect to your local PDB as:

SYS@<pdb service_name> AS SYSDBA

Then execute your CREATE USER and other commands :

CREATE USER HASTANE IDENTIFIED BY HASTANE DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; 
GRANT DBA TO HASTANE; 
IMP HASTANE/HASTANE FILE=c:\VERI.DMP FULL=Y

Answer 3

First learn something about that database. Here is a document about usermanagement in 12c Administering User Accounts and Security The DBA essentials in general is a very good starting point

You are looking in the root of a container database and you need to create your users in a local PDB and import your data in there. Easiest is to create a tnsalias for that pdb that points to the specific PDB service_name for the import.

Answer 4

IMPORT PROCEDURE For importing full DB dump into 12c database. Assuming that during DB installation you have created container and one pluggable DB (pd1).

GO to SQL PLUS

CONN / AS SYSDBA
CONN system/syspassword@//localhost:1521/pd1 # connected to pluggable DB 
CREATE USER pdusr IDENTIFIED BY pdpass3344;
CREATE OR REPLACE DIRECTORY impdir AS 'C:\app\oracle\oradata\impdir';
GRANT READ, WRITE ON DIRECTORY impdir TO pdusr;
GRANT DBA,CONNECT TO pdusr; # granted permissions to pd1 DB because we are connected to mrmspd

Exit sql plus, open CMD

impdp system/syspassword@//localhost:1521/pd1 full=Y directory=impdir dumpfile=expdat.dmp logfile=impdp_mrms.log

And make sure you put expdat.dmp in C:\app\oracle\oradata\impdir