Migrating Dictionary Managed Tablespace To Locally Managed Tablespace

 Migrate a dictionary managed SYSTEM tablespace to locally managed :

SQL> DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL          ('SYSTEM');

Use the below following steps to convert your SYSTEM tablespace from Dictionary Managed to locally Managed :

• Make a complete backup of your Database
• Ensure that the database has a default temporary tablespace that is not SYSTEM. The temporary tablespace is created using the CREATE TEMPORARY TABLESPACE command.
• Eliminate any undo (rollback) segments in disctionary-managed tablespaces.
• There should be at least one online undo segment in a locally managed tablespace, or an undo tablespace should be online.
• All tablespaces other than the tablespace containing the undo space and the default temporary tablespace should be placed in READ ONLY mode.
• Startup the instance in restricted mode.
• Migrate the SYSTEM tablespace by using the above command.

Note: Any non-SYSTEM dictionary managed tablespace that is not migrated to locally managed prior to migrating the SYSTEM tablespace, cannot be altered to READ-WRITE. The tablespace will remain READ-ONLY. Only locally managed tablespaces can be altered to READ-WRITE after the migration of the system tablespace.

Thank you !!

Space Management in Tablespace

Tablespaces allocated space in extents. Tablespaces can be created to use one of the following two different methods of keeping track of free and used space.

• Locally Managed tablespace
• Dictionary Managed Tablespace

Locally Managed tablespace:

- Free Extents are managed in the tablespace.

- Bitmap is used to records free extents.

- Each bit corressponds to a block or group of blocks.

- Bit Value indicates free or used.

The Extents are managed within the tablespace via bitmaps. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, the oracle server changes the bitmap values to show the new status of the bocks. Locally managed is the default tablespace management it is introduced in Oracle 9i. 

Locally managed tablespaces are :

• Reduced contention on data dictionary tables.
• No undo generated when space allocation or deallocation occurs
• No Coalescing required.

SQL Command for Creation of locally managed tablespace for Example:

SQL> CREATE TABLESPACE userdata 

DATAFILE '/u01/oradata/userdata01.dbf' size 100M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; 

The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed. By default, a tablespace is locally managed.   

extent_management_clause :

 [EXTENT MANAGEMENT [ DISCTIONARY | LOCAL 

[AUTOALLOCATE | UNIFORM [SIZE integer [K/M]]]]

Where : 

DICTIONARY: Specifies that the tablespace is managed using dictionary tables.

LOCAL: Specifies that the tablespace is Locally managed via bitmaps. If you specify LOCAL, you cannot specify DEFAULT  storage_caluse, MINIMUM EXTENT, or TEMPORARY.

AUTOALLOCATE: Specifies that the tablespace is system managed. Users cannot specify the extent size. This is the default.

UNIFORM: Specifies that the tablespace is managed with uniform extents of size bytes. Use the K or M to specify the extent size in kilobytes or megabytes. The default size is 1 MB. 

The EXTENT_MANAGEMENT clause can be used in various CREATE command :

• For a permanent tablespace, you can specify EXTENT_MANAGEMENT_LOCAL in the CREATE TABLESPACE command. 

Note : Parior to Oracle 9i release 2 , the SYSTEM tablespace was not LOCALLY managed.

• For a temporary tablespace, you can specify EXTENT_MANAGEMENT_LOCAL in the CREATE TEMPORARY TABLESPACE  command.

 

Advantages of Locally Managed Tablespaces :

A locally managed tablespace has the following advantage  over the Dictionary Managed tablespaces :

• Local Management avoids recursive space management operations. This can occur in Dictionary-Managed tablespaces if consuming or releasing space to an extent and in results of this operation another operation that consumes or release space in an Undo segment or Data Dictionary tables.
• Because locally managed tablespaces do not record free space in the data dictionary tables, they reduce contention on these tables.
• Local management of extents automatically tracks adjacent free space, Eliminating the need to coalesce free extents.
• The sizes of the extents that are managed locally can be determined automatically by the system.
• Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information). 

   

Dictionary- Managed Tablespace: Characteristics : 

- Free Extents are managed by the database disctionary.

- Extents are managed in the data dictionary.

- Appropriate tables are updated when extents are allocated or deallocated.

- Each segment stored in the tablespace can have a different storage clause.

- Coalescing is required.

The extents are managed by the data dictionary. The Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or deallocated.

Segments in dictionary-managed tablespaces can have customized storage. This storage is more flexible than locally managed tablespaces but much less efficient.

SQL Command for Creation of Dictionary managed tablespace for Example:

SQL> CREATE TABLESPACE userdata

 DATAFILE  '/u01/oradata/userdata01.dbf' size 100M 

 EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE 

 (initial 1M next 1M PCTINCREASE 0);

 

Thank you !!

Managing Tablespaces and Data Files

 Tablespace and Data Files : 

Oracle Stores Data logically in tablespaces and Physically in Data files.

• Tablespaces :

- Can belong to only one databses at a time.

- Consist of one or more data files

- Are further devided into logical units of stoarge.

• Data Files :

- Can belong to only one tablespace and one databases

- Are a repository for Schema object data.

Databases, Tablespaces, and Data files are closely related, but they have important differences :

• An Oracle Database consists of one or more logical storage units called tablespaces,  which collectively store all of the database's data.
• Each tablespace in an Oracle Database consists of one or more files called data files, which are physical structures that conform with the operating system in which Oracle is running.
• A database's data is collectively stored in the data files that constitute each tablespace of the database. 

For example, the simplest Oracle Database would have one tablespace and one data file. Another database can have more tablespaces with more data files.

Types of tablespaces :

• SYSTEM  Tablespace

- Created with the database

- Required in all databases  

- Contains the data Dictionary, Including stored program units

- Contains the SYSTEM undo segments

- Should not contain user data, although it is allowed. 

• Non-SYSTEM tablespace

- Separate Segments

- Separate undo temporary, application data, and application index segments 

- Separate Data by backup requirements

- Separate dynamic and static data 

- Eases Space adminstration

- Controls amount of space allocation to a user

The DBA creates tablespace for increased control and ease of maintenance.  

Creating Tablespaces:

A tablespace is created using the command:  CREATE TABLESPACE:

Example:

CREATE TABLEPSACE userdata  DATAFILE 

'/u01/oradata/userdata01.dbf' size 100m;

Tablespace Creation syntax with more option Details :

Syntax: 

CREATE TABLESPACE tablespace name

[DATAFILE  clause] [MINIMUM EXTENT integer [K|M]]

[BLOCKSIZE integer [K]] [LOGGING | NOLOGGING]

[DEFAULT storage_clause] [ONLINE | OFFLINE ]

[PERMANENT | TEMPORARY] 

[extent_management_clause] [ segment_management_clause]

Where :

TABLESPACE NAME: This is the name of the tablespace to be created.

DATAFILE: This specifies the data file Or Data files name that uses to make up tablespace.

MINIMUM EXTENT: This ensures that every used extent size in the tablespace is a multiple of the integer. Use K or M to specify this size in Kilobytes or Megabytes.

BLOCKSIZE: Specifies a nonstandard block size for the tablespace. In order to specify this clause, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

LOGGING: This specifies that, by default, all tables, indexes, and partition within the tablespace have all changes written to online redo log files. LOGGING is the by default.

NOLOGGING: This specifies that, by default, all tables, indexes, and partition within the tablespace do not have all changes written to the online redo log file. NOLOGGING affects only some DML and DDL commands, For Example, Direct loads.

DEFAULT: DEFAULT clause specifies the default storage parameter for all objects created in the tablespace creation. 

OFFLINE: This clause makes the tablespace unavailable immediately after creation.

PERMANENT: This specifies that the tablespace can be used to hold permanent objects.

TEMPORARY: This specifies that the tablespace can be used to hold temporary objects. For example, segments used by implicit sorts caused by an ORDER BY clause. It cannot specify EXTENT MANAGEMENT LOCAL or the BLOCKSIZE clause.

extent_management_clause: This clause specifies how the extents of the tablespace are managed.

segment_management_caluse: This is relevant only for permanent, Locally managed tablespaces. It lets you specify whether Oracle should track the used and free space in the segments in the tablespace using free lists or bitmaps.

  Review below syntax:

datafile_clause:= file_name [SIZE integer (K|M)] [REUSE] [autoextend_caluse] [NEXT]

Filename : this is the name of a data file in the tablespace.

SIZE : This specifies the size if the file. Use K or M to specify the size in Kilobytes or Megabytes 

REUSE: This allows the Oracle server to reuse and existing file.

autoextend_clause : This Clause enables or disables automatic extention of the data file.

NEXT: This Specifies the size in bytes of the next increment of disk space to be allocated automatically when more extents are required.

Where:

MAXSIZE : this specifies the maximum disk space allowed for automatic extention of the datafile.

UNLIMITED :  This specifies the disk space that can be allocated to the data file, or that the tempfile is not limited. 

Thank you !! 

Archived Redo log File

 Archived Redo Log File :

• Filled online redo log files can be archived.
• There are two advantages in running the database in ARCIHIVELOG mode and archiving online redo log files :

- Recovery: A Database backup togather with online and archived redo log files can guarantee recovery of all committed transaction.

- Backup: This can be performed while the database is open

• By Default, The database is created in NOARCHIVELOG mode. 

One of the important decisions that a database administrator has to make is whether the database is configured to operate in ARCHIVELOG mode or in NOARCHIVELOG mode.

NOARCHIVELOG MODE :

In noarchivelog mode, the online redo log files are overwritten each time an online redo log file is filled, and log switches occur. LGWR does not overwrite an online redo log file group until the checkpoint for that group is completed.

ARCHIVELOG MODE :

If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived. Because all changes made to the database are recorded in the online redo log files,  The Database administrator should take the physical backup of the archived online redo log file. Backup of the archived redo log file is used to recover the database without losing any committed data when Database recovery required.

There are two ways  in which online redo log files can be archived :

• Manually
• Automatically ( recommended method )

The LOG_ARCHIVE_START initialization parameter indicates whether archiving should be automatic or manual when the instance starts up.

• TRUE: TRUE indicates that archiving is automatic. ARCn initiates the archiving of the filled log group at every log switch.
• FALSE: The default value is FALSE, and it indicates that the DBA archives the filled online redo log files manually. The DBA must manually execute a command each time you want to archive an online redo log file. All or specific online redo log files can be archived manually.

Please review some important facts about Archive redo log files :

• Archiving accomplished automatically by ARCn
• Archiving accomplished manually through SQL statements
• When the online redo log file successfully archived below event occurs :

- An entry in the control file is made.

- Records: archive log name, log sequemce number, and high and low system change number (SCN)

• Filled online redo log files cannot be reused until: 

- A checkpoint has taken place

- File has been archived by ARCn

• Can be multiplexed
• Maintained by the DBA

Information about archive logs can be obtained from V$INSTANCE view.

SQL> SELECT archiver FROM V$INSTANCE;

ARCHIVE

-------

STARTED

The archiver parameter having a different status as below.

STOPPED: This status shows that the archiving has stopped working.

STARTED: This status shows that archiving is in progress and archiving is done by the Oracle Background Process ARCn.

FAILED: This status shows that the archiver failed to archive a log last time but will try again within 5 minutes.

Thank you !!

Managing Online Redo Log Files With OMF.

Managing Online Redo Log File With OMF :

• Define the DB_CREATE_ONLINE_LOG_DEST_n parameter :

DB_CREATE_ONLINE_LOG_DEST_1

DB_CREATE_ONLINE_LOG_DEST_2

• A group can be added with no file specification.

ALTER DATABASE ADD LOGFILE;

• Dropping a Group

ALTER DATABASE DROP LOGFILE GROUP 3;

Define the DB_CREATE_ONLINE_LOG_DEST_n  parameter: 

To create online redo log files to be managed by OMF, the DB_CREATE_ONLILNE_LOG_DEST_n  parameter must be defined. The parameter must be set for each multiplexed copy identified by the n value.

In the example above two groups have been created with 2 members each. The names will be generated automatically (such as  "ora_1_wo94n2xi.log") and displayed in the alertSID.log. The default size is 100 MB.

Adding Log File Group:

To create a new group of online redo log files, the DBA uses the ALTER DATABASE ADD LOGFILE command. The command has been modified so that the file specification is not necessary.

In the example above, To add a log file with 2 members, One in the location defined by the 

DB_CREATE_ONLINE_LOG_DEST_1  and one in 

DB _CREATE_ONLINE_LOG_DEST_2.

Unique filenames for the logfile members have generated automatically and displayed in the alertSID.log. the Default Size is 100MB.

Dropping a Group :

The above example drops the log file Group 3, and its operating system files associated with each OMF log file members in Group 3.

Archived Redo Log Files and OMF :

Archived redo log files cannot be OMF.

Obtaining Group and Member Information:

Information about a group and its members can be obtained by querying the following views. 

• V$LOG
• V$LOGFILE

V$LOG  view :

The following query returns information about the online redo logfile from the control file :

SQL> SELECT group#, sequence#, bytes, members, status FROM v$log;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

-------- ---------- ---------- ---------- ----------------

       1     403637  209715200          2 CURRENT

       2     403634  209715200          2 INACTIVE

       3     403635  209715200          2 INACTIVE

       4     403636  209715200          2 INACTIVE

       5     403632  209715200          2 INACTIVE

       6     403633  209715200          2 INACTIVE

6 rows selected.

The following items are the most common values for the Status column : 

• UNUSED: This status indicates that the online redo log file group has never been written to. This is the state of the online redo log file group that was just added.
• CURRENT: This status indicates the current online redo log file group. This implies that the online redo log file group is active.
• ACTIVE: This status indicates that the online redo log file group is active but it is not the current online redo log file group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
• CLEARING: This status indicates that the log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE command. After the log is cleared, the status changes the UNUSED.
• CLEARING_CURRENT: This status indicates that the current log file is being cleared of a closed thread. The log can stay in this status id there is some failure in the switch, such as an input/output (I/O) error writing the new log header.
• INACTIVE: This status indicates that the online redo log file group is no longer needed for instance recovery. It may or may not be archived.

V$LOGFILE  View :

To obtain the names of all the members of a group, query the V$LOGFILE view.

SQL> SELECT * FROM V$LOGFILE;

GROUP# STATUS  TYPE    MEMBER        IS_RECOVERY_DEST_FILE

------- ------- ------- ----------------- ---------------

2            ONLINE  /u01/oradata/ORCL/log02a.rdo       NO

2            ONLINE  /u01/oradata/ORCL/log02b.rdo       NO

1            ONLINE  /u01/oradata/ORCL/log01a.rdo       NO

1            ONLINE  /u01/oradata/ORCL/log01b.rdo       NO

4 rows selected.

 The value of the STATUS column could be one of the following.

• INVALID: This status indicates that the file is inaccessible.
• STALE: This status indicates that the contents of the file are incomplete.
• DELETED: This status indicates that the file is no longer used.
• Blank indicates that the file is in use. 

Thank You !!