Wednesday, February 22, 2017

ADD CREDENTIALSTORE goldengate 12c

The ADD CREDENTIALSTORE is a new command in Oracle GoldenGate 12c and the default location of the credential store is “$GG_HOME/dircrd” directory of the GoldenGate software home. Before 12c to hide the passwords from param files we need to encrypt the passwords and place them in all param files


Let’s add a user to credential store

GGSCI (oradev01) 3> DBLOGIN USERID GGADMIN, PASSWORD xxxxx
Successfully logged into database.

GGSCI (oradev01 as GGADMIN@testdb) 4> ADD CREDENTIALSTORE

Credential store created in ./dircrd/.

GGSCI (oradev01 as GGADMIN@testdb) 5> ALTER CREDENTIALSTORE ADD USER GGADMIN ALIAS ggadmin
Password:

Credential store in ./dircrd/ altered.

Now check the login with newly created alias name……..

GGSCI (oradev01 as GGADMIN@testdb) 6> dblogin useridalias ggadmin
Successfully logged into database.

oradev01:testdb:/opt/oracle/GG/home $ cd dircrd
oradev01:testdb:/opt/oracle/GG/home/dircrd $ ls -lrth
total 4.0K
-rw-r----- 1 oracle dba 517 Feb 22 22:55 cwallet.sso

From above we see that Auto Login wallet has been created

To verify:

GGSCI (oradev01) 1>  INFO CREDENTIALSTORE

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggadmin
  Userid: GGADMIN


Thursday, February 9, 2017

OGG-00369 Oracle GoldenGate Capture for Oracle: Error in token clause for TK_HOST.

GGSCI (sourceserver) 3>  view params

SETENV (ORACLE_SID=mydev)
userid ggadmin, password ****
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256)
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
EXTTRAIL /migrate/source/GG/home/dirdat/lt
DISCARDFILE  /migrate/source/GG/home/dirrpt/TEST.dsc, PURGE
--DDL INCLUDE MAPPED OBJNAME *.*;
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE  MYDEV.TEST,
TOKENS ( TK_HOST = @GETENV ("GGENVIRONMENT" , "HOSTNAME"),
TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"),
TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ),
TK_GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME"),
TK_COMMIT_TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TK_POS = @GETENV ("GGHEADER", "LOGPOSITION"),
TK_RBA = @GETENV ("GGHEADER", "LOGRBA"),
TK_TABLE = @GETENV ("GGHEADER", "TABLENAME"),
TK_OPTYPE = @GETENV ("GGHEADER", "OPTYPE"),
TK_BA = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"));


GGSCI (sourceserver) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     EDEV      00:42:32      00:01:14
EXTRACT     RUNNING     PHRDEV      00:00:00      00:00:02


Error:

2017-02-09 16:48:53  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit params EDEV.
2017-02-09 16:51:14  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, edev.prm:  MAP (TABLE) resolved (entry MYDEV.TEST): TABLE  "MYDEV"."TEST", TOKENS ( TK_HOST = @GETENV("GGENVIRONMENT" , "HOSTNAME"), TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"), TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ), TK_GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME"), TK_COMMIT_TS =@GETENV ("GGHEADER", "COMMITTIMESTAMP"), TK_POS = @GETENV ("GGHEADER", "LOGPOSITION"), TK_RBA = @GETENV ("GGHEADER", "LOGRBA"), TK_TABLE = @GETENV ("GGHEADER", "TABLENAME"), TK_OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), TK_BA = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR")).
2017-02-09 16:51:14  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, edev.prm:  No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-02-09 16:51:14  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, edev.prm:  Using the following key columns for source table MYDEV.TEST: ID, NAME.
2017-02-09 16:51:14  INFO    OGG-01298  Oracle GoldenGate Capture for Oracle, edev.prm:  Column function diagnostic message: could not find column "GGENVIRONMENT".
2017-02-09 16:51:14  ERROR   OGG-00369  Oracle GoldenGate Capture for Oracle, edev.prm:  Error in token clause for TK_HOST.
2017-02-09 16:51:14  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, edev.prm:  PROCESS ABENDING.


Solution:

The code in the 12c version is now changed the text enclosed within double quotes is treated as column name and the text enclosed within single quotes is treated as literal text, which used to be double quoted text pre 12c versions.

Now, change Double quotes to Single and start extract

GGSCI (sourceserver) 3> view params

SETENV (ORACLE_SID=mydev)
userid ggadmin, password ****
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256)
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
EXTTRAIL /migrate/source/GG/home/dirdat/lt
DISCARDFILE  /migrate/source/GG/home/dirrpt/TEST.dsc, PURGE
--DDL INCLUDE MAPPED OBJNAME *.*;
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE  MYDEV.TEST,
TOKENS ( TK_HOST = @GETENV ('GGENVIRONMENT' , 'HOSTNAME'),
TK_OSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME'),
TK_DBNAME = @GETENV('DBENVIRONMENT' , 'DBNAME' ),
TK_GROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
TK_COMMIT_TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
TK_POS = @GETENV ('GGHEADER', 'LOGPOSITION'),
TK_RBA = @GETENV ('GGHEADER', 'LOGRBA'),
TK_TABLE = @GETENV ('GGHEADER', 'TABLENAME'),
TK_OPTYPE = @GETENV ('GGHEADER', 'OPTYPE'),
TK_BA = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'));


Reference:
token with getenv bugs in 11.2.1 and 12.1 (Doc ID 1948440.1)


OGG-01031 There is a problem in network communication. Reply received is Output file is not in any allowed output directories

GG version:  12.2
Oracle DB:  12.1.0.2.0


2017-02-09 10:05:05  ERROR   OGG-01031  Oracle GoldenGate Capture for Oracle, edev.prm:  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file /migrate/target/GG/RT000000 is not in any allowed output directories.).
2017-02-09 10:05:05  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, edev.prm:  PROCESS ABENDING.


sourceserver:mydev:/migrate/source/GG/home $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (sourceserver) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EDEV      00:00:02      00:00:07
EXTRACT     RUNNING     PDEV      00:00:00      00:00:05


GGSCI (sourceserver) 3> view params PDEV

EXTRACT PDEV
SETENV (ORACLE_SID='mydev')
USERID ggadmin, password ****
RMTHOST targetserver, MGRPORT 7809
RMTTRAIL  /migrate/target/GG/RT
PASSTHRU
TABLE   MYDEV.TEST ;


Solution:

In order to solve this problem, add the parameter ALLOWOUTPUTDIR in the target system GLOBALS file.
The ALLOWOUTPUTDIR parameter was introduced in Oracle GoldenGate 12.2

Go to TARGET side and add “ALLOWOUTPUTDIR /migrate/target/GG” to GLOBALS file because we are not using “dirdat” (12.2 new feature)

GGSCI (targetserver) 1> edit params ./GLOBALS
GGSCHEMA ggadmin
ALLOWOUTPUTDIR /migrate/target/GG


reference:


OGG-01031 There is a problem in network communication. Reply received is Output file is not in any allowed output directories. (Doc ID 2095284.1)

Failed to enable DBOPTIONS SUPPRESSTRIGGERS.

GG version:  12.2
Oracle DB:  12.1.0.2.0

GGSCI (targetserver) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     RDEV      00:00:00      00:32:14


GGSCI (targetserver) 4> start RDEV

Sending START request to MANAGER ...
REPLICAT RDEV starting

Error:

2017-02-09 13:29:36  ERROR   OGG-06472  Oracle GoldenGate Delivery for Oracle, RDEV.prm:  Failed to enable DBOPTIONS SUPPRESSTRIGGERS.
2017-02-09 13:29:36  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RDEV.prm:  PROCESS ABENDING.


SOLUTION:

SQL> sho parameter spfile;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
spfile                               string                      /opt/oracle/product/12.1.0.2.64/dbs/spfileTDEV.ora

                                                                     
SQL>  sho parameter ENABLE_GOLDENGATE_REPLICATION

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
enable_goldengate_replication        boolean                          FALSE

SQL> alter system set ENABLE_GOLDENGATE_REPLICATION = TRUE scope = both;

System altered.

SQL> sho parameter ENABLE_GOLDENGATE_REPLICATION

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
enable_goldengate_replication        boolean                          TRUE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
targetserver:TDEV:/opt/oracle/product/12.1.0.2.64/dbs $ cd /opt/oracle/product/goldengate
targetserver:TDEV:/opt/oracle/product/goldengate $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (targetserver) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     RDEV      00:00:00      00:40:07


GGSCI (targetserver) 2> start RDEV

Sending START request to MANAGER ...
REPLICAT RDEV starting

GGSCI (targetserver as ggadmin@TDEV) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RDEV      00:00:00      00:41:34


Refer:
GoldenGate Replicat Abends: ERROR OGG-6472 Failed to Enable DBOPTIONS SUPPRESSTRIGGERS (Doc ID 1614302.1)

Friday, January 27, 2017

EXPDP from 12c Standby database

Its not possible to do a datapump export from a standby database because datapump writes to the master table because standby is not open for writes. To overcome this we need open the database in SNAPSHOT STANDBY mode (11g new feature) where the standby becomes read-write  mode. Now I’m going to open my standby database in snapshot standby mode and run the expdp job

Trying expdp from standby and let’s see what error it throws

expdp parfile=expdp_standby.par

Export: Release 12.1.0.2.0 - Production on Fri Jan 13 13:47:32 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-16000: database or pluggable database open for read-only access


oradev01:mydevdb:/bkp/mydevdb/u20 $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 24 11:30:31 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
---------- ----------
       200 199.854408

SQL> Show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /bkp/mydevdb/u200
db_recovery_file_dest_size           big integer 200G

Check the space in the recovery mount point

SQL> !df -h /bkp/mydevdb/u200
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg130-lvol11
                      473G  344G  105G  77% /bkp/mydevdb/u200

I’m increasing size for recovery destination from 200GB to 300GB just for safety

SQL> alter system set db_recovery_file_dest_size=300G;

System altered.

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
---------- ----------
       300 199.854408

SQL> set line 280
SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS  FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


no rows selected

SQL> SQL> SELECT name "Database name", status, ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name, (select status from v$instance) status,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYDEVDB    MOUNTED               1                 818900                818900          0
MYDEVDB    MOUNTED               1                 818900                818900          0

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> set timing on
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;


NAME      OPEN_MODE      GUARD_S   DATABASE_ROLE
--------- --------------------    -------   ----------------
MYDEVDB    MOUNTED              NONE    PHYSICAL STANDBY

Elapsed: 00:00:00.00

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Elapsed: 00:00:00.00
SQL> set time on

SQL> select to_Char(SCN),GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE,TIME, RESTORE_POINT_TIME,  NAME from  v$restore_point;

TO_CHAR(SCN)                             GUA STORAGE_SIZE TIME                              RESTORE_POINT_TIME                NAME
---------------------------------------- --- ------------ --------------------------------- --------------------------------- -------------------------------------------------------
212239961719                             NO             0 14-SEP-13 02.14.52.000000000 AM                                     DR_TO_091413
247526818319                             NO             0 23-AUG-14 01.52.55.000000000 AM                                     DR_TO_082214


11:35:34 SQL>  SELECT name "Database name", status, ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name, (select status from v$instance) status,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
MYDEVDB    MOUNTED               1                 818902                818902          0
MYDEVDB    MOUNTED               1                 818902                818902          0

Elapsed: 00:00:00.55

11:36:07 SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS  FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

no rows selected


11:38:28 SQL> select to_char(CURRENT_SCN) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
320833743962

Elapsed: 00:00:00.00

11:38:39 SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/24/2017 11:40:01'.
ORA-01153: an incompatible media recovery is active


Elapsed: 00:00:00.01

We need to STOP Recovery process to convert to snapshot standby

11:40:01 SQL> alter database recover managed standby database cancel;
Database altered.

Elapsed: 00:00:01.01

11:40:31 SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

Elapsed: 00:00:05.59


11:40:56 SQL>
11:40:56 SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

11:41:21 SQL> Startup
ORACLE instance started.

Total System Global Area 1.6106E+11 bytes
Fixed Size                  7653336 bytes
Variable Size            2.1475E+10 bytes
Database Buffers         1.3905E+11 bytes
Redo Buffers              529215488 bytes
Database mounted.
Database opened.

11:41:41 SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
MYDEVDB    READ WRITE           NONE    SNAPSHOT STANDBY

Elapsed: 00:00:00.00

SQL> select to_Char(SCN),GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE,TIME, RESTORE_POINT_TIME, NAME from  v$restore_point;
TO_CHAR(SCN)                             GUA STORAGE_SIZE TIME                              RESTORE_POINT_TIME                NAME
---------------------------------------- --- ------------ --------------------------------- --------------------------------- -------------------------------------------------------
320874468782                             YES   3221225472 24-JAN-17 11.48.49.000000000 PM                                     SNAPSHOT_STANDBY_REQUIRED_01/24/2017 23:48:49
212239961719                             NO             0 14-SEP-13 02.14.52.000000000 AM                                     DR_BR_TO_RN_091413
247526818319                             NO             0 23-AUG-14 01.52.55.000000000 AM                                     DR_BR_TO_RN_082214


11:41:42 SQL> select to_char(CURRENT_SCN) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
320833828719

Elapsed: 00:00:00.00

Now start the export job again…..

oradev01:mydevdb:/bkp/mydevdb $ expdp parfile=exp_scn_full.par
The Oracle base remains unchanged with value /opt/oracle

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 24 12:18:30 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
Directory created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export mydevdb Start Time: Tue Jan 24 12:18:30 EST 2017
Exporting mydevdb into /bkp/mydevdb/mydevdb_data.dmp

Export: Release 12.1.0.2.0 - Production on Tue Jan 24 12:18:30 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** parfile= exp_scn_full.par
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 317.2 GB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31693: Table data object "SYS"."KU$_USER_MAPPING_VIEW" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-31693: Table data object "SYS"."FGA_LOG$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               5.406 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           5.843 KB      12 rows
ORA-31693: Table data object "SYS"."AUDTAB$TBS$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_SENSITIVE_DATA" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_TSDP_POLICY_PROTECTION" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_ACE_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_HOST_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_WALLET_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC


SolutionAccording to oracle doc (Doc ID 1676411.1) we need to grant below

grant flashback any table to exp_full_database;
grant flashback on SYS.KU$_USER_MAPPING_VIEW to exp_full_database;
grant flashback on SYS.FGA_LOG$FOR_EXPORT to exp_full_database;
grant flashback on SYS.AUDTAB$TBS$FOR_EXPORT to exp_full_database;
grant flashback on SYS.DBA_SENSITIVE_DATA to exp_full_database;
grant flashback on SYS.DBA_TSDP_POLICY_PROTECTION to exp_full_database;
grant flashback on SYS.NACL$_ACE_EXP to exp_full_database;
grant flashback on SYS.NACL$_HOST_EXP to exp_full_database;
grant flashback on SYS.NACL$_WALLET_EXP to exp_full_database;


If you see below wallet error then open the wallet and run the expdp job again

ORA-28365: wallet is not open

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
    CON_ID
----------
FILE
/opt/oracle/admin/mydevdb/wallet
CLOSED                         UNKNOWN              SINGLE    UNDEFINED
         0


SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN identified by XXXXXXXX;
System altered.

Refer:
EXPDP - ORA-31693 ORA-01031 (Insufficient Privileges) On Some Tables When Exporting from 12cR1 (Doc ID 1676411.1)


Check here