Re-start Active Dataguard

RE-Enable log apply service (By restarting Guard)

[sysdba@dataguard:stbydb-/cluster/home/oracle]$ sid

SIDs on this node:

  +ASM

  stbydb

ORACLE_SID = [stbydb] ?

The Oracle base remains unchanged with value /cluster/app/grid

ORACLE_SID=stbydb

ORACLE_HOME=/cluster/app/grid/product/11.2.0/db_1

[sysdba@dataguard:stbydb-/cluster/home/oracle]$ hems

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 14 11:40:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SYS@stbydb >>  alter database recover managed standby database cancel;

Database altered.

SYS@stbydb >> shutdown immediate;

ORA-01013: user requested cancel of current operation

SYS@stbydb >> shutdown abort;

ORACLE instance shut down.

SYS@stbydb >> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 5.3848E+10 bytes

Fixed Size                  2239232 bytes

Variable Size            2.5904E+10 bytes

Database Buffers         2.7783E+10 bytes

Redo Buffers              158928896 bytes

Database mounted.

SYS@stbydb >> alter database open read only;

Database altered.

SYS@stbydb >> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

SYS@stbydb >> alter database recover managed standby database using current logfile disconnect;

Database altered.

SYS@stbydb >>

11g - automatically changes audit_trail to OS

11g - automatically changes audit_trail to OS

From Audit Trail-----


Mon Sep 10 10:53:06 2012
alter database open read only
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-onlyccess

SYS@STANDBY >> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@STANDBY >> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2225064 bytes
Variable Size             159386712 bytes
Database Buffers           50331648 bytes
Redo Buffers                5214208 bytes
Database mounted.
SYS@STANDBY >> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /cluster/app/grid/product/11.2
                                                 .0/db_1/admin/STANDBY/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
SYS@STANDBY >> alter database open read only;

Database altered.

SYS@STANDBY >> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /cluster/app/grid/product/11.2
                                                 .0/db_1/admin/STANDBY/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string       OS
SYS@STANDBY >>
SYS@STANDBY >>
SYS@STANDBY >> alter database recover managed standby database using current logfile disconnect;

Database altered.

Useful Scripts for Data Guard

For Data Guard environments, I find the following scripts very useful.

Script to Collect Data Guard Primary Site Diagnostic Information
Note:241374.1

Script to Collect Data Guard Physical Standby Diagnostic Information
Note:241438.1

Script to Collect Data Guard Logical Standby Table Information
Note:269954.1

CHECKLIST BEFORE SWITCHOVER OPERATION

CHECKLIST BEFORE SWITCHOVER OPERATION
=======================================
Please note

log_archive_dest_state_3 :(archive dest for standby database)
log_archive_dest_state_1 :(archive dest for primary database)

SELECT PROTECTION_MODE FROM V$DATABASE;
SQL> select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_state_1','log_archive_dest_3','log_archive_dest_state_3);

Make sure 'lgwr' and not 'arch' is mentioned here, otherwise new primary database will not open after switchover (if the protection_mode is Maximum availability).

-- on standby database--
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

-- on primary --
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL>select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_state_1', 'log_archive_dest_3','log_archive_dest_state_3);
log_archive_dest_state_3
------------------
DEFER --->DO NOT PROCEED.
issue this command : sql> alter system set log_archive_dest_state_3='ENABLE' scope=both;


---- on primary ----
SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

If any file(s) are in RECOVER status, DO NOT PROCEED with the switchover


-- on standby database--
SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
MOUNTED --->PROCEED with switchover.



SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
READ ONLY --->DO NOT PROCEED with switchover.

To confirm that logs are being shipped and more importantly the LAST LOG archived has been applied…

---- on primary ----
SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
3988

-- on standby database--
SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
3988

---> PROCEED

Thanks

How To Configure Data Guard Broker

Configuring Data Guard Broker:

NOTE: You should have your physical standby already setup.

How to Setup Data Guard

1.) Check parameter DG_BROKER_START on primary:

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE

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

dg_broker_start boolean FALSE

2.) Set DG_BROKER_START to true on primary:

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

3.)Check DG_BROKER_START on standby side:

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE

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

dg_broker_start boolean FALSE

4.) Set DG_BROKER_START to true on standby:

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

5.) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME:

Update the listener.ora file on primary and standby

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl.abc.com)

(ORACLE_HOME = /opt/app/oracle/10.2)

(SID_NAME = orcl)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl_DGMGRL.abc.com) -->_DGMGRL.

(ORACLE_HOME = /opt/app/oracle/10.2)

(SID_NAME = orcl)

)

)

6.) Now to create the data guard broker configurations:

[oracle@APP3 admin]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle

Connected.

DGMGRL> create configuration 'DBTEST'

> as primary database is 'orcl'

> connect identifier is orcl;

Configuration "DBTEST" created with primary database "orcl"

DGMGRL>

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

Current status for "DBTEST":

DISABLED

DGMGRL>

7.) Now add standby DB to the data broker configuration:

DGMGRL> add database 'orcl1' as

> connect identifier is orcl1

> maintained as physical;

Database "orcl1" added

DGMGRL>

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: NO

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

orcl1 - Physical standby database

Current status for "DBTEST":

DISABLED

8.) Now enable the configurations:

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

orcl1 - Physical standby database

Current status for "DBTEST":

Warning: ORA-16607: one or more databases have failed

DGMGRL> show database verbose orcl1

Database

Name: orcl1

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: ONLINE

Instance(s):

orcl

Properties:

InitialConnectIdentifier = 'orcl1'

ObserverConnectIdentifier = ''

LogXptMode = 'ARCH'

Dependency = ''

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '180'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'MANUAL'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '3'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl'

LogFileNameConvert = '/opt/app/oracle/oradata/orcl, /home/oracle/oradata/orcl, /opt/app/oracle/flash_recovery_area/orcl/onlinelog, /home/oracle/oradata/flash_recovery_area/orcl'

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'wrpws'

SidName = 'orcl'

LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=wrpws)(PORT=1521))'

StandbyArchiveLocation = '/home/oracle/oradata/orcl/archive'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.arc'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "orcl1":

Error: ORA-12545: Connect failed because target host or object does not exist

DGMGRL>

Well the problem in my case was that the hostname was not getting resolved. I added the hostname to the host file and it started working.

After this error I got error:

Current status for "orcl1":

Error: ORA-16664: unable to receive the result from a remote database

Well same problem, communication between the 2 database. Make sure that the /etc/hosts files have entries of all other servers, with name and IP address.

Always review the log_archive_dest_2 parameter, to have the right entries and service is accessible.

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name: DBTEST

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

orcl - Primary database

orcl1 - Physical standby database

Current status for "DBTEST":

SUCCESS

DGMGRL>

This should setup Data Guard Broker.

COMMANDS Available in DGMGRL:

DGMGRL> help

The following commands are available:

add Add a standby database to the broker configuration

connect Connect to an Oracle instance

create Create a broker configuration

disable Disable a configuration, a database, or Fast-Start Failover

edit Edit a configuration, database, or instance

enable Enable a configuration, a database, or Fast-Start Failover

exit Exit the program

failover Change a standby database to be the primary database

help Display description and syntax for a command

quit Exit the program

reinstate Change a disabled database into a viable standby database

rem Comment to be ignored by DGMGRL

remove Remove a configuration, database, or instance

show Display information about a configuration, database, or instance

shutdown Shutdown a currently running Oracle instance

start Start Fast-Start Failover observer

startup Start an Oracle database instance

stop Stop Fast-Start Failover observer

switchover Switch roles between the primary database and a standby database

Use "help " to see syntax for individual commands

Manual Standby Database Under Standard Edition

Oracle's Standby technology has been rebranded as DataGuard in recent versions of Oracle. Oracle has added a layer of technology automation on top of the standby technology, making automatic Standby technology seamless. But what about the folks on Standard Edition Oracle? Are they left out in the cold?

Well, it turns out that it is still possible to create a *manual* standby database on Oracle SE. Here's how you do it.

1. First you need to create the initial standby database. Here are the steps to do that:

a. Put the primary database in archivelog mode, if it is not already, and add at least LOG_ARCHIVE_DEST and LOG_ARCHIVE_START to your init.ora.

  SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER SYSTEM ARCHIVE LOG START;

b. Next, create a hotbackup of the primary database. Although you can do this with RMAN, it is probably easiest to just do it manually so you know what is going on. For each tablespace do:

SQL> alter tablespace EXAMPLE begin backup; SQL> !cp example01.dbf /my/db/backup/ SQL> !cp example02.dbf /my/db/backup/ SQL> !cp example03.dbf /my/db/backup/ SQL> alter tablespace EXAMPLE end backup; 

In the above example, the '!' symbol tells sqlplus to run the command from the shell, so we're using the Unix 'cp' command to make copies of those files (which are now frozen in backup mode) in another location.

c. Now, create a standby controlfile from primary database:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.ctl';

d. At this point, you want to copy everything over to the standby server including datafiles, standby controlfile & config files:

$ scp /my/db/backup/*.dbf oracle@192.168.0.10:/export/home/oracle/

e. From the standby machine, edit the standby init.ora file. Use this parameter to tell Oracle where files on the primary database will be located on the standby. For example if you had files in /ora/oracle on primary, and they are moved to /export/home/oracle on standby, this would work for you:

DB_FILE_NAME_CONVERT='/ora/oracle','/export/home/oracle'

Note that you can use MULTIPLE pairs of values here, if you have files in different locations. Alternatively, you can startup and mount the standby database then issue:

SQL> alter database rename file '/ora/oracle/myfile.dbf' to /export/home/oracle/myfile.dbf' as an example.

Now you're also likely to have a new location for your archived redo log files, and that's where the parameter LOG_FILE_NAME_CONVERT comes into play.

Important note, neither of these two parameters work for the ONLINE redolog files. Those you will have to rename yourself. If you do not do so, you will get an error at the time you try to SWITCHOVER your standby database. Such errors are easily remedied by running that command.

f. Now, it's time to start the standby instance and mount it.

SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standby SQL> atler database mount standby database; 

g. Almost there. Lastly, we need to recover the standby database using the AUTO option. Note that you should build a simple shell script to startup sqlplus and run these commands. A name like manual_standby.sh would work well. You can then run this periodically, say every half hour, from cron to apply any new archived redolog files that have showed up via move_standby.sh below.

SQL> recover standby database; AUTO

h. Now, of course, you'll want to test your standby database. You do this by starting up in read-only mode.

SQL> alter database open read only;

i. Don't forget to put it back in standby mode so that when your manual_standby.sh script runs from cron, it won't return errors.

SQL> shutdown immediate; SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standby SQL> atler database mount standby database; 

2. What scripts should run via cron on the primary and standby database?

As we mentioned earlier, a script called manual_standby.sh would work well on the standby database. This script applies new archived redologs that have arrived from the production system. Run it every half hour and see how that works for you. The database must be mounted in standby mode (not read-only) or this script will fail.

You'll also want a script on the production server. Name it move_standby.sh, and run it every thirty minutes to start with. This can use rsync to move redolog files from production to standby. A command like this would work:

$ rsync -e ssh -Pazv /ora/oracle/arch/ oracle@remote:/export/home/oracle/arch/

Note that you may want to adjust options to ssh to your needs. In addition, this presumes you have ssh autologin configured. Read up on the ssh-keygen command. The .ssh directory contains a public key, which is shipped over to the standby machine, and put in the "authorized_keys" file. ssh will then login without a password. Rsync uses ssh as the transport mechanism, so it also executes without a password. Rsync is very smart and only copies blocks and pieces of files that are different, so it is very fast, and also does checksums to guarantee consistency.

3. Is the standby database behind the production database?

Yes, keep in mind we are creating a manual standby database. The standby database will tend to be behind production by about half the size of a redolog file. So if those files are 100M, and you generate 100M of transactions in 30 minutes, then on average standby will be fifteen minutes behind.

4. What types of changes and statements on production will not be automatically applied to standby?

In database parlance, any PHYSICAL changes to the db, plus any commands, issues with the NOLOGGING option. Physical changes include creation of new tablespaces, adding new datafiles, renaming datafiles, autosizing of datafiles, altering redolog files, altering controlfiles and so on. In addition, primary database processes or commands using the UNRECOVERABLE option will not be propagated to the standby database.

There are specific and detailed instructions for making some of these physical changes on the standby db manually, however in many cases recreating the entire standby database per the instructions above, might be the best option.

5. How can we verify that the standby database is up to date?

If you already have the manual_standby.sh script running from cron, disable it.

Then login with sqlplus and issue:

SQL> alter database open read only;

Now that you have the database open read-only, run whatever SQL commands you want to in order to verify some change which you know about on production. When you are done, shutdown, and startup in standby mode again. Don't forget to reenable manual_standby.sh in the crontab.

6. What happens if the standby system restarts?

You could have it automatically start the standby database. In that case, be sure to just check the logfiles. If you want to do it manually in those instances, fire up sqlplus and then issue:

SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standby SQL> alter database mount standby database; 

7. What kind of messages can I expect to see in the standby alert.log?

The alert.log is going to have a lot of extra messages since we are repeatedly trying to RECOVER when there may or may not be new transaction logs. When it does this it will say, "looking for archived logfile 1_356.dbf, not found". On the other hand, if it finds it, it will say that it is applying it. You can use unix commands "grep" and "less" to scan through the alert.log file quickly.

8. What other scripts should be put in place?

a. a script to cleanup old archived redo logs on primary.

b. a script to cleanup old archived redo logs on standby

c. a script to rotate and archive the alert.log file when it gets large

d. a script to watch the alert.log file for ORA-xxxxx errors and report them to nagios if it finds any (on both primary and standby)

e. a script to login (via ssh autologin) and check what the latest archived redolog file is, and then also login to the standby and check the alert.log file to verify that those transactions have been applied.

9. How do we switchover in the event of a failure of the primary?

Switchover *can* be done with a script, however I recommend with our manual standby database that you (a) monitor for emergencies on production and (b) manually perform the failover if necessary. This will avoid false positives. Also, it allows you to ship additional redolog data if you have it available from production.

The switchover is a two-step process.

a. Apply remaining redo as we have done before with commands in manual_standby.sh.

b. Startup the database normally, in a read-write mode.

10. What network changes need to happen to failover?

The listener.ora file should be already configured. You can use the same config as primary with a different IP, or you can give this db a different tnsname. For instance, you could call primary SEANA and standby SEANB. Then in your application server configs, when you failover, your database connection configurations need to be updated to point to SEANB. The app servers will probably also need to be restarted at this point.

11. Why can't the primary ship redologs and synchronous changes?

Basically they call it a *manual* standby database for a reason. DataGuard supports options that look like the following:

LOG_ARCHIVE_DEST_3='SERVICE=stby1 LGWR SYNC AFFIRM' LOG_ARCHIVE_DEST_STATE_3=ENABLE

Again, these are not available in Oracle SE.

12. Once we've failed over, how do we switch back to the primary?

Switching back to the primary database involves these steps:

a. Follow the steps in item 1 above to create a standby database on what was the primary system.

b. If you want to be perfectly clean syncing, do the following:

SQL> shutdown immediate SQL> startup restrict SQL> alter system switch logfile SQL> shutdown immediate

c. Copy over the last archived redolog files

d. Apply them and switchover as described in item 8 above.

13. Are there special init.ora parameters? What makes our standby database special?

The main two things that make it a standby database are:

a. The standby control file (created from primary)

- alter database create standby controlfile as '/my/path/to/standby.ctl

b. The process of mounting as a standby database

- startup nomount pfile=standby.ora

- alter database mount standby database;

There are of course some init.ora parameters which are special for the standby database as well:

DB_FILE_NAME_CONVERT LOG_FILE_NAME_CONVERT

So if you do a "shutdown immediate" on the standby, you would start again with:

SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standby SQL> alter database mount standby database;   

Conclusion:

Standby database technology in Oracle is a powerful high availability solution. Even if you're using Oracle SE, you can still take advantage of these features built into Oracle, with just a little scripting, hand holding, and ample monitoring. Do your research, test, test, and test again on a development server. And don't forget to monitor all your logfiles for errors. Following these guidelines, you should be in very good shape, at a much lower cost.