Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article describes how to move a database in the file system into ASM. First, Grid Infrastructure installation is required. We are creating two disk groups named DATA and FRA. The following steps are as follows.
First of all, we close the database and create a pfile from the existing spfile. We need to add new parameters in Pfile.
After these changes, we will need to create spfile in ASM.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [root@oradb ~]# su - oracle [oracle@oradb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 14:24:52 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> create pfile='/tmp/initORCL.ora' from spfile; File created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options |
Add the following parameters to “/tmp/initORCL.ora”.
1 2 3 4 5 6 7 8 | *.control_files=(+DATA, +FRA) *.db_recovery_file_dest=+FRA *.db_recovery_file_dest_size=5368709120 *.db_create_file_dest=+DATA *.db_create_online_log_dest_1=+FRA [oracle@oradb ~]$ vi /tmp/initORCL.ora |
We’re backing up the existing spfile under “$ORACLE_HOME/dbs” and copying the updated pfile to this directory.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [oracle@oradb ~]$ cd $ORACLE_HOME/dbs [oracle@oradb dbs]$ mv spfileORCL.ora spfileORCL.ora.31082015 [oracle@oradb dbs]$ mv initORCL.ora initORCL.ora.31082015 [oracle@oradb dbs]$ mv /tmp/initORCL.ora . [oracle@oradb dbs]$ ls -ltr total 28 -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r-----. 1 oracle oinstall 605 Aug 26 16:15 initORCL.ora.31082015 -rw-r-----. 1 oracle oinstall 24 Aug 26 16:16 lkORCL -rw-r-----. 1 oracle oinstall 1536 Aug 26 17:07 orapwORCL -rw-r-----. 1 oracle oinstall 3584 Aug 30 22:02 spfileORCL.ora.31082015 -rw-rw----. 1 oracle oinstall 1544 Aug 31 14:24 hc_ORCL.dat -rw-r--r-- 1 oracle oinstall 949 Aug 31 14:27 initORCL.ora |
We create spfile in ASM.
1 2 3 4 5 6 7 8 9 10 11 | [oracle@oradb dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 14:29:11 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. |
We are opening the database in nomount mode for the control file restore operation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | SQL> startup nomount ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2260088 bytes Variable Size 2466251656 bytes Database Buffers 1795162112 bytes Redo Buffers 12107776 bytes SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oradb dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 31 14:30:41 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore controlfile from '/home/app/oracle/oradata/ORCL/control01.ctl'; Starting restore at 31-AUG-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=398 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATA/ORCL/controlfile/current.256.889194645 output file name=+FRA/ORCL/controlfile/current.256.889194645 Finished restore at 31-AUG-15 RMAN> startup mount database is already started database mounted released channel: ORA_DISK_1 RMAN> configure device type disk parallelism 4; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored |
We create a copy of the database in + DATA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA'; Starting backup at 31-AUG-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=398 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=399 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=14 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=400 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/home/app/oracle/oradata/ORCL/hope_data01.dbf channel ORA_DISK_2: starting datafile copy input datafile file number=00007 name=/home/app/oracle/oradata/ORCL/hope_index01.dbf channel ORA_DISK_3: starting datafile copy input datafile file number=00002 name=/home/app/oracle/oradata/ORCL/sysaux01.dbf channel ORA_DISK_4: starting datafile copy input datafile file number=00001 name=/home/app/oracle/oradata/ORCL/system01.dbf output file name=+DATA/ORCL/datafile/system.260.889194691 tag=TAG20150831T143129 RECID=2 STAMP=889194743 channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_4: starting datafile copy input datafile file number=00003 name=/home/app/oracle/oradata/ORCL/undotbs01.dbf output file name=+DATA/ORCL/datafile/sysaux.259.889194691 tag=TAG20150831T143129 RECID=3 STAMP=889194749 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:02 channel ORA_DISK_3: starting datafile copy input datafile file number=00005 name=/home/app/oracle/oradata/ORCL/example01.dbf output file name=+DATA/ORCL/datafile/example.262.889194753 tag=TAG20150831T143129 RECID=4 STAMP=889194770 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_3: starting datafile copy copying current control file output file name=+DATA/ORCL/controlfile/backup.263.889194777 tag=TAG20150831T143129 RECID=5 STAMP=889194778 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_3: starting datafile copy input datafile file number=00004 name=/home/app/oracle/oradata/ORCL/users01.dbf output file name=+DATA/ORCL/datafile/undotbs1.261.889194745 tag=TAG20150831T143129 RECID=6 STAMP=889194778 channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_4: starting full datafile backup set channel ORA_DISK_4: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_4: starting piece 1 at 31-AUG-15 output file name=+DATA/ORCL/datafile/users.264.889194781 tag=TAG20150831T143129 RECID=7 STAMP=889194781 channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:02 channel ORA_DISK_4: finished piece 1 at 31-AUG-15 piece handle=+DATA/ORCL/backupset/2015_08_31/nnsnf0_tag20150831t143129_0.265.889194781 tag=TAG20150831T143129 comment=NONE channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01 output file name=+DATA/ORCL/datafile/hope_data.257.889194689 tag=TAG20150831T143129 RECID=8 STAMP=889194909 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:47 output file name=+DATA/ORCL/datafile/hope_index.258.889194689 tag=TAG20150831T143129 RECID=9 STAMP=889194932 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:04:07 Finished backup at 31-AUG-15 |
With the following switch operation, we set the copies in +DATA as the actual files of the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | RMAN> SWITCH DATABASE TO COPY; datafile 1 switched to datafile copy "+DATA/ORCL/datafile/system.260.889194691" datafile 2 switched to datafile copy "+DATA/ORCL/datafile/sysaux.259.889194691" datafile 3 switched to datafile copy "+DATA/ORCL/datafile/undotbs1.261.889194745" datafile 4 switched to datafile copy "+DATA/ORCL/datafile/users.264.889194781" datafile 5 switched to datafile copy "+DATA/ORCL/datafile/example.262.889194753" datafile 6 switched to datafile copy "+DATA/ORCL/datafile/hope_data.257.889194689" datafile 7 switched to datafile copy "+DATA/ORCL/datafile/hope_index.258.889194689" RMAN> alter database open; database opened RMAN> exit Recovery Manager complete. |
The database is now running in ASM, and the database files in the file system are marked as copies.
We can begin operations after the switch. First of all, we will create temp tablespace in ASM and drop the current temp tablespace.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | [oracle@oradb dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 14:36:20 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: 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 SQL> select name, bytes from v$tempfile; NAME -------------------------------------------------------------------------------- BYTES ---------- /home/app/oracle/oradata/ORCL/temp01.dbf 66060288 SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M; Tablespace created. SQL> alter database default temporary tablespace temp1; Database altered. SQL> drop tablespace temp including contents; Tablespace dropped. SQL> create temporary tablespace temp tempfile SIZE 1G extent management local uniform size 10M; Tablespace created. SQL> alter database default temporary tablespace temp; Database altered. SQL> drop tablespace temp1 including contents; Tablespace dropped. |
We complete Redolog operations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2260088 bytes Variable Size 2466251656 bytes Database Buffers 1795162112 bytes Redo Buffers 12107776 bytes Database mounted. Database opened. SQL> show parameter control; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DATA/ORCL/controlfile/curr ent.256.889194645, +FRA/dcltes t/controlfile/current.256.8891 94645 control_management_pack_access string DIAGNOSTIC+TUNING SQL> show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 5192M SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /home/app/oracle/oradata/ORCL/redo03.log /home/app/oracle/oradata/ORCL/redo02.log /home/app/oracle/oradata/ORCL/redo01.log SQL> alter database add logfile thread 1 group 21 ('+DATA') size 100M; Database altered. SQL> alter database add logfile thread 1 group 22 ('+DATA') size 100M; Database altered. SQL> alter database add logfile thread 1 group 23 ('+DATA') size 100M; Database altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter database drop logfile group 1; Database altered. |
If you encounter with an error such as the following when you drop the log file, you can correct it with the redo log switch and checkpoint operations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | SQL> alter database drop logfile group 2; alter database drop logfile group 2 * ERROR at line 1: ORA-01624: log 2 needed for crash recovery of instance ORCL (thread 1) ORA-00312: online log 2 thread 1: '/home/app/oracle/oradata/ORCL/redo02.log' SQL> alter database drop logfile group 3; Database altered. SQL> alter system checkpoint; System altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile thread 1 group 1 ('+DATA') size 100M; Database altered. SQL> alter database add logfile thread 1 group 2 ('+DATA') size 100M; Database altered. SQL> alter database add logfile thread 1 group 3 ('+DATA') size 100M; Database altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system checkpoint; System altered. SQL> alter database drop logfile group 21; Database altered. SQL> alter database drop logfile group 22; Database altered. SQL> alter database drop logfile group 23; Database altered. SQL> alter database add logfile member '+FRA' to group 1; Database altered. SQL> alter database add logfile member '+FRA' to group 2; Database altered. SQL> alter database add logfile member '+FRA' to group 3; Database altered. 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 |
Finally, we delete the copy in the file system.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | [oracle@oradb dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 31 15:00:27 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=2455775002) RMAN> DELETE COPY OF DATABASE; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=410 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=418 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=18 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- --------------- 10 1 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/system01.dbf 11 2 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/sysaux01.dbf 12 3 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/undotbs01.dbf 13 4 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/users01.dbf 14 5 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/example01.dbf 15 6 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/hope_data01.dbf 16 7 A 31-AUG-15 121206512 31-AUG-15 Name: /home/app/oracle/oradata/ORCL/hope_index01.dbf Do you really want to delete the above objects (enter YES or NO)? YES deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/system01.dbf RECID=10 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/sysaux01.dbf RECID=11 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/undotbs01.dbf RECID=12 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/users01.dbf RECID=13 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/example01.dbf RECID=14 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/hope_data01.dbf RECID=15 STAMP=889194941 deleted datafile copy datafile copy file name=/home/app/oracle/oradata/ORCL/hope_index01.dbf RECID=16 STAMP=889194941 Deleted 7 objects RMAN> exit Recovery Manager complete. |
Subsequent operations are required to automatically open the database and view the correct spfile from the pfile.
spfile pfile operations:
Because the spfile is now in the +DATA disk group, the initORCL.ora (pfile) file under dbs must also point to this spfile.
We change the contents of the initORCL.ora file under the $ORACLE_HOME/dbs directory as follows.
In this way, in the next restart process, the database will see the spfile in the +DATA disk group and open it from there.
1 | SPFILE='+DATA/ORCL/spfileORCL.ora' |
Adding database information to Cluster:
The following operations are required to include our database in the cluster.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | [oracle@oradb dbs]$ srvctl add database -d ORCL -n oradb -s open -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/ORCL/spfileORCL.ora -a DATA,FRA [oracle@oradb dbs]$ srvctl status database -d ORCL Database is not running. [oracle@oradb dbs]$ srvctl stop database -d ORCL PRCC-1016 : ORCL was already stopped [oracle@oradb dbs]$ exit logout [root@oradb /]# su - oracle [oracle@oradb ~]$ ps -ef|grep pmon oracle 3166 1 0 Aug27 ? 00:00:24 asm_pmon_+ASM oracle 15840 1 0 15:32 ? 00:00:00 ora_pmon_ORCL oracle 16827 16801 0 15:34 pts/0 00:00:00 grep pmon [oracle@oradb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 31 15:34:44 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: 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 SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. 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@oradb ~]$ srvctl start database -d ORCL [oracle@oradb ~]$ srvctl status database -d ORCL Database is running. [oracle@oradb ~]$ . oraenv ORACLE_SID = [ORCL] ? +ASM The Oracle base remains unchanged with value /u01/app/oracle [oracle@oradb ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.DATA.dg ora....up.type ONLINE ONLINE oradb ora.FRA.dg ora....up.type ONLINE ONLINE oradb ora....ER.lsnr ora....er.type ONLINE ONLINE oradb ora.asm ora.asm.type ONLINE ONLINE oradb ora.cssd ora.cssd.type ONLINE ONLINE oradb ora.ORCL.db ora....se.type ONLINE ONLINE oradb ora.diskmon ora....on.type OFFLINE OFFLINE ora.evmd ora.evm.type ONLINE ONLINE oradb ora.ons ora.ons.type OFFLINE OFFLINE |
Our database has started to work in ASM completely.
