O-R-A-C-L-E D-B-A Zara E Query Enduku Tappu E Chepandi Vayya

February 12, 2013

[quote name='Silence..Please' timestamp='1360692492' post='1303266630']
do it in more laziest way .. anni tables mundhu rman ani add cheyyakkarledu..do the below thing and run your query from that session

alter session set current_schema=rman;
[/quote]

idi bagundi bhayya thank,

so paina query lo status ravtam ledhu bhayya completed or failed ani adi ela add cheyali e query lo

select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rman.rc_database a,rman.bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rman.rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rman.rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/

February 12, 2013

[quote name='KOTC' timestamp='1360692897' post='1303266696']

idi bagundi bhayya thank,

so paina query lo status ravtam ledhu bhayya completed or failed ani adi ela add cheyali e query lo

select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rman.rc_database a,rman.bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rman.rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rman.rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/
[/quote] em status kavali niku ? output aah ?

February 12, 2013

[quote name='BlueBerry' timestamp='1360692468' post='1303266626']
Kool... Always preferable prefix the schema name before any table/view. Make it a habit.... if you are a programmer ... it will help you a lot and saves you debug time.
[/quote]

yeah ade bhayya thanks again for your help.

February 12, 2013

[quote name='Silence..Please' timestamp='1360692951' post='1303266707']
em status kavali niku ? output aah ?
[/quote]

yeah bhayya name,DBID,DBBKP,ARCBKP,STATUS(idi) kuda include avali

February 12, 2013

[quote name='BlueBerry' timestamp='1360692877' post='1303266693']

Good to learn new command... but i tested it its not working;

Connected as HR

executed the command alter session set current_schema=scott;

show user; -- showing user as HR

select * from emp; -- Scott schema table

end up with

ORA-00942: table or view does not exist;

I am using SQL developer... oracle version 11.1.0.6
[/quote] may be u dont have that privilege ... memu dba's kada.. we will have dba privilege to our userid's ..

February 12, 2013

[quote name='KOTC' timestamp='1360693045' post='1303266724']

yeah bhayya name,DBID,DBBKP,ARCBKP,STATUS(idi) kuda include avali
[/quote] ni inner query lo status include chesava ? because u r selecting from that inner query

February 12, 2013

[quote name='Silence..Please' timestamp='1360693162' post='1303266743']
ni inner query lo status include chesava ? because u r selecting from that inner query
[/quote]

ledhu cheyale ade kadha e view lo undi status

desc rman.bs

Name Null? Type
----------------------------------------- -------- -----------------

BS_KEY NOT NULL NUMBER
DB_KEY NOT NULL NUMBER
BS_RECID NUMBER
BS_STAMP NUMBER
SET_STAMP NOT NULL NUMBER
SET_COUNT NOT NULL NUMBER
BCK_TYPE VARCHAR2(1)
INCR_LEVEL NUMBER
PIECES NOT NULL NUMBER
START_TIME DATE
COMPLETION_TIME DATE
STATUS VARCHAR2(1)
CONTROLFILE_INCLUDED VARCHAR2(7)
INPUT_FILE_SCAN_ONLY VARCHAR2(3)
KEEP_OPTIONS NOT NULL NUMBER
KEEP_UNTIL DATE
BLOCK_SIZE NUMBER
SITE_KEY NUMBER
MULTI_SECTION VARCHAR2(1)

kani malli rman.rc_database lo ledhu so adukani

February 12, 2013

[quote name='Silence..Please' timestamp='1360693051' post='1303266726']
may be u dont have that privilege ... memu dba's kada.. we will have dba privilege to our userid's ..
[/quote]

OK got it... tried granting the select privs to HR and it works;

Thanks

February 12, 2013

[quote name='KOTC' timestamp='1360693261' post='1303266751']

ledhu cheyale ade kadha e view lo undi status

desc rman.bs

Name Null? Type
----------------------------------------- -------- -----------------

BS_KEY NOT NULL NUMBER
DB_KEY NOT NULL NUMBER
BS_RECID NUMBER
BS_STAMP NUMBER
SET_STAMP NOT NULL NUMBER
SET_COUNT NOT NULL NUMBER
BCK_TYPE VARCHAR2(1)
INCR_LEVEL NUMBER
PIECES NOT NULL NUMBER
START_TIME DATE
COMPLETION_TIME DATE
STATUS VARCHAR2(1)
CONTROLFILE_INCLUDED VARCHAR2(7)
INPUT_FILE_SCAN_ONLY VARCHAR2(3)
KEEP_OPTIONS NOT NULL NUMBER
KEEP_UNTIL DATE
BLOCK_SIZE NUMBER
SITE_KEY NUMBER
MULTI_SECTION VARCHAR2(1)

kani malli rman.rc_database lo ledhu so adukani
[/quote] bs view lo unte .. andhulo nunchi select cheyi status ni.. ni inner query lo .. enti problem dhani valla ?

February 12, 2013

[quote name='Silence..Please' timestamp='1360693505' post='1303266798']
bs view lo unte .. andhulo nunchi select cheyi status ni.. ni inner query lo .. enti problem dhani valla ?
[/quote]

neku gurthu undo ledhu nenu okasari adiganu ninnu ma envi lo ani databases recovery catalog lo register ayai,so daniki rendu scripts dorkiyai andulo just last backup ayina full,archive,ctl,incrmental info undi ala kakunda oka place lo ne ani database backup status vachle oka query em ayina undha ani research start chesa rendu dorikai okati emo nenu post chesindi andulo status ravtam ledhu inka oka query undi adi kuda status evvatam ledhu.any idea?

2nd query vachi

select name, full, inc, log, ctl
from
rman.rc_database d,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') full
from rman.rc_backup_set s
where s.backup_type in ('D')
and controlfile_included = 'NONE'
group by db_id) f,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') inc
from rman.rc_backup_set s
where s.backup_type in ('I')
group by db_id) i,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') log
from rman.rc_backup_set s
where s.backup_type in ('L')
group by db_id) l,
(select db_key, to_char(max(completion_TIME),'MM/DD/YYYY:HH24:MI:SS') ctl
from rman.rc_backup_controlfile s
group by db_key) c
where f.db_id(+) = d.dbid
and i.db_id(+) = d.dbid
and l.db_id(+) = d.dbid
and c.db_key(+) = d.db_key
order by 1;select name, full, inc, log, ctl

from
rman.rc_database d,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') full
from rman.rc_backup_set s
where s.backup_type in ('D')
and controlfile_included = 'NONE'
group by db_id) f,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') inc
from rman.rc_backup_set s
where s.backup_type in ('I')
group by db_id) i,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') log
from rman.rc_backup_set s
where s.backup_type in ('L')
group by db_id) l,
(select db_key, to_char(max(completion_TIME),'MM/DD/YYYY:HH24:MI:SS') ctl
from rman.rc_backup_controlfile s
group by db_key) c
where f.db_id(+) = d.dbid
and i.db_id(+) = d.dbid
and l.db_id(+) = d.dbid
and c.db_key(+) = d.db_key
order by 1;

idi name,FULL,INCR,LOGS,CTL last backup ayina date plus time estundi ila

NAME FULL INC LOG
-------- ------------------- ------------------- -------------------
CTL
-------------------
02/12/2013:10:15:00

XXXXXXX 02/10/2013:02:11:21 02/11/2013:22:40:35 02/12/2013:06:03:01
02/12/2013:06:07:52

February 12, 2013

[quote name='Silence..Please' timestamp='1360693505' post='1303266798']
bs view lo unte .. andhulo nunchi select cheyi status ni.. ni inner query lo .. enti problem dhani valla ?
[/quote]

got it bhayya

neku useful avtundi emo chusuko

[color=#000000][font=Tahoma, Verdana, Helvetica, sans-serif][size=4][b] How to monitor all the RMAN jobs using single script in recovery catalog?[/b][/size][/font][/color]

select
db_name,
start_time,
end_time,
status,
time_taken_display,
output_bytes_display,
output_device_type,
session_key,
session_recid,
session_stamp
from rc_rman_backup_job_details where end_time > sysdate -1

February 12, 2013

[quote name='KOTC' timestamp='1360694094' post='1303266899']

neku gurthu undo ledhu nenu okasari adiganu ninnu ma envi lo ani databases recovery catalog lo register ayai,so daniki rendu scripts dorkiyai andulo just last backup ayina full,archive,ctl,incrmental info undi ala kakunda oka place lo ne ani database backup status vachle oka query em ayina undha ani research start chesa rendu dorikai okati emo nenu post chesindi andulo status ravtam ledhu inka oka query undi adi kuda status evvatam ledhu.any idea?

2nd query vachi

select name, full, inc, log, ctl
from
rman.rc_database d,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') full
from rman.rc_backup_set s
where s.backup_type in ('D')
and controlfile_included = 'NONE'
group by db_id) f,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') inc
from rman.rc_backup_set s
where s.backup_type in ('I')
group by db_id) i,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') log
from rman.rc_backup_set s
where s.backup_type in ('L')
group by db_id) l,
(select db_key, to_char(max(completion_TIME),'MM/DD/YYYY:HH24:MI:SS') ctl
from rman.rc_backup_controlfile s
group by db_key) c
where f.db_id(+) = d.dbid
and i.db_id(+) = d.dbid
and l.db_id(+) = d.dbid
and c.db_key(+) = d.db_key
order by 1;select name, full, inc, log, ctl

from
rman.rc_database d,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') full
from rman.rc_backup_set s
where s.backup_type in ('D')
and controlfile_included = 'NONE'
group by db_id) f,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') inc
from rman.rc_backup_set s
where s.backup_type in ('I')
group by db_id) i,
(select db_id, to_char(max(START_TIME),'MM/DD/YYYY:HH24:MI:SS') log
from rman.rc_backup_set s
where s.backup_type in ('L')
group by db_id) l,
(select db_key, to_char(max(completion_TIME),'MM/DD/YYYY:HH24:MI:SS') ctl
from rman.rc_backup_controlfile s
group by db_key) c
where f.db_id(+) = d.dbid
and i.db_id(+) = d.dbid
and l.db_id(+) = d.dbid
and c.db_key(+) = d.db_key
order by 1;

idi name,FULL,INCR,LOGS,CTL last backup ayina date plus time estundi ila

NAME FULL INC LOG
-------- ------------------- ------------------- -------------------
CTL
-------------------
02/12/2013:10:15:00

XXXXXXX 02/10/2013:02:11:21 02/11/2013:22:40:35 02/12/2013:06:03:01
02/12/2013:06:07:52
[/quote] ni 1st select statement lo status column .. then aah query ekadi nunchi aithe select chesthondhi using 'from' aah internal query lo kuda status column ni select cheyi ..
as of now, nuvu ichina query ni chuusthey .. neither of ur queries have status column in 1st and 2nd select statements

February 12, 2013

[quote name='Silence..Please' timestamp='1360694288' post='1303266927']
ni 1st select statement lo status column .. then aah query ekadi nunchi aithe select chesthondhi using 'from' aah internal query lo kuda status column ni select cheyi ..
as of now, nuvu ichina query ni chuusthey .. neither of ur queries have status column in 1st and 2nd select statements
[/quote]

yeah kani vere query dorikindi bhayya adi useful avtundi le backups ki

February 12, 2013

[quote name='KOTC' timestamp='1360694585' post='1303266977']

yeah kani vere query dorikindi bhayya adi useful avtundi le backups ki
[/quote] dhorikithe well n gud .. kani try the one i suggested .. u will get an idea .. epudu anni okey table or view lo undavu .. alaantapudu e complex queries ni ardham cheskune laga undali nuvu

February 12, 2013

[quote name='Silence..Please' timestamp='1360694656' post='1303266987']
dhorikithe well n gud .. kani try the one i suggested .. u will get an idea .. epudu anni okey table or view lo undavu .. alaantapudu e complex queries ni ardham cheskune laga undali nuvu
[/quote]

yeah nenu asale sql lo weak,PL/SQL aithe modda be radhu evaru ayina unte chepu manchi trainers pay chesi nerchukunta.

Sign In

O-R-A-C-L-E D-B-A Zara E Query Enduku Tappu E Chepandi Vayya

Recommended Posts

KOTC

Gachibowli_Diwakar

KOTC

KOTC

Gachibowli_Diwakar

Gachibowli_Diwakar

KOTC

Ka NTR i

Gachibowli_Diwakar

KOTC

KOTC

Gachibowli_Diwakar

KOTC

Gachibowli_Diwakar

KOTC

Tell a friend

Most viewed in last 30 days

Activity