Oracle DBA Internals

This page is intended to submit Oracle DBA Internals related Postings

ORACLE INTERNALS

Oracle Internals for a Single Instance

1)      Physical and Logical Structures of Database

2)      Instance (SGA & Background Processes) and it components

3)      SGA & PGA processes

4)      Parameters that govern the size of Control File

* maxlogfile

* maxlogmembers

* maxloghistory

* maxinstances

* control_file_record_keep_time

Specifying a Flash Recovery Area

DB_RECOVERY_FILE_DEST: Location of the flash recovery area. This can be a directory, file system, or Automatic Storage Management (ASM) disk group. It cannot be a raw file system.

DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum total bytes to be used by the flash recovery area. This initialization parameter must be specified before DB_RECOVERY_FILE_DEST is enabled.

Specifying Control Files

The CONTROL_FILES initialization parameter specifies one or more control filenames for the database. When you execute the CREATE DATABASE statement, the control files listed in the CONTROL_FILES parameter are created.

CREATE CONTROLFILE USE RESET LOGS ARCHIVE LOG

   SET DATABASE prod

   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', 

                                   '/u01/oracle/prod/redo01_02.log'),

               GROUP 2 ('/u01/oracle/prod/redo02_01.log', 

                                   '/u01/oracle/prod/redo02_02.log'),

                GROUP 3 ('/u01/oracle/prod/redo03_01.log', 

                                   '/u01/oracle/prod/redo03_02.log') 

   DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,

            '/u01/oracle/prod/rbs01.dbs' SIZE 5M,

            '/u01/oracle/prod/users01.dbs' SIZE 5M,

            '/u01/oracle/prod/temp01.dbs' SIZE 5M

   MAXLOGFILES 50

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 400

   MAXDATAFILES 200

   MAXINSTANCES 6

TEMP_TABLESPACE=TEMP;

UNDO_TABLESPACE=UNDOTBS1;

UNDO_MANAGEMENT=AUTO;

Specifying Database Block Sizes - DB_BLOCK_SIZE Initialization Parameter

UNDO_MANAGEMENT Initialization Parameter :  The UNDO_MANAGEMENT initialization parameter determines whether or not an instance starts in automatic undo management mode, which stores undo in an undo tablespace. Set this parameter to AUTO to enable automatic undo management mode. Beginning with Release 11g, AUTO is the default if the parameter is omitted or is null.

UNDO_TABLESPACE Initialization Parameter

Tools for Administering your Database:-

·         Oracle Universal Installer

·         Oracle Database Configuration Assistant (DBCA)

·         Database Upgrade Assistant (DBUA)

·         Net Configuration Assistant (NETCA)

·         Oracle Enterprise Manager Database Control (./emctl start dbconsole, ./emctl stop dbconsole, ./emctl status dbconsole)

INIT.ORA PARAMETER FILE

Unix / Linux :- ORACLE_HOME/dbs

Windows:- ORACLE_HOME/database

DB_NAME = test

DB_DOMAIN = us.acme.com

Commands:-

ALTER DATABASE RENAME GLOBAL_NAME TO new_domain

ALTER DATABASE BACKUP CONTROLFILE TO TRACE

Changing the DBID and DB Name:-

Step1:-

SHUTDOWN IMMEDIATE

STARTUP MOUNT

Step2:- Run nEW DBid utility

% nid TARGET=SYS

% nid TARGET=SYS DBNAME=test_db

Step3:- Run New Database Mount and Reset Logs

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';

CREATE PFILE='/u01/oracle/dbs/test_init.ora'

       FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=3

                 COMMENT='Reduce from 10 for tighter security.'

                 SCOPE=SPFILE;

SCOPE = SPFILE    The change is applied in the server parameter file only.

SCOPE = MEMORY    The change is applied in memory only

SCOPE = BOTH      The change is applied in both the server parameter file and memory.