We're here to help!

Handling table corruption on Production using Standby DB

Follow

Read our docs We have a corrupt table in Oracle in our production database , but changes has not been propagated to Standby database


Problem Details

Problem Statement

Corruption in production Database

Applicable Error Code

Database errors in alertlog 

Affected Versions

Applicable to all versions of database based on standby version used

Affected Platforms

Platform independent

 

Description

We have a corrupt table in Oracle in our production database. We would like to perform a datapump of that same table in our dbvisit database without doing a switchover. I would like to just do the export. It would be helpful if you could list steps to achieve this. Then we will need to set this back to standby.

Solution

It is only possible if the corruption is not applied on the Standby database. If it has been already applied then you might need to look at going to your backups to extract this table. 

Step1. Open the Standby Database in read-only mode.

./dbv_oraStartStop open <DDC> (Version 7 Standby)
./dbvctl -d <DDC> -o open (Version 8 Standby)

or

sqlplus /nolog
connect / as sysdba
alter database open read only;

Step2. Use export to get the table out. The below is just an example:

oracle@dbvaix01[/usr/local/dbvisit/standby]: exp dbvisit7/XYZ file=exp.dmp statistics=none tables=dbv_version
Export: Release 11.2.0.1.0 - Production on Sat Feb 21 11:20:45 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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

About to export specified tables via Conventional Path ...
. . exporting table                    DBV_VERSION          1 rows exported
Export terminated successfully without warnings.

Note:  You cannot use datapump on a standby database because it needs to create and maintain a Master Table which requires that a database would be open in "READ WRITE" mode. You can use the traditional export - exp/imp to get the table you want.

Step3. Import the table on Primary DB.

Step4. Restart the Standby Database in recovery mode

./dbv_oraStartStop restart <DDC> (Version 7 Standby)
./dbvctl -d <DDC> -o restart (Version 8 Standby)

Step5. Perform the normal log ship and log apply using 

./dbvisit <DDC> (Version 7 Standby)
./dbvctl -d <DDC>  (Version 8 Standby)

 

Have more questions? Submit a request

Comments