Lab: Which one is better and quicker - rman switch df in run or without run... when siwtching multiple datafiles.
oracle version: 11.2.0.1
Without Run version:
-----------------
RMAN> @rmn_swtch.cmd
RMAN> switch datafile 8 to copy;
datafile 8 switched to datafile copy "/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST2_FNO-8_08tl5igt"
RMAN> switch datafile 9 to copy;
datafile 9 switched to datafile copy "/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST3_FNO-9_09tl5ih1"
RMAN> switch datafile 10 to copy;
datafile 10 switched to datafile copy "/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST4_FNO-10_0atl5ih2"
the rman job in run is swift compared to non run rman session.
No run - just switch in a command file:
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 209 0.12 0.21 0 0 0 0
Execute 221 0.17 0.22 0 0 0 137
Fetch 75 0.11 0.13 0 0 0 75
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 505 0.41 0.57 0 0 0 212
Misses in library cache during parse: 14
Misses in library cache during execute: 4
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 257 0.00 0.00
SQL*Net message from client 256 78.14 128.98
control file sequential read 1141 0.00 0.05
asynch descriptor resize 26 0.00 0.00
SQL*Net break/reset to client 12 0.00 0.00
Disk file operations I/O 33 0.00 0.00
RMAN backup & recovery I/O 6 0.00 0.00
control file parallel write 72 0.00 0.08
control file single write 39 0.00 0.03
recovery area: computing dropped files 3 0.00 0.01
recovery area: computing obsolete files 3 0.45 1.17
recovery area: computing backed up files 3 0.06 0.10
recovery area: computing applied logs 3 0.00 0.01
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 228 0.06 0.07 0 0 0 0
Execute 346 1.37 1.44 0 0 0 102
Fetch 403 0.20 0.20 0 26 0 309
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 977 1.64 1.71 0 26 0 411
Misses in library cache during parse: 32
Misses in library cache during execute: 11
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 0.00 0.00
control file sequential read 1506 0.00 0.02
asynch descriptor resize 66 0.00 0.00
Disk file operations I/O 24 0.00 0.00
db file sequential read 30 0.00 0.00
rdbms ipc reply 14 0.00 0.00
211 user SQL statements in session.
346 internal SQL statements in session.
557 SQL statements in session.
********************************************************************************
Trace file: ggtest1_ora_2440.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
211 user SQL statements in trace file.
346 internal SQL statements in trace file.
557 SQL statements in trace file.
67 unique SQL statements in trace file.
99729 lines in trace file.
131 elapsed seconds in trace file.
******************************************************************************
In rman under run prompt...
command generator:
-------------------
select 'switch datafile '||''''||a.file_name||''''||' to datafilecopy '||''''||b.name||''''||';'
from dba_data_files a,v$datafile_copy b
where a.file_id=b.file#
and b.status='A'
and a.file_id in (8,9,10)
order by a.file_name;
--------------------
RMAN> run
2> {
3> switch datafile '/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST2_FNO-8_08tl5igt' to datafilecopy '/oracle_data_2/ggtest1/test2_01.dbf';
4> switch datafile '/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST3_FNO-9_09tl5ih1' to datafilecopy '/oracle_data_2/ggtest1/test3_01.dbf';
5> switch datafile '/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST4_FNO-10_0atl5ih2' to datafilecopy '/oracle_data_2/ggtest1/test4_01.dbf';
6> }
using target database control file instead of recovery catalog
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=995281762 file name=/oracle_data_2/ggtest1/test2_01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=995281764 file name=/oracle_data_2/ggtest1/test3_01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=995281764 file name=/oracle_data_2/ggtest1/test4_01.dbf
RMAN> **end-of-file**
==============================================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 536 0.02 0.03 0 0 0 0
Execute 546 0.29 0.34 0 0 0 484
Fetch 54 0.09 0.11 0 0 0 54
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1136 0.41 0.49 0 0 0 538
Misses in library cache during parse: 15
Misses in library cache during execute: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 564 0.00 0.00
SQL*Net message from client 563 0.22 0.41
control file sequential read 897 0.00 0.01
asynch descriptor resize 18 0.00 0.00
SQL*Net break/reset to client 12 0.00 0.00
Disk file operations I/O 24 0.00 0.00
RMAN backup & recovery I/O 9 0.00 0.00
control file parallel write 30 0.00 0.03
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20 0.00 0.00 0 0 0 0
Execute 27 0.06 0.06 0 0 0 0
Fetch 33 0.02 0.02 0 16 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80 0.10 0.09 0 16 0 12
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 197 0.00 0.00
asynch descriptor resize 24 0.00 0.00
536 user SQL statements in session.
27 internal SQL statements in session.
563 SQL statements in session.
********************************************************************************
Trace file: ggtest1_ora_2598.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
536 user SQL statements in trace file.
27 internal SQL statements in trace file.
563 SQL statements in trace file.
39 unique SQL statements in trace file.
25741 lines in trace file.
1 elapsed seconds in trace file.
=====================================================================
******************************************************************************
oracle version: 11.2.0.1
Without Run version:
-----------------
RMAN> @rmn_swtch.cmd
RMAN> switch datafile 8 to copy;
datafile 8 switched to datafile copy "/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST2_FNO-8_08tl5igt"
RMAN> switch datafile 9 to copy;
datafile 9 switched to datafile copy "/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST3_FNO-9_09tl5ih1"
RMAN> switch datafile 10 to copy;
datafile 10 switched to datafile copy "/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST4_FNO-10_0atl5ih2"
the rman job in run is swift compared to non run rman session.
No run - just switch in a command file:
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 209 0.12 0.21 0 0 0 0
Execute 221 0.17 0.22 0 0 0 137
Fetch 75 0.11 0.13 0 0 0 75
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 505 0.41 0.57 0 0 0 212
Misses in library cache during parse: 14
Misses in library cache during execute: 4
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 257 0.00 0.00
SQL*Net message from client 256 78.14 128.98
control file sequential read 1141 0.00 0.05
asynch descriptor resize 26 0.00 0.00
SQL*Net break/reset to client 12 0.00 0.00
Disk file operations I/O 33 0.00 0.00
RMAN backup & recovery I/O 6 0.00 0.00
control file parallel write 72 0.00 0.08
control file single write 39 0.00 0.03
recovery area: computing dropped files 3 0.00 0.01
recovery area: computing obsolete files 3 0.45 1.17
recovery area: computing backed up files 3 0.06 0.10
recovery area: computing applied logs 3 0.00 0.01
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 228 0.06 0.07 0 0 0 0
Execute 346 1.37 1.44 0 0 0 102
Fetch 403 0.20 0.20 0 26 0 309
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 977 1.64 1.71 0 26 0 411
Misses in library cache during parse: 32
Misses in library cache during execute: 11
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 0.00 0.00
control file sequential read 1506 0.00 0.02
asynch descriptor resize 66 0.00 0.00
Disk file operations I/O 24 0.00 0.00
db file sequential read 30 0.00 0.00
rdbms ipc reply 14 0.00 0.00
211 user SQL statements in session.
346 internal SQL statements in session.
557 SQL statements in session.
********************************************************************************
Trace file: ggtest1_ora_2440.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
211 user SQL statements in trace file.
346 internal SQL statements in trace file.
557 SQL statements in trace file.
67 unique SQL statements in trace file.
99729 lines in trace file.
131 elapsed seconds in trace file.
******************************************************************************
In rman under run prompt...
command generator:
-------------------
select 'switch datafile '||''''||a.file_name||''''||' to datafilecopy '||''''||b.name||''''||';'
from dba_data_files a,v$datafile_copy b
where a.file_id=b.file#
and b.status='A'
and a.file_id in (8,9,10)
order by a.file_name;
--------------------
RMAN> run
2> {
3> switch datafile '/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST2_FNO-8_08tl5igt' to datafilecopy '/oracle_data_2/ggtest1/test2_01.dbf';
4> switch datafile '/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST3_FNO-9_09tl5ih1' to datafilecopy '/oracle_data_2/ggtest1/test3_01.dbf';
5> switch datafile '/oracle_data_2/cpydata_D-GGTEST1_I-1273850353_TS-TEST4_FNO-10_0atl5ih2' to datafilecopy '/oracle_data_2/ggtest1/test4_01.dbf';
6> }
using target database control file instead of recovery catalog
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=995281762 file name=/oracle_data_2/ggtest1/test2_01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=995281764 file name=/oracle_data_2/ggtest1/test3_01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=995281764 file name=/oracle_data_2/ggtest1/test4_01.dbf
RMAN> **end-of-file**
==============================================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 536 0.02 0.03 0 0 0 0
Execute 546 0.29 0.34 0 0 0 484
Fetch 54 0.09 0.11 0 0 0 54
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1136 0.41 0.49 0 0 0 538
Misses in library cache during parse: 15
Misses in library cache during execute: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 564 0.00 0.00
SQL*Net message from client 563 0.22 0.41
control file sequential read 897 0.00 0.01
asynch descriptor resize 18 0.00 0.00
SQL*Net break/reset to client 12 0.00 0.00
Disk file operations I/O 24 0.00 0.00
RMAN backup & recovery I/O 9 0.00 0.00
control file parallel write 30 0.00 0.03
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20 0.00 0.00 0 0 0 0
Execute 27 0.06 0.06 0 0 0 0
Fetch 33 0.02 0.02 0 16 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80 0.10 0.09 0 16 0 12
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 197 0.00 0.00
asynch descriptor resize 24 0.00 0.00
536 user SQL statements in session.
27 internal SQL statements in session.
563 SQL statements in session.
********************************************************************************
Trace file: ggtest1_ora_2598.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
536 user SQL statements in trace file.
27 internal SQL statements in trace file.
563 SQL statements in trace file.
39 unique SQL statements in trace file.
25741 lines in trace file.
1 elapsed seconds in trace file.
=====================================================================
******************************************************************************
No comments:
Post a Comment