Moving a datafile Online

A datafile cannot be completely moved online in a user transparent way, but what it can be done is to minimize the time required to perform the operation, and avoid the traditional procedure executed when the database is mounted, which permits more availability for the non affected production tablespaces.

The operation can be performed by creating a datafile copy, either with the traditional commands or by means of recovery manager. In this case the following script was prepared to move a datafile.

run {
copy datafile ''
to '';
sql 'ALTER TABLESPACE OFFLINE IMMEDIATE';
set newname for datafile ''
to '';
switch datafile all;
recover tablespace TABLESPACE_NAME;
sql 'ALTER TABLESPACE TABLESPACE_NAME ONLINE';
}

The most time consuming task is moving or copying the file by itself, so this operation goes first, once it's ready, then the datafile goes offline and the switch command is issued, this updates the controlfile contents and performs the recover from the time the tablespace was backed up until it was moved.

This is the part of the process that will generate errors if used with concurrent operation on this.

ERROR at line 1:
ORA-00376: file cannot be read at this time
ORA-01110: data file 7: ''

So a Maintenance window has to be open to be able to work on the affected tablespaces.

What happens with a Dataguard Environment?
It is not affected when datafiles are moved at the primary database since the recovery process looks for the datafile ID, not the datafile location at the destination.

The listener supports no services

This error means in general there are communication issues. It can be generic, so the outline here only depicts a very particular case.

Environment:

• Windows 2003, x86
• Oracle 11.1.0.7.0

When trying to start the listener, it took more time than usual, and after a while it showed up the following messages on the console:

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.1.0.7.0 - Production
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


And when trying to display the services this was shown by the lsnrctl services command:
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=echo.world)(PORT=15
21)))
TNS-12535: TNS:operation timed out
TNS-12560: TNS:protocol adapter error
TNS-00505: Operation timed out
32-bit Windows Error: 60: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

The trace was enabled and the stack of messages was recorded at the trace file. By taking a look at the file a particular message came to my attention:
...
2011-07-05 15:09:59.231337 : nsinh_hoff:connection inherited
2011-07-05 15:09:59.231349 : nsinherit:connected
2011-07-05 15:09:59.237138 : nsglma:Listener's pid=3056
2011-07-05 15:09:59.249332 : nsglbgetRSPidx:returning ecode=0
2011-07-05 15:09:59.249389 : nsc2addr:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=echo.oracle.com)(PORT=1521)))
2011-07-05 15:09:59.249561 : snlinGetAddrInfo:getaddrinfo() failed with error 11001
2011-07-05 15:09:59.249633 : nttbnd2addr:looking up IP addr for host: echo.oracle.com

From this point on, several other errors showed up, but this is the first point where everything began to be not normal.

The call getaddrinfo() failed, which means there was an inconsistency when trying to resolve the host IP address.

By taking a look at the hosts file and the ipconfig command output the discrepancies appeared.
Contents at the windows hosts file:
192.168.50.78 echo.world echo

ipconfig output:

C:\Oracle\app\product\11.1.0\db_1\NETWORK\ADMIN>ipconfig
Windows IP Configuration

Ethernet adapter Loopback Adapter:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.2.115
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . : world
IP Address. . . . . . . . . . . . : 192.168.50.68
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.50.1

The network adapter is configured in a DHCP environment, and according to the Oracle installation manual for DHCP environments, a loopback adapter has to be configured, and its address is the one configured at the local hosts file.

So configuring the adapters according to the installation guide it makes the system to run as expected.