nohup error

$nohup ./rmanbkp.sh
nohup: cannot run command ‘./rmanbkp.sh’: Permission denied

$chmod +x rmanbkp.sh
$nohup ./rmanbkp.sh
nohup: appending output to ‘nohup.out’
nohup: cannot run command ‘./rmanbkp.sh’: No such file or directory

The first line in rmanbkp.sh was

#!/usr/bin/sh

It might be a shell problem. Check ksh path

$which ksh
/bin/ksh

Change it to

#!/bin/ksh

Or, it might be a path problem. Check sh path

$which sh
/bin/sh

Change it to

#!/bin/sh

Reference:

1. http://docs.jach.hawaii.edu/JAC/JACUN/008.0/node42.html
2. http://www.gammon.com.au/forum/?id=8009&page=1

RMAN: can’t open target

$rman target / catalog
rman: can’t open target % ERROR INFO

Because there is another rman file in the system (use which to see the file location as follows). The ORACLE rman is in $ORACLE_HOME/bin.

$which rman
/usr/X11R6/bin/rman

Two ways to solve this problem:

1) Put $ORACLE_HOME/bin at the very beginning of $PATH in your environment

2) run with Oracle RMAN path

$ $ORACLE_HOME/bin/rman target / catalog <id>/<password>@<catalog database name>

Reference:

1. http://forums.oracle.com/forums/thread.jspa?threadID=198071
2. http://www.shutdownabort.com/dbaqueries/Backup_RMAN.php

Storage Parameters in a Local Managed Tablespace

Storage_Clause:

INITIAL

Specify in bytes the size of the object’s first extent. Oracle allocates space for this extent when you create the schema object. Use K or M to specify this size in kilobytes or megabytes.

The default value is the size of 5 data blocks. In tablespaces with manual segment-space management, the minimum value is the size of 2 data blocks plus one data block for each free list group you specify. In tablespaces with automatic segment-space management, the minimum value is 5 data blocks. The maximum value depends on your operating system.

In dictionary-managed tablespaces, if MINIMUM EXTENT was specified for the tablespace when it was created, then Oracle rounds the value of INITIAL up to the specified MINIMUM EXTENT size if necessary. If MINIMUM EXTENT was not specified, then Oracle rounds the INITIAL extent size for segments created in that tablespace up to the minimum value (see preceding paragraph), or to multiples of 5 blocks if the requested size is greater than 5 blocks.

In locally managed tablespaces, Oracle uses the value of INITIAL in conjunction with the size of extents specified for the tablespace to determine the object’s first extent. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then Oracle creates five 1M extents.

Restriction on INITIAL: You cannot specify INITIAL in an ALTER statement.

MINEXTENTS

Specify the total number of extents to allocate when the object is created. This parameter lets you allocate a large amount of space when you create an object, even if the space available is not contiguous. The default and minimum value is 1, meaning that Oracle allocates only the initial extent, except for rollback segments, for which the default and minimum value is 2. The maximum value depends on your operating system.

If the MINEXTENTS value is greater than 1, then Oracle calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE storage parameters.

When changing the value of MINEXTENTS (that is, in an ALTER statement), you can reduce the value from its current value, but you cannot increase it. Resetting MINEXTENTS to a smaller value might be useful, for example, before a TRUNCATE … DROP STORAGE statement, if you want to ensure that the segment will maintain a minimum number of extents after the TRUNCATE operation.
Restriction on MINEXTENTS

You cannot change the value of MINEXTENTS for an object that resides in a locally managed tablespace.

MAXEXTENTS

Specify the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1 (except for rollback segments, which always have a minimum value of 2). The default value depends on your data block size.
Restriction on MAXEXTENTS

You cannot change the value of MAXEXTENTS for an object that resides in a locally managed tablespace.

UNLIMITED

Specify UNLIMITED if you want extents to be allocated automatically as needed. Oracle Corporation recommends this setting as a way to minimize fragmentation.

However, do not use this clause for rollback segments. Rogue transactions containing inserts, updates, or deletes that continue for a long time will continue to create new extents until a disk is full.

Reference:

1. http://www.cs.bris.ac.uk/maintain/oracle9docs/server.920/a96540/clauses9a.htm

Common Problems

Some silly problems may incur everyday:

• Wrong spelling. ex. “contraint” -> “constraint”
• Missing parenthesis
• Make sure all the commands in the scripts have the right path, for example, the RMAN case
• Correct shell choice

SQL Statements used in Oracle DBA Books

Please see this file.

Reference:

1. http://forums.oracle.com/forums/thread.jspa?messageID=923473

Oracle 9i: How to Create Dictionary Managed Tablespace

When we tried to change tablespace storage paramters in a DBCA created database, we may get the following error information because the storage settings for locally managed tablespaces cannot be altered.

ORA-25143: default storage clause is not compatible with allocation policy.

Or, if you want to create a tablespace dictionary managed, you might get this error information:

ORA-12913: Cannot create dictionary managed tablespace

In Unix, type “oerr ora 12913″, you’ll get:

*Cause: Attemp to create dictionary managed tablespace in database which has system tablespace as locally manged

*Action: Create a locally managed tablespace.

Using the following SQL scripts, you will find the status of SYSTEM tablespace.

select tablespace_name, extent_management
from dba_tablespaces
where tablespace_name = ‘SYSTEM’;

TABLESPACE_NAME EXTENT_MAN
—————- ———–
SYSTEM LOCAL

Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open. DBCA creates by default a database with locally managed system tablespace.

To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.

In a database with a locally managed SYSTEM tablespace, dictionary managed tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.

Once a tablespace is locally managed, it cannot be reverted back to being dictionary managed.

You can create a database with a dictionary managed system tablespace. A simple SQL script can be created and then executed via SQL*Plus which will create your database with all the options you might want. A simple example:

create database yourdb controlfile reuse
maxdatafiles 1064
character set “AL32UTF8″
national character set “UTF8″
datafile
‘d:\yourfilesystem\yourdb_system_01.dbf’ size 250M
extent management dictionary
undo tablespace yourdb_undo
datafile ‘d:\anotherfilesystem\yourdb_undo_01.dbf’ size 400M
logfile
group 1 (………… etc… etc.

This is a very simplistic example with limited options included. You can check the “create database” command options in any Oracle reference with the appropriate version of database you are using. The GUI tools such as DBCA don’t always provide the easiest nor complete options available for managing your Oracle databases.

Reference: Oracle 9i How to Create Dictionary Managed Tablespace

Oracle 9i Installation in Red Hat Enterprise Linux 4

Oracle 9i Installation in Red Hat Enterprise Linux 4