Manually Resolving a Gap

Manually Resolving a Gap:

In some rare cases it might be necessary to manually resolve gaps. The following section describes how to query the appropriate views to determine if a gap exists.

On your physical standby database:

Query the V$ARCHIVE_GAP view:

SQL> SELECT * FROM V$ARCHIVE_GAP;


THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

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

1 443 446


The query results show that your physical standby database is currently missing logs from sequence 443 to sequence 446 for thread 1. After you identify the gap, issue the following SQL statement on the primary database to locate the archived redo logs on your primary database:


SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 443 AND 446;


NAME


/u01/oradata/arch/arch_1_443.arc

/u01/oradata/arch/arch_1_444.arc

/u01/oradata/arch/arch_1_445.arc


Copy the logs returned by the query to your physical standby database and

register using the ALTER DATABASE REGISTER LOGFILE command.


SQL> ALTER DATABASE REGISTERLOGFILE '/u01/oradata/stby/arch/arch_1_443.arc';

SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_444.arc';

SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_445.arc';


Once the log files have been registered in the standby controlfile, you can restart the MRP process.


On a logical standby database:

Query the DBA_LOGSTDBY_LOG view.


SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L WHERE NEXT_CHANGE# NOT IN (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# =THREAD#) ORDER BY THREAD#,SEQUENCE#;


THREAD# SEQUENCE# FILE_NAME


1 451 /u01/oradata/logical_stby/arch/arch_1_451.arc

1 453 /u01/oradata/logical_stby/arch/arch_1_453.arc


Copy the missing logs to the logical standby system and register them using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on your logical standby db.


SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE /u01/oradata/logical_stby/arch/arch_1_452.arc;

DataGuard How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

1- let’s learn current SCN number with the following query on the Primary.

SQL> select current_scn from v$database;
CURRENT_SCN
———–
1289504966

2- let’s learn current SCN number with the following query on the Standby
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1289359962

using the function scn_to_timestamp(SCN_NUMBER) you can check the time difference between primary and standby.

3- Stop apply process on the Standby database.
SQL> alter database recover managed standby database cancel;

4- Shutdown the Standby database.
SQL> shutdown immediate;

5- Take incremental backup from the latest SCN number of the Standby database on the Primary database. And copy backup to the standby server.

RMAN> backup incremental from scn 1289359962 database;

# scp /backup_ISTANBUL/dun52q66_1_1 oracle@192.168.2.3:/oracle/ora11g

6- Create new standby control file on the Primary database. And copy this file to standby server.

SQL> alter database create standby controlfile as ‘/oracle/ora11g/standby.ctl’;

# scp /oracle/ora11g/standby.ctl oracle@192.168.2.3:/oracle/ora11g

7- Open the Standby database on NOMOUNT state to learn control files location.

SQL> startup nomount

SQL> show parameter control_files

8- Replace new standby control file with old files.

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data1/control01.ctl
# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data2/control02.ctl

9- Open the Standby database on MOUNT state.

SQL> alter database mount standby database;

10- Connect to the RMAN and register backup to catalog.

# rman target /

RMAN> catalog start with ‘/oracle/ora11g’;

It will ask for confirmation. Click “y” .
11- Now, you can recover the Standby database. Start recover database.

RMAN> recover database;

When recover of database is finished, it searches the latest archive file. And it gives an ORA-00334 error. In this case, don’t worry about it. Exit from RMAN and start apply process on the standby database.

SQL> alter database recover managed standby database disconnect from session;

Renaming ASM Disk group in Oracle 11gR2

Renaming ASM Disk group in Oracle 11gR2:

The renamedg utility is new in Oracle 11gR2 and it's documented in the storage administrator’s guide.

You can use this tool to rename ASM diskgroups. The prerequisite is to unmount the disk group on all cluster nodes.

The tool works in 2 phases, in the first phase it generates a configuration file and in the second phase it discovers the disks and rename the disk group.

How to use renamedg command to rename the ASM diskgroup?

The following example demonstrates how to rename the ASM diskgroup from A_DATA to B_DATA.

a. Setup the environment:
$ . oraenv
+ASM1
$ export PATH=$PATH:/dev/oracleasm/disks
$ echo $PATH

b. Dismount the ASM diskgroup to be renamed on all cluster nodes:
$ asmcmd umount A_DATA -- on all nodes.

c. Verify the diskgroup was dismounted:
$ crsctl status resource ora.A_DATA.dg

NAME=ora.A_DATA.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE

d. Using renamedg utility to rename the diskgroup:

$ renamedg phase=both dgname=A_DATA newdgname=B_DATA verbose=true
Output:
----***********----
Parsing parameters..
Parameters in effect:
Old DG name : A_DATA
New DG name : B_DATA
Phases :
Phase 1
Phase 2
Discovery str : (null)
Clean : TRUE
Raw only : TRUE
renamedg operation: phase=both dgname=A_DATA newdgname=B_DATA verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)
Checking if the diskgroup is mounted
Checking disk number:0
Checking disk number:1
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:S_1873_0681
Modifying the header
Looking for ORCL:S_1873_0685
Modifying the header
Completed phase 2
Terminating kgfd context 0x2b06e27d20a0
----***********----
e. Mounting the ASM diskgroup:
$ asmcmd mount B_DATA -- on all nodes.

f. Check if the diskgroup was renamed and mounted successfully:

$ crsctl status resource ora.B_DATA.dg

NAME=ora.B_DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on atld380, ONLINE on atld381
Yaaa, diskgroup has been renamed from A_DATA to B_DATA successfully.
Possible error when issuing renamedg command:
Error: "KFNDG-00408: disk (string:string) could not be discovered error"
Solution: Add the ASM disk path to $PATH environment variable "export PATH=$PATH:/dev/oracleasm/disks"
Output:
----***********----
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0638 with disk number:0 and timestamp (32937868 354065408)
Checking if the diskgroup is mounted
Checking disk number:0
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:S_1873_0638
ERROR: -9(Error 7217, OS Error ()
)KFNDG-00408: file not found; arguments: [] [ORCL:S_1873_0638]
Terminating kgfd context 0x2b3ddb1d80a0
----***********----

The renamedg command usage:

$ renamedg -help

Parsing parameters..
phase Phase to execute (phase=ONE|TWO|BOTH), default BOTH
dgname Diskgroup to be renamed
newdgname New name for the diskgroup
config intermediate config file
check just check-do not perform actual operation,
(check=TRUE/FALSE), default FALSE
confirm confirm before committing changes to disks,
(confirm=TRUE/FALSE), default FALSE
clean ignore errors (clean=TRUE/FALSE), default TRUE
asm_diskstring ASM Diskstring (asm_diskstring='discoverystring',
'discoverystring1' ...)
verbose verbose execution (verbose=TRUE|FALSE), default
FALSE
keep_voting_files Voting file attribute,
(keep_voting_files=TRUE|FALSE), default FALSE

OEM Grid Control Maintenance Tasks

OEM Grid Control Maintenance Tasks - Weekly/Monthly:
OEM Grid Control Maintenance Tasks - Weekly/Monthly:
OMS Monthly Maintenance Tasks:
1. OEM database partition Maintenance:
a. Shut down all OMSs:
$OMS_HOME/bin/emctl stop oms
b. Connect as SYSMAN to the OEM database and run following two procedures:
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.analyze_end_schema('SYSMAN');
SQL>commit;
SQL>exec emd_maintenance.partition_maintenance;
SQL>commit;
If you are in a scenario where you hit Bug 5357916 and can't apply the patch or upgrade to a version where the patch is available you need to do the following:
SQL>connect / as sysdba
SQL>alter system set job_queue_processes = 0;
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.remove_em_dbms_jobs;
SQL>exec emd_maintenance.partition_maintenance;
SQL>@/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql
SQL>alter system set job_queue_processes = 10;
SQL>exec emd_maintenance.submit_em_dbms_jobs;
SQL>commit;
Note: The partition maintenance will be performed automatically if you have the following configuration:
Grid Control 10gR3 (10.2.0.3) or later with the repository installed in a 10.2.0.2 database or later.
c. Confirm the Partition maintenance was successful:
select count(1) from sysman.mgmt_metrics_raw where collection_timestamp < sysdate -9;
d. Restart all OMSs:
$OMS_HOME/bin/emctl start oms
2. Rebuild/Shrink tables and indexes as required:
OMS Weekly Maintenance Tasks:
1. Check and Clear OMS and OEM database system errors.
a. OMS:
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Errors' Tab
b. OEM Database alertlog errors:
Goto: $ORACLE_BASE/POEM/bdump/alert_POEM[12].log
2. Fix target metric collection errors.
a. Cluster agent:
$AGENT_HOME/bin/agentca -d -c catlmsxt261 -- Discover
$AGENT_HOME/bin/agentca -f -c catlmsxt261 -- Reconfigure
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent
b. Standalone agent:
$AGENT_HOME/bin/agentca -f
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent
3. Start or remove targets in DOWN status and also remove all the Duplicate Targets.
4. Force delete all the "Deleted Targets"
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Overview' Tab >> Deleted Targets list
(or DELETE FROM SYSMAN.MGMT_TARGETS_DELETE; COMMIT ;)
5. Clear Critical and Warning alerts:
Goto: Alerts tab on the OEM main page.
6. Analyze Metric Rollup Tables as required:
MGMT_METRICS_RAW
MGMT_METRICS_1DAY
MGMT_METRICS_1HOUR
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
Reference:
Doc ID: 456101.1
Doc ID: 370695.1

Data Guard implementation for Oracle 10gR2 Windows

http://www.orafaq.com/wiki/Data_Guard_implementation_for_Oracle_10gR2

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.

DATAGUARD SWITCHOVER

Antes de realizar cualquier cambio de configuración o aplicar algún
procedimiento, es necesario verificar, como lo apunte anteriormente, con todo el
detenimiento necesario, el estado de operación de la configuración de nuestra
instalación.En el caso de requerir realizar un "Switchover", ó como lo
diríamos en español, "un cambio en el rol de trabajo, asumido por nuestras bases
de datos Oracle", en nuestra solución de contingencia, es importante tomar en
cuenta, las siguientes recomendaciones.

Tome su tiempo, para determinar sin grado de error, las direcciones IP y los
nombres de los host, involucrados en su configuración.
Determine, cuál servidor es el Primario y cuál el STANDBY. Si estamos
trabajando en ambiente Linux o Unix, y estamos haciendo acceso, utilizando una
herramienta de SSH o Telnet como PUTTY por ejemplo, cambié el fondo de la
terminal a un color, que le permita distinguir sin la más mínima duda, cuál
servidor es cuál.

Verifique que el servidor Primario, tiene activo el modo archive.
Verifique que el servidor STANDBY, esta recibiendo los cambios realizados en
el servidor PRIMARIO.

Antes de proceder a realizar el cambio de rol, verique que cuente con un
respaldo funcional de las bases de datos. Si realiza, respaldos diariamente, a
través de exports por ejemplo, verifique el log, para determinar que el respaldo
terminó satisfactoriamente y verifique, que todas las piezas del respaldo, estan
físicamente en el lugar donde deben estar.

Una vez, verificado los puntos anteriores proceda con los siguientes pasos:
En la base de datos PRIMARIA, verifique el estado de la base de datos de
producción, en la columna switchover_status de la vista v$database. El valor de
esta columna puede variar, según las condiciones actuales de funcionamiento de
la base de datos: NOT ALLOWED - En este estado, la base de datos
standby y la base de datos primaria, no pueden ser intercambiadas entre
sí.SESSIONS ACTIVE - Indica que existen sesiones activas en la base de
datos. Esto puede ser, tanto en la base de datos primaria, como standby. Para
poder continuar con el proceso, es necesario inicialmente desconectar a todas
las sesiones, antes de realizar el cambio de rol.SWITCHOVER PENDING - La
solicitud de intercambio ha sido recibida en la base de datos, pero aún no ha
sido procesada.SWITCHOVER LATENT - El switchover se encuentra en estado
pendiente, pero no ha sido completado y la base de datos y las bases de datos,
podrían ser devueltas a su estado original.TO PRIMARY - Esta es una base de
datos standby, sin sesiones activas y que permite ser promocionada como una base
de datos primaria.TO STANDBY - Esta es una base de datos primaria, sin
sesiones activas y que podría ser promocionada como base de datos
standby.RECOVERY NEEDED - Esta es una base de datos standby, que no ha
recibido una respuesta de intercambio de rol de trabajo.El valor
devuelto por la columna en la vista debe ser "TO STANDBY", para poder continuar con el
proceso.SQL>select switchover_status from v$database;Nota: Si al realizar la consulta, el valor indica sesiones
activas y al consultar la vista v$session, logras observar que el usuario DBSNMP
y el SYSMAN están logeados, corresponden a las sesiones, que se crean producto
de que se encuentre corriendo el "Database Console". Detenga el servicio de
DBC, con el comando emctl stop dbconsole, para poder continuar.Si
el valor obtenido es el indicado, podemos proceder a cambiar el rol de la base
de datos primaria a standby, con la siguientes
instrucciones:SQL>alter database commit to switchover to physical
standby with session shutdown;SQL>shutdown immediateSQL>startup
nomountSQL>alter database mount standby database;En este punto,
estamos desactivando, el envió del nivel transaccional a la base de datos remota
standby.SQL>alter system set
log_archive_dest_state_2=defer;En la base de datos STANDBY, una vez
realizada las tareas anteriores, debemos verificar el estado de la instancia.
El valor de la columna "switchover_status", debe ser "TO PRIMARY", para poder
continuar con la tarea.SQL>select switchover_status from
v$database;Seguidamente, debemos promocionar la base de datos standby
como base de datos primaria.SQL>alter database commit to switchover
to primary;SQL>shutdown immediateSQL>startupFinalmente,
para concluir, en la nueva base de datos STANDBY, debemos activar el modo
automático de recuperación, para que empieze a aplicar el redo generado en la
nueva base de datos PRIMARIA.SQL>recover managed standby database
disconnectFinalmente, verifique que el redo generado esta siendo
aplicado en la base de datos STANDBY, a través de la siguiente
consulta:SQL>select sequence#,applied from v$archived_log;