September 24, 2015

How to move a datafile from filesystem to ASM

We can move the datafile from filesystem to ASM by bringing the corresponding tablespace offline or the only datafile which you needs to be move. We can see these 2 scenarios.

 Here we have added the datafile wrongly due to that datafile is not created in a specified location.

 SQL> alter tablespace TCB_TSQ01 add datafile 'CRM_DBF01' size 100m autoextend on next 20m maxsize 32g;

Tablespace altered.


 
I) Tablespace offline:
******************

1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';



FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                  TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32



6 rows selected.


2. Take the corresponding tablespace offline. 

SQL> alter tablespace TCB_TSQ01 offline;



Tablespace altered.


3. Copy the datafile to the desired location using RMAN
-bash-4.1$ rman target /




RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';



Starting backup at 20-SEP-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01

output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-SEP-15



Starting Control File and SPFILE Autobackup at 20-SEP-15

piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE

Finished Control File and SPFILE Autobackup at 20-SEP-15



RMAN> exit




Recovery Manager complete.


4. Update the controfile with the new location of the datafile.



SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';



Database altered.



SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';



FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01



6 rows selected.

5. Bring the tablespace to the online.



SQL> alter tablespace TCB_TSQ01 online;



Tablespace altered.



SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';



FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32



6 rows selected.

 II) Datafile Offline:
****************
1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------
 /u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                          TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

2. Take the datafile offline.

SQL> alter database datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' offline;

Database altered.

3. Copy the datafile to the desired location using RMAN

-bash-4.1$ rman target /

RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';

Starting backup at 20-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01
output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-SEP-15

Starting Control File and SPFILE Autobackup at 20-SEP-15
piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE
Finished Control File and SPFILE Autobackup at 20-SEP-15

RMAN> exit

Recovery Manager complete.

4. Update the controlfile with the new location of the datafile.

SQL> set pages 200 lines 200
SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

Database altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------
+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                               
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

5. Recover the datafile using RMAN.

RMAN> recover datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

6. Bring the datafile to the online.

SQL> alter database datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477' online;

Tablespace altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- -------------
+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

Note:
*****
If your oracle version is 12c you can execute the below statement is enough to move the datafile. No need to perform all the above steps.

SQL>  alter database move datafile
'/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to ' +CRM_DBF01';
Happy Learning :)
Posted by at No comments:
Labels:

September 2, 2015

[INS-35172] Target database memory (XXXMB) exceeds the systems available shared memory ({0} MB) solution


If you receives the above error while installation of Oracle database, Please follow as below.

You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm filesystem to be mounted for at least maximum size than you specified at the time of installation. 

df -h /dev/shm will show you the current size of your shared memory file system.

[oracle@node1 Desktop]$ df -h /dev/shm
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 504M  272K  503M   1% /dev/shm

The size of this file is the real size of the key control AMM.  Modify the size of the tmpfs greater than the size of MEMORY_TARGET. Modify the /etc/fstab file  like below.


To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory. However this is temporary

# umount tmpfs
# mount -t tmpfs shmfs -o size=2G /dev/shm




Make the setting permanent by amending the "tmpfs" setting of
the "/etc/fstab" file to look like this.

After the above change if you check shared memory file system value will effect like below.





[oracle@node1 Desktop]$ df -h /dev/shm



Filesystem            Size  Used Avail Use% Mounted on



tmpfs                     2G  272K  2G   1% /dev/shm









Before:



*******



[root@node1 ~]# cat /etc/fstab







#



# /etc/fstab



# Created by anaconda on Sat Jan 10 19:24:12 2015



#



# Accessible filesystems, by reference, are maintained under '/dev/disk'



# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more
info



#



/dev/mapper/vg_node1-lv_root /            
          ext4    defaults    
   1 1



UUID=510fd298-43dc-419d-a3ae-829e999d2430 /boot      
            ext4    defaults  
     1 2



/dev/mapper/vg_node1-lv_swap swap          
         swap    defaults    
   0 0



tmpfs                  
/dev/shm                tmpfs  
defaults        0 0



devpts                
 /dev/pts                devpts
 gid=5,mode=620  0 0



sysfs                  
/sys                    sysfs
  defaults        0 0



proc                  
 /proc                   proc
   defaults        0 0







After:



*****



[root@node1 ~]# cat /etc/fstab







#



# /etc/fstab



#Created by anaconda on Sat Jan 10 19:24:12 2015



#



# Accessible filesystems, by reference, are maintained under '/dev/disk'



# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more
info



#



/dev/mapper/vg_node1-lv_root /            
          ext4    defaults    
   1 1



UUID=510fd298-43dc-419d-a3ae-829e999d2430 /boot      
            ext4    defaults  
     1 2



/dev/mapper/vg_node1-lv_swap swap          
         swap    defaults    
   0 0



tmpfs                  
/dev/shm                tmpfs  
defaults,size=2G  0 0



devpts                
 /dev/pts                devpts
 gid=5,mode=620  0 0



sysfs                  
/sys                    sysfs
  defaults        0 0





proc                  
 /proc                   proc
   defaults        0 0







To avoid reboot after editing /etc/fstab you can do:






umount tmpfs
mount -a









Happy Learning :)












Posted by



at





No comments:
  


















Labels:

















        

      









Subscribe to:
Posts (Atom)