What is a Checkpoint

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk. It offers Oracle the means for ensuring the consistency of data modified by transactions. The mechanism of writing modified blocks on disk in Oracle is not synchronized with the commit of the corresponding transactions.
A checkpoint has two purposes:
(1) to establish data consistency, and
(2) enable faster database recovery.
The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).

Depending on the number of datafiles in a database, a checkpoint can be a highly resource intensive operation, since all datafile headers are frozen during the checkpoint. Frequent checkpoints will enable faster recovery, but can cause performance degradation

Key Initialization parameters related to Checkpoint performance.

• FAST_START_MTTR_TARGET
• LOG_CHECKPOINT_INTERVAL
• LOG_CHECKPOINT_TIMEOUT
• LOG_CHECKPOINTS_TO_ALERT

FAST_START_MTTR_TARGET: It enables you to specify the number of seconds the database takes to perform crash recovery
of a single instance. Based on internal statistics, incremental checkpoint automatically adjusts the checkpoint target to meet the requirement of FAST_START_MTTR_TARGET. V$INSTANCE_RECOVERY.ESTIMATED_MTTR shows the current estimated mean time to recover (MTTR) in seconds. This value is shown even if FAST_START_MTTR_TARGET is not specified.

LOG_CHECKPOINT_INTERVAL: It influences when a checkpoint occurs, which means careful attention should be given to the setting of this parameter, keeping it updated as the size of the redo log files is changed. The checkpoint frequency is one of the factors which impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that if the system crashes, more time will be needed for the database to recover. Shorter checkpoint intervals mean that the database will recover more quickly, at the expense of increased resource utilization during the checkpoint operation

LOG_CHECKPOINT_TIMEOUT: The parameter specifies the maximum number of seconds the incremental checkpoint target should lag the current log tail. In another word, it specifies how long a dirty buffer in buffer cache can remain dirty. Checkpoint frequency impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that more time will be required during database recovery.

LOG_CHECKPOINTS_TO_ALERT: It lets you log your checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency.

Relationship between Redologs and Checkpoint: A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint. Maintain well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches. The alert log is a valuable tool for monitoring the rate that log switches occur, and subsequently, checkpoints occur.

Checkpoint not complete: This message in alert log indicates that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log. In this case, Oracle must wait until the checkpoint position passes that log.When the database waits on checkpoints,redo generation is stopped until the log switch is done. This situation may be encountered if DBWR writes
too slowly, or if a log switch happens before the log is completely full, or if log file sizes are too small.

What information is Stored in Alert.log

Oracle's Alert Log [alert_sid.log/alert_(db_name).log] is a text file which can be opened with any text editor. The directory where it is found can be determined by the initialization parameter " background_dump_dest ", it records messages and errors that arise during the day to day database activities.
The messages in the alert log includes:

• Information of Instance Startup and Shutdown
• Errors causing generation of trace files.
• Create, alter and drop SQL statements executed on database, tablespaces and rollback segments.
• Errors arising during a materialized view is refreshed.
• ORA-00600 internal errors.
• Block Corruption (ORA-01578) Errors.
• Dead Locks(ORA-00060) Errors.
• Pointers to error generated trace files and dump files.

The alert log can be deleted and renamed if required for ex: if it reaches certain size, then you can delete or rename the alert log. Oracle will simply create a new alert log and start writing to it. Deleting the alert log or renaming won't affect the database functionality or performance.

Things a DBA must know

There are some things a good DBA must know.

1. This task? its easy, go to Google, DB Forums, etc.

But I know, however, that it cannot be a replacement for foundational knowledge of your chosen database server.
Sure, I can (and sometimes do) google the syntax for a command to get it right, but foundational knowledge is too important to be left to searches.

2. Continuing education is very Crucial, be aware of your technology.

This is in continue of point #1. Things change, even in the world of Oracle, so keep yourself up to date on the latest releases, patches, etc.

3. Monitoring is critical.

It doesn't matter which monitoring tool/system you use(OEM, Grid Control, third party tools, etc), just make sure you have one in place and it should be working! You should know when a server has too many connections, not enough memory, or if it's running out of disk space (or a hundred other things). Montoring is the base of tuning.

4. There is nothing more important than backups.

Performance tuning and building an awesome server are cool, but this is the bottom line: our data is sacred. Before anything else, you have to make regular backups and you have to make certain that those backups work properly. You must have a backup strategy, verify all the scheduled backups run successfully(by checking the backup logs) on a daily basis and perform test recoveries if needed to check the backup status.

5. Have fun at your job.

I hope you do what you do because it's your passion. It is for me, when I manage databases I am protecting client's most valuable resource and always learning something new. I get satisfaction from what I do. Because it matters a lot, whether you like your work or not.

Oracle Grid Control Architecture

Overview of Oracle Grid Architecture

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.

For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Difference between a cluster and a grid

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered - as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don't know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name, The Grid. This view of utility computing is, of course, a "client side" view.

From the "server side", or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.

DBA Activities

DBA BEST PRACTICES

One question that comes up frequently for new DBAs or under trainee DBAs are 1. what is my job? 2. when I take over a new system what should I do first? This page gives you a slight review of dba best practices, advice on what to do in certain situations. Actions are on the left, commands on the right.
What should I do today?
1. Review the alert log	Check for any errors.
2. Determine if there is block level corruption	conn / as sysdba SELECT * FROM v$database_block_corruption;
3. Verify the status of last night's backup	conn / as sysdba SELECT * FROM v$backup_corruption;
4. Look for newly invalidated objects	conn / as sysdba SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;
5. Are there any hung resumable sessions	SELECT user_id, session_id, status, suspend_time, error_number FROM dba_resumable;
6. Are there any blocked sessions	SELECT ( SELECT username FROM gv$session WHERE sid=a.sid) blocker, a.sid, ' is blocking ', ( SELECT username FROM gv$session WHERE sid=b.sid) blockee, b.sid FROM gv$lock a, gv$lock b WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;
What should I do in a new environment/system?
1. Determine the actual version of Oracle	conn / as sysdba SELECT * FROM v$version; Also run the demos under DBMS_UTILITY.DB_VERSION and DBMS_UTILITY.PORT_STRING
2. Determine what components are installed	conn / as sysdba col comp_name format a40 SELECT comp_name, version, status FROM dba_registry;
3. Determine if there are any invalid objects in the DB	conn / as sysdba SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;
4. Is the instance part of a RAC cluster	Run the demo code under DBMS_UTILITY.ACTIVE_INSTANCES
5. Is there Data Guard replication running?	conn / as sysdba SELECT protection_mode, protection_level, remote_archive, database_role, dataguard_broker, guard_status FROM v$database;
6. Is the database in ARCHIVE LOG mode?	conn / as sysdba SELECT log_mode FROM v$database;
6. Are FLASHBACK LOGS being collected?	conn / as sysdba SELECT flashback_on FROM v$database;
7. Is there supplemental logging in place?	conn / as sysdba SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database;
8. Where are the control files?	conn / as sysdba SELECT name FROM v$controlfile;
9. Where are the log files?	conn / as sysdba SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS FROM v$log l, v$logfile lf WHERE l.group# = lf.group# ORDER BY 1,3;
10. What are the initialization parameters?	conn / as sysdba CREATE PFILE='/home/oracle/initparams.txt' FROM memory;
11. Capture database information	conn / as sysdba SELECT dbid, name, open_mode, database_role, platform_name, FROM v$instance;
12. Capture instance information	conn / as sysdba SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state FROM v$instance;
13. Are default passwords in use?	conn / as sysdba SELECT d.username, u.account_status FROM dba_users_with_defpwd d, dba_users u WHERE d.username = u.username AND account_status = 'OPEN' ORDER BY 2,1; If default passwords are in either lock the accounts for change them: ALTER USER <user_name> ACCOUNT LOCK; ALTER USER <user_name> IDENTTIFIED BY <new_password>;
14. Is BLOCK CHANGE TRACKING enabled?	conn / as sysdba SELECT filename, status, bytes FROM v$block_change_tracking;
15. What features are being used?	Run the demo code under DBMS_FEATURE_USAGE_REPORT
16. What profiles exist and are in use?	conn / as sysdba SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; SELECT profile, resource_name, limit FROM dba_profiles ORDER BY 1,2; SELECT username, profile FROM dba_users WHERE account_status = 'OPEN' ORDER BY 1;
17. Are case sensitive passwords enabled?	SELECT name, value FROM gv$parameter WHERE (name LIKE '%sensitive%');
18. Is FORCE LOGGING enabled?	SELECT force_logging FROM v$database;
19. Is ADVANCED REWRITE in use?	SELECT owner, name FROM dba_rewrite_equivalences;

Oracle Initialization Parameters

Initialization Parameters: Oracle designed its database products to be flexible and configurable on a variety of hardware platforms and they succeeded. At present Oracle software can run on more than 70 platforms, from mainframes to home PC networks to PDAs. The secret of its success lies in the software initilization parameters. These parameters can be set and tune for top performance in countless environments. On the other side improper setting and can slow down and even halt the system.
Oracle has now divided the initialization parameters into two groups basic and advanced. In majority of cases, it is necessary to tune only the basic parameters of which there are 20 to 30 to get reasonable performance from the database. In rare cases modification of advanced parameters may required.
Below are the Basic Parameters in alphabetical order with their default value underlined and their breif description.
1. CLUSTER_DATABASE (TRUE/FALSE) boolean: It is a real application cluster RAC parameter that specifies whether or not RAC is enabled.
2. COMPATIBLE(9.2.0 to Default release) integer: It allows you to use a new release at the same time guaranteeing backward compatibility with an earlier release.
3. CONTROL_FILES(OS dependent , 1 to 8 filenames) string: Every database has a controlfile which contains entries that describe the structure of the database. We can specify one or more filenames seperated with a comma.
4. DB_BLOCK_SIZE(8192, 2048 to 32768 depending on OS) integer: It determines the size of the blocks at the time of creation of a database. The value of this parameter must be a multiple of the phyisical block size at device level.
5. DB_CREATE_FILE_DEST(no default value, directory) string: Specifies the default location for Oracle Managed datafiles and controlfiles.
6. DB_CREATE_ONLINE_LOG_DEST_n( no default value, directory) string: Specified the default location of oracle manged controlfiles and redo log files, where n=1, 2, 3, ....5.
7. DB_DOMAIN(no default value, any legal string of name components) string: In a distributed database system, this parameter specifies the logical location of the database within the network structure.
8. DB_NAME(no default value, any legal string) string:It specifies the database identifier of up to 8 characters. It must be specified and must correspond to the name specified in the create database statement.
9. DB_RECOVERY_FILE_DEST(no default value, directory) string: It specifies the default locatiton for flash recovery area(FRA). FRA contains multiplexed copies of contro files, redo logs, archived logs, flashback logs and RMAN backup.
10. DB_RECOVERY_FILE_DEST_SIZE(no default value, bytes)Big integer: It specifies the hard limit on the total space to be used by target database recover files created in flash recovery area.
11. DB_UNIQUE_NAME(db_name/+ASM, any legal string) string: It specifies a globally unique name for the database. Databases with the same db_name within the same db_domain must have a unique db_unique_name.
12. INSTANCE_NUMBER(lowest available no./0, 1-max instances specified during db creation stmt) Integer: It is a RAC parameter that can be specified in parallel mode or exclusive mode.
13. JOB_QUEUE_PROCESSES(0, 0 to 1000) Integer: It specifies the maximum number of processes that can be created for the execution of jobs.
14. LOG_ARCHIVE_DEST_n(no default value, any legal string) string: It is the attribute to specify where to archive redo data, where (n=1, 2, 3...10) destinations.
15. LOG_ARCHIVE_DEST_STATE_n(enable, alternate, reset, defer, enable) string: It specifies the availability state of the corresponding destination, where (n=1, 2, 3...10) for the LOG_ARCHIVE_DEST_n destination parameters.
16. NLS_LANGUAGE(OS dependent, language) string: It specifies the default language of the database.
17. NLS_TERRITORY(OS dependent, territory) string: It specified the name of the territory whose conventions are to be followed for day and week numbering.
18. OPEN_CURSORS(50, 0 to 65535) integer: It specifies the maximum number of open cursors(handles to private SQL areas) a session can have at once.
19. PGA_AGGREGATE_TARGET(10M or 20% of the size of SGA, whichever is greater, 10m-4096gb-1) big integer: It specifies the target aggregate PGA memory available to all server processes attached to the instance.
20. PROCESSES(40 to OS dependent, 6 to OS dependent) integer: It specifies the maximum number of OS user processes that can simultaneously connect to Oracle, including backgroun processes.
21. REMOTE_LISTENER(no default value, any legal string) string: It specifies a network name that resolves to an address or address list of Oracle Net remote listeners(i.e. listeners that are not running on the same machine as this instance).
22. REMOTE_LOGIN_PASSWORD_FILE(shared, none/shared/exclusive) string: It specifies whether oracle checks for a password file.
23. ROLLBACK_SEGMENTS(if not specified, instance uses public rbs, unless undo_management is set to auto) string: Allocates one or more rollback segments by name to this instance.
24. SESSIONS({derived :1.1*PROCESSES)+5} ) It specifies max no. of sessions that can be created in the system. The value should be your estimate of the max no. of concurrent users, plus no. of the backgroun processes, plus approximately 10% recursive sessions.
25. SGA_TARGET(0, OS dependent) big integer: It specifies the total size of all SGA components. If it is specified then following memory parameters are automatically sized.
a. Buffer Cache(DB_CACHE_SIZE)
b Shared Pool( SHARED_POOL_SIZE)
c Large Pool(LARGE_POOL_SIZE)
d Java Pool (JAVE_POOL_SIZE)
e Streams Pool(STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero then that value is used as min leves by ASMM.
26. SHARED_SERVERS(0, value should be less than max_shared_servers) integer: It specifies the number of server processes that you want to create when an instance is started.
27. UNDO_MANAGEMENT(manual, manual/auto) string: It specifies which undo space management mode the system should use.
28. UNDO_TABLESPACE(the first available undo tbs in the db, undo tbs name) string: It specifies the undo tablespace to be used when an instance starts up.

Oracle Database Structures

The Relational Data Model has three major aspects

1. Structures

2. Operations

3. Integrity Rules

Structures: These are well defined objects (tables) that store data of a database. Structure and the data within them can be manipulated by operations.

Operations: Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations must adhere to a pre-defined set of integrity rules.

Integrity Rules: Integrity rules are the laws that govern which operations are allowed on the data and structure of a database. Integrity rules protect data and the structure of a database.

An Oracle Database has both Logical and Physical Structure. (Fig#1 for reference)

Logical Database Structure: An Oracle database's logical structure is determined by one or more tablespaces, the database's schema objects (ex. tables, views, indexes, clusters, sequences, stored procedures, etc) The logical structures including tablespaces, segments, extents and blocks dictates how the physical space of a database is used. A tablespace can be associated with only one database.

Physical Database Structure: An Oracle database's physical structure is determinded by datafiles. One or more datafiles are grouped together to make a tablespace. A datafile can be associated with only one tablespace.

Although databases, tablespaces, datafiles and segments are closely related they have important difference. The differences are categorized below.

1. Databases & Tablespaces: An Oracle Database comprises of one or more logical storage untis called tablespaces. The database's data is collectively stored in the database's tablespaces.

2. Tablespaces & Datafiles: In Oracle Database each tablespace comprises of one or more physical operating system files called Datafiles. A tablespace's datafile physically stores the associated database data on disk.

3. Databases & Datafiles: A database's data is collectively stored in the datafiles that constitute each tablespace of the database.

The units of logical database allocations are data blocks, extents and segments.

(Fig#2 for reference)

1. Data Blocks: At the finest level of granularity, an Oracle Database's data is stored in data blocks, also known as logical blocks, oracle blocks, or pages.

2. Extents: An extent is a specific number of contigious data blocks that are allocated for storing a specific type of information.

3. Segments: A segment is a set of extents which have been allocated to for a specific type of data structure, and all are stored in the same tablespace. i.e. a segment can be associated with only one tablespace, though it can be spread over one of more datafiles.