手工Hot-backup 热备实验

Hot backup 是指数据库在archivelog 模式并处理open状态下的备份。实验一下几个方面。
a、备份表空间
b、在热备期间，可否向表空间写数据
c、在热备期间，模拟断电操作
1、备份表空间
第一步：通过以下sql查询当前数据库有哪些表空间
SYS@ orcl >col tablespace format a20
SYS@ orcl >col datafile format a50
SYS@ orcl >SELECT t.name tablespace, f.name datafile
  2   FROM v$tablespace t, v$datafile f
  3   WHERE t.ts# = f.ts#
  4   ORDER BY t.name;

TABLESPACE           DATAFILE
-------------------- --------------------------------------------------
AAAA                 /tmp/aaaa.db
EXAMPLE              /u01/app/oradata/orcl/example01.dbf
PERFSTAT             /u01/app/oradata/orcl/perfstat01.dbf
SYSAUX               /u01/app/oradata/orcl/sysaux01.dbf
SYSTEM               /u01/app/oradata/orcl/system01.dbf
TESTING_LMT_MSSM     /tmp/testing_lmt_mssm.dbf
UNDOTBS1             /u01/app/oradata/orcl/undotbs01.dbf
USERS                /u01/app/oradata/orcl/users01.dbf

8 rows selected.

第二步：执行检查点，确保脏数据真正的写入数据文件
SYS@ orcl >alter system checkpoint;
System altered.
第三步：执行备份操作
SYS@ orcl >alter tablespace users begin backup;

Tablespace altered.

此条语句之后，可以通过以下sql查看相关情况
select A.ts#,name ,b.*from  v$tablespace a, v$backup b
  2  where a.ts#=b.file#;

       TS# NAME                                FILE# STATUS                CHANGE# TIME
---------- ------------------------------ ---------- ------------------ ---------- --------------
         1 SYSAUX                                  1 NOT ACTIVE                  0
         2 UNDOTBS1                                2 NOT ACTIVE                  0
         4 USERS                                   4 ACTIVE                2057636 03-7月 -12
         3 TEMP                                    3 NOT ACTIVE                  0
         6 EXAMPLE                                 6 NOT ACTIVE                  0
         7 TESTING_LMT_MSSM                        7 NOT ACTIVE                  0
         8 PERFSTAT                                8 NOT ACTIVE                  0

7 rows selected.
执行Os，cp操作，把表空间文件拷贝到相应地方。

SYS@ orcl >!cp  /u01/app/oradata/orcl/users01.dbf /tmp/backup

SYS@ orcl >alter tablespace users end backup;

Tablespace altered.

最后结束表空间backup模式。
表空间备份这样既可，表的表空间同理备份。

2、在表空间处于backup模式的时候，别的用户可以访问该空间

SYS@ orcl >create user test identified  by test  default tablespace example;

User created.

SYS@ orcl >grant connect,resource to test;

Grant succeeded.

SYS@ orcl >conn test/test
Connected.
TEST@ orcl >create table t(name varchar2(50));

Table created.

TEST@ orcl >insert into fruit values('orange');

1 row created.

TEST@ orcl >commit;

Commit complete.

TEST@ orcl >conn /as sysdba
Connected.
SYS@ orcl >alter system checkpoint;

System altered.

SYS@ orcl >select dbms_rowid.rowid_block_number(rowid) blk,name from test.fruit;

       BLK NAME
---------- --------------------------------------------------
       487 orange

SYS@ orcl >show parameter db_block;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
SYS@ orcl >!dd if=/u01/app/oradata/orcl/example01.dbf ibs=8192 skip=486 count=2 |strings
        [       k
6;--  DEVICE_DOES_NOT_EXIST_ERR: device does not exist error
PROCEDURE get_snmp_device_data
  (device_name_in IN VARCHAR2,
:$   device_data_out OUT CURSOR_TYPE,
;+   device_rules_list_out OUT CURSOR_TYPE);
>!-- Name: delete_snmp_device_data
@0-- PURPOSE: delete the snmp device detail data.
B9-- NOTE: This API should be called only by a super user.
-- IN parameters:
ED--  device_name: VARCHAR2 The name of the snmp device to be deleted
-- OUT parameters:
--   None
-- ERROR CODES:
K?--  INSUFFICIENT_PRIVILEGES_ERR: insufficient privileges error
L;--  DEVICE_DOES_NOT_EXIST_ERR: device does not exist error
N"PROCEDURE delete_snmp_device_data
O   (device_name_in IN VARCHAR2);
-- Name: get_java_device_data
TJ-- PURPOSE: get the java device detail data. a list of all the rules that
U2--          use this java device is also returned
W       -- NOTE:
X4--  This API should be called only by a super user.
-- IN parameters:
[6--  device_name: VARCHAR2 The name of the java device
-- OUT parameters:
^"--   device_data_out: CURSOR_TYPE
_6--    A cursor which returns a single row containing:
`9--     device_name: VARCHAR2 The name of the java device
aG--     device_description: VARCHAR2 The description of the java device
b=--     type: NUMBER The type associated with the java device
c?--     profile_name: VARCHAR2 The user who created this device
d6--     program: VARCHAR2 The java class path and name
(--   device_parameters_out: CURSOR_TYPE
;--    A cursor which returns 0/1/multiple rows containing:
6--     position: NUMBER The position of the parameter
(--   device_rules_list_out<
U7^M
U7^M
U7^M
MGMT_JOB_TARGET_LIST_ARRAY
2+0 records in
32+0 records out
        y       a       I       1
orange

SYS@ orcl >alter tablespace example begin backup;

Tablespace altered.

SYS@ orcl >conn test/test
Connected.
TEST@ orcl >select * from fruit;

NAME
--------------------------------------------------
orange

TEST@ orcl >update fruit set name = 'apple';

1 row updated.

TEST@ orcl >commit;

Commit complete.

TEST@ orcl >conn /as sysdba
Connected.
SYS@ orcl >alter system checkpoint;

System altered.

SYS@ orcl >!dd if=/u01/app/oradata/orcl/example01.dbf ibs=8192 skip=486 count=2 |strings
        [       k
6;--  DEVICE_DOES_NOT_EXIST_ERR: device does not exist error
PROCEDURE get_snmp_device_data
  (device_name_in IN VARCHAR2,
:$   device_data_out OUT CURSOR_TYPE,
;+   device_rules_list_out OUT CURSOR_TYPE);
>!-- Name: delete_snmp_device_data
@0-- PURPOSE: delete the snmp device detail data.
B9-- NOTE: This API should be called only by a super user.
-- IN parameters:
ED--  device_name: VARCHAR2 The name of the snmp device to be deleted
-- OUT parameters:
--   None
-- ERROR CODES:
K?--  INSUFFICIENT_PRIVILEGES_ERR: insufficient privileges error
L;--  DEVICE_DOES_NOT_EXIST_ERR: device does not exist error
N"PROCEDURE delete_snmp_device_data
O   (device_name_in IN VARCHAR2);
-- Name: get_java_device_data
TJ-- PURPOSE: get the java device detail data. a list of all the rules that
U2--          use this java device is also returned
W       -- NOTE:
X4--  This API should be called only by a super user.
-- IN parameters:
[6--  device_name: VARCHAR2 The name of the java device
-- OUT parameters:
^"--   device_data_out: CURSOR_TYPE
_6--    A cursor which returns a single row containing:
`9--     device_name: VARCHAR2 The name of the java device
aG--     device_description: VARCHAR2 The description of the java device
b=--     type: NUMBER The type associated with the java device
c?--     profile_name: VARCHAR2 The user who created this device
d6--     program: VARCHAR2 The java class path and name
(--   device_parameters_out: CURSOR_TYPE
;--    A cursor which returns 0/1/multiple rows containing:
6--     position: NUMBER The position of the parameter
(--   device_rules_list_out<
U7^M
U7^M
U7^M
MGMT_JOB_TARGET_LIST_ARRAY
        y       a       I       1
2+0 records in
32+0 records out
apple,
orange
SYS@ orcl >alter tablespace example end backup;
Tablespace altered.
3、在热备过程中，crash 实验

SYS@ orcl >alter tablespace example end backup;
Tablespace altered.
SYS@ orcl >
SYS@ orcl >alter tablespace example begin backup;
Tablespace altered.
SYS@ orcl >shutdown abort
ORACLE instance shut down.
SYS@ orcl >startup
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             255855704 bytes
Database Buffers           50331648 bytes
Redo Buffers                6336512 bytes
Database mounted.
ORA-10873: file 5 needs to be either taken out of backup mode or media recovered
ORA-01110: 数据文件 5: '/u01/app/oradata/orcl/example01.dbf'
SYS@ orcl >alter database end backup; --也可以是alter tablespace example end backup ;前提是知道是那个表空间在进行备份。
Database altered.
SYS@ orcl >alter database open;
Database altered.

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15489979/viewspace-734472/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15489979/viewspace-734472/