Arkansas Banner Users Group (ABUG) 2006

1
Arkansas Banner Users Group (ABUG) 2006

• Crashes Happen - Downtime Won'twith Data Guard
• Stephen Rea
• Monday, April 24, 2006 1130 AM

2
Session Rules of Etiquette

• No smokin'
• No drinkin'
• No cussin'
• No tobaccer spittin'
• No tomater throwin'

3
Data Guard - Oracle's Answer to Disaster Recovery

• See how to quickly implement a Data Guard
  physical standby database in a day.
• Learn how to switch 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!"
• Oracle Data Guard Concepts and Administration
  Release 2 (9.2)

4
Data Guard Flow (Oracle 9i)
5
Data Guard Flow (Oracle 10g)
6
Data Guard Protection Modes

• Maximum Performance
• Updates committed to primary and sent to standby
  without waiting to see if they were applied to
  standby
• Pros Little or no effect on performance of
  primary
• Cons Slight chance of lost transactions (on
  failover)
• Maximum Availability (we will implement this one)
• Attempts to apply updates to standby before
  committed to primary
• Lowers protection to Maximum Performance
  temporarily if updates can't be applied to
  standby
• Pros Primary continues unaffected if connection
  to standby is lost or the updates are delayed
• Cons Slight performance hit on primary lost
  transactions on failover possible only if the
  standby has been unreachable

7
Data Guard Protection Modes

• Maximum Protection
• Assures updates are applied to standby before
  committed to primary
• Pros No chance of lost transactions
• Cons Primary will freeze if connection to
  standby is lostor the updates are delayed
• Notes We will be implementing a physical standby
  database locally on the same server here.
  Logical standby databases are not covered.
  Implementing the standby on a remote server will
  be similar - just use a different IP address, and
  the standby instance name can be the same as the
  primary in that case.

8
Primary Database Requirementsfor Data Guard

• FORCE LOGGING must be enabledSQLgt select
  force_logging from vdatabaseSQLgt alter
  database force logging
• ARCHIVELOG mode and automatic archiving must be
  enabledSQLgt archive log list
• MAXLOGFILES gt (2 Current Redo Log Groups)
  1SQLgt select records_used "Current Groups",
  records_total "Max Groups" from
  vcontrolfile_record_section where type
  'REDO LOG'

9
listener.ora Additions

• Define the standby database SID on the standby
  site
• (SID_DESC
• (SID_NAMEPROD2)
• (ORACLE_HOME/pgms/oracle/product/v9204)
• )
• (in ORACLE_HOME/network/admin/listene
  r.ora)

10
tnsnames.ora Additions

• Define the standby database connect string on the
  primary site
• myserver_prod2
• (DESCRIPTION
• (ADDRESS_LIST
• (ADDRESS
• (PROTOCOL TCP)
• (Host 123.45.67.89) -- whatever host
  IP has PROD2
• (Port 1521)
• )
• )
• (CONNECT_DATA (SID PROD2)
• )
• )
• (define myserver_prod and
  myserver_prod2 on both
• primary and standby sites for quick
  switchovers)

11
sqlnet.ora and /etc/oratab Additions

• Enable dead connection detection on the primary
  and standby sites
• sqlnet.expire_time2
• (in ORACLE_HOME/network/admin/sqlnet.
  ora)
• Add the standby database's entry to /etc/oratab
  on the standby site
• PROD2/pgms/oracle/product/v9204N

12
Standby Database Parameter File

• Create the initPROD2.ora parameter file to be
  used for the standby database (done from primary
  database)
• If your primary is using an spfile sqlplus "/
  as sysdba"SQLgt create pfile'ORACLE_HOME/dbs/ini
  tPROD2.ora' from spfile
• Else, if your primary is using a pfile cp -p
  ORACLE_HOME/dbs/initPROD.ora
  ORACLE_HOME/dbs/initPROD2.ora
• Note We will be modifying both the primary and
  standby parameter files to handle being in either
  the primary or the standby mode for quick
  switchovers.

13
Standby Database Parameters (changes in copy of
primary's values)

• Change pathnames, such as control_files,
  background_dump_dest,
• core_dump_dest, user_dump_dest, and
  audit_file_dest, and add
• log_archive_dest /orcl/oradata/PROD2/archivelo
  gs
• log_archive_dest_1 'LOCATION/orcl/oradata/PROD2
  /archivelogs MANDATORY' for switchover
• log_archive_dest_state_1 ENABLE for
  switchover
• log_archive_dest_2 'SERVICEmyserver_prod LGWR
  SYNC' for switchover
• log_archive_dest_state_2 ENABLE for
  switchover
• standby_archive_dest /orcl/oradata/PROD2/archive
  logs
• standby_file_management AUTO or MANUAL for
  raw devices
• remote_archive_enable TRUE TRUE or RECEIVE,
  change RECEIVE to SENDon switchover
• instance_name PROD2
• lock_name_space PROD2 use when primary and
  standby on same system same as instance_name
• fal_server myserver_prod "fal" is Fetch
  Archive Log, for log gap resolution
• fal_client myserver_prod2
• db_file_name_convert ('/PROD/','/PROD2/')
• log_file_name_convert ('/PROD/','/PROD2/')

14
Primary Database Parameters (changes in primary's
values)

• log_archive_dest /orcl/oradata/PROD/archivelogs
  
• log_archive_dest_1 'LOCATION/orcl/oradata/PROD/
  archivelogs MANDATORY'
• log_archive_dest_state_1 ENABLE
• log_archive_dest_2 'SERVICEmyserver_prod2 LGWR
  SYNC'
• log_archive_dest_state_2 ENABLE
• standby_archive_dest /orcl/oradata/PROD/archivel
  ogs for switchover
• standby_file_management AUTO for switchover
  or MANUAL for raw devices
• remote_archive_enable TRUE TRUE or SEND,
  change SEND to RECEIVE on switchover
• instance_name PROD
• lock_name_space PROD use when primary and
  standby on same system same as instance_name
• fal_server myserver_prod2 for switchover
• fal_client myserver_prod for switchover
• db_file_name_convert ('/PROD2/','/PROD/')
  for switchover
• log_file_name_convert ('/PROD2/','/PROD/')
  for switchover
• (If primary uses spfile,
  wait until after the standby database
• files are
  copied/created to make these parameter changes.)

15
Standby Database Datafiles, etc.

• Create the standby control file from the primary
  databaseSQLgt alter database create standby
  controlfile as '/orcl/oradata/PROD2/ctrl_
  PROD_01.ctl'
• Shut down the primary database and copy or FTP
  its datafiles, redo log files, and the
  just-created standby parameter file and standby
  control file, to the standby site.

16
Standby Database Datafiles, etc.

• Copy the standby control file on the standby site
  to the other file names listed in the
  control_files init.ora parameter.
• Create the standby's password file, if needed, on
  the standby site orapwd fileORACLE_HOME/dbs/o
  rapwPROD2 passwordltsys passwordgt entries5
• Reload the listener on the primary and standby
  sites lsnrctl reload

17
Standby Database Startup

• Start the standby database in nomount mode,
  create the spfile if wanted, mount the standby
  database, and change to managed recovery .
  oraenv PROD2 sqlplus "/ as sysdba"SQLgt
  create spfile from pfileSQLgt startup
  nomountSQLgt alter database mount standby
  databaseSQLgt alter database recover managed
  standby database disconnect from
  sessionSQLgt exit

18
Primary Database Startup

• If your primary is using an spfile, set
  theprimary database parameters in the spfileas
  listed earlier. Sample "alter system" commands
  are shown belowSQLgt startup nomountSQLgt alter
  system reset log_archive_dest scopespfile
  sid''SQLgt alter system set
  log_archive_dest_1 'LOCATION/orcl/orad
  ata/PROD/archivelogs MANDATORY'
  scopespfile etc SQLgt shutdown

19
Primary Database Startup

• Start up the primary database with the new
  parametersSQLgt startup
• Start archiving to the standby database by
  issuing a log switchSQLgt alter system switch
  logfile

Congratulations! You now have a working standby
database for your primary database.
But wait There's more
20
Add Standby Redo Log Groups to Standby Database

• Create standby redo log groups on standby
  database (start with next group number create
  one more group than current number of groups)
  after switching out of managed recovery
  modeSQLgt sqlplus "/ as sysdba"SQLgt alter
  database recover managed standby database
  cancelSQLgt alter database open read onlySQLgt
  select max(group) maxgroup from vlogfileSQLgt
  select max(bytes) / 1024 "size (K)" from
  vlogSQLgt alter database add standby logfile
  group 4 ('/orcl/oradata/PROD2/stby_log_PR
  OD_4A.rdo', '/orcl/oradata/PROD2/stby_log
  _PROD_4B.rdo') size 4096K etc SQLgt
  column member format a55SQLgt select
  vs.group,vs.bytes,vl.member from vstandby_log
  vs, vlogfile vl where vs.group
  vl.group order by vs.group,vl.member

21
Add Tempfile To Standby

• Add a tempfile to the standby database for
  switchover or read-only access, then, switch back
  to managed recoverySQLgt alter tablespace temp
  add tempfile '/data/oradata/PROD2/temp_PR
  OD_01.dbf' size 400064K reuseSQLgt
  alter database recover managed standby database
  disconnect from sessionSQLgt select
  from vtempfileSQLgt exit

22
Add Standby Redo Log Groups to Primary Database

• Create standby logfile groups on the primary
  database for switchovers (start with next group
  number create one more group than current number
  of groups) sqlplus "/ as sysdba"SQLgt select
  max(group) maxgroup from vlogfileSQLgt select
  max(bytes) / 1024 "size (K)" from vlogSQLgt
  alter database add standby logfile group 4
  ('/orcl/oradata/PROD/stby_log_PROD_4A.rdo',
  '/orcl/oradata/PROD/stby_log_PROD_4B.rdo')
  size 4096K etc SQLgt column member
  format a55SQLgt select vs.group,vs.bytes,vl.membe
  r from vstandby_log vs, vlogfile vl
  where vs.group vl.group order by
  vs.group,vl.member

23
Switch To Maximum Availability Protection Mode

• Switch to the desired "maximum availability"
  protection mode on the primary database (from the
  default "maximum performance")SQLgt select value
  from vparameter where name
  'log_archive_dest_2' -- must show LGWR
  SYNCSQLgt shutdown normalSQLgt startup mountSQLgt
  alter database set standby database to
  maximize availabilitySQLgt alter database
  openSQLgt select protection_mode from vdatabase

24
Test Updates Propagatingto Standby

• Try some edits on the primary and check to see
  that the changes made it to the standby
• On the primarySQLgt update spriden set
  spriden_first_name 'James' where
  spriden_pidm 1234 and spriden_change_ind
  is nullSQLgt commitSQLgt alter system switch
  logfile
• On the standby (wait a few seconds first)SQLgt
  alter database recover managed standby database
  cancelSQLgt alter database open read
  onlySQLgt select from spriden where
  spriden_pidm 1234 and
  spriden_change_ind is nullSQLgt alter database
  recover managed standby database
  disconnect from session

25
Running Reports with a Standby

• Set standby to Read Only to run reportsSQLgt
  alter database recover managed standby
  database cancelSQLgt alter database open read
  onlySQLgt _at_myreport.sqlSQLgt alter database
  recover managed standby database
  disconnect from session

26
Shutdown and Startup for Standby Database

• To shut down a standby database
• If in read-only access, switch back to managed
  recovery(after terminating any other active
  sessions)SQLgt alter database recover managed
  standby database disconnect from
  session
• Cancel managed recovery and shutdownSQLgt alter
  database recover managed standby database
  cancelSQLgt shutdown immediate
• To start up a standby databaseSQLgt startup
  nomountSQLgt alter database mount standby
  databaseSQLgt alter database recover managed
  standby database disconnect from session

27
Switchover - Swapping Primary and Standby

• End all activities on the primary and standby
  database.
• On the primary (switchover status should show "TO
  STANDBY")SQLgt select database_role,switchover_st
  atus from vdatabaseSQLgt alter database commit
  to switchover to physical standbySQLgt shutdown
  immediateSQLgt startup nomountSQLgt alter
  database mount standby database
• On the standby (switchover status should show
  "SWITCHOVER PENDING")SQLgt select
  database_role,switchover_status from
  vdatabaseSQLgt alter database commit to
  switchover to primarySQLgt shutdown normalSQLgt
  startup
• On the primarySQLgt alter database recover
  managed standby database disconnect from
  session
• On the standbySQLgt alter system archive log
  current
• Change tnsnames.ora entry on all servers to swap
  the connect strings (myserver_prod and
  myserver_prod2).

28
Failover - Standby Becomes Primary

• End all activities on the standby database.
• May need to resolve redo log gaps (not shown
  here).
• On the standbySQLgt alter database recover
  managed standby database finishSQLgt
  alter database commit to switchover to
  primarySQLgt shutdown immediateSQLgt startup
• Change tnsnames.ora entry on all servers to point
  the primary connect string to the standby
  database.
• New standby needs to be created. Old primary is
  no longer functional.

29
Summary

• Data Guard provides an automated standby database
  which can essentially eliminate downtime of your
  production data.
• Setup is easy and fairly straightforward.
• Maintenance is minimal.
• Switchovers and failovers can be done within a
  few minutes.
• Reporting can be offloaded to the standby to ease
  the workload on the primary.
• And It's Free! (Included with Enterprise
  Edition)

30
Whew! Glad Thats Over!Any Questions?
31
Presenter Information

• Stephen Rea, Oracle Database Administrator
• Univ of AR Cooperative Extension Service2301 S.
  University AvenueP.O. Box 391Little Rock, AR
  72203
• 501-671-2207
• srea_at_uaex.edu
• http//www.uaex.edu/srea/dataguard.htm