cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle table nologging during r3load

Former Member
0 Kudos

Hi,

Need some advise here. We are working on hardware migration from AIX to Linux. In the test system export/import is taking way too long. I am working on tuning the table splitting. At the same time was also thinking if we turn off the table logging during import, it would speed up the whole process significantly. I can't find any OSS note regarding the same so far.

Have you use nologging during imprt in such a case?

If you did, how did you do?

Gratly appreciate any idea/help here.

Thank you,

Sameer Pant

Accepted Solutions (0)

Answers (3)

Answers (3)

audunlea_hansen
Active Participant
0 Kudos

Hi!

Markus have a point you should use most of your time on.

If you desire to migrate with a tablespace in nologing mode, you should think about turning login off for the whole database during the import. This can speed up the import since the machine doesn't need to write arch-files to disk. If something goes wrong, you have to do the whole import again since you can't restore point in time. When switching login off, take a full backup of database again as soon as logging are turned on and before users are allowed to log on. This is not my preferred option...

As Markus tells, take a close look to identify why the import are taking long time. Import-buffer too small? Not optimized parallel? Too many parallel load jobs can also slow down the import. Do you have enough and big enough redolog files? Take a look in alert.log for wait-messages pointing to redolog. 8-12 redolog groups may are a good idea during the import. Have them on fast disks.

Regards

Audun

DBA

Former Member
0 Kudos

Running the database in ARCHIVELOG mode during the load is definitely a no go in my opinion, i could have bet that this is even the default behavior. I think i would have had more archiver stucks during database loading...

Cheers Michael

former_member709110
Active Participant
0 Kudos

Hi Sameer,

You can pass the NOLOGGING clause in the TPL file, but i would advice you to follow Markus's recommendation. As a best practise first identify what is your bottleneck , and then go for the various options to speedup the process. Sometimes just blindly going for all the methods available to accelerate (unsorted , table spliting , etc) doesn't help much instead makes the entire process too complicated. So first find out what your bottleneck ....

Regards,

Neel

markus_doehr2
Active Contributor
0 Kudos

> Need some advise here. We are working on hardware migration from AIX to Linux. In the test system export/import is taking way too long. I am working on tuning the table splitting. At the same time was also thinking if we turn off the table logging during import, it would speed up the whole process significantly. I can't find any OSS note regarding the same so far.

Did you figure out what acutally is taking too long? You have limited i/O? Doing export and import in parallel? It's technically possible (with the proper hardware) to migrate 1 TB/hour.

Markus