Oracle DBA & Data Science Enthusiast: June 2015

Monday 15 June 2015

TNS-00525: Insufficient privilege for operation

[oracle@DB11G2 .oracle]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-JUN-2015 09:45:41

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

Starting /data01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /data01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /data01/app/oracle/diag/tnslsnr/DB11G2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB11G2.localdomain)(PORT=1521)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12555: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00525: Insufficient privilege for operation
Linux Error: 1: Operation not permitted

Listener failed to start. See the error message(s) above...

Cause:

1) Ensure that /tmp/.oracle or /var/tmp/.oracle directory exists.

2) Confirm that the DBA user who is trying to start the listener has adequate read and write permissions on the directory specified above. The permissions should be 777.

3) If the /tmp directory has reached full capacity, this would cause the listener to fail to write the socket files.

Solution

To implement the solution, please use the following example:

1. cd /var/tmp

2. Check the whether the .oracle directory exists:

cd .oracle

3. If the directory does not exist, request the System Administrator create the directory and set the ownership as root:root with the permissions set to 01777

mkdir /var/tmp/.oracle
chmod 01777 /var/tmp/.oracle
chown root /var/tmp/.oracle
chgrp root /var/tmp/.oracle

[oracle@DB11G2 admin]$ ls -lrtha /var/tmp/
total 12K
drwxr-xr-x. 22 root root 4.0K May 2 04:40 ..
drwxrwxrwt. 3 root root 4.0K Jun 7 08:06 .
drwxrwxrwt. 2 root root 4.0K Jun 15 09:54 .oracle

4. start listener now .

[oracle@DB11G2 network]$ cd /tmp/.oracle/
[oracle@DB11G2 .oracle]$ ls -l
total 0

If my case there were few files under /var/tmp/.oracle with privilege different from oracle . It seems someone tried to start listener from root or non-previleged
user .So remove those files & start listener again .

[oracle@DB11G2 .oracle]$ cd /var/tmp/.oracle
[oracle@DB11G2 .oracle]$ ls -l
total 0
srwxrwxrwx. 1 oracle oinstall 0 Jun 9 08:39 s#7003.1
srwxrwxrwx. 1 oracle oinstall 0 Jun 9 08:39 s#7003.2
srwxrwxrwx. 1 200 200 0 May 2 07:55 s#9991.1
srwxrwxrwx. 1 200 200 0 May 2 07:55 s#9991.2
srwxrwxrwx. 1 200 200 0 May 2 07:55 sEXTPROC1521

rm -rf s#9991.1
rm -rf s#9991.2
rm -rf sEXTPROC1521

[oracle@DB11G2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-JUN-2015 09:54:20

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

Starting /data01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /data01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /data01/app/oracle/diag/tnslsnr/DB11G2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB11G2.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 15-JUN-2015 09:54:20
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /data01/app/oracle/diag/tnslsnr/DB11G2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB11G2.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@DB11G2 admin]$

[oracle@DB11G2 .oracle]$ ls -lrth
total 0
srwxrwxrwx. 1 oracle oinstall 0 Jun 15 09:47 s#2986.1
srwxrwxrwx. 1 oracle oinstall 0 Jun 15 09:47 sEXTPROC1521
srwxrwxrwx. 1 oracle oinstall 0 Jun 15 09:47 s#2986.2
[oracle@DB11G2 .oracle]$

List and Delete archivelogs using RMAN

List Archivelog commands :
====================

RMAN>list archivelog all;

RMAN>list copy of archivelog until time 'SYSDATE-5';

RMAN>list copy of archivelog from time 'SYSDATE-5'

RMAN>list copy of archivelog from time 'SYSDATE-5' until time 'SYSDATE-2';

RMAN>list copy of archivelog from sequence 500;

RMAN>list copy of archivelog until sequence 1500;

RMAN>list copy of archivelog from sequence 500 until sequence 1500;

Delete Archivelog commands
=======================

RMAN>delete archivelog all;

RMAN>delete archivelog until time 'SYSDATE-5';

RMAN>delete archivelog from time 'SYSDATE-5'

RMAN>delete archivelog from time 'SYSDATE-5' until time 'SYSDATE-2';

RMAN>delete archivelog from sequence 500;

RMAN>delete archivelog until sequence 1500;

RMAN>delete archivelog from sequence 500 until sequence 1500;

RMAN>delete noprompt archivelog until time 'SYSDATE-5';

Friday 12 June 2015

Rollback a PSU patch ( Database Patch Set Update 11.2.0.3.14)

[oracle@DB11G 20299017]$ pwd
/home/oracle/psu/20299017

[oracle@DB11G ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@DB11G ~]$ which opatch
/data01/app/oracle/product/11.2.0/db_1/OPatch/opatch
[oracle@DB11G ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.

Oracle Home : /data01/app/oracle/product/11.2.0/db_1
Central Inventory : /data01/app/oraInventory
from : /data01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.3.0
Log file location : /data01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-06-12_01-35-10AM_1.log

Lsinventory Output file location : /data01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-06-12_01-35-10AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: DB11G.localdomain
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 20299017 : applied on Tue Jun 09 07:34:44 GMT-05:00 2015
Unique Patch ID: 18588265
Patch description: "Database Patch Set Update : 11.2.0.3.14 (20299017)"
Created on 2 Mar 2015, 21:45:38 hrs PST8PDT
Sub-patch 19769496; "Database Patch Set Update : 11.2.0.3.13 (19769496)"
Sub-patch 19121548; "Database Patch Set Update : 11.2.0.3.12 (19121548)"
Sub-patch 18522512; "Database Patch Set Update : 11.2.0.3.11 (18522512)"
Sub-patch 18031683; "Database Patch Set Update : 11.2.0.3.10 (18031683)"
Sub-patch 17540582; "Database Patch Set Update : 11.2.0.3.9 (17540582)"
Sub-patch 16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch 16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
13593999, 10350832, 19433746, 14138130, 12919564, 14198511, 13561951
13588248, 13080778, 20134036, 13804294, 16710324, 18031683, 12873183
16992075, 14193240, 14472647, 12880299, 13369579, 14799269, 13840704
14409183, 13492735, 14263036, 12857027, 13496884, 14263073, 16038929
13834436, 13015379, 17748833, 13732226, 16563678, 13866822, 20134034
13742434, 13944971, 12950644, 17748831, 12899768, 16929165, 16272008
13063120, 14613900, 13958038, 13503204, 13972394, 11877623, 17088068
13072654, 12395918, 16710753, 13429702, 13814739, 17343514, 13649031
13981051, 10256843, 15981698, 13901201, 12797765, 17333200, 19211724
12923168, 16761566, 13384182, 16279401, 13466801, 15996344, 14207163
13596581, 18673304, 13724193, 11063191, 13642044, 12940637, 19915271
12595606, 18641419, 14052871, 9163477, 15931756, 18262334, 13945708
12797420, 14123213, 13041324, 12865902, 15869211, 14003090, 16314468
16019955, 11708510, 17865671, 13026410, 14637368, 13737746, 13742438
15841373, 16347904, 16088176, 15910002, 19517437, 19827973, 16362358
16505333, 14398795, 14182835, 13579992, 11883252, 16344871, 10182005
10400244, 13742436, 14275605, 19197175, 9858539, 20477071, 14841812
16338983, 9703627, 13483354, 14393728, 14207317, 17165204, 20477069
12764337, 16902043, 14459552, 14191508, 14588746, 12964067, 19358317
20477440, 12780983, 12583611, 14383007, 14546575, 13476583, 15862016
13489024, 12985237, 17748830, 19554106, 14088346, 13448206, 19458377
16314466, 13419660, 18139695, 12591399, 14110275, 13430938, 13467683
17767676, 14548763, 19638161, 13424216, 12834027, 13632809, 13853126
13377816, 13036331, 14727310, 9812682, 12320556, 16747736, 13584130
16175381, 17468141, 12829021, 14138823, 15862019, 12794305, 14546673
12791981, 13503598, 13787482, 10133521, 12744759, 13399435, 18641461
19433747, 14023636, 13553883, 14762511, 9095696, 14343501, 12977562
13860201, 13257247, 14176879, 13783957, 16014985, 14480675, 12312133
13559697, 13146182, 16306019, 12974860, 9706792, 12940620, 13098318
13773133, 15883525, 16794244, 13340388, 13528551, 13366202, 12894807
13259364, 12747437, 13454210, 12748240, 13385346, 15987992, 13923995
16101465, 14571027, 13582702, 12784406, 13907462, 19769496, 13493847
13035804, 13857111, 13544396, 16710363, 10110625, 20134033, 14128555
12813641, 8547978, 14226599, 17478415, 17050888, 16923127, 17333197
9397635, 14007968, 13912931, 12693626, 12925089, 14189694, 17761775
12815057, 16721594, 13332439, 20477068, 19972198, 14038787, 11071989
14207902, 12596444, 14062796, 12913474, 20299010, 14390252, 13840711
13370330, 16314470, 14062794, 13358781, 12960925, 17333202, 9659614
13699124, 14546638, 13936424, 9797851, 19433745, 16794240, 14301592
13338048, 12938841, 12620823, 12656535, 12678920, 13719292, 14488943
14062792, 16850197, 14791477, 13807411, 16794238, 13250244, 12594032
15862022, 14098509, 15826962, 12612118, 9761357, 18096714, 19854461
14053457, 18436647, 13918644, 13527323, 10625145, 18173595, 12797620
19289642, 15862020, 13910420, 12780098, 13696216, 14774091, 14841558
10263668, 13849733, 16794242, 16944698, 15862023, 16056266, 13834065
20134035, 13853654, 14351566, 13723052, 18173593, 14063280, 13011409
13566938, 13737888, 13624984, 16024441, 17333199, 13914613, 17540582
14258925, 14222403, 14755945, 13645875, 12571991, 13839641, 14664355
12998795, 14469008, 13719081, 13361350, 14188650, 17019974, 13742433
14508968, 16314469, 16368108, 12905058, 6690853, 13647945, 16212405
12849688, 18641451, 13742435, 13464002, 18681866, 12879027, 13534412
18522512, 12585543, 12747740, 12535346, 13878246, 13790109, 16382448
12588744, 13916549, 13786142, 12847466, 13855490, 13551402, 12582664
19972199, 13871316, 14262913, 14657740, 17332800, 14558880, 14695377
13612575, 12912137, 13484963, 12387467, 14163397, 17437634, 13772618
19006849, 16694777, 13070939, 15994107, 14369664, 12391034, 13605839
12588237, 16279211, 16314467, 12945879, 15901852, 17762296, 14692762
12976376, 7276499, 12755231, 13680405, 13742437, 14589750, 14318397
11868640, 14644185, 13326736, 19309466, 13596521, 13001379, 12898558
13099577, 17752121, 13911711, 9873405, 18673325, 16372203, 16344758
11715084, 9547706, 16231699, 14040433, 12662040, 12617123, 14406648
17748832, 16530565, 12845115, 16844086, 13354082, 17748834, 13794550
13397104, 19537916, 13913630, 16524926, 16462834, 12983611, 13550185
13810393, 14121009, 13065099, 11840910, 13903046, 15862017, 13572659
16294378, 13718279, 13657605, 17716305, 14480676, 13632717, 14668670
14063281, 14158012, 13736413, 13420224, 13812031, 12646784, 16299830
18440047, 14512189, 10359307, 12755116, 14035825, 17230530, 13616375
13366199, 13427062, 18673342, 12861463, 15862021, 13092220, 17721717
13043012, 16619892, 13685544, 18325460, 13499128, 15862018, 19727057
13839336, 13866372, 13561750, 12718090, 13848402, 13725395, 12401111
5144934, 12796518, 13362079, 12917230, 12614359, 13042639, 14408859
13923374, 11732473, 14220725, 12621588, 13524899, 14480674, 14751895
13916709, 14781609, 14076523, 15905421, 12731940, 13343438, 14205448
17748835, 15853081, 17082364, 14127231, 14273397, 16844448, 14467061
12971775, 16864562, 20074391, 14489591, 14497307, 13872868, 12748538
10242202, 14230270, 13931044, 13686047, 16382353, 14095982, 17333203
19121548, 13591624, 14523004, 13440516, 16794241, 13499412, 13035360
14062795, 12411746, 13040943, 12905053, 13843646, 20296213, 18173592
16794243, 13477790, 14841409, 14609690, 14062797, 13059165, 12959852
12345082, 16703112, 13890080, 17333198, 16048375, 16450169, 12658411
13780035, 14062793, 19271438, 19259446, 13038684, 18740215, 16742095
13742464, 14052474, 13066936, 13060271, 13911821, 13457582, 7509451
19710542, 13791364, 12821418, 13502183, 13705338, 15856660, 14237793
16794239, 13554409, 15862024, 13103913, 13645917, 12772404

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

OPatch succeeded.

[oracle@DB11G 20299017]$ opatch rollback -id 20299017
Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.

Oracle Home : /data01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /data01/app/oraInventory
from : /data01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.3.0
Log file location : /data01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/20299017_Jun_12_2015_01_12_38/rollback2015-06-12_01-12-37AM_1.log

Patches will be rolled back in the following order:
20299017 19769496 19121548 18522512 18031683 17540582 16902043 16619892 16056266 14727310 14275605 13923374 13696216 13343438
The following patch(es) will be rolled back: 20299017 19769496 19121548 18522512 18031683 17540582 16902043 16619892 16056266 14727310 14275605 13923374 13696216 13343438
Sub-patches of a composite series are being rolled back. The system will be returned to a state where all subpatches are rolled back.
The following bug fixes will be removed: 20296213,14692762,20477068,20477069,17762296,13840711,20477440,19915271,20299010,20477071,19358317,18641461,20134034,15856660,20134035,18641451,19827973,20074391,19854461,19309466,19197175,16923127,19972198,18440047,19972199,18436647,11883252,20134033,19769496,20134036,19006849,9812682,19433747,11732473,16710753,19211724,12320556,19271438,13871316,19727057,19289642,18673304,19259446,12588237,19554106,19121548,19537916,12387467,16505333,18673342,19433745,5144934,17050888,9163477,18673325,12591399,13647945,12595606,12411746,10256843,15826962,12596444,16524926,18262334,14182835,19517437,10182005,19710542,18641419,19458377,19433746,19638161,14799269,16929165,14781609,18740215,12940637,12747740,18096714,18325460,14558880,7276499,14408859,16992075,10110625,13366199,14138823,14123213,13066936,14193240,14657740,13794550,15994107,13840704,17165204,14588746,18522512,13911711,13834436,13429702,13931044,16088176,15901852,13839336,13866372,17767676,13503204,15931756,13361350,18681866,13736413,13849733,12614359,13918644,16048375,14489591,12945879,14237793,14406648,14609690,12985237,17716305,14774091,18031683,16747736,13719292,12977562,12905053,14098509,17865671,14508968,14052871,13901201,13912931,12797620,18173592,13853654,14158012,13499412,16272008,18173595,13878246,12813641,17468141,10359307,13853126,13839641,13872868,16101465,13477790,18139695,13424216,13259364,13146182,18173593,17721717,13783957,13596581,15981698,12898558,16462834,12571991,15883525,14343501,14207902,17748833,16844086,16038929,13369579,17019974,16450169,16347904,17761775,17748835,17478415,14497307,15987992,9797851,17343514,17748831,14488943,16563678,14318397,16338983,13065099,13780035,17540582,13043012,16850197,10400244,14230270,13944971,13866822,13916549,13553883,12391034,17748832,16761566,16721594,14369664,17748830,10625145,13042639,16019955,13890080,16864562,14121009,16844448,15996344,13551402,14222403,17748834,17437634,14383007,17752121,16944698,17088068,14163397,13737888,14637368,14523004,16902043,14198511,12865902,14695377,14191508,16710324,17333197,13072654,13834065,11868640,13605839,14189694,16372203,14007968,13686047,13092220,14003090,14095982,16024441,17333200,12755231,9397635,13440516,13723052,17230530,12784406,15910002,17333202,16362358,13040943,12748538,13843646,12747437,17082364,14128555,14755945,14393728,14762511,17333198,14751895,14188650,13945708,13591624,12899768,12744759,13725395,13642044,16710363,12821418,17333203,12621588,13972394,14791477,16344758,13483354,12796518,12312133,13060271,14351566,16212405,17333199,12815057,17332800,9547706,16794239,14053457,16794240,13790109,12772404,13791364,9095696,16279211,13814739,12395918,12912137,16619892,14512189,16794238,13063120,13907462,16742095,15841373,12731940,16382448,12925089,14571027,16794244,16794241,13855490,16794242,14467061,16703112,15869211,14398795,13489024,13493847,13561750,16694777,12755116,16794243,12940620,14127231,6690853,13015379,7509451,13579992,16306019,16279401,13724193,16014985,15905421,14668670,14301592,13848402,16530565,13857111,13923995,13427062,12974860,11071989,14207317,16344871,16231699,14841558,14176879,14273397,13913630,16056266,13496884,13914613,16382353,16299830,14589750,16314469,14841812,13632809,11715084,16314466,13812031,10242202,13910420,14207163,14644185,13680405,14110275,13584130,16175381,16314467,14220725,14472647,13860201,13561951,16314468,8547978,16314470,14088346,16368108,13582702,9706792,13911821,13786142,13616375,13958038,16294378,12983611,13596521,14459552,14226599,13810393,13098318,15862019,10263668,14040433,13448206,13645875,15862023,15862017,14841409,13719081,13685544,15862016,14023636,12791981,13026410,12857027,14263073,13732226,12873183,13011409,13534412,12345082,13038684,12959852,9659614,13787482,12964067,15853081,14262913,13399435,13559697,15862018,13593999,14546575,13524899,14258925,15862021,14546638,15862020,14546673,15862022,14263036,14727310,14205448,14035825,15862024,14052474,13454210,13936424,13544396,13645917,13807411,14469008,14138130,13099577,14076523,13699124,13612575,13566938,13358781,13773133,13705338,14480675,14409183,13059165,14613900,14664355,13430938,12585543,13624984,12794305,14548763,12594032,13377816,13397104,13080778,13649031,13257247,11708510,14480676,13250244,13476583,12583611,14275605,14480674,13804294,13464002,13484963,13550185,13385346,12693626,13737746,12971775,13384182,13718279,14390252,13572659,13467683,14062793,14062794,14062795,12401111,14062796,13502183,14062797,12797420,12913474,10133521,12678920,13340388,12976376,12617123,13923374,13457582,13103913,11840910,13527323,13035360,12879027,13588248,13419660,14038787,13001379,13370330,12658411,9761357,14063280,9858539,14063281,13981051,13366202,13903046,13657605,13338048,12535346,13916709,12646784,12880299,13326736,14062792,13036331,11877623,12845115,12829021,13554409,12923168,13742464,13332439,12780098,12656535,13070939,12960925,10350832,12612118,13696216,12919564,13632717,12780983,13742433,13742434,13742435,13742436,13742437,13742438,12588744,12748240,12894807,12764337,12582664,12662040,12849688,13354082,13420224,11063191,13772618,13503598,12998795,13466801,13492735,12834027,12620823,13035804,13362079,12718090,13528551,13041324,13343438,12917230,12847466,9703627,12797765,12950644,12905058,13499128,12861463,9873405,12938841

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data01/app/oracle/product/11.2.0.3/db_1')

Is the local system ready for patching? [y|n]

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Rolling back patch 20299017...

RollbackSession rolling back interim patch '20299017' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.xdk.parser.java, 11.2.0.3.0...

Patching component oracle.sysman.agent, 10.2.0.4.3...

Patching component oracle.xdk.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.xdk, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
RollbackSession removing interim patch '20299017' from inventory

Rolling back patch 19769496...

RollbackSession rolling back interim patch '19769496' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.sysman.agent, 10.2.0.4.3...

Patching component oracle.ovm, 11.2.0.3.0...

Patching component oracle.xdk, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.xdk.parser.java, 11.2.0.3.0...

Patching component oracle.oraolap, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.xdk.rsf, 11.2.0.3.0...
RollbackSession removing interim patch '19769496' from inventory

Rolling back patch 19121548...

RollbackSession rolling back interim patch '19121548' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.sysman.console.db, 11.2.0.3.0...
Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.ordim.client, 11.2.0.3.0...

Patching component oracle.ordim.jai, 11.2.0.3.0...
RollbackSession removing interim patch '19121548' from inventory

Rolling back patch 18522512...

RollbackSession rolling back interim patch '18522512' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
RollbackSession removing interim patch '18522512' from inventory

Rolling back patch 18031683...

RollbackSession rolling back interim patch '18031683' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
RollbackSession removing interim patch '18031683' from inventory

Rolling back patch 17540582...

RollbackSession rolling back interim patch '17540582' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo, 11.2.0.3.0...

Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.ordim.client, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.ordim.jai, 11.2.0.3.0...
RollbackSession removing interim patch '17540582' from inventory

Rolling back patch 16902043...

RollbackSession rolling back interim patch '16902043' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.ldap.rsf, 11.2.0.3.0...
Deleting "ssl_modifycountermeasurescbcchosenplaintextattack.o" from archive "/data01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.a"
Deleting "ssl_setcountermeasurescbcchosenplaintextattack.o" from archive "/data01/app/oracle/product/11.2.0.3/db_1/lib/libnnz11.a"

Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...

Patching component oracle.owb.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...
RollbackSession removing interim patch '16902043' from inventory

Rolling back patch 16619892...

RollbackSession rolling back interim patch '16619892' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.marvel, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Deleting "kstst.o" from archive "/data01/app/oracle/product/11.2.0.3/db_1/lib/libserver11.a"
Deleting "kststqad.o" from archive "/data01/app/oracle/product/11.2.0.3/db_1/lib/libserver11.a"

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sysman.agent, 10.2.0.4.3...

Patching component oracle.sysman.console.db, 11.2.0.3.0...
Patching component oracle.sysman.repository.core, 10.2.0.4.4...

Patching component oracle.xdk, 11.2.0.3.0...

Patching component oracle.xdk.parser.java, 11.2.0.3.0...

Patching component oracle.xdk.rsf, 11.2.0.3.0...
RollbackSession removing interim patch '16619892' from inventory

Rolling back patch 16056266...

RollbackSession rolling back interim patch '16056266' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.network.listener, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.ovm, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.rdbms.deconfig, 11.2.0.3.0...
RollbackSession removing interim patch '16056266' from inventory

Rolling back patch 14727310...

RollbackSession rolling back interim patch '14727310' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...
Patching component oracle.sysman.oms.core, 10.2.0.4.4...
RollbackSession removing interim patch '14727310' from inventory

Rolling back patch 14275605...

RollbackSession rolling back interim patch '14275605' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.network.client, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...
RollbackSession removing interim patch '14275605' from inventory

Rolling back patch 13923374...

RollbackSession rolling back interim patch '13923374' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.network.listener, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...
RollbackSession removing interim patch '13923374' from inventory

Rolling back patch 13696216...

RollbackSession rolling back interim patch '13696216' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.4...
RollbackSession removing interim patch '13696216' from inventory

Rolling back patch 13343438...

RollbackSession rolling back interim patch '13343438' from OH '/data01/app/oracle/product/11.2.0.3/db_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
RollbackSession removing interim patch '13343438' from inventory

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
/data01/app/oracle/product/11.2.0.3/db_1/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
/data01/app/oracle/product/11.2.0.3/db_1/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/data01/app/oracle/product/11.2.0.3/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/data01/app/oracle/product/11.2.0.3/db_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/data01/app/oracle/product/11.2.0.3/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'

OPatch Session completed with warnings.
Log file location: /data01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/20299017_Jun_12_2015_01_12_38/rollback2015-06-12_01-12-37AM_1.log

OPatch completed with warnings.

[oracle@DB11G 20299017]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.10
Copyright (c) 2015, Oracle Corporation. All rights reserved.

Oracle Home : /data01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /data01/app/oraInventory
from : /data01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.10
OUI version : 11.2.0.3.0
Log file location : /data01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2015-06-12_01-33-33AM_1.log

Lsinventory Output file location : /data01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-06-12_01-33-33AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: DB11G.localdomain
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.
[oracle@DB11G 20299017]$

[oracle@DB11G admin]$ ls -lrth catbun*
-rw-r--r--. 1 oracle oinstall 30K Aug 8 2011 catbundle.sql
-rw-r-----. 1 oracle oinstall 925 Jun 12 03:00 catbundle_PSU_RACDB_ROLLBACK.sql
-rw-r-----. 1 oracle oinstall 916 Jun 12 03:00 catbundle_PSU_RACDB_APPLY.sql
[oracle@DB11G admin]$ pwd
/data01/app/oracle/product/11.2.0.3/db_1/rdbms/admin
[oracle@DB11G admin]$

[oracle@DB11G admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 12 03:01:53 2015

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

Connected to an idle instance.

SQL> startup ;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 591400320 bytes
Database Buffers 239075328 bytes
Redo Buffers 2396160 bytes
Database mounted.

SQL> @?/rdbms/admin/catbundle_PSU_RACDB_ROLLBACK.sql

[oracle@DB11G admin]$ tail -100f /data01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_RACDB_APPLY_2015Jun12_03_00_19.log
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.3',
9 0,
10 'PSU',
11 'Patchset 11.2.0.2.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off

SQL> select action_time , id , comments , action from registry$history order by 1 ;

ACTION_TIME ID COMMENTS ACTION
--------------------------------------------------------------------------- ---------- ------------
17-SEP-11 10.21.11.595816 AM 0 Patchset 11.2.0.2.0 APPLY
29-MAY-15 11.51.04.716724 AM 0 Patchset 11.2.0.2.0 APPLY
08-JUN-15 02.29.47.458834 PM 14 PSU 11.2.0.3.14 APPLY
12-JUN-15 03.00.12.279947 AM 14 PSU 11.2.0.3.14 ROLLBACK
12-JUN-15 03.00.19.302543 AM 0 Patchset 11.2.0.2.0 APPLY

Tuesday 9 June 2015

Recovery Manager (RMAN) automatic tablespace point-in-time recovery

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

How TSPITR Works With an RMAN-Managed Auxiliary Instance

RMAN carries out the following steps:

If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
Takes the tablespaces to be recovered offline in the target database
Restores a backup controlfile from a point in time before the target time to the auxiliary instance
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE)
Recovers the restored datafiles in the auxiliary instance to the specified time
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database
Shuts down the auxiliary instance
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.

At that point the TSPITR process is complete. The recovery set datafiles are returned to their contents at the specified point in time, and belong to the target database.

Deciding When to Use TSPITR

Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

Recovering data lost after an erroneous TRUNCATE TABLE statement
Recovering from logical corruption of a table
Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR.

Determining the Recovery Set: Analyzing Data Relationships

Your recovery set starts out including the datafiles for the tablespaces you wish to recover. If, however, objects in the tablespaces you need have relationships (such as constraints) to objects in other tablespaces, you will have to account for this relationship before you can perform TSPITR. You have three choices when faced with such a relationship:

Add the tablespace including the related objects to your recovery set
Remove the relationship
Suspend the relationship for the duration of TSPITR

Identifying and Preserving Objects That Will Be Lost After TSPITR

When RMAN TSPITR is performed on a tablespace, any objects created after the target recovery time are lost. You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.

To see which objects will be lost in TSPITR, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database.

Example of TSPITR :

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

SQL> show user ;

USER is "SUMIT"

SQL> create table tspitr ( i int , J date) tablespace USERSTBS ;

Table created.

insert some data in tspitr table and commit ;

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Rman target /

backup database plus archivelog ;

SQL> set line 1000

SQL> SELECT * FROM sys.ts_pitr_check WHERE(ts1_name = 'USERSTBS' AND ts2_name != 'USERSTBS') OR (ts1_name != 'USERSTBS' AND ts2_name = 'USERSTBS');

no rows selected

SQL> SELECT owner, name, tablespace_name , creation_time FROM sys.ts_pitr_objects_to_be_dropped WHERE tablespace_name ='USERSTBS' ;

OWNER NAME TABLESPACE_NAME CREATION_TIME

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

SCOTT PK_DEPT USERSTBS 17-SEP-2011 10:21:59

SCOTT DEPT USERSTBS 17-SEP-2011 10:21:59

SCOTT EMP USERSTBS 17-SEP-2011 10:21:59

SCOTT PK_EMP USERSTBS 17-SEP-2011 10:21:59

SCOTT SALGRADE USERSTBS 17-SEP-2011 10:21:59

SUMIT TSPITR USERSTBS 08-JUN-2015 23:15:16

SUMIT TEST USERSTBS 08-JUN-2015 23:42:25

SQL> select * from tspitr ;

I J

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

1 08-JUN-2015 23:15:24

2 08-JUN-2015 23:15:38

3 08-JUN-2015 23:15:46

3 08-JUN-2015 23:16:57

3 08-JUN-2015 23:17:01

3 08-JUN-2015 23:31:01

3 08-JUN-2015 23:31:08

3 08-JUN-2015 23:32:25

4 08-JUN-2015 23:43:26

5 08-JUN-2015 23:43:45

Now drop few tables or truncate ot purge :

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

SQL> select count(*) from scott.emp ;

COUNT(*)

----------

SQL> select count(*) from scott.dept ;

COUNT(*)

----------

SQL> drop table tspitr ;

Table dropped.

SQL> drop table scott.EMP ;

Table dropped.

SQL> drop table scott.DEPT ;

Table dropped.

SQL>

SQL> select sysdate from dual ;

SYSDATE

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

08-JUN-2015 23:53:42

Suppose we want out all tables back until time : 08-JUN-2015 23:44:00 : ( You can use Logminer tool to find the exact time of drop statement)

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

[oracle@DB11G RMAN]$ cd /data01/RMAN/

[oracle@DB11G RMAN]$ mkdir TSPITR

[oracle@DB11G RMAN]$ chown oracle:oinstall TSPITR/

[oracle@DB11G RMAN]$ chmod 775 TSPITR/

[oracle@DB11G RMAN]$

SQL> select timestamp_to_scn(to_timestamp('08-JUN-2015 23:44:00','DD-MON-YYYY HH24:MI:SS')) as scn from dual ;

SCN

----------

1362639

SQL> select scn_to_timestamp(1362639) as timestamp from dual ;

TIMESTAMP

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

08-JUN-15 11.44.00.000000000 PM

RMAN> recover tablespace USERSTBS until scn 1362639 auxiliary destination '/data01/RMAN/TSPITR' ;

Starting recover at 09-JUN-15

using channel ORA_DISK_1

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='veyq'

initialization parameters used for automatic instance:

db_name=RACDB

db_unique_name=veyq_tspitr_RACDB

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/data01/RMAN/TSPITR

log_archive_dest_1='location=/data01/RMAN/TSPITR'

#No auxiliary parameter file used

starting up automatic instance RACDB

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2227744 bytes

Variable Size 100663776 bytes

Database Buffers 184549376 bytes

Redo Buffers 4837376 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:

{

# set requested point in time

set until scn 1362639;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 09-JUN-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /data01/RMAN/c-878593708-20150608-03

channel ORA_AUX_DISK_1: piece handle=/data01/RMAN/c-878593708-20150608-03 tag=TAG20150608T232715

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/data01/RMAN/TSPITR/RACDB/controlfile/o1_mf_bqdt069y_.ctl

Finished restore at 09-JUN-15

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until scn 1362639;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 4 to

"/data01/app/oracle/datafiles/racdbf/USERSTBS01.dbf";

set newname for datafile 5 to

"/data01/app/oracle/datafiles/racdb/USERSTBS02.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2, 4, 5;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-JUN-15

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /data01/app/oracle/datafiles/racdbf/USERSTBS01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /data01/app/oracle/datafiles/racdb/USERSTBS02.dbf

channel ORA_AUX_DISK_1: reading from backup piece /data01/RMAN/4bq90tjc_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 09-JUN-15

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=881885722 file name=/data01/RMAN/TSPITR/RACDB/datafile/o1_mf_system_bqdt0rhx_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=881885723 file name=/data01/RMAN/TSPITR/RACDB/datafile/o1_mf_undotbs1_bqdt0roo_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=9 STAMP=881885723 file name=/data01/RMAN/TSPITR/RACDB/datafile/o1_mf_sysaux_bqdt0rkg_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 1362639;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 4 online";

sql clone "alter database datafile 5 online";

# recover and open resetlogs

recover clone database tablespace "USERSTBS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 5 online

Starting recover at 09-JUN-15

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /data01/arch/arch_1_5_881841121

archived log for thread 1 with sequence 6 is already on disk as file /data01/arch/arch_1_6_881841121

archived log for thread 1 with sequence 7 is already on disk as file /data01/arch/arch_1_7_881841121

archived log for thread 1 with sequence 8 is already on disk as file /data01/arch/arch_1_8_881841121

archived log for thread 1 with sequence 9 is already on disk as file /data01/arch/arch_1_9_881841121

archived log file name=/data01/arch/arch_1_5_881841121 thread=1 sequence=5

archived log file name=/data01/arch/arch_1_6_881841121 thread=1 sequence=6

archived log file name=/data01/arch/arch_1_7_881841121 thread=1 sequence=7

archived log file name=/data01/arch/arch_1_8_881841121 thread=1 sequence=8

archived log file name=/data01/arch/arch_1_9_881841121 thread=1 sequence=9

media recovery complete, elapsed time: 00:00:19

Finished recover at 09-JUN-15

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace USERSTBS read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/data01/RMAN/TSPITR''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/data01/RMAN/TSPITR''";

}

executing Memory Script

sql statement: alter tablespace USERSTBS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data01/RMAN/TSPITR''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_veyq":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table "SYS"."TSPITR_EXP_veyq" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_veyq is:

EXPDP> /data01/RMAN/TSPITR/tspitr_veyq_29688.dmp

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace USERSTBS:

EXPDP> /data01/app/oracle/datafiles/racdb/USERSTBS02.dbf

EXPDP> /data01/app/oracle/datafiles/racdbf/USERSTBS01.dbf

EXPDP> Job "SYS"."TSPITR_EXP_veyq" successfully completed at 00:17:47

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace USERSTBS including contents keep datafiles';

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

executing Memory Script

database closed

database dismounted

Oracle instance shut down

sql statement: drop tablespace USERSTBS including contents keep datafiles

Performing import of metadata...

IMPDP> Master table "SYS"."TSPITR_IMP_veyq" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_veyq":

IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

IMPDP> Job "SYS"."TSPITR_IMP_veyq" successfully completed at 00:56:05

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace USERSTBS read write';

sql 'alter tablespace USERSTBS offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace USERSTBS read write

sql statement: alter tablespace USERSTBS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_temp_bqdw9928_.tmp deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/onlinelog/o1_mf_3_bqdw8t8x_.log deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/onlinelog/o1_mf_2_bqdw8o7v_.log deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/onlinelog/o1_mf_1_bqdw8jkw_.log deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_sysaux_bqdw42n1_.dbf deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_undotbs1_bqdw42r5_.dbf deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_system_bqdw42kd_.dbf deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/controlfile/o1_mf_bqdw3jov_.ctl deleted

Finished recover at 09-JUN-15

SQL> Alter tablespace USERSTBS online ;

Tablespace altered.

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select * from sumit.tspitr ;

I J

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

1 08-JUN-2015 23:15:24

2 08-JUN-2015 23:15:38

3 08-JUN-2015 23:15:46

3 08-JUN-2015 23:16:57

3 08-JUN-2015 23:17:01

3 08-JUN-2015 23:31:01

3 08-JUN-2015 23:31:08

3 08-JUN-2015 23:32:25

4 08-JUN-2015 23:43:26

5 08-JUN-2015 23:43:45

SQL> select count(*) from scott.emp ;

COUNT(*)

----------

SQL> select count(*) from scott.dept ;

COUNT(*)

----------

Total Pageviews

Monday 15 June 2015

TNS-00525: Insufficient privilege for operation

List and Delete archivelogs using RMAN

Friday 12 June 2015

Rollback a PSU patch ( Database Patch Set Update 11.2.0.3.14)

Tuesday 9 June 2015

Recovery Manager (RMAN) automatic tablespace point-in-time recovery