Duplicate ORCL (no-archivemode) (diskgroup DATA) to UAT (no-archive) ( diskgroup UAT_DATA)

[oracle@node1 BACKUP]$ export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
[oracle@node1 BACKUP]$ rman target  /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 9 01:49:46 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1449691228)

RMAN> list backup ; 

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
1       Incr 0  1.02M      DISK        00:00:01     Sep 08 2016 08:22:01
        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_02rf9b18_1_1.bak
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1931739    Sep 08 2016 08:14:38 +DATA/ORCL/DATAFILE/sumit.272.921987559

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
2       Incr 0  1.09M      DISK        00:00:07     Sep 08 2016 08:22:09
        BP Key: 2   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_07rf9b1a_1_1.bak
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  6    0  Incr 1931739    Sep 08 2016 08:14:38 +DATA/ORCL/DATAFILE/users.259.921910449

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
3       Incr 0  80.00K     DISK        00:00:00     Sep 08 2016 08:22:06
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_08rf9b1e_1_1.bak
  SPFILE Included: Modification time: Sep 08 2016 08:10:24
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
4       Incr 0  16.77M     DISK        00:00:12     Sep 08 2016 08:22:13
        BP Key: 4   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_05rf9b19_1_1.bak
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    0  Incr 1931739    Sep 08 2016 08:14:38 +DATA/ORCL/DATAFILE/undotbs1.260.921910449

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
5       Incr 0  21.41M     DISK        00:00:28     Sep 08 2016 08:22:27
        BP Key: 5   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_01rf9b17_1_1.bak
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1931739    Sep 08 2016 08:14:38 +DATA/ORCL/DATAFILE/example.270.921910549

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
6       Incr 0  1.03M      DISK        00:00:32     Sep 08 2016 08:22:33
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_06rf9b19_1_1.bak
  Control File Included: Ckp SCN: 1931739      Ckp time: Sep 08 2016 08:14:38

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
7       Incr 0  99.54M     DISK        00:00:39     Sep 08 2016 08:22:39
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_04rf9b18_1_1.bak
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1931739    Sep 08 2016 08:14:38 +DATA/ORCL/DATAFILE/sysaux.257.921910247

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
8       Incr 0  204.73M    DISK        00:00:54     Sep 08 2016 08:22:54
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T082159
        Piece Name: /data01/BACKUP/orcl_LVL0_03rf9b18_1_1.bak
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1931739    Sep 08 2016 08:14:38 +DATA/ORCL/DATAFILE/system.258.921910333

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
9       Incr 0  1.02M      DISK        00:00:00     Sep 08 2016 12:07:06
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0brf9o7a_1_1.bak
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1942586    Sep 08 2016 12:07:06 +DATA/ORCL/DATAFILE/sumit.272.921987559

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
10      Incr 0  80.00K     DISK        00:00:04     Sep 08 2016 12:07:25
        BP Key: 10   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0hrf9o7p_1_1.bak
  SPFILE Included: Modification time: Sep 08 2016 12:06:36
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
11      Incr 0  1.40M      DISK        00:00:22     Sep 08 2016 12:07:28
        BP Key: 11   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0erf9o7a_1_1.bak
  List of Datafiles in backup set 11
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    0  Incr 1942590    Sep 08 2016 12:07:07 +DATA/ORCL/DATAFILE/undotbs1.260.921910449

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
12      Incr 0  21.41M     DISK        00:00:25     Sep 08 2016 12:07:30
        BP Key: 12   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0arf9o79_1_1.bak
  List of Datafiles in backup set 12
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1942587    Sep 08 2016 12:07:06 +DATA/ORCL/DATAFILE/example.270.921910549

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
13      Incr 0  1.09M      DISK        00:00:25     Sep 08 2016 12:07:40
        BP Key: 13   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0grf9o7j_1_1.bak
  List of Datafiles in backup set 13
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  6    0  Incr 1942599    Sep 08 2016 12:07:30 +DATA/ORCL/DATAFILE/users.259.921910449

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
14      Incr 0  1.03M      DISK        00:00:37     Sep 08 2016 12:07:48
        BP Key: 14   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0frf9o7f_1_1.bak
  Control File Included: Ckp SCN: 1942593      Ckp time: Sep 08 2016 12:07:15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
15      Incr 0  99.77M     DISK        00:00:44     Sep 08 2016 12:07:50
        BP Key: 15   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0drf9o7a_1_1.bak
  List of Datafiles in backup set 15
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1942589    Sep 08 2016 12:07:06 +DATA/ORCL/DATAFILE/sysaux.257.921910247

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
16      Incr 0  204.66M    DISK        00:00:55     Sep 08 2016 12:08:01
        BP Key: 16   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120704
        Piece Name: /data01/BACKUP/orcl_LVL0_0crf9o7a_1_1.bak
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1942588    Sep 08 2016 12:07:06 +DATA/ORCL/DATAFILE/system.258.921910333

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
17      8.94M      DISK        00:00:01     Sep 08 2016 12:08:14
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T120812
        Piece Name: /data01/BACKUP/orcl_arch_0irf9o9d_1_1.bak

  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    8       1930443    Sep 08 2016 08:08:06 1942733    Sep 08 2016 12:08:08

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
18      Incr 0  1.02M      DISK        00:00:01     Sep 08 2016 12:09:44
        BP Key: 18   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0lrf9oc7_1_1.bak
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1943154    Sep 08 2016 12:09:43 +DATA/ORCL/DATAFILE/sumit.272.921987559

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
19      Incr 0  80.00K     DISK        00:00:00     Sep 08 2016 12:09:55
        BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0rrf9ocj_1_1.bak
  SPFILE Included: Modification time: Sep 08 2016 12:09:49
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
20      Incr 0  1.41M      DISK        00:00:16     Sep 08 2016 12:10:00
        BP Key: 20   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0orf9oc8_1_1.bak
  List of Datafiles in backup set 20
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    0  Incr 1943158    Sep 08 2016 12:09:45 +DATA/ORCL/DATAFILE/undotbs1.260.921910449

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
21      Incr 0  21.41M     DISK        00:00:35     Sep 08 2016 12:10:18
        BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0krf9oc7_1_1.bak
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  5    0  Incr 1943153    Sep 08 2016 12:09:43 +DATA/ORCL/DATAFILE/example.270.921910549

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
22      Incr 0  1.03M      DISK        00:00:37     Sep 08 2016 12:10:21
        BP Key: 22   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0prf9oc8_1_1.bak
  Control File Included: Ckp SCN: 1943156      Ckp time: Sep 08 2016 12:09:44

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
23      Incr 0  99.80M     DISK        00:00:45     Sep 08 2016 12:10:28
        BP Key: 23   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0nrf9oc7_1_1.bak
  List of Datafiles in backup set 23
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1943156    Sep 08 2016 12:09:44 +DATA/ORCL/DATAFILE/sysaux.257.921910247

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
24      Incr 0  1.09M      DISK        00:00:45     Sep 08 2016 12:10:29
        BP Key: 24   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0qrf9oc8_1_1.bak
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  6    0  Incr 1943169    Sep 08 2016 12:09:56 +DATA/ORCL/DATAFILE/users.259.921910449

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
25      Incr 0  204.66M    DISK        00:00:51     Sep 08 2016 12:10:34
        BP Key: 25   Status: AVAILABLE  Compressed: YES  Tag: TAG20160908T120942
        Piece Name: /data01/BACKUP/orcl_LVL0_0mrf9oc7_1_1.bak
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1943155    Sep 08 2016 12:09:43 +DATA/ORCL/DATAFILE/system.258.921910333

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
26      136.50K    DISK        00:00:01     Sep 08 2016 12:10:46
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0trf9oe5_1_1.bak

  List of Archived Logs in backup set 26
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    9       1942733    Sep 08 2016 12:08:08 1943057    Sep 08 2016 12:09:28

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
27      9.50K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0urf9oe6_1_1.bak

  List of Archived Logs in backup set 27
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    13      1943066    Sep 08 2016 12:09:34 1943226    Sep 08 2016 12:10:44

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
28      8.94M      DISK        00:00:01     Sep 08 2016 12:10:46
        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0srf9oe5_1_1.bak

  List of Archived Logs in backup set 28
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    8       1930443    Sep 08 2016 08:08:06 1942733    Sep 08 2016 12:08:08

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
29      3.50K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0vrf9oe6_1_1.bak

  List of Archived Logs in backup set 29
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    10      1943057    Sep 08 2016 12:09:28 1943060    Sep 08 2016 12:09:30

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
30      3.00K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 30   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_10rf9oe6_1_1.bak

  List of Archived Logs in backup set 30
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    11      1943060    Sep 08 2016 12:09:30 1943063    Sep 08 2016 12:09:33

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
31      2.50K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_11rf9oe6_1_1.bak

  List of Archived Logs in backup set 31
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    12      1943063    Sep 08 2016 12:09:33 1943066    Sep 08 2016 12:09:34

RMAN> list backup of archivelog all ; 


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
17      8.94M      DISK        00:00:01     Sep 08 2016 12:08:14
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T120812
        Piece Name: /data01/BACKUP/orcl_arch_0irf9o9d_1_1.bak

  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    8       1930443    Sep 08 2016 08:08:06 1942733    Sep 08 2016 12:08:08

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
26      136.50K    DISK        00:00:01     Sep 08 2016 12:10:46
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0trf9oe5_1_1.bak

  List of Archived Logs in backup set 26
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    9       1942733    Sep 08 2016 12:08:08 1943057    Sep 08 2016 12:09:28

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
27      9.50K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0urf9oe6_1_1.bak

  List of Archived Logs in backup set 27
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    13      1943066    Sep 08 2016 12:09:34 1943226    Sep 08 2016 12:10:44

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
28      8.94M      DISK        00:00:01     Sep 08 2016 12:10:46
        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0srf9oe5_1_1.bak

  List of Archived Logs in backup set 28
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    8       1930443    Sep 08 2016 08:08:06 1942733    Sep 08 2016 12:08:08

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
29      3.50K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_0vrf9oe6_1_1.bak

  List of Archived Logs in backup set 29
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    10      1943057    Sep 08 2016 12:09:28 1943060    Sep 08 2016 12:09:30

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
30      3.00K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 30   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_10rf9oe6_1_1.bak

  List of Archived Logs in backup set 30
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    11      1943060    Sep 08 2016 12:09:30 1943063    Sep 08 2016 12:09:33

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ --------------------
31      2.50K      DISK        00:00:00     Sep 08 2016 12:10:46
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T121045
        Piece Name: /data01/BACKUP/orcl_arch_11rf9oe6_1_1.bak

  List of Archived Logs in backup set 31
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    12      1943063    Sep 08 2016 12:09:33 1943066    Sep 08 2016 12:09:34

[oracle@node1 BACKUP]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 8 08:43:06 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1449691228, not open)

RMAN> report schema ; 

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     +DATA/ORCL/DATAFILE/system.258.921910333
2    1024     SUMIT                ***     +DATA/ORCL/DATAFILE/sumit.272.921987559
3    610      SYSAUX               ***     +DATA/ORCL/DATAFILE/sysaux.257.921910247
4    80       UNDOTBS1             ***     +DATA/ORCL/DATAFILE/undotbs1.260.921910449
5    1243     EXAMPLE              ***     +DATA/ORCL/DATAFILE/example.270.921910549
6    5        USERS                ***     +DATA/ORCL/DATAFILE/users.259.921910449

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       +DATA/ORCL/TEMPFILE/temp.269.921910529

2. Add TNS entry for UAT in ORCL and ORCL entry in UAT . 

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

UAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = uat)
    )
)

3) Copy Password File from ORCL to UAT and rename it to orapw$ORACLE_SID



4.) Add static entry of UAT is listener file ( grid )

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = uat)
    (ORACLE_HOME = /data01/app/oracle/product/11g/db_1)
    (SID_NAME = uat)
   )
 )

 [grid@node2 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-SEP-2016 09:15:09

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2.localdomain)(PORT=1521)))
The command completed successfully
[grid@node2 admin]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-SEP-2016 09:15:16

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "uat" has 1 instance(s).
  Instance "uat", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


 5.)Verify TNS Connectivity to UAT from PROD


[oracle@node1 dbs]$ sqlplus sys/xxxxx@uat as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 8 09:52:19 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

6.) start UAT on node2

[oracle@node2 dbs]$
mkdir -p /data01/app/oracle/admin/uat/adump
mkdir -p /data01/app/oracle/admin/orcl/adump

SQL>  cat inituat.ora
db_name='uat'


[oracle@node2 dbs]$ export ORACLE_SID=uat
[oracle@node2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 8 10:12:07 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount  ;
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size    2922712 bytes
Variable Size  159385384 bytes
Database Buffers   50331648 bytes
Redo Buffers    5464064 bytes
SQL> show parameter db_name ;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name     string uat


7) Connect RMAN to Target (orcl) and Auxiliary (uat) using TNS

[oracle@node2 dbs]$ rman  auxiliary /  | tee rman_duplicate_achive.log

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 8 12:24:07 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: UAT (not mounted)

RMAN>
echo set on

RMAN> @rman_duplicate.cmd
RMAN> run {
 duplicate database to uat 
 spfile 
 parameter_value_convert ('ORCL','UAT')
 set control_files='+UAT_DATA'
set db_file_name_convert='+DATA','+UAT_DATA'
 set log_file_name_convert='+DATA','+UAT_DATA'
 set db_name='uat'
 BACKUP  LOCATION '/data01/BACKUP_NODE2/' ;
 }
Starting Duplicate Db at 08-SEP-2016 12:24:32

contents of Memory Script:
{
   restore clone spfile to  '/data01/app/oracle/product/11g/db_1/dbs/spfileuat.ora' from
 '/data01/BACKUP_NODE2/orcl_LVL0_0rrf9ocj_1_1.bak';
   sql clone "alter system set spfile= ''/data01/app/oracle/product/11g/db_1/dbs/spfileuat.ora''";
}
executing Memory Script

Starting restore at 08-SEP-2016 12:24:32
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK

channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /data01/BACKUP_NODE2/orcl_LVL0_0rrf9ocj_1_1.bak
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-SEP-2016 12:24:33

sql statement: alter system set spfile= ''/data01/app/oracle/product/11g/db_1/dbs/spfileuat.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''UAT'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''+UAT_DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''+DATA'', ''+UAT_DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''+DATA'', ''+UAT_DATA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_name =
 ''uat'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''UAT'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''+UAT_DATA'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA'', ''+UAT_DATA'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA'', ''+UAT_DATA'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''uat'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     515899392 bytes

Fixed Size                     2926224 bytes
Variable Size                436210032 bytes
Database Buffers              71303168 bytes
Redo Buffers                   5459968 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+UAT_DATA/UAT/CONTROLFILE/current.256.922019095'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''UAT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/data01/BACKUP_NODE2/orcl_LVL0_0prf9oc8_1_1.bak';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+UAT_DATA/UAT/CONTROLFILE/current.256.922019095'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''UAT'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     515899392 bytes

Fixed Size                     2926224 bytes
Variable Size                436210032 bytes
Database Buffers              71303168 bytes
Redo Buffers                   5459968 bytes

Starting restore at 08-SEP-2016 12:25:21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=28 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+UAT_DATA/UAT/CONTROLFILE/current.256.922019095
Finished restore at 08-SEP-2016 12:25:24

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=28 device type=DISK
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set until scn  1943226;
   set newname for datafile  1 to
 "+UAT_DATA";
   set newname for datafile  2 to
 "+UAT_DATA";
   set newname for datafile  3 to
 "+UAT_DATA";
   set newname for datafile  4 to
 "+UAT_DATA";
   set newname for datafile  5 to
 "+UAT_DATA";
   set newname for datafile  6 to
 "+UAT_DATA";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-SEP-2016 12:25:30
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +UAT_DATA
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_LVL0_0mrf9oc7_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_LVL0_0mrf9oc7_1_1.bak tag=TAG20160908T120942
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +UAT_DATA
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_LVL0_0lrf9oc7_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_LVL0_0lrf9oc7_1_1.bak tag=TAG20160908T120942
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +UAT_DATA
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_LVL0_0orf9oc8_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_LVL0_0orf9oc8_1_1.bak tag=TAG20160908T120942
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +UAT_DATA
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_LVL0_0krf9oc7_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_LVL0_0krf9oc7_1_1.bak tag=TAG20160908T120942
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to +UAT_DATA
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_LVL0_0qrf9oc8_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_LVL0_0qrf9oc8_1_1.bak tag=TAG20160908T120942
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +UAT_DATA
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_LVL0_0nrf9oc7_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_LVL0_0nrf9oc7_1_1.bak tag=TAG20160908T120942
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 08-SEP-2016 12:28:25

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=922019305 file name=+UAT_DATA/UAT/DATAFILE/system.263.922019131
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=922019306 file name=+UAT_DATA/UAT/DATAFILE/sumit.262.922019197
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=922019306 file name=+UAT_DATA/UAT/DATAFILE/sysaux.257.922019261
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=922019306 file name=+UAT_DATA/UAT/DATAFILE/undotbs1.259.922019221
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=922019306 file name=+UAT_DATA/UAT/DATAFILE/example.261.922019225
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=922019306 file name=+UAT_DATA/UAT/DATAFILE/users.258.922019259

contents of Memory Script:
{
   set until scn  1943226;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-SEP-2016 12:28:26
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_AUX_DISK_1: reading from backup piece /data01/BACKUP_NODE2/orcl_arch_0urf9oe6_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data01/BACKUP_NODE2/orcl_arch_0urf9oe6_1_1.bak tag=TAG20160908T121045
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=+FRA/UAT/ARCHIVELOG/2016_09_08/thread_1_seq_13.256.922019317 thread=1 sequence=13
channel clone_default: deleting archived log(s)
archived log file name=+FRA/UAT/ARCHIVELOG/2016_09_08/thread_1_seq_13.256.922019317 RECID=1 STAMP=922019317
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-SEP-2016 12:28:39
Oracle instance started

Total System Global Area     515899392 bytes

Fixed Size                     2926224 bytes
Variable Size                436210032 bytes
Database Buffers              71303168 bytes
Redo Buffers                   5459968 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''UAT'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''UAT'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area     515899392 bytes

Fixed Size                     2926224 bytes
Variable Size                436210032 bytes
Database Buffers              71303168 bytes
Redo Buffers                   5459968 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UAT" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+UAT_DATA', '+UAT_DATA' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+UAT_DATA', '+UAT_DATA' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+UAT_DATA', '+UAT_DATA' ) SIZE 50 M  REUSE
 DATAFILE
  '+UAT_DATA/UAT/DATAFILE/system.263.922019131'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+UAT_DATA";
   switch clone tempfile all;
   catalog clone datafilecopy  "+UAT_DATA/UAT/DATAFILE/sumit.262.922019197",
 "+UAT_DATA/UAT/DATAFILE/sysaux.257.922019261",
 "+UAT_DATA/UAT/DATAFILE/undotbs1.259.922019221",
 "+UAT_DATA/UAT/DATAFILE/example.261.922019225",
 "+UAT_DATA/UAT/DATAFILE/users.258.922019259";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +UAT_DATA in control file

cataloged datafile copy
datafile copy file name=+UAT_DATA/UAT/DATAFILE/sumit.262.922019197 RECID=1 STAMP=922019343
cataloged datafile copy
datafile copy file name=+UAT_DATA/UAT/DATAFILE/sysaux.257.922019261 RECID=2 STAMP=922019343
cataloged datafile copy
datafile copy file name=+UAT_DATA/UAT/DATAFILE/undotbs1.259.922019221 RECID=3 STAMP=922019343
cataloged datafile copy
datafile copy file name=+UAT_DATA/UAT/DATAFILE/example.261.922019225 RECID=4 STAMP=922019343
cataloged datafile copy
datafile copy file name=+UAT_DATA/UAT/DATAFILE/users.258.922019259 RECID=5 STAMP=922019343

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=922019343 file name=+UAT_DATA/UAT/DATAFILE/sumit.262.922019197
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=922019343 file name=+UAT_DATA/UAT/DATAFILE/sysaux.257.922019261
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=922019343 file name=+UAT_DATA/UAT/DATAFILE/undotbs1.259.922019221
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=922019343 file name=+UAT_DATA/UAT/DATAFILE/example.261.922019225
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=922019343 file name=+UAT_DATA/UAT/DATAFILE/users.258.922019259

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 08-SEP-2016 12:29:49

RMAN Partial restore / Skip forever

RMAN> run
{

ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
ALLOCATE CHANNEL c5 DEVICE TYPE disk;


 set until time "to_date( '18-05-2015 13:37', 'DD-MM-RRRR HH24:MI')";

 set newname for datafile 1 to 'U:\ORCL\SYSTEM01.DBF';
 set newname for datafile 2 to 'U:\ORCL\UNDOTBS01.DBF';
 set newname for datafile 3 to 'U:\ORCL\SYSAUX01.DBF';
 set newname for datafile 230 to 'U:\ORCL\UNDOTBS02.DBF';
 set newname for datafile 211 to 'U:\ORCL\ORCLUNDOTS01.DBF';
 set newname for datafile 141 to 'U:\ORCL\ORCLLARGE.DBF';
 set newname for datafile 42 to 'U:\ORCL\ORCLINDEX01.DBF';
 set newname for datafile 43 to 'U:\ORCL\ORCLINDEX02.DBF';
 set newname for datafile 225 to 'U:\ORCL\ORCLINDEX03.DBF';
 set newname for datafile 227 to 'U:\ORCL\ORCLINDEX04.DBF';
 set newname for datafile 228 to 'U:\ORCL\ORCLINDEX05.DBF';

 restore tablespace system , sysaux , undotbs1 , ORCLUNDOTS , ORCLLARGE , ORCLINDEX ;
 switch datafile all;

 sql "alter database datafile 1,2,3,230,211,141,42,43,225,227,228 online";

 recover database skip forever tablespace ORAPP,ORARCH,ORLARGE4M,ORWORK ;

 release channel c2;
 release channel c3;
 release channel c4;
 release channel c5;
 }

RMAN Active Duplicate

[oracle@node1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-OCT-2016 09:41:13

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                03-OCT-2016 09:32:55
Uptime                    0 days 0 hr. 8 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/app/11.2.0.4/grid_11204/network/admin/listener.ora
Listener Log File         /data01/app/grid/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@node1 ~]$ clear
cd
[oracle@node1 ~]$ cat /data01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /data01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

[oracle@node1 ~]$ cat /data01/app/11.2.0.4/grid_11204/network/admin/listener.ora
# listener.ora Network Configuration File: /data01/app/11.2.0.4/grid_11204/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /data01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent



1) Add Static Listener Entry in UAT :

[grid@node2 ~]$ cat /data01/app/11.2.0/grid_11204/network/admin/listener.ora
# listener.ora Network Configuration File: /data01/app/11.2.0/grid_11204/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
(GLOBAL_DBNAME = UAT)
(ORACLE_HOME = /data01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = UAT)
            )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /data01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

2. Reload listener and confirm the UAT service in listener :

[grid@node2 ~]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-OCT-2016 10:08:23

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[grid@node2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-OCT-2016 10:08:32

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                03-OCT-2016 09:12:26
Uptime                    0 days 0 hr. 56 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/app/11.2.0/grid_11204/network/admin/listener.ora
Listener Log File         /data01/app/grid/diag/tnslsnr/node2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node2.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "UAT" has 1 instance(s).
  Instance "UAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[grid@node2 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-OCT-2016 10:09:27

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "UAT" has 1 instance(s).
  Instance "UAT", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


3) Add TNS entry for UAT in ORCL  ,
 
[oracle@node2 admin]$ cat tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

UAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = UAT)
    )
  )

[oracle@node2 admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 03-OCT-2016 10:11:55
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (40 msec)

[oracle@node2 admin]$ tnsping UAT

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 03-OCT-2016 10:11:58
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = UAT)))
OK (40 msec)


4) Copy Password File from ORCL to UAT :

[oracle@node1 dbs]$ scp orapworcl oracle@node2:$ORACLE_HOME/dbs/orapwUAT
orapworcl                                                                                                                            100% 1536     1.5KB/s   00:00  
[oracle@node1 dbs]$



5.) Verify TNS Connectivity to UAT from orcl :

  Make sure the servive name is listener is matching with the name of password file orapw$SERVICE_NAME  . It is case sensitive , otherwise you will get
  invalid login password error .

[oracle@node1 dbs]$ sqlplus sys/sys@UAT as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 3 10:21:37 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.
SQL>

6) Create Directory Structures for UAT

 Create directory to support audit_file_dest

 [oracle@node2 dbs]$ mkdir -p /data01/app/oracle/admin/UAT/adump

7) Start UAT (auxiliary) instance in NOMOUNT mode :

[oracle@node2 dbs]$ cat initUAT.ora
*.db_name='UAT'

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size    2251816 bytes
Variable Size  159384536 bytes
Database Buffers   50331648 bytes
Redo Buffers    5189632 bytes


[oracle@node2 dbs]$ export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"

[oracle@node2 dbs]$ rman target sys/sys@ORCL auxiliary sys/sys@UAT
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 3 13:04:52 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1451945373)
connected to auxiliary database: UAT (not mounted)

RMAN> set echo on


echo set on

RMAN> @rman_dup.cmd
@rman_dup.cmd
RMAN> run {
 allocate channel prm1 type disk ;
 allocate channel prm2 type disk ;
 allocate auxiliary channel stdby type disk ;
DUPLICATE TARGET DATABASE TO UAT FROM ACTIVE DATABASE
SPFILE
 PARAMETER_VALUE_CONVERT 'orcl','uat' , 'ORCL' , 'UAT'
 set control_files='+DATA','+DG2'
 set db_file_name_convert='+DATA','+DG2'
 set log_file_name_convert='+DATA','+DG3'
SET DIAGNOSTIC_DEST='/data01/app/oracle'
set audit_file_dest='/data01/app/oracle/admin/UAT/adump'
LOGFILE
 GROUP 1 ('+DG3','+DG2') SIZE 50M ,
 GROUP 2  ('+DG3','+DG2') SIZE 50M ,
 GROUP 3 ('+DG3','+DG2') SIZE 50M ;
 }

Or if you have backup location you can use below :

run {
DUPLICATE DATABASE TO UAT
SPFILE
SET CONTROL_FILES='+DG2'
SET DB_FILE_NAME_CONVERT='+DATA','+DG2'
SET LOG_FILE_NAME_CONVERT='+DATA','+DG3'
SET DB_CREATE_ONLINE_LOG_DEST_1='+DG3'
SET DB_CREATE_ONLINE_LOG_DEST_2='+DG3'
SET DB_RECOVERY_FILE_DEST='+DG3'
BACKUP LOCATION '/data01/BACKUP'
SET DIAGNOSTIC_DEST='/data01/app/oracle'
set audit_file_dest='/data01/app/oracle/admin/UAT/adump'
NOFILENAMECHECK;
}

Redo Failure Scenarios

Handling Redo Log Failure:

Determine the course of action and how to restore:

Yoy have experienced a problem with your online redo log files and need to determaine what shape they are in and what course of action to take.

Solution:

Follow these steps when dealing with online redo log failures:

1.Inspect the alert.log file to determine which redo log files have experienced a media failure.

2.Query v$log and v$logfile to determine the status of the log group and degree ogf multiplexing.

SELECT a.group#,a.thread#,a.status grp_status

From v$log a,

V$logfile b

Where a.group#=b.group#

Order by a.group#=b.group#

GROUP#	THREAD#	GRP_STATUS	MEMBER	STATUS
1	1	ACTIVE	/d1/rptprd01/logs01/redo01.log
1	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo01b.log
2	1	ACTIVE	/d1/rptprd01/logs01/redo02.log
2	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo02b.log
3	1	CURRENT	/d1/rptprd01/oracle/rptprd01/redologs/redo03b.log
3	1	CURRENT	/d1/rptprd01/logs01/redo03.log
4	1	INACTIVE	/d1/rptprd01/logs01/redo04.log
4	1	INACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo04b.log
5	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo05b.log
5	1	ACTIVE	/d1/rptprd01/logs01/redo05.log
6	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo06b.log
6	1	ACTIVE	/d1/rptprd01/logs01/redo06.log

3. Depending on the status of the log group use the below table to determine what action to take:

When diagnosing online redo log issues, the V$LOG and V$LOGFILE views are particularly helpful. You can query these views while the database is mounted or open.

Type of failure	Status of v$log	Action
One member failed in multiplexed group	N/A	Re-create member.
All members of a group	Inactive	Clear logfile.
All members of a group	Active	Attempt checkpoint, and if successful, clear logfile. If checkpoint is unsuccessful, perform incomplete recovery.
All members of a group	Current	Attempt to clear log, and if unsuccessful, perform incomplete recovery.

Useful Views Related to Online Redo Logs
V$LOG	Displays the online redo log group information stored in the control file.
V$LOGFILE	Displays online redo log file member information.
Status for Online Redo Log Groups in the V$LOG View
CURRENT	The log group that is currently being written to by the log writer.
ACTIVE	The log group is required for crash recovery and may or may not have been archived.
CLEARING	The log group is being cleared out by an alter database clear logfile command
CLEARING_CURRENT	The current log group is being cleared of a closed thread.
INACTIVE	The log group isn’t needed for crash recovery and may or may not have been archived.
UNUSED	The log group has never been written to; it was recently created.
Status for Online Redo Log File Members in the V$LOGFILE View
INVALID	The log file member is inaccessible, or it has been recently created.
DELETED	The log file member is no longer in use.
STALE	The log file member’s contents are not complete.
NULL	The log file member is being used by the database.

It’s important to differentiate between the STATUS column in V$LOG and the STATUS column in V$LOGFILE. The STATUS column in V$LOG reflects the status of the log group. The STATUS column in V$LOGFILE reports the status of the physical online redo log file member.

Restoring After Losing One Member of the Multiplexed Group

Problem

You are experiencing media failure with one member of a multiplexed online redo log group and need to restore the damaged online redo log file member.

Solution

If your online redo log file members are multiplexed, the log writer will continue to function as long as it can successfully write to one member of the current log group. If the problem is temporary, then as soon as the online redo log file becomes available, the log writer will start to write to the online redo log file as if there was never an issue. If the media failure is permanent (such as a bad disk), then you’ll need to replace the disk and drop and re-create the bad member to its original location. If you don’t have the option of replacing the bad disk, then you’ll need to drop the bad member and re-create it in an alternate location.

For permanent media failures, here are the instructions for dropping and re-creating one member of an online redo log group:

·         Identify the online redo log file experiencing media failure --- using alert log.

·          Ensure that the online redo log file is not part of the current online log group. (v$log & v$logfile) – If it is showing Current status , then use the alter system switchlogfile command to make the next group the current group.

·         Drop the damaged member.

SQL> alter database drop logfile member '<\directory\member>';

·         Add a new member to the group.

SQL> alter database add logfile member '<\new directory\member>' to group <group#>;

If an unused log file already happens to exist in the target location, you can use the reuse parameter to overwrite and reuse that log file. The log file must be the same size as the other log files in the group.

SQL> alter database add logfile member '\directory\member>' reuse to group <group#>;

Recovering After Loss of All Members of the INACTIVE Redo Log Group

Problem

You’re attempting to open your database and receive this message: Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01B.LOG'

The message indicates that two members of an online redo log group in your database have experienced a media failure. You wonder how you’re going to get your database open again.

Solution

To recover when you’ve lost all members of an inactive redo log group, perform the following steps:

·         Verify that all members of a group have been damaged.

·         Verify that the log group status is INACTIVE.

·         Re-create the log group with the clear logfile command.

·         If the re-created log group has not been archived, then immediately back up your database.

If all members of an online redo log group are damaged, you won’t be able to open your database. In this situation, Oracle will allow you to only mount your database. Inspect your alert.log file, and verify that all members of a redo log group are damaged.

You should see a message indicating that all members of an online redo log group are damaged and database cannot open:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01B.LOG'

ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-313 signalled during: alter database open...

Next, ensure that your database is in mount mode:

Next, run the following query to verify that the damaged log group is INACTIVE and determine whether it has been archived:

SQL> select group#, status, archived, thread#, sequence# from v$log;

If the status is INACTIVE, then this log group is no longer needed for crash recovery .Therefore, you can use the clear logfile command to re-create all members of a log group. The following example re-creates all log members of group 1:

SQL> alter database clear logfile group 1;

If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:

SQL> alter database clear unarchived logfile group 1;

If the cleared log group had not been previously archived, it’s critical that you immediately create a backup of your database

If the online redo log group has not been archived, then it may be required for media recovery. In this case, use the clear unarchived logfile command to re-create the logfile group members. Back up your database as soon as possible in this situation.

The unarchived log group may be needed for media recovery if the last database backups were taken before the redo information in the log was created. This means if you attempt to perform media recovery, you won’t be able to recover any information in the damaged log file or any transactions that were created after that log. If the clear logfile command does not succeed because of an I/O error and it’s a permanent problem, then you will need to consider dropping the log group and re-creating it in a different location.

You can drop a log group with the drop logfile group command:

SQL> alter database drop logfile group <group #>;

You can add a new log group with the add logfile group command:

SQL> alter database add logfile group <group_#> ('\directory\file') SIZE <bytes> K|M;

You can specify the size of the log file in bytes, kilobytes, or megabytes. The following example adds log group 4 with two members sized at 50MB:

SQL> alter database add logfile group 4 ('C:\ORADATA\ORCL\REDO04A.LOG','D:\ORADATA\ORCL\REDO04B.LOG') SIZE 50M;

Recovering After Loss of All Members of the ACTIVE Redo Log Group

Problem

All the members of an active online redo log group in your database have experienced media failure.

Solution

Perform the following steps when restoring an active online redo log group:

·         Verify the damage to the members.

·         Verify that the status is ACTIVE.

·         Attempt to issue a checkpoint. (alter system checkpoint;) If the checkpoint completes successfully, then the active log group should be marked as INACTIVE. A successful checkpoint ensures that all modified database buffers have been written to disk, and at that point, only transactions contained in the CURRENT online redo log will be required for crash recovery.,so you can clear the log group.

·         If the log group that was cleared was unarchived, back up your database immediately.

·         If the checkpoint is unsuccessful, then you will have to perform incomplete recovery

Verify the status of the log group to see whether it is now INACTIVE with this query:

SQL> select group#, status, archived, thread#, sequence# from v$log;

If the status is inactive and the log has been archived, you can use the clear logfile command to re-create the log group, as shown here:

SQL> alter database clear logfile group <group#>;

If the status is inactive and the log group has not been archived, then re-create it with the clear unarchived logfile command, as shown here:

SQL> alter database clear unarchived logfile group <group#>;

If the cleared log group had not been previously archived, it’s critical that you immediately create a backup of your database.

How It Works

An online redo log group with an ACTIVE status is still required for crash recovery. If all members of an active online redo log group experience media failure, then you must attempt to issue a checkpoint. If the checkpoint is successful, then you can clear the log group. If the checkpoint is unsuccessful, then you will have to perform an incomplete recovery.

If the checkpoint is successful and if the log group has not been archived, then the log may be required for media recovery. Back up your database as soon as possible in this situation. The unarchived log group may be needed for media recovery if the last database backups were taken before the redo information in the log was created. This means if you attempt to perform media recovery, you won’t be able to recover any information in the damaged log file or any transactions that were created after that log.

Recovering After Loss of All Members of the CURRENT Redo Log Group

Problem

All of the members of a current online redo log group in your database have experienced media failure.

Solution

Unfortunately, your alternatives are limited when you lose all members of a current online redo log group. Here are some possible options:

·         Perform an incomplete recovery up to the last good SCN.

·         If flashback is enabled, flash your database back to the last good SCN.

·         If you’re using Oracle Data Guard, fail over to your physical or logical standby database.

·         Contact Oracle Support for suggestions.

In preparation for an incomplete recovery, first determine the last good SCN by querying the FIRST_CHANGE# column from V$LOG. In this scenario, you’re missing only the current online redo logs. Therefore, you can perform an incomplete recovery up to, but not including, the FIRST_CHANGE# SCN of the current online redo log.

SQL> shutdown immediate;

SQL> startup mount;

SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;

GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#

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

1            INACTIVE  YES  1                 50                  1800550

2            INACTIVE  YES  1                 49                  1800468

3            CURRENT  NO  1                  51                  1800573

In this case, you can restore and recover up to, but not including, SCN 1800573. Here’s how you’d do that:

RMAN> restore database until scn 1800573;

RMAN> recover database until scn 1800573;

RMAN> alter database open resetlogs;

How It Works

Losing all members of your current online redo log group is arguably the worst thing that can happen to your database. If you experience media failure with all members of the current online redo group, then you will lose any transactions contained in those logs. In this situation, you will have to perform incomplete recovery before you can open your database.