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
Solution: According 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)
0 comments:
Post a Comment