Oracle FAQ

Installing Oracle 12.1 on CentOS or Red Hat 6.5 or greater

If you do NOT do the following, the prerequisite checks will always fail.

Change directory to /u01/installer/database/stage/cvu/cv/admin

vi cvu_config

Search for CV_ASSUME_DISTID=OEL4 and replace it with CV_ASSUME_DISTID=OEL6 or SUSE11

Then proceed as normal

Licensing

VMWare Processor Licensing FAQ

http://www.vmware.com/files/pdf/techpaper/vmw-understanding-oracle-certification-supportlicensing-environments.pdf

Official Pricing from Oracle

http://www.oracle.com/us/corporate/pricing/price-lists/index.html

1. Installation Issues

Change OMF Location (Oracle Managed Files)

Make sure the directory d:\oradata exists first.

ALTER SYSTEM SET db_create_file_dest = "d:\oradata" SCOPE=SPFILE

ALTER SYSTEM SET db_create_file_dest = "d:\oradata" SCOPE=MEMORY

See top queries

SELECT * FROM (SELECT sql_fulltext, sql_id, child_number, disk_reads, executions, first_load_time, last_load_time FROM v$sql ORDER BY elapsed_time DESC) WHERE ROWNUM < 10

Query to see users and open cursor count

select a.value, s.username, s.sid, s.serial#
 from v$sesstat a, v$statname b, v$session s
 where a.statistic# = b.statistic# and s.sid=a.sid
 and b.name = 'opened cursors current';

If alter system register is not registering the database with asm

try this:

alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST =10.100.56.31)(PORT=1521))';

If CRS is not starting on ASM Oracle Linux try these commands as ORACLE

cat /etc/oratab

export ORACLE_SID=+ASM

export ORACLE_HOME= (The home directory of ASM)

cd $ORACLE_HOME/bin

./crsctl start has

Automatic Startup Script

Log on as root.
Create a file called /etc/init.d/dbora with this content:

#!/bin/sh

# chkconfig: 345 99 10

# description: Oracle auto start-stop script.

#

# Set ORA_HOME to be equivalent to the $ORACLE_HOME

# from which you wish to execute dbstart and dbshut;

#

# Set ORA_OWNER to the user id of the owner of the

# Oracle database in ORA_HOME.

# Add like this:

ORA_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]

then

    echo "Oracle startup: cannot start"

    exit

fi

case "$1" in

    'start')

        # Start the Oracle databases:

        # The following command assumes that the oracle login

        # will not prompt the user for any values

        su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"

        ;;

    'stop')

        # Stop the Oracle databases:

        # The following command assumes that the oracle login

        # will not prompt the user for any values

        su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"

        ;;

esac

Run these commands as the root user:

chmod 775 /etc/init.d/dbora

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora

ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora

ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

chkconfig --level 345 dbora on

Set the SID to Autostart for the DBORA Script

Run this command as the Oracle user:

vi /etc/oratab

There will be a line towards the end of the file that looks something like this:

genesis:/u01/app/oracle/product/11.2.0/dbhome_1:N 

Change it to (The N at the end becomes a Y):

genesis:/u01/app/oracle/product/11.2.0/dbhome_1:Y 

SuSE - ip_local_port_range kernel param does not persist between reboots

This is caused by the built in Firewall.  The firewall will automatically set this Kernel parameter as a "security precaution".   You can either:

1. Disable the firewall
2. Modify the /etc/sysconfig/SuSEfirewall2 file.  Search for the paramater FW_KERNEL_SECURITY and set it to no; then reboot.

Invalid MEMORY_TARGET during installation

Add this line to the /etc/fstab and reboot:

shmfs                /dev/shm             tmpfs      size=16g              0 0

2. Maintenance Tasks

Analyze statistics in all tables and indexes

execute dbms_stats.GATHER_SCHEMA_STATS('DEMO');

When trying to login to sqlplus or ODBC; I'm getting an error that the account is locked

Oracle has locked the account; because too many bad password attempts were made on the account. To unlock the account; run this command in SQLPlus:

ALTER USER USER_NAME ACCOUNT UNLOCK;

SQLPlus says my password is going to expire in X days.

Somehow, Oracle has a expiration date on the password on that account. To clear it run this command in sqlplus as a DBA:

alter user LOGONID identified by PASSWORD;

Oracle 11g - how do I make passwords never expire

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

This will not change accounts that are already expired. To identify the state of existing accounts; run this query:

SQL> select username,account_status,expiry_date from dba_users order by username;

How do I kill a session in SQL Plus

Identify active connections to Oracle with this SQL command:

select sid,serial#,username,terminal,username from v$session where username='NORTHBRUNSWICK';

The command to kill a session is:

alter system kill session 'sid,serial#' immediate;

Configure Oracle Client to connect to remote server without specifying TNS Name

Normally, if you install the Oracle client on a workstation; you must access the server explicitly by entering the connection information; for example:

c:\> sqlplus username/password@server/sid

You can configure the Oracle tools to connect to a specific server/sid by default by configuring an environment variable.

• Windows
  Create an environment variable called LOCAL and set it to IPAddress/SID. For example:
• c:\> set LOCAL=10.1.1.1/genesis

To make the change permanent; add the local variable to the computer by:

• Right click, properties on My Computer
• Click on the Advanced Settings button
• Click on the Environment Variables Button
• Add the environment variable to the System Variables section.

• Linux
  Create an environment variable called TWO_TASK and set it to IPAddress/SID. For example:
• $ export TWO_TASK="10.1.1.1/genesis"

To make the change permanent; modify the file: /etc/profile (or equivalent file depending on the Linux Distribution*

Creating secure Oracle accounts for use in Genesis

Create tablespaces for the indexes and data

Make sure the database was installed using OMF or ASM. (Oracle Managed Files or Automatic Storage Management).

SQLPLUS>  create tablespace district_data;

SQLPLUS>  create tablespace district_index;

Create a user

SQLPLUS>  create user district identified by password default tablespace district_data;

SQLPLUS>  grant connect,resource to district;

The connect role gives the following rights to a user:

1. The ability to connect to the server

The resource role gives the following rights to a user:

1. CREATE CLUSTER
2. CREATE INDEXTYPE
3. CREATE OPERATOR
4. CREATE PROCEDURE
5. CREATE SEQUENCE
6. CREATE TABLE
7. CREATE TRIGGER
8. CREATE TYPE.

Importing a dump file:

imp system/systempassword@server/sid fromuser=district touser=district file=district.dmp

This process imports the district file through the system account. Because the system account is a DBA, it is allowed to import files from any user (or intsance) to any other user or instance.

When I run the Oracle installer, the terminal window shows for 1 second and disappears

The installer will do this when the OS partition exceeds 2TB.

ORA-12638 Credential Retrieval Error when connecting with ODBC

EXP-00056: ORACLE error 12638 encountered

ORA-12638: Credential retrieval failed

Open up SQLNET.ORA file (Usually in the network/admin directory of your Oracle client instalation)

Add or change to this line:

SQLNET.AUTHENTICATION_SERVICES= (NONE)

How can I see Object Locks?

Go to SQL Pad (or sqlplus) and run this query:

See Object Locks 

SELECT l.session_id||','||v.serial# sid_serial,

       l.ORACLE_USERNAME ora_user,

       o.object_name, 

       o.object_type, 

       DECODE(l.locked_mode,

          0, 'None',

          1, 'Null',

          2, 'Row-S (SS)',

          3, 'Row-X (SX)',

          4, 'Share',

          5, 'S/Row-X (SSX)',

          6, 'Exclusive', 

          TO_CHAR(l.locked_mode)

       ) lock_mode,

       o.status, 

       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl

FROM dba_objects o, gv$locked_object l, v$session v

WHERE o.object_id = l.object_id

      and l.SESSION_ID=v.sid

order by 2,3;

Tablespace has hit the 16/32gb cap:

 You have to add datafiles to the tablespace

Check to see which tablespace has hit the 16/32gb cap (dependent on which OS they are running (x86/x64))

To see where the dbf files are: select * from v$datafile;

Findind an ORacle Session when you have a PID

For example:  running top on the database shows several Oracle processing running at full speed.

open sqlplus on the Oracle database and run this command:

SELECT

a.sid,a.serial#,
a.username,
b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
and b.spid = 21421;

21421 is the first process in the TOP list.  This will return something like this:

SID,Serial,Username,SPID

3, 37601, BURLINGTON, 7416

You should attempt to bring down the instance normally at all costs.

If the instance is down and this is truely a zombie process; you can use this command to kill the session

alter system kill session '3,37601';

ORA-00059: maximum number of DB_FILES exceeded

 If you have this error in your alertSID.log:

ERROR at line 1:

ORA-00059: maximum number of DB_FILES exceeded

 You have to increase the number of file allowed for the database. The instance has to be restarted:

SQL> SHOW parameter db_files

--> 1500

SQL> ALTER system SET db_files = 3000 scope = BOTH;

SQL> shutdown immediate

SQL> startup

SQL> SHOW parameter db_files

--> 3000

SYSTEM Tablespace is full (32 Gigs)

Run this query: 

Select * from dba_segments where tablespace_name='SYSTEM' order by bytes desc; 

If use see a table called AUD$ and it is using most of the space, you can freely truncate it: 

truncate table sys.aud$ 

EOA 

Moving/renaming  tablespace datafiles 

Step One: BACKUP THE DATABASE

c:\> exp username/password@server/sid file=fullbackup.dmp grants=none statistics=none

Step Two: Shutdown Genesis

c:\> net stop "Apache Tomcat"

Step Three: Determine the location and name of the datafiles according to their associated tablespace

c:\> sqlplus username/password@server/sidSQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'GENESIS_DOCS';FILE_NAME

--------------------------------------------------------------------------------

c:\oradata\GENESIS\datafile\o1_mf_demo_doc_7k38q6lj_.dbf

Step Four: Take the tablespace containing the datafiles offline

SQL> ALTER TABLESPACE GENESIS_DOCS OFFLINE NORMAL;

Step Five: Copy the datafiles to their new locations and/or rename them using the operating system. This can be done via CLI or GUI.

Step Six: Rename the datafiles within the database. The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

Renaming a single datafile:

SQL> ALTER TABLESPACE GENESIS_DOCS RENAME DATAFILE 'C:\oradata\GENESIS\datafile\o1_mf_demo_doc_7k38q6lj_.dbf' TO 'D:\oracle\datafile\genesis_docs.dbf';

Renaming multiple datafiles at once:

SQL> ALTER TABLESPACE GENESIS_DOCS

 RENAME DATAFILE 'C:\oradata\GENESIS\datafile\o1_mf_demo_doc_7k38q6lj_.dbf',

 'C:\ORADATA\GENESIS\DATAFILE\DOCS2.DBF',

 'C:\ORADATA\GENESIS\DATAFILE\DOCS3.DBF',

 'C:\ORADATA\GENESIS\DATAFILE\DOCS4.DBF' 

 TO 'D:\oradata\GENESIS\datafile\o1_mf_demo_doc_7k38q6lj_.dbf', 

 'D:\oracledata\genesis\DOCS2.DBF',

 'D:\oracledata\genesis\DOCS3.DBF',

 'D:\oracledata\genesis\DOCS4.DBF';

Step Seven: Bring the tablespace back online and startup Genesis

SQL> ALTER TABLESPACE GENESIS_DOCS ONLINE;

SQL> quit;

C:\> net start "Apache Tomcat"

java.sql.RecoverableException: No more data to read from socket...

If you get a stack trace like the above while writing something to VirtualFiles...

Solution:   Make sure the OJDBC and UCP driver files match the database version.

The above error was produced from running a Pay Stub report in SchoolFi from the Payroll screen.  That screen records the results in VirtualFiles which caused the above stack trace.

Errors during DBUA (From 12.x to >12.x)

Error during prerequisite check:  ORA-29548 classes.bin mismatched in binaries and database

Solution:  Open command window as admin.   Run the datapatch command in the C:\app\oracle\product\12201\dbhome_1\OPatch directory  (or whereever the latest OPatch directory is located on that machine)

Recompile Invalid Objects

log into sqlplus / as sysdba

sql> SQL> @Oracle_home/rdbms/admin/utlrp.sql

SQL> @Oracle_home/rdbms/admin/utlrp.sql