It is needed to export/import full database. To export full database, "EXPORT FULL DATABASE" privilege is granted and to import full database "IMPORT FULL DATABASE" privilege is granted. Now while doing full database export/import, exp fails with EXP-00023 and expdp fails with
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
In the following example the error case is demonstrated.
A user named exp_user is created and it is granted EXPORT FULL DATABASE privilege.
E:\>sqlplus / as sysdbaFrom the above example, we see both exp and expdp operations have failed.
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 22 19:35:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user exp_user identified by exp_user;
User created.
SQL> grant create session, resource, export full database to exp_user;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee='EXP_USER';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
EXP_USER EXPORT FULL DATABASE NO
EXP_USER CREATE SESSION NO
EXP_USER UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='EXP_USER';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
EXP_USER RESOURCE NO YES
E:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log
Export: Release 10.2.0.1.0 - Production on Thu Jul 22 19:43:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00023: must be a DBA to do Full Database or Tablespace export
(2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user EXP_USER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EXP_USER
About to export EXP_USER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export EXP_USER's tables via Conventional Path ...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
E:\>expdp exp_user/exp_user full=y dumpfile=exp_dump.dmp logfile=exp_dump.log
Export: Release 10.2.0.1.0 - Production on Thursday, 22 July, 2010 19:51:21
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
Cause of the Problem
The system privileges "EXPORT FULL DATABASE" and "IMPORT FULL DATABASE" was introduced in oracle database 10gR1. But these two privileges are not currently in use by oracle. May be they will be implemented in future releases. In oracle 10g and 11g these two system privileges are not operational and hence assigning these privileges will do nothing.
The right privileges used by export/import are the roles EXP_FULL_DATABASE/ IMP_FULL_DATABASE.
Solution of the Problem
Assign correct privileges to the user. He who will do full database export operation assign him EXP_FULL_DATABASE role and he who will perform full database import operation assign him IMP_FULL_DATABASE role.
E:\>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 22 19:53:32 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> revoke export full database from exp_user;
Revoke succeeded.
SQL> grant exp_full_database to exp_user;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee = 'EXP_USER';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
EXP_USER CREATE SESSION NO
EXP_USER UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee = 'EXP_USER';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
EXP_USER EXP_FULL_DATABASE NO YES
EXP_USER RESOURCE NO YES
Now invoking exp and expdp with full=y went fine.
D:\>exp exp_user/exp_user full=y file=exp_dump.dmp log=exp_dump.log
Export: Release 11.1.0.6.0 - Production on Thu Jul 22 21:19:03 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
.
.
.
This worked for me, however I notice that the .dmp file was smaller when I run as the user I created vs the system user.
ReplyDeleteIm working on comparing what is different between the two different users doing a full backup.
Thanks, it works perfectly
ReplyDelete