News

  1. Home
  2. All posts
All posts, Oracle by Team Ajara

Import Running longer time due to huge log switch

Import Running longer time due to huge log switch.

 

Issue:

 

For 56G schema it is taking 3 hours to import the data which is too slow. One of the main issue it seems log switching,
There’s  huge number of archives are getting generated.
Even though “disable logging” parameter enabled in impdp parfile, it is generating huge archives.

There are 20 group in two threads of ORCLP1 database and size of each groups 250 MB.

 

Action taken:

Resized redo log group from 250MB to 4GB and 10 groups in each thread.

And resized redo log files on all the databases that are using frequently by the application Team.

 

Before Resizing the redo log files

 

SQL> select GROUP#, THREAD#,SEQUENCE#,BYTES/1024/1024/1024,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;

GROUP# THREAD# SEQUENCE# BYTES/1024/1024/1024 BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ——————– ———- ———- — —————- ————- ——— ———— ———
100 1 26273 .244140625 512 2 YES INACTIVE 404720645 18-NOV-17 404723066 18-NOV-17
101 1 26274 .244140625 512 2 YES INACTIVE 404723066 18-NOV-17 404731870 18-NOV-17
102 1 26275 .244140625 512 2 YES INACTIVE 404731870 18-NOV-17 404768070 18-NOV-17
103 1 26276 .244140625 512 2 YES INACTIVE 404768070 18-NOV-17 404839433 18-NOV-17
104 1 26277 .244140625 512 2 NO CURRENT 404839433 18-NOV-17 2.8147E+14
105 1 26268 .244140625 512 2 YES INACTIVE 404708922 18-NOV-17 404709260 18-NOV-17
106 1 26269 .244140625 512 2 YES INACTIVE 404709260 18-NOV-17 404712039 18-NOV-17
107 1 26270 .244140625 512 2 YES INACTIVE 404712039 18-NOV-17 404715078 18-NOV-17
108 1 26271 .244140625 512 2 YES INACTIVE 404715078 18-NOV-17 404719254 18-NOV-17
109 1 26272 .244140625 512 2 YES INACTIVE 404719254 18-NOV-17 404720645 18-NOV-17
200 2 21413 .244140625 512 2 YES INACTIVE 404719633 18-NOV-17 404721248 18-NOV-17

GROUP# THREAD# SEQUENCE# BYTES/1024/1024/1024 BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ——————– ———- ———- — —————- ————- ——— ———— ———
201 2 21414 .244140625 512 2 YES INACTIVE 404721248 18-NOV-17 404723722 18-NOV-17
202 2 21415 .244140625 512 2 YES INACTIVE 404723722 18-NOV-17 404726012 18-NOV-17
203 2 21416 .244140625 512 2 YES INACTIVE 404726012 18-NOV-17 404728495 18-NOV-17
204 2 21417 .244140625 512 2 YES INACTIVE 404728495 18-NOV-17 404733898 18-NOV-17
205 2 21418 .244140625 512 2 YES INACTIVE 404733898 18-NOV-17 404738844 18-NOV-17
206 2 21419 .244140625 512 2 YES INACTIVE 404738844 18-NOV-17 404767223 18-NOV-17
207 2 21420 .244140625 512 2 YES INACTIVE 404767223 18-NOV-17 404844165 18-NOV-17
208 2 21421 .244140625 512 2 NO CURRENT 404844165 18-NOV-17 2.8147E+14
209 2 21412 .244140625 512 2 YES INACTIVE 404715569 18-NOV-17 404719633 18-NOV-17

Redo logfiles of ORCLP1 database have been resized to 4G as given below

Drop inactive redo log groups and Add with 4GB.

SQL>ALTER DATABASE DROP LOGFILE GROUP 1; 

--Add that same group with new size , i.e)
SQL>alter database add logfile thread 1 group 1 ('+DATA','+FRA') size 4G;

 SQL>alter database add logfile thread 2 group 1 ('+DATA','+FRA') size 4G;
 
 

After Resizing the redo log files , Verify with the below query

SQL> select GROUP#, THREAD#,SEQUENCE#,BYTES/1024/1024/1024,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;

 

See also: