sábado, 17 de septiembre de 2016

Oracle Referencia Rápida: Como mover un "datafile" de un filesystem a un Grupo de ASM


SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string      +FRA
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter system set db_create_file_dest='/opt/oracle/app/oradata/lab1' scope=both;

System altered.

SQL> create tablespace tbs_mover_data;

Tablespace created.

SQL> select file_name from dba_data_files 
where tablespace_name ='TBS_MOVER_DATA';

FILE_NAME
----------------------------------------------------------------------------
/opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf

SQL> alter tablespace tbs_mover_data offline;

Tablespace altered.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
----------------------------------------------------------------------------
+DATA/lab1/datafile/users.259.915448727
+DATA/lab1/datafile/undotbs1.258.915448727
+DATA/lab1/datafile/sysaux.257.915448727
+DATA/lab1/datafile/system.256.915448727
+DATA/lab1/datafile/example.269.915448839
+DATA/lab1/datafile/tbs_datos_nuevos.265.922792725
/opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf

7 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@lab1 ~]$ ./connect_rman.sh
ORACLE_SID = [lab1] ?
The Oracle base remains unchanged with value /opt/oracle/app

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Sep 17 11:47:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LAB1 (DBID=2468432034)
connected to recovery catalog database

RMAN> copy datafile
2> /opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf' to '+DATA';

Starting backup at 17-SEP-16
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf
output file name=+DATA/lab1/datafile/tbs_mover_data.264.922794551 tag=TAG20160917T114911 RECID=2 STAMP=922794556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 17-SEP-16

Starting Control File and SPFILE Autobackup at 17-SEP-16
piece handle=/opt/backups/control_bkc-2468432034-20160917-04 comment=NONE
Finished Control File and SPFILE Autobackup at 17-SEP-16

RMAN> exit

Recovery Manager complete.
[oracle@lab1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 17 11:50:00 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
------------------------------------------------------------------------
+DATA/lab1/datafile/users.259.915448727
+DATA/lab1/datafile/undotbs1.258.915448727
+DATA/lab1/datafile/sysaux.257.915448727
+DATA/lab1/datafile/system.256.915448727
+DATA/lab1/datafile/example.269.915448839
+DATA/lab1/datafile/tbs_datos_nuevos.265.922792725
/opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf

7 rows selected.

SQL> alter database rename
  2  file '/opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf'
  3  to
  4  '+DATA/lab1/datafile/tbs_mover_data.264.922794551';

Database altered.

SQL> alter tablespace tbs_mover_data online;

Tablespace altered.

SQL> !rm /opt/oracle/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_move_cxv0copg_.dbf

SQL>

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
-------------------------------------------------------
+DATA/lab1/datafile/users.259.915448727
+DATA/lab1/datafile/undotbs1.258.915448727
+DATA/lab1/datafile/sysaux.257.915448727
+DATA/lab1/datafile/system.256.915448727
+DATA/lab1/datafile/example.269.915448839
+DATA/lab1/datafile/tbs_datos_nuevos.265.922792725
+DATA/lab1/datafile/tbs_mover_data.264.922794551

7 rows selected.

No hay comentarios:

Publicar un comentario en la entrada

Te agradezco tus comentarios. Te esperamos de vuelta.