Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘Dailies’

What are Oracle Managed File (OMF) ?

Posted by FatDBA on September 11, 2012

What are Oracle-Managed Files?
Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames.

– Provides default location, name and size
– OMF is still optional. Normal file creation techniques still available

Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

Online redo log files
Control files

First you have to enable OMF by altering parameter db_create_file_dest.
SQL> show parameter db_create

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string

Here i’m trying to create a tablespace with name ‘tb1’ but you’ll recieve error message asking you to provide DATAFILE/TEMPFILE clause.

SQL> create tablespace tbi;
create tablespace tbi
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
Alter parameter db_create_file_dest.
SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/’ scope=both;

System altered.
SQL> show parameter db_create_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      /u01/app/oracle/oradata/

Let’s try to create Tablespace in same fashion we tried earlier.
SQL> create tablespace tb1;

Tablespace created.


Posted in Advanced | Tagged: , , | 2 Comments »

Alter database VS Alter system

Posted by FatDBA on June 30, 2012

Alter database VS Alter system

An “alter system” command is (mostly) only possible in status OPEN. The only exception from this I recall presently is  “alter system set param=value” to modify initialization parameters. That is already possible in NOMOUNT status.
“alter database” on the other hand is already possible in status MOUNT,

ALTER SYSTEM is an Instance Level command. Generally this applies for running processes, parameters etc. (however “ALTER SYSTEM DUMP” seems to be an exception).

ALTER DATABASE is a Database Level command. Generally, this applies to the physical structure of the database.

Think of an RAC environment. Most ALTER SYSTEM commands (ALTER SYSTEM DUMP is one exception) are local to the instance (although SET can set for multiple instances). The ALTER DATABASE commands are for the whole database.

Posted in Basics | Tagged: | Leave a Comment »

RMAN (Do’s & Dont’s)

Posted by FatDBA on June 26, 2012

No SPFILE before control file if both are backed up at the same time.

RMAN> backup current spfile controlfile;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “spfile”: expecting one of: “controlfile”
RMAN-01007: at line 1 column 16 file: standard input

RMAN> backup current controlfile spfile;

Starting backup at 29-MAY-12
using channel ORA_DISK_1

* RMAN don’t support PFILE backups. SPFILE is only support parameter type in RMAN.

* RMAN does not backup TEMP tablespaces.
That is because “TEMP” was not backed up at the outset. RMAN does not backup Temporary Tablespaces. Such tablespaces do NOT need to be backed up, and, therefore are not restored or cloned.

* Redo logs can’t be backed up using RMAN but REDO Archivelogs can.

Posted in Advanced, Basics, Uncategorized | Tagged: , | Leave a Comment »

%d bloggers like this: