Oracle Database Administration

1. Oracle Database Administration Session 14 Product Review Course Review

2. Product Review • DataGuard • Streams • RAC • Partitioning

3. DataGuard • Dataguard is an Oracle technology used for data protection, High Availability and Disaster Recovery • It requires one primary database and you may have up to 9 standby databases • The standby databases can be physically located anywhere, as long as they have a connection to the primary. The primary and standby can be on the same server • DataGuard technology ships with the Oracle database and is available automatically after installation

4. DataGuard • A standby database is a consistent copy of the primary database • It is initially created from a backup copy of the primary database; the backup copy may be hot or cold • DataGuard processes maintain the standby database by applying redo data from the primary to it • Dataguard uses managed recovery or logminer technology depending upon the type of standby you have

5. DataGuard • The standby database can be a physical standby or a logical standby • A physical standby is an identical copy of the primary database – down to the block level • A physical standby database is not open for access – it remains in a mounted state (except in 11g you can have an Active Physical Standby database)

6. DataGuard • A logical standby has the same logical data as the primary, but its physical structures can be different – it can contain schemas not in the primary • A logical standby is open for read access to the “guarded” schemas and read/write access to other schemas • Non System schemas from the primary are “guarded” in the standby – i.e. they are the schemas in the standby updated by DataGuard processes

8. DataGuard Requirements • Each server in a DataGuard configuration must be running the same OS • Each server should have the same number of CPUs • Each server should have the same physical memory size • Each must run the same Oracle release (i.e. 10g, 11g) although patch set may be different • Each server should have the same number and size of database file systems

9. DataGuard - Configuration • Both the primary and the standby databases are configured for DataGuard using init.ora parameters • To update the standby with changes from the primary, log writer transfers archive logs to the standby • What happens to the archive logs on the standby depends on whether the standby is physical or logical • In addition, standby redo logs are configured on the standby database allowing simultaneous writing of redo from the primary to the standby – this allows for real time updates on a physical standby

10. DataGuard – Physical Standby • A physical standby runs in what is called “managed recovery” mode • As archive logs arrive on the standby server in a directory designated in the standby init.ora file, the rec0 process applies the archive logs to the standby just as if the database were being recovered using the recover command

12. DataGuard – Logical Standby • A logical standby runs in what is called “sql apply” mode • As archive logs arrive on the standby server logminer processes convert the contents of the archive logs to sql statements which are applied to the standby database • Logical standby is transactionally consistent with the primary

14. DataGuard – Physical and Logical

15. Physical Standby Database is always in mounted state and cannot be used for any other purposes Archive logs are applied in managed recovery mode Physical Standby is exact copy of the primary database Physical organization and structure exact same as primary Logical Standby Database is always open Logical standby can be used for read access on schemas coming over from primary and read/write access on other schemas local only to standby Dml/ddl statements are generated from archivelogs by LogMiner and applied to standby database Logical Standby is not an exact copy of the primary but is a transactionally consistent copy of the primary DataGuard – Physical and Logical

16. Oracle Streams enables information sharing. Streams propagates information within a database or from one database to another. Oracle Streams Capture Staging Consumption Capture

17. Streams • Streams is an Oracle technology to share information • Each UNIT of information is called a message • These Messages are transferred through a Stream • The Stream can move data within a database or between databases

18. Streams • Streams Capture, Stage and Apply messages • It can apply • data manipulation language (DML) changes • data definition language (DDL) changes

19. One or more target databases Source database Apply queue Apply process Capture queue Capture process Propagation processes Apply queue Apply process Overview of Streams Schema-A Schema-A Db-Link Schema-A Db-Link All the DML and DDL changes on schema 'A' reflect on both target databases.

20. Streams Capture and Apply Replication Configuration

21. Logical Change Record (LCR)

22. Downstream Capture

23. Oracle RAC • Oracle RAC extends the Oracle Database • So that you can store, update, and retrieve data from multiple instances on different servers, at the same time • The RAC software allows the servers to work together as a cluster.

24. Oracle RAC • The files that make up the database must be on shared storage that is available to all servers in the cluster • Each server in the cluster must run the Oracle RAC software

25. Oracle RAC • The Oracle RAC infrastructure is a large component of implementing an Oracle enterprise grid computing architecture • It is also part of any High Availability (HA) solution

26. Single Instance vs. RAC

27. RAC Databases • All Single Instance Oracle databases have a one-to-one relationship between datafiles and the instance. • Oracle RAC environments have a one-to-many relationship between datafiles and instances. • Using Oracle RAC, multi-server cluster database instances form a single (virtual) database. • The instances are on different servers (or nodes)

28. RAC Databases • Each database instance in the cluster uses its own memory structures and background processes • Oracle RAC uses Cache Fusion to synchronize the data stored in the buffer cache of each cluster database instance

29. RAC Hardware Architecture

30. RAC Technology Layers

31. RAC Setup Order

32. RAC Technology Layers

33. 10g RAC Architecture

34. Cluster Interconnect

36. Partitioning • Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. • Indexes can be partitioned in similar fashion. • Each partition is stored in its own segment and can be managed individually.

37. Partitioning • There are a number partitioning methods • Range partitioning is based on ranges of column values. • Hash partitioning provides a method of evenly distributing data across a specified number of partitions. • List partitioning gives explicit control over how rows map to partitions.

38. Partitioning • More partitioning methods • Composite range-hash partitioning gives a subpartition option • Composite range-list partitioning gives another subpartition option

39. General Course Review • Setup the Server • Create dba Group • Create oracle user • Create a profile for the Oracle user • Add all variables that are needed • Install the software

40. Installation • Oracle 10gR2 Enterprise Edition for Solaris • Set the DISPLAY variable • Java based installer • ./runinstaller

41. Database Creation • OFA standard • The /admin directory tree • mkdir udump cdump bdump pfile create • mkdir arch export

42. Database Creation • Create a database • Build the scripts, with the tablespaces • Ensure that /u*/oradata/<SID>/ directories exist

43. Database Creation • Build the database to the requirements of your application • What tablespaces are needed • How many rollback segments (automatic UNDO) • How many redo log groups • How many members in each redo log group

44. Database Creation • Do you need a special database user to hold the objects for your application • Do other users need accounts to access the application • What privileges do these uses need • Should we create roles

45. Database Creation • Use a script to create your database objects • Make sure all tables and indexes have storage parameters, Use ASSM • What level of activity will occur on each table or function (group of tables) • What is the projected growth rate • If you do not have good information to understand the growth, make an educated guess

46. Database Creation • Growth rate will affect the storage parameters • Set the PCTFREE to at least 30 on very active tables, if used • Set the PCTUSED to 60 on those tables, if used • Increase the freelist value from 1 to 3, 4 or 5, on tables that will be accessed by concurrent users, if not using ASSM

47. Post Creation Activities • Setup the TNS listener • Add the database to the tnsnames.ora file • Test access to the database through sql*net • Look into the use of LDAP, Oracle Internet Directory

48. Post Creation Activities • Develop a backup strategy • Offline • Online (hot) • When • How oven • Backup the database • Test database recovery • Do we take an export of the database ?

49. Post Creation Activities • Develop scripts to shutdown and startup the database • Automate this process • User crontab or some other scheduler • Man crontab for more information

50. Post Creation Activities • Build a script to document the database • It can also be used for a basic level of performance monitoring • Look at the number of extents used by an object • What tablespaces are your users using