Oracle DBA: May 2017

Friday, 26 May 2017

How to restore a sequence of archivelogs back to their original location

connect catalog RMAN//*********@RMANCAT

connect target
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=ora_dbserver4506zone1,NB_ORA_CLIENT=dbserver4505-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
restore archivelog from logseq 32567 until logseq 32569;
release channel t1;
release channel t2;
}

Thursday, 25 May 2017

How to find the SQL script which executed more than 5 hrs in database

set wrap off
col elapsed_time_delta format 9999999999
col plan_hash_value format 9999999999
col seconds format 99999
col executions_total format 99999999

select
stat.sql_id,
plan_hash_value,
rpad(parsing_schema_name,10) "schema",elapsed_time_total/1000000 "seconds",
elapsed_time_delta,disk_reads_delta,
stat.executions_total,
to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "endtime",
rpad(sql_text,40) text,ss.snap_id
from
dba_hist_sqlstat stat,
dba_hist_sqltext txt,
dba_hist_snapshot ss
where
stat.sql_id = txt.sql_id
and
stat.dbid = txt.dbid
and
ss.dbid = stat.dbid
and
ss.instance_number = stat.instance_number
and
stat.snap_id = ss.snap_id
and
parsing_schema_name not like 'sys%'
and
ss.begin_interval_time >= sysdate-40 ----you can change it accordingly
and
stat.elapsed_time_total/1000000 > 18000 ----you can change it accordingly
order by
elapsed_time_total desc;

Wednesday, 24 May 2017

how to restore the database upto until time( point in time recovery) using RMAN

Let Say you got a request from a application team that , their application upgrade has failed and they wants to restore back the database to back date.

Application team asked you to restore the database to specific date in our case they wants to restore the database to 2017-05-18 22:00 CET

1. We can check by the RMAN preview command if we have available backups to restore or recover the database or not , it will not do the actual restore and recovery , also it will not check that backups are valid to restore or not, we have to use RMAN VALIDATE command in order to check the validity if the backups.

run {
set command id to 'rman restore until time';
set until time "to_date('2017-05-18:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
allocate channel t1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_sfadb.opt)';
restore database preview;
recover database preview;
}

2. After the successful completion of the above command we can restore/recover the database actually:

run {
set command id to 'rman restore until time';
set until time "to_date('2017-05-18:22:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
shutdown abort;
startup nomount;
allocate channel t1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_sfadb.opt)';
alter database mount;
restore database;
recover database;
release channel t1;
sql 'alter database open resetlogs';
}

Script to find the status of the RMAN backup from the command prompt

SQL> select start_time,end_time, status, input_type from v$rman_backup_job_details order by start_time;

START_TIM END_TIME STATUS INPUT_TYPE
--------- --------- ----------------------- -------------
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
11-MAY-17 11-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED DB FULL
12-MAY-17 12-MAY-17 COMPLETED DB FULL

START_TIM END_TIME STATUS INPUT_TYPE
--------- --------- ----------------------- -------------
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
12-MAY-17 12-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED DB FULL
13-MAY-17 13-MAY-17 COMPLETED DB FULL
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA

START_TIM END_TIME STATUS INPUT_TYPE
--------- --------- ----------------------- -------------
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
13-MAY-17 13-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED DB FULL
14-MAY-17 14-MAY-17 COMPLETED DB FULL
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA

START_TIM END_TIME STATUS INPUT_TYPE
--------- --------- ----------------------- -------------
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
14-MAY-17 14-MAY-17 COMPLETED RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED DB FULL
15-MAY-17 15-MAY-17 COMPLETED DB FULL
15-MAY-17 15-MAY-17 COMPLETED RECVR AREA
15-MAY-17 15-MAY-17 COMPLETED RECVR AREA

How to find out upto what time we can flashback our database

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

COLUMN oldest_flash_scn FOR 999,999,999

ALTER SESSION
SET nls_date_format='DD MON YYYY hh24:mi:ss'
/

SELECT
oldest_flashback_scn,
oldest_flashback_time
FROM
v$flashback_database_log
/

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM

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

787294429 22 MAY 2017 06:35:11

Labels