Oracle FULL EXP/IMP 11i Database - 911

1. Oracle FULL EXP/IMP 11i Database - 911 SURENDER SARA NCOAUG Email : SURENDER.SARA@ORABYTE.COM SURENDER.SARA@SERACONSULTING.US

2. Export – Import Need • As a last resort for resolving complex problems like fixing data dictionary • Required when migrating from one platform to other, ex- from SUN SOLARIS to LINUX • Can be used to fix mass fragmentation issues where tablespace is not locally managed yet !

3. Some Example of Why ? • You have a data file what was corrupted that was offline dropped. Seems file is normal under dba_data_files select file_id, file_name, status from dba_data_files where file_id=437 FILE_ID FILE_NAME STATUS ---------- ---------------------------------------- --------- 437 /u09/oradata/FINPROD/fndx05.dbf AVAILABLE • Data dictionary will still hold the file name in the v$recover_file select * from v$recover_file; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- ------------------------------ ---------- --------- 437 OFFLINE UNKNOWN ERROR 434357829 03-NOV-03

4. What is the issue with Offline Drop Datafile? • As long as you do hot and cold backups you are fine. • As long as you do RMAN backup you are fine • If you try to clone the database using RMAN cloning you will be asked to supply all the archivelogs since the file was offline drop, unless you plan to skip the file. • DATA DICTIONARY ENTRY WILL NEVER BE REMOVED. Creating a new data dictionary is the only way to fix it ! ( true on 8i and 9i, 10g not tested)

5. Environment Setup • 11.5.9, 8174 database and Sun Solaris 8 • MINIPACK AD-H Applied • Patch 2447246, 2096242 is already applied as part of 11.5.9 install base

6. PRE EXPORT Steps • Apply latest AD minipack F or higher ( In my environment we were at AD-H) • Apply the Applications consolidated export/import utility patch. Apply patch 3201141 to the source administration server node • Apply patch 2096242 to every application tier server node in the source system. ( It is already a part of 11.5.9 install base) • If you have installed the 11.5.8 Rapid Install, apply patch 2634422 ( we were at 11.5.9) • select * from global_name. Will need this after the export and before the import. • Create the export parameter file ( see next slide) • Generate target database instance creation script adcrdb.sql using sracrdb.sql ( EXP.zip file) • Record Advanced Queue settings using auque1.sql ( EXP.zip file) • Record Oracle ConText default tablespace On the source database server node, use SQL*Plus to connect to the source database as sysdba and run the following query: • $ sqlplus /nolog SQL> connect sys/<sys password> as sysdba; • SQL> select username, default_tablespace from dba_users where username='CTXSYS'; • Shut down all Applications server processes except the database and the Net8 listener for the database

7. Dynamic SQL to see how many rows exist • “WORKFLOW TABLES will take lot of time if not clean” • select • 'select count(*) from ' || owner ||'.'|| object_name || ' ;' from dba_objects • where • object_type = 'TABLE' and • ( • object_name like '%WF_JMS_IN%' or • object_name like '%WF_JMS_OUT%' or • object_name like '%WF_CONTROL%' or • object_name like '%WF_NOTIFICATION%' • ) • /

8. Exporting 11i database • exp sys/pass9999 parfile=auexpimp.dat • auexpimp.dat • userid="sys/pass9999 as sysdba" • buffer=100428800 • file=/scratch/EXP/FINPROD.exp • compress=y • grants=y • indexes=y • rows=y • constraints=y • full=y • log=/scratch/EXP/finprod.log

9. PRE IMPORT- sracrdb.sql File – Fixing path • Copy script adclondb.sql to sracrdb.sql • Fix the function to look like below function file_name(X_file_name in varchar2) return varchar2 is l_slash_pos number; begin return(''''||X_file_name||''''); end;

10. PRE IMPORT – Delete the current database • Make sure EXPORT is good • Run extend_data_files.sql to produce extendme.sql • Delete all datafiles, controlfiles and logfiles

11. PRE IMPORT – Recreate a New One • Remove the password file from $ORACLE_HOME/dbs • Modify init.ora to comment the line REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE • Run adcrdb.sql sqlplus /nolog __1 connect internal; spool adcrdb.log; startup nomount; @adcrdb.sql exit; __1

12. PRE IMPORT – Recreate Dictionary • Run post DATABASE create script to install the data dictionary sqlplus /nolog __1 connect internal; spool sys_system_java_misc1.log; connect system/manager @addb817.sql @adsy817.sql @addbjava.sql exit; __1

13. PRE IMPORT – ORD Install Set ORACLE_HOME variable. • @?/ord/admin/ordisys.sql • connect ordsys/ordsys • @?/ord/admin/ordiords.sql

14. interMedia Install • connect ordsys/ordsys; • @?/ord/im/admin/imiordsy.sql • connect ordplugins/ordplugins; • @?/ord/im/admin/imiordpl.sql • connect ordsys/ordsys; • @?/ord/im/admin/imchk.sql • connect mdsys/mdsys; • @?/ord/im/admin/imimdloc.sql

15. Install Spatial Option • connect mdsys/mdsys • @?/md/admin/catmd.sql • begin • ORDSYS.Cartridge.Register( • 'SDO', • 'Oracle Spatial Cartridge', • '8.1.7.0.0 Development', • 'Oracle8 Spatial Cartridge', • 'INVALID' • ); • end; • / • @?/md/admin/mdchk.sql • AR import and HR import of few tables will fail if this is not installed properly

16. Intermedia install • Pass these 4 parameters • @?/ctx/admin/dr0csys FALSE USERS TEMP /u01/oracle/finproddb/8.1.7/ctx/lib/libctxx8.so

17. ctxsys • connect ctxsys/ctxsys • prompt • prompt The output from dr0inst.sql is in dr0inst.log • prompt • @?/ctx/admin/dr0inst u01/oracle/finproddb/8.1.7/ctx/lib/libctxx8.so • @?/ctx/admin/defaults/drdefus.sql

18. Pre-Import • alter database rename global_name to <source db global name> ;

19. Import DAT file • userid="sys/pass9999 as sysdba" • buffer=100428800 • file=/scratch/EXP/FINPROD.exp • grants=y • indexes=y • rows=y • constraints=y • full=y • log=/scratch/EXP/impFINPROD.log • ignore=y • analyze=n • commit=y

20. Import Script • imp sys/pass9999 parfile=imp.dat

21. Script to See The data Export • For example we can see what was exported for given tables select 'grep –I “ ' || object_name || ‘ “ finprod.log' from dba_objects where object_type = 'TABLE' and ( object_name like '%WF_JMS_IN%' or object_name like '%WF_JMS_OUT%' or object_name like '%WF_CONTROL%' or object_name like '%WF_NOTIFICATION%' ) /

22. Run auque2.sql • Run auque2.sql to enable the AQ

23. Possible AQ That will not work • ERROR at line 1:ORA-20000: Oracle Error Mkr4= -24002 - ORA-24002: QUEUE_TABLE APPLSYS.WF_JMS_INdoes not existORA-06512: at line 24 • The see the AQ table for the queue that are failing • select * from user_queue_tables where queue_table in ('WF_JMS_IN', 'WF_JMS_OUT', 'WF_CONTROL' ,'WF_NOTIFICATION_IN', 'WF_NOTIFICATION_OUT'); • To see the objects related the the above queues • select owner , object_type , status , object_name from dba_objects where object_type = 'TABLE' and ( object_name like '%WF_JMS_IN%' or object_name like '%WF_JMS_OUT%' or object_name like '%WF_CONTROL%' or object_name like '%WF_NOTIFICATION%' ) ;

24. Check How much Data Was Exported Dynamic SQL to see how much data was exported may be needed to estimate the table level import ( AGAIN ) select 'grep -i " ' || object_name || ' " finprod.log' from dba_objects where object_type = 'TABLE' and ( object_name like '%WF_JMS_IN%' or object_name like '%WF_JMS_OUT%' or object_name like '%WF_CONTROL%' or object_name like '%WF_NOTIFICATION%' ) / grep -i " AQ\$_WF_CONTROL_H " finprod.log grep -i " AQ\$_WF_CONTROL_I " finprod.log

25. AQ Fix – Drop it all ! • set linesize 10000 • select object_name,object_type FROM user_objects where object_name like 'WF_%'; • col OBJECT_NAME format a40 • select * from user_queue_tables where queue_table in • ('WF_JMS_IN', 'WF_JMS_OUT', 'WF_CONTROL' ,'WF_NOTIFICATION_IN', • 'WF_NOTIFICATION_OUT'); • select owner , object_type , status , object_name from dba_objects • where • object_type = 'TABLE' and • ( • object_name like '%WF_JMS_IN%' • ) ; • connect as applsys • drop view AQ$WF_JMS_IN ; • drop view AQ$WF_JMS_IN_S ; • drop sequence AQ$_WF_JMS_IN_N ; • ALTER SESSION SET EVENTS '10851 trace name context forever, level 2'; • drop table AQ$_WF_JMS_IN_H cascade constraint ; • drop table AQ$_WF_JMS_IN_I cascade constraint ; • drop table AQ$_WF_JMS_IN_S cascade constraint ; • drop table AQ$_WF_JMS_IN_T cascade constraint ; • drop table WF_JMS_IN cascade constraint ;

26. Identify AQ tables • connect sys • SELECT schema, name, objno FROM SYSTEM.AQ$_QUEUE_TABLES • WHERE • name like '%WF_JMS_IN%' ; • SCHEMA NAME OBJNO • ------------------------------ ------------------------------ ---------- • APPLSYS WF_JMS_IN 196658 • APPLSYS WF_JMS_OUT 196676

27. Identify AQ • SELECT oid, table_objno, name FROM SYSTEM.AQ$_QUEUES WHERE name IN • ( • 'AQ$_WF_JMS_IN_E' , • 'AQ$_WF_JMS_OUT_E' , • 'AQ$_WF_CONTROL_E' , • 'AQ$_WF_NOTIFICATION_IN_E' , • 'AQ$_WF_NOTIFICATION_OUT_E' • ); • OID TABLE_OBJNO NAME • -------------------------------- ----------- ------------------------------ • ECA3727F39B26E6FE0340003BA0D8DD5 109546 AQ$_WF_NOTIFICATION_OUT_E • ECA3727F39B16E6FE0340003BA0D8DD5 109532 AQ$_WF_NOTIFICATION_IN_E • ECA3727F39B06E6FE0340003BA0D8DD5 109535 AQ$_WF_CONTROL_E • ECA5415C274B703AE0340003BA0D8DD5 196676 AQ$_WF_JMS_OUT_E • ECA5415C274A703AE0340003BA0D8DD5 196658 AQ$_WF_JMS_IN_E

28. Delete the AQ data • DELETE FROM SYSTEM.AQ$_QUEUE_TABLES where objno in ( • SELECT objno FROM SYSTEM.AQ$_QUEUE_TABLES • WHERE • name like '%WF_JMS_IN%' or • name like '%WF_JMS_OUT%' or • name like '%WF_CONTROL%' or • name like '%WF_NOTIFICATION%' ) • ; • DELETE FROM SYSTEM.AQ$_QUEUES WHERE table_objno in ( • SELECT table_objno FROM SYSTEM.AQ$_QUEUES WHERE name IN • ( • 'AQ$_WF_JMS_IN_E' , • 'AQ$_WF_JMS_OUT_E' , • 'AQ$_WF_CONTROL_E' , • 'AQ$_WF_NOTIFICATION_IN_E' , • 'AQ$_WF_NOTIFICATION_OUT_E' • ) );

29. Locate OBJ$ Entries • connect as sys • select distinct o.owner# from obj$ o, user$ u where o.owner#=u.user# and u. • name='APPLSYS'; • OWNER# • ---------- • 35 • SELECT obj#,name FROM SYS.OBJ$ WHERE type#=24 AND owner# =35 AND name IN • ( • 'AQ$_WF_JMS_IN_E' , • 'AQ$_WF_JMS_OUT_E' , • 'AQ$_WF_CONTROL_E' , • 'AQ$_WF_NOTIFICATION_IN_E' , • 'AQ$_WF_NOTIFICATION_OUT_E' • ); • OBJ# NAME • --------- ------------------------------ • 196656 AQ$_WF_NOTIFICATION_OUT_E • 196640 AQ$_WF_NOTIFICATION_IN_E • 196624 AQ$_WF_CONTROL_E • 196692 AQ$_WF_JMS_OUT_E • 196674 AQ$_WF_JMS_IN_E

30. Fix OBJ$ table • SELECT obj#,name FROM SYS.OBJ$ WHERE type#=46 AND owner# = 35 AND name in • ( • 'AQ$_WF_JMS_IN_E' , • 'AQ$_WF_JMS_OUT_E' , • 'AQ$_WF_CONTROL_E' , • 'AQ$_WF_NOTIFICATION_IN_E' , • 'AQ$_WF_NOTIFICATION_OUT_E' • ); • DELETE FROM SYS.OBJ$ WHERE obj# IN • ( • SELECT obj# • FROM SYS.OBJ$ WHERE type#=24 AND owner# =35 AND name IN • ( • 'AQ$_WF_JMS_IN_E' , • 'AQ$_WF_JMS_OUT_E' , • 'AQ$_WF_CONTROL_E' , • 'AQ$_WF_NOTIFICATION_IN_E' , • 'AQ$_WF_NOTIFICATION_OUT_E' • ));

31. Final Step • select name from SYS.EXPACT$ WHERE owner='APPLSYS' AND name LIKE '%WF%'; • select name from SYS.NOEXP$ WHERE owner='APPLSYS' AND name LIKE '%WF%'; • DELETE FROM SYS.EXPACT$ WHERE owner='APPLSYS' AND name LIKE '%WF%'; • DELETE FROM SYS.NOEXP$ WHERE owner='APPLSYS' AND name LIKE '%WF%';

32. POSIBLE Queues you have to fix • select * from user_queue_tables where queue_table in • ('WF_JMS_IN', 'WF_JMS_OUT', 'WF_CONTROL' ,'WF_NOTIFICATION_IN', • 'WF_NOTIFICATION_OUT'); • select owner , object_type , status , object_name from dba_objects • where • object_type = 'TABLE' and • ( • object_name like '%WF_JMS_IN%' or • object_name like '%WF_JMS_OUT%' or • object_name like '%WF_CONTROL%' or • object_name like '%WF_NOTIFICATION%' • ) ;

33. Contact and QA • Surender.sara@veritiesllc.com