Monday, April 23, 2018

RMAN list backup of database completed between dates


RMAN> list backup of database completed between "to_date('19/03/2018','DD/MM/YYYY')" and "to_date('21/03/2018','DD/MM/YYYY')";


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31418348 Incr 1  23.50M     SBT_TAPE    00:01:25     19-MAR-18     
        BP Key: 31418353   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20180319T083057
        Handle: 1062101_HDB01_bbsu5kub_1_1   Media: V_23423423_4082593
  List of Datafiles in backup set 31418348
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31435758 Incr 1  70.75M     SBT_TAPE    00:01:50     19-MAR-18     
        BP Key: 31435763   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20180319T203102
        Handle: 1062535_HDB01_bjsu6v4u_1_1   Media: V_23423423_4084511
  List of Datafiles in backup set 31435758
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31450103 Incr 0  11.90G     SBT_TAPE    00:11:13     20-MAR-18     
        BP Key: 31450108   Status: AVAILABLE  Compressed: NO  Tag: TAG20180320T063112
        Handle: 1062980_HDB01_brsu829m_1_1   Media: V_2371232_4086316
  List of Datafiles in backup set 31450103
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31451918 Incr 1  7.25M      SBT_TAPE    00:01:23     20-MAR-18     
        BP Key: 31451923   Status: AVAILABLE  Compressed: NO  Tag: TAG20180320T083049
        Handle: 1063057_HDB01_c3su89aa_1_1   Media: V_2371319_4086641
  List of Datafiles in backup set 31451918
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31467652 Incr 1  22.75M     SBT_TAPE    00:01:21     20-MAR-18     
        BP Key: 31467657   Status: AVAILABLE  Compressed: NO  Tag: TAG20180320T203055
        Handle: 1063477_HDB01_cbsu9jgh_1_1   Media: V_2370174_4088496
  List of Datafiles in backup set 31467652
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/ab.dbf

RMAN>

Posted by Harvey 0 comments

Thursday, April 19, 2018

RMAN using TAG to recover database in RAC from backup

I want to recover database from backup using the TAG.

 Please note that if use the duplicate command below then it will give you an error 

DUPLICATE DATABASE prd1db TO tes1db from tag 'TAG20180319T142455' NOFILENAMECHECK;


So what can be done?

The solution is find the scn number attached to controlfile under RMAN backups and us it to create the duplicate database as:

DUPLICATE DATABASE prd1db TO tes1db until SCN 2816323557926 NOFILENAMECHECK;



log in to RMAN using the recovery catalog after setting the env for source database in our case it is prd1db.

$ORACLE_HOME/bin/rman target / catalog rman@rcat

RMAN> list backup;


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


Please note that above command will list all the backups for the database. You can try getting the list between time stamps if you know.

The output of the above command will be something like 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431944 Incr 0  66.38G     SBT_TAPE    00:07:35     19-MAR-18      
        BP Key: 31431951   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455
        Handle: 1062193_TES1DB_66su69lo_1_1   Media: V_2370411_4083354
  List of Datafiles in backup set 31431944
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1362.971174187
  3    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/sysaux.1307.971174905
  6    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/users.1282.971175289
  7    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1361.971174187
  10   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1360.971174187
  13   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1359.971174189
  16   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1358.971174189
  19   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1357.971174189
  22   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1356.971174189
  25   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1355.971174189
  28   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/enterprise.1288.971175261
  31   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1338.971174499
  34   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1337.971174499
  39   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1291.971175153
  41   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/refmu.1284.971175261
  42   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1336.971174499
  45   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1335.971174499
  48   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1334.971174499
  51   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1333.971174501
  54   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/rds.1283.971175261
  57   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1332.971174501
  60   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1331.971174501
  63   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1314.971174903
  66   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts.1286.971175261
  69   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_mds.1287.971175261
  70   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_attrstore.1285.971175261
  72   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1313.971174905
  75   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1312.971174905
  78   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1311.971174905
  81   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1310.971174905
  85   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1309.971174905
  88   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1308.971174905

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431945 Incr 0  65.54G     SBT_TAPE    00:07:43     19-MAR-18      
        BP Key: 31431952   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455
        Handle: 1062193_TES1DB_65su69lo_1_1   Media: V_2368252_4083353
  List of Datafiles in backup set 31431945
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4    0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/undotbs1.1353.971174219
  9    0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1350.971174219
  12   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1348.971174219
  15   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1346.971174219
  18   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1344.971174219
  21   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1342.971174219
  24   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1340.971174219
  30   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1339.971174219
  33   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1323.971174569
  36   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1293.971174927
  37   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1292.971175151
  38   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1322.971174569
  44   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1321.971174571
  47   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1320.971174571
  50   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1319.971174571
  56   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1318.971174571
  59   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1317.971174571
  62   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1315.971174815
  71   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1299.971174927
  74   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1298.971174927
  77   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1297.971174927
  80   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1296.971174927
  83   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1295.971174927
  87   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1294.971174927
  89   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1290.971175175

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431946 Incr 0  35.00M     SBT_TAPE    00:00:01     19-MAR-18      
        BP Key: 31431953   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455
        Handle: 1062193_TES1DB_68su6a4b_1_1   Media: V_2370411_4083355
  Control File Included: Ckp SCN: 2816323557926   Ckp time: 19-MAR-18

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431947 Incr 0  1.00M      SBT_TAPE    00:00:00     19-MAR-18      
        BP Key: 31431954   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455
        Handle: 1062193_TES1DB_69su6a4d_1_1   Media: V_2370411_4083357
  SPFILE Included: Modification time: 19-MAR-18
  SPFILE db_unique_name: TES1DBP

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431948 Incr 0  67.01G     SBT_TAPE    00:06:50     19-MAR-18      
        BP Key: 31431955   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455
        Handle: 1062193_TES1DB_67su6a4a_1_1   Media: V_2368252_4083356
  List of Datafiles in backup set 31431948
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/system.1300.971174923
  5    0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/undotbs2.1354.971174219
  8    0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1352.971174219
  11   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1351.971174219
  14   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1349.971174219
  17   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1347.971174219
  20   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1345.971174219
  23   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1343.971174219
  26   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/const.1280.971175313
  27   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/cdrnps.1281.971175291
  29   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1341.971174219
  32   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1330.971174557
  35   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1329.971174557
  40   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/dart_admin.1275.971175321
  43   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1328.971174557
  46   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1327.971174557
  49   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1326.971174557
  52   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1325.971174559
  53   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_disco_ptm5_cache.1274.971175339
  55   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_disco_pstore.1279.971175321
  58   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1324.971174559
  61   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1316.971174805
  64   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1306.971174923
  65   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_default.1277.971175321
  67   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_battrstore.1276.971175321
  68   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_ct_store.1278.971175321
  73   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1305.971174923
  76   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1304.971174923
  79   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1303.971174923
  82   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1302.971174923
  84   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1289.971175203
  86   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1301.971174923


Life is not easy, in my case the source database had many incarnations so best find the incarnation you want to use

RMAN> list incarnation of database prd1db;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
70111   70159   PRD1DB  2306850230       PARENT  1          17-SEP-11
70111   70112   PRD1DB  2306850230       PARENT  995548     18-FEB-13
70111   72689   PRD1DB  2306850230       PARENT  1250093    21-FEB-13
70111   388729  PRD1DB  2306850230       PARENT  2790127477836 04-JUN-13
70111   390747  PRD1DB  2306850230       PARENT  2790137436622 05-JUN-13
70111   445093  PRD1DB  2306850230       PARENT  2790285891826 19-JUN-13
70111   447758  PRD1DB  2306850230       CURRENT 2790286767652 19-JUN-13
3756286 3756430 PRD1DB  2368813298       PARENT  22644025   02-OCT-13
3756286 3756287 PRD1DB  2368813298       CURRENT 2797234764372 22-JAN-15
7714199 7714327 PRD1DB  2397641027       PARENT  22644025   02-OCT-13
7714199 7714200 PRD1DB  2397641027       CURRENT 2801717021314 15-DEC-15
31308663 31308850 PRD1DB  2469971143       PARENT  1594143    11-MAY-16
31308663 31308664 PRD1DB  2469971143       CURRENT 2816323493241 15-MAR-18
31418462 31418649 PRD1DB  2470302757       PARENT  1594143    11-MAY-16
31418462 31418463 PRD1DB  2470302757       CURRENT 2816323493241 19-MAR-18

RMAN>

things for out interest are in red above. In nutshell my SCN number is 2816323557926 and DBID for the source database is 2470302757 

Let's do the duplicate database. Make sure /etc/oratab has the tes1dbp1 

Create the init_aux.ora file on the server where you want to create the new database tes1db


-- Create init parameter file to start the dupication
vi /u01/app/oracle/product/12.1.0.2/dbs/init_aux.ora 

*.audit_file_dest='/u01/app/oracle/admin/tes1dbp/adump' 
*.compatible='12.1.0.2.0' 
*.db_create_file_dest='+DATA' 
*.db_domain='example.com'
*.db_name='tes1db'
*.db_recovery_file_dest='+FRA' 
*.db_recovery_file_dest_size=180G 
*.db_unique_name='tes1dbp'
*.standby_file_management='AUTO'
*.sga_target=2399141888
*.job_queue_processes=0



. oraenv
tes1dbp1


cd /u01/app/oracle/product/12.1.0.2/dbs/

sqlplus "/as sysdba" 
startup nomount pfile='?/dbs/init_aux.ora';
exit;

create the RMAN session (ofcourse the tnsnames.ora file and listener.ora files are updated and done lsnrctl reload listener as grid)

rman
connect auxiliary sys/<sys password>@tes1dbp_DGMGRL
connect catalog rman/<rman password>@rcat

on RMAN prompt run the following command 

run {
set dbid 2470302757
allocate auxiliary channel ch1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
allocate auxiliary channel ch2 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
allocate auxiliary channel ch3 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
allocate auxiliary channel ch4 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
DUPLICATE DATABASE prd1db TO tes1db until SCN 2816323557926 NOFILENAMECHECK;
}


Now the above command will succeed and the prd1db will be duplicated to tes1db up to the SCN 2816323557926  and that SCN is for TAG TAG20180319T142455


Posted by Harvey 0 comments

Tuesday, April 10, 2018

ORA-00851: SGA_MAX_SIZE 15032385536 cannot be set to more than MEMORY_TARGET 1073741824.

Trying to start one of the RAC nodes and getting the error message that MEMORY_TARGET is smaller than SGA_MAX_SIZE

in my case I had disabled the AMM and i should not be using memory_target parameter. To get it fixed what I did was.

 Error message:

[oracle@rac01 (dev1tesp1) dbs]$ srvctl start database -d dev1tesp -i dev1tesp1
PRKO-2002 : Invalid command line option: -i
[oracle@rac01 (dev1tesp1) dbs]$ srvctl start instance -d dev1tesp -i dev1tesp1
PRCR-1013 : Failed to start resource ora.dev1tesp.db
PRCR-1064 : Failed to start resource ora.dev1tesp.db on node rac01
CRS-5017: The resource action "ora.dev1tesp.db start" encountered the following error:
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 15032385536 cannot be set to more than MEMORY_TARGET 1073741824.
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac01/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.dev1tesp.db' on 'rac01' failed
[oracle@rac01 (dev1tesp1) dbs]$


 Created the pfile from spfile 

SQL> create pfile='/u01/app/oracle/product/12.1.0.2/dbs/initdev1tesp1_change.ora' from spfile='+DATA/dev1tesp/spfiledev1tesp.ora';

File created.

SQL>


Then remove the parameter 
*.memory_target=1073741824


Try to startup database with pfile as there is no memory_target values set in that pfile

SQL> startup pfile='/u01/app/oracle/product/12.1.0.2/dbs/initdev1tesp1_change.ora';
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                  7660464 bytes
Variable Size            6006246480 bytes
Database Buffers         8992587776 bytes
Redo Buffers               25890816 bytes
Database mounted.
Database opened.
SQL>

re-create spfile

SQL> create spfile='+DATA/dev1tesp/spfiledev1tesp.ora' from pfile='/u01/app/oracle/product/12.1.0.2/dbs/initdev1tesp1_change.ora';

File created.

SQL>


Checks status of database 

[oracle@rac01 (dev1tesp1) dbs]$ srvctl status database -d dev1tesp
Instance dev1tesp1 is running on node rac01
Instance dev1tesp2 is running on node racdev01n02
[oracle@rac01 (dev1tesp1) dbs]$

[oracle@rac01 (dev1tesp1) dbs]$ srvctl status service -d dev1tesp
Service dev1tes_ha is running on instance(s) dev1tesp1,dev1tesp2
[oracle@rac01 (dev1tesp1) dbs]$


Stop the instance and restart it 

[oracle@rac01 (dev1tesp1) dbs]$ srvctl stop service -d dev1tesp -s dev1tes_ha -i dev1tesp1
[oracle@rac01 (dev1tesp1) dbs]$ srvctl status service -d dev1tesp
Service dev1tes_ha is running on instance(s) dev1tesp2
[oracle@rac01 (dev1tesp1) dbs]$


[oracle@rac01 (dev1tesp1) dbs]$ srvctl stop instance -d dev1tesp -i dev1tesp1
[oracle@rac01 (dev1tesp1) dbs]$
[oracle@rac01 (dev1tesp1) dbs]$
[oracle@rac01 (dev1tesp1) dbs]$ srvctl start instance -d dev1tesp -i dev1tesp1
[oracle@rac01 (dev1tesp1) dbs]$ srvctl status database -d dev1tesp
Instance dev1tesp1 is running on node rac01
Instance dev1tesp2 is running on node racdev01n02
[oracle@rac01 (dev1tesp1) dbs]$ srvctl status service -d dev1tesp
Service dev1tes_ha is running on instance(s) dev1tesp1,dev1tesp2
[oracle@rac01 (dev1tesp1) dbs]$






Posted by Harvey 0 comments

Monday, April 9, 2018

Puppet learning 0curl: (7) Failed connect to learning.puppetlabs.vm:8140; Connection refused

Trying the very first few commands from the tutorial of puppet and getting the error:

learning@hello: $ curl -k https://learning.puppetlabs.vm:8140/packages/current/install.bash | sudo bash                                                                                                                                   
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0curl: (7) Failed connect to learning.puppetlabs.vm:8140; Connection refused
[~]
learning@hello: $


After searching some time over the net the solution is restart pe-puppetserver

root@learning: # service pe-puppetserver restart
Redirecting to /bin/systemctl restart pe-puppetserver.service
[~]
root@learning: #



 Then run the command again and issue is all sorted now.


learning@hello: $ curl -k https://learning.puppetlabs.vm:8140/packages/current/install.bash | sudo bash
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 25790  100 25790    0     0   149k      0 --:--:-- --:--:-- --:--:--  149k
Loaded plugins: fastestmirror, ovl
Repository 'local' is missing name in configuration, using id
Cleaning repos: pe_repo
Cleaning up everything
Maybe you want: rm -rf /var/cache/yum, to also free up space taken by orphaned data from disabled or removed repos
Cleaning up list of fastest mirrors
.
.
.
.
.
.
  ensure => 'link',
  target => '/opt/puppetlabs/puppet/bin/puppet',
}
Notice: /File[/usr/local/bin/pe-man]/ensure: created
file { '/usr/local/bin/pe-man':
  ensure => 'link',
  target => '/opt/puppetlabs/puppet/bin/pe-man',
}
Notice: /File[/usr/local/bin/hiera]/ensure: created
file { '/usr/local/bin/hiera':
  ensure => 'link',
  target => '/opt/puppetlabs/puppet/bin/hiera',
}
[~]
learning@hello: $

Posted by Harvey 1 comments
Subscribe to: Posts (Atom)