12cR2 Installation on Linux (Step by Step)

Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

<IP-address>  <fully-qualified-machine-name>  <machine-name>
For example.

127.0.0.1       localhost localhost.localdomain
192.168.1.21  testdb.com    testdb

Set the correct hostname in the "/etc/hostname" file.

Manual Setup

If you have not used the "oracle-database-server-12cR2-preinstall" package to perform all

prerequisites, you will need to manually perform the following setup tasks.

Add the following lines to the "/etc/sysctl.conf" file, or in a file called

"/etc/sysctl.d/98-oracle.conf".

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000

than in order to be activate/to take effects now run below command

/sbin/systcl -p



Add the following lines to a file called "/etc/security/limits.d/oracle-database-server-

12cR2-preinstall.conf" file.

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728

Install RPMS from /media/ENterpise/Server



 rpm -Uvh --force --nodeps gcc-4.4.7-3.el6.x86_64.rpm
 rpm -Uvh --force --nodeps gcc-c++-4.4.7-3.el6.x86_64.rpm
 rpm -Uvh --force --nodeps binutils*
 rpm -Uvh --force --nodeps compat-libcap1*
 rpm -Uvh --force --nodeps compat-libstdc++-33*
 rpm -Uvh --force --nodeps compat-libstdc++-33.i686*
 rpm -Uvh --force --nodeps glibc*
 rpm -Uvh --force --nodeps glibc.i686*
 rpm -Uvh --force --nodeps glibc-devel*
 rpm -Uvh --force --nodeps glibc-devel.i686*
 rpm -Uvh --force --nodeps ksh*
 rpm -Uvh --force --nodeps libaio*
 rpm -Uvh --force --nodeps libaio.i686*
 rpm -Uvh --force --nodeps libaio-devel*
 rpm -Uvh --force --nodeps libaio-devel.i686*
 rpm -Uvh --force --nodeps libX11*
 rpm -Uvh --force --nodeps libX11.i686*
 rpm -Uvh --force --nodeps libXau*
 rpm -Uvh --force --nodeps libXau.i686*
 rpm -Uvh --force --nodeps libXi*
 rpm -Uvh --force --nodeps libXi.i686*
 rpm -Uvh --force --nodeps libXtst*
 rpm -Uvh --force --nodeps libXtst.i686*
 rpm -Uvh --force --nodeps libgcc*
 rpm -Uvh --force --nodeps libgcc.i686*
 rpm -Uvh --force --nodeps libstdc++*
 rpm -Uvh --force --nodeps libstdc++.i686*
 rpm -Uvh --force --nodeps libstdc++-devel*
 rpm -Uvh --force --nodeps libstdc++-devel.i686*
 rpm -Uvh --force --nodeps libxcb*
 rpm -Uvh --force --nodeps libxcb.i686*
 rpm -Uvh --force --nodeps make*
 rpm -Uvh --force --nodeps nfs-utils*
 rpm -Uvh --force --nodeps net-tools*
 rpm -Uvh --force --nodeps smartmontools*
 rpm -Uvh --force --nodeps sysstat*
 rpm -Uvh --force --nodeps unixODBC*
 rpm -Uvh --force --nodeps unixODBC-devel*

Define different groups


groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
#groupadd -g 54324 backupdba
#groupadd -g 54325 dgdba
#groupadd -g 54326 kmdba
#groupadd -g 54327 asmdba
#groupadd -g 54328 asmoper
#groupadd -g 54329 asmadmin
#groupadd -g 54330 racdba

Creating Oracle User

useradd -u 54321 -g oinstall -G dba,oper oracle

passwd oracle

Creating Directories structure

mkdir -p /u01/app/oracle/product/12.2.0.1/dbhome
chown -R oracle:oinstall /u01
chmod -R 775 /u01


Setting Environment Variable



# Oracle Settings
    TMP=/tmp; export TMP
    TMPDIR=$TMP; export TMPDIR

    ORACLE_HOSTNAME=testdb.com; export ORACLE_HOSTNAME
    ORACLE_UNQNAME=cdb1; export ORACLE_UNQNAME
    ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
    ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1; export ORACLE_HOME
    ORACLE_SID=cdb1; export ORACLE_SID
    PATH=/usr/sbin:$PATH; export PATH
    PATH=$ORACLE_HOME/bin:$PATH; export PATH

    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Moving Files across Linux Servers without prompting for passwords

Transfer Files between 2 Linux Machine without prompt for the Password

Server A [192.168.1.20]
Server B [192.168.1.21]

You want to automate it that shell script run at any time and move file from Server A to Server B with out prompting for the passwords.

-- Ping Server B from Server A and and Server A from Server B.

ping -t 192.168.1.21
64 bytes from 192.168.1.21 icmp_seq=1 ttl=64 time=0.246

Let suppose that Oracle user should be used to transfer files a folder in Server A to Server B

Login through Oracle User to Server A and run from below command :

Oracle] $  ssh-keygen -t rsa
Just press enter

It will generate 2 files in /home/oracle/.ssh directory:
id_rsa.pub
id_rsa

-- File id_rsa.pub are your public keys copy this file to Server B to location /home/oracle/.ssh

.ssh] $scp id_rsa.pub oracle@192.168.1.21:/home/oracle/.ssh
password: *********

--Now Goto Server 2

oracle] $ cd .ssh
.ssh] $ cat id_rsa.pub >>~/.ssh/authorized_keys

It will be appended to authorized keys file.

chmod 600 authorized_keys  &

chmod -R 700 .ssh/


--Now move any file from Server A to Server B with prompting for passwords

scp any_directory/anyfiles oracle@serverB:/anydiretory


File will be copied without prompting for password.

Logical Backup (Datapump Utility)

Oracle backup Logical Backup

About the backup, i've already give a bit clue in my previous blog (https://tauseefdba.blogspot.com/2018/01/backing-up-oracle-database-physical.html), Logical backup is one in which we didn't copy files but copy structure and the data with in that structure is copied. Logical backup means backing up the database objects like tables, views , indexes...etc using the datapump (EXPDP,IMDP) utility given by Oracle.
Datapump is a bit faster than export utility used in past, but its over all too slow as compared to physical/RMAN backup, but it is handy in moving objects or schemas in between the databases. Simple prcoess to take backup through Datapump is :

-- Getting started

create user testuser identified by test default tablespace users;

-- Create directory at location physically

$ cd /u01

u01$ mkdir backup

$ cd backup

$ pwd
/u01/backup

-- Creating directory in Oracle Database

$sqlplus / as sysdba

SQL> create directory backup as '/u01/backup';

-- Granting privileges

SQL> grant read,write ON DIRECTORY backup TO testuser;

--Full database backup

expdp system/oracle full=y dumpfile=testdb_full.dmp logfile=export_testdb_full.log directory=backup

Impdp system/oracle Full=y dumpfile=testdb_full.dmp logfile=import_testdb_full.log directory=backup

-- Specific Schemas

expdp system/oracle schemas=hr,scott dumpfile=testdb_hrscott.dmp logfile=export_hrscott_full.log directory=backup

Impdp system/oracle schemas=hr,scott dumpfile=testdb_hrscott.dmp logfile=import_hrscott_full.log directory=backup

--  Selective Tables

expdp hr/hr tables=EMPLOYEES,DEPARTMENT directory=backup dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp hr/hr tables=EMPLOYEES,DEPARTMENT directory=backup dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

-- Backing meta data only

expdp  system/oracle directory=backup dumpfile=hr_metadata_content.dmp logfile=exp_content_hr.log schemas=hr content=METADATA_ONLY

impdp directory=backup dumpfile=hr_metadata_content.dmp logfile=imp_content_meta_hr.log remap_schema=hr:hr_new full=y

Backing Up Oracle Database (Physical Backup RMAN Utility)

Backup Oracle Database

There are 2 main types of backup in Oracle.
1- Physical Backup 
2- Logical Backup

But in this blog we will focus on physical Backup. In physical backup, all files such as datafiles, controlfile and archive redolog files are stored physically on other disk or some other medium like tape storages. Further we can take 2 types of physical backup. i.e cold backup and hot backup.

--Cold backup: It is type of backup when we stop the database server to perform any operations on database and we close the database, that is database is in mount stage, it is
the most safiest way of complete/full backup. It can be called as offline backup.

--Hot backup: It is type of backup when operations are being performed on database by the users and we take the backup, but for this type of backup to be full/completed we need database
to be in archive log mode (please read archive log mode blog), This type of backup is also called as hot backup.

Backup through RMAN:

--Cold backup:

Sqlplus / as sysdba

sql> alter database close; or

sql> shutdown immediate;

sql> startup mount;

this will take you to mount state, now you are ready to go offline or cold backup:

$ rman target /

rman> backup database;

exit

--Hot backup:

Full Datafiles backup:

$ rman target /
      or
$ rman target user/password

rman> backup database;

rman> exit;

Full Datafiles backup plus archivelog:

$rman target /

rman> backup database plus archivelog;

rman> exit;

Dropping ASM Disk Group in detail (Step by Step)

--check following parameter before dropping ASM DG:

Before dropping the ASM DiskGroup please check the below parameter as any one of them is pointing to the Disk group which you are going drop, if there are some parameter found point to that DG, we have to set new location for it.

Sqlplus / as sysdba

--Checking Oracle OMF Location

SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +FRA_TESTDB/TESTDB
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

we have to update the old DG name with new DG as:

alter system set db_create_file_dest='+FRA_TESTDB_01/TESTDB' scope=both;

SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
db_create_file_dest                  string      +FRA_TESTDB_01/TESTDB
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

-- Now check the postion of Flash recovery Area

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA_TESTDB
db_recovery_file_dest_size           big integer 8016M
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

This location should also be changed to new DG as:

alter system set db_recovery_file_dest='+FRA_TESTDB_01' scope=both;

Similarly check Controlfiles,log_archive_destination if you have set, change it to new disk group.

For controlfile movement (follow the blog moving data accross the disk groups)

-- Checking all the datafiles, there shouldn't exists any datafile on this DG:


select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking
union
select name from v$flashback_database_logfile;


-- Delete a directory and all its contents.

ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+FRA_TESTDB/backup' FORCE;

When its all clear that there is no DB parameter pointing to the DG going to be dropped and no live data is there we can drop that disk group.

-- To check dependancy & Remove Dependacny


#./srvctl config database -d DBTESTQ

SQL> drop diskgroup PLAY force including contents;

Or we can do it by Srv utility

Stopping the FRA resources

[root@testdb bin]# ./srvctl stop diskgroup -g FRA

Dropping the Disk

[root@testdb bin]# ./srvctl remove diskgroup -g FRA

Checking the Status

[root@testdb bin]# ./srvctl config database -d testdb
Database unique name: testdb
Database name: testdb
Oracle home: /u01/app/oracle/product/11.2.0
Oracle user: grid
Spfile: +DATA/testdb/spfiletestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,ARCH
Services:
[root@testdb bin]# 

Configuring ASM for single Instance DB

Automatic Storage Management (ASM)

ASM is advance feature provided by Oracle for management of storage devices and data placed on it form 10g ownwards. ASM combines the storage disks to form a disk groups, used
for storing of Data. It is automatic in the sense that you can add disks and remove disks from Disk group even its online and data is written on it live. Other features are stripping
mirroring of data across different disks. There are other storage management tool as well but Oracle ASM is best of them all is because it provides you with
Oracle Automatic Storage Management Cluster File System (Oracle ACFS).

ASM can be implemented in 2 ways: i.e ASMLib drivers and through Raw Devices

 1) ASM configuration through ASMLib: (Below method is use to configure ASM in all most all environments)

--First of All you'll Install rpm, 3 rpms

1) oracleasm-support-2.1.7-1.el5.i386.rpm
2) oracleasm-2.6.18-92.el5-2.0.4-1.el5.i686.rpm
3) oracleasmlib-2.0.4-1.el5.i386.rpm

-- Partition Harddisk

#fdisks -l


#fdisk /dev/sda

n for new partition
P for primary partition
1 starts
enter
enter
wq to write quite

similarly you'll partition all the disks

--Now configure ASM 

#/etc/init.d/oracleasm configure

than will ask for user : oracle
                       : oinstall
                       : y
                       : y 

-- Naming ASM Disks

/etc/init.d/oracleasm createdisk vol1 /dev/sda1
/etc/init.d/oracleasm createdisk vol2 /dev/sda2
/etc/init.d/oracleasm createdisk vol3 /dev/sda3
/etc/init.d/oracleasm createdisk vol4 /dev/sda4
/etc/init.d/oracleasm createdisk vol4 /dev/sda5

--Listing all the diks created

/etc/init.d/oracleasm listdisks
vol1
vol2
vol3
vol4
vol4

Once the formating is done execute runinstaller


2)  Raw Device Configuration: (These days its not configured but one should have knowledge)

In linux disk are partitioned but not formated for raw formate

# fdisk -l 

/dev/sda1
/dev/sda2
/dev/sda3 etc...

In some release of linux raw devices configuration are depreciated
If you don't have raw devices attached to  than you can check it by command:


(i)  service rawdevices start
     (unrecognised command)
(ii) ll /etc/sysconfig/raw*
      (no such file or directory)
 
  You above commands didn't return any result please follow the below procedure:
 

cd /etc/udev/rules.d
(rules file are executed when machine startups )
these are called udev rules

vi 60-raw.rules
paste :

ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"
ACTION=="add", KERNEL=="sdf1", RUN+="/bin/raw /dev/raw/raw5 %N"
KERNEL=="raw[1-2]*", OWNER="root", GROUP="oinstall", MODE="640"
KERNEL=="raw[3-5]*", OWNER="oracle", GROUP="oinstall", MODE="644"

if you want that it will be effected immediate use this command :

/bin/raw /dev/raw/raw1 /dev/sdb1
/bin/raw /dev/raw/raw2 /dev/sdc1
/bin/raw /dev/raw/raw3 /dev/sdd1
/bin/raw /dev/raw/raw4 /dev/sde1
/bin/raw /dev/raw/raw5 /dev/sdf1

keep in mind that this is the drawback of rawdevices that after restart these permisstion reset to root but keeping the 2 lines in 60-raw files keep it static.

chown oracle:oinstall /dev/raw/raw*
chmod 640 /dev/raw/*

Now Run the runinstaller  

Moving Data from one ASM DG with EXTERNAL redundancy to another ASM DG with NORMAL redundancy (Offline approach)

Moving Data from one ASM DG to another ASM DG Offline approach.

If we are having diskgroup with redundancy EXTERNAL and want to configure another ASM DG with redundancy Level NORMAL, so its no other way to create new Disk group and move all your
from old ASM DG wiht EXTERNAL to New ASM DG with NORMAL redundancy.

--Check all the datafiles of the DB and their location:

select file_id,bytes/1024/1024 MB,tablespace_name,file_name from dba_data_files;

File Size(MB) Tablespace            Datafile Name
---- -------- --------------------  ------------------------
1    3072     SYSTEM                +PRE_TEST_DATA/dbTESTq/datafile/system.266.864839123
2    3072     SYSAUX                +PRE_TEST_DATA/dbTESTq/datafile/sysaux.265.864839135
3    4470     UNDOTBS1              +PRE_TEST_DATA/dbTESTq/datafile/undotbs1.258.864839143
4    5445     UNDOTBS2              +PRE_TEST_DATA/dbTESTq/datafile/undotbs2.264.864839153
5    5        USERS                 +PRE_TEST_DATA/dbTESTq/datafile/users.263.864839157
6    1024     vr_TEST_DATA          +PRE_TEST_DATA/dbTESTq/datafile/vr_TEST_data.dbf
7    1024     TS_DATA_TESTIKMS       +PRE_TEST_DATA/dbTESTq/datafile/ts_data_TESTikms.dbf
8    1024     TS_INDEX_TESTIKMS      +PRE_TEST_DATA/dbTESTq/datafile/ts_index_TESTikms.dbf
9    1024     TS_KSA_TEST_DAT        +PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_dat_1.dbf
10   1024     TS_KSA_TEST_IDX        +PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_idx_1.dbf
11   1024     TS_KSA_TEST_LOB        +PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_lob_1.dbf
12   1024     TS_KSA_TEST_LOG        +PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_log_1.dbf
13   1024     TS_KSA_TEST_LOG_LOB    +PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_log_lob_1.dbf

-- Checking all Temp files location

select file_id,bytes/1024/1024 MB,tablespace_name,maxbytes/1024/1024 Maxsize(MB),file_name from dba_temp_files;

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    73       TEMP                 32767       +PRE_TEST_DATA/dbTESTq/tempfile/temp.257.864839147

-- Check Controlfile Location

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +PRE_TEST_DATA/dbTESTq/controlfi
                                                 le/current.267.864839117, +PRE
                                                 _TEST_FRA/dbTESTq/controlfile/cu
                                                 rrent.256.864839117
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>

-- Check below parameters if any of them pointing to Old disk group

Show parameter DB_CREATE_FILE_DEST
Show parameter DB_CREATE_ONLINE_LOG_DEST_n
Show parameter DB_RECOVERY_FILE_DEST
Show parameter LOG_ARCHIVE_DEST_n
Show parameter LOG_ARCHIVE_DEST
Show parameter STANDBY_ARCHIVE_DEST

-- Changing location of backup controlfile to new ASM DG having normal redundancy

sql> alter database backup controlfile to '+PRE_TEST_DATA_01';

Database altered.

SQL> alter system set control_files='+PRE_TEST_DATA_01/DBTESTQ/CONTROLFILE/Backup.256.912776299' scope=spfile;

System altered.

--Now restoring current Controlfile status to newly move controlfile (offline so that it should be synced with original one)

rman target TESTqrman/RmanTEST11q catalog catqrman/Rmanora11q@dboemq1

rman target /
shutdown immediate;

RMAN> restore controlfile from '+PRE_TEST_DATA/dbTESTq/controlfile/current.267.864839117';

Starting restore at 25-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 instance=DBTESTQ1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+PRE_TEST_DATA_01/dbTESTq/controlfile/backup.256.912776299
Finished restore at 25-MAY-16

--Moving Datafiles thorugh RMAN to new ASM Disk Group through RMAN backup as copy

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+PRE_TEST_DATA_01';

Starting backup at 25-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 instance=DBTESTQ1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+PRE_TEST_DATA/dbTESTq/datafile/undotbs2.264.864839153
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/undotbs2.257.912777743 tag=TAG20160525T132222 RECID=4 STAMP=912777794
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+PRE_TEST_DATA/dbTESTq/datafile/undotbs1.258.864839143
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/undotbs1.258.912777797 tag=TAG20160525T132222 RECID=5 STAMP=912777835
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+PRE_TEST_DATA/dbTESTq/datafile/system.266.864839123
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/system.259.912777843 tag=TAG20160525T132222 RECID=6 STAMP=912777869
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+PRE_TEST_DATA/dbTESTq/datafile/sysaux.265.864839135
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/sysaux.260.912777877 tag=TAG20160525T132222 RECID=7 STAMP=912777910
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+PRE_TEST_DATA/dbTESTq/datafile/vr_TEST_data.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/vr_TEST_data.261.912777913 tag=TAG20160525T132222 RECID=8 STAMP=912777926
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_data_TESTikms.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_data_TESTikms.262.912777929 tag=TAG20160525T132222 RECID=9 STAMP=912777937
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_index_TESTikms.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_index_TESTikms.263.912777943 tag=TAG20160525T132222 RECID=10 STAMP=912777950
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_dat_1.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_dat.264.912777959 tag=TAG20160525T132222 RECID=11 STAMP=912777964
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_idx_1.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_idx.265.912777965 tag=TAG20160525T132222 RECID=12 STAMP=912777972
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_lob_1.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_lob.266.912777981 tag=TAG20160525T132222 RECID=13 STAMP=912777990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_log_1.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_log.267.912777995 tag=TAG20160525T132222 RECID=14 STAMP=912778003
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=+PRE_TEST_DATA/dbTESTq/datafile/ts_TEST_log_lob_1.dbf
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_log_lob.268.912778011 tag=TAG20160525T132222 RECID=15 STAMP=912778017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+PRE_TEST_DATA_01/dbTESTq/controlfile/backup.269.912778019 tag=TAG20160525T132222 RECID=16 STAMP=912778019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+PRE_TEST_DATA/dbTESTq/datafile/users.263.864839157
output file name=+PRE_TEST_DATA_01/dbTESTq/datafile/users.270.912778021 tag=TAG20160525T132222 RECID=17 STAMP=912778020
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 25-MAY-16
channel ORA_DISK_1: finished piece 1 at 25-MAY-16
piece handle=+PRE_TEST_DATA_01/dbTESTq/backupset/2016_05_25/nnsnf0_tag20160525t132222_0.271.912778021 tag=TAG20160525T132222 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-MAY-16

--Updating location of all DATAFILES in controlfile from old ASM DG to new ASM DG

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/system.259.912777843"
datafile 2 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/sysaux.260.912777877"
datafile 3 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/undotbs1.258.912777797"
datafile 4 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/undotbs2.257.912777743"
datafile 5 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/users.270.912778021"
datafile 6 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/vr_TEST_data.261.912777913"
datafile 7 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_data_TESTikms.262.912777929"
datafile 8 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_index_TESTikms.263.912777943"
datafile 9 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_dat.264.912777959"
datafile 10 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_idx.265.912777965"
datafile 11 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_lob.266.912777981"
datafile 12 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_log.267.912777995"
datafile 13 switched to datafile copy "+PRE_TEST_DATA_01/dbTESTq/datafile/ts_ksa_TEST_log_lob.268.912778011"

RMAN>

RMAN> exit

--Opening the DATABASE from new ASM DG

[oracle@TESTQDB09 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 13:28:16 2016

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, Real Application Clusters and Automatic Storage Management options

SQL>
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>

--Setting location to NEW FRA Disk group wiht normal redundancy

SQL> alter system set db_recovery_file_dest='+PRE_TEST_FRA_01' scope=both;

System altered.

SQL>
-- Creating new temp file as it is not backup through Rman


SQL> create temporary tablespace temp01 tempfile '+PRE_TEST_DATA_01' size 32767M;

Tablespace created.

SQL> alter database default temporary tablespace temp01;

Database altered.

--Droping Old Temporary TABLESPACE

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> exit


[oracle@TESTQDB09 ~]$ cd /u01/app/oracle/product/11.2.0/dbs/
[oracle@TESTQDB09 dbs]$ ls -ltr
total 47540

-rw-r----- 1 oracle oinstall       47 Nov 28  2014 initDBTESTQ1.ora

-- Moving Spfile from Current ASM Disk Group and Updating Spfile location to NEW ASM DG


[oracle@TESTQDB09 dbs]$ cat initDBTESTQ1.ora
SPFILE='+PRE_TEST_DATA/DBTESTQ/spfileDBTESTQ.ora'
[oracle@TESTQDB09 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbs
[oracle@TESTQDB09 dbs]$
SQL> create pfile='/u01/app/oracle/product/11.2.0/dbs/initMIGTESTq.ora' from spfile;

File created.

SQL>
oracle@TESTQDB09 dbs]$ ls -ltr /u01/app/oracle/product/11.2.0/dbs/initMIGTESTq.ora
-rw-r--r-- 1 oracle asmadmin 1987 May 25 15:08 /u01/app/oracle/product/11.2.0/dbs/initMIGTESTq.ora
[oracle@TESTQDB09 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 15:10:25 2016

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, Real Application Clusters and Automatic Storage Management options

SQL> create spfile='+PRE_TEST_DATA_01' from pfile='/u01/app/oracle/product/11.2.0/dbs/initMIGTESTq.ora';

File created.

SQL> exit

[[oracle@TESTQDB09 dbs]$ cat initDBTESTQ1.ora
SPFILE='+PRE_TEST_DATA_01/DBTESTQ/spfileDBTESTQ.ora'

-- Moving Redo Log files

If your redo log group are having 2 members drop 1 from each group

select * from v$logfile;

select * from v$log;

alter database drop logfile member '+PRE_TEST_FRA/dbTESTq/onlinelog/group_2.259.864839121';
alter database drop logfile member '+PRE_TEST_FRA/dbTESTq/onlinelog/group_3.258.864840619';
alter database drop logfile member '+PRE_TEST_FRA/dbTESTq/onlinelog/group_1.260.864839119';
alter database drop logfile member '+PRE_TEST_FRA/dbTESTq/onlinelog/group_4.257.864840621';

-- Now creating new redo log Group at new ASM DG having NORMAL redundancy

alter database add logfile member '+PRE_TEST_DATA_01' to group 1;
alter database add logfile member '+PRE_TEST_DATA_01' to group 2;
alter database add logfile member '+PRE_TEST_DATA_01' to group 3;
alter database add logfile member '+PRE_TEST_DATA_01' to group 4;


-- Drop Second log member from group and also drop the old redo log group as well

alter database drop logfile member '+PRE_TEST_DATA/dbTESTq/onlinelog/group_1.256.864839119';
alter database drop logfile member '+PRE_TEST_DATA/dbTESTq/onlinelog/group_2.262.864839121';
alter database drop logfile member '+PRE_TEST_DATA/dbTESTq/onlinelog/group_3.261.864840617';
alter database drop logfile member '+PRE_TEST_DATA/dbTESTq/onlinelog/group_4.260.864840619';


-- updating configuration at in Services

  ./srvctl config database -d DBTESTQ
  ./srvctl modify database -d DBTESTQ -a 'PRE_TEST_DATA_01,PRE_TEST_FRA_01'
 ./srvctl start instance -d dbTESTq -i DBTESTQ1