Oracle DBA: 2009

Tuesday, October 27, 2009

Enabling Fast Start Failover using DGMRL

This article show how to enable FSFO, you can refer to my earlier posts on how to implement Data Guard and the Broker.

In my test the environment is being operated at Max Performance Mode.

DGMGRL> show configuration

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - Physical standby database

Fast-Start Failover: DISABLED
Configuration Status:

SUCCESS

Since it is being operated at Max Performance let us make sure the property LogXptMode has the value SYNC.

DGMGRL> SHOW DATABASE 'mobpro_collab1' 'LogXptMode';

LogXptMode = 'ASYNC'
DGMGRL> SHOW DATABASE 'mobpro_collab2' 'LogXptMode';
LogXptMode = 'ASYNC'

On the primary we need to setup a property FastStartFailoverTarget to point to the standby, however this is important if there is more than one primary, In my case since I have one standby I can ignore it, for the sake of clarity I will setup value for this property.

DGMGRL> EDIT DATABASE mobpro_collab1 set property 'FastStartFailoverTarget'='mobpro_collab2';

Property "FastStartFailoverTarget" updated
DGMGRL> EDIT DATABASE mobpro_collab2 set property 'FastStartFailoverTarget'='mobpro_collab1';
Property "FastStartFailoverTarget" updated

The above command conveys that for the primary the target would be mobpro_collab2, if in case the standby becomes primary Iam indicating that for the failover it would be mobpro_collab1.

One more important property to be set is FastStartFailoverThreshold, it indicates that the observer or the standby will wait for so many seconds before initiating the failover, default is 30 secs.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;

Property "faststartfailoverthreshold" updated

One more important property worth discussing is FastStartFailoverAutoReinstate, default value is FALSE, what it does is it bring up the Primary after the FSFO if the issues on the primary is fixed, for now we wiull have this FALSE.

Next step will be to enable fast-start failover

DGMGRL> ENABLE FAST_START FAILOVER;

Enabled.

We will now start the observer

DGMGRL> START OBSERVER;

Observer started

Let look the output of the configuration,

DGMGRL> show configuration

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - (*) Physical standby database

Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS

DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED
Threshold: 45 seconds
Target: mobpro_collab2
Observer: collab1
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)

Let us now test the failover,

I will crash the primary database by terminating the smon process
collab1:/u01/app/oracle/diag/rdbms/mobpro_collab1/mobpro/trace[mobpro]$ ps -ef

grep smon
oracle 24021 1 0 11:31 ? 00:00:00 ora_smon_mobpro
oracle 30781 8828 0 12:11 pts/2 00:00:00 grep smon
collab1:/u01/app/oracle/diag/rdbms/mobpro_collab1/mobpro/trace[mobpro]$ kill -9 24021

Now on the other terminal of the observer, I can see this

12:12:21.38 Tuesday, October 27, 2009

Initiating Fast-Start Failover to database "mobpro_collab2"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "mobpro_collab2"
12:12:28.60 Tuesday, October 27, 2009

As you see it automatically failed over to the standby after waiting 45 secs which is the threshold.

I now bring up the primary and will reinstate

DGMGRL> startup mount

ORACLE instance started.
Database mounted.

DGMGRL> REINSTATE DATABASE mobpro_collab1;

Reinstating database "mobpro_collab1", please wait...
Operation requires shutdown of instance "mobpro" on database "mobpro_collab1"
Shutting down instance "mobpro"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "mobpro" on database "mobpro_collab1"
Starting instance "mobpro"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "mobpro_collab1" ...
Reinstatement of database "mobpro_collab1" succeeded

Let see the config now

DGMGRL> show configuration

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab2 - Primary database
mobpro_collab1 - (*) Physical standby database
Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

As you see we have got back the old primary, however the current primary continues the work, if required we can now switch over to the standby as a primary.

DGMGRL> switchover to mobpro_collab1;

Performing switchover NOW, please wait...
New primary database "mobpro_collab1" is opening...
Operation requires shutdown of instance "mobpro" on database "mobpro_collab2"
Shutting down instance "mobpro"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "mobpro" on database "mobpro_collab2"
Starting instance "mobpro"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "mobpro_collab1"

Monday, October 26, 2009

Fast Start failover using Data Guard

Converting Physical Standby to Snapshot using DGMRL

In this post, I talk about converting a physical standby tp Snapshot, for those who are not aware of what is snapshot, it is a fully updateable database, for this to happen flashback should have been enabled. Though redo transport does happens from primary to snapshot, redo is not applied until it is concerted back to physical.

DGMGRL> convert database 'mobpro_collab2' to snapshot standby;

Converting database "mobpro_collab2" to a Snapshot Standby database, please wait...
Database "mobpro_collab2" converted successfully

The snapshot is totally updateable as hown below.

SQL> select * from t;

N
---------
9999
9999
9999
9999
SQL> insert into t values(100);
1 row created.
SQL> commit;
Commit complete.

Let us also see the entries of the alert log at the standby site
Mon Oct 26 13:14:35 2009

Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
Mon Oct 26 13:14:38 2009

We will again convert snapshot to physical.

DGMGRL> convert database 'mobpro_collab2' to physical standby;

Converting database "mobpro_collab2" to a Physical Standby database, please wait...
Operation requires shutdown of instance "mobpro" on database "mobpro_collab2"
Shutting down instance "mobpro"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "mobpro" on database "mobpro_collab2"
Starting instance "mobpro"...
ORACLE instance started.
Database mounted.
Continuing to convert database "mobpro_collab2" ...
Operation requires shutdown of instance "mobpro" on database "mobpro_collab2"
Shutting down instance "mobpro"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "mobpro" on database "mobpro_collab2"
Starting instance "mobpro"...
ORACLE instance started.
Database mounted.
Database "mobpro_collab2" converted successfully

Changing protection modes using DGMGRL

How do we change the protection mode, my current protection mode is maximum performance, lets try changing this to max protection and max availability.

To get this started, we need to change the database property 'LogXptMode' of the standby and also the primary so that its taken care during a transition.

DGMGRL> EDIT DATABASE 'mobpro_collab1' SET PROPERTY 'LogXptMode'='SYNC';

Property "LogXptMode" updated
DGMGRL> EDIT DATABASE 'mobpro_collab2' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

DGMGRL> show configuration

Configuration - mobpro
Protection Mode: MaxAvailability
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - Physical standby database

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;

Succeeded.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Succeeded.

As you see, changing the protection mode is made veyr simple using dgmgrl, however a point to note that when you upgrade from max performace to max protection, make sure it is first changed to max availability and then to max protecion.

The reason is, primary will be taken down if there is a direct change from performance to protection. As you see from my below example, I had the configuration at Max Performance and then changed it to Max Protection.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;

Operation requires shutdown of instance "mobpro" on database "mobpro_collab1"
Shutting down instance "mobpro"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "mobpro" on database "mobpro_collab1"
Starting instance "mobpro"...
ORACLE instance started.
Database mounted.
Database opened.

Can also check for the alert log on the primary to see a series of commands it has executed.

Regards

Asad

Sunday, October 25, 2009

Managing Database objects using Data Guard Broker

In this article I talk about managing the DB Objects using the broker, Objects here could be a Primary or Standby. Every DB Object has a profile associated with them which distinguishes them from physical, standby, snapshot. Note that every type of standby db is configured which has properties and state. We will understand what a state is and how we can alter the property and the state.

Let us understand the states of the Database by running a command shown below.

DGMGRL> show database verbose 'mobpro_collab1';

Database - mobpro_collab1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
mobpro

***********************************************************************
DGMGRL> show database verbose 'mobpro_collab2';

Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
mobpro
***********************************************************************

Looking at the output marked in red, gives us a idea that the primary state indicates the the transport of the redo log is switched on and on the standby the redo log apply is on.

There are different states associated with the database depending on the type of database.

PRIMARY : TRANSPORT-ON, TRANSPORT-OFF
PHYSICAL STANDBY : APPLY-ON, APPLY-OFF
SNAPSHOT STANDBY: APPLY-OFF
LOGICAL STANDBY : APPLY-ON, APPLY-OFF

We will run a small test to understand what happens when we change the state of a database. Let us observe by changing the state of the primary database.

On the standby

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES

On the primary switch a logfile

SQL> alter system switch logfile;

System altered.

Confirm if this is applied on the standby

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED

---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES

As you see Sequence 194 is applied on the standby. Lets run the same test again by changing the state of the transport on the primary.

DGMGRL> EDIT DATABASE 'mobpro_collab1' SET STATE='TRANSPORT-OFF';

Succeeded.

Transport is switched off, Switch a log on the primary.

SQL> alter system switch logfile;

System altered.

Check if this log is applied on the Standby.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES

Nope, not applied since the transport is switched off, internally the broker has changed the value of the parameter log_archive_dest_state_2, this can be confirmed by looking at the alert log of the primary.

ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

Sun Oct 25 15:47:19 2009

Remember in my earlier posting I had mentioned that spfile is mandatory, since the broker dynamically changes the parameter values and stores the information in its configuartion file.

Verify if the transport begins

DGMGRL> EDIT DATABASE 'mobpro_collab1' SET STATE='TRANSPORT-ON';

Succeeded.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

---------- ---------
185 YES
186 YES
187 YES
188 YES
189 YES
190 YES
191 YES
192 YES
193 YES
194 YES
195 YES
196 YES
197 YES
198 IN-MEMORY

Now I change the state of the standby database as shown below.

DGMGRL> EDIT DATABASE 'mobpro_collab2' SET STATE='APPLY-OFF';

Succeeded.

Checking the information

DGMGRL> show database verbose 'mobpro_collab2';

Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 1 minute 14 seconds
Real Time Query: OFF
Instance(s):
mobpro

Switch a logfile on the primary

SQL> alter system switch logfile;

System altered.

Check if the log has been applied,
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
196 YES
197 YES
198 YES
199 NO

Log is shipped but not applied, the broker has actually cancelled the recovery, it is indicated by the entry in the alert log of the standby as show below.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Sun Oct 25 16:05:45 2009
MRP0: Background Media Recovery cancelled with status 16037

So far we had looked at the state of the DB Objects, let us now visit the properties of the Database objects. Not a rocket science to know whats a property,

DGMGRL> show database verbose 'mobpro_collab1';

Database - mobpro_collab1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab1_dg'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab1'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOBPRO_COLLAB1_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS

All the above marked in red describes the property of the primary database. Some of the properties is editable and some are readonly, hence Oracle document has named it monitorable and configurable. In short if you look at the above output the readonly properties has the value '(monitor)'.

To get more info, run the command as shown below.

DGMGRL> show database 'mobpro_collab1' 'TopWaitEvents';

TOP SYSTEM WAIT EVENTS
Event Wait Time
rdbms ipc message 36099998
SQL*Net message from client 3630353
DIAG idle wait 3627291
jobq slave wait 3219358
pmon timer 1813734

Here is the listing to see the value of the configurable property and how to change it.

DGMGRL> show database 'mobpro_collab1' 'NetTimeout';
NetTimeout = '30'
DGMGRL> EDIT DATABASE 'mobpro_collab1' SET PROPERTY 'NetTimeout' = 60;
Property "NetTimeout" updated
DGMGRL> show database 'mobpro_collab1' 'NetTimeout';
NetTimeout = '60'

A very important note, if we disable the primary to stop the transport of redo it effects the all the standby databases, if we choose to stop the redo transport for a particular standby then we need to change the configurable property of a particular standby as shown below.

DGMGRL> EDIT DATABASE 'mobpro_collab2' SET PROPERTY 'LogShipping'='OFF';

Property "LogShipping" updated

One more important property worth mentioning is DelayMins, the value to be supplied is in minutes which means that the standby will be behind the primary in terms of the apply services, useful if there is a user error on the primary like dropping a table, can be recovered from the standby.

Saturday, October 24, 2009

Implementing Data Guard Broker 11g

In this article let us try to understand the method to implement data guard broker, It is assumed that we have a working primary and physical standby servers in place. Let us proceed on how to implement Broker.

Broker works with spfile, in the coming steps you will realize, hence make sure the databases are using spfile.

The first parameter which holds the entire configuration is determined by the parameter

DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

The default location is $ORACLE_HOME/dbs

Let us check this on the database.

SQL> show parameter dg_broker_config_file

NAME                        TYPE          VALUE
----------------------   ----------- ------------------------------
dg_broker_config_file1 string     /u01/app/oracle/product/11.2.0/dbs/dr1MOBPRO_COLLAB1.dat
dg_broker_config_file2 string     /u01/app/oracle/product/11.2.0/dbs/dr2MOBPRO_COLLAB1.dat

Let us understand few important points on this configuration file

By default the name takes dr1.dat & dr2.dat. We can confirm this by check the unique db name on the database.

        SQL> show parameter db_unique_name

        NAME                          TYPE        VALUE
        ----------------------- ----------    ------------
      db_unique_name string MOBPRO_COLLAB1

There are two copies of this file, it is advisable not to keep both the copies on the single file system to avoid loosing if there is a disk failure.
On the RAC make sure this configuration file lies where it is accesible by all the instances.

Let us now enable the broker process to start by altering a parameter, this needs to be done on the primary as well as standby.

collab1:/u01/app/oracle/product/11.2.0/network/admin[mobpro]$ ps -ef | grep dmon
oracle 24024 3599 0 17:49 pts/1 00:00:00 grep dmon

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

[oracle@collab1 dbs]$ ps -ef | grep dmon
oracle 24064 1 0 17:50 ? 00:00:00 ora_dmon_mobpro
oracle 24174 23811 0 17:51 pts/2 00:00:00 grep dmon

Before the parameter is set I checked for a process dmon, it didn't exist, after the parameter is set the process is created and I have painted in red .

There is a lot more to go, till now we have just started the broker background process. Probably we can do a check by invoking the command line interface to check whats going on until now.

collab1:/u01/app/oracle/product/11.2.0/dbs[mobpro]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>

The above message clearly tells that configuration does not exist, means we need to create a configuration, this can be done as shown below.

DGMGRL> CREATE CONFIGURATION MOBPRO AS PRIMARY DATABASE IS MOBPRO_COLLAB1 CONNECT IDENTIFIER IS MOBPRO_COLLAB1_DG;

Configuration "mobpro" created with primary database "mobpro_collab1"

The above conveys that in this configuration the primary is MOBPRO_COLLAB1 which is nothing but the value of the parameter DB_UNIQUE_NAME. Also we are specifiying that the broker will connect to the primary using TNS service MOBPRO_COLLAB1_DG. For your convenience here is the entry from tnsnames.ora.

MOBPRO_COLLAB1_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

Let us now go back the command line prompt of the broker and run a command to check the configuration.

DGMGRL> show configuration
Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

It means we have created a configuration by name called mobpro with the primary database called mobpro_collab1and the status is disabled, which clearly indicates that we should now go ahead and enable this configuration.

Before we enable the configuration, its quiet obvious we are missing something here, we are missing the standby configuration details, just like how we created a configuration by specifying the name of the primary, now its time that to the configuration created above we need to attach the details of the standby, let us go ahead and complete this.

DGMGRL> ADD DATABASE MOBPRO_COLLAB2 AS CONNECT IDENTIFIER IS MOBPRO_COLLAB2_DG;

Database "mobpro_collab2" added

DGMGRL> SHOW CONFIGURATION

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

MOBPRO_COLLAB2 is the db_unique_name of the standby database and MOBPRO_COLLAB2_DG is the TNS service name which connects to standby from the primary. For your convenience here is the entry.

MOBPRO_COLLAB2_DG =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

Let us recap as to what we have done so far

Created a configuration called mobpro, indicated the name of the primary along with the connect identifier
Added the details of the Standby Database along with the connect identifier
As of now the configuration details are stored in the files indicated by the parameter dg_broker_config_file1 & dg_broker_config_file2. However the configuraion is still in disabled state

We can see the different properties our configuration has with respect to the primary and the standby as shown below.

DGMGRL> show database verbose 'mobpro_collab1';

Database - mobpro_collab1
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab1_dg'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab1'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOBPRO_COLLAB1_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
standbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED

DGMGRL> show database verbose 'mobpro_collab2';

Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab2_dg'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab2'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mobpro_collab2_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED

Now the configuration is all set to be enabled

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

We have completed the configuration of the broker, please look forward in the coming days on using the broker for effectively managing the environment.

Sunday, October 18, 2009

Create standby database with 11g using RMAN duplicate from active database

Hello to all the oracle dba bloggers, I always used to appreciate the so many thousands of bloggers who post their work which immensely benefits to so many DBA's. I also decided to post my work and spread accross the knowledge amongst us. This is my first posting on the Data Guard implementation.

Primary Host = collab1
Standby Host = collab2
DB Version : 11.2.0.1
This example has the DB_NAME as mobpro on the primary and standby
Create primary database as usual with the following parameters

LISTENER.ORA for primary

SID_LIST_LISTENER = 
(SID_LIST = 
(SID_DESC =
(GLOBAL_DBNAME =mobpro) 
(ORACLE_HOME = /u01/app/oracle/product/11.2.0) 
(SID_NAME = mobpro) 
) ) 



LISTENER = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = collab1)(PORT = 1521)) 
) 

TNSNAMES.ora for both primary and standby

MOBPRO_COLLAB1_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

MOBPRO_COLLAB2_DG =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

Initialization parameters on the Primary (these paqrameters to be set on the host collab1)

db_name='mobpro'
db_unique_name=mobpro_collab1

##COMMON TO BOTH PRIMARY AND STANDBY ROLES

LOG_ARCHIVE_CONFIG='DG_CONFIG=(mobpro_collab1,mobpro_collab2)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/mobpro/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mobpro_collab1'

LOG_ARCHIVE_DEST_2='SERVICE=mobpro_collab2_dg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mobpro_collab2' VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_MAX_PROCESSES=2

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

#SPECIFIC TO STANDBY ROLE

  
STANDBY_FILE_MANAGEMENT=AUTO 

STANDBY_ARCHIVE_DEST='/u01/app/oracle/admin/mobpro/arch'

FAL_SERVER=MOBPRO_COLLAB2_DG

FAL_CLIENT=MOBPRO_COLLAB1_DG

Make sure you create the standby redo log on the primary, RMAN will create it automatically on the standby

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/u01/app/oracle/admin/mobpro/srl01.log') SIZE 10M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/u01/app/oracle/admin/mobpro/srl02.log') SIZE 10M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/u01/app/oracle/admin/mobpro/srl03.log') SIZE 10M;

Let us now prepare the STANDBY system

Create a static listener,

       SID_LIST_LISTENER = 
       (SID_LIST = 
       (SID_DESC = 
       (GLOBAL_DBNAME =mobpro) 
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0) 
       (SID_NAME = mobpro) 
       )) 
    
      LISTENER = 
      (DESCRIPTION = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = collab2)(PORT = 1521)) 
     )

Create a pfile under $ORACLE_HOME/dbs/initmobpro.ora with only one entry
db_name=mobpro
Create a password file under the dbs directory, make sure it matches the password of sys of the primary, in my example the password is for sys is sys.
orapwd file=orapwmobpro password=sys
Startup the Standby Instance
SQL>STARTUP NOMOUNT

Now we all are set to use RMAN to create the STANBY system

RMAN> connect target sys/sys@mobpro_collab1_dg
connected to target database: MOBPRO (DBID=694528147)
RMAN> connect AUXILIARY sys/sys@mobpro_collab2_dg
connected to auxiliary database: MOBPRO (not mounted)

RMAN> run {
 allocate channel pm1 type disk;
 allocate channel pm2 type disk;
 allocate channel pm3 type disk;
 allocate channel pm4 type disk;
 allocate channel pm5 type disk;
 allocate auxiliary channel st1 type disk;
 duplicate target database for standby from active database
 spfile
 set 'db_unique_name'='mobpro_collab2'
 set control_files='/u01/app/oracle/oradata/mobpro/control.ctl'
 set db_create_online_log_dest_1='/u01/app/oracle/oradata/mobpro/'
 set db_create_online_log_dest_2='/u01/app/oracle/oradata/mobpro/'
 nofilenamecheck;
 }


using target database control file instead of recovery catalog

allocated channel: pm1
channel pm1: SID=47 device type=DISK
allocated channel: pm2
channel pm2: SID=17 device type=DISK
allocated channel: pm3
channel pm3: SID=48 device type=DISK
allocated channel: pm4
channel pm4: SID=44 device type=DISK
allocated channel: pm5
channel pm5: SID=43 device type=DISK
allocated channel: st1
channel st1: SID=20 device type=DISK
Starting Duplicate Db at 18-OCT-09
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbs/orapwmobpro' auxiliary format 
'/u01/app/oracle/product/11.2.0/dbs/orapwmobpro' targetfile 
'/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora' auxiliary format 
'/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora''";
}
executing Memory Script
Starting backup at 18-OCT-09
Finished backup at 18-OCT-09
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name = 
''mobpro_collab2'' comment=
'''' scope=spfile";
sql clone "alter system set control_files = 
''/u01/app/oracle/oradata/mobpro/control.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_online_log_dest_1 = 
''/u01/app/oracle/oradata/mobpro/'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_online_log_dest_2 = 
''/u01/app/oracle/oradata/mobpro/'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''mobpro_collab2'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/mobpro/control.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_create_online_log_dest_1 = ''/u01/app/oracle/oradata/mobpro/'' comment= '''' scope=spfile
sql statement: alter system set db_create_online_log_dest_2 = ''/u01/app/oracle/oradata/mobpro/'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
oracle instance started
Total System Global Area 828608512 bytes
Fixed Size 1339684 bytes
Variable Size 620760796 bytes
Database Buffers 201326592 bytes
Redo Buffers 5181440 bytes
allocated channel: st1
channel st1: SID=18 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/mobpro/control.ctl';
}
executing Memory Script
Starting backup at 18-OCT-09
channel pm1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbs/snapcf_mobpro.f tag=TAG20091018T163844 RECID=5 STAMP=700591126
channel pm1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-OCT-09
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/mobpro/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/mobpro/system01.dbf";
set newname for datafile 2 to 
"/u01/app/oracle/oradata/mobpro/sysaux01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/mobpro/undotbs01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/mobpro/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/mobpro/system01.dbf" datafile 
2 auxiliary format 
"/u01/app/oracle/oradata/mobpro/sysaux01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/mobpro/undotbs01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/mobpro/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/mobpro/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-OCT-09
channel pm1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/mobpro/system01.dbf
channel pm2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/mobpro/sysaux01.dbf
channel pm3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/mobpro/undotbs01.dbf
channel pm4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/mobpro/users01.dbf
output file name=/u01/app/oracle/oradata/mobpro/users01.dbf tag=TAG20091018T163858
channel pm4: datafile copy complete, elapsed time: 00:01:08
output file name=/u01/app/oracle/oradata/mobpro/system01.dbf tag=TAG20091018T163858
channel pm1: datafile copy complete, elapsed time: 00:02:38
output file name=/u01/app/oracle/oradata/mobpro/sysaux01.dbf tag=TAG20091018T163858
channel pm2: datafile copy complete, elapsed time: 00:02:38
output file name=/u01/app/oracle/oradata/mobpro/undotbs01.dbf tag=TAG20091018T163858
channel pm3: datafile copy complete, elapsed time: 00:02:38
Finished backup at 18-OCT-09
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/users01.dbf
Finished Duplicate Db at 18-OCT-09
released channel: pm1
released channel: pm2
released channel: pm3
released channel: pm4
released channel: pm5
released channel: st1




Let us set up few parameters on the standby system

SQL> alter system set fal_server=MOBPRO_COLLAB2_DG scope=botSQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=mobpro_collab1_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mobpro_collab1' scope=both;h;
SQL> alter system set fal_client=MOBPRO_COLLAB1_DG scope=both;

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/mobpro/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mobpro_collab2' scope=both; 


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT; 2 

Database altered.



Identify existing files on standby

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
150 18-OCT-09 18-OCT-09
151 18-OCT-09 18-OCT-09


Switch a log on the primary

SQL> alter system switch logfile;

System altered.

Confirm it on standby

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;




SEQUENCE# FIRST_TIM NEXT_TIME

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

150 18-OCT-09 18-OCT-09
151 18-OCT-09 18-OCT-09
152 18-OCT-09 18-OCT-09



Let us see if the real time apply works with out a log switch

On the standby do this

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


SQL> ALTER DATABASE OPEN READ ONLY; 

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

  
Now on the primary insert a value into a table t

SQL> insert into t values(00000);

1 row created.

SQL> commit;

Commit complete.


On the standby now you query to check if this updated value is seen 

SQL> select * from t;


N
---------
1971
0

Yes it works, quiet a simple method to implement Data Guard in 11g.

Regards

Asad

Oracle DBA