Flashback table

The table could be restored to using flashback table option. Before attempting it, find out how far to flash the table.

Steps :-

1. sql> select count(*) from scott.emp;

2.  export of the table 

3. flashback table

sql> alter table scott.emp enable row movement;

sql> flashback table scott.emp to timestamp

       to_timestamp('2010-12-18 12:00:00','YYYY-MM-DD HH24:MI:SS');

      

Flashback complete.

4. sql> select count(*) from scott.emp;

  COUNT(*)

----------

    784871

5. confirm with apps team

Hope this helps! Regards Rupam

Windows - Find space usage by windows folders using diruse

Directory Disk Usage, known as diruse is a free command line tool found on Microsoft's Help . Using diruse is easy. After you have downloaded the tool, install by clicking on diruse_setup.exe.

After installing the program, open a command prompt and run:

cd "\Program Files\Resource Kit"
diruse /M /* c:\

where:
/M – reports in Magabytes
/*  – Uses the top-level directories residing in the specified directory (In the above example C:\ is the specifed directory)

Below is the results of the output:

Date function in Oracle

Date function in Oracle

 examples of date function:

   insert into mydate values (sysdate -1);

   insert into mydate values (sysdate - 6/24); #   6 hours ago

  insert into mydate values (sysdate - 720/1440); # 12 hours ago
  click read for complete example

usage

 delete noprompt archivelog until time 'sysdate - 1';

 delete archivelog all backed up 1 times to device type disk completed before 'sysdate-6/24';

Tablespace details ( includes datafiles, type, autoextend, maxsize etc)

Summary

1. list datafiles

2. get type, auroextend, management type etc

Tablespace report

Run the following sql to get the space report in Oracle

Archiver Hung in Oracle database - ORA-16038, ORA-19504, ORA-00257

Archiver Hung in Oracle database

Steps to delete archive log file not needed for recovery (older than the last backup of the database)

Summary

1. check how much space is used by archiver

2. check the last good backup of the database

3. delete archive log files older than last good backup

4. crosscheck archive log

Blocking in Oracle Database

Follow the steps to locate and terminate blocking session from Oracle database (10g & up). Blocking_session_status contains ‘VALID’ when blocking_session is populated in 10g and up.

steps:

1. Display blocked session and their blocking session details

2. Find what is Blocking session Doing

3. Find sid and serial # of blocking session

4.  To terminate the session:

nfs volumes on Linux

-         Set the mount options explictly. Here are the mount options that need to be used for nfs volumes on Linux.

Use : rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0

default rsize and wsize for NFS is 4096 Blocks so if you have rsize=32k and wsize=32k then NFS would be able to read and write large datagram as compared to deafult one TCP option will make sure that your client are getting the data or not Hard & INTR - The program accessing a file on a NFS mounted file system will hang when the server crashes. The process cannot be interrupted or killed unless you also specify intr. When the NFS server is back online the program will continue undisturbed from where it was. actimeo is for access timeout and it should be 0 .

Hope this Helps!. Rupam

Table lock in Oracle

Explanation

Dropping or truncating a table requires you to acquire an exclusive lock on the table. A table is a "busy" resource if there are other sessions modifying or holding a lock on the same table.

Generate tkprof output the 10046 trace files using following syntax

 # (change the file name, username & password):

    tkprof sys=no explain=/

   

    tkprof ${1} ${1}.log sys=no explain=sysadm/wdutsrff \

    waits=yes \

    sort=exeqry,fchqry,prsqry

    sort=exeela,exeqry,fchela,fchqry,prsela,prsqry

    sort=exeela,fchela,prsela

Hope this helps. Regards Rupam

10046 Trace a Session sid and serial #

Step 1

Get sid and serial #

Step 2

Start trace

sqlplus as sysdba
exec sys.dbms_system.set_ev(130, 54378, 10046, 12, '');  # where (sid=130 and serial#=54378)

Step 3

end trace

exec sys.dbms_system.set_ev(130, 54378, 10046, 0, '');

Hope this helps. Regards Rupam

oradebug 10053 using pid

How to Trace SQL Using oradebug

To collect the event 10053 trace file, the following syntax was used in SQLPlus:

step 1
get pid

step 2
SQL> connect / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug event 10053 trace name context forever, level 1
SQL> ...enter your query here...
SQL> oradebug event 10053 trace name context off
SQL> oradebug tracefile_name

Top of Form

Hope this helps. Regards Rupam

oradebug 10046 using pid

How to Trace SQL Using oradebug

To collect the event 10046 trace file, the following syntax was used in SQLPlus:

step 1
get pid

step 2

Set trace level 10046, 12

SQL>  connect / as sysdba
SQL > oradebug setospid $ospid
SQL > oradebug unlimit
SQL > oradebug event 10046 trace name context forever , level 12

SQL > ...enter your query here...
SQL > oradebug event 10046 trace name context off
SQL> oradebug tracefile_name
SQL > exit

where ospid is the pid number from step 1

Notes :
Level
1 (SQL_TRACE),
4 (SQL_TRACE+BINDS),
8 (SQL_TRACE+WAITS),
12(SQL_TRACE+BINDS+WAITS)

Hope this helps. Regards Rupam

explain plan

Explain plan

steps:-

1. create plan table (global temporary table)

  sqlplus / as sysdba

 @?/rdbms/admin/catplan

2. populate plan table

   Note : 235530.1

   SQL> explain plan for

             

3. Displaying The Execution Plan

SQL> set linesize 150 

>  select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

Or > select * from  table(dbms_xplan.display('plan_table',null,'serial'));

Or > select * from table(dbms_xplan.display);

 

Or >  @?/rdbms/admin/utlxpls

Or > select * from table(dbms_xplan.display(null, null));

Or  > select plan_table_output from table(dbms_xplan.display('plan_table',null,'advanced'));

  More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql

Hope this helps. Regards Rupam

10053 trace

10053 trace is useful to get detailed information about SQL execution
 
# set event  10053 and trace sqlplus
alter session set tracefile_identifier = e10053_literal_Test3;
alter session set events '10053 trace name context forever, level 1';
run sql
alter session set events '10053 trace name context off';
exit from sqlplus

# use tkprof to generate report
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]



Hope this helps. Regards Rupam

10046 trace , tkprof

Tracing sql using 10046 event




# set trace 10046 event and trace sql
connect to user using sqlplus
alter session set tracefile_identifier = e10046_literal_test3;
alter session set events '10046 trace name context forever, level 12';
run sql
alter session set events '10046 trace name context off';
exit from sqlplus


Generate report from the trace using tkprof

use tkprof from unix prompt
Generate tkprof output the 10046 trace files using following syntax (change the file name, username & password):
tkprof sys=no explain=/

sort options for tkprof

• sort=exeqry,fchqry,prsqry
• sort=exeela,exeqry,fchela,fchqry,prsela,prsqry
• sort=exeela,fchela,prsela

Hope this helps. Regards Rupam

Autotrace , plustrace role

Using AUTOTRACE in SQL*Plus

Steps
 Creating the PLUSTRACE ROLE
 Granting the PLUSTRACE ROLE to the user
 Using autotrace

Trace for database

-- These may be used to start tracing at database level

EXECUTE dbms_monitor.database_trace_enable;

EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE);

EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE);

-- This may be used to start tracing at instance level

EXECUTE dbms_monitor.database_trace_enable (instance_name=>’RAC1);

-- All outstanding traces can be displayed in an Oracle Enterprise Manager report or

with the DBA_ENABLED_TRACES or v$client_stats views.

-- In the DBA_ENABLED_TRACES view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether

the trace is enabled for client identifier, session, service, database, or a

combination of service, module, and action.

Hope this helps. Regards Rupam

Trace levels

# Oracle Trace  Levels

     0 - No trace. Like switching sql_trace off.

     2 - The equivalent of regular sql_trace.

     1 (SQL_TRACE)

     4 (SQL_TRACE+BINDS)

     8 (SQL_TRACE+WAITS)

     12(SQL_TRACE+BINDS+WAITS)

Hope this helps. Regards Rupam

Oracle Database Backup Report - v$rman_backup_job_details

/*

-- rman - Query using V$RMAN_BACKUP_JOB_DETAILS is taking too long to execute both in SQL*Plus and also in DATABASE CONSOLE.

-- doc : 420200.1

*/

Steps :-

1. sqlplus as sysdba

2. execute following procedure

Sql> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); # deletes the statistics on the fixed object.

Sql> exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');   # lock that object so that statistics will not be collected in future.

  

Hope this helps. Regards Rupam

Database usage

Database feature usage report 

Method 1

1. Login to oem

2. Select database

3. Select server tab

4. Select database Feature Usage View

Method 2

1. sqlplus as system

2. execute following sql statement

SELECT output

FROM TABLE(dbms_feature_usage_report.display_text);

Hope this helps. Regards Rupam

List CPU patch applied to database

List CPU patch applied to database 

1. sqlplus  as system

2. execute the following sql statement

  set linesize 90 

 

  col action format a7

  col version format a10

  col id format 99999

  col when format a20

  select d.name,to_char(r.action_time,'YYYY/MM/DD.hh24:mi')    when,r.action,r.version,r.id,r.bundle_series

  from v$database d left outer join dba_registry_history r

  on r.bundle_series in ('CPU','PSU');

Hope this helps. Regards Rupam

Missing Tempfiles

Missing Tempfiles

Tempfiles are usually not backed up.

Since Oracle does not record checkpoint information in tempfiles, Oracle can start up a database with a missing tempfile.

Likewise, it is possible to remove (or in this case, not recreate) all tempfiles from a temporary tablespace and keep it empty.

But when a user attempts to sort to the TEMPORARY tablespace, an error is generated.

The solution is to add a new tempfile

--------

Redo log switch report from alert.log

Redo log file switch Report from alert.log using perl script

Redo log switch History

Redo log switch History

Find out  date  & time, SCN and other details about log switch

How Much Redo log is generated

Redo log Daily and Hourly volume calculated 

Find out how many MB of redo log is generated

Redo log Switch

Redo log Switch Rate by Date and Hour     

Find out how many log switches are taking place per hour

Redo Log Files and Sizing

 

Redo Log Files and Sizing         

set heading off;

select '******************************************************' from dual;

select '****           Redo Log Files and Sizing          ****' from dual;

select '******************************************************' from dual;

timing start 'Redo Sizing';

set heading on;

col "File Name" for a60;

col "Size in MB" format 999,999,999,999,990

select a.group#, thread#, substr(a.member,1,80) as "File Name",b.bytes/1024/1024 as "Size in MB" from v$logfile a,v$log b where a.group#=b.group#;

timing stop 'Redo Sizing';

Hope this helps. Regards Rupam

Find Sessions Generating Lots of Redo or Archive logs

Find Sessions Generating Lots of Redo or Archive logs

1. Login to a user with dba privilege

2. Execute the query

sql> SELECT s.sid, s.serial#, s.username, s.program,  i.block_changes

         FROM v$session s, v$sess_io i

       WHERE s.sid = i.sid 

       ORDER BY 5 asc, 1, 2, 3, 4;

3. Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

Hope this helps. Regards Rupam

Redo Log File - add and drop

Steps to add and drop redo log in database using ASM

1. Check redo log file destination

sql> show parameter db_create

NAME                                 TYPE        VALUE

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

db_create_file_dest                        string      +ORADATA001

db_create_online_log_dest_1          string      +ORADATA001

db_create_online_log_dest_2          string      +ORAFLASH001

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

CPU Patch

CPU Patch Install  Process  

1. Follow the instructions  to apply the  patch using OPatch but do not start the instances or the listeners

Apply the Patch using Opatch

2. Apply the CPU sql script to each database from one node/instance only

   

    set your environment to the instance name
    cd  directory specified in README.txt of patch

    > cd $ORACLE_HOME/rdbms/admin

       sqlplus / as sysdba

       SQL>@catbundle.sql cpu apply

       SQL>@utlrp

       SQL>exit

3. After all databases have been updated, start the remaining instances(in RAC).

4. Start the listeners

5. Check connectibity

Hope this help. Regards Rupam

change adrci destination

How to change adrci diagnostics destination in 11g.

Steps :

1. sqlplus as sysdba

2. check current diagonostic destination

Sample Interview Questions

1. for standby – which one is used archive log or redo log?

2. steps to apply patchset  

5. steps for disaster recovery

5. Asm – to add space  is required  shutdown?

6. How to add datafiles to database ?

6. why stats is important?

7. steps to clone a database

8. one morning got the performance issues. What do you do ?

9. change management process

10.education and certification

Hope this help. Regards Rupam

tablespace

Space Management in Oracle

-- create system managed tablespace using OFA

     CREATE SMALLFILE TABLESPACE USERS

     LOGGING

     DATAFILE SIZE 2M

     AUTOEXTEND ON

     MAXSIZE UNLIMITED

     EXTENT MANAGEMENT LOCAL

     SEGMENT SPACE MANAGEMENT AUTO;

# to add datafile

alter tablespace USERS add datafile size 10M autoextend on;

   

Register the database with CRS

CRS  SRVCTL

Register database with CRS to enable automatic startup/shutdown of database and services when server is bounced

To register the database with CRS

Peepli live

Review: Peepli live dekhe.

Gather statistics of Partition Tables Part 2

--  collect global statistics on Partition Tables

Option # 1

set serveroutput on

set time on

declare

this_day       char(8);

part_name    char(10);

tab_name     varchar2(30);

v_num          number;

v_string        varchar2(4000);

cursor tab_cur IS select table_name from user_tables where PARTITIONED='YES'  and table_name in ('EVENT_LOG1','EVENT_LOG2');

begin

for table_rec in tab_cur loop

  exit when tab_cur%NOTFOUND;

  tab_name := table_rec.table_name;

  dbms_stats.gather_table_stats( ownname =>'SCOTT', tabname => tab_name, granularity=> 'GLOBAL', estimate_percent => 15, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', degree => 1, cascade => TRUE );

end loop;

end;

/

Note : granularity is GLOBAL, which means only GOBAL statustics on partition table  is going to be collected.

For other options, check Gather statistics of Partition Tables Part 1

Option # 2

begin

dbms_stats.gather_table_stats(ownname=>SCOTT',

tabname=>'EVENT_LOG1’,

granularity=>'ALL',

estimate_percent=>15,

method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree => 1, cascade=>TRUE); end;

/

Note : granularity is ALL, which means Gathers all (subpartition, partition, and global) statistics.

Hope this help. Regards Rupam

Gather statistics of Partition Tables Part 1

Granularity of statistics to collect (only pertinent if the table is partitioned).

Constant	Description
ALL	Gathers all (subpartition, partition, and global) statistics
AUTO	Determines the granularity based on the partitioning type. This is the default value
DEFAULT	Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality
GLOBAL	Gathers global statistics
GLOBAL AND PARTITION	gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
PARTITION	gathers partition-level statistics
SUBPARTITION	gathers subpartition-level statistics

Hope this help. Regards Rupam

Gather Statistics in Oracle part 2

-- collect statistics for missing statistics tables

declare

cursor miss_cur IS select table_name from user_tables where last_analyzed is null;

begin

for miss_rec in miss_cur loop

  exit when miss_cur%NOTFOUND;

  dbms_stats.Gather_Table_Stats('SCOTT',miss_rec.table_name,

           estimate_percent => 15,

           method_opt       =>'FOR ALL INDEXED COLUMNS SIZE 1',

           cascade          => TRUE);

  end loop;

end;

/

Hope this help. Regards Rupam

Gather Statistics in Oracle part 1

dbms_stats  package used for collecting statistics

summary

   gather statistics -   system, dictionary, fixed_objects , sys  

   gather schema statistics

   gather table stats

   gather index stats

Install Oracle InstantClient basic and instantclient sqlplus on win32

1. Download Oracle Instant Client  from Oracle site

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

e.g

   instantclient-basic-win32-11.1.0.7.0.zip
   instantclient-sqlplus-win32-11.1.0.7.0.zip

Oracle JDBC Connections

Example of connect string using jdbc

Recommend

the method we use for WebSphere:

jdbc:oracle:thin:@ldap://oid-uat:389/demo,cn=OracleContext,dc=csxt,dc=csx,dc=com  

A single instance:

   jdbc:oracle:thin:@lnx101-vip:1521:demo

Using TNSName: 

   jdbc:oracle:thin:@demo.world

Full URL:

   jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = lnx101-vip.csxt.csx.com)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = lnx102-vip.csxt.csx.com)(PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = on)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)(FAILOVER_MODE =(TYPE = session)(METHOD = basic)(RETRIES = 10)(DELAY = 1))))

another option :

jdbc:oracle:thin:@ldap:// oid-dev:389/ demo,cn=OracleContext,dc=csxt,dc=csx,dc=com ldap:// oid-dev:389/ demo,cn=OracleContext,dc=csxt,dc=csx,dc=com

Hope this help. Regards Rupam

Install Oracle Instant Client Installation on UNIX / Linux Server

1. Confirm you can login as the oracle account (primary group dba) on the target server:

[oracle@lnx544 ~]$ id

uid=5005(oracle) gid=56(dba) groups=56(dba),57(oinstall),58(dbasudo),102(operator),2003(prpftp),2004(tcsgrp),5084(paiseasftp),5107(da)

Oracle Instant Client

Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle.

Hope this help. Regards Rupam

Recover database until cancel

Quick steps

1. Setup oracle database environment using . oraenv

2. restore controlfile

    $ rman target / nocatalog

    RMAN> set dbid=1185150074;

    RMAN> startup nomount;

    RMAN> run

                {

                restore controlfile from ‘/u01/BACKUP/demo/demo_control_backup.ctl’;

                }

RMAN Configuration

The default RMAN configuration may be changed as per need. Here is sample configuration change and also changing it back to default setting.

Connect to database using RMAN
$ rman target / nocatalog

# to check the current configuration

show all;

RMAN Backup quick reference guide

configure RMAN configuration

$ rman target / nocatalog

RMAN>configure controlfile autobackup on;

RMAN>configure retention policy to recovery window of 14 days;

RMAN backup Report - Oracle Database Backup Report

 script  : RMAN backup Report - Oracle Database Backup Report

Script : chkbkpstatus.sql

 set pages 999 lines 120
   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       -- SESSION_KEY,
       INPUT_TYPE, STATUS,
       to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
       to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

Output

INPUT_TYPE    STATUS    START_TIME      END_TIME    HRS DEV   INBYTES    OUTBYTES

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

ARCHIVELOG    COMPLETED 08/11/10 15:00  08/11/10 15:01      .02 DISK    257.08M    107.06M

DB FULL       COMPLETED 08/11/10 21:00  08/11/10 23:57     2.96 DISK    227.16G     43.24G

ARCHIVELOG    COMPLETED 08/12/10 06:00  08/12/10 06:01      .02 DISK    260.92M     87.39M

ARCHIVELOG    COMPLETED 08/12/10 15:00  08/12/10 15:01      .02 DISK    250.10M    104.85M

Hope this help. Regards Rupam

Delete archive log using RMAN

Caution : By deleting archive log file, you may not be able to perform point-in-time recovery of the database.

Summary

1. connect to database using rman
2. issue archive log delete command and confirm
3. crosscheck archivelog all

Recover from Consistent Backup

Scenario  : One of more datafiles are lost of a nonarchive database

Action    :  Recover the database from consistent backup

RMAN Consistent Backup

A Consistent backup of a Database is made by starting the Database in MOUNT mode. The backup is consistent, and does not require recovery after it is restored. 

Negative Values For Tablespace

11g OEM Grid Control

OEM 11g Bug

bug 9548105

• OEM 11g doesn't list datafiles when tablespace is 100% full
• Negative Values Reported By Dbconsole For Tablespace With At Least Two Datafiles

AWR

Oracle Database Performance

AWR

Metalink Note:276103.1

STATISTICS_LEVEL initialization parameter must be set to the

TYPICAL or ALL to enable the Automatic Workload Repository

ADDM Report

Oracle Database Performance

ADDM Report

Option 1 : OEM

               Database -> Advisor Center -> ADDM

Option 2 : using sqlplus

@$ORACLE_HOME/rdbms/admin/addmrpt.sql  

Input : begin_snap

           ending_snap

and      filename

            

Hope this help. Regards Rupam

ADDM

Oracle Database Performance

ADDM

The Automatic Database Diagnostic Monitor ( ADDM ) is an advisor which detects problem area' s in the database and and which gives recommendations.

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?

Oracle Roles

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?

Datapump Orphaned Job

Datapump Orphaned Job

Follow the steps to cleanup Datapump orphaned job

DataPump

DataPump  

Setup

Connect to instance as sysdba using sqlplus

grant create any directory to scott;

create or replace directory dpdump as '/u01/app/oracle/export/';

grant read, write on directory dpdump to scott;

Check the directories

sql>  select * from dba_directories;

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter.

Export

expdp scott/tiger@demo parfile=expdp.par

script : expdp.par

job_name=job1

directory=dpdump

filesize=20000000000

schemas=scott

dumpfile=expdp_demo_scott.dmp

logfile=expdp_demo_scott.log

FLASHBACK_TIME="TO_TIMESTAMP('05-10-2010 10:30:00', 'DD-MM-YYYY HH24:MI:SS')"

Import

impdp scott/tiger@demo parfile=impdp.par

script : impdp.par

directory=DPDUMP

job_name=job1

EXCLUDE=statistics

schemas=SCOTT

dumpfile=expdp_demo_scott.dmp

logfile=impdp_demo_scott.log

Monitoring

Sql> select count(*) from scott.job1;   #

Monitor export Using expdp

expdp scott/tiger@demo attach=job1

Hope this help. Regards Rupam

Gather Statistics Intro

Gather Statistics Intro

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-06512 and ORA-00955

expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-06512 and ORA-00955

While exporting data using expdp, got following error

Error

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYS.JOB1"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1020

ORA-00955: name is already used by an existing object

Cause

There is already as job with same  name in the master table

Solution

Option 1: Change the job name

Option 2: Clean up Orphaned job. Read More

Refresh schema

Refresh schema

Summary

Refresh test database schema(scott) with production database schema(scott).

1. export production database schema(scott) using datapump
2. copy dump file to test server
3. generate script to drop objects from test database
4. drop objects from test schema(scott) and purge from recyclebin
5. import data into test database schema(scott)
6. recompile invalid objects
7. match the objects count between production and test schema
8. collect statistics in test database schema (scott)
9. open for business

Flashback Table to Before drop

Oracle FlashBack Table

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline.

Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints

Example:

 sql> DROP TABLE flashback_drop_test;

 sql> SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

You can retrieve it and assign it a new name using a RENAME TO clause.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP

RENAME TO flashback_drop_test_old;

### Several purge options exist:

PURGE TABLE tablename;

PURGE INDEX indexname;

PURGE TABLESPACE ts_name;

PURGE TABLESPACE ts_name USER username;

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;   

### Query Recycle Bin

col original_name format a10

col owner format a10

col type format a10

col droptime format a10

col pace format a12

select owner, original_name, object_name, type, droptime

from dba_recyclebin

where can_undrop='YES';

### Query the dropped table data from Recycle bin

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

### Bypass the Recycle Bin

drop table flashback_drop_test purge;

Hope this help. Regards Rupam