Moving Datafile from File System to ASM

If datafile is created on file system instead of ASM, following procedure can be applied to move datafile to ASM


SQL > select NAME,STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                       STATUS         ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn         ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME .nnn.nnn         ONLINE         READ WRITE

SQL > ALTER TABLESPACE TS_NAME ADD DATAFILE 'DATA' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 10M;

NAME                                                                              STATUS         ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE

/u01/app/.../DBNAME/dbs/DATA                             ONLINE         READ WRITE  <- asm="" at="" created="" file="" font="" instead="" of="" system=""> 

Take the datafile to offline:

SQL> alter database datafile '/u01/app/.../DBNAME/dbs/DATA' offline drop;

Database altered.

SQL > select NAME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

/u01/app/.../DBNAME/dbs/DATA                        RECOVER       READ WRITE

Copy datafile to ASM using RMAN. If you use asmcmd for file copy then file will be created as non-oracle managed file (with alias)

RMAN> COPY DATAFILE ' /u01/app/.../DBNAME/dbs/DATA' TO '+DATA';

Starting backup at ....

allocated channel...

...

channel ORA_DISK_1: starting datafile copy

input datafile file number=nnnnn name= /u01/app/.../DBNAME/dbs/DATA

output file name=+DATA/DBNAME/datafile/TS_NAME.nnn.nnn  tag=...

channel ORA_DISK_1: datafile copy complete, elapsed time: ...

Finished backup ...

Update file location

SQL> alter tablespace TS_NAME  rename  datafile '/u01/app/.../DBNAME/dbs/DATA' to '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';

Tablespace altered.

Recover datafile and bring it online

SQL> recover datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';

Media recovery complete.

SQL > alter database datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn' online;

Database altered.

SQL > select NAME,CREATION_TIME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

ORA-00942 while creating materialized view

I hit following problem while creating the materialized view with option refresh fast although   SCHEMA2.TABNAME  exists and SCHEMA1 user has select privilege on  it

CREATE MATERIALIZED VIEW SCHEMA1.MVIEW ....
SELECT .... FROM SCHEMA2.TABNAME....

ORA-12018: following error encountered during code generation for  "SCHEMA1"."MVIEW "
ORA-00942: table or view does not exist

Problem is not the source table itself but the materialized view log table created before. In order to solve the problem you should identify  materialized view log table and give select permission to  materialized view owner

select LOG_TABLE from ALL_MVIEW_LOGS where log_owner=' SCHEMA2' and master='TABNAME'

grant select on  SCHEMA2.LOG_TABLE to  SCHEMA1;