Oracle database administration
Download
1 / 55

Oracle Database Administration - PowerPoint PPT Presentation


  • 101 Views
  • Uploaded on

Oracle Database Administration. Session 4 Database Creation. Database Creation Overview. Init<SID>.ora Create database scripts Directory Structure Created. Init<SID>.ora (PFILE). Pfile Example - initE256.ora Contains all required the parameters Location of the Control files

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Oracle Database Administration' - alexa-stanton


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Oracle database administration

Oracle Database Administration

Session 4

Database Creation


Database creation overview
Database Creation Overview

  • Init<SID>.ora

  • Create database scripts

  • Directory Structure Created


Init sid ora pfile
Init<SID>.ora (PFILE)

  • Pfile

  • Example - initE256.ora

  • Contains all required the parameters

  • Location of the Control files

  • Location of the dump and trace files


Server parameter file spfile
Server Parameter File (SPFILE)

  • Used to make persistent changes to the initialization parameters.

  • This eliminates the need to make changes to the init.ora, to preserve updates due to ‘Alter System’ commands.

  • Default location /$ORACLE_HOME/dbs

  • File name spfile<SID>.ora


Server parameter file
Server Parameter File

  • Create spfile='/u01/app/oracle/product/11.1.0test/dbs/

    spfile<SID>.ora' FROM pfile='/u01/app/oracle/admin/test/scripts/

    init<SID>.ora';


The sgadef file
The SGADEF File

  • The contents of this file have been deleted,.

  • If it exists, do not delete this file. It will be desupported in a future release.

  • Location /$ORACLE_HOME/dbs

  • lk<sID> --- lk<test>


The most crucial parameters
The Most Crucial Parameters

  • The following init.ora parameters are the most crucial

    • Db_cache_size

    • * Db_block_buffers*

    • Db_block_size

    • Shared_pool_size

    • log_buffer

    • Sort_area_size

    • sga_target

    • pga_aggregate_target

    • Optimizer_mode


Control files
Control Files

  • The init<SID>.ora file lists the control files

  • There should be at least 3 copies of the control file

  • On a multi-disk system, the control files should be stored on separate disks

  • The database keeps these files in-sync.

  • This reduces the risk of catastrophe failure due to media failures


Control file locations
Control File Locations

control_files = (/u03/oradata/E256/control01.ctl,

/u04/oradata/E256/control02.ctl,

/u05/oradata/E256/control03.ctl)


Dump file locations
Dump File Locations

background_dump_dest = /u01/app/oracle/admin/E256/bdump

core_dump_dest = /u01/app/oracle/admin/E256/cdump

user_dump_dest = /u01/app/oracle/admin/E256/udump

diagnostic_dest = /u01/app/oracle/admin/E256/diag


Parameters
Parameters

  • rollback_segments = (r01,r02,r03,r04)

  • Manual undo

  • #rollback_segments = (r0)

  • optimizer_mode = CHOOSE

  • db_block_size = 8192 (8k)

  • compatible = 11.1.0

  • compatible = 10.2.0 (old)


Parameter value examples
Parameter Value Examples

  • db_files = 100 (Number)

  • db_cache_size = 100 (Mb)

  • shared_pool_size = 1500 (Mb)

  • java_pool_size = 1024000 (mb)

  • processes = 90 (Number)

  • log_buffer = 32768 (kb)

  • undo_management = AUTO (mode)

  • undo_tablespace = UNDOTBS


Parameters1
Parameters

  • To get the deprecated Initialization Parameters in Oracle 11g

    • SQL> SELECT name FROM v$parameter WHERE isdeprecated = ‘TRUE’;

    • Select * from sys.v_$obsolete_parameter;


Parameters2
Parameters

  • Initialization Parameters Deprecated in Oracle Database 11g Release 1 (11.1):

    • BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)

    • COMMIT_WRITE

    • CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)

    • INSTANCE_GROUPS

    • LOG_ARCHIVE_LOCAL_FIRST


Parameters3
Parameters

  • Initialization Parameters Deprecated in Oracle Database 11g Release 1 (11.1):

    • PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)

    • PLSQL_V2_COMPATIBILITY

    • REMOTE_OS_AUTHENT

    • STANDBY_ARCHIVE_DEST

    • TRANSACTION_LAG (attribute of the CQ_NOTIFICATION$_REG_INFO object)

    • USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)


Optimizer
Optimizer

  • There are two main optimization modes

    • Choose uses a cost_based optimization, when all tables are analyzed. This is a data_centric solution

    • Rule uses a rule_based optimization. This is an Oracle_centric solution based on rules set by the rdbms. Deprecated in 10g


Redo log files
Redo Log Files

  • Redo log files should be mirrored

  • To mirror online Redo Log files, use redo log groups

  • Using redo log groups, removes the need for the O/S to maintain mirrored copies

  • The database maintains them automatically, using LGWR


Redo log files1
Redo Log Files

  • The LGWR writes to the Redo Log Group

  • It then cycles through the groups

  • A member of each group should be stored on separate disks. This will remove disk contention between members of each group

  • LGWR will experience little change in performance


Redo log files2
Redo Log files

logfile group 1 ('/u03/oradata/E256/log1mem1.log',

'/u04/oradata/E256/log1mem2.log') size 5M,

group 2 ('/u04/oradata/E256/log2mem1.log',

'/u05/oradata/E256/log2mem2.log') size 5M,

group 3 ('/u05/oradata/E256/log3mem1.log',

'/u03/oradata/E256/log3mem2.log') size 5M


Data dictionary views
Data Dictionary Views

REM # install data dictionary scripts

@/u01/app/oracle/product/11.1.0/rdbms/admin/catalog.sql

@//u01/app/oracle/product/11.1.0/rdbms/admin/catproc.sql

@/u01/app/oracle/product/11.1.0/rdbms/admin/catexp.sql


Automatic rollback
Automatic Rollback

  • Automatic Undo Management allows Oracle to manage the rollback function

  • Choice of managing undo (rollback) segments automatically or manually

  • Controlled by the init.ora parameter ‘undo_management’


Automatic rollback1
Automatic Rollback

  • The undo data is managed in a single undo tablespace

  • Use ‘Create undo tablespace’ command or the ‘undo_tablespace clause of the ‘Create database’ command, to create the tablespace


Automatic rollback2
Automatic Rollback

  • Create UNDO TABLESPACE "UNDOTBS" DATAFILE '/u04/oradata/test/undotbs01.dbf' SIZE 200M


Manual rollback
Manual Rollback

  • The first tablespace created is the System tablespace

  • Create a rollback segment in the SYSTEM tablespace, before creating any more tablespaces

  • This rollback segment is usually called r0

  • Alter this segment online. This way you do not need to shutdown and restart the database.


Manual rollback1
Manual Rollback

Connect as internal

create rollback segment r0 tablespace system

storage (initial 16k next 16k minextents 2 maxextents 20);

Use ALTER ROLLBACK SEGMENT ONLINE to put r0 online

alter rollback segment r0 online;


Manual rollback2
Manual Rollback

create tablespace rollback datafile

'/u05/oradata/E256/rbs01.dbf' size 50M

default storage (

initial 128k

next 128k

pctincrease 0

minextents 2

);


Default accounts
Default Accounts

REM * Alter SYS and SYSTEM users.

alter user sys temporary tablespace temp;

alter user system temporary tablespace temp;


Temp tablespace dictionary managed
Temp Tablespace – Dictionary Managed

create tablespace temp tempfile

'/u04/oradata/E256/temp01.dbf' size 50M

default storage (

initial 128k

next 128k

pctincrease 0

minextents 1

);


Temp tablespace locally managed
Temp Tablespace – Locally Managed

  • CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE'/u04/oradata/test/temp01.dbf' SIZE 40M EXTENT MANAGEMENT LOCAL;


Users tablespace
Users Tablespace

  • CREATE TABLESPACE "USERS" DATAFILE '/u04/oradata/test/users01.dbf' SIZE 250M

    EXTENT MANAGEMENT LOCAL;


Save the output
Save the Output

  • REM * Log output of this script.

  • Give this file a meaningful name

    spool create_db.lst

    .

    .

    .

    spool off


Directory structure
Directory Structure

  • Create an instance directory structure under the /admin directory

  • Example: create an E256 directory

  • Create the following directories under this

    • pfile

    • create

    • udump

    • bdump

    • cdump


Directory structure1
Directory Structure

  • Create /oradata directories under /u03, /u04 and /u05 etc

  • Under those directories, create /E256 directories. This ensures that the files related to this database is separate from those of another database.

  • Example: /u04/oradata/E256


Useful commands
Useful commands

  • You can use the following commands to track and manage datafiles

    • ls -lasd /u*/oradata/<SID>/* lists all the files related to a database

    • rm /u*/oradata/<SID>/* removes all the files related to a database

    • rm /u*/oradata/<SID>/*.ctl removes the control files


Server manager
Server Manager

  • Use Sqlplus /nologin

  • CONNECT / as SYSDBA

  • or

  • CONNECT username/password as sysdba


Start and stop
Start and stop

  • Startup

  • Startup nomount

  • Startup mount

  • Shutdown

  • Shutdown immediate

  • Shutdown abort


V database
V$database

  • SQLPLUS> select name from v$database;

    NAME

    ---------

    E256


Global name
Global Name

  • SQLPLUS> select * from global_name;

    GLOBAL_NAME

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

    E256.HARVARD.EDU


V tablespace
v$tablespace

  • SQLPLUS> select * from v$tablespace;

    TS# NAME

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

    0 SYSTEM

    1 ROLLBACK

    2 TEMP

    3 TOOLS

    4 USERS

  • 5 rows selected.


Show sga
Show SGA

  • Show sga, will give us the size of the sga and breaks it into fixed and variable parts

  • v$parameter

  • V$sga


Unix process
Unix Process

  • We can use

    • ps -eaf | grep dbw -- to show us that the database is up and running

    • ps -eaf | grep oracle -- will give us all the Oracle processes

    • ps -eaf | grep E256 -- will give all the processes related to our database.


Unix process1
Unix Process

elmo oracle $ ps -eaf | grep E256

oracle 16623 1 0 21:44:04 ? 0:02 ora_ckpt_E256

oracle 16625 1 0 21:44:04 ? 0:04 ora_smon_E256

oracle 16621 1 0 21:44:04 ? 0:19 ora_lgwr_E256

oracle 16617 1 0 21:44:04 ? 0:00 ora_pmon_E256

oracle 16627 1 0 21:44:04 ? 0:00 ora_reco_E256

oracle 16619 1 0 21:44:04 ? 0:11 ora_dbw0_E256


Multiple block size support
Multiple Block Size Support

  • The standard database block size was set using db_block_size parameter

  • It cannot be changed after the database is created, it requires the recreation of the database

  • The standard block size is used to create the SYSTEM tablespace


Multiple block size support1
Multiple Block Size Support

  • To use non-standard block size tablespaces, sub-caches must be configured inside the buffer cache area of the SGA

  • Using the db_nK_cache_size parameter, where n = 2, 4, 8 16, or 32


Multiple block size support2
Multiple Block Size Support

  • The db_cache_size parameter replaces the db_block_buffers for the standard block size

  • The db_nK_cache_size parameter must be set for each block size used.

  • The default is 0


Sid sh
<sid>.sh

  • #!/bin/sh

  • mkdir -p /u01/app/oracle/admin/test/diag

  • mkdir -p /u01/app/oracle/admin/test/create

  • mkdir -p /u01/app/oracle/admin/test/pfile

  • mkdir -p /u01/app/oracle/flash_recovery_area

  • mkdir -p /u04/app/oracle/oradata

  • mkdir -p /u18/oradata/test

  • ORACLE_SID=dev; export ORACLE_SID


Sid sh1
<sid>.sh

  • echo Add this entry in the oratab file dev:/u01/app/oracle/product/11.1.0test:Y

  • /u01/app/oracle/product/10.2.0test/bin/sqlplus /nolog @/u01/app/oracle/admin/test/scripts/dev.sql


Sid sql
<sid>.sql

  • @/u01/app/oracle/admin/test/scripts/CreateDB.sql

  • @/u01/app/oracle/admin/test/scripts/CreateDBFiles.sql

  • @/u01/app/oracle/admin/test/scripts/CreateDBCatalog.sql

  • @/u01/app/oracle/admin/test/scripts/JServer.sql

  • @/u01/app/oracle/admin/test/scripts/interMedia.sql

  • @/u01/app/oracle/admin/test/scripts/xdb_protocol.sql

  • @/u01/app/oracle/admin/test/scripts/emRepository.sql

  • @/u01/app/oracle/admin/test/scripts/postDBCreation.sql


Create database
Create Database

  • connect SYS/&&sysPassword as SYSDBA

  • set echo on

  • spool /u01/app/oracle/product/11.1.0test/assistants/dbca/logs/CreateDB.log

  • startup nomount pfile="/u01/app/oracle/admin/test/scripts/init.ora";


Create database1
Create Database

  • CREATE DATABASE "dev"

  • MAXINSTANCES 8

  • MAXLOGHISTORY 1

  • MAXLOGFILES 16

  • MAXLOGMEMBERS 3

  • MAXDATAFILES 100

  • DATAFILE '/u18/oradata/test/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

  • EXTENT MANAGEMENT LOCAL


Create database2
Create Database

  • SYSAUX DATAFILE '/u18/oradata/test/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

  • DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u18/oradata/test/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

  • UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u18/oradata/test/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED


Create database3
Create Database

  • CHARACTER SET UTF8

  • NATIONAL CHARACTER SET AL16UTF16

  • LOGFILE GROUP 1 ('/u18/oradata/test/redo01.log') SIZE 10240K,

    • GROUP 2 ('/u18/oradata/test/redo02.log') SIZE 10240K,

    • GROUP 3 ('/u18/oradata/test/redo03.log') SIZE 10240K

  • USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";

  • spool off


  • Createdbcatalog
    CreateDBCatalog

    • connect SYS/&&sysPassword as SYSDBA

    • set echo on

    • spool /u01/app/oracle/product/11.1.0test/assistants/dbca/logs/CreateDBCatalog.log

    • @/u01/app/oracle/product/11.1.0test/rdbms/admin/catalog.sql;

    • @/u01/app/oracle/product/11.1.0test/rdbms/admin/catblock.sql;

    • @/u01/app/oracle/product/11.1.0test/rdbms/admin/catproc.sql;


    Createdbfiles
    CreateDBFiles

    • connect SYS/&&sysPassword as SYSDBA

    • set echo on

    • spool /u01/app/oracle/product/11.1.0test/assistants/dbca/logs/CreateDBFiles.log

    • CREATE TABLESPACE "USERS" LOGGING DATAFILE '/u18/oradata/test/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

    • EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

    • ALTER DATABASE DEFAULT TABLESPACE "USERS";

    • spool off


    Reading
    Reading

    • 10g and 11g Administrators Guides

      • Part 1

    • Concepts Manual

      • Part 2 Chap 12


    ad