Create Database Structure / MetaData / scripts using EXPDP

Dont Use PLSQL Developer, it will corrupt code bodies


1) Take Export Full Dump of apx1213 DB using Data Pump

conn sys@apx1213srv as sysdba

CREATE DIRECTORY DPUMP_DIR1 AS 'D:\expdp1213';

GRANT read, write ON DIRECTORY DPUMP_DIR1 TO ldbo;

cmd
expdp ldbo/ldbo@apx1213srv FULL=y directory=dpump_dir1 dumpfile=apx1213.DMP LOGFILE=apx1213.LOG EXCLUDE=STATISTICS

2) Install Oracle 11g

3) Create apx1314 Database (Note: Don't Change oracle default parameters)

4) Create LDBO user using attached script (Note:only used attached, Don't use old createuserldbo.sql)

5) Login into LDBO user and create synonym

create synonym script from last year database apx1213 and run into current year apx1314

conn sys@apx1213srv as sysdba
set pagesize 1000
set linesize 150
set long 1000
col metadata for a145
SPOOL C:\YEP1314\SYNONYMNS1213.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name, 'PUBLIC') || '/' metadata
from dba_synonyms
where owner = 'PUBLIC'
  and table_owner = 'LDBO'
order by synonym_name;
SPOOL OFF

conn sys@apx1314srv as sysdba
@C:\YEP1314\SYNONYMNS1213.SQL

6) Login into LDBO user and create Database Link

Note: Please change the password and connect string as per client software

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 'APX1213SRV';

 CREATE PUBLIC DATABASE LINK "CMLDLINK"
  CONNECT TO cmuser IDENTIFIED BY cmuser USING 'CMMASTERSRV';

7) Login into LDBO user and create Sequence
Already exist in Createuserldbo sql

please drop all sequences and recreate them

select 'DROP SEQUENCE ' || Sequence_Name || ';' from dba_Sequences where Sequence_Owner='LDBO';


Create Sequence LDBO.Dematuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Saudauniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Billuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Jobbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Delbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Turnbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Fwdbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Extbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Dpuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Bankuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Payrequniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Riskuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Portuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Axiscms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Pmsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Debarreduniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Bkrecouniquenumber minvalue 50000000 maxvalue 9999999999 start with 50000000 increment by 1 nocache;
Create Sequence LDBO.Sq_Smsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Offlinenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Phytodemnumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Ipouniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Trademodification minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_SpicePoa minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Idbicms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Scripuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Clientuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Brokeruniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Nsdlcdsldpuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Orarupee minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Rtgssequence minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Bobbentopool minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Clientcode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Familycode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_NigPortclose Minvalue 1 maxvalue 9999999999 Start With 1 Increment by 1 noCache;
Create Sequence LDBO.Seqclientsequence minvalue 1 maxvalue 99 start with 1 increment by 1;
Create Sequence LDBO.Sq_collateral minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Nsdlkra minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.LdOraclebatchid minvalue 1 maxvalue 9999999999 nocache;
Create Sequence LDBO.Sq_Limittokenno minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.SQ_CDSLXMLUNIQUENUMBER minvalue 10000 maxvalue 99999;


8) Create Data pump Directory expdp1314 and copy apx1213 dump (last year dump file) and Create optable

Note: Run from Database server only

conn sys@apx1314srv as sysdba

host mkdir D:\expdp1314

CREATE DIRECTORY DPUMP_DIR1 AS 'D:\expdp1314';

GRANT read, write ON DIRECTORY DPUMP_DIR1 TO ldbo;

cmd
impdp ldbo/ldbo@apx1314SRV directory=DPUMP_DIR1 dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG schemas=LDBO content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT sqlfile=optable.sql

9) Create splogin,tglogin manually
Already exist in Createuserldbo sql

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

10)  Compile Invalid Objects

conn sys@apx1314srv as sysdba

EXEC sys.UTL_RECOMP.recomp_serial;

select object_name  from dba_objects where owner='LDBO' and status='INVALID';
select * from SYS.USER_ERRORS ;

11) Match Last and Current Year Database Objects (Run the following command in last and current year database and match the records)

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_Synonyms Where Table_Owner='LDBO';
select * from dba_Constraints where owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;

12) Create Users

conn sys@apx1213srv as sysdba

set head off
set pages 0
set long 9999999
spool c:\yep1314\user_script.sql
SELECT DBMS_METADATA.GET_DDL('PROFILE',profile) || '/' DDL
FROM DBA_PROFILES WHERE profile != 'DEFAULT' GROUP BY PROFILE;
UNION ALL
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_DDL('ROLE', role)  || '/' DDL
FROM dba_roles
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 ALLs
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);
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', GRANTEE) || '/' DDL
FROM DBA_TAB_PRIVS GROUP BY GRANTEE;
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', GRANTEE) || '/' DDL
FROM DBA_ROLE_PRIVS GROUP BY GRANTEE;
spool off;

conn sys@apx1314srv as sysdba
@c:\yep1314\user_script.sql

13) Create Roles using database link
Note: Please check database link (lnk_previousyearbalance) is working or not before running following script

conn sys@apx1314srv as sysdba
BEGIN
 FOR cur_rec IN (SELECT role
                  FROM   dba_roles@lnk_previousyearbalance) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


BEGIN
 FOR cur_rec IN (SELECT grantee,privilege
                  FROM   DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP
    BEGIN
      EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

BEGIN
 FOR cur_rec IN (SELECT grantee,privilege,table_name
                  FROM   dba_tab_privs@lnk_previousyearbalance  Where Grantor='LDBO') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

BEGIN
 FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name
                  FROM   dba_col_privs@lnk_previousyearbalance  Where Grantor='LDBO') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


BEGIN
 FOR cur_rec IN (SELECT grantee,granted_role
                  FROM   dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


impdp ldbo/ldbo@apx1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG schemas=LDBO content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT sqlfile=optable.sql

D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe LDBO/LDBO@ALP1314SRV DIRECTORY=DPUMP_DIR1 DUMPFILE=ALP1213.DMP LOGFILE=ALP1314METADATA1.LOG CONTENT=METADATA_ONLY SCHEMAS=LDBO EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT,ROLE,ROLE_GRANT,DIRECTORY,PROCACT_SCHEMA,AUDIT_OBJ sqlfile=optable1.sql

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

EXPDP Data Pump Job Scheduling with rename dump and remove old files

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 => 'exp1213.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);

DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'export.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file);

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);

----------------------RENAME BACKUP WITH DATE

begin

UTL_FILE.FRENAME ('EXPORT_AUTO','exp1213.DMP','EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.DMP');

end;

begin

UTL_FILE.FRENAME ('EXPORT_AUTO','export.log','EXPORT_AUTO','export'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.LOG');

end;

------------DELETE TWO DAYS BEFORE BACKUP

begin

UTL_FILE.FREMOVE ('EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.DMP');

end;

begin

UTL_FILE.FREMOVE ('EXPORT_AUTO','export'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.log');

end;

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;

/

Note: Rename the dmp file with sysdate on daily basis before next schedule time

manually execute backup job

EXEC dba_export_user.start_export;

check running job status

select * from DBA_datapump_jobs;

drop job

EXEC dbms_scheduler.drop_job('dba_export_user.start_export');