oracle 数据文件、表空间、日志文件、控制文件数据库管理

实验四 oracle 数据库管理

一、试验目的

掌握对数据文件、表空间、日志文件、控制文件的常用命令，作为DBA的必要准备。

二、实验内容

2.1 数据文件的管理

（1）在安装完毕之后，在INITsid.ORA参数文件有一个DB_FILES 参数，用于设置当前实例的数据外文件的个数。如：

db_files = 80

如果在INITsid.ORA文件没有该参数，则可以用下面查询语句从视图中查到。如：

SQL> col name for a20

SQL> col value for a50

SQL> set lin 100

SQL> select name,value from v$parameter where name = 'db_files';

NAME V ALUE

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

db_files 1024

（2）行命令建立表空间：

例1 CREATE TABLESPACE user_stu

DA TAFILE 'h:/oracle/oradata/orcl/user_stu.dat' SIZE 20M

DEFAULT STORAGE

( INITIAL 10K

NEXT 50K

MINEXTENTS 1

MAXEXTENTS 99

PCTINCREASE 10 )

ONLINE ;

例2：建立一个新的表空间，具有两个数据文件：

CREATE TABLESPACE CRM_TAB

DA TAFILE 'h:/oracle/oradata/orcl/crm01.dbf' size 10 MB,'h:/oracle/oradata/orcl/crm02.dbf' size 10 MB;

（3）对一个已存在的表空间追加新数据文件：

例1 ALTER TABLESPACE user_stu

Add datafile 'H:/oracle/oradata/orcl/user_stu01.dbf' size 30M;

例2 为表空间增加数据文件

ALTER TABLESPACE users

ADD DATAFILE 'userora1.dbf ' SIZE 10M ;

（4）数据文件更名

ALTER TABLESPACE users

RENAME DATAFILE? 'filename1', 'filename2'

TO 'filename3', 'filename4' ;

（5）变更数据文件大小

在创建表空间时，可以将表空间说明为自动扩展或固定大小。因而管理员的一项工作就是查看系统所有的表空间对应的数据文件情况。看是否为自动扩展。如：

SQL> col tablespace_name for a12

SQL> col file_name for a48

SQL> select tablespace_name,file_name,AUTOEXTENSIBLE ,bytes from dba_data_files

（6）数据文件的自动扩展与调整。重新调整数据文件大小的命令如下：

ALTER DATABASE DATAFILE [datafile_name] RESIZE [new_size]；

当我们发现数据文件过大而不可能用完时，可以用上面命令将数据文件调小。

对于设置数据文件的自动扩展问题，可用下面命令来达到：

ALTER DATABASE DATAFILE [ file_spec ]

AUTOEXTEND ON NEXT [increment_size] MAXSIZE [max_size，UNLIMITED]；

如：

ALTER DATABASE DATAFILE 'H:\ORACLE\ORADA TA\ORCL\USER_STU01.DBF' AUTOEXTEND ON NEXT 10m MAXSIZE 60M;

（7）行命令修改表空间：

ALTER TABLESPACE USER_STU

ONLINE ;

（8）ALTER TABLESPACE accounting

OFFLINE NORMAL;

（9）行命令删除表空间：

DROP TABLESPACE 表空间名

INCLUDING CONTENTS

CASCADE CONSTRAINTS

(10)、与数据文件有关的视图

select file_name from dba_data_files;

select * from v$datafile;

DBA_DATA_FILES

DBA_EXTENTS

DBA_FREE_SPACE

V$DA TAFILE

V$DA TAFILE_HEADER

(11)、为了保证表空间的可用，除了掌握表空间的创建外，还应该查看dba_free_space中表空间的信息，以确保系统正常运行。一般管理员应该关心的内容有：表空间共有多少个；总共有多少自由空间；最大的自由空间是什么；下面例子是一个经常使用的脚本，可以查出数据文件和表空间的可用情况。

clear buffer

clear columns

clear breaks

column a1 heading 'Tablespace' format a15

column a2 heading 'data File' format a45

column a3 heading 'Total|Space' format 999,999.99

column a4 heading 'Free|Space' format 999,999.99

column a5 heading 'Free|perc' format 999,999.99

break on a1 on report

compute sum of a3 on a1

compute sum of a4 on a1

compute sum of a3 on report

compute sum of a4 on report

set linesize 120

select a.tablespace_name a1, a.file_name a2, a.avail a3, nvl(b.free,0) a4,

nvl(round(((free/avail)*100),2),0) a5

from (select tablespace_name, substr(file_name,1,45) file_name,

file_id, round(sum(bytes/(1024*1024)),3) avail

from sys.dba_data_files

group by tablespace_name, substr(file_name,1,45),

file_id) a,

(select tablespace_name, file_id,

round(sum(bytes/(1024*1024)),3) free

from sys.dba_free_space

group by tablespace_name, file_id) b

where a.file_id = b.file_id (+)

order by 1, 2;

(12) 查询是否存在表的扩展超出表空间可用大小

一般在系统使用较长时间后，表空间的连续块被多次的修改与删除等操作后出现了许多的不连续的块（叫碎片）。这样就有可能出现表的扩展所需要的连续块不能满足的情况。为了避免这样的情况发生而导致扩展失败，管理员要经常查询系统的表空间情况。下面就是这样的一个脚本：

Col segment_name for a20

Select segment_name, segment_type, owner, a.tablespace_name tablespace,

Initial_extent, next_extent, pct_increase,b.bytes max_bytes

From dba_segments a,

( select tablespace_name, max(bytes) bytes from dba_free_space

group by tablespace_name ) b

where a.tablespace_name=b.tablespace_name and next_extent > b.bytes ;

(13) 查询表空间自由、最大及碎片

其实，平常管理最关系就是表空间的总量、最大字节、使用多少、碎片多少等。下面脚本可以查询出所有表空间的自由空间、总空间数、已用空间、自由百分比及最大块的字节数。set pau off

col free heading 'Free(Mb)' format 99999.9

col total heading 'Total(Mb)' format 999999.9

col used heading 'Used(Mb)' format 99999.9

col pct_free heading 'Pct|Free' format 99999.9

col largest heading 'Largest(Mb)' format 99999.9

compute sum of total on report

compute sum of free on report

compute sum of used on report

break on report

select substr(a.tablespace_name,1,13) tablespace,

round(sum(a.total1)/1024/1024, 1) Total,

round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,

round(sum(a.sum1)/1024/1024, 1) free,

round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,

round(sum(a.maxb)/1024/1024, 1) largest,

max(https://www.360docs.net/doc/1b8579514.html,t) fragment

from

(select tablespace_name, 0 total1, sum(bytes) sum1,

max(bytes) MAXB,

count(bytes) cnt

from dba_free_space

group by tablespace_name

union

select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files

group by tablespace_name) a

group by a.tablespace_name

2.2 日志文件的管理

（0）日志查询

select group#,bytes from v$log;?

select group#,bytes from v$logfile;

（1）实现强行的日志切换

Alter system switch logfile;

（2）检测点（checkpoint）

Oracle为了在出现故障后能退回去重演原来的信息，就需要一个叫开始点。在这个开始点时刻，数据和事务是已知的。这样的开始点就叫检测点。

在Oracle里，只要检查点一到（出现）。Oracle就强行将当前的SGA中的redo区的改动过的块写入重做日志文件中。这个步骤完成后，在重做日志文件中放入一个特殊的检测点标志记录。如果在下一个检测点完成前出现失败，恢复操作进程就会在日志文件和数据文件前一个检测点同步（改回去）。检测点检查完成后，对数据块的任何改动都记录在其检测点标志后写入重做日志项中。因此，恢复也就只能从最近的检测点标志记录开始。

Oracle在INITsid.ORA文件中给出LOG_CHECKPOINT_INTERV AL参数可以设置检测点的数目。比如日志文件大小为1000块，而设置检查点间隔LOG_CHECKPOINT_INTERV AL 为250，则文件写达到1/4、2/4、3/4及4/4时产生检测点(250块、500块、750块和1000块处)。可用下面命令来查日志文件大小：

SQL> select group#,bytes from v$log;

show parameter log_checkpoint_interval

（3）添加日志组

建立一个新组4，组内有两个成员

ALTER DATABASE ADD LOGFILE

GROUP 4 ('H:/oracle/oradata/orcl/redo0401.log',

'H:/oracle/oradata/orcl/redo0402.log') size 10m;

===当添加一个日志组时，可以不给出组号，有系统自动分配一个组号===

ALTER DATABASE ADD LOGFILE

('H:/oracle/oradata/orcl/redo0501.log',

'H:/oracle/oradata/orcl/redo0502.log') size 10m;

此时添加的组号为5

（4）为日志组增加成员

ALTER DATABASE

ADD LOGFILE MEMBER 'log22.log' TO GROUP 2 ;

===添加成员时，可以不给定成员的大小，因为oracle要求组内的所有成员的大小一律相等。

（5）如果将一个日志成员从一个硬盘移到另一个硬盘，就需要重新命名日志成员名字。需要进行下面步骤：

1．关闭数据库，并进行完全备份；

2．使用操作系统命令拷贝原来的日志文件到新的地方；

3．用startup mount 启动数据库；

4．用ALTER DATABASE RENAME FILE '' TO ''; 5．用ALTER DA TABASE OPEN 打开数据库；

6．备份控制文件。

为文件更名

ALTER DATABASE

RENAME FILE 'filename1', 'filename2'

TO 'filename3', 'filename4' ;

（6）日志删除

a．删除日志组成员：

在下面情况下可能需要删除重做日志文件，如日志文件个数太多（超出需要）；日志文件的大小不一致等。这样的情况可以删除日志组成员。

b．当日志组损坏时，就删除日志组，但必须满足：

a) 删除一个日志组后，系统中至少还有两个其它的日志组；

b) 被删除的日志组必须是不需要存档；

c) 不是正在使用的日志组。

删除日志文件的语法：

ALTER DATABASE database_name

DROP LOGFILE

GROUP group_number | file_name | (file_name,file_name(,...) )

删除日志成员的语法：

ALTER DATABASE database_name

DROP LOGFILE MEMBER file_name;

c.联机重做日志的紧急替换：

当一个重做日志组偶而被损坏使数据库不能继续使用时，不能直接删除它们，而是要用一个

干净的文件或一组成员去替代这个损坏的日志组。联机重做日志的紧急替换命令语法如下：ALTER DATABASE database_name

CLEAR[UNARCHIVED] LOGFILE group_identifier

[UNRECOVERABLE DA TAFILE]

如果该文件正在等待存档(归档模式)，就需要UNARCHIVED。

如果需要脱机恢复一个数据文件，就用UNRECOVERABLE DATAFILE。

d．了解重做日志的当前状态：

V$LOGFILE

V$LOG

V$THREAD

V$LOG_HISTORY

e.例子：

删除一个组3：

ALTER DATABASE DROP LOGFILE GROUP 3;

删除一个成员：

ALTER DATABASE DROP? LOGFILE MEMBER

'/orant/oradata'mydb02'redo02.log' ;

4．了解重做日志的当前状态的视图主要有：

V$LOGFILE

V$LOG

V$THREAD

V$LOG_HISTORY

5.例子：

察看是否归档

ARCHIVE? LOG? LIST

日志归档

alter database noarchivelog;

3、控制文件的管理

（1）关于控制文件

控制文件存放有数据库的结构信息，包括数据文件、日志文件。控制文件是一个二进制文件，它是在数据库建立时自动被建立。控制文件可以在当你改变文件名或移动文件时而被更新。在任何时候，你都不能编辑控制文件。

控制文件的内容包括：

1数据库名字（控制文件只能属于一个数据库）。

2数据库建立时的邮戳。

3数据文件-名字，位置及联机/脱机。

4重做日志文件-名字及位置。

5表空间名字。

6当前日志序列号。

7最近检查点信息。

8恢复管理器信息（RMAN）。

（2）控制文件信息

在spfile.ora的配置文件中有：

control_files = ("/home/oracle/app/oracle/oradata/s450/control01.ctl",

"/home/oracle/app/oracle/oradata/s450/control02.ctl",

"/home/oracle/app/oracle/oradata/s450/control03.ctl")

（3）查询控制文件的信息

show parameters;

select * from v$controlfile;

desc V$CONTROLFILE_RECORD_SECTION

v$datafile

db_data_files

v$logfile

v$log

v$controlfile

show parameter control_files

其中，

RECORD_SIZE NUMBER 记录字节大小

RECORDS_TOTAL NUMBER 段的记录数

RECORDS_USED NUMBER 段中已使用的记录数

FIRST_INDEX NUMBER 第一个记录索引位置

LAST_INDEX NUMBER 最后一个记录索引位置

LAST_RECID NUMBER 最后一个记录ID号

select * from V$CONTROLFILE_RECORD_SECTION;

7.6.4 控制文件信息的更改

需要在spfile.ora中做出相应的更改。

建立控制文件的步骤

1. 建立控制文件准备。

必须有数据文件(查DBA_DA TA_FILES数据字典)、日志文件（查V$LOGFILE数据字典）的详细列表。下面是创建控制文件的命令：

CREATE CONTROLFILE SET DA TABASE "ORACLE"

NORESETLOGS NOARACHIVELOG

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDA TAFILES 32

MAXINSTANCES 1

MAXLOGHISTORY 1630

LOGFILE

GROUP 1 'C:\ORACLE\DA TABASE\LOG1ORCL.LOG' SIZE 200K,

GROUP 2 'C:\ORACLE\DA TABASE\LOG2ORCL.LOG' SIZE 200K,

DA TAFILE

'C:\ORACLE\DATABASE\SYS1ORCL.ORA',

'C:\ORACLE\DATABASE\USER1ORCL.ORA',

'C:\ORACLE\DATABASE\RBS1ORCL.ORA',

'C:\ORACLE\DATABASE\TMP1ORCL.ORA',

'C:\ORACLE\DATABASE\APPDATA1.ORA',

'C:\ORACLE\DATABASE\APPINDX1.ORA',

;

在例子中，参数选件与CREATE DATABASE类似。NORESETLOGS 指定联机的日志文件不要重新设置。

2．关闭数据库。

3．用NOMOUNT选件启动数据库，记住，安装数据库，Oracle需要打开控制文件。4．用类似上面建立新的控制文件。并在INITsid.ORA参数文件中指定。

5．使用ALTER DATABASE OPEN命令打开数据库。

6．关闭数据库并备份数据库。

提示：如果你的数据库正常，则可以用ALTER DA TABASE BACKUP CONTROLFILE TO TRACE 命令来产生一个CREATE CONTROLFILE 的命令。该命令产生后被写到跟踪文件中。请参考INITsid.OAR的USER_DUMP_DEST所指的目录找到跟踪文件，它就在跟踪文件中。

建立好控制文件后，就要确定如何来补救被丢失的数据文件，可以从V$DATAFILE视图来查到丢失的数据文件，这些数据文件的名字为MISSINGnnnn。如果你建立的控制文件带有RESETLOGS选件，则丢失的数据文件就不能加回数据库中。如果你建立的控制文件带有NORESETLOGS选件，则丢失的数据文件可以由数据库的介质恢复被加回数据库中。

当数据库启动后可以用下面命令进行备份：

ALTER DATABASE BACKUP CONTROL FILE TO '' REUSE;

Oracle公司建议无论你是否改变数据库结构、或加数据文件、或重新命名文件或删除重做日志文件，都要进行备份。

可以从视图V$CONTROLFILE中查到控制文件的信息，status字段表示控制文件的状态，一般总是为空。如：

SQL> select * from v$controlfile ;

另外V$CONTROLFILE_RECORD_SECTION 视图存储控制文件所记录的信息。它的结构如下：

SQL> desc V$CONTROLFILE_RECORD_SECTION