How to Automate the caculation of Database Growth and scheduling it in DBMS JOBS....

1.Create a Table By the Name db_growth as shown below...

Name Null? Type
----------------------------------------- -------- ----------------------------
DAY DATE
DATABASE_SIZE_MB NUMBER
DAILY_GROWTH_MB NUMBER


2.create or replace PROCEDURE database_growth
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d
ay,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size - yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

3.Submit in DBMS_JOBS

variable jobno number;
begin
dbms_job.submit(
:jobno,
'database_growth ;',
trunc(sysdate+1) + 4/24,
'trunc(sysdate+1) + 4/24'
);
commit;
end;
/
print :jobno

How to change hostname on Linux (Red Hat or Oracle)

This is post for those who wish to change hostname .

How to find hostname on Linux/OEL ?
Use command “hostname” to find host name of Linux

How to change hostname on Linux/OEL ?

1. Open file /etc/sysconfig/network using editor like vi , nano or ed

2. Change entry HOSTNAME=XXXXXX to HOSTNAME=New_Host_Name

3. Restart xinetd service or reboot Linux/OEL
Restart xinetd service:- xinetd restart
Reboot Linux:- reboot

4. If you have defined hostname resolution in hosts file then change entry in /etc/hosts

How to calculate the bandwidth required between Primary and Standby Database for Archive log shipping?

Below is the detailed explaination for calculation of bandwith between primary and standby database.

The formula used (assuming a conservative TCP/IP network overhead of 30%) for calculating the network bandwidth is :
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps

Measuring the Peak Redo Rate
Use the Oracle Statspack utility for an accurate measurement of the redo rate.
Based on your business you should have a good idea as to what your peak periods of normal business activity are. For example, you may be running an online store which historically sees the peak activity for 4 hours every Monday between 10:00 am - 2:00 pm. Or, you may be running a merchandising database which batch-loads a new catalog every Thursday for 2 hours between 1 am - 3 am. Note that we say "normal" business activity - this means that in certain days of the year you may witness much heavier business volume than usual, e.g. the 2-3 days before Mother's Day or Valentine's Day for an online florist business. Just for those days, perhaps you may allocate higher bandwidth than usual, and you may not consider those as "normal" business activity.However, if such periodic surges of traffic are regularly expected as part of your business operations, you must consider them in your redo rate calculation.

During the peak duration of your business, run a Statspack snapshot at periodic intervals. For example, you may run it three times during your peak hours, each time for a five-minute duration. The Statspack snapshot report will include a "Redo size" line under the "Load Profile" section near the beginning of the report. This line includes the "Per Second" and "Per Transaction" measurements for the redo size in bytes during the snapshot interval. Make a note of the "Per Second" value. Take the highest "Redo size" "Per Second" value of these three snapshots, and that is your peak redo generation rate.

Note that if your primary database is a RAC database, you must run the Statspack snapshot on every RAC instance. Then, for each Statspack snapshot, sum the "Redo Size Per Second" value of each instance, to obtain the net peak redo generation rate for the primary database. Remember that for a RAC primary database, each node generates its own redo and independently sends that redo to the standby database - hence the reason to sum up the redo rates for each RAC node, to obtain the net peak redo rate for the database.

Example:
Let us assume the redo rate is a 500 KB/sec.(500*1024=512000)
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps Required bandwidth = ((512000/0.7) * 8) /1,000,000 Required bandwidth = 4.85 Mbps

Hope this helps...

How to change the location of datafiles in Oracle database?

Below are steps to move the datafiles without shutting down the database.

1. Make the tablespaces offline.
ALTER TABLESPACE tablespace_name offline;

2.copy the files to the new location at the OS level

3.Modify the location of the datafiles
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'datafile_name' to 'datafile_name';

4.Make the tablesapces online
ALTER TABLESPACE tablespace_name online;

Changing the ODS schema password in OAS!!!

OID Database Schema Owner

The Oracle Internet Directory runs on an Oracle database

and creates two database users: ODS and ODSCOMMON. ODS

is the schema owner that contains all of the database

objects (tables, views, objects, etc.) used for OID

functionality and directory storage. When the OID needs

to login to the database, it uses the ODS database

account which has a default password of ODS. You should

secure this database user account before putting the

LDAP directory into production.

1. Change the ODS password to the desired value, and

create the new wallet:

oidpasswd connect= change_oiddb_pwd=true

oidpasswd connect= create_wallet=true

2. Restart OID:

opmnctl stopproc process-type=OID

opmnctl status

opmnctl startproc process-type=OID


3. Change the password in Enterprise Manager:

1. Login to EM

2. Click Targets tab > Click All Targets tab

3. Find the Name reported in the emagent.trc file (e.g.,10_1_2_portoidd.libby.oracle.com_LDAP) > click on it

4. Click Monitoring Configuration at the bottom right

5. Enter:

Username = ods

Password =

Using the OID Database Password Utility


The DBA can change this password by using the OID

Database Password Utility (included with the OID

installation). The following example uses this utility

to change the database password for ODS:


# $ORACLE_HOME/bin/oidpasswd

current password: ods

new password: new_secret_password

confirm password: new_secret_password

password set.

How oidpasswd Works

The oidpasswd utility connects as the ODSCOMMON user and

uses the role ODS_SERVER with the original password to

perform the following:

1. Changes the password for the ODS user for the OID

schema database.

2. Updates the SYSTEM.ODSINSTANCES table with the new,

encrypted password for ODS.

3. Changes the password for the ODS_SERVER role to the

new password.

4. Updates the $ORACLE_HOME/ldap/admin/oidpwdr file with

the encrypted password.


NOTE: Some Oracle OID patches and scripts may assume the

user ODS/ODS. It is advised to change the password for

ODS back to its default of ODS during application of

patches or when running OID scripts.


But what about ODSCOMMON?

When connecting to the database schema, the OID

executables, such as oidctl or oidldapd servers connect

as the database user ODSCOMMON. The password for

ODSCOMMON is ODSCOMMON, and that password cannot be

changed. It is hardcoded in the executables and changing

it on the database level will cause an ORA-01017 error.

There is no security risk, however, not being able to

change the ODSCOMMON password, as it has only CONNECT

privilege. Once connected as ODSCOMMON, the executable

will obtain the privileges it needs via the role

ODS_SERVER, which is protected by the ODS password. The

password is obtained by the executable from the

SYSTEM.ODSINSTANCES table. This is the password

encrypted and set by the oidpasswd utility.

How to Unlock/Reset Super User cn=orcladmin When the ODS's Password Has Been Forgotten or is Unknown?

Solution

If the ODS password has been lost then follow steps 1-7:

1. Shutdown the OID processes

% opmnctl stopproc ias-component=OID

2. Verify that there is no OID process running

% ps -ef | grep -i oidmon
% ps -ef | grep -i oidldapd
% ps -ef | grep -i odisrv

3. Go to the $ORACLE_HOME/ldap/admin directory and rename the files oidpwdlldap1 and oidpwdrxxxx

where xxxx is the instance name

4. Login to the OID database via sqlplus as an user with DBA privileges (example. SYS or SYSTEM) and reset the ODS schema password:

SQL> alter user ods identified by password;

Note: replace password for the desirable value.

5. Verify that you are able to login to the database using following command.

% sqlplus ods/password@connect_string

Where connect_string is the alias defined in the tnsnames.ora to connect to the OID database.

6. Execute the oidpasswd command to recreate the files renamed in step 3

%oidpasswd connect=connect_string create_wallet=true

Note: To use in this step the same password defined in step 4.

7. Verify that the files renamed in step 3 have been recreated under $ORACLE_HOME/ldap/admin directory

8. If the orcladmin account is locked then use following command

% oidpasswd connect=connect_string unlock_su_acct=true

9. If the password for orcladmin needs to be changed then use following command

% oidpasswd connect=connect_string reset_su_password=true

10. Start the OID process

% opmnctl startproc ias-component=OID

11. Verify that the account orcladmin has been unlocked or that the password has been changed successfully

% $ORACLE_HOME/bin/ldapbind -p port -D cn=orcladmin -w passwd

For example:

$ORACLE_HOME/bin/ldapbind -p 389 -D cn=orcladmin -w welcome1

ORA-08104: this index object string is being online built or rebuilt

Run the below script with the object id and your problem will be resolved.

DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;

BEGIN
OBJECT_ID := 63556;
WAIT_FOR_LOCK := NULL;

RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;

After running the above script your index will be ready for use.