Oracle Common failure Issues

1. Ora-27102:out of Memory

To create a pfile file:

Create Pfile from SPFile;

Modify the Pfile file

Modify File/home/oracle/app/oracle/product/10g/dbs/initradius.ora

PGA,SGA size

Use Pfile to start a database

Startup pfile= '/home/oracle/app/oracle/product/10g/dbs/initradius.ora ';

Synchronize pfile files from SPFile,

Create SPFile from Pfile;

Then switch to the root user to restart the database

2. Unable to use the upper and lower left buttons

Yum-y Install Readline-devel ncurses-devel libtool*

Download Unzip installation

Http://down1.chinaunix.net/distfiles/rlwrap-0.24.tar.gz

./configure make && make install

Vim/etc/profile

Aliase sqlplus= ' Rlwrap sqlplus '

3. Packaging

TAR-JVCF oracle_mydradius.tar.bz2/etc/ora*/etc/sysctl.conf/etc/security/limits.conf/etc/pam.d/login/etc/passwd/ etc/shadow/etc/group/etc/profile/home/oracle//etc/init.d/ora10g/ora/etc/hosts/usr/local/rlwrap

4.CPU High-Utilization Query method

PID 735

Sql> Select S.sid from v$process p,v$session s where s.paddr=p.addr and p.spid = 735;

Sid

----------

149

Select Sql_text from v$sqltext_with_newlines where Hash_value = (select Sql_hash_value from v$session where SID = 149);

Sql> Select Sql_text from v$sqltext_with_newlines where Hash_value = (SELECT sql_hash_value from v$session where SID = 149);

Sql_text

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

Timebyuseragent (O.scalledno) = 1

is not NULL and dexpiredate <= sysdate))) and Pkg_util.hitfree

Sysdate and (ibilltype = 0 or Ibilltype = 7)) OR (dexpiredate

Ain, Icurrflow) = 0) OR (doverdate is not NULL and Doverdate <=

Er.sp_flowuserflowremain (sUserName, Ibilltype, Iispid, Fmoneyrem

PID, Icurrflow) = 0) OR (ibilltype = 2 and IType!=3 and Pkg_us

Pkg_user.sp_userflowremain (sUserName, Dfirstdate, Ibilltype, IIs

Ctets + o.foutputmoctets) OR (ibilltype = 0 and IType!=3 and

OR (Ibilltype = 5 and IType!=3 and Fmoneyremain <= O.finputmo

The Users WHERE ((istatus <= 0 and Istatus! =-4)

Linerecord o WHERE susername in (SELECT sUserName from Tbl_

Sql_text

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

MeDIP, Pkg_util.iip_to_aip (Iframedip) as Aframedip from tbl_on

INASIP) as Cnasip, Pkg_util.iip_to_cip (IFRAMEDIP) as Cfra

SELECT Inasport, sUserName, Ssessionid, Pkg_util.iip_to_cip (

Rows selected.

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

can be used directly

Select Sql_text from V$sqltext a WHERE (A.hash_value, a.address) in (select Decode (sql_hash_value, 0, Prev_hash_value, SQL _hash_value), decode (sql_hash_value, 0, Prev_sql_addr, sql_address) from v$session b WHERE b.paddr = (SELECT addr from v$p Rocess c WHERE c.spid = ' 3127 ')) ORDER by piece ASC;

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

5.11G Password Expiration question

Oracle 11G needs to be modified

Set Password never expires

ORACLE11G R2 database hint ora-28002:the password would expire within 5 days,

SELECT * from Dba_profiles s WHERE s.profile= ' DEFAULT ' and resource_name= ' password_life_time '; View Validity period

ALTER profile DEFAULT LIMIT password_life_time UNLIMITED; Set Password never expires

Alter user joffice identified by Joffice; Change Password

6. Unable to delete a user

1. Unable to delete a user

Ora-24170:mydradius. Primary_queue_r is created by AQ, cannot be dropped

Method:

ALTER SESSION SET EVENTS = ' 25475 TRACE NAME CONTEXT FOREVER, Level 2 ';

Drop user Mydradius cascade;

Ora-00604:error occurred at recursive SQL level 1

Method:

Alter session SET Events ' 10851 Trace name context Forever,level 1 ';

7. Modifying the character Set method

View the character set of Oracle

Select Userenv (' language ') from dual;

Results: AMERICAN _ AMERICA. Zhs16gbk

To modify a character set:

Shutdown immediate;

startup Mount;

Alter system enable restricted session;

alter system set job_queue_processes=0;

ALTER DATABASE open;

ALTER DATABASE character set Internal_use us7ascii;

ALTER DATABASE character set Internal_use ZHS16GBK;

Shutdown immediate;

Startup

8. See Utf-8 garbled

nls_lang= "Simplified Chinese_china.utf8"; Export Nls_lang

Add a secondary environment variable

9. View the number of connections and sessions and modify

Select COUNT (*), program, Machine,osuser from V$session Group by Program,machine,osuser have Count (*) >1;

Current number of connections for each client

Select COUNT (0) from v$process;

Current number of connections

Select COUNT (*) from v$session;

Current number of sessions

Select value from V$parameter where name= ' processes ';

Show parameter process

Maximum number of connections set by the system

Show parameter session

Maximum number of sessions set by the system

Alter system set PROCESSES=500 Scope=spfile;

To modify the maximum number of connections

Alter system set SESSIONS=500 Scope=spfile;

To modify the maximum number of sessions

Connections and session modifications must be restarted to take effect, their relationship sessions= (1.1 * processes + 5)

10. Modifications can use memory SGA and link using memory PGA

Sql> Show parameter Sga_//view memory allocation

NAME TYPE VALUE

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

Sga_max_size Big Integer 1120M

Sga_target Big Integer 1120M

Alter system set SGA_MAX_SIZE=1024M Scope=spfile;

Alter system set SGA_TARGET=1024M Scope=spfile; cannot be dynamically modified, requires restart of Oracle

11.Oracle using SQL file to import data garbled

Export Nls_lang=american_america. Zhs16gbk

Add environment variable/etc/profile

Oracle Common failure Issues