Friday, September 28, 2018

Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I


Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I

Here in this article I am going to cover steps by step approach to configure Oracle 11g Data Guard Physical Standby.

In my case, Ingredients to simulate Physical Standby data guard environment are as below:

  • 2 VM’s, Primary and DR with enough CPU and RAM in order to run oracle database.
  • Primary server configuration
    • CentOS 6.5
    • Server name: pr
    • IP: 192.168.17.131
    • Oracle 11g software plus oracle instance.
    • Oracle SID/Global_name: RTS
    • Oracle db_unique_name: RTS
  • Secondary server configuration
    • CentOS 6.5
    • Server name: dr
    • IP: 192.168.17.132
    • Oracle 11g software only.
    • Oracle SID/Global name: RTS
    • Oracle db_unique_name: RTSDR

Note:

  • Oracle version on Primary and secondary should be identical. i.e. In my case it’s 11.2.0.1.0
  • db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
  • Primary and DR server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

 

Primary Server Configurations:


<Step – 1 >

Enable Archive log:
Primary database is in No Archive Mode, Enable it:

 SQL> archive log list
 Database log mode No Archive Mode
 Automatic archival Disabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Current log sequence 3
 SQL> SHUTDOWN IMMEDIATE;
 SQL> STARTUP MOUNT;
 SQL> ALTER DATABASE ARCHIVELOG;
 SQL> ALTER DATABASE OPEN;
 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 1
 Next log sequence to archive 3
 Current log sequence 3

<Step – 2 >

Enable force logging with the help of following SQL command:

SQL> ALTER DATABASE FORCE LOGGING;

<Step – 3 >

Verify initialization parameters db_name and db_unique_name on primary, In my case those are set to: RTS

 SQL> show parameter db_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_name string RTS
 SQL> show parameter db_unique_name
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_unique_name string RTS

<Step – 4 >

Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard config.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';

Ensure your above changes as below:

 SQL> show parameter LOG_ARCHIVE_CONFIG
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_config string DG_CONFIG=(RTS,RTSDR)

<Step – 5 >

Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery area for remote archive log destination.

Note: Service and DB_UNIQUE_NAME reference the remote standby location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';

Ensure your above changes as below:

SQL> show parameter LOG_ARCHIVE_DEST_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V
 ALID_FOR=(ONLINE_LOGFILES,PRIM
 ARY_ROLE) DB_UNIQUE_NAME=rtsdr

Enable LOG_ARCHIVE_DEST_2:

SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_dest_state_2 string ENABLE

<Step – 6 > 

Set log_archive_format parameter as below:

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_format string %t_%s_%r.arc

Set log_archive_max_processes parameter to 30:

SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 log_archive_max_processes integer 30

Set remote_login_passwordfile parameter to exclusive:

 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
 SQL> show parameter remote_login_passwordfile
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 remote_login_passwordfile string EXCLUSIVE

<Step – 7 > 

Set fal_server and fal_client parameter for primary database:

SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_server string RTSDR
SQL> alter system set fal_client='RTS';
 SQL> show parameter fal_client
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 fal_client string RTS

Set standby_file_management parameter to auto:

 SQL> alter system set standby_file_management=auto;
 SQL> show parameter standby_file_management
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 standby_file_management string AUTO

<Step – 8 > 

Configure listener file and copy it to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Listener file:

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = RTS)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RTS)
 ) )
 LISTENER =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 ADR_BASE_LISTENER = /u01/app/oracle

<Step – 9 > 

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both the servers.

 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 (GLOBAL_NAME = RTS)
 (UR=A)
 ) )

Ensure your above configuration by TNSPing utility on Primary and standby:

 [oracle@pr admin]$ tnsping RTS
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:09
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts) (GLOBAL_NAME = RTS)))
 OK (10 msec)
 [oracle@pr admin]$ tnsping RTSDR
 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014 06:04:15
 Copyright (c) 1997, 2009, Oracle. All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS)))
 OK (20 msec)

<Step – 10 > 

Backup primary database via RMAN backup utility:

 [oracle@pr admin]$ rman target /
 Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 connected to target database: RTS (DBID=1421312347)
 RMAN> backup database plus archivelog;

Note: Backup location: Flash Recovery Area.

<Step – 11> 

Create Standby control file.

SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';

<Step – 12>

Create pfile from spfile:

SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;

<Step – 13>

After creating parameter file as above, edit following changes in newly created pfile:

 *.db_unique_name='RTSDR'
 *.fal_server='RTS';
 *.log_archive_dest_2='SERVICE=RTS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'

<Step – 14>

Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr bkup]$ scp initRTS.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl & /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
 [oracle@pr bkup]$ scp stndbyctrl.ctl oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl

Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 [oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
 [oracle@pr dbs]$ scp orapwRTS oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Copy Listener file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

 [oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
 [oracle@pr admin]$ scp listener.ora oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

Edit DR database listener file with host name. ( i.e. (HOST = dr))

And finally copy RMAN backup to DR @ flash recovery area.

 [oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
 [oracle@pr flash_recovery_area]$ scp -r RTS oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/

Standby/DR Server Configurations:


<Step – 15>

Startup standby database in mount state:
Set following environment variable as oracle user OR edit those in /home/oracle/.bash_profile in order to set it for every time while oracle user logged in:

 export ORACLE_SID=RTS
 export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
 export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
 
 SQL> sqlplus / as sysdba
 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 566231800 bytes
 Database Buffers 398458880 bytes
 Redo Buffers 5988352 bytes
 Database mounted.

<Step – 16>

Create spfile from pfile:

 SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';

<Step – 17>

Start listener on standby:

[oracle@dr ~]$ lsnrctl start

<Step – 18>

Create follwoing directories on DR server, in case those are not available:
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_area

<Step – 19>

Restore and recover database @ DR with RMAN backup utility.

RMAN> list backup of database summary;
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 6 B F A DISK 05-OCT-14 1 1 NO TAG20141005T065604
RMAN> restore database;
RMAN> recover database;

Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.

Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554

<Step – 20>

Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.
Note: Create one additional standby redolog file on both.

 SQL> sqlplus / as sysdba
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby2.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby3.log') size 51M;
 SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/RTS/stndby4.log') size 51M;
 SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;

<Step – 21>

Start apply process @ DR.

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;

In case of you want to cancel apply process, issue following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

<Step – 22>

After graceful completion of above apply process, verify archive logs on Primary as well as DR.
On Primary: 

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 10
 Current log sequence 10

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 8
 Next log sequence to archive 0
 Current log sequence 10

By above result, archive logs on both the databases are in sync now.

To test your data guard configuration, generate archive logs on primary site and verify it on DR site:

SQL> alter system switch logfile; //Give this command multiple times for testing.

On Primary:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 21
 Current log sequence 21

OR

SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

On Standby:

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 19
 Next log sequence to archive 0
 Current log sequence 21

OR

 SQL> select max(sequence#) from v$archived_log;
 MAX(SEQUENCE#)
 --------------
 20

Cheers!! Our Data Guard configuration has been configured successfully.

Verify database roles by below mentioned SQL query:

On Primary:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

On DR:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

You can also verify total number of log sequence generated and applied on DR site, by below SQL query:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 SQL> SELECT sequence#, first_time, next_time, applied
 FROM v$archived_log
 ORDER BY sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
 ---------- --------- --------- ---------
 3 04-OCT-14 04-OCT-14 YES
 4 04-OCT-14 04-OCT-14 YES
 5 04-OCT-14 04-OCT-14 YES
 6 04-OCT-14 05-OCT-14 YES
 7 05-OCT-14 05-OCT-14 YES
 8 05-OCT-14 05-OCT-14 YES
 9 05-OCT-14 05-OCT-14 YES
 10 05-OCT-14 05-OCT-14 YES
 11 05-OCT-14 05-OCT-14 YES
 12 05-OCT-14 05-OCT-14 YES
 13 05-OCT-14 05-OCT-14 YES
 14 05-OCT-14 05-OCT-14 YES
 15 05-OCT-14 05-OCT-14 YES
 16 05-OCT-14 05-OCT-14 YES
 17 05-OCT-14 05-OCT-14 YES
 18 05-OCT-14 05-OCT-14 YES
 19 05-OCT-14 05-OCT-14 YES
 20 05-OCT-14 05-OCT-14 YES

In case of you are facing any kind of error than following SQL query will help you to diagnose it.

 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID

Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync.

OR

SQL> select message from v$dataguard_status;

Note: This command will give you appropriate message about the dataguard current status.

By default, for a newly created standby database, the primary database is in maximum performance mode.

Protection Mode:
Default protection mode of newly configured standby database would be maximum performance mode.
There are 3 protection modes: Maximum Availability, Maximum Performance and Maximum Protection. for more information, click me.

By above mentioned steps you can configure Data Guard in your environment, Stay tune with my next article about Data Guard Switch Over and Switch Back steps.

 

Oracle 11g Data Guard Switchover and Switchback – Active Data Guard Part-II


Description: Oracle 11g Logo

In my Previous article we have covered How to configured oracle 11g Data Guard on CentOS 6.5, Now we will look into switchover and switch back activity:

After configuring data guard, databases would be either primary and standby database role, and we can altered these roles without loss of data or without resetting logs. called switchover and switch back.

Switchover:

Before performing switchover, kindly verify the state of data guard on both the instances by following SQL queries:

 SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 SQL> SELECT sequence#, first_time, next_time, applied
 FROM v$archived_log
 ORDER BY sequence#;
 ...
 ...
 59 08-OCT-14 08-OCT-14 YES
 60 08-OCT-14 08-OCT-14 YES
 61 08-OCT-14 08-OCT-14 YES
 SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID
 SQL> select message from v$dataguard_status;

Note: This command will give you appropriate message about the data guard current status.

After getting confirmation on data guard smooth activity, we can instantiate switchover activity by issuing following sort of commands:

On Primary database:

Step-1>>
Connect to Primary database and convert primary database to standby.

 [oracle@pr ~]$ sqlplus / as sysdba
 SQL> alter database commit to switchover to standby;
 Database altered.

Step-2>>
Shutdown primary database:

SQL> shutdown immediate;

Step-3>>

Startup nomount old primary database as new standby database:

 SQL> startup nomount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 805307128 bytes
 Database Buffers 159383552 bytes
 Redo Buffers 5988352 bytes
 SQL> alter database mount standby database;
 Database altered.
 SQL> alter database recover managed standby database disconnect from session;
 Database altered.

Verify database role on old primary database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

 

On Standby database:

Step-4>>
On original standby database, Convert old standby database to primary database:

[oracle@dr ~]$ sqlplus / as sysdba

Step-5>>

Convert old standby database as primary and shutdown database:

 SQL> alter database commit to switchover to primary;
 Database altered.
 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.

Step-6>>

Startup old standby database as primary database:

 SQL> startup
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 717226744 bytes
 Database Buffers 247463936 bytes
 Redo Buffers 5988352 bytes
 Database mounted.
 Database opened.

Verify database role on old standby database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.

Note

To test above switchover activity, generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.

 

Switchback:
To switchback, you have to follow same above mentioned 6 steps.

 

How to open physical standby database in read only mode – Active Data Guard Part -III


Description: Oracle 11g Logo

One of the benefit of Physical standby data guard is to use standby database for query access for offloading and reporting purpose by making standby database as a read only mode. So that we can efficiently use or manage primary database resources for core purpose.

Note:
When physical standby database in read only mode, archive log transmission from primary database to standby database continues, but managed recovery is stopped. Once your database back to mount state than managed recovery will be start.

Previously, We covered procedure for Oracle 11g Data Guard Switchover and Switchback

Let me demonstrate the scenario for you, In this we will open our standby database in read-only mode for query access.

Verify database name, its open mode and database role by following command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Database is in mount state.

To make it online, gracefully shutdown the database and open in mount state as follows:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.

Issue following command to make database in read only mode:

SQL> alter database open read only;
Database altered.

Again verify database name, its open mode and database role, your database is in READ ONLY mode.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ ONLY PHYSICAL STANDBY

As we discussed, while standby database is in READ ONLY mode, archive log transfer from primary to standby database is continues, but managed recovery has been stopped.

To simulate environment, Manually i have generated archive logs at primary database and it automatically transferred to standby database while opened in READ ONLY mode.
Archive logs details on standby before generating multiple manual archive logs, Current log sequence: 61

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 0
Current log sequence 61

Archive logs details on standby after generating manual archive logs, Current log sequence: 66

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 61
Next log sequence to archive 0
Current log sequence 66

To get back to managed recovery, gracefully shutdown standby database, Startup with mount state and finally start managed recovery with the help of following sort of commands:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Verify database name, its open mode and database role by following command, Its get back to mount state.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

With above method we can open standby database in read only mode and get back to its managed recovery.

Kindly tune with me to know how to how to apply primary database redo information to standby database while database is in read only mode. Its new feature introduced in Oracle 11g.


 

how to apply primary database redo information to standby database while standby database is in read only mode – Active Data guard feature – Active Data Guard Part-IV


Description: Oracle 11g Logo

Active Data Guard

Oracle 11g introduced Active Data Guard new Feature, in this standby database is allowed to be open in read only mode for query access and managed recovery also started and standby database is in sync/up-to-date with primary database.
Note:
You need licence to get benefit of this feature.

Previously, we covered How to open physical standby database in read only mode

Let me demonstrate for you to how to do it:

Verify database name, its open mode and database role by following command:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Standby database is in mount state.

Following sort of command will use to enable active data guard feature at standby database:

Gracefully shutdown your standby database and startup with mount state:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 905972144 bytes
Database Buffers 553648128 bytes
Redo Buffers 7958528 bytes
Database mounted.

Open standby database is in read only mode:

SQL> alter database open read only;
Database altered.

Following command will help you to enable active dataguard feature.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

Again verify database name, its open mode and database role by following command, Your standby database is in READ ONLY WITH APPLY. It means redo apply process in place and running.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ ONLY WITH APPLY PHYSICAL STANDBY

With the help of above, we can open standby database in read only mode for query access along with redo apply.

 
-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.

[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files. I got the issue below wh...