IBM DB2 UDB (DB2 Universal Database) IBM Database 2 was first released on IBM Mainframe platform (1983) and these were the times when it was exclusively available on mainframe systems for around a decade and later it was brought to other platforms - UNIX/Linux and Windows servers.
Oracle RAC vs IBM DB2-
For large database (many terabytes) Database Partitioning Feature (DPF) is a clustered DB2 implementation wherein the a single and large database is partitioned across multiple DB2 servers communicating over a low latency network (high speed interconnect), this works great with OLAP -Online Analytical Processing systems.
DB2 pureScale is a shared-disk database cluster solution and is widely used for high-capacity OLTP systems.
A DB2 database can be administered from a GUI or command-line interface (CLI)
Here are some important DB2 concepts for a system administrator's perspective-
- DB2 Locking and Isolation Levels.
- RUNSTATS, and
- Reorg
DB2 Locking and Isolation Levels.
DB2 isolates transactions from each other through the use of locks. A lock is a mechanism that is used to associate a data resource with a single transaction, controlling how other transactions interact with that resource while it is associated with the owning transaction.
A lock prohibits a transactions from accessing uncommitted data written by other transactions (unless the Uncommitted Read isolation level is used) and to prohibit the updating of rows by other transactions when the owning transaction is using a restrictive isolation level. Once a lock is acquired, it is held until the owning transaction is terminated (COMMIT or ROLLBACK). At that point, the lock is released and the data resource is made available to other transactions.
Lock deferral - a important feature ( introduced with UDB v8) which improves concurrency of scans. The lock on a row is acquired once it is determined that the row qualifies for the query.
Default is to lock each row before determining whether row qualifies for the query during a table/index scan.
Oracle Real Application Cluster- provides clustering and high availability by running Oracle RDBMS software on multiple machines(servers/nodes, 11g supports up to 100 nodes) while accessing a single Oracle database. All the instances in a RAC cluster access therefore a single database, however each of node maintains its own set of redo logs. srvctl utility is used to administer a cluster e,g. adding or removing a database from cluster.
Oracle ASM -
Oracle Grid Infrastructure -
SCAN (Single Client Access Name) - is a feature used in RAC environments that provides a single name or cluster alias for the database running in cluster so the client connect string does not get changed when you add or remove nodes in a cluster.
Data Guard- oracle Data Guard provides a solution to prevent a single point of failure by maintaining a synchronized physical replica (standby database) of a production database at a remote location. If the production database goes unavailable, the client connections transparently failover to synchronized replica.
Oracle Database
The Oracle database consists of both the physical files that reside on the system and the logical elements as schema. The Oracle database server consists of the Oracle database and an Instance. The instance is the method to access the data and comprises of database processes and system memory.
Oracle Physical Data Structure - The Oracle database consists of several types of files - Data files, Control files, Redo Log files, Archive Log files (optional), Parameter files, Alert and Trace Log files, Backup files.
Data files - contains the information stored in the database, With Linux, the maximum size of a datafile is 4 million data blocks. The information for a single file can span many datafiles and many tables can share one data file. Oracle ASM technology uses a small oracle instance called ASM instance to manage the ASM devices. ASM data storage option has below features -
- 10,000 ASM disks in a storage system,
- 4 petabytes maximum for each ASM disk,
- 1 million files for each disk group, and
- 2.4 terabytes max storage per each file.
Parameter files - stores system configuration information. The pfile is an ASCII text file that can be modified in a text editor and is used during system startup. The spfile (server parameter file) is an Oracle readable file that is modified only by Oracle server. The recommended method is to
1) create pfile,
2) modify system by editing the pfile,
3) start instance using the pfile,
4) make any changes to system e.g, ALTER DATABSE ARCHIVELOG.
5) create spfile from pfile, and
6) restart the instance using the spfile.
Alert and Trace files - the Alert log contains information about the startup and shutdown operations and the configuration of the system. The trace file stores information about the problems occurred in the database to facilitate the administrator what went wrong in the system and h0w to fix issues.
Logical Data Structures - Tablespaces, Segments, Extents and Data Blocks.
Tablespaces - an Oracle Tablespace is the repository of database objects in database. It can be made up of just one data file to as many as 1024 data files. Every database is required to have at least one tablespace. SYSTEM tablespace is created by default when you create a database. It is the tablespace where data dictionary is kept. There are 2 types of tablespaces - Dictionary Managed tablespaces and 2) Locally Managed tablespaces which is a default since Oracle 9i. With Locally Managed tablespaces, a bitmap is used to manage the extents within the tablespace. If you specify the extent size then it is uniform throughout the tablespace.
Coalescing the tablespace - not required for a Locally managed tablespace. By coalescing, the adjacent free extents are coalesced into larger free extents. The SMON (system monitor) process automatically coalesce tablespaces.
* If your system tablespace is Locally managed, you can not create any dictionary managed tablespace.
Read-Only Tablespace - When a tablespace is made read-only, the need to take a backup of tablespace is eliminated. Once the data and indexes have been created in a read-only tablepsace, it can be made read-only by issuing-
ALTER TABLESPACE <tblspc> READ ONLY;
Temporary Tablespace- are used to perform sort and join operations.
The most important task in tablespace management is the space management. It is necessary to monitor both the space used and the load balancing of the tablespace. It is very important to monitor the physical I/O usage of disk drives to optimize the database performance. If you have multiple data files, then spread them equally among the disk drives.
Oracle Statistics -
Check when stat was last gathered -
SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='PSACCESSPRFL';
LAST_ANAL
---------
17-JUL-13
Create statistics -
SQL> exec dbms_stats.gather_table_stats('sysadm', 'psaccessprfl');
PL/SQL procedure successfully completed.
Data Blocks -
http://community.dbapool.com/articlelist.php?articles=1029200701
A Peoplesoft Database refresh
Backup and Recovery - Concepts
RMAN
Oracle Errors
Database Backup
Database Recovery
Posts
No comments:
Post a Comment