Monthly Archives: July 2007

Moving datafiles,control files and log files – Part 1

Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can’t take SYSTEM tablespace offline)


SYS@orcl AS SYSDBA> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SYS@orcl AS SYSDBA>

Well lets try moving USERS tablespace.


SYS@orcl AS SYSDBA> column file_name format a50

SYS@orcl AS SYSDBA> set lines 100
SYS@orcl AS SYSDBA> select file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORCL\USERS01.DBF                         USERS

SYS@orcl AS SYSDBA>

The current location of the datafile is C:\ORACLE\ORCL\. Suppose I have to move it to c:\oracle\oradata. So first lets take the tablespace offline


SYS@orcl AS SYSDBA> alter tablespace users offline;

Tablespace altered.

SYS@orcl AS SYSDBA>

Now copy the datafile to new location [Note the new directory should be already created]


C:\oracle\ORCL>copy USERS01.DBF c:\oracle\oradata
1 file(s) copied.

C:\oracle\ORCL>

Now make database aware of the new location of the datafile using alter database rename file:


SYS@orcl AS SYSDBA> alter database rename file 'c:\oracle\orcl\users01.dbf' to 'c:\oracle\oradata\users01.dbf';

Database altered.

SYS@orcl AS SYSDBA>

The last thing is to bring the tablespace online. If everything has gone rightly the message like this will appear and you can view the new location of the datafile.


SYS@orcl AS SYSDBA> alter tablespace users online;

Tablespace altered.

SYS@orcl AS SYSDBA> select file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\USERS01.DBF                      USERS

SYS@orcl AS SYSDBA>

In next post we will discuss moving datafiles, controlfiles and logfiles.

Batch file for ftp’ing files…

Today I came across a requirement where users needed to ftp files time and again. So ftp’ing again and again is not a very good option. I wrote a small batch file for the same. Just sharing the same over here. I created a folder ftp in C drive and a file get_file.bat
Contents of get_file.bat are:


set /p file_name=Enter the name of the file you want to ftp:
echo oracle>c:\ftp\param.cfg
echo oracle123>>c:\ftp\param.cfg
echo cd /home/oracle>>c:\ftp\param.cfg
echo lcd c:\ftp>>c:\ftp\param.cfg
bin
get %file_name%
ftp -s:param.cfg 127.0.0.1

It will create a file param.cfg having all the things like username, password and command to get the file in the same folder (c:\ftp). Then we invoke ftp with -s option with specifying the file param.cfg. It will ask the user to enter the file name and ftp the file from server to c:\ftp

Sidhu

Import ORA-01435: user does not exist…

One may encounter this error while importing from a dmp file from older versions of Oracle. Genereally this error is caused by some statement like alter session set current_schema=scott; And the simple reason is that the user scott doesn’t exist. Yesterday I came across this error. And the reason was that user was not created. As in case of import we generally create tablespaces first (by creating the DDL using option show=Y) but creation of users is done by import itself. In older versions of Oracle, the temp tablespaces were no different from other tablespaces. But in newer versions temp tablespaces are different. So in dmp files from thoese older versions create user statements are written like create user t1 identified by t1 default tablespace temp temporary tablespace temp. This thing worked fine in older versions but in newer versions we cannot specify the TEMP tablespace as the default tablespace for a user. So the statement create user t2 identified by t2 default tablespace temp temporary tablespace temp throws ORA-12910: cannot specify temporary tablespace as default tablespace. In such cases the users (for which default statement is specified as TEMP) have to be created manually by specifying the appropriate tablespace as default tablespace and then the import should be run with ignore=Y.

Sidhu

Hats off…

Since I switched to this new job, my profile has changed. Here I work as a DBA. So my interaction with Oracle & anything related to Oracle has also increased. Started exploring Oracle related forums and websites specially OTN forums (http://forums.oracle.com) This is the only page thats almost always open on my Desktop in office & Laptop at home and I refresh more than my office Lotus Notes. Today I was reading APCs blog , that Jonathan Lewis has also started posting on OTN forums and being so busy man, from where he finds the time ? I too think the same. There are so many people having many years of experience in industry, answering the questions on OTN, uesnet groups and various other forums and everything is for free. They are not paid anything for the same thing. Its like taking time out of your time, understand somebody’s problem, create same scenario your PC, try out and then post the answer ! I am, sort of new to the forums and sometimes for whole of week, I am unable to post any answers, even knowing something about the issue someone has posted. Just the “time” thing. These days I am very close to OTN forums, visit for whole of the day and also post answers to the questions I know something about. There are many people who are regular visitors and are answering questions on the daily basis. Hats off ! to all these “big bosses” of the technology !

Sidhu