How to move a datafile from a file system to ASM - System Tablespace - 1
Moving oracle system datafile while the database is in mount stage is performed in the following way:
1) Add the sample datafile in system tablespace location of the file system.
SQL> alter tablespace system add datafile 'system1' size 500m;
Tablespace altered.
2) Ensure you have enough space in the ASM diskgroup to copy the datafile. First identify the size of the datafile you wish to move.
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;
FILE# NAME FILE_SIZE_MB
---------- ---------------------------------------------------------------------- ------------
1 +DATA01/TSTWPS/DATAFILE/system.278.920287861 790
2 +DATA01/TSTWPS/DATAFILE/enc.297.920408943 10
3 +DATA01/TSTWPS/DATAFILE/sysaux.277.920287797 780
4 +DATA01/TSTWPS/DATAFILE/undotbs1.280.920287929 205
5 +DATA01/TSTWPS/DATAFILE/undotbs2.285.920288099 50
6 +DATA01/TSTWPS/DATAFILE/users.279.920287927 5
7 /u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1 500
7 rows selected.
3) Stop the RAC database and start the database as a mount stage using the below command.
[oracle@exadb01 admin]$ srvctl stop database -d tstwps
[oracle@exadb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:11:58 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1929379840 bytes
Fixed Size 2942352 bytes
Variable Size 444598896 bytes
Database Buffers 1476395008 bytes
Redo Buffers 5443584 bytes
Database mounted.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
4) Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.
[oracle@exadb01 admin]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 11:12:34 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TSTWPS (DBID=591086125, not open)
RMAN> copy datafile '/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1' to '+DATA01';
Starting backup at 28-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=TSTWPS1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=59 instance=TSTWPS1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1
output file name=+DATA01/TSTWPS/DATAFILE/system.298.926421235 tag=TAG20161028T111353 RECID=1 STAMP=926421250
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 28-OCT-16
Starting Control File and SPFILE Autobackup at 28-OCT-16
piece handle=+RECO01/TSTWPS/AUTOBACKUP/2016_10_28/s_926421065.380.926421263 comment=NONE
Finished Control File and SPFILE Autobackup at 28-OCT-16
RMAN>
Recovery Manager complete.
5) Update the controlfile with the new location of the datafile.
[oracle@exadb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:15:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database rename file '/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1' to '+DATA01/TSTWPS/DATAFILE/system.298.926421235';
Database altered.
6. Restart the database and check the file is now if the new location.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@exadb01 admin]$ srvctl start database -d tstwps
[oracle@exadb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:18:42 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA01/TSTWPS/DATAFILE/system.278.920287861
+DATA01/TSTWPS/DATAFILE/enc.297.920408943
+DATA01/TSTWPS/DATAFILE/sysaux.277.920287797
+DATA01/TSTWPS/DATAFILE/undotbs1.280.920287929
+DATA01/TSTWPS/DATAFILE/undotbs2.285.920288099
+DATA01/TSTWPS/DATAFILE/users.279.920287927
+DATA01/TSTWPS/DATAFILE/system.298.926421235
7 rows selected.
SQL>
7) The system datafile has been successfully moved to the ASM diskgroup.
1) Add the sample datafile in system tablespace location of the file system.
SQL> alter tablespace system add datafile 'system1' size 500m;
Tablespace altered.
2) Ensure you have enough space in the ASM diskgroup to copy the datafile. First identify the size of the datafile you wish to move.
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;
FILE# NAME FILE_SIZE_MB
---------- ---------------------------------------------------------------------- ------------
1 +DATA01/TSTWPS/DATAFILE/system.278.920287861 790
2 +DATA01/TSTWPS/DATAFILE/enc.297.920408943 10
3 +DATA01/TSTWPS/DATAFILE/sysaux.277.920287797 780
4 +DATA01/TSTWPS/DATAFILE/undotbs1.280.920287929 205
5 +DATA01/TSTWPS/DATAFILE/undotbs2.285.920288099 50
6 +DATA01/TSTWPS/DATAFILE/users.279.920287927 5
7 /u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1 500
7 rows selected.
3) Stop the RAC database and start the database as a mount stage using the below command.
[oracle@exadb01 admin]$ srvctl stop database -d tstwps
[oracle@exadb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:11:58 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1929379840 bytes
Fixed Size 2942352 bytes
Variable Size 444598896 bytes
Database Buffers 1476395008 bytes
Redo Buffers 5443584 bytes
Database mounted.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
4) Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.
[oracle@exadb01 admin]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 11:12:34 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TSTWPS (DBID=591086125, not open)
RMAN> copy datafile '/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1' to '+DATA01';
Starting backup at 28-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=TSTWPS1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=59 instance=TSTWPS1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1
output file name=+DATA01/TSTWPS/DATAFILE/system.298.926421235 tag=TAG20161028T111353 RECID=1 STAMP=926421250
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 28-OCT-16
Starting Control File and SPFILE Autobackup at 28-OCT-16
piece handle=+RECO01/TSTWPS/AUTOBACKUP/2016_10_28/s_926421065.380.926421263 comment=NONE
Finished Control File and SPFILE Autobackup at 28-OCT-16
RMAN>
Recovery Manager complete.
5) Update the controlfile with the new location of the datafile.
[oracle@exadb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:15:13 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database rename file '/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system1' to '+DATA01/TSTWPS/DATAFILE/system.298.926421235';
Database altered.
6. Restart the database and check the file is now if the new location.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@exadb01 admin]$ srvctl start database -d tstwps
[oracle@exadb01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:18:42 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA01/TSTWPS/DATAFILE/system.278.920287861
+DATA01/TSTWPS/DATAFILE/enc.297.920408943
+DATA01/TSTWPS/DATAFILE/sysaux.277.920287797
+DATA01/TSTWPS/DATAFILE/undotbs1.280.920287929
+DATA01/TSTWPS/DATAFILE/undotbs2.285.920288099
+DATA01/TSTWPS/DATAFILE/users.279.920287927
+DATA01/TSTWPS/DATAFILE/system.298.926421235
7 rows selected.
SQL>
7) The system datafile has been successfully moved to the ASM diskgroup.
Comments
Post a Comment