'오라클' 카테고리의 글 목록 (46 Page)

'오라클'에 해당되는 글 473건

2007.01.22 ORA-00600 [kkqcby: qbcfkkc not zero] After Applying 10.2.0.2
2007.01.22 How install Partitioning after Patchset Installation
2007.01.04 oracle relink하기 2
2007.01.02 controlfile 재생성하기
2006.12.28 ORA-00257
2006.12.27 Manual Migration of Oracle 8.1.x to 8.1.7
2006.12.21 Oracle profile 기능
2006.12.12 CRS and 10g Real Application Clusters
2006.12.05 Oracle Created Database Users: Password, Usage and Files
2006.11.16 Oracle Data Mining 삭제하기

2007. 1. 22. 09:18 오라클

ORA-00600 [kkqcby: qbcfkkc not zero] After Applying 10.2.0.2

Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 10.2.0.2
This problem can occur on any platform.
SymptomsThe following error is consistently appearing in the alert.log:

ORA-00600: internal error code, arguments: [kkqcby: qbcfkkc not zero], [], [], [], [], [], [], []

The Call Stack trace will include: kkqcbyCountJoinKeys kkqcbydrv qksqbApplyToQbcLoc
ChangesPatchset 10.2.0.2 has been applied.

Cause
This problem is due to unpublished Bug 4639977.

Solution
This bug fixed in 10.2.0.3 and 11i.
1. A one off patch exists for some 10.2.0.2 platforms on MetaLink:
Patch 4639977

-or-

2. The Workaround is to set the following parameter:
_optimizer_connect_by_cost_based = false

Posted by pat98

2007. 1. 22. 09:00 오라클

How install Partitioning after Patchset Installation

이미 패치셋이 적용되었는데, 뒤늦게 Partitoning 기능을 추가해서 사용해야 한다면?

다음과 같이 하면 된다. runInstaller 를 띄워서 custum option 에서 Partitioning 기능을 추가해주고 9.2.0.7 patchset 띄워서 다시 patch 를 New install 해주면 된다. 당연히 catproc.sql, catalog.sql은 한번씩 돌려줘야 겠지.

Goal
When the software in ORACLE_HOME was installed partition option was not included.
The 9.2.0.6 patchset has been installed to this ORACLE_HOME.
Now you would lilke to install the partition option at this time

Solution
You can install the Oracle Partitioning option by invoking the runInstaller from the 9.2.0.1
software kit and then select the existing Oracle Home to which you have applied the 9.2.0.7
patchset and then go for the 'Custom Install' option. In the custom install option you will be
able to select the Oracle Partitioning option and then proceed to install the partitioning option.
After installing the partitioning option from the 9.2.0.1 base software kit, invoke the installer from the 9207 patchset and then select the same Oracle Home as before and if you scroll down and see the list of components that will be installed, you will see "installed" for all the components that are already installed and patched to 9207. Whereas if you expand 'Oracle Partitioning' you will see "installed" for 9201 and "new install" for 9207 patch, just proceed and click on install and wait for the installation to complete.

The same procedure holds good for 10gR1 and 10gR2 also.

Posted by pat98

2007. 1. 4. 11:08 오라클

oracle relink하기

OS patch를 했거나, oracle engine 을 다른 서버로 통째로 copy 했을 경우 등에
relink 명령을 해주는데 이에 대한 설명이다.
============================================================

Relinking occurs automatically under these circumstances

An Oracle product has been installed with an Oracle provided installer.
An Oracle patch set has been applied via an Oracle provided installer.

Relinking Oracle manually is suggested under these circumstances

An OS upgrade has occurred.
A change has been made to the OS system libraries. This can occur during the application of an OS patch.
A new install failed during the relinking phase.
Individual Oracle executables core dump during initial startup.
An individual Oracle patch has been applied with explicit relink instructions or the relink is integrated into the patch install script.

Steps to manually relink Oracle

Logon and set environment variables:

as oracle:
source $csetsid
env | grep ORA
Verify and/or Configure the Unix Environment for Proper Relinking:
Sun Solaris Oracle 7.3.x, 8.0.x, 8.1.x or 9.0.x:
- Ensure that /usr/ccs/bin is before /usr/ucb in $PATH:
  
  which ld
  
  ( should return "/usr/ccs/bin/ld" )
- Set LD_LIBRARY_PATH to include $ORACLE_HOME/lib
- If using 64bit Oracle, LD_LIBRARY_PATH should also include $ORACLE_HOME/lib64.
Verify the correct absolute path for $ORACLE_HOME

env | grep ORACLE_HOME
Before relinking Oracle, shutdown all the databases and the listener.
Run the OS Commands to Relink Oracle:
- Oracle 7.3.x
  - For executables: oracle, exp, imp, sqlldr, tkprof
    
    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk install
  - For executables: svrmgrl, svrmgrm
    
    cd $ORACLE_HOME/svrmgr/lib
    make -f ins_svrmgr.mk linstall minstall
    linstall is for svrmgrl, minstall is for svrmgrm
  - For executables: sqlplus
    
    cd $ORACLE_HOME/sqlplus/lib
    make -f ins_sqlplus.mk install
  - For executables: dbsnmp, oemevent, oratclsh
    
    cd $ORACLE_HOME/network/lib
    make -f ins_agent.mk install
  - For executables: names, namesctl
    
    cd $ORACLE_HOME/network/lib
    make -f ins_names.mk install
  - For executables: tnslsnr, lsnrctl, tnsping, csmnl, trceval, trcroute
    
    cd $ORACLE_HOME/network/lib
    make -f ins_network.mk install
- Oracle 8.0.x
  - For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman, svrmgrl, ogms, ogmsctl
    
    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk install
  - For executables: sqlplus
    
    cd $ORACLE_HOME/sqlplus/lib
    make -f ins_sqlplus.mk install
  - For executables: dbsnmp, oemevent, oratclsh, libosm.so
    
    cd $ORACLE_HOME/network/lib
    make -f ins_oemagent.mk install
  - For executables: tnslsnr, lsnrctl, namesctl, names, osslogin, trcasst, trcroute
    
    cd $ORACLE_HOME/network/lib
    make -f ins_network.mk install
- Oracle 8.1.x or 9.0.xA "relink" script ( 8i new feature ) is provided in the $ORACLE_HOME/bin directory:-or-
  - For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman, svrmgrl, ogms, ogmsctl
    
    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk install
  - For executables: sqlplus
    
    cd $ORACLE_HOME/sqlplus/lib
    make -f ins_sqlplus.mk install
  - For executables: dbsnmp, oemevent, oratclsh
    
    cd $ORACLE_HOME/network/lib
    make -f ins_oemagent.mk install
  - For executables: names, namesctl
    
    cd $ORACLE_HOME/network/lib
    make -f ins_names.mk install
  - For executables: osslogin, trcasst, trcroute, onrsd, tnsping
    
    cd $ORACLE_HOME/network/lib
    make -f ins_net_client.mk install
  - For executables: tnslsnr, lsnrctl
    
    cd $ORACLE_HOME/network/lib
    make -f ins_net_server.mk install
- You still have the option of running the "make" commands independently:
- cd $ORACLE_HOME/bin
  relink
  
  usage: relink >parameter<
  accepted values for parameter: all, oracle, network, client, client_sharedlib,
  interMedia, precomp, utilities, oemagent
  
  i.e. You can relink ALL executables with the following command: relink all
  bug 1337908 : on Solaris with Oracle 8.1.6, also do: relink utilities
How to tell if relinking was successful:

If relinking was successful, the make command will eventually return to the OS prompt without an error. There will not be a "Relinking Successful" type message.

If you receive an error message during relinking:

Relinking errors usually terminate the relinking process and contain verbage similar to the following:
Fatal error', 'Ld: fatal', 'Exit Code 1

Posted by pat98

2007. 1. 2. 22:17 오라클

controlfile 재생성하기

controlfile 재생성하기

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:
--------

You should only need to recreate your control file under very special
circumstances:

- All current copies of the control file have been lost or are corrupted.

- You need to change a "hard" database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

- You are restoring a backup in which the control file is corrupted or
missing.

- Oracle Customer Support advises you to do so.

- If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

Instructions:
=============

I. CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE:
--------------------------------------------------------------

1. If you are running Oracle7 or higher you can get Oracle to generate
a script for you that enables you to recreate the controlfile. Run the
following command while the database is mounted or open and connected
as a user with DBA privileges:

   % svrmgrl
   SVRMGR> connect internal
   SVRMGR> startup mount
   SVRMGR> alter database backup controlfile to trace;

   If you are running Oracle9i or higher you need to use sqlplus instead of
   svrmgrl.

   Oracle6 does not have this feature and therefore you will need to build
   the CREATE CONTROLFILE statement yourself. The syntax is discussed in
   detail in the Oracle SQL Reference Guide.

2. The trace file will be stored in the USER_DUMP_DEST destination,
   which is set to "$ORACLE_HOME/rdbms/log" by default on Unix platforms.

   To find out what USER_DUMP_DEST is set to, follow one of the following:

   a) Look in the parameter file (init<SID>.ora on UNIX and Windows NT,
<node>_<ora_sid>_init.ora on VMS) for the parameter:

   USER_DUMP_DEST = d:/oradata/orcl/trce/udump

   b) Using SQL*PLus you can issue the following command:

SQL> SELECT value
2> FROM v$parameter
3> WHERE    name = 'user_dump_dest';

VALUE
------------------------------------------------
d:/oradata/orcl/trace/udump

   c) Using Server Manager you can issue the following command:

   SVRMGR> show parameter <string>
   SVRMGR> show parameter user_dump_dest;

   The easiest way to locate the correct trace is to look at its date.
   A file will exist with the current date and time. The naming
   convention for these files is operating system specific.

   Example:
   --------

   % cd $ORACLE_HOME/rdbms/log
   % ls -l
   -rw-r--r-- 1 osupport dba 2315 Oct 3 16:39 alert_p716.log
   -rw-r--r-- 1 osupport dba 1827 Oct3 16:39 p716_ora_26220.trc

   In this example, the file "p716_ora_26220.trc" is the trace file
   produced that contains a script to create the control file.

   NOTE: The trace file is handled a bit differently when issuing this
   command from a connection to the database using shared server. The
   shared server connection is created by PMON and the connection inherits
   its environment, meaning the trace file will be created in the directory
   referenced by the initialization parameter BACKGROUND_DUMP_DEST
   instead of the USER_DUMP_DEST.

   Use similar commands as given above to locate the directory
   referenced in the BACKGROUND_DUMP_DEST.

3. Modify the trace file and use it as a script to create the control
   file. Copy the trace file to a script file, such as "new_control.sql",
   delete the header information prior to the words STARTUP NOMOUNT,
   and make any other desired changes, such as increasing MAXDATAFILES,
   MAXLOGFILES, etc.

   Sample:
   -------------------------- <start trace> -----------------------------
   Dump file /u01/oracle/7.1.6/rdbms/log/p716_ora_26220.trc
   Oracle7 Server Release 7.1.6.2.0 - Production Release
   With the distributed and replication options
   PL/SQL Release 2.1.6.2.0 - Production
   ORACLE_HOME = /u01/oracle/7.1.6
   ORACLE_SID = p716
   Oracle process number: 9 Unix process id: 26220
   System name:    SunOS
   Node name:    tcsun2
   Release:    5.4
   Version:    Generic_101945-27
   Machine: sun4m

   Tue Oct 3 16:39:13 1995
   *** SESSION ID:(6.61)
   # The following commands will create a new control file and use it
   # to open the database.
   # No data other than log history will be lost. Additional logs may
   # be required for media recovery of offline data files. Use this
   # only if the current version of all online logs are available.
   STARTUP NOMOUNT

   CREATE CONTROLFILE REUSE DATABASE "P716" NORESETLOGS NOARCHIVELOG
   MAXLOGFILES 32
   MAXLOGMEMBERS 2
   MAXDATAFILES 30
   MAXINSTANCES 8
   MAXLOGHISTORY 800
   LOGFILE
   GROUP 1 '/u01/oracle/7.1.6/dbs/log1p716.dbf' SIZE 500K,
   GROUP 2 '/u01/oracle/7.1.6/dbs/log2p716.dbf' SIZE 500K,
   GROUP 3 '/u01/oracle/7.1.6/dbs/log3p716.dbf' SIZE 500K
   DATAFILE
   '/u01/oracle/7.1.6/dbs/systp716.dbf' SIZE 40M,
   '/u01/oracle/7.1.6/dbs/tempp716.dbf' SIZE 550K,
   '/u01/oracle/7.1.6/dbs/toolp716.dbf' SIZE 15M
   ;
   # Recovery is required if any of the datafiles are restored backups,
   # or if the last shutdown was not normal or immediate.
   RECOVER DATABASE
   # Database can now be opened normally.
   ALTER DATABASE OPEN;

   ---------------------- <end trace> ----------------------------------

4. Shutdown the database (NORMAL, IMMEDIATE, TRANSACTIONAL (Oracle8 only)
   but not ABORT).

   SVRMGR> shutdown immediate

   If you are running Oracle9i or higher you need to use sqlplus instead of
   svrmgrl.

5. Take a full database backup.

6. Rename/move the existing database controlfiles to a backup (The REUSE
   option will overwrite the original files). The size of the controlfile
   will be increased    by increasing the value of    MAXDATAFILES,
   MAXLOGMEMBERS, etc.

   Example:
   --------

   % cd $ORACLE_HOME/dbs
   % mv ctrlV716.ctl ctrlV716.bak

7. Create the controlfile within Server Manager

SVRMGR> connect internal
SVRMGR> @new_control.sql

   If you get the "Statement processed" message, the database will
   be opened with a brand new control file.

   If you are running Oracle9i or higher you need to use sqlplus instead of
   svrmgrl.

8. At the first opportunity, shut the database down (normal, immediate or
   transactional oracle8 only) and take a full backup.

II. CREATING A NEW CONTROL FILE WITHOUT AN EXISTING CONTROL FILE:
-----------------------------------------------------------------

CREATE CONTROLFILE SYNTAX:
The following is information on the create control file syntax. This
information is fully documented in the Oracle SQL Reference Manual.

CREATE CONTROLFILE [REUSE]
   DATABASE name
   [LOGFILE filespec [, filespec] ...]
RESETLOGS | NORESETLOGS
   [MAXLOGFILES integer]
   [DATAFILE filespec [, filespec] ...]
   [MAXDATAFILES integer]
   [MAXINSTANCES integer]
   [ARCHIVELOG | NOARCHIVELOG]
   [SHARED | EXCLUSIVE]

The complete procedure follows:

1. Take a full backup of the database, including all datafiles and redo
   log files.

2. Go into SQL*DBA or Server Manager and do a STARTUP NOMOUNT.

3. Issue the CREATE CONTROLFILE statement.

   Example:
   --------

   CREATE CONTROLFILE REUSE DATABASE "P716" NORESETLOGS NOARCHIVELOG
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXDATAFILES 300
   MAXINSTANCES 8
   MAXLOGHISTORY 500
   LOGFILE
   GROUP 1 '/u01/oracle/7.1.6/dbs/log1p716.dbf' SIZE 1M,
   GROUP 2 '/u01/oracle/7.1.6/dbs/log2p716.dbf' SIZE 1M,
   GROUP 3 '/u01/oracle/7.1.6/dbs/log3p716.dbf' SIZE 1M
   DATAFILE
   '/u01/oracle/7.1.6/dbs/systp716.dbf' SIZE 40M,
   '/u01/oracle/7.1.6/dbs/tempp716.dbf' SIZE 1M,
   '/u01/oracle/7.1.6/dbs/toolp716.dbf' SIZE 15M ;

4. Perform media recovery on the database.

   SVRMGR> recover database;

   If you are running Oracle9i or higher you need to use sqlplus instead of
   svrmgrl.

5. Open the database.

   SVRMGR> alter database open;

   If you are running Oracle9i or higher you need to use sqlplus instead of
   svrmgrl.

6. At the first opportunity, shut the database down and take a full cold
   backup.

Additional Errors:
------------------
ORA-205 ORA-7360 ORA-376 ORA-1110 ORA-1111

Posted by pat98

2006. 12. 28. 09:05 오라클

ORA-00257

Applies to:

Oracle Server - Standard Edition - Version: 10.1.0.0
This problem can occur on any platform.

Symptoms

Connections to the database Receive ORA-00257

Cause

The max limit for flash recovery area(db_recovery_file_dest_size) is reached.

Solution

SOLUTION:
========

1. Check whether the database is in archive log mode and automatic archiving is enabled.

SQL> archive log list;

2. If Archive destination is USE_DB_RECOVERY_FILE_DEST, find the archive destination by :

SQL> show parameter db_recovery_file_dest;

Check what is the value set for db_recovery_file_dest_size

3. Find the space used in flash recovery area by :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

4. If SPACE_USED is equal to SPACE_LIMIT, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;

NOTE: See Note 315098.1 for pointers on backing up files from the flash recovery area to decrease space usage

6. If ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEED_DEST

is received for step 5, then

For each destination give the correct archivelog path and issue:

SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';

NOTE:

If you have configurated Flash Recovery Area (default in 10gR2)--meaning that you have set the two initialization parameters:
-- DB_RECOVERY_FILE_DEST_SIZE
-- DB_RECOVERY_FILE_DEST:
you can not use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters any more. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area. LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

If you try to use LOG_ARCHIVE_DEST with a Flash Recovery Area configured you will receive errors like

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

7. Just switch the logs to verify:

SQL> alter system switch logfile;

NOTE:
====

If you want to increase the flash recovery area:

STEPS:
=====

1. Note down the path of flash recovery area.

SQL> show parameter db_recovery_file_dest;

2. Disable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';

3. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g SCOPE=BOTH SID='*';

4. Enable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/dir1' SCOPE=BOTH SID='*';

If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';

Posted by pat98

2006. 12. 27. 08:56 오라클

Manual Migration of Oracle 8.1.x to 8.1.7

Oracle 8.1.6 -> 8.1.7 로 upgrade 하는 방법
==============================================================================
This is a short Guide to Migrate Oracle 8.1.x (x < 7) to 8.1.7 using Oracle Migration Scripts. We have successfully migrated several databases using this method.

Content

Special Migration Steps are needed (not documented here) for
Prepare to migrate
Migration Steps
Postmigration
Additional Checks after the Migration

Special Migration Steps are needed (not documented here) for

Upgrading interMedia Text From a Previous 8.1 Release
Upgrading JServer
Upgrading Oracle Parallel Server
Upgrading Snapshots
Upgrading the Advanced Queuing Option

Prepare to migrate

Complete the following steps to begin the upgrade process:

Before you migrate or upgrade to release 8.1, make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following:

.WORLD
A valid domain setting for your environment

Make sure the _SYSTEM_TRIG_ENABLED initialization parameter is set to FALSE in the initialization parameter file. If this initialization parameter is not currently set, then explicitly set it to FALSE:

_SYSTEM_TRIG_ENABLED = FALSE

Do NOT drop the OUTLN User

If you are upgrading from an 8.1 release, then you do not need to perform this check because the OUTLN user should have been created when you installed the previous 8.1 release. Do not drop the OUTLN user if you are upgrading from a previous 8.1 release.

Check free Space in SYSTEM and Rollback Segment Tablepace

Upgrading to a new release requires more space in your SYSTEM tablespace and in the tablespaces where you store rollback segments. If you have enough space on your system, then consider adding more space to these tablespaces. In general, you need at least 20 MB of free space in your SYSTEM tablespace to upgrade. Check the free space with the following SQL statement

clear buffer
clear columns
clear breaks
set linesize 500
set pagesize 5000
column a1 heading 'Tablespace' format a15
column a2 heading 'Data File' format a45
column a3 heading 'Total|Space [MB]' format 99999.99
column a4 heading 'Free|Space [MB]' format 99999.99
column a5 heading 'Free|%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,
   a.file_name a2,
   a.avail a3,
   NVL(b.free,0) a4,
   NVL(ROUND(((free/avail)*100),2),0) a5
FROM (SELECT tablespace_name,
   SUBSTR(file_name,1,45) file_name,
   file_id,
   ROUND(SUM(bytes/(1024*1024)),3) avail
FROM sys.dba_data_files
GROUP BY tablespace_name,
   SUBSTR(file_name,1,45),
   file_id) a,
   (SELECT tablespace_name,
   file_id,
   ROUND(SUM(bytes/(1024*1024)),3) free
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id) b
WHERE a.file_id = b.file_id (+)
ORDER BY 1, 2
/

Save your 8.1.6 Database

Determine the files that you will back up by issuing the following SQL statements:

SPOOL v8files.log;
SELECT member FROM v$logfile;
SELECT name FROM v$datafile;
SELECT value FROM v$parameter WHERE name = 'control_files';
SPOOL OFF;

Migration Steps

Shutdown your 8.1.x Database and Listener
(Use shutdown immediate not shutdown abort)

SVRMGR> SHUTDOWN IMMEDIATE
LSNRCTL> STOP <Listener-Name>

Adjust the initialization parameter file for use with the new release.

db_domain = .WORLD
optimizer_mode = choose
job_queue_processes = 0
aq_tm_processes = 0

Switch to the Oracle 8.1.7 Environment

Make sure that the following environment variables point to the new release 8.1 directories:

ORACLE_HOME

PATH

ORA_NLS

LD_LIBRARY_PATH

Start the Migration (this is the Main Task)

cd $ORACLE_HOME/rdbms/admin

As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get an error messge:

svrmgrl
connect internal
STARTUP RESTRICT
SPOOL revoke_restricted_session;
SELECT 'REVOKE restricted session FROM '
|| username || ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
SPOOL OFF;
@revoke_restricted_session.log;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

SPOOL catoutu.log
SET ECHO ON
@u0801060.sql # Script for 8.1.6 -> 8.1.7SET ECHO OFF
SPOOL OFF

ALTER SYSTEM DISABLE RESTRICTED SESSION;
SHUTDOWN IMMEDIATE

The script you run creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the release to which you are upgrading, which create the system catalog views and all the necessary packages for using PL/SQL.

Important

Executing this clean shutdown flushes all caches, clears buffers, and performs other DBMS housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle8i database

If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new release 8.1 installation directory.

Postmigration

You may execute additional scripts, e.g.

# Run all sql scripts for replication option
@$ORACLE_HOME/rdbms/admin/catrep.sql

# Collect I/O per table (actually object) statistics by
# statistical sampling
@$ORACLE_HOME/rdbms/admin/catio.sql

# This package creates a table into which references to
# the chained rows for an IOT (Index-Only-Table) can be
# placed using the ANALYZE command.
@$ORACLE_HOME/rdbms/admin/dbmsiotc.sql

# Wrap Package which creates IOTs (Index-Only-Table)
@$ORACLE_HOME/rdbms/admin/prvtiotc.plb

# This package allows you to display the sizes of objects
# in the shared pool, and mark them for keeping or
# unkeeping in order to reduce memory fragmentation.
@$ORACLE_HOME/rdbms/admin/dbmspool.sql

# Creates the default table for storing the output
# of the ANALYZE LIST CHAINED ROWS command
@$ORACLE_HOME/rdbms/admin/utlchain.sql

# Creates the EXCEPTION table
@$ORACLE_HOME/rdbms/admin/utlexcpt.sql

# Grant public access to all views used by TKPROF
# with verbose=y option
@$ORACLE_HOME/rdbms/admin/utltkprf.sql

# Create table PLAN_TABLE that is used by the EXPLAIN PLAN
# statement. The explain statement requires the presence
# of this table in order to store the descriptions of
# the row sources.
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

# Create performance tuning views
@$ORACLE_HOME/rdbms/admin/catperf.sql

# Create v7 style export/import views against the v8 RDBMS
# so that EXP/IMP v7 can be used to read out data in a
# v8 RDBMS. These views are necessary if you want to export
# from Oracle8 and import in an Oracle7 database.
@$ORACLE_HOME/rdbms/admin/catexp7.sql

# Create views of oracle locks
@$ORACLE_HOME/rdbms/admin/catblock.sql

# Print out the lock wait-for graph in a tree
# structured fashion
@$ORACLE_HOME/rdbms/admin/utllockt.sql

# Creates the default table for storing the output of the
# analyze validate command on a partitioned table
@$ORACLE_HOME/rdbms/admin/utlvalid.sql

# PL/SQL Package of utility routines for raw datatypes
@$ORACLE_HOME/rdbms/admin/utlraw.sql
@$ORACLE_HOME/rdbms/admin/prvtrawb.plb

# Contains the PL/SQL interface to the cryptographic toolkit
@$ORACLE_HOME/rdbms/admin/dbmsoctk.sql
@$ORACLE_HOME/rdbms/admin/prvtoctk.plb

# This package provides a built-in random number
# generator. It is faster than generators written in PL/SQL
# because it calls Oracle's internal random number
# generator.
@$ORACLE_HOME/rdbms/admin/dbmsrand.sql

# DBMS package specification for Oracle8 Large Object
# This package provides routines for operations on BLOB
# and CLOB datatypes.
@$ORACLE_HOME/rdbms/admin/dbmslob.sql

# Procedures for instrumenting database applications
# DBMS_APPLICATION_INFO package spec.
@$ORACLE_HOME/rdbms/admin/dbmsapin.sql

# Run obfuscation toolkit script.
@$ORACLE_HOME/rdbms/admin/catobtk.sql

# Create Heterogeneous Services data dictionary objects.
@$ORACLE_HOME/rdbms/admin/caths.sql

# Stored procedures for Oracle Trace server
@$ORACLE_HOME/rdbms/admin/otrcsvr.sql

# Oracle8i Profiler for PL/SQL
# Profilers are helpful tools to investigate programs and
# identify slow program parts and bottle necks. Furthermore
# you can determine which procedure, function or any other
# code part is executed how many times. To be able to use
# the DBMS_PROFILER package you have to install once for
# your database the following packages. Do this as user SYS
@$ORACLE_HOME/rdbms/admin/profload.sql
@$ORACLE_HOME/rdbms/admin/proftab.sql
@$ORACLE_HOME/rdbms/admin/dbmspbp.sql
@$ORACLE_HOME/rdbms/admin/prvtpbp.plb

Recompiling Invalid PL/SQL Modules

The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, etc. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Additional Checks after the Migration

Check for Bad Date Constraints

A bad date constraint involves invalid date manipulation, which is a date manipulation that implicitly assumes the century in the date, causing problems at the year 2000. The utlconst.sql script runs through all of the check constraints in the database and marks constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end. Oracle7 let you create constraints with a two-digit year date constant. However, version 8 returns an error if the check constraint date constant does not include a four-digit year.

To run the utlconst.sql script, complete the following steps:

cd $ORACLE_HOME/rdbms/admin
svrmgrl

SVRMGR> CONNECT INTERNAL
SVRMGR> SPOOL utlresult.log
SVRMGR> @utlconst.sql
SVRMGR> SPOOL OFF

Server Output ON
Statement processed.
Statement processed.
Checking for bad date constraints
Finished checking -- All constraints OK!

After you run the script, the utlresult.log log file includes all the constraints that have invalid date constraints. The utlconst.sql script does not correct bad constraints, but instead it disables them. You should either drop the bad constraints or recreate them after you make the necessary changes.

Rebuild Unusable Bitmap Indexes

During migration, some bitmap indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT index_name, index_type, table_owner, status
FROM dba_indexes
WHERE index_type = 'BITMAP'
AND status = 'UNUSABLE';

Rebuild Unusable Function-Based Indexes

During upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT owner, index_name, funcidx_status
FROM dba_indexes
WHERE funcidx_status = 'DISABLED';

Change the Password for the OUTLN User

The OUTLN user is created automatically during installation of Oracle8i. This user has DBA privileges. Use the ALTER USER statement to change the password for this user. Oracle8i adds the OUTLN user schema to support Plan Stability. The OUTLN user acts as a place to centrally manage metadata associated with stored outlines.

Posted by pat98

2006. 12. 21. 10:37 오라클

Oracle profile 기능

PURPOSE
-------

Profile을 이용한 사용자 자원 관리에 대해서 알아보자

Explanation
-----------

(1) Profile을 사용할 수 있도록 환경 설정

SQL> connect system
Enter password:
Connected.

SQL> show parameter resource

NAME TYPE    VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources    integer 968
resource_limit boolean FALSE
resource_manager_plan    string

SQL> alter system set resource_limit = true scope=spfile;
System altered.

# resource_limit를 true로 변경한 후 DB 재시작.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 236000476 bytes
Fixed Size 451804 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> show parameter resource

NAME TYPE    VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources    integer 968
resource_limit boolean TRUE
resource_manager_plan    string

(2) Profile에는 어떤것들이 있는지 조회해 보자

SQL> desc dba_profiles;

Name    Null?    Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE    VARCHAR2(8)
LIMIT    VARCHAR2(40)

SQL> select resource_name, resource_type from dba_profiles;

RESOURCE_NAME    RESOURCE
-------------------------------- --------
COMPOSITE_LIMIT    KERNEL
FAILED_LOGIN_ATTEMPTS    PASSWORD
SESSIONS_PER_USER    KERNEL
PASSWORD_LIFE_TIME PASSWORD
CPU_PER_SESSION    KERNEL
PASSWORD_REUSE_TIME    PASSWORD
CPU_PER_CALL KERNEL
PASSWORD_REUSE_MAX PASSWORD
LOGICAL_READS_PER_SESSION    KERNEL
PASSWORD_VERIFY_FUNCTION PASSWORD
LOGICAL_READS_PER_CALL KERNEL

RESOURCE_NAME    RESOURCE
-------------------------------- --------
PASSWORD_LOCK_TIME PASSWORD
IDLE_TIME    KERNEL
PASSWORD_GRACE_TIME    PASSWORD
CONNECT_TIME KERNEL
PRIVATE_SGA    KERNEL

(3) Profile 생성

SQL> create profile test_profile limit
2 sessions_per_user 2
3 cpu_per_session unlimited
4 cpu_per_call 6000
5 idle_time 1
6 connect_time 480
7 /
Profile created.

# 세션당 2명의 유저를 허용하고 Session의 접속후 1분 동안 아무런 작업이 일어나지 않는
다면 자동으로 Session을 종료한다.

# Profile을 생성하여 사용자에게 지정하지 않으면 새롭게 생성되는 모든 사용자는 Default로
정의된 모든 Profile의 기능을 수행할수 있다.

# connect_time은 8분( 480 )동안만 Session을 사용할 수 있게 제한한다.

SQL> select profile, resource_name, limit from dba_profiles
2 where profile = 'TEST_PROFILE';

PROFILE    RESOURCE_NAME    LIMIT
------------------------------ -------------------------------- --------------
TEST_PROFILE COMPOSITE_LIMIT    DEFAULT
TEST_PROFILE SESSIONS_PER_USER    2
TEST_PROFILE CPU_PER_SESSION    UNLIMITED
TEST_PROFILE CPU_PER_CALL 6000
TEST_PROFILE LOGICAL_READS_PER_SESSION    DEFAULT
TEST_PROFILE LOGICAL_READS_PER_CALL DEFAULT
TEST_PROFILE IDLE_TIME    1
TEST_PROFILE CONNECT_TIME 480
TEST_PROFILE PRIVATE_SGA    DEFAULT
TEST_PROFILE FAILED_LOGIN_ATTEMPTS    DEFAULT
TEST_PROFILE PASSWORD_LIFE_TIME DEFAULT
TEST_PROFILE PASSWORD_REUSE_TIME    DEFAULT
TEST_PROFILE PASSWORD_REUSE_MAX DEFAULT
TEST_PROFILE PASSWORD_VERIFY_FUNCTION DEFAULT
TEST_PROFILE PASSWORD_LOCK_TIME DEFAULT
TEST_PROFILE PASSWORD_GRACE_TIME    DEFAULT

# 설정한 Profile이 제대로 적용되었는지 확인.

(4) 생성한 Profile을 사용자에게 적용시키고 결과를 확인한다

SQL> alter user kigook profile test_profile;
User altered.

SQL> connect kigook/kigook
Connected.

SQL> select * from t;

ID
--
aa

# 1분후에 다시 같은 문장을 조회해 보자.

SQL> /
select * from t
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

# 접속후 1분간 아무런 작업이 없으면 자동으로 Session을 종료시키는지 확인.

(5) Profile의 수정

SQL> connect system
Enter password:
Connected.

SQL> alter profile test_profile limit
2 idle_time 10
3 /
Profile altered.

SQL> select resource_name, limit from dba_profiles
2 where resource_name like '%IDLE_TIME%';

RESOURCE_NAME    LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME    UNLIMITED
IDLE_TIME    10

# 변경된 Profile의 내용 확인.

Posted by pat98

2006. 12. 12. 23:16 오라클

CRS and 10g Real Application Clusters

CRS (Clsuter Ready Service)와 10g RAC 사용에 관한 문서입니다.참고하세요. 오라클은 문서가 넘 불친절해.ㅋ

PURPOSE
-------

This document is to provide additional information on CRS (Cluster Ready Services)
in 10g Real Application Clusters.

SCOPE & APPLICATION
-------------------

This document is intended for RAC Database Administrators and Oracle support
enginneers.

CRS and 10g REAL APPLICATION CLUSTERS
-------------------------------------

CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters
that provides a standard cluster interface on all platforms and performs
new high availability operations not available in previous versions.

CRS KEY FACTS
-------------

Prior to installing CRS and 10g RAC, there are some key points to remember about
CRS and 10g RAC:

- CRS is REQUIRED to be installed and running prior to installing 10g RAC.

- CRS can either run on top of the vendor clusterware (such as Sun Cluster,
HP Serviceguard, IBM HACMP, TruCluster, Veritas Cluster, Fujitsu Primecluster,
etc...) or can run without the vendor clusterware. The vendor clusterware
was required in 9i RAC but is optional in 10g RAC.

- The CRS HOME and ORACLE_HOME must be installed in DIFFERENT locations.

- Shared Location(s) or devices for the Voting File and OCR (Oracle
Configuration Repository) file must be available PRIOR to installing CRS. The
voting file should be at least 20MB and the OCR file should be at least 100MB.

- CRS and RAC require that the following network interfaces be configured prior
to installing CRS or RAC:
- Public Interface
- Private Interface
- Virtual (Public) Interface
For more information on this, see Note 264847.1

- The root.sh script at the end of the CRS installation starts the CRS stack.
If your CRS stack does not start, see Note 240001.1

- Only one set of CRS daemons can be running per RAC node.

- On Unix, the CRS stack is run from entries in /etc/inittab with "respawn".

- If there is a network split (nodes lose communication with each other). One
or more nodes may reboot automatically to prevent data corruption.

- The supported method to start CRS is booting the machine. MANUAL STARTUP OF
THE CRS STACK IS NOT SUPPORTED UNTIL 10.1.0.4 OR HIGHER.
- The supported method to stop is shutdown the machine or use "init.crs stop".

- Killing CRS daemons is not supported unless you are removing the CRS
installation via Note 239998.1 because flag files can become mismatched.

- For maintenance, go to single user mode at the OS.

Once the stack is started, you should be able to see all of the daemon processes
with a ps -ef command:

[rac1]/u01/home/beta> ps -ef | grep crs

oracle 1363 999 0 11:23:21 ? 0:00 /u01/crs_home/bin/evmlogger.bin -o /u01
oracle 999 1 0 11:21:39 ? 0:01 /u01/crs_home/bin/evmd.bin
root    1003 1 0 11:21:39 ? 0:01 /u01/crs_home/bin/crsd.bin
oracle 1002 1 0 11:21:39 ? 0:01 /u01/crs_home/bin/ocssd.bin

CRS DAEMON FUNCTIONALITY
------------------------

Here is a short description of each of the CRS daemon processes:

CRSD:
- Engine for HA operation
- Manages 'application resources'
- Starts, stops, and fails 'application resources' over
- Spawns separate 'actions' to start/stop/check application resources
- Maintains configuration profiles in the OCR (Oracle Configuration Repository)
- Stores current known state in the OCR.
- Runs as root
- Is restarted automatically on failure

OCSSD:
- OCSSD is part of RAC and Single Instance with ASM
- Provides access to node membership
- Provides group services
- Provides basic cluster locking
- Integrates with existing vendor clusteware, when present
- Can also runs without integration to vendor clustware
- Runs as Oracle.
- Failure exit causes machine reboot.
--- This is a feature to prevent data corruption in event of a split brain.

EVMD:
- Generates events when things happen
- Spawns a permanent child evmlogger
- Evmlogger, on demand, spawns children
- Scans callout directory and invokes callouts.
- Runs as Oracle.
- Restarted automatically on failure

CRS LOG DIRECTORIES
-------------------

When troubleshooting CRS problems, it is important to review the directories
under the CRS Home.

$ORA_CRS_HOME/crs/log - This directory includes traces for CRS resources that are
joining, leaving, restarting, and relocating as identified by CRS.

$ORA_CRS_HOME/crs/init - Any core dumps for the crsd.bin daemon should be written
here. Note 1812.1 could be used to debug these.

$ORA_CRS_HOME/css/log - The css logs indicate all actions such as
reconfigurations, missed checkins , connects, and disconnects from the client
CSS listener . In some cases the logger logs messages with the category of
(auth.crit) for the reboots done by oracle. This could be used for checking the
exact time when the reboot occured.

$ORA_CRS_HOME/css/init - Core dumps from the ocssd primarily and the pid for the
css daemon whose death is treated as fatal are located here. If there are
abnormal restarts for css then the core files will have the formats of
core.<pid>. Note 1812.1 could be used to debug these.

$ORA_CRS_HOME/evm/log - Log files for the evm and evmlogger daemons. Not used
as often for debugging as the CRS and CSS directories.

$ORA_CRS_HOME/evm/init - Pid and lock files for EVM. Core files for EVM should
also be written here. Note 1812.1 could be used to debug these.

$ORA_CRS_HOME/srvm/log - Log files for OCR.

STATUS FOR CRS RESOURCES
------------------------

After installing RAC and running the VIPCA (Virtual IP Configuration Assistant)
launched with the RAC root.sh, you should be able to see all of your CRS
resources with crs_stat. Example:

cd $ORA_CRS_HOME/bin
./crs_stat

NAME=ora.rac1.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac1.oem
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac1.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac1.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.gsd
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.oem
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.ons
TYPE=application
TARGET=ONLINE
STATE=ONLINE

NAME=ora.rac2.vip
TYPE=application
TARGET=ONLINE
STATE=ONLINE

There is also a script available to view CRS resources in a format that is
easier to read. Just create a shell script with:

--------------------------- Begin Shell Script -------------------------------

#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#    format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
AWK=/usr/xpg4/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
   printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

--------------------------- End Shell Script -------------------------------

Example output:

[opcbsol1]/u01/home/usupport> ./crsstat
HA Resource Target State
----------- ------ -----
ora.V10SN.V10SN1.inst ONLINE ONLINE on opcbsol1
ora.V10SN.V10SN2.inst ONLINE ONLINE on opcbsol2
ora.V10SN.db    ONLINE ONLINE on opcbsol2
ora.opcbsol1.ASM1.asm ONLINE ONLINE on opcbsol1
ora.opcbsol1.LISTENER_OPCBSOL1.lsnr ONLINE ONLINE on opcbsol1
ora.opcbsol1.gsd    ONLINE ONLINE on opcbsol1
ora.opcbsol1.ons    ONLINE ONLINE on opcbsol1
ora.opcbsol1.vip    ONLINE ONLINE on opcbsol1
ora.opcbsol2.ASM2.asm ONLINE ONLINE on opcbsol2
ora.opcbsol2.LISTENER_OPCBSOL2.lsnr ONLINE ONLINE on opcbsol2
ora.opcbsol2.gsd    ONLINE ONLINE on opcbsol2
ora.opcbsol2.ons    ONLINE ONLINE on opcbsol2
ora.opcbsol2.vip    ONLINE ONLINE on opcbsol2

CRS RESOURCE ADMINISTRATION
---------------------------

You can use srvctl to manage these resources. Below are syntax and examples.

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

CRS RESOURCE STATUS

srvctl status database -d <database-name> [-f] [-v] [-S <level>]
srvctl status instance -d <database-name> -i <instance-name> >[,<instance-name-list>]
[-f] [-v] [-S <level>]
srvctl status service -d <database-name> -s <service-name>[,<service-name-list>]
[-f] [-v] [-S <level>]
srvctl status nodeapps [-n <node-name>]
srvctl status asm -n <node_name>

EXAMPLES:

Status of the database, all instances and all services.
srvctl status database -d ORACLE -v
Status of named instances with their current services.
srvctl status instance -d ORACLE -i RAC01, RAC02 -v
Status of a named services.
srvctl status service -d ORACLE -s ERP -v
Status of all nodes supporting database applications.
srvctl status node

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

START CRS RESOURCES

srvctl start database -d <database-name> [-o < start-options>]
[-c <connect-string> | -q]
srvctl start instance -d <database-name> -i <instance-name>
[,<instance-name-list>] [-o <start-options>] [-c <connect-string> | -q]
srvctl start service -d <database-name> [-s <service-name>[,<service-name-list>]]
[-i <instance-name>] [-o <start-options>] [-c <connect-string> | -q]
srvctl start nodeapps -n <node-name>
srvctl start asm -n <node_name> [-i <asm_inst_name>] [-o <start_options>]

EXAMPLES:

Start the database with all enabled instances.
srvctl start database -d ORACLE
Start named instances.
srvctl start instance -d ORACLE -i RAC03, RAC04
Start named services. Dependent instances are started as needed.
srvctl start service -d ORACLE -s CRM
Start a service at the named instance.
srvctl start service -d ORACLE -s CRM -i RAC04
Start node applications.
srvctl start nodeapps -n myclust-4

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

STOP CRS RESOURCES

srvctl stop database -d <database-name> [-o <stop-options>]
[-c <connect-string> | -q]
srvctl stop instance -d <database-name> -i <instance-name> [,<instance-name-list>]
[-o <stop-options>][-c <connect-string> | -q]
srvctl stop service -d <database-name> [-s <service-name>[,<service-name-list>]]
[-i <instance-name>][-c <connect-string> | -q] [-f]
srvctl stop nodeapps -n <node-name>
srvctl stop asm -n <node_name> [-i <asm_inst_name>] [-o <start_options>]

EXAMPLES:

Stop the database, all instances and all services.
srvctl stop database -d ORACLE
Stop named instances, first relocating all existing services.
srvctl stop instance -d ORACLE -i RAC03,RAC04
Stop the service.
srvctl stop service -d ORACLE -s CRM
Stop the service at the named instances.
srvctl stop service -d ORACLE -s CRM -i RAC04
Stop node applications. Note that instances and services also stop.
srvctl stop nodeapps -n myclust-4

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

ADD CRS RESOURCES

srvctl add database -d <name> -o <oracle_home> [-m <domain_name>] [-p <spfile>]
[-A <name|ip>/netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}]
[-s <start_options>] [-n <db_name>]
srvctl add instance -d <name> -i <inst_name> -n <node_name>
srvctl add service -d <name> -s <service_name> -r <preferred_list>
[-a <available_list>] [-P <TAF_policy>] [-u]
srvctl add nodeapps -n <node_name> -o <oracle_home>
[-A <name|ip>/netmask[/if1[|if2|...]]]
srvctl add asm -n <node_name> -i <asm_inst_name> -o <oracle_home>

OPTIONS:

-A vip range, node, and database, address specification. The format of
   address string is:
[<logical host name>]/<VIP address>/<net mask>[/<host interface1[ |
   host interface2 |..]>] [,] [<logical host name>]/<VIP address>/<net mask>
   [/<host interface1[ | host interface2 |..]>]
-a for services, list of available instances, this list cannot include
   preferred instances
-m domain name with the format “us.mydomain.com”
-n node name that will support one or more instances
-o $ORACLE_HOME to locate Oracle binaries
-P for services, TAF preconnect policy - NONE, PRECONNECT
-r for services, list of preferred instances, this list cannot include
   available instances.
-s spfile name
-u updates the preferred or available list for the service to support the
   specified instance. Only one instance may be specified with the -u
   switch. Instances that already support the service should not be
   included.

EXAMPLES:

Add a new node:
srvctl add nodeapps -n myclust-1 -o $ORACLE_HOME –A
   139.184.201.1/255.255.255.0/hme0
Add a new database.
srvctl add database -d ORACLE -o $ORACLE_HOME
Add named instances to an existing database.
   srvctl add instance -d ORACLE -i RAC01 -n myclust-1
   srvctl add instance -d ORACLE -i RAC02 -n myclust-2
   srvctl add instance -d ORACLE -i RAC03 -n myclust-3
Add a service to an existing database with preferred instances (-r) and
available instances (-a). Use basic failover to the available instances.
srvctl add service -d ORACLE -s STD_BATCH -r RAC01,RAC02 -a RAC03,RAC04
Add a service to an existing database with preferred instances in list one and
available instances in list two. Use preconnect at the available instances.
srvctl add service -d ORACLE -s STD_BATCH -r RAC01,RAC02 -a RAC03,RAC04 -P PRECONNECT

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

REMOVE CRS RESOURCES

srvctl remove database -d <database-name>
srvctl remove instance -d <database-name> [-i <instance-name>]
srvctl remove service -d <database-name> -s <service-name> [-i <instance-name>]
srvctl remove nodeapps -n <node-name>

EXAMPLES:

Remove the applications for a database.
srvctl remove database -d ORACLE
Remove the applications for named instances of an existing database.
srvctl remove instance -d ORACLE -i RAC03
srvctl remove instance -d ORACLE -i RAC04
Remove the service.
srvctl remove service -d ORACLE -s STD_BATCH
Remove the service from the instances.
srvctl remove service -d ORACLE -s STD_BATCH -i RAC03,RAC04
Remove all node applications from a node.
srvctl remove nodeapps -n myclust-4

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

MODIFY CRS RESOURCES

srvctl modify database -d <name> [-n <db_name] [-o <ohome>] [-m <domain>]
[-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}]
[-s <start_options>]
srvctl modify instance -d <database-name> -i <instance-name> -n <node-name>
srvctl modify instance -d <name> -i <inst_name> {-s <asm_inst_name> | -r}
srvctl modify service -d <database-name> -s <service_name> -i <instance-name>
-t <instance-name> [-f]
srvctl modify service -d <database-name> -s <service_name> -i <instance-name>
-r [-f]
srvctl modify nodeapps -n <node-name> [-A <address-description> ] [-x]

OPTIONS:

-i <instance-name> -t <instance-name> the instance name (-i) is replaced by the
instance name (-t)
-i <instance-name> -r the named instance is modified to be a preferred instance
-A address-list for VIP application, at node level
-s <asm_inst_name> add or remove ASM dependency

EXAMPLES:

Modify an instance to execute on another node.
srvctl modify instance -d ORACLE -n myclust-4
Modify a service to execute on another node.
srvctl modify service -d ORACLE -s HOT_BATCH -i RAC01 -t RAC02
Modify an instance to be a preferred instance for a service.
srvctl modify service -d ORACLE -s HOT_BATCH -i RAC02 –r

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

RELOCATE SERVICES

srvctl relocate service -d <database-name> -s <service-name> [-i <instance-name >]-t<instance-name > [-f]

EXAMPLES:

Relocate a service from one instance to another
srvctl relocate service -d ORACLE -s CRM -i RAC04 -t RAC01

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

ENABLE CRS RESOURCES (The resource may be up or down to use this function)

srvctl enable database -d <database-name>
srvctl enable instance -d <database-name> -i <instance-name> [,<instance-name-list>]
srvctl enable service -d <database-name> -s <service-name>] [, <service-name-list>] [-i <instance-name>]

EXAMPLES:

Enable the database.
srvctl enable database -d ORACLE
Enable the named instances.
srvctl enable instance -d ORACLE -i RAC01, RAC02
Enable the service.
srvctl enable service -d ORACLE -s ERP,CRM
Enable the service at the named instance.
srvctl enable service -d ORACLE -s CRM -i RAC03

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

DISABLE CRS RESOURCES (The resource must be down to use this function)

srvctl disable database -d <database-name>
srvctl disable instance -d <database-name> -i <instance-name> [,<instance-name-list>]
srvctl disable service -d <database-name> -s <service-name>] [,<service-name-list>] [-i <instance-name>]

EXAMPLES:

Disable the database globally.
srvctl disable database -d ORACLE
Disable the named instances.
srvctl disable instance -d ORACLE -i RAC01, RAC02
Disable the service globally.
srvctl disable service -d ORACLE -s ERP,CRM
Disable the service at the named instance.
srvctl disable service -d ORACLE -s CRM -i RAC03,RAC04

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

Posted by pat98

2006. 12. 5. 10:26 오라클

Oracle Created Database Users: Password, Usage and Files

오라클 default 유저 및 passwd에 대한 설명

Purpose
~~~~~~~
The following table lists the default usernames and passwords you may encounter
in your database as a result of installing various additional
--> products/components
--> options
--> new features of the version
It provides references to
--> which option or product should be installed to have it
--> what script created it
--> what it is used for
--> suggestion to secure the account

This note is intended to be an up-to-date list of default usernames: as such the
information is subject to change.

SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~
Database Administrators who need to export full databases, manage users and
security.

List Of Users Created at Database Creation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
By default, are automatically created during database creation :
SCOTT by script $ORACLE_HOME/rdbms/admin/utlsampl.sql
OUTLN by script $ORACLE_HOME/rdbms/admin/sql.bsq

Optionally:
DBSNMP    if Enterprise Manager Intelligent Agent is installed
MGMT_VIEW is part of the DB Control Repository
SYSMAN    is part of the DB Control Repository and Grid
   see Note 331209.1 for details
TRACESVR    if Enterprise Manager is installed
AURORA$ORB$UNAUTHENTICATED \
AURORA$JIS$UTILITY$ -- if Oracle Servlet Engine (OSE) is installed
OSE$HTTP$ADMIN /
MDSYS if Oracle Spatial option is installed
ORDSYS    if interMedia Audio option is installed
ORDPLUGINS    if interMedia Audio option is installed
CTXSYS    if Oracle Text option is installed
REPADMIN    if Replication Option is installed
LBACSYS if Oracle Label Security option is installed
ODM if Oracle Data Mining option is installed
ODM_MTR idem
DMSYS (10g) if Oracle Data Mining 10g option is installed
DMSYS in 10g version to replace ODM and ODM_MTR schemas
OLAPSYS if OLAP option is installed
WMSYS if Oracle Workspace Manager script owmctab.plb is
   executed.
ANONYMOUS if catqm.sql catalog script for SQL XML management
XDB is executed
EXFSYS    is the Expression Filter Feature repository
DIP for provisioning event processing
Oracle9i Sample Schemas if you need to test through a complete sample
   system of information (see Note 207560.1)
Note
~~~~
If you have already the 9i sample schemas (hr, oe, pm, etc.) and you don't need
them, you can removed them safely and without damage to the database.

a. The SCOTT user is created by default to provide sample user tables. You can
alter the password. To create the default tables, run the script
$ORACLE_HOME/sqlplus/demo/demobld.sql. To drop the objects, run
$ORACLE_HOME/sqlplus/demo/demodrop.sql connected as SCOTT.
b. The OUTLN (OUTLiNes) user is automatically created during installation of
Oracle8i and Oracle9i. OULTN is the schema of Stored Outlines.
The package OUTLN_PKG is used to manage stored outlines and categories.
OUTLN is the owner of tables OL$, OL$HINTS (Oracle8i) as well as OL$NODES
(Oracle9i) used to store hints for stored outlines.
You can change the password for the OUTLN schema just as for the SYS and SYSTEM
schemas.
b. The DBSNMP user can be dropped by running the catnsnmp.sql script. If you
need to re-create it, run the catsnmp.sql script. Ensure that there are no
jobs running or scheduled in Oracle Enterprise Manager before you run the
script.
MGMT_VIEW is part of the DB Control Repository, it is created by running
the script $OH/sysman/admin/emdrep/bin/RepManager, the password is
autogenerated (it does not have a fixed or default value).
SYSMAN belongs to the same feature ( see Note 331209.1 for details):
it is the schema of the Grid OMS repository of the 10G Enterprise Manager Grid.
c. The TRACESVR user is created by $ORACLE_HOME/otrace/admin/otrcsvr.sql during
the installation of OEM.
If you change TRACESVR user's password, you will not be able to do OTrace
collections. This user is only used with 7.x Databases where stored procedures
are used by OTrace to start and stop data collections. The TRACESVR/stored
procedure mechanism is no longer used to control OTrace collections starting
with Oracle 8.x Databases. Therefore in this case you can change the password
(or drop that user).
d. The three JSERV accounts (AURORA$JIS$UTILITY$, AURORA$ORB$UNAUTHENTICATED and
OSE$HTTP$ADMIN) are used internally by Enterprise Java Beans and CORBA Tools
and created with randomly-generated passwords 'INVALID_ENCRYPTED_PASSWORD'.
-> jisorb.sql creates user AURORA$ORB$UNAUTHENTICATED.
-> jisbgn.sql creates user AURORA$JIS$UTILITY$
-> jishausr.sql creates user OSE$HTTP$ADMIN
These 3 scripts are launched by init_jis.sql script to install the Oracle
Servlet Engine (OSE)

Changing their passwords would prevent the ORB from working.
This is supposed to change in a future version so that you can change their
password.
e. MDSYS, ORDSYS, CTXSYS, and ORDPLUGINS are created to support Oracle
Intermedia. The default password for the ORDSYS user during automatic
installation is 'ORDSYS', and for ORDPLUGINS is 'ORDPLUGINS'.
You have to change these passwords once the installation is complete.
f. REPADMIN user: it is usual to have a separate user for the replication
administrator to protect master groups from being managed by snapshot
administrators.
This user configures the replicated environment and performs administration
of all replicated schemas / groups.
g. LBACSYS user: the Oracle Label Security administrator username.

h. ODM user: who performs data mining operations.
In 10g, a user can be created with a chosen name.
i. ODM_MTR user: the account associated with the data repository for data
mining sample programs.
In 10g, DMSYS is the schema for the data repository.
j. OLAPSYS user: identity used to create OLAP metadata structures.
k. WMSYS user: used to store all the metadata information for Oracle Workspace
Manager.
l. ANONYMOUS user: allows HTTP access to Oracle XML DB.

m. XDB user: Used for storing Oracle XML DB data and metadata.
n. EXFSYS is the Expression Filter Feature repository (see Note 258618.1):
the user is created with the script exfsys.sql that asks for a password.
o. DIP is created in rdbms/admin/catdip.sql (password is DIP): for provisioning
event processing
p. The Oracle9i Sample Schemas provides installed schemas meant to be used for
demonstration purposes only:
-> HR: Human Resources schema
   The Human Resources division tracks information on the company's
   employees and facilities.
-> OE: Order Entry schema requires "Oracle Spatial" option.
   The Order Entry division tracks product inventories and sales of the
   companyï¿½s products through various channels.
-> PM: Product Media schema requires "Oracle JVM" and "Oracle Intermedia"
   options.
   The Product Media division maintains descriptions and detailed
   information on each product sold by the company.
-> SH: Sales History schema requires "Oracle OLAP" set up.
   The Sales History division tracks business statistics to facilitate
   business decisions.
-> QS: Queued Shipping schema
   The Shipping division manages the shipping of products to customer.
   The sample company has decided to test the use of messaging to
   manage its proposed B2B applications.
-> QS_ES (Eastern Shipping)
-> QS_WS (Western Shipping)
-> QS_OS (Overseas Shipping)
-> QS_CB (Customer Billing)
-> QS_CS (Customer Service)
-> QS_ADM (Administration)
-> QS_CBADM (Customer Billing Administration)
If any of the user accounts is locked and expired upon installation and needs to
be activated, unlock and assign a new meaningful password to that user account:

SQL> ALTER USER "schema_name" IDENTIFIED BY "newpass" ACCOUNT UNLOCK;

Passwords and Referenced Files List
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Username    | Description | Default Password | Created or Referenced |Comments
   | |    | in $ORACLE_HOME/ files    |
------------+-------------------------+------------------+-------------------------------+--------
   | |    | |
CTXSYS    | Intermedia Text schema | CTXSYS |./ctx/admin/dr0csys.sql    |change
   | |    |./bin/ctxsrv -user ctxsys/*    |
------------+-------------------------+------------------+-------------------------------+--------
DBSNMP    | Intelligent Agent user | DBSNMP |./rdbms/admin/catsnmp.sql    |change
   | |    |./bin/dbsnmp |
   | |    |./network/admin/snmp_rw.ora    |
------------+-------------------------+------------------+-------------------------------+--------
MGMT_VIEW | DB Control Repository | autogenerated    |./sysman/admin/emdrep/bin/RepManager|
------------+-------------------------+------------------+-------------------------------+--------
SYSMAN    | DB Control Repository | asked    | |See Note 259379.1
------------+-------------------------+------------------+-------------------------------+--------
MDSYS | Spatial Data Option user| MDSYS    |./md/admin/mdinst.sql    |change
   | |    |./ord/admin/ordisysc.sql |
------------+-------------------------+------------------+-------------------------------+--------
ODM | Oracle Data Mining    | ODM    |./dm/admin/dmcrt.sql |change
ODM_MTR | Oracle Data Mining    | MTRPW    |./dm/admin/dmcrt.sql |change
DMSYS (10g) | Oracle Data Mining    | DMSYS    |./dm/admin/odmcrtm.sql |change
any name    | Oracle Data Mining    | any pasword    |./dm/admin/odmcrt.sql    |
------------+-------------------------+------------------+-------------------------------+--------
ORDPLUGINS | InterMedia Audio option | ORDPLUGINS |./ord/admin/ordisysc.sql |change
ORDSYS    | InterMedia Audio option | ORDSYS |./ord/admin/ordisysc.sql |change
------------+-------------------------+------------------+-------------------------------+--------
OUTLN | Stored Outlines | OUTLN    |./rdbms/admin/c0703040.sql |lock
   | |    |./rdbms/admin/c0800050.sql |
   | |    |./rdbms/admin/sql.bsq    |
------------+-------------------------+------------------+-------------------------------+--------
PERFSTAT    | STATSPACK Repository    | PERFSTAT |./rdbms/admin/spcreate.sql |change
   | |    |(./rdbms/admin/spcusr.sql) |
------------+-------------------------+------------------+-------------------------------+--------
RMAN    | RMAN catalog Owner    | RMAN | manually    |change
------------+-------------------------+------------------+-------------------------------+--------
SCOTT | Demo user | TIGER    |./rdbms/admin/utlsampl.sql |*drop
------------+-------------------------+------------------+-------------------------------+--------
WKPROXY | Ultraseach user | change_on_install|./ultrasearch/admin/wk0csys.sql|change
WKSYS | Ultraseach user | change_on_install|./ultrasearch/admin/wk0install.sql| ""
------------+-------------------------+------------------+-------------------------------+--------
WMSYS | Oracle Workspace Manager| wmsys    |./rdbms/admin/owmctab.plb    |lock
------------+-------------------------+------------------+-------------------------------+--------
XDB | SQL XML management    | change_on_install|./rdbms/admin/catqm.sql    |lock
ANONYMOUS | SQL XML management    | values anonymous |./rdbms/admin/catqm.sql    |lock
------------+-------------------------+------------------+-------------------------------+--------
TRACESVR    | Oracle Trace user | trace    |./rdbms/admin/otrcsvr.sql    |change
------------+-------------------------+------------------+-------------------------------+--------
OAS_PUBLIC | Web Toolkit/Content | manager    | See Note 99088.1    |change
WEBSYS    | Web Toolkit/Content | manager    | See Note 99088.1    |change
------------+-------------------------+------------------+-------------------------------+--------
REPADMIN    | Replication user    | managed by DBA |created manually by CREATE USER|
   | |    |./ldap/admin/oidrsrms.sql    |change
   | |    |./ldap/admin/oidrsms.sql |
------------+-------------------------+------------------+-------------------------------+--------
AURORA$ORB$UNAUTHENTICATED| OSE | random value |./javavm/install/jisorb.sql    |lock
AURORA$JIS$UTILITY$ | OSE | random value |./javavm/install/jisbgn.sql    |lock
OSE$HTTP$ADMIN    | OSE | random value |./javavm/install/jishausr.sql |lock
------------+-------------------------+------------------+-------------------------------+--------
LBACSYS | Label Security    | LBACSYS    |./rdbms/admin/catlbacs.sql |change
------------+-------------------------+------------------+-------------------------------+--------
SYS | Administrative | change_on_install|./rdbms/admin/sql.bsq    |change
SYSTEM    | Administrative | manager    |./rdbms/admin/sql.bsq    |change
------------+-------------------------+------------------+-------------------------------+---------
EXFSYS    | Expression Filter Feature repository |asked|./rdbms/admin/exfsys.sql |locked
------------+-------------------------+------------------+-------------------------------+--------
DIP | Provision event processing| DIP    |./rdbms/admin/catdip.sql |locked
------------+-------------------------+------------------+-------------------------------+--------
* User SCOTT is for test purposes: drop it if of no use.

Posted by pat98

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.

Posted by pat98

1 ··· 43 44 45 46 47 48

home tag media location guestbook admin write

pat98's blog is powered by Daum/designed by Tistory

pat98's always & forever

'오라클'에 해당되는 글 473건

ORA-00600 [kkqcby: qbcfkkc not zero] After Applying 10.2.0.2

How install Partitioning after Patchset Installation

oracle relink하기

controlfile 재생성하기

ORA-00257

Applies to:

Symptoms

Cause

Solution

Manual Migration of Oracle 8.1.x to 8.1.7

Oracle profile 기능

CRS and 10g Real Application Clusters

Oracle Created Database Users: Password, Usage and Files

Oracle Data Mining 삭제하기

카테고리

태그목록

글 보관함

최근에 올라온 글

달력

링크

최근에 달린 댓글

티스토리툴바

« | » 2024.5
일	월	화	수	목	금	토
			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