Part II - Convert database files from filesystem to ASM Storage
1. Prepare the ORACLE_HOME environment for the 2nd node (.bash_proofile & ORACLE_HOME)
[oracle@testRAC2 ~]$ vi .bash_profile
[oracle@testRAC2 ~]$ . ./.bash_profile
[oracle@testRAC2 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.0.1/db_1
[oracle@testRAC2 ~]$ cd $ORACLE_HOME
-bash: cd: /u01/app/oracle/product/12.0.1/db_1: No such file or directory
[oracle@testRAC2 ~]$ cd $ORACLE_BASE
[oracle@testRAC2 ~]$ ll
total 8
-rw-r--r-- 1 oracle oinstall 50 Sep 19 13:00 afiedt.buf
drwxr----- 3 oracle oinstall 4096 Sep 15 14:07 oradiag_oracle
[oracle@testRAC2 ~]$ mkdir -p product/12.0.1/db_1
2. Copy over the zipped ORACLE_HOME to the newly created directory and unzip/untar to create your ORACLE_HOME in the second node and relink the binaries
[oracle@testRAC2 12.0.1]$ pwd
/u01/app/oracle/product/12.0.1
[oracle@testRAC2 12.0.1]$ scp
oracle@student190:/oradata/backup/DB12c_HOME.tar.gz .
The authenticity of host 'student190 (192.168.x.x)' can't be established.
RSA key fingerprint is 56:6c:41:17:2d:9e:a8:0a:a9:2f:08:a9:b8:9a:e1:4c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'student190,192.168.x.x' (RSA) to the list of known hosts.
oracle@student190's password:
DB12c_HOME.tar.gz 4% 115MB 9.5MB/s 04:27 ETA
DB12c_HOME.tar.gz 100% 2661MB 7.9MB/s 05:36
[oracle@testRAC2 12.0.1]$ pwd
/u01/app/oracle/product/12.0.1
[oracle@testRAC2 12.0.1]$ ll
total 2727320
-rw-r--r-- 1 oracle oinstall 2790039829 Jan 30 21:12 DB12c_HOME.tar.gz
[oracle@testRAC2 12.0.1]$ gunzip DB12c_HOME.tar.gz
[oracle@testRAC2 12.0.1]$ ll
total 5403744
-rw-r--r-- 1 oracle oinstall 5528023040 Jan 30 21:12 DB12c_HOME.tar
[oracle@testRAC2 12.0.1]$ tar -xvf DB12c_HOME.tar
db_1/
db_1/lib/
db_1/lib/sscoreed.o
db_1/lib/libsql12.a
db_1/lib/libclntsh.so.11.1
db_1/lib/libons.so
db_1/lib/libsrvmhas12.so
db_1/lib/libclsce12.so
:
:
[oracle@testRAC2 12.0.1]$ pwd
/u01/app/oracle/product/12.0.1
[oracle@testRAC2 12.0.1]$ ll
total 5403748
drwxr-xr-x 70 oracle oinstall 4096 Jan 30 09:30 db_1
-rw-r--r-- 1 oracle oinstall 5528023040 Jan 30 21:12 DB12c_HOME.tar
[oracle@testRAC2 12.0.1]$ rm -f DB12c_HOME.tar
[oracle@testRAC2 12.0.1]$ cd db_1/bin
[oracle@testRAC2 bin]$ pwd
/u01/app/oracle/product/12.0.1/db_1/bin
[oracle@testRAC2 bin]$ ./relink all
writing relink log to: /u01/app/oracle/product/12.0.1/db_1/install/relink.log
[oracle@testRAC2 bin]$
3. Switch over to node 1 & login as the grid user & install the 12cR1 Grid Infrastructure software (See screen shots)
[grid@testRAC1 ~]$ cd /downloads/ORACLE_12C/GridInfrastructure/grid
[grid@testRAC1 grid]$ pwd
/downloads/ORACLE_12C/GridInfrastructure/grid
[grid@testRAC1 grid]$ ll
total 44
drwxrwxrwx 4 500 student01 4096 Jul 1 2015 install
drwxrwxrwx 2 500 student01 4096 Jul 7 2014 response
drwxrwxrwx 2 500 student01 4096 Jul 7 2014 rpm
-rwxrwxrwx 1 500 student01 5085 Dec 20 2013 runcluvfy.sh
-rwxrwxrwx 1 500 student01 8534 Jul 7 2014 runInstaller
drwxrwxrwx 2 500 student01 4096 Jul 7 2014 sshsetup
drwxrwxrwx 14 500 student01 4096 Jul 7 2014 stage
-rwxrwxrwx 1 500 student01 500 Feb 6 2013 welcome.html
[grid@testRAC1 grid]$ ./runInstaller
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 415 MB. Actual 10629 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3999 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
[grid@testRAC1 grid]$
Install and Configure Grid Infrastructure for a Cluster
Too bad we can't install a Flex Cluster (One of the 12cR1 new features as I have just 2 nodes to play with)
4. Use the ASM Configuration Assistant (asmca) to create DATA & FRA disk groups (see creen shots)
5. Check the status of the clusterware and related high availability resources on both nodes.
[grid@testRAC1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[grid@testRAC1 ~]$
[grid@testRAC1 ~]$ crsctl check cluster -all
**************************************************************
testRAC1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
testRAC2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
6. Create ASM diskgroups and add appropriate diskgroups for your DB storage and backup/recovery in ASM using asmca (see screen shots)
7. Convert your database files from filesystem to ASM storage using RMAN
- Switch user to "oracle", login to the DB and take an inventory of all the database files to be converted
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/ORCL/controlfile/o1_mf_c985pl01_.ctl
/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_c985ploz_.ctl
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/ORCL/datafile/o1_mf_system_c985kcf0_.dbf
/u01/oradata/ORCL/datafile/o1_mf_sysaux_c985g9fz_.dbf
/u01/oradata/ORCL/datafile/o1_mf_undotbs1_c985o01b_.dbf
/u01/oradata/ORCL/datafile/o1_mf_users_c985nyqx_.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/ORCL/datafile/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/ORCL/onlinelog/o1_mf_3_c985q5oy_.log
/u01/oradata/ORCL/onlinelog/o1_mf_2_c985pxxc_.log
/u01/oradata/ORCL/onlinelog/o1_mf_1_c985pq3w_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/redo01.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/redo02.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/redo03.log
6 rows selected.
- Convert the above files to ASM files
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORCL OPEN
SQL>
- Disable block change tracking if you're using it
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled
- I'm not using BCT hence command fails
SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES CON_ID
---------- ----------------------------------------------------------------- ---------- ----------
DISABLED
- Create a pfile and add/modify the below parameters in the initORCL.ora file created
SQL> create pfile from spfile;
File created.
*.control_files=(+DATA, +FRA)
*.db_recovery_file_dest=+FRA
*.db_create_file_dest=+DATA
*.db_create_online_log_dest_1=+FRA
*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.
- With the target database open, edit the initialization parameter control_files and db_create_file_dest to point to the ASM disk group +DATA.
- Also configure db_recovery_file_dest to point to the ASM disk group +FRA
SQL> ALTER SYSTEM SET control_files='+DATA','+FRA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='+FRA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET db_create_online_log_dest_1='+FRA' scope=spfile;
System altered.
- shutdown the database cleanly and bring it backup NOMOUNT mode:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 2929888 bytes
Variable Size 557845280 bytes
Database Buffers 268435456 bytes
Redo Buffers 5455872 bytes
SQL>
- From an RMAN session, copy one of your controlfiles from the local file system to its new location in ASM.
- The new controlfile will be copied to the value specified in the initialization parameter control_files:
RMAN> RESTORE CONTROLFILE FROM '/u01/oradata/ORCL/controlfile/o1_mf_c985pl01_.ctl';
Starting restore at 31-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCL/CONTROLFILE/current.276.902588359
output file name=+FRA/ORCL/CONTROLFILE/current.257.902588361
Finished restore at 31-JAN-16
RMAN>
- mount the database in your RMAN session
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN>
- Use RMAN to copy the database files from the local file system to ASM
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
released channel: ORA_DISK_1
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 31-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=7 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=30 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oradata/ORCL/datafile/o1_mf_sysaux_c985g9fz_.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00001 name=/u01/oradata/ORCL/datafile/o1_mf_system_c985kcf0_.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00004 name=/u01/oradata/ORCL/datafile/o1_mf_undotbs1_c985o01b_.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00006 name=/u01/oradata/ORCL/datafile/o1_mf_users_c985nyqx_.dbf
output file name=+DATA/ORCL/DATAFILE/users.281.902588631 tag=TAG20160131T150349 RECID=10 STAMP=902588632
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_4: starting datafile copy
copying current control file
output file name=+DATA/ORCL/CONTROLFILE/backup.282.902588635 tag=TAG20160131T150349 RECID=11 STAMP=902588635
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:01
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-JAN-16
channel ORA_DISK_4: finished piece 1 at 31-JAN-16
piece handle=+DATA/ORCL/BACKUPSET/2016_01_31/nnsnf0_tag20160131t150349_0.283.902588637 tag=TAG20160131T150349 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01
output file name=+DATA/ORCL/DATAFILE/undotbs1.280.902588631 tag=TAG20160131T150349 RECID=12 STAMP=902588638
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/ORCL/DATAFILE/sysaux.278.902588631 tag=TAG20160131T150349 RECID=13 STAMP=902588641
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:12
output file name=+DATA/ORCL/DATAFILE/system.279.902588631 tag=TAG20160131T150349 RECID=14 STAMP=902588644
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:20
Finished backup at 31-JAN-16
RMAN>
- Switch the database now to the copy in RMAN
RMAN> SWITCH DATABASE TO COPY;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/ORCL/DATAFILE/system.279.902588631"
datafile 3 switched to datafile copy "+DATA/ORCL/DATAFILE/sysaux.278.902588631"
datafile 4 switched to datafile copy "+DATA/ORCL/DATAFILE/undotbs1.280.902588631"
datafile 6 switched to datafile copy "+DATA/ORCL/DATAFILE/users.281.902588631"
RMAN>
- From an SQL*Plus session, perform incomplete recovery and open the database using the RESETLOGS option.
- When prompted for three options, type "CANCEL"
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 3120135 generated at 01/31/2016 14:56:47 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'ORCL'
ORA-00280: change 3120135 for thread 1 is in sequence #71
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
- Recreate any tempfiles that are still on filesystem storage
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/ORCL/datafile/temp01.dbf
SQL> alter tablespace temp add tempfile '+DATA' size 128M reuse;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u01/oradata/ORCL/datafile/temp01.dbf';
Tablespace altered.
- Determine the current online redo logfiles to move to ASM by examining the file names (and sizes) from V$LOGFILE
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ----------------------------------------------------------------- ----------
3 /u01/oradata/ORCL/onlinelog/o1_mf_3_c985q5oy_.log 52428800
2 /u01/oradata/ORCL/onlinelog/o1_mf_2_c985pxxc_.log 52428800
1 /u01/oradata/ORCL/onlinelog/o1_mf_1_c985pq3w_.log 52428800
1 /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/redo01.log 52428800
2 /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/redo02.log 52428800
3 /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/redo03.log 52428800
6 rows selected.
Force a log switch until the last redo log is marked "CURRENT"
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
- Drop the first online redolog file
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
- Recreate the first online redo log group in ASM for a RAC DB instance and do same for all redo log files
SQL> alter database add logfile group 1 '+FRA' size 128M reuse;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 '+FRA' size 128M reuse;
Database altered.
SQL> alter system switch logfile;
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 '+FRA' size 128M reuse;
Database altered.
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ----------------------------------------------------------------- ----------
3 +FRA/ORCL/ONLINELOG/group_3.260.902589839 134217728
2 +FRA/ORCL/ONLINELOG/group_2.259.902589793 134217728
1 +FRA/ORCL/ONLINELOG/group_1.258.902589727 134217728
- Multiplex the online redo logs
SQL> alter database add logfile member '+DATA' to group 1;
Database altered.
SQL> alter database add logfile member '+DATA' to group 2;
Database altered.
SQL> alter database add logfile member '+DATA' to group 3;
Database altered.
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ----------------------------------------------------------------- ----------
3 +FRA/ORCL/ONLINELOG/group_3.260.902589839 134217728
2 +FRA/ORCL/ONLINELOG/group_2.259.902589793 134217728
1 +FRA/ORCL/ONLINELOG/group_1.258.902589727 134217728
1 +DATA/ORCL/ONLINELOG/group_1.285.902589995 134217728
2 +DATA/ORCL/ONLINELOG/group_2.286.902590003 134217728
3 +DATA/ORCL/ONLINELOG/group_3.287.902590007 134217728
6 rows selected.
- Relocate the SPFILE from the local file system to an ASM disk group
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.0.1
/db_1/dbs/spfileORCL.ora
SQL> create spfile='+DATA/ORCL/spfileORCL.ora' from pfile='initORCL.ora';
File created.
- Shutdown the DB instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
- Update the text-based initORCL.ora file with the new location of the SPFILE in ASM
[oracle@testRAC1 dbs]$ echo "SPFILE='+DATA/ORCL/spfileORCL.ora'" > $ORACLE_HOME/dbs/initORCL.ora
- Rename the old SPFILE on the local file system so that the new text-based initORCL.ora will be used
[oracle@testRAC1 dbs]$ mv $ORACLE_HOME/dbs/spfileORCL.ora $ORACLE_HOME/dbs/intORCL_bkp.ora
- Open the Oracle database using the new SPFILE
SQL> startup
ORACLE instance started.
Total System Global Area 834666496 bytes
Fixed Size 2929888 bytes
Variable Size 557845280 bytes
Database Buffers 268435456 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ORCL/spfileorcl.ora
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/ORCL/CONTROLFILE/current.276.902588359
+FRA/ORCL/CONTROLFILE/current.257.902588361
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/ORCL/DATAFILE/system.279.902588631
+DATA/ORCL/DATAFILE/sysaux.278.902588631
+DATA/ORCL/DATAFILE/undotbs1.280.902588631
+DATA/ORCL/DATAFILE/users.281.902588631
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/ORCL/TEMPFILE/temp.284.902589053
- We have now completely migrated all our database files to ASM storage. Take a full backup of your database at this point
[oracle@testRAC1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 31 15:59:34 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1428644494)
RMAN> configure device type disk parallelism 4;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> RMAN> alter database archivelog;
Statement processed
RMAN> backup database archivelog all;
Starting backup at 31-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=31 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=32 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=33 device type=DISK
specification does not match any archived log in the repository
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.278.902588631
channel ORA_DISK_1: starting piece 1 at 31-JAN-16
:
:
piece handle=+FRA/ORCL/BACKUPSET/2016_01_31/nnndf0_tag20160131t160139_0.262.902592101 tag=TAG20160131T160139 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:18
Finished backup at 31-JAN-16
- You can do a cleanup of your filesystem on disk at this point as well as delete backup copies from RMAN which your created earlier
Thanks for reading. Your feedback is always welcome...
