Troubleshoot ORA-10878

You will probably not hit this bug unless you perform some media recovery in 11.2.0.1/AIX.

Ok. In case you hit ORA-10878: parallel recovery slave died unexpectedly during a DUPLICATE or a RESTORE command, you can disable parallel media recovery with _log_parallelism_max=1.

The usual warning applies : do not use hidden parameter without guidance of Oracle Support. Open an SR if you hit this bug. Check for a patch on your plateform. Read notes 9728806.8 and 315631.1.

Note: for a RECOVER, the option RECOVER NOPARALLEL must be safer. Unfortunately there is no such thing like DUPLICATE NOPARALLEL

Update: This could also happened with standby, if you have stopped your standby site for a while and after restart you get ORA-10878 and ORA-00448 and evtl core dumps or internal errors, then stop dataguard (set dg_broker_start to false) and start the recovery manually with the noparallel option, until all logs are applied. Once this is done, you can restart dataguard, which will then in normal operation mode apply only one log at the time.
Diggout out from Helios’s Blog

scp tuning

I twitted yesterday :

laurentsch
copying 1TB over ssh sucks. How do you fastcopy in Unix without installing Software and without root privilege?

I got plenty of expert answers. I have not gone to far in recompile ssh and I did not try plain ftp.

Ok, let’s try first to transfer 10 files of 100M from srv001 to srv002 with scp :

time scp 100M* srv002:
100M1    100%   95MB   4.5MB/s   00:21
100M10   100%   95MB   6.4MB/s   00:15
100M2    100%   95MB   6.0MB/s   00:16
100M3    100%   95MB   4.2MB/s   00:23
100M4    100%   95MB   3.4MB/s   00:28
100M5    100%   95MB   4.2MB/s   00:23
100M6    100%   95MB   6.4MB/s   00:15
100M7    100%   95MB   6.8MB/s   00:14
100M8    100%   95MB   6.8MB/s   00:14
100M9    100%   95MB   6.4MB/s   00:15

real    3m4.50s
user    0m27.07s
sys     0m21.56s

more than 3 minutes for 1G.

I got hints about the buffer size, about SFTP, about the cipher algorythm, and about parallelizing. I did not install new software and I have a pretty old openssh client (3.8). Thanks to all my contributors tmuth, Ik_zelf, TanelPoder, fritshoogland, jcnars, aejes, surachart, syd_oracle and the ones the will answer after the writting of this blog post…

Ok, let’s try a faster algorythm, with sftp (instead of scp), a higher buffer and in parallel

$ cat batch.ksh
echo "progress\nput 100M1" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M2" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M3" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M4" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M5" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M6" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M7" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M8" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M9" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
echo "progress\nput 100M10" | sftp -B 260000 -o Ciphers=arcfour -R 512 srv002&
wait
$ time batch.ksh
real    0m19.07s
user    0m12.08s
sys     0m5.86s

This is a 1000% speed enhancement :-)

What is the current setting of NLS_LANG in sqlplus?

I just learnt a neat trick from Oracle Support.

How do you see the current value of NLS_LANG in SQLPLUS ?

HOST is not the right answer.

E.g.:
Unix:


SQL> host echo $NLS_LANG
AMERICAN_SWITZERLAND

Windows:

SQL> HOST ECHO %NLS_LANG%
%NLS_LANG%

The correct setting is revealed by @.[%NLS_LANG%]
E.g.:
Unix:


SQL> @.[$NLS_LANG]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

Windows:

SQL>  @.[%NLS_LANG%]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

It could well be that both return the same answer, but not necessarly, as shown above.

The unix discrepancy is related to the subshell created by HOST. The subshell may read some .profile and overwrite the value of NLS_LANG

In Windows, the NLS_LANG setting may be set by sqlplus according to some registry entries