PRIMARY SITE: (Information)
Machine IP: 192.168.1.46
Database name (db_name): ORCL
Database Unique Name (db_unique_name): primary
TNS Service Name: to_standby (Through this service, the primary
machine will be connected to STANDBY machine)
[edit]
STANDBY SITE: (Information)
Machine IP: 192.168.1.96
Database name (db_name): ORCL
Database Unique Name (db_unique_name): standby
TNS Service Name: to_primary (Through this service, the standby
machine will be connected to PRIMARY machine)
[edit]
CONFIGURATION ON PRIMARY
Create pfile from spfile on the primary database:
SQL>
Create pfile=’C:\oracle\product\10.2.0\db_1\dbs\spfilePrimary.ora’ from spfile;
Then make/add following settings in the initPrimary.ora file on the
PRIMARY Machine.
db_unique_name=’PRIMARY’
FAL_Client=’to_primary’
FAL_Server=’to_standby’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’Location=c:\oracle\backup
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=primary’
Log_archive_dest_2=’Service=to_standby
lgwr async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=standby’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=DEFER
Service_names=’primary’
Standby_File_Management=’AUTO’
Create password file using ‘cmd’.
C:\>
orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDOrcl.ora password=oracle
entries=5 [force=y].
Force option is used to replace an existing password file. Now startup
the PRIMARY database to MOUNT stage.
• SQL> startup mount;
Make the following changes: Take the database to Archive Mode.
• SQL> Alter database ArchiveLog;
Enable Force Logging.
• SQL> Alter database Force Logging;
On the PRIMARY site, also create standby redo logfile for the Standby database.
This standby redo logfile will be used for Dataguard Observer later on. If you
don’t want to use DG broker (observer), then there is no need to create standby
redo logfile.
• SQL>Alter database add standby logfile
(‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo.log’)
size 150m;
Now shutdown the primary database.
• SQL> shutdown immediate;
Copy all the Datafiles and standby redo logfile from PRIMARY site to the same
location on the STANDBY site. Then again startup the PRIMARY database to mount
stage.
• SQL> startup mount;
Now create a
standby controlfile on the PRIMARY site.
• SQL> Alter database create standby
controlfile as ‘c:\oracle\backup\standcontrol.ctl’;
Now copy this new created standby control file to the standby site where
other database file like datafiles, logfiles and control files are located.
Rename this file to Control01.ctl, Control02.ctl and Control03.ctl.
Create spFile from pfile.
• SQL> Create spfile from pfile;
Restart the primary database.
Now on PRIMARY site create a service in TNSnames.ora file through which
the PRIMARY site will be connected to the Standby machine.
TO_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.96)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
Also check the connectivity from the SQL Prompt.
• SQL> connect sys/oracle@to_standby as
sysdba
Connected.
Service can also be created through Net Manager utility available with
Oracle Server. Connectivity can also be checked there. Register the Primary
Database in the Listener.ora file. Then stop and start the listener in the
‘cmd’.
> Lsnrctl
stop
> Lsnrctl
start
Query the DATABASE_ROLE column from V$DATABASE to view the role of
primary database. It should return ‘PRIMARY’.
[edit]
CONFIGURATION ON STANDBY
Check the mode of Archiving by following command:
• SQL> Archive Log List
Then create pfile from spfile on the standby database:
SQL>
Create pfile from spfile=’C:\oracle\product\10.2.0\db_1\dbs\spfileStandby.ora’;
Then make/add following settings in the initStandby.ora file on the
STANDBY Machine.
db_unique_name=’STANDBY’
FAL_Client=’to_standby’
FAL_Server=’to_primary’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’Location=c:\oracle\backup
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=standby’
Log_archive_dest_2=’Service=to_primary
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=primary’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names=’STANDBY’
Standby_File_Management=’AUTO’
db_file_name_convert='/home/sanath/primary/','/home/sanath/standby/'
log_file_name_convert='/home/sanath/primary/','/home/sanath/standby/'
lock_name_space=standby
Create password file using ‘cmd’.
C:\>
orapwd file=C:\oracle\product\10.2.0\db_1\database\PWDOrcl.ora password=oracle
entries=5 [force=y].
Force option
is used to replace an existing password file.
Now on STANDBY site create a service in TNSnames.ora file through which
the STANDBY site will be connected to the PRIMARY machine.
TO_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)
Check the connectivity from the SQL Prompt.
• SQL> connect sys/oracle@to_primary as
sysdba
Connected.
Service can also be created through Net Manager utility available with
Oracle Server. Connectivity can also be checked there. Register the Standby
Database in the Listener.ora file. Then stop and start the listener in the
‘cmd’.
> Lsnrctl
stop
> Lsnrctl
start
Create spfile from pfile.
• SQL> Create spfile from pfile;
Restart the database. Now startup the STANDBY database to mount stage.
• SQL> startup mount
Enable Force Logging.
• SQL> Alter database Force Logging;
Query the column DATABASE_ROLE from V$DATABASE to view the role of
standby database. It should return ‘PHYSICAL STANDBY’.
[edit] LOG
SHIPPING
On PRIMARY site enable Log_archive_dest_state_2 to start shipping
archived redo logs.
• SQL> Alter system set
Log_archive_dest_state_2=ENABLE scope=both;
System
Altered.
Check the sequence # and the archiving mode by executing following
command.
• SQL> Archive Log List
Then switch the logfile.
• SQL> Alter system switch logfile;
System
Altered.
Now on the PRIMARY site check the status of Standby Archiving destination.
• SQL> Select Status, Error
from
v$Archive_dest
where
dest_id=2;
The STATUS should return – VALID. If it returns Error, then check the
connectivity between the Primary and Standby machines.
[edit] START PHYSICAL LOG APPLY SERVICE.
On the STANDBY database execute the following command to start Managed
Recovery Process (MRP). This command is executed on Mount stage.
• SQL> Alter Database Recover Managed
Standby Database;
Database Altered.
By executing the above command the current session will become hanged
because MRP is a foreground recovery process. It waits for the logs to come and
apply them. To avoid this hanging, you can execute the following command with
DISCONNECT option.
• SQL> Alter Database Recover Managed
Standby Database Disconnect;
Database
Altered.
Now the session will be available to you and MRP will work as a
background process and apply the redo logs.
You can check whether the log is applied or not by querying
V$ARCHIVED_LOG.
• SQL> Select Name, Applied, Archived
from
v$Archived_log;
This query will return the name of archived files and their status of
being archived and applied.
[edit]
ROLE TRANSITION :
In Dataguard configuration, two roles are defined; Primary and Standby.
Primary database is the production database which is used by the users. For
high availability purpose, the dataguard provides a standby database which
remains available side by side to the primary database. Standby databases can
be more than one and can be at remote locations as well.
Oracle Data Guard supports two role transition operations:
Switchover:
Switchover allows the primary database to switch roles with one of its
standby databases. There is no data loss during a switchover. After a
switchover, each database continues to participate in the Data Guard
configuration with its new role.
Failover:
Failover transitions a standby database to the primary role in response
to a primary database failure. If the primary database was not operating in
either maximum protection mode or maximum availability mode before the failure,
some data loss may occur. After a failover, the failed database no longer
participates in the Data Guard configuration. It needs to be reinstated to
become an active part of Data Guard configuration.
[edit]
Manual Switchover:
On the PRIMARY Database: (Open stage)
Query V$DATABASE to check the role of Primary Database.
• SQL> Select Database_role
from
v$Database;
It will return “PRIMARY”,
Now check the Switchover Status of the Primary Database.
• SQL> Select switchover_status
from
v$Database;
It will return “SESSIONS ACTIVE”.
Now you are ready to perform a manual switchover. Execute the following
command using “WITH SESSION SHUTDOWN” option.
• SQL> Alter Database Commit to
Switchover to Physical Standby with session Shutdown;
Database
Altered.
Now your PRIMARY Database has become Physical Standby. Before To verify
this change, You must Shutdown the database and again Start it to mount stage.
Again query the Database_role column of V$DATABASE. Now it will return
“PHYSICAL STANDBY”.
On the PHYSICAL STANDBY Database: (Mount stage)Query V$DATABASE
to check the role of Standby Database.
• SQL> Select Database_role
from
v$Database;
It will return “PHYSICAL STANDBY”,
Now check the Switchover Status of the Standby Database.
• SQL> Select switchover_status
from
v$Database;
It will return “SESSIONS ACTIVE”.
Now cancel the MRP which is running in the background of the Standby
Database. Execute the following command:
• SQL> Alter database Recover Managed
Standby Database Cancel;
Database
Altered.
Now you are ready to perform a manual switchover from Physical Standby
to Primary. Execute the following command using “WITH SESSION SHUTDOWN” option.
• SQL> Alter Database Commit to
Switchover to PRIMARY with session Shutdown;
Database
Altered.
Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this
change, again query the Database_role column of V$DATABASE. Now it will return
“PRIMARY”.
Shutdown the database and again Start it to Open stage.
[edit]
DATAGUARD BROKER :
The Data Guard broker is a distributed management framework that
automates and centralizes the creation, maintenance, and monitoring of Data
Guard configurations. You can use either the Oracle Enterprise Manager
graphical user interface (GUI) or command-line interface (CLI) to automate and
simplify:
• Creating and enabling Data Guard configurations, including setting up
log transport services and log apply services.
• Managing an entire Data Guard configuration from any system in the
configuration.
• Managing and monitoring Data Guard configurations that contain Real
Application Clusters primary or standby databases.
[edit]
BROKER CONFIGURATION:
On both Primary and Standby sites, change the initialization parameter
in the spfile to enable the Data guard broker.
•SQL>
Alter system set dg_broker_start=True scope=both;
System
Altered.
On the PRIMARY site, open the ‘cmd’ and start Command Line Interface
(CLI) of the Dataguard Broker (DGMGRL).
C:\>
dgmgrl
DGMGRL for
32-bit Windows: Version 10.2.0.1.0 - Production
Copyright
(c) 2000, 2005, Oracle. All rights reserved.
Welcome to
DGMGRL, type "help" for information.
DGMGRL>_
Now connect to the database through the service you made previously.
DGMGRL>
connect sys/oracle@to_primary
Connected.
Create broker configuration.
DGMGRL>
create configuration ‘broker1’ as
> primary
database is ‘primary’
> connect
identifier is to_primary;
(‘to_primary’ in Connect identifier is the service name through which
the broker is connected to the PRIMARY database)
Add Standby Database to the above configuration.
DGMGRL>
Add database ‘standby’ as
> connect
identifier is to_standby
>
maintained as physical;
(‘to_standby’ in Connect identifier is the service name through which
the broker is connected to the STANDBY database)
Now the configuration has been set up but it is still disabled. You can
view the configuration by executing:
DGMGRL>
show configuration
Configuration
Name: broker1
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLE
Databases:
primary - Physical standby database
standby - Primary database
Current
status for "broker1":
DISABLE
The next step is to ENABLE the configuration ‘broker1’.
DGMGRL>
enable configuration;
Enabled
Again view the configuration.
DGMGRL>
show configuration
Configuration
Name: broker1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLE
Databases:
primary - Physical standby database
standby - Primary database
Current
status for "broker1":
SUCCESS
[edit] Switchover:
Now we are ready to switch over the PRIMARY database Role to STANDBY
database Role.
DGMGRL>
switchover to ‘Standby’;
…..
…..
Primary Database Successfully converted to Physical Standby. You can
again switch over the Standby Database to Primary by executing following command.
DGMGRL>
switchover to ‘Primary’;
…..
…..
Standby Database is successfully converted to Primary Database.
Failover:
Failover can be done through the same configuration without any
alteration. You simply need to execute following command:
DGMGRL>
failover to ‘Standby’;
….
Failover to
standby succeeded.
And also…
DGMGRL>
failover to ‘Primary’;
….
Failover to
primary succeeded.
[edit]
DATAGUARD OBSERVER :
Observer is a utility that is available with the dataguard. Its basic
purpose is to keep the database available to the clients all the time. It is
started on a separate location other than Primary and Standby locations. After
starting the observer, it starts watching continuously both the sites. Whenever
the PRIMARY database fails due to any reason and the connection between the
PRIMARY site and the observer breaks, the observer waits for a certain time
which is set up during setting the broker configuration. When this time passes,
the observer automatically starts the failover process. As a result, the
Physical Standby database is converted to PRIMARY database. After this
failover, the users again connect to the database. This connection redirects
them automatically to the new PRIMARY database.
[edit]
OBSERVER CONFIGURATION:
There are some important configuration settings that are needed to be
set before starting the Dataguard (DG) Observer. Main thing is to set the
STANDBY database to Maximum Availability protection mode. DG broker
configuration must also be set to “MaxAvailability” protection mode. After this
we need to enable “fast_start failover” parameter of the broker configuration.
Without setting these options, broker will not allow to proceed for observer to
work for remote failover. Following are the steps to enable the observer.
[edit]
OBSERVER SITE: (Information)
OBSERVER Machine IP: 192.168.1.65PRIMARY Machine IP: 192.168.1.46STANDBY
Machine IP: 192.168.1.96
TNS Service Name for PRIMARY site: to_primary (Through this service, the
observer machine will be connected to PRIMARY machine)
TNS Service Name for STANDBY site: to_standby (Through this service, the
observer machine will be connected to STANDBY machine)
(NOTE: Fresh DG broker configuration will be created for implementation
of Observer. Therefore, remove any previously created configuration.)
[edit] Step 1:
Create TNS services on the observer machine for PRIMARY site and STANDBY
site through which the observer machine will be connected to the primary and
standby databases.
TNS service name for PRIMARY site is ‘to_primary’.TNS service name for
STANDBY site is ‘to_standby’.
[edit] Step 2:
Before starting the broker CLI (DGMGRL) we have to enable FLASHBACK on
both the Primary and Standby databases.
At mount stage execute:
• SQL> Alter database Flashback on;
Database
altered.
[edit] Step 3:
On the STANDBY site, edit Log_Archive_Dest_2 parameter and add three
properties i.e. LGWR AFFIRM SYNC. These properties are needed to bring the
broker protection mode to MaxAvailability.
• SQL> Alter System set
Log_archive_dest_2=’Service=to_primary
LGWR AFFIRM
SYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=primary’;
System altered.
[edit] Step 4:
Now execute the following command on the primary database to change the
protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY.
• SQL> Alter database set primary
database to maximize availability;
Shut down the PRIMARY database and then restart it. Check the protection
mode on both PRIMARY and STANDBY databases.
• SQL> select protection_mode
from
v$database.
It should return MAXIMUM AVAILABILITY.
[edit] Step 5:
Now start the DG CLI (DGMGRL) to start the broker.
C:\>
dgmgrl
DGMGRL for
32-bit Windows: Version 10.2.0.1.0 - Production
Copyright
(c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information. DGMGRL>
DGMGRL>
connect sys/oracle@primary
Connected.
Check any previously created configuration and remove it.
DGMGRL>
show configuration;
If a configuration is displayed then remove it.
DGMGRL>
remove configuration;
Removed.
Now create a new configuration.
DGMGRL>
create configuration ‘broker1’ as
> primary
database is ‘primary’
> connect
identifier is to_primary;
Configuration
“broker1” created with primary database “primary”
DGMGRL>
add database ‘standby’ as
> connect
identifier is to_standby
>
maintained as physical;
Database “broker1”
added
DGMGRL>
show configuration;
Configuration
Name: broker1
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLE
Databases:
primary - Physical standby database
standby - Primary database
Current
status for "broker1":
DISABLE
DGMGRL>
enable configuration;
Enabled.
DGMGRL>
show configuration;
Configuration
Name: broker1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLE
Databases:
primary - Physical standby database
standby - Primary database
Current
status for "broker1":
SUCCESS
DGMGRL>
edit database PRIMARY set property LogXptMode=’SYNC’;
Property
"logxptmode" updated
DGMGRL>
edit database STANDBY set property LogXptMode=’SYNC’;
Property
"logxptmode" updated
DGMGRL>
edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL>
enable fast_start failover;
Enabled.
DGMGRL>
show configuration;
Configuration
Name: dg1
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
primary - Physical standby database
- Fast-Start Failover target
standby - Primary database
Current status for "dg1": Warning: ORA-16608: one or more
databases have warnings
If you check this error by querying StatusReport property of the PRIMARY
database, you will find that Fast Start Failover OBSERVER is not started.
Simply start the observer.
DGMGRL>
show database primary StatusReport
Now start the observer.
DGMGRL>
start observer
Observer
Started.
The Observer has started its working. The current session of ‘cmd’ will
not be returned to you because it is a foreground process. It will continuously
observe PRIMARY site. To check the configuration, open another ‘cmd’ window and
check the configuration.
DGMGRL>
show configuration;
Configuration
Name: broker1 Enabled: YES Protection Mode: MaxAvailability Fast-Start
Failover: Enabled Databases: standby - Primary database primary - Physical
standby database- Fast – Start Failover target
Fast-Start
Failover
Threshold: 30 seconds Observer: orat
Current
status for “broker1”:
SUCCESS
___________________________________________
At this stage every thing is going on normally. An identical copy of
PRIMARY database is being made at the STANBY site. If due to any reason,
PRIMARY database crashes, the observer will start its working.
DGMGRL>
start observer
Observer
started.
15:21:21.69
Wednesday, March 07, 2007
Initiating
fast-start failover to database “standby”…
Performing
failover NOW, please wait…
Operation
requires shutdown of instance “orcl” on database “standby”
Shutting
down instance “orcl”…
ORA-01109:
database not open
Database
dismounted.
ORACLE
instance shut down.
Operation requires startup of instance “orcl” on database
“standby”Starting instance “orcl”…
[edit]
Data Guard With Real Time
Redo data is applied to the standby database as soon as it is received
from the primary database.
In Oracle9i Data Guard this apply has to wait till an archivelog is� created on the standby databaseFor Redo Apply:
ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
For SQL Apply:
ALTER
DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
When real time apply is enabled, RECOVERY_MODE column in
V$ARCHIVE_DEST_STATUS displays “MANAGED REAL�TIME APPLY”
[edit]
Real Time Apply – Benefits
Standby databases now more closely synchronized with the primary
More up-to-date, real-time reporting
Faster switchover and failover times
Reduces planned and unplanned downtime
Better Recovery Time Objective (RTO) for DR
[edit] Note
For Real Time We must create standby log files .Redo logs can be
created even after the standby has been created
Create the SRL's :
SQL>ALTER DATABASE ADD STANDBY LOGFILE
GROUP 4
(‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo4.log’) SIZE 100M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE
GROUP 5
(‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo5.log’) SIZE 100M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE
GROUP 6
(‘C:\oracle\product\10.2.0\oradata\ORCL\StandbyRedo6.log’) SIZE 100M;
[edit]
Converting To Real time
Real-time apply : When real-time apply is enabled, the log apply
services can apply redo data as it is received, without waiting for the current
standby redo log file to be archived. In this example we are going to use LGWR
on the primary for redo transport just to prove that a committed record on the
primary without switching a log will show up on the standby. However real-time
apply will work with both LGWR and ARCH using SRL's.
Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC
log_archive_dest_2='SERVICE=to_standby
LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby
'
shutdown and startup the primary or if done dynamically switch a
log file
You will see the following message in the alert log
*LGWR: Setting 'active' archival for
destination LOG_ARCHIVE_DEST_2
[edit]
Enable Real Time
On the standby cancel out of the current managed recovery
SQL>ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Place it back in recovery with Real time apply
SQL>ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
[edit] Test
Case
Create a table on Primary.
SQL> create table test
2 (
name varchar2(30));
Table
created.
Now Insert some records and commit.
SQL> insert into test
2
values
3
('Michel');
1 row created.
SQL>
commit;
Commit
complete.
Now on Standby Run these commands.
SQL>ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database
altered.
SQL>ALTER
DATABASE OPEN READ ONLY;
Database
altered.
Now check the table and record which you inserted.
SQL>
select * from test;
NAME
------------------------------
Michel
We can our table and record without any log switch.
After Checking your Real Time work Dont Forget to put your database back
in recover mode ;)
Place the standby back in managed recover mode
SQL>ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT
LOGFILE DISCONNECT;
Database
altered.
No hay comentarios:
Publicar un comentario