Import Error

[Former Member]

Hello All,

We have built a new system SAP ECC6.0. We need to import the User master data from the already existing system into the newly built system.

When I try doing the same it is ending with the RC16.

*===> HALT: maximal number of SQL errors reached. It may be better to stop now. Please contact the SAP support.====>

2EETW125 SQL error "4030" during "AGR_HIERT" access: "ORA-04030: out of process memory when trying to allocate 8224 bytes (sort subheap,sort key)"*

<SID>adm => ulimit -a

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) unlimited

stack(kbytes) unlimited

memory(kbytes) unlimited

coredump(blocks) 2097151

nofiles(descriptors) 2000

ora<SID> => ulimit -a

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) 131072

stack(kbytes) 32768

memory(kbytes) 32768

coredump(blocks) 2097151

nofiles(descriptors) 2000

PSAPTEMP table space has 20GB space. Any help?

Thanks,

Nick S

[Former Member]

.pga_aggregate_target has been increased and its resolved.

[Former Member]

Hi,

I think You have sufficent memory you should increase shared_pool

Surendra Jain

[Former Member]

Hi,

I tried increasing it to 3073741824 also. Still not solved. Any other parameters to change?

Thanks,

Nick S

Edited by: Nike S on Apr 27, 2010 12:26 PM

[former_member204746]

you are probably using a SPFILE.

Sqlplus / as sysdba

show parameter pfile;

if this provides an empty entry, then modify initSID.ora

if you get an entry, you must change this like this:

alter system set shared_pool_size=2048000000 scope both;

exit;

optionally, I would also restart Oracle.

Good luck.

[Former Member]

Hi,

This problem is now resolved. I have Increased the .pga_aggregate_target and the import has been finished.

Thanks,

Nick S

[markus_doehr2]

> We have built a new system SAP ECC6.0. We need to import the User master data from the already existing system into the newly built system.

How do you transfer those tables?

Markus

[Former Member]

Hi,

I used the Tcode SCC8 to export the User Master data in the Source system. Then I manually moved the datafile and control file from Source system to Target System.

I have added the TR in the buffer and tried importing that.

Thanks,

Nick S

[Former Member]

Hi,

Can you paste the Spfile or the init<sid>.ora whichever convenient..

Mark

[Former Member]

Hi,

PFB the init<SID>.ora.

*._B_TREE_BITMAP_PLANS=FALSE

*._FIX_CONTROL='5705630:ON'

*._IN_MEMORY_UNDO=FALSE

*._INDEX_JOIN_ENABLED=FALSE

*._OPTIM_PEEK_USER_BINDS=FALSE

*._OPTIMIZER_MJC_ENABLED=FALSE

*._SORT_ELIMINATION_COST_RATIO=10

*._TABLE_LOOKUP_PREFETCH_SIZE=0

*.background_dump_dest='/oracle/<SID>/saptrace/background'

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/<SID>/origlogA/cntrl/cntrl<SID>.dbf','/oracle/<SID>/saparch/cntrl/cntrl<SID>.dbf','/oracle/<SID>/sapdata1/cntrl/cntrl<SID>.dbf'

*.core_dump_dest='/oracle/<SID>/saptrace/background'

*.db_block_buffers=125795

*.db_block_size=8192

*.db_files=254

*.db_name='<SID>'

*.event='10191 trace name context forever, level 1','10027 trace name context forever, level 1','10028 trace name context forever, level 1','10091 trace name

context forever, level 1','10162 trace name context forever, level 1','10183 trace name context forever, level 1','10191 trace name context forever, level 1

','10629 trace name context forever, level 32','38068 trace name context forever, level 100'

*.FILESYSTEMIO_OPTIONS='setall'

*.log_archive_dest='/oracle/<SID>/saparch'

*.log_archive_format='<SID>arch%t_%s_%r.dbf'

*.log_buffer=1048576

*.log_checkpoints_to_alert=true

*.MAX_DUMP_FILE_SIZE='2000'

*.open_cursors=900

*.OPTIMIZER_INDEX_COST_ADJ=20

*.parallel_execution_message_size=16384

*.PARALLEL_MAX_SERVERS=80

*.PARALLEL_THREADS_PER_CPU=1

*.pga_aggregate_target=1030941132

*.processes=800

*.query_rewrite_enabled='false'

*.recyclebin='off'

*.remote_login_passwordfile='exclusive'

*.remote_os_authent=true

*.replication_dependency_tracking=false

*.sessions=1600

*.shared_pool_size=698205849

*.sort_area_retained_size=0

*.sort_area_size=2097152

*.star_transformation_enabled='true'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.undo_management='AUTO'

*.undo_retention=43200

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='/oracle/<SID>/saptrace/usertrace'

Thanks,

Nick S

[Former Member]

Hi,

how much memory has your server got ?

Also, cannot see parameter in your *.ora file db_cache =

Can you dump the information from front screen of ST04 here ?

Mark

[Former Member]

DB instance EE0 Day, Time 26.04.2010 15:51:20

DB node ukapx060 Start up at 26.04.2010 11:49:43

DB release 10.2.0.2.0 Sec. since start 14 497

Sec. btw. snaps. 0

Size (kB) 0 Logical reads 12 589 443

Quality (%) 98,2 Physical reads 229 047

Size default pool (kB) 0 Physical writes 91 349

Size keep pool (kb) 0 Buffer busy waits 979

Size others (kb) 0 Buffer wait time (s) 7

Size (kB) 688 128 Size (kB) 14 356

DD-cache Quality (%) 99,5 Entries 5 653 301

SQL area getratio(%) 79,2 Allocation retries 18

SQL area pinratio(%) 96,8 Alloc fault rate(%) 0,0

SQLA.Reloads/pins(%) 0,0115 Redo log wait (s) 1

Log files (in use) 8 ( 8 )

User calls 277 700 Recursive calls 748 304

User commits 1 777 Parse count 44 624

User rollbacks 27 User/recursive calls 0,4

Log.Reads/User Calls 45,3

Busy wait time (s) 296 Sessions busy (%) 0,15

CPU time session (s) 145 CPU usage (%) 0,17

Time/User call (ms) 2,00 Number of CPUs 6

[Former Member]

Thanks,

How much memory have you got ?

Mark

[Former Member]

Hi,

How much RAM in your system.According to shared_pool_advice Increase Shared_pool_size .

PGA_aggreate_target also recommended to increase & sort_area_size to decrease for this error.

First increase shared_pool only and try.

Surendra Jain

[Former Member]

Hi,

I have the RAM 22GB. Two instances are running on that server. The *.shared_pool_size=1073741824. Tried increasing it to 2073741824 but still getting the same error.

Thanks,

Nick S

[Former Member]

Hi Nike,

Check the Oracle alert log /oracle/SID/saptrace/background/alertSID.log

You might have to increase your Oracle parameters sessions and processes.

Kind regards,

Mark

[Former Member]

Hi,

PFB the content of alertSID.log.

processes = 800

sessions = 1600

event = 10191 trace name context forever, level 1, 10027 trace name context forever, level 1, 10028 trace name context forever, level 1,

10091 trace name context forever, level 1, 10162 trace name context forever, level 1, 10183 trace name context forever, level 1, 10191 trace name context fo

rever, level 1, 10629 trace name context forever, level 32, 38068 trace name context forever, level 100

shared_pool_size = 704643072

filesystemio_options = setall

control_files = /oracle/<SID>/origlogA/cntrl/cntrl<SID>.dbf, /oracle/<SID>/saparch/cntrl/cntrl<SID>.dbf, /oracle/<SID>/sapdata1/cntrl/cntrl<SID>.dbf

control_file_record_keep_time= 30

db_block_buffers = 125795

db_block_size = 8192

compatible = 10.2.0

log_archive_dest = /oracle/<SID>/saparch

log_archive_format = <SID>arch%t_%s_%r.dbf

log_buffer = 14254080

db_files = 254

standby_file_management = AUTO

log_checkpoints_to_alert = TRUE

replication_dependency_tracking= FALSE

undo_management = AUTO

undo_tablespace = PSAPUNDO

inmemory_undo = FALSE

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

parallel_max_servers = 80

parallel_execution_message_size= 16384

tablelookup_prefetch_size= 0

fixcontrol = 5705630:ON

background_dump_dest = /oracle/<SID>/saptrace/background

user_dump_dest = /oracle/<SID>/saptrace/usertrace

max_dump_file_size = 2000

core_dump_dest = /oracle/<SID>/saptrace/background

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = <SID>

open_cursors = 900

sortelimination_cost_ratio= 10

btree_bitmap_plans = FALSE

star_transformation_enabled= true

parallel_threads_per_cpu = 1

optimizer_index_cost_adj = 20

query_rewrite_enabled = false

indexjoin_enabled = FALSE

optimpeek_user_binds = FALSE

pga_aggregate_target = 1030941132

optimizermjc_enabled = FALSE

Thanks,

Nick S

[Former Member]

Do you have any ORA- errors in the alert log during this situation?

Kind regards,

Mark

[Former Member]

Hi,

No errors are displayed in the alert<SID>.log

Thanks,

Nick S