Docstoc

Getting started with standby databases

Document Sample
Getting started with standby databases Powered By Docstoc
					       Getting started with standby databases



                                          www.dbvisit.com
Oracle is a registered trademark of Oracle Corporation. Dbvisit is a registered trademark of Avisit Solutions Limited
Introducing myself – Arjen Visser
Founder and CEO of Avisit Solutions Limited in New Zealand

The creators of:
Dbvisit – Standby Database Technology
Leading software solution providing Oracle Disaster Recovery
No1 - Data Guard alternative.


Past Experience:
- Technical Director
- DBA
- Team leader/project manager
- Datawarehouse developer
- PL/SQL programmer
- Unix administrator
                       Dbvisit Customers
Some of the companies who entrust the security of their data to Dbvisit include:
Trusted by companies and DBA's in 52 countries:
                         Agenda
● What is it (logical versus physical standby)
● Why do we need one


● How to create one


● How to keep up to date


● Day to day management


● What is important


● Other uses


● Best practices
                       Not covered
● Data Guard
● Applications


● RAC and standby database


● Networking


● Enterprise Edition standby database features
         Most valuable asset of business today


                       Database



Needs protecting against natural and man-made disasters
ie: Disaster Recovery.

Why? - Ensure business continuity.

Who is responsible for database? - DBA

Protect the database AND business with the best method out
there – Standby Database
                  Why is Disaster Recovery Important?

43% of US businesses never reopen again after a
disaster and a further 29% close within 2 years. (*)

93% of companies that suffer significant data loss are out
of business within 5 years. (**)
(* US Small Business administration)
(** US Bureau of Labor)
                 Key Disaster Recovery metrics
RTO - (Recovery Time Objective)
  Maximum amount of time before systems are up and running again. How
  long before business is operational again.

RPO - (Recovery Point Objective)
  Maximum amount of data loss (measured in time) acceptable in the event of
  a disaster. How much data can we afford to lose without serious impact to
  the business.

   Example:
    Tier         RPO            RTO         Cost
    I            No data loss   <30 min     $$$$$$$$$$
    II           < 30 min       < 1 hour    $$$
    III          24+ hours      48+ hours   $$
    IV           7+ days        3+ days     $
             What is a standby database?
Primary database - Contains production data that must be
protected against any kind of loss.

Standby database - Copy of production database that can
be brought online to become the production database.

                Everyone should have one!
          Main purpose of standby database

Users can be transferred to the standby database (with
limited downtime) when main database has a major outage




             End Goal: Business Continuity
              2 types of standby databases
Physical
A physical standby database maintains a copy of a production
database but in a permanent state of recovery. If the
production database fails then the standby database can be
opened (or activated) and be ready for use.

Logical
An independent database to the production database but kept
in sync by replication mechanism. A logical standby database
is available at all times.
   Differences between logical and physical standby
                     database
Physical standby database is a binary copy - applies redo.
Logical standby database is a logical copy - applies SQL.

Analogy:
Keeping a standby copy of a word document in sync:

Physical → Use rsync to synchronise the word document file
with the standby word document file.

Logical → Open document, look for changes, select changes,
copy changes, close document. Open standby standby word
document, copy changes, making sure that the changes are
copied into the right place.
 Disadvantages of logical standby database:
●   Not a binary copy (not 100% guaranteed to be correct)
●   Not all data types are replicated.
●   Conflict resolution needs to be setup.
●   More complex to administer (ie what to do when it falls too far
    behind).
●   DBA needs to understand data and application to resolve
    conflicts.
●   Performance issues. SQL is applied to standby database, so it
    may experience the same load as the production database.

Logical standby database is generally not recommended for DR.
Better suited for replication – data distribution.
Disadvantages of physical standby database:
●   Need the same OS on both primary and standby server.
●   Need the same Oracle version on primary and standby.
●   Need the same amount of disk space.
●   Standby is READ ONLY.
●   READ ONLY is not real time (except with Active Data Guard).
●   Once standby is activated, cannot revert back to standby.
    Standby must be rebuild.
Advantages:
Logical Standby:
● Primary and standby can be on different platforms.


● Primary and standby can be different Oracle versions.


● Great for migration.


● Real time reporting.




Physical Standby:
● Easier to setup and maintain.


● Less overhead.


● 100% guaranteed to be correct (if best practices are followed).


● DBA's are more familiar with them – more common.




              Physical Standby best solution for DR
What is a physical standby database
          What is a physical standby database




Differences:
- No redo logs
- Standby controlfile instead of normal controlfile
Primary / Standby Database architecture




Copy entire backup copy of primary database to standby server
                     (except redo logs)
         Keeping Standby Database up to date
The standby database is in constant recovery state.
Archive log files from primary database are “applied” to
standby database.

Standby Database is constantly being recovered
Keeping the standby database up to date




               This is the process that must be managed
                        and automated to keep the
                       standby database up to date
Keeping the standby database up to date

     Standby Database ≠ Data Guard

Data Guard is Oracle's solution to
keeping the standby database up to date.
Data Guard manages this process.

There are other solutions.....(more later)
  Steps to implementing Physical Standby Database

7 Step method
1) Check pre-requisites
2) Install Oracle software on Standby Server
3) Create a backup of primary database
4) Transport backup to standby server
5) Configure standby database using backup
6) Deploy method to keep standby database in sync
7) Monitor and test
                Pre-requisites (step 1 of 7)
Primary
● Primary Database is in archive log mode




Standby
● Setup the same user accounts (Oracle software owner)

  and groups (dba)
● Recreate the same Oracle directory structure on the

  standby server (/u01/oradata, bdump, cdump etc).
● Copy the /var/opt/oracle/oratab or /etc/oratab from the

  production server (Linux/Unix only).
● Copy the pfile or spfile.
      Install Oracle on standby server (step 2 of 7)

Standby Server
● Install Oracle software on standby server.

● Patch Oracle to same version as production.

● Create ASM instance (if needed).

● Create service (Windows only).

● Setup listener.
    Install Oracle on standby server (step 2 of 7)
Standby Database license:
Standby database needs to be fully licensed using the same
metric as the primary database.

If primary database is Oracle Enterprise Edition (EE), then
standby database needs to be EE.

If primary database is Oracle Standard Edition (SE), then
standby database needs to be SE.
   Create a backup of primary database (step 3 of 7)
1) Rman
    - No need to shutdown primary database.
    - You do not need to create an Rman catalog.
    - Most efficient.
    - Requires listener.

    The easiest way to use Rman is to create the backup on local disk. However, this
    requires significant free space if your database is very large. If tape is used, a
    tape or media manager is required as part of your Rman configuration.


2) Traditional method (hot backup)
    - No need to shutdown primary database.
    - Tablespaces need to go into backup mode.
    - More redo generated.
    - Does not require listener.
    - Database files can be copied directly to standby server
  Create a backup of primary database (step 3 of 7)
1) Rman command to create backup
$ rman
connect target /
run{
change archivelog all crosscheck;
allocate channel ch1 type disk;
backup incremental level 0 database format
'/oracle/orabase/backupfile/bk_inc0_%s_%p' setsize=8250000 include current
controlfile for standby ;
sql "alter system archive log current";
backup archivelog all format '/oracle/orabase/backupfile/al_%s_%p';
release channel ch1;
}




- There are other RMAN commands available (RMAN Duplicate)
- Makes backup to local disk
- Creates the standby controlfile
   Create a backup of primary database (step 3 of 7)
2) Traditional command to create backup

Create standby controlfile
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
'/oracle/orabase/admin/dbvisitp/create/STANDBY_DBVISIT_control01.ctl' REUSE;




Create hot backup for each datafile
SQL> ALTER TABLESPACE tablespace_name BEGIN BACKUP;
!cp /u01/oradata/db/datafile01.dbf /backup/oradata/db/datafile01.dbf
SQL> ALTER TABLESPACE tablespace_name END BACKUP;




Save all archives from beginning of backup to the end of backup. They
are needed to create standby database.
   Transfer backup to standby server (step 4 of 7)
Transfer backed-up database (or backup pieces) to
standby server including new archive files.

Through network:
● Ssh (secure shell)

● Ftp (sftpc)

● NFS




Non network if database is too big:
● Tape

● Media device (3TB USB drive)
   Configure standby database using backup (step 5 of 7)



1) Rman
    - Requires SQL*Net connect from primary to standby.
    - Requires database backup pieces on the standby server.



2) Traditional method (hot backup)
    - Does not require SQL*Net or listener
    - Requires backup database files and standby controlfile on standby
    server.
   Configure standby database using backup (step 5 of 7)
1) Rman command to create standby database
$ rman
connect auxiliary sys/password@standbydb
connect target /
run {
allocate auxiliary channel ch1 type disk;
duplicate target database for standby dorecover nofilenamecheck;
release channel ch1;
}




- Runs from primary server.
- Creates the standby controlfile.
- Place all datafiles in the same location as primary database.
- Can be used to create ASM standby database.
- Can be used to create RAC standby database.
    Configure standby database using backup (step 5 of 7)
2) Traditional command to create standby database

Copy standby controlfile to correct locations:
cp /oracle/orabase/admin/dbvisitp/create/STANDBY_DBVISIT_control01.ctl
/u01/oradata/dbvisitp/control01.ctl
cp /oracle/orabase/admin/dbvisitp/create/STANDBY_DBVISIT_control01.ctl
/u01/oradata/dbvisitp/control02.ctl
cp /oracle/orabase/admin/dbvisitp/create/STANDBY_DBVISIT_control01.ctl
/u01/oradata/dbvisitp/control03.ctl



Startup standby database and recover
sqlplus /nolog
SQL> connect / as sysdba ;
SQL> startup nomount
SQL> alter database mount standby database ;
SQL> recover standby database ;
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


Apply all available archive log files
               Create standby database (step 5 of 7)
Complete steps for reference:

Manually creating a standby database for Windows:
http://www.dbvisit.com/docs/Creating_a_standby_database_windows.pdf


Manually creating a standby database for Linux/Unix:
http://www.dbvisit.com/docs/Creating_a_standby_database_linux.pdf
Deploy method to keep standby database in sync (step 6 of 7)
  Different options to keep standby database up to date:

  1) Data Guard - Need Oracle Enterprise Edition

  2) Dbvisit - All Oracle Editions and versions

  3) Home grown solution (manual log shipping)
Deploy method to keep standby database in sync (step 6 of 7)
  What is important in a standby database solution
  ● High reliability, robust and proven solution
  ● High resilience


  ● Expert technical support

  ● Support for RAC, OMF and ASM


  ● Creates standby database

  ● Low noise (only tells you when things go wrong)


  ● Fast to setup, easy to use, short learning curve

  ● Low Total Cost of Ownership (TCO)


  ● DBA's feel comfortable using it


  ● Gives peace of mind
Deploy method to keep standby database in sync (step 6 of 7)

   Home grown solution:
   On primary: Transfer archive logs to standby server
   On standby: Apply available logs
   sqlplus /nolog
   SQL> connect / as sysdba ;
   SQL> startup nomount
   SQL> alter database mount standby database ;
   SQL> recover standby database ;
   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}




        Seems simple, so what is hard about it
Deploy method to keep standby database in sync (step 6 of 7)
  Home grown - what is hard about it
  ● Robust/secure enough. Can it recover from all outages and
    glitches, good locking and transport mechanism?
  ● Solid notification.

  ● Has it been tested under all scenarios.


  ● Does it cover all Oracle errors and exceptions.


  ● What will happen when you change, upgrade or patch Oracle.

  ● What happens when you rebuild or refresh the standby

    database.
  ● Does it support RAC, OMF and ASM.


  ● Support and documentation.

  ● Comfort – will other DBA's be comfortable using it?
Monitor and Test (step 7 of 7)

  DR is not DR if it is not tested regularly
  ●   Test DR every quarter.

  ●   Monitoring to ensure standby database is up to date
      ● Exception reporting (only reports exception and

        errors).
      ● Daily heartbeat message to say everything is ok.

      ● Historical reporting on standby statistics.
   Managing standby databases (1 of 6)
Starting a standby database in normal recovery mode:
 Oracle 9i
 sqlplus /nolog
 SQL> connect / as sysdba ;
 SQL> startup nomount
 SQL> alter database mount standby database ;

 Oracle 10g and 11g
 sqlplus /nolog
 SQL> connect / as sysdba ;
 SQL> startup mount;


Shutdown a standby database:
 sqlplus /nolog
 SQL> connect / as sysdba ;
 SQL> shutdown
 ORA-01109: database not open


 Database dismounted.
 ORACLE instance shut down.
   Managing standby databases (2 of 6)
Standby Database status - Normal recovery mode:
 sqlplus /nolog
 SQL> connect / as sysdba;
 SQL> select name, controlfile_type,open_mode from v$database;

 NAME      CONTROL OPEN_MODE
 --------- ------- ----------
 DBVISITP STANDBY MOUNTED




Standby Database status - Read only mode:
 sqlplus /nolog
 SQL> connect / as sysdba;
 SQL> select name, controlfile_type,open_mode from v$database;

 NAME      CONTROL OPEN_MODE
 --------- ------- ----------
 DBVISITP STANDBY READ ONLY
    Managing standby databases (3 of 6)
Adding tempfile to standby database in recovery mode:
 SQL> alter tablespace temp add tempfile
 2* '/oracle/oradata/dbvisitp/temp01.dbf' size 10m reuse
 SQL> /
 alter tablespace temp add tempfile
 *
 ERROR at line 1:
 ORA-01109: database not open




Adding tempfile to standby database in READ ONLY mode:
 SQL> alter tablespace temp add tempfile
 '/oracle/oradata/dbvisitp/temp01.dbf' size 10m reuse

 Tablespace altered.
    Managing standby databases (4 of 6)

Adding datafiles and tablespaces to primary database
Will not be added to the standby automatically (resizing datafiles is fine).
Solution: Set Oracle parameter on standby database (from
version 10g)
   SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO
   SCOPE = BOTH;


  Different filename between primary and standby database.
       DB_FILE_NAME_CONVERT
    Managing standby databases (5 of 6)
Checking Standby Database is up to date.
Archive log list command does not give correct standby sequence
(except when using Data Guard).
 SQL> archive log list
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            /oracle/oraarch/dbvisitp
 Oldest online log sequence     1640
 Next log sequence to archive   1642
 Current log sequence           1642



Use v$log_history
 SQL> select max(sequence#) from v$log_history;

 MAX(SEQUENCE#)
 --------------
           1668
      Managing standby databases (6 of 6)
  Activating Standby Database.
  When primary database is no longer available, activate the standby
  database (failover)
   sqlplus /nolog
   SQL> connect / as sysdba ;
   SQL> alter database activate standby database;
   SQL> shutdown immediate;
   SQL> startup

Activating the standby database initiates an OPEN RESETLOGS operation on the
standby database to create the redo logs.

Note: all previous backups of this database are now invalid and cannot be used to restore
this database. This is because of the RESETLOGS command which resets the archive
sequence number (SEQUENCE#) and invalidates all previous archive logs. The SCN
number of the database is not reset.

Now is a good time to make a new backup of your database!
                              What is important

Oracle strongly recommends that all NOLOGGING operations be
avoided in the primary database. Oracle will not be able to keep the
standby database consistent with the primary database during nologging
operations.
    SQL> ALTER DATABASE FORCE LOGGING;
    Ensures all operations are logged (Oracle 9i and above)


 The following error occurs when trying to open the standby database in read only mode:

 ORA-01578: ORACLE data block corrupted (file # n, block # nnn)
 ORA-01110: data file n: <file_name>
 ORA-26040: Data block was loaded using the NOLOGGING option
                          What is important

Monitor the alert log for the standby database.
What does the alert log look like:
ALTER DATABASE RECOVER FROM '/oracle/oraarch/w112g' standby
database
Media Recovery Start
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
ORA-279 signalled during: ALTER DATABASE RECOVER FROM
'/oracle/oraarch/w112g' standby database    ...
ALTER DATABASE RECOVER FROM '/oracle/oraarch/w112g'    CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER FROM '/oracle/oraarch/w112g'
CANCEL

ORA-279 code in alert log is normal.
                   What is important

Propagation limitations of standby databases.
All DML is propagated to standby database (*).
Most DDL is propagated to standby database (**).
ALTER SYSTEM is not propagated to standby database.
ALTER DATABASE SET is not propagated to standby database.
ALTER DATABASE RECOVER will invalidate standby.

* Enable FORCE LOGGING
** Enable STANDBY_FILE_MANAGEMENT = auto
                      What is important

Patching of Oracle with a standby database
1)Stop standby update proces.
2)Patch or upgrade the Oracle software on the primary server.
3)Patch or upgrade the Oracle software on the standby server.
4)On primary server:
  Startup the primary instance on the primary
  sqlplus "/ as sysdba"
  SQL> startup migrate
5)Run patch script:
  SQL> @?/rdbms/admin/catpatch.sql
6)Restart standby update process.
    What else can I use my standby database for

Reporting database. Offset load on primary database.
   SQL> ALTER DATABASE OPEN READ ONLY;

Shadow environment
   Used to investigate bugs, inconsistencies etc without taking down or affecting primary database.

Capturing snapshots for batch transactions
   Used to rollback overnight batch processing gone wrong.

Test environments
   Capture snapshots for testing purposes

Offload backups
   Offload backups from primary server to standby server.

Planned outages
   Switch roles between primary and standby database to minimize downtime during maintenance
               Best practice standby database
1) Keep it simple. Keep the same layout as primary
   database (mount points, UID, GID, portnumbers etc).
2) One master (ie /etc/oratab, tnsnames.ora etc).
3) Automate, automate, automate.
4) Manage archives on standby.
5) Include standby database impact on change control.
6) Monitor, monitor, monitor.
7) Become familiar - Play, play play.
8) Test, test and test.
                                   End of presentation
                                      QUESTIONS?
                                       Thank you
                                    www.dbvisit.com

www.dbvisit.com/dbman
    Oracle is a registered trademark of Oracle Corporation. Dbvisit is a registered trademark of Avisit Solutions Limited

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:14
posted:8/9/2011
language:English
pages:52