Some tips about select COALESCE function in oracle

COALESCE  will return the first not null value. 

It requires at least two arguments

If the arguments entered are of different datatype then it will throw an error

SQL> select  coalesce(1,3,null,'jeet') from dual;
select  coalesce(1,3,null,'jeet') from dual
                          *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
SQL> select  coalesce('a','as',null) from dual;
C
-
a
SQL> select COALESCE(null,null) from dual;
C
-

SQL> select COALESCE (null) from dual;
select COALESCE (null) from dual
       *
ERROR at line 1:
ORA-00938: not enough arguments for function
SQL> select COALESCE('a') from dual;
select COALESCE('a') from dual
       *
ERROR at line 1:
ORA-00938: not enough arguments for function

Getting "ORA-01722: invalid number " while using NVL function

This error occurs if the first and the second parameter are different datatype, example is show below:

SQL> select nvl(1,'jeet') from dual;
select nvl(1,'jeet') from dual
             *
ERROR at line 1:
ORA-01722: invalid number

SQL> select nvl(1,2) from dual;
  NVL(1,2)
----------
         1
SQL> select nvl('jeet','oracle') from dual;
NVL(
----
jeet
SQL>
SQL>  select nvl(sysdate,'characters') from dual;
 select nvl(sysdate,'characters') from dual
                    *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select nvl(sysdate,123) from dual;
select nvl(sysdate,123) from dual
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> select nvl(sysdate, to_date('01-01-11','DD-MM-YY')) from dual;
NVL(SYSDA
---------
14-FEB-11

Easiest way to move oracle database files from one location to another

In this we will only copy the datafiles from one location to another and then create the symbolic link ot the new location.
Basically we are bluffing database, database from OS layer. Database will see that the datafiles are still at the same location.
Physically datafiles are located at different location and symbolic link is making it possible.
This technique helps moving datafiles on different disks without issuing any command in database.

This will be done with an outage and the only two database commands issued are shutdown immediate and startup.

The example of achieving this is demonstrated below:


1. Shutdown the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

2. Copy the files to new location but with the same name of the containing folder. I am copying because I want to keep the backup of the database.

$ pwd
/u01/app/oracle/oradata/new_location
$ ls -lah
total 3.9G
drwxr-xr-x 2 oracle oinstall 4.0K Feb  7 13:48 .
drwxr-x--- 5 oracle oinstall 4.0K Feb  7 11:12 ..
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:35 michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 01:06 redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 08:00 redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:35 redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:35 system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:35 users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 users02.dbf
$ mkdir /u01/app/oracle/oradata/orcl/new_location
$ pwd
/u01/app/oracle/oradata/new_location
$ cp -p * ../orcl/new_location/
$ ls -lah ../orcl/new_location/*
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 ../orcl/new_location/example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:35 ../orcl/new_location/michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:35 ../orcl/new_location/michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 01:06 ../orcl/new_location/redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 08:00 ../orcl/new_location/redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:35 ../orcl/new_location/redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:35 ../orcl/new_location/sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:35 ../orcl/new_location/system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:35 ../orcl/new_location/undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:35 ../orcl/new_location/users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:35 ../orcl/new_location/users02.dbf

3. Rename the original directory to something else

$ pwd
/u01/app/oracle/oradata
$ mv new_location new_location_backup

4. Create the symbolic link on the old location for the new location

$ pwd
/u01/app/oracle/oradata
$ ln -s /u01/app/oracle/oradata/orcl/new_location
$ ls -la
total 44
drwxr-x--- 5 oracle oinstall 4096 Feb 14 09:41 .
drwxr-xr-x 9 oracle oinstall 4096 Nov 24 13:10 ..
lrwxrwxrwx 1 oracle oinstall   41 Feb 14 09:41 new_location -> /u01/app/oracle/oradata/orcl/new_location
drwxr-xr-x 2 oracle oinstall 4096 Feb  7 13:48 new_location_backup
drwxr-x--- 3 oracle oinstall 4096 Feb 14 09:35 orcl

5. Startup database

SQL> startup
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             390071944 bytes
Database Buffers          138412032 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
$ cd /u01/app/oracle/oradata/orcl/new_location
$ ls -lah
total 3.9G
drwxr-xr-x 2 oracle oinstall 4.0K Feb 14 09:38 .
drwxr-x--- 3 oracle oinstall 4.0K Feb 14 09:41 ..
-rw-r----- 1 oracle oinstall  11M Feb 14 09:42 control01.ctl
-rw-r----- 1 oracle oinstall 101M Feb 14 09:41 example01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:41 example02.dbf
-rw-r----- 1 oracle oinstall 101M Feb 14 09:41 michael01.dbf
-rw-r----- 1 oracle oinstall 253M Feb 14 09:41 michael02.dbf
-rw-r----- 1 oracle oinstall  51M Feb 14 09:41 redo01.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:41 redo02.log
-rw-r----- 1 oracle oinstall  51M Feb 14 09:42 redo03.log
-rw-r----- 1 oracle oinstall 1.3G Feb 14 09:41 sysaux01.dbf
-rw-r----- 1 oracle oinstall 741M Feb 14 09:41 system01.dbf
-rw-r----- 1 oracle oinstall 501M Feb 14 09:41 undotbs01.dbf
-rw-r----- 1 oracle oinstall 826M Feb 14 09:41 users01.dbf
-rw-r----- 1 oracle oinstall  11M Feb 14 09:41 users02.dbf

Note that database assume that the files are still at the same location because of the symbolic link

TABLESPACE_NAME      FILE_NAME                                          
-------------------- --------------------------------------------------
EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM               /u01/app/oracle/oradata/new_location/system01.dbf
UNDOTBS1             /u01/app/oracle/oradata/new_location/undotbs01.dbf
USERS                /u01/app/oracle/oradata/new_location/users01.dbf  
USERS                /u01/app/oracle/oradata/new_location/users02.dbf   

Oracle 11 - How to move all the datafiles, redo logs, tempfiles, undo datafile and controlfiles in a database

There are times when you need to move the whole oracle database to another location which includes moving all datafiles (system, undo, sysaux and other datafiles), redo logs, temp files and controlfiles.

I am demonstrating this in five steps with example. The steps are as follows (Please note that I am assuming that there are no queries running on the database and I am the only user connected to the database):

1. Moving all datafiles expect system datafiles
2. Moving oracle temp datafile
3. Moving oracle undo tablespace/datafile 
4. Moving oracle system datafiles
5. Moving oracle redo log files
6. Moving oracle controlfiles


Moving all datafiles expect system datafiles:



SQL> select files.tablespace_name, files.file_name
  2  from
  3  (select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,
  4  decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb
  5  from dba_data_files) files,
  6  (select file_id, sum(bytes)/1024/1024 freemb
  7  from dba_free_space
  8  group by file_id) free
 9  where files.file_id = free.file_id (+)
 10  and files.file_name like '%&Fname%'
 11  order by 1,2;
Enter value for fname:
old  10: and files.file_name like '%&Fname%'
new  10: and files.file_name like '%%'

TABLESPACE_NAME      FILE_NAME                                        
-------------------- --------------------------------------------------
EXAMPLE              /u01/app/oracle/oradata/orcl/example01.dbf      
EXAMPLE              /u01/app/oracle/oradata/orcl/example02.dbf      
MICHAEL              /u01/app/oracle/oradata/orcl/michael01.dbf      
MICHAEL              /u01/app/oracle/oradata/orcl/michael02.dbf      
SYSAUX               /u01/app/oracle/oradata/orcl/sysaux01.dbf        
SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf        
UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf      
USERS                /u01/app/oracle/oradata/orcl/temp/users01.dbf    
USERS                /u01/app/oracle/oradata/orcl/temp/users02.dbf    

SQL> alter tablespace example offline;

Tablespace altered.

SQL> alter tablespace michael offline;

Tablespace altered.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> SELECT NAME,

  2      FILE#,

  3      STATUS,

  4      CHECKPOINT_CHANGE# "CHECKPOINT"   

  5    FROM   V$DATAFILE;

NAME                                            FILE# STATUS  CHECKPOINT

----------------------------------------------------- ------- ----------

/u01/app/oracle/oradata/orcl/system01.dbf           1 SYSTEM    11517939

/u01/app/oracle/oradata/orcl/sysaux01.dbf           2 ONLINE    11517939

/u01/app/oracle/oradata/orcl/undotbs01.dbf          3 ONLINE    11517939

/u01/app/oracle/oradata/orcl/temp/users01.dbf       4 OFFLINE   11536249

/u01/app/oracle/oradata/orcl/example01.dbf          5 OFFLINE   11536178

/u01/app/oracle/oradata/orcl/example02.dbf          6 OFFLINE   11536178

/u01/app/oracle/oradata/orcl/michael01.dbf          7 OFFLINE   11536204

/u01/app/oracle/oradata/orcl/temp/users02.dbf       8 OFFLINE   11536249

/u01/app/oracle/oradata/orcl/michael02.dbf          9 OFFLINE   11536204

$ cp /u01/app/oracle/oradata/orcl/temp/users01.dbf /u01/app/oracle/oradata/new_location/users01.dbf
$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/new_location/example01.dbf
$ cp /u01/app/oracle/oradata/orcl/example02.dbf /u01/app/oracle/oradata/new_location/example02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael01.dbf /u01/app/oracle/oradata/new_location/michael01.dbf
$ cp /u01/app/oracle/oradata/orcl/temp/users02.dbf /u01/app/oracle/oradata/new_location/users02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael02.dbf /u01/app/oracle/oradata/new_location/michael02.dbf
$ cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/new_location/sysaux01.dbf


SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users01.dbf' to '/u01/app/oracle/oradata/new_location/users01.dbf';
SQL>
Tablespace altered.

SQL> alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';
Enter value for ts_name: users
Enter value for old_location: /u01/app/oracle/oradata/orcl/temp/users02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/users02.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users02.dbf' to '/u01/app/oracle/oradata/new_location/users02.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example01.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example01.dbf' to '/u01/app/oracle/oradata/new_location/example01.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example02.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example02.dbf' to '/u01/app/oracle/oradata/new_location/example02.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael01.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael01.dbf' to '/u01/app/oracle/oradata/new_location/michael01.dbf'

Tablespace altered.

SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael02.dbf
old   1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new   1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael02.dbf' to '/u01/app/oracle/oradata/new_location/michael02.dbf'

Tablespace altered.

SQL>  alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';

Enter value for ts_name: sysaux

Enter value for old_location: /u01/app/oracle/oradata/orcl/sysaux01.dbf

Enter value for new_location: /u01/app/oracle/oradata/new_location/sysaux01.dbf

old   1:  alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'

new   1:  alter tablespace sysaux rename datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/new_location/sysaux01.dbf'

Tablespace altered.

SQL> alter tablespace michael online;

Tablespace altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> SELECT NAME,
  2      FILE#,
  3      STATUS,
  4      CHECKPOINT_CHANGE# "CHECKPOINT"
  5    FROM   V$DATAFILE;

NAME                                               FILE# STATUS  CHECKPOINT
-------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf              1 SYSTEM    11517939
/u01/app/oracle/oradata/new_location/sysaux01.dbf              2 ONLINE    11517939
/u01/app/oracle/oradata/orcl/undotbs01.dbf             3 ONLINE    11517939
/u01/app/oracle/oradata/new_location/users01.dbf       4 ONLINE    11536743
/u01/app/oracle/oradata/new_location/example01.dbf     5 ONLINE    11536772
/u01/app/oracle/oradata/new_location/example02.dbf     6 ONLINE    11536772
/u01/app/oracle/oradata/new_location/michael01.dbf     7 ONLINE    11536718
/u01/app/oracle/oradata/new_location/users02.dbf       8 ONLINE    11536743
/u01/app/oracle/oradata/new_location/michael02.dbf     9 ONLINE    11536718

Moving oracle temp datafile:




SQL> SELECT FILE_NAME , TABLESPACE_NAME FROM dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 5M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/app/oracle/oradata/new_location/temp01.dbf' SIZE 100M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE 500M
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;


Tablespace dropped.


SQL> SELECT FILE_NAME,TABLESPACE_NAME  FROM dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME  
-------------------------------------------------- --------------------
/u01/app/oracle/oradata/new_location/temp01.dbf     TEMP            


Moving oracle undo tablespace/datafile:





SQL> alter system set undo_tablespace = '';

System altered.

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/new_location/undotbs01.dbf' size 500M reuse autoextend on maxsize 2000M;    

Tablespace created.

SQL> alter system set undo_tablespace = 'UNDOTBS1';

System altered.

SQL> select files.tablespace_name, files.file_name

  2  from

  3  (select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible, 

  4  decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb

  5  from dba_data_files) files,

  6  (select file_id, sum(bytes)/1024/1024 freemb

  7  from dba_free_space

  8  group by file_id) free

 9  where files.file_id = free.file_id (+)

 10  and files.file_name like '%&Fname%'

 11  order by 1,2;

Enter value for fname: 

old  10: and files.file_name like '%&Fname%'

new  10: and files.file_name like '%%'

TABLESPACE_NAME      FILE_NAME                                         

-------------------- --------------------------------------------------

EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf

EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf

SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf 

SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf         

UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf        

USERS                /u01/app/oracle/oradata/new_location/users01.dbf  

USERS                /u01/app/oracle/oradata/new_location/users02.dbf  

Moving oracle system datafiles:


Moving system file is little tricky we can not bring the system tablespace offline and then copy the files from old to new location and then rename.


SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Below steps will recreate the system datafile at new location:


SQL> alter database backup controlfile to trace;

Database altered.

SQL> !ls -laht /u01/app/oracle/diag/rdbms/orcl/orcl/trace | head -10

total 10M

drwxr-x---  2 oracle oinstall  52K Feb  7 13:12 .

-rw-r-----  1 oracle oinstall 515K Feb  7 13:10 alert_orcl.log

-rw-r-----  1 oracle oinstall 7.3K Feb  7 13:10 orcl_ora_16748.trc

-rw-r-----  1 oracle oinstall  131 Feb  7 13:10 orcl_ora_16748.trm

-rw-r-----  1 oracle oinstall  843 Feb  7 13:06 orcl_ora_17506.trc

-rw-r-----  1 oracle oinstall   60 Feb  7 13:06 orcl_ora_17506.trm

-rw-r-----  1 oracle oinstall  881 Feb  7 13:05 orcl_dbrm_16711.trc

-rw-r-----  1 oracle oinstall   60 Feb  7 13:05 orcl_dbrm_16711.trm

-rw-r-----  1 oracle oinstall  840 Feb  7 13:03 orcl_mman_16717.trc

SQL> !cat  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

.

.

.

.

.

copy the command from startup mount to the semicomma (;)

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 1168

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/orcl/system01.dbf',

  '/u01/app/oracle/oradata/new_location/sysaux01.dbf',

  '/u01/app/oracle/oradata/new_location/undotbs01.dbf',

  '/u01/app/oracle/oradata/new_location/users01.dbf',

  '/u01/app/oracle/oradata/new_location/example01.dbf',

  '/u01/app/oracle/oradata/new_location/example02.dbf',

  '/u01/app/oracle/oradata/new_location/michael01.dbf',

  '/u01/app/oracle/oradata/new_location/users02.dbf',

  '/u01/app/oracle/oradata/new_location/michael02.dbf'

CHARACTER SET WE8MSWIN1252

;

Then change the line  '/u01/app/oracle/oradata/orcl/system01.dbf' with the new location which is '/u01/app/oracle/oradata/new_location/system01.dbf' do the following steps.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/new_location/system01.dbf

SQL> STARTUP NOMOUNT

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1337720 bytes

Variable Size             385877640 bytes

Database Buffers          142606336 bytes

Redo Buffers                5840896 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 1168

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/u01/app/oracle/oradata/new_location/system01.dbf',

 14    '/u01/app/oracle/oradata/new_location/sysaux01.dbf',

 15    '/u01/app/oracle/oradata/new_location/undotbs01.dbf',

 16    '/u01/app/oracle/oradata/new_location/users01.dbf',

 17    '/u01/app/oracle/oradata/new_location/example01.dbf',

 18    '/u01/app/oracle/oradata/new_location/example02.dbf',

 19    '/u01/app/oracle/oradata/new_location/michael01.dbf',

 20    '/u01/app/oracle/oradata/new_location/users02.dbf',

 21    '/u01/app/oracle/oradata/new_location/michael02.dbf'

 22  CHARACTER SET WE8MSWIN1252

 23  ;

Control file created.

SQL> alter database open;

Database altered.

SQL> select files.tablespace_name, files.file_name

  2  from

  3  (select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible, 

  4  decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb

  5  from dba_data_files) files,

  6  (select file_id, sum(bytes)/1024/1024 freemb

  7  from dba_free_space

  8  group by file_id) free

 9  where files.file_id = free.file_id (+)

 10  and files.file_name like '%&Fname%'

 11  order by 1,2;

Enter value for fname: 

old  10: and files.file_name like '%&Fname%'

new  10: and files.file_name like '%%'

TABLESPACE_NAME      FILE_NAME                                         

-------------------- --------------------------------------------------

EXAMPLE              /u01/app/oracle/oradata/new_location/example01.dbf

EXAMPLE              /u01/app/oracle/oradata/new_location/example02.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael01.dbf

MICHAEL              /u01/app/oracle/oradata/new_location/michael02.dbf

SYSAUX               /u01/app/oracle/oradata/new_location/sysaux01.dbf 

SYSTEM               /u01/app/oracle/oradata/new_location/system01.dbf 

UNDOTBS1             /u01/app/oracle/oradata/new_location/undotbs01.dbf

USERS                /u01/app/oracle/oradata/new_location/users01.dbf  

USERS                /u01/app/oracle/oradata/new_location/users02.dbf  

Moving oracle redo log files:

SQL> select     lf.member

  2  from       v$logfile lf

  3  ,  v$log lg

  4  where      lg.group# = lf.group#

  5  order      by 1;

MEMBER                                  

----------------------------------------

/u01/app/oracle/oradata/orcl/redo01.log 

/u01/app/oracle/oradata/orcl/redo02.log 

/u01/app/oracle/oradata/orcl/redo03.log 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cp /u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/new_location/redo01.log

SQL> !cp /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/new_location/redo02.log

SQL> !cp /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/new_location/redo03.log

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1337720 bytes

Variable Size             385877640 bytes

Database Buffers          142606336 bytes

Redo Buffers                5840896 bytes

Database mounted.

SQL> 

SQL> 

SQL> alter database rename file '&old_redo_file' to '&new_redo_file';

Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo01.log

Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo01.log

old   1: alter database rename file '&old_redo_file' to '&new_redo_file'

new   1: alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/new_location/redo01.log'

Database altered.

SQL> /

Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo02.log

Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo02.log

old   1: alter database rename file '&old_redo_file' to '&new_redo_file'

new   1: alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/new_location/redo02.log'

Database altered.

SQL> /

Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo03.log

Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo03.log

old   1: alter database rename file '&old_redo_file' to '&new_redo_file'

new   1: alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/new_location/redo03.log'

Database altered.

SQL> alter database open;

Database altered.

SQL> select     lf.member

  2  from       v$logfile lf

  3  ,  v$log lg

  4  where      lg.group# = lf.group#

  5  order      by 1;

MEMBER                                          

------------------------------------------------

/u01/app/oracle/oradata/new_location/redo01.log 

/u01/app/oracle/oradata/new_location/redo02.log 

/u01/app/oracle/oradata/new_location/redo03.log 

Moving oracle controlfiles:

SQL> select name from v$controlfile;

NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> show parameter control_files

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 flash_recovery_area/orcl/contr

                                                 ol02.ctl

SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 flash_recovery_area/orcl/contr

                                                 ol02.ctl

SQL> select name from v$controlfile;

NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/orcl/contr
                                                 ol02.ctl
SQL> select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl



All the files are moved to new directory


SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/orcl/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/orcl/contr
                                                 ol02.ctl
SQL> select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

I hope above steps will help you.

ORA-01410: invalid ROWID

This error will occur if you are trying to find the row from a table using the ROWID which does not exist in the table.

Example:


SQL> select * from dual where rowid='a';
select * from dual where rowid='a'
                               *
ERROR at line 1:
ORA-01410: invalid ROWID


SQL> select rowid from dual;

ROWID
------------------
AAAAB0AABAAAAOhAAA

SQL> select * from dual where rowid ='AAAAB0AABAAAAOhAAA';

D
-
X