Database proactive patching step (PSU) 26635880 for 12.1.0.2 October 2017

Posted on November 2, 2017April 26, 2018 by debasis maity

PSU patching for 12.1.0.2:-

First Identify the patch to be downloaded:-

Oracle 12.1.0.2 proactive DB patching . Patch number:-26635880.We needed to apply in RHEL 7 64-bit.

Please download this patch from Oracle support.

We need to follow below link for details of steps:-

https://updates.oracle.com/Orion/Services/download?type=readme&aru=21529863

We need to download following OPATCH utility as well as this patch requires version12.2.0.1.7 .

https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?release=600000000063735&plat_lang=233P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=21286070&patch_num=6880880&patch_num_id=2875132&default_release=600000000063735&default_plat_lang=226P&default_compatible_with=&patch_password=&orderby=&direction=&no_header=0&sortcolpressed=&tab_number=

Unzip the patch in mount point accessible by Grid/Oracle user.

For example:-

cd /oraclecd/

unzip p26635880_121020_<platform>.zip

chmod -R 777 26635880

chown -R grid:oinstall 26635880

If this change permission is not done,you may get error mentioned in section 10.A

OPatch Installation

Copy the opatch binary using root user to $ORACLE_HOME and $GRID_HOME.

Then please keep backup of old OPatch directory.

Unzip the binary in the $ORACLE_HOME and $GRID_HOME.

Change owner of the folder OPatch.

chown -R oracle:oinstall $ORACLE_HOME/OPatch

chown -R oracle:oinstall $GRID_HOME/OPatch

Please check lsinv command is working fine and note opatch version updated.

Run OPatch Conflict Check

For Oracle user:-

For Database home, as home user:

% $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/26635880/26717470

% $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/26635880/26392192

For Grid user:-

For Grid Infrastructure Home, as home user:

% $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/26635880/26717470

% $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/26635880/26392192

% $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/26635880/26392164

% $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/26635880/21436941

List of patch to be applied for GI Home:-

For Grid Infrastructure Home, as home user:

Create file /tmp/patch_list_gihome.txt with the following content:

% cat /tmp/patch_list_gihome.txt

<UNZIPPED_PATCH_LOCATION>/26635880/21436941

<UNZIPPED_PATCH_LOCATION>/26635880/26392164

<UNZIPPED_PATCH_LOCATION>/26635880/26392192

<UNZIPPED_PATCH_LOCATION>/26635880/26717470

List of patch to be applied for ORACLE Home:-

For Database home, as home user:

Create file /tmp/patch_list_dbhome.txtwith the following content:

2. % cat /tmp/patch_list_dbhome.txt3.

<UNZIPPED_PATCH_LOCATION>/26635880/26392192

<UNZIPPED_PATCH_LOCATION>/26635880/26717470

Check disk space for GI. It requires 14GB free space for GI Home .

Check disk space for ORACLE. It requires 8GB free space for ORACLE Home .

Please check status before applying patch

[grid@xxx~]$ crsctl stat res -t

Now login to root user and perform patching using following steps for GI Home:-

[root@xxx oraclecd]# export ORACLE_HOME=/u01/app/product/12.1.0/grid

[root@xxx oraclecd]# export ORACLE_SID=+ASM1

[root@xxx oraclecd]# echo $PATH

/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

[root@xxx oraclecd]# export PATH=/u01/app/product/12.1.0/grid/bin/:/u01/app/product/12.1.0/grid:/u01/app/product/12.1.0/grid/OPatch:$PATH

[root@xxx oraclecd]# echo $PATH

/u01/app/product/12.1.0/grid/bin/:/u01/app/product/12.1.0/grid:/u01/app/product/12.1.0/grid/OPatch:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

To patch only the GI home:

# opatchauto apply /oraclecd/p26635880/26635880/  -oh /u01/app/product/12.1.0/grid

Patch log : /u01/app/product/12.1.0/grid/cfgtoollogs/opatchauto/opatchauto2017-11-01_07-45-35AM.log

Error while patching:

After fixing permission issue in Patch Source directory. Resume Patching.

Please check the status of applied patch using below step:-

[grid@xxx ~]$ cd $ORACLE_HOME/OPatch

[grid@xxx OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.8

Oracle Home : /u01/app/product/12.1.0/grid

Central Inventory : /u01/app/oraInventory

from : /u01/app/product/12.1.0/grid/oraInst.loc

OPatch version : 12.2.0.1.8

OUI version : 12.1.0.2.0

Log file location : /u01/app/product/12.1.0/grid/cfgtoollogs/opatch/opatch2017-11-01_08-19-10AM_1.log

Lsinventory Output file location : /u01/app/product/12.1.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2017-11-01_08-19-10AM.txt

——————————————————————————–

Local Machine Information::

Hostname: xxx.tdeprdpe.internal

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 12c 12.1.0.2.0

There are 1 products installed in this Oracle Home.

Interim patches (4) :

Patch 26717470 : applied on Wed Nov 01 08:06:04 PET 2017

Unique Patch ID: 21611145

Patch description: “Database Bundle Patch : 12.1.0.2.171017 (26717470)”

Created on 4 Oct 2017, 23:16:37 hrs PST8PDT

Sub-patch 26609798; “DATABASE BUNDLE PATCH: 12.1.0.2.170814 (26609798)”

Sub-patch 25869760; “DATABASE BUNDLE PATCH: 12.1.0.2.170718 (25869760)”

Sub-patch 25397136; “DATABASE BUNDLE PATCH: 12.1.0.2.170418 (25397136)”

Sub-patch 24732088; “DATABASE BUNDLE PATCH: 12.1.0.2.170117 (24732088)”

Sub-patch 24340679; “DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)”

Sub-patch 23144544; “DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)”

Sub-patch 22806133; “DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133)”

Sub-patch 21949015; “DATABASE BUNDLE PATCH: 12.1.0.2.160119 (21949015)”

Sub-patch 21694919; “DATABASE BUNDLE PATCH: 12.1.0.2.13 (21694919)”

Sub-patch 21527488; “DATABASE BUNDLE PATCH: 12.1.0.2.12 (21527488)”

Sub-patch 21359749; “DATABASE BUNDLE PATCH: 12.1.0.2.11 (21359749)”

Sub-patch 21125181; “DATABASE BUNDLE PATCH: 12.1.0.2.10 (21125181)”

Sub-patch 20950328; “DATABASE BUNDLE PATCH: 12.1.0.2.9 (20950328)”

Sub-patch 20788771; “DATABASE BUNDLE PATCH: 12.1.0.2.8 (20788771)”

Sub-patch 20594149; “DATABASE BUNDLE PATCH: 12.1.0.2.7 (20594149)”

Sub-patch 20415006; “DATABASE BUNDLE PATCH: 12.1.0.2.6 (20415006)”

Sub-patch 20243804; “DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804)”

Bugs fixed:

17551261, 19215966, 19215968, 21293693, 18257176, 19410056, 19791273

19441649, 20131617, 21542577, 19238590, 21281532, 24577566, 19410553

19662976, 25427662, 21330561, 20717359, 19512155, 23209741, 20569094

22380132, 21575362, 21547079, 17258582, 22507210, 20994184, 16010876

19183813, 18829196, 23310662, 19358317, 19376952, 20593729, 25211628

19906611, 23521523, 14178080, 18940497, 22159570, 21877749, 25119290

19176326, 19035573, 20011897, 25995938, 19542737, 19982584, 20904530

19875836, 19347744, 21072646, 22507234, 26261327, 21445204, 19183834

19468991, 20824172, 18418934, 22514212, 19310488, 18390735, 22238921

19146778, 18369237, 21365452, 19068610, 20984916, 19348649, 16777441

20048359, 20952633, 18877780, 19632217, 20603378, 23089357, 19794897

21834568, 21093564, 20658124, 21387964, 19970163, 19563433, 20627866

20844426, 19811763, 23075177, 21188532, 18791688, 21442094, 20063894

18953292, 20882568, 20936905, 21218243, 20235511, 21220620, 20801783

18938517, 19243313, 21517261, 19271894, 21153266, 19409212, 20657441

18996066, 25410017, 18740837, 19662635, 18440095, 19021645, 22194927

19065556, 21651772, 18996056, 20938170, 24350831, 20328205, 25377044

20313052, 19693124, 19446007, 18952989, 19029674, 19043177, 18999568

21629064, 18760601, 24386767, 18520414, 20394750, 20732272, 20536378

21320182, 21305617, 25643931, 20024112, 20074391, 19995675, 21951106

19191274, 26430737, 14643995, 20794951, 22540743, 20832474, 19824871

20355900, 19686260, 22729345, 18900917, 19174430, 22068305, 25654936

19175732, 25362317, 19195409, 22168163, 18764866, 18794814, 20106567

19144692, 20447445, 18856999, 21793604, 19899833, 17890099, 19910052

18990023, 21300341, 26096382, 22734038, 20897759, 19482919, 25480641

20637701, 19052488, 25606091, 19562381, 25539063, 18497527, 20328248

23194817, 22220568, 22865673, 23642667, 22816287, 20347562, 23262847

18764841, 20595686, 22664402, 20313024, 21641760, 21899588, 21476308

20754928, 20354411, 24297898, 21297872, 24308635, 20390564, 18430870

19240456, 20171986, 22815955, 22819742, 22492474, 20881450, 19289499

21273804, 23125826, 20424183, 20591915, 22062026, 19647503, 21180173

19787571, 19143193, 19440586, 18974476, 24825843, 18895170, 20708701

24674955, 17027695, 24326846, 20716291, 21121136, 20328279, 19393542

21847223, 25823754, 21174395, 19065222, 19793397, 19440520, 19248799

22053453, 20165291, 20663222, 21532755, 20232621, 21665955, 20070112

24385983, 20711718, 22527857, 18911956, 26112084, 19664340, 25034396

19523462, 19773811, 21142837, 19188683, 18594569, 22528741, 19288324

20003240, 19212370, 20009569, 21774511, 19989904, 19248279, 26385473

21286665, 19315691, 21058333, 19547013, 23072137, 18816732, 20960011

19768226, 17867700, 20023092, 19541796, 23528412, 20201112, 19532017

20300454, 19818513, 19879445, 20040448, 25423453, 19495405, 21813529

19682726, 22068270, 23197103, 8899370, 19195895, 20043616, 20857041

19824028, 25330273, 22518491, 21378561, 23718355, 19520602, 19174521

19440105, 20093776, 19647567, 21610048, 19019270, 21246723, 20803014

21308727, 20074466, 23514911, 23026585, 19627012, 19066316, 22087683

14283239, 20001362, 19701015, 21422580, 19213447, 18610915, 20697178

20494924, 20653340, 18306996, 19151370, 19524158, 20513399, 17285555

25091141, 18856106, 19680898, 19886365, 20146809, 19163723, 19383839

22458049, 19607219, 21430035, 19475971, 22353199, 19907473, 18897633

22916353, 21136435, 19028800, 21555019, 21380789, 19077215, 19884571

19540171, 23533524, 22365117, 18647293, 19689979, 17409174, 22836801

14705949, 24334708, 19609438, 20508559, 22146062, 20318889, 20287585

20591183, 19480309, 19485593, 21625179, 13683743, 23499160, 21632821

22695831, 19140924, 19587324, 24316947, 19578350, 22978673, 19637186

20950535, 23749454, 19854717, 20808265, 19054077, 19988852, 21971099

22894949, 20978173, 19024388, 22140382, 19624452, 24421668, 25489607

20228468, 25484507, 20421900, 22074084, 22520320, 22734547, 19978542

20243268, 21915719, 21534893, 20879709, 21516425, 20677396, 20451844

19990037, 20703629, 19811709, 18909599, 22460450, 25600421, 19526750

18642040, 18250893, 19230595, 17670355, 19576559, 17414008, 20441797

20704450, 19705781, 22338374, 25612095, 21897746, 19157754, 19207117

18663279, 24437510, 20320834, 19494323, 19230581, 20673810, 15894842

18893947, 19168395, 19350952, 18417036, 23084507, 20028247, 20919320

22503297, 20474192, 19161450, 18542562, 20920911, 18122373, 20808220

19957298, 20667450, 19606174, 18436647, 25110233, 19597583, 18889867

19232702, 19723273, 21099555, 21174504, 22175564, 20577490, 20842388

18727933, 20117253, 20830459, 19784751, 20951038, 22165897, 20545234

19908836, 20739811, 20804108, 21848185, 19187708, 17994602, 22882992

20082778, 20563314, 20387265, 18739883, 19149990, 21263635, 20534663

18886413, 26762083, 24719736, 22160989, 20010996, 23338911, 19703301

20717091, 18007682, 20949664, 20216232, 18665091, 24285405, 25655390

20267166, 20005796, 23130347, 24844019, 18797519, 22148226, 19173409

19679504, 21196809, 19670108, 19649152, 19638998, 19404068, 22496904

20815650, 20087383, 18964978, 19512276, 20717081, 19819141, 19176223

19152115, 20134339, 20213973, 20144042, 19341709, 21322887, 20425790

19170047, 21329301, 19468890, 16739157, 19333670, 19883092, 23148260

19548688, 20075921, 19347898, 22936751, 19658708, 19591608, 22501616

19051985, 20276452, 19563300, 26095405, 21795111, 22066671, 22380919

19971335, 19176241, 20746251, 21896069, 20898391, 19185876, 21893830

19433200, 20281121, 21281607, 6599380, 25733479, 19577410, 21117640

22092979, 22761738, 19566974, 19653712, 19277787, 18890834, 25721405

19330795, 19394032, 20026982, 19509982, 22675506, 20596234, 19631051

20938019, 20392464, 19303936, 21899224, 20900013, 21479753, 18964939

19430401, 22806698, 21394225, 24521608, 26198926, 19585708, 20528052

19076106, 22717225, 18701619, 16087033, 20212067, 18897147, 19583624

22268833, 24942924, 23235386, 19148478, 20887355, 25335790, 19147016

20679868, 20144308, 25056052, 16870214, 23539020, 19835133, 20005313

19871910, 22652097, 22199829, 20019581, 25490238, 23326243, 21660388

19730508, 22347934, 19327391, 20250147, 20381311, 22653923, 20001466

18307021, 23533807, 20356733, 18508040, 19566012, 23115408, 19065677

19271943, 20148087, 21960504, 19264172, 18022774, 18967382, 19029552

18419520, 19900800, 18678808, 20310880, 22086638, 20468223, 19136936

18549042, 18424767, 21132297, 19973780, 21419192, 20471920, 21424824

21279842, 18127250, 23096938, 18990693, 19131607, 19914604, 24812585

21554789, 21241052, 21668627, 20794034, 22528955, 19883038, 23260854

22837524, 20952966, 23132311, 19201867, 23065323, 19294529, 22022760

25161298, 25237577, 23294548, 19777862, 19698567, 18633374, 19174942

20126138, 20130196, 19680455, 21079172, 20781373, 19032777, 19058490

19399918, 22330282, 20540694, 24718260, 18051556, 20434597, 21136799

19317646, 19212166, 19166028, 23665623, 19284031, 20173897, 19536132

19649591, 22220445, 19334242, 19345973, 20401975, 19537328, 14165431

20927642, 19189762, 19805359, 19155797, 21099502, 22374347, 17210525

20061399, 24402551, 20334691, 22762046, 22075064, 20801390, 18798250

22256431, 20052269, 21345404, 22454326, 24944847, 19331728, 20096277

20925795, 18753782, 19233039, 19018206, 20588502, 22662332, 21911701

18679653, 23501901, 19859472, 21917884, 19189317, 19644859, 19390567

26546754, 20586677, 26308650, 18778801, 19130152, 20927568, 18607546

22129399, 19277336, 22077191, 19676905, 20165574, 20020274, 20734332

20922010, 20217875, 22153958, 20123899, 19222052, 19318409, 18881811

20246939, 19188385, 19250184, 26555609, 21047407, 19154375, 21977392

19708342, 19382851, 19676012, 20081206, 20217801, 19851608, 24299028

21097043, 19354542, 21665897, 19148107, 19982314, 20609727, 18300729

20177577, 19882506, 19958830, 19175543, 20321129, 18533573, 26446098

23492665, 21549434, 20617820, 19272047, 24413809, 23480973, 21626377

20122715, 18110491, 22366322, 20656178, 18967466, 22472825, 19363645

20070689, 20088905, 20880215, 19534363, 15931756, 19478966, 19162085

21068213, 19475148, 20194605, 19513406, 17249267, 20559144, 19563715

20723042, 21059919, 20185943, 20451063, 21903230, 19298213, 19723336

19146626, 19604659, 19150533, 19308965, 18803726, 23068169, 18783224

20241212, 21526048, 19180770, 19191037, 19197175, 20128644, 24573817

19590877, 24835538, 23324000, 19004100, 21492036, 22258300, 24378358

20445031, 22177861, 19067244, 19953052, 23053606, 19682278, 18622436

21568648, 20331945, 21171382, 19461428, 22030856, 19825394, 24835919

18749211, 19727038, 21550777, 19056729, 19539945, 22254139, 19899544

20069788, 21342045, 23736203, 19258504, 22599147, 19881439, 21391017

19817386, 21644640, 19468347, 13846337, 19439759, 22452178, 20230090

23229229, 20064203, 21306811, 19698040, 23134970, 19291380, 25735246

25600342, 19561643, 24438281, 22897344, 19313563, 20831538, 18472224

19197132, 18796543, 20217416, 16756406, 25558986, 19989009, 19932939

22075019, 19270287, 21780146, 25483815, 19990543, 23855307, 20214168

14372604, 20209481, 13640676, 18705806, 23134070, 22264489, 13498243

23314180, 25142953, 20202880, 24308349, 19894622, 20432873, 19131239

24797119, 20899461, 21429602, 21387128, 19526873, 22624709, 24690216

19023822, 24336249, 19178851, 19124589, 19585773, 18689530, 22569317

22083366, 23248603, 22233968, 19243521, 19134173, 14254610, 20031873

20322560, 17551063, 13787015, 25409186, 18463035, 19001390, 21459225

24739928, 20917841, 23037377, 20227126, 20724902, 18866977, 18948177

19359219, 18942489, 20413820, 19189240, 20243804, 22077553, 21087813

19477176, 21106027, 19074147, 23035249, 18411216, 22258530, 21330334

23323592, 18966843, 25861398, 19212061, 19264279, 19454986, 22094845

22094844, 20562898, 20250735, 19364018, 18859658, 16938780, 19391024

22855193, 19910254, 19402853, 20618595, 23200778, 21612959, 19265346

20001738, 21123913, 20632205, 21787056, 20557652, 22178855, 22173980

18913440, 20011646, 21517767, 19769625, 20249656, 19192508, 24309560

23265829, 19381346, 21283023, 18684802, 20700049, 19001359, 17532734

22351572, 18849970, 21083850, 19899243, 24908321, 19186845, 20890311

24811725, 21151241, 18728991, 21296029, 19403858, 20988771, 20176045

18963099, 22536802, 19031341, 22077517, 21249747, 26664361, 26088836

20675854, 22657942, 19890034, 23178558, 24581821, 26088426, 20024405

24693382, 20466322, 19525002, 22304421, 20018770, 21794615, 19159710

18968507, 22905136, 21868720, 24668398, 22905130, 25459958, 19524384

17722075, 20033247, 19200481, 18907688, 22922076, 20704530, 19928926

20161701, 21354456, 23007241, 19542097, 19931709, 19006817, 17805926

22153180, 20828104, 19188927, 20565133, 19536017, 21079201, 18371441

26575788, 20397490, 19666651, 20172151, 19582642, 19184226, 20144871

19954575, 19220183, 19536415, 19171086, 19353144, 21889720, 20426193

19335438, 24397438, 20076781, 21427131, 19881042, 20565112, 19124336

25432250, 20848335, 21744290, 20543011, 22675822, 20032123, 24365589

21812537, 21514877, 20798891, 18743542, 18603592, 20063230, 18735746

22191356, 20049681, 19687159, 20424899, 19945499, 19886165, 19302393

22862134, 19354794, 22347483, 20603181, 21827522, 18952766, 20082651

23272045, 22766304, 20226038, 18043064, 23713236, 21559748, 20505778

20475845, 17274537, 24376848, 20897180, 22961508, 22374754, 17319928

19440386, 19483288, 22809871, 16439813, 19240264, 20766180, 20859910

20624914, 19926255, 22024071, 25079710, 19075256, 20315311, 20756607

20437153, 20936731, 19280225, 20560611, 21756699, 19504946, 19605498

20373598, 20476175, 25264559, 18900245, 19139189, 19240996, 19238815

20611795, 23215145, 22842151, 21680130, 23003979, 19250261, 19843202

19817284, 22707244, 18917800, 25546608, 19327067, 18834993, 21186638

20279368, 19588097, 18966425, 20617709, 19192487, 20830993, 22353346

19049932, 16941434, 18748005, 20361671, 20181155, 21214994, 19571082

20009833, 16556115, 21976960, 20294666, 18120963, 18914624, 24376878

22348206, 20958816, 19460920, 19295348, 21291274, 21875360, 19326908

22841860, 19230722, 19147636, 24583530, 20192960, 19546825, 23548817

20847534, 21839477, 18674024, 20930092, 21863727, 19591408, 20013559

19915271, 21950301, 20284155, 20950386, 20903906, 21533640, 19878169

21080143, 22359063, 23508801, 19927393, 18868195, 21756677, 20707790

19632912, 20668053, 25982666, 18971927, 19034201, 21060755, 21756661

26362821, 20985069, 19649808, 20686773, 20027746, 21421886, 22347859

19262199, 22376809, 22346829, 19048007, 19566592, 19503821, 24662775

20133026, 25429959, 20005124, 22821774, 20496254, 24555417, 20233181

19902195, 19168167, 20939028, 19012119, 19060015, 19131386, 19051205

19512341, 21164840, 19841800, 19818721, 20800890, 18802876, 21646361

26479173, 19537845, 19475484, 19509565, 18674047, 20898997, 20557750

19708632, 19285025, 21091431, 19289642, 21133343, 23263018, 20835241

20869721, 20177858, 20139836, 21419850, 20233159, 18952331, 21373473

20071054, 18944562, 23582997, 21164318, 20752907, 18899662, 23088803

19878106, 19448499, 19329654, 17972478, 21517440, 25897615, 19076343

20672955, 19571055, 25994657, 23240358, 22179537, 19858928, 24411921

19146597, 18254023, 25766822, 19783286, 22641033, 20777159, 19614585

25957038, 24652769, 20020936, 18868646, 22966117, 21221860, 18787718

21450666, 19907278, 22223463, 16923858, 18378632, 18115378, 22046677

21299490, 19347458, 19385656, 19192570, 21315084, 26111842, 16817656

19598441, 23644359, 19617921, 21293453, 19843631, 18499088, 21607719

24439410, 19798240, 19141838, 19795047, 19865345, 19989661, 20962057

20671094, 18813860, 19540182, 21888938, 17008068, 21184223, 20936447

20468401, 18971325, 22212940, 19513298, 19728268, 21623164, 22519146

21275387, 19555029, 19366375, 18202441, 20828433, 19706965, 18549238

19068970, 19153897, 18511269, 20333204, 26544823, 20825533, 22205263

19015671, 19025195, 19855835, 20480623, 20534497, 20969164, 20707932

20099675, 19014915, 18628388, 20868862, 19468612, 25475853, 20862087

24624166, 20969159, 22351417, 20124446, 22302666, 19548064, 19529953

20878790, 18510194, 23149541, 24587802, 20186868, 21266085, 19469538

17835294, 19721304, 19791377, 19547774, 20746044, 19192600, 20907061

22950945, 20159216, 20138303, 19527817, 19516448, 21116398, 20366825

23572982, 19341239, 19490948, 19931367, 22294260, 20832516, 19309466

19377346, 23008056, 19081128, 20134113, 20011515, 20368850, 19594959

19054378, 18973548, 22235858, 19943771, 19167353, 19461270, 23061702

19134095, 19696041, 16875041, 24534401, 20241083, 19403942, 22296366

22529018, 20703000, 19879746, 20557786, 20496341, 21294938, 19684504

19024808, 20977794, 20130826, 22119679, 18799993, 19987300, 24642295

19262984, 19544707, 20228093, 20195148, 25547060, 18758877, 19488514

20446883, 20125550, 18847357, 24523374, 20466628, 19317899, 20072221

19162308, 18819908, 23124895, 23220453, 20265853, 18421248, 18090142

22901797, 19547370, 19799847, 21225209, 23315889, 19352437, 18942923

19582781, 21241829, 19906564, 21174000, 26546664, 21553476, 22465352

18938454, 18648423, 18962266, 19869255, 22051945, 20256086, 21620471

19074086, 24838161, 22981722, 23271423, 21372897, 20101006, 18867241

22091036, 19304354, 23025340, 20106456, 18681056, 19896336, 20828947

24329056, 20296619, 18618122, 20440930, 18456643, 19699191, 18687101

22551446, 24560906, 18441401, 20266282, 21373076, 19803231, 19627721

18899974, 19383247, 21957762, 20598042, 20841419, 19645496, 23265456

22184431, 19434529, 19018447, 22757364, 18851894, 21842017, 19022470

22251691, 18112769, 19791702, 21372880, 20105469, 18687126, 19880190

19871282, 18952631, 16887946, 22594071, 22062517, 17655240, 23093845

23782881, 19933147, 19196832, 20747533, 18663314, 19189525, 21649497

18845653, 20245173, 18988834, 19647120, 8889083, 20245930, 18921743

14043712, 18954112, 18799063, 19571367, 20509482, 15996428, 20181030

22724269, 20884085, 18849537, 24368922, 19183343, 18907741, 20603431

19571350, 22190791, 19982551, 22228997, 19274987, 20340510, 19279273

26171897, 20669434, 16863642, 19175750, 19619732, 20348653, 20547245

19680796, 23507063, 20766694, 20096843, 19475862, 20558005, 19450314

20184217, 21157175, 22366558, 21028698, 18191823, 20860659, 19307662

20261923, 19371175, 20322037, 19336395, 22529728, 16293223, 20261926

20139391, 19593445, 20819901, 19891196, 23602960, 18840932, 20003739

19929706, 18900107, 19143550, 22926613, 20428621, 18775971, 21315800

19138800, 21131203, 19518079, 21350832, 22540882, 25186079, 20539160

18908550, 19645930, 19145675, 20548731, 20721341, 19364502, 25353150

20017509, 19597439, 17734040, 21239530, 19230607, 23108128, 19888853

19354335, 19044962, 19639483, 19889230, 17158137, 20596195, 22243217

18818841, 22917705, 19790243, 21239922, 22243719, 20378086, 21260431

24808595, 19301392, 26550584, 22923409, 20877664, 20672075, 20879889

19120604, 21285458, 20874880, 23170620, 21032832, 18288842, 19566293

19959229, 18934048, 20624386, 19624139, 23749547, 19952975, 21751519

26444887, 20664416, 20340595, 21097914, 19013183, 21187090, 19576659

19688599, 19912337, 20464614, 19438874, 20564072, 21608238, 19200977

22255113, 20893843, 19263008, 16356176, 20051868, 18863094, 22778199

19190401, 19168442, 19810774, 21387771, 19624156, 21218121, 24289874

19573061, 25947799, 21509656, 20699904, 21172913, 19191531, 25602488

19996677, 17365043, 25093739, 16359751, 20027864, 19769480, 18810904

19272708, 20402832, 19873610, 18494306, 17284345, 13542050, 19518539

20808192, 22297022, 25192729, 20225417, 24425998, 19578230, 19447234

19487147, 19016730, 19130972, 19693090, 19362731, 16619249, 18354830

20494044, 21188584, 22529088, 25256170, 20688221, 22666009, 19969872

21131289, 19136799, 19932746, 20448066, 19012044, 18885870, 21765742

21785691, 24341675, 19663176, 19501299, 19146474, 20581111, 22491079

18673090, 20181016, 19578247, 19050649, 20399973, 19181525

Patch 26392192 : applied on Wed Nov 01 08:03:42 PET 2017

Unique Patch ID: 21527520

Patch description: “OCW Patch Set Update : 12.1.0.2.171017 (26392192)”

Created on 2 Oct 2017, 09:28:19 hrs PST8PDT

Bugs fixed:

21339083, 18589889, 20768643, 19061429, 26512962, 19133945, 19341538

19521169, 20011424, 19361757, 19187207, 19302350, 23340259, 19130141

19530755, 21441387, 19028836, 19699720, 19620918, 19168690, 18899171

17005215, 19244316, 19653795, 18634372, 19471722, 19536337, 19184188

19131709, 20160490, 20995001, 24509984, 21131037, 19380733, 20985167

18943696, 20552947, 19591984, 18135723, 18461246, 20014326, 19524857

22999793, 25250824, 18890943, 19154753, 24808260, 19499021, 25206628

19046190, 20923930, 25074091, 20028666, 24451580, 19051385, 19050688

19682695, 19831611, 22891868, 21607972, 18922918, 21087799, 18956780

19683886, 23058136, 23757020, 19026993, 20110156, 21269977, 18261648

18362971, 19558324, 22651583, 20185476, 18882642, 19478119, 19414274

17447588, 19262534, 20752167, 19147513, 22475834, 18893463, 21242118

19514085, 20758997, 19659885, 19319904, 18703978, 23079844, 24796183

19700256, 19703246, 20467806, 23304537, 19192901, 21527118, 22992133

24670214, 25435186, 19479503, 19147751, 18901356, 26546632, 21232394

18964974, 22162062, 18835366, 19013789, 19207286, 18950232, 22583325

19148791, 19158155, 19556820, 16505171, 21210985, 21228405, 22261732

22135419, 19449737, 21878613, 21646169, 19187515, 19230771, 20795241

19453778, 19853036, 18843572, 18520351, 21430567, 19185148, 19232454

24583356, 21220597, 18541110, 18834955, 19204743, 20175174, 19140891

18747335, 20676340, 19270660, 19325701, 21108895, 20877003, 19222693

19662663, 19684131, 19010177, 19272663, 18910576, 18907170, 19426384

9372680, 20737462, 19700294, 24801915, 23640904, 25943805, 20289828

19331454, 22568500, 20278216, 20827053, 20119857, 19703199, 22456393

20588164, 18955644, 20991879, 18508710, 19908346, 19589221, 25795447

18952857, 19537762, 21152052, 16286734, 20669114, 18762843, 18945249

19184799, 21692274, 19205086, 19351363, 20091753, 19031737, 19079087

24932026, 19148367, 18968981, 19150517, 20231741, 19217019, 18730096

18975620, 19067804, 21259359, 19054979, 19209951, 19318983, 18747382

19154673, 18752378, 22905714, 19150088, 19013444, 19234177, 18999857

19075747, 19367276, 19612597, 19874047, 19677813, 23049292, 19427050

18910443, 20033787, 19148982, 25371632, 19777496, 20730170, 24362631

18523468, 19277814, 20562133, 19071526, 21420753, 22986384, 25537905

18850051, 20433932, 20061168, 18468128, 19370739, 20822591, 19609388

18776786, 20136892, 18989446, 19148793, 22904978, 19585454, 18317489

19955755, 19059053, 19807548, 19124972, 22229116, 18910748, 19147509

18849896, 19617592, 19398098, 22840085, 19076165, 19498411, 19704993

21294273, 19459023, 20634511, 21229744, 19516572, 21474380, 21484367

20276459, 21550724, 17208793, 19234907, 20007009, 19538714, 22867050

19525147, 22728264, 19383028, 19513969, 22195657, 19622211, 23111368

19986391, 19314048, 18834934, 19473851, 19458082, 18894342, 23762756

19522067, 23618734, 18116578, 19241857, 19076778, 19066699, 25206563

19068003, 18937186, 20267877, 19916737, 19770543, 22863466, 19635215

19613533, 21057239, 20783416, 19315522, 19141785, 19163887, 19885321

25233268, 18715868, 18852058, 19701617, 25448476, 19280860, 19139608

19908051, 24403376, 21453435, 18946768, 21951266, 23063340, 24578464

24831158, 19135521, 18604143, 23150001, 19627476, 20568884, 20317221

20177779, 19266658, 19491996, 24522717, 23717606, 18330979, 19027351

21227730, 18707416, 21118157, 21113068, 19925992, 20235486, 20006646

15922206, 18439295, 18991776, 21245437, 23170839, 19163425, 19550195

20389257, 18849021, 18861196, 25026932, 17940721, 18524834, 19287706

19522313, 18835283, 18748932, 19184765, 21639478, 14851828, 20598625

19226141, 19053891, 18871287, 25371730, 18998228, 18980002, 19682778

19325604, 8934841, 18777835, 17338864, 18945906, 19513650, 24692493

19702758, 18952577, 19473088, 19178517, 25197670, 18729269, 19529729

19455563, 20877072, 23275930, 20340620, 18536826, 19292605, 22264951

20920711, 20660273, 25037836, 20011635, 17592037, 18935857, 22989069

19029647, 19179158, 22302421, 25562525, 23076471, 19140712, 20012766

25907259, 23049301, 19184276, 25206643, 20510208, 20001507, 20079414

19680763, 19259765, 24422155, 18962892, 19513888, 24464953, 14661880

21519340, 21222147, 20218012, 19551830, 21616278, 19068333, 18945435

20775015, 19319192, 22579220, 25588693, 20839581, 19178629, 18774591

21694237, 19304104, 19284357, 22594310, 20725084, 19457575, 21297657

19069755, 19021575, 18715884, 19584688, 18798573, 19812592, 19018001

19292272, 19270956, 21967791, 21794779, 23055224, 18700893, 21276514

20467377, 18406774, 24341772, 23621494, 20607821, 21976167, 21082737

21694632, 23095976, 20115586, 20883009, 22069809, 25591658, 19164099

18283792, 23635127, 19155350, 25049033, 19884033, 19146822, 18798432

21123424, 24356071, 19471836, 24445255, 20936562, 18750781, 19045143

20559126, 22483804, 19146980, 19601003, 20099624, 21378016, 19537547

19281106, 18862203, 23733697, 19954372, 25851874, 18235274, 19205617

19513351, 20219458, 19150313, 18843054, 18708349, 19332386, 18953639

21423055, 19371270, 25221056, 19203996, 20038431, 19370846, 21053036

18998379, 20157569, 21357522, 24824518, 19273577, 24667931, 24709351

25206634, 22113170, 19632437, 18990354, 19288396, 20620581, 19557558

19127078, 22746166, 22548137, 21792881, 20900588, 20053557, 22088198

22664609, 19315567, 19444471, 19154933, 20953798, 18290252, 18813323

19915945, 19500293, 18643483, 19134098, 23034352, 25206614, 18965694

25233797, 19226858, 19591356, 19602208, 22884308, 25564644, 20069197

18417590, 18920408, 18263259, 18636884, 22289350, 19043795, 20746507

18260170, 18919682, 24657753, 18678829, 19319357, 19273758, 18953878

18999195, 24314323, 18759724, 20458032, 24462646, 25606616, 25569634

19066844, 13843841, 20290467, 22934855, 19649640, 19062675, 21924910

18859710, 20862812, 19504641, 23101611, 19341481, 20293730, 19343245

18304090, 19241655, 19470791, 18242738, 20649573, 18372060, 18953889

18827679, 19140711, 19023430, 19259290, 19045388, 20186278, 25197395

19522571, 19709663, 18875012, 18861564, 21075392, 19273760, 19225265

15885689, 20853464, 21696068, 18819158, 21394146, 19049721, 19368917

25206650, 21255373, 22393909, 25164540, 20408163, 20579351, 18868829

20350915, 21327402, 22024217, 23101338, 19820247, 25206585, 20363708

19538241, 25785073, 19804032

Patch 26392164 : applied on Wed Nov 01 07:51:02 PET 2017

Unique Patch ID: 21527313

Patch description: “ACFS Patch Set Update : 12.1.0.2.171017 (26392164)”

Created on 31 Aug 2017, 23:39:16 hrs PST8PDT

Bugs fixed:

19452723, 18900953, 23625427, 24308283, 21221726, 21208140, 19844362

23639692, 18951113, 19526234, 21815339, 18899600, 22810422, 25560948

20923224, 19434261, 23252830, 19517835, 21474561, 18185024, 19273271

19013966, 25403933, 19195735, 19001684, 19509898, 23112252, 19053182

22087780, 20438706, 19593769, 19610001, 19475588, 19811784, 18957085

19279106, 23181299, 19184398, 20098064, 22222222, 22198405, 22910118

20140148, 21519796, 23100705, 19450090, 19502657, 19859183, 18877486

25098392, 18915417, 21231953, 19060056, 18955907, 19078259, 19919907

25078431, 22250187, 19127216, 20010980, 18934139, 24679041, 22689989

19335268, 22174238, 21555038, 20681968, 25549648, 21058846, 18851012

19149476, 25526314, 20952520, 19526283, 19428756, 20463674, 19183802

24760367, 19051391, 22846612, 19690653, 25375360, 22613250, 19355146

25480028, 21077218, 20314907, 20402287, 22661787, 19644505, 22198176

22975177, 23535076, 19353057, 22591010, 19201087, 19270227, 21233961

21124596, 24964969, 19901825, 19610022, 19446719, 22241945, 19649858

22472364, 24325972, 24609301, 18321597, 19557156, 22319558, 21076821

19469788, 18510745, 19528981, 21369858, 23321114, 23557970, 19134464

Patch 21436941 : applied on Wed Nov 01 07:49:58 PET 2017

Unique Patch ID: 19245012

Patch description: “WLM Patch Set Update: 12.1.0.2.5 (21436941)”

Created on 13 Aug 2015, 04:00:40 hrs PST8PDT

Bugs fixed:

20075154, 20641027, 20548410, 19016964, 19582630

Patch level status of Cluster nodes :

Patching Level                  Nodes

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

3345860768                      xxx

0                               xxy

——————————————————————————–

OPatch succeeded.

Please check overall status after applying patch.

[grid@xxx ~]$ crsctl stat res -t

Now login to root user and perform patching using following steps for ORACLE Home:-

[root@olg1prrptgdbf01 oraclecd]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[root@xxx oraclecd]# export ORACLE_SID=ERPTPD11

[root@xxx oraclecd]# echo $PATH

/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

[root@xxx oraclecd]# export PATH=/u01/app/oracle/product/12.1.0/db_1/bin/:/u01/app/oracle/product/12.1.0/db_1:/u01/app/oracle/product/12.1.0/db_1/OPatch:$PATH

[root@xxx oraclecd]# echo $PATH

[root@xxx oraclecd]# opatchauto apply /oraclecd/p26635880/26635880 -oh /u01/app/oracle/product/12.1.0/db_1

OPatchauto session is initiated at Thu Nov 2 00:04:12 2017
Session log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/opatchauto2017-11-02_12-04-12AM.log
Resuming existing session with id WAU8

Start applying binary patch on home /u01/app/oracle/product/12.1.0/db_1
Binary patch applied successfully on home /u01/app/oracle/product/12.1.0/db_1

Performing postpatch operation on home /u01/app/oracle/product/12.1.0/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/12.1.0/db_1

Starting database service on home /u01/app/oracle/product/12.1.0/db_1
Database service successfully started on home /u01/app/oracle/product/12.1.0/db_1

Preparing home /u01/app/oracle/product/12.1.0/db_1 after database service restarted
No step execution required………
Prepared home /u01/app/oracle/product/12.1.0/db_1 successfully after database service restarted

Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0/db_1
SQL patch applied successfully on home /u01/app/oracle/product/12.1.0/db_1

Verifying patches applied on home /u01/app/oracle/product/12.1.0/db_1
Patch verification completed with warning on home /u01/app/oracle/product/12.1.0/db_1

OPatchAuto successful.

——————————–Summary——————————–

Patching is completed successfully. Please find the summary as follows:

Host:xxx
RAC Home:/u01/app/oracle/product/12.1.0/db_1
Summary:

==Following patches were SKIPPED:

Patch: /oraclecd/p26635880/26635880/21436941
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /oraclecd/p26635880/26635880/26392164
Reason: This patch is not applicable to this specified target type – “rac_database”

==Following patches were SUCCESSFULLY applied:

Patch: /oraclecd/p26635880/26635880/26392192
Log: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-01_22-37-36PM_1.log

Patch: /oraclecd/p26635880/26635880/26717470
Log: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-01_22-37-36PM_1.log

OPatchauto session completed at Wed Nov 1 22:44:06 2017
Time taken to complete the session 8 minutes, 55 seconds

Please perform the above steps to patch 2^nd node as well.The patch can be applied in rolling fashion so one node can be UP when another node is being patched.
Issue Faced:-

A.The opatchauto could not shutdown the database and it went hang.

[root@xxx OPatch]# ./opatchauto apply /oraclecd/p26635880/26635880 -oh /u01/app/oracle/product/12.1.0/db_1

OPatchauto session is initiated at Wed Nov 1 22:02:04 2017

System initialization log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchautodb/systemconfig2017-11-01_10-02-05PM.log.

Session log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/opatchauto2017-11-01_10-02-25PM.log
The id for this session is MIXI

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0/db_1
Patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0/db_1

Verifying patch inventory on home /u01/app/oracle/product/12.1.0/db_1
Patch inventory verified successfully on home /u01/app/oracle/product/12.1.0/db_1

Verifying SQL patch applicablity on home /u01/app/oracle/product/12.1.0/db_1
SQL patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0/db_1

Preparing to bring down database service on home /u01/app/oracle/product/12.1.0/db_1
Successfully prepared home /u01/app/oracle/product/12.1.0/db_1 to bring down database service

Bringing down database service on home /u01/app/oracle/product/12.1.0/db_1

Now we need to open a different terminal and shutdown immediate the databases running.

[root@xxx OPatch]# ./opatchauto resume

OPatchauto session is initiated at Wed Nov 1 22:33:25 2017
Session log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/opatchauto2017-11-01_10-33-25PM.log
Resuming existing session with id MIXI

Bringing down database service on home /u01/app/oracle/product/12.1.0/db_1
Failed to bring down database service on home /u01/app/oracle/product/12.1.0/db_1

Execution of [RACShutDownAction] patch action failed, check log for more details. Failures:
Patch Target : xxx->/u01/app/oracle/product/12.1.0/db_1 Type[rac-nonshared]
Details: [
—————————Patching Failed———————————
Command execution failed during patching in home: /u01/app/oracle/product/12.1.0/db_1, host: xxx.
Command failed: /bin/sh -c ‘ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 /u01/app/oracle/product/12.1.0/db_1/bin/srvctl stop home -o /u01/app/oracle/product/12.1.0/db_1 -n xxx -f -stopoption TRANSACTIONAL -s /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchautodb/statfile/xxx/OracleHome-7f14853a-e42a-471d-bf5e-d903f25ead62_xxx.stat’
Command failure output:
PRKO-3244 : The ‘-statfile <state_file>’ option value “/u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchautodb/statfile/xxx/OracleHome-7f14853a-e42a-471d-bf5e-d903f25ead62_xxx.stat” already exists

After fixing the cause of failure Run opatchauto resume with session id “MIXI”

]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.

OPatchauto session completed at Wed Nov 1 22:33:27 2017
Time taken to complete the session 0 minute, 3 seconds

opatchauto failed with error code 42

But problem is opatchauto already created the “/u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchautodb/statfile/xxx/OracleHome-7f14853a-e42a-471d-bf5e-d903f25ead62_xxx.stat” file which needs to be removed manually.

Now opatchauto resume will be executed successfully.

[root@xxx OPatch]# ./opatchauto resume

OPatchauto session is initiated at Wed Nov 1 22:35:12 2017
Session log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/opatchauto2017-11-01_10-35-12PM.log
Resuming existing session with id MIXI

Bringing down database service on home /u01/app/oracle/product/12.1.0/db_1
Following database(s) and/or service(s) are stopped and will be restarted later during the session: erptpd1
Database service successfully brought down on home /u01/app/oracle/product/12.1.0/db_1

Performing prepatch operation on home /u01/app/oracle/product/12.1.0/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/12.1.0/db_1

Start applying binary patch on home /u01/app/oracle/product/12.1.0/db_1
Binary patch applied successfully on home /u01/app/oracle/product/12.1.0/db_1

Performing postpatch operation on home /u01/app/oracle/product/12.1.0/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/12.1.0/db_1

Starting database service on home /u01/app/oracle/product/12.1.0/db_1
Database service successfully started on home /u01/app/oracle/product/12.1.0/db_1

Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0/db_1
SQL patch applied successfully on home /u01/app/oracle/product/12.1.0/db_1

Verifying patches applied on home /u01/app/oracle/product/12.1.0/db_1
Patch verification completed with warning on home /u01/app/oracle/product/12.1.0/db_1

OPatchAuto successful.

B.opatchauto failed with error code 2 as the OPATCH was not updated.

[root@xxy OPatch]# ./opatchauto apply /oraclecd/p26635880/26635880 -oh /u01/app/oracle/product/12.1.0/db_1
OPatch Automation Tool
Copyright (c) 2017, Oracle Corporation. All rights reserved.

OPatchauto version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Running from : /u01/app/oracle/product/12.1.0/db_1

opatchauto log file: /u01/app/product/12.1.0/grid/cfgtoollogs/opatchauto/26635880/opatch_gi_2017-11-01_22-57-15_deploy.log

Parameter Validation: Successful

Patch Collection failed: /oraclecd/p26635880/26635880/26717470/etc/config/actions.xml (No such file or directory)

opatchauto failed with error code 2.

C.Prerequisite check “CheckActiveFilesAndExecutables” failed.So you need to find if the process is being held by any oracle user.You may use fuser command to check which process is using the file.

[root@xxy OPatch]# ./opatchauto apply /oraclecd/p26635880/26635880 -oh /u01/app/oracle/product/12.1.0/db_1

OPatchauto session is initiated at Wed Nov 1 23:53:53 2017

System initialization log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchautodb/systemconfig2017-11-01_11-53-54PM.log.

Session log file is /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/opatchauto2017-11-01_11-54-03PM.log
The id for this session is WAU8

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0/db_1
Patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0/db_1

Verifying patch inventory on home /u01/app/oracle/product/12.1.0/db_1
Patch inventory verified successfully on home /u01/app/oracle/product/12.1.0/db_1

Verifying SQL patch applicablity on home /u01/app/oracle/product/12.1.0/db_1
SQL patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0/db_1

Preparing to bring down database service on home /u01/app/oracle/product/12.1.0/db_1
Successfully prepared home /u01/app/oracle/product/12.1.0/db_1 to bring down database service

Performing prepatch operation on home /u01/app/oracle/product/12.1.0/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/12.1.0/db_1

Start applying binary patch on home /u01/app/oracle/product/12.1.0/db_1
Failed while applying binary patches on home /u01/app/oracle/product/12.1.0/db_1

Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:
Patch Target : xxy->/u01/app/oracle/product/12.1.0/db_1 Type[rac]
Details: [
—————————Patching Failed———————————
Command execution failed during patching in home: /u01/app/oracle/product/12.1.0/db_1, host: xxy.
Command failed: /u01/app/oracle/product/12.1.0/db_1/OPatch/opatchauto apply /oraclecd/p26635880/26635880 -oh /u01/app/oracle/product/12.1.0/db_1 -target_type rac_database -binary -invPtrLoc /u01/app/oracle/product/12.1.0/db_1/oraInst.loc -persistresult /u01/app/oracle/product/12.1.0/db_1/OPatch/auto/dbsessioninfo/sessionresult_xxyERPTPD12.ser -analyzedresult /u01/app/oracle/product/12.1.0/db_1/OPatch/auto/dbsessioninfo/sessionresult_analyze_xxy_ERPTPD12.ser
Command failure output:
==Following patches FAILED in apply:

Patch: /oraclecd/p26635880/26635880/26717470
Log: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-01_23-58-30PM_1.log
Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: Prerequisite check “CheckActiveFilesAndExecutables” failed.

After fixing the cause of failure Run opatchauto resume with session id “WAU8”

]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.

OPatchauto session completed at Wed Nov 1 23:59:48 2017
Time taken to complete the session 5 minutes, 55 seconds

opatchauto failed with error code 42

Oracle database instance recovery is taking too much time after shutdown immediate

Posted on October 31, 2017November 2, 2017 by debasis maity

1.We were going to open DB after shutdown immediate but it went hang.In alert log , we could only notice following message for 40 minutes.

Completed: ALTER DATABASE MOUNT
Tue Oct 31 01:08:39 2017
ALTER DATABASE OPEN
Beginning crash recovery of 1 threadsparallel recovery started with 9 processes
Started redo scanTue Oct 31 01:08:58 2017
Completed redo scan
read 3733362 KB redo, 2824480 data blocks need recovery
Started redo application at
Thread 1: logseq 39596, block 3434354
Recovery of Online Redo Log: Thread 1 Group 10 Seq 39596 Reading mem 0
Mem# 0: /oracle/data/san_oradata01/eocmdb/redofiles/log10b.rdo
Recovery of Online Redo Log: Thread 1 Group 11 Seq 39597 Reading mem 0
Mem# 0: /oracle/data/san_oradata01/eocmdb/redofiles/log11b.rdo
Tue Oct 31 01:09:18 2017
Recovery of Online Redo Log: Thread 1 Group 13 Seq 39598 Reading mem 0
Mem# 0: /oracle/data/san_oradata01/eocmdb/redofiles/log13b.log
Tue Oct 31 01:09:28 2017
Completed redo application of 628.90MB

2.Troubleshooting using following queries:-

export NLS_DATE_FORMAT=’dd-mon-yyyy hh24:mi:ss’

set feedback on

set heading on

set echo on

set pagesize 100

set linesize 150

col checkpoint_change# format 999999999999999999999

set echo on

set linesize 400

set pagesize 20000

col name format a75

col MEMBER format a100

col file_name format a100

col PLATFORM_NAME format a30

col TABLESPACE_NAME format a30

set numformat 999999999999999

col hxfil format 9999

col fhsta format 9999

col fhscn format 999999999999999999999999

col fhtmn format a30

col fhrba_Seq format 9999999999999

col FHBCP_THR format 9999

col fhthr format 999

col fhrba_seq format 99999999

col fhscn format 999999999999999999

set linesize 175

set pagesize 50

alter session set nls_date_format = ‘DD-MON-RRRR HH24:MI:SS’;

Spool recover.lst

select name,platform_name,open_mode,controlfile_type,log_mode,flashback_on,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$database;

select name, status from v$datafile;

select name, recover, fuzzy, checkpoint_change#,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$datafile_header;

select * from v$backup;

select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

select GROUP#,member from v$logfile;

select * from v$recover_file;

select * from v$recovery_log;

select hxfil FILE#, fhsta STATUS, fhscn SCN, fhrba_Seq SEQUENCE, fhtnm TABLESPACE, FHBCP_THR Thread, fhafs AFUZZY from x$kcvfh order by hxfil;

— Min PITR ABSSCN is minimun SCN needed to open

select min(FHSCN) “LOW FILEHDR SCN”, max(FHSCN) “MAX FILEHDR SCN”, max(FHAFS) “Min PITR ABSSCN” from X$KCVFH;

— Min max SEQUENCE on file headers

SELECT fhthr thread, fhrba_seq sequence, fhscn scn, fhsta status, count(*) FROM x$kcvfh group by fhthr,fhrba_seq,fhscn,fhsta;

select min(FHRBA_SEQ) “LOW FILEHDR SEQUENCE”, max(FHRBA_SEQ) “MAX FILEHDR SEQUENCE” from X$KCVFH;

select HXFIL File_num,substr(HXFNM,1,140) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH;

SELECT fhthr thread, fhrba_seq sequence, fhscn scn, fhsta status, count(*) FROM x$kcvfh group by fhthr,fhrba_seq,fhscn,fhsta;

3. Please execute following command to check recovery.

RMAN>backup validate check logical database;

SQL>alter system dump logfile ‘/oracle/data/san_oradata01/eocmdb/redofiles/log10b.rdo’ validate;

RMAN>recover database test;

4.Re-creating control file and run recover from RMAN

connect target /;
alter session set tracefile_identifier = “10046_trace_file”;
alter session set events ‘10046 trace name context forever, level 3’;
set echo on;
debug on;
run {
allocate channel d1 type disk;
recover database;
}
debug off;
exit

But this was stuck with following:-

RMAN> recover database;

Starting recover at 31-OCT-17
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=39598
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/31/2017 06:29:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39598 and starting SCN of 12444621699598

5.Now we need to validate archive log 39597 for consistency.

RMAN> validate archivelog sequence 39597 thread 1;

6.We need to do following if recover stuck

a)Open one more session and shutdown abort the instance

b)Startup mount

c)Try with no parallel recovery

Now, you are applying the redolog named /oracle/data/san_oradata01/eocmdb/redofiles/log13b.log

with sequence 39598

Which is the current redo log:

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
—————- —————- —————- —————- — —————- —————-
8 1 39593 1 YES INACTIVE 12444097046518
9 1 39595 1 YES INACTIVE 12444097257724
10 1 39596 1 YES ACTIVE 12444097354077
15 1 39592 1 YES INACTIVE 12444096949452
12 1 39594 1 YES INACTIVE 12444097140637
13 1 39598 1 NO CURRENT 12444621699598 >>>>>>>>>>>>>>>>>>
14 1 39591 1 YES INACTIVE 12444095658402
11 1 39597 1 YES ACTIVE 12444621392892

SQL> recover database noparallel using backup controlfile;

If it ask for log sequence 39598, then provide the full path as below and then press <Enter>
/oracle/data/san_oradata01/eocmdb/redofiles/log13b.log (This is current redolog file)

If it says… “Media Recovery complete” then open the db with resetlogs:

SQL> alter database open resetlogs;

Note:-Following views are very useful to monitor instance recovery progress.

Reference:-Monitoring Restore/Recovery Progress (Doc ID 1335910.1)

Session Waits:-

Are there any sessions in wait and what is it waiting for?

Run the following queries at least 3 times, at 5 minute intervals to see progress/change.

set linesize 200 trimspool oncol event form a25
col p1text form a15
col p1 form 999999
col p2text form a15
col p2 form 999999
col p3text form a10
col p3 form 9999
col waited form 9999
col waiting form 9999select sid, event, p1text, p1, p2text, p2, p3text, p3,
wait_time waited, seconds_in_wait waiting
from gv$session_wait
where event not like ‘SQL*Net%’
and event not like ‘%timer%’
and event not like ‘rdbms%’
and event not like ‘pipe%’
and event not like ‘DIAG%’
and event not like ‘Streams AQ%’
and event not like ‘VKTM%’
and state = ‘WAITING’
order by seconds_in_wait
/

Recovery Progress:-

What is the recovery progress? V$RECOVERY_PROGRESS is only populated when RECOVERY is in progress. A restore operation will not populate this view. So if you think a recovery process is slow – is it really at the recovery phase, or still restoring from RMAN backuppieces?

This is an example of a recovery progress:

START_TIME ITEM UNITS SOFAR TOTAL TIMESTAMP
——————– ——————————– ——————————– ———- ———- ——————–
31-OCT-2017 15:52:22 Active Apply Rate KB/sec 164 0
31-OCT-2017 15:52:22 Average Apply Rate KB/sec 167 0
31-OCT-2017 15:52:22 Maximum Apply Rate KB/sec 5460 0
31-OCT-2017 15:52:22 Redo Applied Megabytes 330 0
31-OCT-2017 15:52:22 Last Applied Redo SCN+Time 0 0 30-OCT-2017 13:45:57
31-OCT-2017 15:52:22 Active Time Seconds 1963 0
31-OCT-2017 15:52:22 Elapsed Time Seconds 2018 0
31-OCT-2017 15:14:17 Log Files Files 1 1
31-OCT-2017 15:14:17 Active Apply Rate KB/sec 40 40
31-OCT-2017 15:14:17 Average Apply Rate KB/sec 45 45
31-OCT-2017 15:14:17 Maximum Apply Rate KB/sec 341 341START_TIME ITEM UNITS SOFAR TOTAL TIMESTAMP
——————– ——————————– ——————————– ———- ———- ——————–
31-OCT-2017 15:14:17 Redo Applied Megabytes 89 89
31-OCT-2017 15:14:17 Last Applied Redo SCN+Time 0 0 30-OCT-2017 13:39:19
31-OCT-2017 15:14:17 Active Time Seconds 1903 1903
31-OCT-2017 15:14:17 Apply Time per Log Seconds 1903 1903
31-OCT-2017 15:14:17 Elapsed Time Seconds 2005 2005

6.Root Cause of slowness:-

Hang analyzer log says:-

is waiting for ‘parallel recovery slave idle wait’ with wait info:
{
p1: ”=0x1001ffff
p2: ”=0x1
p3: ”=0x182f945d00
time in wait: 1.004650 sec
timeout after: never
wait id: 57508
blocking: 0 sessions
current sql: <none>

IO stat report:-

The all local disk (not in SAN) was showing await.

AWR report :-

it looks like local disk issue not SAN issue. If you look into Av rd(ms) ,it is 1 -2 ms for SAN and for local it is 20 ms to 60 ms. Oracle suggestion is it should be less than 10 ms. So all datafiles should immediately be moved to SAN from Local mount point (Local does not have good striping capacity compare to SAN which will raise bottleneck) .

Schedule Start & Stop RDS Instances and Save Money

Posted on October 26, 2017October 26, 2017 by samindradey

Introduction

If RDS Instances is AWS keep running without 24×7 usage, it can cost you very high in AWS billing.

Take following example how idle RDS instances can cost you:-

Lets take a medium sized RDS instance db.m4.xlarge (4CPU and 16GB RAM) in Sydney region for MySQL engine costs $0.492 per hour.

Now if we use the calculator, usage running 24×7, costs on average $360.15 per month.

Now if we only had that running business hours (9 hrs a day MON-FRI) , talking on average $92.99.

Thats a cost saving of $267.16. That is just for one instance per month/ OR $3,205.92 per annum.

What if you were working in a large enterprise with 10s or 100s of RDS instances.

Hence it is very important to stop the RDS instance when Idle to save significant cost in AWS Billing.

Let’s use aws-sdk, nodeJs Lambda function and CloudWatch to start/stop RDS instances in schedule interval.

Following are step by step guide with screenshot.

Download

Download the source code in zip format from following link and keep the zip file in local machine for further upload in Lambda function.

https://s3.ap-south-1.amazonaws.com/rdsstopstartcode/rds-lambda-stop-start.zip

Step 1: Create IAM Policy RDSManagement

First, let’s create policy, which is in AWS IAM console.

Go to Services -> Policies -> Create Policy -> Create Your Own Policy.
Let’s call it “RDSManagement”. Put the code given below to the ‘Policy Document’ field:

{
    "Version": "2012-10-17",
    "Statement": [        
        {
            "Action": [
                "rds:StopDBInstance",
                "rds:StartDBInstance"
            ],
            "Effect": "Allow",
            "Resource": "*"
        }
    ]
}

Press ‘Create policy’, Create Your Own Policy put name, description and JSON to the appropriate fields and press save. Eventually, you have to get something like on the screenshot below:

Screen1:-

Screen2:-

Screen3:-

Screen4:-

Step 2: Create Role LambdaRDSManagement

The second step is to create a role which will be associated with lambda function and allow it to manage RDS instances.

Navigate to Services -> Roles -> Create new Role.

Select ‘AWS Lambda’ in ‘AWS Service Role’ section. Search for the policy we created previously, select it, and press ‘Next’.

Screen5:-

Put “LambdaRDSManagement” as a Role Name, set some description and press ‘Create Role’.

Screen6:-

Screen7:-

Screen8:-

Step 3: Create Lambda Function ManageRDSInstances

Now we are ready to go and create lambda function which will manage our instances. Navigate to Services -> Lambda -> Create a Lambda function -> Blank function. Let’s call it ‘ManageRDSInstances’, select latest Node js 6.x as a runtime. Ignore lambda function code for now, and select ‘Choose an existing role’ in ‘Role’ field. You have to be able to find the previously created role in ‘Existing role’ field. Press ‘Next’ -> ‘Create function’.

Screen9:-

Now let’s upload our archive zip file previously downloaded in local machine to newly created lambda function.

Services -> Lambda -> ManageRDSInstances, and change Code entry type to ‘Upload a .ZIP file’. Press ‘Upload’, select your zip file and press ‘Save’.

Screen10:-

Now we need to configure test event: Actions -> Configure test event.

Screen11:-

Where “tomcatdb01” and “mysqlbiz” are testing RDS instances. After pressing Press ‘Save’ and ‘Test’, you will see that your RDS instances changed state to ‘Stopping’ and soon to ‘Stopped’.

After they are stopped, you can run the same test with action ‘start’, which will run change state of RDS instances to running.

Step 4: Create CloudWatch rule to schedule the event

The last thing is to set up CloudWatch rules to trigger these function on schedule.
Services -> CloudWatch -> Rules -> Create Rule.

Screen12:-

Select Schedule instead of default Event Pattern. Now you need to set up cron time. Keep in mind that time must be set in GMT timezone. For instance, to start instances every day at 8 am in cron time will look like this: ‘* 8 * * *’.
After you set cron time for waking up your instances, select Lambda function as a Target and pick your newly created lambda function. Then in Configure Input section put your JSON to Constant(JSON text) field:

{ "instances": ["some-test-instance-1",
"some-test-instance-2"], "action":"start" }

Screen13:-

Screen14:-

Now your instances will be woken on every morning at 8AM. Create a similar rule with correct cron time for stopping them, do not forget to change action from start to stop in the json:

{ "instances": ["some-test-instance-1",
"some-test-instance-2"], "action":"stop" }

So now we have schedule the Lambda functions to trigger at schedule interval to start and stop RDS instances and save significant cost in AWS billing.

Troubleshoot connection issue using standalone jdbc driver to oracle

Posted on October 26, 2017November 15, 2017 by debasis maity

Hi All

Please download following file from Google drive and keep in unix directory say /home/oracle

https://drive.google.com/open?id=0B8-3R318F39hQUJOcnVPMUdrNzg

The files to be downloaded:-

Please use ojdbc7.jar downloaded for 12c.For 11g,you can use ojdbc6.jar.

Please provide execute permissions

Now you can test connection using below:-

[oracle@SBJ1ITESBDBV01 ~]$ sh sqlScript_new.sh
2017-10-26 09:45:53.0

Please note you may get following error if you use ojdbc6.jar to connect 12c database.

[oracle@SBJ1ITESBDBV01 ~]$ sh sqlScript_new.sh
Exception in thread “main” java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:264)
at sqlconnect.main(sqlconnect.java:20)

Different restore and recovery scenario for PDB and CDB in Oracle 12c

Posted on October 25, 2017April 11, 2018 by debasis maity

Introduction

The next document will describe the steps to restore scenario for CDB and PDB using Data protector and RMAN

1.New SYSBACKUP privilege in RMAN 12c

1.1 create common user in CDB with sysbackup privilege.

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
 [oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 19:56:36 2017

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create user c##dba_backup identified by test123;

User created.

SQL> grant sysbackup to c##dba_backup;

Grant succeeded.

1.2 Backup whole database using sysbackup privilege

[oracle@rac1 ~]$ rman target '"c##dba_backup@orcl as sysbackup"'

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Dec 9 19:58:38 2017

target database Password:
connected to target database: ORCL (DBID=1489144156)

RMAN> backup database;

Starting backup at 09-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=105 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.278.961363313
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.277.961363267
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.280.961363369
input datafile file number=00008 name=+DATA/ORCL/DATAFILE/undotbs2.291.961364193
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.279.961363369
channel ORA_DISK_1: starting piece 1 at 09-DEC-17

2.Backup of CDB and PDB

—CDB backup–

export ORACLE_SID=ORCL1

[oracle@rac1 ~]$ rman target /

RMAN> backup database plus archivelog;

—CDB root backup—

RMAN> backup pluggable database "CDB$ROOT";

–Backup pluggable database—

RMAN>backup pluggable database oem;

RMAN>backup pluggable database oem plus archivelog;

3.Active database duplicate database and other new RMAN 12c feature

Active RMAN duplicate clone 12c using section size and compress backupset

4.Lost of temp file scenario for CDB and PDB

When you create a user, you can specify a temporary tablespace to be used by the user. If a temporary tablespace is not specified, the default tablespace for the PDB is used. If a default tablespace is not specified for the PDB, the temporary tablespace for the CDB is used.
If a temp file belonging to the CDB temporary tablespace is lost or damaged, and the user issuing the statement uses it, an error during the execution of SQL statements that require this temporary space occurs.

SQL> select * from dba_source order by 1;

274137 rows selected.

Execution Plan ———————————————————-

ERROR: ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 201 ORA-01110: data file 201: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_temp_do2ljro0_.tmp’

ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3

4.1 Create a temp file in CDB

SQL> alter tablespace temp add tempfile;

Tablespace altered.

NB: Temporary datafile will be automatically re-created once DB is started.

4.2 Temporary tablespace lost for PDB

[oracle@XXX ~]$ sqlplus system/oracle@ORA12CPD1

SQL> set autotrace traceonly;
SQL> select * from dba_source order by 1;

285357 rows selected.

Execution Plan
———————————————————-
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 203
ORA-01110: data file 203:
‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf
_temp_do2lvkg7_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

4.3 Add tempfile to temp tablespace in PDB and then drop corrupted temp file

SQL> alter tablespace temp add tempfile;

Tablespace altered.

SQL> alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_temp_do2lvkg7_.dbf’;

Tablespace altered.

SQL> select * from dba_source order by 1;

285357 rows selected.

NB: The PDB can open with missing temp files. If any of the temporary files do not exist when the PDB is opened , they are not created automatically. They are automatically recreated at CDB startup.

5. Lost of Control file in CDB (PDB does not contain control file)

Failure Scenario

[oracle@xxx ~]$ rm /u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
[oracle@xxx ~]$ sqlplus / as sysdba

SQL> select count(1) from dba_objects;

COUNT(1)
———-
72648

SQL> create table t1(c1 char(100));

Table created.

SQL> insert into t1 select 'A' from dual connect by rownum<100000;
 insert into t1 select 'A' from dual connect by rownum<100000
 *
 ERROR at line 1:
 ORA-00210: cannot open the specified control file
 ORA-00202: control file:
 '/u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl'
 ORA-27041: unable to open file
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

SQL> select count(1) from dba_objects;

COUNT(1)
———-
72649

5.1 Recover the CONTROLFILE now (The database was previously restored to old incarnation)

[oracle@XXX ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:02:13 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 PARENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 CURRENT 1499533 26-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

[oracle@XXX ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 14:58:14 2017

connected to target database: ORA12CP (DBID=1429060559, not open)
connected to recovery catalog database

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> recover database;
 5> alter database open resetlogs;
 6> }

allocated channel: dev_0
channel dev_0: SID=34 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2017 15:04:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf’

You need to restore and recover as you reset incarnation

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore database;
 5> recover database;
 6> alter database open resetlogs;
 7> }

allocated channel: dev_0
channel dev_0: SID=42 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2ljv4y_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2ljv4q_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2ljv4z_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_2:947689345:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_2:947689345:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00014 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_sysaux_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_5:947689374:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_5:947689374:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00013 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_system_do2lvqd7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_9:947689403:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_9:947689403:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00015 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_undotbs1_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_12:947689416:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_12:947689416:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_users_do2lhg0j_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_14:947689422:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_14:947689422:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00016 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_users_do2lvxsx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_16:947689433:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_16:947689433:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2017 15:13:38
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 2

[oracle@xxx ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:16:04 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 CURRENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 ORPHAN 1499533 26-JUN-17

RMAN> reset database to incarnation 3;

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> recover database;
 5> alter database open resetlogs;
 6> }

allocated channel: dev_0
channel dev_0: SID=34 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do3p7ltn_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_2_do3fzo1m_.log
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cv24b_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cv24b_.arc RECID=26 STAMP=947776675
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5cwg82_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5cwg82_.arc RECID=27 STAMP=947776718
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do3p7ltn_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_2_do3fzo1m_.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:25
Finished recover at 27-JUN-17

Statement processed
released channel: dev_0

5.2 Restore database control file current incarnation

[oracle@cdv1pradmdbv01 ~]$ rm /u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
[oracle@cdv1pradmdbv01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 15:26:11 2017

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown abort;
 ORACLE instance shut down.

SQL> startup nomount;
 ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes

[oracle@cdv1pradmdbv01 ~]$ rman target /

connected to target database: ORA12CP (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 27-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area/ORA12cP
database name (or database unique name) used for search: ORA12CP
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/autobackup/2017_06_27/o1_mf_s_947776764_do5cxx44_.bkp found in the recovery area
AUTOBACKUP search with format “%F” not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/autobackup/2017_06_27/o1_mf_s_947776764_do5cxx44_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/controlfile/o1_mf_do2ljh8f_.ctl
Finished restore at 27-JUN-17

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 recover database;
 }2> 3> 4> 5>

allocated channel: dev_0
channel dev_0: SID=35 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_1_do3fznnn_.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_1_do3fznnn_.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-JUN-17
released channel: dev_0

RMAN> alter database open RESETLOGS;

Statement processed

RMAN> list incarnation;

RMAN> alter pluggable database all open;

Statement processed

6.RESTORE TABLESPACE SCENERIO (CDB)

If the missing or corrupted data file belongs to the root container SYSTEM or UNDO tablespace, then the CDB instance will require shutdown, and a media recovery is required. In a RAC environment, you would shut down all instances of the CDB.
This means that all PDBs will be closed.
The CDB must be mounted before restoring and recovering the missing root data file.
After the root data file is recovered, open the CDB and all PDBs.

6.1 RESTORE SYSTEM AND UNDO TABLESPACE FOR ROOT(CDB)

[oracle@cdv1pradmdbv01 ~]$ sqlplus / as sysdba

SQL> shutdown immediate;

ORACLE instance shut down.
 SQL> startup mount;

ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.

[oracle@cdv1pradmdbv01 ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:37:49 2017

connected to target database: ORA12CP (DBID=1429060559, not open)
connected to recovery catalog database

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace 'UNDOTBS1';
 5> recover tablespace 'UNDOTBS1';
 6> }

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5f37t5_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5f37t5_.arc RECID=37 STAMP=947777960
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5f4nw9_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5f4nw9_.arc RECID=38 STAMP=947778004
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-17
released channel: dev_0

RMAN> alter database open;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN> alter pluggable database all open;

Statement processed

NB: If the missing or corrupted file belongs to root container SYSTEM or UNDO tablespace ,then CDB instance needs to shutdown and media recover required. All PDBs will be shutdown also.
After root datafile recovered, CDB and PDB could be opened.

6.2 RESTORE SYSAUX TABLESPACE FOR ROOT IN CDB

There is no need to stop CDB and PDB.This recovery can be done when database is online

[oracle@cdv1pradmdbv01 ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 16:10:13 2017

connected to target database: ORA12CP (DBID=1429060559)
connected to recovery catalog database

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace 'SYSAUX';
 5> recover tablespace 'SYSAUX';
 6> alter tablespace SYSAUX online;
 7> }

allocated channel: dev_0
channel dev_0: SID=58 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5h134y_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5h134y_.arc RECID=42 STAMP=947779940
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5h2hq4_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5h2hq4_.arc RECID=43 STAMP=947779983
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-17
starting full resync of recovery catalog
full resync complete

Statement processed
released channel: dev_0

7.RESTORE TABLESPACE SCENERIO (PDB)

7.1 Restore missing system tablespace in PDB

If the data file missing or corrupted belongs to a PDB and more specifically to the SYSTEM tablespace, the CDB must be closed unless the PDB is already closed.
A pluggable database or tablespace or data file media recovery is required before the PDB can be reopened.
If the PDB was closed at the time issue, the users can still work in other PDBs during the PDB recovery.
If the PDB was still opened at the time issue, users cannot work at all in any other PDB because the CDB needs to be shut down and mounted only.
The recovery must be issued from root.

Option 1:- Recover whole PDB database .CDB needs to be in Mount state.

[oracle@cdv1pradmdbv01 ~]$ sqlplus

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

SQL> startup mount;
 ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@cdv1pradmdbv01 ~]$ rman target /

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore pluggable database ORA12CPD1;
 5> recover pluggable database ORA12CPD1;
 6> alter database open;
 7> alter pluggable database ORA12CPD1 open;
 8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5l1gjw_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5l1gjw_.arc RECID=44 STAMP=947783023
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5l2t4x_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5l2t4x_.arc RECID=45 STAMP=947783066
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:06
Finished recover at 27-JUN-17

RMAN> alter database open;

Statement processed

RMAN> alter pluggable database ORA12CPD1 open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/27/2017 17:05:18
ORA-65019: pluggable database ORA12CPD1 already open

Option2: Only recover the corrupted system tablespace. CDB needs to be in mount state.

[oracle@cdv1pradmdbv01 ~]$ rm /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
[oracle@cdv1pradmdbv01 ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 17:09:48 2017

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
 ORA-01116: error in opening database file 9
 ORA-01110: data file 9: '/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf'
 ORA-27041: unable to open file
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 17:11:18 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace ORA12CPD1:system;
 5> recover tablespace ORA12CPD1:system;
 6> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5llvyw_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5llvyw_.arc RECID=46 STAMP=947783580
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5lnb83_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5lnb83_.arc RECID=47 STAMP=947783626
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:04
Finished recover at 27-JUN-17
released channel: dev_0

database dismounted
Oracle instance shut down

RMAN> shutdown immediate;

RMAN> startup;

connected to target database (not started)
Oracle instance started
database mounted
database opened

8.Point in time recovery of pluggable database (PDB)

If you need to recover a PDB database to a point in time in the past beyond flashback retention, then in this case, flashback is not possible, therefore a point-in-time recovery is necessary.
Recovering a PDB to a point-in-time does not affect all parts of the CDB: the whole CDB is still opened and therefore all other PDBs are opened. After recovering a PDB to a specified point-in-time, when you open the PDB using the RESETLOGS option, a new incarnation of the PDB is created. The PDB RESETLOGS does not perform a RESETLOGS for the CDB.

• A PDB record in the control file is updated.
• Each redo log record carries PDB id in the redo header. This is how recovery knows

which redo applies to which PDB. Redo logs are shared by all PDBs; redo from each PDB is written to a single set of redo logs.
Conceptually a PDB resetlogs is similar to a database resetlogs.
After recovery, the old backup of the PDB remains valid and can be used if a media failure occurs. After restoring/recovering a PDB to a past point in time, one cannot open the PDB read only. PDB read-write open through resetlogs is required.
A PDB incarnation is a subincarnation of the CDB. For example, if the CDB is incarnation 5,and a PDB is incarnation 3, then the fully specified incarnation number of the PDB is (5, 3). The nitial incarnation of a PDB is 0. To view the incarnation of a PDB, query the $PDB_INCARNATION view.

CDB and other PDB remain un affected.

RMAN> run {
 set until time = "TO_DATE('06/27/2017 17:34:00','MM/DD/YYYY HH24:MI:SS')" ;
 2> 3> allocate channel 'dev_0' type 'sbt_tape'
 4> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 5> restore pluggable database ORA12CPD1;
 6> recover pluggable database ORA12CPD1 auxiliary destination '/u01/app/oracle/oradata';
 7> alter pluggable database ORA12CPD1 open resetlogs;
 8> }

executing command: SET until clause

allocated channel: dev_0
channel dev_0: SID=8 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

skipping datafile 9; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do5lkhfq_.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5ndy7b_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5nns56_.arc RECID=50 STAMP=947785690
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5np4dk_.arc RECID=51 STAMP=947785732
media recovery complete, elapsed time: 00:00:06
Finished recover at 27-JUN-17

Statement processed
released channel: dev_0

Restore Database with PDB PITR Oracle 12c

Posted on October 25, 2017April 11, 2018 by debasis maity

1 Introduction

The next document will describe the steps to restore database with CDB and PDB point in time using Data protector and RMAN.

2. Restore Database with PDB PITR using command prompt (Best/Preferred way)

[oracle@orclbkp admin]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 21:32:29 2017

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=orclbkp.tdeprdcl.internal)';
 4> set until time "to_date('06/26/17 21:07:41','MM/DD/YY HH24:MI:SS')";
 restore database;
 5> 6> recover database;
 alter database open resetlogs;
 7> 8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=40 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 26-JUN-17

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2ljv4y_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2ljv4q_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2ljv4z_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_2:947689345:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_2:947689345:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00014 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_sysaux_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_5:947689374:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_5:947689374:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00013 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_system_do2lvqd7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_9:947689403:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_9:947689403:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00015 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_undotbs1_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_12:947689416:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_12:947689416:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_users_do2lhg0j_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_14:947689422:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_14:947689422:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00016 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_users_do2lvxsx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_16:947689433:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_16:947689433:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_2_do3fxw63_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_2_do3fxw63_.arc RECID=4 STAMP=947713277
media recovery complete, elapsed time: 00:00:10
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_3_do3fzm9t_.arc RECID=5 STAMP=947713331
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

Database point in time recovery using Data protector and RMAN

Posted on October 25, 2017November 2, 2017 by debasis maity

1 Introduction

The next document will describe the steps to restore database point in time using Data protector and RMAN

2. Restore using data protector console

2.1. Restore screen shot

3. Restore Database PITR using command prompt (Best/Preferred way)

3.1 Restore database 1st incarnation (No need to reset incarnation)

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:38:28 2017

connected to target database: BKPTST (not mounted)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/23/2017 17:38:32
ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

allocated channel: dev_0
channel dev_0: SID=36 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

creating datafile file number=5 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.2 Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
restore database;
5> 6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:48:01 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 BKPTST 2013020542 PARENT 1 26-JAN-17
2 2 BKPTST 2013020542 PARENT 1408558 22-JUN-17
3 3 BKPTST 2013020542 CURRENT 1511521 23-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> exit

Restoration will now work fine

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:49:33 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
}8>

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: reading from backup piece bkptst_new<BKPTST_22:947418639:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_22:947418639:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece bkptst_new<BKPTST_26:947418651:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_26:947418651:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.3 Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 18:56:47 2017

connected to target database: BKPTST (DBID=2013020542, not open)

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

RMAN> list incarnation;

RMAN> reset database to incarnation 2;

database reset to incarnation 2

Now restore again

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

NB: If you need to restore backup older than (15 days or control file keep record days) ,then you need to connect to recovery catalog as well.

4 . Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

Restore Database

All full backup will be restored

Recover Database

All incremental backup will be restored

All archive log backup will be restored

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Sun Jun 25 19:21:28 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
allocate channel ‘dev_0’ type ‘sbt_tape’
2> 3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/25/17 10:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
recover database;
6> 7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 25-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 25-JUN-17

Starting recover at 25-JUN-17
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_46:947503948:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_46:947503948:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:03

starting media recovery

channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=4
channel dev_0: reading from backup piece bkptst_new<BKPTST_49:947503970:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_49:947503970:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=5
channel dev_0: reading from backup piece bkptst_new<BKPTST_50:947503977:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_50:947503977:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece New3<BKPTST_53:947531995:1>.dbf
channel dev_0: piece handle=New3<BKPTST_53:947531995:1>.dbf tag=TAG20170624T191955
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=8
channel dev_0: reading from backup piece bkptst_new<BKPTST_61:947590300:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_61:947590300:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=9
channel dev_0: reading from backup piece bkptst_new<BKPTST_62:947590308:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_62:947590308:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:06
Finished recover at 25-JUN-17

Statement processed
released channel: dev_0

Database corrupt block recovery using RMAN

Posted on October 25, 2017November 25, 2017 by debasis maity

1 Introduction

The next document will describe the steps to recover corrupt block using RMAN

2. Recover block if datafile header is corrupted

2.1. Please check the physical corruption

[oracle@cdv1pradmdbv01 datafile]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 12:55:06 2017

connected to target database: BKPTST (DBID=2013020542)

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/26/2017 12:55:10
RMAN-06056: could not access datafile 5

2.2. Restore and recover the particular tablespace (For example test1)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> restore tablespace test1;
5> recover tablespace test1;
6> alter tablespace test1 online;
7> }

released channel: ORA_DISK_1
allocated channel: dev_0
channel dev_0: SID=28 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 26-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

2.3. Now please validate after restore and recover (For example test1)

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 12673 12800 1510201
File Name: /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

Finished backup at 26-JUN-17

3. Recover corrupt block (Logical corruption)

3.1 Detect the logical corruption

RMAN> backup database;
…
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/23/2017 11:27:36
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/EAMDB01/DATAFILE/system.266.926328081

$ dbv file=+DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY: Release 12.1.0.2.0 – Production on Fri Jun 23 11:33:22 2017

DBVERIFY – Verification starting : FILE = +DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY – Verification complete

Total Pages Examined : 262144
Total Pages Processed (Data) : 70470
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15803
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6573
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 169298
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

3.2 Validate using RMAN

Validate the datafile using RMAN:RMAN> backup validate check logical datafile 1;2) After completion of RMAN command, provide output from Sqlplus on connection AS SYSDBA:select * from v$database_block_corruption ;set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
– greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks – 1
AND e.block_id + e.blocks – 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||’ Segment Header’ description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
– greatest(f.block_id, c.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks – 1
AND f.block_id + f.blocks – 1 >= c.block#
order by file#, corr_start_block#;

SQL> select * from v$database_block_corruption ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
———- ———- ———- —————— ——— ———-
5 341508 1 0 CORRUPT 0
3 117889 1 0 FRACTURED 0
4 456056 1 0 FRACTURED 0
…

OWNER SEGMENT_TYPE SEGMENT_NAME
——————————————————————————————————————————– —————— ——————————————————————————————————————————–
PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
——————————————————————————————————————————– ———- —————– ————— —————- ————————
SYS INDEX PARTITION WRH$_SYSSTAT_PK
WRH$_SYSSTA_2631080415_9515 3 117889 117889 1 FRACTURED
4 456056 456056 1 Free Block
5 341508 341508 1 Free Block

3.3 Repair corrupt block using RMAN

ACTION PLAN
============

1) Repair the affected blocks using RMAN:

RMAN> blockrecover corruption list;

2) Validate again to repopulate view v$database_block_corruption

RMAN> backup validate check logical datafile1;

3) Verify the output again from sqlplus to make sure all is clear:

select * from v$database_block_corruption ;

set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
– greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks – 1
AND e.block_id + e.blocks – 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||’ Segment Header’ description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
– greatest(f.block_id, c.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks – 1
AND f.block_id + f.blocks – 1 >= c.block#
order by file#, corr_start_block#;

Clone point in time Recovery using Dataprotector and RMAN for single instance to single instance

Posted on October 25, 2017December 4, 2017 by debasis maity

1.Login to source database

2. Create temporary pfile from spfile of source database

2.1. create temporary pfile from spfile

sqlplus / as sysdba

SQL>create pfile=’/tmp/inittest.ora’ from spfile;

2.2. Change parameters in init parameter to fit target database

Please change all source database name to target database name and create audit_file_dest and control_files loction

BKPTSTC.__data_transfer_cache_size=0

BKPTSTC.__db_cache_size=939524096

BKPTSTC.__inmemory_ext_roarea=0

BKPTSTC.__inmemory_ext_rwarea=0

BKPTSTC.__java_pool_size=16777216

BKPTSTC.__large_pool_size=33554432

BKPTSTC.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

BKPTSTC.__pga_aggregate_target=469762048

BKPTSTC.__sga_target=1375731712

BKPTSTC.__shared_io_pool_size=67108864

BKPTSTC.__shared_pool_size=301989888

BKPTSTC.__streams_pool_size=0

*.audit_file_dest=’/u01/app/oracle/admin/BKPTSTC/adump’

*.audit_trail=’db’

*.compatible=’12.2.0′

*.control_files=’/u01/app/oracle/oradata/BKPTSTC/control01.ctl’,’/u01/app/oracle/oradata/BKPTSTC/control02.ctl’

*.db_block_size=8192

*.db_create_file_dest=’/u01/app/oracle/oradata’

*.db_name=’BKPTSTC’

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=BKPTSTCXDB)’

*.local_listener=’LISTENER_BKPTSTC’

*.nls_language=’AMERICAN’

*.nls_territory=’AMERICA’

*.open_cursors=300

*.pga_aggregate_target=436m

*.processes=300

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=1305m

*.undo_tablespace=’UNDOTBS1′

3. Startup no mount the database using temporary parameter file.

[oracle@xxx tmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 26 20:07:11 2017

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile=’/tmp/inittest.ora’;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
SQL> exit

4.Clone the database after connecting to source, catalog and auxiliary

[oracle@xxx tmp]$ rman target sys/oracle@bkptst catalog rco/rco@botdbadb auxiliary /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 20:09:06 2017

connected to target database: BKPTST (DBID=2013020542)
connected to recovery catalog database
connected to auxiliary database: BKPTSTC (not mounted)

RMAN> run {
set until time = “TO_DATE(’06/26/2017 11:59:00′,’MM/DD/YYYY HH24:MI:SS’)” ;
2> 3> configure auxiliary channel 1 device type sbt parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> DUPLICATE TARGET DATABASE TO BKPTSTC
5> logfile
6> group 1 (‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’) size 512M,
7> group 2 (‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’) size 512M,
8> group 3 (‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’) size 512M;
9> }

executing command: SET until clause

new RMAN configuration parameters:
CONFIGURE AUXILIARY CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting Duplicate Db at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=36 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
set until scn 1732640;
sql clone “alter system set db_name =
”BKPTST” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”BKPTSTC” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set db_name = ”BKPTST” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”BKPTSTC” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

Starting restore at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=35 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2013020542-20170626-00
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2013020542-20170626-00 tag=TAG20170626T113217
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/BKPTSTC/control01.ctl
output file name=/u01/app/oracle/oradata/BKPTSTC/control02.ctl
Finished restore at 26-JUN-17

database mounted
datafile 7 not processed because file is offline

contents of Memory Script:
{
set until scn 1732640;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
restore
clone database
skip forever tablespace “USERS” ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1

channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy RECID=21 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k)
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy RECID=20 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n)
channel ORA_AUX_DISK_1: restoring datafile 00004
input datafile copy RECID=19 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r)
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy RECID=17 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q
destination for restore of datafile 00005: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q)
failover to previous backup

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

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

datafile 1 switched to datafile copy
input datafile copy RECID=37 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=38 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=39 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=40 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
set until time “to_date(‘JUN 26 2017 11:59:00’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35

Executing: alter database datafile 7 offline drop
starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf thread=1 sequence=1
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf thread=1 sequence=2
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:28
Finished recover at 26-JUN-17
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”BKPTSTC” 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 = ”BKPTSTC” 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 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “BKPTSTC” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’ ) SIZE 512 M ,
GROUP 2 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’ ) SIZE 512 M ,
GROUP 3 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’ ) SIZE 512 M
DATAFILE
‘/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy “/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf RECID=1 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf RECID=2 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf RECID=3 STAMP=947708374

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

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

database opened

PSU patching for 12.1.0.2:-

Oracle 12.1.0.2 proactive DB patching . Patch number:-26635880.We needed to apply in RHEL 7 64-bit.

Please download this patch from Oracle support.

If this change permission is not done,you may get error mentioned in section 10.A

Copy the opatch binary using root user to $ORACLE_HOME and $GRID_HOME.

Then please keep backup of old OPatch directory.

Unzip the binary in the $ORACLE_HOME and $GRID_HOME.

Change owner of the folder OPatch.

Please check lsinv command is working fine and note opatch version updated.

For Oracle user:-

For Database home, as home user:

For Grid user:-

For Grid Infrastructure Home, as home user:

List of patch to be applied for GI Home:-

For Grid Infrastructure Home, as home user:

Create file /tmp/patch_list_gihome.txt with the following content:

List of patch to be applied for ORACLE Home:-

For Database home, as home user:

To patch only the GI home:

Session Waits:-

Recovery Progress:-

Introduction

Download

Step 1: Create IAM Policy RDSManagement

Step 2: Create Role LambdaRDSManagement

Step 3: Create Lambda Function ManageRDSInstances

Step 4: Create CloudWatch rule to schedule the event

Introduction

The next document will describe the steps to restore scenario for CDB and PDB using Data protector and RMAN

1.New SYSBACKUP privilege in RMAN 12c

1.1 create common user in CDB with sysbackup privilege.

1.2 Backup whole database using sysbackup privilege

2.Backup of CDB and PDB

—CDB backup–

—CDB root backup—

–Backup pluggable database—

3.Active database duplicate database and other new RMAN 12c feature

4.Lost of temp file scenario for CDB and PDB

ERROR: ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 201 ORA-01110: data file 201: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_temp_do2ljro0_.tmp’

ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3

4.1 Create a temp file in CDB

NB: Temporary datafile will be automatically re-created once DB is started.

4.2 Temporary tablespace lost for PDB

4.3 Add tempfile to temp tablespace in PDB and then drop corrupted temp file

SQL> alter tablespace temp add tempfile;

SQL> alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_temp_do2lvkg7_.dbf’;

SQL> select * from dba_source order by 1;

NB: The PDB can open with missing temp files. If any of the temporary files do not exist when the PDB is opened , they are not created automatically. They are automatically recreated at CDB startup.

5. Lost of Control file in CDB (PDB does not contain control file)

Failure Scenario

5.1 Recover the CONTROLFILE now (The database was previously restored to old incarnation)

You need to restore and recover as you reset incarnation

5.2 Restore database control file current incarnation

6.RESTORE TABLESPACE SCENERIO (CDB)

6.1 RESTORE SYSTEM AND UNDO TABLESPACE FOR ROOT(CDB)

ORACLE instance started.

NB: If the missing or corrupted file belongs to root container SYSTEM or UNDO tablespace ,then CDB instance needs to shutdown and media recover required. All PDBs will be shutdown also. After root datafile recovered, CDB and PDB could be opened.

6.2 RESTORE SYSAUX TABLESPACE FOR ROOT IN CDB

There is no need to stop CDB and PDB.This recovery can be done when database is online

7.RESTORE TABLESPACE SCENERIO (PDB)

7.1 Restore missing system tablespace in PDB

Option 1:- Recover whole PDB database .CDB needs to be in Mount state.

Option2: Only recover the corrupted system tablespace. CDB needs to be in mount state.

RMAN> shutdown immediate;

RMAN> startup;

8.Point in time recovery of pluggable database (PDB)

• A PDB record in the control file is updated. • Each redo log record carries PDB id in the redo header. This is how recovery knows

CDB and other PDB remain un affected.

1 Introduction

2. Restore Database with PDB PITR using command prompt (Best/Preferred way)

2. Restore using data protector console

3. Restore Database PITR using command prompt (Best/Preferred way)

3.1 Restore database 1st incarnation (No need to reset incarnation)

3.2 Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

3.3 Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

4 . Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

1 Introduction

2. Recover block if datafile header is corrupted

2.1. Please check the physical corruption

NB: If the missing or corrupted file belongs to root container SYSTEM or UNDO tablespace ,then CDB instance needs to shutdown and media recover required. All PDBs will be shutdown also.
After root datafile recovered, CDB and PDB could be opened.

• A PDB record in the control file is updated.
• Each redo log record carries PDB id in the redo header. This is how recovery knows