Transcript R12 New Features

Oracle FULL EXP/IMP 11i Database - 911
SURENDER SARA
NCOAUG
Email :
[email protected]
[email protected]
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 !
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
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)
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
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
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%'
)
/
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
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;
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
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
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
PRE IMPORT – ORD Install
Set ORACLE_HOME variable.
• @?/ord/admin/ordisys.sql
• connect ordsys/ordsys
• @?/ord/admin/ordiords.sql
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
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
Intermedia install
• Pass these 4 parameters
•
@?/ctx/admin/dr0csys FALSE USERS TEMP
/u01/oracle/finproddb/8.1.7/ctx/lib/libctxx8.so
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
Pre-Import
• alter database rename global_name to
<source db global name> ;
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
Import Script
• imp sys/pass9999 parfile=imp.dat
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%'
)
/
Run auque2.sql
• Run auque2.sql to enable the AQ
Possible AQ That will not work
•
•
ERROR at line 1:
ORA-20000: Oracle Error Mkr4= -24002 - ORA-24002: QUEUE_TABLE APPLSYS.WF_JMS_IN
does not exist
ORA-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%'
);
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
AQ Fix – Drop it all !
•
set linesize 10000
•
select object_name,object_type FROM user_objects where object_name like 'WF_%';
•
col OBJECT_NAME
•
•
•
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 ;
format a40
;
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
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
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'
) );
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
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'
));
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%';
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%'
);
Contact and QA
• [email protected]