Tuesday, December 18, 2018

rman switch df with or without run prompt - which one is quicker & metrics associated

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.

=====================================================================

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


No comments:

Post a Comment

Troubleshooting the “Cannot Generate SSPI Context” Error After SQL Server Migration

  Introduction After a recent  SQL Server migration from 2012 to 2022 , our team encountered a classic authentication issue: The target prin...