Plan change using load_plans_from_cursor_cache

This post is more of a note for myself and might be helpful to few other.

Assuming db is 11gR2 and baselines/spm is used.

When a new query is introduced in db, it might be that it runs with the good plan, but sometimes it picks up wrong plan. It could be that Index Range Scan could have been done, but the sql is going for Full Table Scan (FTS) causing performance issue.

Few of the times we do have issues were the sql is newly introduced to the system and starts taking execution time longer than expected or increase the CPU or sessions starts piling up with some particular session wait event.

Now supposing, a new sql is introduced and you see lots of sessions running it doing ‘direct path reads’ and the sql is taking more than expected time as mostly it would be doing FTS, what can be done in such case?

If we have the same sqlid running fine as expected in some other prod db we can simply migrate the baseline using the below steps (just overview) –

1. dbms_spm.create_stgtab_baseline
2. dbms_spm.pack_stgtab_baseline
3. Export the staging table from source db
4. Import the dump in target db
5. dbms_spm.unpack_stgtab_baseline

In case you don’t have it running anywhere and you know an Index scan would be better you can fake the plan by generating an execution plan using index and transfer its execution plan to bad performing sql using dbms_spm.load_plans_from_cursor_cache().

Original sqlid 9x7g8gyjzr95d is doing FTS and its sql_handle is SQL_677er77f1f7bf077 and we have index which can be used for better performance.


sql> !more bind.sql
accept SQL_ID prompt 'Enter SQL_ID:- '
col name format a10;
col VALUE_STRING format a30;
select sql_id, NAME, position,DATATYPE_STRING, VALUE_STRING from v$sql_bind_capture where sql_id = '&SQL_ID';

sql>@bind
Enter SQL_ID:- 9x7g8gyjzr95d
old   1: select sql_id, NAME, position,DATATYPE_STRING, VALUE_STRING from v$sql_bind_capture where sql_id = '&SQL_ID'
new   1: select sql_id, NAME, position,DATATYPE_STRING, VALUE_STRING from v$sql_bind_capture where sql_id = '9x7g8gyjzr95d'

SQL_ID	      NAME	   POSITION DATATYPE_STRING VALUE_STRING
------------- ---------- ---------- --------------- ------------------------------
9x7g8gykrz95d :1		  1 VARCHAR2(128)   test.xxxxxx

sql>variable lu VARCHAR2(128)
sql>exec :lu:='test.xxxxxx'

PL/SQL procedure successfully completed.

sql>select /*+ index(ia i_demand_src) */ creation_date - time_util.local_time_offset_from_utc() creation_date, ia.* from invent_audits ia where demand_src = :lu

...........
...........

2 rows selected

sql>select sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ index(ia i_demand_src) */ %';

SQL_ID	      PLAN_HASH_VALUE
------------- ---------------
vbw0xrhd0nv00	    1852372768

Now we want the plan_hash_value 1852372768 to be used by our original query.So,

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '&sql_id',
    plan_hash_value => '&plan_hash_value',
    sql_handle => '&handle');
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
Enter value for sql_id: vbw0xrhd0nv00  (The new sql_id, with index)
Enter value for plan_hash_value: 1852372768 (The new plan_hash_value for the new sql_id using index)
Enter value for handle: SQL_677er77f1f7bf077 (The sql_handle of the original/problemetic sql with sql_id 9x7g8gyjzr95d)

SQL_Id and plan_hash_value is more than enough to make it possible to create an SQL Baseline for one query using the execution plan for the other query.

The new baseline will be Enabled and Accepted ‘YES’ and for older baseline ACCEPTED will be changed to NO.

Flush the sql_id once after the new baseline is created ;)

Hope this helps!!!!

Few Scripts for Identify Performance Issues using DBA_HIST view

It has been pretty long that i had blogged.Past year was little busy on both personal and professional front. But this year i am planning to be more active in sharing and learning and with not only Oracle DBMS but could be few others too.

Now, coming back to this blog, i wanted to share certain sql scripts which i have been using mostly for doing the RCA for issues. Sometimes issues are reported lately and DBA are asked for RCA.In past few month i worked on multiple commit latency and high CPU spikes issue which lead to writing these scripts to identify the events, sqlids, module etc in order to catch the culprit.

If the issue is currently being worked on then V$ACTIVE_SESSION_HISTORY is the best place to start with along with v$lock and few other views. In case, spike was seen few hours/a day back, the data to diagnose can be retrieved from DBA_HIST_ACTIVE_SESS_HISTORY.

As we know the time when issue occurred we can use the below sql to identify the top most EVENTS which happened during that time frame. I am displaying the events which have count(*) > 50.

break on TIME skip 1 

accept start_time prompt 'Enter start time [ DD-MM-YY HH24:MI ]: '
accept end_time prompt 'Enter end time [ DD-MM-YY HH24:MI ]: '


select to_char(sample_time,'DD-MM HH24:MI') time,event,count(*) from dba_hist_active_sess_history where sample_time between to_date ('&start_time','DD-MM-YY HH24:MI') and to_date ('&end_time','DD-MM-YY HH24:MI') group by to_char(sample_time,'DD-MM HH24:MI'),event having count(*) > 50 order by 1;

Output –


TIME	    EVENT						 COUNT(1)
----------- -------------------------------------------------- ----------
29-11 09:29 db file sequential read		                    143

29-11 09:31 db file sequential read		                    183
	    library cache: mutex X		                    120
	    log file sync			                    656

29-11 09:32 db file sequential read		                    153
	    library cache: mutex X		                    129
	    log file sync			                    285

To check the sqlid which were most active during that time frame we can use the below sql –

break on TIME skip 1 

accept start_time prompt 'Enter start time [ DD-MM-YY HH24:MI ]: '
accept end_time prompt 'Enter end time [ DD-MM-YY HH24:MI ]: '
accept N prompt 'Enter rownumber to display [N]: '


col module for a45
col opname for a15
select time,sql_id,plan_hash_value,opname,module,count from 
        (select to_char(sample_time,'DD-MM-YY HH24:MI') time,sql_id,sql_plan_hash_value plan_hash_value,module,SQL_OPNAME opname,count(1) count,
          ROW_NUMBER ()  OVER (PARTITION BY to_char(sample_time,'DD-MM-YY HH24:MI') order by count(1) DESC) as rownumber 
          from  dba_hist_active_sess_history  where sample_time between to_date ('&start_time','DD-MM-YY HH24:MI') and to_date ('&end_time','DD-MM-YY HH24:MI') and sql_id is not nul
l group by to_char(sample_time,'DD-MM-YY HH24:MI'),sql_id,sql_plan_hash_value,module,SQL_OPNAME)
where rownumber <=&N;


undef start_time
undef end_time
undef N

Output –


TIME	       SQL_ID	     PLAN_HASH_VALUE OPNAME	     MODULE						COUNT
-------------- ------------- --------------- --------------- --------------------------------------------- ----------
10-01-14 07:35 aurvkajbfxr0z	  2569592323 SELECT	     ABC				         3
	       ccdks1ftnc7x5	   641461876 SELECT	     ABC				         3
	       27yu9pxlppscn	  2950873079 SELECT	     XYZ			                 2
	       38u8w2hohzhha		   0 INSERT	     DEF			                 2
	       89bqc3gp18zya	   438142338 INSERT	     XYZ				         2

10-01-14 07:36 0y95krfumnbr5	   488120578 DELETE	     XYZ				         5
	       1gqi6rs1nj113	  3888582233 INSERT	     DEF				         3
	       2sktfcq1vmd9r		   0 INSERT	     ABC				         2
	       3nbmuejym2ppk		   0 PL/SQL EXECUTE  ABC					    2
	       4vdjfsmzqbyhq	   324236703 SELECT	     ABC				         2

To find top executions between certain time frame –

col BEGIN_INTERVAL_TIME format a30
col module format a50;
col plan_hash_value for 99999999999999

break on TIME skip 1 

accept start_time prompt 'Enter start time [ DD-MM-YY HH24:MI ]: '
accept end_time prompt 'Enter end time [ DD-MM-YY HH24:MI ]: '
accept N prompt 'Enter rownumber to display [N]: '

select to_char(BEGIN_INTERVAL_TIME,'DD-MM-YY HH24:MI') time,sql_id,PLAN_HASH_VALUE,module,"Executions","BG/exec","DR/exec","ET/exec","CT/exec" from (
select  hs.BEGIN_INTERVAL_TIME,
        hss.sql_id,
        hss.plan_hash_value,
        hss.MODULE,
        sum(hss.EXECUTIONS_DELTA) "Executions",
        round(sum(hss.BUFFER_GETS_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,sum(hss.EXECUTIONS_DELTA))) "BG/exec",
        round(sum(hss.DISK_READS_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,nvl(sum(hss.EXECUTIONS_DELTA),0))) "DR/exec",
        round(sum(hss.ELAPSED_TIME_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,nvl(sum(hss.EXECUTIONS_DELTA),0))/1000000) "ET/exec",
        round(sum(hss.CPU_TIME_DELTA)/decode(nvl(sum(hss.EXECUTIONS_DELTA),0),0,1,nvl(sum(hss.EXECUTIONS_DELTA),0))/1000000) "CT/exec",
        ROW_NUMBER ()  OVER (PARTITION BY hs.BEGIN_INTERVAL_TIME ORDER BY sum(hss.EXECUTIONS_DELTA) DESC) as rownumber   
from    dba_hist_sqlstat hss, dba_hist_snapshot hs
where   hss.snap_id=hs.snap_id
and     hs.BEGIN_INTERVAL_TIME between to_date ('&start_time','DD-MM-YY HH24:MI') and to_date ('&end_time','DD-MM-YY HH24:MI')
group by hs.BEGIN_INTERVAL_TIME, hss.sql_id, hss.plan_hash_value, hss.MODULE order by 1) where rownumber <= &N;

undef start_time
undef end_time
undef N

ASH and DBA_HIST are awesome views available to diagnose an issue.
I hope the scripts will be useful for you too!!!

2013 in review

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 140,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 6 days for that many people to see it.

Click here to see the complete report.

Identifying Bloated Index in Oracle

Indexes have always been a topic of interest for DBA/Developers. When it comes to index rebuild there have been many opinions floating across internet on when to rebuild these indexes. Many do say when the BLEVEL is > 3 one should rebuild the indexes. I don’t believe in that and i think i have never seen BLEVEL > 3 for index till now.

Over a period of time, the index can get fragmented because of the DML’s occurring on the table. The free space within the block of index can get used depending on the incoming column value, maintaining the index structure (sorted).

Now, suppose you have a table with one of the column as CREATION_DATE sysdate(DEFAULT), and every night data is loaded into it and suppose as per the application logic previous date data is deleted. Now an index having creation_date column will slowly and gradually increase in size and as the left side of the index will always be empty and index keeps growing toward right side.

With this kind of indexes, the performance many degrade for sqls , the plans many flip etc. It is sometimes good to rebuild indexes.But how to identify which indexes to be rebuild, is the question.

I was working on finding which indexes are bloated and below is the sql based on few logic

WITH spv AS (select di.table_name,
spv.object_name,
di.leaf_blocks,
di.index_type,
di.num_rows,
decode(di.uniqueness,'UNIQUE',0,1)uniq_ind,
di.last_analyzed,sum(bytes),
sum(io_cost) 
from v$sql_plan spv,dba_indexes di
     where spv.object_owner = 'ANAND' and         
           spv.object_type LIKE '%INDEX%' and 
           spv.object_name=di.index_name and 
           spv.object_owner=di.owner and 
           di.leaf_blocks > 1000
     group by di.table_name,
              spv.object_name,
              di.leaf_blocks,
              di.index_type,
              di.num_rows,
              di.uniqueness,
              di.last_analyzed order by 3)
select spv.table_name,
       spv.OBJECT_NAME index_name,
       spv.leaf_blocks leaf_blocks,
       round (100 / 90 *(spv.num_rows * (tab.rowid_length + spv.uniq_ind + 4) + sum((tc.avg_col_len)*(tab.num_rows)))/(8192 - 192))target_blocks, 
       round(((((spv.LEAF_BLOCKS) - (100 / 90 *(spv.num_rows * (tab.rowid_length + spv.uniq_ind + 4) +  sum((tc.avg_col_len)*(tab.num_rows)))/(8192 - 192)))/spv.LEAF_BLOCKS)*100)) DIFF_PCT
from spv, 
     (select table_name,num_rows,decode(partitioned,'YES',10,6) rowid_length  from dba_tables where owner='ANAND') tab,
     dba_tab_cols tc,
     dba_ind_columns ic 
where
     spv.table_name=tab.table_name and
     tc.column_name = ic.column_name  and
     tab.TABLE_NAME=tc.table_name and 
     ic.TABLE_NAME=tab.table_name and 
     spv.object_name=ic.INDEX_NAME
having round(((((spv.LEAF_BLOCKS) - (100 / 90 *(spv.num_rows * (tab.rowid_length + spv.uniq_ind + 4) + sum((tc.avg_col_len)*(tab.num_rows)))/(8192 - 192)))/spv.LEAF_BLOCKS)*100)) > 70
group by spv.table_name,
         spv.object_name,
         spv.leaf_blocks,
         spv.num_rows,spv.uniq_ind, 
         tab.rowid_length 
order by 5

Output from a test db –

TABLE_NAME                     INDEX_NAME                     LEAF_BLOCKS TARGET_BLOCKS   DIFF_PCT
------------------------------ ------------------------------ ----------- ------------- ----------
COMPLETED_CUST                 I_CCS_COND                           25340          7236         71
CUST_ONE_ITEMS                 I_CSI_REQ_ID                          7999          2351         71
DEMANDS                        I_DEMAND                             26920          7478         72
.....................................
.....................................
PROP_VALUES                    PK_SID_VALUE                         11847           831         93
SLA_METRS                      PK_SLA_METCS                         12840           129         99

–> leaf_blocks is the actual leaf blocks of the index from dba_indexes.
–> Target_Blocks represents the estimated no. of leaf blocks based on the current stats on the table. So, stats needs to be latest on the table.
–> The above sql displays all the index name where the % difference between leaf_blocks and estimated target blocks is > 70
–> The index names comes from v$sql_plan as those are the indexes being used by the optimizer.
–> The indexes having leaf_blocks > 1000 are selected

Now, lets rebuild the index and see do we get any closer target_blocks

12:53:24 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SLA_METCS';

LEAF_BLOCKS
-----------
        12840

12:52:59 DBA@test:1> alter index xxx.PK_SLA_METCS rebuild online parallel 4;

Index altered.

Elapsed: 00:00:00.87
12:53:24 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SLA_METCS';

LEAF_BLOCKS
-----------
        135  <-- Estimated TARGET_BLOCK was 129 

Elapsed: 00:00:00.43

13:15:46 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SID_VALUE';

LEAF_BLOCKS
-----------
      11847

Elapsed: 00:00:00.45
13:15:55 DBA@test:1> alter index xxx.PK_SID_VALUE rebuild online parallel 4;


Index altered.

Elapsed: 00:00:25.25
13:16:42 DBA@test:1> select leaf_blocks from dba_indexes where index_name='PK_SID_VALUE';

LEAF_BLOCKS
-----------
        840 <-- Estimated TARGET_BLOCK was 831

 

I would say this is just the version 1.0 :)

REFERENCE –> Script to investigate a b-tree index structure (Doc ID 989186.1)

Flashback : Guaranteed Restore Point

Oracle Flashback database and restore points enables us to rewind the database back in time to correct any problems caused by logical data corruption or user errors and it doesn’t require any restoration of backup. There are 2 types of restoration points –

1. Normal Restore Point –> assigns a restore point name to an SCN or specific point in time.The control file stores the name of the restore point and the SCN.

2. Guaranteed Restore Point –> Like Normal restore point, it also serves as an alias for an SCN in recovery operation. The only difference is that the guaranteed restore points never age out of the control file and must be explicitly dropped.

Logging for Guaranteed Restore Points with Flashback Logging Disabled

Assume that you create a guaranteed restore point when logging for Flashback Database is disabled. In this case, the first time a data file block is modified after the time of the guaranteed restore point, the database stores an image of the block before the modification in the flashback logs. Thus, the flashback logs preserve the contents of every changed data block at the time that the guaranteed restore point was created. Later modifications to the same block do not cause the contents to be logged again unless another guaranteed restore point was created after the block was last modified.


11:31:49 SYS@ORCL:1> select name,database_role,open_mode,flashback_on,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON       LOG_MODE
--------- ---------------- -------------------- ------------------ ------------
ORCL      PRIMARY          READ WRITE           NO                 ARCHIVELOG

11:31:54 SYS@ORCL:1> show parameter recovery

NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------------------------------------------------------------------------------------
db_recovery_file_dest          string      D:\oracle\flashback\orcl
db_recovery_file_dest_size     big integer 2G
recovery_parallelism           integer     0
11:31:56 SYS@ORCL:1>  SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

Create Guaranteed restore point –

11:31:58 SYS@ORCL:1> CREATE RESTORE POINT test_anand GUARANTEE FLASHBACK DATABASE;

Restore point created.

11:32:41 SYS@ORCL:1>
11:33:27 SYS@ORCL:1>  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
------------ ---------- ----------------------------------- --------------------- --- ------------
TEST_ANAND      1297697 14-JAN-13 11.32.39.000000000 AM                         2 YES     52428800

Alert log shows –


Mon Jan 14 11:32:39 2013
Starting background process RVWR
Mon Jan 14 11:32:39 2013
RVWR started with pid=25, OS id=7032 
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point TEST_ANAND

Lets create an user, table and do some dmls

11:35:36 SYS@ORCL:1> create user anand identified by anand123 default tablespace users;

User created.

11:36:16 SYS@ORCL:1>
11:36:17 SYS@ORCL:1> grant connect,resource to anand;

Grant succeeded.

11:36:25 SYS@ORCL:1>
11:36:42 SYS@ORCL:1> grant dba to anand;

Grant succeeded.

11:36:48 SYS@ORCL:1> conn anand/anand123
Connected.
11:36:52 ANAND@ORCL:1>
11:36:53 ANAND@ORCL:1> create table test as select * from all_objects;

Table created.
11:37:52 ANAND@ORCL:1> insert into test select * from test;

72583 rows created.

...................
....................
....................

11:39:01 ANAND@ORCL:1> insert into test select * from test;

1161328 rows created.

11:41:11 ANAND@ORCL:1> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
             1297697 2013.01.14 11:32:41             1440      104857600                        0

11:41:30 ANAND@ORCL:1>
11:41:31 ANAND@ORCL:1>
11:41:31 ANAND@ORCL:1> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME          END_TIME            FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2013.01.14 11:32:41 2013.01.14 11:41:50       12484608  199311360  278596608                        0

11:41:50 ANAND@ORCL:1>
11:42:35 ANAND@ORCL:1> select * from V$FLASHBACK_DATABASE_LOGFILE;

NAME                                                               LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME          TYPE
------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------- ------------------- ---------
D:\ORACLE\FLASHBACK\ORCL\ORCL\FLASHBACK\O1_MF_8H77W05C_.FLB           1          1          1   52428800       1297697 2013.01.14 11:32:41 NORMAL
D:\ORACLE\FLASHBACK\ORCL\ORCL\FLASHBACK\O1_MF_8H77W2JM_.FLB           2          1          1   52428800             0                     RESERVED


V$FLASHBACK_DATABASE_LOG –> displays information about the flashback data. Use this view to help estimate the amount of flashback space required for the current workload.

V$FLASHBACK_DATABASE_STAT displays statistics for monitoring the I/O overhead of logging flashback data.

11:43:32 ANAND@ORCL:1> insert into test select * from test;

2322656 rows created.

11:43:47 ANAND@ORCL:1> commit;

Commit complete.

11:44:47 ANAND@ORCL:1> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME          END_TIME            FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2013.01.14 11:32:41 2013.01.14 11:44:57       26501120  482787328  558669824                        0

11:44:57 ANAND@ORCL:1>

Now, lets try to flashback the database to restore point

11:52:24 SYS@ORCL:1> flashback database to restore point test_anand;
flashback database to restore point test_anand
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

To flashback the database must be in mount mode. Shutdown the db and mount it. Before mounting the database, moved all the archive logs generated from creating the restore point till the shutdown and tried flashback.

11:54:44 SYS@ORCL:1> flashback database to restore point test_anand;
flashback database to restore point test_anand
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1297662 to SCN 1297697
ORA-38761: redo log sequence 13 in thread 1, incarnation 2 could not be accessed

13:28:36 SYS@ORCL:1> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
------------------ ------------------- --------------------- -------------------
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0

13:29:21 SYS@ORCL:1>

Moved the archive log seq 13 (which was the seq# when guaranteed restore point was created)to the archive log destination and tried flashback

13:57:35 SYS@ORCL:1>  flashback database to restore point test_anand;

Flashback complete.

13:58:02 SYS@ORCL:1>

Alert log shows –

Mon Jan 14 13:57:54 2013
 flashback database to restore point test_anand
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
Flashback Media Recovery Log D:\ORACLE\ARCHIVE\ORCL\ORCL_0001_0000000013_0804355822
Mon Jan 14 13:58:02 2013
Incomplete Recovery applied until change 1297698 time 01/14/2013 11:32:41
Flashback Media Recovery Complete
Completed: flashback database to restore point test_anand
13:58:46 SYS@ORCL:1> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
             1297697 14-01-13 11:32:41             1440      104857600                   172032

13:59:07 SYS@ORCL:1> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
14-01-13 11:53:38 14-01-13 13:59:10          16384   25108480          0                        0

14:00:19 SYS@ORCL:1> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
------------------ ------------------- --------------------- -------------------
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0

15:11:28 SYS@ORCL:1> alter database open resetlogs;

Database altered.

15:11:58 SYS@ORCL:1>
15:12:06 SYS@ORCL:1> select username,account_status,default_tablespace,profile from dba_users where username='ANAND';

no rows selected

ORA-38500: Unsupported operation: Oracle XML DB not present

While trying to import using impdp got the below error

Table "ANAND"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "ANAND"."TEST":"Y2012_Q2_M06" failed to load/unload and is being skipped due to error:
ORA-38500: Unsupported operation: Oracle XML DB not present
..............

As it said XML db nor present, check for XDB status in dba_registry


COMP_NAME                            VERSION      STATUS
------------------------------------ ------------ --------
Oracle XML Database                  11.2.0.3.0   VALID

03:15:37 SYS > select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and owner = 'XDB';

no rows selected

As per DOC ID 1424643.1, the error can be generated when tables metadata of the exported table in the dumpfile and the existing table at the target DB are different.On checking the table structure there wasn’t any difference.

After spending some more time on it, finally decided to deinstall/install XDB.

Deinstall -- @?/rdbms/admin/catnoqm.sql
Install --@?/rdbms/admin/catqm.sql {XDB pwd} {XDB default tbs} {XDB temporary tbs} {SecureFiles = YES/NO}

For more details one can refer to Doc id 1292089.1

After this, impdp completed successfully. :)

Lets welcome 2013

Wishing everyone a very happy and prosperous New Year 2013.In this New Year, may you always be blessed with contentment, peace and abundance.

With 12c releasing in 2013, it would be a happening Year and lots of new things to learn and playaround :)

happy-new-year-2013-wallpapers-hd-03

Follow

Get every new post delivered to your Inbox.

Join 462 other followers