oracle 笔记3（Fundamentals II）

十、User-Managed Backups

1、Making a Whole DB Backup

set feedback off heading off verify off trimspool off
set pagesize 0 linesize 200
define dir='/tmp/wb'
define ws='/tmp/ws.sql'
spool &ws
select '! cp ' || name || ' &dir' from v$datafile order by 1;
select '! cp ' || member || ' &dir' from v$logfile order by 1;
select '! cp ' || name || ' &dir' from v$controlfile order by 1;
select '! cp ' || name || ' &dir' from v$tempfile order by 1;
spool off
shutdown immediate
@&ws
startup 

2、热备份时：备份一个表空间

SQL> alter tablespace mytbs begin backup;

Tablespace altered.

SQL> !cp /home/oracle/oradata/TS/mytbs.dbf  /tmp/hb

SQL> alter tablespace mytbs end backup;

Tablespace altered.

SQL> 

3、

SQL> select dbms_rowid.rowid_block_number(rowid) blk, name from fruit;

       BLK NAME
---------- --------------------------------
     11569 orange

SQL>

SQL> show parameter db_block_size;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> !

[oracle@localhost TS]$ dd if=mytbs.dbf ibs=8192 skip=11568 count=2 |strings
2+0 records in
32+0 records out
16384 bytes (16 kB) copied, 0.000276639 seconds, 59.2 MB/s
orange

4、查看backup模式的表空间

--v$backup

select file_id,file_name,tablespace_name from dba_data_files order by 1;

select * from v$backup;

5、正在backup时突然断电导致：ORA-01113: file 6 needs media recovery

SQL> startup;
ORACLE instance started.


Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             113247824 bytes
Database Buffers          167772160 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/oradata/TS/mytbs.dbf'

SQL> select * from v$backup;


     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE            4714633 13-AUG-11
         2 NOT ACTIVE            4714633 13-AUG-11
         3 NOT ACTIVE            4714633 13-AUG-11
         4 NOT ACTIVE            4714633 13-AUG-11
         5 NOT ACTIVE            4714633 13-AUG-11
         6 ACTIVE                4737378 13-AUG-11
         7 NOT ACTIVE            4714633 13-AUG-11


7 rows selected.

SQL> select * from v$backup;


     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE            4714633 13-AUG-11
         2 NOT ACTIVE            4714633 13-AUG-11
         3 NOT ACTIVE            4714633 13-AUG-11
         4 NOT ACTIVE            4714633 13-AUG-11
         5 NOT ACTIVE            4714633 13-AUG-11
         6 ACTIVE                4737378 13-AUG-11
         7 NOT ACTIVE            4714633 13-AUG-11


7 rows selected.


SQL> alter database datafile 6 end backup;   ---或者alter database end backup;


Database altered.


SQL> alter database open;


Database altered.


SQL> 

5、热备时对控制文件的备份

Creating a binary image

--alter database backup controlfile to '/tmp/con/control2.ctl';

Creating a text trace file

--alter database backup controlfile to trace;

--alter database backup controlfile to trace as '/tmp/con/c2.sql';

6、dbverify的使用

SQL> select segment_name,tablespace_id,header_file,header_block
  2  from sys_dba_segs where segment_name='FRUIT';


SEGMENT_NAME                   TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------------------------ ------------- ----------- ------------
FRUIT                                      7           6        11550


SQL> 

[oracle@localhost TS]$ dbv userid=system/sunlin segment_id=7.6.11550


DBVERIFY: Release 10.2.0.1.0 - Production on Sat Aug 13 17:26:20 2011


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


DBVERIFY - Verification starting : SEGMENT_ID = 7.6.11550




DBVERIFY - Verification complete


Total Pages Examined         : 1280
Total Pages Processed (Data) : 16
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 21
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1242
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 4714402 (0.4714402

--检查整个块

[oracle@localhost TS]$ dbv file=mytbs.dbf feedback=100


DBVERIFY: Release 10.2.0.1.0 - Production on Sat Aug 13 17:27:21 2011


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


DBVERIFY - Verification starting : FILE = mytbs.dbf
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
..........................................................................


DBVERIFY - Verification complete


Total Pages Examined         : 64000
Total Pages Processed (Data) : 2893
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 228
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 60877
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 4714402 (0.4714402)
[oracle@localhost TS]$