Create Database Structure using Datapump

You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT. When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader.

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.

Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.

If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.

The target table cannot be index organized or part of a cluster.

The target table cannot contain object type columns.

The target table cannot have any triggers or referential integrity constraints defined on it.

The target table cannot be replicated.

A transaction containing a direct-path INSERT statement cannot be or become distributed.





Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TABLE','INDEX','LOB','PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER');



best way to import full=Y content=metadata_only

and drop sequeunce, link, directories and recreate


G:\app\Administrator\product\11.2.0\dbhome_1\BIN\sqlplus.exe sys/oracle@apx1213srv as sysdba

CREATE DIRECTORY EXPDP11G AS 'd:\exp1213';

GRANT read, write ON DIRECTORY  EXPDP11G TO ldbo;

G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@nbs1213srv directory= EXPDP11G full=Y dumpfile=EXPDP11G.DMP logfile=expdp11meta.log content=metadata_only EXCLUDE=TABLESPACE,STATISTICS,DB_LINK,SEQUENCE


d:\app\Administrator\product\11.2.0\dbhome_1\bin\oradim.exe -delete -sid apx1314


select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;

G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1213srv full=Y directory=EXPDP11G dumpfile=EXPDP11G.DMP LOGFILE=nbs1314_data.LOG content=data_only data_options=skip_constraint_errors


----------------Final-----------------------EXP
d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv Full=Y directory=EXPDP11G dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG  content=metadata_only EXCLUDE=STATISTICS


----------------------
d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv directory=EXPDP11G dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG  content=metadata_only schemas=LDBO EXCLUDE=TABLESPACE,DB_LINK,DIRECTORY,JOB,STATISTICS,USER,ROLE,SEQUENCE

d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv directory=EXPDP11G dumpfile=apx1213userROLE.DMP LOGFILE=apx1213userROLE.LOG  content=metadata_only schemas=LDBO INCLUDE=USER,ROLE

d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv directory=EXPDP11G dumpfile=apx1213Synonyms.DMP LOGFILE=apx1213Synonyms.LOG  content=metadata_only INCLUDE=SYNONYM

-------------------------------------------IMP-----------------------------------------------create optable-----------------------------
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG schemas=LDBO EXCLUDE=TABLESPACE,DB_LINK,JOB,USER,ROLE,SEQUENCE sqlfile=optable.sql

d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213Synonyms.LOG INCLUDE=SYNONYM sqlfile=synonyms.sql


d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213Synonyms.LOG INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='LDBO')"


d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213userROLE.LOG IMPDPINCLUDE=USER,ROLE_GRANT,SYSTEM_GRANT,GRANT,OBJECT_GRANT sqlfile=users.sql


D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1314srv directory=DATA_PUMP_DIR dumpfile=EXPDP11GFull.DMP LOGFILE=EXPDP11GFull.DMP.LOG schemas=LDBO table_exists_action=append data_options=skip_constraint_errors

D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1314srv directory=DATA_PUMP_DIR dumpfile=EXPDP11GFull.DMP LOGFILE=EXPDP11GFull.DMP.LOG schemas=LDBO table_exists_action=replace data_options=skip_constraint_errors

REPLACE option drop the current table in the database and the import recreate the new table as in the dumpfile.
----------------------------------------------------------------------IMPDP----------------------------------------------------------------------------------------------------------
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG schemas=LDBO EXCLUDE=TABLESPACE,DB_LINK,JOB,USER,ROLE,SEQUENCE transform=pctspace:70

 transform=pctspace:70 will reduce intial_extents, next_extents 70%.

select sum(initial_extent/1024/1024/1024) from user_tables;
select Sum(Initial_Extent/1024/1024/1024) from User_Indexes;


d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213Synonyms.LOG INCLUDE=SYNONYM

d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213userROLE.LOG INCLUDE=USER,ROLE

-------------------------------SEQUENCE CREATION--------------------------------------------
SELECT 'Create Sequence ' || Sequence_Name || ' minvalue ' ||   Min_Value || ' maxvalue ' || Max_Value || ' INCREMENT BY ' || Increment_By || ' CACHE ' || Cache_Size || ';' FROM USER_Sequences WHERE SEQUENCE_NAME NOT LIKE 'SQFIN%';

------------------------------------------------------------USER ROLE CREATION------------------------------------------------------------------------------------------------------------------------
SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;

set head off
set pages 0
set long 9999999
spool c:\user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off;



SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;
Select 'GRANT ' || Privilege || ' ON ' ||Table_Name || ' TO "' || Grantee || '";' From Dba_Tab_Privs Where  OWNER='LDBO' ORDER BY GRANTEE,TABLE_NAME;
Select 'GRANT ' || Privilege || ' TO "' || Grantee || '";' From Dba_sys_Privs ORDER BY GRANTEE;
select 'GRANT ' || Privilege || ' (' || COLUMN_NAME ||  ') ON ' ||Table_Name || ' TO "' || Grantee || '";' from Dba_Col_Privs;
Select 'GRANT ' || Granted_Role || ' TO "' || Grantee || '";' From Dba_role_Privs ORDER BY GRANTEE;


---------------------------------DATABASE LINK CREATION

SELECT 'CREATE ' || OWNER || ' DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS  WHERE USERNAME !=' ';

SELECT 'DROP ' || OWNER || ' DATABASE LINK ' || DB_LINK || ';' FROM DBA_DB_LINKS WHERE USERNAME !=' ';



------------------------------------------DIRECTORY CREATION
select 'create directory '||OWNER||'.'||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*)  from user_objects where status='INVALID';
SELECT * FROM DBA_OBJECTS WHERE Owner='PUBLIC';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;


EXEC sys.UTL_RECOMP.recomp_serial('LDBO');


Select * From  Dba_Tablespace_Usage_Metrics;

Select * From Dba_Hist_Tbspc_Space_Usage;

Select Sum(Bytes)/1024/1024, Sum(Bytes)/1024/1024/1024 FROM dba_data_files;----------------DATABASE SIZE

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

INCLUDE=SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='LDBO')" sqlfile=synonyms.sql

INCLUDE=SYNONYM:\"in \(select synonym_name FROM dba_synonyms where table_owner like \'LDBO%\'\)\"

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

EXCLUDE=SEQUENCE:\"in \(select SEQUENCE_NAME from USER_Sequences where SEQUENCE_NAME like \'SQFIN%\'\)\"

SELECT * FROM USER_Sequences WHERE SEQUENCE_NAME NOT LIKE 'SQFIN%';
-------------------------------MANUALLY CREATION

RECREATE SEQUENCE BECAUSE LAST NUMBER IS START WILL PREVIOUS YR VALUE
Users and roles are imported BUT RECREATE AGAIN SOME USER,OBJECT ROLES CAN MISSINGS

Select COUNT(*) From Dba_Users;
Select Count(*) From Dba_Roles;
Select COUNT(*) From Dba_Users;
Select Count(*) From Dba_Roles;
Select Grantee,Count(*) From Dba_Role_Privs Group By Grantee;
Select Grantee,Count(*) From Dba_TAB_Privs Group By Grantee;
Select * From Dba_Directories;
Select * From Dba_Db_Links;

SELECT 'Create Sequence ' || Sequence_Name || ' minvalue ' ||   Min_Value || ' maxvalue ' || Max_Value || ' INCREMENT BY ' || Increment_By || ' CACHE ' || Cache_Size || ';' FROM USER_Sequences WHERE SEQUENCE_NAME NOT LIKE 'SQFIN%';

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

expdp ldbo/ldbo@apx1213srv full=Y directory=dpump_dir1 dumpfile=apx1213_meta.DMP LOGFILE=apx1213_meta.LOG  content=metadata_only

impdp ldbo/ldbo@apx1314srv full=Y directory=dpump_dir1 dumpfile=apx1213_meta.DMP LOGFILE=apx1314_meta.LOG EXCLUDE=DB_LINK,DIRECTORY,JOB,STATISTICS

impdp ldbo/ldbo@NBS1314srv full=Y directory=DATA_PUMP_DIR dumpfile=apx1213_meta.DMP LOGFILE=apx1314_meta.LOG EXCLUDE=DB_LINK,JOB,STATISTICS

IMPDP userid="'sys/oracle@NBS1314srv as sysdba'"  full=Y directory=DATA_PUMP_DIR dumpfile=apx1213_meta.DMP LOGFILE=apx1314_meta.LOG


D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe
------------------DATA IMPORT--------------

D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1314srv full=Y directory=EXPDP11G dumpfile=EXPDP11G15102012.DMP LOGFILE=apx1314.LOG content=data_only


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



DIrectory=EXPORT_AUTO dumpfile=apx1213_SYSmeta.DMP LOGFILE=apx1213_SYSmeta.LOG content=metadata_only schemas=LDBO,SYS INCLUDE=PROCEDURE,TRIGGER


EXCLUDE=DB_LINK,DIRECTORIES,JOB,STATISTICS




exec dbms_stats.delete_database_stats;

1) Users and roles are imported BUT RECREATE AGAIN SOME USER,OBJECT ROLES CAN MISSINGS


Select * From Dba_Users;
SELECT * FROM DBA_ROLES;
Select * From Dba_Role_Privs;
Select * From Dba_TAB_Privs;
Select * From Dba_SYS_Privs;

Select * From Dba_Directories;
Select * From Dba_Db_Links;

SELECT * FROM Dba_Scheduler_Jobs;


BEGIN sys.UTL_RECOMP.recomp_serial('LDBO'); END;
/

select object_name from user_objects where status='INVALID';

SELECT * FROM Dba_Scheduler_Jobs;

SELECT * FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');

CREATE SPLOGININFORMATION,TGLOGININFORMATION MANUALLY

DROP SEQUENCE
SELECT * FROM USER_Sequences WHERE SEQUENCE_NAME LIKE 'SQFIN%';



DATABASE LINK 2
FUNCTION 137
INDEX 730
JOB 21
LOB 255
PACKAGE 115
PACKAGE BODY 115
PROCEDURE 443
QUEUE 6
SEQUENCE 505
TABLE 981
TABLE PARTITION 368
TRIGGER 250
TYPE 13
VIEW 32


Select Tablespace_Name,SUM(bytes/1024/1024/1024) From DBA_Extents GROUP BY Tablespace_Name;




now let’s generate it again but using the transform parameter to reduce the size of it to 70% of original size.

impdp user/password transform=pctspace:70 content=metadata_only directory=datapump dumpfile=ex.dmp



before is to import it telling the Data Pump to reduce the size of extents to 70%


http://oraclenz.wordpress.com/2010/04/29/tip-of-the-month-how-to-have-super-powers-using-data-pump/

http://www.dba-oracle.com/t_data_pump_pctspace.htm




root> impdp transform=pctspace:25

This tells the impdp (import) to re-size the tablespace to 25% of its original size.

 when the table actually gets imported than the extent sizes are same.


impdp scott1/tiger1 directory=dump_dir dumpfil
e=SCOTT1.DMP logfile=import1.log remap_schema=scott:scott1 SQLFILE=dump_dir:SQLf
ile2.log TRANSFORM=PCTSPACE:70


select
  tablespace_name,
   file_id,
   owner,
   segment_name,
   block_id begin,
   blocks,
   block_id+blocks-1 end,
   bytes/1024 KB,
   '' free
from sys.dba_extents
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
union
select
   tablespace_name,
   file_id,
   '' owner,
   '' segment_name,
   block_id begin,
   blocks,
   block_id+blocks+1 end,
   bytes/1023 KB,
   'F' free
from sys.dba_free_space
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
order by 1, 2, 5
;

For IMPORT:
$impdp parfile=import.par

import.par file contains :

full=y
directory=dump_files1
dumpfile=CR06172102_%Uexport.dmp
logfile=CR06172102_import.log
job_name=CR06172102_import
parallel=8
EXCLUDE=TABLESPACE
EXCLUDE=DIRECTORY
EXCLUDE=SCHEMA:"='TSMSYS'"
EXCLUDE=SCHEMA:"='SYS'"
EXCLUDE=SCHEMA:"='SYSTEM'"
EXCLUDE=SCHEMA:"='WMSYS'"
EXCLUDE=SCHEMA:"='DBSNMP'"
EXCLUDE=SCHEMA:"='OUTLN'"
EXCLUDE=SCHEMA:"='SYSMAN'"
EXCLUDE=SCHEMA:"='ANONYMOUS'"
EXCLUDE=SCHEMA:"='CTXSYS'"
EXCLUDE=SCHEMA:"='DIP'"
EXCLUDE=SCHEMA:"='ORAMAN'"
EXCLUDE=SCHEMA:"='XDB'"

expdp "'/ as sysdba'" DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=TRANS_HRDB%U.dmp LOGFILE=TRANS_HRDB.log FULL=Y "EXCLUDE=TABLESPACE,PROFILE,DIRECTORY,statistics,SCHEMA:\"IN ('ARCHIVE_USER','TRANS_HRDB','GLOBAL_HRDB','GLOBAL_INTERFACE_USER','TRANS_HRDD','PERF_DIRECT','GCM_DATA','GCM_APP','COMP','COMP_HIST','SYS','SYSTEM','SYSMAN','RMANUSER','RMAN','EXFSYS','WMSYS','DBSNMP')\"" PARALLEL=4



------------------------------Error

Oracle Bug ID 1434393.1
DataPump Import (IMPDP) With CONTENT=DATA_ONLY Fails With Error ORA-39126 Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [ID 1434393.1]


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "LDBO"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "LDBO"."SYS_IMPORT_SCHEMA_01":  ldbo/********@NBS1314srv directory=DAT
_PUMP_DIR dumpfile=EXPDP11GFull.DMP LOGFILE=EXPDP11GFull.DMP.LOG content=data_only schemas=LDBO
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [112]
TABLE_DATA:"LDBO"."SCHEDULER$_JOB_ARG"
ORA-31603: object "SCHEDULER$_JOB_ARG" of type TABLE not found in schema "LDBO"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FFB3973380     18990  package body SYS.KUPW$WORKER
000007FFB3973380      8192  package body SYS.KUPW$WORKER
000007FFB3973380     18552  package body SYS.KUPW$WORKER
000007FFB3973380      4105  package body SYS.KUPW$WORKER
000007FFB3973380      8875  package body SYS.KUPW$WORKER
000007FFB3973380      1649  package body SYS.KUPW$WORKER
000007FFB360AD30         2  anonymous block

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [112]
TABLE_DATA:"LDBO"."SCHEDULER$_JOB_ARG"
ORA-31603: object "SCHEDULER$_JOB_ARG" of type TABLE not found in schema "LDBO"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FFB3973380     18990  package body SYS.KUPW$WORKER
000007FFB3973380      8192  package body SYS.KUPW$WORKER
000007FFB3973380     18552  package body SYS.KUPW$WORKER
000007FFB3973380      4105  package body SYS.KUPW$WORKER
000007FFB3973380      8875  package body SYS.KUPW$WORKER
000007FFB3973380      1649  package body SYS.KUPW$WORKER
000007FFB360AD30         2  anonymous block

Job "LDBO"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at 15:12:28






. . imported "LDBO"."TBLAUDITEMAILACCOUNTDETAIL"         688.8 MB  389588 rows
ORA-31693: Table data object "LDBO"."JOURNDRCR" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-02291: integrity constraint (LDBO.JOURNALCODE) violated - parent key not found

C:\Users\Administrator>G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@apx1213srv directory= EXPDP11G dumpfile=expdp11g.dmp logfile=expdp11meta.log SCHEMAS=LDBO content=DATA_ONLY data_options=skip_constraint_errors

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39034: Table TABLE_DATA:"LDBO"."SCHEDULER$_JOB_ARG" does not exist.



C:\Users\Administrator>G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@apx1213srv directory= EXPDP11G dumpfile=expdp11g.dmp logfile=expdp11meta.log  Tables=scheduler$_JOB_arg

C:\Users\Administrator>G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@apx1213srv directory= EXPDP11G dumpfile=expdp11g.dmp logfile=expdp11meta.log SCHEMAS=LDBO content=DATA_ONLY data_options=skip_constraint_errors

Create Schema Objects

Note: This will not work from 10g to 11g version......................procedure will be corrupted


set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
exec  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
exec  dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);

SPOOL C:\OPTABLE.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC') metadata from dba_synonyms where owner = 'PUBLIC'   and table_owner = 'LDBO' ;
Select Dbms_Metadata.Get_Ddl('SEQUENCE',Sequence_Name)  from User_Sequences Where Sequence_Name Not Like 'SQFIN_%';
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE = 'TABLE';
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN 'INDEX' ;
Select Dbms_Metadata.Get_Ddl('CONSTRAINT', Constraint_Name) Ddl FROM USER_CONSTRAINTS WHERE Constraint_Type='C' And Constraint_Name Not Like 'BIN%'  order by table_name;
Select Dbms_Metadata.Get_Ddl(decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner)  CONSDDL From Dba_Constraints WHERE owner='LDBO' AND CONSTRAINT_NAME NOT LIKE 'BIN$%' and constraint_type IN('P', 'R', 'U') Order By Case When Constraint_Type In('P','U') Then 0 Else 1 End;
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE');
Select Dbms_Metadata.Get_Ddl('PACKAGE_SPEC', Object_Name)  From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE');
Select Dbms_Metadata.Get_Ddl('PACKAGE_BODY', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE BODY');
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TRIGGER');

SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) DDL FROM DBA_USERS where username in (select username from dba_ts_quotas);
SELECT DBMS_METADATA.GET_DDL('ROLE', role) FROM dba_roles;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', GRANTEE) FROM DBA_SYS_PRIVS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', GRANTEE) FROM DBA_TAB_PRIVS WHERE OWNER='LDBO';
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', GRANTEE) FROM DBA_ROLE_PRIVS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_role_privs);
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_sys_privs)
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off



--------------------------------------------------------------objects---------------------------------------------------------

db_link
sequence
directory
synonym
type
table
index
constraint
package
function
procedure
compile invalid
view
ref_constra
package body
type body
trigger
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
JOB

--------------------------------------------checking------------------------------------------------------------------

Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*)  from user_objects where status='INVALID';
SELECT * FROM DBA_OBJECTS WHERE Owner='PUBLIC';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
select Constraint_Type,count(*) from User_Constraints group by Constraint_Type;
select table_name,count(*) from User_Constraints group by table_name order by 1;
select table_name,Search_Condition from User_Constraints where Constraint_Type='C' and table_name not like 'BIN%' ORDER BY 1;


---------------------------------------------------------------Directory Creation-------------------------------------------

select 'create directory '||OWNER||'.'||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;
or
select dbms_metadata.get_ddl('DIRECTORY',directory_name) from dba_Directories;


get and change the path as you want


------------------------------------------------------------------Database link--------------------------------------------

SELECT 'CREATE ' || OWNER || ' DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS  WHERE USERNAME !=' ';

Select 'DROP '||Decode(U.Name,'PUBLIC','public ')||'database link '||Chr(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10) ||';' TEXT
From Sys.Link$ L, Sys.User$ U
Where L.Owner# = U.User#;

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10)
||' connect to ' || L.Userid || ' identified by values '''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
From Sys.Link$ L, Sys.User$ U
WHERE L.OWNER# = U.USER# AND L.USERID !=' ';



CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
  CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';

 CREATE PUBLIC DATABASE LINK "LNK_CCM"
  CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';

CREATE DATABASE LINK "LNK_DIGITAL"
  CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';

create database link LNK_PREVIOUSYEARBALANCE connect to LDBO identified by ldbo using 'APX1314SRV';

CREATE PUBLIC DATABASE LINK "CMLDLINK"
  CONNECT TO ldbo IDENTIFIED BY ldbo USING 'APX1314SRV';


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

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
exec  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
exec  dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);


SPOOL C:\SEQ.SQL
Select Dbms_Metadata.Get_Ddl('SEQUENCE',Sequence_Name)  from User_Sequences Where Sequence_Name Not Like 'SQFIN_%';
SPOOL OFF

spool c:\tables.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE = 'TABLE';
spool off

spool c:\index.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN 'INDEX' ;
spool off

spool c:\syn.sql
select dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC') metadata from dba_synonyms where owner = 'PUBLIC'   and table_owner = 'LDBO' ;
spool off

spool c:\consc.sql
Select Dbms_Metadata.Get_Ddl('CONSTRAINT', Constraint_Name) Ddl FROM USER_CONSTRAINTS WHERE Constraint_Type='C' And Constraint_Name Not Like 'BIN%'  order by table_name;
spool off


spool c:\consp.sql
Select Dbms_Metadata.Get_Ddl(
decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner)  CONSDDL
From Dba_Constraints
WHERE owner='LDBO' AND CONSTRAINT_NAME NOT LIKE 'BIN$%'
and constraint_type IN('P', 'R', 'U')
Order By Case When Constraint_Type In('P','U') Then 0 Else 1 End;
spool off


spool c:\proc.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE');
spool off

spool c:\pack.sql
Select Dbms_Metadata.Get_Ddl('PACKAGE_SPEC', Object_Name)  From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE');
spool off

spool c:\packb.sql
Select Dbms_Metadata.Get_Ddl('PACKAGE_BODY', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE BODY');
spool off

spool c:\trigger.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TRIGGER');
spool off


------------------------------------------------------------------User Creation and its role--------------------------------------------
set head off
set pages 0
set long 9999999
spool c:\user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off;



--------------------------------------------------------------Role Creation------------------------------------------------------------------

SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;
Select 'GRANT ' || Privilege || ' ON ' ||Table_Name || ' TO "' || Grantee || '";' From Dba_Tab_Privs Where  OWNER='LDBO' ORDER BY GRANTEE,TABLE_NAME;
Select 'GRANT ' || Privilege || ' TO "' || Grantee || '";' From Dba_sys_Privs ORDER BY GRANTEE;
select 'GRANT ' || Privilege || ' (' || COLUMN_NAME ||  ') ON ' ||Table_Name || ' TO "' || Grantee || '";' from Dba_Col_Privs;
Select 'GRANT ' || Granted_Role || ' TO "' || Grantee || '";' From Dba_role_Privs ORDER BY GRANTEE;


-----------------------------------------------------------------------------------User Role Profile


select dbms_metadata.get_ddl('USER', u.username) AS ddl from   dba_users u
union all
select dbms_metadata.get_ddl('ROLE', role) AS ddl FROM dba_roles
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl from   dba_ts_quotas tq
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl from   dba_role_privs rp
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl from dba_sys_privs sp
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl from   dba_tab_privs tp
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl from   dba_role_privs rp
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl from dba_users u where u.profile <> 'DEFAULT'
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from   dba_users u where   u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl from   dba_users u where u.profile <> 'DEFAULT'
;

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

set define off
Turns off substitution variables.

---------------------------------------------------------compile invalid--------------------------------------------------------------------------------------------
EXEC sys.UTL_RECOMP.recomp_serial;



---------------------------------------------------------------Analyze-----------------------------------------------------------------------------------
EXEC DBMS_STATS.gather_database_stats(degree => DBMS_STATS.DEFAULT_DEGREE);




SET HEADING OFF

spool c:\temp\invalid.sql ;
select OBJECT_NAME from dba_objects where STATUS='INVALID';

select
'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '."' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER');

Select decode( object_type, 'PACKAGE BODY', 'ALTER PACKAGE ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('PACKAGE BODY') order by object_type;

select 'ALTER ' || OWNER || ' ' || OBJECT_TYPE || ' "' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('SYNONYM');

Select decode( object_type, 'TYPE', 'ALTER TYPE ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE;')
from dba_objects where status = 'INVALID' and object_type in ('TYPE') order by object_type;

Select decode( object_type, 'TYPE BODY', 'ALTER TYPE ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('TYPE BODY') order by object_type;

spool out ;
@ c:\temp\invalid.sql ;


SELECT * FROM USER_ERRORS;


select 'SELECT INSTANCE_NAME,HOST_NAME FROM V$INSTANCE@' ||Db_Link || ';' from dba_db_links;


















-------------------------------Drop objects

BEGIN
 FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects where object_type='TRIGGER') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
  FOR cur_rec IN (SELECT table_name, constraint_name
                  FROM   user_constraints
                  WHERE  constraint_type = 'R') LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || cur_rec.table_name || ' DROP CONSTRAINT ' || cur_rec.constraint_name;
  END LOOP;
FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner,synonym_name,table_owner FROM  dba_synonyms where table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner || ' SYNONYM ' || cur_rec.table_owner || '.' || cur_rec.synonym_name || ' FORCE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/
purge recyclebin ;

Temporary Tablespace Related Scripts

To check instance-wise total allocated, total used TEMP for both rac and non-rac

set lines 152

col FreeSpaceGB format 999.999

col UsedSpaceGB format 999.999

col TotalSpaceGB format 999.999

col host_name format a30

col tablespace_name format a30

select tablespace_name,

(free_blocks*8)/1024/1024 FreeSpaceGB,

(used_blocks*8)/1024/1024 UsedSpaceGB,

(total_blocks*8)/1024/1024 TotalSpaceGB,

i.instance_name,i.host_name

from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and

i.inst_id=ss.inst_id;

Total Used and Total Free Blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

Another Query to check TEMP USAGE

col name for a20

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management

"ExtManag",

TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,

0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",

TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by

tablespace_name) a,

(select tablespace_name, sum(bytes_cached) bytes from

v$temp_extent_pool group by tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"

from (select sum(used_blocks) tot_used_blocks

from v$sort_segment where tablespace_name='TEMP') s,

(select sum(blocks) total_blocks

from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace

col file_name for a45

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024

size_m

, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used

FROM

sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v

WHERE (t.file_id (+)= d.file_id)

AND (d.file_id = v.file#);

Additional checks

select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Changing the default temporary Tablespace

SQL> alter database default temporary tablespace TEMP;

Database altered.

To add tempfile to Temp Tablespace

alter tablespace  temp  add tempfile '&tempfilepath' size 1800M;

alter tablespace temp add tempfile '/m001/oradata/SID/temp02.dbf' size 1000m;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp04.dbf' size 1800M autoextend on maxsize 1800M;

To resize the  tempfile in Temp Tablespace

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M

alter database tempfile '/SID/oradata/data02/temp12.dbf' autoextend on maxsize 1800M;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp05.dbf' size 1800m reuse;

To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block"

from v$sort_usage

group by username;

To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks

FROM v$session s, v$sort_usage u

WHERE s.saddr=u.session_addr

order by u.blocks desc;

To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,

(used_blocks*8)/1024/1024  UsedSpaceInGB,

(total_blocks*8)/1024/1024  TotalSpaceInGB

from v$sort_segment where tablespace_name like '%TEMP%'

To find  Total Space Allocated for Temp Tablespace

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB'  from dba_temp_files where tablespace_name='TEMP';

Get 10 sessions with largest temp usage

cursor bigtemp_sids is

select * from (

select s.sid,

s.status,

s.sql_hash_value sesshash,

u.SQLHASH sorthash,

s.username,

u.tablespace,

sum(u.blocks*p.value/1024/1024) mbused ,

sum(u.extents) noexts,

nvl(s.module,s.program) proginfo,

floor(last_call_et/3600)||':'||

floor(mod(last_call_et,3600)/60)||':'||

mod(mod(last_call_et,3600),60) lastcallet

from v$sort_usage u,

v$session s,

v$parameter p

where u.session_addr = s.saddr

and p.name = 'db_block_size'

group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,

nvl(s.module,s.program),

floor(last_call_et/3600)||':'||

floor(mod(last_call_et,3600)/60)||':'||

mod(mod(last_call_et,3600),60)

order by 7 desc,3)

where rownum < 11;

Displays the amount of IO for each tempfile

SELECT SUBSTR(t.name,1,50) AS file_name,

f.phyblkrd AS blocks_read,

f.phyblkwrt AS blocks_written,

f.phyblkrd + f.phyblkwrt AS total_io

FROM   v$tempstat f,v$tempfile t

WHERE  t.file# = f.file#

ORDER BY f.phyblkrd + f.phyblkwrt DESC;

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,

i.inst_id,i.host_name

FROM gv$session s, gv$sort_usage u ,gv$instance i

WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id  order by MB DESC) a where rownum<10;

Check for ORA-1652

show parameter background

cd <background dump destination>

ls -ltr|tail

view <alert log file name>

shift + G ---> to get the tail end...

?ORA-1652 ---- to search of the error...

shift + N ---- to step for next reported error...

I used these queries to check some settings:

-- List all database files and their tablespaces:

select  file_name, tablespace_name, status

,bytes   /1000000  as MB

,maxbytes/1000000  as MB_max

from dba_data_files ;

-- What temporary tablespace is each user using?:

select username, temporary_tablespace, default_tablespace from dba_users ;

-- List all tablespaces and some settings:

select tablespace_name, status, contents, extent_management

from dba_tablespaces ;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN STATUS

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

SYSTEM                         PERMANENT DICTIONARY ONLINE

TOOLS                          PERMANENT DICTIONARY ONLINE

TEMP                           TEMPORARY DICTIONARY OFFLINE

TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:

select count(*)  from dba_all_tables

where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get

-- NO rows for this:

column owner        format a20

column object_type  format a30

column object_name  format a40

select

o.owner  ,o.object_name

,o.object_type

from sys_objects s

,dba_objects o

,dba_data_files df

where df.file_id = s.header_file

and o.object_id = s.object_id

and df.tablespace_name = 'TEMP' ;

Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c,

     (select block_size from dba_tablespaces where tablespace_name='TEMP') d

    WHERE b.tablespace = 'TEMP'

    and a.saddr = b.session_addr

    AND c.address= a.sql_address

    AND c.hash_value = a.sql_hash_value

    AND (b.blocks*d.block_size)/1048576 > 1024

    ORDER BY b.tablespace, 6 desc;

------------------------------------11g

select TABLESPACE_NAME , tablespace_size/1024/1024 totalMB, free_spAce/1024/1024 Just_FreeSpaceMB, (TABLESPACE_SIZE-FREE_SPACE)/1024/1024 FreeMb, (TABLESPACE_SIZE-ALLOCATED_SPACE)/1024/1024 AllocSize from dba_temp_free_space ;

SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;

DBA_TEMP_FREE_SPACE displays temporary space usage information at tablespace level.

Column Datatype NULL Description

TABLESPACE_NAME VARCHAR2(30) NOT NULL Name of the tablespace

TABLESPACE_SIZE NUMBER Total size of the tablespace, in bytes

ALLOCATED_SPACE NUMBER Total allocated space, in bytes, including space that is currently allocated and used and space that is currently allocated and available for reuse

FREE_SPACE NUMBER Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated

Blocking Session Lockwait Best

SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2 ,
v$session s2,v$sql sqlt1, v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;

expdp procedure in 11g

 1) create directory export_auto as 'd:\expdp1213';

 create user dba_export_user identified by test123;

grant connect, create database link, resource, create view to dba_export_user;
grant unlimited tablespace to dba_export_user;
grant exp_full_database to dba_export_user;
grant read,write on directory export_auto to dba_export_user;
grant execute on dbms_flashback to dba_export_user;
grant create table to dba_export_user;
grant FLASHBACK ANY TABLE to dba_export_user;


2)
CREATE OR REPLACE PROCEDURE dba_export_user.start_export
IS
   hdl_job        NUMBER;
   l_cur_scn      NUMBER;
   l_job_state    VARCHAR2 (20);
   l_status       SYS.ku$_status1010;
   l_job_status   SYS.ku$_jobstatus1010;
BEGIN

 begin
    execute immediate 'drop table  dba_export_user.AUTO_EXPORT';
    exception when others then null;
   end;

   hdl_job := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', job_mode => 'FULL', job_name => 'AUTO_EXPORT' );
 
   DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'EXPDP1213.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file,reusefile => 1);
                         
   DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'export.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file,reusefile => 1);
                     
   DBMS_DATAPUMP.start_job (handle => hdl_job);
 
   DBMS_DATAPUMP.wait_for_job (handle => hdl_job, job_state => l_job_state);
 
   DBMS_OUTPUT.put_line ('Job exited with status:' || l_job_state);

  DBMS_DATAPUMP.detach(handle => hdl_job);

END;
/


3) Change the time, Date

begin
 dbms_scheduler.create_job(
      job_name => 'EXPORT_JOB'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'dba_export_user.start_export'
     ,start_date => '08-FEB-12 06.02.00.00 PM ASIA/CALCUTTA'
       ,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;'
     ,enabled => TRUE
     ,comments => 'EXPORT_DATABASE_JOB');
end;
/

EXEC  dbms_scheduler.run_job('EXPORT_JOB');

RMAN Block Media Recovery

From Oracle 9i onwards you can use RMAN to recover only blocks while database is up and running.
This could possibly save hours and hours of recovery time as a full database restore  is not necessary.
Error reported by user pointing to block corruption.

 POPULATE_MACSDATA - ORA-01578: ORACLE data block corrupted  (file # 48, block # 142713)
ORA-01110: data file 48: '/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf'
ORA-02063: preceding 2 lines from MODSL_MACSL_LINK
File name : /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf

Check first if the there is only one(few) blocks corrupted or most of the blocks are corrupted.

macsl:/opt/oracle/admin/macsl/bdump>
Issue command below at UNIX prompt.

dbv file=/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf BLOCKSIZE=8192 LOGFILE=test.log

DBV-00200: Block, dba 201469305, already marked corrupted

SQL> Select * from v$database_block_corruption;

You will get block number corrupt.
Ex: block 142713.

After that LOGIN TO RMAN.

rman target / catalog rman10/rman10@rman10p
RMAN> BLOCKRECOVER DATAFILE 48 BLOCK 142713;

V$database_block_corruption is the view to check the list of corrupted blocks.

If you have multiple block list as corrupt, You can use single command to recover them.

RMAN> BLOCKRECOVER corruption list;

Table level Recovery using Flashback Table

---------------------------------------------------Recover Dropped Table from Recyclebin using Flashback Table-------------------------

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many

cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle Flashback Table:

Restores all data in a specified table to a previous point in time described by a timestamp or SCN.

Performs the restore operation online.

Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.

Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK

TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

FLASHBACK TABLE <table_name> TO BEFORE DROP;

Some other variations of the flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

--------------------------------------------------Recover deleted Table Data from Recyclebin using Flashback Table---------------------

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.

Example: Flashback the table back to previous time using SCN


select count(*) from LDBO.test;
COUNT(*)
----------
68781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1584494

SQL> delete from LDBO.test where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
18781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1587106

Enable row movement:

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> FLASHBACK TABLE LDBO.test to scn 1584494;

Flashback complete.

SQL> select count(*) from LDBO.test;


QL> alter table LDBO.test disable row movement;

Table altered.


We can rewind the table back to previous time using timestamp:

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:01:09

SQL> delete from LDBO.test ;

68781 rows deleted.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:03:18

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
0

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');

Flashback complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
68781

--------------------------------------------------------------------------------------------Flashback Table recover table to different table----------------------

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS') RENAME TO test2;

11g Active Data Guard - enabling Real-Time Query

Active Data Guard is a good new feature in 11g (although requires a license) which enables us to query the Standby database while redo logs are being applied to it. In earlier releases, we had to stop the log apply, open the database in read only mode and then start the log apply again when the database was taken out of the read only mode.

With Oracle 11g Active Data Guard, we can make use of our standby site to offload reporting and query type applications while at the same time not compromising on the high availability aspect.

How do we enable Active Data Guard?

If we are not using the Data Guard Broker, we need to open the standby database, set it in read only mode and then start the managed recovery as shown below.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size 2154936 bytes
Variable Size 847257160 bytes
Database Buffers 213909504 bytes
Redo Buffers 5931008 bytes
Database mounted.
Database opened.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

If we are using the Data Guard Broker CLI, DGMGRL, the procedure is a bit different and is not very clearly explained in the documentation.

You need to stop redo apply first via the SET STATE dgmgrl command, then from a SQL*PLUS session, open the database in read only mode, and then back again from dgmgrl via set SET STATE command, start the redo apply again.

Stop redo apply with the following command from Data Guard Broker CLI

DGMGRL> EDIT DATABASE ‘PRODDB’ SET STATE=’APPLY-OFF’;

Open standby read-only via SQL*Plus

SQL> alter database open read only;

Restart redo apply via broker CLI

DGMGRL> EDIT DATABASE ‘PRODDB’ SET STATE=’APPLY-ON’;

I tried to run the same only via DGMGRL and got this error:

DGMGRL> edit database PRODDB set state=”APPLY-OFF”;
Succeeded.

DGMGRL> edit database PRODDB set state=”READ ONLY”;
Error: ORA-16516: current state is invalid for the attempted operation

After we have enabled the Real-Time Query feature, we can confirm the same via the DGMGRL command – SHOW DATABASE

DGMGRL> show database verbose PRODDB_DR

Database – PRODDB_DR

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON

Note:

Even though we have enabled Real-Time Query feature, if we go to Data Guard page via the Enterprise Manager Grid Control GUI, it will show that Real-Time Query is in a Disabled state.

This is apparently a bug which applies to OEM Grid Control 10.2.0.1 to 10.2.0.5 with a 11.2 target database.

Bug 7633734: DG ADMIN PAGE REAL TIME QUERY SHOWS DISABLED WHEN ENABLED FOR 11.2 DATABASES

RMAN Recovery Scenarios

RMAN Backup and Recovery Scenarios

RMAN Backup and Recovery Scenarios

 

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie:
cp -p /usr/backup/RMAN/system01.dbf  /usr/oradata/u01/IASDB/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;

Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. recover tablespace <tablespace_name>;
4. alter tablespace <tablespace_name> online;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed,recovery can be performed after the database is open.
Pre requisites: A closed or open database backup and archived logs.
1.   startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2.   Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
3.   alter database datafile 6 offline; (tablespace cannot be used because the database is not open)
4.   alter database open;
5.   recover datafile 6;
6.   alter tablespace <tablespace_name> online;

Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist.
Pre requisites: All relevant archived logs.
1.   alter tablespace <tablespace_name> offline immediate;
2.   alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’;
3.   recover tablespace <tablespace_name>;
4.   alter tablespace <tablespace_name> online;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    recover tablespace <tablespace_name>;
5.    alter tablespace <tablespace_name> online;

Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.
Pre requisites: A backup of your controlfile and all relevant archived logs.
1.    startup; (you get ora-205, missing controlfile, instance start but database is not mounted)
2.    Use OS commands to restore the missing controlfile to its original location:
cp -p /usr/backup/RMAN/control01.dbf /usr/oradata/u01/IASDB/control01.dbf
cp -p /usr/backup/RMAN/control02.dbf /usr/oradata/u01/IASDB/control02.dbf
3.    alter database mount;
4.    recover automatic database using backup controlfile;
5.    alter database open resetlogs;
6.    make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until before the object was dropped.
Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1.  If the database is open, shutdown abort
2.  Use OS commands to restore all datafiles to its original locations:
cp -p /usr/backup/RMAN/u01/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u02/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u03/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u04/*.dbf /usr/oradata/u01/IASDB/
etc…
3.  startup mount;
4.  recover automatic database until time ’2004-03-31:14:40:45′;
5.  alter database open resetlogs;
6.  make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you   may use instead of until time, until sequence or until cancel:
recover automatic database until sequence 120 thread 1; OR
recover database until cancel;

Rman Recovery Scenarios

Rman recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also.
Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database.
Configuration and operation recommendations:
Set the parameter controlfile autobackup to ON to have with each backup a
controlfile backup also:
configure controlfile autobackup on;
set the parameter retention policy to the recovery window you want to have,
ie redundancy 2 will keep the last two backups available, after executing delete obsolete commands:
configure retention policy to redundancy 2;
Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:
backup database plus archivelog;
Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete backup;
To work with Rman and a database based catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rmuser identified by rmuser default tablespace repcat temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rmuser
5. exit
6. rman catalog rmuser/rmuser          # connect to rman catalog as the rmuser
7. create catalog                     # create the catalog
8. connect target /                   #

Complete Closed Database Recovery. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing,so the database can be opened without reseting the redologs.
1.  rman target /
2.  startup mount;
3.  restore database;
4.  recover database;
5.  alter database open;

Complete Open Database Recovery. Non system tablespace is missing,database is up

1.   rman target /
2.   sql ‘alter tablespace <tablespace_name> offline immediate’;
3.   restore datafile 3;
4.   recover datafile 3;
5.   sql ‘alter tablespace <tablespace_name> online’;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

A user datafile is reported missing when tryin to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.
1.    sqlplus /nolog
2.    connect / as sysdba
3.    startup mount
4.    alter database datafile ‘<datafile_name>’ offline;
5.    alter database open;
6.    exit;
7.    rman target /
8.    restore datafile ‘<datafile_name>’;
9.    recover datafile ‘<datafile_name>’;
10.   sql ‘alter tablespace <tablespace_name> online’;

Recovery of a Datafile that has no backups (database is up).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.
Pre requisites: All relevant archived logs.
1.    sqlplus ‘/ as sysdba’
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf;
4.    exit
5.    rman target /
6.    recover tablespace <tablespace_name>;
7.    sql ‘alter tablespace <tablespace_name> online’;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location. Database is up.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs, complete cold or hot backup.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    rman target /
5.    recover tablespace <tablespace_name>;
6.    sql ‘alter tablespace <tablespace_name> online’;

Control File Recovery

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.
Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile.It is the number following the ‘c-’ at the start of the name.
1.   rman target /
2.   set dbid <dbid#>
3.   startup nomount;
4.   restore controlfile from autobackup;
5.   alter database mount;
6.   recover database;
7.   alter database open resetlogs;
8.   make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when the database crash and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing.
Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the object was dropped.
Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1.   If the database is open, shutdown it to perform full restore.
2.   rman target \
3.   startup mount;
4.   restore database;
5.   recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.
6.  alter database open resetlogs;
7.  make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you may use instead of until sequence, until time, ie: ’2012-01-04:01:01:10′.