Oracle DBA Notes  

Index  Java Internet Oracle Notes

   SQL
UNIX Basics Web Basics
Notes
These are my personal notes that I use
as a quick help in my work.  Informatica Servlets Apache BkpRstore SQL*Plus
You are welcome to read them. Visual
  LDAP Storage PL/SQL
Basic
(Disclaimer: I have not fully checked
my notes for errors. Windows     Tables OEM
They are meant for personal reference
only). UML     Net8 Portal

SQL Server   perl Performance OLAP

Contents of Top-level
current page home page Vmware   PHP/MySQL User Mgmt  

More technical pages here

Contents

 Connecting
 Start and stop databases
 Database Basics
 SGA / memory
 General notes for files
 Some notes for installation
 Create a Database
 Copy a Database
 Clone a Database
 Move a Database
 DBA Tasks
 Parameters
 Control Files
 Redo Log Files
 Database Links
 Languages
 User Management (separate file)
 Misc. tips
 National Language Support (another file)
 Audit (see oracle_tables.html)
 Real Application Clusters (RAC) see "backup and restore" page


Some useful links:

 Oracle documentation
 SQL Reference
 Oracle Database Reference
 See this for analytic functions
 SQL Developer

http://www.cthullen.s5.com/
 

Connecting

At DB  SYS   change_on_install 
creation:  
   SYSTEM   manager 
   INTERNAL  oracle 
OEM creation:   SYSMAN   oem_temp 

Change password file (remember parameter remote_login_passwordfile = exclusive in init.ora). It may be necessary to restart
NT to take the changes into account. Change SYSTEM also with ALTER USER.

SET ORACLE_SID=SID_NAME (NT)

orapwd80 file=../database/pwdsid.ora password=! entries=5 (NT) 
or
export ORACLE_SID=SID_NAME (UNIX-ksh)
orapwd file=$ORACLE_HOME/dbs/orapwSID password=! entries=5 (UNIX Tru64)

ALTER USER someone IDENTIFIED BY hard_to_guess REPLACE old_pw; 

Change password in SQL*plus with PASSWORD [user]

Connect with:

SET ORACLE_SID=SID_NAME (NT)

export ORACLE_SID=SID_NAME (UNIX-ksh)
sqlplus /nolog
connect / as sysdba,sysoper # locally with oracle OS account
connect user/pw as sysdba,sysoper # remote
sqlplus "/ as sysdba"

Older versions of Oracle:

Svrmgrl (UNIX) or svrmgr30 (NT)

CONNECT INTERNAL

Connect remote:

connect user/pw@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<IP or name>)(PORT=1521))

(CONNECT_DATA=(SID=<SID>)))

Without password file (remote_login_passwordfile = none in parameter file). The users SYS and SYSTEM must be members
of the UNIX group that has permissions on database (generally the group dba). This group must have been created before the
creation of the database. Connect with the command:

connect / as sysdba or connect / as sysoper

Restricted session:
alter system enable restricted session; --> restrict user access; see also startup
restrict
alter system disable restricted session; --> all users have access

Alternative to restricted session in 9i is quiescing the database, but this needs the Resource Manager:
alter system quiesce restricted;
alter system unquiesce;

http://www.cthullen.s5.com/
The password for "internal" should be changed: delete and recreate the password file (with entries = 5). 
Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE

In SQL*Plus:

DISCONNECT
Disconnect from current database
SET INSTANCE [ xxx | LOCAL ]
Set the instance for connecting
CONNECT username
Connect

Scripts for windows

echo off

SET ORACLE_HOME=C:\oracle\ora92
set ORACLE_SID=%1%
rem Display variables
set ORACLE_HOME
set ORACLE_SID

echo %DATE% - %TIME% : Shutting down (abort)... 

%ORACLE_HOME%\bin\oradim -SHUTDOWN -SID %ORACLE_SID% -SHUTMODE i 
echo %DATE% - %TIME% : Abort on %ORACLE_SID% done

or

echo %DATE% - %TIME% : Shutting down...

%ORACLE_HOME%\bin\oradim -SHUTDOWN -SID %ORACLE_SID% -SHUTMODE i 
echo %DATE% - %TIME% : Shutdown on %ORACLE_SID% done

or

echo %DATE% - %TIME% : starting...

%ORACLE_HOME%\bin\oradim -STARTUP -SID %ORACLE_SID% 
echo %DATE% - %TIME% : Startup on %ORACLE_SID% done

or

echo %DATE% - %TIME% : Executing file dba_panic.sql

echo Enter password for system
sqlplus system@%ORACLE_SID% @dba_panic.sql

Startup and Shutdown

Startup

SQLPLUS /NOLOG
either CONNECT / as { sysoper | sysdba }

http://www.cthullen.s5.com/
or     CONNECT username/password as { sysoper | sysdba } 
STARTUP [NOMOUNT | MOUNT | OPEN] [RESTRICT] [PFILE=path/init...ora]

Path for the spfile is /dbs or \database by default. 

The server looks for spfile$ORACLE_SID.ora, spfile.ora or init$ORACLE_SID.ora (in that order).

STARTUP RESTRICT 
Then later remove with:
ALTER SYSTEM DISABLE RESTRICTED SESSION;

ALTER DATABASE OPEN READ ONLY; 

Note that read-write is the default mode:
ALTER DATABASE OPEN READ WRITE;

Use STARTUP OPEN RECOVER to automatically start the recovery in needed.

 Nomount Mounted Open

startup nomount alter database mount alter database open

 Only the instance is started.  Datafiles are associated to the  Data files are opened
 Parameter file is read instance  Redo log files are opened
 SGA is allocated  Control files are opened and  If needed, SMON restores the
 Processes are started read instance, meaning that the redo
 Trace and alert files are log files are used to restore the
opened The datafiles may be renamed, archive data in the buffer cache:
 The DB_NAME is defined. options of the redo log may be defined o Roll the data forward
and a complete restore of the database that has not been stored
can be done. in the data files.
o Open the database.
o Rollback the non-
restored transactions

Shutdown

SHUTDOWN IMMEDIATE
Users are disconnected
SHUTDOWN ABORT;
Stop all immediately. This guarantees that committed transactions are on the disk (even if it is in the redo log files).
Not good for backup.
SHUTDOWN NORMAL
No new connections, wait until current users are disconnected
SHUTDOWN TRANSACTIONAL
Complete active transactions and users are disconnected
 

 Suspension: stops all I/O to files, use so as to allow backup.

ALTER SYSTEM SUSPEND ;

ALTER SYSTEM RESUME ;
SELECT DATABASE_STATUS FROM V$INSTANCE;

http://www.cthullen.s5.com/
ALTER SYSTEM QUIESCE RESTRICTED;
Place database in quiesced mode
ALTER SYSTEM UNQUIESCE;
Take out of quiesced mode
SELECT ACTIVE_STATE FROM V$INSTANCE;
NORMAL = unquiesced
QUIESCING = in progress to be quiesced, but there are still active non-DBA sessions running 
QUIESCED = no active non-DBA sessions are active or
allowed 

Database Basics

 And read this sometime: Creating an Oracle Database

The Oracle Server = 

  Instance (System Global Area + Processes): identified by ORACLE_SID
    + 
  Database (control files + data files + redo logs + other): identified by DB_NAME

A parallel server has several instances for one database (that is parallel server = multiple instances + database). The files are
shared for several instances. 
Oracle9i RAC: each node within the cluster has an instance referencing the database. The instance name = database name (see
DB_NAME parameter) + unique thread number that starts at 1.

SELECT instance AS oracle_sid FROM v$thread;

 
SELECT name AS db_name 
     , to_char ( created, 'DD/MM/YYYY HH24:MI') as created 
     , log_mode 
     , archive_change# 
     , controlfile_type -- STANDBY, CURRENT
     , open_mode -- READ ONLY, READ WRITE
  FROM v$database; 

SELECT instance_name 
     , host_name 
     , status -- STARTED (not mounted), MOUNTED, OPEN 
     , logins -- ALLOWED, RESTRICTED (ALTER SYSTEM DISABLE RESTRICTED SESSION) 
     , shutdown_pending -- Should be NO 
     , database_status -- Should be ACTIVE 
     , parallel -- NO 
     , archiver -- STARTED, STOPPED 
  FROM v$instance;

http://www.cthullen.s5.com/
 

Database

Global name = DB_NAME + DB_DOMAIN

Change the global name: ALTER DATABASE RENAME GLOBAL_NAME after changing DB_DOMAIN in parameter file. To
change DB_NAME: change in parameter file, then recreate the control files because the DB_NAME is in control files then
use ALTER DATABASE RENAME GLOBAL_NAME.

Various views for seeing objects, with xyz = to "user", "dba", or "all"

 xyz_TABLES: tables
 xyz_TAB_COMMENTS: comments on tables and views
 xyz_TAB_COLUMNS: Details on the columns
 xyz_COL_COMMENTS: Comments on the columns
 xyz_CONSTRAINTS: Gives constraints: primary key, foreign key, not null, check
 xyz_CONS_COLUMNS: Maps constraints to columns
 xyz_INDEXES: Indexes
 xyz_IND_COLUMNS: Indexes to columns.
 xyz_VIEWS: Views and text
 xyz_SYNONYMS: Synonyms
 xyz_SEQUENCES: Sequences
 xyz_TRIGGERS: Trigger and text
 xyz_SOURCE: Source code for all PL/SQL objects

http://www.cthullen.s5.com/
SGA, Memory and Processes

See parameters

 SGA (System Global Area)

SGA (System Global Area) = redo + Shared Pool

One SGA per database. Entirely in the memory.

 Main parameters affecting performance, but all must fit into the SGA. SGA maximum size set with:
ALTER SYSTEM SET sga_max_size=n SCOPE=SPFILE;

 Shared pool (set dynamically with SHARED_POOL_SIZE ). Contains:

o Library cache (SQL, PL/SQL text)
o Data Dictionary cache
o User Global Area (UGA): in some cases (shared server / multi-thread): stack space, user session data and
cursor state.
 Large pool (set dynamically with LARGE_POOL_SIZE): used for various large objects (that would otherwise go into
the shared pool)
 Java pool: size defined by JAVA_POOL_SIZE
 Buffer cache (set dynamically): DB_CACHE_SIZE in bytes not blocks (DB_BLOCK_BUFFER is obsolete)
 Log buffer: LOG_BUFFER (in bytes), contains information for rollback. This data is stored in the log buffer before
being written to the active online redo log file.. With a slow disk and a fast CPU, this buffer can fill before the data is
written to disk.
 Process-private memory (set dynamically)

http://www.cthullen.s5.com/
Other parameters:

 Sort area: size defined by SORT_AREA_SIZE



SGA 
= DB_KEEP_CACHE_SIZE      -- 
+ DB_RECYCLE_CACHE_SIZE   -- 
+ DB_nK_CACHE_SIZE (DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE ... DB_32K_CACHE_SIZE)
+ SHARED_POOL_SIZE (variable size)
+ LARGE_POOL_SIZE (variable size)
+ JAVA_POOL_SIZE (variable size)
+ DB_CACHE_SIZE           -- Buffer cache
+ LOG_BUFFER 
+ 1MB

SHOW SGA: show the memory used.

Within the "Variable Size", the breakdown show by views v$sga_dynamic_components and v$sga_dynamic_free_memory.
Add the javaPool and add ?.
For optimal performance in most systems, the entire SGA should fit in real memory, i.e. ideally, it should not be paged out to
disk.

The memory organized in granules of 4MB (for SGA up to 128MB) or 16MB.

New parameter SGA_MAX_SIZE.

 select 'total size of shared pool' as description

     , to_char (value/1024/1024, '9999D99') as MB
  from v$parameter
  where upper(name) = 'SHARED_POOL_SIZE'
UNION
SELECT 'unused portion' as description
     , to_char (bytes/1024/1024, '9999D99')
  from v$sgastat
  where pool = 'shared pool'
    and name ='free memory';

Shared pool size; unused portion is portion never used

Database Block Size

In 9i, DB_BLOCK_SIZE defines block size for SYSTEM tablespace and is the default for the other tablespaces. Up to four
other block sizes can be supported (but sub-caches must be configured). Values are a multiple of 2K (2K..32K). The black size
cannot be changed after database creation (except in the case of re-creation). Choose a block size larger than the operating
system block size (a multiple if possible). Generally, 2K-4K, 8-16K for data warehousing.

 Database Buffer Cache

The buffer cache size is set with DB_CACHE_SIZE (in bytes not blocks). Dynamic parameter. Note
that DB_BLOCK_BUFFER is obsolete.
In 8i: data duffer dache size defined by DB_BLOCK_BUFFERS * DB_BLOCK_SIZE

Two separate buffer pools exist: the KEEP buffer retains objects in memory, and the RECEYCLE buffer releases the blocks as
soon as they are no longer needed.

http://www.cthullen.s5.com/
Two lists handle the buffer cache: Least Recently Used (LRU) and a write list or dirty list (modified data not written to disk);
note that full table scans put the data at the end and not the top of the list. The blocks are in one of three states: (1) "free"
meaning that the block is a copy of what is on disk; (2) dirty or modified; (3) pinned or in use.

See the state of the buffer cache with: select * from v$buffer_pool;

If the buffer cache is large, more data is kept in memory, but memory is used up. 
Database buffer cache keeps most recently used data blocks in memory. Near 100% buffer hit ratio is good. Best if not below
70%. See hit ratio with:
column "Hit Ratio" format 999.99 
select (sum(decode(lower(name), 'consistent gets' , value, 0)) + 
        sum(decode(lower(name), 'db block gets'   , value, 0)) - 
        sum(decode(lower(name), 'physical reads'  , value, 0)) ) / 
       (sum(decode(lower(name), 'consistent gets' , value, 0)) + 
        sum(decode(lower(name), 'db block gets'   , value, 0)) ) * 100 "Hit Ratio" 
  from v$sysstat;

select (sum(decode(lower(name), 'consistent gets' , value, 0)) + 

        sum(decode(lower(name), 'db block gets'   , value, 0)) - 
        sum(decode(lower(name), 'physical reads direct'  , value, 0)) - 
        sum(decode(lower(name), 'physical reads direct (lob)'  , value, 0)) - 
        sum(decode(lower(name), 'physical reads'  , value, 0)) ) / 
       (sum(decode(lower(name), 'consistent gets' , value, 0)) + 
        0 - sum(decode(lower(name), 'physical reads direct'  , value, 0)) - 
        sum(decode(lower(name), 'physical reads direct (lob)'  , value, 0)) - 
        0 + sum(decode(lower(name), 'db block gets'   , value, 0)) ) * 100 "Hit
Ratio" 
  from v$sysstat;

 Shared Pool

Contains query plans, sql statements, packages and object information.

Flush with: alter system flush shared_pool;

 Parameters

DB_BLOCK_SIZE
The basic block size for the database. Cannot be changed after database creation. Best if a multiple of OS block size.
Generally: 4096 or 8192. For data warehouses, possibly even 16384. In 9i, several "non-standard" sizes are possible.
SGA_MAX_SIZE
New use for parameter in 9i. Not dynamic. All other memory parameters are dynamic. This maximum size limits the
fixed SGA, variable SGA, and redo log buffers.
DB_BLOCK_BUFFER
Obsolete, replaced by DB_CACHE_SIZE
DB_CACHE_SIZE
Size of the database buffer cache in bytes not blocks
DB_CACHE_ADVICE
?
DB_KEEP_CACHE_SIZE
Used for database buffer cache
DB_RECYCLE_CACHE_SIZE
Used for database buffer cache
DB_nk_CACHE_SIZE
Used for database buffer cache: define multiple non-standard block sizes in the buffer cache.
SHARED_POOL_SIZE
Dynamically set the size of the shared pool
LARGE_POOL_SIZE
Dynamically set the size of the large pool

http://www.cthullen.s5.com/
JAVA_POOL_SIZE
Dynamically set the size of the java pool
LOG_BUFFER
Size of redo log buffer
 
Typical Values for Memory (9i)
Oracle
Shared Buffer Large
Type Java Pool PGA Process Sort Area  
Pool Cache Pool
Size
Basic 32 24 0 0 16 40
Data warehouse 100 16 100 16 32 40
OLTP 32 16 32 16 16 40
Multi-purpose 32 32 32 16 25 40
Our experience 64 64-400 50-100 0-120M 2M

Some notes:

 The redo log buffer does not take up a lot of space and is therefore not shown in the table above. Possible value: 160K
(LOG_BUFFER).
 sort_area_retained_size=65K for sort area size of 2M
 sharted_pool_reserved_size=1M for shared_pool_size of 60M
 hash_area_size 4M

Views:

View of dynamic components of memory

column component format a12
select COMPONENT
     , current_size/1024/1024 as "CURRENT_SIZE (MB)"
     , min_size/1024/1024 as "MIN_SIZE (MB)"
     , max_size/1024/1024 as "MAX_SIZE (MB)"
     , OPER_COUNT
     , LAST_OPER_TYPE
     , LAST_OPER_MODE
     , LAST_OPER_TIME
     , granule_size/1024/1024 as "GRANULE_SIZE (MB)"
from V$SGA_DYNAMIC_COMPONENTS ;   

View current size of dynamic free memory

select current_size / 1024 / 1024 as "CURRENT_SIZE (MB)" 
from v$sga_dynamic_free_memory;

View SGA details

column mb format 999G999D999
select pool, name, 
bytes/1024/1024 as mb 
from v$sgastat;

http://www.cthullen.s5.com/
 

View total by pool

select pool, name
, bytes/1024/1024 as mb 
from v$sgastat 
where lower(name)='free memory' ;

 PGA (Process Global Area (?) )

V$PGASTAT
View of PGA statistics
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = n
Set the maximum amount of memory that the PGAs can use. This is a target that Oracle tries to honor by monitoring
the processes.
column used_mb format 999D999 
column alloc_mb format 999D999 
column max_mb format 999D999 
column freeable_mb format 999D999 
select program 
 , pga_used_mem / 1024 / 1024 as used_mb 
 , pga_alloc_mem / 1024 / 1024 as alloc_mb 
 , pga_max_mem / 1024 / 1024 as max_mb 
 , pga_freeable_mem / 1024 / 1024 as freeable_mb
from v$process;

Show details
A user process connects to the database via a PGA. The PGA is not shared as there is one PGA per process (connection or
backgroup process). The PGA contains:

 Memory for sorting

 Information for the session
 State of the cursors used by the session
 Stack space

Treatment of a Query

 Parsing, includes verfication of syntax, semantical analysis (indexes, ...) and creation of an execution plan. This is
done in the shared pool.
 Execution of the query
 Extraction and transfer of results (if there are results)

Treatment of DML (analysis: see above):

 The server process looks in cache for the data. It reads data from files if the data is not in the cache and puts it in the
cache buffer.
 The server process locks the data
 The server process copies the "before" and the "after" image to the redo log buffer.
 The server process saves the "before" image in the rollback block and updates the data block with the "after" image.

Treatment of a commit:

 Write to the redo log buffer with the SCN.

 The LGWR writes to the file
 The user is informed that the transaction is comitted
 The server process removes the locks
 The data is written later by the DBWR.

http://www.cthullen.s5.com/
 

General Notes on Files 

Files include control file (minimum 1), data files (minimum 1), redo logs (minimum 2 files), parameter file, password file,
archived log files.

Parameter file: All parameters are optional, as there are default values for every parameter. The parameters may be in any order.
Comments start with #. Parameters enclosed in double-quotes can include literal characters. Enclose multiple values in
parenthesis and separate by commas. Include additional files with the keyword IFILE. Remember to document the parameter
file name as this is not shown once the database is started.

Log files are in locations defined by parameters BACKGROUND_ DUMP_ DEST, USER_ DUMP_ DEST and CORE_
DUMP_ DEST. These directories are often called bdump, cdump, and udump and are located
in $ORACLE_BASE/$SID_NAME/admin. 

Dictionary views

 v$datafile
 v$tablespace
 v$logfile
 v$controlfile

Installation

Some notes for the installation of the Oracle software

Some documentation (do a search):

 Checklist on metalink
 quick start guide.htm
 Installation Guide

Pre-requisites

UNIX groups and users:

 OSDBA group
 APACHE group
 OSOPER group (optional)
 ORAINVENTORY group (optional)
 Oracle Software Owner
 APACHE account (APACHE account is a UNIX user account that owns the Oracle HTTP Server after installation.
Must be a member of orainventory group)

See file InstallPrep.sh . It is a script designed to check Server to ensure that it has adequate resources to successfully
Install the Oracle Database Software.

http://www.cthullen.s5.com/
Requirements (from Pre-Installation Requirements.htm):

 Check memory: /bin/vmstat -P | grep "Total Physical Memory"

 Amount of swap: /sbin/swapon -s
 At least 3.5 GB + 1Gb for seed database.
 400 MB of space in the /tmp directory
 Tru64 5.1 or 5.1A
 System parameters: /sbin/sysconfig -q ipc 
ipc:  shm_max = 4278190080
      shm_mni = 256
      shm_seg = 128
proc: per_proc_stack_size = 33554432
      per_proc_data_size = 201326592 
vm:   new_wire_method = 0
 Create groups
 Create orainstall directory
 Set path: $ORACLE_HOME/bin 
(also must have: /usr/bin, /etc, /usr/bin/X11, /usr/local/bin )
 set ORACLE_HOME, ORACLE_BASE, DISPLAY
 Set umask 022 in .profile
 Test that X11 runs: /usr/bin/X11/xclock

Just before installing, check the following again:

 set ORACLE_HOME, ORACLE_BASE, DISPLAY

 export ORACLE_HOME=/u00/app/oracle/product/9.2.0.1
 export PATH=/u00/app/oracle/product/9.2.0.1/bin:$PATH
 Set umask 022 in .profile
 backup ORACLE INVENTORY directory (location in /var/opt/oracle/oraInventory.loc)

Log of installation is in oraInventory/logs/InstallActionsyyy-mm-dd-hh-mm...

Look at the end of the file

Backup the root.sh file

Enable the rollback segments (see init parameters)

Note: Location of the oraInventory directory is indicated by a file whose location depends on the
OS: /etc/oraInst.loc (AIX) or /var/opt/oracle/oraInst.loc (HP, Linux, Solaris, and Tru64).

default group for ownership of the oraInventory directory is the ORAINVENTORY group.

Check existing components either by running opatch lsinventory –detail (I did not get this to work) or by
launching the Installer.

 See Apache information in: 

$ORACLE_HOME/Apache/Apache/setupinfo.txt :

The HTTP Server can be accessed using the following URLs:

Non SSL Mode (executed at install time): http://server12:7777
SSL mode: http://server12:80
SSL mode: https://server12:443

http://www.cthullen.s5.com/
Start Apache with: 
$ORACLE_HOME/Apache/Apache/bin/apachectl start

Oracle Enterprise Manager: 

$ORACLE_HOME/bin/oemapp console oem.LoginMode=standalone

Automatic startup and shutdown:

 SIDs are defined in /etc/oratab (or /var/opt/oracle/oratab )

 ORACLE_SID:ORACLE_HOME:{Y|N} 
DB_NAME:ORACLE_HOME:{Y|N} (for Oracle Real Application Clusters) 
Y or N for automatic startup
 Create a script in /sbin/init.d for automatic startup. See post-installation documentation.

Note scripts in $ORACLE_HOME/bin :

 dbca: configuration assistant, needs X11 (see below)

 dbhome SID --> returns the oracle home for the given sid
 dbshut --> shutdown all databases. Could use this as basis for creating my own script
 dbstart --> startup all databases with "Y" in oratab
 dbsnmpwd --> start and keep agent alive
 dbua
 /usr/local/bin/oraenv : useful for setting the variables ORACLE_HOME, ORACLE_SID, PATH

Scripts: modifications to be done still

 set the path (and other env variables?)

 maybe: remove the specific listeners for the databases
 remove any use of scripts in local/dba/bin

Set these variables

 ORACLE_HOME = $ORACLE_BASE/product/release
 CLASSPATH (for java)
 LD_LIBRARY_PATH
 SHLIB_PATH = $ORACLE_HOME/lib32
 ORACLE_SID
 Specifics: LIBPATH for AIX, LD_LIBRARY_PATH_64 for Solaris, TWO_TASK if needed
 PATH
 TNS_ADMIN

For OID

see ldap_notes.html

Oracle network

Reserve the port for the listener(s) in the /etc/services file (user root):

listener_name 1521/tcp       #Oracle Net listener

See notes for net8

Databases

Run @$ORACLE_HOME/rdbms/admin/utlrp.sql script after creating, upgrading, or migrating a database. This script

recompiles all PL/SQL modules (CONNECT SYS/PASSWORD AS SYSDBA)

http://www.cthullen.s5.com/
 

Database Creation

See database creation scripts

Naming conventions

 Database names have a maximum of 8 characters. Use D=development, T=test, P=production. Note: no underscore "_"
(9i on Windows).
 Datafiles: <DB-NAME>_<ABBREV>_01.dbf where ABBREV takes values DATA, INDX, RBS, SYSTEM,
TEMP, etc
 Tables are entities, so put names in plural. As short as possible, but remain meaningful.
 Column names: foreign keys have abbrev of table referenced. Primary key too: personal preference (so that primary
key column and foreign key column have the same name).
 Redo logs: use "LOG" in name, member 1 is A, member 2 is B.
 Index: PK=primary key index, UI=unique index, FK=foreign key index, LI=lookup index.
 Prefix views with V_
 Constraints: use PK=primary key, FK=foreign key, UV=unique value, CK=check constraint, NN=not null.
 Use "SEQ" in the name of sequences.

Preliminary checks

 Must have operating system privileges

 Consider file locations according to OFA (Optimal Flexible Architecture)
 Define location and calculate the disk space for
o Data files: put spread data, indexes and temporary over separate disks
o Control files: at least 2 in different locations
o Redo logs: create several members in different locations
 Memory must be sufficient for SGA, Oracle executable and Oracle processes   

Tool for database creation: $ORACLE_HOME/bin/dbca

Note that to create scripts for a new database, you have to choose "New database" (without the creation of database files).

Questions to ask before starting:

 What will SID and GLOBAL_NAME be (the first part of the GLOBAL_NAME being the SID) ?

 Which options do I want to install (spatial, ultra search, label security, data mining, OLAP, example schemas)
 Do I disable the standard database features: JVM, Intermedia, Oracle Text, XML DB?
 Dedicated Server Mode (resources allocated for each connection) or Shared Server Mode (this implies additional
configurations)?
 Memory allocation (see memory 9i)
 (Database) character set: the default is WE8ISO8859P1, unicode is AL32UTF8. See National Language Support
 National caracter set: utf8 (AL16UTF16)
 Database block size (the basic block size); cannot be changed after database creation. Generally: 2048, 4096 or 8192.
For data warehouses, possibly even 16'384.
 Sort area size (example: 2 MB)
 File locations. Typically:
o 3 different locations for control files and redo logs: main disk, two non-system disks;
o /dbs for spfile, etc
o data files on separate non-system disks
o Location for archived logs, on a disk that does not contain data
   

http://www.cthullen.s5.com/
Creation

 Backup existing databases.

 Create the directories for
o Data files (if possible over several disks)
o Control files in at least 2 in different locations
o Redo logs in different locations
o bdump, cdump, create, pfile, udump
 Environment variables (Korn: ORACLE_SID=PRD; export ORACLE_SID; Cshell setenv ORACEL_SID PRD.. In
NT in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. In NT, do set ORACLE_SID=PRD).
o ORACLE_HOME = /mount_point/app/oracle/product
o ORACLE_SID (max 8 char)
o ORACLE_BASE = /mount_point/app/oracle
o ORA_NLS33 = WE8ISO8859P1 (see v$nls_parameters)
 Include $ORACLE_HOME/bin in the path
 Archive log format: {DB_NAME}_%s_%t.arc
 Copy init file from sample and name initSID.ora
o DB_NAME is same name as in CREATE DATABASE. May be different from name of instance (in variable
ORACLE_SID). DB_NAME cannot be changed. No more than 8 characters
o DB_BLOCK_SIZE: 2048, 4096 or 8192
o DB_DOMAIN: name of the machine
o DB_BLOCK_BUFFERS
o PROCESSES
o ROLLBACK_SEGMENTS
o CONTROL_FILES
o You should also edit the appropriate license parameter(s): LICENSE_MAX_SESSIONS,
LICENSE_SESSION_WARNING, LICENSE_MAX_USERS
o ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/initSID.ora initSID.ora
 Create the password file (NT): ORADIM80 -NEW -SID sid -INTPWD password -STARTMODE auto -PFILE
ORACLE_HOME\DATABASE\initsid.ora. This creates the service and the password file.
 Create the password file (Unix): 
export ORACLE_SID=SID_NAME (UNIX-ksh) 
orapwd file=$ORACLE_HOME/dbs/orapwSID password=! entries=5 (UNIX Tru64)
 Connect to svrmgr as dba using the authentication of the operating system or using the password file. 
In more recent databases: SQLPLUS /nolog then CONNECT SYS/password AS SYSDBA. See section
"connecting"
 Start up the instance STARTUP NOMOUNT PFILE=initSID.ora
 Start spooling
 Create the database using the command CREATE DATABASE. 
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY glu USER SYSTEM IDENTIFIED BY glu -- not mandatory
LOGFILE GROUP 1 ('.../redo01.log', '.../redo01.log') SIZE 100M,
        GROUP 2 ('.../redo02.log', '.../redo02.log') SIZE 100M,
        GROUP 3 ('.../redo03.log', '.../redo03.log') SIZE 100M
MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 -- parameters for redo logs
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P15 -- Put correct value. See National Language Support
NATIONAL CHARACTER SET WE8ISO8859P15 -- Put correct value
DATAFILE '.../system01.dbf' SIZE 325M REUSE AUTOEXTEND ON EXTENT MANAGEMENT
LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1 DATAFILE '.../temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE undotbs DATAFILE '.../undotbs01.dbf' 
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

http://www.cthullen.s5.com/
  Run additional scripts (spool file first). See more details below. For 8i: 
connect internal 
@$ORACLE_HOME/rdbms/admin/catalog.sql; -- essential
@$ORACLE_HOME/rdbms/admin/catproc.sql -- essential
@$ORACLE_HOME/rdbms/admin/caths.sql 
connect system/manager 
@$ORACLE_HOME/sqlplus/admin/pupbld.sql
 Create tablespaces (see tablespaces): 
CREATE TABLESPACE tbsp DATAFILE '.../tools01.dbf' 
SIZE 12M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
 For 8i, create rollback segments:
CREATE ROLLBACK SEGMENT DUMMY TABLESPACE SYSTEM
    STORAGE (INITIAL 512K NEXT 512K OPTIMAL 4096K MINEXTENTS 8 MAXEXTENTS
4096);
ALTER ROLLBACK SEGMENT "DUMMY" ONLINE; 
CREATE ROLLBACK SEGMENT RBS0 TABLESPACE RBS 
    STORAGE (INITIAL 512K NEXT 512K OPTIMAL 4096K MINEXTENTS 8 MAXEXTENTS
4096);
ALTER ROLLBACK SEGMENT "RBS0" ONLINE;
ALTER ROLLBACK SEGMENT "DUMMY" OFFLINE; -- Keep off-line
 To drop a database, simply delete the files. In NT, remove the services too.
 In NT, the default database is defined by the variable ORACLE_SID in the registry.
 For creating a database in NT, use the script build_db.sql under %ORACLE_HOME%\rdbms80\admin or simply use
the Oracle Database Assistant
 In case of errors, close the database and delete the database files. Common errors are:
o Errors in the scripts
o Files exist already
o Operating system errors such as file or directory permissions
o Operating system errors such as insufficient memory
 After database creation (sql.bsq executed with success), the following objects exist (view with V$LOGFILE,
V$CONTROLFILE and V$DATAFILE): data files for tablespace SYSTEM, control files, redo log files, rollback
segments, internal tables (but without the views on the data dictionary)
 Create the SPFILE as SYS, then shutdown and startup (9i): CREATE SPFILE='.../dbs/spfileSID.ora'
FROM PFILE='.../initSID.ora';
 See default passwords in section "connecting"
 Backup the database !
 Add the database SID to /etc/oratab, listener.ora, tnsnames.ora files.

Scripts:

 $ORACLE_HOME/rdbms/admin/catalog.sql: commonly used data dictionary views.

 $ORACLE_HOME/rdbms/admin/catproc.sql: build the Oracle utilities including PL/SQL.
 Some usefule scripts
o catblock.sql: views of Oracle locks.
o catsnmp.sql: creates an SNMPAgent role to access the v$ tables.

Undo management (locally managed, with a uniform extent size). Several undo tablespaces may exist, but only one is active
(alter system set undo_tablespace).
CREATE UNDO TABLESPACE whatever DATAFILE ‘...whatever01.dbf’ SIZE 50M; 
ALTER SYSTEM SET UNDO_TABLESPACE=whatever; 
New views: V$UNDOSTAT and DBA_UNDO_EXTENTS. 
See undo tablespaces 

Example for 8i:

http://www.cthullen.s5.com/
set echo on
set trimspool on
spool $ORACLE_BASE/.../create_db.log

CREATE DATABASE DB8I

LOGFILE GROUP 1 ('.../DB8I/redo01.log', '.../DB8I/redo01.log') SIZE 10M,
GROUP 2 ('.../DB8I/redo02.log', '.../DB8I/redo02.log') SIZE 10M,
GROUP 3 ('.../DB8I/redo03.log', '.../DB8I/redo03.log') SIZE 10M
MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 -- parameters for redo logs
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1 
NATIONAL CHARACTER SET WE8ISO8859P1 
DATAFILE '.../system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 2048M ;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '.../DB8I/temp01.dbf' 

SIZE 500M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

CREATE TABLESPACE RBS DATAFILE '.../rbs01.dbf' SIZE 100M REUSE

AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

CREATE TABLESPACE USERS DATAFILE '.../users01.dbf' SIZE 25M REUSE

AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;

CREATE TABLESPACE INDX DATAFILE '.../indx01.dbf' SIZE 12M REUSE

AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE ROLLBACK SEGMENT DUMMY TABLESPACE SYSTEM

STORAGE (INITIAL 512K NEXT 512K OPTIMAL 4096K MINEXTENTS 8 MAXEXTENTS 4096);
ALTER ROLLBACK SEGMENT "DUMMY" ONLINE; 
CREATE ROLLBACK SEGMENT RBS0 TABLESPACE RBS 
STORAGE (INITIAL 512K NEXT 512K OPTIMAL 4096K MINEXTENTS 8 MAXEXTENTS 4096);
ALTER ROLLBACK SEGMENT "RBS0" ONLINE;
ALTER ROLLBACK SEGMENT "DUMMY" OFFLINE; -- Keep off-line

spool off

@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;

Example for 9i (this works but is not optimal):

set echo on
set trimspool on

spool $ORACLE_BASE/.../create_db.log

-- create database
CREATE DATABASE db9i
USER SYS IDENTIFIED BY change_on_install USER SYSTEM IDENTIFIED BY change_on_install 
LOGFILE GROUP 1 ('.../db9i/redo01.log', '.../db9i/redo01.log') SIZE 10M,
GROUP 2 ('.../db9i/redo02.log', '.../db9i/redo02.log') SIZE 10M,
GROUP 3 ('.../db9i/redo03.log', '.../db9i/redo03.log') SIZE 10M
MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 -- parameters for redo logs
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8 

http://www.cthullen.s5.com/
NATIONAL CHARACTER SET AL16UTF16 
DATAFILE '.../db9i/system01.dbf' SIZE 325M REUSE AUTOEXTEND ON EXTENT MANAGEMENT
LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '.../db9i/temp01.dbf' 
SIZE 200M REUSE AUTOEXTEND OFF UNIFORM SIZE 2M
UNDO TABLESPACE undotbs DATAFILE '.../db9i/undo01.dbf' 
SIZE 200M REUSE AUTOEXTEND OFF ;

CREATE TABLESPACE USERS LOGGING DATAFILE '.../db9i/users01.dbf' SIZE 25M REUSE 

AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;

CREATE TABLESPACE INDX LOGGING DATAFILE '.../db9i/indx01.dbf' SIZE 12M REUSE

AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

spool off

@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;       

Example of initial pfile for 9i (transform into spfile after creation).

This needs work as many parameters are not optimal:

db_name = "db9i"
instance_name = db9i 
service_names = db9i
control_files = "/u00/.../db9i/control01.ctl"
control_files = "/u01/.../db9i/control02.ctl" 
control_files = "/u02/.../db9i/control03.ctl"
open_cursors = 500
max_enabled_roles = 30
shared_pool_size = 66104524
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 500
log_buffer = 163840 
audit_trail = none 
timed_statistics = true 
max_dump_file_size = 10000 
log_archive_start = true
log_archive_dest_1 = "location=/.../db9i/arch MANDATORY"
log_archive_format = arcdb9i_%s_%t.arc 
UNDO_MANAGEMENT=AUTO
background_dump_dest = .../db9i/bdump
core_dump_dest = .../db9i/cdump
user_dump_dest = .../db9i/udump
DB_BLOCK_SIZE = 8192 
#or: DB_BLOCK_SIZE=4096 
remote_login_passwordfile = exclusive 
os_authent_prefix = ""
compatible = "9.2.0"
sort_area_size = 2097152
sort_area_retained_size = 65536
optimizer_index_caching = 50
optimizer_index_cost_adj = 10
job_queue_processes = 4      

http://www.cthullen.s5.com/
 

See database creation scripts

Copy a Database

Copy database from one machine to another, but with the same DB_NAME. 
To change the DB_NAME, see clone a database. To move a database, see move a database.

First copy:

 Files in hot backup mode

 Archived logs
 Redo logs
 Control files
 Initialization file

Do the following:

 Copy the more recent control file onto the control file from the clone.
 Create password file
 Configure the listener (file listener.ora in network/admin)
 Open database in mount and recover database

Clone a Database

Make a copy of the database and give it a different name. 

For a simple copy of a database from one machine to another, see copy a database. 
To move a database, see move a database.

 Creation of new control file: 

Connect / as sysdba
alter database backup controlfile to trace;
 Edit the trace file in user_dump_dest and create a script file "new_ctrl_file.sql". Put the file in admin/SID/create:
o Replace the "#" with "- -" (   :%s/^#/--/   )
o Change database name, add SET, put RESETLOGS (without NO)
old: CREATE CONTROLFILE REUSE     DATABASE "glu"    NORESETLOGS
[NO]ARCHIVELOG
new: CREATE CONTROLFILE REUSE SET DATABASE "new_glu"  RESETLOGS
[NO]ARCHIVELOG
o Change all the directories
o Comment out "RECOVER DATABASE"
o Add RESETLOGS so that the last line reads: 
ALTER DATABASE OPEN RESETLOGS;
o If a recovery has to be done, then comment out the last two lines (open statement and tempfile statement).
o The result should look like this:
STARTUP NOMOUNT 
CREATE CONTROLFILE REUSE SET DATABASE "new-SID" RESETLOGS [NO]ARCHIVELOG 
    MAXLOGFILES 32 
    MAXLOGMEMBERS 2 
    MAXDATAFILES 254 

http://www.cthullen.s5.com/
     MAXINSTANCES 8 
    MAXLOGHISTORY 907 
    LOGFILE 
      GROUP 1 ( '...', '...') SIZE 20M, 
      GROUP 2 ( '...', '...') SIZE 20M, 
      GROUP 3 ( '...', '...') SIZE 20M    
    DATAFILE '...', '...'
    CHARACTER SET WE8ISO8859P15;
ALTER DATABASE OPEN RESETLOGS; -- Comment out in case of recovery
ALTER TABLESPACE TEMP ADD TEMPFILE '...' REUSE; -- Comment out in case of
recovery
 If the clone is a new database, then prepare the "infrastructure":
o Add new SID to oratab, tnsnames.ora, listener.ora...
o Create sub-directories in $ORACLE_BASE/admin (arch, bdump, cdump, pfile, udump)
o Create directories for data files and archived log files
o Create the password file (see chapter connecting)
o Copy the initSID.ora parameter file, rename the file and edit (database name and file locations)
o Create link to initSID.ora in $ORACLE_HOME/dbs
ln -s $ORACLE_BASE/admin/$ORACLE_SID/pfile/initSID.ora initSID.ora
o Add the new database to the backup scripts
 Stop the database (if changing the name or if no-archivemode)
 Copy all the datafiles (put in hot backup mode if running)
 Connect internal with new ORACLE_SID.
 Run the file "$ORACLE_BASE/admin/SID/create/new_ctrl_file.sql"
 In case of recovery, the last two lines of new_ctrl_file.sql should not be executed. Execute the following manually:
o recover database using backup controlfile until cancel;
o Note that the archived log files have the old database name in them; they are located in the sub-directory of
the old database
o ALTER DATABASE OPEN RESETLOGS;
o ALTER TABLESPACE TEMP ADD TEMPFILE '...' REUSE;
 Restart listeners and OEM agent (if new database).
 Change the database name with: 
ALTER DATABASE RENAME GLOBAL_NAME TO "<newdb_name>.<domain>"

March 2003: changed name. Cloned with recover.

Move a Database

For a simple copy of a database from one machine to another, see copy a database. 
To change the DB_NAME, see clone a database.

 Creation of new control file: 

Connect / as sysdba
alter database backup controlfile to trace;
 Edit the trace file in user_dump_dest and create a script file "new_ctrl_file.sql":
o Replace the "#" with "- -"
o Change all the directories
 Shutdown
 Move all the datafiles
 Edit the initSID.ora parameter file (file locations)
 Change link to initSID.ora in $ORACLE_HOME/dbs
 Connect internal with new ORACLE_SID.
 Run the file "new_ctrl_file.sql"

http://www.cthullen.s5.com/
Or move all the files:

In SQL*Plus OS
create pfile='...' from spfile; Edit the pfile (it is not necessary to remove the "*.")
Edit temporary pfile and modify parameters: Move the bdump, cdump, udump sub-directories to the
  background_dump_dest='...' new location.
  core_dump_dest='...'
  user_dump_dest='...' Also move pfile and create.
Edit temporary pfile and modify parameters: 
  log_archive_dest_1 = '...'  Move the archived logs
  standby_archive_dest = '...'
Edit temporary pfile and modify parameter:
  control_files = '...' 
  control_files = '...' Move the control files
  control_files = '...'
startup mount pfile='...';  
create spfile from pfile='...'; Change the link for the spfile before creating spfile.
alter database rename file '...' to '...'; Move the files before renaming
alter database drop logfile group 1; 
alter database add logfile group 1 
   ('...', '...') size 100M reuse;
Copy the redo files before renaming
or (use this option for current log file):
alter database rename file '...' to '...'; 
alter database add logfile member '...' to
group 3;
alter database open;  
alter database tempfile '...' drop; 
      -- Drop the tempfile, NOT the tablespace
alter tablespace tmp_tbsp add tempfile '...'  Copy the temporary files before hand
    size 20M reuse;

DBA Tasks

Daily

DB running ? 
Also DBSNMP ?

1. Alert log entries ? Note in Database_Recovery_log (text file)

2. Backup OK ? 
Backup of database
Backup to tape

http://www.cthullen.s5.com/
 3. Verifications:
Tablespaces (dba_datafiles.sql) 
Rollback segments (dba_rollback_segs.sql): segments should be online. Offline for specially created segments. 
Look for bad growth projections (daily_01.sql and nr_extents.sql). See table and index sizing, look at trends 
Check space-bound objects (next-extent > largest available): see dba_no_extend.sql 
Review contention for CPU, memory, network and disk resources.
4. To analyze tables and indexes: 
BEGIN 
   dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ; 
END ; 
/

Weekly

1. Consistent Objects: NEXT_EXTENT of objects should match default NEXT_EXTENT 

  SELECT segment_name, segment_type, dt.tablespace_name, ds.next_extent 
    FROM dba_tablespaces dt, dba_segments ds 
    WHERE dt.tablespace_name = ds.tablespace_name 
      AND dt.next_extent !=ds.next_extent; 

Check existing extents 

  SELECT count(*), segment_name, segment_type, dt.tablespace_name 
    FROM dba_tablespaces dt, dba_extents dx 
    WHERE dt.tablespace_name = dx.tablespace_name  
      AND dt.next_extent != dx.bytes 
  GROUP BY segment_name, segment_type, dt.tablespace_name;

2. Primary keys 
Tables without primary keys 
  SELECT owner, table_name 
    FROM dba_tables 
  MINUS 
  SELECT owner, table_name 
    FROM dba_constraints 
    WHERE constraint_type = 'P'; 

Disabled primary keys 

  SELECT owner, constraint_name, table_name, status 
    FROM dba_constraints 
    WHERE status = 'DISABLED' 
       AND constraint_type = 'P'; 

Primary keys are unique 

  SELECT owner, index_name, table_name, uniqueness 
    FROM dba_indexes 
    WHERE uniqueness = 'NONUNIQUE';

3. Indexes 
All indexes should be in separate table space
4. Compare environments: the differences between test and production environments should be explained 
  SELECT table_name, column_name, data_type, data_length, data_precision,
data_scale, nullable 
    FROM all_tab_columns -- first environment 
    WHERE owner = '&OWNER' 
  MINUS 
  SELECT table_name, column_name, data_type, data_length, data_precision,
data_scale, nullable 

http://www.cthullen.s5.com/
     FROM all_tab_columns@&my_db_link -- second environment 
    WHERE owner = '&OWNER2' 
  order by table_name, column_name;
5. Check security policy violations
6. Check network problems (SQLnet logs on client and server sides)

7. Clean up logs:
o alert log in bdump
o other logs in bdump
o trace files in cdump
o trace files in udump
8. Archive server logs

Monthly

1. Anticipate problems with growing: compare segment growth reports to identify potential problems
2. Review tuning oppurtunities
3. I/O contention: compare previous reports to see trends
4. Fragmentation (row chaining)
5. Project performance into the future
6. Tuning and
maintenance 

Initialization Parameters

Initialization parameters stored in init<SID>.ora file. Generally in directory ".../admin/pfile/". A link should be made
from "$ORACLE_HOME/dbs/init<SID>.ora" to ".../admin/pfile/init<SID>.ora". In Unix: 
ln -s /.../admin/pfile/init<SID>.ora $ORACLE_HOME/dbs/init<SID>.ora

Starting with 9i, Oracle uses a "Server Parameter File" (SPFILE).

By default at startup, the database looks in "$ORACLE_HOME/dbs" (windows: "%ORACLE_HOME%\database") for:

 spfile "spfileSID.ora"
 then "spfile.ora"
 then the init file "initSID.ora".

Note that windows stores the pfile for automatic startup of database in the registry: so put the following line in the pfile: 
spfile=...

CREATE SPFILE[='...'] FROM PFILE='/u01/oracle/dbs/init.ora';

Create the spfile with "create spfile" command. The default file name is "spfile<SID>.ora". The default location is
platform specific.
CREATE PFILE='...' FROM SPFILE[='...'];
Create pfile from spfile; use this command to backup the spfile. Works in no-mount. Do this regularly.
STARTUP PFILE=...
Startup using a pfile. But "startup pfile=...spfile" does not work. Instead, create an initSID.ora file with
the line "SPFILE=..."
ALTER SYSTEM SET param=value COMMENT='...' SCOPE={ SPFILE | MEMORY | BOTH };
Modify parameters. Scope: stores change in memory or in spfile or in both. Static parameters are stored in spfile and
applied at next startup, with scope=spfile.
ALTER SYSTEM SET parameter = '';

http://www.cthullen.s5.com/
 Return parameter to default value (N.B. is it set or reset??)
BACKGROUND_DUMP_DEST
Location of background process trace files and the alert log; generally .../bdump
USER_DUMP_DEST
Location of user trace files, generally .../udump
MAX_DUMP_FILE_SIZE
Maximum size of user trace files, in OS blocks.

View parameters:

OEM
Oracle Enterprise Manager
SHOW PARAMETERS
Current parameter values

SELECT * FROM V$PARAMETER or V$PARAMETER2

View of current parameter values

SELECT * FROM V$SPPARAMETER

Contents of server parameter file. NULL --> spfile not used by instance.

Example: spfile name

SHOW PARAMETER SPFILE

See current spfile in use (SQL*Plus)

SELECT name, value FROM v$parameter WHERE name = 'spfile';

spfile used at startup is in v$parameter.

Explanation of some parameters:

DB_NAME, DB_DOMAIN, SGA_MAX_SIZE, etc

See "Database Basics"

PROCESSES=N where N=#users+5

initialization parameter: maximum number of operating system processes connected concurrently. Number of
concurrent users + a minumum of 5 for background processes.

UNDO_MANAGEMENT = AUTO
Undo is stored in an undo tablespace (see undo tablespaces)

UNDO_MANAGEMENT = MANUAL
Undo is stored in rollback segments <-- backward compatibility

ROLLBACK_SEGMENTS
List of non-system rollback for manual undo management mode (i.e. without an undo tablespace).

rollback_segments = (r0, r1, r2, r3)

Rollback segments are put online automatically

O7_DICTIONARY_ACCESSIBILITY
If false, then sys must connect as sysdba or sysoper
SPFILE
The spfile used at startup (9i)

FAST_START_MTTR_TARGET

http://www.cthullen.s5.com/
 Set a target for fast instance recovery, i.e. flush buffers to disk so that there is less redo logs to apply. Note the
column CKPT_BLOCK_WRITES in the view V$INSTANCE_RECOVERY. If this value is high,
thenFAST_START_MTTR_TARGET is probably too low.

DB_CREATE_FILE_DEST
Default location of new files to be created. Naming conventions are (%u = 8 character string guaranteeing uniqueness,
%g = group n°, %t = tablespace name)
Control files: ora_%u.ctl
Log Files: ora_%g_%u.log
Data Files: ora_%t_%u.dbf
Temp Files: ora_%t_%u.tmp

DB_CREATE_ONLINE_LOG_DEST_n
Default location of online log files and control files.
_SYSTEM_TRIG_ENABLED = TRUE
Set normally to TRUE. Reset to FALSE only for installing and upgrading
 Parameters for memory:
see Memory 

Control Files

Best view: 
select name from v$controlfile;

See control file records:

select type, record_size, records_total, records_used 
  from v$controlfile_record_section;

Location of control files defined in init.ora with parameter (the only parameter that may be repeated in parameter file):
  control_files = ...

ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'xyz' [REUSE];

Create a control file for a standby database.

ALTER DATABASE BACKUP CONTROLFILE TO 'xyz' [REUSE];

Create extra copy of control file

ALTER DATABASE BACKUP CONTROLFILE TRACE [NO]RESETLOGS;

Create backup control file with SQL script. Add RESETLOGS option to override default.

Redo Log Files

alter system switch logfile;

Switch to next redo log

alter database add logfile ('log file 1', 'log file 2') size 5M [REUSE];
Add another log file. Note that the new files are "invalid" until they are used for the first time

alter database add LOGFILE GROUP 1 ('log file member 1', 'log file member 2') SIZE
20M [REUSE];
Add a group

alter database add logfile member 

    'log file member 2' to group 1, 
    'log file member 2' to group 2 ;
Add a second member to the existing log files

http://www.cthullen.s5.com/
alter database drop logfile member 'log file member 2';
Remove member from the existing log files (will refuse if in currently used redo log file, so do for all except the
current log file then switch)

alter database drop logfile group n;

Drop a group

ALTER DATABASE RENAME FILE '.../redo0n.log' TO 'new.../redo0n.log';

Rename log files when database is mounted.

Change log file sizes:

 Add a logfile group (there must be always at least three groups)

  alter database add LOGFILE GROUP 4 ('log file member 1', 'log file member 2')
SIZE 20M REUSE;
 Drop and re-create each group:
  alter database add LOGFILE GROUP 1 ('log file member 1', 'log file member 2')
SIZE 20M REUSE; 
  alter database drop logfile group n;
 Swith log files when necessary:
  alter system archive log current;
 Drop the last group that was created temporarily
  alter database drop logfile group n;

Views: 
SELECT GROUP#, 
       MEMBERS, -- Number of members in the group 
       -- THREAD#, 
       SEQUENCE#, 
       DECODE(STATUS, 'CURRENT', '<--', ' ') AS W_STATUS, 
       STATUS, 
       ARCHIVED, 
       DECODE(ARCHIVED, 'NO', '<--', ' ') AS W_ARCHIVED, 
       BYTES / 1024 as kbytes 
  FROM V$LOG
  ORDER BY GROUP# ; 

SELECT GROUP#, 
       STATUS, 
       SUBSTR (MEMBER, 1, 50) AS MEMBER 
  FROM V$LOGFILE;
SELECT GROUPS, 
       CURRENT_GROUP#, 
       SEQUENCE#
  FROM V$THREAD;

Nice summary:
break on group# skip 1 dup 
select l.group#, 
       l.sequence#, 
       l.bytes / 1024 /1024 as MBytes, 
       l.bytes / 1024 as KBytes, 
       l.status,
       --l.members, 
        f.status, 
       substr( f.member, 1, 200) as member 
  from v$log l , 

http://www.cthullen.s5.com/
       v$logfile f 
  where l.group# = f.group# 
  order by l.group#; 

clear break

Initialization parameter DB_BLOCK_CHECKSUM = TRUE --> enable redo log block checking (default value
of DB_BLOCK_CHECKSUM is FALSE). (Check this)

If a redo log block is corrupted in all members of a group, archiving stops. Eventually all the redo logs become filled and
database activity is halted until archiving can resume. Clear the corrupted redo logs and avoid archiving them (but backup the
database!): 
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Situation of resetlogs: 

select NAME -- db name

     , CREATED -- creation date 
     , OPEN_RESETLOGS -- next database open allows or requires the resetlogs option 
     , RESETLOGS_CHANGE# 
     , to_char ( RESETLOGS_TIME , 'DD/MM/YYYY HH24:MI:SS') as resetlogs_time
     , PRIOR_RESETLOGS_CHANGE#
     , to_char ( PRIOR_RESETLOGS_TIME , 'DD/MM/YYYY HH24:MI:SS') as
prior_resetlogs_time
  from v$database;

The log files are too small if the error "thread 1 cannot allocate new log, sequence xxx. Checkpoint not complete" appears
often.

States of redo log files:

 UNUSED: never written to.

 CURRENT: log currently being used. Needed for instance recovery.
 ACTIVE: It is also needed for instance recovery.
 CLEARING: being re-created after an ALTER DATABASE CLEAR LOGFILE statement.
 CLEARING_CURRENT: being cleared of a closed thread.
 INACTIVE: no longer needed for instance recovery, but may be in use for media recovery. May or may not be
archived.

Database Links

create database link prd1 connect to user_name identified by password using 'connect-
string';
The connect string is something like "prd1.world". If it is for the current user then the "identified by…" is not
necessary. To view the data, add @ (without the spaces around the @). For functions, add the "@dblink" after the
function name. Example: select * from parameters@prd1;
drop database link prd1;
Drop the database link
ALTER SESSION CLOSE DATABASE LINK prd1;
To close the session on the remote database explicitely, use the command
See SQL reference in the Oracle Doc. 
Note: the Oracle parameters "open_links" and "open_links_per_instance" are set to 4 by default.
 
GLOBAL_NAMES

http://www.cthullen.s5.com/
 Initialization parameter. If set to TRUE, then the name fo the database link must have the same name as the remote
database. If set to FALSE, then there is no restrictions on naming.

Check this section 

Languages

Views:

select * from GV$NLS_PARAMETERS ;

?
select * from GV$NLS_VALID_VALUES ;
List of valid values

select * from NLS_DATABASE_PARAMETERS ;

The database parameters

select * from NLS_INSTANCE_PARAMETERS ;

Instance parameters

select * from NLS_SESSION_PARAMETERS ;

Session parameters

select * from V$NLS_PARAMETERS ;

??

select * from V$NLS_VALID_VALUES ;

Valid
values 

Miscellaneous Tips

 Put these in appropriate places:

ALTER DATABASE MOUNT CLONE DATABASE;

Mount clone
ALTER DATABASE RENAME GLOBAL_NAME TO database.domain;
Change global name
ALTER DATABASE [NATIONAL] CHARACTER SET char_set;
Character sets
ALTER DATABASE RENAME FILE 'xyz', 'abc' TO 'wxy', 'bcd';
Rename datafiles, tempfiles, or redo log file members

 Some thoughts

 When working in development, close all windows/applications open on production databases.

 Think twice before ENTER after shutdown: are you on the correct database?
 character set: see select * from v$nls_parameters;
 Store the control files on two different disks.
 Regularly move the alert log files
 Define the default date format in the parameter file: 
NLS_DATE_FORMAT = 'DD/MM/YYYY'
 Remove the role CONNECT from the users and replace with a new role with "CREATE SESSION" and
"SELECT_ON_SCHEMA". The following instructions are used: REVOKE CONNECT FROM user_xyz; GRANT

http://www.cthullen.s5.com/
 CREATE SESSION TO user_xyz; GRANT SELECT_ON_SCHEMA TO user_xyz; The role CONNECT also allows
creating of tables in the user’s schema, which is not necessary.

Installing

 Set the local (control panel / regional settings) to English (US)

 Copy contents of CD to disk in temp directory
 Search and remove symcjit.dll
 Launch from directory install\win32 on the disk.
 Put regional settings back to French (Swiss).

Kill processes in windows using 

%ORACLE_HOME%\bin\orakill sid thread 
Get the thread from column spid:

select spid, osuser, s.program 

  from v$process p, v$session s 
  where p.addr=s.paddr;

ALTER SESSION ENABLE RESUMABLE [TIMEOUT x];

If a large transaction fails because of lack of space, then it is not rolled back but only suspended. Correct the problem
and the transaction will resume automatically. Note that a tablespace has to be explicitely extended and it is not
sufficient to set the autoextend on. It is necessary to have the "resumable" privilege (GRANT RESUMABLE TO
SCOTT;). New view dba_resumable.
select * from v$version;
select * from v$option;
See version and installed options.
select Sessions_Highwater from V$LICENSE;
See maximum number of concurrent sessions.

Original in O:\tech-tips\my_notes. Last update on Friday, 26 December, 2008 23:16

http://www.cthullen.s5.com/