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

Data Synchronization Monitoring

Case Study-1
Oracle Streaming between two remote site having 2 Node Oracle10g RAC in Solaris 10 platform

CREATE OR REPLACE PROCEDURE PROC_DB_MONITOR AS
SITEA_CNT NUMBER;
SITEB_CNT NUMBER;
DIFF_CNT NUMBER;
BEGIN

/* This Procedure is Created in SITEA Database for Monitor
   the data synchroization between SITEA Database and SITEB Database */

---For SITEA Database use DB Link.
SELECT COUNT(1) INTO SITEA_CNT FROM AAA.CUSTOMER@@DBLINKSITEA;

---For SITEB Database use DB Link
SELECT COUNT(1) INTO SITEB_CNT FROM AAA.CUSTOMER@DBLINKSITEB;

--Print the count of SITEA Database.
DBMS_OUTPUT.PUT_LINE('CNT OF SITEA SITE:=====>'||SITEA_CNT);

--Print the count of SITEB Database.
DBMS_OUTPUT.PUT_LINE('CNT OF SITEB SITE:=====>'||SITEB_CNT);

--Find the count different between SITEA and SITEB Database.
SELECT TO_NUMBER(SITEA_CNT) - TO_NUMBER(SITEB_CNT) INTO DIFF_CNT FROM DUAL;

--Print the count different between SITEA and SITEB Database.
DBMS_OUTPUT.PUT_LINE('DIFF OF SITEA AND SITEB SITE: <======> '||DIFF_CNT);

END;

#SCRIPT for database synchronization monitor and send mail

#Read the database environment variables.
. /export/home/oracle/.profile
#Connect to the database subdbdel
sqlplus aaa/******@SITEB <

cronjob

#This process should run every early morning between 5 am.
0 5 * * * /export/home/oracle/hitesh/SCRIPTS/dbalert.sh > /tmp/datasynch/err 2>&1

Case Study-2
Oracle Data Guard between two remote site having 2 Node Oracle11g RAC in Linux 6.4 platform

V$ARCHIVED_LOG
V$LOG_HISTORY
V$ARCHIVE_GAP

GRANT SELECT ON V_$ARCHIVED_LOG to reports;
GRANT SELECT ON V_$LOG_HISTORY to reports;
GRANT SELECT ON V_$ARCHIVE_GAP to reports;

—Primary Server(sqlplus reports/*******@prddb)

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM sys.V_$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

–Standby Server(sqlplus reports/********@stydb)

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM sys.V_$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM sys.V_$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM sys.V_$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM sys.V_$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM sys.V_$ARCHIVE_GAP;

How to calculate network bandwidth for dataguard

Calculate Data guard required Bandwidth

The formula to calculate Bandwidth

Let us assume the redo rate is a 500 KB/sec.(500*1024=512000)
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000
bandwidth in Mbps Required bandwidth = ((512000/0.7) * 8) /1,000,000
Required bandwidth = 4.85 Mbps
In CASE WE HAVE RAC DATABASE then
Required bandwidth = 4.85 Mbps * no of RAC nodes

https://forums.oracle.com/thread/2280928

http://yvrk1973.blogspot.in/2013/09/calculation-for-network-bandwidth.html

Standby Network Tuning

ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

/u01 is 100% full

we came to know that undotbs1 is increase around 30 gb

Undo Tablespace

The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I’ve used autoextend, but you may wish to remove this if you want manual control over the datafile size.

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

SQL> drop tablespace UNDOTBS01 including contents;
drop tablespace UNDOTBS01 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

Tried to delete the rollback segment, but it did not work:

SQL> drop rollback segment '_SYSSMU1_1255349037$';
drop rollback segment '_SYSSMU1_1255349037$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment name

http://davidalejomarcos.wordpress.com/2010/11/09/ora-01548-active-rollback-segment-_syssmu1_xxxx-found-terminate-dropping-tablespace-while-deleting-undotbs/

http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php#undo_tablespace

 

 

 

 

MRP0: Background Media Recovery terminated with error 1274

RACDR-Node-1
shutdown immediate
RACDR-Node-2
shutdown immediate

RACDR-Node-1
startup nomount
alter database mount standby database;
alter database open read only;

–error come and not able to open and asking for do recovery
–tried to recover but not succeed

alter database datafile ‘/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005’ offline drop;

alter database open read only;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

RACDR-Node-2
startup nomount
alter database mount standby database;

alter database open read only;

we got succeed 🙂

MRP0: Background Media Recovery terminated with error 1274 [ID 739618.1]

dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 – 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> help

The following commands are available:

add Adds a standby database to the broker configuration
connect Connects to an Oracle database instance
convert Converts a database from one type to another
create Creates a broker configuration
disable Disables a configuration, a database, or fast-start failover
edit Edits a configuration, database, or instance
enable Enables a configuration, a database, or fast-start failover
exit Exits the program
failover Changes a standby database to be the primary database
help Displays description and syntax for a command
quit Exits the program
reinstate Changes a database marked for reinstatement into a viable standby
rem Comment to be ignored by DGMGRL
remove Removes a configuration, database, or instance
show Displays information about a configuration, database, or instance
shutdown Shuts down a currently running Oracle database instance
sql Executes a SQL statement
start Starts the fast-start failover observer
startup Starts an Oracle database instance
stop Stops the fast-start failover observer
switchover Switches roles between a primary and standby database

Use “help ” to see syntax for individual commands

Managing Data Guard using DGMGRL

 
http://davidalejomarcos.wordpress.com/category/dataguard-broker/