Create DataGuard on Windows Server

Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.

Windows 2000 with Oracle 9.2.0.8
Primary Server is called “Primary”
Standby Server is called “Standby”
A working DNS is available for both servers.
Oracle Instance is called DATAGUARD on both Servers
TNS Listener is configured as Primary.world and Standby.world

IMP:
- Configure listener.ora /Tnsnames.ora and add both Primary as well as Standby Database Services
- Always Copy the up -to- date password file from Primary to Standby
- Use Spfile for all configuration settings

Init file for Primary Database initprimary.ora
Init file for Standby Database inittstandby.ora

Sample Listener file listener.ora
Sample Tnsnames file tnsnames.ora

At Primary Database

1. Check Archivelog mode and force database into Archivelog

SQLPLUS>archive log list;
If this returns “Noarchivelog Mode” then
Add following parameters at initPrimary.ora ….
log_archive_dest_1 = ‘location=f:\Oracle\oradata\Primary MANDATORY REOPEN=5′
log_archive_format = “Primary_%S.ARC”
log_archive_start = true
log_buffer = 1000000
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

SQL>Startup nomount;
SQL>Alter database archivelog;
Do “alter system switch logfile” and see archive files are dunped at right location

SQL> archive log list;
Standbybase log mode Archive Mode
Automatic archival Enabled
Archive destination f:\Oracle\primary\DATAGUARD
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

At this stage
- COLD backup Primary Database files and ship it to Standby Server.
- At primary create standby control file
SQLPLUS> Alter Database Create Standby Controlfile as ‘D:\Oracle\Oradata\Primary\control.ctl’;
- Copy standby.ctl on to Standby Machine

SQL> startup;
SQL> show parameter spfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string %ORACLE_HOME%\StandbyBASE\SPFILE%
ORACLE_SID%.ORA

SQL> connect / as sysdba
Connected.

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

C:\Documents and Settings\oracle>dgmgrl
DGMGRL for 32-bit Windows: Version 9.2.0.1.0 – Production.
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys/oracle
Connected.

DGMGRL> show configuration verbose;
Error: ORA-16532: Standby Guard configuration does not exist

SQL> connect / as sysdba
Connected.

SQL> show parameter dg_broker_start
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

DGMGRL> show configuration verbose;
Error: ORA-16532: Standby Guard configuration does not exist

Above Error indicates we need configuration at Data Guard

Configure Data Guard

Create a new DRC and define a primary Site

DGMGRL> create configuration ‘Primary Site’
> as primary site is ‘Primary_site’
> resource is ‘Primary_db’
> hostname is ‘Primary’
> instance name is ‘dataguard’
> service name is ‘primary’
> site is maintained as physical;
Configuration “Primary Site” added with primary site “Primary_site”
Standbybase resource “Primary_db” added.
Create Secondary Site

DGMGRL> create site ‘Standby_site’
> resource is ‘Standby_db’
> hostname is ‘Standby’
> instance name is ‘dataguard’
> service name is ’standby’
> site is maintained as physical;
Site “Standby_site” added to configuration.
Standbybase resource “Standby_db” added.
View DRC config

DGMGRL> show configuration verbose;
Configuration
Name: ‘Primary Site ‘
Enabled: ‘no’
Default state: ‘ONLINE’
Intended state: ‘OFFLINE’
Protection Mode: ‘MaxPerformance’
Number of sites: 2
Sites:
Primary Site: Primary_site
Standby Site: Standby_site
Current status for “Primary Site”:
DISABLED
Enable DRC
Please review alrtlog as well as log for DMON process($BDUMP/alrtDMON.log).

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration;
Configuration ‘Primary Site’ is
Primary Site is ‘Primary_site’
Standby Site is ‘Standby_site’
Current status for “Genesys Newport”:
SUCCESS

DGMGRL> show resource Primary_db
Resource ‘Primary_db’ on site ‘Primary_site’
depends on ‘Primary_site’
Current status for “Primary_db”:
SUCCESS

DGMGRL> show resource Standby_db
Resource ‘Standby_db’ on site ‘Standby_site’
depends on ‘Standby_site’
Current status for “Standby_db”:
SUCCESS

DGMGRL> exit

At Physical Standby Database

- Configure listener.ora /Tnsnames.ora and add both Primary as well as Standby Database Services
- Always Copy up -to- date password file from Primary to Standby
- Use Spfile for all Configuration Settings
=========================================================================

Add following 2 parameters at standby init.ora file
standby_file_management=auto
standby_archive_dest=’f:\oracle\oradata\standby’

C:\Documents and Settings\oracle>oradim -new -sid DATAGUARD -startmode m

C:\Documents and Settings\oracle>set oracle_sid=DATAGUARD

C:\Documents and Settings\oracle>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 – Production on Wed Feb 14 16:12:33 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\Oracle\oradata\primary
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes
ORA-01666: controlfile is for a standby database
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> show parameter dg_broker_start;
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

Init file for Primary Database InitPrimary.ora
Init file for Standby Database Inittstandby.ora

Sample Listener file listener.ora
Sample Tnsnames file tnsnames.ora

/

Oracle Data Guard View

V$ARCHIVE_DEST Primary, physical, and logical
Describes all of the destinations in the Data Guard configuration, including each destination’s current value, mode, and status.

Note: The information in this view does not persist across an instance shutdown.

V$ARCHIVE_DEST_STATUS Primary, physical, and logical
Displays runtime and configuration information for the archived redo log destinations.

Note: The information in this view does not persist across an instance shutdown.

V$ARCHIVE_GAP Physical and logical
Displays information to help you identify a gap in the archived redo log files.

V$ARCHIVED_LOG Primary, physical, and logical
Displays archive redo log information from the control file, including names of the archived redo log files.

V$DATABASE Primary, physical, and logical
Provides database information from the control file. Includes information about fast-start failover (available only with the Data Guard broker).

V$DATABASE_INCARNATION Primary, physical, and logical
Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGS option. Records about the current and the previous incarnation are also contained in the V$DATABASE view.

V$DATAFILE Primary, physical, and logical
Provides datafile information from the control file.

V$DATAGUARD_CONFIG Primary, physical, and logical
Lists the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIG initialization parameters.

V$DATAGUARD_STATS Primary, physical, and logical
Displays how much redo data generated by the primary database is not yet available on the standby database, showing how much redo data could be lost if the primary database were to crash at the time you queried this view. You can query this view on any instance of a standby database in a Data Guard configuration. If you query this view on a primary database, then the column values are cleared.

V$DATAGUARD_STATUS Primary, physical, and logical
Displays and records events that would typically be triggered by any message to the alert log or server process trace files.

V$LOG Primary, physical, and logical
Contains log file information from the online redo log files.

V$LOGFILE Primary, physical, and logical
Contains information about the online redo log files and standby redo log files.

V$LOG_HISTORY Primary, physical, and logical
Contains log history information from the control file.

DBA_LOGSTDBY_EVENTS Logical only
Contains information about the activity of a logical standby database. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to a logical standby database.

DBA_LOGSTDBY_HISTORY Logical only
Displays the history of switchovers and failovers for logical standby databases in a Data Guard configuration. It does this by showing the complete sequence of redo log streams processed or created on the local system, across all role transitions. (After a role transition, a new log stream is started and the log stream sequence number is incremented by the new primary database.)

DBA_LOGSTDBY_LOG Logical only
Shows the log files registered for logical standby databases.

DBA_LOGSTDBY_NOT_UNIQUE Logical only
Identifies tables that have no primary and no non-null unique indexes.

DBA_LOGSTDBY_PARAMETERS Logical only
Contains the list of parameters used by SQL Apply.

DBA_LOGSTDBY_SKIP Logical only
Lists the tables that will be skipped by SQL Apply.

DBA_LOGSTDBY_SKIP_TRANSACTION Logical only
Lists the skip settings chosen.

DBA_LOGSTDBY_UNSUPPORTED Logical only
Identifies the schemas and tables (and columns in those tables) that contain unsupported data types. Use this view when you are preparing to create a logical standby database.

V$LOGSTDBY_PROCESS Logical only
Provides dynamic information about what is happening with SQL Apply. This view is very helpful when you are diagnosing performance problems during SQL Apply on the logical standby database, and it can be helpful for other problems.

V$LOGSTDBY_PROGRESS Logical only
Displays the progress of SQL Apply on the logical standby database.

V$LOGSTDBY_STATE Logical only
Consolidates information from the V$LOGSTDBY_PROCESS and V$LOGSTDBY_STATS[/b] views about the running state of SQL Apply and the logical standby database.

Switchover from primary to standby/ Switchover Primary

This process will reverse database roles in a Data Guard setup, i.e. standby database becomes primary.

It’s assumed that:

1. You already have working Data Guard setup between primary and standby
2. Archive redo is begin shipped and applied from primary to standby
3. Archive redo application is up to date between primary & standby

Requirements :

1. All applications must be shutdown
2. Switchover must be initiated on primary database before being completed on standby database
3. The primary database must be open, the standby database must be mounted and in managed recovery mode

Note
When you perform a switchover the controlfile type is converted in-place, i.e. the primary controlfile becomes a standby controlfile and vice-versa. You must be CAREFUL which database you are working on…..

On the STANDBY database

1. Check archive_lag_target

SQL> show parameters lag

NAME TYPE VALUE ———————————— ———– —————————-
archive_lag_target integer 0

If non-zero set to zero using

SQL> alter system set archive_lag_target=0 scope=both;

On the PRIMARY database

1. Shutdown RAC databases

(RAC environment only) Shutdown all but one of the primary database instances.

2. Verify it is possible to switchover

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

Switchover requires that there be only one active session (the one performing the switchover).

Documentation states the above query should return “TO STANDBY” for switchover to succeed but it – always returns “SESSIONS ACTIVE” because of the one active session – Problemo!!

The key point is to make sure there is only one user connected – SYS – check V$SESSION

3. Initiate the switchover

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

This converts the primary database to standby. Current controlfile is backed up to trace should you need to reverse the switchover. At this stage we temporarily have two standby databases…

4. Shutdown, restart and mount as standby

SQL> SHUTDOWN NORMAL;

ORA-01507: database not mounted <= This can be ignored

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 920912008 bytes
Fixed Size 711896 bytes
Variable Size 285212672 bytes
Database Buffers 111554492 bytes
Redo Buffers 811008 bytes

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

5. Verify switchover status

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

According to documentation this should return “SWITCHOVER PENDING”, same catch as before, there has to be one session active to do the switchover.

6. Verify switchover status

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

According to the documentation this should return “SWITCHOVER PENDING” but I have only ever seen “SESSIONS ACTIVE”, same situation as before – there has to be one session active to do the switchover.

On the STANDBY database

6. Switch standby database to primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;

Database altered.

7. Shutdown and restart the new primary database

SQL> SHUTDOWN

ORA-01507: database not mounted <= This can be ignored

ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 920912008 bytes
Fixed Size 711896 bytes
Variable Size 285212672 bytes
Database Buffers 111554492 bytes

Redo Buffers 811008 bytes
Database mounted.
Database opened.

The database is now the primary database.

On the STANDBY database (the original primary)

8. Start managed recovery and log apply services and set archive lag target

SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=900 SCOPE=BOTH;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

On the PRIMARY database (the original standby)

9. Begin archiving logs

SQL> ALTER SYSTEM ARCHIVE LOG START;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

On BOTH databases

10. Check the alert logs to make sure archived redo is being sent and received

The primary alert log should look like this

ARC1: Evaluating archive log 3 thread 1 sequence 30

ARC1: Archive destination LOG_ARCHIVE_DEST_2: Previously completed

ARC1: Beginning to archive log 3 thread 1 sequence 30

Creating archive destination LOG_ARCHIVE_DEST_1: ‘/u01/app/oracle/archive/arch/DATAGD_1_30.arc’

ARC1: Completed archiving log 3 thread 1 sequence 30

The standby alert log should look like this

Media Recovery Waiting for thread 1 seq# 30 (in transit)

Fri Sep 3 10:12:14 2004

Media Recovery Log  ‘/u01/app/oracle/archive/arch/DATAGD_1_30.arc

Step by step creating Data Guard . . .

Step by Step creating Oracle Dataguard
—————————————
STARTUP MOUNT;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/boston.ctl’;
ALTER DATABASE OPEN;
CREATE PFILE=’/tmp/initboston.ora’ FROM SPFILE;

MODIFIKASI INIT FOR PRIMARY DB AND STANDBY DB

COPY DATAFILE AND LOGFILE TO STANDBY SERVER    ( ex : using scp)

CREATING PASSWORD FILE  ON STANDBY DATABASE (PASSWORD MUST BE SAME BETWEEN PRIMARY AND STANDBY) using orapwd utility as following :

orapwd file=password_file_name password=the_secret_password
orapwd file=password_file_name password=the_secret_password entries=n

STARTUP NOMOUNT PFILE=INITSTDBY.ora

CREATE SPFILE FROM SPFILE

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

Connect to the physical standby database, and create the standby redo logs to support the standby role.

standby redo logs must be the same size as the primary database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads
This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

QL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
Creating a Single Instance Physical Standby for a RAC Primary Page 6
Maximum Availability Architecture
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;