Archive for July, 2011

How big is my oracle database?

Thursday, July 28th, 2011 by Matt Canning

People ask this question a lot on the internet and most of the answers just seem to focus on a query of dba_data_files, similar to that shown below:

select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;
That’s fine to start with, but you should also include tempfiles, which are used when an operation such as a large sort is too big to fit into the relevant memory allocated to the session.
select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;

Your redo logs can also use up a large amount of disk space - especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).

select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database.  If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time. 

Flash Recovery Area
select * from v$recovery_file_dest; 
select * from v$flash_recovery_area_usage;  

These views will show sizing details and free space available. 

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).

If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file
select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;

Files referenced by database directories or the utl_file_dir parameter
Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables -

select a.owner||’.'||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;
(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;

Miscellaneous files
There are a large number of files which you could also include in your sizing if you wanted to. Though most of these are really external to the database.
Examples include:

(a) The spfile/pfile and any ifile referenced files.
(b) Any external scheduler jobs (i.e. program_type=’EXECUTABLE’ and program_action which points to a shell script).
(c) Configuration files such as Oracle wallet files and database gateway/hs services files.                   

(d) Oracle networking files. (e.g. tnsnames.ora, sqlnet.ora, listener.ora)
(e) Passwordfile.
(f) Any application code that needs to be deployed to the database server.
(g) Any database management or monitoring scripts that need to be on the server. 
(h) Files referenced by the audit_file_dest parameter, if audit_trail is set to use the “OS” or “XML” options.
(i) Archived redo logs and standby redo logs. Be aware of space usage related to the workload of your database and whether or not you have multiple destinations defined.
(j) Any software that needs to be deployed to the server. (e.g. The oracle software itself takes up several gigabytes of space).

If you wanted to be very precise, you could remove unused space from the calculations above, but I haven’t done that here, in order to keep things more straightforward. In real-life you would probably be better leaving the extra space available to allow for future growth of the database - which you should also plan into your size calculations.

As you can see, calculating size requirements for an Oracle database is not always as simple as you would think.  I’ve tried to include all elements in this post, though in reality a lot of them won’t apply to most databases. Please feel free to share any other items not on the list, that you feel should be included.

Database backup checks - what you could be missing

Tuesday, July 19th, 2011 by Matt Canning

If your backup scripts have been in use for several years, it is easy to become relaxed about them. You just assume that because the scripts have been around for a long time they must be fine.

Whilst in many cases this may be true, it’s always a good idea to check your scripts from time to time.  If you don’t, then you could miss some of the following issues:

1) When did you last test that you can actually restore from your backups?  (e.g. Are you running test restores, or using the RMAN validate commands?) 

2) Does your script look for logical corruptions?  (RMAN will pick up physical corruptions by default, but you need to run specific commands to detect logical corruptions).

Space Issues
3) Do you review available disk space before running backups?  Do these take into account additional disk space requirements if the backup files are compressed?  (i.e. They may take up less space once compressed, but the initial compression operation will temporarily use a lot more space). 
4) Are you looking at the size of your backup files? (e.g. If running a cold backup have you run out of disk space?  Do any file transfer tasks complete successfully?)

Backup Timings
5) Are your backups over-running into the on-line day and affecting database performance for business users? 

Are they running at a time when you have an overnight batch job running? 

As the database size and its workload increases over time, you’ll need to adjust your timings accordingly.
6) If you have a backup to disk, followed by a backup to tape or a lower-level of tiered storage, is this still working correctly?  (i.e. You don’t want to have your disk backup still running when the secondary backup starts, otherwise you could experience various performance and corruption issues). 

If your tablespaces are being put into backup mode to take a hot backup or snapshot of the database, ensure that this change is still happening at the correct point in time.

Error checking
7) Are you searching for the correct error messages?   Is it OK to just look for ORA- errors or would it be advisable to also check for other messages such as “warning”, “unsuccessful”, “usage”, “error”, “RMAN-” etc.
8) Are you aware of the date and time of your backup logs?  Just doing a tail of the last few lines of the log without checking this, could mean that you miss any new errors.

Backup Options
9) Unless you’re using Oracle-aware software (i.e. Oracle agents) to backup an online database, then is there any point in backing up database files?  Should you just be backing up your backup files.  (If you’re not using oracle-aware software, then backups of open database files will be unusable - though you may not see any errors).

If you’re using a tool that is Oracle-aware, are you definitely backing up all the database files.  It’s easy to miss out a tickbox in a GUI tool and find out later that you have an incomplete backup.

10) Are you using the latest backup and recovery features?   Has your script been optimised in terms of performance and functionality? Could it be:

(a) Faster,  so that it has less of a performance impact? 
(b) Smaller to save on storage costs?    
(c) More secure? 

11) Would it be easier to use separate version-specific scripts, so that it’s easier to use and test new features?  One large script for all releases can become very complex over time.  Issues can take longer to resolve.


 Record Keeping
12) Are you maintaining a log or history of backups and their contents?  This is straightforward if you’re using an RMAN recovery catalog, or backing up to some storage or backup management software. Even something as simple as a spreadsheet could be used to record a history of your backups.

These are just a few things to think about when checking your backups.  As a DBA, backups should be your number one priority. If you can’t restore from them in a DR situation, then you may find that your number one priority suddenly becomes finding a new job!

Dealing with multiple tnsnames.ora files on a windows client - Part 2

Tuesday, July 12th, 2011 by Matt Canning

In part 1 of this blog, we discussed how to deal with multiple tnsnames.ora files on a windows client by using the TNS_ADMIN variable. This ensures that each Oracle installation on the client uses the same central file.

Obviously there will be some users - such as developers or testers - who require connection strings that shouldn’t be available to ordinary users.  One way of getting around this issue is the following:

(i) Point the TNS_ADMIN variable on the local client to a shared local tnsnames.ora file.
(ii) Within the shared tnsnames.ora file, add an ifile entry which points to a shared location on a mapped network drive. 

Using this method, clients have access to the shared information on the network drive, but are also able to add their own private connection strings to the local file. To create an ifile entry you just add a line similar to that shown below to your tnsnames.ora file:


Note: There should be no spaces before or after the “=” sign, otherwise SQLDeveloper can have issues connecting.

In this example, we have specified a tnsnames.ora file on the U: drive.  The file can be called anything that you like.  It is also possible to have several ifile entries in your local tnsnames.ora file, if you want to sub-divide access to selected databases. It is advisable to set permissions on the remote file to read-only, in order to prevent clients using GUI tools such as Net Configuration Assistant or Net Manager from over-writing the file.

You should be aware that where you place the ifile entry is important.

If you place the ifile entry at the start of the local client tnsnames.ora file, then a duplicate entry in the local file would take precedence over an entry in the ifile-referenced file, as the entry in the local file would be read last.

If you place the ifile entry at the end of the local client tnsnames.ora file, then a duplicate entry in the ifile-referenced file would take precedence over an entry in the local tns file, as the ifile entry would be read last.


(a) Having a central tnsnames.ora file may reduce support calls and reduce the administrative problems of maintaining several files, but some sites may view this as being a central point of failure - especially if the clients are pointing to a tnsnames.ora file on a remote network drive. To resolve this, you could have another copy on another network drive which is updated regularly.
(b) Always test that the ifile setting does not have an adverse effect on client connections to the database. (Some Java clients may experience issues when using this parameter). 
(c) You can experience connection issues if you have a session open and you edit the ifile or tnsnames.ora file without exiting your session.
(d) If a shared tnsnames.ora file is not suitable for your needs, then check the Oracle documentation for other methods to identify Oracle database services. (e.g. Oracle Names Server, Oracle Internet Directory etc.)

Wednesday, July 6th, 2011 by Matt Canning

Dealing with multiple tnsnames.ora files on a windows client

Wednesday, July 6th, 2011 by Matt Canning

Part 1

A common problem found at many sites is having Windows clients with several Oracle products installed.

If each installation uses its own tnsnames.ora file to identify the database services that the client can connect to, then users often experience connection issues. 

If a new database service is added, it’s not always clear which tnsnames.ora file(s) need to be updated.

Some sites get around this problem using scripts or batch files. Only one tnsnames.ora file is updated and then it is automatically copied to the other locations on the client.  Other sites just update each file manually.

A better method is to use the TNS_ADMIN environment variable.  Setting this variable to point to the directory of your choice ensures that all Oracle installations use the same copy of the tnsnames.ora file.

Note 111942.1 describes this as: 

“The TNS_ADMIN parameter/environment variable is used to explicitly define the directory location for Oracle Net to resolve where to find its configuration files. TNS_ADMIN over-rides the default Oracle Net location.”

To add this setting for an Oracle 10 client running on Windows XP, all you would need to do is:

Right-click on “My Computer” - Properties - Advanced - Environment Variables - Under the “System variables” section - New -
In the “Variable Name” box type - TNS_ADMIN
For the “Variable Value” box type -  C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN  - OK - OK

Note: The location can be on a local or remote network drive. Just specify the directory that contains the file - not the name of the file. 

The Oracle client will search for the tnsnames.ora file in the following locations and order:
(i) The current directory. (i.e. When running from a Windows command prompt, if there is a tnsnames.ora file in your current directory, then this will be referenced first).

(ii) A location specified in your session (e.g. SET TNS_ADMIN=C:\SHARED_TNS )  or within a script. 

(iii) A global environment variable (i.e. setting TNS_ADMIN via My Computer).

(iv) A registry key entry. (e.g. You could use regedt32 to create a new String Value called TNS_ADMIN under the Oracle home branch. (see Note 290124.1 for further details).

Setting the TNS_ADMIN environment variable is a better solution to our issue than using a registry key entry, as we would need to create a registry key under every ORACLE_HOME on the client.  Note 1020336.102 mentions that

“Different versions of Oracle client software will not read a variable defined within another version’s HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key”

(v) %ORACLE_HOME%\network\admin  (or %ORACLE_HOME%\net80\admin for an Oracle 8.0 client).

Once you’ve configured the TNS_ADMIN variable, you can just type tnsping service_name  (e.g. tnsping ORCL10)  and then you should see an “OK” message.

If it’s still not working then check on Oracle Support, as there are various issues which could be affecting the connection (e.g. NAMES.DIRECTORY_PATH, NAMES.DEFAULT_DOMAIN and SQLNET.AUTHENTICATION_SERVICES settings in your sqlnet.ora etc.).

Also check that you don’t have duplicate tns connection string entries in the file.  If you do, then the last entry in the file is the one that will be used. 
Note:  If you have a command prompt window open, it won’t pick up the new setting dynamically - you’ll need to close it and open up a new window. 

Just type echo %TNS_ADMIN% and you should see the new setting.

You may not want all users to have access to every connection string that is available, so in part 2 of this blog, we’ll discuss how to deal with this situation.

(a) You should double-check that each of the Oracle products installed on the client still works correctly when using a central file. Some oracle applications require their own settings, which may not be compatible with other oracle installations.  As with any changes, you should always test any changes before rolling them out to clients.

(b) When un-installing Oracle software, you should un-set the TNS_ADMIN variable if it points to a shared location. Some of the GUI tools (e.g. 10g Net Configuration Assistant) could remove files specified at this location.

Buy Hydrocodone Valium Buy Propecia Buy Percocet Buy Celexa Buy Bupropion Lexapro Online Xanax Codeine Acyclovir Clonazepam Buy Cheap Phentermine Xanax On Line Adipex Buy Clonazepam Buy Meridia Acyclovir Ultram Buy Paxil Buy Clonazepam Buy Lortab Buy Norvasc Online Xanax Lortab Buy Xanax Acyclovir Buy Diazepam Buy Flexeril Buy Biaxin Phentermine Online Ultram Meridia Buy Lorazepam Buy Cialis Buy Lexapro Propecia Buy Zyban Carisoprodol Buy Propecia Buy Seroquel Tramadol Online Buy Flexeril Buy Codeine Buy Alprazolam Buy Vicodin Online Buy Meridia Buy Bontril Buy Flexeril Buy Zyrtec Cialis Tenuate Adderall Online Xanax Buy Xanax Fioricet Buy Ephedrine Buy Online Xanax Buy Adderall Buy Phentermine Online Buy Valium Online Buy Norco Buy Valium Buy Phentermine Soma Buy Vicodin Buy Zocor Buy Lexapro Buy Hydrocodone Buy Cipro Darvocet Soma Buy Adipex Buy Zocor Buy Cialis Buy Diflucan Buy Carisoprodol Zyrtec Buy Oxycontin Buy Zithromax Glucophage Buy Tramadol Online