2006. 11. 28. 23:29 아무거나
'분류 전체보기'에 해당되는 글 1016건
- 2006.11.28 중국 출장 다녀왔어용 ~ 1
- 2006.11.16 Oracle Data Mining 삭제하기
- 2006.11.12 G250 을 지르다. 1
- 2006.11.07 Heap size 2294K exceeds notification threshold (2048K)
- 2006.11.06 Asynchronous I/O Does Not Work In 10gr2
- 2006.11.04 Windows2003환경에서 4G 이상 메모리 셋팅하기
- 2006.11.02 full scan 하는 table 찾는 query
- 2006.11.01 conn as sysdba 가 안될때 checklist
- 2006.10.31 발이 아프다 3
- 2006.10.31 Undo tablespace size 줄이기
2006. 11. 16. 23:52 오라클
Oracle Data Mining 삭제하기
Oracle 을 default 로 설치하면 Data mining option 이 설치되는데, 없애고 싶으면 아래와 같이 하도록 한다.
Oracle Data Mining - Version: 9.2.0.0 to 10.1.0.4
Goal
Data Mining is a Licenced Database Option that is generally installed by default.
This note explains how to remove the Oracle Data Mining option from the Database.
===============================================================================================
Solution
The DBA should be able to deinstall Data Mining from the 'Deinstall' icon on the OUI (Oracle Universal Installer) page. This is a supported way.
If OUI does not work for some reasons, you may do the following to deinstall ODM :
1. Start SQLPLUS and connect with user sys as sysdba
2. Remove ODM repositories from the database :
2.1 For Database release 9iR2 (9.2.x).
Drop ODM, ODM_MTR schemas using the command :
SQL> DROP USER ODM_MTR CASCADE;
SQL> DROP USER ODM CASCADE;
2.2 For Database release 10gR1 (10.1.x)
Drop DMSYS schema using the command :
SQL> DROP USER DMSYS CASCADE;
Followed by :
SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS';
3. In a SQLPLUS session, update the DBA registry :
SQL> exec dbms_registry.removed('ODM');
For UNIX Platform the following steps allow to remake Oracle executable without DM libraries
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
The sqlplus banner should no longer display the Data Mining option and v$option should show 'Oracle Data Mining' as false on a Unix system based install.
Please note: It is possible synonyms may exist for Data Mining objects. If any exist, remove them to complete the removal.
2006. 11. 12. 23:31 아무거나
G250 을 지르다.
2006. 11. 7. 08:53 오라클
Heap size 2294K exceeds notification threshold (2048K)
10g R2 로 upgrade 하거나 새로 설치하면 나타나는 증상인데 (SQL문 실행하면 alertSID.log 화일에 계속 기록됨) heap size 기본셋팅 사이즈때문에 나타나는 warning message 이므로 무시하거나, 신경쓰이시는 분들은 아래와 같이 조치하면 됨
================================================================================================
Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
Symptoms
The following messages are reported in alert.log after 10g Release 2 is installed.
Memory Notification: Library Cache Object loaded into SGA
Heap size 2294K exceeds notification threshold (2048K)
Changes
Installed / Upgraded to 10g Release 2
Cause
These are warning messages that should not cause the program
responsible for these errors to fail.
They appear as a result of new event messaging mechanism
and memory manager in 10g Release 2. The meaning is that the
process is just spending a lot of time in finding free memory
extents during an allocate as the memory may be heavily fragmented.
Real memory messages should be ORA-403x when a real memory allocation problem
occurs.
Solution
In 10g we have a new undocumented parameter that sets the KGL heap size
warning threshold. This parameter was not present in 10gR1.
Warnings are written if heap size exceeds this threshold.
Set _kgl_large_heap_warning_threshold to a reasonable high value or zero
to prevent these warning messages. Value needs to be set in bytes.
If you want to set this to 8192 (8192 * 1024) and are using an spfile:
(logged in as "/ as sysdba")
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
SQL> shutdown immediate SQL> startup
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608
If using an "old-style" init parameter,
Edit the init parameter file and add
_kgl_large_heap_warning_threshold=8388608
In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.
References
2006. 11. 6. 23:45 오라클
Asynchronous I/O Does Not Work In 10gr2
주로 Linux plaform 에서 이런현상이 있는거 같더군요.
Applies to:
------------------
Oracle Server - Enterprise Edition - Version: 10.2.0.1.0
Linux Itanium
SUSE \ UnitedLinux x86-64
Red Hat Enterprise Linux Advanced Server x86-64 (AMD Opetron Architecture)
Linux x86-64
Red Hat Enterprise Linux Workstation
Linux x86
Symptoms
------------------
This is Linux Specific Issue. ( RedHat and Suse Linux Mainly )
In Oracle Database 10G you dont need to relink oracle binary with async_on.It comes default with oracle 10G.This note applies in such case wherein async_on is enable at Oracle Database level though Operating System is not able to get the I/O events.
You have set all required parameters for enabling the asynch I/O and finds that asynch I/O is not available from /proc/slabinfo.
SQL> sho parameter disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
SQL> sho parameter filesystemio_options
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL
The Asynchronous I/O does not work.
cat /proc/slabinfo | grep kio ( See the value of kioctx.Its zero )
kioctx 0 0 384 42 1 : tunables 54 27 8 :
slabdata 0 0 0
kiocb 0 0 256 62 1 : tunables 120 60 8 :
slabdata 0 0 0
Cause
---------------
Oracle 10GR2 doesnt requires relinking of oracle binary for async I/O to work but in oracle 10GR2 i found that Oracle is not able to report the I/O activity or event to OS ( in /proc/slabinfo )
As for example.( Please look at the value of kioctx.Its zero )
cat /proc/slabinfo | grep kio
kioctx 0 0 384 42 1 : tunables 54 27 8 : slabdata 0
0 0
kiocb 0 0 256 62 1 : tunables 120 60 8 : slabdata 0
0 0
oracle@tslxprac01:~> cat /proc/sys/fs/aio-nr
0
The reson behind this behavior is that LIBAIO_0.1 Linux OS library is not attached for io_getevents.
So you will have to relink the Oracle 10gr2 with async_on option.Oracle 10gr2 does not come with file called skgaio.o so if you try to relink oracle with async_on , it will fail with following error:
oracle@tslxprac01:/u01/app/oracle/product/10.2.0/db_1/rdbms/lib> make
PL_ORALIBS=-laio -f ins_rdbms.mk async_on ioracle
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o
cp /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o
/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o
cp: cannot stat `/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o': No
such file or directory
make: Following error says that while trying to relinking oracle 10g with async_on it tried to look for
file called skgaio.o and it got failed because the file skgaio.o is not there.
cp: cannot stat `/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o': No such file or directory
Solution
---------------------
To implement the solution, please execute the following steps:
SQL>shutdown immediate
$cd $ORACLE_HOME/rdbms/lib
$ln -s /usr/lib/libaio.so.1 skgaio.o
$make PL_ORALIBS=-laio -f ins_rdbms.mk async_on
SQL>startup
$grep kio /proc/slabinfo
2006. 11. 4. 11:32 오라클
Windows2003환경에서 4G 이상 메모리 셋팅하기
1번은 OS 단에서 enable 해주는 방법이고
2번은 oracle DB 단에서 enable 해주는 방법입니다.
메타링크 문서 번호 [342080.1]
===============================================================
Goal
How to use Very Large Memory, higher than 4Gb on Windows 2003
Solution
We can make use of more than 4 GB of RAM on Windows using the AWE(Advanced Windowing Extentions).
The Steps we need to do are
1- Enabling support at the OS level
AWE can be enabled at the OS by adding the /PAE switch to the boot.ini as such:
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /PAE
2- Enabling AWE Support at the Database/Instance Level:
To enable the AWE implementation on Oracle, you must set the following parameter in the
init file (or spfile) used to start the instance:
USE_INDIRECT_DATA_BUFFERS=TRUE
buffer cache MUST be defined using the parameter DB_BLOCK_BUFFERS
3- In order to take advantage of the additional memory afforded through PAE,the operating system
user account which is used to start the OracleService must be granted the 'Lock Pages in Memory'
system privilege at the operating system level. By default, the OracleService starts as the
LocalSystem account. The LocalSystem account has the privilege to Lock Pages in Memory granted to
it by default.
2006. 11. 2. 15:36 오라클
full scan 하는 table 찾는 query
SQL> column sql_text format A100
SQL> select sid,sql_text
From v$session a, v$sqlarea b
Where a.sql_hash_value = b.hash_value
And sid in
(Select sid From v$session_wait
Where event like 'db file scattered read');
를 수행하거나 아니면 ,,,
SQL> rem
SQL> rem ************************************
SQL> rem * Disk 과다사용하는 SQL
SQL> rem ************************************
SQL> rem
SQL>
col buffer_gets format 999999 heading "BUFFER|GETS"
col disk_reads format 999999 heading "DISK|READ"
col executions format 9999 heading "EXEC|CNT"
col parse_calls format 9999 heading "PARSE|CALLS"
col sql_text format A70
select sql_text,
buffer_gets, disk_reads, executions, parse_calls
from v$sqlarea where disk_reads > 1000000
where buffer_gets > 1000 -----------------> 환경에 맞게 조정
or disk_reads > 100000 -----------------> 환경에 맞게 조정
order by disk_reads ;
2006. 11. 1. 13:08 오라클
conn as sysdba 가 안될때 checklist
connect as sysdba 로 접속했는데 ORA-01031 insufficient privileges 에러가 날때 조치방법을 알아보면,
=============================================================================================
[Metalink 문서번호 69642.1]
Introduction:
~~~~~~~~~~~~~
This bulletin lists the documented causes of getting
---> prompted for a password when trying to CONNECT as SYSDBA
---> errors such as ORA-01031, ORA-01034, ORA-06401, ORA-03113,ORA-09925,
ORA-09817, ORA-12705, ORA-12547
The dbacheck script in Note 67984.1 may help troubleshoot issues of this nature.
a) SQLNET.ORA Checks:
---------------------
1. The "sqlnet.ora" can be found in the following locations (listed by search order):
$TNS_ADMIN/sqlnet.ora
$HOME/sqlnet.ora
$ORACLE_HOME/network/admin/sqlnet.ora
Depending upon your operating system, it may also be located in:
/var/opt/oracle/sqlnet.ora
/etc/sqlnet.ora
A corrupted "sqlnet.ora" file, or one with security options set, will cause
a 'connect internal' request to prompt for a password.
To determine if this is the problem, locate the "sqlnet.ora" that is being used.
The one being used will be the first one found according to the search order
listed above.
Next, move the file so that it will not be found by this search:
% mv sqlnet.ora sqlnet.ora_save
Try to connect internal again.
If it still fails, search for other "sqlnet.ora" files according to the search order listed
above and repeat using the move command until you are sure there are no other
"sqlnet.ora" files being used.
If this does not resolve the issue, use the move command to put all the
"sqlnet.ora" files back where they were before you made the change:
% mv sqlnet.ora_save sqlnet.ora
If moving the "sqlnet.ora" resolves the issue, then verify the contents of the file:
a) SQLNET.AUTHENTICATION_SERVICES
If you are not using database links, comment this line out or try setting it to:
SQLNET.AUTHENTICATION_SERVICES = (BEQ,NONE)
Remark: on certain platforms, this is the default behavior.
b) SQLNET.CRYPTO_SEED
This should not be set in a "sqlnet.ora" file on UNIX.
If it is, comment the line out. (This setting is added to the "sqlnet.ora"
if it is built by one of Oracle's network cofiguration products shipped with client products)
c) AUTOMATIC_IPC
If this is set to "ON" it can force a "TWO_TASK" connection.
Try setting this to "OFF":
AUTOMATIC_IPC = OFF
2. Set the permissions correctly in the "TNS_ADMIN" files.
The environment variable TNS_ADMIN defines the directory where the "sqlnet.ora",
"tnsnames.ora", and "listener.ora" files reside.
These files must contain the correct permissions, which are set when "root.sh" runs
during installation.
As root, run "root.sh" or edit the permissions on the "sqlnet.ora", "tnsnames.ora",
and "listener.ora" files by hand as follows:
$ cd $TNS_ADMIN
$ chmod 644 sqlnet.ora tnsnames.ora listener.ora
$ ls -l sqlnet.ora tnsnames.ora listener.ora
-rw-r--r-- 1 oracle dba 1628 Jul 12 15:25 listener.ora
-rw-r--r-- 1 oracle dba 586 Jun 1 12:07 sqlnet.ora
-rw-r--r-- 1 oracle dba 82274 Jul 12 15:23 tnsnames.ora
b) Software and Operating System Issues:
----------------------------------------
1. Be sure $ORACLE_HOME is set to the correct directory and does not have any
typing mistakes:
% cd $ORACLE_HOME
% pwd
If this returns a location other than your "ORACLE_HOME" or is invalid, you
will need to reset the value of this environment variable:
sh or ksh:
----------
$ ORACLE_HOME=<path_to_ORACLE_HOME>
$ export ORACLE_HOME
Example:
$ ORACLE_HOME=/u01/app/oracle/product/7.3.3
$ export ORACLE_HOME
csh:
----
% setenv ORACLE_HOME <path_to_ORACLE_HOME>
Example:
% setenv ORACLE_HOME /u01/app/oracle/product/7.3.3
If your "ORACLE_HOME" contains a link or the instance was started with the
"ORACLE_HOME" set to another value, the instance may try to start using the
memory location that another instance is using.
An example of this might be:
You have "ORACLE_HOME" set to "/u01/app/oracle/product/7.3.3" and start the
instance.
Then you do something like:
% ln -s /u01/app/oracle/product/7.3.3 /u01/app/oracle/7.3.3
% setenv ORACLE_HOME /u01/app/oracle/7.3.3
% svrmgrl
SVRMGR> connect internal
If this prompts for a password then most likely the combination of your
"ORACLE_HOME" and "ORACLE_SID" hash to the same shared memory address of
another running instance. Otherwise you may be able to connect internal
but you will receive an ORA-01034 "Oracle not available" error.
In most cases using a link as part of your "ORACLE_HOME" is fine as long as
you are consistent.
Oracle recommends that links not be used as part of the "ORACLE_HOME", but
their use is supported.
2. Check that $ORACLE_SID is set to the correct SID, (including capitalization),
and does not have any typos:
% echo $ORACLE_SID
Refer to Note 1048876.6 for more information.
3. Ensure $TWO_TASK is not set.
To check if "TWO_TASK" is set, do the following:
sh, ksh or on HP/UX only csh:
-----------------------------
env |grep -i two
- or -
echo $TWO_TASK
csh:
----
setenv |grep -i two
If any lines are returned such as:
TWO_TASK=
- or -
TWO_TASK=PROD
You will need to unset the environment variable "TWO_TASK":
sh or ksh:
----------
unset TWO_TASK
csh:
----
unsetenv TWO_TASK
Example :
$ TWO_TASK=V817
$ export TWO_TASK
$ sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Dec 31 10:12:25 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
$ unset TWO_TASK
$ sqlplus /nolog
SQL> conn / as sysdba
Connected.
If you are running Oracle release 8.0.4, and upon starting "svrmgrl" you
receive an ORA-06401 "NETCMN: invalid driver designator" error, you should
also unset two_task.
The login connect string may be getting its value from the TWO_TASK
environment variable if this is set for the user.
4. Check the permissions on the Oracle executable:
% cd $ORACLE_HOME/bin
% ls -l oracle ('ls -n oracle' should work as well)
The permissions should be rwsr-s--x, or 6751.
If the permissions are incorrect, do the following as the "oracle"
software owner:
% chmod 6751 oracle
If you receive an ORA-03113 "end-of-file on communication" error followed
by a prompt for a password, then you may also need to check the ownership
and permissions on the dump directories.
These directories must belong to Oracle, group dba, (or the appropriates names
for your installation).
This error may occur while creating a database.
Permissions should be: 755 (drwxr-xr-x)
Also, the alert.log must not be greater than 2 Gigabytes in size.
When you start up "nomount" an Oracle pseudo process will try to write the
"alert.log" file in "udump".
When Oracle cannot do this (either because of permissions or because of the
"alert.log" being greater than 2 Gigabytes in size), it will issue the
ORA-03113 error.
5. "osdba" group checks:
a. Make sure the operating system user issuing the CONNECT INTERNAL belongs
to the "osdba" group as defined in the "$ORACLE_HOME/rdbms/lib/config.s"
or "$ORACLE_HOME/rdbms/lib/config.c". Typically this is set to "dba".
To verify the operating system groups the user belongs to, do the following:
% id
uid=1030(oracle) gid=1030(dba)
The "gid" here is "dba" so the "config.s" or "config.c" may contain an
entry such as:
/* 0x0008 15 */ .ascii "dba\0"
If these do not match, you either need to add the operating system user
to the group as it is seen in the "config" file, or modify the "config"
file and relink the "oracle" binary.
Refer to entry Note 50507.1 section 3 for more details.
b. Be sure you are not logged in as the "root" user and that the environment
variables "USER", "USERNAME", and "LOGNAME" are not set to "root".
The "root" user is a special case and cannot connect to Oracle as the
"internal" user unless the effective group is changed to the "osdba" group,
which is typically "dba".
To do this, either modify the "/etc/password" file (not recommended) or
use the "newgrp" command:
# newgrp dba
"newgrp" always opens a new shell, so you cannot issue "newgrp" from
within a shell script.
Keep this in mind if you plan on executing scripts as the "root" user.
c. Verify that the "osdba" group is only listed once in the "/etc/group" file:
% grep dba /etc/group
dba::1010:
dba::1100:
If more than one line starting with the "osdba" group is returned, you
need to remove the ones that are not correct.
It is not possible to have more than one group use a group name.
d. Check that the oracle user uid and gid are matching with /etc/passwd and
/etc/group :
$ id
uid=500(oracle) gid=235(dba)
$ grep oracle /etc/passwd
oracle:x:500:235:oracle:/home/oracle:/bin/bash
^^^
$ grep dba /etc/group
dba:x:253:oracle
^^^
The mismatch also causes an ORA-1031 error.
6. Verify that the file system is not mounted no set uid:
% mount
/u07 on /dev/md/dsk/d7 nosuid/read/write
If the filesytem is mounted "nosuid", as seen in this example, you will need
to unmount the filesystem and mount it without the "nosuid" option.
Consult your operating system documentation or your operating system vendor
for instruction on modifying mount options.
7. Please read the following warning before you attempt to use the information
in this step:
******************************************************************
* *
* WARNING: If you remove segments that belong to a running *
* instance you will crash the instance, and this may *
* cause database corruption. *
* *
* Please call Oracle Support Services for assistance *
* if you have any doubts about removing shared memory *
* segments. *
* *
******************************************************************
If an instance crashed or was killed off using "kill" there may be shared
memory segments hanging around that belong to the down instance.
If there are no other instances running on the machine you can issue:
% ipcs -b
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 0 0x50000ffe --rw-r--r-- root root 68
m 1601 0x0eedcdb8 --rw-r----- oracle dba 4530176
In this case the "ID" of "1601" is owned by "oracle" and if there are no
other instances running in most cases this can safely be removed:
% ipcrm -m 1601
If your SGA is split into multiple segments you will have to remove all
segments associated with the instance. If there are other instances
running, and you are not sure which memory segments belong to the failed
instance, you can do the following:
a. Shut down all the instances on the machine and remove whatever shared
memory still exists that is owned by the software owner.
b. Reboot the machine.
c. If your Oracle software is release 7.3.3 or newer, you can connect into
each instance that is up and identify the shared memory owned by that
instance:
% svrmgrl
SVRMGR> connect internal
SVRMGR> oradebug ipc
In Oracle8:
-----------
Area #0 `Fixed Size', containing Subareas 0-0
Total size 000000000000b8c0, Minimum Subarea size 00000000
Subarea Shmid Size Stable Addr
0 7205 000000000000c000 80000000
In Oracle7:
-----------
-------------- Shared memory --------------
Seg Id Address Size
2016 80000000 4308992
Total: # of segments = 1, size = 4308992
Note the "Shmid" for Oracle8 and "Seg Id" for Oracle7 for each running instance.
By process of elimination find the segments that do not belong to an
instance and remove them.
8. If you are prompted for a password and then receive error ORA-09925 "unable
to create audit trail file" or error ORA-09817 "write to audit file failed",
along with "SVR4 Error: 28: No space left on device", do the following:
Check your "pfile". It is typically in the "$ORACLE_HOME/dbs" directory
and will be named "init<your_sid>.ora, where "<your_sid>" is the value of
"ORACLE_SID" in your environment. If the "init<your_sid>.ora" file has
the "ifile" parameter set, you will also have to check the included file
as well. You are looking for the parameter "audit_file_dest".
If "audit_file_dest" is set, change to that directory; otherwise change to
the "$ORACLE_HOME/rdbms/audit" directory, as this is the default location
for audit files. If the directory does not exist, create it.
Ensure that you have enough space to create the audit file.
The audit file is generally 600 bytes in size.
If it does exist, verify you can write to the directory:
% touch afile
If it could not create the called "afile", you need to change the permissions
on your audit directory:
% chmod 751
9. If connect internal prompts you for a password and then you receive an
ORA-12705 "invalid or unknown NLS parameter value specified" error, you
need to verify the settings for "ORA_NLS", "ORA_NLS32", "ORA_NLS33" or
"NLS_LANG".
You will need to consult your Installation and Configuration Guide for the
proper settings for these environment variables.
10. If you have installed Oracle software and are trying to connect with
Server Manager to create or start the database, and receive a TNS-12571
"packet writer failure" error, please refer to Note 1064635.6
11. If in SVRMGRL (Server Manager line mode), you are running the "startup.sql"
script and receive the following error:
ld:so.1: oracle_home/bin/svrmgrl fatal relocation error
symbol not found kgffiop
RDBMS v7.3.2 is installed.
RDBMS v8.0.4 is a separate "oracle_home", and you are attempting to have
it coexist.
This is due to the wrong version of the client shared library "libclntsh.so.1"
being used at runtime.
Verify environment variable settings.
You need to ensure that "ORACLE_HOME" and "LD_LIBRARY_PATH" are set correctly.
For C-shell, type:
% setenv LD_LIBRARY_PATH $ORACLE_HOME/lib
% setenv ORACLE_HOME /u01/app/oracle/product/8.0.4
For Bourne or Korn shell, type:
$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_LIBRARY_PATH
$ ORACLE_HOME=/u01/app/oracle/product/8.0.4
$ export ORACLE_HOME
12. Ensure that the disk the instance resides on has not reached 100% capacity.
% df -k
If it has reached 100% capacity, this may be the cause of 'connect internal'
prompting for a password.
Additional disk space will need to be made available before 'connect internal'
will work.
For additional information refer to Note 97849.1
13. Delete process.dat and regid.dat files in $ORACLE_HOME/otrace/admin directory.
Oracle Trace is enabled by default on 7.3.2 and 7.3.3 (depends on platform)
This can caused high disk space usage by these files and cause a number of
apparently mysterious side effects.
See Note 45482.1 for more details.
14. When you get ora-1031 "Insufficient privileges" on connect internal after you
supply a valid password and you have multiple instances running from the same
ORACLE_HOME, be sure that if an instance has REMOTE_LOGIN_PASSWORDFILE set to
exclusive that the file $ORACLE_HOME/dbs/orapw<sid> does exist, otherwise it
defaults to the use of the file orapw that consequently causes access problems
for any other database that has the parameter set to shared.
Set the parameter REMOTE_LOGIN_PASSWORDFILE to shared for all instances that share
the common password file and create an exclusive orapw<sid> password files for any
instances that have this set to exclusive.
15. Check permissions on /etc/passwd file (Unix only).
If Oracle cannot open the password file, connect internal fails with
ORA-1031, since Oracle is not able to verify if the user trying to connect
is indeed in the dba group.
Example:
--------
# chmod 711 /etc/passwd
# ls -ltr passwd
-rwx--x--x 1 root sys 901 Sep 21 14:26 passwd
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 21 16:21:18 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Trussing sqlplus will show also the problem:
25338: munmap(0xFF210000, 8192) = 0
25338: lwp_mutex_wakeup(0xFF3E0778) = 0
25338: lwp_mutex_lock(0xFF3E0778) = 0
25338: time() = 1032582594
25338: open("/etc/passwd", O_RDONLY) Err#13 EACCES
25338: getrlimit(RLIMIT_NOFILE, 0xFFBE8B28) = 0
c) Operating System Specific checks:
------------------------------------
1. On OpenVMS, check that the privileges have been granted at the Operating System
level:
$ SET DEFAULT SYS$SYSTEM:
$ RUN AUTHORIZE
If the list returned by AUTHORIZE does not contain ORA_<SID>_DBA, or ORA_DBA,
then you do not have the correct OS privileges to issue a connect internal.
If ORA_<SID>_DBA was added AFTER ORA_DBA, then ORA_DBA needs to be removed
and granted again to be updated.
Please refer to Note 1010852.6 for more details.
2. On Windows NT, check if DBA_AUTHORIZATION is set to BYPASS in the registry.
3. On Windows NT, if you are able to connect internally but then startup fails
for some reason, successive connect internal attempts might prompt for a
password. You may also receive errors such as:
ORA-12705: invalid or unknown NLS parameter value specified
ORA-01012: not logged on
LCC-00161: Oracle error (possible syntax error)
ORA-01031: insufficient privileges
Refer to entry Note 1027964.6 for suggestions on how to resolve this problem
4. If you are using Multi-Threaded Server (MTS), make sure you are using a dedicated
server connection.
A dedicated server connection is required to start up or shutdown the database.
Unless the database alias in the "TNSNAMES.ORA" file includes a parameter to make
a dedicated server connection, it will make a shared connection to a dispatcher.
See Note 1058680.6 for more details.
5. On Solaris, if the file "/etc/.name_service_door" has incorrect permissions,
Oracle cannot read the file. You will receive a message that "The Oracle
user cannot access "/etc/.name_service_door" (permission denied).
This file is a flavor of IPC specific to Solaris which Oracle software is using
This can also cause connect internal problems. See entry Note 1066589.6
6. You are on Digital Unix, running SVRMGRL (Server Manager line mode), and you
receive an ORA-12547 "TNS:lost contact" error and a password prompt.
This problem occurs when using Parallel Server and the True Cluster software together.
If Parallel Server is not linked in, svrmgrl works as expected.
Oracle V8.0.5 requires an Operating System patch which previous versions of
Oracle did not require.
The above patch allows svrmgrl to communicate with the TCR software.
You can determine if the patch is applied by running:
% nm /usr/ccs/lib/libssn.a | grep adjust
If this returns nothing, then you need to:
1. Obtain the patch for TCR 1.5 from Digital.
This patch is for the MC SCN and adds the symbol "adjustSequenceNumber"
to the library /usr/ccs/lib/libssn.a.
2. Apply the patch.
3. Relink Oracle
Another possibility is that you need to raise the value of kernel parameter
per-proc-stack-size
when increased from its default value of 2097152 to 83886080 resolved this problem.
7. You are on version 6.2 of the Silicon Graphics UNIX (IRIX) operating system
and you have recently installed RDBMS release 8.0.3.
If you are logged on as "oracle/dba" and an attempt to log in to Server Manager
using "connect/internal" prompts you for a password, you should refer to entry
Note 1040607.6
8. On AIX 4.3.3 after applying ML5 or higher you can not longer connect as internal
or if on 9.X '/ as sysdba' does not work as well.
This is a known AIX bug and it occurs on all RS6000 ports including SP2.
There is two workarounds and one solution. They are as follows:
1) Use mkpasswd command to remove the index.
This is valid until a new user is added to "/etc/passwd" or modified:
# mkpasswd -v -d
2) Touch the "/etc/passwd" file.
If the "/etc/passwd" file is newer than the index it will not use the
password file index:
# touch /etc/passwd
3) Obtain APAR IY22458 from IBM.
Any questions about this APAR should be directed to IBM.
d) Additional Information:
--------------------------
1. In the "Oracle7 Administrator's Reference for UNIX", there is a note that states:
If REMOTE_OS_AUTHENT is set to true, users who are members of the dba group
on the remote machine are able to connect as INTERNAL without a password.
However, if you are connecting remotely, that is connecting via anything
except the bequeath adapter, you will be prompted for a password regardless
of the value of "REMOTE_OS_AUTHENT".
Refer to Bug 644988
2006. 10. 31. 00:02 오라클
Undo tablespace size 줄이기
* Goal
-----------------------------------------------------------------------------------------------------
Your production database has bi-annual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on file system.
The purging process is run only few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don't want to buy additional disks unnecessarily.
You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA 1651 : unable to extend save undo segment by <num> in tablespace <name>.
You have tried "alter database datafile .. resize" which always fails with Error: ORA 3297 : file contains <num> blocks of data beyond requested RESIZE value.
You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.
* Solution
-----------------------------------------------------------------------------------------------------
-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;
-- Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;
-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.
NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.
Points to Consider:
- The value for undo_retention also has a role in growth of undo tablespace. If there is no way to get the undo space for a new transaction, then the undo space (retention) will be reused. But, if the datafiles for undo tablespace are set to auto extensible, it will not reuse the space. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.
- Is big really bad? Overhead on larger file/tablespaces can theoretically impact the database and the OS. With a small file, the OS would have to do minimal I/O. Oracle would be able to cache the whole file and there would be less segments to manage. With AUM you get bitmapped files and all its (space management) performance benefits-- (number of) undo segments are automatically managed and are not related to the size of the tablespace. With the bigger file/tablespace you will have other overhead--e.g. backup will take longer--but as far as the undo management there should be no performance impact just because the file/tbs is bigger. That said, it is important to monitor systems (e.g. with statspack) and watch for environment-specific issues.
ReferencesNote 135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
ErrorsORA-30013 undo tablespace '%s' is currently in use