十二
20

【Oracle教学】10g Administration Workshop I

麻袋爸爸 Oracle数据库 2011-12-20

【Oracle教学】10g Administration Workshop I

2. 安装Oracle ★★★★★
安装Oracle软件的相关操作系统，软件架构知识点
安装数据库的二三事

3. 创建数据库 ★★ (ref: 1-创建数据库时指定字符集，连接数设置)
使用DBCA创建自定义不同应用类型的数据库
eBay数据库和上证所Topview数据库的区别

0．在linux下安装oracle及Linux的基本用户、文件、文件夹管理
1) 安装Linux，选择安装全部“开发工具”组件，再安装“libxp.rpm”，参见网络视频。

2) 验证已安装的程序包：

# rpm -q make gcc compat-db
make-3.80-5
gcc-3.4.3-9.EL4
compat-db-4.1.25-9

3）添加组和用户

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle
id oracle

4）修改密码

passwd oracle

5）创建目录

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

6）root用户执行，修改内核参数

cat >> /etc/sysctl.conf <

7）root用户执行，修改系统配置：


cat >> /etc/security/limits.conf <> /etc/pam.d/login <> /etc/profile <> /etc/csh.login <

8）oracle用户执行，修改profile文件：


cat >> /home/oracle/.bash_profile <

4. 管理数据库实例	★★★★

使用DB Control管理数据库，数据库关闭、启动内部操作

数据库启动关闭的排障方法	


1. Oracle产品线简述	★

网格化应用和云计算；Oracle数据库架构

图书馆和Oracle的关系	


43-8. 监控和管理内存	★★★★

详解Oracle实例的各个组成部分

著名的ORA-4031错误	


5. 数据库存储结构	★★★★★

数据库，表空间，数据文件，段、区间、块的概念和管理

如何规划设计一个500G的数据库	


6. 用户安全性	★★

创建用户，管理权限和密码口令

重建Oracle密码文件	


7. 管理方案对象	★★★★

管理数据库的约束、索引、表、序列等等

怎么都查不出结果的数据字典	


8. 管理数据和并发处理	★★★

PL/SQL语言和对象的使用；数据库的封锁机制

死锁：蛇吃尾巴的启示	


9. 管理还原	★★★

了解UNDO的概念和工作原理

经典的ORA-01555错误	


10. 实施Oracle安全性	★★

数据库审计、精细访问控制的使用

将要上市的公司如何欺骗四大会计事务所	


SQL> alter system set audit_trail =’DB’ scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> audit session;
SQL> select * from dba_stmt_audit_opts;



11. 配置Oracle网络环境	★★★★★

数据库如何建立一个连接，tnsnames和listener的作用

2个数据库网络故障的解决案例	


12. 自主维护的Oracle	★★★

使用AWR和ADDM解决数据库性能问题，使用数据库预警功能

解读一个性能问题重重的数据库AWR报告	


13. 性能管理	★★

使用SQL指导器优化查询，如何打开自动内存共享管理

如何建立数据库性能监控和排障的体系	


grant advisor to hr;

DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext   := 'SELECT distinct a.employee_id, a.last_name, a.job_id, a.department_id FROM employees a, employees b WHERE a.employee_id=b.manager_id';
  my_task_name := dbms_sqltune.create_tuning_task(
      sql_text    => my_sqltext,
      bind_list   => null,
      user_name   => 'HR',
      scope       => 'COMPREHENSIVE',
      time_limit  => 60,
      task_name   => 'sql_tuning_test',
      description => 'Tuning Task');
END;
/

exec dbms_sqltune.execute_tuning_task('sql_tuning_test');

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test';

SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

exec dbms_sqltune.drop_tuning_task('sql_tuning_test');



14. 备份和恢复的概念	★★★★★

数据库的故障类型，备份恢复中最重要的是哪些文件？

因为归档导致的数据库挂起事故	


15. 进行数据库备份	★★★★

各种备份数据库的方法、优劣比对

简单的RMAN备份操作	


SQL> startup nomount;
SQL> alter system set log_archive_dest_1＝'LOCATION=/archive';
SQL> alter database mount;
SQL> alter database archivelog; / alter database noarchivelog;
SQL> alter database open;

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';

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

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



16. 执行数据库恢复	★★★★

在数据文件、控制文件、重做日志丢失的情况下恢复数据库

数据文件，控制文件，重做日志都没了，还能拯救数据库么？	


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';



17. 执行闪回	★★

闪回在各个场景下的使用方法，与数据库恢复的区别在那里

上海电力使用闪回找回误删除的电费单	


SQL> create table fb_test as select employee_id,first_name,last_name from employees;
SQL> select count(*) from fb_test;
SQL> delete from fb_test where employee_id<105;
SQL> commit;
SQL> select count(*) from fb_test;
SQL> select count(*) from fb_test as of timestamp sysdate-1/1440;



18. 移动数据	★★★ (ref: 12-会使用exp、expdp进行用户、表的迁移)

使用SQL Loader、data pump和外部表转移数据

面对数亿的海量数据导入，如何快点解决去陪女朋友吃饭？


19. RMAN使用

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';
Address: http://www.housong.net/oracle-10gocp-042.html
«
»
Trackback

no comment untill now

Add your comment now

[show_theme_switch_link]