Installing Oracle on a machine with DHCP

22 Replies

During Oracle installation on a windows machine that is configured to use DHCP in order to obtain its IP address, the Following message will probably be displayed:

Checking Network Configuration requirements …
Check complete. The overall result of this check is: Failed <<<<
Problem: The install has detected that the primary IP address of the system is DHCP-assigned.
Recommendation: Oracle supports installations on systems with DHCP-assigned IP addresses; However, before you can do this, you must configure the Microsoft LoopBack Adapter to be the primary network adapter on the system.  See the Installation Guide for more details on installing the software on systems configured with DHCP.

As the error message say, this due to the fact that you are trying to install Oracle on a machine that is using DHCP to obtain its IP address. You can still install oracle on a machine without a constant IP address. All you need to do is configure the LoopBack adapter:

Install the Loopback adapter
1)     Go to the control panel and choose add hardware
2)    The Add Hardware wizard will be displayed, click Next
3)    Choose “Yes, I have already connected the hardware” and click Next
4)    An hardware list will be displayed. Navigate to the bottom of the list and choose “Add new hardware       device”
5)    Choose Install the hardware that I manually select from a list (Advance)”
6)    Choose “Network adapter”
7)    Choose “Microsoft” on the left side of the window and “Microsoft Loopback Adapter” on the right             window”
8)    Click Next and verify that Microsoft Loopback Adapter is about to install and click next again and       finish.

Configure the loop back adapter
1)    Go to control panel – network connections, you will see a new network connection.
2)    Choose “local area connection 2” (the name may vary but basically it is the one we just added) and       click properties.
3)    Click on tcp/ip and then properties.
4)    Enter an IP address and a subnet mask (you can use 1.1.1.2 and subnet 255.255.255.0)

Go to network connection and click on Advanced settings under the Advanced menu and verify that the loopback adapter is in the first one. (Update: it look like this step is optional because it seems to work in most cases anyway)

Now you can continue your oracle installation.

ORA-01758 when trying to alter table

2 Replies

If you are trying to add a new Not NULL columns to a table and you get the following error:

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Since the values in the new column are null (after all, you just added the column) the new constraint can not be satisfied.

There are several option to overcome ORA-01758:

1) provide a default value for the column.

ALTER TABLE table-name ADD column-name VARCHAR2(15) DEFAULT ‘X’ NOT NULL

If you don’t want the default value you can remove it after the column will be populated with the new data.

2) Add the column without the NOT NULL constraint,

ALTER TABLE table-name ADD (column-name VARCHAR2(15));

— add the data

UPDATE table-name set column-name=’x’;

— apply the NOT NULL  constraint.

ALTER TABLE table-name MODIFY (column-name NOT NULL)

3) Empty the table, apply the NOT NULL and add the data back to the table

How to move tablespace to a new location

Leave a reply

1)    Take the tablespace offline
ALTER TABLESPACE tablespace-name OFFLINE;
2)    Use the OS to Move the tablespace to the new location
3)    Run the following command:
ALTER TABLESPACE tablespace-name RENAME DATAFILE ‘OS path to old tablespace\tablespace-datafile- name.dbf’ TO ‘OS path to new location\tablespace-datafile-name.dbf’;
4)    Take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;
If the following error is displayed
ORA-01113: file n needs media recovery
ORA-01110: data file n: ‘new location file name’
Issue the the following command:
recover datafile ‘new location file name ‘;
and take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;

Deleting large number of rows without filling the transaction log

Sometimes, when you try to delete a large number of rows from a table the transaction file may grow to the point that you will run out of disk space. In other occasions, when the transaction log growth is limited, the delete can fail with the following message:

The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
There are several ways to overcome this issue:

1)    If you want to delete all rows in the table, consider using TRUNCATE TABLE instead of delete. Truncate will not fill the transaction log. Remember that TRUNCATE will also reset any auto incrementing keys.
2)    In case you need to keep some of the data in the table, depending on the ratio between the numbers of rows you are keeping to the number of rows you wish to delete, you may want to:

a)    Copy to the rows you want to keep to another table

b)   Truncate the original table

c)    Copy the data from the temporary table back to the original table.

3)    Delete manually in chunks. You can run: delete TOP (10000) from [your big table]. Remember to commit between deletes.

4)    Use script to delete in chunks with commit every number of rows.
This script for example commits every 100000 rows. Change it to fit your specific scenario:


DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [Your big table] where  [Your Where Clause]

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) FROM  [Your big table] where  [Your Where Clause]

COMMIT TRAN
END

Oracle enterprise manager is not accepting host logon credentials

8 Replies

When trying to perform backup and other operations using the Oracle enterprise manager, The Oracle Enterprise manager asks you for the host credentials (operating system login). You may get the following error even if you are sure that you provided the correct credentials:

Validation Error
Examine and correct the following errors, then retry the operation:
ERROR: Wrong password for user

To solve this, you need to give the user you are trying to connect with the privilege, “Logon as batch job”.
on windows, go to
Administrative Tools –>
Local Security Policy –>
Security Settings  –>
Local Policies –>
User Rights Assignments –>
Double click “Log on as batch job” and add the user.