Oracle Technologies Blog

By ASKM

Using expdp To Export From Physical Standby Database

Posted by Srikrishna Murthy Annam on February 16, 2012

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.

  1. Verify primary and Standby Databases
  2. Cancel recovery and Open standby database in Read only Mode
  3. Create a physical directory on primary server to keep export dump files
  4. Create DB Link, Oracle Directory and test it.
  5. 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: