This is for my personal refference
Server1(Primary DB- orcl)
1) Install
Oracle 11g on the server1
2) Enable Archive
logs
3) Enable
Flashback logs
4) Setup RMAN
with target Database
5) Enable Force
Logging
6) Create Stand
By Logs
1) Install
Oracle 11g on server1.
Run the Oracle
setup and get the whole s/w with database install on server1 successful with
the DB name as ORCL. And alter the force
logging to yes.
Sql> alter
system set force_logging =true;
It can be verified
by
Sql> select
force_logging from v$database;
2) Enable
Archive Logs
To
set archive log, always login through SYSDBA
Now,
let us check the current log mode either in "Archivelog" or
"Noarchivelog":
C:\Documents
and Settings\navneet>sqlplus
SQL*Plus:
Release 11.2.0.1.0 Production on Tue Mar 27 11:19:53 2012
Copyright
(c) 1982, 2010, Oracle. All rights reserved.
Enter
user-name: /as sysdba
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>
archive log list
Database
log mode No Archive Mode
Automatic
archival Disabled
Archive
destination USE_DB_RECOVERY_FILE_DEST
Oldest
online log sequence 2296
Current
log sequence 2299
OR
SQL>
select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
We
have checked that our Database is in "NOARCHIVE LOG". Below query
will set the location where to save the archive logs.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\ARCHIVE_LOG' SCOPE=SPFILE;
System
altered.
SQL>
SHUTDOWN IMMEDIATE
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
STARTUP MOUNT
ORACLE
instance started.
Total
System Global Area 535662592 bytes
Fixed
Size 1375792 bytes
Variable
Size 385876432 bytes
Database
Buffers 142606336 bytes
Redo
Buffers 5804032 bytes
Database
mounted.
The
below query will make database into archivelog.
SQL>
ALTER DATABASE ARCHIVELOG;
Database
altered.
SQL>
ALTER DATABASE OPEN;
Database
altered.
SQL>
SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
OR
SQL>
ARCHIVE LOG LIST
Database
log mode Archive Mode
Automatic
archival Enabled
Archive
destination E:\ARCHIVE_LOG
Oldest
online log sequence 2296
Next
log sequence to archive 2299
Current
log sequence 2299
SQL> alter system
switch logfile;
Now
database is in Archivelog mode, We have verified by checking archivelogs in
directory.
E:\>dir
Volume
in drive E has no label.
Volume
Serial Number is AA1B-C0B9
Directory
of E:\
12/09/2011
09:15 AM app
03/27/2012
11:43 AM 13,952,512 ARCHIVE_LOG2ARC0000002299_0769427405.0001
03/27/2012
11:46 AM 1,536 ARCHIVE_LOG2ARC0000002300_0769427405.0001
03/28/2012
05:39 AM 50,630,144 ARCHIVE_LOG2ARC0000002301_0769427405.0001
03/29/2012
05:38 AM 40,771,072 ARCHIVE_LOG2ARC0000002302_0769427405.0001
03/29/2012
06:30 PM 40,671,232 ARCHIVE_LOG2ARC0000002303_0769427405.0001
02/17/2009
11:33 AM 23,516,968 SkypeSetupFull.exe
6
File(s) 169,543,464 bytes
1
Dir(s) 38,746,923,008 bytes free
3) Enabling
Flashback Logs
Before enable the
flashback we need to take our database in archive log mode. After
enabling the archive logs now we need to set 3 paramaters for flashback are
1 db_recovery_file_dest
2
db_recovery_file_dest_size
3
db_flashback_retention_target
>>This parameter
"db_recovery_file_dest" is used to set the destination where
we want to store
the location of flashback
logs. The location of flashback logs may or maybe be same as the
location of archive logs, here i gave different location from archive
logs.
>>The parmeter
"db_recovery_file_dest_size" is used to define the size that
can be used by
flashback
directory/folder provided with above parameter. This size can be calculated on
the basis of transactions happened in our database and if archive logs,
only when the destination of archive logs
& flashback logs are
same.
>>The parameter
"db_flashback_retention_target " is used to provide time limit
upto which flashback logs should be in the memory of database, So that
database can be flashed up back with in time limit. Time is provided in
minutes, so we set retention time 1440 which is about a day.
>>Set
flashback database parameters
alter system set
db_recovery_file_dest='E:\app\navneet\FRA' scope=spfile;
alter system set
db_recovery_file_dest_size=20G scope=spfile;
alter system set
db_flashback_retention_target=1440 ;
>> Turn
flashback on for the database
shutdown immediate;
startup mount;
alter database flashback
on;
alter database open;
>>Now check if
flashback is enabled
select flashback_on from
v$database;
FLASHBACK_ON
------------------
YES
Now Flashback is
ENABLED.
>>To disable
flashback
alter system set
db_recovery_file_dest=" scope= both sid='*';
Some related queries are
as follow
>> To check the
flashback logs
select * from
v$flashback_database_log;
>>To check the
estimated flashback size
SELECT
ROUND(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024) FROM
V$FLASHBACK_DATABASE_LOG;
>>To create a
guaranteed restore point
create restore point
RESTORE_POINT guarantee flashback database;
select NAME, SCN, TIME
from V$RESTORE_POINT;
>>To flashback the
database to specific time
flashback database to
timestamp to_timestamp('dd/mm/yy HH24:MI:SS', 'DD/MM/YYYY
HH24:MI:SS');
>>To drop a restore
point
select NAME, SCN, TIME from
V$RESTORE_POINT;
drop restore point
RESTORE_POINT;
The
V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help
to
find out the current location, disk quota, space in use, space reclaimable by
deleting files,
total
number of files, the percentage of the total disk quota used by different types
of files.
4)
Setting
up RMAN
Create Recovery
Catalog
CONNECT sys/password@w2k1
AS SYSDBA
-- Create tablepsace to
hold repository
CREATE TABLESPACE
"RMAN"
DATAFILE 'C:\ORACLE\ORADATA\W2K1\RMAN01.DBF'
SIZE 6208K REUSE
AUTOEXTEND ON NEXT 64K
MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT
AUTO;
-- Create rman schema
owner
CREATE USER rman
IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
GRANT connect, resource,
recovery_catalog_owner TO rman;
Create
the recovery catalog.
C:>rman
catalog=rman/rman@w2k1
Recovery Manager: Release
9.2.0.1.0 - Production
Copyright (c) 1995, 2002,
Oracle Corporation. All rights reserved.
connected to recovery
catalog database
recovery catalog is not
installed
RMAN> create catalog
tablespace "RMAN";
recovery catalog created
RMAN> exit
Recovery Manager
complete.
Register Database
Each
database to be backed up by RMAN must be registered with the catalog.
C:>rman
catalog=rman/rman@w2k1 target=sys/password@w2k2
Recovery Manager: Release
9.2.0.1.0 - Production
Copyright (c) 1995, 2002,
Oracle Corporation. All rights reserved.
connected to target
database: W2K2 (DBID=1371963417)
connected to recovery
catalog database
RMAN> register
database;
database registered in
recovery catalog
starting full resync of
recovery catalog
full resync complete
5) Enable
force logging
Check whether Force logging is
YES or NO.
SQL> select force_logging
from v$database;
FOR
---
NO
The above o/p shows the force logging is not enabled so
Enable it by followed syntax.
SQL> alter database force
logging;
Database altered.
Check again if force logging is
enabled.
SQL> select force_logging
from v$database;
FOR
---
YES
The above o/p shows that force
logging is enabled now.
6) Create Standby Logs
Standby redo logs are required to enable real time apply of
redo data onto the standby. This standby redo logs are populated with redo
information as fast as the primary redo logs, rather than waiting for the redo
log to be archived and shipped to the standby. This results in
faster switchover and failover times because the standby redo log files have
been applied already to the standby database by the time the failover or
switchover begins.
Ensure
log file sizes are identical on the primary and standby databases. The
size of the current standby redo log files must exactly match the size of the
current primary database online redo log files. For example, if the primary
database uses two online redo log groups whose log files are 200K, then the
standby redo log groups should also have log file sizes of 200K.
Determine the appropriate number of standby redo log file
groups. Logs should be equal in number. If Primary has 3 redo logs then we
should made 3 or more Standby redo logs. Oracle recommends that you create a
standby redo log on the primary database so that the primary database can
switch over quickly to the standby role without the need for additional DBA
intervention.
Syntax to create
Standby Log
alter database add standby logfile group 1 ‘%ORACLE_BASE%\ORADATA\%ORACLE_SID%\REDO01.LOG’ size 50m;
Alter system set dg_broker_start=TRUE scopth = both;
Go to server 2(To create Standby DB -
strdb)
Install the Oracle software only without database. Then create
an instance (STRDB) by firing the command on command prompt. The following
commands you can see is how i tried.
C:\Users\Administrator>ORADIM -NEW -SID STRDB
Instance created.
Now creating password
file in %Oracle_home% \db_home.
C:\Users\Administrator>ORAPWD
FILE="C:\app\Administrator\product\11.2.0\dbhome_1
\database\PWDSTRDB.ora" PASSWORD=system
Create folder for
Flash Area Recovery
%Oracle_base%\FRA\strdb
Create folder for
Flash Area Recovery
%Oracle_base%\ORADATA\strdb
Create pfilestrdb.ora
tp startup database in nomount stage, place anywhere in the Hard drive and add
two lines in that file.
*.db_name='ORCL'
*.db_unique_name='STRDB'
Save & exit.
Now create listener
& tns entry so that database instance can be able to talk.
Go to listner.ora, edit it by copy & pasting the same
entry as mentioned below.
OLD ENTERY OF
LISTNER.ORA
# listener.ora Network Configuration File:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME =
CLRExtProc)
(ORACLE_HOME =
C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM =
extproc)
(ENVS =
"EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS =
(PROTOCOL = TCP)(HOST = eagle394.server4you.de)(PORT = 1521))
)
)
ADR_BASE_LISTENER = C:\app\Administrator
NEW ENTRY OF
LISTNER.ORA
# listener.ora Network Configuration File:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME =
CLRExtProc)
(ORACLE_HOME =
C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM =
extproc)
(ENVS =
"EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = STRDB)
(ORACLE_HOME =
C:\app\Administrator\product\11.2.0\dbhome_1)
(ENVS =
"EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS =
(PROTOCOL = TCP)(HOST = eagle394.server4you.de)(PORT = 1521))
)
)
ADR_BASE_LISTENER = C:\app\Administrator
Now for TNS ENTRY
OLD TNS ENTRY
# tnsnames.ora Network Configuration File:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID =
CLRExtProc)
(PRESENTATION =
RO)
)
)
NEW TNS ENTRY
# tnsnames.ora Network Configuration File:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
STRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
69.64.58.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STRDB)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID =
CLRExtProc)
(PRESENTATION =
RO)
)
)
Now to make the network
connectivity of both the DBs. Just add the servers 1’s tns entry to
server2’s tns entry & vice versa.
Server1’s TNS
ENTRY after adding the server2’s tns
entry
# tnsnames.ora Network Configuration File:
c:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
STRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
69.64.58.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STRDB)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID =
CLRExtProc)
(PRESENTATION =
RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = pro1848.server4you.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
orcl)
)
)
Server2’s TNS
ENTRY after adding the server1’s tns
entry
# tnsnames.ora Network Configuration File:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
STRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 69.64.58.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =
STRDB)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
pro1848.server4you.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID =
CLRExtProc)
(PRESENTATION =
RO)
)
)
Restart the LSNRCTL
by firing the command on command prompt on both servers.
cmd> LSNRCTL STOP
cmd> LSNRCTL START
Now on server 2 make the DB in nomount stage
C:\Users\Administrator>sqlplus sys/system@strdb as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6
09:55:33 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> startup nomount pfile='C:\app\pfile.ora';
ORACLE instance started.
Total System Global Area 1.3696E+10 bytes
Fixed Size
2188768 bytes
Variable Size
6878661152 bytes
Database Buffers
6777995264 bytes
Redo Buffers
37044224 bytes
SQL>
Go to server 1
Connect
to Rman with orcl database to make the StandBy database with the script
provided below.
C:\Users\Administrator>rman
target sys/system@orcl
Recovery
Manager: Release 11.2.0.1.0 - Production on Fri Jul 6 12:13:36 2012
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: ORCL (DBID=1314151480)
RMAN>
conn auxiliary sys/system@strdb
RMAN>
DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> SPFILE
5> SET DB_NAME='ORCL'
6> SET DB_UNIQUE_NAME='STRDB'
7> SET
DB_RECOVERY_FILE_DEST='C:\app\Administrator\FRA'
8> SET
CONTROL_FILES='C:\app\Administrator\ORADATA\STRDB\CONTROL01.CTL',
'C:\app\Administrator\FRA\STRDB\CONTROL02.CTL'
9> SET DB_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\',
'C:\app\Administrator\ORADATA\STRDB\'
10> SET
LOG_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\',
'C:\app\Administrator\ORADATA\STRDB\';
Script in simple form is used
in RMAN
DUPLICATE TARGET DATABASE
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
SET DB_NAME='ORCL'
SET DB_UNIQUE_NAME='STRDB'
SET
DB_RECOVERY_FILE_DEST='C:\app\Administrator\FRA'
SET
CONTROL_FILES='C:\app\Administrator\ORADATA\STRDB\CONTROL01.CTL',
'C:\app\Administrator\FRA\STRDB\CONTROL02.CTL'
SET DB_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\',
'C:\app\Administrator\ORADATA\STRDB\'
SET
LOG_FILE_NAME_CONVERT='C:\app\Administrator\oradata\orcl\',
'C:\app\Administrator\ORADATA\STRDB\';
Note- All the locations
provided in the script are according to the database located on the Test
Servers.
Go to Server2 - strdb
This
will create exact database at server 2 (STRDB) with its all redo logs and
Standby logs, multiplexed control file with archive logs and flashback
database. After finishing the script under RMAN, the database will be in mount
stage. Database will be in the Physical standby position. It can be checked by
the query
Sql>
select name, db_unique_name, database_role,
open_mode from v$database;
Output
would be
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
---------
------------------------------ ---------------- --------------------
ORCL STRDB PHYSICAL STANDBY MOUNTED
Check the parameter whether dg_broker_start is true or not, if not then make it true by the
followed query.
Sql>Alter system set dg_broker_start=TRUE scopth = both;
Go to server 1 - orcl
Now setting up Dataguard Manager(dgmgrl) to make failover
automatic.
C:\Users\Administrator>set
oracle_sid=orcl
C:\Users\Administrator>dgmgrl/
DGMGRL
for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production
Copyright
(c) 2000, 2009, Oracle. All rights reserved.
Welcome
to DGMGRL, type "help" for information.
Connected.
The
following command is used to create the configuration known as DG with primary
database. Only primary database would be integrated in this configuration.
StandBy database will be added in next step.
DGMGRL>
CREATE CONFIGURATION dg AS
>PRIMARY DATABASE IS orcl
>CONNECT IDENTIFIER IS orcl;
Configuration
“dg” has been configured with primary database “orcl”
Now
to add standby database in the same configuration and same primary database.
DGMGRL>
ADD DATABASE strdb
>AS CONNECT IDENTIFIER IS strdb
>MAINTAINED AS physical;
Database
“strdb” added
DGMGRL>
show configuration
Configuration
- dg
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
strdb - Physical standby database
Fast-Start Failover: DISABLE
Configuration Status:
DISABLE
Now the configuration is Disabled as above, to make it enable run the
below command
DGMGRL>enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
strdb - Physical standby
database
Fast-Start Failover: DISABLE
Configuration Status:
SUCCESS
Now the configuration is success after enabling. If it is not
configured well then it will show error instead of success. And Fast-Start
Failover is Disable which is okay for now. We can check that archive logs from
Primary (orcl) database are shipping to the standby (strdb) database by going
physically on their appropriate place with same sequence number. In this
scenario archive log destinations are
Primary DB- C:\app\Administrator\archivelogs
Standby DB- C:\app\Administrator\archivelogs
Next step is to make Failover automatic known as FastStart Failover , for that we need to set the parameters as
below:
>LogXptMode should be
SYNC for both the database
Dgmgrl> edit database orcl set property ‘LogXptMode’ ='SYNC’;
Dgmgrl> edit database strdb set property ‘LogXptMode’ ='SYNC’;
>change the protection mode to MaxAvailability
Dgmgrl> edit configuration set protection mode as maxavailability
> Standbyfile management should be Auto
Dgmgrl> edit database orcl set property StandbyFileManagement =
'AUTO';
> FastStartFailoverTarget should be Standby database so that it got
connect o SBDB
Dgmgrl> edit database orcl set property FastStartFailoverTarget = ‘strdb’;
Dgmgrl> edit database strdb set property FastStartFailoverTarget = ‘orcl’;
>Now enabling the FastStart_Failover
Dgmgrl> enable FastStart_failover;
enabled
Now check the status of configuration and notice that Protection mode
has been changed and Fast start failover has been Enabled.
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxAvailability
Databases:
orcl - Primary database
strdb - (*) Physical standby
database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
We can check by firing the command on sqlplus of Primary Database, here
output shows that Standby database is synchronized with primary database, and
if primary(orcl) fails then target database id STRDB which will become Primary.
SQL> select name, db_unique_name, database_role,
fs_failover_current_target, fs_failover_status
from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_STATUS
--------- ------------------------------ ----------------
------------------------------ ----------------------
ORCL ORCL PRIMARY STRDB SYNCHRONIZED
Setting up Observer
which
can on another machine
Take the third machine for setting the observer and install only oracle
software instead of Oracle software+database. Copy the tns entery of strdb & orcl
database & paste it on the third Machine’s tns file so that third machine
would be able to contact both the servers.
STRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
69.64.58.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STRDB)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
pro1848.server4you.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Now we need to connect dataguard manager tostart the observer on this
machine which will observe the primary database. Whenever Primary database goes
down due to any reason then it will automatically switch STRDB to primary &
make it in use with clients with in 30 seconds
C:\Users\Administrator>set
oracle_sid=orcl(primary)
C:\Users\Administrator>dgmgrl/
DGMGRL
for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production
Copyright
(c) 2000, 2009, Oracle. All rights reserved.
Welcome
to DGMGRL, type "help" for information.
Connected.
DGMGRL> start observer
Observer started
Now this window needs to be open till observer is running, if by chance
this window has been closed then observer will get close itself and automatic
switchover can never be performed.
Automatic Application
switchover
In my simple scenario, I have one Primary Database (orcl) and one
Physical Standby Database (strdb). On The challenge is now to get the connect
from the client side to the right (primary) database. That is called Connect
Time Failover and is achieved as follows:
First, we make sure that the client uses a tnsnames.ora with a connect
descriptor that uses a SERVICE_NAME instead of a SID
MYAPP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST
= 69.64.58.18)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST
= pro1848.server4you.de)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myapp)
)
)
Second, we take care that the service myapp is offered only at the
right database – only on the primary. Notice that the PMON background processes
of both databases must be able to communicate with the (local) listeners in
order to register the service myapp. If you don’t use the listener port 1521,
they can’t. You have to point to that listener port then with the initialization
parameter LOCAL_LISTENER.
We create and start now the service myapp manually on the primary:
begin
dbms_service.create_service('myapp','myapp');
end;
/
begin
DBMS_SERVICE.START_SERVICE('myapp');
end;
/
Then we create a trigger, that ensures that this service is only
offered, if the database is in the primary role:
create trigger myapptrigg after startup on database
declare
v_role varchar(30);
begin
select database_role into v_role
from v$database;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('myapp');
else
DBMS_SERVICE.STOP_SERVICE('myapp');
end if;
end;
/
The event after startup is fired, if an instance changes from status
MOUNT to OPEN. If you use a logical standby, it is not fired, because the
logical standby remains in status OPEN. You may use the event after
db_role_change in this case. The creation of the trigger and of the service is
accompanied with redo protocol (the Data Dictionary has changed) and therefore
also present at strdb without any additional work to do there for the DBA. With
the present setup, we have already achieved Connect Time Failover: Clients can
use the same connect descriptor (myapp) to get to the right (primary) database
now, regardless of switchover or failover.
But sessions that are connected to orcl are disconnected if a
switchover or failover to strdb takes place. They have got to connect again
then. We can change that, so that a Runtime Failover is possible, under ideal
circumstances, that failover is even completely transparent to the client and proceeds
without error messages. To achieve that, you don’t have to touch the
tnsnames.ora on the client side. Instead, you do the following on the primary
database:
begin
dbms_service.modify_service
('myapp',
FAILOVER_METHOD => 'BASIC',
FAILOVER_TYPE => 'SELECT',
FAILOVER_RETRIES => 200,
FAILOVER_DELAY => 1);
end;
/
Connections to the service myapp are now automatically failed over
together with the service to the new primary. Should they have done nothing
during the time of the failover/switchover, or even if they had run a select
statement, they will not receive any error but only notice a short
interruption.
This is all done in configuration.
No comments:
Post a Comment