Oracle help at your desk....:-)

NOLOGGING affect the recoverability. Before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, there are some points regarding it:

NOLOGGING is designed to handle bulk inserts of data which can be easy reproduced.

Regardless of LOGGING status, writing to undo blocks causes generation of redo.

LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. (Means that the NOLOGGING attribute will not have any effect on the segments) If the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.

FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases).

Enjoy:-)

Friday, September 30, 2011

How To Check Whether Physical Standby is in Sync with the Primary or Not?

The following tips will help you in monitoring sync between primary and standby databases.

1. Check for GAP on standby

2. Check redo received on standby

3. Check redo applied on standby

Solution:

Execute following queries:

A. On Primary

SQL> SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Thread Last Sequence Generated

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

1 19

2 13

3 11

B. On Physical Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 19 19 0

2 13 13 0

3 11 11 0

C. On Physical Standby

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Now perform following checks:

1. Check for GAP

If query “C” returns any row then this means there are some archive log missing on standby.

Example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Thread Low Sequence High Sequence

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

1 8 9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.

If query “C” does not returns any row and output is “no row selected” than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.

If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

enjoy:-)

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements..

-- Convert primary database to standby

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

if the above error occurred, then try to shut the database then startup then try again.

-If still the same error persist,do the following in sequence: -

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; (value for this should be 0)

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

SQL> alter database commit to switchover to PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the standby database issue the following commands: -

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

if the above error occurred then execute the below commands:

SQL> alter database recover managed standby database finish;

SQL> alter database commit to switchover to physical primary with session shutdown;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

enjoy:-)

Defining the time interval at which redo log wil apply to standby:

Here in this post we will learn how to set time interval of redo log application to standby.

How to create a physical standby in oracle 11g:

You can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

On standby server:

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

You may also changed the scenario as NODELAY means redo log will immediately applied using the below command:

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

enjoy:-)