Implementing Data Guard

Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service





Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search \| Publications \| Jobs \| Personnel Directory \| Links

Implementing Data Guard

Note: Except for the "Switching Primary and Standby for Disaster Planning Tests" section, most of this has been superseded by "Creating a Data Guard Standby Database Using RMAN" on the "RMAN Backups and Cloning" web page. However, the explanation of the Data Guard terminology, parameters, and commands, the dgstartup.shl and dgshutdown.shl scripts, and the reference links for Data Guard will remain on this page.

Recovering from a database crash or hardware failure can take hours or days. But, with a Data Guard standby database, your users can be back up and running within minutes. This presentation shows you step-by-step how to implement and configure a standby database locally or on a remote site so that it's ready to take over your processing at a moment's notice with no data loss, just using sqlplus and the Oracle 9i or later that you probably have now.

See how to quickly implement a Data Guard standby database in a day.
Learn how to switch over or fail over to your standby database in minutes.
Possibly offload your batch reporting workload to your standby database.
Replace your forebodings about crashes with "Don't worry ... be happy!"
(by Bobby McFerrin; Simple Pleasures; EMI-Manhattan Records; 1988)

The presentation given at SETA Central 2005 and the Arkansas Banner Users Group 2006 entitled "Crashes Happen - Downtime Won't - With Data Guard" is in DataGuard.ppt (about 170 K) If you don't have Microsoft PowerPoint on your PC, you can download the presentation along with the PowerPoint viewer (for Windows XP) at DataGuard_with_Viewer.zip (about 3 Meg).

Note: This is a work in progress, which I will be adding to as we test our implementation (we went live with Data Guard on our production database on 6/15/07, with our standby on a remote site). However, there is enough information here for you to create and begin testing your own Data Guard implementation.

Note 2: I am only covering implementing physical standby databases here. Logical standby databases can only be implemented in Banner for reporting purposes, not for failover or switchover purposes, since Banner uses datatypes that aren't supported by logical standby databases (such as LONG's). To see those tables with unsupported datatypes, run the following (thanks to Lee Johnston at the University of West Florida for this heads-up):
    select distinct owner,table_name from dba_logstdby_unsupported order by owner,table_name;
If you do implement logical standby's for Banner, you should exclude those unsupported tables from the standby processing, such as using the following for each of those tables listed above (read the chapters on logical standby's for further information; thanks to April Sims at Southern Utah University for this information):
    exec dbms_logstdby.skip('DML','<owner>','<tablename>');
    exec dbms_logstdby.skip('SCHEMA_DDL','<owner>','<tablename>');

For this particular Oracle 9iR2 Data Guard implementation, we will be creating and testing a local physical standby database running in Maximum Availability mode [1.4] for a primary database, then, recreating the standby database on the remote site and testing it again there (to be added later). For the local standby database (which, in this case, is not on a separate local server), the same directory structure as the primary database will be used here, except for the instance-level name (for example, /data/oradata/PPRD2 contains the standby's datafiles paralleling the primary's /data/oradata/PPRD directory). If you want to skip the local testing, you could modify these steps to create the standby directly on the remote site or on a separate local server (which, in addition to FTP'ing instead of copying the files, would require that some of the commands and "file name convert" parameters be changed to accomodate any differences in directory structures on the remote site, the primary site's tnsnames.ora file would have to point to the remote site for the standby, and the remote site's listener.ora, tnsnames.ora, and sqlnet.ora files would have to be set up).

For your own Data Guard implementation, you might want to copy and paste this page into Notepad (with Word Wrap turned on), then:

Change PPRD2 to be the SID for your standby database,
Change PPRD to be the SID for your primary database,
Change myserver_pprd2 to be the connect string for your standby database,
Change myserver_pprd to be the connect string for your primary database,
Change the directories to match your own directory structures, such as for data, index, and redo log directories (but this also shows the SQL to list them), along with the Oracle home directory,
Change 123.45.67.89 to be your own host IP number for the primary site in the tnsnames.ora files,
Change 234.56.78.90 to be your own host IP number for the standby site in the tnsnames.ora files,
Possibly change the "file name convert" parameters and other SQL commands if you're doing doing anything more complicated than just putting the local standby database in directories similar to the primary database except for the SID level (such as "/data/oradata/<SID>/...").

The following steps are mostly from the "Oracle Data Guard Concepts and Administration Release 2 (9.2)" guide and "Implementing Oracle9i Data Guard for Higher Availability" by Daniel T. Liu (see References at end; [the numbers in brackets are chapter and section numbers in the Oracle guide]). I will not be covering everything in Data Guard here or explaining what each command or entry is, but will just be giving you enough information to get it going at your site. See the references, guides, and other information available on the internet and elsewhere for detailed explanations and other options that may be applicable to your particular site and configuration needs (such as multiple standby's). This implementation does not use the Oracle Data Guard Broker (graphical user interface), or the Data Guard command-line interface (DGMGRL), but, instead, will just be using sqlplus commands to set up, test, and monitor the standby database. (Note: Oracle Data Guard is only available in Oracle Database Enterprise Edition, not Standard Edition.)

These instructions can be used with either spfile's (server parameter files) or pfile's (init.ora parameter initialization files). All of the Data Guard parameters are included in both the primary and standby databases so that you can quickly switch between the two (primary becomes standby and standby becomes primary; called a switchover) or bring up the standby as the new primary (when the primary has crashed; called a failover) without having to modify or switch to a different parameter file (for primary or standby configuration).

Also included here are shutdown and startup scripts that can be run on either a Data Guard primary database or standby database (or with non Data Guard databases). The scripts test to see what kind of database they are running on, and run the appropriate commands to gracefully shut down or start up that particular database.

Step-By-Step Instructions for Implementing Data Guard:

Note: Be sure to set the correct Oracle environment and SID for the database you need to work with, pointing either to the primary database:

   . oraenv
      PPRD

or to the standby database:

   . oraenv
      PPRD2

before running any of the commands below (but after adding the standby database to /etc/oratab). The best way may be to have two unix sessions running, one with PPRD and one with PPRD2. To see which instance you are in (such as PPRD or PPRD2), and it's role (such as PRIMARY or PHYSICAL STANDBY) and status (such as OPEN or MOUNTED):

   select name,instance_name,database_role,status,open_mode from v$instance,v$database;

Also note that you need to be connected as sysdba (sqlplus "/ as sysdba") to run most of these commands.

Create an spfile (in the $ORACLE_HOME/dbs directory) for your primary database, if needed (see Notes at end), and bounce the database to have it take effect.

   . oraenv
      PPRD
   sqlplus "/ as sysdba"
   create spfile from pfile;
   shutdown immediate
   startup
   exit

Note: BEFORE making any of the Data Guard changes to the primary's parameter file (spfile or init.ora pfile), prepare the primary database for standby database creation, including the FORCE LOGGING, ARCHIVELOG mode, MAXLOGFILES check, and creating the directory and datafile copy commands below, just in case you need to shut down and start up the primary database during this preparation.

Turn on FORCE LOGGING on the primary database, if not already on (keep it on as long as the standby database is required) [3.1.1].

   . oraenv
      PPRD
   sqlplus "/ as sysdba"
   select force_logging from v$database;
   alter database force logging;

Ensure the primary database is in ARCHIVELOG mode and automatic archiving is enabled [3.1.2] (the standby database will also need to be in ARCHIVELOG mode if it is switched to become a primary database [7.1.2.1]). For log_archive_format, you may want to include %D the for database ID, such as in arch_PPRD_%D_%S.arc, and %T for the thread (required for RAC - Real Application Clusters), such as in arch_PPRD_%D_%T_%S.arc.

   archive log list
      should show:
         Database log mode    Archive Mode
         Automatic archival   Enabled
   If the database is not in ARCHIVELOG mode:
      If you are using an spfile:
         alter system set log_archive_dest = '/orcl/oradata/PPRD/archivelogs' scope = spfile;
         alter system set log_archive_format = 'arch_PPRD_%S.arc' scope = spfile;
         alter system set log_archive_start = true scope = spfile;
      Else, if you are using a pfile:
         edit the $ORACLE_HOME/dbs/initPPRD.ora file to contain:
            log_archive_dest = /orcl/oradata/PPRD/archivelogs
            log_archive_format = arch_PPRD_%S.arc
            log_archive_start = true
      shutdown immediate
      startup mount
      alter database archivelog;
      alter database open;
      archive log list

Ensure that the MAXLOGFILES value for the primary database is at least one more than twice the actual number of redo log groups in the primary database, since we will be adding standby redo log groups to both the primary and standby databases [5.3.3.1.1-2].

   select records_used "Current Groups",records_total "Max Groups",
      decode(sign(records_total-(records_used*2+1)),-1,LPAD('YES',21),LPAD('NO',21))
      "Recreate MAXLOGFILES?"
      from v$controlfile_record_section where type = 'REDO LOG';

If Max Groups is less than (Current Groups * 2) + 1, then recreate the control file with a larger value for MAXLOGFILES.

   alter database backup controlfile to trace;
   select value from v$parameter where name = 'user_dump_dest';
   !ls -ltr /pgms/oradata/PPRD/udump | tail
   !vi /pgms/oradata/PPRD/udump/pprd_ora_475358.trc
      Edit the latest .trc (textual control) file and remove all lines before the
      STARTUP NOMOUNT line, change the maxlogfiles value from, say, 6 to 10, comment
      out (put # in front of) the RECOVER command, and, for Oracle 9i and above,
      remove the lines after ALTER DATABASE OPEN, or, if present, ALTER TABLESPACE
      TEMP ADD TEMPFILE, and change all comment lines to start with dashes.  The vi
      commands to do these (for 9i+) are usually:
         :1,/STARTUP NOMOUNT/-1d
         :/MAXLOGFILES/s/6/10/
         :/RECOVER DATABASE/s/^/# /
         :/ALTER TABLESPACE TEMP/+2,$d
         :1,$s/^#/--/
         :wq
   shutdown immediate
   @/pgms/oradata/PPRD/udump/pprd_ora_475358.trc

Set up the listener.ora file (in $ORACLE_HOME/network/admin) to specify the new PPRD2 standby at the standby site, which is this local site for a local standby [3.2.8]. When they are not on the same system, this means to put PPRD2 into the remote site's listener.ora file. (The SID_NAME is actually the INSTANCE_NAME parameter value specified in the standby's pfile or spfile, not the DB_NAME parameter value.)

  (SID_DESC=
   (SID_NAME=PPRD2)
   (ORACLE_HOME=/pgms/oracle/product/v9203)
  )

Set up the tnsnames.ora file (in $ORACLE_HOME/network/admin) to specify the connect string (myserver_pprd2) for the new standby at the primary site, which is this local site [3.2.10]. When they are not on the same system, this means a minimum is to put myserver_pprd2 into the primary site's tnsnames.ora file (but, go ahead and put both myserver_pprd on the standby site's tnsnames.ora file and myserver_pprd2 on the primary site's tnsnames.ora file for switchovers).

      myserver_pprd2 = 
         (DESCRIPTION = 
         (ADDRESS_LIST = 
             (ADDRESS = 
               (PROTOCOL = TCP)
               (Host = 234.56.78.90) -- whatever host IP has PPRD2
               (Port = 1521)
             )
         )
         (CONNECT_DATA = (SID = PPRD2)
         )
       )

In the sqlnet.ora parameter file (in $ORACLE_HOME/network/admin) on the standby site, enable dead connection detection [3.2.9], in minutes (but, put this on both primary and standby sqlnet.ora files for switchovers [A.7]).

   sqlnet.expire_time=2

Add the standby database to /etc/oratab on the standby site. Note: It can't be set to start or stop when dbstart or dbshut is run (requires special commands), so, set the flag to N. TBD: Will also have to set PPRD's startup to N and change the backup scripts and any shutdown scripts to cancel managed recovery on the standby, and change any startup scripts (and the backup scripts) to start the standby in managed recovery mode.

   PPRD2:/pgms/oracle/product/v9203:N

Create the directories for the standby database (from unix user oracle; including directories that may be used after switching standby to primary role). Note that the "replace's" here change the directory name strings containing "/PPRD" for the primary database to "/PPRD2" for the standby database. Equivalent conversions are used throughout this presentation. If your directory name mapping from primary to standby is different, you will need to modify the code and parameters for all of these conversions to match your site's needs.

   On the primary database:
      select distinct 'mkdir -p ' ||
         replace(substr(file_name,1,instr(file_name,'/',-1)-1),'/PPRD','/PPRD2')
         from dba_data_files
      union
      select distinct 'mkdir -p ' || replace(value,'/PPRD/','/PPRD2/') from v$parameter
         where (name in ('background_dump_dest','user_dump_dest','core_dump_dest',
         'audit_file_dest') or name like 'log_archive_dest%')
         and name not like 'log_archive_dest_state%' and value is not null;
   Run the resulting unix commands on the standby's site, for example for this
   local standby:
      host
      mkdir -p /data/oradata/PPRD2
      mkdir -p /ndxs/oradata/PPRD2
      mkdir -p /orcl/oradata/PPRD2/archivelogs
      mkdir -p /pgms/oradata/PPRD2/audit
      mkdir -p /pgms/oradata/PPRD2/bdump
      mkdir -p /pgms/oradata/PPRD2/cdump
      mkdir -p /pgms/oradata/PPRD2/udump
      exit

Create commands to copy the primary database datafiles to the standby directories (use these commands in 3.2.2.2) (or just get a list of the datafiles if you are going to copy or transfer them manually) [3.2.1].

   set pagesize 0 recsep off linesize 160 trimspool on feedback off
   spool cp_standby.shl
   select 'cp -p ' || name || ' ' || replace(name,'/PPRD/','/PPRD2/') from v$datafile
      order by substr(name,instr(name,'/',-1));
   spool off
   !cat cp_standby.shl | sed '/^[^c][^p]/d' >cp_standbyx.shl; mv cp_standbyx.shl cp_standby.shl

Create the standby init.ora parameter file from the primary parameter file [3.2.4].

   If you are using an spfile:
      create pfile='$ORACLE_HOME/dbs/initPPRD2.ora' from spfile;
   Else, if you are using a pfile:
      !cp -p $ORACLE_HOME/dbs/initPPRD.ora $ORACLE_HOME/dbs/initPPRD2.ora

Modify the init.ora file for the standby database ($ORACLE_HOME/dbs/initPPRD2.ora), leaving all of the parameters the same as the primary database, including db_name (PPRD), compatible, log_archive_format, and log_archive_start (true), except for making the following changes and additions [3.2.6, 11.3] (note: if the pfile was created from an spfile, it will contain "*." at the beginning of the parameter names, which means the default SID). (Note: In order to get to the Maximum Availability protection mode (instead of staying at the default Maximum Performance protection mode) requires specifying "LGWR SYNC" in the log_archive_dest_2 parameter [5.7].)

   control_files = (/orcl/oradata/PPRD2/ctrl_PPRD_01.ctl,
      /pgms/oradata/PPRD2/ctrl_PPRD_02.ctl)  # [3.2.6]
   background_dump_dest = /pgms/oradata/PPRD2/bdump  # [3.2.6]
   core_dump_dest = /pgms/oradata/PPRD2/cdump  # [3.2.6]
   user_dump_dest = /pgms/oradata/PPRD2/udump  # [3.2.6]
   audit_file_dest = /pgms/oradata/PPRD2/audit  # [3.2.6]
   #log_archive_dest = /orcl/oradata/PPRD2/archivelogs
   log_archive_dest_1 = 'LOCATION=/orcl/oradata/PPRD2/archivelogs MANDATORY'  # [3.1.2,3.2.6,5.4.2,5.8.2.2,12]; for switchover
   log_archive_dest_state_1 = ENABLE  # [5.8.2.2]; for switchover
   log_archive_dest_2 = 'SERVICE=myserver_pprd LGWR SYNC'  # [5.7,5.8.2.2,12]; for switchover
   log_archive_dest_state_2 = ENABLE  # [5.8.2.2]; for switchover
   standby_archive_dest = /orcl/oradata/PPRD2/archivelogs  # [3.2.6,5.4.2,5.8.2.2]
   standby_file_management = AUTO  # [3.2.6,5.8.2.2]; or MANUAL for raw devices [8.4.1.2]
   remote_archive_enable = TRUE  # [3.2.6,5.3.2.1,5.8.2.2]; TRUE or RECEIVE, but must change RECEIVE to SEND on switchover
   instance_name = PPRD2  # [3.2.6]
   lock_name_space = PPRD2  # [3.2.6]; use when primary and standby on same system; same as instance_name
   fal_server = myserver_pprd  # [5.8.2.2,6.4.4]
   fal_client = myserver_pprd2  # [5.8.2.2,6.4.4]
   db_file_name_convert = ('/PPRD/','/PPRD2/')  # [3.2.6,5.8.2.2]
   log_file_name_convert = ('/PPRD/','/PPRD2/')  # [3.2.6,5.8.2.2]
   # log_archive_trace = 15  # [5.8.2.4.5,6.7.3]; to see progression of archiving of redo logs to the standby site
   audit_trail = false  # do not have auditing turned on in a standby database - can't audit to read-only database!

Shut down the primary database [3.2.2.1] (using shutdown normal, or, if that hangs, do shutdown immediate, startup, shutdown normal; see note at end if you want your standby to have its database name to be the same as its instance name or some other value, instead of being the same as the primary database name).

   shutdown normal

Copy the primary database datafiles to the standby directory if local (see 3.2.1) or to a temporary staging directory if remote or on a separate local server (which will be transferred to the standby site below; log files and control files for the primary will not be copied to the standby site) [3.2.2.2].

   !sh cp_standby.shl

Create the standby control file from the primary database (copying it to the standby directory if local, or to the temporary staging directory if remote or on a separate local server) [3.2.3].

   startup
   select replace(value,'/PPRD/','/PPRD2/') from v$parameter where name = 'control_files';
   alter database create standby controlfile as '/orcl/oradata/PPRD2/ctrl_PPRD_01.ctl';
   exit

Then, copy the resulting control file to the other standby control file(s) listed in the standby's parameter file (initPPRD2.ora), if any:

   cp -p /orcl/oradata/PPRD2/ctrl_PPRD_01.ctl /pgms/oradata/PPRD2/ctrl_PPRD_02.ctl

Transfer the primary database datafiles and the standby control file from the temporary staging directory to the standby site if on a remote system or on a separate local server (else already done by 3.2.2.2) [3.2.5].

Transfer the standby init.ora file from the $ORACLE_HOME/dbs directory to the standby site if on a remote system or on a separate local server (else already done by 3.2.4) [3.2.5].

Reload the primary database listener with the modified listener.ora file on the primary site [3.2.8].

   lsnrctl reload

Reload the standby database listener with the modified listener.ora file on the standby site if on a remote system or on a separate local server (else already done above) [3.2.8].

   lsnrctl reload

Create the standby's password file, if needed.

   rm $ORACLE_HOME/dbs/orapwPPRD2
   orapwd file=$ORACLE_HOME/dbs/orapwPPRD2 password=<sys password> entries=5

Connect as sysdba on the standby database, bring up in nomount mode, create the spfile if needed, mount the standby database, and change to managed recovery mode (note: "alter database force logging" already set from primary copy) [3.2.11-3.2.13, 6.2.1, 6.2.2.1, 8.1.1, 8.2.3.1].

   . oraenv
      PPRD2
   sqlplus "/ as sysdba"
   create spfile from pfile;
   startup nomount
   alter database mount standby database;
   alter database recover managed standby database disconnect from session;
   exit

If you are using a pfile instead of an spfile for the primary database, modify the init.ora file (initPPRD.ora) for the primary database (or, for spfile, the mods will be done in the next step).

   #log_archive_dest = /orcl/oradata/PPRD/archivelogs
   log_archive_dest_1 = 'LOCATION=/orcl/oradata/PPRD/archivelogs MANDATORY'  # [3.1.2,5.8.2.1,12]
   log_archive_dest_state_1 = ENABLE  # [5.8.2.1]
   log_archive_dest_2 = 'SERVICE=myserver_pprd2 LGWR SYNC'  # [3.2.14,5.4.1,5.7,5.8.2.1,12]
   log_archive_dest_state_2 = ENABLE  # [3.2.14,5.4.1,5.8.2.1]
   standby_archive_dest = /orcl/oradata/PPRD/archivelogs  # [5.8.2.1]; for switchover
   standby_file_management = AUTO  # [5.8.2.1]; for switchover; or MANUAL for raw devices [8.4.1.2]
   remote_archive_enable = TRUE  # [5.3.2.1,5.8.2.1]; TRUE or SEND, but must change SEND to RECEIVE on switchover
   instance_name = PPRD  # [3.2.6]
   lock_name_space = PPRD  # [3.2.6]; use when primary and standby on same system; same as instance_name
   fal_server = myserver_pprd2  # [5.8.2.1,6.4.4]; for switchover
   fal_client = myserver_pprd  # [5.8.2.1,6.4.4]; for switchover
   db_file_name_convert = ('/PPRD2/','/PPRD/')  # [5.8.2.1]; for switchover
   log_file_name_convert = ('/PPRD2/','/PPRD/')  # [5.8.2.1]; for switchover
   # log_archive_trace = 15  # [5.8.2.4.5,6.7.3]; to see progression of archiving of redo logs to the standby site

Start up the primary database with the modified parameters [3.2.2.3].

   . oraenv
      PPRD
   If you are using an spfile (the first alter system command removes log_archive_dest
   from the spfile; for a description of the others, see the pfile initPPRD.ora above):
      sqlplus "/ as sysdba"
      shutdown normal
      startup nomount
      alter system reset log_archive_dest scope=spfile sid='*';
      alter system set log_archive_dest_1 = 'LOCATION=/orcl/oradata/PPRD/archivelogs MANDATORY' scope=spfile;
      alter system set log_archive_dest_state_1 = ENABLE scope=spfile;
      alter system set log_archive_dest_2 = 'SERVICE=myserver_pprd2 LGWR SYNC' scope=spfile;
      alter system set log_archive_dest_state_2 = ENABLE scope=spfile;
      alter system set standby_archive_dest = '/orcl/oradata/PPRD/archivelogs' scope=spfile;
      alter system set standby_file_management = AUTO scope=spfile;
      alter system set remote_archive_enable = TRUE scope=spfile;
      alter system set instance_name = PPRD scope=spfile;
      alter system set lock_name_space = PPRD scope=spfile;
      alter system set fal_server = myserver_pprd2 scope=spfile;
      alter system set fal_client = myserver_pprd scope=spfile;
      alter system set db_file_name_convert = '/PPRD2/','/PPRD/' scope=spfile;
      alter system set log_file_name_convert = '/PPRD2/','/PPRD/' scope=spfile;
      # alter system set log_archive_trace = 15 scope=spfile;
      shutdown
      startup
   Else, if you are using a pfile:
      sqlplus "/ as sysdba"
      shutdown normal
      startup

Start archiving to the standby database by issuing a log switch on the primary database [3.2.14].

   alter system switch logfile;

Create standby logfile groups on the standby database, starting with the next group number and adding one more group than the current number of log groups on the primary database [5.3.3.3]. Standby redo logs are an exact replica of the primary database�s online redo logs (instead of waiting for a complete archive log) and are required for maximum availability protection mode and when using LGWR process with maximum performance mode [5.7].

   On the primary database:
      column o1 noprint
      column o2 noprint
      column maxgroup new_value maxgroup
      select max(group#) maxgroup from v$logfile;
      select group# o1,1 o2,'alter database add standby logfile group ' ||
            to_char(group#+&maxgroup) || ' ('
            from v$log
         union all
         select group#,2,'   ''' || replace(replace(member,group#,group#+&maxgroup),
            'PPRD/','PPRD2/stby_') || ''','
            from v$logfile lf1 where member not in (select max(member) from v$logfile lf2
            where lf1.group# = lf2.group#)
         union all
         select l1.group#,3,'   ''' || replace(replace(member,l1.group#,l1.group#+&maxgroup),
            'PPRD/','PPRD2/stby_') || ''') size ' || bytes / 1024 || 'K;'
            from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
            and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
         union all
         select group#+1,1,'alter database add standby logfile group ' ||
            to_char(group#+1+&maxgroup) || ' (' from v$log
            where group# = &maxgroup
         union all
         select group#+1,2,'   ''' || replace(replace(member,group#,group#+1+&maxgroup),
            'PPRD/','PPRD2/stby_') || ''',' from v$logfile lf1 where group# = &maxgroup
            and member not in (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
         union all
         select l1.group#+1,3,'   ''' || replace(replace(member,l1.group#,l1.group#+1+&maxgroup),
            'PPRD/','PPRD2/stby_') || ''') size ' || bytes / 1024 || 'K;'
            from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
            and l1.group# = &maxgroup
            and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
         order by 1,2,3;
      exit
   On the standby database, run the resulting sql from the above. (Note: This can't be done
   until after archiving has been started by issuing "alter system switch logfile;" on the
   primary.):
      . oraenv
         PPRD2
      sqlplus "/ as sysdba"
      alter database recover managed standby database cancel;
      alter database open read only;
      alter database add standby logfile group 4 (
         '/orcl/oradata/PPRD2/stby_log_PPRD_4A.rdo',
         '/orcl/oradata/PPRD2/stby_log_PPRD_4B.rdo') size 4096K;
      alter database add standby logfile group 5 (
         '/orcl/oradata/PPRD2/stby_log_PPRD_5A.rdo',
         '/orcl/oradata/PPRD2/stby_log_PPRD_5B.rdo') size 4096K;
      alter database add standby logfile group 6 (
         '/orcl/oradata/PPRD2/stby_log_PPRD_6A.rdo',
         '/orcl/oradata/PPRD2/stby_log_PPRD_6B.rdo') size 4096K;
      alter database add standby logfile group 7 (
         '/orcl/oradata/PPRD2/stby_log_PPRD_7A.rdo',
         '/orcl/oradata/PPRD2/stby_log_PPRD_7B.rdo') size 4096K;
      column member format a55
      select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl
         where vs.group# = vl.group# order by vs.group#,vl.member;

Then, add a tempfile to the standby database for switchover or read-only access. (Note: This can't be done until after archiving has been started by issuing "alter system switch logfile;" on the primary.)

   alter tablespace temp add tempfile '/data/oradata/PPRD2/temp_PPRD_01.dbf'
      size 400064K reuse;
   alter database recover managed standby database disconnect from session;
   select * from v$tempfile;
   exit

Create standby logfile groups on the primary database for switchovers, adding one more group than the current number of log groups on the primary database [5.3.3.3].

   . oraenv
      PPRD
   sqlplus "/ as sysdba"
   column o1 noprint
   column o2 noprint
   column maxgroup new_value maxgroup
   select max(group#) maxgroup from v$logfile;
   select group# o1,1 o2,'alter database add standby logfile group ' ||
         to_char(group#+&maxgroup) || ' ('
         from v$log
      union all
      select group#,2,'   ''' || replace(replace(member,group#,group#+&maxgroup),
         'PPRD/','PPRD/stby_') || ''','
         from v$logfile lf1 where member not in (select max(member) from v$logfile lf2
         where lf1.group# = lf2.group#)
      union all
      select l1.group#,3,'   ''' || replace(replace(member,l1.group#,l1.group#+&maxgroup),
         'PPRD/','PPRD/stby_') || ''') size ' || bytes / 1024 || 'K;'
         from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
         and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
      union all
      select group#+1,1,'alter database add standby logfile group ' ||
         to_char(group#+1+&maxgroup) || ' (' from v$log
         where group# = &maxgroup
      union all
      select group#+1,2,'   ''' || replace(replace(member,group#,group#+1+&maxgroup),
         'PPRD/','PPRD/stby_') || ''',' from v$logfile lf1 where group# = &maxgroup
         and member not in (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
      union all
      select l1.group#+1,3,'   ''' || replace(replace(member,l1.group#,l1.group#+1+&maxgroup),
         'PPRD/','PPRD/stby_') || ''') size ' || bytes / 1024 || 'K;'
         from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
         and l1.group# = &maxgroup
         and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
      order by 1,2,3;
   Run the resulting sql from the above, for example:
   alter database add standby logfile group 4 (
      '/orcl/oradata/PPRD/stby_log_PPRD_4A.rdo',
      '/orcl/oradata/PPRD/stby_log_PPRD_4B.rdo') size 4096K;
   alter database add standby logfile group 5 (
      '/orcl/oradata/PPRD/stby_log_PPRD_5A.rdo',
      '/orcl/oradata/PPRD/stby_log_PPRD_5B.rdo') size 4096K;
   alter database add standby logfile group 6 (
      '/orcl/oradata/PPRD/stby_log_PPRD_6A.rdo',
      '/orcl/oradata/PPRD/stby_log_PPRD_6B.rdo') size 4096K;
   alter database add standby logfile group 7 (
      '/orcl/oradata/PPRD/stby_log_PPRD_7A.rdo',
      '/orcl/oradata/PPRD/stby_log_PPRD_7B.rdo') size 4096K;
   column member format a55
   select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl
      where vs.group# = vl.group# order by vs.group#,vl.member;

Issue some log switches on the primary database, and confirm that the log files were received on the standby archive destination and processed by the standby database. (Note: If the archiver process is being used to write to the standby database when in Maximum Performance mode (the initial default), you may need to issue as many "alter system switch logfile;" commands as there are redo log groups before you see them processed in the alert log.)

   alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
   select sysdate from dual;
   alter system switch logfile;
   !ls -ltr /orcl/oradata/PPRD2/archivelogs
     You should see the archive logs that were received by the standby.
   !tail /pgms/oradata/PPRD2/bdump/alert_PPRD2.log
     You should see a message in the standby alert log such as:
       Media Recovery Log /orcl/oradata/PPRD2/archivelogs/arch_PPRD_0000002866.arc
   On the standby (8.5.3.1):
     select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

Another way to check to see that the archived redo logs are going to the standby:

   On the standby, see what logs are already there:
      alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
      select sequence#, first_time, next_time, archived, applied
         from v$archived_log order by sequence#;
   On the primary, force a log switch:
      alter system archive log current;
   On the standby, see the new logs and if they have been applied (may need to wait
   a few seconds before they are applied):
      select sequence#, first_time, next_time, archived, applied
         from v$archived_log order by sequence#;
   On the primary, you can also see the new logs and if the standby has acknowledged
   that they've been applied (again, may need to wait a few seconds before they
   are acknowledged):
      column name format a15
      select name,sequence#, first_time, next_time, archived, applied
         from v$archived_log where name not like '%/%' order by sequence#;

Switch to the desired "maximum availability" protection mode on the primary database. (The default is "maximum performance"; the redo log transmission (in log_archive_dest_2) must have been set to use the log writer process in synchronous mode (LGWR SYNC) before changing the protection mode to maximum availability; if standby database becomes unavailable, maximum availability mode is temporarily switched to maximum performance mode.) [1.4, 5.7, 13.14].

   . oraenv
      PPRD
   sqlplus "/ as sysdba"
   select value from v$parameter where name = 'log_archive_dest_2';
   shutdown normal
      (If that hangs, then do: shutdown immediate, startup, shutdown normal)
   startup mount
   alter database set standby database to maximize availability;
   alter database open;
   select protection_mode from v$database;

Note: If you don't have logging to the standby site set to "LGWR SYNC" (in the log_archive_dest_2 parameter in the pfile or spfile) when you try to change the protection mode to maximum availability, you will get:

   ORA-03113: end-of-file on communication channel

In that case, you will have to switch the protection mode back to get the primary to open in order to remedy the problem:

   exit
   sqlplus "/ as sysdba"
   startup mount
   alter database set standby database to maximize performance;
   alter database open;

Try some edits on the primary, archive the current log with the edits, open the standby as read-only, and check to see that the changes made it to the standby.

   On the primary:
      update spriden set spriden_first_name = 'James'
         where spriden_pidm = 1234 and spriden_change_ind is null;
      commit;
      alter system switch logfile;
   On the standby (may take a few seconds for the change to be applied):
      select name,instance_name,database_role,status,open_mode from v$instance,v$database;
      alter database recover managed standby database cancel;
      alter database open read only;
      select name,instance_name,database_role,status,open_mode from v$instance,v$database;
      set pagesize 60
      select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;
      alter database recover managed standby database disconnect from session;
      select name,instance_name,database_role,status,open_mode from v$instance,v$database;
   On the primary:
      update spriden set spriden_first_name = 'Stephen' where spriden_pidm = 1234
         and spriden_change_ind is null;
      commit;
      alter system switch logfile;
   On the standby:
      alter database recover managed standby database cancel;
      alter database open read only;
      set pagesize 60
      select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;
      alter database recover managed standby database disconnect from session;

A standby database can be used to run reports on and do other intensive queries on if you need to offload that workload from your primary database. Like the above edit tests, you will switch the standby database to read-only mode, then run your reports or queries on the standby, then switch the standby back to managed recovery mode. Changes in the primary database during that time are sent to the standby's archive log files, but aren't applied to the standby database until the mode is switched back to managed recovery mode. To run an SQL report (such as myreport.sql) on the standby database (the connect's are for when you need to run the report as a specific user, such as myuserid):

   sqlplus "/ as sysdba"
   alter database recover managed standby database cancel;
   alter database open read only;
   connect myuserid/mypassword
   @myreport.sql
   connect / as sysdba
   alter database recover managed standby database disconnect from session;

If the primary database crashes while the standby is in read-only mode, you can still switch the standby back to managed recovery mode to let the queued-up changes be applied to the standby. After switching back to managed recovery mode, you can keep checking to see what queued-up changes have yet to be applied (which will return no rows once they have all been applied, which may take several minutes if there are lots of changes queued up):

   alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
   select sequence#, first_change#, next_change#, first_time, next_time
      from v$archived_log where applied = 'NO' order by sequence#;

Note: If you try to update data in a standby database open for read-only, you will get:

  ORA-01552: cannot use system rollback segment for non-system tablespace

The primary database can be shut down and started up as usual without any detrimental effects on the standby. However, you might want to archive the current redo log file so that the latest changes are sent to the standby database before shutting the primary database down (such as for backup).

   alter system switch logfile;

NOTE: To avoid creating archive gaps [B.3.1.2] (however, archive gaps should be resolved by the RFS process anyway, so they shouldn't be a problem; be aware that trying to start up a primary database while archiving to the standby is set to "defer" causes an "ORA-03113: end-of-file on communication channel" error):

   Start the standby databases and listeners before starting the primary database.
   Shut down the primary database (or defer sending transactions to the standby)
   before shutting down the standby database.

To shut down a standby database [8.1.2]:

   If standby is currently in read-only access, terminate any active user sessions (run
   the resulting SQL and commands generated below) and switch back to managed recovery:
      select open_mode from v$database;
      select 'alter system kill session ''' || sid || ',' || serial# || ''';'
         from v$session where username is not null
         and sid not in (select distinct sid from v$mystat);
      select '!kill -9 ' || vp.spid
         from v$session vs,v$process vp where vs.paddr = vp.addr
         and vs.username is not null
         and vs.sid not in (select distinct sid from v$mystat);
      alter database recover managed standby database disconnect from session;
   Then, cancel managed recovery (but see Note below):
      select * from v$standby_log;
         Should show all standby logs with a status of UNASSIGNED if archiving is
         deferred (otherwise, the primary database is still sending transactions
         to the standby - but this isn't always the case).
      alter database recover managed standby database cancel;
      shutdown immediate
   Note: Before canceling managed recovery on the standby, it is suggested that you defer
   archiving to the standby by doing the following on the primary.  However, trying to
   start up a primary database while archiving to the standby is set to "defer" causes an
   "ORA-03113: end-of-file on communication channel" error (see further down below for
   how to start up the primary if you encounter this problem).  So, until this startup
   problem is addressed and resolved by Oracle, don't defer archiving.
      alter system set log_archive_dest_state_2 = DEFER;
      alter system switch logfile;

To start up a standby database in the usual managed recovery mode [8.1.1]:

   On the standby (note: on startup, you will get the message: "ORA-01666: controlfile
   is for a standby database"):
      startup nomount
      alter database mount standby database;
      alter database recover managed standby database disconnect from session;
   On the primary, re-enable archiving to the standby database, if needed:
      alter system set log_archive_dest_state_2 = ENABLE;
      alter system switch logfile;

To start up a standby database for read-only access when it is currently down [8.2.2]:

   startup nomount
   alter database mount standby database;
   alter database open read only;

To switch a standby database from managed recovery mode to read-only access (note: be sure to switch back to managed recovery mode after you have finished running whatever queries you were doing in read-only mode) [8.2.2]:

   alter database recover managed standby database cancel;
   alter database open read only;

To switch a standby database from read-only access to managed recovery mode [8.2.2]:

   Terminate any active user sessions on the standby database (except your own):
      select 'alter system kill session ''' || sid || ',' || serial# || ''';'
         from v$session where username is not null
         and sid not in (select distinct sid from v$mystat);
      select '!kill -9 ' || vp.spid
         from v$session vs,v$process vp where vs.paddr = vp.addr
         and vs.username is not null
         and vs.sid not in (select distinct sid from v$mystat);
   alter database recover managed standby database disconnect from session;

To see if a standby database is in managed recovery or read-only mode, or if managed recovery has been cancelled, or if this database is a primary database instead:

   select decode(database_role,'PRIMARY','PRIMARY',decode(open_mode,
      'MOUNTED',decode((select count(*) from v$managed_standby
         where process like 'MRP%'),0,'CANCELLED','MANAGED RECOVERY'),
      'READ ONLY','READ ONLY','UNKNOWN')) from v$database;

To initiate a switchover in which the primary database becomes the standby database and the standby database becomes the primary database (this is not for when the primary database has crashed - see failover below for that; you probably will want have two telnet sessions going to do this switchover - one pointing to the primary and one pointing to the standby):

   End all activities on the primary and standby database [7.1.2.1], probably just
   doing on the primary database (PPRD):
      select 'alter system kill session ''' || sid || ',' || serial# || ''';'
         from v$session where username is not null
         and sid not in (select distinct sid from v$mystat);
      select '!kill -9 ' || vp.spid
         from v$session vs,v$process vp where vs.paddr = vp.addr
         and vs.username is not null
         and vs.sid not in (select distinct sid from v$mystat);
   Check primary database (PPRD) switchover status (on primary; looking for
   "TO STANDBY"; but mine said "SESSIONS ACTIVE" (my sysdba session)) [7.2.1.1]:
      select database_role,switchover_status from v$database;
   Initiate switchover operation on the primary database (PPRD) [7.2.1.2]:
      alter database commit to switchover to physical standby;
   Shut down and restart the former primary instance (PPRD) as the new standby [7.2.1.3]:
      shutdown immediate
      if remote_archive_enable is set to SEND in the primary's init.ora file
      (initPPRD.ora), change it to RECEIVE:
         If you are using an spfile (do this after the startup below):
            alter system set remote_archive_enable = RECEIVE scope = both;
         Else, if you are using a pfile:
            remote_archive_enable = RECEIVE
      if audit_trail is set to anything but FALSE in the primary's init.ora file
      (initPPRD.ora), change it to FALSE:
         If you are using an spfile (do this after the startup below):
            alter system set audit_trail = FALSE scope = both;
         Else, if you are using a pfile:
            audit_trail = FALSE
      if log_archive_dest_state_2 is set to ENABLE in the primary's init.ora file
      (initPPRD.ora), change it to DEFER (on pre-10g databases; added 8/23/07):
         If you are using an spfile (do this after the startup below):
            alter system set log_archive_dest_state_2 = DEFER scope = both;
         Else, if you are using a pfile:
            log_archive_dest_state_2 = DEFER
      startup nomount
      alter database mount standby database;
   Check standby database (PPRD2) switchover status (on standby; looking for
   "SWITCHOVER PENDING"; but mine said "TO PRIMARY") [7.2.1.4]:
      select database_role,switchover_status from v$database;
   Change the former standby instance (PPRD2) to the primary role, shut down, and
   restart [7.2.1.5-6]:
      alter database commit to switchover to primary;
      shutdown normal
      if remote_archive_enable is set to RECEIVE in the standby's init.ora file
      (initPPRD2.ora), change it to SEND:
         If you are using an spfile (do this after the startup below):
            alter system set remote_archive_enable = SEND scope = both;
         Else, if you are using a pfile:
            remote_archive_enable = SEND
      if audit_trail needs to be turned on for this "new" primary, change it
      to TRUE (or whatever setting is needed) in the standby's init.ora file
      (initPPRD2.ora):
         If you are using an spfile (do this after the startup below):
            alter system set audit_trail = TRUE scope = both;
         Else, if you are using a pfile:
            audit_trail = TRUE
      if log_archive_dest_state_2 is set to DEFER in the standby's init.ora file
      (initPPRD2.ora), change it to ENABLE (on pre-10g databases; added 8/23/07):
         If you are using an spfile (do this after the startup below):
            alter system set log_archive_dest_state_2 = ENABLE scope = both;
         Else, if you are using a pfile:
            log_archive_dest_state_2 = ENABLE
      startup
      select name,instance_name,database_role,status,open_mode from v$instance,v$database;
   Start managed recovery on the new standby (on old primary) (PPRD) [7.2.1.7]:
      alter database recover managed standby database disconnect from session;
      select name,instance_name,database_role,status,open_mode from v$instance,v$database;
   Write the current archive log on the new primary (on old standby) (PPRD2) [7.2.1.8]:
      alter system archive log current;
   Change tnsnames.ora entry on all application hosts (T:\APPS\ban6\orawin\NET80\ADMIN)
   to point to the new primary (PPRD2) (or, just tell the users to use myserver_pprd2 as
   the database to log into - make sure myserver_pprd2 is defined in the tnsnames.ora
   file on the network; but, see "Multiple tnsnames addresses" for another option) [10.1.1]:
      myserver_pprd = 
         (DESCRIPTION = 
         (ADDRESS_LIST = 
             (ADDRESS = 
               (PROTOCOL = TCP)
               (Host = 234.56.78.90) -- whatever host IP has PPRD2
               (Port = 1521)
             )
         )
         (CONNECT_DATA = (SID = PPRD2) -- the new primary (old standby)
         )
       )
      myserver_pprd2 = 
         (DESCRIPTION = 
         (ADDRESS_LIST = 
             (ADDRESS = 
               (PROTOCOL = TCP)
               (Host = 123.45.67.89) -- whatever host IP has PPRD
               (Port = 1521)
             )
         )
         (CONNECT_DATA = (SID = PPRD) -- the new standby (old primary)
         )
       )

To initiate a failover in which the standby database becomes the primary database because the primary database has crashed (primary becomes useless and must be rebuilt as a new standby, then do a switchover back to it when things are back to normal; there is a remote possibility for data loss on a failover), with all of these commands being run on the standby (i.e., PPRD2):

   If running in maximum protection mode [7.1.3.1]:
      select protection_mode from v$database;
      alter database set standby database to maximize performance;
   See if any archived redo log gaps exist [7.2.2.1.1]:
      select thread#,low_sequence#,high_sequence# from v$archive_gap;
   If any gaps exist:
      Copy or transfer all missing archive logs from the primary system to the
      standby system; then, for each (substituting filename):
         alter database register physical logfile 'filename';
   See if other missing archived redo logs exist (this actually refers to looking
   at other standby sites, but, this can also be done on a single standby if you
   could get the archive logs off the failed primary site) [7.2.2.1.2]:
      select unique thread# as thread, max(sequence#) over (partition by thread#)
         as last from v$archived_log;
      !ls -ltr /orcl/oradata/PPRD/archivelogs | tail
   If missing sequences are found:
      Copy or transfer all missing archive logs from the other standby systems
      to the first standby system; then, for each (substituting filename):
         alter database register physical logfile 'filename';
   Repeat steps 7.2.2.1.1 and 7.2.2.1.2 until no gaps remain [7.2.2.1.3].
   Initiate failover operation on the standby database [7.2.2.1.4].  If the standby
   database has standby redo logs and you have not manually registered any partial
   archived redo logs, issue the following statement:
      alter database recover managed standby database finish;
   Otherwise, issue the following statement:
      alter database recover managed standby database finish skip standby logfiles;
   Then, convert the standby database to the primary role [7.2.2.1.5]:
      alter database commit to switchover to primary;
      shutdown immediate
         (You might want to make a backup of this new primary database now, just in
         case you have to recover it before the primary site is fixed and back up and
         running.)
      if audit_trail needs to be turned on for this "new" primary, change it
      to TRUE (or whatever setting is needed) in the standby's init.ora file
      (initPPRD2.ora):
         If you are using an spfile (do this after the startup below):
            alter system set audit_trail = TRUE scope = both;
         Else, if you are using a pfile:
            audit_trail = TRUE
      startup
      select name,instance_name,database_role,status,open_mode from v$instance,v$database;
   Change tnsnames.ora entry on all application hosts (T:\APPS\ban6\orawin\NET80\ADMIN)
   to point to the new primary (PPRD2) (or, just tell the users to use myserver_pprd2 as
   the database to log into - make sure myserver_pprd2 is defined in the tnsnames.ora
   file on the network; but, see "Multiple tnsnames addresses" for another option) [10.1.1]:
      myserver_pprd = 
         (DESCRIPTION = 
         (ADDRESS_LIST = 
             (ADDRESS = 
               (PROTOCOL = TCP)
               (Host = 234.56.78.90) -- whatever host IP has PPRD2
               (Port = 1521)
             )
         )
         (CONNECT_DATA = (SID = PPRD2) -- the new primary (old standby)
         )
       )
   When the hardware or other problem is corrected, create a new "standby" database
   on the primary site (as shown above), then do a switchover (also as shown above),
   resulting in the primary database being back on the primary site, and the standby
   database being on the standby site.

To see Data Guard messages on the primary or standby [6.5.5]:

   alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
   set recsep off
   column message format a59
   select timestamp,message from v$dataguard_status;

If you start up your primary database and get:
ORA-03113: end-of-file on communication channel
after the "Database mounted." message, check at the end of the alert.log file (such as alert_PPRD.log) in the primary database's bdump directory. If you see:
ORA-16072: a minimum of one standby database destination is required
that probably means that archiving to the standby database was set to DEFER when you tried to start up the primary database. To start up your primary, re-enable archiving to the standby, mount and open the primary, and switch back to defer archiving:

   connect / as sysdba
   startup nomount
   column name format a25
   column value format a52
   select name,value from v$parameter where name like 'log_archive_dest_%';
   alter system set log_archive_dest_state_2=ENABLE scope=both;
   alter database mount;
   alter database open;
   alter system set log_archive_dest_state_2=DEFER scope=both;

If the entire standby site is down, and the above fails, you may have to switch the protection mode back to "maximum performance" to get the primary to open until the standby site is available again:

   connect / as sysdba
   startup mount
   alter database set standby database to maximize performance;
   alter database open;

Then, when the standby site is up again, shut down the primary database and switch the protection mode back to "maximum availability":

   sqlplus "/ as sysdba"
   shutdown immediate
   startup mount
   alter database set standby database to maximize availability;
   alter database open;

If you add a datafile to the primary, you may also have to add it to the standby. If standby_file_management is set to AUTO in the init.ora files on the standby site (and on the primary site for switchover), an "add datafile" is automatically propagated to the standby database on the next logfile switch. Otherwise, to add the matching datafile on the standby (note that no size information is needed):

   alter database recover managed standby database cancel;
   alter database create datafile '/data/oradata/PPRD2/devl_PPRD_02.dbf';
   alter database recover managed standby database disconnect from session;

Scripts:

Below are scripts that will start up and shut down a Data Guard primary or physical standby database. They test to see which type of database they are running on, and what state it is in, and take the appropriate action to start it up or to shut it down. They must be run when logged in as sysdba (sqlplus "/ as sysdba"). They can also be run against non Data Guard databases.

Also included is a dataguard_state function, which, optionally, enables or defers archiving to the standby database from the primary database, and returns the resulting archiving state. Both the start up and shut down scripts run it on the primary database when this option is turned on; however, if a primary database running with maximum availability or maximum performance mode is shut down while archiving has been deferred, starting it up will fail with an "ORA-16072: a minimum of one standby database destination is required" error, so, for now, deferring and enabling archiving to the standby is turned off in the two scripts. A limited user, named dgstate, is created for security reasons to run it so that you don't have to connect to system to run it (thus the system password isn't required in the shut down and start up scripts), and so that you don't need to grant alter system privileges to dgstate. To load dataguard_state.sql and to create the dgstate user (be sure to change dgstatepw to some other password here and in the Data Guard shutdown and startup scripts first):

   sqlplus "/ as sysdba"
   grant select on v_$parameter to system;
   connect system
   @dataguard_state.sql
   create user dgstate identified by dgstatepw
       default tablespace users temporary tablespace temp
       quota 0 on users quota 0 on temp;
   grant create session to dgstate;
   grant alter system to system;
   grant execute on system.dataguard_state to dgstate;

The dgstartup.sql script starts up a Data Guard primary or physical standby database. If this is a primary database, it just has to be mounted and opened. If this is a physical standby database, the startup command will fail with "ORA-01666: controlfile is for a standby database" when the mount is attempted. So, try mounting the database again, this time as a standby database, and enable managed recovery, then, enable the archiving by the primary database to this standby database (optional).

The dgshutdown.sql script shuts down a Data Guard primary or physical standby database. If this is a primary database, just do a log file switch before shutting it down. Otherwise, if this is a standby database, terminate any active sessions on the standby and switch to managed recovery mode if currently in read-only access, then, defer the archiving by the primary database to this standby database (optional), and cancel managed recovery.

You'll probably want to edit the dbstart and dbshut scripts (in $ORACLE_HOME/bin) to run these Data Guard startup and shutdown scripts, replacing the "startup" commands with "@/home/oracle/dgstartup.sql", and the "shutdown" commands with "@/home/oracle/dgshutdown.sql" (assuming they are put in the /home/oracle directory). Just don't forget to do @dgstartup.sql and @dgshutdown.sql instead of the startup and shutdown commands when you want to start up or shut down a Data Guard primary or physical standby database.

Be aware that the functionality in these two scripts can't be incorporated into PL/SQL database procedures (such as database startup and shutdown triggers) because of all of the restrictions on what can be run and accessed in PL/SQL from a database that is not in the open state.

References and Notes:

Oracle Data Guard Concepts and Administration Release 2 (9.2)
   http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653.pdf

Implementing Oracle9i Data Guard for Higher Availability; by Daniel T. Liu (note the tips, especially RMAN backup, disabling log transport services when the standby is down)
   http://www.dbazine.com/oracle/or-articles/liu4

Oracle9i Data Guard Configuration Example - (Physical, Maximum Performance Mode); by Jeff Hunter
   http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard_9i/DG_1.shtml

If your backup scripts look for SID-based file names and directories to back up, you may need to change them to exclude the standby database directories, depending on how you named your standby, i.e., find PPRD, but exclude PPRD2 directories, such as by using:

   grep -v "/[^/]*${BACKUP_SID}[^/].*/"

so that the standby files don't overwrite the primary files in the backup staging directory, such as:

   replace:
      find / -name "*${BACKUP_SID}*" ! -type d 2>/dummy |
         egrep '(\.ora$|\.dbf$|\.ctl$|\.rdo$)' | sort -t/ -k3 >backup_list.lst
   with:
      find / -name "*${BACKUP_SID}*" ! -type d 2>/dummy |
         grep -v "/[^/]*${BACKUP_SID}[^/].*/" |
         egrep '(\.ora$|\.dbf$|\.ctl$|\.rdo$)' | sort -t/ -k3 >backup_list.lst

You may need to make similar changes to other scripts that do SID-based searches.

Multiple tnsnames addresses:

For standby databases off-site (at a different IP address; and where the SID is the same for the primary and standby), if you don't want your users to have to enter a different database name connect string after a switchover or a failover, you can use this trick from Helena Whitaker. Just define two addresses for the connect string in the tnsnames.ora file on the primary site, with the main address listed first and the secondary address listed second. If Oracle can't reach the database at the first address, it will try to reach it at the second address. So, when the user specifies the myserver_pprd connect string for the Banner database, if the primary is down, and you have done a failover or switchover, Oracle will not find that main address (for the original primary), but will find the secondary address (for the original standby, which is now the primary). To do this, your connect string in tnsnames.ora on the primary site will be something like:

      myserver_pprd = 
         (DESCRIPTION = 
         (ADDRESS_LIST = 
             (ADDRESS = 
               (PROTOCOL = TCP)
               (Host = 123.45.67.89) -- whatever host IP has PPRD
               (Port = 1521)
             )
             (ADDRESS = 
               (PROTOCOL = TCP)
               (Host = 234.56.78.90) -- whatever host IP has PPRD2
               (Port = 1521)
             )
         )
         (CONNECT_DATA = (SID = PPRD)
         )
       )

RFS errors:

If you are getting an RFS message "Destination database mount ID mismatch" in the alert log and an RFS trace file generated in the udump directory on each log switch, you can stop this from happening by disabling the LOG_ARCHIVE_DEST_2 on the standby database by putting "log_archive_dest_state_2 = DEFER" in the standby's init.ora file on pre-10g databases, or, preferrably, by putting the "VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)" parameter in the LOG_ARCHIVE_DEST_2 line in the standby and primary (for switchover) init.ora files on 10g databases.

The problem is caused because the standby LOG_ARCHIVE_DEST_2 defaults to valid for all roles and is enabled, so RFS is trying to send log information from the standby to the primary. This doesn't cause a problem applying the changes to the standby, except for generating those messages in the alert log and creating those RFS trace files. The VALID_FOR parameter indicates that this destination only should be use when this database become primary.

Note that if you disable LOG_ARCHIVE_DEST_2 on the standby, you will have to set it back to ENABLE if you do a switchover to have the standby become the primary (and you will have to DISABLE it on the old primary at that time as well to prevent those messages and trace files). Also, if you use the "VALID_FOR" option, you may want to put "VALID_FOR=(ALL_LOGFILES,ALL_ROLES)" in the LOG_ARCHIVE_DEST_1 line for consistency.

Notes on using server parameter files:

The use of a server parameter file (spfile), which is a binary version of the init.ora file (pfile - parameter initialization file), is required with Oracle9i Release 2 when using a Data Guard Broker Configuration (the GUI interface to Data Guard). If you don't need a Data Guard Broker configuration, which I currently don't, you could continue to use a pfile if you wanted to. Oracle will look for an spfile first and use it when starting up the database instead of the init.ora file. When using an spfile, a parameter can be changed using the "alter system set parameter_name=parameter_value scope=both;" command (such as "alter system set log_archive_dest_state_2 = DEFER;") without editing the init.ora file and possibly without having to shut down and start up the database to have the change take effect (however, some parameters require using scope=spfile in the nomount state and bouncing the database to take effect). It is for that reason that I'm switching over to using spfile's. (Note: Put the scope setting at the end of the alter system command, such as if you are including a comment for the parameter.) WARNING: IF YOU MAKE A CHANGE TO A PARAMETER IN THE PRIMARY DATABASE THAT YOU WANT REFLECTED IN THE STANDBY DATABASE, YOU MUST ALSO MAKE THE CHANGE IN THE STANDBY DATABASE, since primary database parameter changes are not automatically propagated to the standby. For more information on spfile's, see:

   So You Want to Use Oracle's SPFILE; by James Koopmann
      http://www.dbasupport.com/oracle/ora9i/spfile.shtml

   Oracle's SPFILE; by Amar Kumar Padhi
      http://www.dbasupport.com/oracle/ora10g/spfile01.shtml

You may need to change your $ORACLE_HOME/bin/dbstart script to check for server parameter files in addition to parameter files:

   1) after:
         PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
      add:
         SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
   2) replace:
         if [ -f $PFILE ] ; then
      with:
         if [ -f $PFILE -o -f $SPFILE ] ; then

If you use the RESETLOGS option on the primary database, you have to re-create the standby database [8.4]. Also see chapter 8 for adding and dropping tablespaces and datafiles and online redo logs (note: standby logs aren't the same as online redo logs), or renaming datafiles, whether standby_file_management is set to MANUAL or AUTO.

The database name in your standby is the same as the database name in your primary, but they differ in their instance name. If you want your database name in your standby to be the same as it's instance name, you can make the following modifications to steps 3.2.2.1, 3.2.2.2, and 3.2.3 above. Thanks to Lee Johnston of the University of West Florida for this tip.

   Change the primary database name the secondary instance name (PPRD2) before shutting it
   down to do the copy (see 3.2.2.1 above):
      shutdown normal
      startup mount
      !nid target=sys/<syspassword> dbname=PPRD2 setname=YES
         answer Y
      shutdown normal
   Copy the primary database to the standby directory (see 3.2.2.2 above):
      !sh cp_standby.shl
   Create the standby control file (see 3.2.3 above):
      startup
      select replace(value,'/PPRD/','/PPRD2/') from v$parameter where name = 'control_files';
      alter database create standby controlfile as '/orcl/oradata/PPRD2/ctrl_PPRD_01.ctl';
   Change the primary database name back to the primary instance name (PPRD):
      shutdown normal
      !nid target=sys/<syspassword> dbname=PPRD setname=YES
         answer Y
      alter database open;
      exit

Switching Primary and Standby for Disaster Planning Tests

Below is a step-by-step description of how we ran a Disaster Planning test to make sure that we could switch to the standby, run a real payroll (yikes!), and switch back to the primary. During this test, no one could access the original primary database (since it was in managed recovery mode), and the only ones that could get into Banner were the ones that knew the standby's connect string (StandbyServer_prod in this case). Since I was the only one with the password to the standby site, I ended up running some batch jobs for the test and FTP'ing the resulting files to our primary site (such as to print checks here and transfer the direct deposit file to the bank). We came across a few glitches, which are listed below, that were fairly easy to fix. Other than these, the payroll test went smoothly (although the Banner screens were slow). The standby payroll run was completed in a day, and we switched modes back to the original primary and original standby without having to recreate either of those databases. The glitches were:

First, the jobsub output went to the $ORACLE_HOME directory instead of the /home/jobsub directory. I haven't figured that one out, yet.
Second, the Banner programs (and our local ones) had to be recompiled on the standby server to use the correct C and COBOL libraries that are installed there.
Third, the IntelleCheck seeds.ini files had to be modified to include a "StandbyServer_prod" section to have IntelleCheck run here and reference the database there.
Fourth, the one of the tablespaces ran out of room, and had to be increased.

Turn off the secondary standby database(s) on all other servers (except for the main standby database):
    OtherServer:
        login oracle
            PROD
        sqlplus "/ as sysdba"
            @dgshutdown.sql
            exit

Shut down the Application Server after the next full backup until we get ready to activate the standby for testing:
    Access the Oracle Enterprise Manager:
        http://<whatever Application Server URL>:1811
            User Name: ias_admin
            Password: <our ias_admin password>
        Click <whatever ias instance name>
        Click Stop All
        Click Yes button to confirm.
    Turn off access to PROD:
        PrimaryServer:
            login oracle
                PROD
            sqlplus "/ as sysdba"
                alter system enable restricted session;
                exit;

Turn off posting check and db status check in oracle's cron (these are our procedures - you may have to turn off other cron jobs):
    PrimaryServer:
        login oracle
            PROD
        crontab -e
            comment out the following lines:
                # 0,5,10,15,20,25,30,35,40,45,50,55 8-17 * * 1-5 /home/oracle/fgractg_check.shl
                # 0,5,10,15,20,25,30,35,40,45,50,55 8-17 * * 1-5 /home/oracle/check_db_status.shl
            :wq
        crontab -l
Turn off posting check in jobsub's cron:
    PrimaryServer:
        login jobsub
            crontab -e
                comment out the following line:
                    # 0,10,20,30,40,50 8-16 * * 1-5 ./monitor.shl
            :wq
        crontab -l

Switch primary/standby modes on StandbyServer_prod and PrimaryServer_prod:

On the primary site, shut down daemon (one of our procedures), job submission, posting and approvals (waiting about 5 minutes to complete) and any other user processes:
    PrimaryServer:
        login oracle
            PROD
        rm /home/jobsub/PROD*
        daemon_stop.shl
        sqlplus "/ as sysdba"
            @kill_user.sql
                Kill the GURJOBS, GENLPRD, INTEGMGR, and COMMON processes (and any other user processes).
                    Note: You may still see 'INACTIVE' sessions in v$session, but, the shutdown below will clear them out.

Initiate switchover operation on the primary database, shut down, and restart (also changing audit_trail to false, since we can't audit while in standby mode; note: there is no "with session shutdown" prior to 10g):
            alter system set audit_trail=FALSE scope=spfile;
            shutdown immediate
            startup
            alter database commit to switchover to physical standby with session shutdown;
            shutdown immediate
            startup nomount
            alter database mount standby database;
            (leave sqlplus running here while we work on the standby site)

Check standby database switchover status (on standby; looking for "SWITCHOVER PENDING"):
    StandbyServer:
        login oracle
            PROD
        sqlplus "/ as sysdba"
            select database_role,switchover_status from v$database;
            select protection_mode from v$database;
        Initiate switchover operation on the standby database, shut down, and restart:
            alter database recover managed standby database disconnect from session;
            alter database commit to switchover to primary;
            shutdown immediate
            startup
            select name,instance_name,database_role,status,open_mode from v$instance,v$database;
            select * from dba_temp_files;
                If that shows no temp files allocated, then:
                    alter tablespace temp add tempfile '/data/oradata/PROD/temp_PROD_01.dbf'
                    size 400064K reuse;
            (leave sqlplus running here while we work on the primary site)

Start managed recovery on the new standby (on old primary):
    PrimaryServer:
        alter database recover managed standby database disconnect from session;
        select name,instance_name,database_role,status,open_mode from v$instance,v$database;
        exit

Write the current archive log on the new primary (on old standby):
    StandbyServer:
        alter system archive log current;
        exit
See if it came through on the new standby (on old primary):
    PrimaryServer:
        ls -ltr /orcl/oradata/PROD/archivelogs | tail

Start up jobsub and sleep/wake on StandbyServer (start_jobsub.shl and start_sleep_wake.shl are our processes - you may have something similar):
    start_jobsub.shl
    cat /home/jobsub/gurjobsPROD.out
        shows connected.
    start_sleep_wake.shl
    ps -ef | egrep '(fgractg|orappl)'
        shows both fgractg and forappl are running.

Turn on access to PROD:
    StandbyServer:
        login oracle
            PROD
        sqlplus "/ as sysdba"
            select logins from v$instance;
            If it shows RESTRICTED:
                alter system disable restricted session;
        exit

Start up the Application Server (leave them running for the payroll test):
    Access the Oracle Enterprise Manager:
        http://<whatever Application Server URL>:1811
            User Name: ias_admin
            Password: <our ias_admin password>
        Click <whatever ias instance name>
        Click Start All

Can now connect to StandbyServer_prod in Banner Webforms INB. To connect to Banner Self Service, use (note: we have a StandbyServer_prod DAD defined which points to the standby):
https://<your URL>/pls/StandbyServer_prod/twbkwbis.P_WWWLogin

Run the disaster planning payroll test (done by payroll personnel).

To FTP a file from the StandbyServer server to PrimaryServer, such as kgrigsby_phpmtim_148991.log:
    StandbyServer:
        cd /home/jobsub
        ftp PrimaryServer.uaex.edu
                jobsub
            cd /home/jobsub
            type ascii
            put kgrigsby_phpmtim_148991.log
            bye

Had to recompile PHPCALC and its dependencies on StandbyServer before it would work (just get PHPCALC working now, and compile the others on StandbyServer later):
    cd $BANNER_LINKS
    export COBDIR=/usr/lib/cobol
    export PATH=/usr/vac/bin:$PATH
    gencmplc.shl
    gencmpl.shl
    paycmpl.shl
Then, run phpcalc.shl for payroll.

Run any other scripts through Secure FTP as needed, and FTP any files back to the primary site as needed.

Afterwards, compile all of the programs on StandbyServer for future runs (allcmpl.shl is our script, which contains all of the Banner product compile scripts; cescmp.shl and cescmpl2.shl are our scripts which compile our locally-developed programs; runsqlplus and daemon are programs posted on my main web page):
    cd $BANNER_LINKS
    nohup allcmpl.shl >allcmpl.PROD.StandbyServer.log 2>&1 &
    tail -f allcmpl.PROD.StandbyServer.log
    make -f sctproc.mk runsqlplus
    nohup cescmpl.shl >cescmpl.PROD.StandbyServer.log 2>&1 &
    tail -f cescmpl.PROD.StandbyServer.log
    grep 'Open fail' cescmpl.PROD.StandbyServer.log
    nohup cescmpl2.shl >cescmpl2.PROD.StandbyServer.log 2>&1 &
    tail -f cescmpl2.PROD.StandbyServer.log
    grep 'Open fail' cescmpl2.PROD.StandbyServer.log
Compile E-Visions print software: eviadm, evippgrm, evilp:
    cd $BANNER_HOME/evisions
    make -f $BANNER_LINKS/sctproc.mk eviadm CHECKOPT=sqlcheck=limited
    $EXE_HOME/eviadm
        B - COMPILE FORMFUSION SERVER SOFTWARE
        Enter the password for EVISIONS
        Version to compile and install: 1.6.5.326 (latest)
        A. Compile/Execute all
        5.) Standard ANSI C Compiler
        X - EXIT INSTALL MENU
Compile daemon:
    cd /home/common
    rm -f daemon
    make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT="sqlcheck=semantics userid=<ID/password>" daemon
    mv $BANNER_HOME/general/exe/daemon .

After the disaster planning payroll test, switch primary/standby modes again on StandbyServer_prod and PrimaryServer_prod to restore their original modes, and start up the secondary standby databases, if any:

On the standby site, shut down job submission, posting and approvals (waiting about 5 minutes to complete) and any other user processes:
    StandbyServer:
        login oracle
            PROD
        rm /home/jobsub/PROD*
        sqlplus "/ as sysdba"
            @kill_user.sql
                Kill the GENLPRD, GURJOBS, and any other user processes.

Initiate switchover operation on the primary database, shut down, and restart (note: there is no "with session shutdown" prior to 10g):
            alter database commit to switchover to physical standby with session shutdown;
            shutdown immediate
            startup nomount
            alter database mount standby database;
            (leave sqlplus running here while we work on the primary site)

Check standby database switchover status (looking for "SWITCHOVER PENDING"; but mine said "TO PRIMARY"):
    PrimaryServer:
        login oracle
            PROD
        sqlplus "/ as sysdba"
            select database_role,switchover_status from v$database;
            select protection_mode from v$database;
        Initiate switchover operation on the standby database, shut down, and restart (also changing audit_trail back to true):
            alter database commit to switchover to primary;
            shutdown immediate
            startup nomount
            alter system set audit_trail=TRUE scope=spfile;
            shutdown immediate
            startup
            select name,instance_name,database_role,status,open_mode from v$instance,v$database;
            (leave sqlplus running here while we work on the primary site)

Start managed recovery on the original standby:
    StandbyServer:
        alter database recover managed standby database disconnect from session;
        select name,instance_name,database_role,status,open_mode from v$instance,v$database;
        exit

Write the current archive log on the original primary:
    PrimaryServer:
        alter system archive log current;
        exit

Make a full backup of the primary database:
rmanbackup.shl PROD full

Start up jobsub and sleep/wake on PrimaryServer (these are our scripts - you may have something similar):
    login root
    ./jsbringup.shl
    ./swbringup.shl

Turn on posting check and db status check in oracle's cron:
    PrimaryServer:
        login oracle
            PROD
        crontab -e
            uncomment the following lines:
                0,5,10,15,20,25,30,35,40,45,50,55 8-17 * * 1-5 /home/oracle/fgractg_check.shl
                0,5,10,15,20,25,30,35,40,45,50,55 8-17 * * 1-5 /home/oracle/check_db_status.shl
            :wq
        crontab -l

Turn on posting check in jobsub's cron:
    login jobsub
    crontab -e
        uncomment the following line:
            0,10,20,30,40,50 8-16 * * 1-5 ./monitor.shl
        :wq
    crontab -l

Start up the secondary standby database(s), if any, and it will start grabbing all of the archive logs from the original primary site since it was shut down:
    OtherServer:
        login oracle
            PROD
        sqlplus "/ as sysdba"
            @dgstartup.sql
            exit
        ls -ltr /ndxs/oradata/PROD/archivelogs | tail -20

This Page was Last Updated on 06/09/09

You Are Visitor Number
Hit Counter

Back To CES Home Page

Back To Steve Rea's Oracle Page

Back To Top

University of Arkansas • Division of Agriculture
Cooperative Extension Service
2301 South University Avenue
Little Rock, Arkansas 72204 • USA
Phone (501) 671-2000 • Fax (501) 671-2209

Mission • Disclaimer • EEO •
Privacy • FOI