How to separate datafiles from all other Oracle database files for SnapManager vol-based fast restore

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make Oracle RAC database 11gR2 11.2.0.3 64bit to be eligible for fast restore
Environment: Oracle 11.2.0.3 RAC databases 2 node VMs sitting on an Oracle VM Server 3.1.1 hypervisor, using centralized Netapp storage for the following volumes: /u01 for binary, /u02 for datafile, password file, pfile, ocr/votedisk, controlfile, online and archived redo logs

Concept: In oder to be eligible for fast restore, we need to separte datafile volume, let /u02 is only for datafiles.


Steps:

1. pfile/spfile and password file
In Oracle RAC, we use symbolic link for shared spfile for RAC, in defualt pfile:

Assuming database name is racdb, 2 instance names are racdb1 and racdb2

su - oracle
cd $ORACLE_HOME/dbs
more initINSTANCENAME.ora

oracle@racdb1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ more initracdb1.ora 
SPFILE='/u06/spfile/spfileracdb.ora'

the password file which is symbolic link:

lrwxrwxrwx 1 oracle oinstall   28 Mar 14 16:57 orapwracdb1 -> /u06/passwdfile/orapwracdb


2. control files

sqlplus / as sysdba
sql> create pfile='/tmp/pfile' from spfile;
sql> exit

vi /tmp/pfile for control_files line

sql> shutdown immediate;  # for both database

copy required controlfiles from existing one to new one in new locations

sqlplus / as sysdba
sql> startup mount;
sql> create spfile='/u06/spfile/spfileracdb.ora' from pfile='/tmp/pfile'
sql> alter database open;

3. online redo log file

offline method:
sqlplus / as sysdba
sql> shutdown immediate;
sql> exit

cp required online redo log to new locations
sqlplus / as sysdba
sql> startup mount;
alter datbase rename file 'old location in old controlfile' to 'new location'
...
e.g.
alter database rename file '/u03/oralog/racdb/redo02.log' to '/u04/oralog/racdb/redo02.log';
alter database rename file '/u03/oralog/racdb/redo03.log' to '/u04/oralog/racdb/redo03.log';
sql> alter database open;
sql> select member from v$logfile;

online method:

ALTER DATABASE ADD LOGFILE THREAD 1
 GROUP 5 ('/u04/oralog/NRMAPS0/redo05.log') size 100M,
  GROUP 6 ('/u04/oralog/NRMAPS0/redo06.log') size 100M;


ALTER DATABASE ADD LOGFILE THREAD 2
 GROUP 7 ('/u04/oralog/NRMAPS0/redo07.log') size 100M,
  GROUP 8 ('/u04/oralog/NRMAPS0/redo08.log') size 100M;

alter system archive log current;
alter system switch logfile;
alter database drop logfile group 1;


4. ocr/votedisk
/u01/app/11.2.0/grid/ocrcheck
 crsctl query css votedisk

oracle@db02:/u02$ more /etc/oracle/ocr.loc
ocrconfig_loc=/u02/crscfg/ocr
local_only=FALSE

===OCR===

As root user on racdb1: run
touch /u06/crscfg/ocrdisk
chown root:oinstall /u06/crscfg/ocrdisk
chmod 640 /u06/crscfg/ocrdisk

/u01/app/11.2.0/grid/bin/ocrconfig -showbackup
/u01/app/11.2.0/grid/bin/ocrconfig -manualbackup

root@db01:/tmp# /u01/app/11.2.0/grid/bin/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
/u01/app/11.2.0/grid/bin/ocrconfig –add /u06/crscfg/ocrdisk
/u01/app/11.2.0/grid/bin/ocrcheck
/u01/app/11.2.0/grid/bin/ocrconfig –delete /u02/crscfg/ocr
/u01/app/11.2.0/grid/bin/ocrcheck

===Votedisk===


root@db01:/tmp# /u01/app/11.2.0/grid/bin/crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   10ab40543f574f1abf3a5f3135fb3532 (/u02/crscfg/vdsk) []
Located 1 voting disk(s).


/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u06/crscfg/votedisk
/u01/app/11.2.0/grid/bin/crsctl delete css votedisk /u02/crscfg/vdsk


Useful commands:
show parameter control;
show parameter pfile;

orapwd file=orapwTEST01 password=Welc0me1 ignorecase=n
select * from v$pwfile_users;

5. check CRS status

su - oracle
. grid_env
crs_stat -t
or
crsctl stat res -t