小布老师讲座笔记（四）

 

小布老师讲座笔记（四）

charpter 4 database

After completing this lesson ,you should be able to do the following:

★   List the prerequisites necessary for database creation

★   Create a database using Oracle Database Configuration Assistant

★   Create a database manually

★   Create a database using Oracle Managed Files

Plannning and Organinzing a DB

Planning for your database is the first step in managing a database system.

★   Define the purpose of the database.

★   Define the type of the database.

★   Outline a database architectural design

★   Choose the database name

★   Choose the database name.

Use Oracle Data Migration Assistant to migrate from a earlier version of the database.

Creation Prerequisites

To create a new database, you must have the following :

A privileged account authenticated by one of the following:

   ★Operating system

★Password file

Authentication Methods

Remote database administration , Local database administration

do you have a secure connection? do you want fo use OS authentication OS authentication

use a password file

Password File Authentication

★   Create the passwd file using the password utility

orapwd file=$ORACLE_HOME/dbs/orapwU15 passord=admin entries=5

★   Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in initialization parameter file

★   Add users to the password file

★   Assign appropriate priviledges to each user.

grant sysdba to hr

Set the following environment variables:

★   ORACLE_BASE

★   ORACLE_HOME

★   ORACLE_SID

★   ORA_NLS33

★   PATH

★   LD_LIBARARY_PATH

Creating a DB Manually

★   Choose a unique instance and database name

★   Choose a database character set

★   Set operating system variables

★   Create the initialization parameter file

★   Start the instance in NOMOUNT stage

★   Create and execute the CREATE DATABASE

command

★   Run scripts to generate the data dictionary and accomplish post-creation steps

★   Create additional tablespaces as needed

Creating a DB Using OMF

Define the OMF parameter

parameter file, Example:

★   DB_CREATE_FILE_DEST=/$HOME/ORADATA/u05

★   DB_CREATE_ONLINE_LOG_DEST_1=/$HOME/ORADATA/u01

★   DB_CREATE_ONLINE_LOG_DEST_2=/$HOME/ORADATA/u02

★   @cddba01.sql  CREATE DATABASE dba01;

Chapter 5

Using Data Dictionary and Dynamic Performance Views

After completing this lesson, you should be able to do the following:

★   Identify built-in database objects

★   Identify the contents and uses of the data dictionary

★   Describe how data dictionary views are created

★   Identify data dictionary view categories

★   Query the data dictionary and dynamic performance views

★   Describe administrative script naming conventions

Other objects created with the database

★   Data dictionary

★   Performance tables

★   PL/SQL packages

★   Database event triggers

Data Dictionary

★   Central to every Oracle database

★   Describes the database and its objects

★   Contains read-only tables and views

★   Stored in the SYSTEM tablespace

★   Owned by the user SYS

★   Maintained by the Oracle Server

★   Accessed with SELECT

Base Tables && DD Views

The data dictionary contains two pars:

   *Base tables(sql.bsq创建)

   ▼Stores description of the database

   ▼Created with CREATE DATABASE

Data dictionary views（catalog.sql）

   ▼Used to simplify the base table information

   ▼Accessed through public synonyms

   ▼Created with the catalog.sql script

Data Dictionary Contents

The data dictionary provides information about:

   ▼Logical and physical database strutures

   ▼Definitions and space allocations of objects

   ▼Integrity constraints

   ▼Users

   ▼Roles

   ▼Privileges

How the DD Is Used

Primary uses:

   ▼Oracle server uses it to find information about

      ﹟Users

      ﹟Schema objects

      ﹟Storage structures

   ▼Oracle server modifies it when a DDL statement is executed.

▼Users and DBAs use it as a read-only reference for information about the database.

Destinguished by their scope:

   ▼DBA: What is in all the schemas

   ▼ALL: What the user can access

   ▼USER: What is in the user’s schema

DD Example

★   General overview: DICTIONARY, DICT_COLMNS

★   Schema objects:DBA_TABLES, DBA_INDEXES

★   DBA_TAB_COLUMNS, DBA_CONSTRAINTS

★   Space allocation: DBA_SEGMENTS,DBA_EXTENTS

★   Database structure:DBA_TABLESPACES, DBA_DTATA_FILES

Dynamic Performance Table

★   Virtual tables

★   Record current database activity

★   Continually undated while the database is operational

★   Information is accessed from memory and control file

★   Used to monitor and tune the database

★   Owned by SYS user

★   Synonyms begin with V$

★   Listed in v$FIXED_TABLE

Admin Script Naming

cat*.sql     Catlog and data dictionary information

dbms*.sql    Database package specifications

prvt*.plb    Wrapped database package code

utl*.sql     Views and tables for database utilities

DP Examples;

v$controlfile, v$database, v$datafile, v$instance, v$parameter, v$session, v$sga, v$spparameter, v$tablespace,

v$thread, v$version.

dba_data_files. dba_free_space, dba_segments, dba_users

Chapter 6

Maintaining the control file

Objectives: After completing this lesson, you should be able to do the following:

★   Explain the uses of the control file

★   List the contents of the control file

★   Multiplex and manage the control file

★   Manage the control file with Oracle Managed Files(OMF)

★   Obtain control file information

Control file

★   A small binary file

★   Defines current state of physical database

★   Maintains integrity of database

★   Required:

★   At MOUNT state during database start up

★   To operate the database

★   Loss may require recovery

★   Sized initially by CREATE DATABASE

The Size of Control File

   The main determinants of the size of a control file are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCE parameters in the CREATE DATABASE statement that created associated database statement that created assocaiated database, Increasing the values of these of these parameters increases the size control file of the associated database.

multiple control files

When Using SPFILE

1.Alter the SPFILE

alter system set

control_files='/u01/oradata/bbk/control01.ctl','/u01/oradata/bbk/control02.ctl','/u01/oradata/bbk/control03.ctl','/u01/oradata/bbk/control04.ctl'

2.Shut down the database

shutdown immediate

3.Create additional control files: cp ctrl01.ctl ctl04.ctl

4.Start the database startup

when using the pfile

1.  shut down the database

shutdown immediate

2.  Create additional control files:

cp ctrl01.ctl ctrl05.ctl

3.  Add control file names to pfile:

4.  startup the database

Using OMF

★   OMF is created if the CONTROL_FILES parameter is not specified .

★   Locations are defined by DB_CREATE_ONLINE_LOG_DEST_n.

★   Names are uniquely generated and displayed in the alertSID.log.

Get Control File Information

Information about control file status and locations can be retrieved by querying the following views.

★   V$CONTROLFILE: Lists the name and status of all control files associated with the instance v$PARAMETER:Lists status and location of all parameters.

★   V$CONTROLFILE_RECORD_SECTION: Provides information about the control file record sections

★   SHOW PARAMETER CCONTROL_FILES: List the name, status, and location of the control files.

Chapter 7 Maintaining Online Redo Log Files

After completing this lesson, you should be able to do the following:

★   Explain the purpose of online redo log files

★   Outline the structure of online redo log files

★   Control log switches and checkpoints

★   Multiplex and maintain online redo log files

★   Manage online redo logs files with OMF

★   Obtain online redo log file information

Using Online Redo Log Files

Online Redo log files have the following

characteristics:

★   Record all changes made to data

★   Provide a recovery mechanism

★   Can be organized into groups

★   At least two groups required

Online Redo Log File Groups

★A set of identical copies of online redo log files is called

   an online redo log file group.

★   The LGWR background process concurrently writes the same information to all online redo log files in a group.

★   The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database.

Online Redo Log File Member

★   Each online redo log file in a group is called a member

★   Each member in a group has identical Log Sequence Number and are of the same size.

★   The LSN(Log Sequence Number) is assigned each time that the Oracle server writes to a log group to uniquely identifiy each online redo log file.

How Redo Files Work ?

★   Online Redo Log files are used in a cyclic fashion.

★   When a online redo log file is full, LGWR will move to the next log group.

﹟Called a log switch

﹟Checkpoint operation also occurs

﹟Information written to the control file

The act of switching from one log file group to the other is called a log switch. A checkpoint is the writing of dirty(modified)blocks from the buffer cache to disk.

 

When will LGWR write redo?

★   When a transaction commits.

★   Every three seconds

★   When the Redo Log Buffer becomes one-third full.

★   When there is more than a megabyte of changed records in the Redo Log Buffer.

★   Before the DBWn writes modified blocks in the Database Buffer Cache to the data files.

★   alter system switch logfile;

手动切换日志文件

     SQL> select group#, thread#, sequence#, status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS

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

         1          1          7 CURRENT

         2          1          5 ACTIVE

         3          1          6 ACTIVE

★Force Log Switches & CK

  Forcing a log switch:

  alter system switch logfile;

★Checkpoints can be forced by:

★    etting fast_start_mttr_parget=600

★   alter system checkpoint;

add logfile

alter database add logfile group 4

2         ('/u01/oradata/bbk/redo04a.rdo') size 100M;

Adding Redo File Members

alter database add logfile member

‘/u01/oradata/bbk/redo4b.rdo’ to group 4;

alter database drop logfile group 3;

Relocate & Rename

Relocate or rename online redo log files in one of the two following ways:

★   alter database rename file command

﹟Shutdown the database.

﹟Copy the online redo log files to the new location

﹟Place the database in mount mode.

﹟Execute the command.

﹟Open database for normal operation.

alter database rename file ‘$home/oradata/u01/log2a.rdo’ to ‘$home/oradata/u02/log1c.rdo’;

 Add new members and drop old members.