Search This Blog

Total Pageviews

Tuesday 5 May 2015

ORA-38784: Cannot create restore point

ORA-38784: Cannot create restore point 

ORA-01153: an incompatible media recovery is active




SQL> create restore point RP_15042015 guarantee flashback database ;
create restore point RP_15042015 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP_15042015'.
ORA-01153: an incompatible media recovery is active


Check is RMAN process running if yes------- then kill all the process !!!!!!!!!!!!!!
1.
ps -ef|grep -i rman


2.
select SID,SERIAL#,PROGRAM from v$session where PROGRAM like '%rman%';

195 35085 rman@*******db (TNS V1-V3)
629 58871 rman@*******db (TNS V1-V3)
1202 47627 rman@*******db (TNS V1-V3)


3.
check for any datafiles that is in BACKUP mode.

SQL> select * from v$backup where status = 'ACTIVE';





script to check rman process ...

set head off verify off echo off pages 1500 linesize 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
DEFINE _br=chr(10)
select
' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' || &_br ||
'Inst id .............................................: '||s.inst_id,
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '||s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from gv$session s, gv$process p
where 1=1
and p.addr=s.paddr
and s.program like '%rman%'; -------- to check the rman process !!!!
set head on verify on echo on




alter system kill Session '50,4771,@1' immediate;
Inst id .............................................: 1
Session Id.............................................: 50
Serial Num..............................................: 4771
User Name ..............................................: SYS
Session Status .........................................: ACTIVE
Client Process Id on Client Machine ....................: *10962*
Server Process ID ......................................: 11233
Sql_Address ............................................: 00
Sql_hash_value .........................................: 0
Schema Name ..... ......................................: SYS
Program ...............................................: rman@********db (TNS V1-V3)
Module .................................................: backup incr datafile
Action .................................................: 0000267 STARTED16
Terminal ...............................................:
Client Machine .........................................: ********db
LAST_CALL_ET ...........................................: 274
S.LAST_CALL_ET/3600 ....................................: .076111111111111111111111111111111111111

alter system kill Session '536,55845,@1' immediate;
Inst id .............................................: 1
Session Id.............................................: 536
Serial Num..............................................: 55845
User Name ..............................................: SYS
Session Status .........................................: INACTIVE
Client Process Id on Client Machine ....................: *10962*
Server Process ID ......................................: 10972
Sql_Address ............................................: 00
Sql_hash_value .........................................: 0
Schema Name ..... ......................................: SYS
Program ...............................................: rman@********db (TNS V1-V3)
Module .................................................: rman@********db (TNS V1-V3)
Action .................................................: 0000010 FINISHED131
Terminal ...............................................:
Client Machine .........................................: ********db
LAST_CALL_ET ...........................................: 274
S.LAST_CALL_ET/3600 ....................................: .076111111111111111111111111111111111111

alter system kill Session '677,48685,@1' immediate;
Inst id .............................................: 1
Session Id.............................................: 677
Serial Num..............................................: 48685
User Name ..............................................: SYS
Session Status .........................................: INACTIVE
Client Process Id on Client Machine ....................: *10962*
Server Process ID ......................................: 10973
Sql_Address ............................................: 00
Sql_hash_value .........................................: 0
Schema Name ..... ......................................: SYS
Program ...............................................: rman@********db (TNS V1-V3)
Module .................................................: rman@********db (TNS V1-V3)
Action .................................................:
Terminal ...............................................:
Client Machine .........................................: ********db
LAST_CALL_ET ...........................................: 9
S.LAST_CALL_ET/3600 ....................................: .0025



set linesize 200  numf 999999999999999
col TIME format a31
col name format a25
col STORAGE_SIZE format 9999999999999999
col DATABASE_INCARNATION# format 99999999999
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';


NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------- ---------- ------------------------------- --------------------- --- -----------------
RP_15042015 692690908 13-APR-15 12.57.21.000000000 PM 3 YES 52428800

1 row selected.

Oracle DBA

anuj blog Archive