HOW TO CHECK GROWTH OF THE DATABASE

Thanks to: http://ayyudba.blogspot.com/2007/09/how-to-check-growth-of-database.html

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 “TOTAL SIZE (MB)” from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 “FREE SPACE (MB)” from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total “TOTAL SIZE”,t1.free “FREE SPACE”,(t1.free/t2.total)*100 “FREE (%)” ,(1-t1.free/t2.total)*100 “USED (%)”
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE% “
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE%”
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE%”
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format =’dd-mon-yyyy hh24:mi:ss’ ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, ‘begin insert into db_growth select * from v_db_growth;commit;end;’, sysdate, ‘SYSDATE+ 24′, TRUE);
commit;
end;

PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it’s other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth

Tracking oracle Logons

There are a couple ways of tracking logon and logoff information within an Oracle database, however few offer the simplicity and flexibility of the use of an logon and logoff trigger. Here is how I built a simple set of triggers and accompanying table to track usernames, logon time, logoff time, machine connected from and program used.

I performed these steps as system. If you want to install these as another user you will need to assign the appropriate permissions first.

The first step is to create a table to store the information. This table may grow quickly if this is a very active system so we want to put it somewhere other than the system tablespace. For reference, mine currently has around 3000 records in it and is around 200k in size. Of course mileage may vary.

CREATE TABLE session_audit
(
user_id VARCHAR2(30),
session_id NUMBER,
host VARCHAR2(30),
program VARCHAR2(60),
logon_time DATE,
logoff_time DATE
)
TABLESPACE users;

This will be the table you query to determine login information. Of course you will probably want to purge data from here occasionally to keep it from getting too big.

Next we create the logon trigger which will populate all but the logoff_time of the session_audit table. This uses the sys_context function to lookup the host and session id (different from the SID) of the session. The program is retrieved by a subquery on the V$SESSION table.

CREATE OR REPLACE TRIGGER tr_session_audit_logon
AFTER LOGON ON DATABASE
DECLARE
session_id number;
BEGIN
select sys_context(’USERENV’,'SESSIONID’) into session_id from dual;
IF session_id != 0 –ignore internal connections
THEN
BEGIN
INSERT INTO session_audit (
user_id,
session_id,
host,
program,
logon_time,
logoff_time
)
VALUES(
user,
session_id,
sys_context(’USERENV’,'HOST’),
(SELECT program FROM v$session
WHERE sys_context(’USERENV’,'SESSIONID’) = AUDSID),
sysdate,
NULL
);
END;
END IF;
END;


UPDATE: I have found that the old version of this resulted in an ORA-1427 error when someone made an internal connection. The problem also came up when dbms_jobs were run. The code now ignores internal connections (where the session id is 0).

Finally we create the logoff trigger to fill in the logoff_time.

CREATE OR REPLACE TRIGGER tr_session_audit_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE session_audit
SET logoff_time = sysdate
WHERE sys_context(’USERENV’,'SESSIONID’) = session_id;
END;

That’s it. You can now search the session_audit table for logins durring a time window or all logins for a specific user. You could even check who is using a certain application.

I mentioned one possible issue, that you don’t want the session_audit table to fill up your system tablespace, however it is also important to mention that if your logon trigger fails for some reason people will not be able to log in, so watch the space, no matter where you put it.

Donald Burleson (who desperately needs a new portrait for his website) of Burleson Consulting offers these instructions for a similar trigger, however he is grabbing more information and fills most of it in at the end.

oracle, sql, dba, database administration, database development, database security, database, oracle security

Running SQLPlus and PL/SQL Commands From A Shell Script

I got this from Tim Archer..

http://timarcher.com/?q=node/48

The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.

The Basic Syntax

In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:

#!/bin/sh
sqlplus system/manager@prod_db <<ENDOFSQL
select sysdate from dual;
exit;
ENDOFSQL

Try to create that shell script on your server and run it. Be sure to enter in the correct connect string. You’re screen output should show all of the output from the SQL Plus session that executes.

A More Advanced Example

Now I will show you a more advanced script that I run on my servers. It builds off the simple example above, except it does a few extra things:

• Checks command line parameters for the appropriate number of parameters.
• Exports the ORACLE_SID as passed in from the command line parameters.
• Runs the oraenv script for the specified ORACLE_SID to setup all the Oracle specific environment parameters.
• Checks the return code from SQL Plus to determine if the SQL script successfully ran and takes an appropriate course of action.

Create a file named test.sh and put the following code in it:

#!/bin/sh

# The name of this script
SCRIPT_NAME=test.sh

###################################################################
#
# Check for Invalid Command Line Arguments
# $1 = ORACLE_SID
#
###################################################################
if [ $# -lt 1 ]
then
  echo "Usage: $SCRIPT_NAME <ORACLE_SID>"
  echo "Example: $SCRIPT_NAME DWHSE PROD"
  exit
fi

###################################################################
#
# Setup Script Variables
#
###################################################################
#This is the oracle_sid of the instance that the oracle environment
#will be setup for.
export ORACLE_SID=$1

#
# Initially we need some oracle path in the PATH for the dbhome command
# to be found. We reset the PATH variable to the proper oracle_home/bin
# below
#
export ORACLE_HOME=/usr/local/banner/oracle/product/current
export PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH

export ORAENV_ASK=NO

. /usr/local/bin/oraenv

export ORAENV_ASK=YES

echo "Starting Create PIN for ORACLE_SID: $ORACLE_SID"

###################################################################
#
# Run our SQL Plus Commands
#
###################################################################
sqlplus system/manager <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
select sysdate from dual;
exit;
ENDOFSQL

ERRORCODE=$?

#Check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
  echo "********************"
  echo "ERROR: The SQL Plus Command Failed. ErrorCode: $ERRORCODE"
else
  echo "********************"
  echo "SQL Plus Successfully Ran. ErrorCode: $ERRORCODE"
fi

The format to run this script will then be:

./test.sh ORACLE_SID

You’ll need to modify a few sections of the script to work in your environment as described below:

• In the section labeled Check for Invalid Command Line Arguments you will want to change the script to take an ORACLE_SID appropriate for your environment. The SID’s that are valid in my environment are PROD and DWHSE.
• Change the connect string from system/manager to be what is required for your environment. There is no need to specify the Oracle SID in this part of the connect string since the beginning of the script exports the appropriate SID.
• Plug in your own SQL to run within SQL Plus. In the example I gave you it will always succeed since selecting sysdate from dual will always work.

Running PL/SQL Procedures And Passing In Unix Environment Variables

Within the section specifying the SQL to run you can even call PL/SQL functions and procedures. If you want to call a PL/SQL function or procedure, make sure you prefix the procedure call with the keyword exec. Furthermore, you can pass your Unix environment variables into your SQL Plus session. The following example creates the environment variable named TEST_VAR and then runs SQL Plus calling DBMS_OUTPUT to print out the value in the variable.

export TEST_VAR="HELLO WORLD"
sqlplus usfbanner/webster <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
set serveroutput on;
exec dbms_output.put_line('${TEST_VAR}');
exit;
ENDOFSQL

Running The Script Through Cron

Once you are able to get a script created that performs all of the appropriate actions that you need to do, you can automate running it through cron. This is the simple part.

• As the user who will run the script (probably the oracle user), edit the crontab.

  crontab –e

• Add a line to run your script at the time you desire. For example, I have the following entry in the crontab for my oracle user that will run a script to send emails to people who new user accounts have been generated for.

  30 13 * * * /usr/local/banner/scripts/send_new_account_email.shl PROD

  The script will run at 1:30pm every day connecting to the PROD database instance. Lastly, the output from running this script will be emailed to the oracle user for my review. The emailing of any output is standard cron functionality.

Monitor the size of Log Files using OEM

answer got from HarryP

From Grid Control:

Navigate to Hosts.
Select a host you wish to monitor for disk usage/whatever.
Navigate to Metric and Policy Settings (near bottom of the page)
Ta-da! It’s all there for you to play with! You can add new metrics to monitor for.

Got my New 2008 Nissan Versa

I got my new Nissan Versa last May 17, 2008 for a total price of $16,108 from Tom Naquin. It was an automatic with a power package and remote keyless entry. The car was very nice in its super black color with silver kick plates and splash guards. I thought to my self the only missing is the rain guard and so I ordered a new rain guard from Ebay. A full tank of Gas on my nissan versa cost around $50 ~ $55 depending on the gas per gallon with a 13.2 gallon in a tank. This full tank of gas allows me to run my car for a month without replenishing my gas. A sure fuel economy car that gives me around 33 mpg on a highway and 26 on city.

Unfortunately it was June 15, 2008 (Fathers day) when a Hail storm hit our car at the Mishawaka area.  Good thing that me and my wife were inside the store (Best buys) when it happened. After the Hail Storm we immediately checked our car and it got a lot of dimples on the hood, Roof and Drivers side fender. Even my new Rain Guard was broken into pisces. We’ll we can’t do anything about that, It’s nobodys fault. But deep inside me it really hurt seeing my car like that.

So we decided to call my car Insurance (Geico) and they covered my damage claims. I told my self that it would be better if  Nissan Bodyshop will do the repair so I made an arrangement to have my car fix. But I was totally wrong! The guy from Nissan BodyShop named Greg Wittner did a lot of mess in my car. They broke my plastic panel on the Lift gate during the dent repair process, missing plastic clips, Untidy works, so many  tiny scratches made from my car. In short I’m not impressed with their work and I was totally upset. So I called on my dealer and report everything to him and he talked to Greg. The car was then scheduled again for polishing all the issues I have with my car and it stayed for about 2 weeks from them. Guess what? Some scratches were present, and lot of white wax on the edges. Well the plastic clips and panel boards were replaced but the service they provided me didn’t even reach my expectation. They have a very low customer service index.

So what else can we do? I grab my car back and I just polished my car to bring back its natural beauty. You can see its pictures at http://mykelchua.multiply.com

Hmmn… what a relief! Finally my car looks briliant once again and it has a new Rain guard too but its a mugen style  (much better than before). Now that everybody is looking for a fuel economy car, I really love my car. It serves its purpose!

Installing management Agent (10.2.0.3)

Select this option to install the Management Agent in an Oracle home directory on a specified host target. Each host you wish to monitor must have an Agent installed. Software for OEM agent is available from oracle site.

You can install the Management Agent in the following ways:

· On each host computer, run Oracle Universal Installer from the Enterprise Manager DVD-ROM and select the Additional Management Agent installation type. This method installs one Agent at a time in interactive GUI mode. You may also perform silent installations using a response file.

· Use the Agent Deploy application to perform a fresh agent installation (standalone or cluster agent) or a shared agent installation.

· Use the nfsagentinstall script to install and deploy the shared Mangement Agent.

· Use the agentDownload script to install and deploy Management Agents to many managed hosts through HTTP or over the network.

See Also: See Chapter 5, ” Deploying the Management Agent” for a full and detailed explanation on installing Management Agents using the agentDownload script and the nfsagentinstall script. See Appendix G, “Assigning Custom Ports” for information on assigning custom port numbers for Enterprise Manager components.

Attention: If you install the Management Agent on a system where you have already installed an Oracle Application Server instance, you must install the Management Agent from a user account that belongs to the same OS Group group that was used to install the Application Server. Otherwise, you cannot monitor the application server metrics. · To see which group was used to install the Application Server, type ls -l on the command line in the directory containing opmn.xml: <AS_ORACLE_HOME>/opmn/conf. · To see the groups to which you belong, type groups on the command line. You may be a member of several groups. Note that it is not sufficient to be a member of the group used to install the Application Server; that group must be your current group. · To see which user/group you are currently using, type id on the command line. Use the newgrp command to change to the group used to install the Application Server. Oracle recommends installing all software using a single group.

Prerequisites

Ensure the Agent Oracle home must not contain any other Oracle software installation.

Note: Enterprise Manager does not support uploading of data to the same Management Repository from two Management Agents running on the same host.

To Install an Additional Management Agent Using OUI

1. Start the Oracle Universal Installer by running the runInstaller script in Linux (<DVD>/runInstaller) from the top directory of the DVD.

2. In the Specify Installation Type page, select the fourth option (Additional Management Agent), and specify the parent directory path and installation name.

Figure 3-20 Specify Installation Type

Specify Installation Type

3. In the Specify Installation Location page, specify the full path to the parent directory (base directory), for example, /scratch/OracleHomes. The agent home created during the installation is placed as a sub-directory under this parent directory. For example: agent10g.

Note: Ensure you do not use symbolic links to the Oracle home path.

The installer by default installs the selected products in the English language.

a. If you want to install the product in a different language, click Product Languages.

b. The Language Selection page appears. Make the required language selections here, and click Next. See Figure 3-3, “Language Selection” for details.

4. The Product Specific Prerequisites Check page appears.

This page displays the name, type, and status for all prerequisite checks designed for the installation. Automatic checks are run first, followed by optional and manual checks.

Depending on the status of the automatic checks, you must verify all warning and manual checks. To do this, select the appropriate prerequisite status check box and click Retry. As each check runs, a progress bar is shown, and test details (expected results, actual results, error messages, instructions) are displayed in the details section at the bottom of the page. See Figure 3-5, “Product-Specific Prerequisite Checks” for more information.

Note: You can also run these prerequisite checks in standalone mode, prior to starting the runInstaller. For more information on running these prerequisite checks in standalone mode, see Chapter1, “Running the Prerequisite Checker in Standalone Mode”for more information.

5. Click Next. The Specify Oracle Management Service Location page appears.

Figure 3-21 Specify Oracle Management Service Location

a. Specify the Management Service host name. For example: dlsun1444.acme.com. Use the fully-qualified host name (including domain).

ATTENTION: When specifying the host name, ensure you do not include the protocol (that is, http:// or https://).

b. Enter the port number for the Management Service. The default port is 4889 and the default secure port number is 1159.

However, even if you are specifying a secure Management Service, you must still enter the non-secure port number (4889) here. You must connect over HTTP to receive the certificate before you can connect over HTTPS.

Note: If your Management Service has been configured to use HTTPS, you are prompted to enter the Agent Registration password (used to secure the management Service environment). If you do not know the password, obtain it from the user who configured the Management Service for SSL.

6. Click Next. If the Management Service is found to be running in a secure mode, the Specify Agent Registration Password page appears. You must provide the correct password to enable communications between the new Management Agent and the SSL-enabled Management Service.

Figure 3-22 Specify Agent Registration Password

IMPORTANT: If you do not know the password and choose to leave the Password field blank, you must do the following after installation to enable communication between the agent and secure OMS: · Find out the correct password for the secure OMS environment. If you do not know the password, obtain it from the user who configured the Management Service for SSL. · In the <AGENT_HOME>/bin directory, execute the following command: ·                emctl secure agent <password> ·                  where <password> is the Agent Registration Password.

7. Click Next. The Summary page appears.

This page displays a summary of the options that you have selected during the installation process. Depending on the installation type, this page displays any or all of the following details:

· Global Settings

· Product Languages

· Space Requirements

· New Installations

For more information on each of the above-listed details, see the Enterprise Manager Online Help.

Verify the choices that you have made and click Install to start the installation. Enterprise Manager Installer starts installing the selected Oracle product.

8. During the installation, you are prompted to execute certain configuration scripts. These scripts and their locations are listed in the Execute Configuration Scripts dialog that is displayed (only for Linux). Figure 3-12, “Execute Configuration Scripts” provides details on this page .

. To execute these scripts, go to the terminal window, log in as root, and run these configuration scripts.

a. Return to the Execute Configuration Scripts dialog box after executing the scripts, and click OK to continue the installation.

9. The Configuration Assistants page appears. At this point, the installer starts running the recommended configuration tools.

This page displays the name, status, and the type of each configuration tool that Oracle recommends to be run before completing the installation. Refer to Table 3-4 to see the list of configuration tools that are run.

See Appendix D, “Executing the runConfig Command Line Tool” for information on executing the runConfig tool.

10. After successfully running all the recommended configuration tools, click Next. The End of Installation page appears.

This page displays some important information about the products you have installed. This information is also available in the <AGENT_HOME>/sysman/setupinfo.txt file.

For example, it might contain information about the URLs for particular Web applications. If necessary, write this information down so that you can remember it.

Protected: How to Install and Configure OID with two way replication

This post is password protected. To view it please enter your password below:

Password: