Oracle 11g OCM 考试练习笔记-阿里云开发者社区

-- OCM 11G 考试

-- 考场机器环境：

Oracle Linux Release 5.4 (32-bit)

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 (32-bit)

Oracle Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0) (32-bit)

-- Data recovery Advisor

list failure;

advise failure;

repair failure;

change failure;

rman target / catalog rmanrc/rmanrc@prod4

一、Server Configuration

1.Create the database

$ mkdir -p /u01/app/oracle/oradata/PROD3

$ orapwd file=$ORACLE_HOME/dbs/orapwprod3 password=oracle entries=5

$ vi ora.ora

$ vi /home/oracle/3.ora

db_create_file_dest='/u01/app/oracle/oradata/PROD3'

db_block_size=8192

db_name=PROD3

sga_target=500m

control_files='/u01/app/oracle/oradata/PROD3/control01.ctl'

$ . oraenv

PROD1

$ export ORACLE_SID=PROD3

$ sqlplus / as sysdba

SQL> startup nomount pfile='/home/oracle/ora.ora'

SQL> create database prod3 character set al32utf8 extent management local;

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> alter user system identified by oracle;

SQL> conn system/oracle

SQL> @?/sqlplus/admin/pupbld.sql

SQL> conn / as sysdba

SQL> startup force

SQL> create spfile from memory;

SQL> alter user dbsnmp identified by oracle account unlock;

$ emca -config dbconsole db -repos recreate

$ emctl status dbcosole

https://10.10.10.10:1158/em

# vi /etc/oratab

orcl:/u01/app/oracle/product/11.2.0/db_1:N

PROD2:/u01/app/oracle/product/11.2.0/db_1:N

2.Determine and set sizing parameters for database structures

3.Create and manage temporary, permanent, and undo tablespaces

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/PROD1/temp1.dbf' size 50m autoextend on tablespace group temp_grp;

SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PROD1/temp2.dbf' size 50m autoextend on tablespace group temp_grp;

SQL> alter database default temporary tablespace temp_grp;

SQL> create tablespace indx datafile '/u01/app/oracle/oradata/PROD1/indx1.dbf' size 40m;

SQL> create tablespace tools datafile '/u01/app/oracle/oradata/PROD1/tools1.dbf' size 10m;

SQL> create tablespace oltp datafile '/u01/app/oracle/oradata/PROD1/oltp1.dbf' size 48m autoextend on next 2m extent management local uniform size 2m segment space management auto;

SQL> alter system set undo_retention=5400;

SQL> alter system set undo_management=auto scope=spfile;

SQL> alter system set processes=135 scope=spfile;

4.Stripe data files across multiple physical devices and locations

5.Configure the database environment to support optimal data access performance

6.Create and manage database configuration files

7.Create and manage bigfile tablespaces

SQL> create bigfile tablespace test datafile '/u01/app/oracle/oradata/PROD1/test01.dbf' size 400m autoextend on next 1m maxsize 4t extent management local uniform size 1m;

8.Create and Manage a tablespace that uses NFS mounted file system file

SQL> shutdown immediate

$ cd $ORACLE_HOME/rdbms/lib/

$ make -f ins_rdbms.mk dnfs_on

SQL> startup

SQL>create tablespace shared_data datafile '/u02/oradata/prod1/shared_data1.dbf' size 10m;

SQL>select * from v$dnfs_servers;

$ cd ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk dnfs_off

9.Create and manage multiple network configuration files

$ vi tnsname.ora

10.Create and configure a listener

$ vi listener.ora

11.Configure the database instance to support shared server connections

alter systemset shared_servers = 1 scope=both;

alter systemset max_shared_servers = 5 scope=both ;

alter systemset dispatchers='(PROTOCOL=TCP) (SERVICE=emrepXDB)';

alter system set dispatchers='(protocol=TCP)(disp=8)(serv=xxx)';

-- 上面的模式指定只启动某个服务的共享模式，如果要设置所有服务都使用共享模式，则设置为

alter system set dispatchers='(PROTOCOL=TCP)';

alter systemset shared_servers = 0 ; -- 关闭共享

-- TNS配置在客户端的tnsnames.ora 的tns设置中，在 CONNECT_DATA 设置中将SERVER 的值 DEDICATED 改为，即可使用共享服务器连接。

EMREP =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = gcprod2)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED/SHARED)

(SERVICE_NAME = emrep)

)

-- 查看会话是否使用了共享

SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER FROM V$SESSION WHERE USERNAME IS NOT NULL;

COL USERNAME FOR A20

COL OSUSER FOR A10

COL MACHINE FOR A20

COL TERMINAL FOR A20;

SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER FROM V$SESSION

12.Set up network tracing　　

13.Manage Oracle network processes　　

14.Configure the network environment to allow connections to multiple databases　　

15.Use configurationless connections　

16.Use OPatch to install a patch

$ ./opatch apply

-- 检查版本

-- OUI 补丁

$ crsctl query crs softwareversion [hostname] -- 单节点

$ crsctl query crs activersion -- 集群

$ srvctl stop nodeapps -n node1 -r

$ cd patchset_dir/

$ ./runInstaller

-- OPatch 补丁

$ export ORACLE_HOME=$GI_HOME

$ opatch command [options] or

$ opatch command -oh GI_HOME [options]

$ opatch command -help

$ opatch query -all | grep -i Rolling

-- grid OPatch

$ opatch lsinventory -detail -oh GI_HOME

# crsctl stop crs

# cd <patch_location>

# ./custom/scripts/prerootpatch.sh -crshome GI_HOME -crsuser grid

$ ./custom/scripts/prepatch.sh -crshome GI_HOME

$ opatch apply -local -oh GI_HOME patch_location

$ ./custom/scripts/postpatch.sh -crshome GI_HOME

# ./custom/scripts/postrootpatch.sh -crshome GI_HOME

$ opatch lsinventory -detail -oh GI_HOME

-- psu

$ $GI_HOME/OPatch/ocm/bin/emocmrsp -output /app/setup/ocm.rsp

$ $GI_HOME/OPatch/opatch auto <patch_location> -ocmrf /app/setup/ocm.rsp

chown oracle:asmadmin /u01/app/oracle/product/11.2.0/db_1/bin/oracle

chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

@?/rdbms/admin/catbundle.sql psu apply

@?/rdbms/admin/utlrp.sql

17.Use Grid Infrastructure to manage oracle databases and other resources

-- 安装 asmlib 库 oracleasm-support\oracleasm

-- 初始化服务

# oracleasm configure -i

# oracleasm init

# ls -ltr /dev/oracleasm/disks

-- 创建磁盘

# oracleasm createdisk asmdisk1 /dev/sdb1

# oracleasm listdisks

# ls -ltr /dev/oracleasm/disks

-- 安装grid

-- 这里执行asmca之前需要在目录 $GRID_HOME/crs/install/找到脚本roothas.pl

-- /u01/app/11.2.0/grid/crs/install/roothas.pl

# $GRID_HOME/crs/install/roothas.pl

-- 上面执行配置完成后执行下面命令

$ export DISPLAY=192.168.56.1:0.0

$ ./asmca -- 创建两个磁盘组，data为正常冗余、fra为外部冗余磁盘组到 oracle restart

-- 加如下服务来让grid管理：emrep,prod4,listener

-- 加监听

$ $CRS_HOME/bin/srvctl add listener -l LISTENER -s -o $ORACLE_HOME

$ ./srvctl config listener

$ ./srvctl start listener

-- 加数据库

$ ./srvctl add database -d emrep -o /u01/app/oracle/product/dbhome_1

$ ./srvctl start database -d emrep

$ ./srvctl config database

$ ./srvctl config database -v

$ ./srvctl status database -d emrep

$ ./srvctl config database -d emrep -a

18.Use Enterprise Manager Configuration Assistant(EMCA) utility　　使用企业管理配置助手（EMCA）工具

二、Enterprise Manager Grid Control

1.Install and Patch Enterprise Manager Grid Control software

-- 主要是 agent 的安装

$ emca -deconfig dbcontrol db -repos drop -- 卸载系统原有的dbcontrol

-- 配置数据库参数

ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;

ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;

alter database datafile '/u01/app/oracle/oradata/prod1/undotbs01.dbf' resize 200m;

-- 环境

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=prod1

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/Middleware

export OMS_HOME=/u01/app/gc_inst

-- 安装 agent

EM 图形化安装安装

emctl exportconfig oms -dir <backup location>

-- 如何启动 GC 考试考自动启动

/u01/app/Middleware/oms11g/bin/emctl status oms -details

/u01/app/Middleware/oms11g/opmn/bin/opmnctl status

/u01/app/Middleware/oms11g/bin/emctl secure unlock -sysman_pwd oracle123

-- oms log trc 文件位置

/u01/app/gc_inst/em/EMGC_OMS1/sysman/log/

/u01/app/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs

2.Configure the Enterprise Manager repository　

3.Create Enterprise Manager Grid Control users

-- setup->administrator->create(super administrator)

4.Use Enterprise Manager to modify a database configuration　

5.Configure Enterprise Manager to modify database availability　　

6.Create and manage jobs　

7.Create and monitor alerts　　

8.Create notifications　　

9.Implement Grid Control and Database Control　

10.Choose the appropriate tablespace type for the intended use　　

11.Create Scheduler jobs　

12.Create schedules　　

13.Assign jobs to windows　　

14.Create programs　

15.Create job classes　

16.Install the Enterprise Manager Grid Control infrastructure　

17.Deploy Enterprise Manager Grid Control agents　

18.Configure Grid Control for business requirements　

三、Managing Database Availability　

1.Mantain recovery catalogs

-- 创建恢复目录步骤

-- prod4 创建恢复目录数据库表空间及用户

create tablespace ts_rman datafile '/u01/app/oracle/oradata/orcl/ts_rman01.dbf' size 100m autoextend on;

create user rmanrc identified by rmanrc default temporary tablespace temp default tablespace ts_rman quota on ts_rman unlimited;

grant recovery_catalog_owner to rmanrc;

-- 创建恢复目录数据库

rman catalog rmanrc/rmanrc@prod4

create catalog;

-- prod1、prod2 连接到恢复目录并注册到prod4中考试从这里开始

rman target sys/oracle@prod1 catalog rmanrc/rmanrc@prod4

-- rman target sys/oracle@prod1

-- connect catalog rmanrc/rmanrc@prod4

upgrade catalog;

list db_unique_name all;

resync catalog;

-- local script

create script scp1 {

allocate channel c1 device type disk;

backup tablespace users;

release channel c1;

}

list script names;

print script scp1;

replace global script scp2 {rman command;}

replace global script scp2 from file 'file_name';

delete script scp2;

-- global script

create script scp2 {

allocate channel c1 device type disk;

backup spfile;

release channel c1;

}

run {execute script scp1;}

report schema; -- 查看需要备份的数据

backup as backupset tablespace users;

backup as copy tablespace users format '/home/oracle/backup/';

backup database plus archivelog;

-- 考试需要配置快速恢复区

alter system set db_recovery_file_dest='/u01/app/fast_recovery_area';

alter system set db_recovery_file_dest_size=10Gp;

-- 增量更新备份比较占空间

recover copy of database with tag 'dialy_inc';

backup incremental level 1 for recover of copy with tag 'daily_inc' database;

-- 0 level backup

backup incremental level 0 database;

backup incremental level 1 database; -- 差异增量

backup incremental level 1 cumulative database; -- 累积增量 level 0 开始

crosscheck backup;

list expired backup;

-- 压缩备份

configure compression algorithm 'high/medium/low/basic'

backup as compressed backupset database plus archivelog;

-- 多段备份比较大的数据文件分段，比如大文件表空间

validate datafile <option> section size <integer> [K M G];

backup database 5 section size = 500M tag 'section25mb';

vi rman.sh -- 考试可以按照如下方式备份

rman target / <<EOF

run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup tablespace sysaux section size 50m tag 'sysauxsection50m';

}

EOF

-- 双工备份同时备份两份

backup as backupset device type sbt copies 2 incremental level 0 database;

-- 备份备份集

backup device type disk as backupset database plus archivelog;

backup device type sbt backupset all;

-- 配置保留备份

backup tablesapce users format '/home/oracle/db_%U.bak' keep until time 'sysdate+365' -- 备份保留一年

-- 创建归档备份考试要求创建一个0级备份永久保留考试执行下面语句即可，但是备份必须连接到恢复目录数据库

backup incremental level 0 database format '/home/oracle/db_%U.bak' keep forever tag='ABC';

-- 备份快速恢复区文件

backup recovery area to destination '/home/oracle';

backup recovery files; -- 所有文件

-- 控制文件的备份

-- 手工创建控制文件到跟踪文件

alter database backup controlfile to trace;

alter database backup controlfile to trace as '/home/oracle/control.sql' noresetlogs;

-- 恢复目录添加备份文件如添加归档日志到恢复目录

catalog archivelog '/tmp/arch_logs/archive1_731.log','/tmp/arch_logs/archive1_732.log';

catalog start with '/tmp/arch_logs/';

-- block change tracking

alter system set db_create_file_dest = '/u01/app/oracle/oradata/';

alter database enable block change tracking; -- 默认存放到上述指定的路径下

alter database enable block change tracking using file '/../../blcok...';

-- 验证块改变比

select file#,

avg(datafile_blocks),

avg(blocks_read),

avg(blocks_read / datafile_blocks) * 100 as pct_read_for_backup,

avg(blocks)

from v$backup_datafile

where used_change_tracking = 'YES'

and incremental_level > 0

group by file#;

-- 恢复数据库

-- 设定快速恢复时间

alter system set fast_start_mttr_target = 30 scope=both;

-- 考试考对system sysaux 表空间的恢复

shutdown abort;

startup mount;

rman target /

restore datafile 1;

recover datafile 1;

-- 还原点

create restore point before_mods;

create restore point end_q1 as of scn 100;

-- 闪回保证保留时间内能闪回查询数据（想要回退闪回事物操作必须开启附加日志）

-- 可以查看表 dba_flashback_txn_state/report 了解闪回事物回退情况

alter database add supplemental log data;

alter database add supplemental log data (primary key) columns;

alter tablespace undotbs1 retention guarantee;

-- 闪回查询闪回版本查询

-- 考试考闪回事物

-- 调用如下包做事物回退

-- 考试可以使用如下方式，也可以配置使用EM

-- 首先找到事物ID

select xid,operation,undo_sql from flashback_transaction_query where table_name='EMPLOYEES';

declare

v_xid xid_appay;

begin

v_xid:=sys.xid_appay('0050050000035b');

dbms_flashback.transaction_backout(1,v_xid);

end;

select employee_id,salary from employees as of timestamp <time1> where employee_id=200;

select employee_id,salary from employees as of timestamp to_timestamp('2019-06-06 14:14:14','yyyy-mm-ddhh24:mi:ss') where employee_id=200;

select versions_xid,versions_starttime,versions_endtime,salary from employees versions between timestamp <time1> and <time2> where employee_id=200;

alter table employees enable row movement;

flashback table hr.departments to timestamp to_timestamp('2019-06-06 14:14:14','yyyy-mm-ddhh24:mi:ss');

select * from flashback_transaction_query; -- 查询闪回事物记录

-- 闪回删除12C OCM 会考到

flashback table "BIN$employees" to before drop rename to emp_dept;

-- 闪回数据归档(支持DDL操作) 12C 会考到(需要有系统权限 flashback archive administer)

create tablespace fda_tbs1 datafile '+DATA' size 100m;

create flashback archive fda1 tablesapce fda_tbs1 optimize data quota 10M retention 1 year;

alter table hr.employees flashback archive fda1;

-- 闪回数据库

alter system set db_flashback_retention_target=2880 scope=both; -- 两天

alter database flashback on;

flashback database to time = "to_date('2019-06-06 14:14:14','yyyy-mm-ddhh24:mi:ss')";

flashback database to scn=23565;

flashback database to sequence=223 thread=1;

flashback database to timestamp(sysdate-1/24);

flashback database to restore point b4_load;

create restore point before_upgrade guarantee flashback database;

2.Configure Recovery Manager　

3.Use Recovery Manager to perform database backups　

4.Use Recover Manager to perform complete database restore and recovery operations　

5.Configure RMAN　

6.Create different types of RMAN backups to cater for different performance and retention requirements

7.Set Flashback Database parameters

8.Configure a Fast Recovery Area　　

9.Perform various recovery operations using Flashback technology　　

四、Data Management　　

1.Manage Materialized Views to improve rewrite and refresh performance

-- prod1创建允许快速刷新以及允许重写的物化视图

select owner,mview_name from dba_mviews where mview_name='PROD_COST_MV';

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','PROD_COST_MV','SH') from dual;

-- 考试考查询重写及快速刷新（改造他给你的视图）定义刷新机制

-- 使用下面助手

dbms_advisor.tune_mview -- 考试利用这个过程产生新的SQL语句，执行新语句创建物化视图

dbms_advisor.explain_mview -- 解释出物化视图有些什么功能，比如查询重写、快速刷新

dbms_mview.explain_rewrite -- 解释为什么不重写的原因

-- 允许查询重写

create materialized view cust_sales_mv

pctfree 0 tablespace users

storage(initial 1m next 1m pctincrease 0)

build deferred -- build immediate

refresh fast next sysdate + 7

-- refresh complete

-- refresh fast next sysdate +7

-- refresh start with round(sysdate+1)+11/24

-- next next_day(trunc(sysdate),'MONDAY') + 15/24

enable query rewrite

select c.cust_id,sum(amount_sold)

from sales s,customers c

where s.cust_id=c.cust_id

group by c.cust_id;

drop materialized view cust_sales_mv;

exec dbms_mview.refresh('cust_sales_mv','FC','',false,true,0,0,0,false);

variable fail number;

exec dbms_mview.refresh_all_mviews(:fail,'C','',true,false);

variable fail number;

exec dbms_mview.refresh_dependent(:fail,'CUSTOMERS','SALES','CF','',TRUE,FALSE);

variable deplist varchar2(500)

exec dbms_mview.get_mv_dependencies('CUSTOMERS,SALES',:deplist);

dbms_mview.fast_refreshable

create materialized view log on sh.sales

tablespace users

nologging

parallel

with rowid,sequence(cust_id,"AMOUNT_SOLD","QUANTITY_SOLD") i ncluding new vslues;

drop materialized view log on customers;

comment on materialized view cust_sales_mv is 'sales materialized view';

alter materialized view cust_sales_mv refresh fast on commit;

alter materialized view cust_sales_mv compile;

alter materialized view cust_sales_mv disable query rewrite;

alter table cust_sales_mv allocate extent;

alter materialized view cust_sales_mv nologging;

alter materialized view cust_sales_mv log force;

alter materialized view cust_sales_mv truncate partition year_1995;

alter materialized view cust_sales_mv drop partition year_1995;

alter materialized view exchange partition year_1995 with table sales_1995;

alter materialized view MV_name consider fresh;

QUERY_REWRITE_INTEGRITY -- 控制参数完整性(ENFORCED TRUSTED STALE_TOLERATED)

-----------------------------------

dbms_mview.explain_mview

@?/rdbms/admin/utlxmv.sql

truncate table mv_capabilities_table;

BEGIN

DBMS_MVIEW.EXPLAIN_MVIEW('select a.id,b.name,a.rowid a_rowid,b.rowid b_rowid from comp1 a,comp2 b where a.id=b.id');

END;

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('cust_sales_mv','123');

SELECT MSGTXT

FROM MV_CAPABILITIES_TABLE

WHERE CAPABILITY_NAME = 'REFRESH_FAST_AFTER_INSERT';

select capability_name,possible,related_text,msgtxt from mv_capabilities_table where statement_id='123' order by seq;

SELECT * FROM MV_CAPABILITIES_TABLE;

-----------------------------------

dbms_mview.explain_rewrite

@?/rdbms/admin/utlxrw.sql

BEGIN

DBMS_MVIEW.EXPLAIN_REWRITE(QUERY => 'select a.id,b.name,a.rowid a_rowid,b.rowid b_rowid from comp1 a,comp2 b where a.id=b.id',

MV => 'sh.cust_sales_mv' STATEMENT_ID => '');

END;

SELECT MESSAGE FROM REWRITE_TABLE;

alter materialized view cust_sales_mv enable query rewrite;

truncate table rewrite_table;

-----------------------------------

-- 要想让多个相关的SQL走物化视图（一个物化视图满足多个查询，反之也可以，需要去衡量），必须有外键依赖及修改下面参数

alter system set query_rewrite_integrity=trusted; -- 考试改为trusted

alter table sales modify constraint sales_customer_fk norely;

-- 创建一个物化视图对应会有一个物理表（容器表）和一个物化视图

select * from dba_mviews;

select * from dba_objects where object_name='cust_sales_mv';

-- 什么时候该选择物化视图呢

1、分析工作负载

2、使用 SQL access advisor 来帮我们选择

3、可以使用 dbms_mview.explain_rewrite 分析为什么没有使用物化视图

-- 考试考调优物化视图查询重写(考试就考DBMS_ADVISOR.TUNE_MVIEW的使用)

-- DBMS_ADVISOR.TUNE_MVIEW 使用示例

variable task_cust_mv varchar2(30);

variable create_mv_ddl varchar2(4000);

execute :task_cust_mv := 'cust_mv';

execute :create_mv_ddl := ' -

create materialized view cust_sales_mv -

pctfree 0 tablespace users -

storage(initial 1m next 1m pctincrease 0) -

build deferred -

refresh fast next sysdate + 7 -

enable query rewrite -

as -

select c.cust_id,sum(amount_sold) -

from sales s,customers c -

where s.cust_id=c.cust_id -

group by c.cust_id';

execute dbms_advisor.tune_mview(:task_cust_mv,:create_mv_ddl);

execute dbms_advisor.delete_task(:task_cust_mv);

-- chaeck report

create directory tune_results as '/home/oracle';

grant read,write on directory tune_results to sh;

execute dbms_advisor.create_file(dbms_advisor.get_task_script(:task_cust_mv),'tune_results','mv_create.sql');

select statement from user_tune_mview where task_name = :task_cust_mv and script_type='IMPLEMENTATION';

2.Configure and manage distributed materialized views　　配置和管理分布式物化视图

3.Create and Manage encrypted tablespaces　　创建和管理加密表空间

4.Manage Transport of tablespaces across platforms

-- 考试在prod1库考传输表空间考 Solaris 64 拷贝过来的一个文件加载到linux 32(一个dump文件一个数据文件)

-- 考试的时候字节序没有转换，我们需要使用rman转换

-- 查询平台名称

select * from v$transportable_platform;

select platform_name from v$database;

-- 确定字节序

select tp.endian_format v$transportable_platform tp,v$database d where tp.platform_name = d.platform_name;

-- 例如使用 rman 转换

rman

connect target sys@orcl

sql 'alter tablespace hr read only';

convert tablesapce hr to platfoem 'Solaris[tm] OE (64-bit)' format '/tmp/transport_to_solaris/%U';

-- 考试示例 prod1

-- 可以选择配置 dbconsole

emca -deconfig dbcontrol db -repos drop

emca -config dbcontrol db -repos create

alter tablespace tbs read only;

expdp system/oracle directory td dupfile=expdp_tran_tbs.dmp transport_tablespaces=tbs logfile=tran.log

rman target /

convert datafile '/home/oracle/tbs01.dbf' from platfoem 'Solaris[tm] OE (32-bit)' to platfoem 'Linux IA (32-bit)' format '/tmp/transport_to_solaris/tbs01.dbf';

cp /tmp/transport_to_solaris/tbs01.dbf /u01/app/oracle/oradada/prod1/tbs01.dbf

sqlplus / as sysdba

create user sst identified by sst;

grant connect,resource to sst;

create directory td as '/home/oracle';

impdp system/oracle directory=td dumpfile=expdp_tran_tbs.dmp remap_schema=sst:sst transport_datafiles='/u01/app/oracle/oradada/prod1/tbs01.dbf' logfile=tran.log

strings /u01/app/oracle/oradada/prod1/tbs01.dbf|grep more -- 查看数据文件字符集

-- 查看并修改表空间状态

select tablespace_name,status from dba_tablespaces;

alter tablespace tbs read write;

5.Configure a schema to support a star transformation query

-- 考试要求配置查询模式支持星型转换

-- 把如下参数设置为 true 就可以了

-- 注意：星型转换不支持绑定变量，自连接表以及部分分区表

-- 星型转换要求在连接(join)字段的每一个列上面有一个单列位图索引

ALTER SYSTEM SET star_transformation_enabled='TRUE' SCOPE=BOTH;

-- 建立位图连接索引

create bitmap index sales_q_bix on sales(times.calender_quarter_desc)

from sales,times where sales.time_id=times.time_id local;

6.Administer external tables

-- 考试一定要考的手工建立

-- 考试就是用如下方式生成一个外部表，控制文件已经准备好了

sqlldr sh/sh control=info.ctl external_table=GENERATE_ONLY log=info.sql

-- 考试时执行完成上面语句才能将原表删除，然后在用生成的脚本创建外部表

create directory ext_tab_dir as '/usr/apps/datafiles';

grant read on directory ext_tab_dir to sh;

vi info.dat

56,"november,15,1980",baker,mary,alice, 09/01/2009

create table emp_load

(

employee_id char(5),

emp_dob char(20),

last_name char(20),

first_name(15),

middle_name char(15),

hire_date date

)

organization external

(

type oracle_loader

default directory ext_tab_dir

access parameter

(

records delimited by newline characterset US7ASCII

badfile 'ext_tab_dir':'info.bad'

logfile 'info.log'

readsize 1048576

fields terminated by ',' optionally enclosed by '"' ldrtrim

missing filed values are null

reject rows with all null fields

(

"employee_id" char(255) terminated by "," optionally enclosed by '"',

"emp_dob" char(255) terminated by "," optionally enclosed by '"',

"last_name" char(255) terminated by "," optionally enclosed by '"',

"first_name" char(255) terminated by "," optionally enclosed by '"',

"middle_name" char(255) terminated by "," optionally enclosed by '"',

"hire_date" char(255) terminated by "," optionally enclosed by '"' date_format date mask "mm/mm/yyyy"

)

)location ('info.dat')

)reject limit unlimited

parallel;

create table ext_test

(

name char(256),

name_desc char(256)

)

organization external

(

type oracle_loader

default directory ext_tab_dir

access parameters

(

records delimited by newline

badfile ext_tab_dir:'test.bad'

logfile ext_tab_dir:'test.log'

discardfile ext_tab_dir:'test.dsc'

skip 1

fields terminated by';'

reject rows with all null fields

(

NAME char(256),

NAME_DESC char(256)

)

location ('test.csv')

)

reject limit unlimited

parallel;

7.Implement Data Pump export and import jobs for data transfer　

8.Implement Data Pump to and from remote databases　

-- 考试考使用 dblink prod2 连接到prod1

9.Configure and use parallel execution for queries

-- 考试考并行配置

SQL> alter system set parallel_degree_policy=limited; -- auto

SQL> alter system set parallel_max_servers=100 scope=spfile;

SQL> alter system set parallel_min_servers=10 scope=spfile;

SQL> alter system set parallel_min_percent=100 scope=spfile;

SQL> alter system set parallel_adaptive_multi_user=true scope=spfile;

SQL> alter table sh.sales parallel 8;

SQL> alter system set parallel_degree_policy=manual scope=spfile;

-- 执行时间少于5秒就不使用并行

SQL> alter system set parallel_min_time_threshold=5 scope=spfile;

SQL> alter session enable|disable|force parallel [DML|DDL|QUERY](parallel n)

-- General information

v$filestat v$sesstat v$sysstat

-- 并行相关的系统视图

v$px_session v$px_process v$px_process_sysstat v$pq_sesstat v$px_buffer_advice v$pq_tqstat

10.Use SQL*Loader

-- 考试必考使用命令方式

vi salesdec01.dat

133|6538|05-DEC-2001|P|999|1|33.25|

134|6539|06-DEC-2001|P|1999|2|34.25|

-- 在数据库创建一张表

sqlplus sh/sh

create table sales(

prod_id number not null,

cust_id number not null,

time_id date not null,

channel_id varchar2(10) not null,

promo_id number not null,

quantity_sold number(10,2) not null,

amount_sold number(10,2) not null

);

-- 创建一个控制文件

$ vi sales_dec01.ctl

load data infile salesdec01.dat

append into table sh.sales

fields terminated by "|" optionally enclosed by '"'

trailing nullcols

(

prod_id,

cust_id,

time_id date 'yyyy-mm-dd',

channel_id,

promo_id "trim(:promo_id)"(nullif promo_id=''),

quantity_sold,

amount_sold

)

$ sqlldr sh/sh control=sales_dec01.ctl direct=true skip 2

11.Administer, manage and tune parallel execution　　管理和优化并行操作

五、Data Warehouse Management　　数据仓库管理

1.Administer partitioned tables and indexes using appropriate methods and keys　　使用适当地方法和键管控分区表和索引

-- 创建自动分区表

2.Perform partition maintenance operations

-- 考试考分区表 prod1 使用 sh 用户创建分区表(原题)

create table sales_history_2006(

prod_id number not null,

cust_id number not null,

time_id data not null,

channel_id number not null,

promo_id number not null,

quantity_sold number(10,2) not null,

amount_sold number(10,2) not null

)

partition by range(time_id)

interval (numtoyminterval('1','month'))

(

partition sal1 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace sal01,

partition sal2 values less than (to_date('2003-01-01','yyyy-mm-dd')) tablespace sal02,

partition sal3 values less than (to_date('2004-01-01','yyyy-mm-dd')) tablespace sal03,

partition sal4 values less than (to_date('2005-01-01','yyyy-mm-dd')) tablespace sal04,

);

-- 考题引用分区

create table orders(

order_id number(12) not null,

order_date TIMESTAMP,

order_mode VARCHAR2(8),

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

sales_rep_id NUMBER(6),

promotion_id NUMBER(6),

CONSTRAINT orders_pk PRIMARY KEY(order_id)

)

partition by range(order_date)

(

partition Q1_2005 values less than (to_date('20150201','yyyymmdd')),

partition Q2_2005 values less than (to_date('20150301','yyyymmdd')),

partition Q3_2005 values less than (to_date('20150401','yyyymmdd')),

partition Q4_2005 values less than (to_date('20150501','yyyymmdd'))

);

create table order_items(

order_id number(12) not null,

line_item_id number(3) not null,

product_id number(6) not null,

unit_price number(8,2) not null,

quantity number(8) not null,

constraint order_items_fk foreign key(order_id) references new_orders(order_id)

)partition by reference(order_items_fk);

-- SQL Access Advisor 可以帮助我们判断是否需要建立分区

create table example(

idx number,name varchar2(20)

)

partition by range(idx)

(

partition p1 values less than(0) tablespace users,

partition p2 values less than(maxvalue) tablespace users

);

select * from dba_tab_partitions where table_name='EXAMPLE';

delete from example subpartition(sp1);

insert into example partition(sp1) values(4,5,...);

select idx from example partition(p1);

create table com2 as select * from example partition(sp1);

-- 数据泵导出分区数据

expdp sh/sh tables=(SALES:SALES_Q1_2001)

-- 分区索引

create index idx on emp(first_name) global partition by range(first_name)

(

partition x1 values less than('H') tablespace users,

partition x2 values less than(maxvalue)

);

create index idx on hr_emp(first_name) local;

drop index idx;

create index idx on hr_emp(first_name) tablespace indx01 local

(

partition ex1 tablespace index02,

partition ex2 tablespace index03,

partition ex3

);

select * from dba_ind_partitions where index_name='IDX';

3.Maintain indexes on a partitioned table　　

-- 考点

4.Implement securefile LOB　

5.Create and manage LOB segments　　

-- 考题创建和管理LOB段

6.Implement fine-grained access control　

-- 考题细粒度审计

7.Create and manage contexts　　

8.Administer flashback data archive and schema evolution　　

-- 考题管理闪回数据归档和模式演进

六、Performance Management　

1.Administer Resource Manager　　

-- 考试考创建资源计划 prod1，HR 用户作为资源管理用户，这里要授予资源管理权限，必须配置好 GC 才可以做

dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER',grantee_name=>'HR',admin_option=>FALSE);

-- 然后创建一个消费组OLTP和DSS,然后创建资源计划TOP1(OLTP,DSS)

2.Use Result Cache　　

3.Use multi column statistics

-- 考试考收集多列统计信息

exec sys.dbms_stats.create_extended_stats(ownname => 'SH',tabname => 'CUSTOMERS',extension => '(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)');

select * from dba_tab_col_statistics where table_name='CUSTOMERS' and owner='SH';

select * from dba_tab_cols where table_name='CUSTOMERS' and owner='SH';

select name,value from v$sys_optimizer_env;

begin

dbms_stats.gather_table_stats(

ownname => 'SH',

tabname => 'CUSTOMERS',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

cascade =>DBMS_STATS.AUTO_CASCADE,

degree => null,

no_invalidate => DBMS_STATS.AUTO_INVALIDATE,

granularity => 'AUTO',

methods_opt => 'FOR ALL COLUMNS(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID) SIZE 10'

);

end;

4.Gather statistics on a specific table without invalidating cursors Use partitioned indexes　　

5.Administer and tune schema object to support various access methods Interpret execution plan　　

6.Use SQL tuning tools and features　　

7.Use SQL Tuning Advisor　

-- 使用助手

8.Use SQL Access Advisor　　

9.Use SQL Performance Analyzer Configure baseline templates　　

10.Use SQL Plan Management feature Implement instance caging　　

-- 考试考SQL计划管理，考试的时候要求把执行计划抓取出来，只需要把如下参数改为TRUE就可以

alter system set optimizer_capture_sql_plan_baselines=TRUE;

select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines;

exec :cnt := dbms_spm.alter_sql_plan_baseline(

sql_handle => 'SYS_SQL_....',

plan_name => 'SYS_SQL_PLAN_...',

attribute_name => 'ENABLED',

attribute_value => 'NO'

);

declare

c_re number;

begin

c_re := dbms_spm.drop_sql_plan_baseline('SQL_...','SQL_PLAN_...');

end;

七、Grid Infrastructure and ASM

1.Install Oracle Grid Infrastructure Create ASM Disk Groups　

2.Create and manage as ASM instance Implement ASM failure groups Creating ACFS File System　　

-- 考试要求创建acfs快照注意使用命令操作

$ acfsutil -h|grep snap -- 查看帮助

$ acfsutil snap create/delete SNAP01 /u01/app/oracle/db_files

$ cd /u01/app/oracle/db_files

$ ls -ltr .ACFS

$ acfsutil info fs <mount point> ls -l <mount_point>/.ACFS/snaps

$ acfsutil info fs -- 查看信息

-- 可以通过快照恢复数据到创建的时间点

-- 必考创建和管理ASM实例，实现ASM故障恢复（失败组）组，创建ACFS ASM集群文件系统

-- 拷贝文件到ACFS 并备份acfs

3.Start, stop, configure and administer Oracle Grid Infrastructure　

4.backup grid aource ocr.

-- OCR做镜像备份

八、Real Application Clusters

1.Install the Oracle Database 11gR2 software

-- 通过VNC远程连接上去安装

# vncvlewer host01:2 -- 输入密码就可以登录到需要安装集群的机器上

$ cd /stage/grid

$ ./runInstaller

-- 也可使用SSH 先登录本地oracle用户

# xhost +

# su - oracle

$ ssh -X host01

# dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK01 bs=1024K count=10

-- 考试要求重启机器后不自动启集群 CRS

# $GRID_HOME/bin/crsctl config crs

# $GRID_HOME/bin/crsctl disable crs

-- CRS CHECK

# crsctl query css votedisk

$ cat /etc/oracle/ocr.loc

$ grep voting <GI_HOME>/log/<hostname>/ceed/ocssd.log

$ cluvfy comp ocr -n all -verbose

$ ocrcheck

# crsctl add css votedisk path_to_voting_disk -- 只能把CRS放到ASM或是集群文件系统

# crsctl replace votedisk +asm_disk_group

# crsctl delete css votedisk path_to_voting_disk

$ crsctl query crs activeversion

-- 考试让你加一块 OCR 镜像盘上去执行下面命令(考试就加FRA磁盘组)

# ./ocrconfig -add +FRA

# ./ocrconfig -add /dev/sde1

# ./ocrconfig -replace /dev/sde1 -replacement +DATA2

# ./ocrconfig -repair -add +DATA1

# ./ocrcheck -- 就可以看到两个磁盘组

-- 考点 CRS 的备份

# ./ocrconfig -showbackup manual

# ./ocrconfig -manualbackup -- 物理备份

# ./ocrconfig -export /home/oracle/ocr.backup -- import 导入配置

-- 考点备份 OLR(oracle local register)

# ./ocrdump -local /home/oracle/MYOLR -- 考试(两个节点)

# ./ocrdump -local

-- 恢复OCR

# ./ocrconfig -showbackup

# ./crsctl stop cluster -all

# ./crsctl stop crs

# ./ocrconfig -restore /u01/app/.../cdata/cluster01/day.ocr

# ./ocrconfig -import /shared/export/ocrback.dmp

# ./crsctl start crs

$ cluvfy comp ocr -n all

-- 网络设置

$ oifcfg iflist -p -n

$ oifcfg getif

$ srvctl config nodeapps -a

$ srvctl stop service -d RDBA -s sales,oltp -n host01

$ srvctl config vip -n host01

$ srvctl stop listener

$ srvctl stop vip -n host01

$ srvctl modify nodeapps -n host01 -A 192.168.2.125/255.255.255.0/eth0

$ srvctl config nodeapps -n host01

$ srvctl start vip -n host01

$ cluvfy comp nodecon -n all -verbose

-- 修改私网网卡 IP

$ oifcfg setif -global eth2/192.0.2.0:cluster_interconnect

# oifcfg getif

# crsctl stop crs

# ifconfig eth2 192.0.2.15 netmask 255.255.255.0 broadcast 192.0.2.255

$ oifcfg delif -global eth1/192.168.1.0

# crsctl start crs

-- ADD SCAN IP

$ srvctl add scan -n cluster01-scan

$ srvctl remove scan [-f]

$ srvctl add scan_listener

$ srvctl add scan_listener -p 65536 -- using mondefault port number

$ srvctl remove scan_listener [-f]

$ srvctl modify scan -n cluster01-scan

$ srvctl modify scan_listener -u

2.Configure ASM for the shared disks and create a clustered database Configure archiving

3.Configure services using both Manual and Policy Managed methods　　

-- 注意使用手动和策略管理方法配置服务

-- 还会考创建一个服务只允许访问一个节点

4.add and delete cluster node.

-- add cluster node

-- 在存在的节点执行检查

-- 硬件及操作系统检查

$ cluvfy stage -post hows -n node3

$ cluvfy comp peer -refnode node1 -n node3 -orainv oinstall -osdba asmdba -verbose

$ cluvfy stage -pre nodeadd -n node3

$ export IGNORE_PREADDNODE_CHECKS=Y

$ $GI_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"

# /u01/app/oraInventory/orainstRoot.sh

# /u01/app/11.2.0/grid/root.sh

$ cluvfy stage -post nodeadd -n node3-verbose

-- delete cluster node

node1# crsctl unpin css -n node3

node3# $GI_HOME/crs/install/rootcrs.pl -delete -force

node1# crsctl delete node -n node3

node3$ $GI_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=GI_HOME "CLUSTER_NODES={node3}" CRS=TRUE -local

node3$ ./runInstaller -detachHome ORACLE_HOME=/u01/app/11.2.0/grid

node3# ./deinstall -local

node1$ $GI_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/11.2.0/grid "CLUSTER_NODES={node1,node2}" CRS=TRUE

node1$ cluvfy stage -post nodedel -n node3 [-verbose]

九、Data Guard

1.Create Physical Standby Database with real-time apply

-- 手工创建

-- 注意：配置使用连接串的大小写要一致

-- primary change to force logging mode;

SQL> select log_mode,force_logging from v$database;

SQL> alter database force logging;

-- primary parameter

*.audit_file_dest='/u01/app/oracle/admin/prod2/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/prod2/control01.ctl','/u01/app/oracle/oradata/prod2/control02.ctl'

*.db_block_size=8192

*.db_domain='com'

*.db_name='prod2'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod2XDB)'

*.log_archive_dest_1='location=/u01/app/oracle/archive_log'

*.memory_target=524288000

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='prod2'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod2,prod2sby)'

*.LOG_ARCHIVE_DEST_2='SERVICE=prod2sby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod2sby'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'

*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

*.FAL_CLIENT = 'prod2'

*.FAL_SERVER = 'prod2sby'

alter system set standby_file_management=auto;

-- primary tns

pc01sby1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)

(HOST = edbvr6p2.us.oracle.com)

(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = pc01sby1.us.oracle.com)

)

-- standby parameter

*.audit_file_dest='/u01/app/oracle/admin/prod2/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/prod2/control01.ctl','/u01/app/oracle/oradata/prod2/control02.ctl'

*.db_block_size=8192

*.db_domain='com'

*.db_name='prod2'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod2XDB)'

*.log_archive_dest_1='location=/u01/app/oracle/archive__prod2_log'

*.memory_target=524288000

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='prod2sby'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod2,prod2sby)'

*.LOG_ARCHIVE_DEST_2='SERVICE=prod2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod2'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'

*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

*.FAL_CLIENT = 'prod2sby'

*.FAL_SERVER = 'prod2'

alter system set standby_file_management=auto;

-- standby listener UNKNOWN

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = pc01sby1.us.oracle.com)

(OACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = pc01sby1)

)

-- standby listener READY

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = edbvr6p1.us.oracle.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

-- standby tns

pc01prmy =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)

(HOST = edbvr6p1.us.oracle.com)

(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = pc01prmy.us.oracle.com)

)

-- 以上配置好后进行活动数据库复制

-- rman target sys/oracle@pc01prmy auxiliary sys/oracle@pc01sby1

rman target /

connect target sys/oracle -- primary db

connect auxiliary sys/oracle@pc01sby1 -- standby db

run {

allocate channel prmy1 device type disk;

allocate channel prmy2 device type disk;

allocate channel prmy3 device type disk;

allocate channel prmy4 device type disk;

allocate auxiliary channel stby device type disk;

-- duplicate target database for standby;

duplicate target database for standby from active database;

-- duplicate target database for standby from active database nofilenamecheck;

}

rman target sys/oracle@pc01prmy auxiliary sys/oracle@pc01sby1

run{

allocate auxiliary channel aux1 device type disk;

allocate auxiliary channel aux2 device type disk;

duplicate target database to auxdb;

}

-- 在主备库都增加配置备用日志文件，如果主库有三组日志文件，则在备库创建四组日志，比主库多一组；如果是rac集群则比主库多两组。

alter database add standby logfile group 4 ('/u01/app/oracle/oradada/redo04.log') size 100M;

alter database add standby logfile group 5 ('/u01/app/oracle/oradada/redo05.log') size 100M;

alter database add standby logfile group 6 ('/u01/app/oracle/oradada/redo06.log') size 100M;

alter database add standby logfile group 7 ('/u01/app/oracle/oradada/redo07.log') size 100M;

-- alter database add standby logfile thread 1 '+REDO' size 100M; -- 默认thread 1 也就是第一节点

-- standby db 启用实时应用日志

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select process,status from v$managed_standby;

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

SQL> alter system switch logfile;

-- ORA-01152

SQL> alter database recover managed standby database disconnect from session;

SQL> select dest_name,status from v$archive_dest;

SQL> select * from v$archive_gap;

-- 把没有传到备库的日志拷贝过去并注册到备库

SQL> alter database register physical logfile '/u01/app/oracle/archive_log/1_6_928338773.dbf';

SQL> alter database recover managed standby database disconnect from session;

-- EM 创建ADG --------------------------------**********-------------------------------------------

-- broker 命令方式进入 broker 直接输入 DGMGRL

-- 注意静态监听GLOBAL_DBNAME的配置(db_unique_name_DGMGRL.do_main)，主备库添加如下配置

-- 主库

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = prod2_DGMGRL.com)

(OACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = prod2)

)

SQL> alter system set dg_broker_start=true; -- ps -ef|grep dmon

$ dgmgrl

connect sys/oracle@prod2

-- 创建主库配置

create configuration dgconfig1 as primary database is prod2 connect identifier is prod2;

-- 在主库增加备库配置

add database prod1 as connect identifier is prod1 maintained as physical;

-- 查看配置

show configuration verbose; -- show configuration verbose prod2;

-- 启用配置

enable configuration;

-- 编辑配置

edit database prod2 set property LogXptMode='SYNC';

edit database prod1 set state='APPLY-OFF'; -- 不应用日志

edit database prod2 set state='TRANSPORT-OFF'; -- 不传输日志

-- 备库

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = prod1_DGMGRL.com)

(OACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = prod1)

)

remove database 'prod2' [PRESERVE DESTINATIONS];

remove configuration [PRESERVE DESTINATIONS];

-- 通过GC EM 创建物理备库 ---------------------------------------------------------

-- EM 创建broker configuration

-- EM 创建物理备库

-- add a database to a broker configuration

-- 考试主库 prod1 备库 sbdb1

-- EM 图形化界面创建备库方式省略

-- 最大保护模式

-- 修改备库为 SYNC 同步

edit database sbdb1 set property LogXptMode='SYNC';

edit dataase set protection mode as MaxAvailability; -- 考试改为最大可用模式

2.Configure the data guard environment to reduce overheads of fast incremental backups on the primary database

-- 主要配置块改变跟踪(使用备库做备份)

select * from v$block_change_tracking;

alter database enable block change tracking using file '/u01/.../block.trc'

3.Configure the Observer　

4.Switchover and switch back

-- 主备切换，切换过后一定要切换回来

-- switchover failover

-- switchover

dgmgrl

connect sys/oracle@prod1

switchover to sbdb1;

-- failover

failover to sbdb1 [IMMEDIATE];

-- 重新恢复主库

REINSTARTE DATABASE prod1;

ENABLE DATABASE prod1;

5.Configure connect time failover

-- ADG 最后一题快速启动failover -- 前面的题做好了我推荐不做这题，以免切换失败

-- Fast Failover

edit configuration set property FastStartFailoverLagLimit = {n};

edit configuration set property FastStartFailoverThreshold = threshold-val;

edit database prod1 set property LogXptMode=SYNC/ASYNC;

edit database sbdb1 set property LogXptMode=SYNC/ASYNC;

edit configuration set protection MODE AS Maxvailability/MaxPerformance; --

edit configuration set property FastStartFailoverTarget = sbdb1;

edit configuration set property FastStartFailoverTarget = prod1;

edit configuration set property FastStartFailoverAutoReinnstate = {TRUE|FALSE}

edit database prod1 set property ObserverConnectIdentifier = '';

enable FAST_DTART FailoverCondition "value" --

enable FAST_START FAILOVER;

START OBSERVER;

STOP OBSERVER;

show fast_start failover;

REINSTATE DATABASE prod1; -- restart init

-- 考试的时候在偶数机使用服务控制工具srvctl去创建一个服务也就是在备库创建

-- 注意sbdb1也需要加入到ASM里面去 . oraenv -> +ASM -> 添加数据库

srvctl add service -d <db_unique_name> -s <server_name> [-l PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY] [-y AUTOMATIC|MANUAL]

srvctl add service -d sbdb1 -s s0001 -l PHYSICAL_STANDBY -y AUTOMATIC;

srvctl start service -d sbdb1 -s s0001 -- 先把备库切换为主库启动服务，然后在切换回来服务就自动起来了

srvctl config service -d sbdb1 -s s0001

-- 服务起来过后去奇数机创建服务(也就是偶数机创建服务奇数机创建连接串)

netmgr -> db_prod -> gcprod2(连接到偶数机) -> s0001(服务名在偶数机上面查看lsnrctl status)

-- 如果没有勾选 Configure Standby Database with Oracle Restart 则在备库执行下面语句手动添加（-a diskgroup_list）

srvctl add database -d sbdb1 -o $ORACLE_HOME -m gcprod2 -p $ORACLE_HOME/dbs/spfilesbdb1.ora -r PHYSICAL_STANDBY -n prod1 -a "SBDAT,SBFRA"

-- 注意考试的时候偶数机要作为主库

6.Convert the standby to a snapshot standby

-- 图形界面点下就可以（是备库而不是快照）

-- snapshot standby -- EM图形界面就可以帮助我们完成（是备库而不是快照）

-- 在物理备库的基础上把物理备库转换成快照备库，快照备库可以以读写模式打开做DML操作

-- 快照备库可以接受主库的日志但是不会应用，创建快照备库的前提是数据库开启闪回数据库

-- 创建快照备库前需要创建一个保证还原点，用完了要退回来取消快照备库到物理备库

-- 快照数据库不能做主备切换和failover操作，如果只有一个备库并且在最大保护模式也不能做快照备库

-- 手工转换

DGMGRL

connect sys/oracle@prod1

convert database sbdb1 to snapshot standby;

SQL> select flashback_on from v$database;

SQL> select name from v$restore_point;

SQL> select database_role from v$datbase;

convert database sbdb1 to physical standby;

-- EM 转换点击下 convert 就可以

7.Configure archivelog deletion policy for the Dataguard configuration　

-- 在备库配置删除归档策略，一定要备库应用过后才能删除

以上是考试之前在自己搭建的环境下练习记录的笔记，考试环境自己可以模仿搭建，建议多多练习，

本人考试感觉如果考试过程中出错时间可能不是很充裕哦。

Oracle 11g OCM 考试练习笔记

热门文章

最新文章

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle 11g OCM 考试练习笔记

热门文章

最新文章

相关电子书

推荐镜像