Tales From A Lazy Fat DBA

Den of an Oracle DB Performance & Troubleshooting freak … \,,/

  • Likes

    • 204,127
  • Archives

  • Categories

  • Subscribe

Posts Tagged ‘Database’

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:

Tablespaces
Online redo log files
Control files

First you have to enable OMF by altering parameter db_create_file_dest.
Example:
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.
Done.

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

ORA-00265: Cannot set archivelog (FIX)

Posted by FatDBA on August 28, 2012

Today while turning on Archivelog facility on one of my Oracle Database i recieved an error while reads “instance recovery required, cannot set ARCHIVELOG mode” and i find a very unique way to get rid of this problem.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

 

Resolution Steps:
SHUTDOWN -> STARTUP -> SHUTDOWN IMMEDIATE -> STARTUP MOUNT -> alter database archivelog

 

SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             104859096 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             109053400 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

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

 
%d bloggers like this: