【Oracle教学】10g Administration Workshop II

1. 简介 ★
回顾总结DBA I中的学习内容
如何给出一个数据库规划方案

2. 配置RMAN ★★★
配置使用RMAN资料库,修改RMAN的参数
如何优化RMAN的性能

RMAN> show all;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

SQL> create tablespace rcat_ts datafile '/u01/app/oracle/oradata/djedu/rcat01.dbf' size 10m;
SQL> CREATE USER rman IDENTIFIED BY cat DEFAULT TABLESPACE rcat_ts QUOTA UNLIMITED ON rcat_ts; 
SQL> GRANT RECOVERY_CATALOG_OWNER TO rman; 

$ rman catalog rman/cat@djedu
RMAN> CREATE CATALOG TABLESPACE rcat_ts; 

$ rman target / catalog rman/cat@djedu
RMAN> register database; 

run 
{ 
   ALLOCATE CHANNEL c1 DEVICE TYPE disk; 
   BACKUP DATAFILE 3; 
   RELEASE CHANNEL c1;
} 

SQL> drop tablespace rcat_ts including contents and datafiles;

3. 使用RMAN ★★★★
RMAN的各种备份种类和使用方式,如何管理RMAN的备份信息
设计一个数据库RMAN备份策略的相关脚本

run {
allocate channel 'dev1' type disk;
backup full format '/home/oracle/backup/%U' database include current controlfile;
sql 'change archivelog all crosscheck';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/backup/arc_%U' delete input;
release channel dev1;
}
RMAN> BACKUP INCREMENTAL LEVEL 0 format '/home/oracle/backup/inc0_%U' DATABASE; 
RMAN> BACKUP INCREMENTAL LEVEL 1 format '/home/oracle/backup/inc1_%U' DATABASE; 
RMAN> BACKUP INCREMENTAL LEVEL 1 format '/home/oracle/backup/cinc1_%U' CUMULATIVE DATABASE; 

RMAN> backup as copy incremental level 0 format '/home/oracle/backup/iub0_%U' datafile 1 tag 'basecopy';   
RMAN> backup incremental level 1 for recover of copy with tag 'basecopy' format '/home/oracle/backup/iub1_%U' datafile 1;
RMAN> recover copy of datafile 1 with tag 'basecopy';  

4. 恢复非关键性的数据丢失 ★★★★★
恢复临时表空间,重做日志,丢失的索引,数据库密码文件
9i中由于恢复temp表空间导致的故障

5. 数据库恢复 ★★★★★ (ref: 10-简单的不完全恢复)
完全恢复和不完全恢复的区别,基于时间,scn,取消的不完全恢复
搭积木和数据库恢复概念的辩证

RUN {
allocate channel 'dev1' type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
SET UNTIL TIME = '2011-12-08 22:32:31';
RESTORE DATABASE;
RECOVER DATABASE;
sql 'ALTER DATABASE OPEN RESETLOGS';
release channel dev1;
}

RUN {
allocate channel 'dev1' type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME = '2011-12-08 22:32:31';
sql 'ALTER DATABASE OPEN RESETLOGS';
release channel dev1;
}

6. 闪回 ★★ (ref: 20-会简单的闪回查询,全库闪回)
配置和使用Flashback
挽救被DROP的表数据

SQL> alter system set db_recovery_file_dest_size=2G;
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flashback';
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive';
SQL> alter database archivelog;
SQL> alter database force logging;
SQL> alter database flashback on;
SQL> select flashback_on,force_logging from v$database;
SQL> select current_scn from v$database;
SQL> create table hr.fb_test as select employee_id,first_name,last_name from hr.employees;
SQL> select count(*) from hr.fb_test;
SQL> delete from hr.fb_test where employee_id<105;
SQL> commit;
SQL> select count(*) from hr.fb_test;
SQL> conn / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 506002;
SQL> alter database open resetlogs;
SQL> select count(*) from hr.fb_test;

7. 处理数据库损坏 ★★
物理和逻辑损坏及其检测修复方法的使用
使用RMAN轻巧修复巨型数据库

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
select dbms_rowid.ROWID_OBJECT(rowid) data_object_id#,
       dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#,
       dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,
       dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,
       rowid
  from employees
 where employee_id = 100;

select file#||' '||name||' '||bytes from v$datafile;

BBED> set dba 5,88
BBED> find /c WARD
BBED> modify /c yang dba 4,396 offset 8072
BBED> dump /v dba 4,396 offset 8072
BBED> sum dba 4,396
BBED> sum dba 4,396 apply

$ dbv file=/u01/app/oracle/oradata/djedu/example01.dbf blocksize=8192

SET SERVEROUTPUT ON
DECLARE
BEGIN
 DBMS_REPAIR.ADMIN_TABLES (
   table_name => 'REPAIR_TABLE',
   table_type => DBMS_REPAIR.REPAIR_TABLE,
   action => DBMS_REPAIR.CREATE_ACTION,
   tablespace => 'USERS');
END;
/
DECLARE
BEGIN
 DBMS_REPAIR.ADMIN_TABLES (
   table_name => 'ORPHAN_KEY_TABLE',
   table_type => DBMS_REPAIR.ORPHAN_TABLE,
   action => DBMS_REPAIR.CREATE_ACTION,
   tablespace => 'USERS');
END;
/
DECLARE num_corrupt INT;
BEGIN
 num_corrupt := 0;
 DBMS_REPAIR.CHECK_OBJECT (
   schema_name => 'HR',
   object_name => 'EMPLOYEES',
   repair_table_name => 'REPAIR_TABLE',
   corrupt_count => num_corrupt);
END;
/
DECLARE num_fix INT;
BEGIN
 num_fix := 0;
 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     schema_name => 'HR',
     object_name => 'EMPLOYEES',
     object_type => DBMS_REPAIR.TABLE_OBJECT,
     repair_table_name => 'REPAIR_TABLE',
     fix_count => num_fix);
END;
/
DECLARE
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('HR', 'EMPLOYEES');
END;
/

RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 3; 

9. 自动性能管理 ★★
性能规划,内存和SQL优化
如何对一个PV千万的网站后台数据库进行性能规划

10. 管理不同类型的表 ★★
根据业务需求选择正确的表类型,如何估算表容量的增长趋势
某B2C网站估算数据库存储的增长速度
范围分区表:

CREATE TABLE sales 
(invoice_no NUMBER, 
sale_date DATE NOT NULL ) 
PARTITION BY RANGE (sale_date) 
(PARTITION sales1999_q1 
VALUES LESS THAN (TO_DATE('1999-04-01','YYYY-MM-DD') 
TABLESPACE ts_sale1999q1, 
PARTITION sales1999_q2 
VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD') 
TABLESPACE ts_sale1999q2, 
PARTITION sales1999_q3 
VALUES LESS THAN (TO_DATE('1999-10-01','YYYY-MM-DD') 
TABLESPACE ts_sale1999q3, 
PARTITION sales1999_q4 
VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD') 
TABLESPACE ts_sale1999q4
values less than (maxvalue)
TABLESPACE ts_salemax); 

列表分区表:

create table emp ( 
empno number(4), 
ename varchar2(30), 
location varchar2(30)) 
partition by list (location) 
(partition p1 values ('北京'), 
partition p2 values ('上海','天津','重庆'), 
partition p3 values ('广东','福建'),
partition p0 values (default)
);

哈希分区:

create table emp ( 
empno number(4), 
ename varchar2(30), 
sal number) 
partition by hash (empno) 
partitions 8 
store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

范围哈希组合分区:

create table emp ( 
empno number(4), 
ename varchar2(30), 
hiredate date) 
partition by range (hiredate) 
subpartition by hash (empno) 
subpartitions 2 
(partition e1 values less than (to_date('20020501','YYYYMMDD')), 
partition e2 values less than (to_date('20021001','YYYYMMDD')), 
partition e3 values less than (maxvalue)); 

范围列表组合分区:

CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2)) 
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE 
(SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
create table t1 (id1 number,id2 number) 
partition by range (id1) subpartition by list (id2)
(partition p11 values less than (11)
(subpartition subp1 values (1))
);

索引分区:

CREATE INDEX month_ix ON sales(sales_month)
GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

11. 管理数据库存储 ★★
存储相关的参数,如何回收空间
Shrink space的本质

SQL> create table hr.t1 as select * from dba_objects;
SQL> insert into hr.t1 select * from dba_objects;
SQL> commit;
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='T1';

SQL> delete from hr.t1 where rownum<100000;
SQL> delete from hr.t1 where rownum<100000;
SQL> commit;
SQL> select * from v$sesstat where sid =(select distinct sid from v$mystat) and statistic#=(select statistic# from v$statname where name='redo size');
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='T1';

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select sysdate from dual;
SQL> alter table hr.t1 enable row movement;
SQL> alter table hr.t1 shrink space;
SQL> select sysdate from dual;
SQL> alter system archive log current;

SQL> col name for a55
SQL> select name, first_time, next_time from v$archived_log;

SQL> exec sys.dbms_logmnr.add_logfile(LogFileName=>'/u01/app/oracle/oradata/djedu/archive/1_6_762903957.dbf');
SQL> exec sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.dict_from_online_catalog);
SQL> select operation,sql_redo from v$logmnr_contents where seg_name='T1';
SQL> exec sys.dbms_logmnr.end_logmnr

12. ASM-自动存储 ★★★★★ (ref: 17-懂得ASM基本概念,与RAID区别开)
ASM的各功能单元概念,与传统的文件系统的异同
自动存储、裸设备、文件系统的管理与性能评比

# /u01/app/oracle/product/10.2.0/db_1/bin/localconfig add
# fdisk /dev/sdb
# cd /dev
# MAKEDEV raw
# raw /dev/raw/raw1 /dev/sdb1
# raw /dev/raw/raw2 /dev/sdb2
# raw /dev/raw/raw3 /dev/sdb3
# raw /dev/raw/raw4 /dev/sdb4
# chown oracle:oinstall /dev/raw/raw*
cat >> /etc/udev/rules.d/60-raw.rules << EOF
ACTION=="add", KERNEL=="/dev/sdb1",RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="17",RUN+="/bin/raw /dev/raw/raw1 %M %m"
ACTION=="add", KERNEL=="/dev/sdb2",RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="18",RUN+="/bin/raw /dev/raw/raw2 %M %m"
ACTION=="add", KERNEL=="/dev/sdb3",RUN+="/bin/raw /dev/raw/raw3 %N"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="19",RUN+="/bin/raw /dev/raw/raw3 %M %m"
ACTION=="add", KERNEL=="/dev/sdb4",RUN+="/bin/raw /dev/raw/raw4 %N"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="20",RUN+="/bin/raw /dev/raw/raw4 %M %m"
KERNEL=="raw[1-4]", OWNER="oracle", GROUP="oinstall", MODE="640"
EOF
alter system set control_files ='+test/djedu/control01.ctl','+test/djedu/control02.ctl','+test/djedu/control03.ctl' scope=spfile;

restore controlfile to '+test/djedu/control01.ctl' from '/u01/app/oracle/oradata/djedu/control01.ctl';
restore controlfile to '+test/djedu/control02.ctl' from '/u01/app/oracle/oradata/djedu/control02.ctl';
restore controlfile to '+test/djedu/control03.ctl' from '/u01/app/oracle/oradata/djedu/control03.ctl';

backup as copy database format '+test';
switch database to copy;
alter database open;

alter database add logfile group 4 '+test/redo04.log' size 50m;
alter database add logfile group 5 '+test/redo05.log' size 50m;
alter database add logfile group 6 '+test/redo06.log' size 50m;

*13. 管理资源 ★
Resource Manager的使用和管理
使用资源管理器来保护数据库

*14. 调度任务-SCHEDULER ★
调度任务和JOB的异同,如何使用调度任务
自动归档游戏网站的日志数据

*15. 数据库安全性 ★
使用透明数据加密和虚拟私有数据库加固数据库
银行是如何加密用户信息的

16. 全球化支持 ★★
理解字符集的使用和转换
中文乱码是如何产生的

*17. 课堂练习 ★
综合运用知识
模拟OCM考试的一个综合性数据库部署,排障,优化,备份恢复案例

18. 附录
1). 显示当前所有的配置

RMAN> show all;

2). 配置RMAN参数

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

3). 列出数据库中的备份信息:

RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST COPY OF TABLESPACE SYSTEM;
RMAN> LIST BACKUP OF DATAFILE 1;

4). 检查备份:

RMAN> report obsolete;
RMAN> change backupset id delete;
RMAN> change archivelog all crosscheck;
RMAN> crosscheck backup;
RMAN> delete expired backup;

5). 删除备份

RMAN> delete backup;
RMAN> delete copy;
RMAN> delete obsolete;
RMAN> delete expired backup;
RMAN> delete expired copy;
RMAN> delete backupset 19;
RMAN> delete backuppiece 'd:\backup\DEMO_19.bak';
RMAN> delete datafilecopy 'd:\backup\DEMO_19.bak';

6). 备份后删除输入对象

RMAN> delete archivelog all delete input;
RMAN> delete backupset 22 format = ''/home/oracle/backup/%u.bak'' delete input;

7). 数据库完全备份

RMAN> backup database;
RMAN> BACKUP DATABASE FORMAT '/home/oracle/backup/%U';
RMAN> BACKUP DATABASE FORMAT '/home/oracle/backup/%U' INCLUDE CURRENT CONTROLFILE;
RMAN> backup tablespace users;
RMAN> BACKUP DATAFILE 1;
RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/home/oracle/backup/djedu_dbfno4.dbf';

8). 备份控制文件

RMAN> BACKUP CURRENT CONTROLFILE;
SQL> alter database backup control file to trace;

9). 备份归档日志

RMAN> BACKUP ARCHIVELOG ALL;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT; 

10). 恢复控制文件

RMAN> startup nomount;
RMAN> set dbid=1383524435;
RMAN> restore controlfile to '/u01/app/oracle/oradata/djedu/control01.ctl' from '/home/oracle/backup/04msd4c3_1_1';
RMAN> restore controlfile to '/u01/app/oracle/oradata/djedu/control01.ctl' from autobackup;

11). 基于OS操作备份恢复

SQL> alter tablespace system begin backup;
SQL> !cp /u01/app/oracle/oradata/djedu/system01.dbf /home/oracle/backup/system01.dbf.bak;
SQL> alter tablespace system end backup;
SQL> !rm /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> select * from v$recover_file;
SQL> !cp /home/oracle/backup/system01.dbf.bak /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> recover database;

SQL> alter tablespace users begin backup;
SQL> !cp /u01/app/oracle/oradata/djedu/users01.dbf /home/oracle/backup/users01.dbf.bak;
SQL> alter tablespace users end backup;
SQL> !rm /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> select * from v$recover_file;
SQL> alter database datafile 4 offline drop;
SQL> alter database open;
SQL> !cp /home/oracle/backup/users01.dbf.bak /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> recover datafile 4;

12). 基于RMAN的基本备份恢复

SQL> !rm /u01/app/oracle/oradata/djedu/system01.dbf;
SQL> select * from v$recover_file;
RMAN> restore database;
RMAN> recover database;
RMAN> sql 'alter database open';

SQL> !rm /u01/app/oracle/oradata/djedu/users01.dbf;
SQL> select * from v$recover_file;
SQL> alter database datafile 4 offline drop;
SQL> alter database open;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> sql 'alter database datafile 3 online';

13). RMAN catalog的使用

SQL> create tablespace rcat_ts datafile '/u01/app/oracle/oradata/djedu/rcat01.dbf' size 10m;
SQL> CREATE USER rman IDENTIFIED BY cat DEFAULT TABLESPACE rcat_ts QUOTA UNLIMITED ON rcat_ts; 
SQL> GRANT RECOVERY_CATALOG_OWNER TO rman; 

$ rman catalog rman/cat@djedu
RMAN> CREATE CATALOG TABLESPACE rcat_ts; 

$ rman target / catalog rman/cat@djedu
RMAN> register database; 

run 
{ 
   ALLOCATE CHANNEL c1 DEVICE TYPE disk; 
   BACKUP DATAFILE 3; 
   RELEASE CHANNEL c1;
} 

SQL> drop tablespace rcat_ts including contents and datafiles;

14). 全备和增量备份:

run {
allocate channel 'dev1' type disk;
backup full format '/home/oracle/backup/%U' database include current controlfile;
sql 'change archivelog all crosscheck';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/backup/arc_%U' delete input;
release channel dev1;
}
RMAN> BACKUP INCREMENTAL LEVEL 0 format '/home/oracle/backup/inc0_%U' DATABASE; 
RMAN> BACKUP INCREMENTAL LEVEL 1 format '/home/oracle/backup/inc1_%U' DATABASE; 
RMAN> BACKUP INCREMENTAL LEVEL 1 format '/home/oracle/backup/cinc1_%U' CUMULATIVE DATABASE; 

15). 增量更新备份

RMAN> backup as copy incremental level 0 format '/home/oracle/backup/iub0_%U' datafile 1 tag 'basecopy';   
RMAN> backup incremental level 1 for recover of copy with tag 'basecopy' format '/home/oracle/backup/iub1_%U' datafile 1;
RMAN> recover copy of datafile 1 with tag 'basecopy';  

16). RMAN不完全恢复

SQL> select * from V$BACKUP_CORRUPTION;
SQL> select * from V$COPY_CORRUPTION ;

RUN {
allocate channel 'dev1' type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
SET UNTIL TIME = '2011-12-08 22:32:31';
RESTORE DATABASE;
RECOVER DATABASE;
sql 'ALTER DATABASE OPEN RESETLOGS';
release channel dev1;
}

RUN {
allocate channel 'dev1' type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME = '2011-12-08 22:32:31';
sql 'ALTER DATABASE OPEN RESETLOGS';
release channel dev1;
}

17). 块恢复:

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

select dbms_rowid.ROWID_OBJECT(rowid) data_object_id#,
       dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#,
       dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,
       dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,
       rowid
  from employees
 where employee_id = 100;

select file#||' '||name||' '||bytes from v$datafile;

BBED> set dba 5,88
BBED> find /c WARD
BBED> modify /c yang dba 4,396 offset 8072
BBED> dump /v dba 4,396 offset 8072
BBED> sum dba 4,396
BBED> sum dba 4,396 apply

$ dbv file=/u01/app/oracle/oradata/djedu/example01.dbf blocksize=8192

SET SERVEROUTPUT ON
DECLARE
BEGIN
 DBMS_REPAIR.ADMIN_TABLES (
   table_name => 'REPAIR_TABLE',
   table_type => DBMS_REPAIR.REPAIR_TABLE,
   action => DBMS_REPAIR.CREATE_ACTION,
   tablespace => 'USERS');
END;
/
DECLARE
BEGIN
 DBMS_REPAIR.ADMIN_TABLES (
   table_name => 'ORPHAN_KEY_TABLE',
   table_type => DBMS_REPAIR.ORPHAN_TABLE,
   action => DBMS_REPAIR.CREATE_ACTION,
   tablespace => 'USERS');
END;
/
DECLARE num_corrupt INT;
BEGIN
 num_corrupt := 0;
 DBMS_REPAIR.CHECK_OBJECT (
   schema_name => 'HR',
   object_name => 'EMPLOYEES',
   repair_table_name => 'REPAIR_TABLE',
   corrupt_count => num_corrupt);
END;
/
DECLARE num_fix INT;
BEGIN
 num_fix := 0;
 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     schema_name => 'HR',
     object_name => 'EMPLOYEES',
     object_type => DBMS_REPAIR.TABLE_OBJECT,
     repair_table_name => 'REPAIR_TABLE',
     fix_count => num_fix);
END;
/
DECLARE
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('HR', 'EMPLOYEES');
END;
/

RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 3; 
Trackback

no comment untill now

Add your comment now

切换到手机版