CUNEYT YILMAZ - ORACLE BLOG: Nisan 2011

Oracle 11g R2 - Configuring Data Guard / Part III

Performing a Switchover Test

The last article is about performing a switchover test. After you configure the Data Guard configuration, it’s a good practice to simulate a disaster. There are also other ways but I’m going to make a switchover test only.

*** Be careful about the server that you execute the commands on. In every section, there’s a note about on which server that the script is going to be executed***

PRIMARY SERVER

SQL/PLUS

[oracle@rome ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 28 10:43:05 2010

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hr.emp_hist as select * from hr.employees;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> exit

DGMGRL

[oracle@rome ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show database verbose 'primary';

Database - primary

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

primary

Properties:

DGConnectIdentifier = 'primary'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'MANUAL'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = ''

LogFileNameConvert = ''

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'rome.europe'

SidName = 'primary'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rome.europe)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL.europe)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

STANDBY SERVER

[oracle@venice ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show database verbose 'sby1';

Database - sby1

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

sby1

Properties:

DGConnectIdentifier = 'sby1'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '5'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/primary/, /sby1/'

LogFileNameConvert = '/primary/, /sby1/'

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'venice.europe'

SidName = 'sby1'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=venice.europe)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sby1_DGMGRL.europe)(INSTANCE_NAME=sby1)(SERVER=DEDICATED)))'

StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

PRIMARY SERVER

DGMGRL> edit database 'primary' set property DbFileNameConvert='/sby1/, /primary/';

Property "dbfilenameconvert" updated

DGMGRL> edit database 'primary' set property

> LogFileNameConvert='/sby1/, /primary/';

Property "logfilenameconvert" updated

DGMGRL>

DGMGRL> show configuration ;

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

sby1 - Primary database

primary - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

SQL> select status from v$instance;

STATUS

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

MOUNTED

STANDBY SERVER

DGMGRL> show configuration

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

sby1 - Primary database

primary - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

sby1

LATER SWITCHOVER TO PRIMARY AGAIN

SERVER: SBY1

DGMGRL> switchover to primary;

Performing switchover NOW, please wait...

New primary database "primary" is opening...

Operation requires shutdown of instance "sby1" on database "sby1"

Shutting down instance "sby1"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "sby1" on database "sby1"

Starting instance "sby1"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "primary"

DGMGRL>

DGMGRL> show configuration;

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

primary - Primary database

sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Oracle 11g R2 - Configuring Data Guard / Part II

Enabling Data Guard Broker

The following article, depends on the previous article which is “Configuring Data Guard Part I”.

After I created the physical standby server, although it’s necessary, I decided to enable Data Guard Broker. The advantage of Data Guard Broker is easier management. You’ll see this on my other article (Part III).

So here are the steps.

*** Be careful about the server that you execute the commands on. In every section, there’s a note about on which server that the script is going to be executed***

PRIMARY SERVER

SQL> show parameter dg_broker

NAME TYPE VALUE

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

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0

/db_1/dbs/dr1primary.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0

/db_1/dbs/dr2primary.dat

dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME TYPE VALUE

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

dg_broker_start boolean TRUE

STANDBY SERVER

SQL> show parameter dg_broker

NAME TYPE VALUE

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

dg_broker_config_file1 string /u01/app/oracle/product/11.2.0

/db_1/dbs/dr1primary.dat

dg_broker_config_file2 string /u01/app/oracle/product/11.2.0

/db_1/dbs/dr2primary.dat

dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME TYPE VALUE

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

dg_broker_start boolean TRUE

LISTENER.ORA --STANDBY SERVER -- Add this section to listener.ora.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = sby1_DGMGRL.europe)

(SID_NAME = sby1)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1))

)

lsnrctl stop

lsnrctl start

LISTENER.ORA --PRIMARY SERVER -- Add this section to listener.ora.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = primary_DGMGRL.europe)

(SID_NAME = primary)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1))

)

lsnrctl stop

lsnrctl start

Still on the primary server

[oracle@rome ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show configuration

ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

DGMGRL> create configuration 'DGConfig1' as

> primary database is 'primary'

> connect identifier is primary;

Configuration "DGConfig1" created with primary database "primary"

DGMGRL> show configuration

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

primary - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> add database 'sby1' as

> connect identifier is sby1;

Database "sby1" added

DGMGRL> show configuration

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

primary - Primary database

sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - DGConfig1

Protection Mode: MaxPerformance

Databases:

primary - Primary database

sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Oracle 11g R2 - Configuring Data Guard / Part I

High Availability - Creating Standby Server

After several busy weeks, I didn’t want to miss this chance. It was definetely very overloaded time. I spent one week for a LVC (Live Virtual Class) and the participants were from all around the world. Then I went to Malta to provide consultancy service. And now I’m in Bucharest delivering a Siebel 8.1 Core Consultant Course. At last I start to write a new article. I decided to give some examples from high availability point of view. Actually, I was involved in a project and this was the main idea. So here is the demonstration of Data Guard configuration on 11g R2 database.

In order to demonstrate this infrastructure, I had configured 2 VMware images based on Linux operating systems. I configured all network configurations and hosts files on these 2 Linux computers. So I can easily access from both computers to each other. All the prerequisites are done for Oracle 11g R2 database which includes additional rpm packages, creating OS groups & users, environment variables etc..

In addition to these, I’ve installed Oracle Software on both of the servers and create a database on one of the instance which will be PRIMARY server.

So at this point, there are 2 servers. A database is already created on one of them. There is only Oracle Software installed on the other. Host names of the computers are my favorite cities in Italy. :) Primary server is Rome and Standby server will be Venice.

I created a pfile on the standby server. $ORACLE_HOME/dbs/initSBY1.ora

The contents of the pfile is below, only one parameter:

DB_NAME=SBY1

The next step is configuring the network components of the databases. The content of the listener.ora files are below:

LISTENER.ORA --STANDBY SERVER so on Venice

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = venice.europe)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = sby1.europe)

(SID_NAME = sby1)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1))

)

TNSNAMES.ORA --PRIMARY & STANDBY Servers so on Rome & Venice

SBY1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = venice.europe)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = sby1.europe)

)

PRIMARY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rome.europe)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary.europe)

)

There is one more step before generating the standby database. This should be done before the DUPLICATE command from RMAN console. Otherwise you may experience an Oracle Error 00845: Memory_target is not supported on the standby server. The reason of this is the temp device size is less than Memory_target size. So I just increased the device size by executing these commands with Root user account.

root@venice ~]# mount -t tmpfs shmfs -o size=3g /dev/shm

[root@venice ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

45G 19G 24G 45% /

/dev/sda1 99M 12M 82M 13% /boot

tmpfs 3.0G 0 3.0G 0% /dev/shm

/dev/hdc 2.8G 2.8G 0 100% /media/Enterprise Linux dvd 20090127

shmfs 3.0G 0 3.0G 0% /dev/shm

Then I started the instance on the standby server (Venice):

EXPORT ORACLE_SID=sby1

sqlplus / as sysdba

startup nomount pfile= /u01/app/oracle/product/11.2.0/db_1/dbs/initsby1.ora

Standby server is ready for the Data Guard configuration. Now there are several steps on the Primary server. The tasks below will be done on the server named “Rome”.

sqlplus / as sysdba

alter system set log_archive_dest_1 = 'SERVICE=sby1 ASYNC VALID_FOR = (ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=sby1';

alter system set log_archive_config = 'dg_config=(primary,sby1)';

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl01.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl02.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl03.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl04.log' size 50M;

The recommended practice is the number of standby log files should be greate than the number of redo log files. That’s why I created one extra standby log on the primary. It’s very obvious that Primary database should be running in Archivelog mode. So:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/flash_recovery_area

Oldest online log sequence 38

Next log sequence to archive 40

Current log sequence 40

Before going one step ahead, check the archive log destinations. There should be 2 destinations, one on the localhost and one on the remote server which is standby server. If not, take the corrective action.

In order to establish a succesful connection from Primary to Standy server, you should create a password file on the standby server. The steps are:

STANDBY SERVER

[root@venice ~]# rpm -q vsftpd

vsftpd-2.0.5-12.el5

[root@venice ~]# service vsftpd start

Starting vsftpd for vsftpd: [ OK ]

[root@venice ~]#

PRIMARY SERVER

cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwprimary.ora

ftp venice

user: oracle

pwd: oracle

put orapwprimary.ora

STANDBY SERVER

mv /home/oracle orapwprimary.ora /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsby1.ora

PRIMARY SERVER

rman target sys/xxx@primary auxiliary sys/yyy@sby1

RMAN>@dg.txt

DG.TXT Content is:

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

spfile parameter_value_convert 'primary','sby1'

set db_unique_name='sby1'

set db_file_name_convert='/primary/','/sby1/'

set log_file_name_convert='/primary/','/sby1/'

set control_files='/u01/app/oracle/oradata/sby1.ctl'

set log_archive_max_processes='5'

set fal_client='sby1'

set fal_server='primary'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(primary,sby1)'

set log_archive_dest_1='service=primary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primary';

}

This will take sometime. After it finishes the process, the next step is:

PRIMARY SERVER

SQLPLUS / AS SYSDBA

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

SEQUENCE# FIRST_TIM NEXT_TIME

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

31 27-OCT-10 27-OCT-10

32 27-OCT-10 28-OCT-10

33 28-OCT-10 28-OCT-10

34 28-OCT-10 28-OCT-10

alter system switch logfile;

And the last step is starting the Apply Service on the Standby server:

STANDBY SERVER

sqlplus sys/yyy@sby1 as dba

SQL> select status from v$instance;

STATUS

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

MOUNTED

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

SEQUENCE# FIRST_TIM NEXT_TIME

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

31 27-OCT-10 27-OCT-10

32 27-OCT-10 28-OCT-10

33 28-OCT-10 28-OCT-10

34 28-OCT-10 28-OCT-10

After several log swithces on the primary server --> Query the archived_log on the standby server

SQL> select sequence#, first_time, next_time, applied from v$archived_log

2 order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

31 27-OCT-10 27-OCT-10 NO

32 27-OCT-10 28-OCT-10 NO

33 28-OCT-10 28-OCT-10 NO

34 28-OCT-10 28-OCT-10 NO

35 28-OCT-10 28-OCT-10 NO

36 28-OCT-10 28-OCT-10 NO

37 28-OCT-10 28-OCT-10 NO

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

Database altered.

SQL> select sequence#, first_time, next_time, applied from v$archived_log

2 order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

31 27-OCT-10 27-OCT-10 YES

32 27-OCT-10 28-OCT-10 YES

33 28-OCT-10 28-OCT-10 YES

34 28-OCT-10 28-OCT-10 YES

35 28-OCT-10 28-OCT-10 YES

36 28-OCT-10 28-OCT-10 YES

37 28-OCT-10 28-OCT-10 YES

38 28-OCT-10 28-OCT-10 YES

39 28-OCT-10 28-OCT-10 IN-MEMORY

CUNEYT YILMAZ - ORACLE BLOG

7 Nisan 2011 Perşembe

Oracle 11g R2 - Configuring Data Guard / Part III

Oracle 11g R2 - Configuring Data Guard / Part II

Oracle 11g R2 - Configuring Data Guard / Part I

About me

Useful Links

Blog History