Using expdp To Export From Physical Standby Database

In this article, we will discuss the steps to use expdp on physical standby database. There may be some situations where you don’t want your production database loaded with the expdp operation. The other option you have in a standby setup is that you can use your standby database for export operations. But in a physical standby database, the database is in recovery mode and if we want to open the database for expdp operation, we can open the physical standby database in read only mode. Expdp operation needs to create and maintain a master table which needs requires the physical standby database opened in read write mode.

So we have an option in expdp to export standby database. We can use expdp parameter NETWORK_LINK from any non standby database to export database.

Verify primary and Standby Databases

Cancel recovery and Open standby database in Read only Mode

Create a physical directory on primary server to keep export dump files

Create DB Link, Oracle Directory and test it.

Execute expdp on primary server.

1. Verify primary and Standby Databases :

On Primary :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      orcl                           PRIMARY          TO STANDBY

SQL> arcvhive log list
SP2-0734: unknown command beginning "arcvhive l..." - rest of line ignored.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     522
Next log sequence to archive   524
Current log sequence           524
SQL>

On Standby :

SQL> select name,user,database_role,switchover_status from v$database;

NAME      USER                           DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      SYS                            PHYSICAL STANDBY NOT ALLOWED

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     522
Next log sequence to archive   0
Current log sequence           524
SQL>

2. Cancel recovery and Open standby database in Read only Mode :

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
sbyorcl          MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
sbyorcl          OPEN

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL>

3. Create a physical directory on primary server to keep export dump files :

[oracle@dgaskmpri01 ~]$ mkdir /tmp/askm
[oracle@dgaskmpri01 ~]$ chmod 777 /tmp/askm
[oracle@dgaskmpri01 ~]$

4. Create DB Link, Oracle Directory and test it :

[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 04:25:18 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn system/xxxxx
Connected.
SQL> create database link db_sbyorcl connect to system identified by oracle using 'sbyorcl';

Database link created.

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
orcl

SQL> select db_unique_name from v$database@db_sbyorcl;

DB_UNIQUE_NAME
------------------------------
sbyorcl

SQL> create directory sby_dump_dir as '/tmp/askm';

Directory created.

SQL>

5. Execute expdp on primary server :

[oracle@dgaskmpri01 askm]$ expdp system/oracle full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log

Export: Release 11.2.0.2.0 - Production on Tue Nov 22 04:50:05 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 183.1 MB
Processing object type DATABASE_EXPORT/TABLESPACE

.....
.....
....

ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('OE',0,1,'11.02.00.00.00',newblock)
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_CUBE_EXP
ORA-16000: database open for read-only access
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 7564
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PROCACT_SCHEMA:"OE"]
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_METADATA", line 5466

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358

----- PL/SQL Call Stack -----
object      line object
handle    number name
0x34f7b2c0     19208 package body SYS.KUPW$WORKER
0x34f7b2c0      8385 package body SYS.KUPW$WORKER
0x34f7b2c0      2935 package body SYS.KUPW$WORKER
0x34f7b2c0      9054 package body SYS.KUPW$WORKER
0x34f7b2c0      1688 package body SYS.KUPW$WORKER
0x34d28650         2 anonymous block

Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 04:55:56

[oracle@dgaskmpri01 askm]$ expdp system/oracle attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Tue Nov 22 05:03:32 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: B2533E2386512016E040A8C022010D27
Start Time: Tuesday, 22 November, 2011 5:03:43
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** full=y directory=sby_dump_dir network_link=db_sbyorcl dumpfile=sby_db.dmp logfile=sby_db.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /tmp/askm/sby_db.dmp
bytes written: 950,272

Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: OE
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 36
Worker Parallelism: 1

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@dgaskmpri01 askm]$ clear
[oracle@dgaskmpri01 askm]$

I got error while exporting the database. This is identified as bug in 10g and 11gR1. As per the oracle documentation, it is rectified in 11gR2, but I am still getting this issue on 11gR2. I need to further investigate it. Comments and Feedbacks are accepted pls Smile

Reference:

How to use Data Pump Export (expdp) to export from Physical Standby Database [ID 1356592.1]

Thanks

SRI

askMLabs