The first enhancement is the ADDM (Automatic Diagnostic Monitor). Now in order to use ADDM, you must have an Oracle Diagnostic Pack license. Typically you use and access ADDM through your database control environment but you can also access it via packages and procedures. By default ADDM runs every hour. It is dependent upon the AWR (Automatic Workload Repository), which is like statspack on steroids and also runs every hour by default. You need an Oracle Diagnostic Pack license to look at AWR snapshots. If you want to look at an AWR report then just run awrrpt.sql which is located in your rdbms/admin directory. I’ll do a post about doing statspack in a previous post, but I only look at statspack for pre 10g databases. Also, there is a new background process, MMON, which attaches directly to the SGA and gathers statistics rather than querying the data dictionary. Back to ADDM. ADDM diagnoses many types of performance issues. Those include:

• Configuration issues
• Improper application usage
• Expensive SQL statements
• I/O performance issues
• Locking issues
• Excessive parsing
• CPU bottlenecks
• Undersized memory allocation
• Connection management issues

The thing with the ADDM is that is looks at actual DB time instead of a bunch of ratios. Where statspack can give you too much information, ADDM looks for items that exceed the threshold for database time. This gives you a much clearer picture into what is most likely causing you your performance issues. In order to use ADDM you must set TIMED_STATISTICS = TRUE and STATISTICS_LEVEL to TYPICAL or ALL. If STATISTICS_LEVEL is set to BASIC then you will have to manually run your AWR snapshot, which you can do by using the DBMS_WORKLOAD_REPOSITORY package. By default in 10g, this is already configured for you. You can also manually run an ADDM job in between snapshots if you choose. While you need a Diagnostic Pack license to use ADDM, if you have this setup at home and are using it for studying, I don’t think Oracle will have a problem with you analyzing, prototyping, and studying ADDM. To run an ADDM report, you can do it via Database Control / Grid Control or via command line by running @$ORACLE_HOME/rdbms/admin/addmrpt.sql from the sqlplus prompt. I would highly recommend the OCP Oracle Database 10g study guide by Sam Alapati for further detail on this topic and future ones in this post. This is merely a high level overview.

The next part to talk about is Automatic Shared Memory Management. The main idea behind this is that you set the SGA_MAX_SIZE and SGA_TARGET_SIZE and you leave the traditional SGA parameters like DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE empty. Oracle will then manage the SGA. This is supposed to make the DBAs life somewhat easier because the database should know where it needs resources and make adjustments accordingly. Imagine you have a database that is hit during the day with small OLTP transactions and at night there are some large batch jobs. The database will be able to dynamically make the adjustments that it needs in order to maintain efficiency. There is a new background process, MMAN, that dynamically makes these adjustments. The gotchas with ASMM is that it really only auto tunes 4 parameters. They are DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE. However, it takes SGA_TARGET and subtracts what you have given to other SGA parameters that it does not auto tune and then use that for these parameters. If you want to set a minimum for these though, you can. For example, if I set my DB_CACHE_SIZE to 300MB then ASMM will ensure that as it auto tunes it will not reduce the amount of memory for DB_CACHE_SIZE below 300MB. As you can see, this gives the DBA a lot of power to set thresholds that ASMM must work within in automatically tuning your database. One other thing to keep in mind is that if you want to use ASMM then you should use an SPFILE over a pfile for your init parameters. If you have an init.ora file, then login to Oracle and issue this command from sqlplus: create spfile from pfile. Automatic Optimizer Statistics is another area to be aware of on the 10g OCP exam. By Automating the collection of statistics you can ensure that your stats will be up to date. This is one of the most critical areas when it comes to database optimization. If the optimizer is aware of how many rows are in a table, the cardinality etc… then the optimizer can make an intelligent, accurate decision as to the best and quickest way to perform a query. Ensure that you are up to speed on this method or it will come back to bite you come exam time and in the real world. The main thing you need to know is that in order to to ensure that Automatic Optimizer Statistics is enabled you need to set the parameter to STATISTICS_LEVEL = Typical or All. Typical is the default setting in 10g. By setting it to ALL you can also get extra information in your AWR reports such as latch information. The name of the job that runs is called Gather_Stats_job. The job only collects statistics if there is no stats for an object or if they are stale. Oracle marks a tables stats as stale when about 10% of the rows have changed. The DBMS_STATS package is the preferred method for gathering database stats. In 10g, they give you the option of restoring historical optimizer statistics. If you have any user defined statistics or any statistics you gather with the analyze command, you cannot restore those using the DBMS_STATS package.

So how are statistics collected automatically? Oracle by default creates a job named ‘GATHER_STATS_JOB’ which is scheduled to run during the maintenance windows of 10pm-6am M-F and Sat from 12am to Mon 12am. This can be changed or the job can be disabled entirely. The job calls the DBMS_STATS.GATHER_DATABASE_STATS to gather the optimizer statistics. This proc collects stats on objects that either have no statistics or the statistics are stale. When tables are volatile, statistics can be locked. Simply use DBMS_STATS.LOCK_TABLE_STATS (‘SCHEMA’ , ‘TABLE_NAME’); DELETE_TABLE_STATS can be run prior if the DBA wants to force dynamic sampling at query runtime which is great for temp tables and staging tables etc…By default Oracle will also keep statistics on all objects for 31 days. In order to restore these statistics one can use the RESTORE_*_STATS procedure in the DBMS_STATS package.

Oracle now automatically tunes undo retention. This is also referred to rollback. Oracle needs undo for several reasons such as read consistency, rollback or undo uncommitted changes, aid in DB recovery, help facilitate flashback etc… ORA-1555 caused many headaches for Oracle DBAs. But instead of assigning individual undo segments, Oracle is now in charge of the undo segment. Oracle Automatic Undo Management (AUM) can be enabled by setting UNDO_MANAGEMENT to AUTO. It is recommending that the DBA create a separate undo tablespace, otherwise Oracle will use the system tablespace. Finally, by setting UNDO_RETENTION, the DBA specifies how long data should be retained in seconds — 900 is the default. Oracle also provides an undo-advisor which can be accessed easily from database control and grid control.Oracle will also alert you if your queries may result in ORA-1555 (retention time not long enough) and if your undo tablespace begins to run out of free space. Last but not least, if the DBA wants to guarantee retention, the he or she can enable the retention guarantee option on the tablespace. This is especially critical if you are using the flashback features in Oracle database 10g.

So what has changed?

First of all, the command is different. To invoke export or import you would just use exp or imp. With datapump it is similar but follows with ‘dp’ for data pump: expdp and impdp. Also, if you are using new features such as Transparent Data Encryption, the old exp utility will not export encrypted columns. Your only choice is to use datapump in place of it. Datapump is also much faster than export and import as well as more flexible. You can run multiple jobs as well as run single jobs in parallel. You can also attach from jobs and detach from jobs.  You cannot use datapump to import an export from the traditional utilities. You can still use imp in Oracle 10g.

What does the datapump architecture look like?

For starters when you invoke a datapump job it starts a process on the server. If you use export and import you would attach across the network to the database server but you would work with files that were local to your computer meaning that if you imported a file you did it from a local drive and if you exported a file you would do it to a local drive. This makes it more efficient from not going over the network. The one drawback is you do lose some flexibility but nothing that FTP or SCP can’t solve.

Datapump has an interactive mode just like the old utilities it replaces. The drawback of using interactive mode is you have a limited set of choices and you lose many options (but most people use interactive mode for simplicity anyways). With Datapump you can only backup to disk. In the Veritas Net Backup Agent for Oracle, there is some interesting information for taking datapump backup jobs and sending them across the network, but I believe that is external to Oracle, meaning Oracle is not aware of this, unlike RMAN being aware of tape backups because of the MML that links the libobk.so file.

For help on the command structure and parameters of datapump export and import you can issue: expdp help=y and impdp help=y. There are many options with datapump and to get the exact specifics I would recomment reading Sam Alapati’s Oracle 10g study guide and / or refer to the Oracle documentation. What I intend to go over are some of the highlights and strong points for the exam. (What I think you need to know).

Benefits of Datapump technology:

• Ability to restart data pump jobs
• Parallel execution capabilities
• Ability to attach to running jobs
• Database to Database direct transfer using DB Links and SQL Net
• Finegrained data import capability
• Remap schemas, tablespaces, and filenames
• Ability to estimate space requirements.

What does the datapump technology consist of?

1. The DBMS_DATA_PUMP package
2. The DBMS_METADATA package
3. expdp and impdp command line utilities.

There are two data access methods.

1. Direct Path
   • Part of the direct path API. Much faster load and dump times.
2. External Tables
   • Read and write data to OS files. This gives a lot of flexibility and I have seen many people use this in place of SQL Loader for getting data from those files inside the database.

With the Direct path option, you want to be aware of clustered tables, active triggers on tables, single partitions with global indexes, referential integrity constraints, domain indexes on LOB columns, fine grained access control enabled in the insert mode, and tables with BFILE or opaque column types.

WHEW, now I can breath. : )

Data pump creates a dump file which is a proprietary format file holds your data. You also have the option of log files and SQL Files which will give you all the DDL statements it will use for importing. You specify this by setting the SQLFILE parameter. You will also have to setup Directory objects. Once you identify this, you have to grant a user read and write access to the directory. The oracle user or whoever the DB runs as also needs permissions to this directory on the operating system.

A simple datapump export command will look something like this:

expdp / directory=data_pump_dir dumpfile=dumpfilename.dmp

You also have the option of using a parameter file much in the same way you did in the original imp/exp commands. It would look something like this for datapump:

expdp parfile=myparfile.par

You can decide to do interactive data pump if you would like but the thing to remember is it will not give you as much control or as many option as explicitly defining everything in the command line.

There are 4 types of exports that you can do.

1. Full export mode
2. Schema mode
3. Tablespace mode
4. Table mode

In data pump there are so many options that I am not going to go through them here. I would recommend Sam Alapati’s book or going directly to the Oracle documentation. I would also go ahead and play with it and try some of the things out in order to get comfortable with it. I will also talk about some of the import functionality but also refer to the documentation to find all the answer’s and to get some experience with it.

Data pump import lets you manipulate your data in many way.

• Filtering (Include or Exclude items even based on a Query)
• Remap Schema
• Remap Tablespace
• Remap Datafiles
• Transform (ability to exclude certain attributes like storage and tablespace clauses, modify the DDL)
• Flashback Time (import data consistent to a flashback time – some restrictions)

If you want to view data pump jobs in the data dictionary to be able to attach to them using either expdp or impdp, it is DBA_DATAPUMP_JOBS.

External Tables is another thing that has been enhanced in 10g. External Tables used to have the restriction of being read only. You now have the ability to read and write to external tables. There are two types. The Oracle_loader type and the Oracle_datapump type. The Oracle_loader is the traditional and default. It will not write to an external table but the new Oracle_datapump will. The reason is because they use 2 different access drivers. Datapump does write it’s external tables using a proprietary format. Another restriction on external tables is that you cannot index them or use DML against them. You can however use them on another database if you’d like. You can also populate external tables utilizing parallel operations.

Transportable tablespaces is the last item I will cover. You use datapump to export the metadata and import it into the new database. If you want to transport the tablespace across platforms then you will have to make sure the endianess matches or modify the file. You can query the v$transportable_platform view to see which endian formats are used on which platform. If you have to modify the tablespace, then you can use RMAN on the source or destination machine to do so. Some other restrictions are the tablespace must be in read only mode prior to exporting metadata and copying the datafiles and the compatibaility level on both databases must be at 10.0.0 or higher.

To install on Windows, it is pretty straight forward. You pop in the disk and go. With unix / linux / solaris you have to use XWindows. At home, many people just run the GUI that is installed with the Linux installation, but in most production environments the GUI and XWindows are not run on the server as to not impact performance. What you have to do is run an XWindows server such as exceed or cygwin on your machine if it is Linux, you can just use that.

At work I have a Windows XP laptop with Cygwin (free GUI), and I have a Linux laptop running Ubuntu. I have the Oracle Client installed on both (10.2.0.3) and SQL Developer (free IDE). Both work great. I’m not going to get into the actual XWindows and how to use it, but you can find it in Oracle’s installation documentation quite easily.

So what has changed with the 10g installation?

The good news about the Oracle installation is that is does a precheck to ensure that that your system meets all the system requirements. If you are installing on an “unsupported” Linux such as Ubuntu, you will need to run the installer with the -ignoresysprereqs flag. For example, ./runInstaller -ignoresysprereqs which will skip the check. With Ubuntu, you will have to do a few things to make DB or client installations work but it is not something you need to know for the exam, so it will be in another post. The main benefit of the pre-installation check is that it verifies the operationg system, version, OS patches and packages, kernel or OS parameters, if the Oracle Home is empty, and that you have sufficient memory and file system space to do the install.

Well for starters, you have 2 different options for your management choices. You can use the default OEM Database Control (local management) or you can choose the OEM Grid Control which is a separate install. If you don’t choose the default local Database Control option then you will need to install OEM Grid Control on a separate set of servers. On top of that, you will then need to install an Agent on every host you want to monitor regardless of the number of databases or homes on that machine. The Agent then feeds monitoring information back to the Grid Control servers. Both the OEM Grid Control and local OEM Database Control are html based. That means, you can access either from a Web Browser. On some further posts later, I will get into OEM Grid Control installation, troubleshooting, and use. I will even show some screen shots but for the 10g OCP, the local Database Control that comes with the standard default installation will suffice. To find which port your local OEM database control runs on, type emctl status dbconsole from the command line. It will give you a port number. Then to access, just enter http://hostname:<port#>/em to access it. You can use your database id, you can use sys or system, and you can even use sysman once you unlock that user acccount. If you did not select the local OEM Database Control and want to install it after the fact for your database, you need to use the emca or emca.bat script to install it. If it is installed and not running, you can enter emctl start dbconsole (emctl stop dbconsole to stop it). Make sure you ORACLE_HOME is set before you do this and that the ORACLE_HOME/bin is in your path.

You are also prompted for your backup options. It will only let you use this if you have chosen the default local OEM Database Control.

File storage options gives you the choice of ASM (Automated Storage Management), Raw Devices, and File Systems. I would recommend either ASM (for it’s dynamic, pooling, load balancing capabilities) or File System, for the flexibility that it gives you. I would be willing to be that 95% of installations out there right now use filesystem but there is a huge push for us internally to go to ASM because of the performance gains we have seen.

With Oracle Cluster Ready Services you can start the install from one node in the cluster and have it install on all nodes in the cluster. How does it do this? Before you setup CRS, you have to do a bunch of things but basically something called user equivalence is enabled which allows the oracle user to ssh across the interconnect to any machine in the node. You then have the /etc/hosts file setup where it knows where the other nodes are. It is then able to copy the install files to the other servers and do the install behind the scenes. I plan on doing some interesting posts on doing a RAC install on a machine that has 2GB of memory with VMWare and shared VMWare disks. It will be something you all can build at home and play with, however, it is not a requirement for the OGP upgrade exam. Just know it is an option. You install CRS before you install RAC.

Oracle has also given you the option of giving it your metalink credentials and then being able to check for any patches or updates and notify you about them. You have to have either Grid Control or Database Control to use this feature. I’ve played with it and will blog about it. I still have a knack for doing this the manual way of downloading the file and applying the patch/update.

The requirements are 256MB for the DB and 512 if you want the DB Control. You would only install on this kind of memory if playing at home and learning. If you are using this for production, you will obviously want a bigger box : ) The space requirements are 2.5GB for the installation, 1GB of swap space, and 400MB in /tmp.

The deinstallation is also much cleaner. Before you had to clean things out, but now it does a much better job of removing everything. Before doing an uninstall, make sure you shutdown all databases, your database control, and your listeners.

The response file is another option. It allows you to perform installation silently and you do not need an xserver…. really : )

DBCA also has some enhancements. What you will notice is one of the options is to create an ASM instance. This is where you use either raw disks / devices or disks controlled by ASMLIB if on Linux. It will enable you to create tablespaces and have your new databases use ASM for the file options. The SYSAUX tablespace is now a new requirement in 10g. The SYSAUX tablespace is where Oracle stores most of it’s tools. By default, DB Control, AWR, ADDM, RMAN etc gets consolidated in this tablespace now instead of the SYSTEM tablespace. This makes it much easier to manage.

Another option is the FLASH RECOVERY AREA. How are we using it? Well if you don’t specify backup locations or places to put your archive logs, they will go here. We are sizing our Flash Recovery Area at 125% of the size for our database. We then have all archive logs go here as well as our backups. It can be file system or another ASM diskgroup (could be the same but not recommended). We are actually putting 1 uncompressed level 0 and then putting uncompressed level 1′s each night using a blok changing file. We then merge the level 1 with the level 0 to create a new level 0 and then ship that off to tape.  We then run backup validate database to check for corruption. Not needed for OCP but is something I will write about when I finish the OCP and get into many of the things I am doing at work. To query information about the flash recovery area, you can do a describe on the V$FLASH_RECOVERY_FILE_DEST view.

In older versions you could set the compatibility setting to a higher version and then go back by using alter database reset compatibility. This is now deprecated and you are unable to do this in 10g. You cannot go back once going forward.

In 10g you can also track database usage statistics. The view is DBA_FEATURE_USAGE_STATISTICS. It will tell you how you are using a feature, if it is currently being used, as well as the first and last time it was used. You can also view the HWM of the feature. The features you can track are Advanced Replication, Oracle Streams, Virtual Private Database etc… You can also track the HWM for things like tables, datafiles, session, and segments.

So what collects all these statisics?

You guessed it! (maybe you didn’t) Oracle has a new process called MMON. So what does that mean? It’s called the Manageability Monitor Process. If you do a ps -ef in Unix, Linux, or Solaris, you will see it. With Windows you won’t, because Windows uses threads and you would need something to peak inside the process to see the MMON thread. You can also use the Register_DB_Feature and Register_High_Water_Mark procedures in the new DBMS_FEATURE_USAGE package to track usage. You can also see these inside of OEM (Grid Control or DB Control).

So what are the supported upgrade paths?

Anything under 8.1.6 must be upgraded to at least 8.1.7 with the exception of 8.0.6 before upgrading to 10g. All of 9i can go straight to 10g as well.

Upgrading?

It used to be that the DBA had to run a bunch of scripts and follow a bunch of manual steps in order to upgrade the database. Oracle also has the DBUA – Database Upgrade Assistant. This enables you to startup a GUI and it will connect to the database and run the scripts in the proper order and issue the proper commands to upgrade your database. I have used this a few times and it worked really well.

Oracle also includes an Upgrade Information Tool to help the DBA collect information before upgrading. It does a few things. It makes sure you have a SYSAUX tablespace. The redo logs must be at leats 4MB in size. Tablespace sizes are checked. Init parameters are checks. Versions and compatibilities are checked. It will also tell you how long it things the upgrade will take. To invoke it, access utlu101i.sql.

There is also a new post ugprade tool which is script utlu101s.sql. You can also query the DBA_SERVER_REGISTRY view to see if the proper components were upgraded and if the status is VALID or INVALID.

This was chapter 1 in Sam Alapti’s book. It was actually 49 pages but these are my notes. I would highly recommend you go and buy his book online or from your local bookstore

My goal is to get 5 posts out this week about the 10g DBA OCP upgrade exam.

So what am I using to study for the exam? Well this will be the first post: Study Material!

The Oracle Education site explains what all the requirements of the exam are as well as where you can get some training and study material. Since I am self-studying, I have decided to just use the exam topics to guide me. To view those topics, click here.

Next, we can discuss where you can learn about those topics. One site that anyone working with Oracle cannot live without is the Oracle Documentation. The other website that Oracle provides is OTN (Oracle Technology Network). There are several books you can buy. I have chosen the OCP Oracle Database 10g: New Features for Administrators Exam Guide by Sam Alapati. So far, it looks like an excellent read. I will write a review on here and also post it at Amazon.com and our user group site SOUG.org. It looks like Sybex also has a book and Cramsession has a free studyguide to help you as well. As with any exam, do not depend on any one source for your study material. Chances are, they will miss something.

What I plan on blogging about next is Installation and New Feature Support for the 10g OCP. My plan is to have about 15-20 posts on the 10g OCP and my exam experience. I look forward to blogging about it and hope that this pushes me to get this done! : )