Friday, 7 October 2011

Data Guard/Broker Configuration Oracle 10g

PRIMARY SITE: (Information)
  •                      Machine IP: 192.168.1.10
  •                      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)
STANDBY SITE: (Information)
  •                      Machine IP: 192.168.1.20
  •                      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)


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;
initPrimary.ora file setting 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, create standby log file for 
and data broker setting
•       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 the created standby control file to the standby datafile location.
Create spFile from pfile.
•       SQL> Create spfile from pfile;
Restart the primary database.
on PRIMARY machine create an entry in TNSnames.ora file through which the PRIMARY site will be connected to the Standby.
TO_STANDBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = standby)
   )
 )
Also check the connectivity from the SQL Prompt.
•       SQL> connect sys/oracle@to_standby as sysdba
Connected.
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’.
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’;
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 an entry in TNSnames.ora file through which the STANDBY machine will be connected to the PRIMARY machine.
TO_PRIMARY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = primary)
   )
 )
Check the connectivity from the SQL Prompt.
•       SQL> connect sys/oracle@to_primary as sysdba
Connected.
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’.
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.
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 your session will be taken by oracle and become in hang state . 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 (media recovery process )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.
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 “NOT ALLOWED”
Now check the Switchover Status of the Standby Database.
•       SQL> Alter database commit to switch over to primary;
It will change the role of standby then shutdown immediate the standby and start it again as you start your primary and start the log apply services as mentioned above.
Revert back the standby database to Primary and vice versa
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.
DATAGUARD BROKER :
• 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.
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
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.
 with the same configuration and pc names and IP you can perfectly implement data guard / broker to your oracle database.