Direct Path Read and DB File Multi Block read count

https://community.oracle.com/thread/3647112?start=30&tstart=0

I Just tried it on my test Windows 2008/ 11.2.0.3

By increasing the DB_FILE_MULTIBLOCK_READ_COUNT- It significantly improves the response time for DIRECT PATH READ


SQL> show parameter multiblock


NAME                                 TYPE        VALUE                          

------------------------------------ ----------- ------------------------------   

db_file_multiblock_read_count        integer     128                            
SQL> ALTER SESSION SET sql_trace=TRUE;


Session altered.


SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';


Session altered.


SQL> select count(*) from  big_table;


  COUNT(*)                                                                      
----------                                                                      
  37118976                                                                      


SQL> set timing on
SQL> /


  COUNT(*)                                                                      
----------          

                                                           
  37118976                                                                      


Elapsed: 00:00:24.91
and the trace file is the below
PARSING IN CURSOR #335902688 len=31 dep=0 uid=0 oct=3 lid=0 tim=2775080312320 hv=1330050261 ad='7ff151fb990' sqlid='bd5mjp97ndx6p'
select count(*) from  big_table
END OF STMT
PARSE #335902688:c=15600,e=57945,p=6,cr=94,cu=0,mis=1,r=0,dep=0,og=1,plh=599409829,tim=2775080312318
EXEC #335902688:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2775080312473
WAIT #335902688: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=9 tim=2775080312514
WAIT #335902688: nam='direct path read' ela= 16918 file number=1 first dba=90329 block cnt=39 obj#=81856 tim=2775080330042
WAIT #335902688: nam='direct path read' ela= 1302 file number=1 first dba=96512 block cnt=88 obj#=81856 tim=2775080331939
WAIT #335902688: nam='direct path read' ela= 48985 file number=1 first dba=96640 block cnt=128 obj#=81856 tim=2775080381642
WAIT #335902688: nam='direct path read' ela= 2208 file number=1 first dba=96768 block cnt=128 obj#=81856 tim=2775080384758
WAIT #335902688: nam='direct path read' ela= 641 file number=1 first dba=97024 block cnt=128 obj#=81856 tim=2775080387051

Now increasing the multiblock read count
SQL> alter session set db_file_multiblock_read_count=1024;


Session altered.


Elapsed: 00:00:00.00
SQL> select count(*)  from big_table;


  COUNT(*)                                                                      
----------                                                                      
  37118976                                                                      


Elapsed: 00:00:09.31
and the trace is below for this

ARSING IN CURSOR #335902688 len=52 dep=0 uid=0 oct=42 lid=0 tim=2775211375349 hv=302278082 ad='0' sqlid='7d0uktn908tf2'
alter session set db_file_multiblock_read_count=1024
END OF STMT
PARSE #335902688:c=0,e=205,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2775211375348
EXEC #335902688:c=0,e=114,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2775211375513
WAIT #335902688: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775211375544


*** 2015-01-13 10:56:04.533
WAIT #335902688: nam='SQL*Net message from client' ela= 12462509 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775223838075
CLOSE #335902688:c=0,e=16,dep=0,type=1,tim=2775223838194
=====================
PARSING IN CURSOR #348591800 len=399 dep=1 uid=0 oct=3 lid=0 tim=2775223840266 hv=2987523179 ad='7ff1528ad50' sqlid='8mu3z0kt13z3b'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("BIG_TABLE") FULL("BIG_TABLE") NO_PARALLEL_INDEX("BIG_TABLE") */ 1 AS C1, 1 AS C2 FROM "SYS"."BIG_TABLE" SAMPLE BLOCK (0.011589 , 1) SEED (1) "BIG_TABLE") SAMPLESUB
END OF STMT
PARSE #348591800:c=0,e=1202,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=4255388684,tim=2775223840264
EXEC #348591800:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=4255388684,tim=2775223840402
FETCH #348591800:c=15600,e=9533,p=0,cr=93,cu=0,mis=0,r=1,dep=1,og=1,plh=4255388684,tim=2775223849959
STAT #348591800 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=93 pr=0 pw=0 time=9536 us)'
STAT #348591800 id=2 cnt=4454 pid=1 pos=1 obj=81856 op='TABLE ACCESS SAMPLE BIG_TABLE (cr=93 pr=0 pw=0 time=5521 us cost=2 size=12 card=1)'
CLOSE #348591800:c=0,e=6,dep=1,type=0,tim=2775223850075
=====================
PARSING IN CURSOR #348592672 len=31 dep=0 uid=0 oct=3 lid=0 tim=2775223850333 hv=2920928257 ad='7ff1f316670' sqlid='a9c9vrkr1mp01'
select count(*)  from big_table
END OF STMT
PARSE #348592672:c=15600,e=12081,p=0,cr=94,cu=0,mis=1,r=0,dep=0,og=1,plh=599409829,tim=2775223850331
EXEC #348592672:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2775223850406
WAIT #348592672: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=81856 tim=2775223850437
WAIT #348592672: nam='direct path read' ela= 21781 file number=1 first dba=90329 block cnt=39 obj#=81856 tim=2775223872881
WAIT #348592672: nam='direct path read' ela= 161673 file number=1 first dba=96640 block cnt=640 obj#=81856 tim=2775224039504
WAIT #348592672: nam='direct path read' ela= 18055 file number=1 first dba=98304 block cnt=1024 obj#=81856 tim=2775224075163
WAIT #348592672: nam='direct path read' ela= 35412 file number=1 first dba=99328 block cnt=1024 obj#=81856 tim=2775224117398
WAIT #348592672: nam='direct path read' ela= 63280 file number=1 first dba=100352 block cnt=640 obj#=81856 tim=2775224187153


*** 2015-01-13 10:56:04.907
WAIT #348592672: nam='direct path read' ela= 18056 file number=1 first dba=100992 block cnt=384 obj#=81856 tim=2775224209450
WAIT #348592672: nam='direct path read' ela= 160670 file number=1 first dba=101376 block cnt=1024 obj#=81856 tim=2775224373623
WAIT #348592672: nam='direct path read' ela= 16050 file number=1 first dba=103424 block cnt=1024 obj#=81856 tim=2775224407786
WAIT #348592672: nam='direct path read' ela= 29265 file number=1 first dba=104448 block cnt=1024 obj#=81856 tim=2775224443701
WAIT #348592672: nam='direct path read' ela= 13106 file number=1 first dba=105472 block cnt=1024 obj#=81856 tim=2775224463860
WAIT #348592672: nam='direct path read' ela= 12313 file number=1 first dba=106496 block cnt=1024 obj#=81856 tim=2775224483484
WAIT #348592672: nam='direct path read' ela= 5320 file number=1 first dba=107520 block cnt=1024 obj#=81856 tim=2775224496871
WAIT #348592672: nam='direct path read' ela= 12907 file number=1 first dba=108544 block cnt=1024 obj#=81856 tim=2775224517080
WAIT #348592672: nam='direct path read' ela= 20352 file number=1 first dba=109568 block cnt=1024 obj#=81856 tim=2775224544060
WAIT #348592672: nam='direct path read' ela= 23962 file number=1 first dba=111616 block cnt=1024 obj#=81856 tim=2775224582782
WAIT #348592672: nam='direct path read' ela= 11246 file number=1 first dba=113664 block cnt=1024 obj#=81856 tim=2775224612256
WAIT #348592672: nam='direct path read' ela= 11687 file number=1 first dba=114688 block cnt=1024 obj#=81856 tim=2775224630478
WAIT #348592672: nam='direct path read' ela= 19856 file number=1 first dba=115712 block cnt=1024 obj#=81856 tim=2775224656787
WAIT #348592672: nam='direct path read' ela= 19851 file number=1 first dba=116736 block cnt=1024 obj#=81856 tim=2775224685003
WAIT #348592672: nam='direct path read' ela= 18767 file number=1 first dba=118784 block cnt=1024 obj#=81856 tim=2775224718882
WAIT #348592672: nam='direct path read' ela= 15290 file number=1 first dba=120832 block cnt=1024 obj#=81856 tim=2775224752371
WAIT #348592672: nam='direct path read' ela= 26578 file number=1 first dba=122880 block cnt=1024 obj#=81856 tim=2775224794862
WAIT #348592672: nam='direct path read' ela= 17840 file number=1 first dba=124928 block cnt=1024 obj#=81856 tim=2775224828274
WAIT #348592672: nam='direct path read' ela= 10446 file number=1 first dba=126208 block cnt=768 obj#=81856 tim=2775224847268
WAIT #348592672: nam='direct path read' ela= 14746 file number=1 first dba=126976 block cnt=1024 obj#=81856 tim=2775224867798
WAIT #348592672: nam='direct path read' ela= 13894 file number=1 first dba=129024 block cnt=1024 obj#=81856 tim=2775224896643
WAIT #348592672: nam='direct path read' ela= 4059 file number=1 first dba=130048 block cnt=1024 obj#=81856 tim=2775224908034
WAIT #348592672: nam='direct path read' ela= 8972 file number=1 first dba=131072 block cnt=1024 obj#=81856 tim=2775224924395
WAIT #348592672: nam='direct path read' ela= 5014 file number=1 first dba=132096 block cnt=1024 obj#=81856 tim=2775224936765
WAIT #348592672: nam='direct path read' ela= 9246 file number=1 first dba=133120 block cnt=1024 obj#=81856 tim=2775224953730


So repeats the same behaviour mentioned in https://fritshoogland.wordpress.com/tag/oracle-io-multiblock-read-mbrc-multiblock-read-count/

I just split the trace file for clarity- attached is the untouced onespool.GIFTrace.GIFTrace128block.GIF

But changing the value may drive towards lot of fullscans .


I just tried by creating a 16KB block tablespace and it doesnt sound good...
copied the same table to the 16KB block and  it performs bad actually
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';


Session altered.


SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,
  2  dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tabl
  3  esPace_name=b.tablespace_name;
esPace_name=b.tablespace_name
*
ERROR at line 3:
ORA-00920: invalid relational operator

SQL> CREATE TABLESPACE big_block DATAFILE 'D:\oracledb\oradata\LMSDB\sb01.dbf' S
IZE 2048M
  2      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
  3      BLOCKSIZE 16K;


Tablespace created.


Elapsed: 00:00:12.09








SQL> create table big_table_bigblock tablespace big_block as select * from big_t
able;


Table created.


SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tablesPace_name=b.tablespace_name;


BLOCK_SIZE TABLESPACE_NAME                TABLE_NAME
---------- ------------------------------ ------------------------------
      8192 SYSTEM                         BIG_TABLE
     16384 BIG_BLOCK                      BIG_TABLE_BIGBLOCK




SQL> select a.block_size,a.tablespace_name ,b.table_name from dba_tablespaces a,dba_tables b where b.table_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK') and a.tablesPace_name=b.tablespace_name;


BLOCK_SIZE TABLESPACE_NAME                TABLE_NAME                          
---------- ------------------------------ ------------------------------      
      8192 SYSTEM                         BIG_TABLE                            
     16384 BIG_BLOCK                      BIG_TABLE_BIGBLOCK                  


SQL> show parameter multi


NAME                                 TYPE        VALUE                        
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128                          
parallel_adaptive_multi_user         boolean     TRUE                          
SQL> select bytes/1024/1024/1025 as GB, segment_name from dba_segments where segment_name in ('BIG_TABLE','BIG_TABLE_BIGBLOCK');


        GB                                                                    
----------                                                                    
SEGMENT_NAME                                                                  
--------------------------------------------------------------------------------
1.22195122                                                                    
BIG_TABLE_BIGBLOCK                                                            
                                                                               
1.24878049                                                                    
BIG_TABLE                                                                      
                                                                               


SQL> select count(*) from big_table;


  COUNT(*)                                                                    
----------                                                                    
  11000000                                                                    


SQL> select count(*) from big_table_big_block;
select count(*) from big_table_big_block
                     *
ERROR at line 1:
ORA-00942: table or view does not exist




SQL> select count(*) from big_table_bigblock;


  COUNT(*)                                                                    
----------                                                                    
  11000000                                                                    


SQL> spoll off
SP2-0042: unknown command "spoll off" - rest of line ignored.
SQL> spool off
PARSING IN CURSOR #101737960 len=30 dep=0 uid=0 oct=3 lid=0 tim=2787353576538 hv=1326693165 ad='7ff25361b18' sqlid='6fxg0qj7j7ftd'
select count(*) from big_table
END OF STMT
PARSE #101737960:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2787353576537
EXEC #101737960:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=599409829,tim=2787353576657
WAIT #101737960: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=589 tim=2787353576691
WAIT #101737960: nam='direct path read' ela= 24563 file number=1 first dba=90585 block cnt=39 obj#=83398 tim=2787353602067
WAIT #101737960: nam='direct path read' ela= 11121 file number=1 first dba=98176 block cnt=128 obj#=83398 tim=2787353614787
WAIT #101737960: nam='direct path read' ela= 7432 file number=1 first dba=98304 block cnt=128 obj#=83398 tim=2787353623248
WAIT #101737960: nam='direct path read' ela= 229 file number=1 first dba=98432 block cnt=128 obj#=83398 tim=2787353624329
WAIT #101737960: nam='direct path read' ela= 10556 file number=1 first dba=98560 block cnt=128 obj#=83398 tim=2787353635721
WAIT #101737960: nam='direct path read' ela= 17376 file number=1 first dba=98688 block cnt=128 obj#=83398 tim=2787353653941
WAIT #101737960: nam='direct path read' ela= 615 file number=1 first dba=98944 block cnt=128 obj#=83398 tim=2787353656291
WAIT #101737960: nam='direct path read' ela= 43335 file number=1 first dba=99072 block cnt=128 obj#=83398 tim=2787353700478
WAIT #101737960: nam='direct path read' ela= 1379 file number=1 first dba=99200 block cnt=128 obj#=83398 tim=2787353702886
WAIT #101737960: nam='direct path read' ela= 795 file number=1 first dba=99328 block cnt=128 obj#=83398 tim=2787353704582
now read from 16KB Block - The block count decreases resulting in slower response time
PARSING IN CURSOR #101737960 len=39 dep=0 uid=0 oct=3 lid=0 tim=2787379405714 hv=3142550934 ad='7ff203f7978' sqlid='0xufk4uxnz1cq'
select count(*) from big_table_bigblock
END OF STMT
PARSE #101737960:c=0,e=5371,p=0,cr=1081,cu=0,mis=1,r=0,dep=0,og=1,plh=3466094791,tim=2787379405713
EXEC #101737960:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3466094791,tim=2787379405788
WAIT #101737960: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=83398 tim=2787379405819
WAIT #101737960: nam='direct path read' ela= 169123 file number=6 first dba=67 block cnt=13 obj#=83404 tim=2787379575386
WAIT #101737960: nam='direct path read' ela= 11124 file number=6 first dba=81 block cnt=15 obj#=83404 tim=2787379586898
WAIT #101737960: nam='direct path read' ela= 2629 file number=6 first dba=97 block cnt=15 obj#=83404 tim=2787379590024
WAIT #101737960: nam='direct path read' ela= 4623 file number=6 first dba=113 block cnt=63 obj#=83404 tim=2787379595208


*** 2015-01-13 14:18:40.366
WAIT #101737960: nam='direct path read' ela= 6177 file number=6 first dba=177 block cnt=63 obj#=83404 tim=2787379602175
WAIT #101737960: nam='direct path read' ela= 4827 file number=6 first dba=241 block cnt=63 obj#=83404 tim=2787379607863
WAIT #101737960: nam='direct path read' ela= 23539 file number=6 first dba=305 block cnt=63 obj#=83404 tim=2787379632185
WAIT #101737960: nam='direct path read' ela= 2795 file number=6 first dba=369 block cnt=63 obj#=83404 tim=2787379636121
WAIT #101737960: nam='direct path read' ela= 5415 file number=6 first dba=433 block cnt=31 obj#=83404 tim=2787379642265
WAIT #101737960: nam='direct path read' ela= 2689 file number=6 first dba=464 block cnt=32 obj#=83404 tim=2787379645402
WAIT #101737960: nam='direct path read' ela= 11709 file number=6 first dba=497 block cnt=63 obj#=83404 tim=2787379657645
WAIT #101737960: nam='direct path read' ela= 112017 file number=6 first dba=625 block cnt=63 obj#=83404 tim=2787379771231
WAIT #101737960: nam='direct path read' ela= 9686 file number=6 first dba=753 block cnt=63 obj#=83404 tim=2787379783335
WAIT #101737960: nam='direct path read' ela= 1070 file number=6 first dba=864 block cnt=16 obj#=83404 tim=2787379785805

Migrate ASM From One Storage to Another Storage

 

 

 

 

 

 

 

ORACLE ZFS ->VNX 5400 MIGRATION

 

 

 

 

 

Document History
Version	Author	Description of Changes	Supported / Review by	Date	Status
1.0	Elamaran Elango	First Release		21-Aug-2014	Draft

 

 

 

 

 

 

 

 

 

 

 

 

 

Contents

 

1. Purpose: 3

 

2. Audience. 3

 

3. Details: 3

 

4. Fix: 4

 

5. Resolution: 5

 

 

 

 

1. Purpose:

 

The purpose of the change is to virtualize the Storage used at COMPANY for the databases and especially the critical Oracle APPS/APP databases hosted in ZFS Storage. As a part of this project, LUNS will be consolidated and will be named in such a way that we should be able to identify the LUNS from all the way up from

STORAGE->HOST->DB

 

2. Audience

 

This documentation is primarily intended for Database Administrators, IT Managers who administer Database Management Systems.

3. Procedure

Component for Migrations:

1.        SPFILE

2.        CONTROL FILE

3.        VOTING DISK

4.        OCR

5.        DATAFILES

6.        REDOLOG FILES

7.        TEMP FILES

8.        ARCHIVED REDO LOG

 

This documents will cover the production TEST  since it is the first one to be migrated. All the steps have been tested in UAT.

 

We  use ASM for Orace databases.

 

 

                Current Groups at the DB Level

                ASM_ARC

ASM_DATA01

ASM_DATA02

ASM_DATA03

ASM_DATA04

ASM_DATA05

ASM_DATA06

ASM_DATA07

ASM_OCRVOTE – This is Normal Redundancy Disk Group

ASM_OCR

ASM_REDO01

New Disk Groups will be  like below.

 

                ASM_VARCH

ASM_VDATA1

ASM_VDATA2

ASM_VDATA3

ASM_VDATA4

ASM_VREDO

ASM_VOCR – This will be Normal Redundancy Disk Group.

 

 

NEW DISK GROUPS WILL BE CREATED IN TEST with External Redundancy with  AU 1 M:

 

ASM_VARCH

ASM_VDATA1

ASM_VDATA2

ASM_VDATA3

ASM_VDATA4

ASM_VREDO

Backup the Current Database :

 

To Backup the DB :

 

Make the below changes in LISTENER.ora

 

 

DONT DROP ANY REDOLOG FILES UNTIL OPENING THE DATABASE WITH RESETLOGS.

 

GRID_HOME

1. Get the service_name from DB Parameter:

2. Check the TNSNAMES.ora in the Server GRID home for the SERVICE_NAME also.

2. Get the SCAN_name from the current listener.ora

3. Add the below entry in the GRID Listener

4. Reset the sysdba password.

 

************************** cluster environment********************************

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = testsrv.ww005.Company.net)   --- Service Name

      (ORACLE_HOME = /oracle/app/oracle/1120/db)

      (SID_NAME = test_1)  --- INSTANCE NAME

    )

  )

SID_LIST_LISTENER_SCAN1 =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = testsrv.ww005.Company.net)

      (ORACLE_HOME = /oracle/app/oracle/1120/db)

      (SID_NAME = test_1)

    )

  )

SID_LIST_LISTENER_SCAN2  =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = testsrv.ww005.Company.net)

      (ORACLE_HOME = /oracle/app/oracle/1120/db)

      (SID_NAME = test_1)

    )

  )

****************************************************************

 

5. SRVCTL STOP SCAN_LISTENER

6. SRCTL START SCAN_LISTENER

7. Sqlplus sys@test as sysdba

 

Run the Backup script

RMAN_COLD_full from networker backup software or any software

 

1.        Create DISKGROUPS for VOTING DISKS:

Using ASMCA

 

Check the Backups for OCR :

 

                ocrconfig -showbackup

 

Verify if the backup file is correct

               

                ocrdump -backupfile backup_file_name

 

Replace the VOTING DISK

 

Change ASM Disk Group for Voting Disk to the new ASM Disk Group:

 

crsctl query css votedisk

 

+ASM_VOCRVOTE – DISKGROUP should have been created before replacing.

 

crsctl replace votedisk +ASM_VOCRVOTE

 

crsctl query css votedisk

 

REPLACE THE OCR FILE LOCATION

 

$oracle_home/bin/Ocrcheck

 

Add the  new ASM Disk Group  to the OCR Disk Group (Note that New ASM Group +ASM_VOCRVOTE must be mounted on all the nodes in the RAC)

 

ocrconfig –add +ASM_VOCRVOTE

 

 

 

After you confirm that you will no longer use the old Disk Group for OCR, you can remove the previous ASM Disk Group (ASM_OCR)

 

ocrconfig –delete +ASM_OCRVOTE

 

 

Before stopping the CRS the databases have to be stopped

 

CRCTL STOP CRS

 

CRSCTL START CRS

 

Add the new diskgroup to the cluster resources.

 

Crsctl stat  res –init –t

Crsctl check cluster –all

Crsctl stat res –t

 

Crsctl stat  res –init –t

Crsctl check cluster –all

Crsctl stat res –t

 

 

 

ASM SPFILE RELOCATION :

 

Login as «sysasm »

 

SQL> create pfile=’/export/home/oracle/initasmpfile’ from spfile ;

 

SQL>Create spfile =’+ASM_VDATA01’ from ’/export/home/oracle/initasmpfile’

 

 

5. Migrate Database

 

SPFILE MOVE:

 

 

-- Backup the spfile:

 

·         SQL> CREATE file='/export/home/oracle/asm_migration2014/inittest_beforemigration.ora' from spfile;

 

-- Backup the Control File:

 

·         SQL> Alter database backup controlfile to '+ASM_VDATA1/test/controlbackup.ctl';

·         SQL> Alter database backup controlfile to trace;

 

 Verify the backed up files for SPFILE and CONTROLFILE location

                 

-- Set the control file location to the new disk group to spfile:

 

·         SQL>ALTER SYSTEM SET CONTROL_FILES='+ASM_VDATA1/test/control01.ctl','+ASM_VDATA2/test/control02.ctl' scope =spfile sid='*';

 

----Set archive log location

·         SQL > alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=+ASM_VARCH' scope=spfile sid='*'

 

--Now create the pfile from spfile to reflect the new changes.

                 

·         SQL> create pfile='/export/home/oracle/asm_migration2014/inittest_aftermigration.ora' from spfile ;

 

--Create the spfile based on step above

                 

·         SQL> create spfile='+ASM_VDATA1/test/spfiletest.ora' from  pfile='/export/home/oracle/asm_migration2014/inittest_aftermigration.ora';

               

NOTE DOWN THE LCOATION IN THE NEW ASM DISK GROUP and change the INITFILE to point to new location

 

change the SPFILE location in the cluster

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

bash-3.2$  srvctl config database

 

bash-3.2$

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

 

srvctl modify database -d test -p '+ASM_VDATA1/test/spfiletest.ora'

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>APPSlprd>>>>>>>>>>>>>>>>>>>>>>>>>>>>

bash-3.2$ srvctl modify database -d test -p '+ASM_VDATA1/test/spfiletest.ora'

bash-3.2$ srvctl config database -d  test

 

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

 

Check – if the database starts with new spfile: - Not mandatory

 restart database

 

DATAFILES/CONTROLFILE:

 

RMAN>shutdown immediate

RMAN> STARTUP NOMOUNT- this will start with new spfile

 

RMAN > restore controlfile to '+ASM_VDATA1/test/control01.ctl' from '+ASM_VDATA1/test/controlbackup.ctl';

 

RMAN > restore controlfile to '+ASM_VDATA2/test/control02.ctl' from '+ASM_VDATA1/test/controlbackup.ctl';

 

Control files are restored :

 

Run the below to copy the file via rman to move to new storage :

 

Nohup ./RMAN_BACKUP_DG.SH  > rman_backup_dg.log &

 

 

Sample  script:

 

run {

CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO copy; - This is to speed the backup copy

e.g

Backup as copy datafile "+ASM_DATA01/test/datafile/appsidx.256.809013267" FORMAT "+ASM_VDATA1";

Backup as copy datafile "+ASM_DATA02/test/datafile/appsidx.260.809014683" FORMAT "+ASM_VDATA2";

}

 

 

Once the backup is completed, we need to update the controlfile with the new names:

 

 

nohup ./rman_switch.sh >rman_switch.log &

 

contents of the datafile switch

 

 SWITCH DATAFILE "+ASM_DATA01/test/datafile/appsdat.270.792265887" TO COPY;

 

 

 SWITCH DATAFILE "+ASM_DATA01/test/datafile/appsdat.285.807732001" TO COPY;

 

 

 SWITCH DATAFILE "+ASM_DATA01/test/datafile/appsidx.266.792265591" TO COPY;

 

 

 SWITCH DATAFILE "+ASM_DATA01/test/datafile/appsidx.271.792265961" TO COPY;

 

 

 SWITCH DATAFILE "+ASM_DATA01/test/datafile/appsidx.272.792266033" TO COPY;

 

 

 SWITCH DATAFILE "+ASM_DATA01/test/datafile/appsidx.273.792266107" TO COPY;

 

 

Verify for errors:

 

 

 

Try to open the database:

 

SQL>recover database using backup controlfile;

 

Apply the logs if required

 

 

 

alter database open ; or resetlogs option.

 

 

.4 change  tempfile to new location

SQL> select NAME,STATUS,bytes/1024/1024/1024  size_GB from  v$tempfile;

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE  '+ASM_VDATA4' SIZE  30g ;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE  '+ASM_VDATA1' SIZE  20g ;---- APPS

SQL> ALTER DATABASE TEMPFILE '+ASM_DATA06/test/tempfile/temp.276.826393817' drop;

SQL> ALTER DATABASE TEMPFILE '+ASM_DATA01/test/temp01.dbf' drop;

 

 

 

 

 

 

 

 

 change redo LOG LOCATION

 find out how many members in redo log :

select  a.GROUP#, b.MEMBER,a.THREAD# ,a.ARCHIVED  FROM V$LOG a, V$LOGFILE  b where a.GROUP#=b.GROUP# order by a.GROUP#;

 

 add more member

 

alter database add logfile member  '+ASM_VREDO/test/redo01.log'   to group 1;

alter database add logfile member  '+ASM_VREDO/test/redo02.log'   to group 2;

alter database add logfile member  '+ASM_VREDO/test/redo03.log'   to group 3;

alter database add logfile member  '+ASM_VREDO/test/redo04.log'   to group 4;

alter database add logfile member  '+ASM_VREDO/test/redo05.log'   to group 5;

alter database add logfile member  '+ASM_VREDO/test/redo06.log'   to group 6;

 

select  a.GROUP#, b.MEMBER,a.THREAD# ,a.ARCHIVED ,a.status FROM V$LOG a, V$LOGFILE  b where a.GROUP#=b.GROUP# order by a.GROUP#, member;

 

 

 

 

--------------------------TEST-------------------------------------------------------------------

SQL> alter database add logfile member  '+ASM_VREDO/test/redo01.log'  to group 1;

SQL> alter database add logfile member  '+ASM_VREDO/test/redo02.log'  to group 2;

SQL> alter database add logfile member  '+ASM_VREDO/test/redo03.log'  to group 3;

SQL> alter database add logfile member  '+ASM_VREDO/test/redo04.log'  to group 4;

SQL> alter database add logfile member  '+ASM_VREDO/test/redo05.log'  to group 5;

SQL> alter database add logfile member  '+ASM_VREDO/test/redo06.log'  to group 6;

 

 

 

Drop the old files :

 

backup validate check logical database ;

 

SQL> alter database drop logfile member  '+ASM_REDO01/test/redo01.log',

SQL> alter database drop logfile member  '+ASM_REDO01/test/redo02.log,

SQL> alter database drop logfile member  '+ASM_REDO01/test/redo03.log,

SQL> alter database drop logfile member  '+ASM_REDO01/test/redo04.log,

SQL> alter database drop logfile member  '+ASM_REDO01/test/redo05.log,

SQL> alter database drop logfile member  '+ASM_REDO01/test/redo06.log

 

 

backup validate check logical database ;

 

The above command will validate all the logical and physical blocks of the DB:

 

 

 

Have a nice day!!!