ORA-07391: sftopn: fopen error, unable to open text file

Have gotten this error a number of times while attempting to create a PFILE from SPFILE:

CREATE PFILE='/u01/oracle/product/11.2.0.3/dbs/initdb1.ora' FROM SPFILE='/u01/oracle/product/11.2.0.3/dbs/spfiledb1.ora'

Cause: Not sufficient permisiions to read from SPFILE

Solution: Run chmod 777 /u01/oracle/product/11.2.0.3/dbs/spfiledb1.ora to set appropriate permissions

The Oracle system identifier '' already exists, specify another SID

The Oracle system identifier '' already exists, specify another SID error occurs during database creation via dbca utility.

Cause: You have probably previously created database with the same name and have removed it but the traces of it still remain

Solution: Open /etc/oratab file in edit mode and remove the line containing SID that is causing an error message. Another place to look would be $ORACLE_BASE/oraInventory/ContentsXML/inventory.xml - this file could also contain an entry with the offending SID

How to find out when is the last time a particular table was updated

In order to find out when was a particular table updated last (in Oracle), use ORA_ROWSCN:

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM TableName;

How to modify a parameter in SPFILE

Method 1:

1. Run
   

   ALTER SYSTEM SET processes=(processes + 5) SCOPE=SPFILE SID='*';
   

2. Reboot (Issue shutdown and startup)

if it is not a parameter that can be modified via ALTER SYSTEM then use:

Method 2:

1. Run
   

   CREATE PFILE='$ORACLE_HOME/dbs/initSID.ora' FROM SPFILE='location/spfile.ora';
   

   this will create PFILE called initSID.ora at $ORACLE_HOME/dbs
2. Make changes to initSID.ora file via vi editor



3. Shutdown the database



4. Startup (nomount) the instance where you created and altered the pfile using this pfile only




STARTUP PFILE=$ORACLE_HOME/dbs/initSID.ora

5. Create a new spfile




 CREATE SPFILE='location/spfile.ora' FROM PFILE=‘$ORACLE_HOME/dbs/initSID.ora’;

6. Shutdown this instance again



7. Now startup normally without PFILE or SPFILE option:




STARTUP

8. Remove the PFILE (Optional)



9. To confirm that the parameter has been modified, issue following sql statement:




SHOW PARAMETER 

[INS-30043] The grid infrastructure home does not exist or is empty

I have gotten this error while attempting to re-install Oracle on the UNIX server.

The old version of Oracle was not de-installed properly, and so when new version was being installed, I got an error "[INS-30043] The grid infrastructure home '/u10/app/oracle/product/11.2.0/grid' does not exist or is empty".sion i was installing was 11.2.0.3 and the folder 11.2.0 didn't even exist.

The resolution was quite simple though - manually editing inventory.xml file. It can be found in $ORACLE_BASE/oraInventory/ContentsXML directory. I had to remove the entries pointing to the deleted folders and my issue was resolved.

inventory.xml file lists all the Oracle products installed on the machine and when directories are deleted manually, the entries are not removed from inventory.xml file. That's why it is a good idea to perform a deinstallation when removing any Oracle products from the server as opposed to just manually removing directores (applies to 11.2.0 and higher)

Some useful commands for vi Editor

Command	Action
x	delete one character
dw	delete current word
dd	delete current line
D	delete all content to the rigght of the cursor
:u	undo last command
:q	quit editor without saving
:wq	save and quit editor
:w	write without exit
:[n]	goto line [n]
b	move backwards one word
i	begin inserting text at the current cursor location

Oracle vs. SQL Server - Oracle equivalents for SQL Server DBAs

SQL Server	Oracle
SELECT GETDATE()	select sysdate from dual
SUBSTRING function does not take argument for starting position with negative value	SUBSTR can have negative starting position in Oracle
SELECT INTO Table1 FROM Table2	CREATE Table1 AS SELECT FROM Table2
master database	data dictionary
syslogins view	dba_users view
sys.objects view	dba_objects view
sys.tables	dba_tables view
tempdb	Temporary tablespace
IDENTITY field automatically incremented by 1 on INSERT	Creating a sequence using: CREATE SEQUENCE name_seq START WITH 1000 INCREMENT BY 1; and a trigger to activate the sequence on BEFORE INSERT: CREATE OR REPLACE TRIGGER name_bir BEFORE INSERT on TableName FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT name_seq.NEXTVAL INTO :new.id FROM dual; END;
ISNULL() ISNULL(Field1, 0) returns 0 if Field1 is null	NVL() NVL(Field1, 0) returns 0 if Field1 is null