Archive

Archive for the ‘Data Guard’ Category

let’s meet at TROUG High Availability SIG 2016 – Galatasaray University!

February 22, 2016 Leave a comment

he16-1

For detailed information and registration;

High Availability SIG Meeting 2016

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

April 11, 2012 3 comments

Hi Friends,

I will write about resolving the Primary/Standby log gap in case of we deleted some archive log files from primary. Suppose that we don’t have the backup of the deleted archive files. Normally we (DBAs) should not allow such a situation but such a situation can happen to us. In this case,  we need to learn the current SCN number of Primary and standby databases.

1- let’s learn current SCN number with the following query on the Primary.

SQL> select current_scn from v$database;

CURRENT_SCN

———–

1289504966

2- let’s learn current SCN number with the following query on the Standby

SQL> select current_scn from v$database;

CURRENT_SCN

———–

1289359962

using the function scn_to_timestamp(SCN_NUMBER) you can check the time difference between primary and standby.

3- Stop apply process on the Standby database.

SQL> alter database recover managed standby database cancel;

4– Shutdown the Standby database.

SQL> shutdown immediate;

5- Take incremental backup from the latest SCN number of the Standby database on the Primary database. And copy backup to the standby server.

RMAN> backup incremental from scn 1289359962 database;

# scp /backup_ISTANBUL/dun52q66_1_1 oracle@192.168.2.3:/oracle/ora11g

6- Create new standby control file on the Primary database. And copy this file to standby server.

SQL> alter database create standby controlfile as ‘/oracle/ora11g/standby.ctl’;

# scp /oracle/ora11g/standby.ctl oracle@192.168.2.3:/oracle/ora11g

7- Open the Standby database on NOMOUNT state to learn control files location.

SQL> startup nomount

SQL> show parameter control_files

8- Replace new standby control file with old files.

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data1/control01.ctl

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data2/control02.ctl

9- Open the Standby database on MOUNT state.

SQL> alter database mount standby database;

10- Connect to the RMAN and register backup to catalog.

# rman target /

RMAN> catalog start with ‘/oracle/ora11g’;

It will ask for confirmation. Click “y” .

11- Now, you can recover the Standby database. Start recover database.

RMAN> recover database;

When recover of database is finished, it searches the latest archive file. And it gives an ORA-00334 error. In this case, don’t worry about it. Exit from RMAN and start apply process on the standby database.

SQL> alter database recover managed standby database disconnect from session;

We solved the Primary/Standby log gap with RMAN incremental backup . When we faced with such a situation we don’t need to think about re-installing standby database. Because time is very valuable for us.

Talip Hakan Öztürk

Physical Standby Database SWITCHOVER & FAILOVER Operations

July 9, 2011 1 comment

SWITCHOVER:

Article I had written previously was the  creation standby database with RMAN active database duplication. In this article, I will write about  how to switchover from the previous article scenario.

In short, switchover is the planned role change. So, primary database will operate as standby and standby will operate as primary database. Spare our database interoperability testing, to do maintenance work in production (primary) database , etc. can make for things like switchover. Switchover process  can be done in Enterprise Manager, Data Guard Broker and also manually via sqlplus. In this article I will use sqlplus on the manual switch method. I will  remind you the servername  [server]  before each processing step .I want to make more of a reminder. If we run commands on linux, it will start with “$”,  on sqlplus is “SQL>” and, while on rman “RMAN>” as a start.

1. [istanbul] Switch log file on primary database.

SQL>alter system switch logfile;

2. [istanbul] Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_STANDBY” as result.

3. [istanbul] Switch primary database to standby database.

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

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

4. [istanbul] Defer for archive log apply. Because I didn’t set  my standby database as primary yet.

SQL>alter system set log_archive_dest_state_2=defer;

5. [Baku] Switch standby database to primary. Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_PRIMARY” as result. Now let’s swtich

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate;

SQL>startup;

Our switchover process is successfully completed .
6. [Istanbul] Start real-time recovery process..

SQL>recover managed standby database using current logfile disconnect;

Finally let’s open our database with “Read Only with Apply”.

SQL>recover managed standby database cancel;

SQL>alter database open;

SQL>recover managed standby database using current logfile disconnect;

FAILOVER:

In short, the failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database. What to do in case of failover:

(Important note: Istanbul is the primary server and Baku is the standby server)

1. [Istanbul] If the primary database is accessible and running, then it must provided  to send redo buffer to the standby database.

SQL> alter system flush redo to standby_db_name;

SQL>alter system archive log current;

If you don’t receive an error, you can continue with step 5th. In this case, the system can be opened by zero data loss. If you receive an error, We continue with step 2 to open the system at least data loss.

2. [Baku] We must run the following query to learn last applied archive log sequence number.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

3. [Istanbul’dan Baku’ye] If you can access archive logs which are not copied to standby then copy archives to standby. After copy archive log files we must register them to standby database. This operation must be done for every thread.

SQL> alter database register physical logfile '/oracle/ora11g/dbs/arch/ TALIP_991834413_1_102.arc ';

4. [Baku] Check the standby database for redo gap. If there is a gap then we must copy archive log files and register.

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

SQL> alter database register physical logfile '/oracle/ora11g/dbs/arch/ TALIP_991834413_1_101.arc ';

As a result of the above query until it returns to zero.

5. [Baku] Stop the redo apply process in standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6. [Baku] Finish to apply archive logs copied from primary.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

If you get an error, it means there are redo logs not applied. Consider 2th and 4th steps. You can also continue with following command;

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

In this situation you can open database in 8th step.  If you get no error, continuw with 7th step.

7. [Baku] Switch standby database to primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

8. [Baku] Open database.

SQL> ALTER DATABASE OPEN;

After opening standby database as primary with failover you must take full backup.

Failover ile Standby veritabanı primary olarak açıldığında mutlaka full yedek alınmalıdır. And as soon as possible  you must configure a new standby database. Please note that our data are very valuable for us 🙂

Talip Hakan Öztürk

Creating Standby Database with RMAN Active Database Duplication in 11gR2

June 26, 2011 2 comments

Hello Friends,

In this article, I will write steps required for installation about Standby database creation with RMAN active database duplication. There are several methods for the installation of the Physical Standby database. These are using cold backup (database closed while the manual copying of data files), RMAN with the taking a backup of the primary database (main database) and restoring it in standby database (backup database) . I’ll explain below, the creation of standby database through RMAN active database duplication in 11g R2.

First let’s select our scenario. We are establishing a bank. Our bank  will operate in Turkey and Azerbaijan 🙂  General directorate of the Bank will locate in Istanbul and our disaster location will locate in Baku.  The bussines task is to create a backup of the extraordinary situation in Baku.  Our managers will be doing real-time reporting through Baku Standby database. Our databases are 11g R2 and will be working on Oracle Enterprise Linux 5 as the operating system . For easy understanding of the manifestations I have given server names as Istanbul (primary) and Baku (standby). My primary database name is TALIP which is located in Istanbul. And standby database name is TALIPDR which will be located in Baku. I will  remind you the servername  [server]  before each processing step .I want to make more of a reminder. If we run commands on linux, it will start with “$”,  on sqlplus is “SQL>” and, while on rman “RMAN>” as a start.

In short, our structure is;

Sunucu adı IP Veritabanı adı Rolü
Istanbul 192.168.2.101 TALIP Primary
Baku 192.168.2.102 TALIPDR Standby

1- According to article https://taliphakanozturken.wordpress.com/2010/12/12/oracle-enterprise-linux-5-installation/  I have written about installation of Oracle Enterprise Linux. During the installation of the server as manifestations of Istanbul and Baku, I would set my ip addresses according to the above table.

2- According to article https://taliphakanozturken.wordpress.com/2010/12/21/oracle-database-11g-r2-preinstallation-requirements-on-linux/ we must set kernel parameters .

3- According to article https://taliphakanozturken.wordpress.com/2011/01/01/oracle-database-11g-r2-installation/ ,  We are doing the installation of oracle database 11g r2 in Istanbul (primary) server.  On  the  16th Step I give the name as “TALIP” to the “Global Database Name” and “SID” .

4-  We are installing only oracle software in Baku (standby) server . Don’t create a database. We will create a standby database with RMAN later.

5-  Our primary database must be in archive log mode in Istanbul (primary) server. If it is not in arhive log mode then you can do it with following.

First, let’s check whether the primary database is in as follows ;

 select log_mode from v$database;

If it is  not in arhive log mode then we will see log_mode as (NOARCHIVELOG) . Already we are a bank 🙂  and our database met be accurate in archive mode

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

6- [Istanbul]  Copy oracle password file to standby server (Baku) with ftp or scp command on Linux.

$scp orapwTALIP oracle@192.168.2.102:/oracle/ora11g/dbs/orapwTALIPDR

7- [Baku] Create “adump” folder in default location on standby server. Because the database will look for it on startup. If can not find it in this folder then returns an error at startup.

$mkdir $ORACLE_BASE/admin/$ORACLE_SID/adump

8- [Baku] Create pfile parameter file for standby database . ( under $ORACLE_HOME/dbs) And write only db_name in it.

 $vi $ORACLE_HOME/dbs/initTALIPDR.ora

Write db_name parameter in initTALIPDR.ora

db_name='TALIPDR'

9- [Baku ] On standby site open listener.ora file and edit it as below.

 $vi $ORACLE_HOME/network/admin/listener.ora

My ORACLE_HOME directory is /oracle/ora11g .

LISTENER =
   (DESCRIPTION_LIST =
        (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = baku)(PORT = 1521))
        )
 )

SID_LIST_LISTENER =
  (SID_LIST =
        (SID_DESC =
        (GLOBAL_DBNAME = TALIPDR)
        (SID_NAME = TALIPDR)
        (ORACLE_HOME = /oracle/ora11g)
        )
   )

10- [Baku] Stop/Start listener.

$lsnrctl stop
$lsnrctl start

11- [Istanbul ve Baku ikisinde de yapılacak] Edit tnsnames.ora file on both server as belows.

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

Add following lines to tnsnames.ora file

TALIP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALIP)
    )
  )

TALIPDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = baku)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALIPDR)
    )
  )

12-[Istanbul] Need to create standby redo log file in the primary database as the number of online redo log files . There are 3 redo log files by default. And I create 3 standby redo files. These files will be moved to standby database (Baku)with RMAN.

SQL>alter database add standby logfile '/oracle/ora11g/data_TALIP/srl01.log' size 10M;

SQL>alter database add standby logfile '/oracle/ora11g/data_TALIP/srl02.log' size 10M;

SQL>alter database add standby logfile '/oracle/ora11g/data_TALIP/srl03.log' size 10M;

13-[Baku] Open TALIPDR database in nomount mode.

 SQL> startup nomount;

14-[Istanbul] let’s prepare our RMAN script to create standby database.

 $vi /oracle/ora11g/dataguard

Write following lines to script

 run {
         allocate channel prmy1 type disk;
         allocate channel prmy2 type disk;
         allocate channel prmy3 type disk;
         allocate channel prmy4 type disk;
         allocate auxiliary channel stby type disk;
duplicate target database for standby from active database nofilenamecheck
         spfile
             parameter_value_convert 'TALIP','TALIPDR'
                  set db_file_name_convert='/TALIP/','/TALIPDR/'
                   set log_file_name_convert='/TALIP/','/TALIPDR/'
                   set db_unique_name='TALIPDR'
                   set control_files='/oracle/ora11g/data_TALIP/TALIPDR.ctl'
                   set log_archive_max_processes='5'
                   set fal_client='TALIPDR'
                   set fal_server='TALIP'
                   set standby_file_management='AUTO'
                   set log_archive_config='dg_config=(TALIP,TALIPDR)'
                   set log_archive_dest_1='service=TALIP LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TALIP ;
         sql channel prmy1 "alter system set log_archive_config=''dg_config=(TALIP,TALIPDR)''";
         sql channel prmy1 "alter system set log_archive_dest_2=''service=TALIPDR LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TALIPDR''";
         sql channel prmy1 "alter system set log_archive_max_processes=5";
         sql channel prmy1 "alter system set fal_client=TALIP";
         sql channel prmy1 "alter system set fal_server=TALIPDR";
         sql channel prmy1 "alter system set standby_file_management=AUTO";
         sql channel prmy1 "alter system set log_archive_dest_state_1=enable";
         sql channel prmy1 "alter system archive log current";
         sql channel stby "alter database recover managed standby database using current logfile disconnect";
}

15-[Istanbul] And run the script in RMAN

RMAN target sys/oracle@talip auxiliary sys/oracle@talipdr

RMAN>@dataguard

RMAN will do the all:)

16-[Istanbul ve Baku] Finally, with the two following queries can learn the status of our server.

To see instances status;

SQL>select status from v$instance

To see switchover status and our database role;

SQL>select switchover_status,database_role from v$database;

[Baku] To see archive logs whether is applied ;

select sequence#, first_time, next_time, applied
from v$archived_log
order by sequence#;

One of the important features of 11g, standby database can be opened in read-only mode when the archive is applying.

1- [Baku] Cancel the recovery process.

SQL>alter database recover managed standby database cancel;

2- [Baku] open the database read-only.

SQL>alter database open read only;

3- [Baku] Check database mode.

SQL>select open_mode from v$database;

4- [Baku] Start apply  again.

SQL>alter database recover managed standby database using current logfile disconnect;

5- [Baku] Check database mode.

SQL>select open_mode from v$database;

You can see result as “READ ONLY WITH APPLY”

In the following articles I will write what to do in case of switchover and failover.

Talip Hakan Öztürk

Brief Information About the Oracle Data Guard

Hello Friends,

In this article, I will write about Oracle’s disaster recovery (disaster recovery) solution. I will give the basic terminology in a short brief information about the Data Guard .

First, let’s try to understand  the word “Disaster”. We can see some of the disasters occurred in many parts of the world by television, radio, newspapers, etc. through the media .  Some of them lived in the past such as Tsunami in Indonesia , hurricanes and hoses in America , close to the 1999 earthquake in Turkey. Such events is called as disaster. As a result of natural disasters,  If we lose our data center, I’m sure it will be very high cost to us. Our data is very valuable for us to say that even the company’s treasury. Not only natural disasters, instantaneous power failure, disk failures and user errors can give rise to events such as losing our data.

As data managers, we are obligated to take necessary actions before these disasters occur. Also need to consider taking the necessary actions which are very important. These are;

RPO (Recovery Point Objective) – How much data can you afford to lose?

RTO (Recovery Time Objective) – Without data access what time can you stand?

According to the answers of these questions We must establish the backup system. Not satisfied with just setting up our system, and we must monitor our system.

Now, let’s learn Oracle’s beautiful solution “Oracle Data Guard”.

Oracle Data Guard is the disaster recovery solution. Protects our production database from disasters, reducing the work load on it and more effective use it. Technology, first introduced by setting the standby database manually with Oracle 7. Appeared as a Data Guard with Oracle 8i. If we examine a Data Guard features from past to present;

ORACLE 8i

Read-Only Standby Database

Managed recovery

Remote archiving redo log files

ORALCE 9i

“Zero Data Loss” Integration

Data Guard Broker and Data Guard Manager GUI

Swithcover and Failover operations

Automatical synchronous

Logical Standby Database

Maximum Protection

ORACLE 10g

Real-Time Apply

Forced support for Oracle RAC

Fast-Start Failover

Asynchronous redo transfer

Flashback Database

ORACLE 11g

Active Standby Database (Active Data Guard)

Snapshot Standby

Heterogeneous platform support (Production –Linux, Standby – Windows)

Characteristics and evolved over time as it stands today.

DATA GUARD 11g SYNCHRONOUS REDO TRANSFER PROCESS ARCHITECTURE (SYNC)-ZERO DATA LOSS

Process flow;

1 – The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2 LNS (logwriter Network Service) reports to RFS (Remote File Service) committed redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply to standby database . In  Logical Standby this is made by LSP (Logical Standby Process) .

3 – RFS sends information to LNS that data is processed successfully. LNS transmits this information to LGWR . Finally, commit information is send to the user that initiated the transaction (transaction) .

Data transfer is ensured by the synchronous redo transfer. But there is a disadvantage. If  a network failure occurs between production database (Primary) and the standby database  or  Primary database can not access  to the standby database then the primary database  will hang until standby response. In other words, the primary database can not serve. To avoid such situation, I think the most logical one to use “NET_TIMEOUT” parameter. With this parameter you can determine the timeout period. In case of an outage, Primary waits until the timeout period and will continue to serve when timeout period expires. Default value of this parameter in 10g is 180s and in 11g is 30s.

DATA GUARD 11g ASYNCHRONOUS REDO TRANSFER PROCESS ARCHITECTURE (ASYNC)

Asynchronous reod transfer flow;

1 – The user initiates a transaction. This transaction is written to a redo buffer. When the user commit the transaction then the LGWR process writes it redo log file.

2 – LNS (logwriter Network Service) reports to RFS (Remote File Service) committed redo. RFS writes to standby redo log file. If we use physical standby, the MRP (Managed Recovery Process) will apply to standby database . In  Logical Standby this is made by LSP (Logical Standby Process) .

3 – Once Redo Buffer is recycled, LNS  automatically reads redo log files and begins to send redo from log files.

RFS doesn’t send information to LNS that data is processed successfully.

The most common used process architecture. Asynchronous redo transfer does not guarantee zero data loss. The system has recovered with minimal data loss.

DATA GUARD PROTECTION MODES

There are 3 protection modes. Summarized in the table below.

Mode The risk of data loss Transfer method Primary status
Maximum Protection Zero data loss – two-sided protection SYNC primary waits for the receipt of information. if it doesn’t respond to primary, primary hangs
Maximum Availability Zero data loss – single-sided protection SYNC primary waits for the receipt of information. if it doesn’t respond to primary, primary waits until the timeout parameter . (NET_TIMEOUT)
Maximum Performance least data loss ASYNC primary never waits for the receipt of information

PHYSICAL STANDBY – REDO APPLY

Physical Standby Database is block by block  copy of production database. It uses database recovery function to apply the changes. When redo apply is active, it can be opened for reporting and querying in read-only mode (a feature that comes with the Active Data Guard-11g). Also it can be used for backup operations. For opening in Read-Write mode, you must enable Flashback log . Onece Flashback log enabled you can open it in read-write mode after creating restore point. And again you can revert to standby. I have explained above, with one of the desired modes you can transfer redo. I’ll write the next article on Creating Standby Database with RMAN Active Database Duplication in 11gR2

In some cases, you may want to create a time lag between the time when redo data is received from the primary site and when it is applied to the standby database. If you enabled the delayed apply, then real-time apply will be disabled. Real-time apply can be activated as follows.

SQL>alter database recover managed standby database using current logfile disconnect;

If the real-time apply feature is enabled as shown above , log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins. As the remote file server (RFS) process writes the redo data to standby redo log files on the standby database, log apply services can recover redo from standby redo log files as they are being filled.

Delayed apply can be activated;

SQL> alter database recover managed standby database delay 10;

SNAPSHOT STANDBY(11g)

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. Snapshot standby is a feature that comes with 11g. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database. When it is converted back into a  physical standby database, it discars all local updates. You can do test, patch, etc.  by opening it in Read-write mode the test.

LOGICAL STANDBY – SQL APPLY

Logical Standby Databases are logically identical to primary databases although the physical organization and structure of the data can be different. Logical Standby Databases are updated using SQL statements. Logical standby database can be used for recovery and reporting simultaneously. Determine if the primary database contains tables and datatypes that were not supported by a logical stand by database. If the primary database contains tables that were unsupported, log apply services will exclude the tables applying to the logical stand by database. Run the following queries to see unsupported tables.

 select * from DBA_LOGSTDBY_UNSUPPORTED;

 select * from LOGSTDBY_UNSUPPORTED_TABLES;

ACTIVE DATA GUARD (11g)

Thanks to this feature that comes with 11g standby database can be open in read-only mode while redo apply  (Read-Only with Apply). Therefore, the database is called the Active Standby. If we look at in terms of benefits to us of the Active Data Guard; We can make our real-time reporting (while continuing to apply redo),backup operations through the active standby database.Thus, we will reduce the processing load of production. Production database will serve our customers more effectively.

The other beauty that comes with 11g R2 is standby database count. Prior to 11g R2, you can use 9 standby database at the same time. With 11g R2 you can use 32 standby database at the same time.

SWITCHOVER & FAILOVER

Finally, I want to mention briefly the terms of switchover and failover.Switchover is the planned role change. It does not require re-installation of a new database. It can be used to test interoperability of  standby database, OS and hardware maintenance. The failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database.

Pictures that I used from sources:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/toc.htm

Oracle Data Guard 11g Handbook – Charles Kim

Talip Hakan Öztürk

%d bloggers like this: