ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

This error normally occurs when you are trying to import a dump file which is residing on an NFS file system or you are copying a file from NFS filesystem onto ASM. The way to resolve the problem is to mount the NFS filesystem with the correct options.

1. Unmount the NFS file system where the file is residing

 umount -f /filesystem 

2. Mount with correct options

  mount -F nfs -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,forcedirectio,nointr,proto=tcp,suid  host:/folder1/to1 /folder2/to2 

Now try importing the dump/ copying the file to ASM again and it should work like a charm.

Advertisements

ORA-29701: unable to connect to Cluster Synchronization Service

Problem
——
Error on starting ASM from SQLPLUS or ASMCMD

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL> exit
Disconnected

Solution
——–

As Grid User

$ crsctl start res ora.cssd
CRS-2672: Attempting to start ‘ora.cssd’ on ‘prmdb’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘prmdb’
CRS-2676: Start of ‘ora.diskmon’ on ‘prmdb’ succeeded

$ asmcmd
Connected to an idle instance.
ASMCMD> startup
ORA-00099: warning: no parameter file specified for ASM instance

ASM instance started

Total System Global Area 317333504 bytes
Fixed Size 2221120 bytes
Variable Size 289946560 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

$ sqlplus “/as sysasm”

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 15 12:53:09 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Automatic Storage Management option

SQL> select name,state from v$asm_diskgroup;

NAME STATE
—————————— ———–
DATA MOUNTED

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

bash-3.2$ impdp system/***** directory=import_dir nologfile=y dumpfile=exp_swx_20092012.dmp full=y

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/USwitch/exportdump/exp_swx_20092012.dmp" for read
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 18

While trying to import a dumpfile from an NFS mount point via impdp in Oracle 11g 11.2.0.3 you get below error

Solution : I solved this issue using a dual approach. the 2nd step is one I got from searching on the internet but the directory permission i discovered after a lot of trial and error methods.

1. Give full permission to the import directory from OS. Suppose the directory which you created from the database was called import_dir . Then check location of directoru from view dba_directories and give full permission to it.

SQL> select * from dba_directories;

$ chmod 777 /USwitch/exportdump

Unmount and Mount the NFS file system again.

2. Set below parameter in init.ora file and then bounce database

event=’10298 trace name context forever, level 32′

Now try import again. If running RAC use nologfile=y parameter

bash-3.2$ impdp system/sys123 directory=import_dir nologfile=y dumpfile=exp_swx_20092012.dmp full=y

ORA-00257 archiver error. Connect internal only, until freed

// *Cause: The archiver process received an error while trying to archive
// a redo log. If the problem is not resolved soon, the database
// will stop executing transactions. The most likely cause of this
// message is the destination device is out of space to store the
// redo log file.
// *Action: Check archiver trace file for a detailed description
// of the problem. Also verify that the
// device specified in the initialization parameter
// ARCHIVE_LOG_DEST is set up properly for archiving.

This error occurs when the archive log destination has got full and archiver process cannot flush redo log file to disk. The database will not be able to execute any transactions. The best way is to take backup of the archivelogs and free the disk space where archivelogs reside.

Delete large number of records Oracle

When a large number of records neds to be deleted from a table. If you do a normal delete it will consume the UNDO tablespace. To avoid such a scenario in a production environment. You can use below procedure to commit frequently. In my opinion it is better to schedule the procedure as a daily job.

declare

— commit every ‘i_commit’ rows
i_commit pls_integer := 1000;

— row counter, ‘i_rowcount’ rows deleted
i_rowcount pls_integer := 0;

begin

— define ‘infinite’ loop
loop

— delete ‘i_commit’ rows, name of table and other criteria
delete from obj
where rownum <= i_commit and dd_date < sysdate-3;

i_rowcount := i_rowcount + sql%rowcount;

— now it's time to exit the loop
if sql%rowcount = 0 then
commit;
exit;
end if;

commit;

end loop;

— feedback
dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) ||

' rows deleted') ;

end;
/

ORA-00980: synonym translation is no longer valid

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,’SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

 

Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.

The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;

ORA-38753: Cannot flashback data file ; no flashback log data. ORA-01110: data file

CAUTION !!!! DO NOT DO THIS ON YOUR PRIMARY. IF YOU DO THEN YOU WOULD REQUIRE MEDIA RECOVERY ON THE DATAFILE YOU BROUGHT OFFLINE !!!

 

If your getting this error when you converting a Snapshot Standby Database (11gR2) back to Physical Standby Database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: ‘+DATA/swxstandby/datafile/swx.271.780160475’

When you get this error do as below :

SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

TS# TS_NAME                        FLA ———- ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— ———- —————————— —          1 +DATA/swxstandby/datafile/system.270.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             0 SYSTEM                         YES          2 +DATA/swxstandby/datafile/sysaux.269.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             1 SYSAUX                         YES          3 +DATA/swxstandby/datafile/undotbs1.268.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2 UNDOTBS1                       YES          5 +DATA/swxstandby/datafile/undotbs2.267.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           5 UNDOTBS2                       YES          4 +DATA/swxstandby/datafile/swx.271.780160475                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                7 SWX                            NO          6 +DATA/swxstandby/datafile/osms.266.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               9 OSMS                           YES

6 rows selected.

See which datafile has flashback_on colmn set as NO

SQL> alter database datafile 4 offline;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— RESTORE POINT ONLY

SQL> select name, flashback_on from v$tablespace ;

NAME                           FLA —————————— — SYSTEM                         YES SYSAUX                         YES UNDOTBS1                       YES SWX                            NO TEMP                           YES UNDOTBS2                       YES OSMS                           YES

SQL> alter tablespace SWX flashback on;

Tablespace altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— NO

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE ——————– ——————– —————- MOUNTED              RECOVERY NEEDED      PHYSICAL STANDBY