Table Block Curruption during DataPump Backup

Advertisements

2 – Node RAC Stadby database datafiles recovery from Primary Site

Standby Site Recovery Senario

Environment
2-Node RAC Oracle11g 11.2.0.3.0 with ASM /RHEL 5.6 64bit/ RAC to RAC Data guard configuration

Problem:

Due to miscommunication by Junior engineer who had communication gap with Storage Admin and so Storage Administrator had perform activity for drop the live raw partitions and recreate the same partition again.

I got the call from my client that services is down I checked 360 degree to recover the services first.

–Recover the Database1 – Datafile – webinpwifi
run{
2> set newname for datafile 6 to ‘C:APPMAZARORADATAOWNnew/test01.dbf’;
3> set newname for datafile 7 to ‘C:APPMAZARORADATAOWNnewtest02.dbf’;
4> restore tablespace “TEST”;
5> switch datafile all;
6> recover tablespace “TEST”;
7> alter database open;
8> }

–Recover the Database2 which is standby database in rac mode

--Recover Standby database - ASM Diskgroup
oracleasm listdisks
oracleasm scandisks
/usr/sbin/oracleasm createdisk MYDSK /dev/mapper/MYDSKp1

CREATE DISKGROUP MYDSK EXTERNAL REDUNDANCY
DISK 'ORCL:MYDSK'
ATTRIBUTE 'au_size'='4M',
'compatible.asm' = '11.2.0.0.0',
'compatible.rdbms' = '11.2.0.0.0',
'compatible.advm' = '11.2.0.0.0';

--Recover Standby database datafile from Primary Site
Step-1 connect to the standby server and startup database in mount stage
Standby Node1
sqlplus> STARTUP MOUNT;

Standby Node2
sqlplus> STARTUP MOUNT;

or

srvctl stop database -d stydb
srvctl stop database -d stydb -o mount

Step–2 Using RMAN, connect to primary as target and standby as auxiliary

rman target / auxiliary sys/*******@duaaa

Step-3 Take image copy backup of example tablespace on primary so that backup file is created on standby
RMAN> backup as copy  tablespace ELITECG auxiliary format '+MYDSK';
inputfile name=x1
outputfile name=x2

RMAN> backup as copy  tablespace ELITEAAAPWIFI auxiliary format '+MYDSK';
inputfile name=y1
outputfile name=y2

Example
=======
-bash-3.2$ rman TARGET / AUXILIARY sys/*******@stydb

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 18 21:17:22 2015

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

connected to target database: DUAAA (DBID=3171721304)
connected to auxiliary database: DUAAA (DBID=3171721304, not open)

RMAN> backup as copy  tablespace ELITECG auxiliary format '+MYDSK';

Starting backup at 18-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=554 instance=duaaadr2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00109 name=+MYDSK/duaaadr/datafile/elitecg.256.866041173
output file name=+MYDSK/duaaa/datafile/elitecg.259.874703867 tag=TAG20150318T211745
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-MAR-15

RMAN> backup as copy  tablespace ELITEWIFI auxiliary format '+MYDSK';

Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00110 name=+MYDSK/duaaadr/datafile/elitewifi.257.866043479
output file name=+MYDSK/duaaa/datafile/elitewifi.257.874703897 tag=TAG20150318T211817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-MAR-15

Step3 — Connect to standby database

rman target
RMAN > report schemas;

109 x1
110 y1

run
{
set newname for datafile 109 to x2;
switch datafile 109;
}

run
{
set newname for datafile 110 to y2;
switch datafile 110;
}

Example
=======
catalog datafilecopy '+MYDSK/duaaa/datafile/elitecg.259.874703867';
catalog datafilecopy '+MYDSK/duaaa/datafile/elitewifi.257.874703897';

run {
set newname for datafile 109 to '+MYDSK/duaaa/datafile/elitecg.259.874703867';
switch datafile 109;
}

run {
set newname for datafile 110 to '+MYDSK/duaaa/datafile/elitewifi.257.874703897';
switch datafile 110;
}

Step-4 — Wait some time and check the alert logs of standby database

archive log list
confirm the generated and apply archive of primary and standby site

Step-5 — Standby Node1

ALTER DATABASE OPEN READ ONLY;

Step-6 -- Standby Node2
ALTER DATABASE OPEN READ ONLY;

Step-7 — Only from Standby Node1

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Reference
http://oracleinaction.com/recover-standby-datafile-primary/
http://www.oraclecommunity.net/profiles/blogs/rmanrestore-datafiles-to
http://www.thesqlreport.com/?p=728
http://www.oracle-wiki.net/startdocshowtomoveanasmdatafilediskgrp
https://aprakash.wordpress.com/2011/05/29/moving-datafiles-using-asmcmd-11gr2/

The price we pay for the speedup of the insert

The Great artical by uhesse

The outcome of NOLOGGING operations, should the affected datafiles get damaged before we manage to take a backup of them.

As you probably know, we are allowed to suppress the usual redo protocol generation for certain statements, especially for
CREATE TABLE AS SELECT, INSERT INTO … SELECT and CREATE INDEX. If we can take a backup of the related datafiles after these
operations, everything is ok. We simply saved time during the above mentioned operations. But what if the datafiles actually
get damaged?

Hands On
http://uhesse.com/2009/09/15/how-do-nologging-operations-affect-recovery/

Oracle Data Guard – OTN Thread
NOLOGGING tables and recovery of database (and standby)

Even if you have tables with NOLOGGING minimal logging with respect to table structure changes are recorded, so in the case
of recovery, you can recover the database with nologging tables from the last full backup but data changes after the full
backup can not be recovered on the nologging tables.

You should avoid this situation by saying

SQL> alter database force logging;

on the Primary – best before creation of the Standby. Else you will lose the objects created with nologging in case of a failover.
If nologging operations have been done on the Primary already, but no damage there yet – better recreate the Standby after force
logging on the Primary.

1. So basically, if we are using Dataguard, NOLOGGING is not a good idea.
exactly.
2. What actually happens there? does the standby is not maintained at all?
No. It is maintained.
3. only the NOLOGGING objects will be in a corrupt/unusable state?
Yes
4. Will the standby have the DDL changes on NOLOGGING objects?
yes
5. Is it possible to find out such corrupt objects (if we are switching to the standby)?
You use them and get error messages.
6. Also, even if we are FORCE LOGGING, if we truncate a table or partition, will we brake the standby?
no. the truncate is reproduced on the standby.

https://community.oracle.com/thread/2146256?start=0&tstart=0

How to Recover !!!!!
No way, still last valid datafile backup

http://docs.oracle.com/cd/E11882_01/server.112/e41134/scenarios.htm#i1015738

13.4 Recovering After the NOLOGGING Clause Is Specified

In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the online redo log file. Even though the user specifies the clause, a redo record is still written to the online redo log file. However, there is no data associated with this record. This can result in log application or data access errors at the standby site and manual recovery might be required to resume applying log files.

Note:
To avoid these problems, Oracle recommends that you always specify the FORCE LOGGING clause in the CREATE DATABASE or ALTER DATABASE statements. See the Oracle Database Administrator’s Guide.

13.4.1 Recovery Steps for Logical Standby Databases

For logical standby databases, when SQL Apply encounters a redo record for an operation performed on an interesting table with the NOLOGGING clause, it stops with the following error: ORA-16211 unsupported record found in the archived redo log.

To recover after the NOLOGGING clause is specified, re-create one or more tables from the primary database, as described in Section 10.5.5.

Note:
In general, use of the NOLOGGING clause is not recommended. Optionally, if you know in advance that operations using the NOLOGGING clause will be performed on certain tables in the primary database, you might want to prevent the application of SQL statements associated with these tables to the logical standby database by using the DBMS_LOGSTDBY.SKIP procedure.
13.4.2 Recovery Steps for Physical Standby Databases

When the archived redo log file is copied to the standby site and applied to the physical standby database, a portion of the datafile is unusable and is marked as being unrecoverable. When you either fail over to the physical standby database, or open the standby database for read-only access, and attempt to read the range of blocks that are marked as UNRECOVERABLE, you will see error messages similar to the following:

ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: ‘/oracle/dbs/stdby/tbs_1.dbf’
ORA-26040: Data block was loaded using the NOLOGGING option

To recover after the NOLOGGING clause is specified, you need to copy the datafile that contains the missing redo data from the primary site to the physical standby site. Perform the following steps:

Step 1 Determine which datafiles should be copied.

Follow these steps:

Query the primary database:

        SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;

        NAME                                                  UNRECOVERABLE
        ----------------------------------------------------- -------------
        /oracle/dbs/tbs_1.dbf                                       5216
        /oracle/dbs/tbs_2.dbf                                          0
        /oracle/dbs/tbs_3.dbf                                          0
        /oracle/dbs/tbs_4.dbf                                          0
        4 rows selected.

Query the standby database:

        SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;

        NAME                                                  UNRECOVERABLE
        ----------------------------------------------------- -------------
        /oracle/dbs/stdby/tbs_1.dbf                                 5186
        /oracle/dbs/stdby/tbs_2.dbf                                    0
        /oracle/dbs/stdby/tbs_3.dbf                                    0
        /oracle/dbs/stdby/tbs_4.dbf                                    0
        4 rows selected.

Compare the query results of the primary and standby databases.

Compare the value of the UNRECOVERABLE_CHANGE# column in both query results.
If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.

In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs_1.dbf datafile is greater, so you need
to copy the tbs_1.dbf datafile to the standby site.

Step 2 On the primary site, back up the datafile you need to copy to the standby site.

Issue the following SQL statements:

    SQL> ALTER TABLESPACE system BEGIN BACKUP;
    SQL> EXIT;

    Copy the needed datafile to a local directory.

    SQL> ALTER TABLESPACE system END BACKUP;

Step 3 Copy the datafile to the standby database.

Copy the datafile that contains the missing redo data from the primary site to a location on the physical standby site where files related to recovery are stored.

Step 4 On the standby database, restart Redo Apply.

Issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

You might get the following error messages (possibly in the alert log) when you try to restart Redo Apply:

ORA-00308: cannot open archived log ‘standby1’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/oracle/dbs/stdby/tbs_1.dbf’

If you get the ORA-00308 error and Redo Apply does not terminate automatically, you can cancel recovery by issuing the following SQL statement from another terminal window:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

These error messages are returned when one or more log files in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps, and repeat Step 4. See Section 6.4.3.1 for information about manually resolving an archive gap.

13.4.3 Determining If a Backup Is Required After Unrecoverable Operations

If you performed unrecoverable operations on your primary database, determine if a new backup operation is required by following these steps:

Query the V$DATAFILE view on the primary database to determine the system change number (SCN) or the time at which the Oracle database generated the most recent invalidated redo data.

Issue the following SQL statement on the primary database to determine if you need to perform another backup:

    SQL> SELECT UNRECOVERABLE_CHANGE#,TO_CHAR(UNRECOVERABLE_TIME, 'mm-dd-yyyy hh:mi:ss') FROM   V$DATAFILE;

If the query in the previous step reports an unrecoverable time for a datafile that is more recent than the time when the datafile was last backed up, then make another backup of the datafile in question.

See Oracle Database Reference for more information about the V$DATAFILE view.

Ref

Nologging operations affect standby database

Resizing online and standby redologs for dataguard configuration

Steps:-

1.Move standby database to manual management mode.

2.drop and add orl and srl files.

3.stop recovery on standby

3.on standby clear,drop and add logfiles.

4.start recovery and move database to auto management mode.

Handling ORL and SRL (Resize) on Primary and Physical Standby in Dataguard Environment [ID 1532566.1]

How to recover Standby database from a missing archivelog

A Physical Standby database relies on continuous application of archivelogs from a Primary Database to be in synch with it. In Oracle Database versions prior to 10g in the event of an archivelog gone missing or corrupt you had to rebuild the standby database from scratch.

In 10g you can use an incremental backup from SCN and recover the standby using the same to compensate for the missing archivelogs as shown below

http://dbaregistry.blogspot.in/2010/01/how-to-recover-standby-database-from.html

http://oracledbabhuvan.blogspot.in/2011/10/recovering-standby-database-from.html