oracle fra空间不足,ORACLE 基础解决方案1_扩大FRA区

ORACLE 基础解决方案(一)

Reviewing

the Database Architecture

Reviewing

the Database Architecture

回顾数据库结构

For page

1-3: Naming the Core Components of an Oracle Database

Server

叫出ORACLE数据库核心构件

1)

The two main components of a basic Oracle Database system: instance

and database.

2)

The Instance consists of memory structures and background

processes.

3)

The three major structures in Oracle Database server architecture

are: memory

structures, process structures and storage

structures.

4)

A session is a connection between the user login and the database

instance.

5)

The graphic on slide 1-4 is the answer to the task on slide

1-3

1)

一个基础oracle数据库系统的两个主要组成部分：实例和数据库

2)

实例包括内存结构和后台进程

3)

ORACLE的三个主要组成结构：内存结构，进程结构和存储结构。

4)

一个会话是指用户登陆和数据库实例的一个连接。

Naming the

Memory Structures of an Oracle Database

命名ORACLE数据库的内存结构

1)

Which are the components of the

PGA: stack space and user global area.

PGA的组成：堆栈空间和用户全局区。

2)

Name the main components of the

SGA:

SGA的主要组成：

- Shared

pool

-

Database buffer cache

-

Redo log buffer

- Large

pool

- Java

pool

- Streams

pool

- Keep

buffer pool

-

Recycle buffer pool

-

nK buffer pool

Adding

Process Names

进程名：

1)

The DBWn process writes the

dirty buffers to the data files.

DBWn进程写脏缓存进数据文件

2)

The LGWR process writes the

redo entries to the online redo log files.

LGWR进程写重做记录进在线重做日志

3)

The CKPT process writes checkpoint information in the control file

and each data file

header.

CKPT进程写检查点信息进控制文件和每个数据文件头

3)

The SMON process performs

recovery on instance startup.

SMON进程在实例启动时候实施恢复。

4)

The PMON process performs

process recovery when a user process fails.

PMON进程在用户进程失败时候进行进程恢复。

5)

The RECO process resolves

in-doubt distributed transactions.

RECO进程解决分布式事务

6)

The ARCn processes copy redo

log files to a designated storage device.

ARCn进程拷贝重做日志文件到一个指定的存储设备。

Configuring ARCHIVELOGMode

配置归档模式

In this

practice, you configure your database to archive redo logs before

reusing them.

改联系，需要你在重新使用归档日志以前进行数据库配置。

1)

Set up the environment for the ORCLdatabase, using the

oraenvscript, and enter

orclwhen

prompted for ORACLE_SID. If it is already set up for orcl(that is,

you

see orclin

the brackets), press Enter.

使用ORAENVSCRIPT建立ORCL数据库环境，当跳出ORACLE_SID时输入ORCL(如果你已经将实例名命名为ORCL)。

Note:This

command is a period, followed by a space, and then the

oraenvscript

name.

注意：该命令是一个逗号，跟着一个空格，然后输入oraenv脚本名。

$ .

oraenv

ORACLE_SID

= [orcl] ? orcl

The Oracle

base for

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

is

/u01/app/oracle

2) Use

labs as your working directory. Unless otherwise indicated, always

work from

that

directory.

建立一个实验文件夹命名为“labs”。除非特殊指定，总是以该文件夹作为工作目录。

$ cd

~/labs

3)

Determine the archive mode

yourdatabase is running in now.

决定你正在运行中的数据库归档模式。

$ sqlplus

/ as sysdba

SQL*Plus:

Release 11.2.0.1.0 Production on Tue Jul 21 14:32:29

2009

Copyright

(c) 1982, 2009, Oracle. All rights reserved.

Connected

to:

Oracle

Database 11g Enterprise Edition Release 11.2.0.1.0 -

Production

With the

Partitioning, Automatic Storage Management, OLAP,

Data

Mining and Real Application Testing options

显示归档日志列表：

SQL>

archive log list

Database

log mode No Archive Mode

Automatic

archival Disabled

Archive

destination USE_DB_RECOVERY_FILE_DEST

Oldest

online log sequence 6

Current

log sequence 8

SQL>

Note that

it is running in NOARCHIVELOGmode.

注意到：数据库不是运行在归档日志模式。

4)

Because you can change the ARCHIVELOGmode only when the database

is

mounted,

shut down the database.

关闭数据库，切换归档日志模式。

SQL>

shutdown immediate

Database

closed.

Database

dismounted.

ORACLE

instance shut down.

5)

Mount the database.

SQL>

startup mount

ORACLE

instance started.

Total

System Global Area 481259520 bytes

Fixed Size

1337324 bytes

Variable

Size 339740692 bytes

Database

Buffers 134217728 bytes

Redo

Buffers 5963776 bytes

Database

mounted.

SQL>

6)

Alter the database to use ARCHIVELOGmode.

SQL>

alter database archivelog;

Database

altered.

SQL>

7)

Open the database.

SQL>

alter database open;

Database

altered.

Resizing

the Flash Recovery Area

重定义FRA大小：

you

enlarge the Flash Recovery Area.

你要扩大FRA

1)

Determine how big the Flash

Recovery Area is now.

先看现在的FRA多大

SQL>

show parameter recovery_file_dest_size

NAME TYPE

VALUE

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

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

db_recovery_file_dest_size big integer 4062M

SQL>

Note that

it is 4062 MB or 4 GB in size.

注意到现在的FRA是4GB大小。

2) How big

couldthe Flash Recovery Area be?

a)

Determine where the Flash Recovery Area is located.

SQL>

show parameter recovery_file_dest

NAME TYPE

VALUE

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

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

db_recovery_file_dest string +FRA

db_recovery_file_dest_size big integer 4062

SQL>

Note that

the Flash Recovery Area is in the FRAdisk group.

Do not

exit from your SQL*Plus session.

注意到FRA配置在FRA磁盘组(使用ASM)

不要退出你的SQL*PLUS

3) Use

thehttps://:1158/emURL to log into Enterprise Manager,

as the

SYSuser with the oracle_4Upassword, and connect as

SYSDBA.

以SYS用户身份连接到OEM

Note:The

first time that you are using a newly configured machine, you may

be asked to

add a

security exception. The exact formulation and number of pages

depends on your

web

browser. Follow the prompts and add the exception in the training

environment.

不要管OEM页面的安全提示。

Note

2:Also if Enterprise Manager shows, that the database is down, wait

a minute and

refresh

the page. (This can occur due to the time intervals in which the

database agent

works.)

如果OEM显示数据库关闭，等一会儿再刷新页面。(可能由于数据库agent长时间不运行导致)

4)

On the database home page,

click the +ASM_ link.

点击+ASM_链接

5)

Click the Disk Groups

tab.

点击进入磁盘组选项

6)

If prompted for ASM login credentials, enter sys as username and

oracle_4Uas

password.

Connect as SYSASM, click “Save as Preferred Credential”, and then

click

Login.

以SYSASM模式SYS用户登陆ASM实例。

7)

When the disk group list appears, note the Usable Free column for

the FRAdisk

group.

当出现磁盘组列表，注意FRA磁盘组的空闲空间列。

注意到：FRA磁盘组有足够空间然你扩大FRA区域。

8)

Change the Flash Recovery Area size to 6 GB.

改变FRA区尺寸为6GB。

a)

Navigate to Database > Availability > Recovery Settings and

change the Flash

Recovery

Area Size to 6 GB.

b)

Click Show SQL, and note the SQL thatwill be run. This is important

to know

because if

the Flash Recovery Area ishaving sizing problems, you may not

be

able to

run Enterprise Manager to change it.

点击显示SQL，并记下该条SQL改动FRA的语句。如果FRA区域不足，你可能没办法运行OEM来进行更改。

ALTER

SYSTEM SET db_recovery_file_dest_size = 6442450944

SCOPE=BOTH

c)

Click Return, and then click Apply.

点击返回，点击应用。

9)

Verify the size of the Flash Recovery Area by using SQL*Plus. Then

exit your

核对FRA区大小。

SQL*Plus

session.

SQL>

show parameter recovery_file_dest_size

NAME TYPE

VALUE

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

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

db_recovery_file_dest_size big integer 6G

SQL>

exit

Disconnected from Oracle Database 11g Enterprise

Edition

Release

11.2.0.1.0 - Production

With the

Partitioning, Automatic Storage Management, OLAP,

Data

Mining

and Real

Application Testing options

Verifying

the Backup Destination

核实你的备份路径。

In this

practice, you test the backup destination to see where backups are

written.

Use the

oraenvscript to ensure you are still using the orclinstance in your

terminal

session.

该练习中，测试备份目的地来查看备份写的位置。

$ .

oraenv

ORACLE_SID

= [orcl] ? orcl

1) Start

RMAN.

$ rman

target /

Recovery

Manager: Release 11.2.0.1.0 - Production on Tue Jul

21

14:52:20 2009

Copyright

(c) 1982, 2009, Oracle and/or its affiliates. All

rights

reserved.

connected

to target database: ORCL (DBID=1220535480)

RMAN>

2)

See if the control files are

automatically backed up.

查看控制文件是否为自动备份。

RMAN>

show CONTROLFILE AUTOBACKUP;

using

target database control file instead of recovery catalog

RMAN

configuration parameters for database with

db_unique_name

ORCL

are:

CONFIGURE

CONTROLFILE AUTOBACKUP OFF; # default

RMAN>

Note that

automatic backup is not enabled.

注意到自动备份控制文件没开。

3)

Configure RMAN to automatically back up the control file when any

backups are

done.

配置RMAN在任何备份完成时候自动备份控制文件。

RMAN>

configure controlfile autobackup on;

new RMAN

configuration parameters:

CONFIGURE

CONTROLFILE AUTOBACKUP ON;

new RMAN

configuration parameters are successfully stored

RMAN>

4)

List the database files in your schema, to understand which file

you back up in the

next

task.

列出你所在模式下的数据文件，明白接下去的任务中你要备份什么文件。

RMAN>

report schema;

Report of

database schema for database with db_unique_name

ORCL

List of

Permanent Datafiles

Practice

2-3: Verifying the Backup Destination (continued)

Oracle

Database 11g: Administration Workshop II A - 13

===========================

File

Size(MB) Tablespace RB segs Datafile Name

----

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

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

1 680

SYSTEM YES

+DATA/orcl/datafile/system.256.692754557

2 590

SYSAUX NO

+DATA/orcl/datafile/sysaux.257.692754559

3 100

UNDOTBS1 YES

+DATA/orcl/datafile/undotbs1.258.692754561

4 5 USERS

NO

+DATA/orcl/datafile/users.259.692754561

5 100

EXAMPLE NO

+DATA/orcl/datafile/example.265.692754837

List of

Temporary Files

=======================

File

Size(MB) Tablespace Maxsize(MB) Tempfile Name

----

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

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

1 28 TEMP

32767 +DATA/orcl/temp01.dbf

RMAN>

5)

Which tablespace is stored in file 5?

Answer:

The EXAMPLEtablespace

6)

Take a backup of data file 5, and note where the backup is written,

then exit.

备份数据文件5，注意到备份目的地，然后退出。

RMAN>

backup datafile 5;

Starting

backup at 21-JUL-09

allocated

channel: ORA_DISK_1

channel

ORA_DISK_1: SID=55 device type=DISK

channel

ORA_DISK_1: starting full datafile backup set

channel

ORA_DISK_1: specifying datafile(s) in backup set

input

datafile file number=00005

name=+DATA/orcl/datafile/example.265.692754837

channel

ORA_DISK_1: starting piece 1 at 21-JUL-09

channel

ORA_DISK_1: finished piece 1 at 21-JUL-09

piece

handle=+FRA/orcl/backupset/2009_07_21/nnndf0_tag20090721t14535

8_0.260.692808839 tag=TAG20090721T145358

comment=NONE

channel

ORA_DISK_1: backup set complete, elapsed time:

00:00:15

Practice

2-3: Verifying the Backup Destination (continued)

Oracle

Database 11g: Administration Workshop II A - 14

Finished

backup at 21-JUL-09

Starting

Control File and SPFILE Autobackup at 21-JUL-09

piece

handle=+FRA/orcl/autobackup/2009_07_21/s_692808854.261.6928088

57

comment=NONE

Finished

Control File and SPFILE Autobackup at 21-JUL-09

RMAN>

RMAN>

exit

Note that

the backup file is written to the FRA.

注意到备份写进了FRA区域。