1주일간 중국 출장 갔다 왔어용. 너무나도 빡셌던 이번 출장 ~ 하루정도 상해 구경하고 나머지는 계속 일만 T.T
피곤하니깐 사진은 차차..후 또 적응 안되겠군.
Posted by pat98


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.


Posted by pat98

2006. 11. 12. 23:31 아무거나

G250 을 지르다.


아~ 기타 치고 싶어서 콜트 G250 을 지르긴 질렀는데. 이건 대체 만져볼 시간도 없다. 물론 실력도 없지만.
Neil Zaza 의 I'm alright 을 멋지게 치는 날까지 ~~~


Posted by pat98

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

Posted by pat98

주로 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

Posted by pat98

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.

Posted by pat98

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 ;

Posted by pat98


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

Posted by pat98

2006. 10. 31. 00:21 아무거나

발이 아프다



갑자기 발이 쑤셔서 걸을 수가 없다. 병원에 가서 침 맞고 뜸도 뜨고 별거 다했는데 소용이 없다.
의사는 피로골절일거 같다고 겁을 주는데..뭐야..환자겁이나 주고.
아무튼 몸이 아프니깐 신경질이 그냥 난다. 빨리 나아야 할텐데. 컨디션 0
Posted by pat98

* 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

Posted by pat98

12-21 00:06
Flag Counter
Yesterday
Today
Total

글 보관함

최근에 올라온 글

달력

 « |  » 2024.12
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

최근에 달린 댓글