oracle 笔记3（Fundamentals II）

来源：互联网发布：天猫双十一销售数据编辑：程序博客网时间：2024/06/06 06:44

十、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.

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]$