Oracle mass data migration-use shell to enable multiple dynamic Parallelism
In Oracle data migration, there may be hundreds of tables, some of which are large and some are small.
If multiple parallel processes are enabled, resource allocation may occur.
For example, there are 10 tables, and 100 represents the estimated time of 100 minutes.
Table1 100
Table2 90
Table3 90
Table4 80
Table5 80
Table6 70
Table7 60
Table8 60
Table9 50
Table10 40
If there are four processes for parallel execution, an ideal solution may be:
Parallel1: table1, table8
Parallel2: table2, table5, table9
Parallel3: table3, table6, table9
Parallel4: table4, table7
However, in actual execution, the actual execution time may vary greatly because of the table partition, table data type, and table Storage,
It is possible that paralle2, 3, 4 have been executed, while parallel1 has not yet executed 50%.
In this way, table8 has been pending there.
In this case, you can consider using dynamic parallelism to enable some parallel processes in the background. For example, if four parallel processes are required, use nohup to enable four parallel processes.
Do not perform specific data operations.
Parfile = par2_tab_parall.lst
Logfile = 'echo $ parfile | awk-F. '{print $1}' ". log"
While true
Do
If [-f $ parfile]
Then
Tab_exists_flag = 'cat $ parfile | wc-l'
If [$ {tab_exists_flag}-eq 0]
Then
Sleep 5;
Elif [$ {tab_exists_flag}-gt 0]
Then
Tab_name = 'cat $ parfile'
Ksh appendata. sh $ tab_name> $ logfile
Touch $ {parfile}. tmp
Mv $ {parfile}. tmp $ {parfile}
Fi
Fi
Done
We use appendata. sh to simulate data insertion. In fact, it does not actually insert data, which is the log Content.
Echo $1
Sqlplus-s n1/n1 <EOF
Set time on
Set timin on
Set pages 0
Select 'insert into' | '$1;' from dual;
Select 'commit; 'from dual;
EOF
If [$? -Eq 0]
Then
Echo ''> parallel1.lst
Fi
Use the following command to enable a process. For example, run the following command to enable process 2. If other processes are enabled, the command is similar
Nohup ksh par2.sh> par2_tab_parall.log &
You only need to put the processed table name in a file. For process 2, put the table name in par2_tab_parall.lst. Assume that the table is named test.
Par2_tab_parall.lst
[Ora11g @ rac1 parallel] $ cat par2_tab_parall.lst
Test
If you put the test in the table, you will find that the 2nd processes start to process the test table.
Test
Insert into test;
Elapsed: 00:00:00. 00
Commit;
Elapsed: 00:00:00. 01
Put another table, and the process will immediately begin to process the table tab_test. If there is no table, it will perform sleep in the background.
[Ora11g @ rac1 parallel] $ echo tab_test> par2_tab_parall.lst
Tab_test
Insert into tab_test;
Elapsed: 00:00:00. 01
Commit;
Elapsed: 00:00:00. 00
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian