Home » RDBMS Server » Server Administration » Some operations on large tables on dNFS Clone failing with ORA-03113: end-of-file on communication c (11.2.0.4)
Some operations on large tables on dNFS Clone failing with ORA-03113: end-of-file on communication c [message #622735] |
Thu, 28 August 2014 14:17 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fe7731edc1888c91b902e9ee3e931cf8?s=64&d=mm&r=g) |
dstolf
Messages: 9 Registered: August 2014 Location: Brazil
|
Junior Member |
|
|
This is a though one, with many variables, so bear with me.
I have created two databases using Oracle dNFS clone. The backup image is stored on ASM and both clones are stored on NFS and use the same backup image. It's a plain simple single instance configuration with ASM, no RAC or RAC OneNode.
It s a setup I've always made with no major issues, until now. The only major difference between my previous setups and now is that NFS is configured in a loop, pointing to an export inside the own database machine. I know it's ugly, but I don't have a storage with sharing options available this time and no spare machine to work as NFS server either ![Sad](images/smiley_icons/icon_sad.gif)
On many operations on some large tables (`CREATE INDEX` or `CREATE TABLE AS SELECT` or even and `UPDATE` statement), I'm getting the following error:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 26928
Session ID: 951 Serial number: 25
The error is NOT consistent. Some times I try to create a large 'temporary' table to do some tests and it works... some time it doesn't. Creating indexes on big tables always trigger the error. `UPDATES` may or may not trigger the error.
The database instance alert.log shows me:
ERROR: unrecoverable error ORA-15310 raised in ASM I/O path; terminating process 8111
The ASM alert.log shows nothing. Really, I'm not omitting anything I deem useless, it really shows nothing at the moment the error occurs.
I've used the clone method explained in the link bellow, just adding a few changes to clone.pl to be able to use backup image stored on ASM (and some other more to make it more... hmm... automatic).
http://www.oracle-base.com/articles/11g/clonedb-11gr2.php
Anyways, the actual perl script doesn't really matter, it's the SQL generated scripts that do matter.
crtdb.sql looks like this
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
STARTUP NOMOUNT PFILE=/mnt/nfs/oracle/SIGEPSQ2/initSIGEPSQ2.ora
CREATE CONTROLFILE REUSE SET DATABASE SIGEPSQ2 RESETLOGS
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXINSTANCES 1
MAXLOGHISTORY 908
MAXDATAFILES 300
LOGFILE
GROUP 1 '/mnt/nfs/oracle/SIGEPSQ2/onlinelog/SIGEPSQ2_log1.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/mnt/nfs/oracle/SIGEPSQ2/onlinelog/SIGEPSQ2_log2.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'+DATA_SIGEPSQA/sigepsqa/datafile/ANALISTA.343.856398795',
'+DATA_SIGEPSQA/sigepsqa/datafile/SYSAUX.339.856398619',
'+DATA_SIGEPSQA/sigepsqa/datafile/SYSTEM.340.856398765',
'+DATA_SIGEPSQA/sigepsqa/datafile/TOOLS.342.856398793',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_AUDITSIS.341.856398767',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.328.856396117',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.329.856396853',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.330.856396887',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.337.856398299',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.332.856397587',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.333.856397587',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.334.856397663',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.335.856398189',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.336.856398213',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.308.856390947',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.309.856391053',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.310.856391199',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.311.856391783',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.312.856391949',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.313.856391993',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.314.856392609',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.315.856392793',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.316.856392819',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.317.856393455',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.318.856393629',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.319.856393665',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.320.856394309',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.321.856394465',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.322.856394479',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.323.856395185',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.324.856395291',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.273.856380501',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.274.856380517',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.275.856381351',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.296.856387511',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.300.856388377',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.325.856395291',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.326.856396067',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.327.856396091',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.270.856379625',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.272.856380501',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.276.856381367',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.277.856381451',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.298.856387611',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.306.856390167',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.269.856379625',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.279.856382263',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.280.856382327',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.281.856383103',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.282.856383147',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.283.856383203',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.284.856383989',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.285.856384043',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.286.856384047',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.287.856384891',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.297.856387527',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.299.856388377',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.304.856389427',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.288.856384891',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.289.856384937',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.292.856385801',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.293.856386655',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.295.856386685',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.301.856388551',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.302.856389207',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.305.856390073',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.271.856379625',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.278.856382217',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.290.856385781',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.291.856385785',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.294.856386671',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.303.856389273',
'+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.307.856390323',
'+DATA_SIGEPSQA/sigepsqa/datafile/UNDOTBS1.331.856396903',
'+DATA_SIGEPSQA/sigepsqa/datafile/UNDOTBS1.338.856398573'
CHARACTER SET WE8DEC;
dbren.sql looks like this:
declare
begin
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/ANALISTA.343.856398795' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ20.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/SYSAUX.339.856398619' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ21.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/SYSTEM.340.856398765' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ22.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TOOLS.342.856398793' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ23.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_AUDITSIS.341.856398767' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ24.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.328.856396117' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ25.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.329.856396853' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ26.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.330.856396887' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ27.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIDATA01.337.856398299' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ28.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.332.856397587' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ29.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.333.856397587' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ210.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.334.856397663' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ211.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.335.856398189' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ212.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_PCIINDEX01.336.856398213' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ213.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.308.856390947' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ214.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.309.856391053' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ215.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.310.856391199' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ216.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.311.856391783' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ217.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.312.856391949' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ218.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.313.856391993' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ219.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.314.856392609' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ220.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.315.856392793' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ221.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.316.856392819' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ222.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.317.856393455' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ223.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.318.856393629' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ224.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.319.856393665' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ225.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.320.856394309' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ226.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.321.856394465' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ227.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.322.856394479' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ228.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.323.856395185' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ229.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA01.324.856395291' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ230.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.273.856380501' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ231.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.274.856380517' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ232.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.275.856381351' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ233.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.296.856387511' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ234.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.300.856388377' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ235.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.325.856395291' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ236.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.326.856396067' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ237.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA02.327.856396091' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ238.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.270.856379625' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ239.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.272.856380501' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ240.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.276.856381367' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ241.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.277.856381451' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ242.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.298.856387611' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ243.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSDATA03.306.856390167' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ244.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.269.856379625' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ245.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.279.856382263' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ246.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.280.856382327' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ247.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.281.856383103' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ248.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.282.856383147' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ249.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.283.856383203' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ250.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.284.856383989' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ251.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.285.856384043' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ252.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.286.856384047' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ253.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.287.856384891' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ254.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.297.856387527' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ255.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.299.856388377' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ256.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX01.304.856389427' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ257.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.288.856384891' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ258.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.289.856384937' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ259.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.292.856385801' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ260.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.293.856386655' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ261.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.295.856386685' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ262.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.301.856388551' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ263.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.302.856389207' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ264.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX02.305.856390073' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ265.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.271.856379625' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ266.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.278.856382217' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ267.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.290.856385781' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ268.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.291.856385785' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ269.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.294.856386671' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ270.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.303.856389273' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ271.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/TS_SIGEPSINDEX03.307.856390323' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ272.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/UNDOTBS1.331.856396903' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ273.dbf');
dbms_dnfs.clonedb_renamefile('+DATA_SIGEPSQA/sigepsqa/datafile/UNDOTBS1.338.856398573' , '/mnt/nfs/oracle/SIGEPSQ2/datafile/ora_data_SIGEPSQ274.dbf');
end;
/
show errors;
alter database open resetlogs;
CREATE TEMPORARY TABLESPACE TEMP2;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
DROP TABLESPACE TEMP;
ALTER TABLESPACE TEMP2 RENAME TO TEMP;
Finally init.ora looks like this:
*.aq_tm_processes=3
*.audit_file_dest='/oracle/admin/sigepsq2/adump'
*.audit_trail='NONE'
*.backup_tape_io_slaves=TRUE
*.compatible='11.2.0.0.0'
control_files=/mnt/nfs/oracle/SIGEPSQ2/SIGEPSQ2_ctl.dbf
*.cursor_sharing='FORCE'
*.db_block_size=8192
db_name=SIGEPSQ2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sigepsqaXDB)'
*.fast_start_mttr_target=300
*.job_queue_processes=100
*.log_archive_format='ora9_%t_%s_%r.arc'
*.log_archive_max_processes=10
*.log_checkpoint_timeout=0
*.nls_territory='BRAZIL'
*.open_cursors=1000
*.open_links=10
*.open_links_per_instance=10
*.optimizer_index_caching=70
*.optimizer_index_cost_adj=10
*.pga_aggregate_target=5G
*.processes=2500
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.sec_case_sensitive_logon=FALSE
*.sga_target=5G
*.undo_retention=16000
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'
*.utl_file_dir='*'
db_create_file_dest=/mnt/nfs/oracle
log_archive_dest=/mnt/nfs/oracle/SIGEPSQ2/archivelog
db_create_online_log_dest_1=/mnt/nfs/oracle
clonedb=true
cluster_database=false
_no_recovery_through_resetlogs=TRUE
This is the /etc/exports
/oradatanfs 10.147.0.8(rw,sync,no_wdelay,insecure,insecure_locks,no_root_squash,anongid=601)
This is /etc/fstab
10.147.0.8:/oradatanfs /mnt/nfs/oracle nfs noauto,rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,nfsvers=3,timeo=600 0 0
This is /etc/mtab
10.147.0.8:/oradatanfs /mnt/nfs/oracle nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,nfsvers=3,timeo=600,addr=10.147.0.8 0 0
This is /etc/oranfstab
server: p08
local: 10.147.0.8
path: 10.147.0.8
dontroute
export: /oradatanfs mount: /mnt/nfs/oracle
All permissions on NFS are set to oracle:dba. I've tried chmod'ing everything to 777 or 1777, but the error remained.
I know it's a long post and a very complex and challenging scenario, but can anybody help me on this one? I'm trying to fix it for a couple a days now, but the ORA-15310 error is too generic and there's nothing on MOS to help me out. On top of that, I'm not sure which one, NFS or ASM, is to blame here.
Regards,
Daniel Stolf
|
|
|
|
Re: Some operations on large tables on dNFS Clone failing with ORA-03113: end-of-file on communication c [message #622737 is a reply to message #622736] |
Thu, 28 August 2014 14:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/fe7731edc1888c91b902e9ee3e931cf8?s=64&d=mm&r=g) |
dstolf
Messages: 9 Registered: August 2014 Location: Brazil
|
Junior Member |
|
|
Hi,
This is the last trace generated with the error:
Trace file /oracle/diag/rdbms/sigepsq2/SIGEPSQ2/trace/SIGEPSQ2_ora_23091.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db
System name: Linux
Node name: p08.XXXXXXXXXXXXXXXXXXXX
Release: 2.6.32-431.20.3.el6.x86_64
Version: #1 SMP Fri Jun 6 18:30:54 EDT 2014
Machine: x86_64
Instance name: SIGEPSQ2
Redo thread mounted by this instance: 1
Oracle process number: 57
Unix process pid: 23091, image: oracle@p08.XXXXXXXXXXXXXXXX
*** 2014-08-28 16:26:06.184
*** SESSION ID:(1425.17) 2014-08-28 16:26:06.184
*** CLIENT ID:() 2014-08-28 16:26:06.184
*** SERVICE NAME:(SIGEPSQ2) 2014-08-28 16:26:06.184
*** MODULE NAME:(PL/SQL Developer) 2014-08-28 16:26:06.184
*** ACTION NAME:(Janela SQL - RELEASE_115626_06.sql) 2014-08-28 16:26:06.184
Error ORA-15310 signaled at ksedsts()+465<-ksf_short_stack()+77<-kge_snap_callstack()+63<-kge_sigtrace_dump()+69
<-kgepop()+750<-kgeselv()+290<-ksesecl0()+162<-kfk_io1()+1051<-kfkRequest()+14<-kfk_transitIO()+1712
<-kfioSubmitIO()+4777<-kfioRequestPriv()+182<-kfioRequest()+691<-ksfd_kfioRequest()+649<-ksfd_osmio()+1053
<-ksfd_io()+2782<-ksfdss_checkdata()+922<-ksfdss_checkreq()+305<-ksfdpcl()+1598<-ksfd_odmwat()+1297
<-ksfdwtio()+1073<-ksfdwat_internal()+649<-kfk_reap_ufs_async_io()+101<-kfk_reap_ios_from_subsys()+297
<-kfk_reap_ios()+2152<-kfk_io1()+1108<-kfkRequest()+14<-kfk_transitIO()+1712<-kfioReapIO()+448
<-kfioRequestPriv()+392<-kfioRequest()+691<-ksfd_kfioRequest()+649<-ksfd_osmwat()+942<-ksfdwtio()+1199
<-ksfdwat_internal()+649<-ksfdblock()+210<-kcflwi()+284<-kcflci()+1728<-kcblci()+369<-kcblcio()+192
<-kcblgt()+602<-kcbldrget()+297<-kcbgtcr()+53092<-ktrget3()+708<-ktrget2()+462<-kdst_fetch()+777
<-kdstf00011010000km()+3443<-kdsttgr()+50919<-qertbFetch()+2455<-qersoProcessULS()+223<-qersoFetch()+6131
<-qerliFetch()+324<-kdicrws()+1930<
ERROR: unrecoverable error ORA-15310 raised in ASM I/O path; terminating process 23091
----- Abridged Call Stack Trace -----
ksedsts()+465<-kfk_io1()+2867<-kfkRequest()+14<-kfk_transitIO()+1712<-kfioSubmitIO()+4777
<-kfioRequestPriv()+182<-kfioRequest()+691<-ksfd_kfioRequest()+649<-ksfd_osmio()+1053<-ksfd_io()+2782
<-ksfdss_checkdata()+922<-ksfdss_checkreq()+305<-ksfdpcl()+1598<-ksfd_odmwat()+1297
<-ksfdwtio()+1073<-ksfdwat_internal()+649<-kfk_reap_ufs_async_io()+101<-kfk_reap_ios_from_subsys()+297
<-kfk_reap_ios()+2152<-kfk_io1()+1108<-kfkRequest()+14<-kfk_transitIO()+1712<-kfioReapIO()+448
<-kfioRequestPriv()+392<-kfioRequest()+691<-ksfd_kfioRequest()+649
<-ksfd_osmwat()+942<-ksfdwtio()+1199<-ksfdwat_internal()+649<-ksfdblock()+210<-kcflwi()+284<-kcflci()+1728
<-kcblci()+369<-kcblcio()+192<-kcblgt()+602<-kcbldrget()+297<-kcbgtcr()+53092<-ktrget3()+708<-ktrget2()+462
<-kdst_fetch()+777<-kdstf00011010000km()+3443
<-kdsttgr()+50919<-qertbFetch()+2455<-qersoProcessULS()+223<-qersoFetch()+6131<-qerliFetch()+324<-kdicrws()+1930
<-kdicdrv()+500<-opiexe()+22066<-opiosq0()+3932<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183
<-opitsk()+1710<-opiino()+969<-opiodr()+917
<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+253
[Updated on: Fri, 29 August 2014 00:59] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Some operations on large tables on dNFS Clone failing with ORA-03113: end-of-file on communication c [message #623873 is a reply to message #622735] |
Mon, 15 September 2014 13:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/fe7731edc1888c91b902e9ee3e931cf8?s=64&d=mm&r=g) |
dstolf
Messages: 9 Registered: August 2014 Location: Brazil
|
Junior Member |
|
|
If doing some operations (CREATE INDEX, CREATE TABLE AS SELECT, ALTER TABLE ADD), I would some times get the following error on my client:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 14850
Session ID: 1744 Serial number: 2149
Connecting locally as sysdba would get the same error.
Alert.log would show me the error:
ERROR: unrecoverable error ORA-15310 raised in ASM I/O path; terminating process 15587
A trace file would get the following error:
Error ORA-15310 signaled at ksedsts()+465<-ksf_short_stack()+77<-kge_snap_callstack()+63<-kge_sigtrace_dump()+69<-kgepop()+750<-kgeselv()+290<-ksesecl0()+162<-kfk_io1()+1051<-kfkRequest()+14<-kfk_transitIO()+1712<-kfioSubmitIO()+4777<-kfioRequestPriv()+182<-kfioRequest()+691<-ksfd_kfioRequest()+649<-ksfd_osmio()+1053<-ksfd_io()+2782<-ksfdss_checkdata()+922<-ksfdss_checkreq()+305<-ksfdpcl()+1598<-ksfd_odmwat()+1297<-ksfdwtio()+1073<-ksfdwat_internal()+649<-kfk_reap_ufs_async_io()+101<-kfk_reap_ios_from_subsys()+297<-kfk_reap_ios()+2152<-kfk_io1()+1108<-kfkRequest()+14<-kfk_transitIO()+1712<-kfioReapIO()+448<-kfioRequestPriv()+392<-kfioRequest()+691<-ksfd_kfioRequest()+649<-ksfd_osmwat()+942<-ksfdwtio()+1199<-ksfdwat_internal()+649<-ksfdblock()+210<-kcflwi()+284<-kcflci()+1728<-kcblci()+369<-kcblcio()+192<-kcblgt()+602<-kcbldrget()+297<-kcbgtcr()+53092<-ktrget3()+708<-ktrget2()+462<-kdst_fetch()+777<-kdstf01001010000km()+3578<-kdsttgr()+55084<-qertbFetch()+2455<-rwsfcd()+103<-qerhjFetch()+2943<-qerjotFetch()+467<-qerjotFetch()+467<-rwsf
I didn't get any error on duplicate, but I also noticed that the pfile copy I used had
_no_recovery_through_resetlogs=TRUE
_allow_resetlogs_corruption=TRUE
So maybe I missed some archives needed for recovery when I've opened the clone database.
Copying the database once again and issuing several alter system archive log current on the master copy before using it did the trick, at least for now.
[Updated on: Mon, 15 September 2014 13:44] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sun May 19 19:10:57 CDT 2024
|