Tek Talkin

I have moved my blogging (and some older posts) over to blogger.com

http://tektalkin.blogspot.com/

the RSS Feed is here:

http://feeds.feedburner.com/blogspot/TekTalkin

-Steve

I’ve finally finished the eBook version of my Oracle Certified Associate Exam Cram / Study Guide document. It is 66 pages long and available to download for free from here. Renowned Oracle guru, consultant and author Jonathan Lewis gave up his time to help with corrections and the technical editing.

Creating this document took up far more time than I envisaged, and was far more difficult than I expected ! I hope it is of use to someone :-) The last couple of sections were the hardest as I was busy with two commercial programs that I am currently creating.


Software Development * Security * Database * Training
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"Trouble is only opportunity in work clothes." - Henry J Kaiser

Oracle 10g Database Admin 1 Exam Cram (1Z0-042)
Assorted Facts and Figures

In this the final part of my OCA Study Guide, I have gathered together an assortment of facts and figures that you will need to be aware of to pass the OCA Admin 1 examination.

All committed transactions are guaranteed to be stored in a physical file within the Redo Logs.

Segments
Extents
Database Blocks
Operating System Blocks

QUERY: SELECT
DML: INSERT, UPDATE, DELETE
DDL: CREATE, ALTER, RENAME
DCL: GRANT, REVOKE
        
The USER PROCESS initiates a CONNECTION to the INSTANCE. When connected a SESSION is established in the instance. A SERVER PROCESS is then started on the HOST.

A PGA (Program Global Area) is created for each user. The PGA contains session specific information.

A transaction begins with the first DML commit issued and finishes with a COMMIT or ROLLBACK.

DDL forces an implicit COMMIT.

Abnormal DB connection terminations can result in an implicit ROLLBACK.

Before a DML process is completed, Undo is written to the UNDO SEGMENTS.

UNDO_MANAGEMENT (Auto / Manual) cannot be changed without restarting the Instance.

SQL>alter database backup control file to trace;

The file that results from the above command contains the SQL necessary to recreate the Control File.

A Cartesian Product is the result of joining every row in one table to every row in another table. Usually an undesirable result.

From the Oracle SQL tools the describe command will show the structure of a table, and is used in the following manner:



SMON performs Instance Recovery.

Redo Log Files are usually named in the following fashion:

Redo2c.Log

The ‘2’ means that this file (member) belongs to Redo Log Group Number 2 and the ‘c’ indicates that this is the 3rd of the multiplexed files within this group.

Changes to a ROLE will be available immediately.

To enable ARCHIVELOG:
Database to MOUNT state
ALTER DATABASE ARCHIVELOG

After the loss a CONTROL FILE the database will NOT open

The Oracle Enterprise Management Framework, consists of:
Service
Agent
Repository

The Agent communicates with the Service to populate the Repository.

emctl Default Port: 5500
isqlplus Default Port: 5560


In Oracle 10g the Service Name is usually the same as the Global Database Name.

Key Alert Log Entries:
A Control File is missing
A Datafile is missing
A Redo Member is missing
The SYSTEM tablespace is being used as TEMPORARY space
Internal Errors or Block Corruption
Deadlocks

When using DBCA to create a DB all accounts are locked except:
SYS
SYSTEM
SYSMAN
DBSNMP

AWR Statistics are viewed via: DBMS_WORKLOAD_REPOSITORY

V$SGASTAT shows memory used



SAME: Stripe and Mirror Everything

$ORACLE_HOME - The location of the Oracle Software

If the DBA kills a session, this is not classed as a failure.

A server process is not considered part of the instance.

If running in NOARCHIVELOG mode, you only valid backup options are OFFLINE, FULL backups of everything.

Valid DB States:
SHUTDOWN
OPEN
MOUNT
NOMOUNT


Software Development * Security * Database * Training
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"I was brought up to believe that the only thing worth doing was to add to the sum of accurate information in the world." - Margaret Mead

Oracle 10g Database Admin 1 Exam Cram (1Z0-042)
Database Recovery



A new feature available in Oracle 10g is Oracle Flash Recovery. Flash Recovery is designed to make it easier and more straightforward to perform Oracle Backups and Recovery by providing a Centralised Location for all the files related to your database backup.

The Flash recovery Area is an area of a disk, or a separate disk, that is dedicated to the storage of database backup files and recovery information. It is held completely separate to all other areas of the Oracle database.

Single area for all backup related files
Control File
Archived Log Files
Flashback Logs
SPFILE Backups
Datafile Backup Sets
Datafile Image Copies

The Flash Recovery Area is used in the following ways:

Enterprise Manager can stores its backups here
Oracle can store the Archived Redo Logs here
RMAN can store and maintain details of it’s files here

In short: All files necessary to recover the database in the unlikely event of a media failure are stored as part of the Flash Recovery Area.

The default location for the Flash Recovery Area is ORACLE_BASE/flash_recovery_area. The size of this area defaults to 2GB but can be altered as necessary.

DB_RECOVERY_FILE_DEST_SIZE
DB_RECOVERY_FILE_DEST

The above parameters are dynamic.


Oracle Flashback Query allows users to look backwards in time and see a consistent view of the database as it was sometime in the past. This facility is only relevant if your server is configured for Automatic Undo Management. How far backwards you can see is determined by the UNDO_RETENTION parameter (in seconds) which can be configured in the init.ora file or alternatively like this:

alter system set undo_retention = 600;

This facility is mainly used for recovering from accidental updates and deletes as it is not practical to set the UNDO_RETENTION to an extended period. Nevertheless it is a potentially very powerful facility.

select * from customer as of timestamp to_timestamp(‘30/04/2008 11:00:00’,         ‘DD/MM/YYYY HH24:MI:SS’);

flashback table suppliers to before drop;

flashback database to timestamp sysdate-(1/24);

Only Non System, Locally Managed tables have a recycle bin.


Types of Failure

Statement         - DML
User Process - Single DB Connection Fails
Network - Network Component - Session Fails
User Error - No Error Message - Unintended Action
Instance - Instance Fails Unexpectedly
Media - One of More DB Files, Lost Deleted or Corrupted

V$RECOVER_FILE will show all files needing attention. Join with V$DATAFILE to show the actual file names.

Instance Recovery is automatic:
        Roll Forward the REDO
        Roll Back the UNDO

Transactions not yet written to the DB have a SCN (System Change Number) higher than the last Checkpoint.


It is possible to set a parameter that is the goal for MTTR (Mean Time To Recover) the parameter: FAST_START_MTTR_TARGET is set in seconds and can be set with any value from 0 to 3600 (one hour).

alter system set fast_start_mttr_target = 60 scope = both;


Logminer

Logminer allows you to query the REDO logs via a GUI interface or via SQL. It is a sophisticated tool for allowing system admins to look back at changes to the database. Use V$LOGMNR_CONTENTS to view the changes and access statements to undo changes made.


Losing ANY Control File will abort the Database Instance.


If just one member of the REDO group is available, then the instance will start. Here are the possible statuses of the REDO group members:

INVALID - Corrupt / Missing
STALE - New, Unused
DELETED - No Longer Used
‘blank’ - In Use

If you have an INVALID member of a REDO log group:

Archive the group:        alter system archive logfile group 4;
Clear the group:        alter database clear logfile group 4;


If you lose system critical datafiles in NOARCHIVELOG mode, then you need to restore everything including the Control Files from a backup.

If you lose system critical datafiles in ARCHIVELOG mode:
DB to MOUNT state
Perform Recovery
Open DB

If you lose non system critical datafiles in ARCHIVELOG mode you can recover while the database remains open.


Software Development * Security * Database * Training
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"One must have a good memory to be able to keep the promises one makes." - Friedrich Nietzsche

Oracle 10g Database Admin 1 Exam Cram (1Z0-042)
Database Backups



The Control File is a metadata repository for the physical database. It is absolutely essential to the operation of your database. The Control File contains the following information:

Database Name
Database ID
Creation Time Stamp
Name, Location, Online/Offline Status of Tables
Current Online Redo Log
Current Archive Log Mode
Archive Log History
Tablespace Names
Log Records
Checkpoint Information
Database Block Corruption History
RMAN Catalog

The Control Files are usually setup in a multiplexed fashion. 8 is the maximum number of multiplexed Control Files supported by Oracle. An important note to consider is that if *any* of the Control Files are unavailable, the Database will not continue to operate.

Following any change to the database you should back the Control Files. You can backup using standard O/S commands or alternatively:

alter database backup controlfile to filename;



At every checkpoint (CKPT) the Control File is updated (as well as the datafile headers) with the System Change Number (SCN).

A CKPT is forced by a Redo Log Switch. The Redo Logs contain Change Vectors. The Log Writer (LGWR) writes Redo from the Redo Log Buffer to the online Redo Log files. LGWR activates in the following circumstances:

The user Commits a transaction
The Redo Log Buffer is one third full.
The Redo Buffer contains approximately 1Mb of changed records.

Redo Log File Status:

CURRENT: Being Written To
ACTIVE: Needed for Instance Recovery
INACTIVE: Others

Size the Redo Logs to avoid over frequent log switches.

It is extremely advisable to locate the Redo Logs on seperate disks to the main database, this helps avoid contention on the disks.

When designing your Redo Log structure, be generous with the number of Groups and the number of Members per Group. Make sure you have at the very least 2 Groups each with 2 Members.

If LGWR can only write to one member of the group the database will stay up and the Alert Log will be populated with details of the problem.

Key Parameters:
MAXLOGFILES
MAXLOGMEMBERS

Examples of working with Redo Log Groups:

alter database add logfile group 5
(‘c:\oracle\redo\log5a.rdo’ , ‘c:\oracle\redo\log5b.rdo’) size 1024K;

alter database drop logfile group 5;

Examples of working with Redo Log Members:

alter database add logfile member
‘c:\oracle\redo\log5c.rdo’ to group 5;

alter database drop logfile member
‘c:\oracle\redo\log5c.rdo’;


The Archived Redo Logs are the ultimate protection against data loss. To use this feature your database has to be running in ARCHIVELOG mode. All production databases should be running in ARCHIVELOG mode.

The location for the Archive Logs is set with the following parameter:

LOG_ARCHIVE_DEST

The format of the Archive Log files is made up of the following parameters:

%s - Log Sequence Number
%t - Thread Number
%r - Resetlogs (guarantee uniqueness)
%d - Database ID

Changing to (or from) ARCHIVELOG mode is made with database at MOUNT stage.

SQL>shutdown immediate
c:\sqlplus /nolog
SQL>connect / as sysdba
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;



log_mode from V$DATABASE shows the current Archivelog mode.



Loss of an entire Redo Log Group can lose committed transactions.

DB_RECOVERY_FILE_DEST points to the Flash Memory Area, which is the default area for Archive Log Destination 10.


Oracle backup strategies are:

Whole
Partial

Backup Types are:

Full backups
Partial Backups

Backup Modes:

Consistent - (Database Not Open)        
Inconsistent - (Database Online)

Incremental backups are only available using RMAN.

Online backups are rarely, if ever, in sync with the Control File.


RMAN supports two types of backups:

Image Copies - (full duplicates, only to disk)                
Backup Sets - (proprietary RMAN, used blocks only, disk or tape)

Oracle Recommended Backup Strategy:

RMAN
One Time / Whole Database
Weekly: Level 0 Baseline Incremental
Daily: Level 1 Incremental

If Necessary:

Daily: Lunchtimes, Level 2 Incremental

Below you can see the full input to, and output generated by doing an RMAN backup.



You can also use an incremental backup to update the blocks within an Image Backup. Restore then involves just restoring the Image.

RMAN Options

Catalog
Adds external backups created from outside of RMAN

Crosscheck All
Makes sure all necessary files are available

Delete All Obsolete
Deletes files that are outside of the current retention policy

Delete All Expired
Delete files not found when Crosscheck is done


Software Development * Security * Database * Training
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"Choose the life that is most useful, and habit will make it the most agreeable." - Sir Francis Bacon

Oracle 10g Database Admin 1 Exam Cram (1Z0-042)
Proactive Maintenance and Performance Monitoring



Oracle 10g makes extensive use of cost based optimiser statistics to provide proactive monitoring of your database.

Two key technologies involved are:
AWR (Automatic Workload Repository)
ADDR (Automated Database Diagnostic Monitoring)


AWR

MMON - Memory Monitor
MMNL - Memory Monitor Light

These processes collect performance statistics from the SGA.

SYSMAN owns the AWR tables, they are stored in the SYSAUX tablespace.

To activate AWR set the STATISTICS_LEVEL parameter:

STATISTICS_LEVEL = BASIC
STATISTICS_LEVEL = TYPICAL
STATISTICS_LEVEL = ALL

AWR statistics are stored by default for 7 days. This is altered by DBMS_WORKLOAD_REPOSITORY. The interval and Retention are stored in minutes.


ADDM

ADDM uses the statistics that are collected by the AWR.
ADDM analyses the current statistics and compares them to the previous 2 collections.
        
CPU and I/O Bottlenecks
Resource Intensive SQL
Lock Contentions
Etc.

ADDM may make recommendations to minimise DBTIME.
DBTIME is a calculation based on CPU TIME and WAIT TIME.
A high DBTIME is a bad thing !
Enterprise Manager is the preferred method for viewing ADDM results.
You can also use the addmrpt.sql script to look at the ADDM output.
You can view ADDM results between user define periods.

There are 20+ Data Dictionary views associated with ADDM, listed below are some of them:

DBA_ADVISOR_FINDINGS
DBA_ADVISOR_OBJECTS
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_RATIONALE
DBA_ADVISOR_ACTIONS

ADDM features 4 default alerts, which are sent through to Enterprise Manger:

Tablespace Usage <15% and <3%
Snapshot Too Old
Recovery Area Low on Space
Resumable Session Suspended


ADDM Diagnostic Advisors

SQL Tuning Advisor
SQL Access Advisor
Memory Advisor
Mean Time to Recover (MTTR) Advisor
Segment Advisor
Undo Management Advisor
        
These are all accessed from Advisor Central in the Enterprise Manager.

The SQL Tuning Advisor is used to help minimise DBTIME.

The SQL Access Advisor is used to help find schema modifications to reduce I/O, CPU and WAIT TIME.

The Memory Advisor gives global recommendations about the memory status of Oracle, the SGA, PGA, Shared Pool, Large Pool, Buffer Cache and Java Pool.

The MTTR Advisor helps to minimise recovery time.

The Segment Advisor advises on segments than you can shrink.

The Undo Management Advisor can help minimise I/O and provide consistent queries.


Additional Notes

When performance tuning the type of tuning you do depends on the purpose and use of the database:
        Transactional - Tune for Throughput
        DSS (Decision Support System) - Tune for Response Time Minimisation


Software Development * Security * Database * Training
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"There is an applause superior to that of the multitudes: one's own." - Elizabeth Elton Smith

I had a call off a customer recently. They were having access problems to their old legacy accounting system and wondered if I could help. On the surface this was simple, I retrieved a virtual machine from archive which contained their legacy database and created a query to extract the data they required. Unfortunately there were over half a million rows of data, any of which they may need to view. As they are currently still using Office 2003, Excel would not be able to load the data as there were too many rows.

I tried extracting the data to an html file and displaying it with Internet Explorer and then Word. They just died. At this point I remembered a quick utility I wrote a few months ago designed to quickly load and view the contents of a CSV or Tab Delimited file. I opened the utility and loaded the data, perfect. One of their requirements however was to search for the data in any of the rows or columns of the extract, and there was no search facility in my program.

Less than an hour later the search facility was added and would now find, highlight and scroll to the lines that had a matching, or partially matching, entry in any of the columns. Problem solved and another happy customer :-)

The upgraded version (Windows / MacOSX / Linux) of ViewCSVTAB is available for free download here. A screenshot is shown below:



Software Development * Security * Database * Training
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"Always be nice to people on the way up; because you'll meet the same people on the way down." - Wilson Mizner

Oracle 10g Database Admin 1 Exam Cram (1Z0-042)
Consistency and Concurrency



UNDO SEGMENTS are what used to be known as ROLLBACK SEGMENTS.

UNDO gives the ability for Read Consistent Queries, it enables ROLLBACK, Flashback Query and also enables Database Recovery.

UNDO is stored within the Undo Tablespace, only one Undo Tablespace can be in use at any one time.

Undo Segments grow and shrink as needed and they wrap around to the first segment when full as long as the first segment is not in use by an active transaction.

The view V$ROLLNAME shows the Active Undo Segments.



DBA_ROLLBACK_SEGS shows both the Active and Inactive Segments.

V$TRANSACTION shows the active transactions and their Undo details.

To recover an Undo Tablespace the database must be in MOUNT state.

For recovery purposes, REDO brings both committed and uncommitted transactions forward to the point of instance failure, while UNDO rolls back the uncommitted transactions.

Undo Parameters:
        UNDO_MANAGEMENT
        UNDO_TABLESPACE
        UNDO_RETENTION

RETENTION_GUARANTEE guarantees long running queries will be OK at the expense of any DML transaction - even if this causes the DML to fail ...


UNDO_MANAGEMENT

Manual UNDO_MANAGEMENT is the default with Oracle 10g, however it is not recommended !

        MANUAL        (Using Rollback Segments)
        AUTO        (Using Undo Tablespace)

When changing between MANUAL and AUTO the instance must be restarted for the changes to be effected.

UNDO_TABLESPACE

Default:        SYS_UNDOTBS
View with:        show parameter undo_tablespace;

UNDO_RETENTION

Parameter is in seconds.
Setting to Zero turns on Automatic Undo Retention Tuning. (Attempts to keep 15 minutes (900 Seconds) of Undo)
        show parameter undo_retention;
        alter system set undo_retention = 43200;



Uncommitted Undo Information - Never Overwritten
Committed Undo Information - Can be Overwritten (still needed for UNDO_RETENTION interval)
Expired Undo Information - Can be Overwritten

V$UNDOSTAT takes 10 minute snapshots to calculate the undo rate and make recommendations.

ORA-01555 Snapshot Too Old: You will only receive one of these error notifications every 24 hours, despite the fact that many of these errors may be occurring ...

RETENTION_GUARANTEE can only be setup from the command line:

        alter tablespace undotbs retention guarantee;

This is usually used in Data Warehouse type scenarios.

        alter tablespace undotbs retention noguarantee;

Retention only works for UNDO Tablespaces.


Locks and Transactions

COMMIT / ROLLBACK releases locks.
Queries *never* lock (they use UNDO).
First user gets the lock, next user queues, FIFO.
Subsequent users appear to hang unless using NOWAIT.
Lock one row, multiple rows, entire table.
Possible to manually lock rows, leave it to Oracle is recommended.

        lock table mytable in exclusive mode;
        (only queries allowed on mytable now)


Table Locks

Row Share
Allows connect access to the whole table, stops users locking the entire table for EXCLUSIVE.

Row Exclusive
As Row Share, but stops other users locking in SHARE mode
(used by DML such as INSERT, UPDATE and DELETE)

Share
Permits concurrent queries but prevents any updates to the table.
(used by CREATE INDEX)

Share Row Exclusive
To query the whole table, and allow others to do so, but prevent them locking the table in SHARE mode or doing any updates.

Exclusive
Permits queries, prevents any DML.
(used by DROP TABLE)


You can obtain explicit locks on individual rows using the SELECT ... FOR UPDATE statement.

NOWAIT returns immediately with ORA-00054.

Views for Locks:
        V$SESSION
        V$TRANSACTION
        V$LOCK
        V$LOCKED_OBJECT
        
As an alternative to the views, locks can be monitored via the Enterprise Manager.

A deadlock refers to a specific condition when two or more processes are each waiting for another to release a resource, or more than two processes are waiting for resources in a circular chain. (source: Wikipedia)

The session that first detects a deadlock rolls back with ORA-00060.

Database * Software Development * Security
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"No trumpets sound when the important decisions of our life are made. Destiny is made known silently." - Agnes de Mille

The Oracle Instant Client has now arrived for MacOSX Intel. Although I couldn’t find any mention of it on the Oracle site, it does appear to be OSX Leopard only. So Tiger users are out of luck unfortunately. You can download the client, free of charge, here, and below you can see a successful SQLPlus connection from my OSX Terminal :-)



There is a couple of hoops to jump through to get it running, but it’s really quite straightforward. I will blog the steps required if anybody requests. Interestingly it supports databases from 8i onwards, which is good news for those who have legacy databases to deal with.

What this long awaited download gives us is native, direct access from our Mac applications on our Intel boxes, no need for ODBC drivers any more :-) I’m looking forward to giving it a bit of a workout, unfortunately I have some Windows C# programming and a couple of other things to finish first :-(

Database * Software Development * Security
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"Do definite good; first of all to yourself, then to definite persons." - John Lancaster Spalding

Bigtime is a (very) simple utility that displays the time in the format of a 24 hour digital clock.



The screenshot above is from the MacOSX version and the screenshot below is from the Windows version running on 2003 Server.



Bigtime runs on top of all other windows on your screen. Bigtime remembers it's position on the screen when the application is closed by writing to the Preferences.txt file. This file is stored in the same directory as the application itself.

Bigtime for MacOSX, Windows and Linux can be downloaded here.

Database * Software Development * Security
Technology Consultancy from GENeSYS Solutions
www.genesyssolutions.co.uk

"It's not foresight or hindsight we need. We need sight, plain and simple. We need to see what is right in front of us." - Anonymous