jump to navigation

Creating a 3-node standby database. Part III 29 April 2010

Posted by David Alejo Marcos in ASM, RAC, Standby.
Tags: , ,
trackback

Time has arrived for us to test the results of our hard work by performing a switch between our single instance database to our three-node database.

I always recommend to start any test with a full backup if the space and time to do it does not jeopardize the deadlines.

Below is a list of some pre-checks worth doing:

1.- Check the full backup finished without errors.

2.- Make sure the init.ora file is the same on all 3 nodes (or pointing to the same spfile).

3.- The password file is correct (or recreate it) on all 3 nodes.

4.- check tnsnames.ora and listener.ora files are correct and the same on all 3 nodes.

5.- services are correct and running.

6.- peform a tnsping to and fro everywere to test connectivity and latency.

Ok, so we are ready for the switchover…., let’s do it:

1.- check the status of primary and standby databases:

SELECT switchover_status, database_role, open_mode FROM v$database;

2.- check the standby database is in synch ( I normally perfomr couple of switch logs and see that are being applied).

alter system switch logfile;
alter system switch logfile;

then you can check alert.log,  v$log_history, v$archived_log, etc…

3.- on Primary:

Disable job queue before the switchover on Primary Database (only in memory):

alter system set job_queue_processes=0;

4.- On primary:

Check for any active sessions appart from your own:

SELECT SID, PROCESS, PROGRAM
FROM GV$SESSION
WHERE TYPE = 'USER'
AND SID <> (SELECT DISTINCT SID FROM GV$MYSTAT);

5.- On primary

Perform switchover:

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup mount;

On the alert.log of the new primary database you should see something like:

Sat Apr 24 12:13:17 2010
Media Recovery Log +EVB_RECOVERY/bvmprodsh/archivelog/2010_04_24/thread_1_seq_28879.314.717163985
Identified End-Of-Redo for thread 1 sequence 28879
Sat Apr 24 12:13:17 2010
Media Recovery End-Of-Redo indicator encountered
Sat Apr 24 12:13:17 2010
Media Recovery Applied until change 202225032180
Sat Apr 24 12:13:17 2010
MRP0: Media Recovery Complete: End-Of-REDO (bvmprod3)
Resetting standby activation ID 3970545885 (0xeca9b8dd)

6.- On all standby databases, check the status:

SELECT switchover_status, database_role, open_mode FROM v$database;

7.- Complete the switchover on the NEW Primary Database:

alter database commit to switchover to primary with session shutdown;
srvctl stop database -d bvmprodsh
srvctl start database -d bvmprodsh -i bvmprod1

8.- Enable block Change Tracking if you are using incremental backups is recommended:

alter database enable block change tracking using '+EVB_RECOVERY';

9.- On all standby databases (including the old production database), enable recovery:

alter database recover managed standby database disconnect;

10.- Perform several log switch and verify are being shipped and applied on standby databases:

alter system switch logfile;
alter system switch logfile;

Now you are on a cluster database… but there is some work to be done:

1.- Create undo tablespaces for all instances (this only has to be done the first time you open the RAC database):

create undo tablespace undotbs2 datafile size 5g;
create undo tablespace undotbs3 datafile size 5g;

alter system set undo_tablespace=’undotbs1′ scope=spfile sid=’bvmprod1′;
alter system set undo_tablespace=’undotbs2′ scope=spfile sid=’bvmprod2′;

alter system set undo_tablespace=’undotbs3′ scope=spfile sid=’bvmprod3′;

2.- Add logfiles to threads 2 and 3:

ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 21 '+EVB_REDO1' SIZE 250M,
GROUP 22 '+EVB_REDO1' SIZE 250M,
GROUP 23 '+EVB_REDO1' SIZE 250M,
GROUP 24 '+EVB_REDO1' SIZE 250M,
GROUP 25 '+EVB_REDO1' SIZE 250M;

ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 31 ‘+EVB_REDO1’ SIZE 250M,
GROUP 32 ‘+EVB_REDO1’ SIZE 250M,
GROUP 33 ‘+EVB_REDO1’ SIZE 250M,
GROUP 34 ‘+EVB_REDO1’ SIZE 250M,
GROUP 35 ‘+EVB_REDO1′ SIZE 250M;

alter system set thread=1 scope=spfile sid=’bvmprod1′;
alter system set thread=2 scope=spfile sid=’bvmprod2′;
alter system set thread=3 scope=spfile sid=’bvmprod3’;

3.- Define local_listeners (those entries must exists on the listener.ora on all 3 nodes):

alter system set local_listener='LISTENER_BVMPROD1' scope=spfile sid='bvmprod1';
alter system set local_listener='LISTENER_BVMPROD2' scope=spfile sid='bvmprod2';
alter system set local_listener='LISTENER_BVMPROD3' scope=spfile sid='bvmprod3';

4.- Defile remote_listener (entry must exists on the listener.ora on all 3 nodes):

alter system set remote_listener='listeners_bvmprod' scope=spfile sid='*';

5.- Some RAC parameters…

alter system set instance_number=1 scope=spfile sid='bvmprod1';
alter system set instance_number=2 scope=spfile sid='bvmprod2';
alter system set instance_number=3 scope=spfile sid='bvmprod3';

alter system set cluster_database=true scope=spfile sid=’*’;
alter system set cluster_database_instances=3 scope=spfile sid=’bvmprod3′;

alter database enable instance ‘bvmprod2’;
alter database enable instance ‘bvmprod3’;

alter database enable public thread 2;
alter database enable public thread 3;

6.- Stop the instance and start it as RAC:

srvctl stop database -d bvmprodsh -i bvmprod1
srvctl start database -d bvmprodsh
srvctl start service -d bvmprodsh
srvctl status database -d bvmprodsh
srvctl status service -d bvmprodsh

Thas it pretty much all, the only bit left is to test connectivity and keep monitoring archivelogs (or redo logs) are being transferred to all standby databases and applied.

As always, comments are welcome.

Comments»

1. JC Dauchy - 25 November 2010

scn gap on consecutive sequence number after switchover – RAC – 10.2.0.4 ?

I found something which seems anormal and I noticed that since I have a script which check all the consecutive archivelog for each thread (first_change#, last_change#) and its finds SCN gap if there is a hole.

Before putting my RAC 2 nodes database 10.2.0.4 with dataguard, I had no gap.

I think a do properly the switchover (using LGWR ASYNC), i don’t find any error on both alert_log. The switchover seems to run well.

But when i ran my “gap script”, i found the following oddity :

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
———- ———- ————- ————
2 118 5966403 5966562
2 119 5966562 5966577
2 120 5966577 5970293
2 121 5970293 5970310
2 122 5977084 5977094

There is an SCN gap between Sequence 121 and 122.

Is this normal ? I don’t think so..

One other problem seems to happen when i try to do an incomplete recover to SCN 5970309 =>OK but until SCN 5970310 => RMAN error

ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile

Am I the only one ?

This is really strange ?

Thanks for any inputs or helps.

David Alejo Marcos - 6 December 2010

Afternoon,

it is quite difficult to troubleshoot without seeing your script.

You could try the following script from Oracle (http://download.oracle.com/docs/html/A85455_01/stbyadde.htm):

SELECT high.thread#, “LowGap#”, “HighGap#”
FROM
(SELECT thread#, MIN(sequence#)-1 “HighGap#”
FROM
(SELECT a.thread#, a.sequence#
FROM
(SELECT * FROM V$ARCHIVED_LOG) a,
(SELECT thread#, MAX(next_change#) gap1
FROM V$LOG_HISTORY
GROUP BY thread# ) b
WHERE
a.thread# = b.thread#
AND
a.next_change# > gap1
) GROUP BY thread#
) high,
(SELECT thread#, MIN(gap2) “LowGap#”
FROM
(SELECT thread#, sequence#+1 gap2
FROM V$LOG_HISTORY, V$DATAFILE
WHERE
checkpoint_change# = first_change#
AND
enabled = ‘READ WRITE’
) GROUP BY thread#
) low
WHERE
low.thread# = high.thread#
AND
“LowGap#” < "HighGap#";

Note.- The script will need some changes to work with RAC as it selects from V$ views instead of GV$ views.

Thank you.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: