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)


Saturday, January 7, 2017

ORA-01950: no privileges on tablespace 'SYSAUX'

While taking a 12c database FULL export as sysdba and I got the below error

LINUX01:testdb:/opt/oracle/CB $ expdp directory=EMP_DIR dumpfile=testdb_FULL_dec14.dmp logfile-exp_testdb_FULL_dec14.log full=y exclude=statistics metrics=y

Export: Release 12.1.0.2.0 - Production on Wed Dec 14 15:14:16 2016

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

Username: / as sysdba

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
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA directory=EMP_DIR dumpfile=testdb_FULL_dec14.dmp logfile=exp_testdb_FULL_dec14.log full=y exclude=statistics metrics=y
Startup took 4 seconds
Estimate in progress using BLOCKS method...
>>> ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.00.00'); END;
ORA-01950: no privileges on tablespace 'SYSAUX'
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA


Solution:

To fix this issue just grant below privilege to the SYSMAN user

LINUX01:: testdb:/export/testdb $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 14:54:30 2016

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> alter user SYSMAN quota unlimited on sysaux;
User altered.

Rerunning export job again resolved the issue and it is SUCCESS!!!


Refer:

Datapump Export Fails with ORA-1950: "no privileges on tablespace" (Doc ID 1633698.1)


ORA-39166: Object SYS.AUD$ was not found


When trying to backup the SYS.AUD$ table using datapump for my oracle 11g database, this is what i'm getting below

Oradev01:corpqa:/export/corpqa $ expdp directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 15:31:15 2017

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SYS.AUD$ was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:31:18

Cause:

According to Oracle there is a restriction on dataPump export. It cannot export schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.


Solution:

Export the table SYS.AUD$ using the traditional export:

Oradev01: corpqa:/export/corpqa $ exp file=SYS_AUD_table.dmp log=exp_SYS_AUD_table.log tables=AUD$ statistics=none

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 16:24:40 2017

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


Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           AUD$  504728389 rows exported
Export terminated successfully without warnings.

Reference:
  
DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (Doc ID 553402.1)
How to Export the AUD$ Table (Doc ID 745540.1)



Auto Scroll Stop Scroll