Flashing Back an Activated Standby Database (Primary) into a Physical Standby Database

Hi,

One of my critical primary database was gone to the down due to an uncertain cause. We couldn’t open it.  I started the recovery on primary. During recovery, We also enabled flashback database and activated the standby as primary. When our team controls the new primary (activated standby database) the old primary is recovered and opened. We decided to use the old primary. But we activated standby database and want to back it  into physical standby.

Assume that a failover has been performed to a physical standby database and that Flashback Database was enabled at the time of the failover. The following steps bring the activated standby database (Now it is primary) back into the Oracle Data Guard configuration as a physical standby database.

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

Shut down the activated standby database mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in the previous step.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

To convert the database to a physical standby database, issue the following statement on the activated standby database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

To start transporting redo to the new physical standby database, perform the following steps on the primary database:

Issue the following query to see the current state of the archive destinations:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

Enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

Ensure the standby database begins receiving redo data from the primary database, and verify it was sent successfully.

Start Redo Apply on the physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Redo applying automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply needs to be restarted one or more times until it has applied beyond the SCN at which the primary database became the primary database.

Advertisements

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

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?

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

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

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