BACKING UP ONLINE REDO LOGS (OR NOT)

 Do you need to back up the online redo logs?

 No; you never need to back up the online redo logs as part of any type of backup. 

Then, why do DBAs back up the online redo logs as part of a cold backup?

 One reason is that it makes the restore process for the noarchivelog mode scenario slightly easier. The online redo logs are required to open the database in a normal manner.

PURPOSE OF OPEN RESETLOGS

Sometimes, you’re required to open your database with the OPEN RESETLOGS clause. 

You may do this when recreating a control file, performing a restore and recovery with a backup control file, or performing an incomplete recovery. 

When you open your database with the OPEN RESETLOGS clause, it either wipes out any existing online redo log files or, if the files don’t exist, recreates them.

You can query the MEMBER column of V$LOGFILE to see which files are involved in an OPEN RESETLOGS operation.

Why would you want to wipe out what’s in the online redo logs?

 Take the example of an incomplete recovery, in which the database is deliberately opened to a point in time in the past. In this situation the SCN information in the online redo logs contains transaction data that will never be recovered. Oracle forces you to open the database with OPEN RESETLOGS to purposely wipe out that information.

When you open your database with OPEN RESETLOGS, you create a new incarnation of your database and reset the log sequence number back to 1. Oracle requires a new incarnation so as to avoid accidentally using any old archive redo logs (associated with a separate incarnation of the database), in the event that another restore and recovery is required.

Dataguard - Primary DB Prerequisits

– enabling the archive log mode

– enabling force logging

Force logging

For a physical standby to be a mirror copy, it must receive redo for the changes made to the primary database. In the primary database, when a segment is defined with the NOLOGGING attribute and if a NOLOGGING operation updates the segment, the online redo logfile will be updated with minimal information. This is preferred to complete operations faster but it's not supported in a primary database with the Data Guard configuration. When the redo/ archived logfile containing the NOLOGGING operation is used to recover the datafiles on the standby database, Oracle invalidates such blocks and the error ORA-26040 along with error ORA-1578 are reported by SQL statements in the next block reads. You can see the following errors if operations are performed by NOLOGGING:

ORA-01578: ORACLE data block corrupted (file # 4, block # 84)

ORA-01110: data file 4: ' /u01/app/oracle/oradata/orcl/users01.dbf' 

ORA-26040: Data block was loaded using the NOLOGGING option

Perform the following steps on the primary database:

1. Check the force logging status as follows: 

SQL> select name, force_logging from v$database;

 NAME FOR --------- --- ORCL NO 

2. Enable the force logging mode as follows:

 Enabling Force Logging on Primary Database is mandatory.

 SQL> alter database force logging;

 Database altered.

 3. Check the force logging status again as follows: 

SQL> select name,force_logging from v$database; 

NAME FOR --------- --- ORCL YES 

In the alert log, you'll see following lines: 

alter database force logging 

ALTER DATABASE FORCE LOGGING command is waiting for existing direct writes to finish. 

This may take a long time.

 Completed: alter database force logging

CRS

 Situation :

While installing GRID s/w I used up all my allocated ASM disk space to create the CRS mount/diskgroup

Now I need to create database and need DATA and RECO disk group , but i'm unable to create it as all the disks are already taken up by CRS mount.

SQL> column diskgroup format a10;

SQL> column disk_name format a10;

SQL> column failgroup format a10;

SQL> select dg.name diskgroup, dk.name disk_name, dk.failgroup, dk.mount_status, dk.mode_status from v$asm_disk dk, v$asm_diskgroup dg where dk.group_number = dg.group_number;

DISKGROUP  DISK_NAME  FAILGROUP  MOUNT_S MODE_ST

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

CRS        CRS_0002   CRS_0002   CACHED  ONLINE

CRS        CRS_0001   CRS_0001   CACHED  ONLINE

CRS        CRS_0000   CRS_0000   CACHED  ONLINE

SQL>

ASMCMD> lsdsk -k

Total_MB  Free_MB  OS_MB  Name      Failgroup  Failgroup_Type  Library  Label  UDID  Product  Redund   Path

    4096     3706   4096  CRS_0000  CRS_0000   REGULAR         System                         UNKNOWN  /dev/oracleasm/disks/CRS1

   23552    21510  23552  CRS_0001  CRS_0001   REGULAR         System                         UNKNOWN  /dev/oracleasm/disks/DATA1

   23551    21510  23551  CRS_0002  CRS_0002   REGULAR         System                         UNKNOWN  /dev/oracleasm/disks/FRA1

ASMCMD>

ASMCMD>

SQL> SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,

d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup

FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;  2    3

DISKGROUP        ASMDISK          MOUNT_S STATE    FAILGROUP

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

CRS              CRS_0002         CACHED  NORMAL   CRS_0002

CRS              CRS_0000         CACHED  NORMAL   CRS_0000

CRS              CRS_0001         CACHED  NORMAL   CRS_0001

[oracle@oraclelab1 ~]$ asmcmd

ASMCMD> lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512   4096  1048576     51199    46726                0           46726              0             Y  CRS/

ASMCMD>

AS

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME                           TYPE     TOTAL_MB    FREE_MB

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

REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB

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

CRS                            EXTERN      51199      46726

                      0          46726

As you can see my all 50GB disk space is used up by CRS.

So lets free it up 

lets drop 

Name         Path

CRS_0000     /dev/oracleasm/disks/CRS1

CRS_0001     /dev/oracleasm/disks/DATA1

CRS_0002     /dev/oracleasm/disks/FRA1

SQL> 

alter diskgroup CRS drop disk CRS_0001 rebalance power 5 wait;

ASMCMD shows those disks are freed from ASM.

ASMCMD> lsdsk

Path

/dev/oracleasm/disks/CRS1

/dev/oracleasm/disks/FRA1

ASMCMD> lsdsk --candidate

Path

/dev/oracleasm/disks/DATA1

ASMCMD>

set lines 999;

col diskgroup for a10

col diskname for a12

col path for a30

select a.name DiskGroup,b.name DiskName, b.total_mb, 

(b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,b.header_status from v$asm_disk b, v$asm_diskgroup 

a where a.group_number (+) =b.group_number order by b.group_number,b.name;

ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run

export ORACLE_HOME=/u01/app/12.1.0.2/grid

export PATH=/u01/app/12.1.0.2/gridbin/

export ORACLE_SID=+ASM2

[grid@testdb1 ~]$ asmcmd

ASMCMD> ls

Datagaurd Basics

 In Data Guard, transfer of the changed data from the primary to standby database is

achieved by redo with no alternative. However, the apply process of the redo content to the

standby database may vary. The different methods on the apply process reveal different

type of standby databases.

There were two kinds of standby databases before Oracle database Version 11g, which

were: physical standby database and logical standby database. Within Version 11g we

should mention a third type of standby database which is snapshot standby. Let's look

at the properties of these standby database types.

Physical standby database

The Physical standby database is a block-based copy of the primary database. In a physical

standby environment, in addition to containing the same database objects and same data,

the primary and standby databases are identical on a block-for-block basis. Physical standby

databases use Redo Apply method to apply changes. Redo Apply uses Managed recovery

process (MRP) in order to manage application of the change in information on redo.

In Version 11g, a physical standby database can be accessible in read-only mode while Redo

Apply is working, which is called Active Data Guard. Using the Active Data Guard feature, we

can offload report jobs from the primary to physical standby database.

Logical standby database

Logical standby database is a feature introduced in Version 9iR2. In this configuration, redo

data is first converted into SQL statements and then applied to the standby database. This

process is called SQL Apply. This method makes it possible to access the standby database

permanently and allows read/write while the replication of data is active. Thus, you're also

able to create database objects on the standby database that don't exist on the primary

database. So a logical standby database can be used for many other purposes along with

high availability and disaster recovery.

Due to the basics of SQL Apply, a logical standby database will contain the same data as the

primary database but in a different structure on the disks.

One discouraging aspect of the logical standby database is the unsupported data types,

objects, and DDLs. The following data types are not supported to be replicated in a logical

standby environment:

 BFILE

 Collections (including VARRAYS and nested tables)

 Multimedia data types (including Spatial, Image, and Oracle Text)

 ROWID and UROWID

 User-defined types

The logical standby database doesn't guarantee to contain all primary data because of the

unsupported data types, objects, and DDLs. Also, SQL Apply consumes more hardware

resources. Therefore, it certainly brings more performance issues and administrative

complexities than Redo Apply.

Snapshot standby database

Principally, a snapshot standby database is a special condition of a physical standby

database. Snapshot standby is a feature that is available with Oracle Database Version 11g.

When you convert a Physical standby database into a snapshot standby database, it becomes

accessible for read/write. You can run tests on this database and change the data. When

you're finished with the snapshot standby database, it's possible to reverse all the changes

made to the database and turn it back to a physical standby again.

An important point here is that a snapshot standby database can't run Redo Apply. Redo

transfer continues but standby is not able to apply redo.

Oracle Data Guard architecture

The main architecture of Oracle Data Guard 11gR2 includes a primary database, up to 30

standby databases, the redo transport services, (which automatically ship the redo log data

from the primary to standby server), and Apply Services (which applies the changes in redo

on the standby database). 

Data Guard services

Redo transport services

In a primary database, when a user commits a transaction, the relevant redo data is written

into online redo logfiles from memory (Redo Log Buffer). After the online redo log group

becomes full it is archived into an archived redo logfile with a log switch. It's possible to

configure Data Guard sending the redo data to standby databases from the log buffer as the

transactions are committed (by LGWR process) or from the online redo logfiles when they're

being archived (by ARCn processes). Shipping

Here are the important properties of the log transport with the ARCH attribute:

 Logs are sent by the ARCH process; the LNS process is not in use

 Standby redo logs are not mandatory on the standby database

 Data in the unarchived online redo log will be lost in a failover

=================

If LGWR is used for the redo transportation, it's possible to guarantee zero data loss failovers

by creating a Data Guard configuration in which the primary database waits for confirmation

from the standby database that redo has been received, before it informs that the commit

is completed. This configuration is called Synchronous redo transport (SYNC). However, this

may affect the performance of the primary database.

The following diagram shows the Data Guard configuration with LGWR and SYNC

transportation mode:

The following points explain the diagram in a better way:

 Redo is read and sent to the standby database directly from the log buffer by the
LNS process
 Acknowledgment needed from the standby database (RFS to LNS and LNS to LGWR)
to send COMMIT ACK to the database user
 It's mandatory to use standby redo logs
 Zero data loss in failover can be guaranteed with this configuration
 There maybe slower response times on the primary database
 The primary database stops giving service in a network disruption incident between
primary and standby

The other option is to use the Asynchronous redo transport (ASYNC) method, which avoids
the impact to primary database performance. In this method, the primary database never
waits for any acknowledgment from the standby database in order to complete the commit.
In the ASYNC redo transport method we have the performance gain; however, this method
does not guarantee zero data loss failovers because it does not guarantee all the committed
transactions being received by the standby database at any moment.

The following points explain the diagram in a better way:

 No acknowledgment needed from standby to send the COMMIT ACK to the

database user

 Redo is read and sent to standby from the Redo Log Buffer or online redo logs by

the LNS process. If LNS cannot catch the send data in the Redo Log Buffer before

it is recycled, it automatically reads and sends redo data from the online redo log.

 The committed transactions that weren't shipped to standby yet, may be lost

in a failover

 Potential slower response time on primary database with SYNC mode is not valid here

Datagaurd Creation

 

Step By Step Dataguard Set up in oracle 12c RAC (RAC to RAC)

Prerequisite:

PRIMARY DB :(Node1,Node3)

1-Enable archivelog mode:SQL> archive log list ; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 34 Next log sequence to archive 35 Current log sequence 35

2-Enable force logging mode

SQL> select force_logging from v$database;

FORCE_LOGGING

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

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

3- Configure Standby Redo Log (SRL) files for both the instance :

Min No of SRL= no of threads *(Group in threads +1)
SQL> set lines 200 pages 200

SQL> col MEMBER for a55 

SQL> select group#,thread#,members,status,bytes/1024/1024 from v$log;

    GROUP#    THREAD#    MEMBERS STATUS           BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------------
         1          1          2 CURRENT                       50
         2          1          2 INACTIVE                      50
         3          2          2 CURRENT                       50
         4          2          2 INACTIVE                      50

In our environment we have 4 groups with 2 threads .So here number of SRL will be 
                          SRL= 2*(4+1)=10