oracle过程备份,Oracle备份与恢复系列 （一）实验环境介绍

实验环境简介：

创建测试用户snow，该用户拥有一个表，一个存储过程，一项任务起作用很简单，就是模拟一个用户每分钟像数据库插入一条时间戳，产生数据库行为。在备份与恢复的过程中，该时间戳可以检测恢复操作是否顺利完成。

查看数据库名称和归档模式

SYS@PRACTICE >col name for a10

SYS@PRACTICE >col log_mode for a10

SYS@PRACTICE >select name, log_mode from v$database;

NAME       LOG_MODE

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

PRACTICE   ARCHIVELOG

查看数据库版本

SYS@PRACTICE >select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

数据文件

SYS@PRACTICE >col name for a40

SYS@PRACTICE >select name,bytes/1024/1024 MB from v$datafile;

NAME                                             MB

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

/oradata/PRACTICE/system01.dbf                  710

/oradata/PRACTICE/sysaux01.dbf                  570

/oradata/PRACTICE/undotbs01.dbf                 110

/oradata/PRACTICE/users01.dbf                     5

/oradata/PRACTICE/example01.dbf             313.125

/oradata/PRACTICE/tools01.dbf                    20

/oradata/PRACTICE/indx.dbf                       20

下面的语句也可以显示同样的数据

select file_name, bytes/1024/1024 MB from dba_data_files;

查看联机重做日志的位置和大小

SYS@PRACTICE >select member,bytes/1024/1024 MB from v$logfile lf, v$log l where lf.group# = l.group#;

MEMBER                                                               MB

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

/oradata/PRACTICE/redo03.log                                         50

/oradata/PRACTICE/redo02.log                                         50

/oradata/PRACTICE/redo01.log                                         50

查看控制文件的位置

SYS@PRACTICE >col name for a100

SYS@PRACTICE >select name from v$controlfile;

NAME

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

/oradata/PRACTICE/control01.ctl

/u01/app/oracle/fast_recovery_area/PRACTICE/control02.ctl

查看临时文件的位置及大小

SYS@PRACTICE >select name,bytes/1024/1024 MB from v$tempfile;

NAME                                             MB

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

/oradata/PRACTICE/temp01.dbf                     29

创建测试用户SNOW

GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO snow IDENTIFIED BY snow;

ALTER USER snow DEFAULT   TABLESPACE TOOLS;

ALTER USER snow TEMPORARY TABLESPACE TEMP;

创建时间戳表

CONNECT snow/snow

DROP TABLE date_log;

CREATE TABLE date_log (

create_time DATE CONSTRAINT create_date_pk PRIMARY KEY USING INDEX TABLESPACE INDX,

name varchar2(10)

);

col segment_name for a15

col TABLESPACE_NAME for a15

select segment_name,tablespace_name from user_segments;

SEGMENT_NAME    TABLESPACE_NAME

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

DATE_LOG        TOOLS

CREATE_DATE_PK  INDX

基于时间戳表创建一个存储过程

conn snow/snow

CREATE OR REPLACE PROCEDURE create_date_log_row

IS

BEGIN

INSERT INTO date_log VALUES (SYSDATE,'--');

END;

/

创建一个job来执行存储过程，来模拟数据库在“运行中”

每分钟执行一次

conn snow/snow

VARIABLE jobno number;

BEGIN

-- Run the job every 1 minutes

DBMS_JOB.SUBMIT(:jobno, 'snow.create_date_log_row;', SYSDATE, '(SYSDATE + 1/(24*60))');

commit;

END;

/

SNOW@PRACTICE >print jobno

JOBNO

----------

24

SNOW@PRACTICE >col what for a30

SNOW@PRACTICE >SELECT job, what FROM USER_JOBS;

JOB WHAT

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

24 snow.create_date_log_row;

未完待续.....