• MICROSOFT
• COMPTIA +
• CISCO
• ORACLE
• SUN
• LINUX
• APPLE
• CITRIX

• HTML
• CSS
• JavaScript
• Vb Script
• ASP
• PHP
• Perl
• Visual Basic
• SEO

                           

 

ORACLE ADMINISTRATION DBA STUDY GUIDE

 

Adding a redo log file set
 

 

ALTER DATABASE
ADD LOGFILE
('/data/disk77/log3a.rdo', '/data/disk78/log3b.rdo')
SIZE 500K;


ALTER DATABASE ADD LOGFILE MEMBER
'/data/disk77/log3a.rdo' TO GROUP 1,
'/data/disk78/log3b.rdo' TO GROUP 2;


ALTER DATABASE
ADD LOGFILE
'/data2/oradata/MARSH/redo14.log'
SIZE 10M;


select group#,thread#,archived,status from v$log ;

select * from v$logfile ;

ALTER SYSTEM SWITCH LOGFILE ;

ALTER DATABASE DROP LOGFILE '/data/OraHome1/oradata/TEST/redo01.log'
ALTER DATABASE DROP LOGFILE '/data/OraHome1/oradata/TEST/redo02.log'

Altering table storage

alter table employee move
STORAGE (
INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
TABLESPACE PROD_DATA_04 ;



# Alter table: modifying a column

ALTER TABLE employee MODIFY (last_name varchar2(35));
 

 

Backing up data files

ALTER TABLESPACE ts1 BEGIN BACKUP;

<< backup files, within O/S >>

ALTER TABLESPACE ts1 END BACKUP;

/* control file backup */

ALTER DATABASE BACKUP CONTROLFILE TO 'filename' REUSE;
 

 

Clearing Log Files

 

Clearing the redo log files is usually the first remedy used in
repairing a damaged instance ; it's the equivalent of dropping and
re-adding each file.



SELECT * FROM v$log ;


ALTER DATABASE CLEAR LOGFILE 'filename' ;


/* if archive log mode is true, may need to force the clear */

ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'filename' ;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 ;
 

 

Creating a Schema

 

The steps detailed below depict the creation of a typical schema
within an open instance.


create tablespace ts99 datafile '/data4/ts99.dat'
size 200m default storage ( initial 100k next 100k pctincrease 0) ;

create user user99 identified by pwd99
default tablespace ts99
temporary tablespace temp
quota unlimited on ts99 ;

grant create table,resource,connect to r99 ;
<or>
grant dba to r99 ;
 

Creating users

 

/* create profile */

CREATE PROFILE pr01
SESSIONS_PER_USER 1
IDLE_TIME 30
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_VERIFY_FUNCTION SYSUTILS.VERIF_PWD ;

PROFILE = name of the profile
SESSIONS_PER_USER = limits a user to integer concurrent sessions
CPU_PER_SESSION = limits the CPU time for a session, in hundredth of seconds  
CPU_PER_CALL = limits the CPU time for a call
CONNECT_TIME = limits the total elapsed time of a session, in minutes
IDLE_TIME = limits periods of continuous inactive time during a session, in minutes
LOGICAL_READS_PER_SESSION = number of data blocks read in a session
LOGICAL_READS_PER_CALL = number of data blocks read for a call to process a SQL stmt
FAILED_LOGIN_ATTEMPTS = number of failed attempts to log in, before locking acct
PASSWORD_LIFE_TIME = limits the number of days the same password can be used
PASSWORD_REUSE_TIME = number of days before which a password cannot be reused
PASSWORD_REUSE_MAX = number of password changes required
PASSWORD_LOCK_TIME = number of days an account will be locked
PASSWORD_VERIFY_FUNCTION = a PL/SQL password complexity verification script
DEFAULT = omits a limit for this resource in this profile
COMPOSITE_LIMIT = specifies the total resources cost for a session, in service units
UNLIMITED = a user assigned this profile can use an unlimited amount of this resource


/* create user */

CREATE USER scott
IDENTIFIED BY tiger
DEFAULT TABLESPACE ts01
QUOTA 500M ON ts02
PASSWORD EXPIRE
ACCOUNT UNLOCK
PROFILE pr01 ;


/* user pwd change */

ALTER USER scott
IDENTIFIED BY lion ;

/* add tablespace */
ALTER USER scott
QUOTA 100M ON ts29 ;


/* user drop */

DROP user scott ;
 

 

DB Verify

 

The DB Verify utiltity is the equivalent of Sybase's DBCC utility.
It basically goes through a given data file and checks the consistency,
verifying the pointers are all proper. This should be run on a regular basis,
to find potential disk problems. If it is not, the end users will likely
be the first to notice any problems which may materialize.


# run example

cd /apps/oracle/bin

./dbv /data2/ts23.dbf
 

Database Creation
 

Two examples are detailed below.



--
-- Example #1
--

SPOOL output.log

STARTUP NOMOUNT PFILE=initXX.ora

CREATE DATABASE XX
MAXLOGFILES 3
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXLOGHISTORY 1
MAXINSTANCES 1
LOGFILE
GROUP 1 ('/data/disk1/log1a.rdo','/data/disk2/log1b.rdo') SIZE 10 M,
GROUP 1 ('/data/disk3/log2a.rdo','/data/disk3/log2b.rdo') SIZE 10 M
DATAFILE
'/data/disk8/system01.dbf' SIZE 50 M AUTOEXTEND ON,
'/data/disk9/filexx01.dbf' SIZE 1000 M AUTOEXTEND ON,
'/data/disk10/filexx02.dbf' SIZE 1000 M AUTOEXTEND ON,
'/data/disk11/sortxx99.dbf' SIZE 100 M
CHARACTER SET WE8ISO8859P1 ;

SPOOL OFF


--
-- Example #2
--

connect internal/oracle

SPOOL output.log

STARTUP NOMOUNT PFILE=/apps/oracle/admin/DB19/pfile/init.ora

CREATE DATABASE DB19
MAXLOGFILES 4
MAXLOGMEMBERS 2
MAXDATAFILES 100
MAXLOGHISTORY 1
MAXINSTANCES 1
LOGFILE
GROUP 1 ('/data1/oradata/DB19/log1a.rdo','/data1/oradata/DB19/log1b.rdo') SIZE 20 M,
GROUP 2 ('/data1/oradata/DB19/log2a.rdo','/data1/oradata/DB19/log2b.rdo') SIZE 20 M,
GROUP 3 ('/data1/oradata/DB19/log3a.rdo','/data1/oradata/DB19/log3b.rdo') SIZE 20 M,
GROUP 4 ('/data1/oradata/DB19/log4a.rdo','/data1/oradata/DB19/log4b.rdo') SIZE 20 M
DATAFILE
'/data1/oradata/DB19/system01.dbf' SIZE 100 M AUTOEXTEND ON,
'/data1/oradata/DB19/system02.dbf' SIZE 100 M AUTOEXTEND ON,
'/data1/oradata/DB19/system03.dbf' SIZE 100 M AUTOEXTEND ON
CHARACTER SET WE8ISO8859P1 ;


SPOOL OFF


**************************************************
Next Steps:

2) The following SQL scripts should be run, as internal, following
the DB creation:

catalog.sql catproc.sql dbmspool.sql
catblock.sq catparr.sql pupbld.sql

See oracle/rdbms/admin directory.


3) Creation of temp (sort) tablespace


CREATE TABLESPACE temp01
DATAFILE '/data1/oradata/DB19/temp01.dbf'
SIZE 500 M
TEMPORARY
ONLINE ;


4) Creation of rollback segments

(See section on RBS for examples)

Drop a table and deallocate space
Oracle 10g has a new feature: a Recycle Bin
 

 

-- Drop table, free up space, 10g

drop table TABLE_NAME purge;

purge table TABLE_NAME ;


-- Free up storage, Oracle 10g

alter table TABLE_NAME deallocate unused;


-- empty the recycle bin, Oracle 10g

purge user_recyclebin;
 

 

Dropping a Tablespace
 

Note that dropping a tablespace removes the internal pointers from Oracle ;
you still need to remove the file using OS commands.


ALTER TABLESPACE ts01 OFFLINE IMMEDIATE ;

DROP TABLESPACE ts01 INCLUDING CONTENTS ;

/* then, delete data files using O/S commands */

 

Enabling Archive Logging
 

Archive logging is necessary in order to guarantee that a full recovery is possible.



In the init.ora file, add:

LOG_ARCHIVE_DEST = /data5/archive
LOG_ARCHIVE_FORMAT = arch%s.arc
LOG_ARCHIVE_MAX_PROCESSES=2

Shut down the Oracle instance.

In server manager:

> connect
> startup mount
> alter database archivelog
> alter database open
> shutdown immediate


Run a complete cold backup - this sets the starting point
if a restore is needed.
 

Fixing Invalid Objects
 

How to fix invalid objects - procedures, packages, triggers.
Using this proc to detect and rebuild should solve many issues.





CREATE OR REPLACE PROCEDURE P_holtdw_fix_invalid
IS
CURSOR program_units IS
SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('PROCEDURE','FUNCTION','PACKAGE', 'PACKAGE BODY', 'TRIGGER')
AND object_name <> 'TEST' and status = 'INVALID' ;
BEGIN
FOR x IN program_units LOOP
P_print ( x.object_name ) ;
DBMS_DDL.ALTER_COMPILE (x.object_type,'HOLTDW',x.object_name);
END LOOP;
END;
/
 

Increasing the size of a tablespace

1) Add a data file

ALTER TABLESPACE ts03
ADD DATAFILE
'/data1/oradata/ts03-b.dbf'
SIZE 200M ;

ALTER TABLESPACE ts01
ADD DATAFILE
'/data/disk12/dataxx44.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M
MAXSIZE 500M;


2) or, resize current data file

alter database datafile '/u01/oradata/orcl/users01.dbf' resize 50M;



3) or, change the storage / extent configuration

ALTER TABLESPACE ts01
DEFAULT STORAGE (
INITIAL 200M NEXT 200M MAXEXTENTS 10 ) ;




/* Altering the default storage for a tablespace */

ALTER TABLESPACE TEMP01 DEFAULT STORAGE (PCTINCREASE 0)

ALTER TABLESPACE TEMP01 DEFAULT STORAGE ( initial 1M next 1M )

 

Index Management

Main index types: b-tree, reverse key, and bitmap indexes.



/* unique index, use a low PCTFREE value for ID columns */

CREATE UNIQUE INDEX schema2.idx_invoice
ON schema2.invoice ( inv_id )
PCTFREE 10
STORAGE ( INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
TABLESPACE idx01 ;


/* non-unique index ;
note the NOLOGGING parm, which is recommended for large indexes
also note the NOSORT option, which is good for large tables where the
data has been loaded in sorted order.
*/

CREATE UNIQUE INDEX schema2.idx_customer
ON schema2.customer ( stat_cd, name )
PCTFREE 40
STORAGE ( INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
NOLOGGING
NOSORT
TABLESPACE idx01 ;


/* index extent increase */

ALTER INDEX schema2.idx_customer
STORAGE ( NEXT 300K ) ;


/* indexes should be rebuilt, when there are many deleted entries */

ALTER INDEX schema2.idx_customer
REBUILD
TABLESPACE idx02 ;


alter index PK_EMPLOYEE rebuild compute statistics nologging ;



/* update internal stats on the index, for the optimizer ...
also check for corruption */

ANALYZE INDEX schema2.idx_customer VALIDATE STRUCTURE ;



/* disable a constraint */

ALTER TABLE EMPLOYEE DISABLE CONSTRAINT FK_DEPT ;


/* add a constraint */

alter table REPORT_LIST
add constraint PK_REPORT_LIST primary key(REPORT_ID)
using index tablespace prod_idx_01;
 

Installing DBI Database Drivers

 

Installing DBI for database access from Perl and many other languages / platforms.







1) get DBI (http://search.cpan.org/~timb/DBI/) and download (latest is DBI-1.58)
2) gunzip and untar the file tar -zxf FILE
3) if you are root continue on -- otherwise set your path to pick up your perl path export PATH=your/path/to/perl/bin:$PATH

4) change to the untarred directory (cd DBI-1.58) and execute perl Makefile.PL (remember use the Perl you want to install DBI into)

5) make sure you have cc (c compiler) in your path -- then execute make
6) if no errors -- execute make test
7) if looks OK (you see something like "All tests successful, 31 tests and 379 subtests skipped.") execute make install

Now you can install various db drivers -- let's use Oracle as an example

8) find the oracle DBD (http://search.cpan.org/~pythian/) and download
9) Now, here you need a oracle client setup -- oracle now has an "instant client" which works pretty good (http://www.oracle.com/technology/software/tech/oci/instantclient/index.html)

10) For this example we pick the linux zip instantclient-basic-linux32-10.2.0.3-20061115.zip (since we can't install RPM's on our server)

11) Unzip this file -- it will create a directory called instantclient_10_2 -- move this to where you want.
12) download the sdk file -- instantclient-sdk-linux32-10.2.0.3-20061115.zip (perl needs header files in here to build)
13) unzip to where you installed instant client -- this will create a sub directory called sdk that has header files Perl needs

14) now you need to set 2 environment variables for Perl to Install
export LD_LIBRARY_PATH=/share/holt/ph7/instantclient_10_2
export ORACLE_HOME=/share/holt/ph7/instantclient_10_2
15 ) perl Makefile.PL -V 10.2.0.3 (pass in the version of instant client -- in our case it was 10.2.0.3)
16) to make test work add these 2 env vars
export ORACLE_USERID='userid/passs'
export ORACLE_DSN=DBI:Oracle://YOURHOSTHAME/YOURSERVICE_NAME
17) if you see something like "All tests successful, 4 tests and 122 subtests skipped" go and do make install
18) If make install goes clean now run a test. All the env vars you set can be unset -- as long as you point to the Perl

that you installed this on this will work!
like so (bash shell script -- enter in your particulars)



#!/app/your/path/to/perl
use strict;
use DBI;
my $dbh = DBI->connect( 'DBI:Oracle://YOURHOSTHAME/YOURSERVICE_NAME','YOURUSERID', 'YOURPASSWORD', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT 1 from dual}; # Prepare and execute SELECT
my $sth = $dbh->prepare($sql);
$sth->execute();

my $tret;
$sth->bind_columns( \$tret);

print "Data from Table:\n\n"; # Fetch rows from DB

while( $sth->fetch() ) {
print "$tret\n";
}

$sth->finish();
$dbh->disconnect;

20) Here our your results

Data from Table:

1

19) Optionally you can remove the sdk directory in the instantclient install if you are worried about space
20) You are done



Killing Sessions

 

Runaway processes can be killed on the UNIX side, or within server manager.



/* Kill a session, specified by the returned sess-id / serial number */

SELECT sid, serial#, username from v$session

ALTER SYSTEM KILL SESSION 'sessid,ser#'
 

Moving Data Files

 

Illustrated is the method for moving a datafile for an active instance.




1) take the tablespace offline

2) move the file, using mv command

3) ALTER TABLESPACE ts01
RENAME DATAFILE '/data/ts01.dbf'
TO '/data5/ts01.dbf' ;

4) bring the tablespace online

 

O/S Password Authentication

 

In previous versions of Oracle, the "internal" or super-user account was limited
to one login / password. With Oracle 8, several IDs can be granted the internal
profile's capabilities. Using O/S password authentication is necessary as internal
users may need to get into server manager, and the instance may not be online.

orapwd
Usage: orapwd file=<fname> password=<password> entries=<users>
where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBAs and OPERs (opt),
There are no spaces around the equal-to (=) character.

GRANT SYSDBA TO scott

In the inixx.ora file:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

/* view accounts using external authentication */
SELECT name, sysdba, sysoper FROM V$PWFILE_USERS
 

Oracle Architecture: Overview

 

The Oracle Instance


An Oracle instance consists of:


A) Six processes:
PMON Process Monitor,
SMON System Monitor,
DBWn Database Writer,
LGWR Log Writer,
ARCn Archiver,
and CKPT Checkpoint.
Additional processes on a 10g RAC system may include
RECO Distributed Transaction Recovery,
CJQn Job Coordinator Process,
LCKn Shared Resource Locking,
LMDn Global Lock Manager,
LMON Lock Manager,
LMS* Global Cache Service,
MMAN Memory Manager,
MMNL Metrics Monitor, session hitory, metrics computation,
MMON Metrics Monitor, statistics, snapshots,
and PSPn a PL-SQL plug in.


B) Data files which contain the tables and other data objects, control files which contain configuration information, redo log files for transaction processing, and archive files for recovery purposes.


C) Configuration files which contains the instance attributes, and external security information




Memory Model


The Oracle memory model consists of:


A) the SGA, which contains executable program code;


B) the shared pool, which is split between the library cache and data dictionary cache, optimized queries are stored here;


C) the redo log buffer, which is where transactions are stored before they are written to the redo logs;


D) the db buffer cache, which is where database operations are stored before they are written to the data files.


The Oracle dataserver runs as a multiple processes within the operating system; the number of users connected to the database is reflected in the number of processes managed by the OS. Each Oracle user connection requires 1 meg of server memory.




Transaction Processing


Transactions are written to the redo log buffer, where they advance to the redo logs, data file buffer, and data files. When a rollback occurs, block images are discarded from the redo log buffer; as the previous block data is held in the rollback segment blocks. Committed transactions are promoted to the archive logs. Archive logs are used to restore the data in the event of a hardware failure. A checkpoint operation flushes all updated (committed) memory blocks from the log buffer and database buffer pool.


Note that transaction logging is optional, on a table by table basis, and archive logging is also optional.


During an update transaction, record locking provides prevents data block collisions from occurring. Access to the "before" image of the record(s) is made available during this time, which reduces contention. This is a patented record locking mechanism.




Backup Procedures


Previous to Oracle 8, the only way to back up the database was through a "cold" backup. This involved shutting down the Oracle instance, backing up the data files, and restarting upon completion.


Oracle 8,9, and 10g has the Recovery Manager package which facilitates backing up data files on-line.




Recovery Procedures


Recovery is achieved by restoring the data files, and verifying the control files are synchronized properly. When RMAN is used (it is now the standard in most production databases)
recovery is accomplished via an automated set of processes.




Security and Account Setup


Oracle is shipped with several built-in accounts: system, internal, and sys. Operating system authentication is required in order for a login to be created with similar privileges. After a login is created, access is then granted to the tables within schemas as needed.




Database Creation


Databases are initialized with the "create database" command. In most (99.9%) cases the database name is the same as the instance name, and there is only one database per instance.


Within an Oracle instance, schemas are created which contain the tables for an application. Tables are referenced by schema_name.tablename. Each user is assigned a default schema upon creation; this schema name is the same as the user name. In order for tables to be referenced without the schema name, they must be owned by the user, or in the "system" schema. "Synonyms" can be created to bypass the prefix requirement. Typically, an "internal" or "system" level login will be used to access the database.


A typical Oracle instance will have 12 data files, 6 redo log members, 6 archive files, and 4 control files, all spread across various disk subsystems.




Data Types


Supported data types include number, char, varchar2, date, long raw, clob, and blob. In Oracle 10, numeric data types were introduced.


Blob and clob (Oracle 8+ only) datatypes are implemented via pointers within the physical record structure ; the field contents are stored in dedicated blocks. As a result, each blob or clob field requires at least 2K of storage (depending on the database block size). Long raw datatypes are stored in-line, and are discouraged.


For string data, the varchar2 type can be used for lengths up to 2000; the clob type can be used for longer field data.

( ** blob = binary large object, clob = character large object)


Date fields are represented as a number of days, along with a decimals fraction (accurate to the minute). Additional accuracy can be obtained by purchasing a separate module.


Sequences provide a means of autoincrementing an ID column - selecting sequence_name.NEXTVAL automatically increments the sequence and returns the new value.




Storage Concepts


Tables are stored in tablespaces; a tablespace is made up of one or more data files. Although it is possible to use raw devices within Oracle, it is not recommended. Control files, rollback segments, and redo logs are all stored in separate files within the operating system.
Note that Oracle 10g RAC features a special OFS (Oracle File System), which allows the database to get better i/o
performance over conventional file systems.




Partitioning


Oracle 9i,10g does support semantic partitioning, by list, range, hash, hash range, and composite.




Failover and High Availability


Oracle offers several types of high availability configurations: Transparent Application Failover (TAF),
Oracle Streams (send transactions to remote server), Data Guard, and Standby Database.




PL-SQL


PL-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Arrays are supported (using the table datatype), as well as structures (the record type). Since PL-SQL procedures cannot return a result set, returning rows to a calling application requires implementing arrays as "out" variables.
One powerful feature of PL-SQL is the ability to create custom functions which can be used within SQL statements.
Regular Expressions are supported, in addition to User Defined Operators.




Triggers


Oracle supports select, insert, update, and delete triggers. Triggers can be set to fire 'before' or 'after' a transaction.
NEW and OLD aliases are used to refer to the before and after images of the data.
Triggers by default fire for all rows in the transaction; they can be set to fire for each row also.
Oracle also supports DDL Event Triggers.




Analytic Functions, OLAP


Oracle 10 does support analytic functions like median, n-tile, and lead/lag, over partitions.




Flat File Processing


Oracle includes the SQLLDR utility (SQL Loader) which can import via flat files.
"Direct" mode imports are achieved by simply removing the indexes (and triggers) from the target table,
and specifying direct=yes at the command line.
There is no Oracle utility for exporting data to a flat file; sqlplus must be used to spool the data to a file.




Performance and scalability


Oracle has always been known for its speed and performance. Oracle 10 supports over 15,000 active user connections.
The patented record locking scheme made it an attractive candidate for canned applications marketed by Peoplesoft and the like.
The ability to turn transaction logging on and off at will allows Oracle squeak by competitors during benchmark analysis runs.






Price and Support


Price per seat is relatively high, compared to other vendors.


Support is achieved by opening "TAR" cases with the support team. Response is usually within 48 hours.




Management and Development Tools (for Windows)


SQL-Plus is the interactive query tool used with Oracle; it is useful for entering queries and stored procedures. Complex reporting capabilities are available for those willing to learn SQL-Plus extensions.


Oracle Enterprise ships with Enterprise Manager, a full featured front end to the intricacies of Oracle. The main areas of the system (security, storage, schemas) are managed by totally separate applications, which is cumbersome at times.


The "best of breed" product in this category is DB-Artisan by Embarcadero Technologies. Many developers choose Toad, as it is cheaper and has a lot of nice features.




Additional Features of Interest, in Oracle 10g




ORA_ROWSCN System Column

A timestamp-like data type on each record, used for update consistency.





FORALL Option

A method by which to perform updates via 'bulk' operations. Significant performance improvements can

be obtained via this mechanism.





PRAGMA SERIALLY_REUSABLE Option

A method by which packages can be managed in memory more efficiently.





Function Based Indexes

This allows results to be pre-calced into an index, eliminating the need for calculation at runtime.





DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS Command

A method by which to set status or completion rate of long running processes.





Package Initialization Section

Allows the setup routines for a package to occur just once per session.





Sorted Hash Clusters

Allows groups of records to be read from disk via a hash identifer.





WITH Clause Queries

Allows a query to be referenced as a virtual table multiple times within a SQL block.

Oracle Issues, Facts You Should Know

 

Indexes can become invalid

For Oracle users, this is normal - yes that is correct. Simply loading a table with SQL-Loader can make an index unusable, and invalid. Indexes need to be rebuilt on a regular basis, if sql-loader is used to load data. We'll post common ORA-XXXX errors soon, to illustrate.



With SQL Server and Sybase, this never happens. An index will not become invalid or unusable by simply loading data into a table.






Drop table: does a delete in the background.

This is fixed in Oracle 10g. In previous versions of Oracle, a "delete from" was performed when a DROP TABLE was issues. Crazy, but true. Now, in 10g, the data is moved to the "recycle bin" which brings up a whole new set of problems!






Each connection takes too much memory (2-4 MB)

Yes, that is correct. Each database connection takes 2-4 MB of memory on the host machine.

For Sybase and SQL Server, the amount of memory is less than 1/10th that!







Stored procedures don't return a result set.


You have to use a pointer to an open cursor in your client application.

Oracle developers don't know what you are talking about, when you say: "Stored procedures need to return a result set."

For Sybase and SQL Server developers, this is the core advantage of stored procedure: they can simply replace SQL statements if needed.

This is impossible in Oracle:


create proc P_get_employee as
begin

select emp_id, emp_name from employee

end




Oracle does not support update within a join.


You can't do an update, like this:


update company_data
set t1.msci_gics_sector = t2.msci_id
from company_data t1, msci_map t2
where t2.company_id = t1.company_id


You need to use a convoluted sub-select format, in order to achieve the same result.

Note that Oracle developers consider this to be 'normal' .. so beware.









No real numeric data types


This is fixed in 10g - at last, Oracle has real numeric data types.

The official announcement was made in Oracle Magazine in 2007.







Schema/user methodology is dated, and difficult to work with.


Try setting up a schema with read_only users, dbo users, and read/write users. You'll need to create separate schemas, and then maintain synonyms (at least now there are public synonyms to make this less painful). Note that most enterprise Oracle DBAs cringe when they hear "public synonyms" ...







Error messages are useless most of the time




- try coding a stored proc, and figuring out what the errors are



- typical error statement: Cannot allocate segment in tablespace 10
( yet there is no way to find which tablespace is #10 ?!?!? )






No built-in export utility; for export to delimited files.


Oracle has SQL Loader for getting data in ... but Larry Ellison does not want you to get your data out!
You need to code a hack spool program, using SQL-Plus to export to a flat file.






No database dump utility, tablespace backups require you to offline the file.


There is no "block dump" style backup program in Oracle, export is a p-coded series of SQL statements, transportable tablespaces are basically copies of different segments of the databases. Loading one Oracle instance from another is not a simple task!






PL-SQL is inconsistent with Oracle SQL-Plus implementation


Some functions work in SQL Plus, but not in a PL-SQL procedure ( like to_lob ).






PL-SQL is inconsistent


e.g. When declaring a parm, varchar must be used ... varchar2(10) does not compile!







SQL Plus procs wait until they are completed, before displaying printed terminal output.


This really makes debugging difficult. The output from the PRINT function (DBMS OUTPUT) does not appear to the console until the stored procedure has completed executing.






Space reclamation is poor, table grows after deletes. Need to drop/rebuild tables periodically.


Even after deleting rows ... a query might still scan those empty blocks, which are part of the tables extent list.







Support, software is too expensive, poor value.


Oracle is the true leader when it comes to sucking up IT budgets.







Performance is lacking; this is inherent in the architecture.


- Raw devices are discouraged, Oracle tablespaces are made up of files in filesystems. With 10g, Oracle invented its own filesystem type ... but still, there are files in the filesystem, visible at the OS prompt.



- Oracle keeps 'before image' available, allowing dirty reads. Nice to have that dirty read available, but the overhead with the redo log really puts the damper on update performance.









GUI tools included are poorly designed, rareley implementd at client sites. ( Enterprise Mgr, Oracle Forms, etc )


The toolset which ships with the database is rarely used by DBAs. Hence the popularity of TOAD and DB Artisan.

Also note: Oracle has poor replication tools. Other vendors (like
Informatica) are capitializing on this weakness by releasing their own tools.

 

Recovering an Instance

 

An incomplete recovery is the only option if backups are run periodically
on a cold instance. Complete recovery is possible if archive logging is enabled,
and backups are run while the database is active.



/* diagnose data file problem */
select * from v$recover_file ;

/* diagnose data file problem, by displaying tablespace info */
select file_id, file_name, tablespace_name, status
from dba_data_files ;

/* find archive log files */
select * from v$recovery_log ;


/* incomplete recovery #1 */

svrmgrl> shutdown abort

[[ In Unix copy data files from backup area to data directory(s). ]]

svrmgrl> connect;
svrmgrl> startup;

/* incomplete recovery #2 */

svrmgrl> shutdown abort;
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> alter database rename file '/data2/ts05.dbf' to '/backups/ts05.dbf'
svrmgrl> alter database open;


/* incomplete recovery #3, for user error (i.e. drop table ) */
Note: archive logs must exist in LOG_ARCHIVE_DEST

svrmgrl> shutdown abort

[[ backup all files ]]
[[ restore required data file(s), using OS commands ]]

svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database until time '2002-03-04:15:00:00' ;
svrmgrl> alter database open resetlogs;





/* complete recovery #1, for major recovery operations, closed instance */
Note: archive logs must exist in LOG_ARCHIVE_DEST

svrmgrl> shutdown abort

[[ backup all files ]]

svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database ;
< or >
svrmgrl> recover datafile '/data4/ts03.dbf'
svrmgrl> startup open;


/* complete recovery #2, for major/minor recovery operations, open instance */
Note: archive logs must exist in LOG_ARCHIVE_DEST

svrmgrl> shutdown abort

[[ backup all files ]]
[[ restore corrupted data files, using OS commands ]]

svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> set autorecovery on ;
svrmgrl> recover tablespace ts03 ;
< or >
svrmgrl> recover datafile 4 ;
svrmgrl> startup open;
 

Restricted Mode

Instance is available only to users with "restricted session" privilege.



/* startup in restricted mode */

STARTUP RESTRICT


/* restrict an instance that is already running */

ALTER SYSTEM ENABLE RESTRICTED SESSION

 

Rollback segments

 

Two rollback segments are created per database.
Below are a few handy examples for creating and changing rollback segments.
Note that for some Oracle upgrades, you may need to extend the system rollback
segment ( or increase its next extent size ).



/* example 1 */

CREATE ROLLBACK SEGMENT rbs01
TABLESPACE ts22
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 4M
MINEXTENTS 20 MAXEXTENTS 100) ;

ALTER ROLLBACK SEGMENT rbs01 ONLINE ;


/* example 2 */


CREATE ROLLBACK SEGMENT R05
TABLESPACE rb5
STORAGE ( INITIAL 100K NEXT 100K MAXEXTENTS 300 ) ;

ALTER ROLLBACK SEGMENT R05 ONLINE ;


Note:

Add this line to the initXX.ora file, to ensure the
rollback segments are brought online after each server restart:

rollback_segments=(r01,r02,r03,r04)


Altering a Rollback Segment

ALTER ROLLBACK SEGMENT r03
STORAGE ( MAXEXTENTS 250 );


ALTER ROLLBACK SEGMENT r05 SHRINK TO 10M ;



/* example3, includes tablespace creates */


connect internal/oracle

CREATE TABLESPACE rbs01
DATAFILE '/data1/oradata/DB19/rbs01.dbf' SIZE 100M
MINIMUM EXTENT 10K
DEFAULT STORAGE (
INITIAL 20K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0 )
PERMANENT
ONLINE ;

CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs01
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 10M
MINEXTENTS 20 MAXEXTENTS 999) ;

ALTER ROLLBACK SEGMENT rbs01 ONLINE ;



CREATE TABLESPACE rbs02
DATAFILE '/data1/oradata/DB19/rbs02.dbf' SIZE 100M
MINIMUM EXTENT 10K
DEFAULT STORAGE (
INITIAL 20K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0 )
PERMANENT
ONLINE ;

CREATE ROLLBACK SEGMENT rbs02
TABLESPACE rbs02
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 10M
MINEXTENTS 20 MAXEXTENTS 999) ;

ALTER ROLLBACK SEGMENT rbs02 ONLINE ;


CREATE TABLESPACE rbs03
DATAFILE '/data1/oradata/DB19/rbs03.dbf' SIZE 100M
MINIMUM EXTENT 10K
DEFAULT STORAGE (
INITIAL 20K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0 )
PERMANENT
ONLINE ;

CREATE ROLLBACK SEGMENT rbs03
TABLESPACE rbs03
STORAGE (
INITIAL 100K NEXT 100K OPTIMAL 10M
MINEXTENTS 20 MAXEXTENTS 999) ;

ALTER ROLLBACK SEGMENT rbs03 ONLINE ;
 

Server Manager

Server manager is a command line utility which is used to
administer the Oracle instance.

# To start Server Manager (Unix):
svmgrl

# Connecting to an Oracle instance, within Server Manager
> CONNECT internal/oracle sysdba

# Starting up an Oracle instance, without mounting the database
STARTUP NOMOUNT PFILE=initXX.ora

# Starting an Oracle instance
> STARTUP OPEN PFILE=/data/config/initd50.ora

# Shutting down an Oracle instance, immediately
> SHUTDOWN IMMEDIATE

# Shutting down an Oracle instance, and allowing pending
# transactions to commit
> SHUTDOWN TRANSACTIONAL
 

Tablespace Creation

The examples below create tablespaces for permanent data tables and
temporary sort tables.


CREATE TABLESPACE ts01
DATAFILE '/data/disk9/filexx01.dbf' SIZE 1000M
MINIMUM EXTENT 500K
DEFAULT STORAGE (
INITIAL 500K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 )
PERMANENT
ONLINE ;

CREATE TABLESPACE sort99
DATAFILE '/data/disk11/sortxx99.dbf' SIZE 100M
MINIMUM EXTENT 1M
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 40
PCTINCREASE 0)
TEMPORARY
ONLINE ;


CREATE TABLESPACE ts01
DATAFILE '/data/disk9/filexx01.dbf' SIZE 1000M
MINIMUM EXTENT 500K
DEFAULT STORAGE (
INITIAL 500K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0 )
PERMANENT
ONLINE ;


/* auto extend */

CREATE TABLESPACE prod_idx_01
DATAFILE '/u3/oradata/prod/prod_idx_01.dbf'
SIZE 300M
AUTOEXTEND ON NEXT 10M
MINIMUM EXTENT 320K
DEFAULT STORAGE (
INITIAL 320K
NEXT 320K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
)
PERMANENT
ONLINE ;
 

Taking tablespaces offline

To perform a "hot backup", each tablespace needs to put
offline, and backed up using OS commands.




/* prepare for offline tablespace backup */

ALTER SYSTEM ARCHIVE LOG CURRENT;


/* take tablespace offline */

ALTER TABLESPACE users OFFLINE NORMAL;

/* run backup for datafiles */

/* bring tablespace online */

ALTER TABLESPACE users ONLINE;
 

Updating statistics for a table or schema

 

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1','COMPANY');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA1');

<OR>

ANALYZE TABLE COMPANY COMPUTE STATISTICS ;
 

Using SQL Loader to load data with embedded CRs / Line Feeds
 

Using SQLLDR to Load Data with Embedded CRs in Text/Comment Fields


Oracle's SQLLDR is designed for files having a carriage return as the record separator.
Most of the documentation refers to a line in a file as a 'record' .. which is not true
in many cases. A line in a file is a "line" ... a row in a table is a "record"!

The classic case where you need to vary the record terminator is when you are importing
paragraphs of data, non-HTML text. You want to be able to pull the data from the
database and display it to the screen (or report) with the line feeds intact.

Here's how to load the data into a table via SQL LDR.




Data File.

Data file should have || as the field separator, record separator would be '|\n'


e.g.

12389289||1995||Worked at ABC company as an analyst
using Oracle and SQLLDR.|
12389289||1996||Worked at Acme Inc as a DBA using Oracle
control files with embedded cr in the text.
Very challenging.|
12828929||1990||Worked with VAX VMS|


Sample Control File, for SQLLDR

LOAD DATA
INFILE 'resume_detail.dat'
CONTINUEIF LAST != "|"
INTO TABLE resume_detail
APPEND
FIELDS TERMINATED BY '||' TRAILING NULLCOLS
(
CANDIDATE_ID,
YEAR,
JOB_CMT
)



You will see something like this in the log file.

Continuation: Last non-white character != 0X7c(character '|')
Preserving continuation characters as data