Oracle DBA Notes

  

These are my personal notes that I use as a quick help in my work.
You are welcome to read them.

Contents of current page Top-level home page
 
Index  Java Internet Oracle Notes
Linux Basics Web Basics SQL Notes
Informatica Servlets Apache BkpRstore SQL*Plus
Teradata   LDAP Storage PL/SQL
Windows     Tables OEM
UML Lang.   Net8 Portal
SQL Server Python perl Performance OLAP
Vmware Visual Basic PHP/MySQL User Mgmt  
Git        
More technical pages here

Contents

 

Some useful links:

 

Connecting

At DB creation:    SYS   change_on_install 
   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;

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 }
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.
  • Parameter file is read
  • SGA is allocated
  • Processes are started
  • Trace and alert files are opened
  • The DB_NAME is defined.
  • Datafiles are associated to the instance
  • Control files are opened and read

The datafiles may be renamed, archive options of the redo log may be defined and a complete restore of the database can be done.

  • Data files are opened
  • Redo log files are opened
  • If needed, SMON restores the instance, meaning that the redo log files are used to restore the data in the buffer cache:
    • Roll the data forward that has not been stored in the data files.
    • Open the database.
    • 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;

 

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;

 

 

 

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"

 

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;

Other parameters:

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.

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
JAVA_POOL_SIZE
Dynamically set the size of the java pool
LOG_BUFFER
Size of redo log buffer
 
 

 

 

Typical Values for Memory (9i)

Type Shared Pool Buffer Cache Java Pool Large Pool PGA Oracle Process Size Sort Area  
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:

 

 

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;

 

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:

Treatment of a Query

Treatment of DML (analysis: see above):

Treatment of a commit:

The data is written later by the DBWR.

 

 

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

 

Installation

Some notes for the installation of the Oracle software

Some documentation (do a search):

Pre-requisites

UNIX groups and users:

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.

Requirements (from Pre-Installation Requirements.htm):

 

Just before installing, check the following again:

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

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:

Note scripts in $ORACLE_HOME/bin :

Scripts: modifications to be done still

Set these variables

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)

 

Database Creation

See database creation scripts

Naming conventions

Preliminary checks

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:

Creation

Scripts:

 

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:

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

 

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:

Do the following:

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.

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.

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:
  background_dump_dest='...'
  core_dump_dest='...'
   user_dump_dest='...'

Move the bdump, cdump, udump sub-directories to the new location.

Also move pfile and create.
Edit temporary pfile and modify parameters:
  log_archive_dest_1 = '...'
  standby_archive_dest = '...' 		Move the archived logs

Edit temporary pfile and modify parameter:
  control_files = '...'
  control_files = '...'
  control_files = '...'

Move the 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;

or (use this option for current log file):
alter database rename file '...' to '...';
alter database add logfile member '...' to group 3;

 Copy the redo files before renaming
alter database open;  

alter database tempfile '...' drop;
      -- Drop the tempfile, NOT the tablespace
alter tablespace tmp_tbsp add tempfile '...'
    size 20M reuse;

 Copy the temporary files before hand

 

 

DBA Tasks

Daily

  1. DB running ?
    Also DBSNMP ?
  2. Alert log entries ? Note in Database_Recovery_log (text file)
  3. Backup OK ?
    Backup of database
    Backup to tape
  4. 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.
  5. 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
        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:
  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:

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 = '';
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
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, then FAST_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
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:

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 ,
       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:

 

 

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

Installing

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.