How To Use DataPump Export (EXPDP) To Export From Physical Standby Database

This document describes how to successfully execute DataPump Export to export data from a Physical Standby database.

Taking dump file from a large primary database for some reason can be a challenge for DBA.

Although existing features such as parallelism can be speed up process but this load cannot be tolerated on the production database.

So once solution is taking dump on standby.

SOLUTION

It's important to know that Data Pump Export (expdp) cannot be executed directly on the Physical Standby database. This is due to the fact that Data Pump Export needs to create and maintain a Master Table which requires that a database would be open in "READ WRITE" mode. Therefore it is necessary to connect from a "non-Standby" database (which will maintain the Master Table) to the Physical Standby database using parameter NETWORK_LINK.

The NETWORK_LINK parameter initiates an export by using a valid database link. This means that the system to which the expdp client is connected contacts the Physical Standby database referenced by the source_database_link, retrieves data from it, and writes the data to a dump file set back on the connected system.

The Physical Standby database must be opened in "READ ONLY" mode.

Steps to execute to export from Physical Standby Database

On Physical Standby Database:

-- Connect to Physical Standby database and check its status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

<STDBY_DB_NAME>  MOUNTED

-- Cancel managed recovery and open database in "READ ONLY" mode.

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

-- Verify database status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

<STDBY_DB_NAME>  OPEN

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY

On "Non Standby" Database (another database as a proxy, this can be a light with minimal resource and use only for this way.

-- create DB Link, Oracle Directory and test it

SQL> create database link expdp_primary connect to system identified by <password> using ‘standby_database’;

SQL> select db_unique_name from v$database;

SQL> select db_unique_name from v$database@expdp_primary;

SQL> create directory datapump as ‘/tmp’;

-- Use NETWORK_LINK to database link above to connect to the Physical Standby database:

$expdp system/<password> directory=datapump network_link=expdp_primary full=y dumpfile=standby_database.dmp logfile=standby_database.log

♨️Also another option is convert standby database to snapshot and take export pump and finally comver to physical standby.

This can done by a manual script under a job.

*********************************

Typical Errors when exporting from Physical Standby databases

ISSUE:

UDE-01033: operation generated ORACLE error 1033

ORA-01033: ORACLE initialization or shutdown in progress

UDE-00003: all allowable logon attempts failed

CAUSE:

•No NETWORK_LINK parameter was used to connect to Physical Standby database 
- AND -

•Physical Standby runs in "MOUNTED" state

SOLUTION:

•Use NETWORK_LINK={database link} to connect Physical Standby database.

•Make sure that a valid database link exists to connect to Standby database.

•Run Physical Standby database in "READ ONLY" mode.

ISSUE:

ORA-39006: internal error

ORA-39065: unexpected master process exception in DISPATCH

ORA-01033: ORACLE initialization or shutdown in progress

ORA-02063: preceding line from {database link}

ORA-39097: Data Pump job encountered unexpected error -1033

CAUSE:

•Physical Standby runs in "MOUNTED" state

SOLUTION:

•Connect to Physical Standby database to open the database in "READ ONLY MODE".

Some recommendations for taking dump on large databases:

• Use standby to prevent load affected on primary database.
• Use parallelism to use more process and minimize time.
• Take dump as data_only first 
•     Take all indexes as a sqlfile by import
•     Run DDL commands from multiple sessions at the same time to index creation 
• Exclude statistsics from export if possible
• For partitions table use merge option if possible 
• Optimize  STREAMS_POOL_SIZE param
• Set AQ_TM_PROCESSES>0 if possible
• On Rac PARALLEL_FORCE_LOCAL=TRUE

Regards,

Alireza Kamrani

Recovering a Oracle datafile without backup

If a datafile is damaged and no backup of that file is available, then you can still recover the datafile given the following condition:

1.  All archived log files written after the creation of the original datafile are available

2.The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database).

Steps to recover a datafile without backup

1. Very first step we have to do is to re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. For example, assume that the datafile   /u01/oradata/users01.dbf has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on u02(another location):

ALTER DATABASE CREATE DATAFILE '/u01/oradata/users01.dbf' AS '/u02/users01.dbf';

This statement creates an empty file that is the same size as the lost file. The database looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.

2. Perform media recovery on the empty datafile.

For example, enter:

RECOVER DATAFILE '/u02/users01.dbf'

--Or use datafile number

RECOVER DATAFILE 8;

All archived logs written after the original datafile was created must be applied to the new, empty version of the lost datafile during recovery.

Here is a overall summary:

1.shutdown your database

2.take the database in mount state

3.alter database create datafile 'old_datafile_path' as 'new_datafile_path';

4.once new file created do the incomplete recovery.

5. open the database with resetlogs.

Note:

If after creating new datafile got error:

ERROR at line 1:

ORA-01182: cannot create database file 8 - file is in use or recovery

ORA-01110: data file 8: '/u01/oradata/users01.dbf'

To avoid error - before creating new datafile, you must take the existing datafile (or the tablespace) offline.

SQL> alter database datafile 8 offline drop;

After offline datafile then do recover and finally online it.

********************

Restore/Recover datafile from Standby without database catalog (19c)

 SQL> startup

 ORACLE instance started.

 Total System Global Area #### bytes

 Database mounted.

 ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

 ORA-01110: data file 3: '/u01/oradata/sysaux01.dbf'

RMAN>  list backup of datafile 2;

 specification does not match any backup in the repository

As you can see on primary database we can not start db and have problem on sysaux.

And supposed that we have no backup.

Solution:

Connect to the Standby as primary connection and to the primary as auxiliary connection, and make a backup of the datafile 2.

$ rman target sys/pass@pop_stby auxiliary sys/pass@orcl_prim

 RMAN>

 RMAN> backup as copy datafile 2 auxiliary format '/backup/sysaux01.dbf';

Connect to the primary database, catalog the backup of the datafile as copy, restore and recover the datafile.

$ rman target sys/pass@pop_prim

 connected to target database: pop_prim (DBID=1014132133, not open)

 

RMAN> catalog datafilecopy '/backup/sysaux01.dbf';

 RMAN> restore datafile 3;

 

 RMAN> recover datafile 3;

RMAN> alter database open;

Regards,

Alireza Kamrani.