DOC

Oracle DBA Case Sample

By Judy Clark,2014-05-23 23:16
11 views 0
Oracle DBA Case Sample

? Preface

    Requirements from Enterprises

    From http://www.yahoo.com

From http://www.seek.com.au

Versions of Oracle

    Oracle Release Date Characteristic

    Oracle 1 1979 Assemble Language

    Oracle 2 1980

    Oracle 3 1981 C Language

    Oracle 4 1984

    Oracle 5 1986

    Oracle 6 1988

    Oracle 7 1993

    Oracle 8 1997

    Oracle 8i 1999 Java Language

    Oracle 9i 2001 Memory 512M

    Oracle 10g 2004 Memory 1G

    Oracle 11g 2007 Memory 2G / Orient Cluster and Distributed System

? Install Oracle 10g

    Window Platform Installation

    1uninstall (if necessary)

    (1) Stop all the Oracle services

    (2) Use <> to remove installed Oracle products

    (3) Remove the items from registry and Oracle directory

    ? Use regedit to backup registry

    ? Remove nodes: HKEY_CLASSES_ROOT\ORA*,ORCL*

    ? Remove node: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

    ? Remove nodes: HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\ORACLE*

    ? Restart Computer or Log off

    ? Remove Oracle main directory

    2install

    Close all Firewall software

    Copy all the setup files to the disk root directory such as C:\

    Run setup.exe

    Note: Install mode: interactive or silent

    3others

    SQL> Key word uppercase, others lowercase

    SQL>Alter user scott identified by tiger account unlock;

    Tools Port stores in $Oracle_Base\product\10.2\db_1\install\portlist.ini

Make a full backup:

    C:\oracle\product\10.1.0\Db_1\database

    C:\oracle\product\10.1.0\oradata\orcl

|| 2*45 min

Redhat Linux AS 4 Platform Installation

    Check the installation requirements

    grep MemTotal /proc/meminfo

    grep SwapTotal /proc/meminfo

    -- grep (global search regular expression(RE) and print out the line /find string in several txt files

    Check Disk

    Oracle 10g needs at least 2.5GB and oracle needs extra 1.2GB

    df h

Create Oracle account and group

    /usr/sbin/groupadd oinstall

    /usr/sbin/groupadd dba

    /usr/sbin/useradd -m -g oinstall -G dba oracle

    -m automatic login entry group

    -g group belong to

    -G extra appendix group id oracle -- 501 502

    setup oracle password passwd oracle

    Create directories

    mkdir /opt/oracle /opt/oracle/product /opt/oracle/product/10.2.0

    mkdir -p /u01/oradata --p if some folds do not exist, it will create automatically chown -R oracle:oinstall /opt/oracle /u01/oradata R recursive for all sub directories

    chmod -R 775 /opt/oracle /u01/oradata -readr1writew2executex4

Configure Linux kernel parameters

    /etc/sysctl.conf

    Such as kernel.shmall = 2097152

    kernel.shmmax = 2147483648

    kernel.shmmni = 4096

    kernel.sem = 250 32000 100 128

    fs.file-max = 65536

    net.ipv4.ip_local_port_range = 1024 65000

    net.core.rmem_default=262144

    net.core.wmem_default=262144

    net.core.rmem_max=262144

    net.core.wmem_max=262144

Activate the changes:

    /sbin/sysctl -p

    oracle environment variables

    use oracle to log in.bash_profile or .profilebash or ksh:

    export ORACLE_BASE=/opt/oracle

    export ORACLE_HOME=$ORACLE_BASE/product/10.2.0

    export ORACLE_SID=orcl (here is your real database name)

    PATH=$PATH:$ORACLE_HOME/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin export PATH=$PATH:$HOME/bin:/bin:/sbin:/usr/bin

    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib

    NLS_LANG=$‟SIMPLIFIED CHINESE_CHINA.ZHS16GBK‟

    [root@domain]#source /home/oracle/.bash_profile

    install Oracle

    Unzip oracle_linux_10.2.zip

    ./runInstaller

Stop and start Oracle Enterprise Manager

    $ emctl start dbconsole

    $ emctl stop dbconsole

Stop and start iSQL*Plus

    $ isqlplusctl start

    $ isqlplusctl stop

    You can check etc/orainst.loc oratab which are associated with oracle installation

    ? Oracle FrameworkInstance and Data Dictionary

    Requirements: More than 90% Operation in memory.

    Oracle Server

    Oracle Database

    Consists of data in physical and logical structure, referring to some OS files.

    Oracle Instance

    Startup listener and OracleServiceName.

    Instance comprises of memory structure and background processes.

    Note: Cluster Application Many Instances use one Database.

Instance

    ? Database Buffer Cache / Dirty Data

    ? Redo Log Buffer /SCN

    ? Shared Pool /Library Cache and Date Dictionary ? Large Pool

    ? Java Pool

    SQL>SELECT * FROM V$INSTANCE

    SQL>Show sga

    SQL>Show parameter

    SQL>Select name, type, value from V$parameter where name=sga_max_size

    Note: col name for a20, col value for a20 or third software such as PLSQL Developer

Processes

    User Process

    Server process

    Oracle Process Types:

    ? Dedicated Server Process

    ? Shared Server Process

    Background process

    ? LGWR / When writing activated

    ? DBWN / When writing activated

    ? SMON / Rollback transactions because of power exception

    ? PMON / Similar to the above , suitable to one process

    ? CKPT / Recover more faster , after the latest checkpoint

    ? ARCH / Offline Log Files

    Process Query:

    SQL> SELECT NAME, VALUE, DESCRIPTION FROM V$PARAMETER WHERE NAME LIKE '%processes%„

    SQL> SELECT SID, USERNAME, STATUS, TERMINAL, PROGRAM, TO_CHAR(LOGON_TIME,'YYYY-MON-DD

    HH:MI:SS') LOGON_TIME FROM V$SESSION WHERE TYPE = 'USER';

|| 2*45 min

    SQL working process

    ? User Process -->Server Process //Note: This kind of connection is called session ? SQL>Update emp set sal = &v_sal; Commit;

    ? Server checks the existence of the parse code for the SQL in shared pool.(Y/N) ? New data ->Data Buffer Cache / / LRU

    ? Rollback segment stores the previous data

    ? Write into log files by LGWR.

    ? Write into dbf files by DBWN if necessary

Oracle Tools Softwares

    ? DBCA

    ? OEM

    ? ODBC Test Oracle

    Practice connect sys/system as sysdba in cmd mode.

Parameter File

    Parameter File is an OS file read by instance when it starts. The oracle server processes and background

    processes are arranged according to the spfile.

    (1) SPFILE.ORA

    Binary File, modified by SQL PLUS and so on

(2) PFILE

    Text File, used before Oracle 9i, edited by notebook.

    Shortcoming: Always need to restart DB to apply the new parameter.

Content:

    SQL>SELECT NAME, VALUE, DESCRIPTION FROM V$SYSTEM_PARAMETER order by name;

    The Format in SPFILE:

    ? Some default

    ? Keywordvalue

    ? Many parameters can be separated by comma such as control_files

    Spool:

    Spool d:\parameter;

    SELECT NAME, VALUE, DESCRIPTION FROM V$SYSTEM_PARAMETER order by name;

    Spool off;

    Configure the Sever Parameter File

    There are 3 kinds of parameters

    Type Note

    Dynamic Come into effect at once in memory

    Static Restart DB

    Read Only db_name

    SQL>Alter system set name = value scope = SPFILEMEMORYBOTH;

    Scope Note

    SPFILE Restart DB

    MEMORY Come into effect at once

    BOTH Default Option

SQL> Alter system set user_dump_dest="e:\";

    SQL>Alter system set db_files = 400;

    SQL> Alter system set db_files = 400 SCOPE=SPFILE;

PFILE SPFILE

    || 2*45 min

Startup

SQL>STARTUP NOMOUNT

    Search SPFILE

    Start Instance to build up memory and background process

    Nothing to do with control filesdata files and log files.

    ? Create New DB

    ? Rebuild Control files

    SQL>STARTUP MOUNT

    Read control files

Load data files

    ? Rename DBF Filename

    ? Restore DB

    ? Change DB to archive log mode

     SQL>STARTUP (OPEN)

    OPEN DATAFILES

    ? Can‟t rename filename

SQL>Startup force

    SQL>STARTUP NOMOUNT

    SQL>ALTER DATABASE MOUNT

    SQL>ALTER DATABASE OPEN

    SQL>STARTUP RESTRICT // maintain db or export data SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION SQL>ALTER DATABASE OPEN READ ONLY //Should be in mount status

    SQL>ALTER DATABASE OPEN READ WRITE

Shutdown

    Shutdown process is opposite to Startup process.

Report this document

For any questions or suggestions please email
cust-service@docsford.com