DBMentors is a solution oriented group, started by a team of qualified and committed professionals with vast experience in IT industry. The team has in-depth technical and design expertise with highest standards of programming quality.

Pages

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, May 19, 2013

Configuring Oracle 11gR2 Data Guard - Physical Standby

Brief: Data Guard is an Oracle feature that primarily provides database redundancy. This is done by having a standby (physical copy) database, preferably in another location and on separate disk. This standby database is maintained by applying the changes from the primary database to it. Standby databases can be maintained with either Redo (Physical standby) or SQL (Logical standby).

Environment:
- Oracle 11gR2 (11.2.0.3) on Windows 2008R2
- The Primary databaseis called dubai and resides on a server called OR-11
- The physical standby will be called riyadh and reside on a server called OR-12
- The Primary database is archivelog mode (required)

Preparing the Primary for Data Guard

There are some preparation on Primary database to make it ready for Data Guard. Database must be in archivelog mode.
First, for a Physical Standby to be an exact copy it must receive redo for the changes made to the primary. With Oracle a database user can instruct the database to not log redo (e.g. the NOLOGGING clause). For a physical standby database this is a big problem, so you must make sure that redo is logged regardless of what a user tells the database to do. To do this you turn on Forced Logging.

Make sure that when we add or drop datafiles on our primary database, that those files are also added or dropped on the standby.

SQL> alter system set standby_file_management = 'AUTO';

System altered.5)

Make sure the primary database has Standby Log Files. Standby Log Files are

used by a standby database to store the redo it receives from the primary database. Standby log files should be created the same size as the online logfiles. Preferably you should have as many, or more, standby logfile groups as online logfile groups.

Flashback Database allows you to flashback your database to a previous point in time. This is very useful when a failover occurs because you can use it to flashback your old primary database and then activate it as a Standby. If you don’t have flashback correctly configured across your Data Guard configuration then you have to rebuild the Standby which means

copying all those datafiles again. In addition to this use, Flashback Database can you save you from having to restore/recover your database in some situations.

To setup Flashback Database you need to configure the Fast Recovery Area first. To do this set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters if not set already.

Ensure the databases in configuration will be able to talk to each other. This is required before creating the database if we want to use the RMAN “duplicate from active database” feature. To do this configure both the listener and TNSNames.

This statement says to use the DB_RECOVERY_FILE_DEST as the location to archive to, that it should be used to archive all logfiles in any (all) roles, and that this is for the database dubai. The manual says to use online_logfiles; however, this will result in the Standby being unable to archive the standby logfiles because they are not online logfiles. If you use all_logfiles both the primary and standby will be able to archive logfiles whether they are online or standby. You’ll want this if you’re backing up on the standby and want to have

archived logfiles backed up there too. You could configure this to archive only on the primary or only on the standby if you’d like.

12) Configure the transport of the redo to the Standby database.
SQL> alter system set log_archive_dest_2 = 'service=riyadh async valid_for=(online_logfile,primary_role) db_unique_name=riyadh';
System altered.

This statement says that if it is the primary database, it should transport online logfiles using the service name "riyadh" and that the target is the database "riyadh".

13) Change parameter FAL_SERVER. This specifies where the database should look for missing archive logs if there is a gap in the logs. It is used when the database is in the standby role and has a gap in the redo it has received. This type of situation occurs when redo transport is interrupted, for example when you do maintenance on the standby server. During that maintenance no logs would have been transported and a gap would exist. Setting this parameter allows the standby to find the missing redo and have it transported.

SQL> alter system set fal_server = 'riyadh';

System altered.

14) Let the database know what other databases are in the Data Guard configuration.
SQL> alter system set log_archive_config = 'dg_config=(dubai,riyadh)';

System altered.

Preparing the Standby environment
We are now ready to setup the standby environment for creation of the Standby database.

1) Create a password file and server parameter file (spfile) for the new standby database. The password file can be copied directly over, and only needs it name changed.
copy password file
D:\app\Inam\product\11.2.0.3\dbhome_1\database\PWDdubai.ORA (from dubai server)
to
D:\app\Inam\product\11.2.0.3\dbhome_1\database\PWDriyadh (renamed on riyadh server)

2) Create pfile for standby database

Just create INITriyadh.ORA on standby server with parameters (D:\app\Inam\product\11.2.0.3\dbhome_1\database). I just set one required parameter.
INITriyadh.ora
db_name='riyadh'

1)Make sure our destinations are all valid on both the primary and the standby: select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <=2;
On standby

DEST_ID

STATUS

DESTINATION

ERROR

1

VALID

USE_DB_RECOVERY_FILE_DEST

2

VALID

dubai

On Primary

DEST_ID

STATUS

DESTINATION

ERROR

1

VALID

USE_DB_RECOVERY_FILE_DEST

2

VALID

riyadh

The destinations should show as VALID.2) see if the redo is actually being applied, From the primary run:select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED fromV$ARCHIVED_LOG where name = 'riyadh' order by FIRST_TIME;

SEQUENCE#

FIRST_TIME

NEXT_TIME

APPLIED

ARCHIVED

84

5/19/2013 09:43:05 AM

5/19/2013 10:05:21 AM

YES

YES

85

5/19/2013 10:05:21 AM

5/19/2013 10:05:57 AM

YES

YES

86

5/19/2013 10:05:57 AM

5/19/2013 10:10:43 AM

YES

YES

87

5/19/2013 10:10:43 AM

5/19/2013 10:22:14 AM

YES

YES

88

5/19/2013 10:22:14 AM

5/19/2013 10:31:42 AM

YES

YES

You should see YES for the APPLIED and ARCHIVED columns if both archiving and redo apply are working correctly.

3)If you notice that logs aren’t applying, it is possible you might have a gap in your redo, in which case the standby cannot apply. If you have the FAL_SERVER parameter set correctly, this shouldn’t be a problem. Youcan check to see if there are any gaps in the redo, by running the following query on the primary:

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID =2

STATUS

GAP_STATUS

VALID

NO GAP

It should return VALID and NO GAP if everything is OK. If you want to play around with this and see how the FAL_SERVER works you can shutdown your standby, switch out several logs, wait a bit, bring your standby back up, and switch another log out. You should soon see a GAP appear. If your FAL_SERVER parameter is correct on the standby and points to the service name of the primary, then the logs should be brought over and applied.

4) The V$DATAGUARD_STATUS view is very useful for looking for errors or just seeing what has happened. You can query it on the primary and standby to see status for that database.

select * from V$DATAGUARD_STATUS order by TIMESTAMP;

FACILITY

SEVERITY

DEST_ID

MESSAGE_NUM

ERROR_CODE

CALLOUT

TIMESTAMP

MESSAGE

Log Transport Services

Informational

0

2

0

NO

5/19/2013 10:05:16 AM

ARC1: Archival started

Log Transport Services

Informational

0

1

0

NO

5/19/2013 10:05:16 AM

ARC0: Archival started

Log Transport Services

Informational

0

4

0

NO

5/19/2013 10:05:17 AM

ARC3: Archival started

Log Transport Services

Informational

0

5

0

NO

5/19/2013 10:05:17 AM

ARC3: Becoming the 'no FAL' ARCH

Log Transport Services

Informational

0

3

0

NO

5/19/2013 10:05:17 AM

ARC2: Archival started

5)Sometimes you want to really know the data is there. A more reassuring way to verify is

to actually check the standby and verify that the new data is there. You can do this by changing the standby’s role to readonly. First you’ll need to stop managed recovery and then open databaase:

You can now run your query to see that the changes have come across. When you’re done, do not forget to take your database back to MOUNT and restart recovery.

6)If you prefer, 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.SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.

Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.

Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default, for a newly created standby database, the primary database is in maximum performance mode.SELECT protection_mode FROM v$database;

PROTECTION_MODE

MAXIMUM PERFORMANCE

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.-- Maximum Availability.ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=riyadh';ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;-- Maximum Performance.ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=riyadh';ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;-- Maximum Protection.ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=riyadh AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=riyadh';SHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;ALTER DATABASE OPEN;Note:you can switch to maximum availability and maximum performance any time without bouncing the instance. But once you change to maximum protection, and you want to change to maximum availability or maximum performance back,you will have to do the below- Shutdown the instance- Startup instance with mount- Change to maximum availability or maximum performance- Open the database

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. Disconnect all sessions before doing this activity other wise you may get the error ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected.

After testing switch the primary database back to the original server by doing another switchover. This is known as a switchback.

9) Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements. For this post purpose I just did the shutdown abort on primary "dubai" database.

select name,open_mode,database_role,db_unique_name from v$database;

NAME

OPEN_MODE

DATABASE_ROLE

DB_UNIQUE_NAME

DUBAI

READ ONLY

PHYSICAL STANDBY

riyadh

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

select name,open_mode,database_role,db_unique_name from v$database;

NAME

OPEN_MODE

DATABASE_ROLE

DB_UNIQUE_NAME

DUBAI

MOUNTED

PRIMARY

riyadh

SQL> ALTER DATABASE OPEN;

Database altered.

select name,open_mode,database_role,db_unique_name from v$database;

NAME

OPEN_MODE

DATABASE_ROLE

DB_UNIQUE_NAME

DUBAI

READ WRITE

PRIMARY

riyadh

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily, If not, the whole setup process must be followed, but this time using the original primary server as the standby.